65.9K
CodeProject 正在变化。 阅读更多。
Home

SQL Server 磁盘使用情况统计信息

李松博士
starIconstarIconstarIconstarIconstarIcon

5.00/5 (5投票s)

2015 年 9 月 24 日

CPOL

6分钟阅读

viewsIcon

21857

downloadIcon

351

这是一个用于检查 SQL Server 磁盘和数据文件使用情况统计的示例。

引言

这是一个用于检查 SQL Server 磁盘和数据文件使用情况统计的示例。

背景

对于真正大型且快速增长的数据库，磁盘空间是许多 DBA 关注的问题。同时，这也是一个难题。

  • 在 SQL Server 中，既有数据文件，也有日志文件；
  • 数据文件可以位于多个文件组中；
  • 数据文件可以放置在不同的磁盘上；
  • 每个数据文件都可以为初始大小、增长率和最大大小配置不同的设置；
  • 每个数据文件可能有其物理大小，但很可能其物理大小并未完全使用，因此它可以在需要增长之前容纳更多数据。

由于这些复杂性，需要一个易于使用的监控工具来检查磁盘和文件使用情况。在此示例中，我将介绍几个简单的 SQL 脚本来检查统计信息。我还会包含一个 JAVA Spring MVC 程序，用于在 Web 浏览器中显示信息。由于我们讨论的是 SQL Server，因此使用 .NET 程序来完成 UI 工作是理想的选择，但我注意到 Visual Studio 2015 community 创建了一个包含解决方案的大型包文件夹，其中许多依赖项实际上并未被使用。为了不占用 Code-Project 的硬盘空间，我决定创建一个 JAVA Spring MVC 项目。JAVA 项目要小得多。无论如何，示例的重要部分是 SQL 脚本，如果您对 Web 项目不感兴趣，可以跳过它。

示例数据库

本示例中使用的 SQL Server 是我本地计算机上的 SQL Server Express 2014。如果您想尝试此示例，我认为您也可以使用早期版本的 SQL Server，但我不能保证。要创建示例数据库，可以运行以下脚本。

USE [master]
GO

-- Create a login user
IF EXISTS
    (SELECT * FROM sys.server_principals WHERE name = N'TestUser')
DROP LOGIN [TestUser]
GO
    
EXEC sp_addlogin @loginame = 'TestUser', @passwd  = 'Password123';
GO
    
-- Create the database
IF EXISTS 
    (SELECT name FROM sys.databases 
        WHERE name = N'DBDiskStatusExperiment')
BEGIN
    ALTER DATABASE [DBDiskStatusExperiment] SET SINGLE_USER
        WITH ROLLBACK IMMEDIATE
    DROP DATABASE [DBDiskStatusExperiment]
END
GO

DECLARE @A VARCHAR(100)
CREATE DATABASE [DBDiskStatusExperiment]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = 'DBDiskStatusExperiment', 
    FILENAME =
        'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\DBDiskStatusExperiment.mdf',
    SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'DBDiskStatusExperiment_log',
    FILENAME =
        'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\DBDiskStatusExperiment_log.ldf', 
    SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

-- Add additional database files for test purpose
ALTER DATABASE DBDiskStatusExperiment ADD FILEGROUP TestFileGroup;
GO

ALTER DATABASE DBDiskStatusExperiment 
ADD FILE 
( NAME = 'TestDataFile-1',
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\TestDataFile-1.ndf',
    SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB
),
(NAME = 'TestDataFile-2',
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\TestDataFile-2.ndf',
    SIZE = 5MB, MAXSIZE = UNLIMITED, FILEGROWTH = 5MB
)
TO FILEGROUP TestFileGroup
GO

USE DBDiskStatusExperiment
GO

EXEC sp_grantdbaccess 'TestUser'
GO

