Tuesday, June 11, 2013

[MS SQL Server] I/O requests taking longer than 15 seconds

[MS SQL Server] I/O requests taking longer than 15 seconds

I/O requests taking longer than 15 seconds

Posted: 11 Jun 2013 12:21 AM PDT

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[b]What we noticed?[/b]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[b]What I check?[/b]I check windows events, nothingbut in sql server logs we find this [b]I/O requests taking longer than 15 seconds to complete[/b]I don't know what could be the cause of this, any help will be grateful[code="plain"]Fecha,Origen,Gravedad,Mensaje06/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: 0x000000000a000006/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: 0x000001b924000006/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: 0x000001b221000006/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: 0x000001abad000006/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: 0x00000184eb000006/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.[/code]edit:I'm checking this linkhttp://www.sql-server-pro.com/i-o-requests-taking-longer-than-15-seconds-to-complete.htmlI'm investigating if this could be a bad config of sql server or its related to OS (windows server 2008/storage system).thanks!

How to find out cause of deadlock?

Posted: 11 Jun 2013 04:21 AM PDT

Hi All,2days ago we have a dead lock.how to find out, for what cause the dead lock got occured?

DDL Audit at Instance Level in SQL 2008

Posted: 11 Jun 2013 04:16 AM PDT

Hi Everyone,Can you please guide and correct me if I'm wrong somewhere...We need to capture all DDL events (only) in SQL Server 2008 R2..so as per my understanding I guess we have two of these options as below1) Triggers2) SQL Server Audit specificationNow I further have a requirement to capture all the DDL events both in SQL 2008 and SQL 2005.But in SQL 2005 we don't have Audit specification (not sure), so only option left is triggers.. Please let me know if there are any other options or ways to do this in SQL 2005..?Also is SQL Server Audit Specification the best way to do the DDL Audits in SQL 2008, and what would be the best option then for SQL 2005?Thanks-Nivedita

All Jnr DBA to Create Logins without using the sysadm / securityadmin role

Posted: 11 Jun 2013 01:25 AM PDT

Hi all,I'm looking at a way of allowing a Jnr DBA user to create sql logins and then map those logins to users to what could be one or many databases. Ideally i want to do this without giving them sysadmin rights.If i grant them the server level permission ALTER ANY LOGIN then they are able to create the login but not able to then map the login to a database. I think if i give the Jnr DBA user the ALTER USER permission then this would work but as this is a database level permission i didnt really wont to do this on every database. Any ideas would be much appriciated.Many thanksFrant

SQL Agent permissions model help

Posted: 10 Jun 2013 10:38 PM PDT

Hi there, I've been trying to get some security defined around SQL Agent to a point I'm happy with the controls in place but allowing my developers to do their work without too many restrictions (aka needing my intervention :-D). I've hit an obstacle and I'm not sure of how to get around it, please help! :-)I have three environments - DEV, UAT & PRD. I have granted my developers sysadmin on DEV, no worries there. Moving to UAT, I have granted them SQLAgentOperator role so they have the ability to edit / enable / execute jobs. In PRD, security is stripped back further in that a job needs to be owned by sa to run (i.e. if something gets loaded up, it won't run until I've reviewed it and no one can amend it either). In UAT I have created a proxy account, permissions granted to a database role I've created in msdb, which my developers have been added to, that then allows them to run SSIS jobs (SQL Server doesn't allow you permission to run) by using the runas selection in the jobstep.The issue is around UAT. We generally work in a team, so different people will be working on the same piece of work at different points. The SQLAgentOperator role only allows you to edit jobs in your own name. SQL Server won't allow you to add a group as a job owner and therefore I'm a little stuck, if user A has created a job, then user B wants to edit it, I still need to intervene in order to change the owner (since ownership change is reserved for sysadmin).So how do I get around this? Is this by design? One way I've considered solving this is by granting their secondary ("admin") accounts sysadmin permissions, but ideally I'd like a neater solution.

Automated backup with no Agent i.e. SQL Express. Hide logon details

Posted: 10 Jun 2013 09:42 AM PDT

Hi [url=http://support.microsoft.com/kb/2019698]http://support.microsoft.com/kb/2019698[/url]Shows a SP to backup DB(s) and how to use windows scheduler to achieve this.This would work ok if you could use Windows Authentication, however if the only user you have is an SQL login, the SQLCMD line parameter will contain login credentials i.e. password.I tried creating the backup DB SP with EXECUTE AS but would effectively have to have this on every DB because of reasons explained in [url=http://www.sommarskog.se/grantperm.html]http://www.sommarskog.se/grantperm.html[/url][quote]when impersonating a principal by using the EXECUTE AS USER statement, or within a database-scoped module by using the EXECUTE AS clause, the scope of impersonation is restricted to the database by default. This means that references to objects outside the scope of the database will return an error.[/quote]This could be done with certificates and proxy users - but is OVER my head. It SEEMS difficult to maintain on many client machines. I can only think of creating an EXE (binary code) that is called by Windows Scheduler and connects to the DB Server with backup rights. The Login credentials would be hardcoded but safe in binary form.

SQL 2008R2 Maintenance Plan in a Master/Target Server Environment (MSX/TSX)

Posted: 10 Jun 2013 09:45 PM PDT

Hi,I'm just triialling a situation to see if I can figure out if I can set this up in our SQL Server estate.We have > 180 SQL Servers to maintain, and was hoping I could set up 1 Master server to deploy all the SQ LAgent Jobs to all the remaining servers.There is a mix of SQL 2008R2, SQL 2008 and SQL 2005 (there are a couple of SQL 2000, but I can live with managing these individually).Anyway, I've recreated a basic set up on my laptop. The base install of SQL is SQL2008, then there are 3 more instances installed as well, a SQL 2005, SQL 2008 and SQL2008R2 instances.I can add a SQL agent job and it happily gets populated to all the instances and all seems fine.However, here's my problem. There was a maintenance plan already created for all the instances except the SQL2005 one. When I edit the maintenance plan, and add the instance to the list of servers to deploy to, it issues the message:"Apply to target server failed for job 'MaintenancePlan.Daily (Multi-Server)"I've tried creating a brand new Maintnenace Plan from scratch, using the Wizard and also manually, but still getthe same results.So, now my head is hurting from bashing it against the immovable wall in front of me. :-)Could anyone help to get rid of this wall ?ThanksDave

SQL Server 2008 R2 Maintenance plans and backups

Posted: 10 Jun 2013 01:00 PM PDT

Hi, I have a customised maintenance plan that rebuilds the indexes and updates the statistics and then takes a full DB backup.Is there a way to find out what backup files where created by the maintenance sub-plan?I used to be able to do this with SQL Server 2000 but with 2008 R2 it does not look easy.Regards,Ignacio
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog