?
Solved

Rollback if Transaction log becomes full

Posted on 2012-08-24
7
Medium Priority
?
873 Views
Last Modified: 2012-08-28
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?
0
Comment
Question by:Eamon
7 Comments
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 38329416
The transaction would be rolled back.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 38329529
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
 
LVL 1

Author Comment

by:Eamon
ID: 38329836
@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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 1400 total points
ID: 38329985
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
 
LVL 16

Assisted Solution

by:DcpKing
DcpKing earned 400 total points
ID: 38331934
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
 
LVL 9

Assisted Solution

by:keyu
keyu earned 200 total points
ID: 38335540
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 38341266
That last link is EXTREMELY old -- the latest version it applies to is:

Microsoft SQL Server 6.5 Standard Edition!
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question