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...
>
>
|||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...[vbcol=seagreen]
> 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:
|||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...
>
>
|||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...[vbcol=seagreen]
> Hi thanks for the information. Just wondering if this tool for sale, when
> is
> final testing complete?
> --
> Paul G
> Software engineer.
>
> "Kevin3NF" wrote:
|||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...
>
>
Showing posts with label believe. Show all posts
Showing posts with label believe. Show all posts
Thursday, March 22, 2012
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.
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
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.
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
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...
>
>|||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...[vbcol=seagreen]
> 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:
>|||Hi thanks for the information. Just wondering if this tool for sale, when i
s
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 o
f
> 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...
>
>|||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 NUL
L ,
[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...[vbcol=seagreen]
> Hi thanks for the information. Just wondering if this tool for sale, when
> is
> final testing complete?
> --
> Paul G
> Software engineer.
>
> "Kevin3NF" wrote:
>|||ok thanks for the information. Will look at it when I get a chance. We hav
e
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 o
ld
> 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 schedule
d
> 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...
>
>
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...
>
>|||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...[vbcol=seagreen]
> 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:
>|||Hi thanks for the information. Just wondering if this tool for sale, when i
s
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 o
f
> 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...
>
>|||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 NUL
L ,
[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...[vbcol=seagreen]
> Hi thanks for the information. Just wondering if this tool for sale, when
> is
> final testing complete?
> --
> Paul G
> Software engineer.
>
> "Kevin3NF" wrote:
>|||ok thanks for the information. Will look at it when I get a chance. We hav
e
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 o
ld
> 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 schedule
d
> 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...
>
>
Sunday, March 11, 2012
Detach/Attach Via Management Studio
I really can't believe how difficult this is.
Open SQL Server Management Studio, browse to the databases, right-click the
one to detach, select Tasks>Detach... Check the Drop Connections checkbox,
click OK... Bang! Failed.
"The Database is not accessable..." pops up during the process. Click on the
OK.
"Cannot detach the database 'test' because it is currently in use.
(Microsoft SQL Server, Error: 3703)"
Hold on... Didn't I ask it to drop the connections? So why is it still in
use?
Look at the database, it's in Single User mode. OK. Let's get it back to
normal... Right-click, select properties... Bang! Error.
"Cannot show requested dialog."
"Database 'test' is already open and can only have one user at a time.
(Microsoft SQL Server, Error: 924)"
Who the heck has it open? Check the Activity Monitor:
A suspended delete command through the web application db user...
"(@.p2 int)BEGIN CONVERSATION TIMER ('37238b35-6439-db11-934c-00137260bfc2')
TIMEOUT = 120; WAITFOR(RECEIVE TOP (1) message_type_name,
conversation_handle, cast(message_body AS XML) as message_body from
[SqlQueryNotificationService-5710e78f-2bab-4e58-8567-edb949981446]), TIMEOUT
@.p2;"
Unfortunately, I can't seem to kill the damn process. It just refuses to go
away. And the developers have no idea what it's for, so it must be some .NET
2.0 assembly thing.
Anyone know how to deal with this apart from shutting down the web
application server?Hi,
Open the query window and try this script...
ALTER DATABASE <DBNAME> SET SINGLE_USER WITH Rollback Immediate
GO
SP_Detach_db <dbname>
Thanks
Hari
SQL Server MVP
"Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
news:OGNd4KXzGHA.3464@.TK2MSFTNGP03.phx.gbl...
>I really can't believe how difficult this is.
> Open SQL Server Management Studio, browse to the databases, right-click
> the one to detach, select Tasks>Detach... Check the Drop Connections
> checkbox, click OK... Bang! Failed.
> "The Database is not accessable..." pops up during the process. Click on
> the OK.
> "Cannot detach the database 'test' because it is currently in use.
> (Microsoft SQL Server, Error: 3703)"
> Hold on... Didn't I ask it to drop the connections? So why is it still in
> use?
> Look at the database, it's in Single User mode. OK. Let's get it back to
> normal... Right-click, select properties... Bang! Error.
> "Cannot show requested dialog."
> "Database 'test' is already open and can only have one user at a time.
> (Microsoft SQL Server, Error: 924)"
> Who the heck has it open? Check the Activity Monitor:
> A suspended delete command through the web application db user...
> "(@.p2 int)BEGIN CONVERSATION TIMER
> ('37238b35-6439-db11-934c-00137260bfc2') TIMEOUT = 120; WAITFOR(RECEIVE
> TOP (1) message_type_name, conversation_handle, cast(message_body AS XML)
> as message_body from
> [SqlQueryNotificationService-5710e78f-2bab-4e58-8567-edb949981446]),
> TIMEOUT @.p2;"
> Unfortunately, I can't seem to kill the damn process. It just refuses to
> go away. And the developers have no idea what it's for, so it must be some
> .NET 2.0 assembly thing.
> Anyone know how to deal with this apart from shutting down the web
> application server?
>|||The process is the server side of the Dependency client event that they use
to get a notification when something changes the data for a specified query.
It should be killable unless the client is restarting it. You may need to
stop the client app to get it to go away.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
news:OGNd4KXzGHA.3464@.TK2MSFTNGP03.phx.gbl...
>I really can't believe how difficult this is.
> Open SQL Server Management Studio, browse to the databases, right-click
> the one to detach, select Tasks>Detach... Check the Drop Connections
> checkbox, click OK... Bang! Failed.
> "The Database is not accessable..." pops up during the process. Click on
> the OK.
> "Cannot detach the database 'test' because it is currently in use.
> (Microsoft SQL Server, Error: 3703)"
> Hold on... Didn't I ask it to drop the connections? So why is it still in
> use?
> Look at the database, it's in Single User mode. OK. Let's get it back to
> normal... Right-click, select properties... Bang! Error.
> "Cannot show requested dialog."
> "Database 'test' is already open and can only have one user at a time.
> (Microsoft SQL Server, Error: 924)"
> Who the heck has it open? Check the Activity Monitor:
> A suspended delete command through the web application db user...
> "(@.p2 int)BEGIN CONVERSATION TIMER
> ('37238b35-6439-db11-934c-00137260bfc2') TIMEOUT = 120; WAITFOR(RECEIVE
> TOP (1) message_type_name, conversation_handle, cast(message_body AS XML)
> as message_body from
> [SqlQueryNotificationService-5710e78f-2bab-4e58-8567-edb949981446]),
> TIMEOUT @.p2;"
> Unfortunately, I can't seem to kill the damn process. It just refuses to
> go away. And the developers have no idea what it's for, so it must be some
> .NET 2.0 assembly thing.
> Anyone know how to deal with this apart from shutting down the web
> application server?
>|||There was a Service Broker for the client running on the database server
that wouldn't let the process finish. Rebooting the web application server
allowed me to detach/attach, but I was also able to do the same by removing
and adding the broker.
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:OL%23bT2XzGHA.1536@.TK2MSFTNGP02.phx.gbl...
> The process is the server side of the Dependency client event that they
> use to get a notification when something changes the data for a specified
> query. It should be killable unless the client is restarting it. You may
> need to stop the client app to get it to go away.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
> news:OGNd4KXzGHA.3464@.TK2MSFTNGP03.phx.gbl...
>>I really can't believe how difficult this is.
>> Open SQL Server Management Studio, browse to the databases, right-click
>> the one to detach, select Tasks>Detach... Check the Drop Connections
>> checkbox, click OK... Bang! Failed.
>> "The Database is not accessable..." pops up during the process. Click on
>> the OK.
>> "Cannot detach the database 'test' because it is currently in use.
>> (Microsoft SQL Server, Error: 3703)"
>> Hold on... Didn't I ask it to drop the connections? So why is it still in
>> use?
>> Look at the database, it's in Single User mode. OK. Let's get it back to
>> normal... Right-click, select properties... Bang! Error.
>> "Cannot show requested dialog."
>> "Database 'test' is already open and can only have one user at a time.
>> (Microsoft SQL Server, Error: 924)"
>> Who the heck has it open? Check the Activity Monitor:
>> A suspended delete command through the web application db user...
>> "(@.p2 int)BEGIN CONVERSATION TIMER
>> ('37238b35-6439-db11-934c-00137260bfc2') TIMEOUT = 120; WAITFOR(RECEIVE
>> TOP (1) message_type_name, conversation_handle, cast(message_body AS XML)
>> as message_body from
>> [SqlQueryNotificationService-5710e78f-2bab-4e58-8567-edb949981446]),
>> TIMEOUT @.p2;"
>> Unfortunately, I can't seem to kill the damn process. It just refuses to
>> go away. And the developers have no idea what it's for, so it must be
>> some .NET 2.0 assembly thing.
>> Anyone know how to deal with this apart from shutting down the web
>> application server?
>>
>
Open SQL Server Management Studio, browse to the databases, right-click the
one to detach, select Tasks>Detach... Check the Drop Connections checkbox,
click OK... Bang! Failed.
"The Database is not accessable..." pops up during the process. Click on the
OK.
"Cannot detach the database 'test' because it is currently in use.
(Microsoft SQL Server, Error: 3703)"
Hold on... Didn't I ask it to drop the connections? So why is it still in
use?
Look at the database, it's in Single User mode. OK. Let's get it back to
normal... Right-click, select properties... Bang! Error.
"Cannot show requested dialog."
"Database 'test' is already open and can only have one user at a time.
(Microsoft SQL Server, Error: 924)"
Who the heck has it open? Check the Activity Monitor:
A suspended delete command through the web application db user...
"(@.p2 int)BEGIN CONVERSATION TIMER ('37238b35-6439-db11-934c-00137260bfc2')
TIMEOUT = 120; WAITFOR(RECEIVE TOP (1) message_type_name,
conversation_handle, cast(message_body AS XML) as message_body from
[SqlQueryNotificationService-5710e78f-2bab-4e58-8567-edb949981446]), TIMEOUT
@.p2;"
Unfortunately, I can't seem to kill the damn process. It just refuses to go
away. And the developers have no idea what it's for, so it must be some .NET
2.0 assembly thing.
Anyone know how to deal with this apart from shutting down the web
application server?Hi,
Open the query window and try this script...
ALTER DATABASE <DBNAME> SET SINGLE_USER WITH Rollback Immediate
GO
SP_Detach_db <dbname>
Thanks
Hari
SQL Server MVP
"Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
news:OGNd4KXzGHA.3464@.TK2MSFTNGP03.phx.gbl...
>I really can't believe how difficult this is.
> Open SQL Server Management Studio, browse to the databases, right-click
> the one to detach, select Tasks>Detach... Check the Drop Connections
> checkbox, click OK... Bang! Failed.
> "The Database is not accessable..." pops up during the process. Click on
> the OK.
> "Cannot detach the database 'test' because it is currently in use.
> (Microsoft SQL Server, Error: 3703)"
> Hold on... Didn't I ask it to drop the connections? So why is it still in
> use?
> Look at the database, it's in Single User mode. OK. Let's get it back to
> normal... Right-click, select properties... Bang! Error.
> "Cannot show requested dialog."
> "Database 'test' is already open and can only have one user at a time.
> (Microsoft SQL Server, Error: 924)"
> Who the heck has it open? Check the Activity Monitor:
> A suspended delete command through the web application db user...
> "(@.p2 int)BEGIN CONVERSATION TIMER
> ('37238b35-6439-db11-934c-00137260bfc2') TIMEOUT = 120; WAITFOR(RECEIVE
> TOP (1) message_type_name, conversation_handle, cast(message_body AS XML)
> as message_body from
> [SqlQueryNotificationService-5710e78f-2bab-4e58-8567-edb949981446]),
> TIMEOUT @.p2;"
> Unfortunately, I can't seem to kill the damn process. It just refuses to
> go away. And the developers have no idea what it's for, so it must be some
> .NET 2.0 assembly thing.
> Anyone know how to deal with this apart from shutting down the web
> application server?
>|||The process is the server side of the Dependency client event that they use
to get a notification when something changes the data for a specified query.
It should be killable unless the client is restarting it. You may need to
stop the client app to get it to go away.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
news:OGNd4KXzGHA.3464@.TK2MSFTNGP03.phx.gbl...
>I really can't believe how difficult this is.
> Open SQL Server Management Studio, browse to the databases, right-click
> the one to detach, select Tasks>Detach... Check the Drop Connections
> checkbox, click OK... Bang! Failed.
> "The Database is not accessable..." pops up during the process. Click on
> the OK.
> "Cannot detach the database 'test' because it is currently in use.
> (Microsoft SQL Server, Error: 3703)"
> Hold on... Didn't I ask it to drop the connections? So why is it still in
> use?
> Look at the database, it's in Single User mode. OK. Let's get it back to
> normal... Right-click, select properties... Bang! Error.
> "Cannot show requested dialog."
> "Database 'test' is already open and can only have one user at a time.
> (Microsoft SQL Server, Error: 924)"
> Who the heck has it open? Check the Activity Monitor:
> A suspended delete command through the web application db user...
> "(@.p2 int)BEGIN CONVERSATION TIMER
> ('37238b35-6439-db11-934c-00137260bfc2') TIMEOUT = 120; WAITFOR(RECEIVE
> TOP (1) message_type_name, conversation_handle, cast(message_body AS XML)
> as message_body from
> [SqlQueryNotificationService-5710e78f-2bab-4e58-8567-edb949981446]),
> TIMEOUT @.p2;"
> Unfortunately, I can't seem to kill the damn process. It just refuses to
> go away. And the developers have no idea what it's for, so it must be some
> .NET 2.0 assembly thing.
> Anyone know how to deal with this apart from shutting down the web
> application server?
>|||There was a Service Broker for the client running on the database server
that wouldn't let the process finish. Rebooting the web application server
allowed me to detach/attach, but I was also able to do the same by removing
and adding the broker.
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:OL%23bT2XzGHA.1536@.TK2MSFTNGP02.phx.gbl...
> The process is the server side of the Dependency client event that they
> use to get a notification when something changes the data for a specified
> query. It should be killable unless the client is restarting it. You may
> need to stop the client app to get it to go away.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
> news:OGNd4KXzGHA.3464@.TK2MSFTNGP03.phx.gbl...
>>I really can't believe how difficult this is.
>> Open SQL Server Management Studio, browse to the databases, right-click
>> the one to detach, select Tasks>Detach... Check the Drop Connections
>> checkbox, click OK... Bang! Failed.
>> "The Database is not accessable..." pops up during the process. Click on
>> the OK.
>> "Cannot detach the database 'test' because it is currently in use.
>> (Microsoft SQL Server, Error: 3703)"
>> Hold on... Didn't I ask it to drop the connections? So why is it still in
>> use?
>> Look at the database, it's in Single User mode. OK. Let's get it back to
>> normal... Right-click, select properties... Bang! Error.
>> "Cannot show requested dialog."
>> "Database 'test' is already open and can only have one user at a time.
>> (Microsoft SQL Server, Error: 924)"
>> Who the heck has it open? Check the Activity Monitor:
>> A suspended delete command through the web application db user...
>> "(@.p2 int)BEGIN CONVERSATION TIMER
>> ('37238b35-6439-db11-934c-00137260bfc2') TIMEOUT = 120; WAITFOR(RECEIVE
>> TOP (1) message_type_name, conversation_handle, cast(message_body AS XML)
>> as message_body from
>> [SqlQueryNotificationService-5710e78f-2bab-4e58-8567-edb949981446]),
>> TIMEOUT @.p2;"
>> Unfortunately, I can't seem to kill the damn process. It just refuses to
>> go away. And the developers have no idea what it's for, so it must be
>> some .NET 2.0 assembly thing.
>> Anyone know how to deal with this apart from shutting down the web
>> application server?
>>
>
Friday, March 9, 2012
Detach/Attach Via Management Studio
I really can't believe how difficult this is.
Open SQL Server Management Studio, browse to the databases, right-click the
one to detach, select Tasks>Detach... Check the Drop Connections checkbox,
click OK... Bang! Failed.
"The Database is not accessable..." pops up during the process. Click on the
OK.
"Cannot detach the database 'test' because it is currently in use.
(Microsoft SQL Server, Error: 3703)"
Hold on... Didn't I ask it to drop the connections? So why is it still in
use?
Look at the database, it's in Single User mode. OK. Let's get it back to
normal... Right-click, select properties... Bang! Error.
"Cannot show requested dialog."
"Database 'test' is already open and can only have one user at a time.
(Microsoft SQL Server, Error: 924)"
Who the heck has it open? Check the Activity Monitor:
A suspended delete command through the web application db user...
"(@.p2 int)BEGIN CONVERSATION TIMER ('37238b35-6439-db11-934c-00137260bfc2')
TIMEOUT = 120; WAITFOR(RECEIVE TOP (1) message_type_name,
conversation_handle, cast(message_body AS XML) as message_body from
[SqlQueryNotificationService-5710e78f-2bab-4e58-8567-edb949981446]), TIM
EOUT
@.p2;"
Unfortunately, I can't seem to kill the damn process. It just refuses to go
away. And the developers have no idea what it's for, so it must be some .NET
2.0 assembly thing.
Anyone know how to deal with this apart from shutting down the web
application server?Hi,
Open the query window and try this script...
ALTER DATABASE <DBNAME> SET SINGLE_USER WITH Rollback Immediate
GO
SP_Detach_db <dbname>
Thanks
Hari
SQL Server MVP
"Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
news:OGNd4KXzGHA.3464@.TK2MSFTNGP03.phx.gbl...
>I really can't believe how difficult this is.
> Open SQL Server Management Studio, browse to the databases, right-click
> the one to detach, select Tasks>Detach... Check the Drop Connections
> checkbox, click OK... Bang! Failed.
> "The Database is not accessable..." pops up during the process. Click on
> the OK.
> "Cannot detach the database 'test' because it is currently in use.
> (Microsoft SQL Server, Error: 3703)"
> Hold on... Didn't I ask it to drop the connections? So why is it still in
> use?
> Look at the database, it's in Single User mode. OK. Let's get it back to
> normal... Right-click, select properties... Bang! Error.
> "Cannot show requested dialog."
> "Database 'test' is already open and can only have one user at a time.
> (Microsoft SQL Server, Error: 924)"
> Who the heck has it open? Check the Activity Monitor:
> A suspended delete command through the web application db user...
> "(@.p2 int)BEGIN CONVERSATION TIMER
> ('37238b35-6439-db11-934c-00137260bfc2') TIMEOUT = 120; WAITFOR(RECEIVE
> TOP (1) message_type_name, conversation_handle, cast(message_body AS XML)
> as message_body from
> [SqlQueryNotificationService-5710e78f-2bab-4e58-8567-edb949981446]),
> TIMEOUT @.p2;"
> Unfortunately, I can't seem to kill the damn process. It just refuses to
> go away. And the developers have no idea what it's for, so it must be some
> .NET 2.0 assembly thing.
> Anyone know how to deal with this apart from shutting down the web
> application server?
>|||The process is the server side of the Dependency client event that they use
to get a notification when something changes the data for a specified query.
It should be killable unless the client is restarting it. You may need to
stop the client app to get it to go away.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
news:OGNd4KXzGHA.3464@.TK2MSFTNGP03.phx.gbl...
>I really can't believe how difficult this is.
> Open SQL Server Management Studio, browse to the databases, right-click
> the one to detach, select Tasks>Detach... Check the Drop Connections
> checkbox, click OK... Bang! Failed.
> "The Database is not accessable..." pops up during the process. Click on
> the OK.
> "Cannot detach the database 'test' because it is currently in use.
> (Microsoft SQL Server, Error: 3703)"
> Hold on... Didn't I ask it to drop the connections? So why is it still in
> use?
> Look at the database, it's in Single User mode. OK. Let's get it back to
> normal... Right-click, select properties... Bang! Error.
> "Cannot show requested dialog."
> "Database 'test' is already open and can only have one user at a time.
> (Microsoft SQL Server, Error: 924)"
> Who the heck has it open? Check the Activity Monitor:
> A suspended delete command through the web application db user...
> "(@.p2 int)BEGIN CONVERSATION TIMER
> ('37238b35-6439-db11-934c-00137260bfc2') TIMEOUT = 120; WAITFOR(RECEIVE
> TOP (1) message_type_name, conversation_handle, cast(message_body AS XML)
> as message_body from
> [SqlQueryNotificationService-5710e78f-2bab-4e58-8567-edb949981446]),
> TIMEOUT @.p2;"
> Unfortunately, I can't seem to kill the damn process. It just refuses to
> go away. And the developers have no idea what it's for, so it must be some
> .NET 2.0 assembly thing.
> Anyone know how to deal with this apart from shutting down the web
> application server?
>|||There was a Service Broker for the client running on the database server
that wouldn't let the process finish. Rebooting the web application server
allowed me to detach/attach, but I was also able to do the same by removing
and adding the broker.
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:OL%23bT2XzGHA.1536@.TK2MSFTNGP02.phx.gbl...
> The process is the server side of the Dependency client event that they
> use to get a notification when something changes the data for a specified
> query. It should be killable unless the client is restarting it. You may
> need to stop the client app to get it to go away.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
> news:OGNd4KXzGHA.3464@.TK2MSFTNGP03.phx.gbl...
>
Open SQL Server Management Studio, browse to the databases, right-click the
one to detach, select Tasks>Detach... Check the Drop Connections checkbox,
click OK... Bang! Failed.
"The Database is not accessable..." pops up during the process. Click on the
OK.
"Cannot detach the database 'test' because it is currently in use.
(Microsoft SQL Server, Error: 3703)"
Hold on... Didn't I ask it to drop the connections? So why is it still in
use?
Look at the database, it's in Single User mode. OK. Let's get it back to
normal... Right-click, select properties... Bang! Error.
"Cannot show requested dialog."
"Database 'test' is already open and can only have one user at a time.
(Microsoft SQL Server, Error: 924)"
Who the heck has it open? Check the Activity Monitor:
A suspended delete command through the web application db user...
"(@.p2 int)BEGIN CONVERSATION TIMER ('37238b35-6439-db11-934c-00137260bfc2')
TIMEOUT = 120; WAITFOR(RECEIVE TOP (1) message_type_name,
conversation_handle, cast(message_body AS XML) as message_body from
[SqlQueryNotificationService-5710e78f-2bab-4e58-8567-edb949981446]), TIM
EOUT
@.p2;"
Unfortunately, I can't seem to kill the damn process. It just refuses to go
away. And the developers have no idea what it's for, so it must be some .NET
2.0 assembly thing.
Anyone know how to deal with this apart from shutting down the web
application server?Hi,
Open the query window and try this script...
ALTER DATABASE <DBNAME> SET SINGLE_USER WITH Rollback Immediate
GO
SP_Detach_db <dbname>
Thanks
Hari
SQL Server MVP
"Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
news:OGNd4KXzGHA.3464@.TK2MSFTNGP03.phx.gbl...
>I really can't believe how difficult this is.
> Open SQL Server Management Studio, browse to the databases, right-click
> the one to detach, select Tasks>Detach... Check the Drop Connections
> checkbox, click OK... Bang! Failed.
> "The Database is not accessable..." pops up during the process. Click on
> the OK.
> "Cannot detach the database 'test' because it is currently in use.
> (Microsoft SQL Server, Error: 3703)"
> Hold on... Didn't I ask it to drop the connections? So why is it still in
> use?
> Look at the database, it's in Single User mode. OK. Let's get it back to
> normal... Right-click, select properties... Bang! Error.
> "Cannot show requested dialog."
> "Database 'test' is already open and can only have one user at a time.
> (Microsoft SQL Server, Error: 924)"
> Who the heck has it open? Check the Activity Monitor:
> A suspended delete command through the web application db user...
> "(@.p2 int)BEGIN CONVERSATION TIMER
> ('37238b35-6439-db11-934c-00137260bfc2') TIMEOUT = 120; WAITFOR(RECEIVE
> TOP (1) message_type_name, conversation_handle, cast(message_body AS XML)
> as message_body from
> [SqlQueryNotificationService-5710e78f-2bab-4e58-8567-edb949981446]),
> TIMEOUT @.p2;"
> Unfortunately, I can't seem to kill the damn process. It just refuses to
> go away. And the developers have no idea what it's for, so it must be some
> .NET 2.0 assembly thing.
> Anyone know how to deal with this apart from shutting down the web
> application server?
>|||The process is the server side of the Dependency client event that they use
to get a notification when something changes the data for a specified query.
It should be killable unless the client is restarting it. You may need to
stop the client app to get it to go away.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
news:OGNd4KXzGHA.3464@.TK2MSFTNGP03.phx.gbl...
>I really can't believe how difficult this is.
> Open SQL Server Management Studio, browse to the databases, right-click
> the one to detach, select Tasks>Detach... Check the Drop Connections
> checkbox, click OK... Bang! Failed.
> "The Database is not accessable..." pops up during the process. Click on
> the OK.
> "Cannot detach the database 'test' because it is currently in use.
> (Microsoft SQL Server, Error: 3703)"
> Hold on... Didn't I ask it to drop the connections? So why is it still in
> use?
> Look at the database, it's in Single User mode. OK. Let's get it back to
> normal... Right-click, select properties... Bang! Error.
> "Cannot show requested dialog."
> "Database 'test' is already open and can only have one user at a time.
> (Microsoft SQL Server, Error: 924)"
> Who the heck has it open? Check the Activity Monitor:
> A suspended delete command through the web application db user...
> "(@.p2 int)BEGIN CONVERSATION TIMER
> ('37238b35-6439-db11-934c-00137260bfc2') TIMEOUT = 120; WAITFOR(RECEIVE
> TOP (1) message_type_name, conversation_handle, cast(message_body AS XML)
> as message_body from
> [SqlQueryNotificationService-5710e78f-2bab-4e58-8567-edb949981446]),
> TIMEOUT @.p2;"
> Unfortunately, I can't seem to kill the damn process. It just refuses to
> go away. And the developers have no idea what it's for, so it must be some
> .NET 2.0 assembly thing.
> Anyone know how to deal with this apart from shutting down the web
> application server?
>|||There was a Service Broker for the client running on the database server
that wouldn't let the process finish. Rebooting the web application server
allowed me to detach/attach, but I was also able to do the same by removing
and adding the broker.
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:OL%23bT2XzGHA.1536@.TK2MSFTNGP02.phx.gbl...
> The process is the server side of the Dependency client event that they
> use to get a notification when something changes the data for a specified
> query. It should be killable unless the client is restarting it. You may
> need to stop the client app to get it to go away.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
> news:OGNd4KXzGHA.3464@.TK2MSFTNGP03.phx.gbl...
>
Subscribe to:
Posts (Atom)