Shrink Database知识点以及Truncate和Delete语句的区别
今天帮一个朋友解决一个数据库问题, 他的数据库中共有一张表很大, 有三百多个G, 弄的数据库文件所在分区都没空间了. 于是清理了一下那张表, 运行的命令是delete * from table_name. 结果发现表变小了, 数据库还是那么大.
在Management Studio中运行shrink database task, 执行之前发现available的space 高达百分之九十几. 运行shrink结束之后, 数据库恢复到了正常大小.
如果我们不是用delete语句, 而是用truncate语句, 那么就不需要执行shrink任务了. 下面是相关技术要点的翻译与引用.
Truncate Table(Transact-SQL)
=============================
该语句会移除表中的所有的行, 而不记录每一行的删除动作. Truncate Table跟Delete语句一样, 都没有Where分句, 然而, Truncate Table更快, 也是用更少的系统资源和transaction log资源.
与DELETE语句相比, TRUNCATE TABLE有如下的优势:
- 使用更少的transaction log空间. DELETE语句一次移除一行, 并且在transaction log中位每一个删除了的行做一条记录. TRUNCATE TABLE会通过释放用来存储表数据的数据页来删除表中数据, 然后只在transaction log中记录页面的释放操作.
- 典型地, 它会使用更少的锁. 当DELETE语句执行的时候, 会使用一个row lock, 表中的每一行都会为删除操作而被锁定. TRUNCATE TABLE永远是锁住整个表而不是锁住每一行.
- 无一例外, 表中不会剩下一个page的. 经过DELETE命令之后, 表还是能够包含空page的. 比如说, 堆上的空page没有至少一个排他表锁, 是不会被释放的. 如果delete操作不使用表锁, 那么表就会包含很多的空page. 对于索引, delete操作会留下空页面, 尽管这些pages会很快地被后台清理进程释放掉.
对于标识列(identity column), 该列的计数器会在truncate table后重置为seed的值, 如果没有设置seed值的话, 会使用默认值1。如果想要保留标识列的计数器的话, 应该使用delete * from table.
Shrink Database Task
============================
Shrink Database Task会减小SQL Server数据库中的数据文件以及日志文件的大小.
Shrink Database包装了DBCC SHRINKDATABASE命令.
Shrink a database
============================
数据库中的每个文件都可以通过移出未使用的pages来缩小. 尽管数据库引擎会卓有效率地减小空间, 但是总有些时候一个文件不再需要像它以前那么大, 这是shrink database就会变的很必要了. 数据, 还有transaction log都可以被缩小. 数据库文件可以被手动地缩小, 可以以组的形式来做, 也可以使单个的文件; 数据库文件的缩小也可以被设定在某一时间跨度时, 自动地完成.
关于shrink数据库的最佳实践
- A shrink operation is most effective after an operation that creates lots of unused space, such as a truncate table or a drop table operation.
- Most databases require some free space for regular day-to-day operations. If you shrink a database repeatedly and notice that the database size grows again, this indicates that the space that was shrunk is required for regular operations. In these cases, repeatedly shrinking the database is a wasted operation.
- A shrink operation does not preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree. For example, you should not shrink a database or data file after rebuilding indexes. This is another reason not to repeatedly shrink the database.
- Unless you have a specific requirement, do not set the AUTO_SHRINK database option to ON.
参考文章:
TRUNCATE TABLE (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms177570.aspx
Shrink Database Task
http://msdn.microsoft.com/en-us/library/ms141819.aspx
Shrinking a Database