CREATE PROCEDURE dbo.GetDatabaseDiskStatistics AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @DBName VARCHAR(255) = DB_NAME()
    DECLARE @DiskStatistics TABLE(drive VARCHAR(10), size INT, free_space INT)

    INSERT INTO @DiskStatistics
    SELECT volume_mount_point, 
        total_bytes/1048576 as Size_in_MB,
        available_bytes/1048576 as Free_in_MB
    FROM sys.master_files AS f CROSS APPLY 
        sys.dm_os_volume_stats(f.database_id, f.file_id)
    WHERE DB_NAME(F.database_id) = @DBName
    GROUP BY volume_mount_point, total_bytes, available_bytes

    SELECT * FROM @DiskStatistics

    SELECT ISNULL(FG.name, 'N/A') FileGroup, D.drive, F.name, physical_name,
        F.size/128 file_size, fileproperty(FILE_NAME(F.file_id), 'SpaceUsed')/128 used_size,
        CASE WHEN is_percent_growth = 1 THEN CAST(growth AS VARCHAR(3)) + '%'
            ELSE CAST(growth/128 AS VARCHAR(100)) END growth,
        CASE WHEN max_size < 0 THEN 'UNLIMITED'
            ELSE CAST(max_size/128 AS VARCHAR(100)) END max_size
    FROM sys.master_files F
        LEFT JOIN sys.filegroups FG ON F.data_space_id = FG.data_space_id
        LEFT JOIN @DiskStatistics D ON F.physical_name LIKE D.drive + '%'
    WHERE DB_NAME(database_id) = @DBName
    ORDER BY FG.name

END
GO

GRANT EXECUTE ON OBJECT::[dbo].[GetDatabaseDiskStatistics] TO TestUser
GO

USE [master]
GRANT VIEW SERVER STATE TO TestUser
GRANT VIEW ANY DEFINITION TO TestUser
GO
  • 您需要管理员权限才能运行脚本；
  • 脚本添加了数据库的数据文件和日志文件。请检查用于放置文件的文件夹是否确实存在。如果文件夹不存在，脚本将运行失败；
  • 我的计算机只有一个驱动器。为了更有趣，如果您有更多的驱动器可以使用，可以尝试将更多数据文件添加到不同的驱动器上。

脚本成功运行后，我们创建了 [DBDiskStatusExperiment] 数据库。

  • 为了本示例的目的，我没有向数据库添加任何表，以简化操作；
  • 示例 UI 应用程序将使用 [GetDatabaseDiskStatistics] 存储过程来显示磁盘和文件使用情况统计；
  • UI 应用程序将使用 [TestUser] 来访问 SQL Server。该用户已被授予运行 [GetDatabaseDiskStatistics] 存储过程的权限以及读取磁盘和文件使用情况信息的权限。

如果您在管理工作室中右键单击数据库 -> “属性” -> “文件”，您可以看到该数据库的文件。

检查磁盘和数据库文件信息

[xp_fixeddrives] 存储过程

要检查数据库可用的空闲磁盘空间，我们可以使用 [xp_fixeddrives] 存储过程。

虽然这是一个简单实用的存储过程，但它不提供总磁盘空间。要获取总磁盘空间，我们可以使用 [dm_os_volume_stats] 表值函数。

[dm_os_volume_stats] 函数

[dm_os_volume_stats] 函数需要数据库 ID 和文件 ID 来获取信息。它为我们提供了总磁盘空间和可用空闲空间。如果您查看操作系统中的磁盘属性，可以看到这些数字是正确的。

由于磁盘空间会不断变化，因此运行 [dm_os_volume_stats] 和打开磁盘属性窗口之间的时间差可能会导致数字有所不同。在我的实验中，它们完全匹配。要获取每个数据库文件的信息，可以使用 [master_files] 视图。

[master_files] 视图

  • [data_space_id] 列是文件所属文件组的 ID；
  • [type_desc] 列是文件的类型。如果是“ROWS”，则表示数据文件。如果是“LOG”，则表示日志文件；
  • [physical_name] 列是操作系统文件名和位置；
  • [size] 列是文件的当前大小；
  • [max_size] 列是文件的最大大小。当它为“-1”时，表示文件可以无限增长；
  • [growth] 列是文件的增长率；
  • [is_percent_growth] 列告诉我们增长率是否为百分比。如果为 1，则增长率是百分比；
  • 大小的单位是页。在 SQL Server 中，1 MB 等于 128 页。

