Backups not completing

  • Zubius

    SSC Veteran

    Points: 271

    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

  • sumitagarwal781

    Default port

    Points: 1493

    Use try-catch block in your stored procedure to capture the error message.

  • Divine Flame

    SSCoach

    Points: 15941

    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.


    Sujeet Singh

  • Richard Moore-400646

    SSCrazy

    Points: 2384

    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.

  • Perry Whittle

    SSC Guru

    Points: 233859

    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" 😉

  • Zubius

    SSC Veteran

    Points: 271

    Thanks for all the suggestions.. I'll make a couple modifications, rerun the job and post the results later.

  • Zubius

    SSC Veteran

    Points: 271

    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.

  • Perry Whittle

    SSC Guru

    Points: 233859

    Declaring an Insensitive cursor should also work

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Zubius

    SSC Veteran

    Points: 271

    Thanks Perry. I'm actually executing the stored proc with INSENSITIVE cursor now...

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply