INF: Understanding MSscheduled_Backups Entries

ID: Q122527

The information in this article applies to:

Microsoft SQL Server version 4.2x

SUMMARY

When you encounter difficulties with SQL Monitor scheduled tape events, it is frequently helpful to review the MSscheduled_backups table to quickly review the events that are scheduled. This article discusses the fields that directly affect what is dumped, and when and where it is to be dumped.

MORE INFORMATION

The MSscheduled_backups table has the following structure for SQL Server versions 4.21 and 4.21a. Starting with SQL 4.2, the 'stat' field was added, and in 4.21 both the 'attempts' and 'email_name' fields were added.

NOTE: SQL Server version 4.2b for OS/2 does not contain these additional fields.


   Event_id        int                  4     NOT NULL
   Database_name   varchar             30     NOT NULL
   Database_owner  varchar             30     NOT NULL
   Database_dump   varchar             30     NULL
   Log_dump        varchar             30     NULL
   Day             tinyint              1     NOT NULL
   Frequency       tinyint              1     NOT NULL
   Start_time      char                 5     NOT NULL
   Enabled         bit                  1     NOT NULL
   Last_dump       datetime             8     NULL
   In_progress     bit                  1     NOT NULL
   Stat            tinyint              1     NULL
   attempts        tinyint              1     NULL
   email_name      varchar             60     NULL

The combination of entries in the database_dump, log_dump, and stat fields determine what type of activity will be accomplished by a particular event.

The following chart represents the relationships:


   Activity                         Database_dump   log_dump   stat
   ----------------------------------------------------------------------
   Dump Database to disk               Device         NULL       0
   Dump LOG to disk                    NULL           Device     0
   Dump Database with INIT             Device         NULL       0
   Dump LOG with INIT                  NULL           Device     0
   Dump both DB & LOG with INIT        Device         Device     0
   Dump Database with APPEND           Device         NULL       2
   Dump DB with INIT, APPEND LOG       Device         Device     3
   Dump both DB & LOG with APPEND      Device         Device     4
   Dump Trans with truncate_only       NULL           NULL       5
   Dump Trans with no_log              NULL           NULL       6

The append and init are only appropriate for tape related events; if disk dumps are involved, the stat field is zero. The only way to verify that a dump with a stat of zero is to a tape rather than to disk is by comparing the logical name listed in the MSscheduled_backups table for the database_dump and log_dump with the logical name in sysdevices, where the control type is five.

The start_time, day, and frequency fields will determine when the scheduled event actually occurs. The time is entered using a twelve-hour clock in SQL Administrator but is converted to the 24-hour clock, and it is entered in the start_time field. The day is the day of the week, and the values start with Sunday with a value of one. Daily dumps will have a day value of zero with a frequency value of one.

The additional frequency values are as follows:


   Dump              Frequency      Day
----------------------------------------------------------------------
   Daily                1           day = 0
   Weekly               7           day in {1,2,3,4,5,6,7}
   Bi-Weekly           14           day in {1,2,3,4,5,6,7}
   Monthly Week 1      31           day in {1,2,3,4,5,6,7}
   Monthly Week 2      32           day in {1,2,3,4,5,6,7}
   Monthly Week 3      33           day in {1,2,3,4,5,6,7}
   Monthly Week 4      34           day in {1,2,3,4,5,6,7}

The attempts field was added in SQL Server version 4.21 and corresponds to the SQLMonitor registry entry BackupRetryAttempts. By default, SQL Monitor will attempt to dump a database three times, with each attempt being documented in this field. This retry activity can be manipulated by making the appropriate changes in the Windows NT registry.

Additional query words: windows nt

Keywords : kbinterop kbtool SSrvAdmin
Version : 4.2 4.21 4.21a
Platform : WINDOWS
Issue type :