Post reply

Check if DB is being written

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

  • December 5, 2005 at 3:51 am

    #607636

    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

  • December 5, 2005 at 4:31 am

    #607642

    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

  • December 5, 2005 at 6:44 pm

    #607853

    I'll give it a try. Thanks.

  • December 6, 2005 at 2:17 am

    #607881

    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

  • December 7, 2005 at 3:28 pm

    #608291

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

  • December 9, 2005 at 12:26 am

    #608587

    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)
    Intro to Tally Tables and Functions

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

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