Showing posts with label databases. Show all posts
Showing posts with label databases. Show all posts

Thursday, March 29, 2012

Determine the last time SP was executed

Hello -

I am trying to determine the last time a SP was executed. Does anyone
know how to do this? I'm trying to cleanup some databases.

Thanks!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!You need to be running PROFILER...which would be a big drain to run it 100% in
parallel with a production server...

I suggest running it for a day or 2, during days when you know every part of an
application will get 'touched'. Then investigate the profiler output for your
target objects.|||I have a SP usage table that I use to see what SP's are being ran and
when. The table consists of an ID (autogen), sp_name, LastTimeUsed,
TimesUsed. I can keep track of what SP's are being used, when they
were last used and how many times they were used. I did this one day
as a CYA measure, and found it very useful. And the overhead for my
system is very small.

dcmfan@.aol.comSPNOAM (DCM Fan) wrote in message news:<20031210191602.04241.00000665@.mb-m29.aol.com>...
> You need to be running PROFILER...which would be a big drain to run it 100% in
> parallel with a production server...
> I suggest running it for a day or 2, during days when you know every part of an
> application will get 'touched'. Then investigate the profiler output for your
> target objects.|||Thanks!

Thats what I thought but I was hoping there was somthing else.

Thanks Again!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!sql

Sunday, March 25, 2012

Determine database no longer in use - Redux

I've attempted a number of methods to capture whether
specific databases are in use. I've configured an alert
for each one to trigger if the number of Active
Transactions exceeds 0. If this occurs, a job is run
which sends me an email via SMTP.
The question I have right now is what qualifies as an
Active Transaction? I can't find any reference to the
specific performance conditions in the BOL, just how to
set them up. I make changes to a test database and
sometimes the alert fires and sometimes it doesn't.
Thanks for any insight.
AllenAllen
One way you could do it is to detach the databases you
think are not being used and wait to see if anyone
complains. If you do make a mistake you can attach again
very quickly.
Depending on your company and what the databases are used
for you may or may not be able to do this. I know I would
not be able to, but it would work.
You could also use profiler to see if there is any
activity on a database. You might have issues with that if
you need to run it for several days and you have other
active databases on the same server.
Hope this helps
John
John

Determine database no longer in use

I've got a server with 36 databases which may or may not
be in use any longer. I spent yesterday running SQL
Profiler on that server but the trace doesn't capture the
database in use (or if it does, it doesn't give me the
option of displaying the database).
Does anyone have a relatively painless way (without
affecting performance, if possible) of capturing any
access to any of these databases, so I know whether or not
I can drop the database?
Thanks.
AllenIf you are saving the Profiler trace to a DB table and you only have the
database ID to go on then use DB_NAME(database ID) to get the name.
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Allen White" <awhite@.advanstar.com> wrote in message
news:0c1b01c37222$8ca69dd0$a301280a@.phx.gbl...
> I've got a server with 36 databases which may or may not
> be in use any longer. I spent yesterday running SQL
> Profiler on that server but the trace doesn't capture the
> database in use (or if it does, it doesn't give me the
> option of displaying the database).
> Does anyone have a relatively painless way (without
> affecting performance, if possible) of capturing any
> access to any of these databases, so I know whether or not
> I can drop the database?
> Thanks.
> Allen|||Apparently I wasn't very clear. I have 36 databases which
I'm not sure are still in use at all. I was hoping that
someone could tell me a way I could determine if the
database was accessed at any time within the last couple
of weeks or any time in the next couple of weeks, without
relying on luck for me to 'catch' it via sp_who, or
capturing megabytes worth of data using the profiler, just
to see if the database was used.
Does anyone know a way to trigger something so that if any
one of these databases is accessed I can find out which
one? Something like creating an alert that fires when the
database is used.
Any assistance is greatly appreciated!
Allen
>--Original Message--
>I've got a server with 36 databases which may or may not
>be in use any longer. I spent yesterday running SQL
>Profiler on that server but the trace doesn't capture the
>database in use (or if it does, it doesn't give me the
>option of displaying the database).
>Does anyone have a relatively painless way (without
>affecting performance, if possible) of capturing any
>access to any of these databases, so I know whether or
not
>I can drop the database?
>Thanks.
>Allen
>.
>

Determine All User Databases

Hi All
I have a SQL Server 2005 server and I was wanting to determine all of the
user databases and I was wondering if there was a better way to do this
instead of the following script:
select name
from master.sys.databases
where name not in ('mater', 'tempdb', 'msdb', 'model')
Thanks
If these are the only 4 db's that you want to eliminate, then you could also
query based on database_id, and then it wouldn't matter if you don't spell
one of the database names correctly. :-) You also don't need a 3-part name
when you use the new metadata views.
SELECT name FROM sys.databases
WHERE database_id > 4
If you set up replication, some servers will get a distribution database,
and you may want to eliminate that one also.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"David" <David@.discussions.microsoft.com> wrote in message
news:7B789F7B-702A-4109-9FEB-D8E6F27A4FE5@.microsoft.com...
> Hi All
> I have a SQL Server 2005 server and I was wanting to determine all of the
> user databases and I was wondering if there was a better way to do this
> instead of the following script:
> select name
> from master.sys.databases
> where name not in ('mater', 'tempdb', 'msdb', 'model')
> Thanks

