SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


PREEMPTIVE_OS_FILEOPS


PREEMPTIVE_OS_FILEOPS

Author
Message
Daniel Britten
Daniel Britten
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 124
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?
@SQLFRNDZ
@SQLFRNDZ
SSC Eights!
SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)

Group: General Forum Members
Points: 929 Visits: 1217
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

Daniel Britten
Daniel Britten
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 124
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90335 Visits: 45284
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


GilaMonster
GilaMonster
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90335 Visits: 45284
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


Daniel Britten
Daniel Britten
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 124
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90335 Visits: 45284
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


tom.lux
tom.lux
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 54
I've correlated the hung-in-PREEMPTIVE_OS_FILEOPS issue with tempdb running out of space, so it's something to check...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search