Is there a log of activity as compared to the Tran log?

I am trying to identify if I have unwanted guests gaining access to my
system or it's a run away query by a user on my network. I am thinking of
recording SPIDs as one way.
Via Spotlight I can see that I'm having beyond normal usage. Granted I have
a new PHB who likes to run queries off his laptop to show off to other
managers. Real scary when he has a little understanding of the data.
The box running Spotlight is WAY under powered, but can it keep that logging
data instead of the server itself?You can create your own log with profiler - it's a good tool
to track down performance issues in a database. If you use
it and performance is a concern, don't trace to the database
or from the database. Trace from a client and if tracing to
a file, have it go on that client.
Or use a server side trace. But you'd probably want to play
around with profiler from a client first.
-Sue
On Fri, 23 Sep 2005 08:30:52 -0500, "Stephen Russell"
<srussell@.transactiongraphics.com> wrote:

>I am trying to identify if I have unwanted guests gaining access to my
>system or it's a run away query by a user on my network. I am thinking of
>recording SPIDs as one way.
>Via Spotlight I can see that I'm having beyond normal usage. Granted I hav
e
>a new PHB who likes to run queries off his laptop to show off to other
>managers. Real scary when he has a little understanding of the data.
>The box running Spotlight is WAY under powered, but can it keep that loggin
g
>data instead of the server itself?
>|||2 things here really
1. Tracking "unwanted guests" gaining access to your SQL Server is easily
tracked with no perf hit by setting "Audit level" to "All". This is strictly
for login attempts and can be se via EM. Right click on your server, select
properties and click on the security tab. This puts an entry in the logs
everytime a login attempt is successfully or failed. At the very minimum,
you should log failures.
2. Runaway queries are a little harder to do with SQL Server 2000. First,
you need to define what you mean by runaway queries. CPU consumption?
Memory? Length of query? SQL Profiler and a bunch of 3rd party tools from
folks like Quest, BMC, etc... let's you capture the appropriate data to
"look at" so you can figure out who's got a runaway query. Depending on what
you use, you may have to do extra work. For example, if you capture trace
info with Profiler or SQL Trace, you'll need a way to track the data and
raise alerts based on thresholds that you set. Life gets a LOT better in
2005 with the default "reports" (it's a cool dashboard really) but in 2000,
there's a little bit of work needed to get you there. :-)
Btw, one thing you might want to consider is the Query Governor cost limit
option. It's not the most "precise" tool you can have since it works based
on estimates but it's a pretty good start with minimal effort. Look it up in
BOL. It's well documented.
joe.
"Stephen Russell" <srussell@.transactiongraphics.com> wrote in message
news:u5%23LlMEwFHA.664@.tk2msftngp13.phx.gbl...
>I am trying to identify if I have unwanted guests gaining access to my
>system or it's a run away query by a user on my network. I am thinking of
>recording SPIDs as one way.
> Via Spotlight I can see that I'm having beyond normal usage. Granted I
> have a new PHB who likes to run queries off his laptop to show off to
> other managers. Real scary when he has a little understanding of the
> data.
> The box running Spotlight is WAY under powered, but can it keep that
> logging data instead of the server itself?
>
>