UPDATE: Please follow link for SQL SERVER – SHRINKFILE and TRUNCATE Log File in SQL Server 2008.
Sometime, it looks impossible to shrink the Truncated Log file. Following code always shrinks the Truncated Log File to minimum size possible.
USE DatabaseName
GO
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)
GO
[Update: Please note, there are much more to this subject, read my more recent blogs. This breaks the chain of the logs and in future you will not be able to restore point in time. If you have followed this advise, you are recommended to take full back up right after above query.]
UPDATE: Please follow link for SQL SERVER – SHRINKFILE and TRUNCATE Log File in SQL Server 2008.
Reference : Pinal Dave (http://www.SQLAuthority.com), BOL
314 Comments. Leave new
Hi Pinal,
I keep on follow your blogs – really very good expla.
1. can you tell me what measures I need to take on server level to perform my application will run fast – .NET 2.0 web application?
2. I want to write a script to create a database – parameterized script.
Regards
Seema Reddy Akkala
Hi Pinal,
Pls – notice we are using sql server 2005 – .NET 2.0 web application
I am setting up a maintenance plan to provide daily backups for a set of new databases in SQL Server 2005. I obviously want to keep the log files from groing. Will “Full” backups of each database accomplish this? Do I need to set up a Full Backup and a Transaction Log backup?
Thanks,
Frank
Most of these scripts have a serious (but easy to fix) flaw – they set the size of the log file to 1 megabyte!
Emeka (#43 above) has already pointed out the DANGER of using the TRUNCATE_ONLY option – do not use that option if you require full-time availability and no data loss. It can be made to work – Kimberly Tripp recommends taking a full backup, truncating the log, and then taking another full backup. But, you have to be able to afford the downtime.
If you are going to shrink your log, NEVER shrink it to 1 megabyte! You want to shrink it to where it should be. Otherwise, you get a very inefficiant log file with lots of internal fragmentation.
And where should it be? In a system with proper backups of the log file running successfully and with no long running transactions, the log file IS ALREADY THE SIZE IT SHOULD BE.
As was stated above by Emeka (#46 above), if a log file keeps growing continuously, you have another problem which needs to be fixed, before you shrink the log file.
When you do shrink a log file that got out of control, don’t try to set it to 1 single megabyte!!! Set it to 2 or 3 times the size of the database, if you have no other clues as to the proper size.
David
Hi Pinal…
We have an option with one of our client like disk space alert, when the usage os disk reaches 90-95% it wil create an alert and send an email to all DBA.
All the time Critical suituation is due to growth of LOG files only, rarely on MDF files. Is there any query to find which file MDF/LDF is growing or which is adapting more space.
Is there any way to find the file in short time period because we have to resolve it as quick as posible.
Regards,
Senthil kumar Rajendran.
Hi,
I have a question.
I am going to delete a huge chunk of data for which my log files will be increasing a lot.
I want to know is there is any kind of dml statements which will not log any data into the log files.
Or is there is any way that the to truncate the log file before i start deleting.
I will be deleting large chunk of data and then i will have to shrink the database as shrink file did not work that efficiently.
Also is there is any way while i delete the tables can i disable all the constraints of that table. My deletion is done dynamically or using the ad-hoc query. So for that reason my contraints gives problem.
I want something like this..
disable table name constaraints.
delete table
enable constarain of that table.
Thanks in advance.
Palin, thanks for the info to shrink SS2000 log files. Backups failed for a while and the log files grew. Now the backups are working again but I needed to reset the log file and your instructions were very helpful!
Pinal says:
>>If log are left to un-restricted growth they will grow bigger
>>than hard disk space available it will crash SQL Server >>completely.
Is this really true even with recent versions of SQL such as 2008?
It’s hard to believe that SQL treats running out of disk space so ungracefully.
regards,
Lee
Thank you VERY much. I have been all over looking for a simple solution, and have run a number of scripts and wizards to no avail. I appreciate your easy and EFFECTIVE script!
This commands help me to shrink log file quickly, thanks
No one has put the light on the script which can run on all databases on the server.
EX: If I have 100 Databases and want to run the command DBCC Shrinkfile on all the DBs at one go…Any suggestions/solution appreciated.
Thanks in Advance
Pinal,
Excellent tip thanks a lot!
I had a log that was 17GB in size now its ok
Best regards and keep up the good work!
Hi all;
Been reading all the comments and would like to “shrink” my log file down to a respectable size and get rid of all the “white” space.
Can I run these commands below if there has not been a backup on either the db’s or logs in quite a while?
Also what implications are there using the NO_LOG option?
Thanks in advance, Steve
USE DatabaseName
GO
DBCC SHRINKFILE(, 1)
BACKUP LOG WITH TRUNCATE_ONLY
DBCC SHRINKFILE(, 1)
Hi,
The above query to truncate transaction log is most useful fpr me. Thank you very much
Hi Pinal,
I am new to this forum. We have a DB crash down yesterday due to increasing log file ran over the disk space. We have restored the DB from the backup taken on mirrored server.
We want to reduce the size of log file. Should we use shrink file comand? It would be helpful if you reply with the commands to be run over the SQL server 2000
Thanks in Advance
All the info sounds exactly like what I need. I am not SQL conversant.
Where do I start with the truncate action? Where do I need to be? Command prompt?
Server Management Studio? Server Management Configuration?
Then what steps (right down to the key strokes) do I need to follow?
Server has stopped responding. I have stopped the Integration Services, Fulltextsearch, reporting services, etc. My email box for requests is growing faster than I can respond.
Please advise.
Hi All, How to shrink data file (*.mdf) ???
You are the man! Worked like a charm when all else failed.
Go dude
Hi,
I am unable to use the above command succesfully…
when am trying to execute the below command:
USE Retail_HL_Analytical
GO
DBCC SHRINKFILE(‘Retail_HL_Analytical_PROD_20090120_log’,1)
BACKUP LOG Retail_HL_Analytical WITH TRUNCATE_ONLY
DBCC SHRINKFILE(‘Retail_HL_Analytical_PROD_20090120_log’,1)
GO
the following error msg is getting displayed:
Msg 8985, Level 16, State 1, Line 1
Could not locate file ‘Retail_HL_Analytical_PROD_20090120_log’ for database ‘Retail_HL_Analytical’.
Msg 8985, Level 16, State 1, Line 3
Could not locate file ‘Retail_HL_Analytical_PROD_20090120_log’ for database ‘Retail_HL_Analytical’.
plz let me know where i went wrong in using the command…
Thanks
Dude, your blog has been SUPER helpful.