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

PREEMPTIVE_OS_FILEOPS Expand / Collapse
Author
Message
Posted Monday, November 01, 2010 3:36 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 03, 2014 11:13 AM
Points: 4, Visits: 106
I have a job configured to run a DBCC CHECKDB on all of the databases on my server every 2 weeks. The job has been failing for about a month now. Ok, well actually it does not fail, it just gets stuck. The task shows a wait type of "PREEMPTIVE_OS_FILEOPS". At first it looked like this was limited to one database because it always got stuck on the same one. I have since removed that database from the server (which by the way required rebooting the server with the SQL services set to start manually, deleting the files, starting SQL, and then dropping the DB). Now I have another database stuck with the same issue. I know that this indicates an OS level file operation is running, but that is not very specific. Has anyone seen this issue before?
Post #1014171
Posted Monday, November 01, 2010 4:06 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, March 27, 2014 6:04 PM
Points: 450, Visits: 991
Dbcc checkdb is checks the integrity of the database why you want to do every 2 weeks

and it also a resource intensive .

usually you have to do dbcc checkdb when you set your databse to single user mode.

there is chances of failing job due to concurrency with other transactions parallely.



--SQLFRNDZ
Post #1014179
Posted Monday, November 01, 2010 4:49 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 03, 2014 11:13 AM
Points: 4, Visits: 106
To be clean I am running DBCC CHECKDB ('database name', noindex) WITH PHYSICAL ONLY.
This is actually a Microsoft recommendation, refer to Books Online, DBCC CHECKDB, Best Practice. No matter why I am doing it, it does not work.

I have already verified that there are no other spids using this database. I fact the last time a tried this was after a fresh server reboot on a Sunday when I knew that no users were on the system.

To give a few more details. This server wil not reboot on its own. It getting to the "Shutting Down" screen and just sets there for hours. I have to hard boot it. I just discovered that the SQL Server service will also not shutdown cleanly. The stop request gave an error, however, the Services list showed that it was stopped. So I opened Task Manager and sure enough the SQL service was still running.

As I said before this started with on database have an issue, I was able to delete it, now I have the same issue with another database. I have also tried to detach the database, drop the database, and backup the database. All fail. I can query the data with no problem. It is as if any time SQL needs to talk to the OS about this database, it locks up.

Any suggestions are greatly appreciated.

Thank
Post #1014194
Posted Monday, November 01, 2010 11:50 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:56 AM
Points: 41,524, Visits: 34,440
pavan_srirangam (11/1/2010)
Dbcc checkdb is checks the integrity of the database why you want to do every 2 weeks


Because it's good to know about corruption while it can still be fixed by restoring from backup. Every two weeks is a little less often than I would like. Weekly is usually better

usually you have to do dbcc checkdb when you set your databse to single user mode.


Ur, no.

If you're doing checkDB with repair then the DB has to be single user. There's no requirement that you have to run a checkDB when you switch to single user and a normal check is done online, no locks, no restricted access.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1014285
Posted Monday, November 01, 2010 11:52 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:56 AM
Points: 41,524, Visits: 34,440
Daniel Britten (11/1/2010)
To be clean I am running DBCC CHECKDB ('database name', noindex) WITH PHYSICAL ONLY.


Bear in mind that those options will miss a lot of errors. You should be doing a full checkDB somewhere (restore backup on secondary server and run there perhaps)

As I said before this started with on database have an issue, I was able to delete it, now I have the same issue with another database. I have also tried to detach the database, drop the database, and backup the database. All fail. I can query the data with no problem. It is as if any time SQL needs to talk to the OS about this database, it locks up.


Any errors in the SQL error log? Any errors in the windows event log?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1014286
Posted Tuesday, November 02, 2010 9:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 03, 2014 11:13 AM
Points: 4, Visits: 106
I realize that a full checkDB is better. However, this server has 10TB of data on it and a full checkDB would simply take too long. Also, this is historical data, no updates are occuring so I took the faster approach. I do a full checkDB on occasion, but it just takes too long to schedule.

As for the SQL log, yes, I see something very intersting that I missed before. Every 5 minutes SPID 31 is generating the following message. "A time-out occurred while waiting for buffer latch -- type 4, bp 00000005EFFDE380, page 5:8088, stat 0x7c0040d, database id: 19, allocation unit Id: 10851164356608/292326141067264, task 0x0000000004C7A988 : 0, waittime 300, flags 0x100000003a, owning task 0x0000000004C7A988. Not continuing to wait." SPID 31 is a BACKGROUND GHOST CLEANUP of the database that the checkDB is stuck on. The checkDB is running as SPID 57.

Tracing these back to the first one, I see another message about this database just a few minutes before these started. "SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [G:\WM07DSDM\WM07DSDM2_Data3.ndf] in database [WM07DSDM] (19). The OS file handle is 0x0000000000000A88. The offset of the latest long I/O is: 0x00000003f30000". This was from SPID 4, the LAZY WRITER spid.
Post #1014612
Posted Tuesday, November 02, 2010 11:07 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:56 AM
Points: 41,524, Visits: 34,440
I'm chatting with an expert on this, but from the messages in your last post it looks like you have IO subsystem contention - not enough IOPS to handle the required load. CheckDB when run Physical_Only is disk-bound (if I recall) and hence is going to be hammering the drives.

Maybe worth checking SAN logs (I assume it's a SAN), see what the throughput is and what errors there are, if any.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1014698
Posted Wednesday, February 20, 2013 2:24 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 29, 2014 8:30 PM
Points: 25, Visits: 45
I've correlated the hung-in-PREEMPTIVE_OS_FILEOPS issue with tempdb running out of space, so it's something to check...
Post #1422305
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse