Activity Monitor: SQL 2005

Is this supposed to work with SQL Server 2000 databases? I get the following
message when trying to launch it on one of these:
TITLE: Microsoft SQL Server Management Studio
Cannot show requested dialog.
ADDITIONAL INFORMATION:
Unable to execute requested command.
Unable to launch Activity Monitor. You may not have sufficient permissions.
(ActivityMonitor)
Invalid object name 'sysdatabases'. (Microsoft SQL Server, Error: 208)
For help, click:
http://go.microsoft.com/fwlink?ProdN...8&LinkId=20476
BUTTONS:
OK
It works fine when connected to 2005 servers.
Thanks,
TomT
Hi Tom,
Welcome to MSDN newsgroup.
Regarding on the launching Activity Monitor problem you mentioned, I think
it is the expected behavior since Activity Monitor is a SQL Server 2005
specific tool that targeting server /use activity monitoring. So for
sqlserver 2000 instances they're not supported to use this tool though many
common client components in sql 2005 managment studio are still available
and can be launched for sql 2000 instance. In addition, for sqlserver
2000, there exists other performance and activity monitoring tools:
# Monitoring Server Performance and Activity
http://msdn.microsoft.com/library/en...0mr.asp?frame=
true
Thanks,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
| Thread-Topic: Activity Monitor: SQL 2005
| thread-index: AcYEtqv3CT7/oApERimnOFGHHQ7OsQ==
| X-WBNR-Posting-Host: 68.67.208.184
| From: "=?Utf-8?B?VG9tVA==?=" <tomt@.newsgroup.nospam>
| Subject: Activity Monitor: SQL 2005
| Date: Mon, 19 Dec 2005 08:10:04 -0800
| Lines: 36
| Message-ID: <35FAA28A-3693-4385-A3DF-CE3474AB6B85@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.tools
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSF TNGXA03.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.tools:29191
| X-Tomcat-NG: microsoft.public.sqlserver.tools
|
| Is this supposed to work with SQL Server 2000 databases? I get the
following
| message when trying to launch it on one of these:
|
| TITLE: Microsoft SQL Server Management Studio
| --
|
| Cannot show requested dialog.
|
| --
| ADDITIONAL INFORMATION:
|
| Unable to execute requested command.
|
| --
|
| Unable to launch Activity Monitor. You may not have sufficient
permissions.
| (ActivityMonitor)
|
| --
|
| Invalid object name 'sysdatabases'. (Microsoft SQL Server, Error: 208)
|
| For help, click:
|
http://go.microsoft.com/fwlink?ProdN...dVer=08.00 .2
039&EvtSrc=MSSQLServer&EvtID=208&LinkId=20476
|
| --
| BUTTONS:
|
| OK
| --
|
| It works fine when connected to 2005 servers.
|
| Thanks,
|
| TomT
|
|||It work fine on my machine. Are you sysadmin on the 2000 machine? Perhaps this is the problem and AM
doesn't give a very helpful error message?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"TomT" <tomt@.newsgroup.nospam> wrote in message
news:35FAA28A-3693-4385-A3DF-CE3474AB6B85@.microsoft.com...
> Is this supposed to work with SQL Server 2000 databases? I get the following
> message when trying to launch it on one of these:
> TITLE: Microsoft SQL Server Management Studio
> --
> Cannot show requested dialog.
> --
> ADDITIONAL INFORMATION:
> Unable to execute requested command.
> --
> Unable to launch Activity Monitor. You may not have sufficient permissions.
> (ActivityMonitor)
> --
> Invalid object name 'sysdatabases'. (Microsoft SQL Server, Error: 208)
> For help, click:
> http://go.microsoft.com/fwlink?ProdN...8&LinkId=20476
> --
> BUTTONS:
> OK
> --
> It works fine when connected to 2005 servers.
> Thanks,
> TomT
|||In SQL BOL it states "To view the Activity Monitor the user needs SELECT
permission to the sysprocesses and syslocks tables in the master database on
a SQL Server 2000 server. Permission to view these tables is granted by
default to the PUBLIC database role."
I use two different SQL Server 2000 servers, one I can use Activity Monitor
on the other I get the same error, my securty for both servers is identical.
The only difference I can find is that the server I cannot use Activity
Monitor on has SQL Reporting Services installed.
So TomT is the SQL Server 2000 server you are trying to use Activity Monitor
on have SQL Server Reporting Services installed?
Harolds
"Steven Cheng[MSFT]" wrote:

