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 |
|
brandonl
Yak Posting Veteran
58 Posts |
 Posted - 2005-02-17 : 12:03:19
|
| I have a server with a transaction log that keeps growing. It is now twice the size of the database file. I can kill the file, and generate a new one, but it grows again.Is there anything I can look at or do to determine why the tx log keeps growing so large?Some further information: It's a SQL database for a SMS 2003 server, and a server has been removed from the server list, but still shows up in the database. Is it possible that it's trying to do updates for the records of this server and can't do it, so they are getting stuck in the tx log?~BrandonL |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-02-17 : 12:36:26
|
| are you backing up the tlog at regular intervals? |
 |
|
|
brandonl
Yak Posting Veteran
58 Posts |
Posted - 2005-02-17 : 12:50:01
|
quote:
Originally posted by eyechart
are you backing up the tlog at regular intervals?
Not currently. Should I set it up to backup the tx log (perhaps daily), set to remove inactive entries from the transaction log?I'll try that for the next couple of days, if that's what I need to do, to see if that helps. Fortunately, we have a ton of drivespace on the SAN, so it's got a bit to grow. We have a ticket with MS Premier to see how to remove this "dead" SMS Server-to see if that's what's causing the growth.~BrandonL |
|
|
|
martinch
Starting Member
35 Posts |
Posted - 2005-02-21 : 03:48:21
|
quote:
Originally posted by brandonl
[quote]Originally posted by eyechartShould I set it up to backup the tx log (perhaps daily), set to remove inactive entries from the transaction log?
Yes, this *should* stop the file from growing too quickly. Note that unless you run ShrinkFile, backing up the TLog won't decrease the file's size (and you won't want to do that too often, as grabbing disk space is expensive, and leads to fragmentation, as I'm sure you're aware). Tara (tduggan) has some very good SP's on her Weblog, one of which will do all the transaction log backups for you - no effort needed!  The other thing you could do is set the max size of the TLog in the DB properties, but I'm not sure that's a totally good idea, just in case the log file gets full before it's backed up (unless you set its max size to be something that should never be attained). |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-02-22 : 23:55:21
|
| If you don't need to be able to restore to a "point-in-time" just change the database recovery to "simple", then you will only be able to recover to your last full backup, but the logs won't grow. As martinch said, if they've grown out-of-hand already then shrink the file, but thereafter leave it alone - unless you do some massive delete that has to create an unusually large log fileKristen |
|
|
|
|
|
|
|
|
Transaction Log Growing