I had a database that was working in FULL recovery mode but never had transaction log backups done.
The consequence of that was the fact that the log file kept growing and now its 10 GB in size.
I configured scheduled backups for this database (full + differential + log) and now log % space used is around 1%.
I want to shrink the 10GB file to a sensible size (maybe 200MB with autogrow 5 MB?).
How can I do it and what are the consequences of shrinking the file?
Best Answer
IMHO, 5MB autogrowth is not sensible. You should monitor your log growth using default trace and find a good value for autogrowth.
Below script will help you monitor your Log (and Data file) autogrowths. Trend it over a week (or whatever suits your need). This will help you get a "near to best number" for your autogrowth setting.
This is not true. When you take "Log Backups", SQL Server - for databases in proper FULL or BULK_LOGGED recovery models will truncate the transaction log i.e the portion of transaction log will be marked as "no longer needed" and will be overwritten.
Read up more on : Misconceptions around the log and log backups: how to convince yourself and 8 Steps to better Transaction Log throughput
You can use
DBCC SHRINKFILE(yourdb_log, 200);
. Remember to useSHRINKFILE
and NOTSHRINKDATABASE
.I would suggest you to be PROACTIVE (monitor your log autogrowths and give a sensible value for autogrowth) rather being REACTIVE (frequently shrink log file, because you think that you will need disk space (or for whatever reasons)).
You should read up answers from Mike Walsh and Aaron Bertrand for Why Does the Transaction Log Keep Growing or Run Out of Space? to understand the consequences of shrinking log file.