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 want to know how to restore log record if i delete row from my database and restore it again
1. There are (still) log reader tools out there that might allow you to check the transaction log for commands and undo some of them, DELETE being such a case.
2. Usually, a backup is restored under a different name and you will use a SELECT INTO command, to copy the record from the restored database to the old one. This technique assumes that you have a backup management policy in the first place.
3. Please try to be more specific next time, because there are a plethora of solutions for a plethora of scenarios :-)
best regards,
calin
Hi Pinal,
I would like to tell you that shrinking the database log file doesn’t cause database fragmentation.
Hello sir,I am facing a problem in a database sql server 2008 which is very slow to load the application in windows forms.I manually increased the size of mdf and ldf file but I dont know the proper way too improve the performence in database tuning I also did the tuning advisor no use.I working.as a windows developer and I am struggling every day with this issue.
please help me
Hi,
If I have a DW Database that generate a 20GB Log file some times.
In SQL Server 2005, I use “backup log with truncate only” and after “shrinkfile”. In SQL Server 2008 I will need realize backup to disk and after delete the file?
Thanks
No, you should change your recovery model to Simple and avoid generating the logs to begin with.
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/recovery-models-sql-server?view=sql-server-2017
If you are happy to lose data the simple is fine. If you don’t want to lose data then you are stuck with the discussion in this article. Maybe one day M$ will reimplement their previous model where backups truncated the log file – that was the best of all worlds!
Thanks Bryan,
In SQL Server 2005, A database with simple recovery model generate a Log with more than 20 GB with a great insert. This isn’t minimaly logged.
The table already have data and index.
You should Simply Checkpoint after the insert then.
And then SHRINK ONLY THE LOG FILE. But if its know that the application will need that logs space, then its recommended that the space is reserved for the log file. However in a BI case, I would manually grow the log before the ETL process, and shrink ONLY THE LOGFILE after with SHRINK FILE with the name of the logical log file.
if autogrow option is enable means is it effect the performance?
WHat is best method to follow when mdf and ldf files are growing?
Hi,
What about changing the database model from Full to Bulk-Loged then do some bulk insert operations then switch it back to Full, does it case the 2 points mentioned at the end of this post?
Thanks
@Fabricio: I highly recommend either do the insert in batches, or leave the log as is, after it has grown. SQL Knows how to handle internally your transaction log and what to clear in case of the SIMPLE recovery model. If you constantly shrink a log file that is doomed to grow again, you might end up having physical fragmentation (check your transaction log file with a defragging tool).
@kishore: you want to check a feature called instant file initialization (google is your friend) for the auto-grow of the data file (and only the data file!!). As for the log file, simply define your database to have a large enough transaction log to play with. Unfortunately this also relates to your recovery model (namely transaction log management), so I have no idea what to say about what size is the correct size for the transaction log. No, 25% of the data file, as Microsoft used to recommend is not quite the best starting point.
@Smith: the only major issue is for switching from either FULL of BULK-LOGGED to SIMPLE, or vice-versa, from SIMPLE to either FULL or BULK-LOGGED. Switching from FULL to BULK-LOGGED has no effect on the backup chain whatsoever (the dreadful log sequence number, aka LSN chain). But please keep in mind that BULK-LOGGED minimally logs some operations, and that’s all. Because there are some special pages that are affected by bulk operations, the next transaction log backup has to reflect those changes, so expect it to be bigger than you would normally think.
@Pinal: apologies if I replied to some of the questions, I took the liberty of doing this because I’m sure you don’t have the time to do it right now.
best regards,
calin
sir, i am working in Oracle 10g i want record every transaction (INSERT,ALTER,DELETE,…) in log file it is possible throw oracle. if possible please explain with examples.
Yours
SIVANANDA REDDY
Is there any way or system tbl to find the database name which is scheduled for backup operstion ?
Hi,
I have a scenario where I have 2 SQL servers and a db mirrored between them. I need to backup and truncate the log file, preferably with sql job instead of doing it manually. I see the backup with truncate option has been removed from SQl 2008 unless you do a manual tranlog backup then the option still exists.
Is there some code I can run to emulate transaction log truncation with a backup?
I can’t change the recovery model to simple because I will loose the mirror connection if I had to do this.
Thanks, Shawn.
Hi Pinal,
I am permanent reader of your blog, I am fresh as a DBA.We migrated our DB from SQL server 2005 to 2008. At SQL Server 2005 size of log file was 27GB but at 2008 it increases up to 300GB and DB is pretty Slow now.Please tell me the ideal size of log File and how to reduce the log file size.We are using Full back backup option.
I need help in this case.
Thanks.
Azhar Iqbal
DBA Lahore, Pakistan.
Thanks. It worked. But remember one thing that do not forget to take full backup of your database before truncating log file
I have to write code to get backup in log file
The code is
—————
Backup LOG profusion_dev
To Disk = ‘E:\SQL_Backup_LOB\back_up_files\prof_dev_log.bak’
I got an error this
———————–
Msg 4208, Level 16, State 1, Line 1
The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
Plz any body correct this coading
Hi Dinesh.,
Check the recovery model of the database ‘profusion_dev’.
If the database is in ‘Simple’ recovery model, it will not allow ‘Log Backup’.
Because, the log file is consequently truncated, and there is nothing to backup.
Regards,
Riyaz
hi,
How to Optimize Database Size?
How to Optimize tempdb Database Size?
It works like magic but u need to change the ‘master’ to the ‘DBName’ it reduced my 207MB to 1024kb
Hi guys,
I have followed the recommendations and started backing up my database hosted in MSSQL 2008R2 once a day in with a maintenance plan that does this:
1. full backup of the DB
2. shrink DB
3. History cleanup
Comment: in the Studio Manager 2008 R2 you have the possibility to check the T-SQL statement in each maintenance plan step you plan to implement.
On the step 2 (shrink DB) I see the statement is:
USE [myDBname]
Go
DBCC SHRINKFILE (N’myDBname’, 10, TRUNCATEONLY)
… hm so this means that the maintenance plan is actually using TRUNCATEONLY even though Microsoft suggest stopping using this practice. I’ve stopped the scheduled DB maintenance now, because after 2 runs my LDF file is already 7GB bigger than before, so bad news for me.
So my final question to you guys is: how do I “properly” backup the transaction log? Maybe I mustn’t use a full DB backup and shrink, but other option. I hope you can shine some light. Thank you in advance.
Lucian
I’m in the same boat here. 2008 SQL. I use Symantec BE2010 and also backup using a Maint plan to external HD. My ldf files are growing without bounds. None of the dbcc shrinkfile commands work. Symantec tells me to switch to full from simple recovery. I have tried both no luck. What is the OFFICIAL way to full backup and truncate the logfiles every night so they do not grow and remain small. Please don’t leave any syntax out if you have a script. I have tried many and none work with 2008 SQL. Either bad syntax or just don’t work saying no longer supported.
MUY BUENO MUCHAS GRACIAS
is there any way to know shrink history? i.e, when was last shrink on mdf or ldf performed..?
Dear Pinal,
I have some question.
1. We have SQL 2008 R2 installed. Our system use DPM to backup SQL database periodicaly and other important data. Currently we use full recovery mode to all database and it made log file grow rapidly and create insufficient disk space error. Could we change the recovery mode to simple?
2. What is the difference between DPM and SQL backup?
thank you.