verify_sysusages

IF EXISTS (SELECT * FROM sysobjects 
           WHERE id = OBJECT_ID('dbo.verify_sysusages') 
           AND sysstat & 0xf = 4)
	DROP PROCEDURE dbo.verify_sysusages
GO

CREATE PROCEDURE verify_sysusages
     @dbName VARCHAR(30) = ' ',        -- the database name on both systems
     @source VARCHAR(30) = ' ',        -- the source server name
     @sourcePassword VARCHAR(30) = ' ',-- sa password on source
     @target VARCHAR(30) = ' ',        -- the target server name
     @targetPassword VARCHAR(30) = ' ',-- sa password on target
     @saPassword VARCHAR(30) = ' ',    -- sa password on admin server
     @loadPath VARCHAR(50) = ' ',      -- fully qualified load path 
     @mailTo VARCHAR(30) = 'DBA',      -- address to mail debug info
     @debug VARCHAR(5) = 'false'       -- debug this procedure 
AS

/* 
  Procedure verify_sysusages compares the size allocated in 
  master..sysusages for two databases and reports if they are 
  not the same size. Note that the database must have the same
  name in both locations (so they have to be on different
  SQL Servers).
*/

DECLARE 
     @CMD VARCHAR(255),               -- reusable command holder 
     @CMDstatus INT,                  -- return status of command prompt  
     @myName VARCHAR(30)              -- name of this function 

SET NOCOUNT ON

SELECT @myName = 'admin.verify_sysuasges'

IF     (@dbName   <> ' ') 
   AND (@source   <> ' ') 
   AND (@target   <> ' ') 
   AND (@loadPath <> ' ')
  BEGIN
    SELECT @CMD = "SELECT SUM(size) FROM master..sysusages WHERE dbid = DB_ID('" +
                  @dbName + "')"
    SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + ' -Q"' + 
                  @CMD + '" -o' + @loadpath + '\' + @source + @dbName + '.siz'
    EXEC @CMDstatus = dispatch @CMD, @myName, @debug
    IF (@CMDstatus <> 0) return @CMDstatus
    SELECT @CMD = "SELECT SUM(size) FROM master..sysusages WHERE dbid = DB_ID('" + 
                  @dbName + "')"
    SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + ' -Q"' + 
                  @CMD + '" -o' + @loadpath + '\' + @target + @dbName + '.siz'
    EXEC @CMDstatus = dispatch @CMD, @myName, @debug
    IF (@CMDstatus <> 0) return @CMDstatus

    DELETE messenger WHERE spid = @@SPID

    EXEC hold_semaphore 'textreader' 
      TRUNCATE TABLE textreader
      SELECT @CMD = 'bcp admin..textreader in ' + @loadPath + 
                    '\' + @source + @dbName + '.siz -c -Usa -P' + 

                    @saPassword + ' -S' + @@SERVERNAME 
      EXEC @CMDstatus = dispatch @CMD, @myName, @debug
      IF (@CMDstatus <> 0) return @CMDstatus
      SELECT @CMD = 'bcp admin..textreader in ' + @loadPath + 
                    '\' + @target + @dbName + '.siz -c -Usa -P' + 
                    @saPassword + ' -S' + @@SERVERNAME
      EXEC @CMDstatus = dispatch @CMD, @myName, @debug
      IF (@CMDstatus <> 0) return @CMDstatus
      INSERT messenger
      SELECT @@SPID, 
             text 
      FROM textreader 
      WHERE text NOT LIKE " -%" 
      AND text NOT LIKE "(1 %"
      AND text NOT LIKE " "
      AND text IS NOT NULL
      TRUNCATE TABLE textreader
    EXEC release_semaphore  'textreader' 
    SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + 
                  ' -dmaster -Q"master..xp_cmdshell ' + "'del " + 
                  @loadPath + "\*.siz'" + '"'
    EXEC @CMDstatus = dispatch @CMD, @myName, @debug
    IF (@CMDstatus <> 0) return @CMDstatus
    IF EXISTS (SELECT info FROM messenger
               WHERE spid = @@SPID
               AND ISNUMERIC(info) = 0)
      BEGIN
        SELECT @CMD = 'An error occurred while checking the size of database ' 
                      + @dbName + ' on ' + @source + ' and ' + @target + '. ('
                      + @myName + ')' 
        EXEC @CMDstatus = dispatch @CMD, @myName, @debug
        IF (@CMDstatus <> 0) 
          BEGIN
            SELECT @CMD = "SELECT * FROM admin.dbo.messenger WHERE spid = " 
                          + CONVERT(VARCHAR(5),@@SPID)
            EXEC master..xp_sendmail @mailTo, 
                                     'admin.verify_sysusages error info', 
                                     @query = @CMD            
            RETURN @CMDstatus
          END
        DELETE messenger where spid = @@SPID
        RETURN 99
      END 

    IF NOT EXISTS (SELECT * FROM messenger
                   WHERE spid = @@SPID
                   GROUP BY info
                   HAVING COUNT(*) > 1)
      BEGIN
        IF @debug = 'true'
          BEGIN
            SELECT @CMD = "SELECT * FROM admin.dbo.messenger WHERE spid = " 
                          + CONVERT(VARCHAR(5),@@SPID)
            EXEC master..xp_sendmail @mailTo, 
                                     'admin.verify_sysusages debug info', 
                                     @query = @CMD            
          END
        SELECT @CMD = 'The ' + @source + ' and ' + @target + ' ' + @dbName + 
                       ' databases are not the same size. (' + @myName + ')'
        EXEC @CMDstatus = dispatch @CMD, @myName, @debug
        IF (@CMDstatus <> 0) return @CMDstatus
        DELETE messenger where spid = @@SPID
        RETURN 99     
      END
    DELETE messenger WHERE spid = @@SPID
    RETURN
  END
ELSE
  BEGIN
    PRINT "  usage: verify_sysusages"
    PRINT "                [@dbname         = <database name>  ],"
    PRINT "                [@source         = <source SQL Server>     ]," 
    PRINT "                [@sourcePassword = <source sa password>    ],"
    PRINT "                [@target         = <target SQL Server>     ],"
    PRINT "                [@targetPassword = <target sa password>    ],"
    PRINT "                [@saPassword     = <admin sa password>     ],"
    PRINT "                [@loadPath       = <target restore path>   ]," 
    PRINT "                [@debug          = <debug - default=false> ],"
    RETURN 1
  END
GO