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
Thanks Buddy’s it’s really help me truncate log file for Sql
The following three commands worked for me and it shrunk the log file to 1mb
USE DatabaseName
DBCC SHRINKFILE(, 1)
BACKUP LOG WITH TRUNCATE_ONLY
DBCC SHRINKFILE(, 1)
Thank you so much
Mathi
It really helped. Thanks.
Shiv
Hi pinal
I have database with size of 32Gb and log file size is 17GB. I want to reduce the log file size.How can i do this.
Note :- Its a live server.Please suggest Me.
Hi Ashu
Use the below scripts..
DBCC SHRINKFILE (‘LOGICALFILENAME’,NOTRUNCATE)
GO
DBCC SHRINKFILE (‘LOGICALFILENAME’,TRUNCATEONLY)
-Raushan
When i am use this query its working and the log file size is 1MB.
Thanks Raushan
Hey Guys,
I have SQL2000 and even i face the same issue, log file increases and i have to shrink it in every 2-3 days… can you tell me if i can schedule the same through maintenance plan is there any way i can do auto shrink the log size..
Please let me know..
Thanks.
Suresh Nakka.
It helped me alot, I was trying to backup the file and it was taking forever
Thanks.
Hi Pinal,
I will be regularly reading all your blogs and have got solutions for many of my Database problems and learned many new concepts. Thanks a lot.
For the first time I’m posting my problem here, please help me on this.
I have a batabase of size 3GB (Log file 500MB) and has been configured Every Saturday Full and Every 8 hours Transactional Log Backup. Problem is Log backup will be happenning very frequently every hour (even then its configured for every 8 hours). Due to this, its taking too much of my drive space, also provided drive space for backup is less only, I cant expect more drive space also. When ever Log file size becomes abnormal will shrink the Log file alone. Also transactions on this particular database is average only, not too much.
Can you please help me out for fixing this problem. I tried searching reason for this, but dint succeed.
Thanks… Helped me too
I prefer this method , easy to script as well.
C:Program FilesMicrosoft SQL Server100ToolsBinnosql.exe -E -S Servername -d “DatabaseName”
ALTER DATABASE DATABASENAME SET RECOVERY SIMPLE;
go
DBCC SHRINKFILE (‘DATABASENAME_log’, 200);
go
ALTER DATABASE DATABASENAME SET RECOVERY FULL;
go
Hello, How can I do this on SQL SERVER 2008? If try to run this query the SQL show me:
Msg 155, Level 15, State 1, Line 1
‘TRUNCATE_ONLY’ is not a recognized BACKUP option.
Dave already made an update on this page
https://blog.sqlauthority.com/2010/05/03/sql-server-shrinkfile-and-truncate-log-file-in-sql-server-2008/
Hi Sir, I have Used a wrong update command in sql server i want come back on same database value what can i do Sir?
Thank u sir
very helpful!!!
Thanks Pinal, you saved me a day!
Hi Pinal. I was following your posts since time ago, and I take a brief time to thank you. I’m grateful for all of your articles due I found them very useful for my daily activity. I’m a software developer and use to deal with databases but I’m not a db administrator and time to time I have to become a little bit into it due the maintenance problems of a database. Time to time I stop to think myself what is your motivation to write and share your experiences with all of us. Far to need an answer, I can feel your inspiring sense of generosity. Again I thank you for everything here.
In our case the log backup was running for 10 hours then failing, and the transaction log was 100x the size of the data file. The generic error reported in the log was not helpful but further investigation led to an error related to the log containing transactions pending replication.
This was strange because the db was not published for replication.
Anyway, what worked for ua was to run sp_repldone command first,…
msdn.microsoft.com/en-us/library/ms173775.aspx
…and then follow the instructions in this article.
Hope this helps anyone with the same problem
nice post, related to Backup to file.
thanks, i did it in QA envi as we are already running out of disk space.
i have one concern why we use twice DBCC SHRINKFILE() command ?
I tried but it’s not worked.I know better option as changingn the recovery model and then shrink but ican’t do this because of Disater recovery plan.So,anyone have other option.