| Author |
Topic |
|
jesus4u
Posting Yak Master
204 Posts |
 Posted - 2004-07-27 : 09:09:05
|
| How do I prevent my transaction log from getting full? I am using a shared SQL 2000 with my hosting provider and I get this error about once a week. Isn't there a setting in Enterprise Manager that I can use?thanks |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-27 : 09:11:49
|
| Is your recovery mode set to Simple? If it is, and you're hosting company isn't doing transaction log backups, then tell them to:1. Start doing them.2. or, set the recovery mode to Simple.3. Find someone who has a clue about SQL Server because they're IDIOTS!!MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
jesus4u
Posting Yak Master
204 Posts |
Posted - 2004-07-27 : 09:17:09
|
| then will simple prevent the log from getting full? |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-27 : 09:19:10
|
| It will unless you just perform something huge on the server. If that's the case, then there is nothing to prevent it. Normally, this isn't the case with web hosting databases though. They need to set it to simple and do a DBCC SHRINKDB on the database. If you have the problem again, let us know and we can walk through other ways of preventing it or getting around it.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
jesus4u
Posting Yak Master
204 Posts |
Posted - 2004-07-27 : 09:21:25
|
| What would be an example of something huge? |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-27 : 09:23:10
|
| Oh, a procedure that runs batch updates at night and has cursors six levels deep, updating a couple billion rows.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
jesus4u
Posting Yak Master
204 Posts |
Posted - 2004-07-27 : 09:24:17
|
| LOL That is a good one!Thanks....so I'll shrink the log and ask my host to set the db to simple. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-07-27 : 13:07:01
|
| Reindexing will also cause the transaction log to fill up.Tara |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-27 : 14:03:17
|
| Are you happy that you could only recover from the last full backup? If so, then SIMPLE is fine.Otherwise you need to do transaction backups (every hour or every 10 minutes are popular chooices) and then you can recover to any point in time. This is GREAT when you do DELETE MyTable and evaporate a billion rows by accident!EDIT: Personally I couldn't contemplate having a database that allowed updates etc. (i.e. NOT readonly) that didn't have this facity to save me from myselfKristen |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-07-27 : 14:09:10
|
| We dump trannies every 10 minutes.....Brett8-)EDIT: Oh....ahem...TEST |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-27 : 14:18:56
|
| Remember guys that this is a web host database we're talking about. He'll be lucky if they're really doing their full backups nightly. :)MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-27 : 14:24:58
|
| Pretty scary if they didn't know to set his DB to SIMPLE then ... their servers are all going to be full by the middle of next week!Kristen |
|
|
|
drymchaser
Aged Yak Warrior
552 Posts |
Posted - 2004-07-27 : 16:26:24
|
quote:
Originally posted by X002548
We dump trannies every 10 minutes.....
 |
|
|
|
|
My Transaction Log is FULL