> Hi Tom,
> Welcome to MSDN newsgroup.
> Regarding on the launching Activity Monitor problem you mentioned, I think
> it is the expected behavior since Activity Monitor is a SQL Server 2005
> specific tool that targeting server /use activity monitoring. So for
> sqlserver 2000 instances they're not supported to use this tool though many
> common client components in sql 2005 managment studio are still available
> and can be launched for sql 2000 instance. In addition, for sqlserver
> 2000, there exists other performance and activity monitoring tools:
> # Monitoring Server Performance and Activity
> http://msdn.microsoft.com/library/en...0mr.asp?frame=
> true
> Thanks,
> Steven Cheng
> Microsoft Online Support
> Get Secure! www.microsoft.com/security
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>
> --
> | Thread-Topic: Activity Monitor: SQL 2005
> | thread-index: AcYEtqv3CT7/oApERimnOFGHHQ7OsQ==
> | X-WBNR-Posting-Host: 68.67.208.184
> | From: "=?Utf-8?B?VG9tVA==?=" <tomt@.newsgroup.nospam>
> | Subject: Activity Monitor: SQL 2005
> | Date: Mon, 19 Dec 2005 08:10:04 -0800
> | Lines: 36
> | Message-ID: <35FAA28A-3693-4385-A3DF-CE3474AB6B85@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.tools
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSF TNGXA03.phx.gbl
> | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.tools:29191
> | X-Tomcat-NG: microsoft.public.sqlserver.tools
> |
> | Is this supposed to work with SQL Server 2000 databases? I get the
> following
> | message when trying to launch it on one of these:
> |
> | TITLE: Microsoft SQL Server Management Studio
> | --
> |
> | Cannot show requested dialog.
> |
> | --
> | ADDITIONAL INFORMATION:
> |
> | Unable to execute requested command.
> |
> | --
> |
> | Unable to launch Activity Monitor. You may not have sufficient
> permissions.
> | (ActivityMonitor)
> |
> | --
> |
> | Invalid object name 'sysdatabases'. (Microsoft SQL Server, Error: 208)
> |
> | For help, click:
> |
> http://go.microsoft.com/fwlink?ProdN...dVer=08.00 .2
> 039&EvtSrc=MSSQLServer&EvtID=208&LinkId=20476
> |
> | --
> | BUTTONS:
> |
> | OK
> | --
> |
> | It works fine when connected to 2005 servers.
> |
> | Thanks,
> |
> | TomT
> |
>
|||Harolds (harolds@.community.nospam) writes:
> In SQL BOL it states "To view the Activity Monitor the user needs SELECT
> permission to the sysprocesses and syslocks tables in the master
> database on a SQL Server 2000 server. Permission to view these tables is
> granted by default to the PUBLIC database role."
> I use two different SQL Server 2000 servers, one I can use Activity
> Monitor on the other I get the same error, my securty for both servers
> is identical.
Given the error message you get:
Invalid object name 'sysdatabases'. (Microsoft SQL Server, Error: 208)
My guess is that your default database on this server is not the master
database.
I made an experinment by connecting to an SQL 2000 with a user that
had tempdb as its default database, and indeed I got this exact message.
The part of the message that talks about insuffcient permission is
bogus. On SQL 2005, you cannot tell whether an error is due to lack
of permission or whether the object is really missing. But SQL 2000
will always tell you the exact reason.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
|||Erland Sommarskog (esquel@.sommarskog.se) writes:
> Given the error message you get:
> Invalid object name 'sysdatabases'. (Microsoft SQL Server, Error: 208)
> My guess is that your default database on this server is not the master
> database.
> I made an experinment by connecting to an SQL 2000 with a user that
> had tempdb as its default database, and indeed I got this exact message.
> The part of the message that talks about insuffcient permission is
> bogus. On SQL 2005, you cannot tell whether an error is due to lack
> of permission or whether the object is really missing. But SQL 2000
> will always tell you the exact reason.
And since you should be able to use Activity Monitor even with a
different default database than master, I've filed a bug for this:
http://lab.msdn.microsoft.com/Produc...ckId=FDBK43531
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
|||Thanks, that fixed the problem.
Harolds
"Erland Sommarskog" wrote:

> Harolds (harolds@.community.nospam) writes:
> Given the error message you get:
> Invalid object name 'sysdatabases'. (Microsoft SQL Server, Error: 208)
> My guess is that your default database on this server is not the master
> database.
> I made an experinment by connecting to an SQL 2000 with a user that
> had tempdb as its default database, and indeed I got this exact message.
> The part of the message that talks about insuffcient permission is
> bogus. On SQL 2005, you cannot tell whether an error is due to lack
> of permission or whether the object is really missing. But SQL 2000
> will always tell you the exact reason.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pro...ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinf...ons/books.mspx
>

