Mirroring And Rolling Back Transaction Log
Mar 17, 2008
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?
Thanks.
View 1 Replies
ADVERTISEMENT
Mar 19, 2007
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
View 1 Replies
View Related
Jun 16, 2007
Hi,
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.
Thanks
View 3 Replies
View Related
Apr 9, 2008
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.
what do i have wrong ?
View 5 Replies
View Related
Feb 3, 2004
I have a Development database and I want to roll it back to Monday morning. I backed up the database and used the command:
RESTORE DATABASE ITTEST
FROM ITTEST20040203
WITH NORECOVERY
GO
RESTORE LOG MyNwind
FROM ITTEST20040203
WITH RECOVERY, STOPAT = 'FEBRUARY 2, 2004 09:00 AM'
GO
The transaction logs have never been truncated.
But it does not seem to have worked. It this the best way to do a roll back the database or have I missed out something.
thanks.
View 2 Replies
View Related
Jun 10, 2007
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
Button Click 2:
trans.Rollback()
View 4 Replies
View Related
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?
View 4 Replies
View Related
Sep 22, 2006
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.
Dave Brown
View 1 Replies
View Related
Mar 27, 2001
If I issue -
update x set y = 'z'
is there a way to undone/roll-back what was done in SQL 7 ?
Thanks very much.
-FB
View 2 Replies
View Related
Sep 8, 2004
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.
Can somebody help me?
Thanks in advance.
View 1 Replies
View Related
Jun 15, 2007
Is database mirroring compatible between SQL Server 2005 (Standard Edition) SP1 and SP2? I currently run SP1, and will shortly be introducing a failover partner. I'd like to have the failover partner installed as SP2, start the mirroring, manually failover, then upgrade the existing server to SP2 as well.
Is this a supported upgrade method, or will I need to upgrade the existing server to SP2 before setting up database mirroring? The SP2 installation instructions and readme file don't appear to mention mirroring at all (at least not that I can find).
Many thanks for any assistance.
View 4 Replies
View Related
May 14, 2015
New to Database Mirroring and I have a question about the Principal database server. I have a Database Mirroring setup configured for High-safety with automatic fail over mode using a witness.
When a fail over occurs because of a lost of communication between the principal and mirror, the mirror server takes on the roll of Principal. When communication is returned to the Principal server, at some point does the database that was the previous Principal database automatically go back to being the Principal server?
View 2 Replies
View Related
Feb 19, 2004
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.
Can someone please help soon on urgent basis...
View 6 Replies
View Related
Feb 16, 2006
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?? :)
View 4 Replies
View Related
Mar 3, 2006
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
View 2 Replies
View Related
Feb 15, 2006
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 ?
Any advice would be appreciated.
View 3 Replies
View Related
Apr 4, 2013
I have a stored proc that is executed from within another stored proc, both of these procs run transactions in them that update data.
Now if either of these 2 SPs fail i want to be able to roll back the transactions that have occured so that the data doesn't change.
View 5 Replies
View Related
Mar 29, 2007
Don't know if this is possible or not:
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.
Thanks for any info or pointers.
carl
View 1 Replies
View Related
Aug 9, 2005
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?
Anyway, what do you think?
Cheers
View 2 Replies
View Related
Oct 5, 2007
I have a quick question.
I need to execute some stored procedures in certain steps... all performing some inserts/updates
What i need is, a mechanism, by which i can roll back to the previous state if i encounter an exception (either in the app or SP)
so, if the my first two SP execute fine, perform their functionality like insert/update, and the third one fails...
how can i roll back to the initial state in ASP.NET.
would appreciate any info.
or redirection to the the location where i can look up some info on that.
AJ
View 1 Replies
View Related
Jun 22, 2015
I have SQL Server 2008 R2 database mirroring setup with witness. Below database connection string is used by the application
Â
jdbc:sqlserver://<Server_A>MSSQLSERVER;databaseName=<Mirrored_DB>;port=1433;failoverPartner=<Server_B>MSSQLSERVER
Â
During some maintenance activity at the OS side , I tried to manually failover database from Server_A to Server_B. It took long time for failover to Server_B. While failing over the Mirrored_DB database goes in In Recovery status and application is not able to connect to database. I waited for long time more than 40 mins for database to come online on Server_B but it did not work.So I have recovered database on Server_B using RESTORE DATABASE Mirrored_DB WITH RECOVERY.
Â
After database was recovered on Server_B application was not able to connect to database with error as "The database <Mirrored_DB> on server Server_BMSSQLSERVER is not configured for database mirroring".
Â
My Question is
Why manual failover did not get back database on secondary server upwhy the original  connection string below is not able to connect to the database while database is online on Server_B  but without mirroring
 jdbc:sqlserver://<Server_A>MSSQLSERVER;databaseName=<Mirrored_DB>;port=1433;failoverPartner=<Server_B>MSSQLSERVER