Determine All User Databases

Hi All
I have a SQL Server 2005 server and I was wanting to determine all of the
user databases and I was wondering if there was a better way to do this
instead of the following script:
select name
from master.sys.databases
where name not in ('mater', 'tempdb', 'msdb', 'model')
ThanksIf these are the only 4 db's that you want to eliminate, then you could also
query based on database_id, and then it wouldn't matter if you don't spell
one of the database names correctly. :-) You also don't need a 3-part name
when you use the new metadata views.
SELECT name FROM sys.databases
WHERE database_id > 4
If you set up replication, some servers will get a distribution database,
and you may want to eliminate that one also.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"David" <David@.discussions.microsoft.com> wrote in message
news:7B789F7B-702A-4109-9FEB-D8E6F27A4FE5@.microsoft.com...
> Hi All
> I have a SQL Server 2005 server and I was wanting to determine all of the
> user databases and I was wondering if there was a better way to do this
> instead of the following script:
> select name
> from master.sys.databases
> where name not in ('mater', 'tempdb', 'msdb', 'model')
> Thanks

Determine All User Databases

Hi All
I have a SQL Server 2005 server and I was wanting to determine all of the
user databases and I was wondering if there was a better way to do this
instead of the following script:
select name
from master.sys.databases
where name not in ('mater', 'tempdb', 'msdb', 'model')
ThanksIf these are the only 4 db's that you want to eliminate, then you could also
query based on database_id, and then it wouldn't matter if you don't spell
one of the database names correctly. :-) You also don't need a 3-part name
when you use the new metadata views.
SELECT name FROM sys.databases
WHERE database_id > 4
If you set up replication, some servers will get a distribution database,
and you may want to eliminate that one also.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"David" <David@.discussions.microsoft.com> wrote in message
news:7B789F7B-702A-4109-9FEB-D8E6F27A4FE5@.microsoft.com...
> Hi All
> I have a SQL Server 2005 server and I was wanting to determine all of the
> user databases and I was wondering if there was a better way to do this
> instead of the following script:
> select name
> from master.sys.databases
> where name not in ('mater', 'tempdb', 'msdb', 'model')
> Thanks

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

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

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

Monday, March 19, 2012

Detaching system databases...

I need to detach (in order to move the datafiles) MASTER,
MSDB, MODEL, TEMPDB. I am using EM and the sp_detach
options om SQL Server 2000 but it's saying "Can't detach
system databases". Am I missing a step?
Do I need to end all connections?
Thanks,
Todd
Why are you detaching the system databases? Are you planning on moving them
to another server? Here is some information about moving system databases,
that might help.
http://support.microsoft.com/default...&Product=sql2k
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Todd" <anonymous@.discussions.microsoft.com> wrote in message
news:f1c901c43db7$4ed332f0$a401280a@.phx.gbl...
> I need to detach (in order to move the datafiles) MASTER,
> MSDB, MODEL, TEMPDB. I am using EM and the sp_detach
> options om SQL Server 2000 but it's saying "Can't detach
> system databases". Am I missing a step?
> Do I need to end all connections?
> Thanks,
> Todd
|||Ugh, you can go through everything in KB #224071 and cross your fingers that
it all works well. Or, you can detach your *user* databases (or hopefully
you already have valid backups you can simply restore), uninstall SQL
Server, and then reinstall - placing the data directory where you should
have.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Todd" <anonymous@.discussions.microsoft.com> wrote in message
news:f1c901c43db7$4ed332f0$a401280a@.phx.gbl...
> I need to detach (in order to move the datafiles) MASTER,
> MSDB, MODEL, TEMPDB. I am using EM and the sp_detach
> options om SQL Server 2000 but it's saying "Can't detach
> system databases". Am I missing a step?
> Do I need to end all connections?
> Thanks,
> Todd
|||I want to move the datafiles because I put them on the C:\
(only 3GB) by mistake when I installed the SQL Server. I
want to move everything to another drive with 50GB+. I
have moved the user databases successfully but the system
databases don't want to go.
I reinstall would not be bad but I would prefer not to;)
=20
>--Original Message--
>Why are you detaching the system databases? Are you
planning on moving them
>to another server? Here is some information about moving
system databases,
>that might help.
>http://support.microsoft.com/default...24071&Product=
=3Dsql2k
>--=20
>---=
--
>---=
--
>--
>Need SQL Server Examples check out my website at
>http://www.geocities.com/sqlserverexamples
>"Todd" <anonymous@.discussions.microsoft.com> wrote in message
>news:f1c901c43db7$4ed332f0$a401280a@.phx.gbl...
>
>.
>
|||>> I reinstall would not be bad but I would prefer not to;)
Why? It's much cleaner, IMHO.
|||KB 224071, which Aaron suggested, describes moving files for system databases.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<anonymous@.discussions.microsoft.com> wrote in message news:f40501c43dc5$006531a0$a101280a@.phx.gbl...
I want to move the datafiles because I put them on the C:\
(only 3GB) by mistake when I installed the SQL Server. I
want to move everything to another drive with 50GB+. I
have moved the user databases successfully but the system
databases don't want to go.
I reinstall would not be bad but I would prefer not to;)

