Note: Please read the complete post before taking any actions.
This blog post would discuss SHRINKFILE and TRUNCATE Log File. The script mentioned in the email received from reader contains the following questionable code:
“Hi Pinal,
If you could remember, I and my manager met you at TechEd in Bangalore.
We just upgraded to SQL Server 2008. One of our jobs failed as it was using the following code.
The error was:
Msg 155, Level 15, State 1, Line 1
‘TRUNCATE_ONLY’ is not a recognized BACKUP option.
The code was:
DBCC SHRINKFILE(TestDBLog, 1)
BACKUP LOG TestDB WITH TRUNCATE_ONLY
DBCC SHRINKFILE(TestDBLog, 1)
GO
I have modified that code to subsequent code and it works fine. But, are there other suggestions you have at the moment?
USE [master]
GO
ALTERÂ DATABASE [TestDb] SET RECOVERYÂ SIMPLE WITH NO_WAIT
DBCC SHRINKFILE(TestDbLog, 1)
ALTERÂ DATABASE [TestDb] SET RECOVERY FULLÂ WITH NO_WAIT
GO
Configuration of our server and system is as follows:
[Removed not relevant data]”
An email like this that suddenly pops out in early morning is alarming email. Because I am a dead, busy mind, so I had only one min to reply. I wrote down quickly the following note. (As I said, it was a single-minute email so it is not completely accurate). Here is that quick email shared with all of you.
“Hi Mr. DBA [removed the name]
Thanks for your email. I suggest you stop this practice. There are many issues included here, but I would list two major issues:
1) From the setting database to simple recovery, shrinking the file and once again setting in full recovery, you are in fact losing your valuable log data and will be not able to restore point in time. Not only that, you will also not able to use subsequent log files.
2) Shrinking database file or database adds fragmentation.
There are a lot of things you can do. First, start taking proper log backup using following command instead of truncating them and losing them frequently.
BACKUP LOG [TestDb] TOÂ Â DISKÂ = N'C:\Backup\TestDb.bak'
GO
Remove the code of SHRINKING the file. If you are taking proper log backups, your log file usually (again usually, special cases are excluded) do not grow very big.
There are so many things to add here, but you can call me on my [phone number]. Before you call me, I suggest for accuracy you read Paul Randel‘s two posts here and here and Brent Ozar‘s Post here.
Kind Regards,
Pinal Dave”
I guess this post is very much clear to you. Please leave your comments here. As mentioned, this is a very huge subject; I have just touched a tip of the ice-berg and have tried to point to authentic knowledge.
Update: Small typo correction and small detail corrected based on feedback.
Reference: Pinal Dave (https://blog.sqlauthority.com)
79 Comments. Leave new
i have done test database its successfully executed log file size has been reduced ..shall i execute live database without any upshot?? pls advice me
I have two SQL Server 2008 R2 DB Log file (in FULL recovery model) of size 60GB and 9GB respectively, I found that even after using the SQL Server Management Studio UI to backup the transaction log files, their size did NOT reduce significantly but only reduced by around 1%.
What should I do ?
It is possible that you have an open transaction. Check this link: https://www.sqlskills.com/blogs/paul/script-open-transactions-with-text-and-plans/
I executed the SQL statement in the provided URL and I found the DB Log file of 60GB have 2 open transactions entries while the DB Log file of 9GB have no open transactions entries. In both cases, what does it mean if I have or have not open transactions ? How do I shrink the DB Log file size eventually ?
Hi, I have always found solutions from this site. Need your help to shrink the .LDF file of a database in SQL Server 2008.I did run the scripts below but my .LDF never shrinks. Also, I have read in many blogs to avoid changing the recovery type from simple to full and vice versa as it would corrupt the database.
Need your help at the earliest as I am in a very critical situation.
— Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE
SET RECOVERY SIMPLE;
GO
— Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (, 1);
GO
— Reset the database recovery model.
ALTER DATABASE
SET RECOVERY FULL;
GO
There is no database name in line 4. It should be DBCC SHRINKFILE(DBNAME,1)
I am wrong … it should be DBCC SHRINKFILE(DBNAME_log,1)
Hi, I am having some issue in SQL Server 2012 Miorroring. Can any body help me out here over the mobile. My No is : +91 9711842009.
Regards;
Raj
Hi,This is very useful for me.
Thanx for such a useful blog.
sorry Mr but it didn’t work… I executed it but the log file did not reduce its size…(not a bit)
I found that if issue this command after a database backup it reduce the log size but it does not return the empty space back to the Windows as free space. You need to issue also DBCC SHRINKFILE (Logical Log File) after that.
It is possible to shrink log file without truncate option. I need the script for shrink the log file without truncate.
I had shranked sql database log files but there was no space recovered from server?
Can you help on this…
Gracias firu!
Jorge Gracias!
USE Roehampton_University_MSCRM;
GO
— Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE Roehampton_University_MSCRM
SET RECOVERY SIMPLE;
GO
— Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (Roehampton_University_MSCRM_1, 1);
GO
— Reset the database recovery model.
ALTER DATABASE Roehampton_University_MSCRM
SET RECOVERY FULL;
GO
Replace ‘Roehampton_University_MSCRM’ with your DB name. copy and paste fail.
https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql?view=sql-server-2017
Replace ‘Roehampton_University_MSCRM’ with your DB name. copy and paste fail.
Hi Pinal ,
Recently we have migrated our application (both Web app and Database) to Azure (IASS) . There we have a VM , in which SQL Server 2014 installed . Also i have restored all the necessary databases and SQL Server jobs in the same server (from the existing On-Premise DB Server) . All the settings are verified and the application also working fine for a week . Then we noticed a problem with the log file Size
(Size is increasing and reached around 42 GB and not able to execute any of the basic query and everything break :( ) . The same DB and Job ran in on-premise server for a year and this issue didn’t happen .
Some information about the application and DB
* This Application contains couple of SQL scheduling job which runs each 2 hours of a day and the job take maximum of 5-8 Minutes to complete.
* Application contains less than 200 users and concurrent users are around 20-30.
So please let me know your comments/input on this (Transaction log file size increment) issue . Also please let me know if you need any other information
SQL engine is same in IaaS and On-Prem for same version of SQL.
I am not sure why you say that its due to Azure..
Hi Pinal,
i have two developers for developing the application and i want to purchase the SQl server license, which is good edition for the same, i have also one production server for application and 200 user accessing this application developed in asp.net and sql sever
Hi Pinal,
I am waiting for reply
I have gone through same issue. I used simple above solution and its working good for me. It’s all about recovery and Shrink.
USE [master] GO
ALTER DATABASE [TestDb] SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE(TestDbLog, 1)
ALTER DATABASE [TestDb] SET RECOVERY FULL WITH NO_WAIT
GO
Hope this will help for you also.
just a syntax error in the dbcc and my problem is i always go to your website to get the script :)
USE [master];
GO
ALTER DATABASE [test2] SET RECOVERY SIMPLE WITH NO_WAIT
USE [test2]
DBCC SHRINKFILE([test2]_log, 1)
USE [master]
ALTER DATABASE [test2] SET RECOVERY FULL WITH NO_WAIT
GO
You file name is [test2]_log ? Are your serious? What is your database name?
Hello Pinal Dave. Would you go out with me? I think your site is amazing. I have a huge poster of you hanging above my bed so I can gaze into your eyes while I str
Hello Pinal,
I would like to know if there is any difference between shrink file through GUI and through script because, I scripted out the shrink file and executed and space was not reclaimed but then again I ran the shrinkfile through GUI and it worked helping me reclaiming a lot of space. Note: This was a one time thing and the DB was in full recovery mode.