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
Thanks for your code for SQL SERVER – Shrinking Truncate Log File – Log Full
I tried it and it worked!!!
I wish there was something as plain and simple in Books online.
Albin
Its working fine .It helped so much i was struglling this issue from so many days [truncating log with min size.]
Very very Thanks
It’s amazing how long it took me to figure this out for SQL 2008. All the stuff that worked in SQL 2000 and 2005 no longer works.
Here’s what works.
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)
Msg 155, Level 15, State 1, Line 2
‘TRUNCATE_ONLY’ is not a recognized BACKUP option.
GIVE SOLUTION
USE DEMO
GO
DBCC SHRINKFILE(EUROSHOE_log, 1)
BACKUP LOG DEMO WITH TRUNCATE_ONLY
DBCC SHRINKFILE(EUROSHOE_log, 1)
GO
Thanks. Its work and my issue resolved.
Nitin Joshi
SQL Server 2005 Books Online:
“[TRUNCATE_ONLY]
This option will be removed in a future version of SQL Server. Avoid using it in new development work, and plan to modify applications that currently use it.
…
We recommend that you never use NO_LOG or TRUNCATE_ONLY to manually truncate the transaction log…”
Yeah Its True JAHA …all situations depends;
The NO_LOG or TRUNCATE_ONLY options truncate the transaction log. When you use these options, you might not receive a full database backup!!
I feel that in OLTP envirnment where DML commands are running most of the time .
it creats a lock on .LDF and doesnt allow physical update on LDF ..is it true champ then what we can do such situation .
As you mentioned above in the article that use syntax
USE DatabaseName
GO
DBCC SHRINKFILE(, 1)
BACKUP LOG WITH TRUNCATE_ONLY
DBCC SHRINKFILE(, 1)
, 1 refers to MB if i am not wrong …my question is
1) can we set the minimum size for transaction log
2) If we can ,then we need to mention the minimum size rather then 1 .if i am not wrong .
3) if we simply mention the DBCC SHRINKFILE (ABC_LOG)without mentioning the Size then how much will it reduse .
4) I found sometime command dosent work it comes with error ..
1 refers to the identification (ID) number of the file to be shrunk. To obtain a file ID, use the FILE_ID function or search sys.database_files in the current database.
SELECT file_id, name FROM sys.database_files
If target_size is specified, DBCC SHRINKFILE tries to shrink the file to the specified size. If not specified, DBCC SHRINKFILE reduces the size to the default file size.
1 refers to the identification (ID) number of the file to be shrunk. To obtain a file ID, use the FILE_ID function or search sys.database_files in the current database.
i.e.
SELECT file_id, name FROM sys.database_files
If target_size is specified, DBCC SHRINKFILE tries to shrink the file to the specified size. If not specified, DBCC SHRINKFILE reduces the size to the default file size.
Thanx SAM
…..! Can we Shrink LOG file while using DML commands by users !
If yes Why , If no Why ..!
Thanx in advance
If primary transaction log file is exceed in large volume then is it good to restrict size , is there any wrong impact if we restrict
the size.
…..! Can we Shrink LOG file while using DML commands by users !
If yes Why , If no Why ..!
Thanx in advance
Pinal ,
If my Log Grows bigger then Disk space , will my database comes in SUSPECT MODE ..or crashes the SQL server completly .
Scenrio :
SQL install on C: and Log files on D: ….in this condition also can crash my SQL INSTANCE …
Praveen
Thanx Pinal Dave ….
In both the snerios as i mentioned a previous post !!
And offcourse i would like to thanx for this Shairpoint where we can discuss our views Thanx once again !
How to truncate log file in SQL Server 2005
SQL Server 2005 is quite different from SQL Server 2000. To truncate log file is one thing which is different from SQL Server 2000. In SQL Server 2000, you just use Shrink to whatever file size you like. In SQL Server 2005, sometime I cannot shrink the log file at all.
Here I want to describe some tricks to truncate log file for a database in SQL Server 2005. The work environment is MS SQL Server Management Studio.
I. Shrink the log file size at the right time
I found out this trick:
Immediately after I use the SSIS package or Import the data to the database ( highlight the database->Tasks->Import data … ), or Export the data from the database ( highlight the database->Tasks->Export data … ), I can shrink the log file to the desired size, for example, 1MB. That is, highlight the database->Tasks->Shrink->Files
set the file size, say, 1MB.
Then, click OK and you are done.
II. Eliminate the log file completely
Sometimes we just do not need the big log file. For example, I have 40GB log file. I am sure I do not need this log file and want to get rid of it completely to free up the hard drive space. The logic is
a. Detach the database
b. Rename the log file
c. Attach the database without the log file
d. Delete the log file
Let’s say, the database name is testDev. In the SQL Server Management Studio,
Highlight the database-> Tasks->Detach..-> Click OK
Go to log file folder -> rename the testDev_log.ldf to be like testDev_log-aa.ldf,
Highlight Databases->Attach…-> Click Add -> add the database testDev, highlight the log file and click the ‘Remove’ button. This means you only attach testDev.mdf
After this is done, you can verify the contents of the attached database and then delete the log file.
Hi There…
I am having a problem… i have a database with 12GB log file.. and i have tried to shrink the log file which has not worked then i have tried doing the steps as suggested by Praveen Barath, but while attaching the mdf file with out ldf files. I am getting following error. Please help me to resolve this issue.
TITLE: Microsoft SQL Server Management Studio
——————————
An error occurred when attaching the database(s). Click the hyperlink in the Message column for details.
——————————
BUTTONS:OK
——————————
Thanks
Ram
My pleasure Chief .
Hi Pinal,
I was wondering if there is a way to shrink all log files of all databases. I’m trying to do a script to acomplish this task and so far I cannot make it work.
The problem is that you have to issue a Use DatabaseName before using the shrinkfile(Filename_log) statement. I tried using the Exec and the dynamic command execution but it doesn’t seem to work. I appreciate any help you could give me and keep up the good work!
Regards.
Hi Jean Paul Craig, did you find the answer to this?
I’m trying to do the same thing on all databases.
Thanks in advance.
Tom
Hi, Jean and Tom
Enjob the Below Script…..
DECLARE @DbName VARCHAR(10)
DECLARE Crsr CURSOR
FOR
SELECT name FROM sys.databases
OPEN Crsr
FETCH NEXT FROM Crsr INTO @DbName
WHILE @@Fetch_Status=0
BEGIN
PRINT ‘USE ‘ + @DbName
PRINT ‘GO’
PRINT ‘DBCC SHRINKFILE(‘ + @DbName + ‘_log, 10)’
PRINT ‘GO’
PRINT ‘BACKUP LOG ‘ + @DbName + ‘ WITH TRUNCATE_ONLY’
PRINT ‘GO’
PRINT ‘DBCC SHRINKFILE(‘ + @DbName + ‘_log, 10)’
PRINT ‘GO’
FETCH NEXT FROM Crsr INTO @DbName
END
CLOSE Crsr
DEALLOCATE Crsr
GO
Regards,
Nirav Raval
In the given script I assume that log file name must be with _log name after dbname…. as I am always having the same…. :)
Thank you.
We recently migrated a server to a new PC image. We reinstalled the SQL server to the same versions of the original PC. After this migration our TempDB has started to grow tremendously fast.
We have implemented a purge of the TempDB to prevent the server from crashing.
Is there a way to see what is making the Tempdb grow?
Is the SQL Server log files can help us to restore our database in original situtation if it crashesh.
IF Yes
HOW?? (In SQL 2000 and 2005)
ELSE IF No
THEN WHAT IS THE USE OF LOG FILES???
END IF
Thanks.. Pinal
Hi
This is Dhirendra , n have the problem in SQL Server 2000 as Log file is full while in my Databse Table not much more data.
Thanks
Hi My name is Jitendra Verma,please tell me the procedure of reducing the log file size.
use dbname BACKUP LOG dbname WITH TRUNCATE_ONLY
DBCC SHRINKFILE(dbname_log,2)
use tempdb
BACKUP LOG tempdb WITH TRUNCATE_ONLY
DBCC SHRINKFILE(templog,20)