Backup Job Sometimes Stops at msdb

Hi,
We have been using the same stored procedures for backing up our databases
for years with no issues. We now have a puzzling issue on SQL Server 2005
(sp1) and msdb. The stored procedure uses a cursor to get a list of database
s
in the group (alphabetically). The cursor always gets all of the databases
but sometimes it doesn't back all of them up (like it thinks that it's done)
.
It always seems to stop at msdb. It's not because msdb is the last in the
list, I created a database called mz and it still only backed up master and
model (the only other two in this group). I used @.@.cursor_rows so I know tha
t
all for database names were in the cursor. This doesn't ALWAYS happen -
sometimes it backs up all of the databases just fine. This seems to ONLY
happen on the 64-bit machines. We're using litespeed and I'll try to
reproduce the issue using native commands.
DECLARE @.db VARCHAR(100) -- database name
DECLARE @.NumBakFiles INT -- number of backup files to be created
DECLARE @.DestServer VARCHAR(50) -- backup destination server
DECLARE @.DestShare VARCHAR(50) -- backup destination file share
DECLARE @.dbDestDisk VARCHAR(300) -- database backup destination disk
DECLARE cDB CURSOR FOR
SELECT [name]
FROM master.sys.databases
--WHERE [name] in ('msdb')
WHERE UPPER([name]) NOT IN('TEMPDB', 'NORTHWIND', 'PUBS','SQLPROFILE')
-- AND UPPER([name]) < 'H' -- AthruG
AND [name] > 'h%' -- HthruM
AND [name] < 'n%' -- HthruM
-- AND [name] >= 'n%' -- NthruS
-- AND [name] <= 't%' -- NthruS
-- AND [name] <= 't%' -- TthruZ
ORDER BY
[name],
create_date
OPEN cDB
print @.@.cursor_rows
FETCH NEXT
FROM cDB
INTO @.db
WHILE (@.@.FETCH_STATUS=0)
BEGIN
PRINT @.db
SET @.NumBakFiles = 1
SET @.DestServer = '\\SQLDUMP'
--SELECT @.DestServer = 'D:\SQLDATADUMP'
SET @.DestShare = 'SQLDUMPSHARE'
SET @.dbDestDisk = @.DestServer + @.DestShare + REPLACE(@.@.SERVERNAME, '',
'_') + '\SQLDATADUMP' + @.db + '_FullDB_' +
REPLACE(REPLACE(CONVERT(VARCHAR(17), GETDATE(), 113), ':', ''), ' ','') +
'.bak'
--SELECT @.dbDestDisk = @.DestServer + '' + @.db + '_FullDB_' +
REPLACE(REPLACE(CONVERT(VARCHAR(17), GETDATE(), 113), ':', ''), ' ','') +
'.bak'
-- Backup Database File
EXEC master.dbo.xp_backup_database @.database = @.db, @.filename =
@.dbDestDisk, @.init=1
FETCH NEXT
FROM cDB
INTO @.db
END --WHILE
CLOSE cDB
DEALLOCATE cDB
GOI was able to reproduce the issue using native sql server commands:
4
master
Processed 360 pages for database 'master', file 'master' on file 10.
Processed 2 pages for database 'master', file 'mastlog' on file 10.
BACKUP DATABASE successfully processed 362 pages in 0.186 seconds (15.921
MB/sec).
model
Processed 160 pages for database 'model', file 'modeldev' on file 10.
Processed 2 pages for database 'model', file 'modellog' on file 10.
BACKUP DATABASE successfully processed 162 pages in 0.081 seconds (16.333
MB/sec).
"Michelle" wrote:

> Hi,
> We have been using the same stored procedures for backing up our databases
> for years with no issues. We now have a puzzling issue on SQL Server 2005
> (sp1) and msdb. The stored procedure uses a cursor to get a list of databa
ses
> in the group (alphabetically). The cursor always gets all of the databases
> but sometimes it doesn't back all of them up (like it thinks that it's don
e).
> It always seems to stop at msdb. It's not because msdb is the last in the
> list, I created a database called mz and it still only backed up master an
d
> model (the only other two in this group). I used @.@.cursor_rows so I know t
hat
> all for database names were in the cursor. This doesn't ALWAYS happen -
> sometimes it backs up all of the databases just fine. This seems to ONLY
> happen on the 64-bit machines. We're using litespeed and I'll try to
> reproduce the issue using native commands.
> DECLARE @.db VARCHAR(100) -- database name
> DECLARE @.NumBakFiles INT -- number of backup files to be created
> DECLARE @.DestServer VARCHAR(50) -- backup destination server
> DECLARE @.DestShare VARCHAR(50) -- backup destination file share
> DECLARE @.dbDestDisk VARCHAR(300) -- database backup destination disk
>
> DECLARE cDB CURSOR FOR
> SELECT [name]
> FROM master.sys.databases
> --WHERE [name] in ('msdb')
> WHERE UPPER([name]) NOT IN('TEMPDB', 'NORTHWIND', 'PUBS','SQLPROFI
LE')
> -- AND UPPER([name]) < 'H' -- AthruG
> AND [name] > 'h%' -- HthruM
> AND [name] < 'n%' -- HthruM
> -- AND [name] >= 'n%' -- NthruS
> -- AND [name] <= 't%' -- NthruS
> -- AND [name] <= 't%' -- TthruZ
> ORDER BY
> [name],
> create_date
> OPEN cDB
> print @.@.cursor_rows
> FETCH NEXT
> FROM cDB
> INTO @.db
> WHILE (@.@.FETCH_STATUS=0)
> BEGIN
> PRINT @.db
> SET @.NumBakFiles = 1
> SET @.DestServer = '\\SQLDUMP'
> --SELECT @.DestServer = 'D:\SQLDATADUMP'
> SET @.DestShare = 'SQLDUMPSHARE'
> SET @.dbDestDisk = @.DestServer + @.DestShare + REPLACE(@.@.SERVERNAME, ''
,
> '_') + '\SQLDATADUMP' + @.db + '_FullDB_' +
> REPLACE(REPLACE(CONVERT(VARCHAR(17), GETDATE(), 113), ':', ''), ' ','') +
> '.bak'
> --SELECT @.dbDestDisk = @.DestServer + '' + @.db + '_FullDB_' +
> REPLACE(REPLACE(CONVERT(VARCHAR(17), GETDATE(), 113), ':', ''), ' ','') +
> '.bak'
> -- Backup Database File
> EXEC master.dbo.xp_backup_database @.database = @.db, @.filename =
> @.dbDestDisk, @.init=1
> FETCH NEXT
> FROM cDB
> INTO @.db
> END --WHILE
> CLOSE cDB
> DEALLOCATE cDB
> GO
>|||A couple of comments or suggestions here. One is to print out the results
of the dynamic backup statements and run the one for MSDB to see what you
get. Have you tried changing the order by to see if it always stops on the
same db? Another is that i would recommend creating a table that holds the
meta data for the backups that you want to issue. You can have a row for
each db with a column for Full backups that can denote if that backup should
be done or not instead of the WHERE clause you have now.
Andrew J. Kelly SQL MVP
"Michelle" <Michelle@.discussions.microsoft.com> wrote in message
news:922568F2-A7C0-48F0-A9DE-03FCB49D3CA3@.microsoft.com...
> Hi,
> We have been using the same stored procedures for backing up our databases
> for years with no issues. We now have a puzzling issue on SQL Server 2005
> (sp1) and msdb. The stored procedure uses a cursor to get a list of
> databases
> in the group (alphabetically). The cursor always gets all of the databases
> but sometimes it doesn't back all of them up (like it thinks that it's
> done).
> It always seems to stop at msdb. It's not because msdb is the last in the
> list, I created a database called mz and it still only backed up master
> and
> model (the only other two in this group). I used @.@.cursor_rows so I know
> that
> all for database names were in the cursor. This doesn't ALWAYS happen -
> sometimes it backs up all of the databases just fine. This seems to ONLY
> happen on the 64-bit machines. We're using litespeed and I'll try to
> reproduce the issue using native commands.
> DECLARE @.db VARCHAR(100) -- database name
> DECLARE @.NumBakFiles INT -- number of backup files to be created
> DECLARE @.DestServer VARCHAR(50) -- backup destination server
> DECLARE @.DestShare VARCHAR(50) -- backup destination file share
> DECLARE @.dbDestDisk VARCHAR(300) -- database backup destination disk
>
> DECLARE cDB CURSOR FOR
> SELECT [name]
> FROM master.sys.databases
> --WHERE [name] in ('msdb')
> WHERE UPPER([name]) NOT IN('TEMPDB', 'NORTHWIND', 'PUBS','SQLPROFIL
E')
> -- AND UPPER([name]) < 'H' -- AthruG
> AND [name] > 'h%' -- HthruM
> AND [name] < 'n%' -- HthruM
> -- AND [name] >= 'n%' -- NthruS
> -- AND [name] <= 't%' -- NthruS
> -- AND [name] <= 't%' -- TthruZ
> ORDER BY
> [name],
> create_date
> OPEN cDB
> print @.@.cursor_rows
> FETCH NEXT
> FROM cDB
> INTO @.db
> WHILE (@.@.FETCH_STATUS=0)
> BEGIN
> PRINT @.db
> SET @.NumBakFiles = 1
> SET @.DestServer = '\\SQLDUMP'
> --SELECT @.DestServer = 'D:\SQLDATADUMP'
> SET @.DestShare = 'SQLDUMPSHARE'
> SET @.dbDestDisk = @.DestServer + @.DestShare + REPLACE(@.@.SERVERNAME, '',
> '_') + '\SQLDATADUMP' + @.db + '_FullDB_' +
> REPLACE(REPLACE(CONVERT(VARCHAR(17), GETDATE(), 113), ':', ''), ' ','') +
> '.bak'
> --SELECT @.dbDestDisk = @.DestServer + '' + @.db + '_FullDB_' +
> REPLACE(REPLACE(CONVERT(VARCHAR(17), GETDATE(), 113), ':', ''), ' ','') +
> '.bak'
> -- Backup Database File
> EXEC master.dbo.xp_backup_database @.database = @.db, @.filename =
> @.dbDestDisk, @.init=1
> FETCH NEXT
> FROM cDB
> INTO @.db
> END --WHILE
> CLOSE cDB
> DEALLOCATE cDB
> GO
>|||I reversed the order and was able to reproduce the issue. After several
successful runs, it ran and ONLY backed up the mz database. It's not even
building a backup statement for msdb. It's like it thinks that msdb isn't in
the cursor and quits when it finishes the database just before msdb. We've
only seen this with 2005 64-bit (Enterprise AND Standard Edition).
I appreciate the table suggestion. We've considered doing this for a lot of
admin stuff but just can't get resources allocated to that right now. We hav
e
about 50 sql servers.
"Andrew J. Kelly" wrote:

> A couple of comments or suggestions here. One is to print out the results
> of the dynamic backup statements and run the one for MSDB to see what you
> get. Have you tried changing the order by to see if it always stops on th
e
> same db? Another is that i would recommend creating a table that holds th
e
> meta data for the backups that you want to issue. You can have a row for
> each db with a column for Full backups that can denote if that backup shou
ld
> be done or not instead of the WHERE clause you have now.
> --
> Andrew J. Kelly SQL MVP
> "Michelle" <Michelle@.discussions.microsoft.com> wrote in message
> news:922568F2-A7C0-48F0-A9DE-03FCB49D3CA3@.microsoft.com...
>
>|||If you can reproduce this I would give MS PSS a call. Does this happen if
you just run the select that the feeds the cursor? You might also try
creating a STATIC cursor instead of the default and see if that makes a
difference.
Andrew J. Kelly SQL MVP
"Michelle" <Michelle@.discussions.microsoft.com> wrote in message
news:7A06DB4B-E432-4625-8CEF-F521FB5F587D@.microsoft.com...[vbcol=seagreen]
>I reversed the order and was able to reproduce the issue. After several
> successful runs, it ran and ONLY backed up the mz database. It's not even
> building a backup statement for msdb. It's like it thinks that msdb isn't
> in
> the cursor and quits when it finishes the database just before msdb. We've
> only seen this with 2005 64-bit (Enterprise AND Standard Edition).
> I appreciate the table suggestion. We've considered doing this for a lot
> of
> admin stuff but just can't get resources allocated to that right now. We
> have
> about 50 sql servers.
> "Andrew J. Kelly" wrote:
>|||... and I'd do some more investigating before opening a case with MS PSS. T
his includes:
Do the cursor without backup. Instead of backup, just insert a row into a ta
ble. This would
determine whether the problem is with the cursor or if backup has to be invo
lved.
Do an insert both before and after the backup.
Etc...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OkqrvvJ9GHA.2316@.TK2MSFTNGP04.phx.gbl...
> If you can reproduce this I would give MS PSS a call. Does this happen if
you just run the select
> that the feeds the cursor? You might also try creating a STATIC cursor in
stead of the default and
> see if that makes a difference.
> --
> Andrew J. Kelly SQL MVP
> "Michelle" <Michelle@.discussions.microsoft.com> wrote in message
> news:7A06DB4B-E432-4625-8CEF-F521FB5F587D@.microsoft.com...
>