The other day I received a call from a developer telling me that they could not access a particular database in one of our lower environments. After a bit of investigation, I found the database causing the problem, it was in ‘Suspect’ Mode. Here is what I did to correct the issue.
ALTER DATABASE DatabaseName SET EMERGENCY
ALTER DATABASE DatabaseName SET SINGLE_USER
DBCC CheckDB (DatabaseName , REPAIR)
ALTER DATABASE DatabaseName SET MULTI_USER
Presto, the database was now fully functional and accessible. A very quick fix, to what had the potential to really ruining my Tuesday morning.
I was bouncing around a few of my SQL servers today, and found one of my system drives was below 300mb. Hmmmm can’t be good for business, so I dug a bit deeper. Turns out I had an .ldf file that was nearly 14gbs, ouch! All because the database recovery model was set to FULL, and we were only performing full backups nightly. To remedy the problem is actually very easy.
Backup up the log first:
BACKUP LOG MODEL WITH TRUNCATE_ONLY
Then run this shrink command:
ALTER DATABASE databasename SET RECOVERY SIMPLE
DBCC SHRINKFILE(‘databasename’, 2)
The number 2 represents the target size you want the database in megabytes.
In my case I went from nearly 14GB to only 6.62MB, it gets as close to your target number as possible.
Posted in Backups, DBCC, Full, Log Files, Recovery Model, SHRINKFILE, Simple
- Tagged Backups, DBCC, Full, Log Files, Recovery Model, SHRINKFILE, Simple