0

We have a client using our software with MSSQL and we are trying to solve a problem for them. We work more with MySQL so pardon my ignorance in the ways of MSSQL Server.

They have 3 volumes on that physical server: C:\; D:\; and L:\ The D:\ volume holds the SQL DBs, the L:\ volume holds the transaction logs.

The L:\ volume filled up due to some really awful preparation and management and before contacting us, they stopped SQL, moved the SQL transaction logs off to the D:\ where there was space, proceeded to make a new L:\ raid with larger physical disks, then moved the transaction logs back and restarted SQL services.

Since this point, they have been unable to login to SQL Management Studio or access the databases.

I was able to get in, but all the databases are flagged as (Suspect). I am unable to retrieve any properties on the databases at all.

Some Googling turned up some suggested queries to run against the databases to fix this problem, but I am reluctant to run them. Looks like part of it is a DBCC Check against the databases, but i'm not familiar with the rest.

HERE is that link.

Anyone have any advice on fixing this issue or know what may have occurred?

Thanks in advance for you assistance,

Aaron

Improve this question
2
  • Do they have backups of the databases in question? Commented Feb 3, 2014 at 18:27
  • This is SQL Server 2008 R2 Enterprise (not sure if that matters) and they currently can't find the password for their SysAdmin user. We can get in with our low level "public" user, but I don't think we are going to be able to run that script with the level of access we have. I can pass it along to their "DBA", but I think their primary issue right now is that they can't even get logged in. Commented Feb 3, 2014 at 19:28

1 Answer 1

Reset to default
3

Backup the database (stop service, copy the .mdf), and run that script. The REPAIR_ALLOW_DATA_LOSS line has the potential to be destructive.

Script here for future ref:

EXEC sp_resetstatus [YourDatabase]; ALTER DATABASE [YourDatabase] SET EMERGENCY DBCC checkdb([YourDatabase]) ALTER DATABASE [YourDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE DBCC CheckDB ([YourDatabase], REPAIR_ALLOW_DATA_LOSS) ALTER DATABASE [YourDatabase] SET MULTI_USER

If it is any consolation, our product uses merge replication, so we have thousands of laptops out in the field running SQL Express, and we see suspect databases regularly. Running that exact script after backing up the databases fixes the issue 99% of the time with no adverse effects.

As for the reason, its hard to say, and you may have to do some digging through the logs. Likely, if it is Express, it will be limited to what is turned on for logging, but possible reasons are:

  1. Improper shutdown of the SQL Server Service (like kill of the service, hard restart of machine)
  2. Hardware failure
  3. Not enough disk space when writing data
  4. Various corruptions of db file

All else fails, restore the backups, if they exist. The steps above should get you running in short order, though.

Improve this answer
3
  • I think we are going to give this a try. It may not let us run these queries against the databases without a higher-level user. They are currently trying to "break in" to the management studio. Thanks very much for the help. Commented Feb 3, 2014 at 19:40
  • You will need to be at least an sa user to run these. Commented Feb 3, 2014 at 19:46
  • 1
    Thanks. They finally got back in as their SA user and I had them run the script as suggested. We are now able to get back into the database s and thus, the program. Thanks a million, DanBig. Commented Feb 3, 2014 at 21:45

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.