Microsoft KB Archive/122527

{|
 * width="100%"|

INF: Understanding MSscheduled_Backups Entries

 * }

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

Issue type :

Technology : kbSQLServSearch kbAudDeveloper kbSQLServ420OS2