Database in Suspect Mode… Frowny Face.

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
GO
ALTER DATABASE DatabaseName SET SINGLE_USER
GO
DBCC CheckDB (DatabaseName , REPAIR)
GO
ALTER DATABASE DatabaseName SET MULTI_USER
GO

Presto, the database was now fully functional and accessible. A very quick fix, to what had the potential to really ruining my Tuesday morning.

Cheerio!

Advertisements

How to use SHRINKFILE

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
GO
USE databasename
GO
DBCC SHRINKFILE(‘databasename’, 2)

GO

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.