PREEMPTIVE_OS_FILEOPS

  • 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?

  • 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.

    [font="Tahoma"]
    --SQLFRNDZ[/url]
    [/font]

  • 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

  • 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, MVP, M.Sc (Comp Sci)
    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
  • 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, MVP, M.Sc (Comp Sci)
    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
  • 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.

  • 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, MVP, M.Sc (Comp Sci)
    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
  • I've correlated the hung-in-PREEMPTIVE_OS_FILEOPS issue with tempdb running out of space, so it's something to check...

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply