It's not very likely that you will need to get rid of databases on the production side, but certainly development boxes can be full of junk - test databases, experiments that didn't work out, projects that got cancelled... So you just drop the junk DB, right?
Wrong...
Get the business owner to confirm in writing before you do this stuff, and be prepared to retrieve it in case they change their mind. This is pretty much the opposite of business as usual, so beware.
1)
You can make a database read-only. It is no longer in use, but the users might want to look at historical data sometimes. The users can still see their data, but they can't change anything. But if an application tries to record something like date_last_accessed, it won't work. Consider running optimization processes, such as full UPDATE STATISTICS before changing the status to read only .
--update statistics
use Vanilla
Go
exec sp_updatestats
-- to make a database read only
alter database Vanilla set read_only with rollback immediate;
go
-- and back to normal
alter database Vanilla set read_write;
go
The rollback immediate thing will cope with anyone who is in the middle of working on the database (the one they told you is no longer in use). Of course, it may be a process rather than a person. You may need to make the database single-user
-- if other people are using it, take
control
alter database Vanilla set single_user with rollback immediate;
--and give it back
alter database Vanilla set multi_user;
2)
You can take a database offline. This is a step beyond read only; the users can't see it but you can. If it turns out that they need it after all, it's usually the work of a moment to bring it back online. If not
this may help.
Taking a database offline is a wise precaution to take before deleting it. Tell the helpdesk to let you know if anyone screams. Wait for a month or two, maybe three, depending.. Put a note in Outlook to remind you in three months, otherwise it will stay offline forever. If you are using SQL Server 2005, you may need to tinker with your maintenance plans - the option to ignore offline dbs only came in with 2008.
-- Take the Database Offline
alter database Vanilla set offline with rollback immediate;
go
-- Bring the Database back Online
alter database Vanilla set online;
go
3)
You can detach a database. It's still there, but you can't see it or get it back without a bit of effort. If the file naming isn't routine, it might be very fiddly indeed.
Out of sight, out of mind - once you have detached a database "temporarily" you can't see it unless you go on the box and browse to the folder(s) where the files live. So before you detach the Vanilla database, run this:
execute sp_helpdb Vanilla
This will show you where the files live; copy the results into an Outlook task and mark it for attention a couple of weeks hence. Something like this:
| name |
fileid |
filename |
filegroup |
size |
usage |
maxsize |
growth |
| Vanilla |
1 |
E:\MSSQL.1\MSSQL\DATA\Vanilla.MDF |
PRIMARY |
7808 KB |
Unlimited |
10% |
data only |
| Vanilla_Log |
2 |
F:\MSSQL\Logs\Vanilla_Log.LDF |
NULL |
1024 KB |
Unlimited |
10% |
log only |
There's a fair chance that you or your predecessor forgot to take this precaution some time in the last 10 years. This might mean that the server (especially, as I say, if it's a dev box) is cluttered with long forgotten detached databases.
The script below looks for databases which are not attached to the current instance. Unfortunately if you have other instances on the box, it will detect those databases too. (If you can tell me how to fix that, I'll be very grateful). But apart from that, it's very good at finding potentially forgotten files which are taking up space that you can use.
It uses xp_cmdshell, so assuming you probably have this disabled by default, enable it before and disable it afterwards for security reasons. (Be careful though - if it isn't disabled by default, you might end up disabling something important. ) Then it creates a temporary table, populates it with a list of .mdf files, and compares the mdf files it finds with a list of those that are attached.
---- enable these jobs
---- show advanced options
sp_configure 'show
advanced options', 1;
GO
RECONFIGURE;
GO
---- enable xp_cmdshell
sp_configure 'xp_cmdshell', 1;
GO
RECONFIGURE;
GO
-- based on
http://www.sqlservercentral.com/Forums/Topic6166-5-1.aspx
-- create temporary table
create table #temp_mdf_files
(
full_filename varchar(200)
)
-populate the temp table with any MDF files found
insert #temp_mdf_files
exec xp_cmdshell 'dir c:\*.mdf /s/b'
insert #temp_mdf_files
exec xp_cmdshell 'dir d:\*.mdf /s/b'
insert #temp_mdf_files
exec xp_cmdshell 'dir e:\*.mdf /s/b'
select
-- exclude the subdirectory name
upper(reverse(substring(reverse(full_filename ), 1,charindex('\', reverse(full_filename ) )-1) )) As MDF_FileName,
full_filename
from
#temp_mdf_files
where
--exclude rows which contain system messages or nulls
full_filename like '%\%'
and --exclude system databases
upper(reverse(substring(reverse(full_filename ), 1,charindex('\', reverse(full_filename ) )-1) ))
not in
('DISTMDL.MDF', 'MASTER.MDF', 'MODEL.MDF', 'MSDBDATA.MDF' , 'MSSQLSYSTEMRESOURCE.MDF', 'TEMPDB.MDF' )
and -- MDF filename excluding the subdirectory name
full_filename
not in
(select Upper(FILEname) from sys.SYSdatabases)
-- -- SQL Server 2000
-- not
in (select Upper(FILEname) from sysdatabases)
order by MDF_FileName
-- Housekeeping
drop table #temp_mdf_files
-- disable these jobs
-- show advanced options
sp_configure 'show
advanced options', 1;
GO
RECONFIGURE;
GO
-- disable xp_cmdshell
sp_configure 'xp_cmdshell', 0;
GO
RECONFIGURE;
GO
-- hide advanced options
sp_configure 'show
advanced options', 0;
GO
RECONFIGURE;
GO
4) You can drop a database
You're sure? Really sure?
Not if it's offline, you can't. You need to bring it back online before you do that. It might disappear from SSMS, but the physical files are still lurking in there. In fact I suspect that the reason I am housekeeping a list of detached databases is that people have taken them offline
Someone dropped the database but because it was offline it
never got deleted – it just stayed on as a detached DB
And take a backup before you drop it. Zip up the backup and save it in your Final Backups folder.
