查询MSSQL表空间：精准把握表空间使用情况

1. MSSQL表空间概述

在MSSQL数据库中，表空间是指一组表在物理存储设备上的存储空间。每个数据库都至少有一个表空间，这个表空间叫做默认表空间。默认情况下，该默认表空间承载着整个数据库。但是，在数据库中，可以为不同的表创建不同的表空间，使得表数据能更好地映射到物理设备的磁盘上。通过管理表空间以及监视表空间使用情况，可以帮助DBA更好地维护数据库，避免数据库表空间容量不足的情况。

2. 查看MSSQL表空间使用情况

2.1. 查看表空间

在MSSQL Server中，可以通过查询system表来查看所有的表空间。我们可以使用以下命令来查询MSSQL Server中所有的表空间：

USE master; 
GO 
SELECT name, type_desc, physical_name   
FROM sys.master_files;  
GO

上述命令会返回数据库中的所有表空间的名称，类型以及所在文件的物理路径。

2.2. 查看表空间使用情况

为了了解每个表空间的使用情况，我们需要查询系统表sys.allocation_units。下面的命令将返回每个表空间的使用情况（其中数据库名称为mydb）：

USE mydb; 
GO 
SELECT 
   a.type_desc, 
   COUNT(*) AS Num_of_Allocation_Units, 
   CAST(SUM(a.total_pages) / 128.0 AS DECIMAL(10, 2)) AS Total_Space_MB, 
   CAST(SUM(a.used_pages) / 128.0 AS DECIMAL(10, 2)) AS Used_Space_MB, 
   CAST((SUM(a.total_pages) - SUM(a.used_pages)) / 128.0 AS DECIMAL(10, 2)) AS Free_Space_MB  
FROM sys.allocation_units AS a   
JOIN sys.data_spaces AS d ON a.data_space_id = d.data_space_id  
JOIN sys.database_files AS f ON d.default_file_id = f.file_id   
WHERE f.type = 0    
GROUP BY a.type_desc;
GO

上述命令将返回每个表空间的类型、分配单元数量、总空间、已用空间和可用空间。

3. 监视表空间使用情况

3.1. 使用MSSQL Server Management Studio

MSSQL Server Management Studio提供了一种监视数据库表空间使用情况的简单方法。您可以打开MSSQL Server Management Studio并连接到您的服务器。然后，在Object Explorer窗格中右键单击数据库并选择Properties。在Properties窗格中，选择Storage节点。Properties窗格将显示有关数据库中每个表空间的详细信息，包括名称、状态、文件名、文件大小和可用空间。

3.2. 创建定期查询

为了定期监视表空间使用情况，可以使用以下脚本来创建一个定期查询。该脚本将在每月的1号早上6点运行，将结果写入名为tablespaces_report的表中：

USE mydb;  
GO
IF OBJECT_ID ('dbo.tablespaces_report', 'U') IS NOT NULL  
   DROP TABLE dbo.tablespaces_report;  
GO 
CREATE TABLE dbo.tablespaces_report  
(  
   server_name nvarchar(128) NOT NULL,  
   database_name nvarchar(128) NOT NULL,  
   file_name nvarchar(260) NOT NULL,  
   file_type nvarchar(60) NOT NULL,  
   total_size_mb decimal(12, 2) NOT NULL,  
   used_size_mb decimal(12, 2) NOT NULL,  
   free_size_mb decimal(12, 2) NOT NULL  
);  
GO 
DECLARE @sql nvarchar(max);  
SET @sql = N'  
INSERT INTO dbo.tablespaces_report  
    (server_name, database_name, file_name, file_type, total_size_mb, used_size_mb, free_size_mb)  
SELECT   
    @@SERVERNAME AS server_name,  
    DB_NAME() AS database_name,  
    f.name AS file_name,  
    fg.type_desc AS file_type,  
    CAST(f.size / 128.0 AS decimal(12, 2)) AS total_size_mb,  
    CAST(FILEPROPERTY(f.name, ''SpaceUsed'') / 128.0 AS decimal(12, 2)) AS used_size_mb,  
    CAST((f.size - FILEPROPERTY(f.name, ''SpaceUsed'')) / 128.0 AS decimal(12, 2)) AS free_size_mb  
FROM sys.database_files AS f  
JOIN sys.filegroups AS fg ON f.data_space_id = fg.data_space_id  
WHERE f.type = 0;';  
EXECUTE msdb.dbo.sp_add_jobstep  
    @job_name = N'Tablespace_Usage_Report',  
    @step_name = N'Generate Report',  
    @subsystem = N'TSQL',  
    @command = @sql;  
DECLARE @job_id binary(16);  
EXECUTE msdb.dbo.sp_add_job  
    @job_name = N'Tablespace_Usage_Report',  
    @enabled = 1,  
    @description = N'Reports tablespace usage on the 1st of each month';  
SELECT @job_id = job_id FROM msdb.dbo.sysjobs WHERE name = N'Tablespace_Usage_Report';  
EXEC sp_add_schedule  
    @schedule_name = N'Monthly_Tablespaces_Report',  
    @freq_type = 4,  
    @freq_interval = 1,  
    @active_start_time = 60000;  
EXECUTE msdb.dbo.sp_attach_schedule  
    @job_id = @job_id,  
    @schedule_name = N'Monthly_Tablespaces_Report';  
GO 

上述脚本将在每个月的第一天早上6点钟运行，并将结果写入名为tablespaces_report的表中。您可以根据需要更改此时间或表名。

4. 总结

通过查询系统表或定期查询，可以监视MSSQL Server表空间的使用情况。这有助于DBA更好地维护数据库，并防止表空间容量不足的情况。使用MSSQL Server Management Studio，您可以方便地查看有关每个表空间的详细信息。