SQL Server 2014 :: Stored Proc Timing Out Halfway Through - Rolling Back Without Transaction
Jul 7, 2015
We have a high volume database with 1000's of users and 1000's of procs. Our application enforces a 20 second timeout on all connections.
We can't adjust the 20 seconds - this is a business rule.
It sometimes happens that a proc does not complete within 20 seconds and then times out halfway though. This causes data inconsistency where 50% of the code was saved to the DB and 50% was not - seeing that a stored proc is not transactional and therefor does not roll back the code.
We can't put the code in a TRANSACTION in order to roll back when a time out occurs, because this causes exclusive locks on the tables.
So I guess my question is:
Is it possible to undo/rollback all the code in a proc when a timeout occurs - without using a TRANSACTION? And if a TRANSACTION is the only way - how do I avoid the exclusive lock and blocks?
I have a stored procedure that calls another stored procedure with thefirst stored procedure opening a transaction:BEGINSET XACT_ABORT ONBEGIN TRANSACTIONdoes various updates/insertscalls 2nd stored procedure to proccess updates/inserts common to manyother stored proceduresdoes more various updates/insertscommitENDThe problem I'm having is that within the 2nd stored procedure is thatif it encounters an error, it does not roll back the entiretransaction and I finish up with missing records in the database. Amusing this in the 2nd stored procedure:if(@TypeId1 = @TypeId2 and @Line1 <'' and @Line2 <'')beginRAISERROR('error message', 16, 1)RETURNendWhat could the problem be? From what I've read, it seems as thoughyou can't have an open transaction within one sp that calls another spand it maintains the same transactoin? Is this corrrect?I tired the following too, and I still couldn't get it to work. Anyideas anyone?************ sp 1 ***********Declare @AddressError char(3)SET XACT_ABORT ONBEGIN TRANSACTIONexec Sp2@AddressError OUTPUT,@variable1,@variable2,etc. etc************** sp 2 *****************@AddressError char(3) OUTPUT,if(@TypeId1 = @TypeId2 and @Line1 <'' and @Line2 <'')beginRAISERROR('error message', 16, 1)RETURNendSET XACT_ABORT ONBEGIN TRANSACTIONprocess updates/insertsSet @AddressError = 'no'Commit******** back to sp 1************If @AddressError <'no'BEGINrollback transactionENDcontinue doing updates/insertscommit
I have a case where I read from SQL Server DB and write to a flat file.
I have one Data Flow Task inside which I have a OLEDB source component that feeds rows to a script component that writes to a flat file. I have set the txn attributes for the container to "Required" and "Read committed" . But I find that rows are written to flat file even when I throw an exception from my script component. Question is how do I prevent rows from being written to the flat file if error/exception happens. I want the whole process to be in a single transaction.
Ive got an insert statement that fails, and below that I have code like the following:
IF @@ERROR <> 0 BEGIN -- Roll back the transaction ROLLBACK -- Raise an error and return RAISERROR ('Error INSERT INTO Address.', 16, 1) print 'test was here' RETURN END
However, there is now rollback and the inserts below it are going through.
hi, i want to create a disaster recovery site, to which i can fail over (not automatically), and also to have the option to return the database to a point in time? for example, if my principal server fails in 17:00, i want to have the option to make the mirror server available for users from 17:00 (or at least close to that time), and also to be able the return to the data from 16:00 (in the mirror site). Is it possible, and what is the best way to do it?
I have a page that runs a transaction correctly after a button click. I want to allow someone to click a button that rolls back the transaction, after the transaction runs on the first button click. I can also successfully roll back within the first button click. I'm getting a NullReference error when trying to access SqlTransaction.Rollback() outside the button click. If SqlTransaction.Commit() completes without error can SqlTransaction.Rollback() be called after? I tried making 'trans' a more global variable and it still gave me the error. Button Click 1: Dim trans As SqlTransaction trans = connection.BeginTransaction() try 'run SQL Statement trans.Commit() Catch e As Exception trans.Rollback() throw e end try
Does anybody know of a way to rollback SQL Server 2005 databases back to SQL Server 2000? Is there a way of doing it without resorting to Copy Database Wizard? I love to find a way of attaching a SS 2005 database to a SS 2000 instance without any issues.
I recently upgraded to SS 2005 and I am very unhappy with the SS 2005 and I want to rollback to SS 2000, which was a lot more stable. I am having several major issues that are affecting my whole company's day-to-day operations and the managers are not happy. Some of the issues include night time batch running very sluggish for no apparent reason. This is a biggest problem because it only occurs once or so a week and causes a disturbance with the daily activities when the night time processing isn€™t completed on time. The rest of the time, the batch processing runs great, even a little better then on SS 2000. I don't believe it is a matter of my application needing to be retuned because if that was the case, then why isn't it running sluggish every night? Also, it's never the same day that the sluggish behavior occurs. If it was occurring on the same night, then I would have something to investigate within our application, but it doesn't. Another issue that I am having involves a night time job that restores a copy of the production database to the Data Warehouse server to be used for updating the data warehouse. Again, most of the time it runs great (~2 1/2 hours), but once or twice a week, it goes stupid and takes 6 1/2 hours for no apparent reason. Again, it is not happening the same day either, which could give me something to invesigate. On SS 2000, this same job ran flawlessly. Never I did I run into situation that the database restoration took that long to run. Even another issue involves a SQL Server Agent Job that was put into suspended state. What's a suspended state and how can I get it out of suspended state? I can find no information about suspended state in BOL. I did a Google and nothing came up. If this suspended state was put in for security reasons, great, but then tell me how I can remove the suspended state. I am also not happy with the fact that I can't get accurate information about the queries that are actively running at that particular moment. In SS 2000, when I noticed high CPU usage on the server, I would run the sp_who2 active stored proc and it would show me all the active thread and how much CPU it was consuming. I would then find the running threads with the highest CPU numbers and investigate the query and see if we could improve it. Now in SS 2005, I get in the same situation and run the sp_who2 stored proc, and there is no smoking gun. All of the active threads are showing very little CPU usage, which I am very suspect of. What the heck happen to sp_who2? I looked at some of the other ways of looking at running processes (i.e... sys.sysprocesses) and they don't appear to be giving the information that I need.
I am very unhappy and I just want to roll back to SS 2000 and wait a couple of years before I upgrade to SS 2005.
One of our engineers here by mistake deleted some very important data without any begin trans block and we need the data back very badly.. We have not closed the session as of now.. The engineer was logged in as sa to the DB.
We have installed SQL Server 2000 Service Pack 4 recently and have had various issues with some of our Stored Procedures. (which we didn't have with SP 3)
We are looking at our options regarding rolling back to Service Pack 3.
Can this be done ? & If so, is there a method documented on how to do this ?
I have created a DTS Package that does the following: 1 Delete all data from table 1 (SQL Task) 2 Import Data from .csv file into table 1 (Data import) 3 insert records from table 1 into table 2 when they dont exist in table 2 (SQl Task)
This all works fine, but now i want a rollback function in step 1 and 2. So when Step 1 is finished and something goes wrong i want the deleted data back.
I'm performing a stored proc that has 4 inserts. I only want the inserts to complete as a batch. If one fails, I want to rollback the whole transaction. Does anyone know the syntax?? :)
I want to insure that each of my insert statements in a stored proc are rolled back if any of the inserts fail. I already have the below statement with error handling but is this correct? It seems to me that all the steps should be made part of an entire transaction so if one part fails then it all fails. Can someone help me w/ the syntax of this??
CREATE PROCEDURE Addrecords AS
--USERS INSERT INTO [Production].[dbo].[USERS]([LastName], [UserName], [EmailAddress], [Address1], [WorkPhone], [Company], [CompanyWebsite], [pword], [IsAdmin], [IsRestricted],[AdvertiserAccountID]) SELECT dbo.fn_ReplaceTags (convert (varchar (8000),Advertisername)), [AdvertiserEmail], [AdvertiserEmail],[AdvertiserAddress], [AdvertiserPhone], [AdvertiserCompany], [AdvertiserURL], [AccountNumber],'3',0, [AccountNumber] FROM production WHERE not exists (select * from users Where users.Username = temp.AdvertiserEmail) AND validAD=1 IF @@ERROR <> 0 BEGIN ROLLBACK TRAN RETURN END
--PROPERTY INSERT INTO [Production].[dbo].[Property]([ListDate],[CommunityName],[TowerName],[PhaseName],[Unit], [Address1], [City], [State], [Zip],[IsActive],[AdPrintId]) SELECT [FirstInsertDate],[PropertyBuilding],[PropertyStreetAddress],PropertyCity + ' ' + PropertyState + ' ' + PropertyZipCode as PhaseName,[PropertyUnitNumber],[PropertyStreetAddress],[PropertyCity], [PropertyState], [PropertyZipCode],'0',[AdPrintId] FROM [Production].[dbo].[Temp] WHERE AdvertiserEmail IS NOT NULL AND validAD=1 IF @@ERROR <> 0 BEGIN ROLLBACK TRAN RETURN END
Goal: set security on running specific stored procedures based upon user login and databse access
I have some DBA's who want to retain full control of databses / stored procedures as they now have but I want to restrict or rollback some of the changes that were implemented when the sql 2005 was set up. The sql 2005 EE is in a clustered system and uses Mixed Mode Authentication.
An example of what I want to restrict: The DBA's want to be able to view and kill processes for the different databases that are installed under their instance. The problem is other customer databases are also under the same instance.
Is their a way I can combine or have the stored procedure sp_lock only show the processes for the databases they have access to based upon their login? My concern is they will kill a process and affect the other customers.
I would appreciate a bit of advice here. There is a largish complaint here regarding a cutomer who has entered data in 1 of our online forms, but we suspect this was then overwritten by a cached form she also had open. Anyway to cut a long story short i need to roll back the database to a point in time.
Not something i have ever had to do.
The row where the id field is equal to 3352, and this would have been written to the database at 14:58:36 on 08-aug-2005. This was over written by the data in row with id 3380 at about 11am this morning, now is this is a live database on a webserver so i cant compromise its uptime as it get written to about 3 times a minute, so how should i go about this?
For some reason, my stored procedure which is kicked off by VB.NET is growing the log file for the database in question by gigs, very fast. Why is it doing this?
The code:
Sub Main()
objConn.Open() Dim cmdSql As New SqlClient.SqlCommand("IT_sss_Collector_DotNet", objConn) cmdSql.CommandType = CommandType.StoredProcedure cmdSql.CommandTimeout = 1000
Dim reader As SqlDataReader = cmdSql.ExecuteReader()
Dim ppa_rowcount As Integer ppa_rowcount = 0
If reader.HasRows() Then ' test to see if there is any data in the reader reader.Read() ppa_rowcount = Convert.ToInt16(reader("pcount")) End If reader.Close()
'Dim ppa_rowcount As Integer = Convert.ToInt16(cmdSql.ExecuteScalar())
MsgBox(ppa_rowcount)
If ppa_rowcount > 0 Then MsgBox("got here") RunsssReport() End If
the log is 29 gigs! this is a copy of a production database running on my test server. The production db log is only 15 gigs. How do I manage this and why it is growing at such a crazy porportion just from running this simple stored proc with a cursor???????
I have Full database backup upto previous day and transaction logfile of Today transaction. my database has crashed. I have restored previous day's Full backup. I have faced difficulty to restore today's transaction from today's transaction log. What are the steps to restore full database back and one day's transaction log file. Note: there is no differential database backup and transaction backup.
I am using sql2k5. I just wanted to throw an error from stored procedure with some message to C# to rollback my transaction. Here is how i wnated to do ( in sequence )
C# ===== Open a connection Begin the transaction Execute the command
In the Stored Proc =========== do multiple operations one by one if error stop processing further Throw the error C# ======== if exception rollback the transaction else commit the transaction
I have tried using raise error in stored proc but never thrown exception
Can any one let me know how to achieve this scenario??
I have a script contains multiple statements to update multiple tables. How can I make sure that either all statements get executed successfully or no changes apply to the tables (in case one or more errors occur)? I've been searching on Internet and it seems like I need to use Rollback and begin transaction.
I was trying to configure maintenance plan to take nightly full database backup and Log backup. I was trying to configure it like in attached file. Any links that i can follow and configure as in attached file.
I am confused about save transaction in the below scenario :
begin transaction save transaction t1 delete from #t1 save transaction t2 begin try delete from #t2
[Code] ....
If there is error after delete #t2 , transaction t1 is rolled back. But i am not able to understand why i am getting error in the statement 'rollback transaction t2' . I am getting error as 'Cannot roll back t2. No transaction or savepoint of that name was found.'. but save point t2 is mentioned in the code.
I am working with a large application and am trying to track down a bug. I believe an error that occurs in the stored procedure isbubbling back up to the application and is causing the application not to run. Don't ask why, but we do not have some of the sourcecode that was used to build the application, so I am not able to trace into the code. So basically I want to examine the stored procedure. If I run the stored procedure through Query Analyzer, I get the following error message: Msg 2758, Level 16, State 1, Procedure GetPortalSettings, Line 74RAISERROR could not locate entry for error 60002 in sysmessages. (1 row(s) affected) (1 row(s) affected) I don't know if the error message is sufficient enough to cause the application from not running? Does anyone know? If the RAISERROR occursmdiway through the stored procedure, does the stored procedure terminate execution? Also, Is there a way to trace into a stored procedure through Query Analyzer? -------------------------------------------As a side note, below is a small portion of my stored proc where the error is being raised: SELECT @PortalPermissionValue = isnull(max(PermissionValue),0)FROM Permission, PermissionType, #GroupsWHERE Permission.ResourceId = @PortalIdAND Permission.PartyId = #Groups.PartyIdAND Permission.PermissionTypeId = PermissionType.PermissionTypeId IF @PortalPermissionValue = 0BEGIN RAISERROR (60002, 16, 1) return -3END
At one seemingly inoccuous step in my CLR stored procedure, execution stops and the query times-out.
I've tried debugging the stored proc by stepping into it from within VS. When I do, I get to the code in question, but then simply get this message:
WARNING: Debugger was accessing T-SQL variables while managed code was not suspended. Waiting until the access is done to continue T-SQL execution. Continueing T-SQL execution.
And these messages appear to repeat indefinitely. I'm running SQL Server locally on my machine, but this also happens on out development SQL Server server.
The place in the code it appears to happen is when returning back results from a lower-level CLR stored proc called within the higher-level CLR stored proc -- when piping the result set, I suppose.
We have a problem with one of our MS SQL 2000 databases and some stored procedures.
I'm not sure exactly what the problem is, but these are the symptons....
The stored procedure runs without problems for a period of time. Abruptly, without warning it begins to time out when called from our web application.
Calling it through the query analyzer it runs within a second.
Forcing the stored procedure to recompile allows the web application to start calling it again without it timing out.
We have a DTS package that runs over night and imports a number of records (not sure on the exact numbers, but definately enough to make a difference to indexes) so this could be part of the problem although when I force a recompile I do not do any update stats or anything else.
I wrote a test script to call the stored procedure when it was timing out to ensure it wasn't a web application problem and the procedure continued to time out until the forced recompile. So I don't think the problem is there.
The stored procedure returns multiple results sets and when it starts timing out it is while it is returning the second results sets.
The code for the second results set is...
Select avg(round(p.PricingValue, 5)) as Average, stdev(round(p.PricingValue, 5)) as StdDev, min(p.CaptureDate) as FromDate, max(p.CaptureDate) as ToDate From Pricing p Inner Join Security s On p.SecurityID = s.SecurityID Left Outer Join Issuer i On s.IssuerID = i.IssuerID WHERE p.PricingTypeID = @PricingType And p.TenorTypeID = @TenorType And p.CaptureDate Between @DateFrom And @DateTo AND p.SecurityID IN ( SELECT SecurityId FROM UserResult ur WHERE ur.UserResultSelected = 1 AND ur.UserID = @userID )
Does anyone have any idea what might be going on here?
Is there a formula for calculating how expensive a transaction will be in terms of disk space used before its run. I dont want it accurate to the MB, but rough enough so I can determine how much additional space to assign to a transaction log or SAN volume.
Currently we're reindexing ~25billion rows, nothing too wide, say 12 columns consisting of 1 varchar(50) and the rest ints, bits and money.Roughly speaking if I reindex the clustered index on an int indetity, (with sort_in_tempdb) how would I calculate the the disk space used?
I have a database that is part of AlwaysOn that is filling up the transaction log drive even though I have a daily full backup and transaction logs set for every 2 hours. The backups are going from both the primary and secondary replica backuping up to the shared disk and I have the backup preferences set to the primary.
When I try to shrink the log I get 'The transaction log for database 'DB' is full due to 'LOG_BACKUP''. I have to manually backup the trans log and then shrink, why the maintenance plan backups aren't doing this even though they are "working".
I've recently started working with a public sector organisation who have 4 clustered sql instances that has 80% of it's db mirrored.
Looking at the transaction log - it seems that a transaction log backup is a good idea as the log is 4x larger than the data file.But I'm not allowed access to the physical server to check onto which drive I can create the trn. No RDP, no vmware - let's be honest I'm not even allowed to launch cmd line Also the Server Manager informs me "We will need to carefully look at database backups if you guys want to start doing these backups on box, as that will break our off box backup routine (it will screw the transaction chain)."
I don't understand how backing up the transaction log could break the "transaction chain"?
I'm investigating a poorly performing procedure that I have never seen before. The procedure sets the transaction isolation level, and I suspect it might be doing so incorrectly, but I can't be sure. I'm pasting a bastardized version of the proc below, with all the names changed and the SQL mucked up enough to get through the corporate web filters.
The transaction isolation level is set, but there is no explicit transaction. Am I right that there are two implicit transactions in this procedure and each of them uses snapshot isolation?
SET NOCOUNT ON; SET TRANSACTION ISOLATION LEVEL SNAPSHOT; DECLARE @l_some_type varchar(20), @some_type_code varchar(3), @error int, @error_msg varchar(50);
