Database log file size issue

Recently I faced a problem where my SQLServer 2005 ran out of space for log files. Usually the log files can be reduced by shrinking them but this time the transaction logs were so much huge that I was not even allowed to run shrink log command.

My DB was in Recovery phase.

My friend Dagupati ( a professional DBA ) helped me getting out of this trouble.

Following are the steps to be followed in sequence to achieve this.

1) Take the DB into emergency mode.

ALTER DATABASE yourDBname SET EMERGENCY

2) Bring DB to single user mode.

ALTER DATABASE yourDBname SET SINGLE_USER

3)Repair you DB

DBCC CheckDB ('yourDBname', REPAIR_REBUILD)

if the above command fails, try this

DBCC CheckDB ('yourDBname', REPAIR_ALLOW_DATA_LOSS)

NOTE : This may result in dataloss

4) Bring you DB to multi user mode

ALTER DATABASE yourDBname SET MULTI_USER

By now you should have your DB up & running.

DB at times enter into SUSPECT mode - restarting server should solve them if not follow the above steps.

To be on safer side take full / transactional backups regularly