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.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; GO -- Truncate the log by changing the database recovery model to SIMPLE. ALTER DATABASE DatabaseName SET RECOVERY SIMPLE; GO -- Shrink the truncated log file to 1 MB. DBCC SHRINKFILE (DatabaseName_Log, 1); GO -- Reset the database recovery model. ALTER DATABASE DatabaseName SET RECOVERY FULL; GO
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.