View 3 Replies
View Related
Nov 23, 2006
How sql server will mirror big transaction ?
Example
inserting 10000000 rows in 1 transaction (columns big ,text data ...)
will it transfer data in multiple packets (size, location of temp file) to mirror ?
will it transfer data in 1 packet (size , location of temp file ) to mirror ?
Can you control transfer (size , location of temp file) ?
Thanks
Alex
View 1 Replies
View Related
Feb 8, 2001
Hi all,
I'm getting the following error message in NT event view:
Error: 9002, Severity: 17, State: 2
The log file for database 'db_sys' is full. Back up the transaction log for the database to free up some log space.
But I don't know how to back up the transaction log for the database.
Do I need TRUNCTE LOG (in E.M) to free up some log space?
I only know back up all the database using ALL TASKS -> BACKUP DATABASE in E.M. Please help me. Thanks in advance.
TH
View 1 Replies
View Related
Oct 7, 2015
Today, I became an involuntary DBA until my company finds someone else. We have an SQL R2 DB that is mirrored that failed dur to the transaction log growing and filling up the disk. As a band aid remedy, I was able to add 20GB to the disk (it is a VM) and then I backed up the Transaction log and did select log_reuse_wait_desc from sys.databases where name = 'mydb' and the result showed that the backup was successful (NOTHING, I think was the result).
After this I was able to resume the mirror and everything has been running fine. What I need to do now is to shrink the Transaction log so that if it starts to grow again, I will get an alert and can avoid today's issue. From what I have read, I can just use DBCC SHRINKFILE [logname] after I do a backup and the changes will be reflected on the mirror as well. I have shrunk the T-log in non-mirrored servers before, just don't know if there are any key differences.
View 6 Replies
View Related
Mar 7, 2008
Hi,
I'm trying to add a new transaction log file to a database with mirroring (high safety, automatic failover).
When I add the new transaction log file via SQL management studio, the mirroring status becomes suspended. When I try to restart mirroring, it attempts to re-synchronize and then goes back to the suspended state. Only after I delete the new transaction log file can I sucessfully restart mirroring.
Can you add a transaction log file to a mirrored database? Any ideas would be appreciated.
Also, I'm not sure if this matters, but I am trying this in my development environment and all 3 instances (principal, mirror, witness) are on the same server. It's a long shot, but I thought maybe both the mirror and principal are trying to create the file at the same location on disk.
Thanks,
Don
View 3 Replies
View Related
Feb 28, 2008
Hello, I do not know how to implement transaction roll back in asp.net application. I am using SqlHelper class to communicate with my sql db.Thanks, junior
View 1 Replies
View Related
Apr 3, 2015
The following transaction runs, then reverts back after 10 mins.
begin Tran [UpdateSundays]
update ScanAssetInformation with (rowlock)
set BusinessDate = dateadd(day,-2,BusinessDate)
where datepart(weekday,BusinessDate) = 1
commit tran [UpdateSundays]
begin Tran [UpdateSaturdays]
update ScanAssetInformation with (rowlock)
set BusinessDate = dateadd(day,-1,BusinessDate)
where datepart(weekday,BusinessDate) = 7
commit tran [UpdateSaturdays]
View 2 Replies
View Related
Aug 15, 2007
When using the maintenance plan to backup t logs, should I enclude the system database logs such as master, model, etc...?
Thanks!
View 1 Replies
View Related
Feb 1, 2001
hi, I would like to know the correct reaction for a crash in both senarios.
First senario,
I made a full back up at 6 am , then scheduled sql server to make transaction log back up every 2 hours (8,10,12,2 pm,4,6,8) . If I have a crash at 12:30. How would I resotre the data in the first senario....Can I restore the full back up done at 6 am then restore the last transaction log backup ( which is 12 Noon ) . I am not sure If I need to resotre the whole tran from 6 am till the time it was crashed.
Second senario,
I made a full back up at 6 am, then scheduled sql server to make Incremental backup every 2 hours (8,10,12,2 pm,4,6,8) . If I have a crash at 3:00 pm. How would I resotre the data in the second senario. ....Do I restore the full backup at 6 am then restore each incremental backup backwords ( 2,12,10,8)
AS you can see, I am not sure how to deal with this issue, I do appreciate your feedback.
Best regards
Ahmed
View 1 Replies
View Related
Feb 1, 2001
hi, I would like to know the correct reaction for a crash in both senarios.
First senario,
I made a full back up at 6 am , then scheduled sql server to make transaction log back up every 2 hours (8,10,12,2 pm,4,6,8) . If I have a crash at 12:30. How would I resotre the data in the first senario....Can I restore the full back up done at 6 am then restore the last transaction log backup ( which is 12 Noon ) . I am not sure If I need to resotre the whole tran from 6 am till the time it was crashed.
Second senario,
I made a full back up at 6 am, then scheduled sql server to make Incremental backup every 2 hours (8,10,12,2 pm,4,6,8) . If I have a crash at 3:00 pm. How would I resotre the data in the second senario. ....Do I restore the full backup at 6 am then restore each incremental backup backwords ( 2,12,10,8)
AS you can see, I am not sure how to deal with this issue, I do appreciate your feedback.
Best regards
Ahmed
View 2 Replies
View Related
Mar 4, 2008
Hi,
Is there a way to write to a log-table inside a transaction which is rolled back without rollback of this log-entry.
thanks in advance
Raimund
View 9 Replies
View Related
Aug 18, 2015
We have a SQL server 2012 database with size 200mb and Transaction Log has gone up to 34GB.
We have SQL server Mirror is enabled and principal database is running with Full Recovery Model.
How can I truncate/Shrink Log files? Will it impact on existing mirroring setup? I am very much new to SQL server.
View 17 Replies
View Related
May 4, 2015
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.
View 3 Replies
View Related
