|
Home > Archive > MS SQL Server > October 2006 > Detach/Delete
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| John Holt 2006-10-24, 6:37 pm |
| 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
| |
| Tibor Karaszi 2006-10-24, 6:37 pm |
| 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
>
| |
| John Holt 2006-10-24, 6:37 pm |
| 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@hotmai
l.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...
>
| |
| Tibor Karaszi 2006-10-24, 6:37 pm |
| >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@hotmai
l.nomail.com> wrote in message
> news:u4xObXt7GHA.3604@TK2MSFTNGP02.phx.gbl...
>
>
| |
| John Holt 2006-10-24, 6:37 pm |
| 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@hotmai
l.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...
>
|
|
|
|
|