Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-02-10 : 08:19:51
|
| Tim writes "I am the DBA for a 52GB SQL 2K SP3 Database on Win 2K3. The database has daily loads and jobs that usually push the Transaction log up to a range of 3.5 - 6GB of used space. There is a Maintenance plan to run a daily full backup and transaction log backup. Some days the transaction log backups run in 5 minutes and are 2-3GB in size. Other days the transaction logs, still in the 3.5-6GB range, run for about an hour and end up 50GB+ in size, usually larger than that days Database backup. What would cause this?A shrink of the Database is only done once a week. There does not appear to be any pattern to when it occurs. The maint plan gives the file a timedate stamped name, so it is not appending. I have had this happen running it throught sqlmaint and the backup wizard. There are some Index Defrags run on some days but the Trans log file never reaches 50GB. It occurred last Wednesday ran fine all week and then occurred again Monday night. I have only found one other occurrence of this on the internet and there was never a definitive answer given. I have posted this question and had pretty in depth discussions about possible causes on another board if you are interested in looking at this. The link to that post is http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=24&messageid=158874I just found your site while continuing to try and find a solution, and I decided to give it a try. Thanks,Tim" |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-02-10 : 14:28:46
|
| I would get rid of the maint plans - you don't really know what they are doing.Try using the profiler to trace the commands to see if something else is happenning.Why are you shrinking the database every week - that's usually a really bad idea.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
|
|
|
Why is my Transaction Log Backup 50GB when my original Transaction log was only 5GB?