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
Another best way to truncate log file with out taking transactional backup would be,
1. Change recovery model from Full to Simple ( using Interfaces) and then change it back to Full. By doing this your log file will be truncated.
2. Run DBCC ShrinkFile ( ‘LogfileName’ , 1)
By doing this, you will definitely loose your transactional log data that was stored in log file. But this will truncate Log file with out taking any backup.
Regards,
IM
@srikanth
try and use the Logical Name of the logfile, that you can find in the Database Properties. This name doesn’t have to be the same as the Windows Explorer filename.
Hi Pnal,
I want to truncate the log file automatically after a specific interval. Is there any way to do this?
Thanks
Sanjay
Harsha,
you can quickly delete your database table data with this script:
—
— DELETES ALL TABLE ROWS and resets all IDENTITY fields to start from 1!
—
/*Disable Constraints & Triggers*/
exec sp_MSforeachtable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’
exec sp_MSforeachtable ‘ALTER TABLE ? DISABLE TRIGGER ALL’
/*Perform delete operation on all table for cleanup*/
exec sp_MSforeachtable ‘DELETE ?’
/*Enable Constraints & Triggers again*/
exec sp_MSforeachtable ‘ALTER TABLE ? CHECK CONSTRAINT ALL’
exec sp_MSforeachtable ‘ALTER TABLE ? ENABLE TRIGGER ALL’
/*Reset Identity on tables with identity column*/
exec sp_MSforeachtable ‘IF OBJECTPROPERTY(OBJECT_ID(”?”), ”TableHasIdentity”) = 1 BEGIN DBCC CHECKIDENT(”?”,RESEED,0) END’
Regards,
Vedran Kesegic
You can also use ‘TRUNCATE TABLE ?’ istead of ‘DELETE ?’.
Should be quicker on big tables and also resets identity to starting value, so you do not need last command in script (“reset identity” part).
I’ve been searching everywhere, and I can’t seem to find an answer. This is for SQL Server 2005 SP2.
I’ve got a data warehouse database that is essentially remade each night with new data from our production environment. Since all of the data in the data warehouse exists and is being backed up elsewhere, we can rebuild it in the case of a catastrophic failure and thus we have set the database to SIMPLE logging.
Every night however, the SSIS jobs that fill the data warehouse will completely fill up the log file. This is 80 GB worth of drive space (the .mdf files are on a separate drive), and our log drive is getting full. I’ve scheduled two truncates during the night, and it’s STILL getting full. I’m at my wits end trying to figure out why a SIMPLE log is getting SO much stuff inside it. Any suggestions would be much appreciated.
Hi!
I m using SQL 2K5 standard edition and my database server is mirrored with another server. Now, as my log file size growing and it becomes 35 GB, can anybody guide me how can I reduce size of this log. Just remember that Database is mirrored and I dont want to disturb my mirror setup.
Thanks,
Devang
@Devang
SQL Server by default Truncates Transactional log file when it takes log backup.
But when databases are being mirrored, this condition is not valid. Even though Sql Server takes backup of log file, it does not truncate log file.
You cant even shrink Transaction log file of the database that is being mirrored. Shrinking database will only shrink Principal database but not Mirrored database. (Please look at the link provided below)
Take a look at below link. Author do suggest a method to truncate log file with out actually stopping Database Mirroring, I dont know if this will work. Please do not run these script on Production, first test it out in TEST/DEV Env.
Link :
Note: Again I am not sure, if method described in above link will work. The only way I know of, to truncate log file is to stop Database Mirroring, and then truncate log file and then re initiate database mirroring.
Once you truncate log file, then you can shrink database by using stored procedure provided by Microsoft to be used on mirrored databases.
Link : https://support.microsoft.com/en-us/help/937531/
~ IM.
Imran i m very thankful to you.
It’s very helpful suggestions for me which you provided here.
will work on test env.and let u kno…
Thanks again…
Devang
Imran,
After executing following query
BackupLog to disk=‘D:\dbname.trn’
error shows as ‘ incorrect syntax near the keywork to’
i tried with changing queries by other way but not succeed.
I like 2 know how can we backup database on network drive?
let me know..
thanks
Devang
@Devang
Script :
backup log database_name to disk=’\\myserver\sharenam\foldename\database_name_datetime.bak’
You should have created a share folder on some other server where you will be taking backup to. Folder should have hand symbol.
Remember, the share folder should have write access. Meaning normally when we create a share folder, by default read only permissions are assigned. But since you want to write backup into this shared folder, you should assign write permissions.
Let me know what error you see when you execute above script. Change database_name and file name as per your standards.
~ IM.
Imran
Query for backup log executed successfully, but database log remains in same size, I m using Mirroring and it is not possible to shrink database in mirror mode. How can I shrink database / truncate transaction log if database in mirror mode.
How can we use transaction log in SQL? What exactly use of this log? U may feel this is very sily que.. but, i want to kno depth on it.
Thanks!
Devang
@Devang
First check the occupied space and free space in logfile.
Execute below script in master db.
dbcc sqlperf (‘logspace’)
This will give you how much log file space is occupied and what percentage of it is free. If you see that your log file for your database is all empty, then you can think of shrinking.
How do I shrink MY databases which are being mirrored,
I stop database mirroring and then I truncate log file and then I re initiate database mirroring. I do this in Maintenance window.
Microsoft does provides a stored procedure which can be used to shrink database that are being mirrored. I myself have not used it, so I don’t know what will be the impact of executing this stored procedure. I already provided you link for this stored procedure in my previous reply.
Here is the link : https://support.microsoft.com/en-us/help/937531/
Again, Test it out in DEV/TEST before you implement this in Production.
In case if log file for that particular database is still occupying a huge space (After you have taken Transactional Log backup ) and there is no or very less free space then I suggest you, in your maintenance window, you stop database mirroring and then take log backup, this will definitely truncates your log file, then shrink log file. And then Re Initiate Database Mirroring ( Re-Initiating involves setting up database mirroring right from the scratch)
Note : After you take Transactional Backup, I strongly suggest you, that you first test restore your full backup and then Transactional backup and see if database can be restored successfully. It happened to me that after I took transactional backup, I removed database mirroring and then because of some issue when I wanted to restore database backup with point in time recovery, transactional backup did not work.
So make sure that you test your transactional backup.
Again, Test it out in DEV/TEST before you implement this in Production.
Let us know if you see any issues. Its always good to ask and know if you have any doubts.
~ IM.
Thanks for the script! I have been able to run it successfully in the past, but now I’m running into a problem. I’m trying to run the script as follows:
USE ReportingServices
GO
DBCC SHRINKFILE(ReportingServices_log.LDF, 1)
BACKUP LOG ReportingServices WITH TRUNCATE_ONLY
DBCC SHRINKFILE(ReportingServices_log.LDF, 1)
GO
Everything appears to be correct but when i execute the script I get the following:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘.’.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ‘.’.
Any thoughts as to what I’m doing wrong?
Jeremy,
You cannot have the “.LDF” in your script.
Remove the file extension and just use the “ReportingServices_log” name (without quotes) and that should solve your problem.
Christian
Very useful article thank you. I used your snippet of code as a basis for my code snippet example. Thank you.
Brent
I have a variation on the log problem. I need to shrink/delete my replication logs in the unc folder. Is there a script available that I could automatically run every month that would go into my replication log files, and delete all the logs up to the prior day? I really don’t need them. That way, I could just set up the job and forget about it.
Thanks
For those of you that need a maintenance plan solution.
Add a backup task for the log backups, and then add a Execute T-SQL Statement Task joined by a green arrow (which means on success) Set up your log backups to run as often as you require and then in the Execute T-SQL statement box, paste the following code:
use Database
Dbcc shrinkfile (database_log, 1)
You may not get immediate results but as the check points are marked by the backups, the log file will get smaller.
That works fine……..
USE temp;
GO
— Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE temp
SET RECOVERY SIMPLE;
GO
— Shrink the truncated log file to 100 MB.
DBCC SHRINKFILE (temp_Log, 100);
GO
— Reset the database recovery model.
ALTER DATABASE temp
SET RECOVERY FULL;
GO
Thanks pinaldave &
Thanks Nikhil
Script worked fine :)