Skip to content

Posts tagged ‘checkdb’

SQL Server database restarting every couple of minutes

ISSUE:

You may face an issue when your database is just restarting every couple of minutes and you can see check DB is running at same frequency under same spid, when you look into the SQL Server error log. But users are able to connect to database without any issues.

Below is the error message that you will find in SQL Server error log stating that database is starting:

Date                         6/6/2013 10:10:43 AM

Log                            SQL Server (Current – 6/6/2013 10:10:00 AM)

Source                     spid69

Message

Starting up database ‘WW_Testing’.

Below is the error message that you will find in SQL Server error log stating that checkdb is running on this database:

Date                         6/6/2013 10:10:43 AM

Log                            SQL Server (Current – 6/6/2013 10:10:00 AM)

Source                     spid69

Message

CHECKDB for database ‘WW_Testing’ finished without errors on 2013-06-01 11:00:24.160 (local time). This is an informational message only; no user action is required.

CAUSE:

Regarding database restart, this will happen because you have set auto close database setting to enabled. So, your database is closed as soon as the last connection to this database is closed, but DB again restarts as soon as a new request to connect to database comes to SQL Server instance.

You can run below query to find if it is enabled or not. If output is 1 then its enabled If 0 then its disabled.

select is_auto_close_on from sys.databases

where name='DBNAME'

Now regarding checkDB running at same frequency, This is not a issue. SQL Server always performs checkDB whenever any database is started. This is to make sure a consistent copy of database is available before coming online. You will notice that this message comes every time after the Starting database message.

RESOLUTION:

Only thing you need to do is to disable database auto close parameter by running below query in master database or by changing that by going to SSMS –> Databases –> DBNAME –> Right Click –> Properties –> Options –> Auto Close and set it to FALSE.

USE [master]

GO

ALTER DATABASE [Test] SET AUTO_CLOSE ON WITH NO_WAIT

GO

What caused this database corruption?

Most database corruption (database consistency errors) comes from hardware issues mainly disk issues. The first place to look is, IO subsystem not behaving well. If there are issues then there it could just be a matter of time before other databases on same drive gets corrupted.

Try looking into the system event log. You may find disk related errors which could be the root cause of your corruption.

You may see disk related errors like:

01/12/2010 19:54:36 Cissesrv Error None 24606 N/A TESTSERVER01 "Logical drive 3 configured on array controller P410i [Embedded] returned a fatal error during a read/write request from/to the volume.
Logical block address 257513711, block count 1024 and command 32 were taken from the failed logical I/O request.
Array controller P410i [Embedded] is also reporting that the last physical drive to report a fatal error condition (associated with this logical request), is located on bus 0 and ID 3. "
01/12/2010 19:54:36 Cissesrv Error None 24606 N/A TESTSERVER01 "Logical drive 3 configured on array controller P410i [Embedded] returned a fatal error during a read/write request from/to the volume.
Logical block address 257513711, block count 1024 and command 32 were taken from the failed logical I/O request.
Array controller P410i [Embedded] is also reporting that the last physical drive to report a fatal error condition (associated with this logical request), is located on bus 0 and ID 3. "
01/12/2010 19:54:18 dmio Warning None 35 N/A TESTSERVER01 dmio: Disk Harddisk2 block 257512687 (mountpoint E:): Uncorrectable read error
01/12/2010 19:54:18 dmio Information None 29 N/A TESTSERVER01 dmio: Harddisk2 read error at block 257512687: status 0xc0000185
01/12/2010 19:54:18 Disk Error None 11 N/A TESTSERVER01 The driver detected a controller error on \Device\Harddisk2. 

If you find any disk related issue, you need to engage your platform team to fix the issue or replace the disk as per error messages. Like, the above error messages leads to a disk failure if no action is taken.