>--Original Message--
>Why are you detaching the system databases? Are you
planning on moving them
>to another server? Here is some information about moving
system databases,
>that might help.
>http://support.microsoft.com/default...&Product=sql2k
>--
>----
>----
>--
>Need SQL Server Examples check out my website at
>http://www.geocities.com/sqlserverexamples
>"Todd" <anonymous@.discussions.microsoft.com> wrote in message
>news:f1c901c43db7$4ed332f0$a401280a@.phx.gbl...
>
>.
>

Detaching system databases...

I need to detach (in order to move the datafiles) MASTER,
MSDB, MODEL, TEMPDB. I am using EM and the sp_detach
options om SQL Server 2000 but it's saying "Can't detach
system databases". Am I missing a step?
Do I need to end all connections'
Thanks,
ToddWhy are you detaching the system databases? Are you planning on moving them
to another server? Here is some information about moving system databases,
that might help.
http://support.microsoft.com/default.aspx?scid=kb;en-us;224071&Product=sql2k
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Todd" <anonymous@.discussions.microsoft.com> wrote in message
news:f1c901c43db7$4ed332f0$a401280a@.phx.gbl...
> I need to detach (in order to move the datafiles) MASTER,
> MSDB, MODEL, TEMPDB. I am using EM and the sp_detach
> options om SQL Server 2000 but it's saying "Can't detach
> system databases". Am I missing a step?
> Do I need to end all connections'
> Thanks,
> Todd|||Ugh, you can go through everything in KB #224071 and cross your fingers that
it all works well. Or, you can detach your *user* databases (or hopefully
you already have valid backups you can simply restore), uninstall SQL
Server, and then reinstall - placing the data directory where you should
have.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Todd" <anonymous@.discussions.microsoft.com> wrote in message
news:f1c901c43db7$4ed332f0$a401280a@.phx.gbl...
> I need to detach (in order to move the datafiles) MASTER,
> MSDB, MODEL, TEMPDB. I am using EM and the sp_detach
> options om SQL Server 2000 but it's saying "Can't detach
> system databases". Am I missing a step?
> Do I need to end all connections'
> Thanks,
> Todd|||I want to move the datafiles because I put them on the C:\
(only 3GB) by mistake when I installed the SQL Server. I
want to move everything to another drive with 50GB+. I
have moved the user databases successfully but the system
databases don't want to go.
I reinstall would not be bad but I would prefer not to;)
>--Original Message--
>Why are you detaching the system databases? Are you
planning on moving them
>to another server? Here is some information about moving
system databases,
>that might help.
>http://support.microsoft.com/default.aspx?scid=3Dkb;en-us;224071&Product=
=3Dsql2k
>-- >---=--
>---=--
>--
>Need SQL Server Examples check out my website at
>http://www.geocities.com/sqlserverexamples
>"Todd" <anonymous@.discussions.microsoft.com> wrote in message
>news:f1c901c43db7$4ed332f0$a401280a@.phx.gbl...
>> I need to detach (in order to move the datafiles) MASTER,
>> MSDB, MODEL, TEMPDB. I am using EM and the sp_detach
>> options om SQL Server 2000 but it's saying "Can't detach
>> system databases". Am I missing a step?
>> Do I need to end all connections'
>> Thanks,
>> Todd
>
>.
>|||>> I reinstall would not be bad but I would prefer not to;)
Why? It's much cleaner, IMHO.|||KB 224071, which Aaron suggested, describes moving files for system databases.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<anonymous@.discussions.microsoft.com> wrote in message news:f40501c43dc5$006531a0$a101280a@.phx.gbl...
I want to move the datafiles because I put them on the C:\
(only 3GB) by mistake when I installed the SQL Server. I
want to move everything to another drive with 50GB+. I
have moved the user databases successfully but the system
databases don't want to go.
I reinstall would not be bad but I would prefer not to;)
>--Original Message--
>Why are you detaching the system databases? Are you
planning on moving them
>to another server? Here is some information about moving
system databases,
>that might help.
>http://support.microsoft.com/default.aspx?scid=kb;en-us;224071&Product=sql2k
>--
>----
>----
>--
>Need SQL Server Examples check out my website at
>http://www.geocities.com/sqlserverexamples
>"Todd" <anonymous@.discussions.microsoft.com> wrote in message
>news:f1c901c43db7$4ed332f0$a401280a@.phx.gbl...
>> I need to detach (in order to move the datafiles) MASTER,
>> MSDB, MODEL, TEMPDB. I am using EM and the sp_detach
>> options om SQL Server 2000 but it's saying "Can't detach
>> system databases". Am I missing a step?
>> Do I need to end all connections'
>> Thanks,
>> Todd
>
>.
>

