Activity on a database?

Try seting up a trace, but just put in the connections,
then look at the databases connected to.
J

>--Original Message--
>Hi everyone, I have an SQL2000 with quiet a lot
databases and I want to
>delete some of them. How do I know if there has been any
kind of activity on
>a certain database, so I dont delete one that is being
used.
>Thanks a lot,
>Ivan Mckenzie
>
>.
>
Thanks, I suppose this is done with SQL profiler.
"Julie" <anonymous@.discussions.microsoft.com> escribi en el mensaje
news:2d1d201c469d7$11c0f540$a501280a@.phx.gbl...[vbcol=seagreen]
> Try seting up a trace, but just put in the connections,
> then look at the databases connected to.
> J
>
> databases and I want to
> kind of activity on
> used.
|||Hi,
Run the Profiler for couple of week days . Identify the databases not being
used. Make those databases
offline for minimum 15 days using the below command.
alter database <dbname> set offline
If you are not getting any calls regarding that database, you can backup
those databases , copy to a Tape (or other safe location) and delete the
database.
Thanks
Hari
MCDBA
"Ivan Mckenzie" <imck@.inerza.com> wrote in message
news:u6LMgydaEHA.1652@.TK2MSFTNGP09.phx.gbl...
> Thanks, I suppose this is done with SQL profiler.
>
> "Julie" <anonymous@.discussions.microsoft.com> escribi en el mensaje
> news:2d1d201c469d7$11c0f540$a501280a@.phx.gbl...
>
|||Thanks a lot.
"Hari Prasad" <hari_prasad_k@.hotmail.com> escribi en el mensaje
news:%23V5N%23SiaEHA.3420@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Run the Profiler for couple of week days . Identify the databases not
being
> used. Make those databases
> offline for minimum 15 days using the below command.
> alter database <dbname> set offline
> If you are not getting any calls regarding that database, you can backup
> those databases , copy to a Tape (or other safe location) and delete the
> database.
> --
> Thanks
> Hari
> MCDBA
> "Ivan Mckenzie" <imck@.inerza.com> wrote in message
> news:u6LMgydaEHA.1652@.TK2MSFTNGP09.phx.gbl...
>

Activity on a database?

Hi everyone, I have an SQL2000 with quiet a lot databases and I want to
delete some of them. How do I know if there has been any kind of activity on
a certain database, so I dont delete one that is being used.
Thanks a lot,
Ivan MckenzieTry seting up a trace, but just put in the connections,
then look at the databases connected to.
J
>--Original Message--
>Hi everyone, I have an SQL2000 with quiet a lot
databases and I want to
>delete some of them. How do I know if there has been any
kind of activity on
>a certain database, so I dont delete one that is being
used.
>Thanks a lot,
>Ivan Mckenzie
>
>.
>|||Thanks, I suppose this is done with SQL profiler.
"Julie" <anonymous@.discussions.microsoft.com> escribió en el mensaje
news:2d1d201c469d7$11c0f540$a501280a@.phx.gbl...
> Try seting up a trace, but just put in the connections,
> then look at the databases connected to.
> J
>
> >--Original Message--
> >Hi everyone, I have an SQL2000 with quiet a lot
> databases and I want to
> >delete some of them. How do I know if there has been any
> kind of activity on
> >a certain database, so I dont delete one that is being
> used.
> >
> >Thanks a lot,
> >
> >Ivan Mckenzie
> >
> >
> >.
> >|||Hi,
Run the Profiler for couple of week days . Identify the databases not being
used. Make those databases
offline for minimum 15 days using the below command.
alter database <dbname> set offline
If you are not getting any calls regarding that database, you can backup
those databases , copy to a Tape (or other safe location) and delete the
database.
--
Thanks
Hari
MCDBA
"Ivan Mckenzie" <imck@.inerza.com> wrote in message
news:u6LMgydaEHA.1652@.TK2MSFTNGP09.phx.gbl...
> Thanks, I suppose this is done with SQL profiler.
>
> "Julie" <anonymous@.discussions.microsoft.com> escribió en el mensaje
> news:2d1d201c469d7$11c0f540$a501280a@.phx.gbl...
> >
> > Try seting up a trace, but just put in the connections,
> > then look at the databases connected to.
> >
> > J
> >
> >
> > >--Original Message--
> > >Hi everyone, I have an SQL2000 with quiet a lot
> > databases and I want to
> > >delete some of them. How do I know if there has been any
> > kind of activity on
> > >a certain database, so I dont delete one that is being
> > used.
> > >
> > >Thanks a lot,
> > >
> > >Ivan Mckenzie
> > >
> > >
> > >.
> > >
>|||Thanks a lot.
"Hari Prasad" <hari_prasad_k@.hotmail.com> escribió en el mensaje
news:%23V5N%23SiaEHA.3420@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Run the Profiler for couple of week days . Identify the databases not
being
> used. Make those databases
> offline for minimum 15 days using the below command.
> alter database <dbname> set offline
> If you are not getting any calls regarding that database, you can backup
> those databases , copy to a Tape (or other safe location) and delete the
> database.
> --
> Thanks
> Hari
> MCDBA
> "Ivan Mckenzie" <imck@.inerza.com> wrote in message
> news:u6LMgydaEHA.1652@.TK2MSFTNGP09.phx.gbl...
> > Thanks, I suppose this is done with SQL profiler.
> >
> >
> >
> > "Julie" <anonymous@.discussions.microsoft.com> escribió en el mensaje
> > news:2d1d201c469d7$11c0f540$a501280a@.phx.gbl...
> > >
> > > Try seting up a trace, but just put in the connections,
> > > then look at the databases connected to.
> > >
> > > J
> > >
> > >
> > > >--Original Message--
> > > >Hi everyone, I have an SQL2000 with quiet a lot
> > > databases and I want to
> > > >delete some of them. How do I know if there has been any
> > > kind of activity on
> > > >a certain database, so I dont delete one that is being
> > > used.
> > > >
> > > >Thanks a lot,
> > > >
> > > >Ivan Mckenzie
> > > >
> > > >
> > > >.
> > > >
> >
> >
>

