INF: Troubleshooting Scheduled Backups

ID: Q138118

The information in this article applies to:

Microsoft SQL Server version 4.2x

SUMMARY

In Microsoft SQL Server version 4.2x, scheduled backups are managed and run by the backup engine that is part of the SQL Monitor service, which acts as a SQL Server client on your behalf.

This article explains the limitations of scheduled events (backups) and gives solutions to common problems with scheduled events.

MORE INFORMATION

NOTE: For clarity, this article is organized as follows:


PART      TOPIC
----      ----------------------------------------------------
A         Limitations of scheduled events
B         Causes of not being able to manage scheduled events
C         Causes of one or more scheduled events not occurring
D         Causes of all scheduled events failing or not occurring

PART A:

Here is a list of the by-design limitations of scheduled events:

A scheduled event does not always start at the scheduled time. This is because of the wake-up time of the backup engine. The backup engine wakes up every X minutes and runs the stored procedure sp_MSbackup_now which returns scheduled events that should be started, where X is set either by the BackupTime registry value or the -B command line switch. The BackupTime registry value exists in HKEY_LOCAL_MACHINE\Software\Microsoft\SQLServer\SQLMonitor\Parameters. For example, suppose the wake-up time is fifteen minutes (which is the default), the SQL Monitor service is started at 10:00 AM, and an event is scheduled at 10:20 AM. In this case, the backup engine wakes up at 10:15 AM and every fifteen minutes thereafter. Because the event is scheduled at 10:20 AM, the first time the sp_MSbackup_now stored procedure will return the event to be run is at 10:30 AM. This start time is written into the history log, which is the Actual_start_time column of the MSscheduled_backups_log table.

If the time of a scheduled event is modified after the scheduled event has occurred, the scheduled event will not occur again until the next day, depending on the frequency, at the new time. This is because the sp_MSbackup_now stored procedure performs a datediff in units of days between the Last_dump column of the MSscheduled_backups table and the current date and time. For additional information about the structure of the MSscheduled_backups table, please see the following article in the Microsoft Knowledge Base:

Q122527 : INF: Understanding MSscheduled_Backups Entries

Only one scheduled event can occur at a time, so if multiple scheduled events are returned by the sp_MSbackup_now stored procedure, the events are run one at a time in order of scheduled time and Event_id.

Error messages returned while performing a scheduled event are returned to the backup engine of SQL Monitor, and are not always recorded in the history log, which is the Message column of the MSscheduled_backups_log table.

PART B:

Here is a list of common error messages, their causes, and workarounds of situations which disable the ability to manage scheduled events in SQL Administrator:

If you get a dialog in SQL Administrator with the error "The SQL Server Monitor is not started on this server" or "The database selected for the scheduled backup is not permitted to sa," start the SQLMonitor service with either the SQL Service Manager application or the Services application in the Windows NT Control Panel.

If Step 1 fails or you get any of the following errors, then the sa password has been changed and SQL Monitor needs to be notified.
- A dialog is displayed with the title "Services" and the message "The SQLMonitor service terminated with service-specific error 1."
- The NT Event Viewer shows the following error in the System Log:
  Event Id: 7024
  Source: Service Control Manager
  Type: Error
  Category: None
  Description: The SQLMonitor service terminated with service-
  specific error 1.
- The NT Event Viewer shows the following error in the Application
  Log:
  Event Id: 0
  Source: SQLMonitor
  Type: Error
  Category: (1)
  Description: The description for event id (0) in source
  (SQLMonitor)could not be found. It contains the following insertion
  string(s): Can't Log on to ., user: sa.
To change the sa password for SQL Monitor, use the NET START SQLMONITOR command from a Command Prompt with the /NEWPASSWORD switch, as documented in the SQL Server Release Notes help file.

If the connection to SQL Server is made using the TCP/IP Sockets Net- Library, please see the following article in the Microsoft Knowledge Base for more information:

Q118386 : BUG: SQL Admin Reports SQL Monitor Not Started

Reinstall the SQL Server client software or run Setup to rebuild SQL Server's registry keys according to page 144 of the "Configuration Guide."

Try another Net-Library to connect to SQL Server

