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
Thank you Pinal,
I just used this script. Working fine
my database size is 12 gb but log file 36 gb. Recovery model is set to full.
i want to shrink my log file to 10mb. plz tell me how it is possible without lossing any data. can i apply dbcc shrinfile command on active database?
This might not be the best approach but this was how I reduce my log file.
— First, use the below code to get the file associated with the database in question.
USE [database_name]
GO
SELECT FILE_ID, name, size from sys.database_files;
GO
— Second, use the code below to truncate the log file
dbcc shrinkfile(3,truncateonly);
GO
NOTE :
Only perform this step if and only if you don’t need to restore from the log file you need to truncate.
Make sure you perform full Backup immediately after truncation is perform JUST IN CASE!
The ‘3’ in DBCC SHRINKFILE (3, TRUNCATEONLY) refers to the FILE_ID
‘TRUNCATE_ONLY’ is not supported in SQL Server 2008 onward. Instead use ‘TRUNCATONLY’ one word
Here’s what works for SQL 2008.
ALTER database eConfigDev SET RECOVERY SIMPLE
GO
Then confirm that it’s set to Simple (no logs used for recovery)
Select name, recovery_model_desc from sys.databases
GO
THEN, right-click on database, Tasks, SHRINK, Files, select log file, set it to maybe 1MB or even 0mb
Here’s the article that worked (took me quite a few before I found one that actually told me how to do it)
Hello
in sql2008r2 “WITH TRUNCATE_ONLY”
BACKUP LOG dbLog WITH TRUNCATE_ONLY
is deprcated command
how can shirnk the log of db after backup
best regards
—
GO
Use dbLog
if exists
(select name from master.sys.databases where recovery_model = 1 and name = ‘dbLog’)
begin
if exists
(Select * from
(
select dateadd(n , 90, max(backup_finish_date)) as maxLogdate
from msdb..backupset bckset
where database_name = ‘dbLog’ and type = ‘L’) mm
where maxLogDate > getdate())
begin
BACKUP LOG dbLog WITH TRUNCATE_ONLY
dbcc Shrinkfile (‘dbLog_Log’, 50, truncateonly)
end
end
else
begin
BACKUP LOG dbLog WITH TRUNCATE_ONLY
dbcc Shrinkfile (‘dbLog_Log’, 50, truncateonly)
end
—
Hi Praveen,
Your suggestion was years ago but it worked. Thanks. :)
I had this problem with SQL 2005 and worked like a charm!!
Naresh –
Make sure you take the FULL backup of the database after doing such. This method break the LSN chain for your transaction logs and if you forgot to take the FULL backup – all the t-Log backup after this operation will be insignificant. Means you lost the point in time recovery of the database.
Thanks,
Arshpreet
HI Pinal ,
Shall i delete ERRORLOG.. its growing very big..
its about 100 GB
Does Transaction Log file decrease the performance of query execution if it become full?
Ratan Sharma
India
Yes. The query will run slow and sometimes you may get error
Thank you for reply.
my log file became 40Gb i want to shrink it to 5 Gb. I don’t want to backup of log file.
Can i execute follwing query:-
DBCC SHRINKFILE(log_name, 5000)
plz help me
Hi Ratan,
After reading many topics from Pinal I have given below the suggestions:
1. Take the log backup first (whether you want or don’t want). Then only SQL Server engine knows it and issues checkpoints to close the already opened transactions. (You may need to take a full backup first if your DB is recently restored and not taken the FULL backup till now)
2. USE ;
GO
— Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE
SET RECOVERY SIMPLE;
GO
— Shrink the truncated log file to 5120 MB.
DBCC SHRINKFILE (Log_name, 5120);
–You cannot expect the same size it may be varying depending upon the nature of the logs stored.
GO
— Reset the database recovery model.
ALTER DATABASE
SET RECOVERY FULL;
GO
3. Take a FULL backup
4. Take transaction log backups in a regular interval to avoid the unconditional growth (This is very best way of practicing to reduce the log growth).
Thanks & regards,
Vijay
Hi Pinal,
I’ve SQL Express 2008 database. I have limited hard disk space on machine. I don’t care if there is log file or not. I don’t need to recover from the log file also. So I don’t want the the log file to grow bigger. Is it the best solution to execute just one command “DBCC SHRINKFILE(, 10)” periodically? or do I still need to use below three steps that you suggested originally
USE DatabaseName
GO
DBCC SHRINKFILE(, 1)
BACKUP LOG WITH TRUNCATE_ONLY
DBCC SHRINKFILE(, 1)
GO
Hi Jignesh,
If you dont care if there is log file or not then please change your Recovery model from FULL to SIMPLE.
If your DB’s Recovery model is SIMPLE you don’t need to use the code mentioned by PINAL.
Thanks & regards,
Vijay Anand Madhuranayagam
maybe this is a dummy question….
but is possible to add this code is the SSIS mantaince tool? which toolbox is the correct one
–Shrink the truncated log file to 1 MB.
–DBCC SHRINKFILE (, )
–DBCC SHRINKFILE (‘DATABASE_log’,1)
thanks in advance for your answer,
Regars, Cris
Thanks Buddy…………
Hi Pinal
I am using the SQL 2005, the log file is really big, I have found the reason and I can delete the item from the database,however what I should do with the log file, Is it possible if we can just modify the log file by items,I mean just delete the specific item in LogFile, or what I can do is just shrink or delete the log file?
Thanks for reading and waiting for any reply.
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.