Activity Monitor - host entries blank for sql login ?

The activity monitor is not showing entries for hosts which connect via sql logins (odbc), is this normal? Where can I find a list of protocols in which the hostname/ip can be picked up. We're on SQL 2005 SP1/Build 2153

Thanks

The hostname should show up if the user is connecting from a Windows box using ODBC or OLE-DB. If you are using a JDBC driver or comming from a unix machine this field is typically blank. This can be adjusted by adding the "Workstation ID" paramater to the connection string and setting it to equal the client machines host name. This object probably doesn't exist on all drivers. The string name may also be slightly different so some trial and error may be required.|||

The connections in question are from Windows OS (2000/XP) machines, no JDBC or UNIX involved......

Activity Monitor

When I execute a task against my mssql 2005 server it remains visible in the
activity monitor for up to 10 minutes (status = sleeping. open transactions
= 0, command = awaiting command), I have tried and changed the
commandtimeout = 10, reset the connectiontimeout and still it remains. It
appears that they are consuming resources, my concern is that we have up to
5 million requests a months and as a result the table is exceptionally
large.
Any suggestions would be greatly appreciated.Did you think about connection pooling?
Connection pooling will hold a connection for a specific amount of time for
reuse.
You can disable connection pooling in your connectionstring
...pooling=false...
Don't forget to refresh your activity monitor ;-)
"Tlink" wrote:

> When I execute a task against my mssql 2005 server it remains visible in t
he
> activity monitor for up to 10 minutes (status = sleeping. open transaction
s
> = 0, command = awaiting command), I have tried and changed the
> commandtimeout = 10, reset the connectiontimeout and still it remains. It
> appears that they are consuming resources, my concern is that we have up t
o
> 5 million requests a months and as a result the table is exceptionally
> large.
> Any suggestions would be greatly appreciated.
>
>|||Did you refresh the monitor?
Andrew J. Kelly SQL MVP
"Tlink" <Tlink@.online.nospam> wrote in message
news:eiqciSmTGHA.4308@.TK2MSFTNGP10.phx.gbl...
> When I execute a task against my mssql 2005 server it remains visible in
> the activity monitor for up to 10 minutes (status = sleeping. open
> transactions = 0, command = awaiting command), I have tried and changed
> the commandtimeout = 10, reset the connectiontimeout and still it remains.
> It appears that they are consuming resources, my concern is that we have
> up to 5 million requests a months and as a result the table is
> exceptionally large.
> Any suggestions would be greatly appreciated.
>|||Yes, I set the monitor to refresh ever 10 seconds.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uoFITgnTGHA.5900@.tk2msftngp13.phx.gbl...
> Did you refresh the monitor?
> --
> Andrew J. Kelly SQL MVP
>
> "Tlink" <Tlink@.online.nospam> wrote in message
> news:eiqciSmTGHA.4308@.TK2MSFTNGP10.phx.gbl...
>|||Yes set pooling=false and rechecked the refresh rate on the activity monitor
and no change it still mounts.
"migeold" <migeold@.discussions.microsoft.com> wrote in message
news:9697B6DF-77A8-43B0-B6C7-1884420764BD@.microsoft.com...
> Did you think about connection pooling?
> Connection pooling will hold a connection for a specific amount of time
> for
> reuse.
> You can disable connection pooling in your connectionstring
> ...pooling=false...
> Don't forget to refresh your activity monitor ;-)
> "Tlink" wrote:
>|||Can you post your connectionstring and some more information to reproduce
your problem, please?
"Tlink" wrote:

