August 19, 2011 at 7:39 am
Hi,
I have an SQL Server agent job which essentially fetches the names of databases to be backed up into a cursor and then executes an SP, passing in the name of each database, which will back up each database one at a time.
The code is as follows:
------------ Code Sample --------------------------------
Declare @DatabaseName varchar(250)
Declare @TextString varchar(250)
Declare Cusror_DBNames Cursor For select DatabaseName from UserDatabasesToBackup inner join sys.sysdatabases on UserDatabasesToBackup.DatabaseName = sys.sysdatabases.name where DataBackup = 1
Open Cusror_DBNames
FETCH NEXT FROM Cusror_DBNames INTO @DatabaseName
While (@@FETCH_STATUS <> -1)
BEGIN
EXEC dba.dbo.Backup_Database @DatabaseName
set @TextString = 'dba.dbo.Backup_Database ' + @DatabaseName
FETCH NEXT FROM Cusror_DBNames INTO @DatabaseName
end
Close Cusror_DBNames
Deallocate Cusror_DBNames
---------- End of sample -------------------
The problem is that at random times during the execution of the job; the database name variable is set to a large empty string rather then an actual database name and an error is returned which states that the database does not exist.
If i execute the job myself, it works perfectly and the point at which this replacement happens is different every time.
Does anyone have any advice on what could be causing this?
Thanks.
August 19, 2011 at 10:03 am
Cursors are evil things ... maybe something related to cursors is causing your problem
Try this version instead:
CREATE TABLE #TempDatabaseNames
(
ID INT IDENTITY,
DatabaseName VARCHAR(250)
)
INSERT INTO #TempDatabaseNames (DatabaseName)
SELECT DatabaseName
FROM UserDatabasesToBackup
JOIN sys.sysdatabases ON UserDatabasesToBackup.DatabaseName = sys.sysdatabases.name
WHERE DataBackup = 1
DECLARE @CurrID INT
DECLARE @NewID INT
SET @CurrID = 0
SET @NewID = 0
DECLARE @DatabaseName VARCHAR(250)
DECLARE @TextString VARCHAR(250)
WHILE 1 = 1
BEGIN
SET @DatabaseName = ''
SET @TextString = ''
SELECT TOP 1
@NewID = ID,
@DatabaseName = DatabaseName
FROM #TempDatabaseNames
WHERE ID > @CurrID
ORDER BY ID
IF @CurrID = @NewID
BREAK
EXEC dba.dbo.Backup_Database @DatabaseName
SET @TextString = 'dba.dbo.Backup_Database ' + @DatabaseName
SET @CurrID = @NewID
END
Should do the same thing your script does, just instead of using cursors, uses a while loop, with the db names cached into a temp table.
August 19, 2011 at 10:58 am
kramaswamy (8/19/2011)
Cursors are evil things ... maybe something related to cursors is causing your problemTry this version instead:
CREATE TABLE #TempDatabaseNames
(
ID INT IDENTITY,
DatabaseName VARCHAR(250)
)
INSERT INTO #TempDatabaseNames (DatabaseName)
SELECT DatabaseName
FROM UserDatabasesToBackup
JOIN sys.sysdatabases ON UserDatabasesToBackup.DatabaseName = sys.sysdatabases.name
WHERE DataBackup = 1
DECLARE @CurrID INT
DECLARE @NewID INT
SET @CurrID = 0
SET @NewID = 0
DECLARE @DatabaseName VARCHAR(250)
DECLARE @TextString VARCHAR(250)
WHILE 1 = 1
BEGIN
SET @DatabaseName = ''
SET @TextString = ''
SELECT TOP 1
@NewID = ID,
@DatabaseName = DatabaseName
FROM #TempDatabaseNames
WHERE ID > @CurrID
ORDER BY ID
IF @CurrID = @NewID
BREAK
EXEC dba.dbo.Backup_Database @DatabaseName
SET @TextString = 'dba.dbo.Backup_Database ' + @DatabaseName
SET @CurrID = @NewID
END
Should do the same thing your script does, just instead of using cursors, uses a while loop, with the db names cached into a temp table.
There is no difference at all between using WHILE LOOP and cursor here! So you just replaced one evil with another one :hehe:
Also I should mention that the way you exit from the loop is terrible :w00t:.
Actually, this is the one of the cases where use of cursors are totally justified. To be on the safe end, you can preselect required data into temp table and open cursor on it (declare it as FAST_FORWARD as well).
Also, I would use positive atitude in checking the FETCH_STATUS:
WHILE (@@FETCH_STATUS = 0)
Have you posted the query as it is? May be in the real one you have left join? The posted one looks fine and you should not have entries in sys.databases with empty database name.
What can happen during this query execution, there are some database backup/restore jobs running, which may result in fantom values in sys.databases. As your cursor is not READ ONLY, you may experience these results. So, declare your cursor as read only, or as adviced above, preselect all into temp table and open cursor over it.
September 27, 2011 at 12:21 pm
I just stumbled across this thread. I have been having this exact same problem while using a similar script to back up all of my SQL databases by cursoring over sys.databases. I have a script that runs on about 15 servers and for some reason a couple of them periodically display this behavior. When I stumbled across your post, I decided to figure out what was going on.
As it turns out, what is happening is @@FETCH_STATUS is being set to -2, which implies that "the row fetched is missing". Clearly the record is still in sys.databases, but apparently something is causing the cursor to be invalidated.
In your case, you were only checking for @@FETCH_STATUS <> -1, so you weren't catching this condition. I check for @@FETCH_STATUS = 0, which was previously suggested, and I suggest you do the same.
I haven't fully answered what is causing this yet, though I suspect that the backup operation is changing something in an underlying table as it backs up each database, and depending on the order the records are stored, or something along those lines, the cursor is somehow, sometimes being invalidated.
Like you, I too just did a simple DECLARE CURSOR without any parameters, even though typically if I had to use a cursor I would at least use FAST_FORWARD and READ_ONLY for this type of application. I'm not sure if that would help here, but it's probably worth implementing.
Regardless of the exact cause of the problem, it seems the only sure way to prevent it is to create a copy of the data being cursored over and then work off of that copy. According to Books Online, the "STATIC" keyword does that for you, creating a copy of the data in tempdb. I went ahead and added that to my cursor declaration. Given the randomness of this happening it's too soon to say for sure it's fixed, but hopefully this helps you understand your problem.
September 28, 2011 at 1:21 am
My apologies, i meant to reply to this sooner to give closure on this thread.
As has been suggested, creating a static copy of the data being 'cursored' has stopped the problem from occurring.
Thanks to each person who has responded to this thread.
October 10, 2017 at 7:51 am
Thank you munzelj for your post.
I am using a store procedure to backup my databases and once in a while itfails to backup all the databases. I have just changed my cursor to:
DECLARE
db_cursor CURSOR LOCAL STATIC READ_ONLY FOR -- 10/10/2017 ADDED READ_ONLY AFTER READING A WEB POST WITH SIMILAR ISSUE
SELECT name
FROM sys.databases
where database_id > 4 and name not in ('Electronic_Filing_Archive_Database');
I will follow through with this post in a couple of weeks to see if the issue now goes away. As I backup up my databases at 4:00 am and transaction log backups also run on the top of the hour you may be onto something. I appreciate the advice of checking the @@FETCH_STATUS variable also.
I like to add that I am having this problem on SQL Server 2016 SP1 Standard Edition.
October 27, 2017 at 11:05 am
It has now been a good 2 weeks of modified cursor and my full and differiential backups are now backing up all user databases under my SQL Server Instance. In Short problem solved.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply