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, worked like a charm!!
Wow – I can’t believe you published this as a useful piece of code. This is terrible advice to advocate – and the reason that this option was finally removed in SQL Server 2008.
See my old blog post: https://www.sqlskills.com/blogs/paul/backup-log-with-no_log-use-abuse-and-undocumented-trace-flags-to-stop-it/
PS The code you posted doesn’t work if there are active long-running transactions, a database mirroring SEND queue, or the transactional replication log reader agent job hasn’t scanned the untruncated log.
Pinal,
You are great. Thanks again, this is very very helpful.
Hi Pinal,
My database (replication with one more server) has grown to the hard disk limit, I tired using the above script, it threw an error, secondly when I take the backup the .bak file size is same as data + Log file size. Please suggest.
Dear Pinal Dev,
This is the first time i’m coming to this forum. It is very useful. Imran Mohd also doing wonderful job. He is giving info in depth
hi Pinal,
i have experienced that SHRINK DB causes fragmentation… do TEMP DB grow for SHRINK DB ??
Hi,
My database (sql 2005) is only full recovery mode. The transaction log grew way too big about 34GB. So, what I did was
1. shrink file using dbcc shrink file
2. backed up the transaction log using to a file
3. Issue DBCC shrink file
Now here is my question, I know if I use the truncate only option on step2 and shrink the file, it will reduce the log file size. But If I do this I am seeing messages on event viewer saying
“BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated. The simple
recovery model should be used to automatically truncate the transaction log.”
Since, I have backed up the log file in step2 to a file, Can I safely truncate the log file? If so Will I be able to recover to a point in time?
I am using the newest Microsoft SQL Version 8. Guess what, the shrinkage methods in SQL SERVER MANAGEMENT STUDIO are not working or make hardly a dent in the Log file size. Also the log files grow much faster than the actual databases.
The method I used to shrink the log files is the one described here by Praveen Barath and which involves detaching the database in question, renaming the logfile, attaching the database again but now removing the log file and after DB data verification deleting the log file completely.
I have no idea why the shrinkage utilities within management studio are not working at all.
Thanks for the tip an keep up the good work.
I found this post very helpful but ran into an issue when using this against a Sharepoint database. Full DB Name below:
USE SharePoint_AdminContent_065b8fa2-f141-42ab-8b82-7b8bf659f7c2
The error message that I get is listed below:
Could not locate entry in sysdatabases for database ‘SharePoint_AdminContent_065b8fa2’. No entry found with that name. Make sure that the name is entered correctly.
It looks like it is Stopping after 32 characters. Is there something else I must specify to allow it to use more than 32 characters?
I misread the error message but still seek knowledge. It does not like the dashes…but I am not a DBA(this just fell into my lap and now I have to fix it) and not familiar with what my options are at this point.
@Hans
I’ve seen the issue with the shrink command not working from management studio before. In my case, the logical file names on the .mdf and .ldf didn’t match the name of the database. Fixing this seemed to allow the shrink commands to work.
I work with SQL 2000.
I use the follwoing script for reducing the log file size.
There is no need of “use (database)” command
dbcc shrinkdatabase (powerbrain)
go
backup log powerbrain with truncate_only
go
I use shrinkdatabase instead of shrinkfile where you may need to use “use database” command. I used to run this script in query analyser.
John: Try USE [SharePoint_AdminContent_065b8fa2-f141-42ab-8b82-7b8bf659f7c2]
Some database names needs to have surrounding brackets.
Thanks for the above code,
it worked.
This script worked for all my databases, but any database that starts with a number, I get error. any help appreciated. thanks
USE 8PSTDB
GO
DBCC SHRINKFILE(8PSTDB_log, 1)
BACKUP LOG 8PSTDB WITH TRUNCATE_ONLY
DBCC SHRINKFILE(8PSTDB_log, 1)
GO
————————
Error message
————————-
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘8’.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘rinks_log’.
Msg 319, Level 15, State 1, Line 2
Incorrect syntax near the keyword ‘with’. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ‘rinks_log’.
in case the above error message is misleading, I copy pasted the error message from another db which starts with 8RINKS – which also throws an error same as the 8PSTDB
Nilesh,
why don’t you try this one?
USE 8PSTDB
GO
DBCC SHRINKFILE(‘8PSTDB_log’, 1)
BACKUP LOG 8PSTDB WITH TRUNCATE_ONLY
DBCC SHRINKFILE(‘8PSTDB_log’, 1)
GO
BTW, it is not a good idea to truncate log manually rather do take backup of your database regularly and also take backup of your log files after every few hours, depends on your risk handling capacity.
Is there a way to shrink all log files for many SQL databases in one go for SQL2005 and SQL2000, as I would a like script for this, for SQL2000 and SQL2005
Thanks
@Rakesh,
Can you please post your email id here,
I have a script, with does exactly what you asked for.
please leave your email id, write it in the post, so that it is visible to me and I will have it emailed.
~ IM.
Hi Imran, can you email me the script also.
Thanks.
Tom
Hi Rakesh,
this is possible for sure but I don’t recommend to truncate transaction log manually, even if you would like to do it, please refer below given link.
h ttp://www.sqlhub.com/2009/05/shrink-log-file-for-all-database-in-sql.html