Microsoft sql server error 9001

I have SQL 2012 database on availability group environment
  • Remove From My Forums
  • Question

  • I have SQL 2012 database on availability group environment

    Due to lack of space on SQL log partition. I have added to new hard drive configures on RAID1

    Moved the ldf files from active partition to newly created partition.

    SMS change the path for log drive to new drive

    Restart SQL server services

    I cannot connect to databases anymore and I have also lost all setting of Availability group

    Error message

     

    Event logs for Application

    Log Name:      Application
    Source:        MSSQLSERVER
    Date:          16/07/2015 8:32:22 a.m.
    Event ID:      5123
    Task Category: Server
    Level:         Error
    Keywords:      Classic
    User:          N/A
    Computer:      ***.Local
    Description:
    CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file ‘D:SQL Logstemplog.ldf’.
    Event Xml:
    <Event xmlns=»http://schemas.microsoft.com/win/2004/08/events/event»>
      <System>
        <Provider Name=»MSSQLSERVER» />
        <EventID Qualifiers=»49152″>5123</EventID>
        <Level>2</Level>
        <Task>2</Task>
        <Keywords>0x80000000000000</Keywords>
        <TimeCreated SystemTime=»2015-07-15T20:32:22.000000000Z» />
        <EventRecordID>368973</EventRecordID>
        <Channel>Application</Channel>
        <Computer>****.Local</Computer>
        <Security />
      </System>
      <EventData>
        <Data>3(The system cannot find the path specified.)</Data>
        <Data>D:SQL Logstemplog.ldf</Data>
        <Binary>03140000100000000B0000004E005A00490054005300530051004C0030003400000000000000</Binary>
      </EventData>
    </Event>

    New location for D:SQL Logstemplog.ldf is into L:SQL Logstemplog.ldf


    Muhammad Mehdi

    • Edited by

      Wednesday, July 15, 2015 8:50 PM
      More information

Answers

  • looks like you forgot to alter the database files for tempdb and it is looking for the file in the old location..

    heres what you can to fix it

    1. start sql server in minimal configuration mode — i.s in the configuration manager — go to advanced start up parameters and add -f to it

    2. start the sql server and in the ssms — ALter database tempdb MODIFY FILE ( NAME = templog, FILENAME = ‘L:SQL
    Logstemplog.ld
    f’ )

    3. remove the paraameter added in step 1 and restart . it shoulf work.

    or follow this article..

    https://www.xtivia.com/start-sql-server-lost-tempdb-data-files/


    Hope it Helps!!

    • Marked as answer by
      MM from AUS
      Thursday, July 16, 2015 8:54 PM

Let’s take a closer look at SQL Server Fatal Error 9001 and some fixes available for the error. At Bobcares, with our Server Management Services, we can handle your SQL Server issues.

What Is An SQL Server Fatal Error 9001?

When the SQL server fails to open the database for long enough for the backup to be successfully taken, SQL backup error 9001 happens. If the AutoClose property on the database is ON, the database will automatically close when there is no activity. As a result, this error may occur if the database closes abruptly during the backup.

sql server fatal error 9001

Corrupt databases or log files, a large SQL log file that requires a lot of storage space, and hardware problems are a few additional causes of this error. To get more details on the error, we can run the DBCC CHECKDB‘dbname’.

How To Fix SQL Server Fatal Error 9001?

