Check if DB is being written

Question

Check if DB is being written

Alex Lim

SSC Veteran

Points: 264
More actions
December 4, 2005 at 11:57 pm

#90079

I would like to check if there is data being currrently saved to a table or database. I thought that the funtion would be @@IO_BUSY, but it doesn't return what I want. Any ideas?
Thanks.

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

You could check the syslocks and syslockinfo tables in the master database to see if there are any exclusive locks on the table in question. That would tell you if there's an insert, update or delete occuring on the table.

SQL doesn't write updates immediatly to disk when you do a data modification. The modification is done to pages in memory and written to the disk some time later by one of the system processes.

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

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 2

If actual writing activity in general is wanted, I believe that you could use perfmon for this.. (gotta be some file i/o counters in there, I believe..?)

/Kenneth

Alex Lim SSC Veteran Points: 264 More actions · Answer 3

Alex Lim

SSC Veteran

Points: 264

December 5, 2005 at 6:44 pm

#607853

I'll give it a try. Thanks.

Fernando-235287 SSCrazy Points: 2485 More actions · Answer 4

you can also try with profiler within SQL Server. It basically the same kind of tool as the performer for windows but it can follow all the scripts run at one given time as well as who is doing what (uses sp_who, etc) Profiler is a goods tool and can also give you any kind of error because it follows the raiseerror. Good luck

jasonhatfield01 Right there with Babe Points: 722 More actions · Answer 5

If you are looking for any open transactions, take a look at DBCC OPENTRAN in BOL.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

Exec sp_Locks

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)