Transaction Log error

Question

Transaction Log error

y.koteswarrao-652921

SSCrazy

Points: 2010
More actions
June 17, 2010 at 9:40 am

#140941

Hi,
iam trying to delete the records from the table
and got the below error........
Error:9002,severity:17,state :4 the transaction log for the database is full,to find out why space in the log be reused,see the log_resue_wait_desc column in sys.databases
i have checked the column in the database it is showing "active transaction"
and when is space available in the log file
Max Size :30720
size : 30464
can any give resloution for the issue..........
thanks
Koteswar rao

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Adam Angelini SSCrazy Eights Points: 8548 More actions · Answer 1

run dbcc opentran(databasename) to see what the oldest running transaction is. You can then use sp_who2 and dbcc inputbuffer(spid) to see what the transaction is doing and who is running it.

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 2

Is your log file not allowed to grow? Or is the disk full?

You likely have a large open transaction, as Adam mentioned. Are you running log backups? These clear committed transactions from the log and allow space to be reused. Also, you might want to read this: http://www.sqlservercentral.com/articles/64582/

y.koteswarrao-652921 SSCrazy Points: 2010 More actions · Answer 3

i executed the dbcc opentran(),there were no transactions.............

even i have taken the full database backup & transaction log backup............i tried the shirnk the log file......

after doing above work process............when trying to deleted the records that table contain 20000+ and getting the same error.............

there were two log files which are reside on the different drives.............

one log file is full and another log file as some space.......

plz give me the solution..

koteswarrao

Suresh B. SSC-Insane Points: 22986 More actions · Answer 4

Try to delete the records in smaller chunks. For example: 2,000 records at a time using TOP/WHERE clause.

Also take log backup in between these deletes.

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 5

I would follow Suresh' advice. Delete in small batches. A large delete is logged as one operation, and you need the space to record all those changes in the log file.

y.koteswarrao-652921 SSCrazy Points: 2010 More actions · Answer 6

hi,

thanks for your resolution steps it was resloved..............................

how to list all views from each databases in sql server. Can you help me this out?

catalogviews

compatability views

dynamic management views

and etc.,

Thanks