Detaching system databases...

I need to detach (in order to move the datafiles) MASTER,
MSDB, MODEL, TEMPDB. I am using EM and the sp_detach
options om SQL Server 2000 but it's saying "Can't detach
system databases". Am I missing a step?
Do I need to end all connections'
Thanks,
ToddWhy are you detaching the system databases? Are you planning on moving them
to another server? Here is some information about moving system databases,
that might help.
http://support.microsoft.com/defaul...1&Product=sql2k
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Todd" <anonymous@.discussions.microsoft.com> wrote in message
news:f1c901c43db7$4ed332f0$a401280a@.phx.gbl...
> I need to detach (in order to move the datafiles) MASTER,
> MSDB, MODEL, TEMPDB. I am using EM and the sp_detach
> options om SQL Server 2000 but it's saying "Can't detach
> system databases". Am I missing a step?
> Do I need to end all connections'
> Thanks,
> Todd|||Ugh, you can go through everything in KB #224071 and cross your fingers that
it all works well. Or, you can detach your *user* databases (or hopefully
you already have valid backups you can simply restore), uninstall SQL
Server, and then reinstall - placing the data directory where you should
have.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Todd" <anonymous@.discussions.microsoft.com> wrote in message
news:f1c901c43db7$4ed332f0$a401280a@.phx.gbl...
> I need to detach (in order to move the datafiles) MASTER,
> MSDB, MODEL, TEMPDB. I am using EM and the sp_detach
> options om SQL Server 2000 but it's saying "Can't detach
> system databases". Am I missing a step?
> Do I need to end all connections'
> Thanks,
> Todd|||I want to move the datafiles because I put them on the C:\
(only 3GB) by mistake when I installed the SQL Server. I
want to move everything to another drive with 50GB+. I
have moved the user databases successfully but the system
databases don't want to go.
I reinstall would not be bad but I would prefer not to;)
=20
>--Original Message--
>Why are you detaching the system databases? Are you
planning on moving them
>to another server? Here is some information about moving
system databases,
>that might help.
>http://support.microsoft.com/defaul...224071&Product=
=3Dsql2k
>--=20
>---=
--
>---=
--
>--
>Need SQL Server Examples check out my website at
>http://www.geocities.com/sqlserverexamples
>"Todd" <anonymous@.discussions.microsoft.com> wrote in message
>news:f1c901c43db7$4ed332f0$a401280a@.phx.gbl...
>
>.
>|||>> I reinstall would not be bad but I would prefer not to;)
Why? It's much cleaner, IMHO.|||KB 224071, which Aaron suggested, describes moving files for system database
s.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<anonymous@.discussions.microsoft.com> wrote in message news:f40501c43dc5$006
531a0$a101280a@.phx.gbl...
I want to move the datafiles because I put them on the C:\
(only 3GB) by mistake when I installed the SQL Server. I
want to move everything to another drive with 50GB+. I
have moved the user databases successfully but the system
databases don't want to go.
I reinstall would not be bad but I would prefer not to;)

>--Original Message--
>Why are you detaching the system databases? Are you
planning on moving them
>to another server? Here is some information about moving
system databases,
>that might help.
>]
>--
>----
-
>----
-
>--
>Need SQL Server Examples check out my website at
>[url]http://www.geocities.com/sqlserverexamples" target="_blank">http://support.microsoft.com/defaul...lserverexamples
>"Todd" <anonymous@.discussions.microsoft.com> wrote in message
>news:f1c901c43db7$4ed332f0$a401280a@.phx.gbl...
>
>.
>

Sunday, March 11, 2012

Detaching and moving databases

Not sure if this is the exact forum I need but here goes.

Having a bit of an issue 'moving' a database. I've only just installed SQL Server 2005 on a Development/Test box so I can learn and mess around with it without doing any serious damage to anything important.

One of the first things I've done is install the AdventureWorks database and, having installed it, decided I would move the files to their optimum locations. However having detached the database I can't seem to move or copy the .mdf or .ldf files - something still seems to have its tentacles around them - 'Access denied' error. Even rebooting the server doesn't work. So I've had to delete then re-attach the database. The question then is does Detach serve any useful purpose? Or am am doing something wrong? With SS2000 I could detach a database, relocate the physical files and re-attach in seconds. The same procedure with SS2005 has taken me an hour!

Regards,

Gordon F.Do you have any anti-virus or anti-spyware tools installed on this test box?|||

When you detached the database (assuming Management Studio here) did you check the "Drop Connections" box? Without that, connections are not forced to drop, and you may have lingering connections to the database. I'm not sure why rebooting wouldn't resolve that though.