数据库文件可能不会使用其所有占用空间，这意味着数据文件可以在必须增长之前容纳更多数据。要查找实际使用的空间，可以使用 [fileproperty] 函数。

[fileproperty] 函数

同样，[fileproperty] 函数返回的“used_space”的单位也是页。

摘要

总而言之，我创建了 [GetDatabaseDiskStatistics] 存储过程。

CREATE PROCEDURE dbo.GetDatabaseDiskStatistics AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @DBName VARCHAR(255) = DB_NAME()
    DECLARE @DiskStatistics TABLE(drive VARCHAR(10), size INT, free_space INT)

    INSERT INTO @DiskStatistics
    SELECT volume_mount_point, 
        total_bytes/1048576 as Size_in_MB,
        available_bytes/1048576 as Free_in_MB
    FROM sys.master_files AS f CROSS APPLY 
        sys.dm_os_volume_stats(f.database_id, f.file_id)
    WHERE DB_NAME(F.database_id) = @DBName
    GROUP BY volume_mount_point, total_bytes, available_bytes

    SELECT * FROM @DiskStatistics

    SELECT ISNULL(FG.name, 'N/A') FileGroup, D.drive, F.name, physical_name,
        F.size/128 file_size, fileproperty(FILE_NAME(F.file_id), 'SpaceUsed')/128 used_size,
        CASE WHEN is_percent_growth = 1 THEN CAST(growth AS VARCHAR(3)) + '%'
            ELSE CAST(growth/128 AS VARCHAR(100)) END growth,
        CASE WHEN max_size < 0 THEN 'UNLIMITED'
            ELSE CAST(max_size/128 AS VARCHAR(100)) END max_size
    FROM sys.master_files F
        LEFT JOIN sys.filegroups FG ON F.data_space_id = FG.data_space_id
        LEFT JOIN @DiskStatistics D ON F.physical_name LIKE D.drive + '%'
    WHERE DB_NAME(database_id) = @DBName
    ORDER BY FG.name

END
GO

运行 [GetDatabaseDiskStatistics]，我们得到以下结果，其中应包含有关数据库磁盘空间和文件空间的重要信息。当然，您可以随时对其进行更改以获取更多您感兴趣的信息。如果您有多个驱动器，可以将更多文件放在不同的驱动器上，以查看存储过程的工作方式。

在 UI 上显示信息

为了在 UI 上显示信息，我创建了一个 JAVA Spring MVC 应用程序。如果您对此不感兴趣，可以跳过它。

这是一个 Maven 项目。如果您不熟悉 Maven，可以查看此 链接。要创建此 Web 应用程序，以下是最低 Maven 依赖项。

<dependencies>         
        <!-- Sevlet jars for compilation -->
        <dependency>
                <groupId>org.apache.tomcat</groupId>
                <artifactId>tomcat-servlet-api</artifactId>
                <version>${tomcat.version}</version>
                <scope>provided</scope>
        </dependency>
        
        <!-- Minimal dependencies for Spring MVC -->
        <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-core</artifactId>
                <version>${spring.version}</version>
        </dependency>
        <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-web</artifactId>
                <version>${spring.version}</version>
        </dependency>
        <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-webmvc</artifactId>
                <version>${spring.version}</version>
        </dependency>
        
        <dependency>
        <groupId>net.sourceforge.jtds</groupId>
        <artifactId>jtds</artifactId>
        <version>${jtds.version}</version>
        <scope>runtime</scope>
    </dependency>
</dependencies>

MVC 视图模型实现在 "DatabaseFile.java" 和 "DatabaseDrive.java" 文件中。

package com.song.model;
    
public class DatabaseFile {
    
    private String fileGroup;
    private String drive;
    private String name;
    private String physicalName;
    private Long size;
    private Long usedSize;
    private String growth;
    private String maxSize;
    
    public String getFileGroup() { return fileGroup; }
    public void setFileGroup(String v) { this.fileGroup = v; }
    public String getDrive() { return drive; }
    public void setDrive(String v) { this.drive = v; }
    public String getName() { return name; }
    public void setName(String v) { this.name = v; }
    public String getPhysicalName() { return physicalName; }
    public void setPhysicalName(String v) { this.physicalName = v; }
    public Long getSize() { return size; }
    public void setSize(Long v) { this.size = v; }
    public Long getUsedSize() { return usedSize; }
    public void setUsedSize(Long v) { this.usedSize = v; }
    public String getGrowth() { return growth; }
    public void setGrowth(String v) { this.growth = v; }
    public String getMaxSize() { return maxSize; }
    public void setMaxSize(String v) { this.maxSize = v; }
}
package com.song.model;
    
import java.util.ArrayList;
import java.util.List;
    
public class DatabaseDrive {
    public DatabaseDrive()
    {
        files = new ArrayList<DatabaseFile>();
    }
    
    private String drive;
    private Long size;
    private Long freeSpace;
    private List<DatabaseFile> files;
    
    public String getDrive() { return drive; }
    public void setDrive(String v) { this.drive = v; }
    public Long getSize() { return size; }
    public void setSize(Long v) { this.size = v; }
    public Long getFreeSpace() { return freeSpace; }
    public void setFreeSpace(Long v) { this.freeSpace = v; }
    public List<DatabaseFile> getFiles() { return files; }
}

为了将信息加载到视图模型中，我创建了 "DBConnector" 和 "DAO" 类。

package com.song.database;
    
import java.sql.Connection;
import java.sql.DriverManager;
    
import javax.naming.InitialContext;
    
public class DBConnector {
    private final static class ConnInformation {
        public String DBUrl;
        public String UserName;
        public String Password;
        
        private ConnInformation(String dBUrl,
                String userName, String password) {
            DBUrl = dBUrl;
            UserName = userName;
            Password = password;
        }
        
        private static ConnInformation instance = null;
        private static synchronized ConnInformation getInstance() throws Throwable{
            if (instance == null) {
                try {
                    InitialContext initialContext = new javax.naming.InitialContext();
                    String contextString = (String) initialContext
                            .lookup("java:comp/env/DBConnectionInformation");
                    
                    String[] info = contextString.split("\\|");
                    instance = new ConnInformation(info[0], info[1], info[2]);
                } catch (Throwable e) {
                    instance = null;
                    throw e;
                }
            }
            
            return instance;
        }
    }
    
    public static Connection getAConnection(boolean autocommit) throws Throwable {
        ConnInformation connInfo = ConnInformation.getInstance();
        
        Connection connection = null;
        try {
            Class.forName("net.sourceforge.jtds.jdbc.Driver");
            connection = DriverManager
                    .getConnection(connInfo.DBUrl, connInfo.UserName, connInfo.Password);
            
            connection.setAutoCommit(autocommit);
        } catch (Throwable e) {throw e;}
        
        return connection;
    }
    
    public static Connection getAConnection() throws Throwable {        
        return getAConnection(true);
    }
}
package com.song.database;
    
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.util.HashMap;
import java.util.LinkedHashMap;
    
import com.song.model.DatabaseDrive;
import com.song.model.DatabaseFile;
    
public class DAO {
    
    public HashMap<String, DatabaseDrive> getDiskStatistics() throws Throwable {
        HashMap<String, DatabaseDrive> drives = new LinkedHashMap<String, DatabaseDrive>();
    
        String sql = "{call GetDatabaseDiskStatistics}";
        try (Connection con = DBConnector.getAConnection()) {
            try (CallableStatement stmt = con.prepareCall(sql)) {
    
                stmt.execute();
                ResultSet rs = stmt.getResultSet();
                while (rs.next()) {
                    DatabaseDrive drive = new DatabaseDrive();
                    String driveLetter = rs.getString("drive");
                    drive.setDrive(driveLetter);
                    drive.setSize(rs.getLong("size"));
                    drive.setFreeSpace(rs.getLong("free_space"));
    
                    drives.put(driveLetter, drive);
                }
    
                stmt.getMoreResults();
                rs = stmt.getResultSet();
                while (rs.next()) {
                    DatabaseFile file = new DatabaseFile();
                    String driveLetter = rs.getString("drive");
                    file.setDrive(driveLetter);
                    file.setFileGroup(rs.getString("FileGroup"));
                    file.setName(rs.getString("name"));
                    file.setPhysicalName(rs.getString("physical_name"));
                    file.setSize(rs.getLong("file_size"));
                    file.setUsedSize(rs.getLong("used_size"));
                    file.setMaxSize(rs.getString("max_size"));
                    file.setGrowth(rs.getString("growth"));
    
                    drives.get(driveLetter).getFiles().add(file);
                }
    
            }
        }
    
        return drives;
    }
}

连接数据库的信息在 "web.xml" 文件中配置。

<env-entry>  
        <env-entry-name>DBConnectionInformation</env-entry-name>
        <env-entry-type>java.lang.String</env-entry-type>
        <env-entry-value>
            jdbc:jtds:sqlserver:///DBDiskStatusExperiment|TestUser|Password123
        </env-entry-value>    
</env-entry>

MVC 控制器和 MVC 视图分别在 "HomeController.java" 和 "example.jsp" 文件中实现。

package com.song.web.controller;
    
import java.util.HashMap;
    
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.servlet.ModelAndView;
    
import com.song.database.DAO;
import com.song.model.DatabaseDrive;
    
@Controller
@RequestMapping("/home")
public class HomeController {
    
    @RequestMapping(value = "/example", method = RequestMethod.GET)
    public ModelAndView mainpage() throws Throwable {
    
        DAO dao = new DAO();
        HashMap<String, DatabaseDrive> drives = dao.getDiskStatistics();
    
        ModelAndView modelView = new ModelAndView();
        modelView.addObject("data", drives);
        modelView.setViewName("home/example.jsp");
    
        return modelView;
    }
}
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Database disk status</title>
<link rel="stylesheet" type="text/css"
    href="<%=baseUrl%>styles/app.css" />
<body>
<%for(DatabaseDrive drive : drives.values()) { %>
<div>
<span><%=drive.getDrive() %> - 
    total size: <%=drive.getSize() %> - 
    free space: <%=drive.getFreeSpace() %> -
    ALL unit: MB</span>
<table>
    <thead>
        <tr>
            <td>File Group</td>
            <td>Name</td>
            <td>Size</td>
            <td>Used Size</td>
            <td>Growth</td>
            <td>Max Size</td>
        </tr>
    </thead>
    <tbody>
<%for(DatabaseFile file: drive.getFiles()) { %>
    <tr>
        <td><%=file.getFileGroup() %></td>
        <td><%=file.getName() %></td>
        <td><%=file.getSize() %></td>
        <td><%=file.getUsedSize() %></td>
        <td><%=file.getGrowth() %></td>
        <td><%=file.getMaxSize() %></td>
    </tr>
<%} %>
    </tbody>
</table>
</div>
<%} %>
</body>
</html>

运行 MVC 应用程序

您可以创建 "war" 文件来运行示例 MVC 应用程序，方法是发出 "mvn clean install" 命令并将其部署到 Servlet 容器。您也可以将示例项目导入 Eclipse 并从那里运行。如果您不熟悉导入 Maven 项目，可以查看此 链接。

当您加载网页时，您将看到磁盘和数据库文件信息都按照存储过程 [GetDatabaseDiskStatistics] 显示。如果您能将数据库文件添加到多个磁盘上以查看其工作方式，那就太好了。由于我只有一个驱动器，所以我对现在看到的感到满意。我已经使用 JAVA 8、Eclipse Luna 和 Tomcat 7 测试了这个应用程序。

关注点

  • 这是一个检查 SQL Server 磁盘和数据文件使用情况统计的示例；
  • 此示例附带一个 SQL 脚本和一个 Spring MVC 应用程序。如果您对 MVC 应用程序不感兴趣，可以忽略它；
  • 希望您喜欢我的帖子，希望这篇文章能有所帮助。

历史

首次修订 - 2015 年 9 月 24 日。

© . All rights reserved.