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
this is no more available in 2008.
If you want then follow this :-
1) first check the log file usage by dbcc sqlperf(logspace)
2) you will see the results in % of use, less the percentage more is free to shrink.
3) if your database have less % then check how much you can release using DBCC LOGINFO(DatabaseName)
4) There will be 2 entry 0 and 2. the more 0 in bottom the more you can shrink and will be shrinked till last 2 or default size.
5) run dbcc shrinkfile((‘logical file name’) — it will shrink the file upto default value.
6) take full backup
hope this helps
Hi Pinal,
If i shrink a log file. Is Database must have in simple recovery mode?
Is it true.. or else we shrink a log file at any recovery mode.. Reply plz
With Regards
Balaji.G
@Balaji.G,
It is not necessary to have “Simple” recovery mode for shrinking.
USE Database_name;
GO
— Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE Database_name
SET RECOVERY SIMPLE;
GO
— Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (Database_name_log, 1);
GO
— Reset the database recovery model.
ALTER DATABASE Database_name
SET RECOVERY FULL;
GO
Im shrinking the database log files using this method manually.
But I want to auto shrink the log files when it reaches 1GB.
can any one help me on this..
Thanks in Advance..
instead of shrinking, you should be considering the changing backup interval to more often.
Thanks a lot. It works fine.
Hi
I am working in SQL server2005. there are log files with extention .ldf and .mdf.. These files increasing continuously
I just want to know what is the use of these files in DB and how to shrink them ?
Please help…
the above code is not working in SQL 2008.
It is showing the synatax error i.e ‘TRUNCATE_ONLY’ is not a recognized BACKUP option.
Kindly guide me in this..
Hi Pinal,
Is there any way I can create a Job which triggers native log backup If the log file size reaches 80% of its capacity in any given time ? This is to clears out committed or aborted transactions after a log backup.
Thanks!!!
HI,
how to open the (.ldf) log file in sql server,i want read that file
Hi, Pinal
I am afraid what you have given as a sol’n does not work, at least in some cases – such as mine – see below:
I attempted to shring log file repeatedly without success.
The DB is in Simple Recovery Mode
I use
DBCC SHRINKFILE(mlsMail_Log, 1)
BACKUP LOG mlsMail WITH TRUNCATE_ONLY
DBCC SHRINKFILE(mlsMail_Log, 1)
GO
Have tried
backup log mlsMail with truncate_only
several times, no change
—————-
DBCC sqlperf(logspace) – shows less than .01 % used
DBCC OPENTRAN – shows no open Trans
—
DBCC LOGINFO(‘mlsmail’) shows the following:
FileId FileSize StartOffSet FSeqNo Status Parity Create LSN
2 134152192 8192 42215 0 128 0
2 134152192 134160384 42216 2 128 0 Tags:
Hi,
Which scenario we do shrinking data or log file?
If the log file is growing large and it contains active transactions can we shrink the file
Thanks
koteswarrao
I have a mirrored environment in 2005. Now the log file is 30 GB and my mdf file is 4 GB. Can someone suggest the way to shrink log file.
Normal procedure is not working when i tried to shrink the log file in MIRRORED ENVIRONMENT.
Help me out please.
You really helps me lot, my log file size about 27 GB and i got worried because my server harddisk partition size was just 30 gb and there was only 400 mb remains showing, after getting help by your this post i applied to my server and your syntax is totally correct . thanks a lot.
Thanks
Amit Pathak
Great service.
Use full information.
Thank you for posting.
I wanted to truncate a log file from a java process and whether I did it manually or used logrotate, when the process would output another line the filesize would jump back to what it was previously. I tried sending a HUP signal to the process but that would kill it altogether so not an option. In the end I found a solution which is to start the process with a double arrow redirect instead of a single one, eg:
java file >> log_file 2>&1
I can now use all tools to rotate the file, the process keeps writing to it and the file sizes are permanently reduced.
Hi, Pinal
I wonder am not an IT Guy. can you help me out to work on my LOG file which is growing a lot bigger day to day.
i searched all kind of net notes and even try to read the notes.
can i have the step by step process how to shrink the file.
my date base is 5 GIg but now it is occupies around 50 Gig.
hope i can get a help from you,how to do it
were to write the code and how to work it out.
thanking you
suneel
Thank You. It worked for my system (MS SQL Server 2000) as well where the Log Size was around 160 GB.
It took around 8.5 minutes for the query to complete. Also, an important aspect is that I didnt have to put my DB offline or detach it either.
Hi,
I want to truncate my log from my database in sqlserver.
Like i was using before for sqlserver 2005.
dump tran with no_log.
I don’t want to follow below stpes everytime to release my database.
1) deattach databse
2) rename log file or delete
3) attach again
4) check transaction log file size.
In SQL 2008 use this
USE Database_name;
GO
– Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE Database_name
SET RECOVERY SIMPLE;
GO
– Shrink the truncated log file.
DBCC SHRINKFILE (Database_name_log);
GO
– Reset the database recovery model.
ALTER DATABASE Database_name
SET RECOVERY FULL;
GO
But Dan, don’t forget to take the Full backup after this process
What exactly it means Arshpreet…