none
Does the server get shut down automatically after DB Maintenance Plan backups RRS feed

  • Question

  • Apologies but I have very limited DBA knowledge.  Our application loses the connection to SQL Server overnight which means that SQL Server must have been shut down and restarted at some point.  I can't find anything in the event logs stating that SQL Server is being shut down.  The only information I have is a failure message in the application event log:

    22/06/2009,01:00:26,MSSQL$MICROSOFT##SSEE,Information,(2),17403,N/A,L-MACHINE,Server resumed execution after being idle 85489 seconds. Reason: timer event.
    22/06/2009,00:00:49,MSSQLSERVER,Information,(2),17177,N/A,L-MACHINE,This instance of SQL Server has been using a process id of 1972 since 15/06/2009 14:07:22 (local) 15/06/2009 13:07:22 (UTC).
    22/06/2009,00:00:02,SQLSERVERAGENT,Warning,Job Engine ,208,N/A,L-MACHINE,SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan 'DB Maintenance Plan1'' (0x7C3F8994BD85C544ACC7E213D4F55113) - Status: Failed - Invoked on: 2009-06-22 00:00:00 - Message: The job failed.  The Job was invoked by Schedule 2 (Schedule 1).  The last step to run was step 1 (Step 1).
    22/06/2009,00:00:02,MSSQLSERVER,Information,(6),17055,MCM\amm,L-MACHINE,"18265 :
    Log backed up: Database: model, creation date(time): 2000/08/06(01:40:52), first LSN: 5:34:1, last LSN: 5:51:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'\\m-machine\L-Machine SQl Backups\model_tlog_200906220000.TRN'}).
    "
    Friday, July 3, 2009 2:07 PM