I just went through the whole sequence on my SQL 2005 instance, and it works as expected - no delays.

|||Also check the ACL on the file itself. when I had this issue I gave explicit permissions to the Service account on the mdf and ldf files and then I could copy the files.

Detached model and msdb - sql server stopped!

Hi,
I detached my model and msdb databases to move the data
and log files. The server went down prior to my
reattaching the model. Now, every time I start SQL, it
goes down when I try to connect, or change options, or try
to reattach model, etc.
Can you help?
Lisa.Perhaps you can start SQL Server from the command prompt with mimimal =configuration and then fix the problems. ---
This information is from Books Online. ---
Starting SQL Server with Minimal Configuration
If you have configuration problems that prevent the server from =starting, you can start an instance of Microsoft=AE SQL ServerT using =the minimal configuration startup option. This is the startup option -f. =Starting an instance of SQL Server with minimal configuration places the =server in single-user mode automatically.
When you start an instance of SQL Server in minimal configuration mode:
Only a single user can connect, and the CHECKPOINT process is not =executed.
Remote access and read-ahead are disabled.
Startup stored procedures do not run.
The sp_configure stored procedure allow updates option is enabled. By =default, the allow updates option is disabled. After the server has been started with minimal configuration, you should =change the appropriate server option value or values, stop, and then =restart the server.
Important Stop the SQL Server Agent service before connecting to an =instance of SQL Server in minimal configuration mode. Otherwise, the SQL =Server Agent service uses the connection, thereby blocking it.
To start SQL Server with minimal configuration
Command Prompt
How to start the default instance of SQL Server with minimal =configuration (Command Prompt)
To start the default instance of SQL Server with minimal configuration
From a command prompt, enter the following command to start the default =instance of Microsoft=AE SQL ServerT as a service: sqlservr -c -f
Note You must switch to the appropriate directory (for the instance of =SQL Server you want to start) in the command window before starting =sqlservr.exe.
-- Keith, SQL Server MVP
"Lisa" <lisajphillip@.kerzner.com> wrote in message =news:02f701c34b03$838bace0$a301280a@.phx.gbl...
> Hi,
> > I detached my model and msdb databases to move the data > and log files. The server went down prior to my > reattaching the model. Now, every time I start SQL, it > goes down when I try to connect, or change options, or try > to reattach model, etc.
> Can you help?
> > Lisa.
>|||Hi,
Thanks for the input. I did try it a few times, and finally got it right. I reattached the databases using isql, as enterprise manager couldn't connect.
Thanks again!
Lisa.
>--Original Message--
>Perhaps you can start SQL Server from the command prompt with mimimal configuration and then fix the problems. >---
>This information is from Books Online. >---
>Starting SQL Server with Minimal Configuration
>If you have configuration problems that prevent the server from starting, you can start an instance of Microsoft=AE SQL ServerT using the minimal configuration startup option. This is the startup option -f. Starting an instance of SQL Server with minimal configuration places the server in single-user mode automatically.
>When you start an instance of SQL Server in minimal configuration mode: >Only a single user can connect, and the CHECKPOINT process is not executed.
>
>Remote access and read-ahead are disabled.
>
>Startup stored procedures do not run.
>
>The sp_configure stored procedure allow updates option is enabled. By default, the allow updates option is disabled. >After the server has been started with minimal configuration, you should change the appropriate server option value or values, stop, and then restart the server.
>
>Important Stop the SQL Server Agent service before connecting to an instance of SQL Server in minimal configuration mode. Otherwise, the SQL Server Agent service uses the connection, thereby blocking it.
>
>To start SQL Server with minimal configuration
>Command Prompt
>
>How to start the default instance of SQL Server with minimal configuration (Command Prompt)
>To start the default instance of SQL Server with minimal configuration >From a command prompt, enter the following command to start the default instance of Microsoft=AE SQL ServerT as a service: >sqlservr -c -f
>
> >Note You must switch to the appropriate directory (for the instance of SQL Server you want to start) in the command window before starting sqlservr.exe.
>
>-- >Keith, SQL Server MVP
> >"Lisa" <lisajphillip@.kerzner.com> wrote in message news:02f701c34b03$838bace0$a301280a@.phx.gbl...
>> Hi,
>> >> I detached my model and msdb databases to move the data >> and log files. The server went down prior to my >> reattaching the model. Now, every time I start SQL, it >> goes down when I try to connect, or change options, or try >> to reattach model, etc.
>> Can you help?
>> >> Lisa.
>> >.
>|||Great! -- Keith, SQL Server MVP
"Lisa" <lisaj.phillip@.kerzner.com> wrote in message =news:04ed01c34b0f$86f79130$a101280a@.phx.gbl...
Hi,
Thanks for the input. I did try it a few times, and finally got it right. I reattached the databases using isql, as enterprise manager couldn't connect.
Thanks again!
Lisa.
>--Original Message--
>Perhaps you can start SQL Server from the command prompt with mimimal configuration and then fix the problems. >---
>This information is from Books Online. >---
>Starting SQL Server with Minimal Configuration
>If you have configuration problems that prevent the server from starting, you can start an instance of Microsoft=AE SQL ServerT using the minimal configuration startup option. This is the startup option -f. Starting an instance of SQL Server with minimal configuration places the server in single-user mode automatically.
>When you start an instance of SQL Server in minimal configuration mode: >Only a single user can connect, and the CHECKPOINT process is not executed.
>
>Remote access and read-ahead are disabled.
>
>Startup stored procedures do not run.
>
>The sp_configure stored procedure allow updates option is enabled. By default, the allow updates option is disabled. >After the server has been started with minimal configuration, you should change the appropriate server option value or values, stop, and then restart the server.
>
>Important Stop the SQL Server Agent service before connecting to an instance of SQL Server in minimal configuration mode. Otherwise, the SQL Server Agent service uses the connection, thereby blocking it.
>
>To start SQL Server with minimal configuration
>Command Prompt
>
>How to start the default instance of SQL Server with minimal configuration (Command Prompt)
>To start the default instance of SQL Server with minimal configuration >From a command prompt, enter the following command to start the default instance of Microsoft=AE SQL ServerT as a service: >sqlservr -c -f
>
> >Note You must switch to the appropriate directory (for the instance of SQL Server you want to start) in the command window before starting sqlservr.exe.
>
>-- >Keith, SQL Server MVP
> >"Lisa" <lisajphillip@.kerzner.com> wrote in message news:02f701c34b03$838bace0$a301280a@.phx.gbl...
>> Hi,
>> >> I detached my model and msdb databases to move the data >> and log files. The server went down prior to my >> reattaching the model. Now, every time I start SQL, it >> goes down when I try to connect, or change options, or try >> to reattach model, etc.
>> Can you help?
>> >> Lisa.
>> >.
>

