sql 数据库磁盘空间不足_轻松查看数据库中各个SQL表的磁盘空间使用情况

sql 数据库磁盘空间不足

With any active database, disk storage requirements are going to grow over time. While you can easily view the disk space used by an entire database by either look in the Files page of the database properties in SQL Management Studio or simply viewing the underlying files in Windows Explorer, what if you want to dig a bit deeper and see the parts that comprise the sum of the whole?

对于任何活动数据库，磁盘存储需求将随着时间增长。 尽管可以通过在SQL Management Studio中数据库属性的“文件”页面中查看或在Windows资源管理器中简单地查看基础文件来轻松查看整个数据库使用的磁盘空间，但是如果您想更深入地了解一下部分组成整体的总和？

To see this information, you need to view the size of the individual tables. Thankfully, SQL Server has a built in stored procedure, sp_SpaceUsed, which displays the storage statistics of individual tables. Leveraging this stored procedure, we have created a batch script which allows you to easily produce a listing of each table in a database and view its storage statistics.

要查看此信息，您需要查看各个表的大小。 幸运的是，SQL Server有一个内置的存储过程sp_SpaceUsed，它显示各个表的存储统计信息。 利用此存储过程，我们创建了一个批处理脚本，该脚本可让您轻松生成数据库中每个表的列表并查看其存储统计信息。

When the script is run, the following information for each table in the database is listed in a tabular format:

运行脚本时，将以表格形式列出数据库中每个表的以下信息：

• Database table name
  数据库表名称
• Number of rows in the table
  表中的行数
• Total disk space allocated to this table by SQL
  SQL分配给此表的磁盘总空间
• Amount of disk space used for data storage
  用于数据存储的磁盘空间量
• Amount of disk space used for internal SQL indexes
  用于内部SQL索引的磁盘空间量
• Amount of disk space currently unused
  当前未使用的磁盘空间量

使用脚本(Using the Script)

The DBSize batch script is compatible with SQL 2005 and higher and must be run on a machine which has the SQLCMD tool installed (installed as part of the SQL Server installation). It is recommended you drop this script into a location set in your Windows PATH variable (i.e. C:Windows) so it can easily be called like any other application from the command line.

DBSize批处理脚本与SQL 2005及更高版本兼容，并且必须在安装了SQLCMD工具(作为SQL Server安装的一部分安装)的计算机上运行。 建议您将此脚本放到Windows PATH变量(即C：Windows)中设置的位置，以便可以像从命令行中的任何其他应用程序一样轻松地调用它。

To view the help information, simply enter:

要查看帮助信息，只需输入：

DBSize /?

DBSize /？

Examples

例子

To run a report on “MyDB” on the default instance and direct the output to “MyDB Table Size.txt” on the desktop:

要在默认实例的“ MyDB”上运行报告，并将输出定向到桌面上的“ MyDB Table Size.txt”：

DBSize MyDB > “%UserProfile%DesktopMyDB Table Size.txt”

DBSize MyDB>“％UserProfile％DesktopMyDB表Size.txt”

To run a report on “MyDB” on the named instance “Special” using the “sa” user with password “123456”:

要使用密码为“ 123456”的“ sa”用户在命名实例“ Special”上的“ MyDB”上运行报告：

DBSize MyDB /S:.Special /U:sa /P:123456

DBSize MyDB / S :.特殊/ U：sa / P：123456

Download the Database Table Size Batch Script from SysadminGeek.com

从SysadminGeek.com下载数据库表大小批处理脚本

翻译自: https://www.howtogeek.com/52063/easily-view-the-disk-space-usage-of-individual-sql-tables-in-a-database/

sql 数据库磁盘空间不足