Builder
http://builder.com.com

Presents your 
SQL SERVER E-NEWSLETTER for October 31, 2002

<------------------------------------------->

CIRCUMVENT BACKUP SIZE BUG IN SEM

It appears that the same bug in SQL Server 6.5 that was fixed with the
third service pack (SP3) has found its way back into SQL Server 2000. 
It has to do with a temporary variable that is handling the backup size in
SQL Enterprise Manager (SEM). The variable will only hold up to a 2-GB
value. If it is anything larger, it may be a negative number or a 
number that does not reflect the actual database size.

Microsoft's Knowledge Base article, Q321670, provides a workaround in
the form of a set of queries directed at the msdb database. These are
behind the scenes queries that SEM executes to display the backup size 
of the database backup devices.
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q321670

Following are the work around queries that are found in the Knowledge
Base article. The first query identifies the backup set identifier, but
first you will need to substitute Your_Database_Name with the database 
that is in question. The backup set identifier from the first query needs to
be plugged into the second query to retrieve the correct backup size in
bytes. The backup set identifier that needs to be plugged into the 
second query is identified as Your_Backup_Set_ID.

select
      backup_set_id,
      backup_finish_date,
      name
  from
      msdb..backupset
  where
      database_name = N'Your_Database_Name'
      and type = 'D'
  order by backup_start_date desc
go
select
      backup_set_id,
      backup_start_date,
      backup_finish_date,
      name,
      type,
      backup_size,
      position
  from
      msdb..backupset
  where
      database_name = N'Your_Database_Name'
      and type != 'F'
      and backup_start_date >= (
                                select
                                      backup_start_date
                                  from
                                      msdb..backupset
                                  where
                                      backup_set_id =
'Your_Backup_Set_ID'
                               )
   order by backup_start_date

----------------------------------------