Activity on a database?

Try seting up a trace, but just put in the connections,
then look at the databases connected to.
J

>--Original Message--
>Hi everyone, I have an SQL2000 with quiet a lot
databases and I want to
>delete some of them. How do I know if there has been any
kind of activity on
>a certain database, so I dont delete one that is being
used.
>Thanks a lot,
>Ivan Mckenzie
>
>.
>Thanks, I suppose this is done with SQL profiler.
"Julie" <anonymous@.discussions.microsoft.com> escribi en el mensaje
news:2d1d201c469d7$11c0f540$a501280a@.phx
.gbl...[vbcol=seagreen]
> Try seting up a trace, but just put in the connections,
> then look at the databases connected to.
> J
>
> databases and I want to
> kind of activity on
> used.|||Hi,
Run the Profiler for couple of week days . Identify the databases not being
used. Make those databases
offline for minimum 15 days using the below command.
alter database <dbname> set offline
If you are not getting any calls regarding that database, you can backup
those databases , copy to a Tape (or other safe location) and delete the
database.
Thanks
Hari
MCDBA
"Ivan Mckenzie" <imck@.inerza.com> wrote in message
news:u6LMgydaEHA.1652@.TK2MSFTNGP09.phx.gbl...
> Thanks, I suppose this is done with SQL profiler.
>
> "Julie" <anonymous@.discussions.microsoft.com> escribi en el mensaje
> news:2d1d201c469d7$11c0f540$a501280a@.phx
.gbl...
>|||Thanks a lot.
"Hari Prasad" <hari_prasad_k@.hotmail.com> escribi en el mensaje
news:%23V5N%23SiaEHA.3420@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Run the Profiler for couple of week days . Identify the databases not
being
> used. Make those databases
> offline for minimum 15 days using the below command.
> alter database <dbname> set offline
> If you are not getting any calls regarding that database, you can backup
> those databases , copy to a Tape (or other safe location) and delete the
> database.
> --
> Thanks
> Hari
> MCDBA
> "Ivan Mckenzie" <imck@.inerza.com> wrote in message
> news:u6LMgydaEHA.1652@.TK2MSFTNGP09.phx.gbl...
>

Activity Monitoring for a Single Table

