Our database has gone over a 1 terabyte. We have backed up and now it is time to truncate the log file. Paul S. Randal has mentioned that truncating a database should be fine. However, shrinking a file should be avoided.
Can you share your thoughts on this and your approach? What you would recommend?
Thank You
I'm confused. How large is the log file vs the data file(s)? Shrinking a data file should only be done rarely, but is often needed after a large amount of data is completely removed.
Why do you think you need to truncate the log file? If it was setup appropriately in the first place - then you won't have a large number of VLF's and each VLF is an appropriate size - therefore the size is how large it needs to be...
Scott, our transaction log file has increased to 2 terabytes. A friend of mine gave me a script that auto shrink the log files per database, using DBCC SHRINKFILE.
Jeff, with that being said, can you help me understand how to setup appropriately in the first place? What would be the appropriate shrink file size?
That's what I was trying to find out. You can safely shrink a log file(s) (but normally you'd have only one log file) at any time when it gets too large. If the db is not in simple recovery, put your db in simple mode, shrink the log, then put it back to full/bulk-logged and do a full db backup.
The is NO appropriate shrink file size - that is, you should not automate this process. The only time you should shrink a transaction log is when something unusual/out of the ordinary has occurred that caused the file to grow.
Here are a couple of articles you should read:
https://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/
https://www.sqlskills.com/blogs/kimberly/8-steps-to-better-transaction-log-throughput/
You do not need to switch recovery models to shrink the log file - this will just break the log chain and prevent restoring across that break point if needed.
To shrink - backup the transaction log (if in full recovery) - shrink the file - and repeat as needed until the file is down to the appropriate size. If the database is in simple recovery mode, then perform a checkpoint after each shrinkfile operation.
The only time you should change recovery models is if the database is in full recovery - and you don't have transaction log backups set up (or running). If that is the case, determine if you really need full recovery model - if not, switch to simple and shrink the file - only switch back to full recovery model once you have scheduled transaction log backups.
SQL doesn't require you to switch recovery models to shrink a 1TB+ log file, but common sense does:
Who wants to wait while a 1TB+ log file backs up? No, it would just take far too long.
And would you ever want to try to re-apply a 1TB+ log file? No, it would just take far too long.
So, put the db in simple mode, shrink the log file as fully as you can, do a full backup to start a new recovery base, then re-grow the log in relatively large chunks to whatever total size you need.
This assumes that all VLFs are active - which, if transaction log backups are scheduled and being performed wouldn't be the case. If no transaction log backups are being performed - then of course, switch recovery model to simple but only switch back if you actually need full recovery.
The size of the file isn't a problem - if that is what is required to support the system. I had a 10TB+ database that had a transaction log that was 1.5TB - and that was normal and required to complete index rebuild operations on several tables.
Now - if the transaction log is that size because it grew out in chunks of 10% and the VLF files are extremely large - that is a whole different discussion.
Hi, I appreciate both of your gracious and valuable inputs and experiences.
I have read this over several times.
I would like to provide more information. We will backup our database. After that, when we truncate and shrink, most likely, we will not restore the transaction log. If anything does happen, most likely we will do a restore with the backup.
Thank you again, I am still digesting both advices.
If you don't need point in time recovery - and only need to be able to restore to the latest backup - then you don't need the database set to full recovery. It can be set to simple recovery which does not allow transaction log backups.
If you need to be able to restore to a point in time - then you need full recovery and frequent transaction log backups.
Shrinking is bad! Here are some best practice you can do to avoid Shrinking process:
https://littlekendra.com/2016/11/08/shrinking-sql-server-data-files-best-practices-and-why-it-sucks/