February 11, 2014 at 7:22 am
We have a job on 1 of our servers that loads database names into a cursor, then runs a Differential backup on those databases. For the 1st time I am aware of, it only processed the 1st 2 databases, then completed normally. There were no errors, and the logging shows all the steps completed, but for only 2 databases and the job completed.
It's as if the cursor only got 2 results when selecting from sysdatabases instead of the usual 7. How could that happen ??
DECLARE @dbname VARCHAR(64)
DECLARE dbcursor CURSOR FOR
SELECT [name] FROM master..sysdatabases WHERE [name] in
('Database_1',
'Database_2',
'Database_3',
'Database_4',
'Database_5',
'Database_6',
'Database_7'
) order by [name]
IF DATEPART(DW, GETDATE()) <> 1 ---==== DAILY DIFFERENTIAL
BEGIN
OPEN dbcursor
FETCH NEXT FROM dbcursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @bak_loc = 'F:\transfer\backupdiff\' + CONVERT(VARCHAR(32), GETDATE(), 112) + @dbname + '.bak'
BACKUP DATABASE @dbname
TO DISK = @bak_loc
WITH DIFFERENTIAL
-- log completion status
set @step# = @step# + 1
insert into JobStepStatus
select @step#, 'nightly_backup', GETDATE(), 'Backup ' + @dbname + ' Diff completed',
FETCH NEXT FROM dbcursor INTO @dbname
END
CLOSE dbcursor
DEALLOCATE dbcursor
END
insert into execunet_data_monitor..JobStepStatus
select @step#, 'nightly_backup', GETDATE(), '*** DIFF Job completed',
February 11, 2014 at 5:39 pm
That's not something I've seen before. And that's pretty straight forward code. Any chance the databases are marked as offline or something along those lines? Have they recently failed DBCC checks? Uhmmmm... Not sure. That's odd. Might want to post a Connect item on it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 11, 2014 at 7:22 pm
I wonder if for some reason those database names are not selecting out of the sysdatabases and thus creating the failure.
What is the result of your select query that populates the cursor?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 11, 2014 at 7:42 pm
I ran the select by itself when troubleshooting a few hours later & it worked fine. The next day, the job ran fine again as it has for more than a year.
February 11, 2014 at 9:16 pm
Yeah that is bizarre. Something must have short circuited that select at the time the backup was running. I haven't seen that happen before with this kind of a simple script.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 11, 2014 at 10:20 pm
Could be the Full backup of those 2 database was not performed? And when full backup job completed diff backup also successful?
OR
might be the issue of stale query plan?
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
February 11, 2014 at 11:39 pm
was any other previous backup's running that time?
Regards
Durai Nagarajan
February 12, 2014 at 3:59 am
No other backups running at the time. I can see backup history and the 2 that did run took 4 minutes just after midnight, which is normal. Combined, the differentials would all take about 6 minutes,
February 12, 2014 at 9:50 am
Very strange, if the next DIFFs worked fine without a FULL in between the failure and the success.
The closest I've seen to this is
"Error 952: Database is in 'transition' state"
when transitioning from ONLINE to OFFLINE, and sys.databases still reports a database as ONLINE.
Check your logs - as Grant suggested, perhaps the skipped DB's weren't offline at the time?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply