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 (http://blog.SQLAuthority.com)
i want to know how to restore log record if i delete row from my database and restore it again
1. There are (still) log reader tools out there that might allow you to check the transaction log for commands and undo some of them, DELETE being such a case.
2. Usually, a backup is restored under a different name and you will use a SELECT INTO command, to copy the record from the restored database to the old one. This technique assumes that you have a backup management policy in the first place.
3. Please try to be more specific next time, because there are a plethora of solutions for a plethora of scenarios :-)
best regards,
calin
Hi Pinal,
I would like to tell you that shrinking the database log file doesn’t cause database fragmentation.
Hi Prabhankaran,
Yeah. Shrinking the database file or database creates the fragmentation.
In case of log, if proper backups are taken they will not grow this big.
Kind Regards,
Pinal
Hello sir,I am facing a problem in a database sql server 2008 which is very slow to load the application in windows forms.I manually increased the size of mdf and ldf file but I dont know the proper way too improve the performence in database tuning I also did the tuning advisor no use.I working.as a windows developer and I am struggling every day with this issue.
please help me
Hi,
If I have a DW Database that generate a 20GB Log file some times.
In SQL Server 2005, I use “backup log with truncate only” and after “shrinkfile”. In SQL Server 2008 I will need realize backup to disk and after delete the file?
Thanks
No, you should change your recovery model to Simple and avoid generating the logs to begin with.
http://msdn.microsoft.com/en-us/library/ms189275.aspx
Thanks Bryan,
In SQL Server 2005, A database with simple recovery model generate a Log with more than 20 GB with a great insert. This isn’t minimaly logged.
The table already have data and index.
You should Simply Checkpoint after the insert then.
And then SHRINK ONLY THE LOG FILE. But if its know that the application will need that logs space, then its recommended that the space is reserved for the log file. However in a BI case, I would manually grow the log before the ETL process, and shrink ONLY THE LOGFILE after with SHRINK FILE with the name of the logical log file.
if autogrow option is enable means is it effect the performance?
WHat is best method to follow when mdf and ldf files are growing?
Hi,
What about changing the database model from Full to Bulk-Loged then do some bulk insert operations then switch it back to Full, does it case the 2 points mentioned at the end of this post?
Thanks
[...] SQL SERVER – SHRINKFILE and TRUNCATE Log File in SQL Server 2008 SQL SERVER – Simple Example of Snapshot Isolation – Reduce the Blocking Transactions [...]
Dear pinal,
As i am going through the space issue in my server and my database has a grown bigger so i had deleted some tables from the database which were not necessary .now i have 6 gb unallocated space availlable in my db i shrink the db and my db recovered around 4 gb spaces as after doing R&D in my db i came to know that my 2 gb spaces is been occupied by the log file .
as my platform is sql server 2008 i can’nt truncate the logfile but as i am taking regular log_backup every 1 min.i want to make space free of log file.kindly suggest how to recover the 2 gb space or what to do.
@Fabricio: I highly recommend either do the insert in batches, or leave the log as is, after it has grown. SQL Knows how to handle internally your transaction log and what to clear in case of the SIMPLE recovery model. If you constantly shrink a log file that is doomed to grow again, you might end up having physical fragmentation (check your transaction log file with a defragging tool).
@kishore: you want to check a feature called instant file initialization (google is your friend) for the auto-grow of the data file (and only the data file!!). As for the log file, simply define your database to have a large enough transaction log to play with. Unfortunately this also relates to your recovery model (namely transaction log management), so I have no idea what to say about what size is the correct size for the transaction log. No, 25% of the data file, as Microsoft used to recommend is not quite the best starting point.
@Smith: the only major issue is for switching from either FULL of BULK-LOGGED to SIMPLE, or vice-versa, from SIMPLE to either FULL or BULK-LOGGED. Switching from FULL to BULK-LOGGED has no effect on the backup chain whatsoever (the dreadful log sequence number, aka LSN chain). But please keep in mind that BULK-LOGGED minimally logs some operations, and that’s all. Because there are some special pages that are affected by bulk operations, the next transaction log backup has to reflect those changes, so expect it to be bigger than you would normally think.
@Pinal: apologies if I replied to some of the questions, I took the liberty of doing this because I’m sure you don’t have the time to do it right now.
best regards,
calin
Thanks Calin,
I appreciate your help.
Kind Regards,
Pinal
sir, i am working in Oracle 10g i want record every transaction (INSERT,ALTER,DELETE,…) in log file it is possible throw oracle. if possible please explain with examples.
Yours
SIVANANDA REDDY
[...] talks about the advantage and disadvantage of Shrinking and why one should not be Shrinking a file SQL SERVER – SHRINKFILE and TRUNCATE Log File in SQL Server 2008. On this subject, SQL Server Expert Imran Mohammed left an excellent comment. I just feel that his [...]
Is there any way or system tbl to find the database name which is scheduled for backup operstion ?
Hi,
I have a scenario where I have 2 SQL servers and a db mirrored between them. I need to backup and truncate the log file, preferably with sql job instead of doing it manually. I see the backup with truncate option has been removed from SQl 2008 unless you do a manual tranlog backup then the option still exists.
Is there some code I can run to emulate transaction log truncation with a backup?
I can’t change the recovery model to simple because I will loose the mirror connection if I had to do this.
Thanks, Shawn.
Hi Pinal,
I am permanent reader of your blog, I am fresh as a DBA.We migrated our DB from SQL server 2005 to 2008. At SQL Server 2005 size of log file was 27GB but at 2008 it increases up to 300GB and DB is pretty Slow now.Please tell me the ideal size of log File and how to reduce the log file size.We are using Full back backup option.
I need help in this case.
Thanks.
Azhar Iqbal
DBA Lahore, Pakistan.
Thanks. It worked. But remember one thing that do not forget to take full backup of your database before truncating log file
I have to write code to get backup in log file
The code is
—————
Backup LOG profusion_dev
To Disk = ‘E:\SQL_Backup_LOB\back_up_files\prof_dev_log.bak’
I got an error this
———————–
Msg 4208, Level 16, State 1, Line 1
The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
Plz any body correct this coading
Hi Dinesh.,
Check the recovery model of the database ‘profusion_dev’.
If the database is in ‘Simple’ recovery model, it will not allow ‘Log Backup’.
Because, the log file is consequently truncated, and there is nothing to backup.
Regards,
Riyaz
hi,
How to Optimize Database Size?
How to Optimize tempdb Database Size?
It works like magic but u need to change the ‘master’ to the ‘DBName’ it reduced my 207MB to 1024kb
Hi guys,
I have followed the recommendations and started backing up my database hosted in MSSQL 2008R2 once a day in with a maintenance plan that does this:
1. full backup of the DB
2. shrink DB
3. History cleanup
Comment: in the Studio Manager 2008 R2 you have the possibility to check the T-SQL statement in each maintenance plan step you plan to implement.
On the step 2 (shrink DB) I see the statement is:
USE [myDBname]
Go
DBCC SHRINKFILE (N’myDBname’, 10, TRUNCATEONLY)
… hm so this means that the maintenance plan is actually using TRUNCATEONLY even though Microsoft suggest stopping using this practice. I’ve stopped the scheduled DB maintenance now, because after 2 runs my LDF file is already 7GB bigger than before, so bad news for me.
So my final question to you guys is: how do I “properly” backup the transaction log? Maybe I mustn’t use a full DB backup and shrink, but other option. I hope you can shine some light. Thank you in advance.
Lucian
I’m in the same boat here. 2008 SQL. I use Symantec BE2010 and also backup using a Maint plan to external HD. My ldf files are growing without bounds. None of the dbcc shrinkfile commands work. Symantec tells me to switch to full from simple recovery. I have tried both no luck. What is the OFFICIAL way to full backup and truncate the logfiles every night so they do not grow and remain small. Please don’t leave any syntax out if you have a script. I have tried many and none work with 2008 SQL. Either bad syntax or just don’t work saying no longer supported.
MUY BUENO MUCHAS GRACIAS
is there any way to know shrink history? i.e, when was last shrink on mdf or ldf performed..?
[...] SHRINKFILE and TRUNCATE Log File in SQL Server 2008 [...]
Dear Pinal,
I have some question.
1. We have SQL 2008 R2 installed. Our system use DPM to backup SQL database periodicaly and other important data. Currently we use full recovery mode to all database and it made log file grow rapidly and create insufficient disk space error. Could we change the recovery mode to simple?
2. What is the difference between DPM and SQL backup?
thank you.
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 ?
[...] SHRINKFILE and TRUNCATE Log File in SQL Server 2008 [...]
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.
[...] In year 2006 I started to blog and honestly I had no idea what is the blogging? It was just a collection of the bookmarks and I had a great time writing them up. I always thought I will read it when I need them. Today when I often read my old blog post – I feel nostalgic and also realize that I have improved a lot technically as well professionally. One of the blog posts which I wrote regarding how to truncate log file got quite popular with DBA and Developers who got issues with growing log files. My solution was not perfect as it was breaking the chain of log, leading to create issues with point in time restore. In SQL Server 2008 the method I demonstrate to truncate the log was replaced. I wrote following blog post regarding how to truncate the log file in SQL Server 2008 and later version. [...]
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
[...] SHRINKFILE and TRUNCATE Log File in SQL Server 2008 There are few absolute No-No’s in SQL Server environment. There should be no need to shrink and truncate log files in daily routine. Though this blog post was written keeping SQL Server 2008 in mind, this is also valid in SQL Server 2008 R2 and SQL Server 2012. If you are going to shrink your database, I strongly suggest that you read this blog post before it. [...]