transaction log full error when more than 100000 records in table

[557]

in my sql server database, when i have more than 100,000 records in a table, and i try to delete most of the records or all the records of that table, i get an error saying the transaction log is full and i need to empty it. if the number of records are not much, this doesn't happen. what could be the reason and how can i rectify it?

 

[ArtieChoke]

If you want to delete all of the records, use the truncate table command. Otherwise, you might have to delete in smaller groups or bump the log file size.

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[557]

thanks.

 

[mrdenny]

When you delete records the delete command is logged into the transaction log. Apparently your transaction log is set to either not auto grow, or your drive is full. Either issue will cause a file full error.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[SQLSister]

A third possibility is that it is set to autogrow but not fast enough to account for that many records being deleted. You might consider increasing the percentage or size of the growth allowed.

As far as truncate table, I would beware of using that as it does not log the deletions making it harder to get back the information if deleted in error. PLus of course it is only useful if you are delting the whole table, not just a portion of it.





Questions about posting. See faq183-874

 

[557]

i talked to my database manager about it and he said he'll increase the size of something. don't know whether it's the transaction log or the database. will this solve the issue? when this error occurs when i execute sqls thru vb6, i give

BACKUP LOG ConvCtrl WITH TRUNCATE_ONLY

thru sql query analyzer and then, when i execute the same queries that gave the error, everything works properly, no problem. does this issue concern the size of anything?

 

[SQLSister]

Yes, as I said, it can happen if the transaction log is not set ot grow fast enough. Or as Denny said, the log could not be set to grow or the drive could be full.

BTW, never truncate the transaction log without making a full backup first. If you did that to a database I was the dba for, I'd have you fired. Infact if you did that without my express persmission as the the dba, I'd have you fired. No one except the dba should be taking actions like that.

Will teh actions of the dba solve the issue? Maybe depends on what he does and to what extent. It may take several tries to solve the issue.

Questions about posting. See faq183-874