What are the advantages and disadvantages of shrinking a database? Are there any performance implications?
缩小数据库有哪些优缺点?是否有任何性能影响?
1 个解决方案
#1
11
- Advantage: less disk space used
- Disadvantage: more likely to run out of space
优点:使用的磁盘空间更少
缺点:更有可能耗尽空间
That's basically it - a shrunk DB with no free space will need to be 'grown' in order to complete almost any operation.
基本上就是这样 - 没有可用空间的缩小的数据库需要“增长”才能完成几乎任何操作。
When SQL runs out of space in an MDF file it has to grow it, and that is very slow and expensive.
当SQL在MDF文件中用完空间时,它必须增长它,这非常缓慢且昂贵。
If you know that you've enough space in the file go ahead and shrink. Be careful of fragmented files as you will need to re-index afterwards if they are.
如果您知道文件中有足够的空间,请继续缩小。注意碎片文件,因为如果它们需要重新索引。
Basic example:
500MB database file, 400MB used. You could shrink it to 400MB, but the next operation will be very slow as SQL adds 40MB (10%, by default) to the size.
500MB数据库文件,400MB使用。你可以将它缩小到400MB,但是下一个操作将非常慢,因为SQL会增加40MB(默认为10%)。
500MB database file, 50MB used. You're probably wasting space. Shrink it to 100MB and the DB will still have space to do lots of things.
500MB数据库文件,50MB使用。你可能在浪费空间。缩小到100MB,数据库仍然有足够的空间来做很多事情。
500MB database file, 50MB used, but about to import millions of records. Leave it unshrunk. Check again after import.
500MB数据库文件,使用50MB,但即将导入数百万条记录。让它不受影响。导入后再次检查。
Read only DBs should always be shrunk.
只读DB应该总是缩小。
#1
11
- Advantage: less disk space used
- Disadvantage: more likely to run out of space
优点:使用的磁盘空间更少
缺点:更有可能耗尽空间
That's basically it - a shrunk DB with no free space will need to be 'grown' in order to complete almost any operation.
基本上就是这样 - 没有可用空间的缩小的数据库需要“增长”才能完成几乎任何操作。
When SQL runs out of space in an MDF file it has to grow it, and that is very slow and expensive.
当SQL在MDF文件中用完空间时,它必须增长它,这非常缓慢且昂贵。
If you know that you've enough space in the file go ahead and shrink. Be careful of fragmented files as you will need to re-index afterwards if they are.
如果您知道文件中有足够的空间,请继续缩小。注意碎片文件,因为如果它们需要重新索引。
Basic example:
500MB database file, 400MB used. You could shrink it to 400MB, but the next operation will be very slow as SQL adds 40MB (10%, by default) to the size.
500MB数据库文件,400MB使用。你可以将它缩小到400MB,但是下一个操作将非常慢,因为SQL会增加40MB(默认为10%)。
500MB database file, 50MB used. You're probably wasting space. Shrink it to 100MB and the DB will still have space to do lots of things.
500MB数据库文件,50MB使用。你可能在浪费空间。缩小到100MB,数据库仍然有足够的空间来做很多事情。
500MB database file, 50MB used, but about to import millions of records. Leave it unshrunk. Check again after import.
500MB数据库文件,使用50MB,但即将导入数百万条记录。让它不受影响。导入后再次检查。
Read only DBs should always be shrunk.
只读DB应该总是缩小。