Sometimes it looks impossible to shrink the truncate log file. Before you run any script from this page, please read the warning below, because this is one of those fixes that solves today’s problem and quietly creates next month’s.
Read this before you run anything. Breaking the log chain means you can no longer restore this database to a point in time. If the server fails tomorrow, you can restore your last full backup and nothing after it. Everything since then is gone. Do it only when the alternative is worse, and take a full backup the moment you are done.
First ask why the log is full
A log file that will not shrink is almost never the real problem. Something is holding on to the log, and SQL Server will tell you what, if you ask it.
SELECT name,
recovery_model_desc,
log_reuse_wait_desc
FROM sys.databases
WHERE name = 'YourDBName';That last column is the answer to your question. The ones you will meet most often are these.
LOG_BACKUP means the database is in FULL recovery and nobody is taking log backups. This is the usual cause by a wide margin. The fix is to start taking log backups, not to shrink anything.
ACTIVE_TRANSACTION means somebody left a transaction open. Find it and deal with it. Shrinking will not help while it is still running.
REPLICATION or AVAILABILITY_REPLICA means another feature has not finished reading the log yet. Those need their own attention.
NOTHING means the log is free to be reused, and you may genuinely just have a file that grew once and never came back down.
The proper fix in FULL recovery
If you need point in time recovery, and most production databases do, the answer is log backups on a schedule. Take one, and the space inside the log becomes reusable without breaking anything.
BACKUP LOG YourDBName TO DISK = 'D:\Backup\YourDBName_log.trn';
Put that on a schedule, every fifteen minutes is a fair starting point, and the log stops growing on its own. If the file itself is now far bigger than it needs to be, shrink it once after the backup and then leave it alone.
DBCC SHRINKFILE (YourDBName_Log, 1024);
Notice that asks for 1024 MB rather than the smallest size possible. The last section explains why that matters.
If you do not need point in time recovery
Some databases genuinely do not need it. A reporting copy you rebuild every night, or a development database, can sit in SIMPLE recovery. Then SQL Server clears the log for you and the whole problem goes away.
ALTER DATABASE YourDBName SET RECOVERY SIMPLE; DBCC SHRINKFILE (YourDBName_Log, 1024);
Be honest with yourself about which databases those are. If losing an hour of data would cause a phone call, it is not one of them.
The emergency version
The disk is full, the application is down, and you need the space back now. This is the script most people come to this page for.
ALTER DATABASE YourDBName SET RECOVERY SIMPLE; DBCC SHRINKFILE (YourDBName_Log, 1024); ALTER DATABASE YourDBName SET RECOVERY FULL; BACKUP DATABASE YourDBName TO DISK = 'D:\Backup\YourDBName_full.bak';
That last line is not optional. Switching to SIMPLE and back breaks the log chain, so every log backup you already hold becomes worthless from that moment. The full backup starts a fresh chain. Skip it and you have quietly left the database unprotected, and you will find out on the one day that matters.
About the old script on this page
For years this post carried a script built around BACKUP LOG WITH TRUNCATE_ONLY. If you have found that command in an old script of your own, here is the thing to know: Microsoft removed it in SQL Server 2008. It does not work on any version you are likely to be running, and there is no replacement, because the answer is one of the approaches above. I have left this note here because that command is still passed around, and people deserve to know why it fails rather than wondering what they typed wrong.
Why shrinking again and again is a bad habit
If you find yourself shrinking the same log every month, stop and ask why it keeps growing. The file only grows back to the size it actually needs, and every one of those growths is slow work for the server while your users wait. Shrinking to the smallest size possible makes it worse, because the very next busy transaction has to grow it again from nothing.
Work out the size your workload really needs, set the file to that, and give it a growth setting in megabytes rather than a percentage. Then leave it be. A log file sitting at a steady size is doing its job, not wasting space.
There is more on this in SHRINKFILE and TRUNCATE Log File in SQL Server 2008.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
314 Comments. Leave new
Very nice, worked well for my situation, thank you :-)
Hi, can you please help me?
At this moment I’m using this for purge the log files:
USE myDB
DECLARE @LOG_FILE VARCHAR(100)
SELECT @LOG_FILE = UPPER(LTRIM(RTRIM(NAME))) FROM SYSFILES
WHERE RIGHT(LTRIM(RTRIM(FILENAME)),3) = N’LDF’
CHECKPOINT
BACKUP LOG mYDB WITH NO_LOG
DBCC SHRINKFILE (@LOG_FILE,TRUNCATEONLY)
Is that correct? I need to know if it is eficient, if this command doesnt generate a performance damage to sqlserver.
Do I need to purge tempdb in the same process?
I run this command every 30min.
Thanks in advance
Every 30 minutes? You are tripling or quadrupling the I/O on your disk without appreciable benefit. Why are you doing this?
You’ve broken the backup log chain, so unless you are taking full backups, you have lost the ability to restore to a point in time or do a valid transaction log restore. Any further transaction log backups are useless.
You’re writing to the transaction log file as transactions are submitted to the database. Then you are taking a backup with NO_LOG (removed from SQL Server 2012) . Then you do the SHRINKFILE. Then, with the next transaction, you autogrow the transaction log and start the cycle all over again.
Since transaction log files do not benefit from instant file initialization, as you grow the transaction log file, it must first be zero’d out, then it can be written to. With each autogrow, you are pausing the database. If your autogrow increment is small, you have massive file-system level fragmentation; if it is large, you have noticeable pauses.
Last, with NOTRUNCATE, only empty pages at the end of the transaction log file are released. If you are constantly filling up the transaction log, you are likely not accomplishing anything at all.
Marc, thanks for your reply.
I really dont use the logs, I use full backups. But what I have really noticed is when I truncate or purge the logs, my app’s performance increase dramatically, that is the reason why I truncate every 30min.
What I need is:
– the best scenario for truncate the logs, thinking that I dont need them
– what I must do whit tempdb, do I need to truncate it?
– the correct sql command (transact sql) for doing this
Thanks again
A tiny layout-tip:
The first line of code should read:
USE
(that makes the markup more/100% consistent) :cool:
can log be full for a particular table, even if there is enough space in the drive?
Hi, Please help me to find answer for the follwing:
I am using SQL Server 2008 Standard Edition.
1. Can i take compressed backup with Standard Edition?
2. What is correct time to shrink the transaction log? Before or After Full backup?
3. Is it necessary to backup transaction log if Full backup is completed successfully?
4. If I shrink transaction log once Full backup is completed, will i loose any data?
i need to delete my records in a table as well as reduce my .logfile size how?
plz give me replay@@
Hi Pinal,
I want to monitor log files of all DBs. If any of the log file is grown above 80%, then it should shrink. Please provide solution. Any script?
Hello Pinal,
I want to truncate my sharepoint config database and WSS_Logging database logs the size of sharepoint_config database is growing at a pace of ~10GB every week. I have scheduled a weekly full backup. Current .ldf file size is 113GB.
I am using SQL server 2012 with Always On High Availability feature. I am not able to convert the recovery mode from Full to Simple.
Please suggest in my case to reduce the log file what I need to do.
Thanks & Regards,
Paresh Jain
1. Are you taking regular transaction log backups?
2. Is the secondary replica synchronized?
What do you in for below?
Select log_reuse_wait_desc from sys.databases
1. Are you taking regular transaction log backups?
I am taking weekly full backup using maintenance plan in sql management studio. No, separate backup for transaction log backups has been schedule.
2. Is the secondary replica synchronized?
While I raised this query on my secondary replica it was showing Not-Synchronizing, but later I had to go to individual database under my Always On replica. and had to click Resume Data movement. Wanted to understand after failure is it normal that we will have to individually click on Resume Data Movement? Could you suggest.
After I clicked on resume data I took log backup and shrink the log file, the log file size was reduced drastically.
You are not taking t-log backups and still you are able to get the space back? I don’t believe so. I can bet my money that log backup is taken on other secondary replica.
Hello Pinal,
No, I said I am taking full database backup weekly, not taking transaction log separately. And the file size got reduced as I took manual transaction log backup due to oversizing/growing size of my sharepoint config database log file. Manual backup on both primary and secondary replica helped me to reduce my file size.
Now 2 queries:
1. Is it mandate to take a transaction log backup apart from weekly full backup to keep my transaction log in control.
2. Wanted to understand after failure over is it normal that on each database, we will have to individually click on Resume Data Movement, for secondary replica (primary replica got a roll of secondary after failover) to function in synchronizing mode? Could you suggest.
@Paresh
1. Yes. You MUST take transaction log backups to keep LDF size under control. This is irrespective of availability group.
2. If movement is suspended then resume is needed.
Hi,
I m trying to use the following code, but showing me the error . i m using sql 2008r2
‘truncate_only’ is not a recognized BACKUP option.
use dbname
go
dbcc shrinkfile (xyz_log, 1)
backup log dbname with truncate_only
dbcc shrinkfile (xyz_log, 1)
go
Please solve my query, it’s very urgent for me and my client.
use dbname
go
dbcc shrinkfile (xyz_log, 1)
dbcc shrinkfile (xyz_log, 1)
go
that will work without he backup. you can do the back up before
Thanks a lot .
Hello Team,
We have a SQL database server and the disk space becomes full due to the transaction log file which is huge as 750 GB. And the remaining space on the disk is around 20 GB, and I’m pretty sure this gonna be full at some particular point of time.
To avoid any future interruption on the transaction’s which are happening on daily basis, I need to shrink the file which will be relatively very small when done using the SQL server management studio. As to my knowledge this should be the procedure (select database -> Task -> Shrink -> Log and select the desired option in next window and click OK), please correct me if there is any alteration needed.
At this point, I have 2 questions
i) What will be the total time needed to complete the task (Log file data size is 750 GB).
ii) While this process is ongoing will there be any transaction mismatch/lost or affect anything from user side. In parallel is there any impact gonna happen due to this shrinking.
However, If the process is gonna take for several hours, can we make a partition/split this file to 6 or 7 sub-files depending on the size and time elapsed?
Kind Regards,
Thomas John
Hi,
I am getting below error,can anybody help me out
Cannot shrink log file 2 (TestLog) because total number of logical log files cannot be fewer than 2
i am unable to fix