August 4, 2010 at 2:38 pm
There is a relatively unimportant question that I have not been able to find the answer for ... hoping someone on this forum can clear up the mystery.
When connection to a particular SQL Server using the Management Studio [both SQL Server 2005] I can see in the Activity monitor that my user is connected to "database T" . This is not my user's default database and it is not on any of the ODBC connections as the default database.
So, my question is, why/how does this happen? Where can I make a change to have it stop. The database in question is one that should only be used by a particular credit card application and we monitor the users in the database to make sure it is not used by anyone/thing that is shouldn't be in there.
Thank you in advance for your insight.
August 4, 2010 at 10:52 pm
Get the SPID of your session and DBCC INPUTBUFFER of your session ID should give you the details.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
August 5, 2010 at 6:49 pm
Hi,
You can get more information if you use Profiler. It can show you Application Name, Host and even process ID on that host.
August 6, 2010 at 6:49 am
Adiga (8/4/2010)
Get the SPID of your session and DBCC INPUTBUFFER of your session ID should give you the details.
DBCC INPUTBUFFER is such a limited command and doesn't give the full text of the command. If you're not going to use a trace, there's better code.
DECLARE @Handle BINARY(20)
SELECT @Handle = sql_handle
FROM SysProcesses
WHERE SPID = 133 --Change this SPID as appropriate
SELECT *
FROM ::fn_get_sql(@handle)
But the trace is the best option.
August 9, 2010 at 8:54 am
Thank you all. Using the Activity Monitor I can always see that the database is 'Trans' and the Application is 'Microsoft SQL Server Management Studio'; I am curious as to why when I connect to the server through the Management Studio it connects to this particular database. There is also a connection to master and the query window shows that I am in the master database. Is there a configuration for the initial database connection for Management Studio? I would have thought it would be the users default database, which in the case of my logon is the master database not Trans. Any thoughts?
I used the query statement
DECLARE @Handle BINARY(20)
SELECT @Handle = sql_handle
FROM SysProcesses
WHERE SPID = 51
SELECT *
FROM ::fn_get_sql(@handle)
with the following results:
dbid objectid number encrypted text
NULL NULL NULL 0 select count(*) from #snapshot_processinfo
August 9, 2010 at 10:19 am
Ellen-477471 (8/9/2010)
I am curious as to why when I connect to the server through the Management Studio it connects to this particular database.... I would have thought it would be the users default database, which in the case of my logon is the master database not Trans.
Funny, that's what I would have thought too. When you connect, is this just in Object Explorer or a new query window that's opening up to this db?
August 9, 2010 at 1:46 pm
It is not a new [query] window or the object browser, it is when I open the 'Activity Monitor'. I've noticed on other servers when I open the Activity Monitor the session is shown as connected to 'tempdb' database. It is just on the one server that the session shows in the 'Trans' database.
I queried the sys.sysprocesses table before opening the Activity Monitory and then after opening it, the spid in the Trans database was the one that was looking at activity;
opening the 'details' shows the following statement
create table #tmpDBCCinputbuffer ([Event Type] nvarchar(512), [Parameters] int, [Event Info] nvarchar(512))
insert into #tmpDBCCinputbuffer exec ('DBCC INPUTBUFFER(136)')
select [Event Info] from #tmpDBCCinputbuffer
August 10, 2010 at 5:42 am
Okay, I get what you're saying. Sorry, I'm being dumb. @=)
I don't know why Activity Monitor opens with you in tempdb, but I'm not sure it really matters. As far as the result set you got when you ran my code, I'm assuming you ran the code against the SPID that opened up with your AM connection?
It doesn't surprise me most of the values are NULL at that point. When you run the code, your AM connection isn't actively doing anything. If I understand AM correctly (someone correct me if I'm wrong), AM is just a snapshot of the activity at the time you open the window. It is not a constant, current, monitoring process. In fact, there's a refresh button up there that you have to use if you want to get another snapshot.
So, no activity on the SPID, null results back from the code I supplied. Usually, I use that code for SPIDs being blocked and SPIDs doing the blocking. I've never used it on a sleeping SPID before.
August 10, 2010 at 8:03 am
I just showed the details to prove that the spid that is connected to the 'Trans' database is actually the one that just opened the Activity Monitor.
My initial inquiry to the forum was to find out what controls which database the Activity Monitor monitor connects through.
Is it configurable? If so, where. It seems to have picked a random database and the one it picked on this server is not a database that we want to have random connections.
It just doesn't make sense. It seems like AM should be connected to one of the system databases, master or tempdb, not a user database.
August 10, 2010 at 8:31 am
So far as I know, it is not configurable. However, when I opened mine, I was opened up under tempdb.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply