Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

SQL Server has encountered 1 occurrence of I/O Requests taking longer than 15 seconds Expand / Collapse
Author
Message
Posted Tuesday, March 25, 2014 8:43 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 20, 2014 5:08 PM
Points: 188, Visits: 563
Hello,

we had this problem when sql server did backup of database and third(production) database showed this error and restart sql service.
It was one times and another backups are ok without problem from this time.

I found in our monitoring system Latches wait times in this time.

Next I checked this link http://support.microsoft.com/kb/2137408

When I was thinking about this, it is possible that somebody executed very poor query and during this backup the disk fall down ?
or it is possible that Database Collection caused this ? I have set for DiskUsage, Query statistics and Server Activity. For example that this job for collection was executed in same time like database backup and wrote to disk in same time...( I have MDW Database on same disk )

Because we have externist consultant and he said that probably caused one job which is execute one per minute, but I am not sure, because this job is almost without logical reads and scan very small table ( tracking table, without add rows = just update ) .
Next is that we have more worst jobs, which have more logical reads and executed every 30sec. So I dont think that it caused this jobs, but for sure we turned off..

And why sql service was restarted ? I found this problem because I wanted select statistics for report and it was restarted.


in error log i found just this
2014-03-21 18:07:43.41 spid8s SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [G:\USERDB\MCS_MESDB_1.ldf] in database [MCS_MESDB] (8). The OS file handle is 0x0000000000000D08. The offset of the latest long I/O is: 0x000002e70cb000


Thanks a lot,
Post #1554747
Posted Tuesday, March 25, 2014 11:02 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:56 AM
Points: 2,603, Visits: 2,061
check the code of job and study execution plan which will help you. Also ask your storage team to find out the details of disk issue at specific point of time or any other issue related to I/O.

HTH


---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
Post #1554767
Posted Tuesday, March 25, 2014 11:14 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, November 23, 2014 4:20 AM
Points: 215, Visits: 877
I'm not an expert in this area but the first thing I would do is suspect there was something wrong with the IO subsystem. I'd run SQLIOSIM to apply a load to see if it fails.
Post #1554772
Posted Wednesday, March 26, 2014 12:43 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 20, 2014 5:08 PM
Points: 188, Visits: 563
thanks for response, I created this job so I really know that it couldnt be cause...

Next point
- I checked that was full RAM - it was full about 3days before I think

is just my opinion, that during backup was some IO operation and somebody or program executed poor query and it had to use DISK instead RAM, and there can be problem, that DISK was not response>> lot of IO and disk couldnt handle ...what do you think ?

But still I dont know why sql service was restarted,, it is possible that it was not responded during this situation and it was automatically restart? Any experience with this?
Post #1554788
Posted Wednesday, March 26, 2014 10:51 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, November 21, 2014 9:08 AM
Points: 1,194, Visits: 2,228
That error shouldn't cause SQL Server restart. Could be a problem with the I\O Subsystem. Did you check for the SQL Server dump file and analyze it ? You need to dig deeper to find the issue. Also check the Application and System logs too.

--
SQLBuddy
Post #1555086
Posted Wednesday, March 26, 2014 3:32 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 20, 2014 5:08 PM
Points: 188, Visits: 563
sqlbuddy123 (3/26/2014)
That error shouldn't cause SQL Server restart. Could be a problem with the I\O Subsystem. Did you check for the SQL Server dump file and analyze it ? You need to dig deeper to find the issue. Also check the Application and System logs too.

--
SQLBuddy


I checked all SQL logs and I found just this error. But nothing why the sql service was restarted..
Post #1555190
Posted Wednesday, March 26, 2014 3:46 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, November 21, 2014 9:08 AM
Points: 1,194, Visits: 2,228
Check the Windows Application and System logs in the Event Veiwer ( Control Panel --> Administrative Tools ).

Also check if SQL Server dump file has been created during the SQL Server restart. It's a .mdmp file found in SQL Server Log folder like

C:\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log

--
SQLBuddy

Post #1555197
Posted Wednesday, March 26, 2014 5:12 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 20, 2014 5:08 PM
Points: 188, Visits: 563
sqlbuddy123 (3/26/2014)
Check the Windows Application and System logs in the Event Veiwer ( Control Panel --> Administrative Tools ).
--
SQLBuddy



From this query below I found create date of tempdb - last restart service - 2014-03-21 18:09:43.863
SELECT create_date FROM sys.databases WHERE name = 'tempdb'


Event Viewer I checked also

this is from Application