Detach-Attach

Can we detach and attach SQL 7.0 databases
What are options to move databases in SQl 7.0
Thanks
SanjayHi Sanjay
In SQL Server 7, detach and attach was available as stored procedures, but
not from the Enterprise Manager.
Please read about sp_detachdb and sp_attachdb in the Books Online.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:00aa01c39d72$f41b0570$a301280a@.phx.gbl...
> Can we detach and attach SQL 7.0 databases
> What are options to move databases in SQl 7.0
> Thanks
> Sanjay|||> What are options to move databases in SQl 7.0
In addition to Kalen's response, backup and restore is an option for moving
databases as well.
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:00aa01c39d72$f41b0570$a301280a@.phx.gbl...
> Can we detach and attach SQL 7.0 databases
> What are options to move databases in SQl 7.0
> Thanks
> Sanjay|||In addition to previous responses you can also use DTS for
the database transformation. And in emergency cases also
BCP can be used as well.
Regards
----
All information provided above AS IS
>--Original Message--
>Can we detach and attach SQL 7.0 databases
>What are options to move databases in SQl 7.0
>Thanks
>Sanjay
>.
>

Detach/Delete

I have noticed that a Detach is almost instant and a Delete can take a few.
I have detached a lot of databases and am wondering if there is some kind of
cleanup I should be doing. I know the Detach leaves the data files intact,
that's why I do it. But does it also leave a lot of crap in the master or
msdb databases?
John
How do you perform the "delete" (I assume you mean DROP DATABASE)? If some the GUI tool then the
extra time might be removal of backup history information in msdb for the database. I doubt that
detach does this.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Holt" <johnh@.regionv.k12.mn.us> wrote in message
news:%23IosITt7GHA.4996@.TK2MSFTNGP04.phx.gbl...
>I have noticed that a Detach is almost instant and a Delete can take a few. I have detached a lot
>of databases and am wondering if there is some kind of cleanup I should be doing. I know the
>Detach leaves the data files intact, that's why I do it. But does it also leave a lot of crap in
>the master or msdb databases?
> John
>
|||I use SQL2k5 Management Studio. So every database that I detach, I need to
delete the backup history data in msdb for that database?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u4xObXt7GHA.3604@.TK2MSFTNGP02.phx.gbl...
> How do you perform the "delete" (I assume you mean DROP DATABASE)? If some
> the GUI tool then the extra time might be removal of backup history
> information in msdb for the database. I doubt that detach does this.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John Holt" <johnh@.regionv.k12.mn.us> wrote in message
> news:%23IosITt7GHA.4996@.TK2MSFTNGP04.phx.gbl...
>
|||>I use SQL2k5 Management Studio. So every database that I detach, I need to delete the backup
>history data in msdb for that database?
You don't have to do that, if you don't mind that backup history hanging around... You should have
some job that prunes backup history in any case, so with time history for this database will be
removed.
Whether b-history is removed, well that is easy enough to answer:
1. Read source code for sp_detach_db. I just did, and it doesn't remove backup history.
2. Whether SSMS removes it for you, you can find out by for instance running a Profiler trace when
detaching a database. My guess is that SSMS does not do this for you.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Holt" <johnh@.regionv.k12.mn.us> wrote in message news:uvqmkIw7GHA.2364@.TK2MSFTNGP02.phx.gbl...
>I use SQL2k5 Management Studio. So every database that I detach, I need to delete the backup
>history data in msdb for that database?
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:u4xObXt7GHA.3604@.TK2MSFTNGP02.phx.gbl...
>
|||I'll have to get rid of the junk when I have bit of time. It doesn't seem
to bother anything performance wise. Nice to keep it tidy though.
Thanks for info Tibor.:-)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O8vzmRw7GHA.940@.TK2MSFTNGP03.phx.gbl...
> You don't have to do that, if you don't mind that backup history hanging
> around... You should have some job that prunes backup history in any case,
> so with time history for this database will be removed.
> Whether b-history is removed, well that is easy enough to answer:
> 1. Read source code for sp_detach_db. I just did, and it doesn't remove
> backup history.
> 2. Whether SSMS removes it for you, you can find out by for instance
> running a Profiler trace when detaching a database. My guess is that SSMS
> does not do this for you.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John Holt" <johnh@.regionv.k12.mn.us> wrote in message
> news:uvqmkIw7GHA.2364@.TK2MSFTNGP02.phx.gbl...
>

