Post reply

SQL 2008 - Activity Monitor Not Showing All Activity?

  • January 19, 2010 at 2:24 pm

    #209250

    When restoring a database, I get the message the the database is in use. The Activity Monitor doesn't show any activity for that database. Indeed, it doesn't show the SPID for any of the databases I have an open query window for. If I close down and restart Management Studio, I can restore. What fundamental property am I missing? Under 2005, the Activity Monitor showed everything and I could kill a process rightthere. Why can't I see a process that SQL Server tels me is blocking my ability to restpre a backup? Thanks!

  • January 20, 2010 at 1:36 am

    #1105876

    you may have the database selected in SSMS. These sort of messages/situation are a pain to be sure - I usually issue these two commands from any query window if I'm doing a restore - I usually have restores scripted - rarely use the gui.

    alter database xxxxxx set single_user with rollback immediate;

    alter database xxxxxx set multi_user with rollback immediate;

    try this script to see what connections you have

    SELECT convert(char(9),sess.last_request_start_time,108) AS StartTime,

    sess.session_id,

    sess.login_name,

    sess.[status],

    isnull(req.command,'') as command,

    isnull(db_name(req.database_id),'') as DatabaseName,

    sess.cpu_time,

    sess.reads,

    sess.writes,

    sess.logical_reads,

    left(st.text,100) as [Command text],

    convert(char(20),sess.login_time,113) as login_time,

    sess.[host_name],

    conn.client_net_address,

    sess.[program_name]

    --

    from sys.dm_exec_sessions sess left outer join sys.dm_exec_requests req on req.session_id=sess.session_id

    left outer join sys.dm_exec_connections conn on conn.session_id=sess.session_id

    CROSS APPLY sys.dm_exec_sql_text(conn.most_recent_sql_handle) AS st

    -- user processes only

    where sess.is_user_process =1;

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • January 20, 2010 at 6:58 am

    #1106014

    Thanks for responding! The database in question was not selected, I had considered that. I do run my restores from scripts but in this case, I got the "In Use" message from the script and when using the gui. I shall add the two Alter statements you recommended.

    This line of the script you provided generated a syntax error message

    CROSS APPLY sys.dm_exec_sql_text(conn.most_recent_sql_handle) AS st

    Incorrect syntax near '.'.

    Thanks again.

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

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