SQL SERVER – SHRINKFILE and TRUNCATE Log File in SQL Server 2008
- by pinaldave
Note: Please read the complete post before taking any actions.
This blog post would discuss SHRINKFILE and TRUNCATE Log File. The script mentioned in the email received from reader contains the following questionable code:
“Hi Pinal,
If you could remember, I and my manager met you at TechEd in Bangalore.
We just upgraded to SQL Server 2008. One of our jobs failed as it was using the following code.
The error was:
Msg 155, Level 15, State 1, Line 1
‘TRUNCATE_ONLY’ is not a recognized BACKUP option.
The code was:
DBCC SHRINKFILE(TestDBLog, 1)
BACKUP LOG TestDB WITH TRUNCATE_ONLY
DBCC SHRINKFILE(TestDBLog, 1)
GO
I have modified that code to subsequent code and it works fine. But, are there other suggestions you have at the moment?
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
Configuration of our server and system is as follows:
[Removed not relevant data]“
An email like this that suddenly pops out in early morning is alarming email. Because I am a dead, busy mind, so I had only one min to reply. I wrote down quickly the following note. (As I said, it was a single-minute email so it is not completely accurate). Here is that quick email shared with all of you.
“Hi Mr. DBA [removed the name]
Thanks for your email. I suggest you stop this practice. There are many issues included here, but I would list two major issues:
1) From the setting database to simple recovery, shrinking the file and once again setting in full recovery, you are in fact losing your valuable log data and will be not able to restore point in time. Not only that, you will also not able to use subsequent log files.
2) Shrinking file or database adds fragmentation.
There are a lot of things you can do. First, start taking proper log backup using following command instead of truncating them and losing them frequently.
BACKUP LOG [TestDb] TO DISK = N'C:\Backup\TestDb.bak'
GO
Remove the code of SHRINKING the file. If you are taking proper log backups, your log file usually (again usually, special cases are excluded) do not grow very big.
There are so many things to add here, but you can call me on my [phone number]. Before you call me, I suggest for accuracy you read Paul Randel‘s two posts here and here and Brent Ozar‘s Post here.
Kind Regards,
Pinal Dave”
I guess this post is very much clear to you. Please leave your comments here. As mentioned, this is a very huge subject; I have just touched a tip of the ice-berg and have tried to point to authentic knowledge.
Reference: Pinal Dave (http://blog.SQLAuthority.com)
Filed under: Pinal Dave, SQL, SQL Authority, SQL Backup and Restore, SQL Data Storage, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology
