Post reply

How active are my databases?

  • January 5, 2004 at 9:24 am

    #107567

    Hi,

     

    I reviewed the article by Haidong Ji titled “Using SQL Trace to Audit Database Access” and this fits my environment to a “T’.

     

    To implement disk management and backup & recovery strategies, I want to identify active and stagnant legacy databases on my SQL Server 7/2000 environments.

     

    Any thoughts, suggestions, examples by trial or articles that you can point me towards would be greatly appreciated. 

     

    Many thanks.

    Jeff

     

    Many thanks. Jeff

  • January 7, 2004 at 10:35 pm

    #489075

    Log file(s) growing rate is a good indicator.

  • January 8, 2004 at 7:52 am

    #489131

    Every 5 minutes, I issue sp_who2 from a SQL Agent Job. I save the data to a table and then can query the table over time to see what databases are being used and by absence which ones are not.

    If you need to know down to the table level, you could generate a profiler script to get similar results.

     

    CREATE TABLE [dbo].[db_usage] (

     [spid] [int] NULL ,

     [status] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [login] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [hostname] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [blkby] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [dbname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [command] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [cputime] [int] NULL ,

     [diskio] [int] NULL ,

     [lastbatch] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [programname] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [spid2] [int] NULL

    ) ON [PRIMARY]

    GO

    CREATE TABLE [dbo].[db_usage_ref] (

     [spid] [int] NULL ,

     [status] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [login] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [hostname] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [blkby] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [dbname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [command] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [cputime] [int] NULL ,

     [diskio] [int] NULL ,

     [lastbatch] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [programname] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [spid2] [int] NULL ,

     [survey_time] [datetime] NULL

    ) ON [PRIMARY]

    GO

    --Step1 of SQL Agent Job

    insert db_usage

    exec sp_who2

    --Step2 of SQL Agent Job

    insert into db_usage_ref(spid, status, login, hostname, blkby, dbname, command, cputime, diskio, lastbatch, programname, spid2, survey_time)

    select *, getdate() as survey_time

    from db_usage

    --Step3 of SQL Agent Job

    truncate table db_usage

     

     



    Michelle

  • January 8, 2004 at 10:26 am

    #489170

    Either the solution aobve (which seems simpler and less problem prone) or Profliler will work.

    I've done something similar to that above to track conneciton counts.

     

  • January 8, 2004 at 11:28 am

    #489188

    Hi,

    Any suggestions as to what "events" I should add when I set up my trace within SQL Profiler?

    THanks. Jeff

    Many thanks. Jeff

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply