We help IT Professionals succeed at work.

Select in MSSQL 2005 vs 2008R2

Hi,
I wrote the following to find all files belonging to the latest backup set and verify that they actually exist on disk. It works as it should on MSSQL 2008 and Denali. But when I run it on MSSQL 2005 it 'hangs'.

SELECT      physical_device_name AS FilePath,
bset.media_set_id AS BackupSetMediaID,
bmf.family_sequence_number,
bset.database_name,
bset.type AS BackupType,
backup_finish_date AS BackupDate                  
FROM msdb.dbo.backupset bset
INNER JOIN msdb.dbo.backupmediafamily bmf ON bset.media_set_id = bmf.media_set_id
WHERE database_name IN
      ---List of all the non system databases---
      (SELECT name FROM sys.databases
      WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb'))
      ------------------------------------------
-- All backups since and including last full backup
AND bset.media_set_id >=(SELECT MAX(b.media_set_id) FROM msdb.dbo.backupset b WHERE b.type = 'D' and b.database_name = bset.database_name)

Seems to be related to the following statement 'and b.database_name = bset.database_name'.
Does anyone have any idea why this works in 2008 but not 2005? and what do I do about it?
Comment
Watch Question

SQL SERVER EXPERT
BRONZE EXPERT
Commented:
please clarify "MSSQL 2005 it 'hangs'.?
do you see msdb db blocks?

why this can be differnt? 2008 msdb db may have more indexes and DBE is running this query in more optimized way

Author

Commented:
It was indeed not a fault. The query takes a few milliseconds to complete on 2008R2 but ran for over 3 minutes on 2005  :/

Author

Commented:
Running the same query with sp_msforeachdb dramatically decreased the runtime (3 minutes to 3 seconds for this system)

EXECUTE sp_msforeachdb 'USE ?
IF DB_NAME() NOT IN(''master'',''msdb'',''tempdb'',''model'')
SELECT physical_device_name AS FilePath,
bset.media_set_id AS BackupSetMediaID,
bmf.family_sequence_number,
bset.database_name,
CASE bset.Type    
            WHEN ''L'' THEN ''Transaction Log Backup''
            WHEN ''D'' THEN ''Full Backup''
            WHEN ''F'' THEN ''File Backup''
            WHEN ''I'' THEN ''Differential Backup''
            WHEN ''G'' THEN ''Differential File Backup''
            WHEN ''P'' THEN ''Partial Backup''
            WHEN ''Q'' THEN ''Differential Partial Backup''
      ELSE bset.type END AS BackupType,
backup_finish_date AS BackupDate                  
FROM msdb.dbo.backupset bset
INNER JOIN msdb.dbo.backupmediafamily bmf ON bset.media_set_id = bmf.media_set_id
WHERE bset.database_name = ''?'' AND bset.media_set_id >=(SELECT MAX(b.media_set_id) FROM msdb.dbo.backupset b WHERE b.type = ''D'' AND b.database_name = ''?'')
EugeneZ ZhitomirskySQL SERVER EXPERT
BRONZE EXPERT

Commented:
hmm. you run this on each user db .. it does not make much sense
try to clean msdb backup tables

Explore More ContentExplore courses, solutions, and other research materials related to this topic.