tempdb issue

  • y'day we faced a low disk isue because of temp db .mdf is taking more space , later we restarted the services and it becomes normal . i want to find why the mdf file is taking that much of memory and want to find what transactions are runing on the tempdb at that time .

  • Create a trace and run it during the time you need to find out.

    capture SQL depepnding upon the duration from the trace and run into SQL Analyser for execution plan.

  • run the script below to find any open transactions that could be the culprit...

    dbcc opentran('tempdb')

    The_SQL_DBA
    MCTS

    "Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

  • Keep in mind that you will have to query while the tempdb is growing.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 4 posts - 1 through 3 (of 3 total)

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