All replies

  • No, SQL Server do not shut down for such operations. Defragmentation will cause some locking so your app might be blocked meaning if that app is programmed using some timeout setting, it might error due to that. I suggest you talk to your app vendor.
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Friday, July 3, 2009 8:16 PM
    Moderator
  • The application has an ADO connection which is opened when the app is started and kept open until the app is shut down.  The problem happens when the app is left running overnight.  During the night something external to the app is breaking the ADO connection and the app is unusable until the user restarts it.  The overnight DB Maintenance Plan backs up the app's database, could this cause the ADO connection to be broken?

    Monday, July 6, 2009 12:54 PM
  • The backup command will not break other connections. what else have youin that maint plan? Also, what version of SQL Server?
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Monday, July 6, 2009 3:53 PM
    Moderator
  • give us a complete list of what your plan is doing.  simple backups should not be causing any issue.
    Tuesday, July 7, 2009 2:44 PM
  • Sorry, it is our customer that is having the problem.  I have asked them for details of the plan but no answer yet.  Will post the details as soon as they reply.
    Tuesday, July 7, 2009 2:49 PM
  • As a side note here you may also want to check windows update.  It will auto restart the server if left to default values in windows 2008. 
    Wednesday, July 8, 2009 4:46 PM
  • I have some more information from the customer now.  Our application runs on a dialler and overnight keeps open a connection to SQL Server on a server machine.  This is the connection that is broken during the night.  On the server, the SQL Server version is SQL Server 2000 - 8.00.2039 and the Windows version is Windows Server 2003 Standard Edition Service Pack 2. 

    The Server DB Maintenance plan is:

    DB Maintenance Plan1         master, model, msdb, Northwind, pubs            (local)           Optimizations, Integrity Checks, Database Backup, Transaction...

    Backup
    Rubbish                             ..\SQLBackup\Rubbish                                   Disk Backup
    Backup Aztec                     ..\L-Machine SQL Backups\Aztec.bck               Disk Backup
    Backup ZBSData                ..\L-Machine SQL Backups\ZBSData.bck          Disk Backup

    There is also an "L-Machine System State PLUS.bkf job which runs at 23:40 every day.  In the run box was the following :

    C:\WINDOWS\system32\ntbackup.exe backup "@C:\Documents and Settings\Administrator.MCM\Local Settings\Application Data\Microsoft\Windows NT\NTBackup\data\L-Machine System State.bks" /n "L-Machine System State PLUS.bkf created 2/11/2009 at 1:34 PM" /d "Set created 2/11/2009 at 1:34 PM" /v:yes /r:no /rs:no /hc:off /m normal /j "L-Machine System State" /l:s /f "\\Navisionb\Server-Backups\L-Machine System State PLUS.bkf"

    This is an extract from the Server Application event log of all the events in a single night:

    01/07/2009,16:00:27,SQLSERVERAGENT,Error,Alert Engine ,322,N/A,L-MACHINE,The data portion of event 4373 from MSSQLSERVER is invalid.
    01/07/2009,16:00:27,MSSQLSERVER,Error,None,4373,N/A,L-MACHINE,"The description for Event ID ( 4373 ) in Source ( MSSQLSERVER ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: SQL, KB960082(ENU), Fatal error during installation.
    ."
    01/07/2009,09:42:30,SceCli,Information,None,1704,N/A,L-MACHINE,Security policy in the Group policy objects has been applied successfully.
    01/07/2009,03:08:48,Symantec AntiVirus,Information,None,2,N/A,L-MACHINE,"

    Scan Complete:  Risks: 0   Scanned: 803   Files/Folders/Drives Omitted: 0"
    01/07/2009,03:08:00,Symantec AntiVirus,Information,None,3,N/A,L-MACHINE,"

    Scan started on selected drives and folders and all extensions."
    01/07/2009,03:07:58,Symantec AntiVirus,Information,None,7,N/A,L-MACHINE,"

    New virus definition file loaded. Version: 110630b."
    01/07/2009,01:00:30,MSSQL$MICROSOFT##SSEE,Information,(2),17403,N/A,L-MACHINE,Server resumed execution after being idle 85488 seconds. Reason: timer event.
    01/07/2009,00:00:26,MSSQLSERVER,Information,(2),17177,N/A,L-MACHINE,This instance of SQL Server has been using a process id of 1972 since 15/06/2009 14:07:22 (local) 15/06/2009 13:07:22 (UTC).
    01/07/2009,00:00:02,SQLSERVERAGENT,Warning,Job Engine ,208,N/A,L-MACHINE,SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan 'DB Maintenance Plan1'' (0x7C3F8994BD85C544ACC7E213D4F55113) - Status: Failed - Invoked on: 2009-07-01 00:00:00 - Message: The job failed.  The Job was invoked by Schedule 2 (Schedule 1).  The last step to run was step 1 (Step 1).
    01/07/2009,00:00:02,MSSQLSERVER,Information,(6),17055,MCM\amm,L-MACHINE,"18265 :
    Log backed up: Database: model, creation date(time): 2000/08/06(01:40:52), first LSN: 5:92:1, last LSN: 5:96:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'\\m-machine\L-Machine SQl Backups\model_tlog_200907010000.TRN'}).
    "
    01/07/2009,00:00:02,MSSQLSERVER,Information,(2),17055,MCM\amm,L-MACHINE,"8957 :
    DBCC CHECKDB (pubs, repair_fast) executed by MCM\amm found 0 errors and repaired 0 errors.  Elapsed time: 0 hours 0 minutes 0 seconds.
    "
    01/07/2009,00:00:02,MSSQLSERVER,Information,(2),17055,MCM\amm,L-MACHINE,"8957 :
    DBCC CHECKDB (Northwind, repair_fast) executed by MCM\amm found 0 errors and repaired 0 errors.  Elapsed time: 0 hours 0 minutes 0 seconds.
    "
    01/07/2009,00:00:01,MSSQLSERVER,Information,(2),17055,MCM\amm,L-MACHINE,"8957 :
    DBCC CHECKDB (model, repair_fast) executed by MCM\amm found 0 errors and repaired 0 errors.  Elapsed time: 0 hours 0 minutes 0 seconds.
    "
    30/06/2009,22:10:01,MSSQLSERVER,Information,(6),17055,MCM\amm,L-MACHINE,"18264 :
    Database backed up: Database: ZBSData, creation date(time): 2009/03/12(12:19:56), pages dumped: 1362, first LSN: 3131:42:1, last LSN: 3131:44:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'Backup ZBSData'}).
    "
    30/06/2009,22:00:06,MSSQLSERVER,Information,(6),17055,MCM\amm,L-MACHINE,"18264 :
    Database backed up: Database: Aztec, creation date(time): 2009/03/17(07:40:04), pages dumped: 32866, first LSN: 1004:2402:1, last LSN: 1004:2406:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'Backup Aztec'}).
    "
    30/06/2009,17:31:25,SceCli,Information,None,1704,N/A,L-MACHINE,Security policy in the Group policy objects has been applied successfully.
    30/06/2009,16:00:27,SQLSERVERAGENT,Error,Alert Engine ,322,N/A,L-MACHINE,The data portion of event 4373 from MSSQLSERVER is invalid.
    30/06/2009,16:00:26,MSSQLSERVER,Error,None,4373,N/A,L-MACHINE,"The description for Event ID ( 4373 ) in Source ( MSSQLSERVER ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: SQL, KB960082(ENU), Fatal error during installation.
    ."
    30/06/2009,03:07:52,Symantec AntiVirus,Information,None,2,N/A,L-MACHINE,"

    Scan Complete:  Risks: 0   Scanned: 751   Files/Folders/Drives Omitted: 0"
    30/06/2009,03:07:07,Symantec AntiVirus,Information,None,3,N/A,L-MACHINE,"

    Scan started on selected drives and folders and all extensions."
    30/06/2009,03:07:03,Symantec AntiVirus,Information,None,7,N/A,L-MACHINE,"

    New virus definition file loaded. Version: 110629c."
    30/06/2009,01:00:29,MSSQL$MICROSOFT##SSEE,Information,(2),17403,N/A,L-MACHINE,Server resumed execution after being idle 3854 seconds. Reason: timer event.
    30/06/2009,00:18:02,NTBackup,Information,None,8019,N/A,L-MACHINE,"End Operation:
    The operation was successfully completed.
    Consult the backup report for more details."
    30/06/2009,00:16:38,NTBackup,Information,None,8001,N/A,L-MACHINE,"End Backup of 'System State' '
    The operation was successfully completed.'

      Verify:  On
      Mode:  Append
      Type:  Copy
     
    Consult the backup report for more details.
    "
    30/06/2009,00:12:28,NTBackup,Information,None,8000,N/A,L-MACHINE,"Begin Backup of 'System State'

      Verify:  On
      Mode:  Append
      Type:  Copy
    "
    30/06/2009,00:12:21,SceCli,Information,None,1704,N/A,L-MACHINE,Security policy in the Group policy objects has been applied successfully.
    30/06/2009,00:12:07,NTBackup,Information,None,8001,N/A,L-MACHINE,"End Backup of 'C:' '
    The operation was successfully completed.'

      Verify:  On
      Mode:  Replace
      Type:  Normal
     
    Consult the backup report for more details.
    "
    30/06/2009,00:00:25,MSSQLSERVER,Information,(2),17177,N/A,L-MACHINE,This instance of SQL Server has been using a process id of 1972 since 15/06/2009 14:07:22 (local) 15/06/2009 13:07:22 (UTC).
    30/06/2009,00:00:03,SQLSERVERAGENT,Warning,Job Engine ,208,N/A,L-MACHINE,SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan 'DB Maintenance Plan1'' (0x7C3F8994BD85C544ACC7E213D4F55113) - Status: Failed - Invoked on: 2009-06-30 00:00:00 - Message: The job failed.  The Job was invoked by Schedule 2 (Schedule 1).  The last step to run was step 1 (Step 1).
    30/06/2009,00:00:03,MSSQLSERVER,Information,(6),17055,MCM\amm,L-MACHINE,"18265 :
    Log backed up: Database: model, creation date(time): 2000/08/06(01:40:52), first LSN: 5:88:1, last LSN: 5:92:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'\\m-machine\L-Machine SQl Backups\model_tlog_200906300000.TRN'}).
    "
    30/06/2009,00:00:02,MSSQLSERVER,Information,(2),17055,MCM\amm,L-MACHINE,"8957 :
    DBCC CHECKDB (pubs, repair_fast) executed by MCM\amm found 0 errors and repaired 0 errors.  Elapsed time: 0 hours 0 minutes 0 seconds.
    "
    30/06/2009,00:00:02,MSSQLSERVER,Information,(2),17055,MCM\amm,L-MACHINE,"8957 :
    DBCC CHECKDB (Northwind, repair_fast) executed by MCM\amm found 0 errors and repaired 0 errors.  Elapsed time: 0 hours 0 minutes 0 seconds.
    "
    30/06/2009,00:00:01,MSSQLSERVER,Information,(2),17055,MCM\amm,L-MACHINE,"8957 :
    DBCC CHECKDB (model, repair_fast) executed by MCM\amm found 0 errors and repaired 0 errors.  Elapsed time: 0 hours 0 minutes 0 seconds.
    "
    29/06/2009,23:41:18,NTBackup,Information,None,8000,N/A,L-MACHINE,"Begin Backup of 'C:'

      Verify:  On
      Mode:  Replace
      Type:  Normal
    "
    29/06/2009,23:41:01,MSSQL$MICROSOFT##SSEE,Information,(6),18264,NT AUTHORITY\SYSTEM,L-MACHINE,"Database backed up. Database: msdb, creation date(time): 2005/10/14(01:54:05), pages dumped: 1, first LSN: 130:384:172, last LSN: 130:464:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{FC6D2014-31BA-4878-8726-771B47A0D13C}3'}). This is an informational message only. No user action is required."
    29/06/2009,23:41:01,MSSQL$MICROSOFT##SSEE,Information,(6),18264,NT AUTHORITY\SYSTEM,L-MACHINE,"Database backed up. Database: model, creation date(time): 2003/04/08(09:13:36), pages dumped: 1, first LSN: 30:232:37, last LSN: 30:256:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{FC6D2014-31BA-4878-8726-771B47A0D13C}2'}). This is an informational message only. No user action is required."
    29/06/2009,23:41:01,MSSQL$MICROSOFT##SSEE,Information,(6),18264,NT AUTHORITY\SYSTEM,L-MACHINE,"Database backed up. Database: master, creation date(time): 2009/06/15(14:07:15), pages dumped: 1, first LSN: 273:416:37, last LSN: 273:472:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{FC6D2014-31BA-4878-8726-771B47A0D13C}1'}). This is an informational message only. No user action is required."
    29/06/2009,23:41:01,MSSQL$MICROSOFT##SSEE,Information,(2),3198,NT AUTHORITY\SYSTEM,L-MACHINE,I/O was resumed on database msdb. No user action is required.
    29/06/2009,23:41:01,MSSQL$MICROSOFT##SSEE,Information,(2),3198,NT AUTHORITY\SYSTEM,L-MACHINE,I/O was resumed on database master. No user action is required.
    29/06/2009,23:41:01,MSSQL$MICROSOFT##SSEE,Information,(2),3198,NT AUTHORITY\SYSTEM,L-MACHINE,I/O was resumed on database model. No user action is required.
    29/06/2009,23:41:00,MSSQL$MICROSOFT##SSEE,Information,(2),3197,NT AUTHORITY\SYSTEM,L-MACHINE,"I/O is frozen on database master. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup."
    29/06/2009,23:41:00,MSSQL$MICROSOFT##SSEE,Information,(2),3197,NT AUTHORITY\SYSTEM,L-MACHINE,"I/O is frozen on database msdb. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup."
    29/06/2009,23:41:00,MSSQL$MICROSOFT##SSEE,Information,(2),3197,NT AUTHORITY\SYSTEM,L-MACHINE,"I/O is frozen on database model. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup."
    29/06/2009,23:40:57,VSS,Error,None,6013,N/A,L-MACHINE,"Sqllib error: OLEDB Error encountered calling IDBInitialize::Initialize. hr = 0x80040e4d. SQLSTATE: 42000, Native Error: 18456
    Error state: 1, Severity: 14
    Source: Microsoft OLE DB Provider for SQL Server
    Error message: Login failed for user 'NT AUTHORITY\SYSTEM'.
    "
    29/06/2009,23:40:57,VSS,Error,None,6013,N/A,L-MACHINE,"Sqllib error: OLEDB Error encountered calling IDBInitialize::Initialize. hr = 0x80040e4d. SQLSTATE: 42000, Native Error: 18456
    Error state: 1, Severity: 14
    Source: Microsoft OLE DB Provider for SQL Server
    Error message: Login failed for user 'NT AUTHORITY\SYSTEM'.
    "
    29/06/2009,23:40:57,VSS,Error,None,6013,N/A,L-MACHINE,"Sqllib error: OLEDB Error encountered calling IDBInitialize::Initialize. hr = 0x80040e4d. SQLSTATE: 42000, Native Error: 18456
    Error state: 1, Severity: 14
    Source: Microsoft OLE DB Provider for SQL Server
    Error message: Login failed for user 'NT AUTHORITY\SYSTEM'.
    "
    29/06/2009,23:40:57,VSS,Error,None,6013,N/A,L-MACHINE,"Sqllib error: OLEDB Error encountered calling IDBInitialize::Initialize. hr = 0x80040e4d. SQLSTATE: 42000, Native Error: 18456
    Error state: 1, Severity: 14
    Source: Microsoft OLE DB Provider for SQL Server
    Error message: Login failed for user 'NT AUTHORITY\SYSTEM'.
    "
    29/06/2009,23:40:56,MSSQL$MICROSOFT##SSEE,Information,(2),17401,N/A,L-MACHINE,Server resumed execution after being idle 80715 seconds: user activity awakened the server. This is an informational message only. No user action is required.
    29/06/2009,23:40:11,NTBackup,Information,None,8018,N/A,L-MACHINE,Begin Operation
    29/06/2009,22:10:00,MSSQLSERVER,Information,(6),17055,MCM\amm,L-MACHINE,"18264 :
    Database backed up: Database: ZBSData, creation date(time): 2009/03/12(12:19:56), pages dumped: 1370, first LSN: 3092:42:1, last LSN: 3092:44:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'Backup ZBSData'}).
    "
    29/06/2009,22:00:06,MSSQLSERVER,Information,(6),17055,MCM\amm,L-MACHINE,"18264 :
    Database backed up: Database: Aztec, creation date(time): 2009/03/17(07:40:04), pages dumped: 32098, first LSN: 986:830:1, last LSN: 986:834:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'Backup Aztec'}).
    "
    29/06/2009,16:00:28,SQLSERVERAGENT,Error,Alert Engine ,322,N/A,L-MACHINE,The data portion of event 4373 from MSSQLSERVER is invalid.
    29/06/2009,16:00:27,MSSQLSERVER,Error,None,4373,N/A,L-MACHINE,"The description for Event ID ( 4373 ) in Source ( MSSQLSERVER ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: SQL, KB960082(ENU), Fatal error during installation.
    ."
    29/06/2009,12:49:09,Userenv,Information,None,1516,NT AUTHORITY\SYSTEM,L-MACHINE,Windows unloaded user MCM\Administrator registry when it received a notification that no other applications or services were using the profile.
    29/06/2009,12:49:05,Userenv,Warning,None,1517,NT AUTHORITY\SYSTEM,L-MACHINE,"Windows saved user MCM\Administrator registry while an application or service was still using the registry during log off. The memory used by the user's registry has not been freed. The registry will be unloaded when it is no longer in use.

     This is often caused by services running as a user account, try configuring the services to run in either the LocalService or NetworkService account."
    29/06/2009,12:01:52,Userenv,Information,None,1516,NT AUTHORITY\SYSTEM,L-MACHINE,Windows unloaded user MCM\Administrator registry when it received a notification that no other applications or services were using the profile.
    29/06/2009,12:01:49,Userenv,Warning,None,1517,NT AUTHORITY\SYSTEM,L-MACHINE,"Windows saved user MCM\Administrator registry while an application or service was still using the registry during log off. The memory used by the user's registry has not been freed. The registry will be unloaded when it is no longer in use.

     This is often caused by services running as a user account, try configuring the services to run in either the LocalService or NetworkService account."
    29/06/2009,07:13:16,SceCli,Information,None,1704,N/A,L-MACHINE,Security policy in the Group policy objects has been applied successfully.
    29/06/2009,03:07:07,Symantec AntiVirus,Information,None,2,N/A,L-MACHINE,"

     

    Thursday, July 9, 2009 2:15 PM
  • Remove the "attempt to repair minor problems" from the maint plan (check database integrity task).
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Thursday, July 9, 2009 3:09 PM
    Moderator