在 sql server 数据库中找到可用空间的最佳方法？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/866139/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Best way to find free space in sql server databases?
提问by GordyII
I want to be able to find out how much space is left in my database files so that I can know when to increase the space so it doesn't do it when the application is running hard out.
我希望能够找出我的数据库文件中剩余多少空间，以便我可以知道何时增加空间，以便在应用程序运行困难时不会增加空间。
I would prefer to be able to script this so I can run it across multiple databases ion a regular basis.
我希望能够编写此脚本，以便我可以定期在多个数据库中运行它。
I have SQL Server 2000 and SQL Server 2005 databases but I would prefer to be able to run the same script across both.
我有 SQL Server 2000 和 SQL Server 2005 数据库，但我希望能够在两者上运行相同的脚本。
I can use Management Studio to do this manually on 2005 databases, but not on the 2000 databases.
我可以使用 Management Studio 在 2005 数据库上手动执行此操作，但不能在 2000 数据库上执行此操作。
回答by Michael Petrotta
Try sp_spaceused:
尝试sp_spaceused：
Displays the number of rows, disk space reserved, and disk space used by a table, indexed view, or Service Broker queue in the current database, or displays the disk space reserved and used by the whole database.
显示当前数据库中某个表、索引视图或Service Broker 队列的行数、保留的磁盘空间和使用的磁盘空间，或显示整个数据库保留和使用的磁盘空间。
I believe that this was present in SQL Server 2000, but I can't prove it. It works in 2005 and 2008.
我相信这存在于 SQL Server 2000 中，但我无法证明。它适用于 2005 年和 2008 年。
And you can peek at it with sp_helptext, if you want to tie it into some server-side logic.
sp_helptext如果您想将其与某些服务器端逻辑联系起来，您可以使用 来查看它。
EDIT: expanding on my comment below, and with thanks to the original contributor at http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=82359, here's a way to break down usage by file:
编辑：在下面扩展我的评论，并感谢http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=82359的原始贡献者，这里有一种按文件分解使用情况的方法：
select
name
, filename
, convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB
, convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)) as SpaceUsedMB
, convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) as FreeSpaceMB
from dbo.sysfiles a
回答by Jason Clark
Run the below command to know how much free space currently available in your SQL Server 2000:
运行以下命令以了解 SQL Server 2000 中当前有多少可用空间：
DECLARE @command VARCHAR(5000)
DECLARE @DBInfo TABLE
( ServerName VARCHAR(100),
DatabaseName VARCHAR(100),
PhysicalFileName NVARCHAR(520),
FileSizeMB DECIMAL(10,2),
SpaceUsedMB DECIMAL(10,2),
FreeSpaceMB DECIMAL(10,2),
FreeSpacePct varchar(8)
)
SELECT @command = 'Use [' + '?' + '] SELECT
@@servername as ServerName,
' + '''' + '?' + '''' + ' AS DatabaseName , filename
, convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB
, convert(decimal(12,2),round(fileproperty(a.name,'+''''+'SpaceUsed'+''''+')/128.000,2)) as SpaceUsedMB
, convert(decimal(12,2),round((a.size-fileproperty(a.name,'+''''+'SpaceUsed'+''''+'))/128.000,2)) as FreeSpaceMB,
CAST(100 * (CAST (((a.size/128.0 -CAST(FILEPROPERTY(a.name,' + '''' + 'SpaceUsed' + '''' + ' ) AS int)/128.0)/(a.size/128.0)) AS decimal(4,2))) AS varchar(8)) + ' + '''' + '%' + '''' + ' AS FreeSpacePct
from dbo.sysfiles a'
INSERT INTO @DBInfo
EXEC sp_MSForEachDB @command
SELECT * from @DBInfo
回答by Michael Freidgeim
It's possible to run SQL scripts to check free space manually. But it's better to create jobs and assign alerts to automatically inform admin, when not enough space left.
可以运行 SQL 脚本来手动检查可用空间。但是最好创建作业并分配警报以在剩余空间不足时自动通知管理员。
The best article I found - Managing Database Data Usage With Custom Space Alerts(SQLServerCentral login is required) can be followed even by support personnel without much DBA experience.
我发现的最好的文章 -使用自定义空间警报管理数据库数据使用（需要 SQLServerCentral 登录）即使没有太多 DBA 经验的支持人员也可以遵循。
You can also use similar article Monitor database file sizes with SQL Server Jobs.
您还可以使用类似的文章使用 SQL Server 作业监视数据库文件大小。
Also for manual check I prefer sp_SOS(can be downloaded from http://searchsqlserver.techtarget.com/tip/Find-size-of-SQL-Server-tables-and-other-objects-with-stored-procedure)
同样对于手动检查，我更喜欢sp_SOS（可以从http://searchsqlserver.techtarget.com/tip/Find-size-of-SQL-Server-tables-and-other-objects-with-stored-procedure下载 ）
EXEC dbo.sp_SOS @OrderBy='T'
回答by Christian
For me, sql server always automatically allocated more space until the harddrive was full, so just query the free space on the harddrive.
对我来说，sql server 总是自动分配更多空间，直到硬盘已满，所以只需查询硬盘上的可用空间。
What settings do you use that you have to increase the "space" of a database?
您使用哪些设置来增加数据库的“空间”？
