tshad (4/25/2013)
My Transaction log is about 3GB and I can't shrink it.It is using a FULL backup and I try over and over again to back and shrink it and it won't do it.
DECLARE @DatabaseName VARCHAR(50);
SET @DatabaseName = 'myDatabase'
SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases
WHERE name = @DatabaseName
This gives me:
LOG_BACKUP in the log_reuse_wait_desc.
I have been all over the web looking for an answer but anything I try still leaves it the same size. I tried to change the inital size to 600MB but that didn't work.
Any ideas what I am missing?
Thanks,
Tom
Hi Tom,
There's a ton to learn about the Log File (LDF, usually).
In the area of performance, the initial settings are horrible. It'll grow the log file in a very fragmented way which can have a great impact on performance. It'll also allocate some pretty small "VLF's" or "Virtual Log Files". Think of it kind of like having a disk with a super small sector size. Not very effective. For more on how to "right size" the underlying VLF's in the Sql Server log file, please see the following URL.
http://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/
That brings us to the next point. How big is your database and how big is the largest table in the database? There are a few more aspects to database maintenance that use the log file and that you must consider when trying to right size the log file. Index maintenance and the rebuilding of statistics. Both are extremely import to performance especially when you have nightly jobs that affect or read from many rows and both are logged in the log file when they occur just in case something goes wrong. The link to Gail's article that you were previously provided will help there quite a bit. I use the rule of thumb that the log file should be about 1.5 times the size of the largest table to start with and then should be managed for growth to control the size of the VLFs that I previously spoke of. Log file growth should never be a surprise (should never happen automatically) in my humble opinion. Neither should the data file growth.
Another thing to consider is how important your data is. Way too many people will set a database to "Simple" recovery mode because it's a small database and they do nightly full backups and think that's enough. If the data is important, nothing could be further from the truth. Ask yourself how many minutes or hours of data your company can afford to lose and then setup log backups to run in no more than that amount of time. Personally, I won't tolerate the loss of any data so I do "Point-in-Time" backups on my production databases every 15 minutes. That also helps keep the log size down on busy systems. "Point-in-Time" backups require that only the FULL recovery mode be used. If you go to the Bulk Logged mode, then every backup taken while you're in that mode must be used in it's entirety or not at all during a restore... and you can't skip logs. That's why they call it a "log chain".
Getting back to your original problem, step 1 is to visit the articles I recommended above and figure out what the correct size log file should actually be. Even if you decide it's too small and needs to grow, if the initial settings were left at the default, consider blowing away the log and starting over.
Step 1 would be to take a full backup.
Step 2 would be to take a log file backup.
Step 3 would be to change the recovery model to the SIMPLE mode when not much is happening so that if something does go wrong during this timeframe, you won't lose much.
Step 4 is to shrink the log file to 0 bytes. Don't shrink the database. Shrink only the log file. This will clear out all the "bad stuff" with the incorrect sized VLFs.
Step 5 is to change the recovery model back to FULL.
Step 6 is to change the initial size of the log file to the size you planned on like I recommmended earlier. Also change the growth setting to grow in MB rather than percent just in case there's acccidental growth. The number of MBs should be with what size you want the VLF's to be if growth occurs.
Step 7 would be to take either a differential backup (smaller and quicker than a full backup) or another full backup to restablish the log chain.
Step 8 would be to turn on a maintenance plan or custom code to do log file backups every x number of minutes as previously discussed.
Step 9 would be to ensure that regular tape backups are being taken of all of your SQL Server backups.
Step 10 would be to setup a plan to delete the backups from disk after they've been backed up to tape and after some period of time has elapsed. I keep 2 to 3 days of backups on disk for quick recoveries if anything goes wrong and it has saved my bacon more than once (especially when you have developers and other folks that have too many privs in prod).
To emphasize, the only time that I'd ever set a database to SIMPLE recovery on a permannent basis is when I truly didn't give a damn what was in it. Those could be staging databases, scratchpad databases (similar to Temp DB), and maybe even experimental "sandbox" databases. I would never permanently set a production database to anything other than the FULL recovery mode.
--Jeff Moden
Change is inevitable... Change for the better is not.