Detach/Delete

I have noticed that a Detach is almost instant and a Delete can take a few.
I have detached a lot of databases and am wondering if there is some kind of
cleanup I should be doing. I know the Detach leaves the data files intact,
that's why I do it. But does it also leave a lot of crap in the master or
msdb databases?
JohnHow do you perform the "delete" (I assume you mean DROP DATABASE)? If some t
he GUI tool then the
extra time might be removal of backup history information in msdb for the da
tabase. I doubt that
detach does this.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Holt" <johnh@.regionv.k12.mn.us> wrote in message
news:%23IosITt7GHA.4996@.TK2MSFTNGP04.phx.gbl...
>I have noticed that a Detach is almost instant and a Delete can take a few.
I have detached a lot
>of databases and am wondering if there is some kind of cleanup I should be
doing. I know the
>Detach leaves the data files intact, that's why I do it. But does it also
leave a lot of crap in
>the master or msdb databases?
> John
>|||I use SQL2k5 Management Studio. So every database that I detach, I need to
delete the backup history data in msdb for that database?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u4xObXt7GHA.3604@.TK2MSFTNGP02.phx.gbl...
> How do you perform the "delete" (I assume you mean DROP DATABASE)? If some
> the GUI tool then the extra time might be removal of backup history
> information in msdb for the database. I doubt that detach does this.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John Holt" <johnh@.regionv.k12.mn.us> wrote in message
> news:%23IosITt7GHA.4996@.TK2MSFTNGP04.phx.gbl...
>|||>I use SQL2k5 Management Studio. So every database that I detach, I need to delete the back
up
>history data in msdb for that database?
You don't have to do that, if you don't mind that backup history hanging aro
und... You should have
some job that prunes backup history in any case, so with time history for th
is database will be
removed.
Whether b-history is removed, well that is easy enough to answer:
1. Read source code for sp_detach_db. I just did, and it doesn't remove back
up history.
2. Whether SSMS removes it for you, you can find out by for instance running
a Profiler trace when
detaching a database. My guess is that SSMS does not do this for you.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Holt" <johnh@.regionv.k12.mn.us> wrote in message news:uvqmkIw7GHA.2364@.TK2MSFTNGP02.ph
x.gbl...
>I use SQL2k5 Management Studio. So every database that I detach, I need to
delete the backup
>history data in msdb for that database?
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:u4xObXt7GHA.3604@.TK2MSFTNGP02.phx.gbl...
>|||I'll have to get rid of the junk when I have bit of time. It doesn't seem
to bother anything performance wise. Nice to keep it tidy though.
Thanks for info Tibor.:-)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O8vzmRw7GHA.940@.TK2MSFTNGP03.phx.gbl...
> You don't have to do that, if you don't mind that backup history hanging
> around... You should have some job that prunes backup history in any case,
> so with time history for this database will be removed.
> Whether b-history is removed, well that is easy enough to answer:
> 1. Read source code for sp_detach_db. I just did, and it doesn't remove
> backup history.
> 2. Whether SSMS removes it for you, you can find out by for instance
> running a Profiler trace when detaching a database. My guess is that SSMS
> does not do this for you.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John Holt" <johnh@.regionv.k12.mn.us> wrote in message
> news:uvqmkIw7GHA.2364@.TK2MSFTNGP02.phx.gbl...
>

Detach/Delete

I have noticed that a Detach is almost instant and a Delete can take a few.
I have detached a lot of databases and am wondering if there is some kind of
cleanup I should be doing. I know the Detach leaves the data files intact,
that's why I do it. But does it also leave a lot of crap in the master or
msdb databases?
JohnHow do you perform the "delete" (I assume you mean DROP DATABASE)? If some the GUI tool then the
extra time might be removal of backup history information in msdb for the database. I doubt that
detach does this.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Holt" <johnh@.regionv.k12.mn.us> wrote in message
news:%23IosITt7GHA.4996@.TK2MSFTNGP04.phx.gbl...
>I have noticed that a Detach is almost instant and a Delete can take a few. I have detached a lot
>of databases and am wondering if there is some kind of cleanup I should be doing. I know the
>Detach leaves the data files intact, that's why I do it. But does it also leave a lot of crap in
>the master or msdb databases?
> John
>|||I use SQL2k5 Management Studio. So every database that I detach, I need to
delete the backup history data in msdb for that database?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u4xObXt7GHA.3604@.TK2MSFTNGP02.phx.gbl...
> How do you perform the "delete" (I assume you mean DROP DATABASE)? If some
> the GUI tool then the extra time might be removal of backup history
> information in msdb for the database. I doubt that detach does this.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John Holt" <johnh@.regionv.k12.mn.us> wrote in message
> news:%23IosITt7GHA.4996@.TK2MSFTNGP04.phx.gbl...
>>I have noticed that a Detach is almost instant and a Delete can take a
>>few. I have detached a lot of databases and am wondering if there is some
>>kind of cleanup I should be doing. I know the Detach leaves the data
>>files intact, that's why I do it. But does it also leave a lot of crap in
>>the master or msdb databases?
>> John
>|||>I use SQL2k5 Management Studio. So every database that I detach, I need to delete the backup
>history data in msdb for that database?
You don't have to do that, if you don't mind that backup history hanging around... You should have
some job that prunes backup history in any case, so with time history for this database will be
removed.
Whether b-history is removed, well that is easy enough to answer:
1. Read source code for sp_detach_db. I just did, and it doesn't remove backup history.
2. Whether SSMS removes it for you, you can find out by for instance running a Profiler trace when
detaching a database. My guess is that SSMS does not do this for you.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Holt" <johnh@.regionv.k12.mn.us> wrote in message news:uvqmkIw7GHA.2364@.TK2MSFTNGP02.phx.gbl...
>I use SQL2k5 Management Studio. So every database that I detach, I need to delete the backup
>history data in msdb for that database?
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:u4xObXt7GHA.3604@.TK2MSFTNGP02.phx.gbl...
>> How do you perform the "delete" (I assume you mean DROP DATABASE)? If some the GUI tool then the
>> extra time might be removal of backup history information in msdb for the database. I doubt that
>> detach does this.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "John Holt" <johnh@.regionv.k12.mn.us> wrote in message
>> news:%23IosITt7GHA.4996@.TK2MSFTNGP04.phx.gbl...
>>I have noticed that a Detach is almost instant and a Delete can take a few. I have detached a lot
>>of databases and am wondering if there is some kind of cleanup I should be doing. I know the
>>Detach leaves the data files intact, that's why I do it. But does it also leave a lot of crap in
>>the master or msdb databases?
>> John
>>
>|||I'll have to get rid of the junk when I have bit of time. It doesn't seem
to bother anything performance wise. Nice to keep it tidy though.
Thanks for info Tibor.:-)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O8vzmRw7GHA.940@.TK2MSFTNGP03.phx.gbl...
> >I use SQL2k5 Management Studio. So every database that I detach, I need
> >to delete the backup history data in msdb for that database?
> You don't have to do that, if you don't mind that backup history hanging
> around... You should have some job that prunes backup history in any case,
> so with time history for this database will be removed.
> Whether b-history is removed, well that is easy enough to answer:
> 1. Read source code for sp_detach_db. I just did, and it doesn't remove
> backup history.
> 2. Whether SSMS removes it for you, you can find out by for instance
> running a Profiler trace when detaching a database. My guess is that SSMS
> does not do this for you.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John Holt" <johnh@.regionv.k12.mn.us> wrote in message
> news:uvqmkIw7GHA.2364@.TK2MSFTNGP02.phx.gbl...
>>I use SQL2k5 Management Studio. So every database that I detach, I need
>>to delete the backup history data in msdb for that database?
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:u4xObXt7GHA.3604@.TK2MSFTNGP02.phx.gbl...
>> How do you perform the "delete" (I assume you mean DROP DATABASE)? If
>> some the GUI tool then the extra time might be removal of backup history
>> information in msdb for the database. I doubt that detach does this.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "John Holt" <johnh@.regionv.k12.mn.us> wrote in message
>> news:%23IosITt7GHA.4996@.TK2MSFTNGP04.phx.gbl...
>>I have noticed that a Detach is almost instant and a Delete can take a
>>few. I have detached a lot of databases and am wondering if there is
>>some kind of cleanup I should be doing. I know the Detach leaves the
>>data files intact, that's why I do it. But does it also leave a lot of
>>crap in the master or msdb databases?
>> John
>>
>>
>

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

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