如何确定SQL Server数据库的可用空间和文件大小

In this article, we will discuss the actual database file size and free space in the database file size with the help of monitoring script and shrink solutions. While creating a database initial size of the database files (Data File and Log File) can be defined by us, with the Autogrowth and MAXSIZE parameter. The initial size will be the same as the model database if by chance user forgot to define the initial size parameter while creating a database or do the same intentionally.

在本文中,我们将借助监视脚本和收缩解决方案来讨论实际的数据库文件大小和数据库文件大小中的可用空间。 创建数据库时,我们可以使用AutogrowthMAXSIZE参数来定义数据库文件(数据文件和日志文件)的初始大小。 如果用户偶然在创建数据库时忘记定义初始大小参数或有意这样做,则初始大小将与模型数据库相同。

New database with Database File Size

At the beginning, size of the database can be set up using the SIZE parameter and how much maximum space can be occupied by a database can be defined using the MAXSIZE parameter. How fast a database file can reach its maximum space can be resolved using the FILEGROWTH parameter. We are facilitated in a way we define the FILEGROWTH parameter. It leaves us with two options either by specific absolute value or by percentage, while in both cases we must define the value in MB format. There will be no boundary on the growth of a database file if we do not take care of defining the MAXSIZE parameter, as the default value is UNLIMITED.

最初,可以使用SIZE参数设置数据库的大小,并可以使用MAXSIZE参数定义数据库可以占用的最大空间。 可以使用FILEGROWTH参数解决数据库文件达到其最大空间的速度。 定义FILEGROWTH参数的方式为我们提供了便利。 它为我们提供了两个选项,分别是特定的绝对值或百分比,而在两种情况下,我们都必须以MB格式定义值。 如果我们不用定义MAXSIZE参数,则数据库文件的增长将没有边界,因为默认值是UNLIMITED。

Auto Growth database file parameter

SQL Server can not commit any transaction to disk storage in the situation where disk is full so it’s always good to define the MAXSIZE parameter beforehand to avoid such situation. UNLIMITED would fill up the disk storage but before that, it makes several spaces tinier than before. Even this has a very hazardous impact on the operating system as well if it doesn’t have enough space to execute its system programs. We also need to take into consideration the size of multiple databases before defining the MAXSIZE attribute.

在磁盘已满的情况下,SQL Server无法将任何事务提交到磁盘存储,因此,最好预先定义MAXSIZE参数以避免这种情况。 UNLIMITED会填满磁盘存储空间,但在此之前,它将使空间变小一些。 如果没有足够的空间来执行其系统程序,即使这也会对操作系统造成非常危险的影响。 在定义MAXSIZE属性之前,我们还需要考虑多个数据库的大小。

Gigantic sized table data will be dispensed into multiple files in a larger platform in order to decrease the amount of the disk contention. To increase the I/O performance, SQL Server supports multiple file-group with secondary files, client data and index can be stored in the secondary file-group. The same tactics can be applied with the database log file in which the user can create multiple files with a single database and it is recommended to store log files on a different drive than the main data file (mdf).

大型表数据将分配到更大平台中的多个文件中,以减少磁盘争用的数量。 为了提高I / O性能,SQL Server支持带有辅助文件的多个文件组,客户端数据和索引可以存储在辅助文件组中。 数据库日志文件可以应用相同的策略,用户可以在其中使用一个数据库创建多个文件,建议将日志文件存储在与主数据文件(mdf)不同的驱动器上。

The database file can increase in size without any boundary if the MAXSIZE parameter is not been configured by the user. It is highly suggested to construct an alert with a view to get rid of the chances of a full disk. When certain conditions exist we can report an issue or display it on health monitor using alert. There can be any issues and solutions with each issue to tackle it quickly. For example,

如果用户未配置MAXSIZE参数,则数据库文件的大小可以无限制地增加。 强烈建议构造一个警报,以消除磁盘满的可能性。 当存在某些情况时,我们可以报告问题,也可以使用警报将其显示在健康监视器上。 可能有任何问题和解决方案,每个问题都可以快速解决。 例如,

  • Free space in Data file or Log file

    数据文件或日志文件中的可用空间
  • Heavy Transaction Log data file

    大量事务日志数据文件
  • Increasing Log file Size and not shrinking due to heavy transaction stuck

    日志文件的大小增加,并且由于事务阻塞而没有收缩

Database log file size will be populating continuously till the next transaction backup happens if the database recovery model is not simple and Transaction backup or Log shipping is not set up. However, upon creating a Log backup, there is definitely going to exist a free space. For example, consider taking the log backup of the file with size 1024MB. What happens after this is that all logs will be removed inside the file and as a result of this there will be a plethora of space.

如果数据库恢复模型不是很简单并且未设置事务备份或日志传送,则将连续填充数据库日志文件的大小,直到进行下一次事务备份为止。 但是,在创建日志备份后,肯定会存在一个可用空间。 例如,考虑对大小为1024MB的文件进行日志备份。 此后发生的情况是,所有日志都将在文件内被删除,结果将有过多的空间。