> Yes set pooling=false and rechecked the refresh rate on the activity monit
or
> and no change it still mounts.
>
> "migeold" <migeold@.discussions.microsoft.com> wrote in message
> news:9697B6DF-77A8-43B0-B6C7-1884420764BD@.microsoft.com...
>
>|||Here is an extract of the connection string and some data collection. The
average number of records per database table is 5.6 million records, I have
optimised the files and they all contain indexes.
2 connection string
ConnectionString = "Provider=SQLOLEDB.1;"
or
ConnectionString = "Provider=SQLNCLI;"
ConnectionString = ConnectionString & ";MarsConn=yes"
One of the above sets &
ConnectionString = ConnectionString & ";Database=" & dbname
ConnectionString = ConnectionString & ";Uid=" & dbuid
ConnectionString = ConnectionString & ";Pwd=" & dbpassword
ConnectionString = ConnectionString & ";Encrypt=yes"
ConnectionString = ConnectionString & ";Pooling=false"
DBobj.connectionstring = ConnectionString
DBobj.CommandTimeout = 30
dbobj.open
dbobj.commandtimeout = 10
RTSOBJ = dbobj.execute("Select xxx,bbb,ccc,ddd,eee FROM ControlFile where
ooo = 'pppp';")
RTSOBJ2 = dbobj.execute("Select ffff,hhhh,iii,kkkk,llll FROM ControlFile2
where hhhh = 'pppp';")
RTSOBJ3 = dbobj.execute("Select * FROM ControlFile2 where hhhh = 'pppp';")
' all data returned has at least 1 record but may contain up to 100 records
which are processed in a loop
do while RTSOBJ.EOF = FALSE
:
:processed
:
Loop
RTSOBJ.close
RTSOBJ2.close
RTSOBJ3.close
dbobj.close
"migeold" <migeold@.discussions.microsoft.com> wrote in message
news:D2A10843-7293-4E2D-B3AD-5BBB62242B62@.microsoft.com...
> Can you post your connectionstring and some more information to reproduce
> your problem, please?
> "Tlink" wrote:
>|||Hello,
You may want to check sys.sysprocesses view to see if there is any
difference before and after you close the connection.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Reply-To: "Tlink" <Tlink@.online.nospam>
>From: "Tlink" <Tlink@.online.nospam>
>References: <eiqciSmTGHA.4308@.TK2MSFTNGP10.phx.gbl>
<uoFITgnTGHA.5900@.tk2msftngp13.phx.gbl>
>Subject: Re: Activity Monitor
>Date: Fri, 24 Mar 2006 01:07:03 +1100
>Lines: 26
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
>X-RFC2646: Format=Flowed; Response
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
>Message-ID: <#JWRSMoTGHA.1672@.tk2msftngp13.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.programming
>NNTP-Posting-Host: bus-210-211-121-210.vic.veridas.net 210.211.121.210
>Path: TK2MSFTNGXA03.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
>Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.sqlserver.programming:588412
>X-Tomcat-NG: microsoft.public.sqlserver.programming
>Yes, I set the monitor to refresh ever 10 seconds.
>"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>news:uoFITgnTGHA.5900@.tk2msftngp13.phx.gbl...
in
that
>
>|||Not really sure what I am looking for, sorry can you please provide a little
bit more information.
""privatenews"" <petery@.online.microsoft.com> wrote in message
news:Sw6WnjuTGHA.4768@.TK2MSFTNGXA01.phx.gbl...
> Hello,
> You may want to check sys.sysprocesses view to see if there is any
> difference before and after you close the connection.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> --
> <uoFITgnTGHA.5900@.tk2msftngp13.phx.gbl>
> in
> that
>|||Hello,
To isolate if the issue is issue in activty monitor, you could monitor the
active process via the following query
select * from master.sys.sysprocesses
Usually if a connection is closed by client program, a process with SPID is
also terminated on server.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Reply-To: "Tlink" <Tlink@.online.nospam>
>From: "Tlink" <Tlink@.online.nospam>
>References: <eiqciSmTGHA.4308@.TK2MSFTNGP10.phx.gbl>
<uoFITgnTGHA.5900@.tk2msftngp13.phx.gbl>
<#JWRSMoTGHA.1672@.tk2msftngp13.phx.gbl>
<Sw6WnjuTGHA.4768@.TK2MSFTNGXA01.phx.gbl>
>Subject: Re: Activity Monitor
>Date: Sat, 25 Mar 2006 18:57:34 +1100
>Lines: 80
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
>X-RFC2646: Format=Flowed; Original
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
>Message-ID: <#mitkH#TGHA.4300@.TK2MSFTNGP14.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.programming
>NNTP-Posting-Host: bus-210-211-121-210.vic.veridas.net 210.211.121.210
>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP14.phx.gbl
>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.programming:588696
>X-Tomcat-NG: microsoft.public.sqlserver.programming
>
>Not really sure what I am looking for, sorry can you please provide a
little
>bit more information.
>
>""privatenews"" <petery@.online.microsoft.com> wrote in message
>news:Sw6WnjuTGHA.4768@.TK2MSFTNGXA01.phx.gbl...
changed
>
>