1

We have a maintenance plan on our production server which does all the usual stuff... checking integrity, rebuilding indexes, backups, etc.

Lately, it has been failing every night, complaining about a database which does not exist. The maintenance plan is set to work only on All user databases.

However, the plan craps out every night, and gives the following message in the job history:

Executing the query "USE [RollbackSnapshotTempDB{73B1E0C2-00FC-476A-A67..." failed with the following error:
"Database 'RollbackSnapshotTempDB{12345678-1234-1234-5678-012345678912}' does not exist. Make sure that the name is entered correctly.".
Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

(I changed the GUID, I wasn't sure if that is any sensitive info.)

We definitely do not have a user database (or system database either, for that matter) by that name. And there is nothing listed under the Database Snapshots folder either.

As per Aaron's comment, I queried sys.databases to see if that weird name is listed, but it is not listed in there either. Nor is it listed in SELECT DISTINCT database_name FROM msdb.dbo.backupset.

Running on SQL Server 2012, build 11.0.5343.

  • The maintenance plan isn't making up the database name; it is getting it from somewhere. Did you check sys.databases? There may be a database there that doesn't show up in the flaky UI due to flaky UI. – Aaron Bertrand Sep 10 '15 at 17:00
  • Good point... unfortunately... I just did select * from sys.databases, and that funky name is not listed in there, either. – eidylon Sep 10 '15 at 17:22
  • What about database_name in msdb.dbo.backupset? Can you break up the maintenance plan so you can get some kind of clue when exactly this error is happening, since obviously the error message is of little help? Can you just stop using maintenance plans altogether 9e.g. use Ola's solution), or temporarily change the maintenance plan to handle explicit database names instead of "all user databases"? – Aaron Bertrand Sep 10 '15 at 17:26
  • Argh - nope; if I do a SELECT DISTINCT database_name FROM msdb.dbo.backupset it does not exist in their either. Stupid Sql Server! I guess maybe using explicit names may be the best answer... there aren't many on this server, but it is not ideal for sure. Would love to figure out where Sql Server is getting this name from and how to fix it. – eidylon Sep 10 '15 at 17:32
2

In order to identify where this is coming from (because who knows which step in your maintenance plan is actually tripping on this), I recommend running the following tedious code, which will identify views that show the database name even if it is a database that no longer exists in sys.databases (dropped, renamed, what have you):

DECLARE @db SYSNAME = N'RollbackSnapshotTempDB{12345678-1234-1234-5678-012345678912}';

DECLARE @sql NVARCHAR(MAX) = N'SELECT N''--''';

SELECT @sql += N'
 UNION ALL SELECT TOP (1) ''SELECT * FROM msdb.' 
   + QUOTENAME(s.name) + '.' + QUOTENAME(o.name) + '
   WHERE ' + QUOTENAME(c.name) + ' = '''''' + @db + '''''';''
 FROM msdb.' + QUOTENAME(s.name) + '.' + QUOTENAME(o.name)
 + ' WHERE ' + QUOTENAME(c.name) + ' = @db'
 FROM msdb.sys.all_objects AS o
 INNER JOIN msdb.sys.schemas AS s
 ON o.[schema_id] = s.[schema_id]
 INNER JOIN msdb.sys.all_columns AS c
 ON c.[object_id] = o.[object_id]
 WHERE c.name IN (N'database_name',N'dbname',N'db_name',N'Database Name')
 AND o.type IN (N'U',N'V');

SELECT @sql += N'
 UNION ALL SELECT TOP (1) ''SELECT * FROM master.' 
   + QUOTENAME(s.name) + '.' + QUOTENAME(o.name) + '
   WHERE ' + QUOTENAME(c.name) + ' = '''''' + @db + '''''';''
 FROM master.' + QUOTENAME(s.name) + '.' + QUOTENAME(o.name)
 + ' WHERE ' + QUOTENAME(c.name) + ' = @db'
 FROM [master].sys.all_objects AS o
 INNER JOIN [master].sys.schemas AS s
 ON o.[schema_id] = s.[schema_id]
 INNER JOIN [master].sys.all_columns AS c
 ON c.[object_id] = o.[object_id]
 WHERE c.name IN (N'database_name',N'dbname',N'db_name',N'Database Name')
 AND o.type IN (N'U',N'V');

SELECT @sql;

EXEC sys.sp_executesql @sql, N'@db SYSNAME', @db;

This should produce output in the bottom pane like this (hopefully only a few rows):

--
SELECT * FROM msdb.[dbo].[backupset]
  WHERE [database_name] = 'RollbackSnapshotTempDB{12345678-1234-1234-5678-012345678912}';

You can copy and paste that output into the top pane and run it, and then act accordingly. For rows you find in backupset, for example, you can just delete them. If they are in catalog views this may be a little tougher - tell us what you find and we should be able to figure out how to fix it.

(Also, this isn't exhaustive, there are likely other places a database name might be stored using other variations on the name column. By design, I'm avoiding the obvious places like sys.databases.name, because you claim you've already checked there.)

Now, I would recommend Ola Hallengren's free maintenance scripts or even paid solutions like SQL Sentry Fragmentation Manager* for various types of maintenance, over the cumbersome and buggy native maintenance plans.

Short of that, stop using "all user databases" since this seems to cover more than just the set of databases that are currently present. I'll be interested to hear if you find this database name in any of the catalog views, backup-related, or job-related tables. I also have a tip publishing soon about searching all string columns across all databases on an instance, and it might be even more productive than the targeted script I wrote above. I'll try to remember to come back here and update this post with a link once it has been released.

  • Disclaimer: I work for SQL Sentry.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.