March 5, 2009 at 10:22 am
I have a 500 Mb database with a 500 Mb logfile. Database is in Simple recovery mode, gets a Full backup daily at the end of the day (and a differential backup at noon).
I thought that Simple recovery mode wiped the transaction log on a full backup, so why is the logfile so large on the disk? I issued a Shrink for the logfile in SSMS and it went down to a tiny fraction of its former size.
Thanks,
Rich Mechaber
March 5, 2009 at 10:32 am
Transactional replication?
Done any index rebuilds recently?
rmechaber (3/5/2009)
I thought that Simple recovery mode wiped the transaction log on a full backup
No. In Simple the log is truncated whenever a checkpoint occurs. Truncated meaning that the inactive portions of the log are discarded and the space made available for reuse. The size of the file won't change.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 5, 2009 at 10:50 am
- must be a large open transaction.
does dbcc opentran show info (position in the correct database) ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 5, 2009 at 11:07 am
OK, that makes sense, Gail, thanks. I have no open transactions, so I'm guessing a very large transaction occurred at some point in the past and I've never issued a shrink command for the log file.
Would you recommend as a best practice that I include a Shrinkfile for the log in my daily Agent backup job?
Thanks again,
Rich
March 5, 2009 at 11:30 am
If your log file is 500MB at the end of the day, it probably needs to be that size to handle all the activity that occurs between full backups.
March 5, 2009 at 11:38 am
No, shrink should be rarely run. Only when something happens you didn't expect.
March 5, 2009 at 11:45 am
rmechaber (3/5/2009)
Would you recommend as a best practice that I include a Shrinkfile for the log in my daily Agent backup job?
Absolutely not. Worst thing you could do (short of enabling autoshrink). If you shrink the log it will just grow again, that growth will slow everything down. Repeated shrink/grow cause file-level fragmentation and small growths of the log cause internal log fragmentation resulting in slower backups.
A large log file is not a problem. There's no performance/admin/maintenance/etc overhead from having a log file that's partially empty. Best thing to do is work out how big the log needs to be for regular operations (use DBCC SQLPERF(LogSpace) to see % used), then make the log file slightly bigger than that and leave it alone.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 5, 2009 at 11:57 am
OK, now I'm confused. If a database regularly creates, say, 50 Mb a day in transactions, but once a year I create 500 Mb of transactions, then every day I'll be backing up (on my network tape backup, not in SQL Server) an unnecessary 450 Mb of empty space, won't I? (Probably will be removed by the tape drive's hardware compression, but still...)
Rather than manually shrinking the logfile if I execute an exceptionally large transaction, why not just automatically shrink it daily? Is the I/O performance hit to grow the log file during daily use that significant?
Thanks again,
Rich
March 5, 2009 at 12:05 pm
rmechaber (3/5/2009)
If a database regularly creates, say, 50 Mb a day in transactions, but once a year I create 500 Mb of transactions, then every day I'll be backing up (on my network tape backup, not in SQL Server) an unnecessary 450 Mb of empty space, won't I?
No. Backups just backup data, not empty space.
If you have a 500MB database (with 400 MB used) and a 500MB log file (with say 10MB used), your backup (without any compression) will be at most 410MB.
That's why empty space in a file is not a problem.
If it's a once a year large transaction, sure, shrink it once off afterwards. If it's once a week, rather leave it. Why risk slow performance and timeouts if you don't have to?
It's more likely once a week that the space is needed, or however often you rebuild all the indexes in your database. My guess is that the index rebuild/reorg is the cause of the large log file
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 5, 2009 at 12:39 pm
A few more points (in addition to the already excellent info already out here).
- you can't issue a BACKUP LOG against a DB in simple recovery mode without getting the following:
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.
- Your database backup will contain whatever data space is used in the database file, and (per BOL) "enough of the transaction log to produce a consistent database when the backup is restored". So - if will NOT backup all of the used space in the LOG, just the minimum required to make sure transactions are fully committed.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 5, 2009 at 12:57 pm
Thanks very much to everyone who replied. I appreciate not only the answers but the explanations/recommendations.
Yours,
Rich
March 5, 2009 at 2:37 pm
Gail
i was under the impression that a database backup in simple recovery mode also initiates a checkpoint which will truncate the log file
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 5, 2009 at 2:52 pm
Perry Whittle (3/5/2009)
i was under the impression that a database backup in simple recovery mode also initiates a checkpoint which will truncate the log file
Might do. Did something I said give the impression that it doesn't?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 5, 2009 at 4:28 pm
Another thing to consider is rebuilding indexes, especially wide indexes, will use quite a bit of the Log file and then release it (didn't say shrink). That's another good reason to not shrink it.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 6, 2009 at 4:18 am
that's a good point Jeff, although Simple and Bulk Logged recovery do minimally log create and alter index transactions
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply