|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Activity Monitor not showing Database names
Process ID, User and Database. In 2008, in Activity Monitor and in Processes, I can see SessionID (which I assume is the Process ID from 2005) andLogin (which I assume is User from 2005) and Database which is showing only tempdb and blanks for all the databases????? I am trying to find any users that are connected to a database I am trying to restore and cannot get exclusive access. I have all my queries closed and anything that was using the database has been closed. In the 2005 AM I can see my with one session open and showing the database name. But in 2008, I can see the same session ID with no Database showing - which is pretty much worthless. Why is that??? Also, what would the command line be to get the same information? Thanks, Tom Also, in the 2008 AM, the SessionID 66 shows the command as blank, but when
I do a right click and select details, I get as the last command: (@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000),@_msparam_4 nvarchar(4000),@_msparam_5 nvarchar(4000))SELECT p.name AS [Name] FROM sys.all_objects AS sp INNER JOIN sys.all_parameters AS param ON param.object_id=sp.object_id INNER JOIN sys.extended_properties AS p ON p.major_id=param.object_id AND p.minor_id=param.parameter_id AND p.class=2 WHERE (param.name=@_msparam_0)and((sp.type = @_msparam_1 OR sp.type = @_msparam_2 OR sp.type=@_msparam_3)and(sp.name=@_msparam_4 and SCHEMA_NAME(sp.schema_id)=@_msparam_5)) ORDER BY [Name] ASC But in 2005 AM, the ProcessID shows the command as blank but right clicking and selecting details shows the Last Command as blank. Thanks, Tom Show quoteHide quote "tshad" <t***@pdsa.com> wrote in message news:ervG0Yq9JHA.4900@TK2MSFTNGP02.phx.gbl... > In 2005, I can open the Activity Monitor and in Process Info, I can see > the Process ID, User and Database. > > In 2008, in Activity Monitor and in Processes, I can see SessionID (which > I assume is the Process ID from 2005) andLogin (which I assume is User > from 2005) and Database which is showing only tempdb and blanks for all > the databases????? > > I am trying to find any users that are connected to a database I am trying > to restore and cannot get exclusive access. I have all my queries closed > and anything that was using the database has been closed. > > In the 2005 AM I can see my with one session open and showing the database > name. But in 2008, I can see the same session ID with no Database > showing - which is pretty much worthless. > > Why is that??? > > Also, what would the command line be to get the same information? > > Thanks, > > Tom > sp_who2 should show you want you want to know. But try placing the db in
single user mode with the ROLLBACK IMMEDIATE option to kill any open connections. -- Show quoteHide quoteAndrew J. Kelly SQL MVP Solid Quality Mentors "tshad" <t***@pdsa.com> wrote in message news:ervG0Yq9JHA.4900@TK2MSFTNGP02.phx.gbl... > In 2005, I can open the Activity Monitor and in Process Info, I can see > the Process ID, User and Database. > > In 2008, in Activity Monitor and in Processes, I can see SessionID (which > I assume is the Process ID from 2005) andLogin (which I assume is User > from 2005) and Database which is showing only tempdb and blanks for all > the databases????? > > I am trying to find any users that are connected to a database I am trying > to restore and cannot get exclusive access. I have all my queries closed > and anything that was using the database has been closed. > > In the 2005 AM I can see my with one session open and showing the database > name. But in 2008, I can see the same session ID with no Database > showing - which is pretty much worthless. > > Why is that??? > > Also, what would the command line be to get the same information? > > Thanks, > > Tom > "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message That's fine and it does.news:e9g82z29JHA.1340@TK2MSFTNGP05.phx.gbl... > sp_who2 should show you want you want to know. But try placing the db in > single user mode with the ROLLBACK IMMEDIATE option to kill any open > connections. But why doesn't the Activity Monitor show me the databases I am connected to as it used to. It really is pretty much useless otherwise. If I have 40 processes connected to the database and I know that I need to look at one of the processes connected to DBx, there is no way in AM to do this. Thanks, Tom Show quoteHide quote > > -- > Andrew J. Kelly SQL MVP > Solid Quality Mentors > > > "tshad" <t***@pdsa.com> wrote in message > news:ervG0Yq9JHA.4900@TK2MSFTNGP02.phx.gbl... >> In 2005, I can open the Activity Monitor and in Process Info, I can see >> the Process ID, User and Database. >> >> In 2008, in Activity Monitor and in Processes, I can see SessionID (which >> I assume is the Process ID from 2005) andLogin (which I assume is User >> from 2005) and Database which is showing only tempdb and blanks for all >> the databases????? >> >> I am trying to find any users that are connected to a database I am >> trying to restore and cannot get exclusive access. I have all my queries >> closed and anything that was using the database has been closed. >> >> In the 2005 AM I can see my with one session open and showing the >> database name. But in 2008, I can see the same session ID with no >> Database showing - which is pretty much worthless. >> >> Why is that??? >> >> Also, what would the command line be to get the same information? >> >> Thanks, >> >> Tom >> > I honestly couldn't tell you as I think I have only used AM 2 or 3 times and
that was just for kicks. I usually query the DMV's directly or use sp_who2. -- Show quoteHide quoteAndrew J. Kelly SQL MVP Solid Quality Mentors "tshad" <t**@dslextreme.com> wrote in message news:OsjDKYG%23JHA.4560@TK2MSFTNGP03.phx.gbl... > > "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message > news:e9g82z29JHA.1340@TK2MSFTNGP05.phx.gbl... >> sp_who2 should show you want you want to know. But try placing the db in >> single user mode with the ROLLBACK IMMEDIATE option to kill any open >> connections. > > That's fine and it does. > > But why doesn't the Activity Monitor show me the databases I am connected > to as it used to. > > It really is pretty much useless otherwise. > > If I have 40 processes connected to the database and I know that I need to > look at one of the processes connected to DBx, there is no way in AM to do > this. > > Thanks, > > Tom >> >> -- >> Andrew J. Kelly SQL MVP >> Solid Quality Mentors >> >> >> "tshad" <t***@pdsa.com> wrote in message >> news:ervG0Yq9JHA.4900@TK2MSFTNGP02.phx.gbl... >>> In 2005, I can open the Activity Monitor and in Process Info, I can see >>> the Process ID, User and Database. >>> >>> In 2008, in Activity Monitor and in Processes, I can see SessionID >>> (which I assume is the Process ID from 2005) andLogin (which I assume is >>> User from 2005) and Database which is showing only tempdb and blanks for >>> all the databases????? >>> >>> I am trying to find any users that are connected to a database I am >>> trying to restore and cannot get exclusive access. I have all my >>> queries closed and anything that was using the database has been closed. >>> >>> In the 2005 AM I can see my with one session open and showing the >>> database name. But in 2008, I can see the same session ID with no >>> Database showing - which is pretty much worthless. >>> >>> Why is that??? >>> >>> Also, what would the command line be to get the same information? >>> >>> Thanks, >>> >>> Tom >>> >> > > It looks like Activity Monitor only shows the database name for a spid if the
spid is activvely doing something, not what database it is currently connected. Linchi Show quoteHide quote "tshad" wrote: > > "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message > news:e9g82z29JHA.1340@TK2MSFTNGP05.phx.gbl... > > sp_who2 should show you want you want to know. But try placing the db in > > single user mode with the ROLLBACK IMMEDIATE option to kill any open > > connections. > > That's fine and it does. > > But why doesn't the Activity Monitor show me the databases I am connected to > as it used to. > > It really is pretty much useless otherwise. > > If I have 40 processes connected to the database and I know that I need to > look at one of the processes connected to DBx, there is no way in AM to do > this. > > Thanks, > > Tom > > > > -- > > Andrew J. Kelly SQL MVP > > Solid Quality Mentors > > > > > > "tshad" <t***@pdsa.com> wrote in message > > news:ervG0Yq9JHA.4900@TK2MSFTNGP02.phx.gbl... > >> In 2005, I can open the Activity Monitor and in Process Info, I can see > >> the Process ID, User and Database. > >> > >> In 2008, in Activity Monitor and in Processes, I can see SessionID (which > >> I assume is the Process ID from 2005) andLogin (which I assume is User > >> from 2005) and Database which is showing only tempdb and blanks for all > >> the databases????? > >> > >> I am trying to find any users that are connected to a database I am > >> trying to restore and cannot get exclusive access. I have all my queries > >> closed and anything that was using the database has been closed. > >> > >> In the 2005 AM I can see my with one session open and showing the > >> database name. But in 2008, I can see the same session ID with no > >> Database showing - which is pretty much worthless. > >> > >> Why is that??? > >> > >> Also, what would the command line be to get the same information? > >> > >> Thanks, > >> > >> Tom > >> > > > > > "Linchi Shea" <LinchiS***@discussions.microsoft.com> wrote in message Yup, as I said pretty useless.news:6EC997B3-0A83-4AE7-855A-341ACFAA12C3@microsoft.com... > It looks like Activity Monitor only shows the database name for a spid if > the > spid is activvely doing something, not what database it is currently > connected. 2005 showed the database for all processes. Not sure why they took it out. Thanks, Tom Show quoteHide quote > > Linchi > > "tshad" wrote: > >> >> "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message >> news:e9g82z29JHA.1340@TK2MSFTNGP05.phx.gbl... >> > sp_who2 should show you want you want to know. But try placing the db >> > in >> > single user mode with the ROLLBACK IMMEDIATE option to kill any open >> > connections. >> >> That's fine and it does. >> >> But why doesn't the Activity Monitor show me the databases I am connected >> to >> as it used to. >> >> It really is pretty much useless otherwise. >> >> If I have 40 processes connected to the database and I know that I need >> to >> look at one of the processes connected to DBx, there is no way in AM to >> do >> this. >> >> Thanks, >> >> Tom >> > >> > -- >> > Andrew J. Kelly SQL MVP >> > Solid Quality Mentors >> > >> > >> > "tshad" <t***@pdsa.com> wrote in message >> > news:ervG0Yq9JHA.4900@TK2MSFTNGP02.phx.gbl... >> >> In 2005, I can open the Activity Monitor and in Process Info, I can >> >> see >> >> the Process ID, User and Database. >> >> >> >> In 2008, in Activity Monitor and in Processes, I can see SessionID >> >> (which >> >> I assume is the Process ID from 2005) andLogin (which I assume is User >> >> from 2005) and Database which is showing only tempdb and blanks for >> >> all >> >> the databases????? >> >> >> >> I am trying to find any users that are connected to a database I am >> >> trying to restore and cannot get exclusive access. I have all my >> >> queries >> >> closed and anything that was using the database has been closed. >> >> >> >> In the 2005 AM I can see my with one session open and showing the >> >> database name. But in 2008, I can see the same session ID with no >> >> Database showing - which is pretty much worthless. >> >> >> >> Why is that??? >> >> >> >> Also, what would the command line be to get the same information? >> >> >> >> Thanks, >> >> >> >> Tom >> >> >> > >> >> >> I don't know why either, but I know that this is one of the columns missing
from the port from sysprocesses to sys.dm_exec_sessions and sys.dm_exec_requests. The intention, I gather, to split this into two views was to avoid having to carry around a bunch of useless columns for tasks that weren't currently doing anything. But they only put database context information into the requests view, which is the one that is empty when a session is asleep. Vote for this item, it was closed as fixed but clearly there is still work to do: http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=25750 2 On 7/2/09 5:23 PM, in article #QiMNt1#JHA.4***@TK2MSFTNGP05.phx.gbl, "tshad" <t***@pdsa.com> wrote: Show quoteHide quote > > "Linchi Shea" <LinchiS***@discussions.microsoft.com> wrote in message > news:6EC997B3-0A83-4AE7-855A-341ACFAA12C3@microsoft.com... >> It looks like Activity Monitor only shows the database name for a spid if >> the >> spid is activvely doing something, not what database it is currently >> connected. > > Yup, as I said pretty useless. > > 2005 showed the database for all processes. Not sure why they took it out. > > Thanks, > > Tom > >> >> Linchi >> >> "tshad" wrote: >> >>> >>> "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message >>> news:e9g82z29JHA.1340@TK2MSFTNGP05.phx.gbl... >>>> sp_who2 should show you want you want to know. But try placing the db >>>> in >>>> single user mode with the ROLLBACK IMMEDIATE option to kill any open >>>> connections. >>> >>> That's fine and it does. >>> >>> But why doesn't the Activity Monitor show me the databases I am connected >>> to >>> as it used to. >>> >>> It really is pretty much useless otherwise. >>> >>> If I have 40 processes connected to the database and I know that I need >>> to >>> look at one of the processes connected to DBx, there is no way in AM to >>> do >>> this. >>> >>> Thanks, >>> >>> Tom >>>> >>>> -- >>>> Andrew J. Kelly SQL MVP >>>> Solid Quality Mentors >>>> >>>> >>>> "tshad" <t***@pdsa.com> wrote in message >>>> news:ervG0Yq9JHA.4900@TK2MSFTNGP02.phx.gbl... >>>>> In 2005, I can open the Activity Monitor and in Process Info, I can >>>>> see >>>>> the Process ID, User and Database. >>>>> >>>>> In 2008, in Activity Monitor and in Processes, I can see SessionID >>>>> (which >>>>> I assume is the Process ID from 2005) andLogin (which I assume is User >>>>> from 2005) and Database which is showing only tempdb and blanks for >>>>> all >>>>> the databases????? >>>>> >>>>> I am trying to find any users that are connected to a database I am >>>>> trying to restore and cannot get exclusive access. I have all my >>>>> queries >>>>> closed and anything that was using the database has been closed. >>>>> >>>>> In the 2005 AM I can see my with one session open and showing the >>>>> database name. But in 2008, I can see the same session ID with no >>>>> Database showing - which is pretty much worthless. >>>>> >>>>> Why is that??? >>>>> >>>>> Also, what would the command line be to get the same information? >>>>> >>>>> Thanks, >>>>> >>>>> Tom >>>>> >>>> >>> >>> >>> > >  |
|||||||||||||||||||||||
Other interesting topics