Let’s discuss some of the following methods to fix the error.

  • Turn off Auto Close if it’s currently set to on. With no action throughout the backup process, this will stop the database from closing.
    alter database [database_name] set AUTO_CLOSE OFF;
  • If there is a storage issue, use DBCC CHECKDB.
    dbcc checkdb('database_name')
  • If the error occurs due to the corruption of the SQL database or log file, then launch Emergency Mode Repair. This will also help in the completion of the backup operation and the repair of the log file. It is not advisable to use this method because it can result in the deletion of some log file sections.
    DBCC CHECKDB (N'database_name', REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS;
  • Set SQL Server Database Offline and Online again.
    alter database [database_name] set offline with rollback immediate;

    and

    alter database [database_name] set online;
  • For non-production instances, restarting the SQL Server is a good solution. Restarting SQL Configuration Manager through the Start menu, Windows Server’s Services, or Cmd via net start and net stop are all options.

[Need help with another issue? We’re happy to help 24/7.]

Conclusion

The SQL server fatal error 9001 occurs when the SQL server is unable to open the database for long enough for a backup to be successfully taken. In this article, we post some of the simple methods from our Tech team to fix the error easily.

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

GET STARTED

Symptoms And Resolution Of SQL Backup Error 9001

While performing the backup of a particular SQL database,the backup process can fail with the occurrence of the following error:

SQL Error 9001

Reasons Behind SQL backup Error 9001

1. The foremost reason responsible for occurrence of SQL backup error 9001 is the inability of SQL server to open the database for a time period in which the backup can be taken successfully. When no activity is registered on the database, the database gets automatically closed in case the AutoClose is turned on. Therefore, abrupt closing of the database mi-backup process can lead to this error.

2. A corrupt database or log file can lead to this error.In case a shared server is being used, then the probability of log file corruption is very high.This can be confirmed by running DBCC CHECKDB‘db_name’on the database.The database check will reveal if the database is corrupted or not.

3. At times, SQL log file takes up a lot of storage space than the actual database.This can result in SQL Error 9001.

4. Hardware issues in the server also play a role in the occurrence of this error.

Manual Methods To Resolve SQL Error 9001

Some of the manual methods that can prove to be helpful in eliminating the SQL Error 9001 are:

(1) Turn Off AutoClose
In case Auto Close is set to on, turn it Off. This will prevent the database from closing in the absence of activity during backup process. This will enable SQL VDI backup
(2) Unmount SQL Server
Reboot the SQL server. Also unmounting and remounting the server where log file resides also prove to be helpful in eliminating the error.
(3) DBCC CHECKDB
Run DBCC CHECKDB if you think it’s a storage issue. The command will generate the reason to be a storage issue in case the true reason behind the error is that.
(4) Emergency Mode Repair
Run Emergency Mode Repair in case the SQL database or log file has got corrupted.This will help in repairing the log file and completion of the backup process.

But this process is usually not recommended as it may end up in deleting some parts of the log file. It should be opted as the last resort to fight the SQL Error 9001.

Thus, with the application of above solutions, SQL backup Error 9001 can be successfully removed from the SQL database.

I have encountered this situation on one of our production server.
The database came backup and DBCC Check reported no errors.
Also, there were no other errors logged in Event Viewer.

I know I am behind 4 CU’s for 2019 which I will be updating soon.

SQL Server: 58GB SQL Server Memory with plenty remaining for OS; 12core processor, running on SSD.

Database Recovery Model: SIMPLE

Version:
Microsoft SQL Server 2019 (RTM-CU4) (KB4548597) - 15.0.4033.1 (X64) Mar 14 2020 16:10:35 Copyright (C) 2019 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)

The server is heavily loaded when a job runs(consumes more memory) and there is a TDE as well.

I have went through other articles like
Some Errors Are Not What They Seem
However, the error in those scenarios were 1117 (IO disk subsystem).

I am wondering how to really track this error — may be to engage MS Support guys as there is very little information on this and goes case by case!!
In our case , is it Memory? or TDE? or some corruption in log? Oh SQL!!

Log Details:

