Although moving database files around is simple, it is a still big deal. When you do this, you are moving the entire database contents to another location. If you have the space, a backup beforehand wouldn’t be a bad idea. This is the script that I use to make this happen.
Author: Ben Farnsworth
Get info on Data files
IF OBJECT_ID('tempdb..#space') IS NOT NULL DROP TABLE #space
create table #space(
exec sp_msforeachdb @command1 = '
INSERT INTO #space
, convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB
, convert(decimal(12,2),round(fileproperty(a.name,''SpaceUsed'')/128.000,2)) as SpaceUsedMB
This script is intended for environments where space constraints require the space to be closely maintained. This is not a good script to run as part of a normal production maintenance plan, unless you adjust the variables to high enough levels that it only catches extreme cases. Shrinking a database takes a lot of time and CPU power and won’t help performance.
This script does include an index rebuild at the end as indexes become very fragmented after this process.
This script gets the size of each database file, not including backups. Essentially, you’ll see .MDF, .NDF, and .LDF files.
SELECT DISTINCT bus.database_name
, (buf.file_size / (1024*1024)) AS [File Size MB]
FROM msdb..backupfile buf
INNER JOIN msdb..backupset bus
ON buf.backup_set_id = bus.backup_set_id
ORDER BY bus.database_name