Rollback if Transaction log becomes full

Hi,

A database I'm working on has it recovery model set to FULL and I can not change this. Its a clients DB.

Sample SQL code attached:
BEGIN TRY		
		
		BEGIN TRANSACTION
			-- Do my Insert						  
		COMMIT TRANSACTION
		
END TRY

BEGIN CATCH	
		ROLLBACK TRANSACTION	
END CATCH

Open in new window


If the transaction log become full when I'm doing my insert, will the roll-back happen or does the DB fall over?
LVL 1
EamonAsked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
NO.  Activity STOPS if the log if full -- it does NOT rollback.  Rollback can require making changes -- but changes can't happen if the log is already full!
0
 
EvilPostItCommented:
The transaction would be rolled back.
0
 
Scott PletcherSenior DBACommented:
If the transaction log becomes full, ALL modifications on the db STOP IMMEDIATELY.  NO further inserts/updates/deletes can occur on that db until the log space issue is corrected.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
EamonAuthor Commented:
@ScottPletcher - So are you in agreement with EvilPostIt that the transaction would be rolled back? And then no further inserts/updates/deletes can occur

@EvilPostIt  - So a roll-back can occur when the Transaction log becomes full? I'm just double checking as I think I may have seen that it didn't happen on my Clients DB. It appeared as if the transaction log became full and no roll-back occurred. The DB just hanged until the log space was corrected.

 I don't have access to the DB so I can really check.
0
 
DcpKingCommented:
Scott is right: the rollback won't happen if the log is totally full and can't be extended. It's seriously suggested that you don't let a DB get to this state. You can, for example, have programs that send email to various people when the disk space starts getting low.
0
 
keyuCommented:
hi,,

when it becomes full you can take immediate backup of it and make it empty for further transaction...for more info on this you can also refer link given below...

http://support.microsoft.com/kb/110139
0
 
Scott PletcherSenior DBACommented:
That last link is EXTREMELY old -- the latest version it applies to is:

Microsoft SQL Server 6.5 Standard Edition!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.