Transaction Log Backup fails Exclusive access could not be obtained because the database is in use
-
I have an AM and a PM TransactionLog Backup.
The AM runs fine the PM fails
I get the following error.
Date07/15/2013 03:00:00 PM
LogJob History (Backup_DataWarehouse_TransactionLog_PM_Test.Subplan_1)
Step ID1
ServerMyServer
Job NameBackup_DataWarehouse_TransactionLog_PM_Test.Subplan_1
Step NameSubplan_1
Duration00:00:22
Sql Severity0
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
Executed as user: MyDomain\SQLServerAgent. Microsoft (R) SQL Server Execute Package Utility Version 10.50.4000.0 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 03:00:00 PM Progress: 2013-07-15 15:00:00.77 Source: {247A3977-1976-4E4B-8B35-B14F86B49B5F} Executing query "DECLARE @GUID UNIQUEIDENTIFIER EXECUTE msdb..sp...".: 100% complete End Progress Error: 2013-07-15 15:00:21.01 Code: 0xC002F210 Source: Back Up Database (Transaction Log) Execute SQL Task Description: Executing the query "BACKUP LOG [DataWarehouse] TO DISK = N'D:\Backup\Tran..." failed with the following error: "Exclusive access could not be obtained because the database is in use. BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 03:00:00 PM Finished: 03:00:21 PM Elapsed: 20.811 seconds. The package execution failed. The step failed.
Any ideas?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
is the database put into single user mode or is the option to backup the tail of the log selected? both would cause this error
---------------------------------------------------------------------
-
What is the exact backup command that's been run?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GilaMonster (7/15/2013)
What is the exact backup command that's been run?BACKUP LOG DataWarehouse TO DISK e= N'D:\Backup\TransactionLog\Data\DataWarehouse_backup_2013_07_15_181847_8601173.trn' WITH NOFORMAT, NOINIT, NAME = N'DataWarehouse_backup_2013_07_15_181847_8601173', SKIP, REWIND, NOUNLOAD, NORECOVERY , COMPRESSION, STATS = 10
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
BACKUP LOG [DataWarehouse] TO DISK = N'D:\Backup\TransactionLog\DataWarehouse\DataWarehouse_backup_2013_07_15_182558_8596801.trn' WITH NOFORMAT, NOINIT, NAME = N'DataWarehouse_backup_2013_07_15_182558_8596801', SKIP, REWIND, NOUNLOAD, NORECOVERY , COMPRESSION, STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'DataWarehouse' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'DataWarehouse' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''DataWarehouse'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N'D:\Backup\TransactionLog\DataWarehouse\DataWarehouse_backup_2013_07_15_182558_8596801.trn' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
Results In the following:
Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
Msg 3201, Level 16, State 2, Line 4
Cannot open backup device 'D:\Backup\TransactionLog\DataWarehouse\DataWarehouse_backup_2013_07_15_182558_8596801.trn'. Operating system error 2(The system cannot find the file specified.).
Msg 3013, Level 16, State 1, Line 4
VERIFY DATABASE is terminating abnormally.
000
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
Welsh Corgi (7/15/2013)
GilaMonster (7/15/2013)
What is the exact backup command that's been run?BACKUP LOG DataWarehouse TO DISK e= N'D:\Backup\TransactionLog\Data\DataWarehouse_backup_2013_07_15_181847_8601173.trn'
WITH NOFORMAT, NOINIT,
NAME = N'DataWarehouse_backup_2013_07_15_181847_8601173',
SKIP, REWIND, NOUNLOAD, NORECOVERY, COMPRESSION,
STATS = 10
Why are you trying to take your datawarehouse database into a restoring state in which no one can use it? Do you plan to restore from a full backup every afternoon or something?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GilaMonster (7/15/2013)
Welsh Corgi (7/15/2013)
GilaMonster (7/15/2013)
What is the exact backup command that's been run?BACKUP LOG DataWarehouse TO DISK e= N'D:\Backup\TransactionLog\Data\DataWarehouse_backup_2013_07_15_181847_8601173.trn'
WITH NOFORMAT, NOINIT,
NAME = N'DataWarehouse_backup_2013_07_15_181847_8601173',
SKIP, REWIND, NOUNLOAD, NORECOVERY, COMPRESSION,
STATS = 10
Why are you trying to take your datawarehouse database into a restoring state in which no one can use it? Do you plan to restore from a full backup every afternoon or something?
Yes. It is a backup that can be used for PIT recovery.
Am I missing something?
I modified the AM job to extend into the evening.
So I can perform a PIT recovery.
Thanks.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
Welsh Corgi (7/15/2013)
Yes. It is a backup that can be used for PIT recovery.Am I missing something?
Yes, that you have the NORECOVERY option on the log backup. i.e. back up the log, take the database into a restoring state so that it cannot be used any longer. Usually used on the last log backup taken before starting a restore operation.
Is that really what you want the afternoon log backup to do?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Welsh Corgi (7/15/2013)
Am I missing something?http://msdn.microsoft.com/en-us/library/ms179314.aspx
Use NORECOVERY whenever you intend to continue with a restore operation on the database. NORECOVERY takes the database into the restoring state. This guarantees that the database does not change after the tail-log backup.
The log is truncated unless the NO_TRUNCATE option or COPY_ONLY option is also specified.
-
Thank you for your advice.
I may eventually apply the restore to refresh the Development Environment.
But I'm not trying to restore at this point.
Thanks.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
Welsh Corgi (7/16/2013)
I may eventually apply the restore to refresh the Development Environment.But I'm not trying to restore at this point.
Err.. I think you're missing the point.
WITH NORECOVERY is used on the last log backup when you are just about to restore OVER the database (perhaps because its corrupt or damaged) and you want a last log backup and to ensure that no more transactions can be run against that database.
It's of no relevance in a backup strategy for point in time restore, it's of no relevance when considering restoring a backup to a new database.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
I use the WITH RECOVERY and WITH NORECOVERY quite a bit.
Recently I migrated just under 20 Databases from SQL Server 2005 and applied Full, differential and in some case Transaction Log Backups.
I created the backups via the maintenance wizard the same exact was. The only thing that was different was the schedule.
As far as the option to backup the tail of the transaction log, do you use that for all transaction log backups or for the last transaction log backups.
You can backup the transaction log of a corrupt database?:unsure:
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
Welsh Corgi (7/18/2013)
As far as the option to backup the tail of the transaction log, do you use that for all transaction log backups or for the last transaction log backups.That setting is WITH NORECOVERY, the option that takes the database offline after the log backup.
You can backup the transaction log of a corrupt database?:unsure:
Yes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply