June 11, 2013 at 7:21 am
Hello, we have an issue today and we have to restart our sql server (restart the server not the sql process)
we have sql server 2008 enterprise
What we noticed?
we noticed that all transactions over a table were very slow, a lot of locks, poor performance etc...
when we try to kill one trasaction, it keeps on rollback state, never finish.
so we try to shutdown sql server process, but never finish (i think because of rollback), so we restart windows and everythigs starts working again
What I check?
I check windows events, nothing
but in sql server logs we find this
I/O requests taking longer than 15 seconds to complete
I don't know what could be the cause of this,
any help will be grateful
Fecha,Origen,Gravedad,Mensaje
06/11/2013 09:01:01,Servidor,Desconocido,SQL Server is terminating because of a system shutdown. This is an informational message only. No user action is required.
06/11/2013 09:00:03,spid5s,Desconocido,SQL Server has encountered 11 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\tempdb.mdf] in database [tempdb] (2). The OS file handle is 0x0000000000000558. The offset of the latest long I/O is: 0x000000000a0000
06/11/2013 08:59:42,spid6s,Desconocido,Tiempo de espera agotado para el tipo de bloqueo temporal del búfer 2 de la página (1:9)<c/> id. de base de datos 2.
06/11/2013 08:59:42,spid6s,Desconocido,Error: 845<c/> gravedad: 17<c/> estado: 1.
06/11/2013 08:59:42,spid6s,Desconocido,A time-out occurred while waiting for buffer latch -- type 2<c/> bp 000000041EFE8080<c/> page 1:9<c/> stat 0x3c0040d<c/> database id: 2<c/> allocation unit Id: 72145324650528768<c/> task 0x0000000004286508 : 0<c/> waittime 300<c/> flags 0x100000003a<c/> owning task 0x000000000040E748. Not continuing to wait.
06/11/2013 08:58:16,spid21s,Desconocido,A time-out occurred while waiting for buffer latch -- type 2<c/> bp 000000015DFF0C80<c/> page 1:77<c/> stat 0x3c0040d<c/> database id: 2<c/> allocation unit Id: 536870912/281475513581568<c/> task 0x00000000007ADB88 : 0<c/> waittime 300<c/> flags 0x3a<c/> owning task 0x00000000007ADB88. Not continuing to wait.
06/11/2013 08:55:28,spid72,Desconocido,A time-out occurred while waiting for buffer latch -- type 4<c/> bp 000000010CFCE280<c/> page 1:116<c/> stat 0x3c0040d<c/> database id: 2<c/> allocation unit Id: 72057594574798848<c/> task 0x000000043BD182C8 : 0<c/> waittime 300<c/> flags 0x3a<c/> owning task 0x00000000007AD4C8. Not continuing to wait.
06/11/2013 08:54:41,spid51,Desconocido,FILESTREAM: effective level = 0<c/> configured level = 0<c/> file system access share name = 'MSSQLSERVER'.
06/11/2013 08:54:41,spid51,Desconocido,Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install.
06/11/2013 08:53:16,spid21s,Desconocido,A time-out occurred while waiting for buffer latch -- type 2<c/> bp 000000015DFF0C80<c/> page 1:77<c/> stat 0x3c0040d<c/> database id: 2<c/> allocation unit Id: 536870912/281475513581568<c/> task 0x00000000007ADB88 : 0<c/> waittime 300<c/> flags 0x3a<c/> owning task 0x00000000007ADB88. Not continuing to wait.
06/11/2013 08:48:16,spid21s,Desconocido,A time-out occurred while waiting for buffer latch -- type 2<c/> bp 000000015DFF0C80<c/> page 1:77<c/> stat 0x3c0040d<c/> database id: 2<c/> allocation unit Id: 536870912/281475513581568<c/> task 0x00000000007ADB88 : 0<c/> waittime 300<c/> flags 0x3a<c/> owning task 0x00000000007ADB88. Not continuing to wait.
06/11/2013 08:47:20,spid86,Desconocido,Process ID 66 was killed by hostname PC-SISTEMAS2<c/> host process ID 5140.
06/11/2013 08:45:57,spid86,Desconocido,Process ID 99 was killed by hostname PC-SISTEMAS2<c/> host process ID 5140.
06/11/2013 08:43:16,spid21s,Desconocido,A time-out occurred while waiting for buffer latch -- type 2<c/> bp 000000015DFF0C80<c/> page 1:77<c/> stat 0x3c0040d<c/> database id: 2<c/> allocation unit Id: 536870912/281475513581568<c/> task 0x00000000007ADB88 : 0<c/> waittime 300<c/> flags 0x3a<c/> owning task 0x00000000007ADB88. Not continuing to wait.
06/11/2013 08:38:16,spid21s,Desconocido,A time-out occurred while waiting for buffer latch -- type 2<c/> bp 000000015DFF0C80<c/> page 1:77<c/> stat 0x3c0040d<c/> database id: 2<c/> allocation unit Id: 536870912/281475513581568<c/> task 0x00000000007ADB88 : 0<c/> waittime 300<c/> flags 0x3a<c/> owning task 0x00000000007ADB88. Not continuing to wait.
06/11/2013 08:38:13,spid5s,Desconocido,SQL Server has encountered 18 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\tempdb.mdf] in database [tempdb] (2). The OS file handle is 0x0000000000000558. The offset of the latest long I/O is: 0x000001b9240000
06/11/2013 08:33:16,spid21s,Desconocido,A time-out occurred while waiting for buffer latch -- type 2<c/> bp 000000015DFF0C80<c/> page 1:77<c/> stat 0x3c0040d<c/> database id: 2<c/> allocation unit Id: 536870912/281475513581568<c/> task 0x00000000007ADB88 : 0<c/> waittime 300<c/> flags 0x3a<c/> owning task 0x00000000007ADB88. Not continuing to wait.
06/11/2013 08:31:53,spid5s,Desconocido,SQL Server has encountered 12 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\tempdb.mdf] in database [tempdb] (2). The OS file handle is 0x0000000000000558. The offset of the latest long I/O is: 0x000001b2210000
06/11/2013 08:28:16,spid21s,Desconocido,A time-out occurred while waiting for buffer latch -- type 2<c/> bp 000000015DFF0C80<c/> page 1:77<c/> stat 0x3c0040d<c/> database id: 2<c/> allocation unit Id: 536870912/281475513581568<c/> task 0x00000000007ADB88 : 0<c/> waittime 300<c/> flags 0x1a<c/> owning task 0x00000000007ADB88. Not continuing to wait.
06/11/2013 08:27:39,spid12s,Desconocido,The background checkpoint thread has encountered an unrecoverable error. The checkpoint process is terminating so that the thread can clean up its resources. This is an informational message only. No user action is required.
06/11/2013 08:27:39,spid12s,Desconocido,Error: 18400<c/> gravedad: 16<c/> estado: 1.
06/11/2013 08:27:39,spid12s,Desconocido,Tiempo de espera agotado para el tipo de bloqueo temporal del búfer 2 de la página (1:9)<c/> id. de base de datos 2.
06/11/2013 08:27:39,spid12s,Desconocido,Error: 845<c/> gravedad: 17<c/> estado: 1.
06/11/2013 08:27:39,spid12s,Desconocido,A time-out occurred while waiting for buffer latch -- type 2<c/> bp 000000041EFE8080<c/> page 1:9<c/> stat 0x3c0040d<c/> database id: 2<c/> allocation unit Id: 72145324650528768<c/> task 0x000000000040E748 : 0<c/> waittime 300<c/> flags 0x100000001a<c/> owning task 0x000000000040E748. Not continuing to wait.
06/11/2013 08:26:02,spid5s,Desconocido,SQL Server has encountered 9 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\tempdb.mdf] in database [tempdb] (2). The OS file handle is 0x0000000000000558. The offset of the latest long I/O is: 0x000001abad0000
06/11/2013 08:20:32,spid5s,Desconocido,SQL Server has encountered 14 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\tempdb.mdf] in database [tempdb] (2). The OS file handle is 0x0000000000000558. The offset of the latest long I/O is: 0x00000184eb0000
06/11/2013 08:00:13,,Desconocido,Log was backed up. Database: sistema<c/> creation date(time): 2012/08/12(00:40:07)<c/> first LSN: 279474:3117:1<c/> last LSN: 279496:11627:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'I:\LOG\sistema_backup_2013_06_11_080007_0603540.trn'}). This is an informational message only. No user action is required.
edit:
I'm checking this link
http://www.sql-server-pro.com/i-o-requests-taking-longer-than-15-seconds-to-complete.html
I'm investigating if this could be a bad config of sql server or its related to OS (windows server 2008/storage system).
thanks!
June 11, 2013 at 11:28 am
can you provide more detail on your storage subsystem?
I'm guessing the files with slow I\O are not on local disks 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 11, 2013 at 12:11 pm
yes the files are in a storage system.
Sorry for not bring any info about the storage, the reason is i havent access to it.
if you tell me what info helps i will ask the IT people, I only know there is an storage 😀
I think is a storage bottleneck, but I cant test o access to any info/logs of the storage.
thanks in advance
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply