As a SQL Server user, you may come across situations where you need to manage your log files effectively. In this blog post, we will discuss the concepts of SHRINKFILE and TRUNCATE in SQL Server and understand the implications of using them.
Recently, I received an email from a reader who was facing issues with a job that failed due to the usage of the code snippet below:
DBCC SHRINKFILE (TestDBLog, 1)
BACKUP LOG TestDB WITH TRUNCATE_ONLY
DBCC SHRINKFILE (TestDBLog, 1)
The reader had upgraded to SQL Server 2008 and encountered the error message “Msg 155, Level 15, State 1, Line 1 ‘TRUNCATE_ONLY’ is not a recognized BACKUP option.”
After analyzing the code, I suggested the following modifications:
USE [master]
GO
ALTER DATABASE [TestDb] SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE (TestDbLog, 1)
ALTER DATABASE [TestDb] SET RECOVERY FULL WITH NO_WAIT
GO
However, I also highlighted two major issues with the original code:
- By changing the database recovery model to simple, shrinking the file, and then setting it back to full recovery, valuable log data is lost and point-in-time restoration becomes impossible. Additionally, subsequent log files cannot be used.
- Shrinking a database file or database introduces fragmentation.
To address these issues, I recommended taking proper log backups using the following command:
BACKUP LOG [TestDb] TO DISK = N'C:\Backup\TestDb.bak'
GO
By taking regular log backups, the log file size is usually well-managed and does not grow excessively.
It’s important to note that this blog post only scratches the surface of this topic. For more in-depth knowledge, I suggest reading Paul Randal’s posts here and here, as well as Brent Ozar’s post here.
If you have any further questions or need assistance, feel free to reach out to me at [phone number].
Thank you for reading and I look forward to your comments and feedback.
Kind Regards,
Pinal Dave