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 database 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.
Update: Small typo correction and small detail corrected based on feedback.
Reference: Pinal Dave (https://blog.sqlauthority.com)
79 Comments. Leave new
dbcc shrinkfile(x_log,1)
backup log x to disk = ‘nul:’ –with truncate_onlyto disk = ‘nul:’
dbcc shrinkfile(x_log,1)
due to low disk space , i didn’t configure log backup. But Database is in full recovery model. For this reason log file size goes huge every week. Shall I shrink log file weekly once.. OR What i Want to do before shrnk the log file.
Hi Pinal,
due to low disk space , i didn’t configure log backup. But Database is in full recovery model. For this reason log file size goes huge every week. Shall I shrink log file weekly once.. OR What i Want to do before shrnk the log file. If i shrink log file weekly once will it make physical fragmentation?
Hi Pinal,
Can you please help me. I want to use check point with my job, actually when my job run CPU takes 90-96 % usages which is not good, So please suggest what is the best solution to resolve this issue, so that the job not take a huge CPU usages.
Regards,
Sunil Kumar Kaushal
Thank you. This helped me a lot.
i have SQL Server 2008 DB. usually was not performing log backup. now log size is grow to 60 GB and eating drive space. in order to gain space need to shrink log file.
My database is in FULL recovery and can not change it to simple because i m using sharepoint 2010 database and simple mode is not support in production.
for that i took log full back.
truncate_only is not supported at SQL Server 2008. so i shrink file using management studio interface when i select log file and click to shrink screen close quickly without giving any error or message. and files does not shrink and its size is still same. please tell how to shrink file and get drive space back. thx.
I’ve noticed the same “feature” in SQL 2008. Is this a bug? States 95% of log file empty, yet fails to shrink it at all.
Pinal,
I see the below mentioned code on the MS site,and according to the below mentioned code we can toggle the recovery model, so what is the best method to shrink the log file in 2008?
Shrinking a log file to a specified target size
The following example shrinks the log file in the AdventureWorks2008R2 database to 1 MB. To allow the DBCC SHRINKFILE command to shrink the file, the file is first truncated by setting the database recovery model to SIMPLE.
Transact-SQLCopy
USE AdventureWorks2008R2;
GO
— Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks2008R2
SET RECOVERY SIMPLE;
GO
— Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks2008R2_Log, 1);
GO
— Reset the database recovery model.
ALTER DATABASE AdventureWorks2008R2
SET RECOVERY FULL;
GO
We have a database of 3 Terabytes. its set to Simple mode. Since we were running out of space therefore one of our junior DBA truncated few tables which were no more required. These were 10 tables which were being used for archive purpose.
the data files were placed on different drives which were running out of space. after executing truncate table commands. The database is showing lot of space that can be freed (off coarse). But we are unable to Shrink database. It either takes too long or gives timeout.
What is the quickest way ?
Hi All,
Unable to increase the log file size and not apossbile to set to unrestricted for the same.
Thanks,
Jagan
Hi Pinal,
Sir, I have application on sql server 2008 r2, on a daily basis as a process of End of day, we move data from couple table to archive them and delete the data from the database.
Question:
How do truncate the log ( as we take full back every day during Night)
How to Free the unused space in the data file.
Please advice, Thanks in advance.
I have implemented code, virtually identical to what you mentioned in the first section where I am altering the recovery model to simple, shrinking the log file, the setting it back to full. In our case, this is only on our production (live) servers. This works fine for us when we need to shrink down excessively large log files to recover hard drive space. But everything I am reading is telling me this is bad & causes fragmentation. Okay. Makes sense.
We make nightly backups of our database and log files (in our production/live environment. In development, we only do database backups because in that environment our recovery model is set to SIMPLE.)
So I went to your next suggestion of “BACKUP LOG [TestDb] TO DISK = N’C:\Backup\TestDb.bak'” and tried that with our scenario. Unfortunately, the log file backup that gets created is ~27k-40k in size, this coming from a 97gig log file. That cannot be right. And the 97 gig log file does not shrink down at all.
Am I missing something?
I read Brent Ozar’, and Paul Randel’ sections as you suggested. In my opinion, the weren’t as cut & dry as yours was, although I’m a big fan of Brent Ozar & have watched many of his videos.
Not sure what to at this point. My backups, despite being done daily, do not seem to be shrinking log files down to a manageable size & the SHRINKFILE comes into play with me more often then I am comfortable with.
Thank you for your time.
dear pinal,
One of our sql server 2008 server, database log file cosume total dsk space around 150gb space,i am trying to backup log with truncate_only ,show error its not a prefered option.as said aboue if we take log backup to specific loction(BACKUP LOG [TestDb] TO DISK = N’C:\Backup\TestDb.bak’)
how much space we need for 150 gb log space.In our server only 17gb space available in C drive remaining drives are full.Recovery model is full if i change it simple there might be a data loss is there any other options to get back server space.
Please give me better suggesion .thanks in advance (shirnking log file also not happening, log_reused_space in sys.database is LOG-BACKUP)
please help me.
Below commands works well
ALTER DATABASE ExampleDB SET RECOVERY SIMPLE
DBCC SHRINKFILE(‘ExampleDB_log’, 0, TRUNCATEONLY)
ALTER DATABASE ExampleDB SET RECOVERY FULL
Hi there, been reading your site for a while now, great work & easy to follow! :-)
I had a bit of an issue with a new SQL 2012 setup, it somehow managed to get the Log files set to minimum size of 12GB… not sure how, I used the below to shrink it down (After backing up the Database of course)
USE
GO
DBCC SHRINKFILE(_log, 1)
BACKUP LOG to disk =’nul’
DBCC SHRINKFILE(_log, 1)
GO
So now the log files are initial size of 1mb again, my question is… do you have any ideas on how it might of got the initial size to be so large? My understanding of this value is it should remain at what you set it to, & when you backup the log files, shrink back down to this size.
For reference this is a 5GB Database with only minimal changes per day.
I also faceed the problem of
Msg 155, Level 15, State 1, Line 1
‘TRUNCATE_ONLY’ is not a recognized BACKUP option.
Then I used the second query. It works fine for me.
Thanks a lot
Hi pinal, i am more happy by seeing this blog having good stuff..my problem is
my data base mdf size is around 75 gb and available size is around 50 gb . when i am trying to shrink it is getting the error 1222. can i check with smaller size or is there is any solution to shrink the db. please help me to resolve in best method.
This works for me perfectly. I have used Pinal’s query, but it does not works for me. Recently from msdn I got this and it works upto my satisfaction level. My DB file now reduced to 1mb. Here is the query-
–This is for Back-up
— SELECT PROPER DATE BEFORE TAKE THE BACK-UP
BACKUP LOG [GBKSMART] TO DISK = N’C:\Backup\DBName_log.bak’
–**** Shrinking Database
–USE [DBName];
GO
— Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE DBName
SET RECOVERY SIMPLE;
GO
— Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (DBName_Log, 1);
GO
— Reset the database recovery model.
ALTER DATABASE DBName
SET RECOVERY FULL;
GO
Thank You.
If you keep doing this, dont forget to make a new full backup of your database. When you change your database to simple, you just truncate your TLog every transaction commited. When you change back to full, you must make a full backup of the database that will keep the transactions in the TLog since your full backup.
Regards
Hi Pinal, i am facing space issue in SQL Data and in sql logs for microsoft sql server 2008. Due to this so often i have to shrink the databases but inspite of shrinking for 2-3 hours i am still able to free only 5-6 gb of space. In sql data i have files like .ndf, .mdf,.ldf . so could you suggest some better way to shrink this files .
Thanks
I have a database task setup to take log backups every hour during the day and a task to do a full backup nightly (SQL Server 2012). My log ldf keeps growing, should i change how i am taking backups or shrink the log files?