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 |
|
Jaybee from his castle
Yak Posting Veteran
64 Posts |
 Posted - 2006-12-07 : 05:51:56
|
| Yes folks, this old chestnut....Some DILF (Developer I'd Like to Fire)keeps filling up a tranny log on one of my DB's. It's only 1.7G and she's getting the log up to 4GB every few days with some weird DTS packages the business sees fit to implement...I've truncated the log, what needs to be done to ensure it works more efficiently? The recovery mode is 'Full', we have an automated non-SQL backup strategy in place, but when I tried creating a .bak file on the E: drive and backing up the log in EM I got some "Device offline" message?!?!?!?Any advice appreciated,JBCheers,JB |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-12-07 : 06:47:25
|
| Increase the frequency of the TLog backups, so that they will cut-in before the massive transaction happens and/or perform the DTS in smaller batches.If the DTS is bringing in data that already exists (i.e. grabbing everything from the source, rather than just material that has changed) it could pull it to a staging table in a SIMPLE Recovery model database and then from there selectively update records which are different - thus reducing the "hit" on the main database.Kristen |
 |
|
|
Jaybee from his castle
Yak Posting Veteran
64 Posts |
Posted - 2006-12-07 : 08:49:48
|
| That's a plan - also, is there something about a clustered environment that inhibits ad-hoc backups?? I can't imagine why I'm getting this "Device offline" error, some kind of permissions issue...??Any chance this could be connected with a similar problem with 'Sendmail' not sending emails?? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-12-07 : 09:37:00
|
| Sorry, not things I'm familiar with. |
|
|
|
|
|
|
Trans log filling up with DTS package