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 |
|
richardps
Starting Member
33 Posts |
 Posted - 2007-06-05 : 05:13:57
|
| Hi,On SQL 2000, if I have a large transaction log for my database and I manage to fill it with a single large transaction but it is not set to autogrow, what happens? |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-06-05 : 06:33:53
|
| The statement will fail, and be rolled backKristen |
 |
|
|
richardps
Starting Member
33 Posts |
Posted - 2007-06-05 : 06:42:40
|
| Thanks Kristen, that is indeed my experience. However I'm also finding that the transaction log remains full and I can't do anything further on the database. Can you explain this? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-06-05 : 07:02:20
|
| It will need to get to the next Checkpoint I think. You can force that.You can also truncate the log, but that's a bit extreme! and not really the "right" solution for a production system.Kristen |
|
|
|
richardps
Starting Member
33 Posts |
Posted - 2007-06-05 : 08:25:27
|
| Exactly what I thought!However, it appears not to be the case. If I type checkpoint I get the following error:Could not write a CHECKPOINT record in database ID 126 because the log is out of space.Msg 9002, Level 17, State 6, Line 1The log file for database '<mydbname>' is full. Back up the transaction log for the database to free up some log space.Any ideas? And is this to be expected or have I set something up wrong?Many thanks Kristen. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-06-05 : 08:28:11
|
| "because the log is out of space."hahahaha - Catch-22!You'll have to truncate it then, I reckon that's the only way.Kristen |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-06-05 : 09:43:59
|
| Or expend log to get more free space. |
|
|
|
|
|
|
What is simple recovery mode?