Thursday, December 13, 2007

Abnormally LDF File Grow Problem in SQL Server 2005

Recently I had faced a peculiar problem with my database LDF file. My total database MDF file size was 12 MB but the LDF file size was unexpectedly 21 GB!!! As a result I couldn't take the backup of the LDF file to our web server since our web server had only 1 GB free space. I have solved the problem by the following SQL command

BACKUP LOG databaseName WITH TRUNCATE_ONLY
GO
DBCC SHRINKFILE (N'database_log' , 2)

Note: If you get "file in use" error message from the above command, try changing the permission to single user mode.

The above command shrink my database log file to 2 MB. Its awesome and solve my problem within few seconds. Hope it will help. :)

Happy Programming!!!

No comments: