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?
我想在SQL Server(2005)中删除一个被认为是冗余的文件,但是当我试图删除它时,我被告知文件不是空的。有没有人知道一种方法,可以找出这个文件中还有哪些数据,以便我可以做任何需要的更改,以允许我删除它?
1 个解决方案
#1
4
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
看到DBCC SHRINKFILE
To check (this is a cut'n'paste of a usage script I use):
检查(这是我使用的一个使用脚本的剪切'n'paste):
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]
#1
4
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
看到DBCC SHRINKFILE
To check (this is a cut'n'paste of a usage script I use):
检查(这是我使用的一个使用脚本的剪切'n'paste):
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]