Database go suspend...

Hew... I was almost dead in the last 30 minutes.

Here's the story... our MOM server is disk full. The rapid growth of log file of SystemCenterReporting database hogging the whole drive.

At first I tried to set it to single mode and detach it... But for some reason it lasts forever... I had to cancel the process.. that's was bad by the way. Then after I restart the server the database was on suspend mode...

After googling for 10 minutes and trying different solution. I decided to give a shot... and ... it's works!


Here' s how:



Use Master
Go
sp_configure 'allow updates', 1
reconfigure with override
Go


select * from sysdatabases where name = 'SystemCenterReporting'

--Then write down the status value. My case is 24

begin tran
update sysdatabases set status = 32768 where name = 'SystemCenterReporting'

commit tran


--Stop and restart SQL server.




DBCC rebuild_log('SystemCenterReporting','E:\Program Files\Microsoft SQL Server\MSSQL\Data\Repdata.log')

By default now you should be in "DBO connection only" mode. If you're not sure, try this:

sp_dboption 'SystemCenterReporting', 'single user', 'true'


but If you were in DBO ONLY mode, you will get error saying that you cannot set to single mode while in dbo only mode. In this case you need to first reset it to non-dbo only mode:
sp_dboption 'SystemCenterReporting', 'dbo use only', 'false'

Then set it to single user mode:
sp_dboption 'SystemCenterReporting', 'single user', 'true'

Now you should see the new log file for this database under the path you specified.

Next, check the database:

DBCC checkdb ('SystemCenterReporting',REPAIR_ALLOW_DATA_LOSS)
Go
--It takes about 30 minutes to check the 60G database.

begin tran
update sysdatabases set status = 24 name = 'SystemCenterReporting'

commit tran
Go
sp_configure 'allow updates', 0
reconfigure with override
Go