What is the best way to delete a large, binary column from a SQL Server 2005 database yet minimise the size of the resulting .mdf and .ldf files?
从SQL Server 2005数据库中删除大型二进制列的最佳方法是什么,但最小化了生成的.mdf和.ldf文件的大小?
Our app used to allow users to upload their documents. These were stored as BLOBs in a column in a documents table. Over time, this table grew to be >90% of the overall database size.
我们的应用程序用于允许用户上传他们的文档。它们作为BLOB存储在文档表的列中。随着时间的推移,该表增长到整个数据库大小的90%以上。
We have since changed things and no longer need to store documents like this. I'd now like to zero the data in this column and get the database back to a manageable size (disk space is at a bit of a premium). Being a legacy app, I'd like to maintain compatibility and not change the structure of the table.
我们已经改变了东西,不再需要存储这样的文档。我现在想将此列中的数据归零,并使数据库恢复到可管理的大小(磁盘空间有点溢价)。作为一个遗留应用程序,我想保持兼容性而不是改变表的结构。
The most obvious way is to do something like:
最明显的方法是做一些事情:
update documents set content = 0x0
更新文档设置内容= 0x0
But it seems to blow the .ldf out by a huge amount.
但它似乎将.ldf大量抛出。
Addressing particular symptom, I could then either truncate the log (backup log mydb with truncate_only) or perhaps try a dbcc shrinkdatabase(mydb) or a dbcc shrinkfile(mydb_log, 20) but I've heard these can cause nasty fragmentation, etc. and might not be best in the long term.
解决特定症状,然后我可以截断日志(使用truncate_only备份日志mydb)或者尝试dbcc shrinkdatabase(mydb)或dbcc shrinkfile(mydb_log,20),但我听说这些可能导致令人讨厌的碎片等等。从长远来看可能不是最好的。
Would it be better to create a second table with all but the content column, copy the data across and then truncate the first?
使用除内容列之外的所有内容列创建第二个表是否更好,复制数据然后截断第一个?
Any thoughts would be appreciated.
任何想法将不胜感激。
Cheers
3 个解决方案
#1
try dropping the column:
尝试删除列:
ALTER TABLE foo
DROP COLUMN foo.name
#2
From experimentation, there doesn't seem to be a magic bullet to this.
从实验来看,这似乎没有灵丹妙药。
Both suggestions will allow me to delete the large content without a massive increase in .ldf size, however, the only way to reduce the size of the .mdf afterwards is something like dbcc shrinkdatabase which comes at the cost of page fragmentation.
这两个建议都允许我删除大内容而不会大幅增加.ldf大小,但是,之后减小.mdf大小的唯一方法就是像dbcc shrinkdatabase那样以页面碎片为代价。
#3
Yes, your idea to create a second table from the first minus the content column then truncate the original table would probably be the most efficient.
是的,您想从第一个减去内容列创建第二个表然后截断原始表可能是最有效的。
#1
try dropping the column:
尝试删除列:
ALTER TABLE foo
DROP COLUMN foo.name
#2
From experimentation, there doesn't seem to be a magic bullet to this.
从实验来看,这似乎没有灵丹妙药。
Both suggestions will allow me to delete the large content without a massive increase in .ldf size, however, the only way to reduce the size of the .mdf afterwards is something like dbcc shrinkdatabase which comes at the cost of page fragmentation.
这两个建议都允许我删除大内容而不会大幅增加.ldf大小,但是,之后减小.mdf大小的唯一方法就是像dbcc shrinkdatabase那样以页面碎片为代价。
#3
Yes, your idea to create a second table from the first minus the content column then truncate the original table would probably be the most efficient.
是的,您想从第一个减去内容列创建第二个表然后截断原始表可能是最有效的。