February 15, 2012 at 5:25 am
We've been chasing this issue for a couple weeks now and are ready to call Microsoft.. Our last attempt to fix was to update the MPIO drivers because we were seeing "VDS fails to claim disk" errors in the event viewer. The drivers were updated and it took care of the VDS errors but the backup issue still remains...
Our stored procedure will only backup 40 - 50 databases (out of 125) then stop with no errors logged. We've tried scheduling for different times, re-written the stored procedure and created an SSIS package with the same results.
If I script each database individually for backup, they all complete if run at once -
BACKUP DATABASE [DB Name] TO DISK = N'J:\SQL14_BACKUP\mssql\backup\DC01SQL14_DB Name_.BAK' WITH NOFORMAT, INIT, NAME = N'SharedServices_Content-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [DB Name] TO DISK = N'J:\SQL14_BACKUP\mssql\backup\DC01SQL14_DB Name_.BAK' WITH NOFORMAT, INIT, NAME = N'SharedServices_DB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [DB Name] TO DISK = N'J:\SQL14_BACKUP\mssql\backup\DC01SQL14_DB Name_.BAK' WITH NOFORMAT, INIT, NAME = N'SharedServices_Search_DB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
If using the stored procedure below(scheduled or run manually), only 40 - 50 will backup with no errors -
(This stored proc is used on all other databases with no issues. This is the only DB giving us grief.)
declare @name1 varchar(150), @name2 varchar(150), @dbname varchar(150)
declare cursor_db cursor for
select sdb.name from master.dbo.sysdatabases sdb
join sys.databases db on sdb.dbid = db.database_id
where (status & 512) = 0
AND sdb.name not in ('master','msdb','model','tempdb')
AND snapshot_isolation_state = 0
open cursor_db
fetch next from cursor_db into @dbname
while @@FETCH_STATUS=0
begin
select @name1='J:\SQL14_BACKUP\mssql\backup\' + 'DC01SQL14' + '_' + @dbname + '_' + '.BAK', @name2 = @dbname + '_C'
BACKUP DATABASE @dbname TO DISK = @name1 WITH NOFORMAT, INIT, SKIP, NOREWIND, NOUNLOAD, STATS = 10, NAME = @name2
fetch next from cursor_db into @dbname
end
close cursor_db
deallocate cursor_db
~SQL Server 2005 - 9.00.5000.00 (X64)
Any suggestions would be greatly appreciated.
Thanks
February 15, 2012 at 6:09 am
Zubius (2/15/2012)
If using the stored procedure below(scheduled or run manually), only 40 - 50 will backup with no errors -
(This stored proc is used on all other databases with no issues. This is the only DB giving us grief.)
declare @name1 varchar(150), @name2 varchar(150), @dbname varchar(150)
declare cursor_db cursor for
select sdb.name from master.dbo.sysdatabases sdb
join sys.databases db on sdb.dbid = db.database_id
where (status & 512) = 0
AND sdb.name not in ('master','msdb','model','tempdb')
AND snapshot_isolation_state = 0
open cursor_db
fetch next from cursor_db into @dbname
while @@FETCH_STATUS=0
begin
select @name1='J:\SQL14_BACKUP\mssql\backup\' + 'DC01SQL14' + '_' + @dbname + '_' + '.BAK', @name2 = @dbname + '_C'
BACKUP DATABASE @dbname TO DISK = @name1 WITH NOFORMAT, INIT, SKIP, NOREWIND, NOUNLOAD, STATS = 10, NAME = @name2
fetch next from cursor_db into @dbname
end
close cursor_db
deallocate cursor_db
Thanks
Have you looked at the error log ?
If you don't get any error logged inside the error log I would suggest to:
1. First alter the procedure so that it prints out all the backup commands it will be executing & ensure it is generating the valid command for every database on the instance.
2. Also alter it to contain "[" & "]" beore & after the name of the database (@dbname variable in your case). Because if there is any database which contains space or "-" (or any other invalid charecter according to the rules of identifier) in its name it will be an error.
February 15, 2012 at 6:19 am
This issue is most liley related to the cursor against sysdatabases. Change the cursor to a static cursor and it should resolve the problem.(CURSOR STATIC FOR SELECT). Also use the QUOTENAME() function around the database variable so that it will braket the database name.
February 15, 2012 at 10:01 am
If you really must use a cursor then this would be a little more appropriate
declare cursor_db INSENSITIVE cursor for
select sdb.name from sys.databases sdb
where state = 0 and snapshot_isolation_state = 0
and database_id > 4
Also modify the backup command as follows
BACKUP DATABASE quotename(@dbname) TO DISK = @name1 WITH
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 15, 2012 at 12:20 pm
Thanks for all the suggestions.. I'll make a couple modifications, rerun the job and post the results later.
February 16, 2012 at 8:09 am
Richard Moore-400646 (2/15/2012)
This issue is most liley related to the cursor against sysdatabases. Change the cursor to a static cursor and it should resolve the problem.(CURSOR STATIC FOR SELECT).
This took care of the issue, Thank you Richard (and all others for the suggestions)!
It looks like the cursor defaults to Dynamic when not declared so the data values, order, and membership of the rows can change on each fetch. Essentially it loses it's place and thinks it has completed therefore no errors were logged.
I declared the cursor as static and ran the backup 3 times with no issues, however, this does not explain why this is the only instance having the issue.
February 16, 2012 at 8:26 am
Declaring an Insensitive cursor should also work
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 16, 2012 at 8:36 am
Thanks Perry. I'm actually executing the stored proc with INSENSITIVE cursor now...
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply