November 30, 2010 at 8:47 am
I made a script to backup all my databases locally.
On some nights (about one out of three), the loop exits without any errors while there are still databases in the cursor.
Anyone sees something I don't? Feel free to ask questions if you need me to clarify things.
Notes:
1. It's the same script on all my SQL Server. They all have the same trouble, and not on the same nights.
2. I've checked the output of the SELECT right before the DECLARE CURSOR. All databases are shown.
3. The SQL Servers are on Virtual Servers (VSphere) and a SAN.
4. Versions: SQL Server 2008 SP1, Windows Server 2008 R2
/* BACKUP ALL DATABASES (EXCEPT TEMPDB) */
-- Cursor on all databases (except tempdb)
DECLARE cDatabases CURSOR FOR
SELECT d.name
FROM sys.databases d
WHERE d.name NOT LIKE 'tempdb'
AND d.state = 0
ORDER BY d.name
DECLARE @DBName SYSNAME
DECLARE @QueryToExecute VARCHAR(4000)
DECLARE @CurrentDate VARCHAR(10)
DECLARE @CurrentTime VARCHAR(10)
-- Loop through all the databases
OPEN cDatabases
FETCH NEXT FROM cDatabases INTO @DBName
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @CurrentDate = STUFF(STUFF(CONVERT(VARCHAR(10), GETDATE(), 112), 5, 0, '-'), 8, 0, '-')
SET @CurrentTime = REPLACE(CONVERT(VARCHAR(10), GETDATE(), 108), ':', '')
SET @QueryToExecute =
'BACKUP DATABASE [' + @DBName + '] TO DISK = ''' + @DBName + '_' + @CurrentDate + '.bak'' WITH RETAINDAYS = 1, NOFORMAT, NOINIT, NAME = N''' + @DBName + '_' + @CurrentDate + '_' + @CurrentTime + ''', SKIP, REWIND, NOUNLOAD, STATS = 10'
EXECUTE (@QueryToExecute)
SET @QueryToExecute =
'declare @backupSetId as int' + CHAR(13) + CHAR(10) +
'select @backupSetId = position from msdb..backupset where database_name=N''' + @DBName + ''' and backup_set_id = (select max(backup_set_id) from msdb..backupset where database_name = N''' + @DBName + ''')' + CHAR(13) + CHAR(10) +
'if @backupSetId is null begin raiserror(N''Échec de la vérification. Les informations de sauvegarde pour la base de données « ' + @DBName + ' » sont introuvables.'', 16, 1) end' + CHAR(13) + CHAR(10) +
'RESTORE VERIFYONLY FROM DISK = ''' + @DBName + '_' + @CurrentDate + '.bak'' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND'
EXECUTE (@QueryToExecute)
FETCH NEXT FROM cDatabases INTO @DBName
END
CLOSE cDatabases
DEALLOCATE cDatabases
GO
___________________________________
I love you but you're standing on my foot.
November 30, 2010 at 9:03 am
Do you have a trace that will capture the commands and where they got to? That might help you start out.
I'd definitely look at logging the dynamic commands before the execute statements.
Have you checked the various system and SQL Server logs for errors or issues at the time the script stops? Or are you just looking at the job that calls the script and seeing that it reports "finished successfully"?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 30, 2010 at 9:23 am
I didn't find anything in any of the windows events logs or the SQL Server logs.
As you suggested, I added a bunch of outputs to trace the loop. I'll get back with the results soon.
Thanks!
EDIT: It seems that, while I know it's not on the last database in the WHILE loop, the @@FETCH_STATUS turns to -2, meaning the fetched row is missing. I wonder if it's because the cursor is based on sys.databases and it could change :unsure:. So I added the keyword STATIC in the DECLARE CURSOR. I'll get back with the results.
___________________________________
I love you but you're standing on my foot.
December 3, 2010 at 3:45 am
AlreadyPicked (11/30/2010)
On some nights (about one out of three), the loop exits without any errors while there are still databases in the cursor.
it might be happening when it starts taking backup of master (or from database you are taking up this backup )
1.Use this SP/script in master then take backups
2.then apply it other master and take backup of it.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
December 6, 2010 at 9:31 am
Well, since I added the keyword STATIC in my DECLARE CURSOR, the problem didn't occur. So I'm gonna implement this change on all my servers.
If anyone knows why the resultset of sys.databases system view might change like that during a backup loop, feel free to enlighten me up!
Thanks.
___________________________________
I love you but you're standing on my foot.
December 6, 2010 at 9:29 pm
AlreadyPicked (12/6/2010)
If anyone knows why the resultset of sys.databases system view might change like that during a backup loop, feel free to enlighten me up!Thanks.
Sometime i observed that these catalog view dont provide fair/correct information So we should not blindly lie on these .
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
December 8, 2010 at 6:21 am
Is it possible the state column is changing? Do you have any databases where AutoClose is turned on, or anything like that?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 8, 2010 at 7:38 am
The state column could be changing but I don't see why it would. No process is making it offline or in emergency and there is no recovery process at that time (except the part where it does a "RESTORE VERIFYONLY" which is on the current database anyway). Could the OS give false information about the primary filegroup because it's on a virtual server and force a state of "suspect"? That would be bad... :unsure:
And no, I have no databases that are on autoclose. "Close Cursor on Commit Enabled" is False though I don't know what that does exactly.
___________________________________
I love you but you're standing on my foot.
December 13, 2010 at 7:31 am
The close cursor option wouldn't impact this.
It would be interesting to capture the state of the data in the cursor at each row-move. Not sure it would be worth the effort, since you've solved the problem with the Static option, but would be interesting.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply