Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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

share|improve this question
    
Do they have backups of the databases in question? –  joeqwerty Feb 3 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. –  Aaron Feb 3 at 19:28

1 Answer 1

up vote 3 down vote accepted

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.

share|improve this answer
    
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. –  Aaron Feb 3 at 19:40
    
You will need to be at least an sa user to run these. –  DanBig Feb 3 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. –  Aaron Feb 3 at 21:45

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.