Post reply

How save contents of sys.dm_exec_sessions ?

  • November 10, 2009 at 3:44 am

    #211952

    Hi, is it possible automatically save in a table the temporary contents of sys.dm_exec_sessions ?

    I am a beginner programming sql. Someone can post the code (for SQL2005 and possibly also SQL2000)? Thanks

  • November 10, 2009 at 7:15 am

    #1077223

    Sure you can save it. It's just a view, it behaves like any other view in SQL. INSERT INTO (with a pre-created table) or SELECT ... INTO (to both create and populate) both will work fine.

    Insert: http://msdn.microsoft.com/en-us/library/ms174335%28SQL.90%29.aspx

    Select: http://msdn.microsoft.com/en-us/library/ms189499(SQL.90).aspx

    None of the DMVs existed on SQL 2000 (new 2005 features), for 2000 the closest equivalent is sysprocesses.

    If you want to save the dmv at intervals, look up SQL Agent Jobs. There's no way to put a trigger on a dmv, so you can't automatically have the changes recorded elsewhere.

    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

  • November 10, 2009 at 7:23 am

    #1077232

    Thank you !

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

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