August 24, 2010 at 9:36 am
I inherited a SQL 2000 database that was about "30GB" in size. But it turns out the Transaction Log was 10GB, and the .mdf was 20GB.
I upgraded this database to SQL 2005, then I did a FULL backup, then a Transaction Log backup. Then I did a DBCC SHRINKFILE ( [LogFile], 50 ), and the log shrank to about 300MB -- great!
Then I set: Weekly FULL backup, daily Differential, and hourly Log backups. This ran for about a week, the log stayed the same size. Still great.
Then I needed to do some major work on the database. So I did a FULL backup (right after the scheduled hourly Log backup had taken place.) I ran out for a coffee, when I cam back the next Transaction Log backup had happened... and that .TRN backup file is 10GB! I checked my Log file and it is about 11GB... what the heck! I checked and it is set to auto-grow by 256MB... double-what-the-heck!!
Any ideas what the heck might be going on?
Thanx!!!
August 24, 2010 at 9:43 am
Index rebuild job?
How often are the tran log backups and how long did the full backup take?
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
August 24, 2010 at 9:52 am
GilaMonster (8/24/2010)
Index rebuild job?How often are the tran log backups and how long did the full backup take?
I have a Maintenance Plan that does the FULL backup, and I have it set to also do almost all other maintenance, such as integrity check, rebuild indexes, reorganize indexes, update statistics, and shrink database. I ran this Maintenance Plan to do the FULL backup. Could one of these case the huge Transaction Log file growth?
I didn't watch to see how long the FULL took, sorry.
I have the regular Transaction Log backups occur every hour except at midnight, where weekly at midnight there is a FULL, and every other day at midnight there is a Differential.
Thanx!
August 24, 2010 at 10:18 am
The index rebuild will cause the log expansion.
Also not all those tasks are esential database maintainence, you should not be regularly shrinking your database as this causes massive fragmentation.
August 24, 2010 at 10:38 am
jpSQLDude (8/24/2010)
such as integrity check, rebuild indexes, reorganize indexes, update statistics,
Yeah, that'll cause the log growth. Any reason why you're doing redundant work? Reorganise after rebuild of indexes is a waste of time. The indexes won't be fragmented after a rebuild, so there's no need to reorganise.
...and shrink database.
If you're shrinking you may as well not even bother to rebuild those indexes. The shrink will likely have left them more fragmented than they were before the rebuild started. You're basically spending a lot or resources and time putting the indexes into a nice order (and probably growing the database in the process) then more time and resources squeezing those indexes into the smallest space possible regardless of proper order
See - http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/
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
August 24, 2010 at 2:13 pm
Thank you Gail, I especially love the part where you say, "All too often I hear of maintenance plans that first rebuild all the indexes, then shrink the data files. That kind of maintenance is worse than useless" -- its like you were inside my head!!!
:hehe:
Thanks again.
September 20, 2010 at 12:47 pm
Gail... this is excellent... thanks... Mark 🙂
September 27, 2010 at 8:12 am
Also, why is a 10 GB log file considered to be huge? If the database is 30GB, a log file of 10 GB is not uncommon in a high OLTP system.
But, as usual, Gail is right on target. Youa re doing a lot more work that you need to!
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
September 28, 2010 at 7:11 am
Excellant
October 11, 2010 at 7:10 am
?When our transaction log grows large and we want a quick way to shrink it, then we have to change the database recovery mode of the database we want to shrink from “full” to "simple," then truncate the log file by performing a full backup of the database, then switch back to the “full” recovery mode. By temporally changing from the Full recovery model to the Simple recovery mode, and then back, SQL Server will only keep the "active" portion of the log, which is very small. Hence, the log will be small.
Regards
Sushant Kumar
MCTS,MCP
October 11, 2010 at 7:18 am
By doing that you've also broken the log chain and left the system open to data loss.
After switching to simple recovery and back to full you will not longer be able to take log backups until another full/diff backup of the database is done, and if anything happens to the DB you could be looking at hours of lost data.
Does your business know what risks you're opening them to by doing that? Are they happy with those risks?
The whole 'truncate your log and shrink' is very high on the worst practices around transaction logs. Consider managing your logs properly and please don't tell people to do this unless you also make clear to them what the risks are.
Take a look through this article - http://www.sqlservercentral.com/articles/64582/
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
October 11, 2010 at 7:19 am
SKYBVI (10/11/2010)
(1) ... then truncate the log file by performing a full backup ...(2) By temporally changing from the Full recovery model to the Simple recovery mode, and then back, SQL Server will only keep the "active" portion of the log...
1. I am pretty sure that when you perform a FULL backup, that never truncates the Transaction Log.
2. I am pretty sure that any time you perform a Transaction Log backup, the log is truncated (except in very rare circumstances, such as a very long-running transaction). But this does not actually shrink the Transaction Log physical file, that is done manually.
October 11, 2010 at 7:30 am
jpSQLDude (10/11/2010)
1. I am pretty sure that when you perform a FULL backup, that never truncates the Transaction Log.
It doesn't, however....
In simple recovery a checkpoint truncates the log.
A backup runs a checkpoint right at the beginning of the backup process.
Hence, it may look as though the full backup truncated the log when the DB was in simple recovery. It's not the backup though, it's something the backup runs and this is only in simple recovery
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
October 11, 2010 at 11:20 pm
Okay, I'm stupid. When, if ever, should you truncate the log file? Or should you just move parts of it to another log file? Funny, NONE of this was ever really covered in the 70-431 exam prep books....
October 11, 2010 at 11:45 pm
pietlinden (10/11/2010)
When, if ever, should you truncate the log file?
Generally, never.
If there has been poor (no) management of the log before, or log backups have been failing for days and no one noticed (why) then it may be necessary to abandon the log chain and start over. That would be by switching to simple recovery (backup log... truncate only is deprecated in 2005 and gone in 2008), then switching back to full, taking a full/diff backup and starting/restarting the log backups, probably followed by a once-off shrink of the log (and only the log) to get back to normal.
It's generally something that should only be done in a rare, extreme situation and as part of fixing the problem, not as a fix by itself
Or should you just move parts of it to another log file?
In most cases there's no point to two log files.
Funny, NONE of this was ever really covered in the 70-431 exam prep books....
And we're surprised why?
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
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply