I want to drop a now supposedly redundant file in SQL Server (2005), but when I try to drop it I am told that the file is not empty. Does anyone know of a way to find out what data is still in this file so I can make whatever changes I need to allow me to drop it?
-
What do you mean by "file"? It is a table or database?Oleg Kalenbet– Oleg Kalenbet2009-07-10 12:43:26 +00:00Commented Jul 10, 2009 at 12:43
-
@Oleg. A file in SQL Server is just that: a file on the OS.gbn– gbn2009-07-10 12:44:21 +00:00Commented Jul 10, 2009 at 12:44
-
Like sqlservr.exe or master.mdf?Oleg Kalenbet– Oleg Kalenbet2009-07-10 13:07:44 +00:00Commented Jul 10, 2009 at 13:07
-
@Oleg: MDF, LDF and NDF files are SQL Server database files. sqlerver.exe is the binary.gbn– gbn2009-07-10 13:24:10 +00:00Commented Jul 10, 2009 at 13:24
Add a comment
|
1 Answer
Assuming you're moved the table etc, you'll probably need to run:
DBCC SHRINKFILE (MyLogicalFile, EMPTYFILE) --EMPTYFILE is the important bit!!
See DBCC SHRINKFILE
To check (this is a cut'n'paste of a usage script I use):
SELECT
ds.[name] AS LogicalFileName,
OBJECT_NAME(p.object_id) AS Thing,
SUM(au.total_pages) / 128.0 AS UsedMB,
df.size / 128 AS FileSizeMB,
100.0 * SUM(au.total_pages) / df.size AS PercentUsed
FROM
sys.database_files df
JOIN
sys.data_spaces ds ON df.data_space_id = ds.data_space_id
JOIN
sys.allocation_units au ON ds.data_space_id = au.data_space_id
JOIN
sys.partitions p ON au.container_id = p.hobt_id
WHERE
OBJECTPROPERTYEX(p.object_id, 'IsMSShipped') = 0
GROUP BY
ds.[name], OBJECT_NAME(p.object_id), df.size
ORDER BY
ds.[name]