11/27/2020 05:55:57,spid67,Unknown,DBCC CHECKDB (DB1) WITH all_errormsgs<c/> no_infomsgs executed by domainSqlAgt_Svc found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 1 seconds.  Internal database snapshot has split point LSN = 000002c3:00002e50:0001 and first LSN = 000002c3:00002e40:0001.
11/27/2020 05:25:38,spid75,Unknown,DBCC TRACEOFF 3604<c/> server process ID (SPID) 75. This is an informational message only; no user action is required.
11/27/2020 05:25:38,spid75,Unknown,DBCC TRACEON 3604<c/> server process ID (SPID) 75. This is an informational message only; no user action is required.
11/27/2020 05:04:02,spid54s,Unknown,Parallel redo is shutdown for database 'DB2' with worker pool size [6].
11/27/2020 05:04:02,spid54s,Unknown,Recovery completed for database DB2 (database ID 5) in 3 second(s) (analysis 1117 ms<c/> redo 308 ms<c/> undo 108 ms [system undo 0 ms<c/> regular undo 100 ms].) This is an informational message only. No user action is required.
11/27/2020 05:04:02,spid70s,Unknown,CHECKDB for database 'DB2' finished without errors on 2020-11-22 20:00:05.017 (local time). This is an informational message only; no user action is required.
11/27/2020 05:04:02,spid54s,Unknown,Recovery is writing a checkpoint in database 'DB2' (5). This is an informational message only. No user action is required.
11/27/2020 05:04:02,spid54s,Unknown,12 transactions rolled back in database 'DB2' (5:0). This is an informational message only. No user action is required.
11/27/2020 05:04:02,spid70s,Unknown,Recovery of database 'DB2' (5) is 12% complete (approximately 11 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required.
11/27/2020 05:04:02,spid70s,Unknown,10 transactions rolled forward in database 'DB2' (5:0). This is an informational message only. No user action is required.
11/27/2020 05:04:02,spid70s,Unknown,Recovery of database 'DB2' (5) is 12% complete (approximately 11 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
11/27/2020 05:04:02,spid70s,Unknown,Recovery of database 'DB2' (5) is 3% complete (approximately 34 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
11/27/2020 05:04:00,spid70s,Unknown,Parallel redo is started for database 'DB2' with worker pool size [6].
11/27/2020 05:04:00,spid70s,Unknown,Starting up database 'DB2'.
11/27/2020 05:03:52,Logon,Unknown,Login failed for user 'sql_user'. Reason: Failed to open the explicitly specified database 'DB2'. [CLIENT: 10.1.0.116]
11/27/2020 05:03:52,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 38.
11/27/2020 05:03:48,spid68,Unknown,During undoing of a logged operation in database 'DB2' (page (0:0) if any)<c/> an error occurred at log record ID (11603:2070928:1). Typically<c/> the specific failure is logged previously as an error in the operating system error log. Restore the database or file from a backup<c/> or repair the database.
11/27/2020 05:03:48,spid68,Unknown,Error: 3314<c/> Severity: 21<c/> State: 5.
11/27/2020 05:03:48,spid68,Unknown,During undoing of a logged operation in database 'DB2' (page (0:0) if any)<c/> an error occurred at log record ID (11603:2070928:25). Typically<c/> the specific failure is logged previously as an error in the operating system error log. Restore the database or file from a backup<c/> or repair the database.
11/27/2020 05:03:48,spid68,Unknown,Error: 3314<c/> Severity: 21<c/> State: 3.
11/27/2020 05:03:48,spid68,Unknown,The log for database 'DB2' is not available. Check the operating system error log for related error messages. Resolve any errors and restart the database.
11/27/2020 05:03:48,spid68,Unknown,Error: 9001<c/> Severity: 21<c/> State: 5.
11/27/2020 05:03:48,spid68,Unknown,Error during rollback. shutting down database (location: 1).
11/27/2020 05:03:48,spid68,Unknown,During undoing of a logged operation in database 'DB2' (page (0:0) if any)<c/> an error occurred at log record ID (11603:2070928:25). Typically<c/> the specific failure is logged previously as an error in the operating system error log. Restore the database or file from a backup<c/> or repair the database.
11/27/2020 05:03:48,spid68,Unknown,Error: 3314<c/> Severity: 21<c/> State: 3.
11/27/2020 05:03:48,spid68,Unknown,The log for database 'DB2' is not available. Check the operating system error log for related error messages. Resolve any errors and restart the database.
11/27/2020 05:03:48,spid68,Unknown,Error: 9001<c/> Severity: 21<c/> State: 5.
11/27/2020 05:03:48,spid68,Unknown,During undoing of a logged operation in database 'DB2' (page (0:0) if any)<c/> an error occurred at log record ID (11603:2070928:15). Typically<c/> the specific failure is logged previously as an error in the operating system error log. Restore the database or file from a backup<c/> or repair the database.
11/27/2020 05:03:48,spid68,Unknown,Error: 3314<c/> Severity: 21<c/> State: 2.
11/27/2020 05:03:48,spid68,Unknown,During undoing of a logged operation in database 'DB2' (page (0:0) if any)<c/> an error occurred at log record ID (11603:2070928:15). Typically<c/> the specific failure is logged previously as an error in the operating system error log. Restore the database or file from a backup<c/> or repair the database.
11/27/2020 05:03:48,spid68,Unknown,Error: 3314<c/> Severity: 21<c/> State: 3.
11/27/2020 05:03:48,spid68,Unknown,The log for database 'DB2' is not available. Check the operating system error log for related error messages. Resolve any errors and restart the database.
11/27/2020 05:03:48,spid68,Unknown,Error: 9001<c/> Severity: 21<c/> State: 5.
11/27/2020 05:03:48,spid68,Unknown,During undoing of a logged operation in database 'DB2' (page (0:0) if any)<c/> an error occurred at log record ID (11603:2070928:31). Typically<c/> the specific failure is logged previously as an error in the operating system error log. Restore the database or file from a backup<c/> or repair the database.
11/27/2020 05:03:48,spid68,Unknown,Error: 3314<c/> Severity: 21<c/> State: 2.
11/27/2020 05:03:48,spid68,Unknown,During undoing of a logged operation in database 'DB2' (page (0:0) if any)<c/> an error occurred at log record ID (11603:2070928:31). Typically<c/> the specific failure is logged previously as an error in the operating system error log. Restore the database or file from a backup<c/> or repair the database.
11/27/2020 05:03:48,spid68,Unknown,Error: 3314<c/> Severity: 21<c/> State: 3.
11/27/2020 05:03:48,spid68,Unknown,The log for database 'DB2' is not available. Check the operating system error log for related error messages. Resolve any errors and restart the database.
11/27/2020 05:03:48,spid68,Unknown,Error: 9001<c/> Severity: 21<c/> State: 5.
11/27/2020 05:03:48,spid68,Unknown,During undoing of a logged operation in database 'DB2' (page (0:0) if any)<c/> an error occurred at log record ID (11603:2070928:33). Typically<c/> the specific failure is logged previously as an error in the operating system error log. Restore the database or file from a backup<c/> or repair the database.
11/27/2020 05:03:48,spid68,Unknown,Error: 3314<c/> Severity: 21<c/> State: 2.
11/27/2020 05:03:48,spid68,Unknown,During undoing of a logged operation in database 'DB2' (page (0:0) if any)<c/> an error occurred at log record ID (11603:2070928:33). Typically<c/> the specific failure is logged previously as an error in the operating system error log. Restore the database or file from a backup<c/> or repair the database.
11/27/2020 05:03:48,spid68,Unknown,Error: 3314<c/> Severity: 21<c/> State: 3.
11/27/2020 05:03:48,spid68,Unknown,The log for database 'DB2' is not available. Check the operating system error log for related error messages. Resolve any errors and restart the database.
11/27/2020 05:03:48,spid68,Unknown,Error: 9001<c/> Severity: 21<c/> State: 5.
11/27/2020 05:03:48,spid68,Unknown,During undoing of a logged operation in database 'DB2' (page (0:0) if any)<c/> an error occurred at log record ID (11603:2070928:30). Typically<c/> the specific failure is logged previously as an error in the operating system error log. Restore the database or file from a backup<c/> or repair the database.
11/27/2020 05:03:48,spid68,Unknown,Error: 3314<c/> Severity: 21<c/> State: 2.
11/27/2020 05:03:48,spid68,Unknown,During undoing of a logged operation in database 'DB2' (page (0:0) if any)<c/> an error occurred at log record ID (11603:2070928:34). Typically<c/> the specific failure is logged previously as an error in the operating system error log. Restore the database or file from a backup<c/> or repair the database.
11/27/2020 05:03:48,spid68,Unknown,Error: 3314<c/> Severity: 21<c/> State: 2.
11/27/2020 05:03:48,spid68,Unknown,During undoing of a logged operation in database 'DB2' (page (0:0) if any)<c/> an error occurred at log record ID (11603:2070928:32). Typically<c/> the specific failure is logged previously as an error in the operating system error log. Restore the database or file from a backup<c/> or repair the database.
11/27/2020 05:03:48,spid68,Unknown,Error: 3314<c/> Severity: 21<c/> State: 2.
11/27/2020 05:03:48,spid68,Unknown,During undoing of a logged operation in database 'DB2' (page (0:0) if any)<c/> an error occurred at log record ID (11603:2070928:29). Typically<c/> the specific failure is logged previously as an error in the operating system error log. Restore the database or file from a backup<c/> or repair the database.
11/27/2020 05:03:48,spid68,Unknown,Error: 3314<c/> Severity: 21<c/> State: 2.
11/27/2020 05:03:48,spid68,Unknown,During undoing of a logged operation in database 'DB2' (page (0:0) if any)<c/> an error occurred at log record ID (11603:2070928:28). Typically<c/> the specific failure is logged previously as an error in the operating system error log. Restore the database or file from a backup<c/> or repair the database.
11/27/2020 05:03:48,spid68,Unknown,Error: 3314<c/> Severity: 21<c/> State: 2.
11/27/2020 05:03:48,spid68,Unknown,During undoing of a logged operation in database 'DB2' (page (0:0) if any)<c/> an error occurred at log record ID (11603:2070928:27). Typically<c/> the specific failure is logged previously as an error in the operating system error log. Restore the database or file from a backup<c/> or repair the database.
11/27/2020 05:03:48,spid68,Unknown,Error: 3314<c/> Severity: 21<c/> State: 2.
11/27/2020 05:03:48,spid68,Unknown,During undoing of a logged operation in database 'DB2' (page (0:0) if any)<c/> an error occurred at log record ID (11603:2070928:30). Typically<c/> the specific failure is logged previously as an error in the operating system error log. Restore the database or file from a backup<c/> or repair the database.
11/27/2020 05:03:48,spid68,Unknown,Error: 3314<c/> Severity: 21<c/> State: 3.
11/27/2020 05:03:48,spid68,Unknown,During undoing of a logged operation in database 'DB2' (page (0:0) if any)<c/> an error occurred at log record ID (11603:2070928:34). Typically<c/> the specific failure is logged previously as an error in the operating system error log. Restore the database or file from a backup<c/> or repair the database.
11/27/2020 05:03:48,spid68,Unknown,Error: 3314<c/> Severity: 21<c/> State: 3.
11/27/2020 05:03:48,spid68,Unknown,During undoing of a logged operation in database 'DB2' (page (0:0) if any)<c/> an error occurred at log record ID (11603:2070928:32). Typically<c/> the specific failure is logged previously as an error in the operating system error log. Restore the database or file from a backup<c/> or repair the database.
11/27/2020 05:03:48,spid68,Unknown,Error: 3314<c/> Severity: 21<c/> State: 3.
11/27/2020 05:03:48,spid68,Unknown,During undoing of a logged operation in database 'DB2' (page (0:0) if any)<c/> an error occurred at log record ID (11603:2070928:29). Typically<c/> the specific failure is logged previously as an error in the operating system error log. Restore the database or file from a backup<c/> or repair the database.
11/27/2020 05:03:48,spid68,Unknown,Error: 3314<c/> Severity: 21<c/> State: 3.
11/27/2020 05:03:48,spid68,Unknown,During undoing of a logged operation in database 'DB2' (page (0:0) if any)<c/> an error occurred at log record ID (11603:2070928:28). Typically<c/> the specific failure is logged previously as an error in the operating system error log. Restore the database or file from a backup<c/> or repair the database.
11/27/2020 05:03:48,spid68,Unknown,Error: 3314<c/> Severity: 21<c/> State: 3.
11/27/2020 05:03:48,spid68,Unknown,During undoing of a logged operation in database 'DB2' (page (0:0) if any)<c/> an error occurred at log record ID (11603:2070928:27). Typically<c/> the specific failure is logged previously as an error in the operating system error log. Restore the database or file from a backup<c/> or repair the database.
11/27/2020 05:03:48,spid68,Unknown,Error: 3314<c/> Severity: 21<c/> State: 3.
11/27/2020 05:03:48,spid68,Unknown,The log for database 'DB2' is not available. Check the operating system error log for related error messages. Resolve any errors and restart the database.
11/27/2020 05:03:48,spid68,Unknown,Error: 9001<c/> Severity: 21<c/> State: 5.
11/27/2020 05:03:48,spid68,Unknown,The log for database 'DB2' is not available. Check the operating system error log for related error messages. Resolve any errors and restart the database.
11/27/2020 05:03:48,spid68,Unknown,Error: 9001<c/> Severity: 21<c/> State: 5.
11/27/2020 05:03:48,spid68,Unknown,The log for database 'DB2' is not available. Check the operating system error log for related error messages. Resolve any errors and restart the database.
11/27/2020 05:03:48,spid68,Unknown,Error: 9001<c/> Severity: 21<c/> State: 5.
11/27/2020 05:03:48,spid68,Unknown,The log for database 'DB2' is not available. Check the operating system error log for related error messages. Resolve any errors and restart the database.
11/27/2020 05:03:48,spid68,Unknown,Error: 9001<c/> Severity: 21<c/> State: 5.
11/27/2020 05:03:48,spid68,Unknown,The log for database 'DB2' is not available. Check the operating system error log for related error messages. Resolve any errors and restart the database.
11/27/2020 05:03:48,spid68,Unknown,Error: 9001<c/> Severity: 21<c/> State: 5.
11/27/2020 05:03:48,spid68,Unknown,The log for database 'DB2' is not available. Check the operating system error log for related error messages. Resolve any errors and restart the database.
11/27/2020 05:03:48,spid68,Unknown,Error: 9001<c/> Severity: 21<c/> State: 5.
11/27/2020 05:03:48,spid30s,Unknown,Database DB2 was shutdown due to error 9001 in routine 'XdesRMFull::CommitInternal'. Restart for non-snapshot databases will be attempted after all connections to the database are aborted.
11/27/2020 05:03:48,spid68,Unknown,The log for database 'DB2' is not available. Check the operating system error log for related error messages. Resolve any errors and restart the database.
11/27/2020 05:03:48,spid68,Unknown,Error: 9001<c/> Severity: 21<c/> State: 16.
11/27/2020 05:03:48,spid68,Unknown,The log for database 'DB2' is not available. Check the operating system error log for related error messages. Resolve any errors and restart the database.
11/27/2020 05:03:48,spid68,Unknown,Error: 9001<c/> Severity: 21<c/> State: 16.
11/27/2020 05:03:48,spid30s,Unknown,The log for database 'DB2' is not available. Check the operating system error log for related error messages. Resolve any errors and restart the database.
11/27/2020 05:03:48,spid30s,Unknown,Error: 9001<c/> Severity: 21<c/> State: 4.
11/27/2020 05:03:48,spid69s,Unknown,There is insufficient system memory in resource pool 'internal' to run this query.
11/27/2020 05:03:48,spid69s,Unknown,Error: 701<c/> Severity: 17<c/> State: 123.
11/27/2020 05:03:48,spid8s,Unknown,An error occurred while processing log encryption. The process was recovered automatically. No user action is required.
11/27/2020 05:03:48,spid8s,Unknown,Lock request time out period exceeded.
11/27/2020 05:03:48,spid8s,Unknown,Error: 1222<c/> Severity: 16<c/> State: 55.
11/27/2020 00:00:17,spid44s,Unknown,This instance of SQL Server has been using a process ID of 5096 since 11/21/2020 7:42:00 PM (local) 11/22/2020 1:42:00 AM (UTC). This is an informational message only; no user action is required.
11/26/2020 00:00:07,spid67s,Unknown,This instance of SQL Server has been using a process ID of 5096 since 11/21/2020 7:42:00 PM (local) 11/22/2020 1:42:00 AM (UTC). This is an informational message only; no user action is required.
11/25/2020 11:46:46,spid60,Unknown,DBCC TRACEOFF 3604<c/> server process ID (SPID) 60. This is an informational message only; no user action is required.
11/25/2020 11:46:46,spid60,Unknown,DBCC TRACEON 3604<c/> server process ID (SPID) 60. This is an informational message only; no user action is required.
11/25/2020 00:00:02,spid27s,Unknown,This instance of SQL Server has been using a process ID of 5096 since 11/21/2020 7:42:00 PM (local) 11/22/2020 1:42:00 AM (UTC). This is an informational message only; no user action is required.

[EDIT-1]

At the time of error, Ola Hallengren Index Optimize job was running and has reported the following!!

Database context: [DB2] [SQLSTATE 01000]
Command: UPDATE STATISTICS [schema1].[table1] [_WA_Sys_00000009_08843BF2] [SQLSTATE 01000]
Comment: ObjectType: Table, IndexType: Column, Incremental: N, RowCount: 13228484, ModificationCounter: 215943 [SQLSTATE 01000]
Outcome: Succeeded [SQLSTATE 01000]
Duration: 00:00:00 [SQLSTATE 01000]
Date and time: 2020-11-27 05:03:43 [SQLSTATE 01000]
[SQLSTATE 01000]
Date and time: 2020-11-27 05:03:45 [SQLSTATE 01000]
Database context: [DB2] [SQLSTATE 01000]
Command: ALTER INDEX [index1] ON [schema1].[table1] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = OFF) [SQLSTATE 01000]
Comment: ObjectType: Table, IndexType: NonClustered, ImageText: No, NewLOB: No, FileStream: No, ColumnStore: No, AllowPageLocks: Yes, PageCount: 580560, Fragmentation: 95.1018 [SQLSTATE 01000]
Msg 0, Sev 0, State 1: Unspecified error occurred on SQL Server. Connection may have been terminated by the server. [SQLSTATE HY000]
Msg 596, Sev 21, State 1: Cannot continue the execution because the session is in the kill state. [SQLSTATE HY000]

[EDIT-2]

Worked with IT team and it seems like nothing was wrong at the time of error. Unable to find the actual root cause.

  1. Disk checks were good
  2. No 3rd party tool was taking backup or snapshots at that time
  3. Log Drive did not disconnect or there is no such info in the logs
  4. No errors on VM Host
  5. No OS reboot or SQL Service restarts

Edit-3

Installed latest Cumulative Update (CU8) on 5th and still we get the below every other day. (Got it on 7th, 8th so far)

spids
Date        12/8/2020 8:32:59 AM Error: 1222, Severity: 16, State: 55.
Date        12/8/2020 8:32:59 AM Lock request time out period exceeded
Date        12/8/2020 8:32:59 AM An error occurred while processing log encryption. The process was recovered automatically. No user action is required

Понравилась статья? Поделить с друзьями: