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