21. 3. 2014 18:07:21 Log was backed up. Database: DBNAME, creation date(time): 2014/01/03(18:23:26), first LSN: 48073:10596:1, last LSN: 48079:25339:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'TDPSQL-00000C78-0000'}). This is an informational message only. No user action is required.

21. 3. 2014 18:07:43 SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [G:\USERDB\MCS_MESDB_1.ldf] in database [MCS_MESDB] (8). The OS file handle is 0x0000000000000D08. The offset of the latest long I/O is: 0x000002e70cb000

21. 3. 2014 18:08:31 SQLVDI: Loc=SignalAbort. Desc=Client initiates abort. ErrorCode=(0). Process=3192. Thread=10908. Client. Instance=. VD=Global\TDPSQL-00000C78-0000_SQLVDIMemoryName_0.

21. 3. 2014 18:08:41 Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install.



And Now I am seeing that I forgot System log, but time is after the problem, but maybe was problem with failoverclustering and it caused restart service,,, is it possible?

21. 3. 2014 18:08:39 Cluster resource 'SQL Server' of type 'SQL Server' in clustered role 'SQL Server (MSSQLSERVER)' failed.
Based on the failure policies for the resource and role, the cluster service may try to bring the resource online on this node or move the group to another node of the cluster and then restart it. Check the resource and group state using Failover Cluster Manager or the Get-ClusterResource Windows PowerShell cmdlet.

21. 3. 2014 18:08:54 Cluster resource 'MES-SQL-CL' of type 'SQL Server Availability Group' in clustered role 'MES-SQL-CL' failed.
Based on the failure policies for the resource and role, the cluster service may try to bring the resource online on this node or move the group to another node of the cluster and then restart it. Check the resource and group state using Failover Cluster Manager or the Get-ClusterResource Windows PowerShell cmdlet.

21. 3. 2014 18:09:22 A timeout (30000 milliseconds) was reached while waiting for a transaction response from the MSSQLSERVER service.
21. 3. 2014 18:09:26 Reset to device, \Device\RaidPort2, was issued.
21. 3. 2014 18:09:26 Cluster resource 'MES-SQL-CL' of type 'SQL Server Availability Group' in clustered role 'MES-SQL-CL' failed.
Based on the failure policies for the resource and role, the cluster service may try to bring the resource online on this node or move the group to another node of the cluster and then restart it. Check the resource and group state using Failover Cluster Manager or the Get-ClusterResource Windows PowerShell cmdlet.

21. 3. 2014 18:09:26 The Cluster service failed to bring clustered service or application 'MES-SQL-CL' completely online or offline. One or more resources may be in a failed state. This may impact the availability of the clustered service or application.

21. 3. 2014 18:09:26 Clustered role 'MES-SQL-CL' has exceeded its failover threshold. It has exhausted the configured number of failover attempts within the failover period of time allotted to it and will be left in a failed state. No additional attempts will be made to bring the role online or fail it over to another node in the cluster. Please check the events associated with the failure. After the issues causing the failure are resolved the role can be brought online manually or the cluster may attempt to bring it online again after the restart delay period.

21. 3. 2014 18:09:37 The SQL Server (MSSQLSERVER) service terminated unexpectedly. It has done this 1 time(s).
21. 3. 2014 18:09:39 The SQL Server (MSSQLSERVER) service entered the running state.
Post #1555218
Posted Wednesday, March 26, 2014 5:25 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 20, 2014 5:08 PM
Points: 188, Visits: 563
sqlbuddy123 (3/26/2014)


Also check if SQL Server dump file has been created during the SQL Server restart. It's a .mdmp file found in SQL Server Log folder like

C:\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log

--
SQLBuddy



no dump file was created, just error log, AlwaysOn_health and FDLAUNCHERRORLOG
Post #1555221
Posted Wednesday, March 26, 2014 5:40 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, November 21, 2014 9:08 AM
Points: 1,194, Visits: 2,228
tony28 (3/26/2014)
And Now I am seeing that I forgot System log, but time is after the problem, but maybe was problem with failoverclustering and it caused restart service,,, is it possible?


Good that you didn't have a SQL Server dump file. And basically SQL Error Log entries are logged in Application log too.

Are you using Clustering with Always ON or just Always ON ?

First find the exact stop time of the SQL services from the error log. Note that stop time and check a bunch of log entries before that time.

Looks like we can find some clue from the system log. The system log you posted is not enough. It's better to go back a little bit. Can you post all the system log entries on that day before the SQL Server failure ? You can attach it as a text file.

Also check the same on the SQL\Application log file too i.e all the events before the SQL Server stop time (on that day).

--
SQLBuddy


--
SQLBuddy

Post #1555226
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse