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

From: Joe Yong (NO_jyong_SPAM_at_scalabilityexperts.com)
Date: 09/27/05

  • Next message: Bad Beagle: "Split roles" 
    Date: Tue, 27 Sep 2005 11:26:42 -0500

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

  • Next message: Bad Beagle: "Split roles"

    Relevant Pages

    • Re: CONTAINS performance
      ... That said, and with the query plan, I can start to give you more ... relational join in the context of the free-text optimization, ... SQL Server tables. ...
      (microsoft.public.sqlserver.fulltext)
    • Re: Indexing Service, Openquery and sp_executesql
      ... SQL Server version and sp are you running? ... > data from the indexing service catalog when pasted into ... > query analyzer, but failed when put against sp_executesql ... I would choose Microsoft Indexing ...
      (microsoft.public.sqlserver.fulltext)
    • Re: Problem using Access or Query Designer to run queries in SQL Serve
      ... >or Query Designer within Enterprise Manager, it works and I get data back. ... >ODBC Call Failed [ODBC SQL Server Driver] Timeout Expirederror in Access ... >[ODBC SQL Server Driver] Timeout Expired ...
      (microsoft.public.sqlserver.odbc)
    • Re: Transaction Isolation Level
      ... This means that while I initiate a transaction selecting all new orders it ... My query is executed on a SQL server 2005. ... the isolation level you should use is snapshot isolation. ...
      (comp.databases.ms-sqlserver)
    • Extreme performance issues (SQL Server 2000/ADO.NET/C#)
      ... This process runs very quickly if run through Query ... same exact stored procedures and views, run in the same exact order, through ... system that runs SQL Server (a 4-cpu Xeons system with 2gigs of physical ... When I execute these steps manually through query analyser,, ...
      (microsoft.public.dotnet.framework.adonet)