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
We have lost some of transaction due to sudden power failure, it is possible to recover those lost transactions. We are using SQL Server 2005 with sp3.
We even notice any problem after power was established, but after receiving complaints regarding some transactions lost we came to know that there are some of the last minute transactions are lost.
Mr.Mahesh,
You can recover lost transactions by restoring tail log backup. Before that you have to restore all logs which you have taken after full backup of your database…
Dear pinal,
kindly please tell me how to find max values among multiple columns in separate column which directly shows the max values of every row in that column .
regards
uttam
Refer this post and point 3
Dear All,
I would like to shrink my database transaction log file as I already tried by detaching database, ranaming log file and attach it again but its not allowing me to do so. Could you please tell me otherways to shrink log file.
Thanks
Hi Pinal,
Thanks a lot for your post. I am looking for a script which monitors the log file and sends us alert to us and then automatically triggers log backup job once exceeds max threshold.
Please help me if you have any regarding this.
Thanks,
KKK
Gracias. Tenia una BD que se habia vuelto bien loca y por el manegement intentaba hacer esto mismo pero no lo hacia finalmente.
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.