If SQL Administrator gives a dialog with the error "The connection to the SQL Monitor has timed out, do you wish to continue?", determine if the master database is out of space by running the following query:
```
      USE master
      GO
      CREATE TABLE test1105 (a int)
      GO
      INSERT test1105 VALUES (1)
      GO
      DROP TABLE test1105
 
```
If this query fails with an 1105 error, then truncate the transaction log of the master database or increase the size of the master database.

If displaying the scheduled events history log, please see the following article in the Microsoft Knowledge Base for more information:

Q114662 : PRB: SQL Admin Can Not Display History for Scheduled Dumps

PART C:

Here is a list of common errors and their workarounds of situations which will cause one or more scheduled events not to occur:

Check the history log for errors in the message column.

If the history log contains the error "DUMP DATABASE has been interrupted by a USER ATTENTION signal," confirm that the SQLTimeout value is enough in seconds to complete the longest event. This value exists in

HKEY_LOCAL_MACHINE\Software\Microsoft\SQLServer\SQLMonitor\Parameters.

If the history log or the SQL Server error log contain device errors, see Knowledge Base articles Q123405 ("INF: How to Troubleshoot SQL Server Tape Read/Write Errors") and Q124023 ("INF: Testing Methods for SQL Server Tape Dumps or Loads") for more information.

If the history log shows that the scheduled event seems to skip alternate occurrences, confirm that the scheduled event does not cross a date line. For example, the scheduled event actually starts at 11:00 P.M. and finishes at 12:15 A.M. The workaround for this is to make sure that all scheduled events complete on the same day they start. With the example event above, this could be accomplished be scheduling the event at 10:30 P.M. or 12:00 A.M.

If the history log shows that a scheduled event to tape returns the error "Unknown!", see Knowledge Base article Q114027 ("BUG: Scheduled Tape Dumps Fail w/ Error Unknown") for more information.

If the history log shows no error and the query "SELECT @@version" returns version 4.21.006 or 4.21.007, article Q119266 ("BUG: sp_MSbackup_now Fails on Scheduled Dumps to Same Device") may provide a solution. NOTE: This issue has been fixed in Microsoft SQL Server version 4.21a.

If events that are scheduled near 12:00 A.M. do not occur, see Knowledge Base article Q90484 ("BUG: Scheduled Backup Near Midnight Does Not Occur") for more information.

If monthly scheduled events skip alternate occurrences, and the query "SELECT @@version" returns version 4.2, see Knowledge Base article Q107710 ("FIX: Monthly Scheduled Backups Skip Alternate Months) for more information. NOTE: This issue has been fixed in Microsoft SQL Server version 4.21.

If the actual startup time for scheduled events is not near the scheduled time, see Knowledge Base article Q87834 ("FIX: SQL Admin: Actual Startup Time for Scheduled Backups") for more information. Note: This issue has been fixed in Microsoft SQL Server for OS/2 version 4.2a.

PART D:

Here is a list of common errors and their workarounds of situations which will cause all scheduled events to not occur:

If the SQLMonitor service is not started or fails to start, see PART B (earlier in this article), Steps 1 and 2 for more information.

Confirm if the master database has a 1105 error using query in PART B, Step 6.

If the scheduled event is a dump to a device that has removable media (tape drive or floppy drive), the query "sp_who" shows a process running a DUMP DATABASE or DUMP TRANSACTION command, the status is Sleeping and it is not blocked, and there is no activity to the device, place new media in the device. If the scheduled event completes successfully, the problem was that the media did not have enough space to complete the event and was waiting for new media to continue the scheduled event. If the scheduled event does not complete but there was activity to the device, retry this step.

If the scheduled event is to a tape device and it is not appending to the tape and the media retention setting of sp_configure disables the use of the media, see Knowledge Base article Q117515 ("FIX: Scheduled Backups May Hang w/ Unexpired Tapes") for more information.

If a tape device is available, attempt to dump the master database to tape using the SQL Administrator interface. If the dump completes successfully, skip to step 5.

Stop and restart the SQLMonitor service.

Create a new scheduled event to dump the master database to a disk device within the next couple of minutes. If the scheduled event completes successfully, recreate all scheduled events.

If scheduled events stop occurring after 4/14/93, see article Q99113 ("FIX: Scheduled Backups Stop Working After 4/14/93") for more information. NOTE: This issue has been fixed in Microsoft SQL Server for OS/2, version 4.2b.

Additional query words: Dump Monitor SQLMonitor Task


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

Last Reviewed: April 15, 1999