Suspect database

I got several suspect database this morning after my machine was reboot. I checked the sql server error log, found following entries:
2005-11-09 09:51:36.75 spid11    Starting up database ‘DBName’.
2005-11-09 09:51:36.75 spid11    udopen: Operating system error 5(Access is denied.) during the creation/opening of physical device DBFile.mdf.
2005-11-09 09:51:36.75 spid11    FCB::Open failed: Could not open device DBFile.mdf for virtual device number (VDN) 1.
2005-11-09 09:51:36.92 spid11    Device activation error. The physical file name ‘DBFile.mdf’ may be incorrect.
2005-11-09 09:51:37.00 spid11    Device activation error. The physical file name ‘DBLog.ldf’ may be incorrect.
My SQL server was running under a domain account. I temporarily saved some private files into the sub-folders that hosts the database files several days ago. Since I don’t want others to see the those files, I chanegd the file access permission to the folder. This caused SQL server running account not be able to access database files.
I added the SQL server running account into the access list, rebooted the SQL server service, all databases come back normal.
So if you got suspect databases, the following is the check list:
1) check the drive to have enough space. If it does not, remove some files, and run the
following command to reset the database to normal:
use master
exec sp_resetstatus @DBName =’DBName’
Then add more files from other drives to the database.
2) If the drives have enough space, check SQL errorlogs. Check access permissions.
3) If everything is OK, the reason may be caused by corrupted log file.  Use the follwoing command to rebuild the log:
Sp_configure allow, 1
Reconfigure with override
Update sysdatabases set status = 32768 where name = ‘DBName’
— SQL 6.5, -32768; SQL 7.0 or 2000, 32768
–dbcc rebuild_log(Db_Name, new_log_file)
dbcc rebuild_log(‘DBName’,’DBName_log.ldf’)
Sp_configure allow, 0
Reconfigure with override
This entry was posted in SQL server. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s