We started experiencing a strange problem with one of our database last week. After analyzing the data and comparing with multiple backup sets, we found that data was randomly being deleted from a single table.
Is there any way to monitor a single table for activity, add, delete, change, etc? We want to do this for a day or so to identify the offending process.
Thanks,
William (Bill) Corder
wscorder@.hotmail.com
Sure, look up CREATE TRIGGER in Books Online. You can also run a trace
using Profiler (or better yet, a server-side trace without the GUI)...
"William (Bill) Corder" <wscorder@.hotmail.com> wrote in message
news:07955481-D3E1-48A9-AB7A-16ACA438B7DA@.microsoft.com...
We started experiencing a strange problem with one of our database last
week. After analyzing the data and comparing with multiple backup sets, we
found that data was randomly being deleted from a single table.
Is there any way to monitor a single table for activity, add, delete,
change, etc? We want to do this for a day or so to identify the offending
process.
Thanks,
William (Bill) Corder
wscorder@.hotmail.com
|||Aaron,
Thanks, I am setting up a trace to monitor the activity.
Bill
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:e5TEw8BOIHA.3852@.TK2MSFTNGP06.phx.gbl...
> Sure, look up CREATE TRIGGER in Books Online. You can also run a trace
> using Profiler (or better yet, a server-side trace without the GUI)...
>
> "William (Bill) Corder" <wscorder@.hotmail.com> wrote in message
> news:07955481-D3E1-48A9-AB7A-16ACA438B7DA@.microsoft.com...
> We started experiencing a strange problem with one of our database last
> week. After analyzing the data and comparing with multiple backup sets,
> we found that data was randomly being deleted from a single table.
> Is there any way to monitor a single table for activity, add, delete,
> change, etc? We want to do this for a day or so to identify the offending
> process.
> --
> Thanks,
> William (Bill) Corder
> wscorder@.hotmail.com
>
>
|||William (Bill) Corder (wscorder@.hotmail.com) writes:
> We started experiencing a strange problem with one of our database last
> week. After analyzing the data and comparing with multiple backup sets,
> we found that data was randomly being deleted from a single table.
> Is there any way to monitor a single table for activity, add, delete,
> change, etc? We want to do this for a day or so to identify the
> offending process.
A trigger that logs the deleted rows together with columns populated with
the output from host_name(), app_name(), SYSTEM_USER, getdate() etc is
definitely what you need.
A server-side trace as Aaron suggested gives you the offending statement
more directly, so that is also a good idea. The log table gives you what
was deleted and when, and from that you can look up the trace.
I had to do this recently. Except that I did not come very far. That is,
before I deployed my trigger to production, I tested it, and when I ran
a delete operation from the GUI, I found that the log table said that
two rows had been deleted. Oh-oh. The culprit was a DELETE statement in
a procedure where I had forgotten the WHERE clause, blush!
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||The just re-did this with a trigger and it is more direct. I get exactly
what I need. All I had to do was create a separate log table to to hold the
data.
Thanks for the help and I will take a look at LogManager.

Thanks,
William (Bill) Corder
wscorder@.hotmail.com
"Thomas Steinmaurer" <t.steinmaurer_dontbugmewithspam_@.upscene.com> wrote in
message news:#fQucLOOIHA.1212@.TK2MSFTNGP05.phx.gbl...
> Hi!
>
> You probably won't get the old/new field values with that. This is easily
> done with triggers, as Aaron has pointed out.
> There are tools out there, which help you to setup a trigger-based
> auditing solution. For instance, our MSSQL LogManager product is one of
> them.
>
> --
> Best Regards,
> Thomas Steinmaurer
> LogManager Series - Logging/Auditing Suites supporting
> InterBase, Firebird, Advantage Database, MS SQL Server and
> NexusDB V2
> Upscene Productions
> http://www.upscene.com

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
>
>

Activity log backup

My company requires a 6 month retention of the archive activity logs for
SQL2000 servers. I need a job that will run each night moving the logs to an
archive file naming the logs to date.logs leaving only the "Current-date
time" log. I have set up the sp-cycle-errorlog to run each night. This
creates a new log in the number sequence.
Any assistance is greatly appreciated. Thanks.
Jerry
Hi
You may want to check out http://www.sqldts.com/default.aspx?292
John
"Jerry" wrote:

> My company requires a 6 month retention of the archive activity logs for
> SQL2000 servers. I need a job that will run each night moving the logs to an
> archive file naming the logs to date.logs leaving only the "Current-date
> time" log. I have set up the sp-cycle-errorlog to run each night. This
> creates a new log in the number sequence.
> Any assistance is greatly appreciated. Thanks.
> --
> Jerry

Activity log backup

My company requires a 6 month retention of the archive activity logs for
SQL2000 servers. I need a job that will run each night moving the logs to a
n
archive file naming the logs to date.logs leaving only the "Current-date
time" log. I have set up the sp-cycle-errorlog to run each night. This
creates a new log in the number sequence.
Any assistance is greatly appreciated. Thanks.
--
JerryHi
You may want to check out http://www.sqldts.com/default.aspx?292
John
"Jerry" wrote:

> My company requires a 6 month retention of the archive activity logs for
> SQL2000 servers. I need a job that will run each night moving the logs to
an
> archive file naming the logs to date.logs leaving only the "Current-date
> time" log. I have set up the sp-cycle-errorlog to run each night. This
> creates a new log in the number sequence.
> Any assistance is greatly appreciated. Thanks.
> --
> Jerry

Activity log backup

My company requires a 6 month retention of the archive activity logs for
SQL2000 servers. I need a job that will run each night moving the logs to an
archive file naming the logs to date.logs leaving only the "Current-date
time" log. I have set up the sp-cycle-errorlog to run each night. This
creates a new log in the number sequence.
Any assistance is greatly appreciated. Thanks.
--
JerryHi
You may want to check out http://www.sqldts.com/default.aspx?292
John
"Jerry" wrote:
> My company requires a 6 month retention of the archive activity logs for
> SQL2000 servers. I need a job that will run each night moving the logs to an
> archive file naming the logs to date.logs leaving only the "Current-date
> time" log. I have set up the sp-cycle-errorlog to run each night. This
> creates a new log in the number sequence.
> Any assistance is greatly appreciated. Thanks.
> --
> Jerry

Activity in one DB causes time-outs in another

We have several SQL 2000 databases on one server.
One of the applications I'm responsible for has batch jobs that run for an hour; all activity is on the database. During this hour, other applications that use other databases on the same server experience time-outs. One of my coworkers did a count(*) on an empty table and it took 11 seconds.

We pay people to keep our servers up and running. Is this something they might solve by reconfiguring the server? It seems strange to me that a single database is allowed to hog all server resources.
We are meeting with them later this week, and I'd like to have some knowledge about this; we don't want to BS'ed into buying a new server.personally, I would look at optimizing your hour long batch process if at all possible. If you follow the directions in Brett's sticky at the top, I am sure some one would be happy to look over your code for you.|||There's that,yes. Right now it's cursor driven, because, "you know, if we just make some changes to the previous version we'll be done quicker."

Data is read from a file (created at a specific point in time) into a single table, and the batch process consists of two stages: stage one is comparing the new data to what we already have, and create an entry in the log table for every difference. Stage two is changing our data so that it reflects the data in the file, for that particular point in time.

I'm pretty sure that stage one can be done set-based, but I'm not so sure about stage two. The actions required for each row in the new data depend on what's already there in our db.

Activity Captured by profiler

Hi
Does the profiler catch every activity on the server. I mean what about
the various username & password screens , dont you think then its a
threat to security .
& I wanted to knw how to get the last 10 SQL Statments issued by the
users client to the SQL Server.
dbcc inputbuffer (spid) gives us the last statement how to get a
history of statments by a SPID.
Thanks
Hi
Profiler will not display sensitive data such as passwords etc. If you are
worried about passwords and usernames you should also use protocol encryption
between client and server.
You can use a filter in profiler to restict information display to a given
SPID or use the search options to jump between entries for a given SPID.
John
"Double_B" wrote:

> Hi
> Does the profiler catch every activity on the server. I mean what about
> the various username & password screens , dont you think then its a
> threat to security .
> & I wanted to knw how to get the last 10 SQL Statments issued by the
> users client to the SQL Server.
> dbcc inputbuffer (spid) gives us the last statement how to get a
> history of statments by a SPID.
>
> Thanks
>

Activity Captured by profiler

Hi
Does the profiler catch every activity on the server. I mean what about
the various username & password screens , dont you think then its a
threat to security .
& I wanted to knw how to get the last 10 SQL Statments issued by the
users client to the SQL Server.
dbcc inputbuffer (spid) gives us the last statement how to get a
history of statments by a SPID.
ThanksHi
Profiler will not display sensitive data such as passwords etc. If you are
worried about passwords and usernames you should also use protocol encryptio
n
between client and server.
You can use a filter in profiler to restict information display to a given
SPID or use the search options to jump between entries for a given SPID.
John
"Double_B" wrote:

> Hi
> Does the profiler catch every activity on the server. I mean what about
> the various username & password screens , dont you think then its a
> threat to security .
> & I wanted to knw how to get the last 10 SQL Statments issued by the
> users client to the SQL Server.
> dbcc inputbuffer (spid) gives us the last statement how to get a
> history of statments by a SPID.
>
> Thanks
>

Activity Captured by profiler

Hi
Does the profiler catch every activity on the server. I mean what about
the various username & password screens , dont you think then its a
threat to security .
& I wanted to knw how to get the last 10 SQL Statments issued by the
users client to the SQL Server.
dbcc inputbuffer (spid) gives us the last statement how to get a
history of statments by a SPID.
ThanksHi
Profiler will not display sensitive data such as passwords etc. If you are
worried about passwords and usernames you should also use protocol encryption
between client and server.
You can use a filter in profiler to restict information display to a given
SPID or use the search options to jump between entries for a given SPID.
John
"Double_B" wrote:
> Hi
> Does the profiler catch every activity on the server. I mean what about
> the various username & password screens , dont you think then its a
> threat to security .
> & I wanted to knw how to get the last 10 SQL Statments issued by the
> users client to the SQL Server.
> dbcc inputbuffer (spid) gives us the last statement how to get a
> history of statments by a SPID.
>
> Thanks
>