I would like to say that as one of my production database (.ldf) log file is growing larger than databse size.

The size of database file (.mdf) and their log file (.ldf) is as follows:-

1)database file (.mdf) size is 29 MB
2)log file (.ldf) size is 100 GB

I am working as a DBA, still i don't have any information of that database. Due to System Administrator was maintaining to that SQL Server database. Before hiring to me, no one was as a DBA here. That's why he was maintening the windows as well as MS SQL Server services.

Before two day ago he called to me and told to me this (.ldf) file is growing to day by day. But the database (.mdf) file lesser than that.

First i have checked out the "Recovery Model" of the database. I did found out "Full Recovery Model".

First of all , I have taken full backup of Database with "full" recovery model. Still i didn't take backup of log file (.ldf). I have googling related to this topic and find out so many blogs like

http://www.exactsoftware.com/docs/DocView.aspx?DocumentID=%7Bb66e7524-5818-4867-8a1c-bb3e4246d305%7D, https://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx,
http://www.sqlskills.com/blogs/paul/importance-of-proper-transaction-log-size-management/ \, How does shrinking a SQL Server log file affect performance?, Does .bak file also contain backup of ldf file? ,https://msdn.microsoft.com/en-us/library/ms178052.aspx, and finall this MSDB BOL


Before shrinking the log file (.ldf) of 100 GB, i want to some expert advice . what he will advice to this.

My working Environment is :

1)Windows Server 2012 R2
2) MS SQL Server 2012
3) Recovery Model of that database is "Full Recovery Model"
4) Windows is in Domain Environment
5)VMware Virtual Platform
6) Database (.mdf) Size is 29 MB
7)Log file (.ldf) size is 100 GB
8) No Mirroring of database
9)NO Replication of databse
10) NO AlwaysOn

As per Paul Randal Blog, if i will go 6 point **"Switch to SIMPLE when it fills up, shrink the log, then switch back to FULL"** . http://www.sqlskills.com/blogs/paul/importance-of-proper-transaction-log-size-management/ and also as per MSDN BOL https://msdn.microsoft.com/en-us/library/ms189493.aspx

Shrinking a log file to a specified target size
USE DatabaseName;
-- Truncate the log by changing the database recovery model to SIMPLE.
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (DatabaseName_Log, 1);
-- Reset the database recovery model.

This is a right procedure to shrink the log file (.ldf) of particular production database. Or , Is there any suggestion or idea to shrink (.ldf) file apart from this procedure will be appreciated.

  • Is it necessary to recover some of the space that the log file hold or just keep it from growing? – Aaron Dec 6 '15 at 16:41
  • @Aaron,"To recover some of the space that the log file hold". Due to report database.And want to shrink (ldf) file from 100 GB to 20 GB . As per management direction. – Md Haidar Ali Khan Dec 6 '15 at 16:58