Database File with Free space

To shrink a file in SQL Server, we always use DBCC SHRINKFILE() command. This DBCC SHRINKFILE() command will release the free space for the input parameter.

要在SQL Server中收缩文件,我们总是使用DBCC SHRINKFILE()命令。 此DBCC SHRINKFILE()命令将释放输入参数的可用空间。

DBCC SHRINKFILE([FileName / FileID],[EMPTYFILE / [nMB(Amount for Shrink), NOTRUNCATE / TRUNCATEONLY]])

The file will be shrunk by either file name or file id using the command above. The shrinking amount size will be considered as specified with the command in a unit of MB. Now what could be the Amount for Shrink in the DBCC SHRINKFILE()?

使用上面的命令,文件将按文件名或文件ID缩小。 收缩量的大小将按照命令指定的大小以MB为单位。 现在,DBCC SHRINKFILE()中的收缩量是多少?

Let’s discuss with an example, In the above image, the AdventureWorks database has a one DATA file AdventureWorks2016CTP3_Data with space in the file is 500 MB larger and it also possesses free space of 208.8MB. Where Logfile AdventureWorks2016CTP3_Log is in existence with 600 MB with free space in the file is 362.9 MB. The below script can be put on to use by a user to get free space for the database files.

让我们以一个示例进行讨论,在上图中, AdventureWorks数据库有一个DATA文件AdventureWorks2016CTP3_Data ,文件中的空间大了500 MB,还拥有208.8MB的可用空间。 存在日志文件AdventureWorks2016CTP3_Log的位置,其中600 MB,文件中的可用空间为362.9 MB。 用户可以使用以下脚本来获取数据库文件的可用空间。

Get a list of database files with size for all databases in SQL Server:

获取具有SQL Server中所有数据库大小的数据库文件列表:

  • sys.master_files DMV returns the database files in detail with the current size of each file
  • sys.master_files DMV详细返回数据库文件以及每个文件的当前大小
  • master_files system object will return details for each database of the SQL Server instancemaster_files系统对象将返回SQL Server实例的每个数据库的详细信息
SELECT DB_NAME(database_id) AS database_name, 
    type_desc, 
    name AS FileName, 
    size/128.0 AS CurrentSizeMB
FROM sys.master_files
WHERE database_id > 6 AND type IN (0,1)
SQL Server database file size

Get a list of databases file with size and free space for a database in SQL Server:

获取SQL Server中数据库的大小和可用空间的数据库文件列表:

  • sys.database_files DMV returns the database file with the details
  • sys.database_files DMV返回具有详细信息的数据库文件
  • sys.database_files is a system object which returns information for the selected database only
  • sys.database_files是一个系统对象,仅返回所选数据库的信息
SELECT DB_NAME() AS DbName, 
    name AS FileName, 
    type_desc,
    size/128.0 AS CurrentSizeMB,  
    size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB
FROM sys.database_files
WHERE type IN (0,1);
database file size

Now, free space for the file in the above query result set will be returned by the FreeSpaceMB column. 600 MB of space will be preoccupied with system disk into the file system for the AdventureWorks2016CTP3_Log file, However, 362 MB is free to compress it up to 238 MB (600 – 362 = 238).

现在,以上查询结果集中文件的可用空间将由FreeSpaceMB列返回。 AdventureWorks2016CTP3_Log文件的文件系统将占用系统磁盘600 MB的空间,但是362 MB可以将其最多压缩为238 MB(600 – 362 = 238)。

The example taken above refers to a small database with a very lesser original database file size and free space in a file for the AdventureWorks. But, in real scenarios (i.e. on Production Servers), database size can be humongous with a heavy flow of database transactions. The log file will store the transaction log if your database is in full recovery model. Used space will be neither released nor flushed by log file until the next Log backup and database file size will grow up with the log.

上面的示例涉及一个小型数据库,该数据库的原始数据库文件大小非常小,并且AdventureWorks的文件中具有可用空间。 但是,在实际场景中(即,在生产服务器上),数据库大小可能与大量的数据库事务不一样。 如果您的数据库处于完全恢复模式,则日志文件将存储事务日志。 直到下一个日志备份和数据库文件大小将随日志一起增长,才会释放使用的空间或不通过日志文件清除使用的空间。

When any transaction blocks or stuck any database transactions, database log file size can be rapidly increased and cross the expected output file size to handle this issue. The transaction lock problem can arise with Query performance and the same can happen with the client-side application also. When transactions are truncated and log backup is generated free space will be available. When any bulky Delete, DROP or Truncate operation is executed on the table, there can be a large amount of free space made available for data files. Free space can also be reclaimed by dropping a huge index also.

当任何事务阻塞或卡住任何数据库事务时,数据库日志文件的大小可以快速增加,并超过预期的输出文件大小,以解决此问题。 事务锁定问题可能出现在查询性能上,客户端应用程序也可能发生这种情况。 当事务被截断并生成日志备份时,可用空间将可用。 在表上执行任何大容量的Delete,DROP或Truncate操作时,可能会为数据文件提供大量可用空间。 也可以通过删除巨大的索引来回收可用空间。

Query as above should be performed in each database to monitor the free space for all databases with the criteria and on the result set, space monitor criteria must be applied. Users can use sp_msforeachdb() procedure to execute monitor T-SQL queries in each database of the SQL Server.

应该在每个数据库中执行上述查询,以使用条件监视所有数据库的可用空间,并且在结果集上必须应用空间监视条件。 用户可以使用sp_msforeachdb()过程在SQL Server的每个数据库中执行监视器T-SQL查询。

T-SQL Query to get total space and free space for database files:

T-SQL查询以获取数据库文件的总空间和可用空间:

CREATE TABLE #FileSize
(dbName NVARCHAR(128), 
    FileName NVARCHAR(128), 
    type_desc NVARCHAR(128),
    CurrentSizeMB DECIMAL(10,2), 
    FreeSpaceMB DECIMAL(10,2)
);
    
INSERT INTO #FileSize(dbName, FileName, type_desc, CurrentSizeMB, FreeSpaceMB)
exec sp_msforeachdb 
'use [?]; 
 SELECT DB_NAME() AS DbName, 
        name AS FileName, 
        type_desc,
        size/128.0 AS CurrentSizeMB,  
        size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128.0 AS FreeSpaceMB
FROM sys.database_files
WHERE type IN (0,1);';
    
SELECT * 
FROM #FileSize
WHERE dbName NOT IN ('distribution', 'master', 'model', 'msdb')
AND FreeSpaceMB > ?;
    
DROP TABLE #FileSize;

In the above query, for every individual database file size will be stored by a temporary table called #FileSize. Thereafter, Necessary filter or calculative logic will be applied by Database Administrator Alert with calculation logic can be set as below:

在上面的查询中,对于每个单独的数据库,文件大小将由称为#FileSize的临时表存储。 此后,数据库管理员警报将应用必要的过滤器或计算逻辑,并可以如下设置计算逻辑:

  • If Free Space in the file is exceeding the limit (nMB)

    如果文件中的可用空间超过限制(nMB)
  • If the percentage of Free space is exceeding n% compare to total space

    如果可用空间百分比超过总空间的n%
  • If the current Size of a database file is exceeding the limit (nMB)

    如果当前数据库文件的大小超过限制(nMB)

Action must be taken relative to the priority and level of the problem by DBA on the alert mail database.

DBA必须在警报邮件数据库上针对问题的优先级和级别采取措施。

Space can be reclaimed by making a move either:

可以通过以下任一方式来回收空间:

  • When the disk is in close proximity of getting full then we might have to consider moving a file to a different disk

    当磁盘接近充满时,我们可能不得不考虑将文件移至其他磁盘
  • If a database is in full recovery model and log shipping isn’t configured, make a database to the simple recovery model

    如果数据库处于完全恢复模式,并且未配置日志传送,则将数据库设置为简单恢复模式
  • If a database is in full recovery model and log shipping isn’t configured take a Full backup and Transaction Log backup

    如果数据库处于完全恢复模式,并且未配置日志传送,请进行完全备份和事务日志备份
  • Change the Max Size parameter value for the database file, if it needs to be expanded

    如果需要扩展数据库文件,请更改“最大大小”参数值
  • Disable auto-growth parameter for the database file and limit with the (n)size as the priority of the database

    禁用数据库文件的自动增长参数,并以(n)size作为数据库的优先级进行限制

Space alert can be configured on a health monitor with a mail. Nowadays we have many third-party monitors in the market to monitor database file size, where you can set the output of this query with the priority alert type. Users can attach this logic in Procedure as well with returning mail to the database monitoring team if any alert criteria succeed for the database file size.

可以在运行状况监视器上使用邮件配置空间警报。 如今,市场上有许多第三方监视器可以监视数据库文件的大小,您可以在其中使用优先级警报类型设置此查询的输出。 如果数据库文件大小的任何警报条件成功,用户也可以在“过程”中附加此逻辑,并将邮件退回数据库监视团队。

结论 (Conclusion)

We discussed how we can monitor the actual database file size and its free space with the help of monitoring script and shrink solutions in this article. If you have any questions, feel free to ask in the comments section below.

在本文中,我们讨论了如何通过监视脚本和缩小解决方案来监视实际的数据库文件大小及其可用空间。 如果您有任何疑问,请随时在下面的评论部分中提问。

翻译自: https://www.sqlshack.com/how-to-determine-free-space-and-file-size-for-sql-server-databases/

  • 0
    点赞
  • 0
    评论
  • 0
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

表情包
插入表情
评论将由博主筛选后显示,对所有人可见 | 还能输入1000个字符
©️2021 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值