detecting database access

Hi just wondering if there is an easy way to tell the last time an
application accessed a database? I have several databases and believe some
may not be in use so would be nice if there was an easy way to tell the last
time something was read or written to a table or the last time a stored
procedure was run.
--
Paul G
Software engineer.Paul,
You would need to have had Profiler or some third-party auditing tool
running to determine this. Another option would be to use some extra
auditing code in a the proc or use a trigger.
A third-party log viewing tool would be another option...though entailed.
HTH
Jerry
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:599A3E79-65D1-41B9-9B61-8356705A523C@.microsoft.com...
> Hi just wondering if there is an easy way to tell the last time an
> application accessed a database? I have several databases and believe
> some
> may not be in use so would be nice if there was an easy way to tell the
> last
> time something was read or written to a table or the last time a stored
> procedure was run.
> --
> Paul G
> Software engineer.|||ok thanks for the information. Am not familiar with triggers but have used
stored procedures, views dts packages ect.
--
Paul G
Software engineer.
"Jerry Spivey" wrote:
> Paul,
> You would need to have had Profiler or some third-party auditing tool
> running to determine this. Another option would be to use some extra
> auditing code in a the proc or use a trigger.
> A third-party log viewing tool would be another option...though entailed.
> HTH
> Jerry
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:599A3E79-65D1-41B9-9B61-8356705A523C@.microsoft.com...
> > Hi just wondering if there is an easy way to tell the last time an
> > application accessed a database? I have several databases and believe
> > some
> > may not be in use so would be nice if there was an easy way to tell the
> > last
> > time something was read or written to a table or the last time a stored
> > procedure was run.
> > --
> > Paul G
> > Software engineer.
>
>|||I have recently built and am testing a simple tracking query that reads
master..sysprocesses every minute and records into a log table any logins
that were not already recorded. Hostname and program_name fields may be of
interest to you...
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:47D3B512-CA5B-40D0-A79B-D0EB56428FC3@.microsoft.com...
> ok thanks for the information. Am not familiar with triggers but have
> used
> stored procedures, views dts packages ect.
> --
> Paul G
> Software engineer.
>
> "Jerry Spivey" wrote:
>> Paul,
>> You would need to have had Profiler or some third-party auditing tool
>> running to determine this. Another option would be to use some extra
>> auditing code in a the proc or use a trigger.
>> A third-party log viewing tool would be another option...though entailed.
>> HTH
>> Jerry
>>
>> "Paul" <Paul@.discussions.microsoft.com> wrote in message
>> news:599A3E79-65D1-41B9-9B61-8356705A523C@.microsoft.com...
>> > Hi just wondering if there is an easy way to tell the last time an
>> > application accessed a database? I have several databases and believe
>> > some
>> > may not be in use so would be nice if there was an easy way to tell the
>> > last
>> > time something was read or written to a table or the last time a stored
>> > procedure was run.
>> > --
>> > Paul G
>> > Software engineer.
>>|||Hi thanks for the information. Just wondering if this tool for sale, when is
final testing complete?
--
Paul G
Software engineer.
"Kevin3NF" wrote:
> I have recently built and am testing a simple tracking query that reads
> master..sysprocesses every minute and records into a log table any logins
> that were not already recorded. Hostname and program_name fields may be of
> interest to you...
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:47D3B512-CA5B-40D0-A79B-D0EB56428FC3@.microsoft.com...
> > ok thanks for the information. Am not familiar with triggers but have
> > used
> > stored procedures, views dts packages ect.
> > --
> > Paul G
> > Software engineer.
> >
> >
> > "Jerry Spivey" wrote:
> >
> >> Paul,
> >>
> >> You would need to have had Profiler or some third-party auditing tool
> >> running to determine this. Another option would be to use some extra
> >> auditing code in a the proc or use a trigger.
> >>
> >> A third-party log viewing tool would be another option...though entailed.
> >>
> >> HTH
> >>
> >> Jerry
> >>
> >>
> >> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> >> news:599A3E79-65D1-41B9-9B61-8356705A523C@.microsoft.com...
> >> > Hi just wondering if there is an easy way to tell the last time an
> >> > application accessed a database? I have several databases and believe
> >> > some
> >> > may not be in use so would be nice if there was an easy way to tell the
> >> > last
> >> > time something was read or written to a table or the last time a stored
> >> > procedure was run.
> >> > --
> >> > Paul G
> >> > Software engineer.
> >>
> >>
> >>
>
>|||For sale? nah...its just a hacked together query that you would need to
modify to include the columns you care about. In my case, I am tracking old
dead logins...
The table I dump to:
CREATE TABLE [dbo].[DBA_LoginCapture] (
[LCID] [int] IDENTITY (1, 1) NOT NULL ,
[DBName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[LoginName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[NTName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LoginTime] [datetime] NOT NULL
--add fields here as appropriate...
) ON [PRIMARY]
GO
The stored proc:
/*************************************************************************************
**
** Purpose: Captures all active logins for the current server
**
** History: 2005-09-27 Kevin Hill, 3NF Consulting Created
**
** Parameters: None
**
** Notes: This procedure is intended to be executed via a scheduled
job
** for later analysis to determine active/unsused logins.
**
*************************************************************************************/
CREATE PROCEDURE
dbo.DBA_InsertLoginCapture
AS
SET NOCOUNT ON
DECLARE @.Error INT
DECLARE @.RowCount INT
DECLARE @.Msg VARCHAR(500)
SET @.Error = 0
SET @.RowCount = 0
SET @.Msg = NULL
INSERT [DBA].[dbo].[DBA_LoginCapture](
[DBName],
[LoginName],
[NTName],
[LoginTime])
SELECT
sd.[name] AS dbname,
loginame,
nt_username,
login_time
FROM
master..sysprocesses sp
INNER JOIN master..sysdatabases sd
ON sp.dbid = sd.dbid
LEFT JOIN DBA..DBA_LoginCapture lc
ON sp.Loginame = lc.LoginName
AND sp.login_time = lc.LoginTime
WHERE
lc.loginName IS NULL
AND lc.LoginTime IS NULL
AND sd.[name] <> 'DBA'
SELECT @.Error = @.@.Error, @.RowCount = @.@.Rowcount
IF @.Error <> 0
BEGIN
EXEC dbo.DBA_SendMail
@.Subject = 'Error in DBA_InsertLoginCapture',
@.Message = ''
END
GO
The first time this runs, it will grab all rows from sysprocesses. Every
time after that it will grab whatever is not already there.
If you run it too often, it will take up more resources than you want
(possibly). Too infrequently and you may miss a login that comes and goes
between runs.
IMPORTANT: use at your own risk. If your data goes missing, it ain't my
fault ;-)
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:A63B31F8-BCF8-47A7-9CAF-80D3285C5D63@.microsoft.com...
> Hi thanks for the information. Just wondering if this tool for sale, when
> is
> final testing complete?
> --
> Paul G
> Software engineer.
>
> "Kevin3NF" wrote:
>> I have recently built and am testing a simple tracking query that reads
>> master..sysprocesses every minute and records into a log table any logins
>> that were not already recorded. Hostname and program_name fields may be
>> of
>> interest to you...
>> --
>> Kevin Hill
>> President
>> 3NF Consulting
>> www.3nf-inc.com/NewsGroups.htm
>>
>> "Paul" <Paul@.discussions.microsoft.com> wrote in message
>> news:47D3B512-CA5B-40D0-A79B-D0EB56428FC3@.microsoft.com...
>> > ok thanks for the information. Am not familiar with triggers but have
>> > used
>> > stored procedures, views dts packages ect.
>> > --
>> > Paul G
>> > Software engineer.
>> >
>> >
>> > "Jerry Spivey" wrote:
>> >
>> >> Paul,
>> >>
>> >> You would need to have had Profiler or some third-party auditing tool
>> >> running to determine this. Another option would be to use some extra
>> >> auditing code in a the proc or use a trigger.
>> >>
>> >> A third-party log viewing tool would be another option...though
>> >> entailed.
>> >>
>> >> HTH
>> >>
>> >> Jerry
>> >>
>> >>
>> >> "Paul" <Paul@.discussions.microsoft.com> wrote in message
>> >> news:599A3E79-65D1-41B9-9B61-8356705A523C@.microsoft.com...
>> >> > Hi just wondering if there is an easy way to tell the last time an
>> >> > application accessed a database? I have several databases and
>> >> > believe
>> >> > some
>> >> > may not be in use so would be nice if there was an easy way to tell
>> >> > the
>> >> > last
>> >> > time something was read or written to a table or the last time a
>> >> > stored
>> >> > procedure was run.
>> >> > --
>> >> > Paul G
>> >> > Software engineer.
>> >>
>> >>
>> >>
>>|||ok thanks for the information. Will look at it when I get a chance. We have
a new server not in production yet so I can safely test it there.
--
Paul G
Software engineer.
"Kevin3NF" wrote:
> For sale? nah...its just a hacked together query that you would need to
> modify to include the columns you care about. In my case, I am tracking old
> dead logins...
> The table I dump to:
> CREATE TABLE [dbo].[DBA_LoginCapture] (
> [LCID] [int] IDENTITY (1, 1) NOT NULL ,
> [DBName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [LoginName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [NTName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [LoginTime] [datetime] NOT NULL
> --add fields here as appropriate...
> ) ON [PRIMARY]
> GO
>
> The stored proc:
> /*************************************************************************************
> **
> ** Purpose: Captures all active logins for the current server
> **
> ** History: 2005-09-27 Kevin Hill, 3NF Consulting Created
> **
> ** Parameters: None
> **
> ** Notes: This procedure is intended to be executed via a scheduled
> job
> ** for later analysis to determine active/unsused logins.
> **
> *************************************************************************************/
> CREATE PROCEDURE
> dbo.DBA_InsertLoginCapture
> AS
> SET NOCOUNT ON
> DECLARE @.Error INT
> DECLARE @.RowCount INT
> DECLARE @.Msg VARCHAR(500)
> SET @.Error = 0
> SET @.RowCount = 0
> SET @.Msg = NULL
> INSERT [DBA].[dbo].[DBA_LoginCapture](
> [DBName],
> [LoginName],
> [NTName],
> [LoginTime])
> SELECT
> sd.[name] AS dbname,
> loginame,
> nt_username,
> login_time
> FROM
> master..sysprocesses sp
> INNER JOIN master..sysdatabases sd
> ON sp.dbid = sd.dbid
> LEFT JOIN DBA..DBA_LoginCapture lc
> ON sp.Loginame = lc.LoginName
> AND sp.login_time = lc.LoginTime
> WHERE
> lc.loginName IS NULL
> AND lc.LoginTime IS NULL
> AND sd.[name] <> 'DBA'
> SELECT @.Error = @.@.Error, @.RowCount = @.@.Rowcount
> IF @.Error <> 0
> BEGIN
> EXEC dbo.DBA_SendMail
> @.Subject = 'Error in DBA_InsertLoginCapture',
> @.Message = ''
> END
> GO
> The first time this runs, it will grab all rows from sysprocesses. Every
> time after that it will grab whatever is not already there.
> If you run it too often, it will take up more resources than you want
> (possibly). Too infrequently and you may miss a login that comes and goes
> between runs.
> IMPORTANT: use at your own risk. If your data goes missing, it ain't my
> fault ;-)
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:A63B31F8-BCF8-47A7-9CAF-80D3285C5D63@.microsoft.com...
> > Hi thanks for the information. Just wondering if this tool for sale, when
> > is
> > final testing complete?
> > --
> > Paul G
> > Software engineer.
> >
> >
> > "Kevin3NF" wrote:
> >
> >> I have recently built and am testing a simple tracking query that reads
> >> master..sysprocesses every minute and records into a log table any logins
> >> that were not already recorded. Hostname and program_name fields may be
> >> of
> >> interest to you...
> >>
> >> --
> >> Kevin Hill
> >> President
> >> 3NF Consulting
> >>
> >> www.3nf-inc.com/NewsGroups.htm
> >>
> >>
> >> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> >> news:47D3B512-CA5B-40D0-A79B-D0EB56428FC3@.microsoft.com...
> >> > ok thanks for the information. Am not familiar with triggers but have
> >> > used
> >> > stored procedures, views dts packages ect.
> >> > --
> >> > Paul G
> >> > Software engineer.
> >> >
> >> >
> >> > "Jerry Spivey" wrote:
> >> >
> >> >> Paul,
> >> >>
> >> >> You would need to have had Profiler or some third-party auditing tool
> >> >> running to determine this. Another option would be to use some extra
> >> >> auditing code in a the proc or use a trigger.
> >> >>
> >> >> A third-party log viewing tool would be another option...though
> >> >> entailed.
> >> >>
> >> >> HTH
> >> >>
> >> >> Jerry
> >> >>
> >> >>
> >> >> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> >> >> news:599A3E79-65D1-41B9-9B61-8356705A523C@.microsoft.com...
> >> >> > Hi just wondering if there is an easy way to tell the last time an
> >> >> > application accessed a database? I have several databases and
> >> >> > believe
> >> >> > some
> >> >> > may not be in use so would be nice if there was an easy way to tell
> >> >> > the
> >> >> > last
> >> >> > time something was read or written to a table or the last time a
> >> >> > stored
> >> >> > procedure was run.
> >> >> > --
> >> >> > Paul G
> >> >> > Software engineer.
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>