June 29, 2011 at 10:51 am
We've had a few instances with sharepoint where a database would be in a shutdown state, determined by running:
SELECT DATABASEPROPERTY ( 'MyDatabaseName' , 'IsShutDown' )
This select returns 1 if the database is shutdown.
The recovery is to create a second log file using:
sp_add_log_file_recover_suspect_db MydatabaseName, MyDatabaseLogicalLogFile,
'C:\Data\MyDatabasePhysicalPathLog2.ldf',
'1MB'
It appears that running out of disk space is the catalyst but in other circumstances running out of disk space just caused an error, we increased space then everything was fine.
I also noticed in the log that a transaction was rolled back during the recovery.
Looking for any help on why the database enters a shutdown state and how to prevent this.
June 29, 2011 at 1:04 pm
Next time it happens, check the actual database status. Status_desc in Sys.databases and check the error log.
There's not enough information here to give you a reason.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 29, 2011 at 1:09 pm
Shot in the dark... could it be auto_close on?
June 29, 2011 at 3:21 pm
In this case it was a DEV database and may have been in this state for a long time. it did have a primary file group full error in the log on the 3rd but the error reported by the SP admin appeared to begin on the 20th and was in the sql error log as well with no apparent reason as to why it began other than the DB was in shutdown state.
I will wait till this happens again and check the status description.
also auto close is set to false for that database.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply