How to delete database on production server

Question

How to delete database on production server

Viewing 15 posts - 16 through 30 (of 36 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

bump, as I wanted to know if kras followed up on the reasoning behind wanting to drop the production database.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Derrick Smith SSChampion Points: 10833 More actions · Answer 2

Lowell (10/5/2010)
bump, as I wanted to know if kras followed up on the reasoning behind wanting to drop the production database.

Wait, we need "reasons" for this kind of thing now?

Since when?

Lowell SSC Guru Points: 323518 More actions · Answer 3

Derrick Smith (10/5/2010)
Lowell (10/5/2010)
bump, as I wanted to know if kras followed up on the reasoning behind wanting to drop the production database.
Wait, we need "reasons" for this kind of thing now?
Since when?

Hey a whim counts as a reason!

I know i only like to see 8 or so databases when i see a list in object explorer, so trimming the list seems like a good uead, right?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

Nice avatar lowell. Interesting change.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

tosscrosby SSCoach Points: 18045 More actions · Answer 5

Lowell (10/5/2010)
Derrick Smith (10/5/2010)
Lowell (10/5/2010)
bump, as I wanted to know if kras followed up on the reasoning behind wanting to drop the production database.
Wait, we need "reasons" for this kind of thing now?
Since when?
Hey a whim counts as a reason!
I know i only like to see 8 or so databases when i see a list in object explorer, so trimming the list seems like a good uead, right?

And do it alphabetically, of course.

-- You can't be late until you show up.

tosscrosby SSCoach Points: 18045 More actions · Answer 6

Lynn Pettis (10/4/2010)
As alluded to above in one of the posts, BEFORE dropping the database be SURE you take a FULL BACKUP of the database. This way you can recover it later if needed.

Unless you've already got a new job lined up. Then, who cares?? :w00t:

-- You can't be late until you show up.

Derrick Smith SSChampion Points: 10833 More actions · Answer 7

tosscrosby (10/5/2010)
Lowell (10/5/2010)
Derrick Smith (10/5/2010)
Lowell (10/5/2010)
bump, as I wanted to know if kras followed up on the reasoning behind wanting to drop the production database.
Wait, we need "reasons" for this kind of thing now?
Since when?
Hey a whim counts as a reason!
I know i only like to see 8 or so databases when i see a list in object explorer, so trimming the list seems like a good uead, right?
And do it alphabetically, of course.

Completely inefficient. If you want to be cost effective, you delete by size desc.

sjimmo SSChampion Points: 11139 More actions · Answer 8

And do it alphabetically, of course.

That's how I do it, one a day like a vitamin.

Really, why do you want to delete a production database? Especially when you don't know how???:exclamationmark:

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

homebrew01 SSC Guru Points: 55548 More actions · Answer 9

Derrick Smith (10/5/2010)
Completely inefficient. If you want to be cost effective, you delete by size desc.

That's what I was thinking. You can run this script to identify your biggest databases. Then delete the large ones to free up more disk space.

select

database_name = DB.name

,sum( MF. /131072.) as 'Gig'

frommaster.sys.databasesas DB

inner joinmaster.sys.master_filesas MF on MF.database_id = DB.database_id

group by DB.name

order by DB.name

george sibbald SSC Guru Points: 104210 More actions · Answer 10

WayneS (10/4/2010)
Lynn Pettis (10/4/2010)
As alluded to above in one of the posts, BEFORE dropping the database be SURE you take a FULL BACKUP of the database. This way you can recover it later if needed.
Or, you could always use DBCC TimeWarp!

Aaahh, fond memories. I'd still like to know who timewarped away my points for that thread though. It was some of my finest work.

---------------------------------------------------------------------

SQLRNNR SSC Guru Points: 281334 More actions · Answer 11

tosscrosby (10/5/2010)
Lynn Pettis (10/4/2010)
As alluded to above in one of the posts, BEFORE dropping the database be SURE you take a FULL BACKUP of the database. This way you can recover it later if needed.
Unless you've already got a new job lined up. Then, who cares?? :w00t:

Yeah but that usually catches up to you - eventually.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 12

homebrew01 (10/5/2010)
Derrick Smith (10/5/2010)
Completely inefficient. If you want to be cost effective, you delete by size desc.
That's what I was thinking. You can run this script to identify your biggest databases. Then delete the large ones to free up more disk space.
select
database_name = DB.name
,sum( MF. /131072.) as 'Gig'
frommaster.sys.databasesas DB
inner joinmaster.sys.master_filesas MF on MF.database_id = DB.database_id
group by DB.name
order by DB.name

Too inefficient.

select

database_name = DB.name

,sum( MF. /131072.) as 'Gig'

,'Drop database ' + DB.name '; '

from master.sys.databases as DB

inner join master.sys.master_files as MF on MF.database_id = DB.database_id

group by DB.name

order by DB.name

Now you can just copy and paste the drop statement. 😉

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 13

CirquedeSQLeil (10/5/2010)
select
database_name = DB.name
,sum( MF. /131072.) as 'Gig'
,'Drop database ' + DB.name '; '
from master.sys.databases as DB
inner join master.sys.master_files as MF on MF.database_id = DB.database_id
group by DB.name
order by DB.name
Now you can just copy and paste the drop statement. 😉

Still too much work:

DECLARE @sql VARCHAR(5000)

while 1=2

BEGIN

select TOP 1

@sql = 'Drop databasse ' + DB.name '; '

from master.sys.databases as DB

inner join master.sys.master_files as MF on MF.database_id = DB.database_id

where db.name not in ( 'master', 'model', 'msdb', 'temdb', 'resource')

group by DB.name

order by ME. DESC

IF @@ROWCOUNT = 0 THEN BREAK

EXEC sp_executesql @sql

END

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

WayneS SSC Guru Points: 95460 More actions · Answer 14

Lordy... I sure hope nobody actually tries to run any of the recent code postings on this thread. While it's been fun, I think you'll really should go back and redact those, and the code that has been quoted also.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 15

WayneS (10/5/2010)
Lordy... I sure hope nobody actually tries to run any of the recent code postings on this thread. While it's been fun, I think you'll really should go back and redact those, and the code that has been quoted also.

I think mine is the only REALLY dangerous one... I'll make it break in some obscure way. 🙂

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA