Kiểm tra và thu gọn file data file log của Database trong SQL Server

• Kiểm tra kích cỡ file database
• Kiểm tra kích cỡ bảng trong database
• Thu gọn database với SHRINKDATABASE
• Thu gọn LOG với SHRINKFILE
• Kiểm tra query đang thi hành

Kiểm tra kích thước, nơi lưu file dữ liệu và log của CSDL SQL Server

Mỗi Database trong SQL Server có một định danh database_id trong sys.master_files, mỗi Database có các file dữ liệu và file log. Truy vấn sau sẽ hiện thị kích thước (MB) của các file này, cũng như nơi lưu trữ file này trên ổ đĩa

Trong truy vấn có thiết lập lọc database_id > 4 để không thống kế các Database hệ thống. Ngoài ra nếu muốn truy vấn đến một CSDL cụ thể bạn tự cho vào lọc theo database_id hoặc database_name ở mệnh đề where.

select database_id as db_id,
    DB_NAME(database_id) as db_name,
    type_desc,
    name as [FileName],
    physical_name as [File Path],
    CAST(ROUND(size/128.0, 2) as numeric(36,2))  as [Size (MB)]
from sys.master_files
where type in (0,1) and database_id > 4
order by database_id

Kiểm tra kích thước của các bảng trong CSDL SQL Server

Câu truy vấn sau hiện hiện thị thông tin thống kê về kích thước của các bảng biểu trong cơ sở dữ liệu.

Giả sử kiểm tra CSDL có tên là webdb, thì nó liệt kê tất cả các bảng của CSDL, mỗi bảng có bao nhiêu dòng dữ liệu, bảng đó chiếm bao nhiêu dung lượng (không gian lưu trữ được cấp phát)

use webdb;

select
    t.NAME as [Table],
    p.rows,
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) as numeric(36, 2)) as [Total Space (MB)],
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) as numeric(36, 2)) as [Used Space(MB)],
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) as numeric(36, 2)) as [Unused Space (MB)]
from
    sys.tables t
inner join
    sys.indexes i ON t.OBJECT_ID = i.object_id
inner join
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
inner join
    sys.allocation_units a ON p.partition_id = a.container_id
left outer join
    sys.schemas s ON t.schema_id = s.schema_id
where
    t.NAME not like 'dt%'
    and t.is_ms_shipped = 0
    and i.OBJECT_ID > 255
group by
    t.Name, s.Name, p.Rows
order by
    [Total Space (MB)] DESC, t.Name

Sử dụng SHRINKDATABASE và SHRINKFILE thu nhỏ kích thước file Database

Khi Database hoạt động, file dữ liệu và file log có thể có dung lượng lớn hơn dữ liệu thực nó chiếm dữ (như cấp trước dung lượng dự trữ) hoặc không thu hồi dữ liệu xóa ... Để giảm dung lượng có thể dùng chỉ thị DBCC SHRINKDATABASE

DBCC SHRINKDATABASE cố gắng thu gọn dung lượng file dữ liệu và file log của một database cụ thể.

Cú pháp:

DBCC SHRINKDATABASE(database_name, target_percent)

• database_name tên cơ sơ dữ liệu muốn thu gọn dung lượng
• target_percent để lại phần trăm dung lượng dự trữ sau thu gọn, như 5%, 10%

Ví dụ, thu gọn log và data cho Database có tên shopdata, với mục tiêu để lại 5% dung lượng dự trữ, thực hiện truy vấn T-SQL sau

USE shopdata;

ALTER DATABASE shopdata SET RECOVERY SIMPLE
GO

DBCC SHRINKDATABASE(shopdata,5)
GO

ALTER DATABASE shopdata SET RECOVERY FULL
GO

Trong T-SQL trên có đoạn chuyển Recovery Model sang SIMPLE trước khi SHRINKDATABASE, để biết thêm xem: backup và restore database trong SQL Server

Thu gọn LOG

Đối với log file, bạn có thể sử dụng SHRINKFILE để thu gọn về một kích thước mục tiêu, ví dụ database shopdata có file log là shopdata_log, để thu gọn bạn chạy

use shopdata;
ALTER DATABASE shopdata SET RECOVERY SIMPLE;
GO
DBCC SHRINKFILE(N'shopdata_log', 1)
GO
ALTER DATABASE shopdata SET RECOVERY FULL;
GO

Kiểm tra các query đang chạy trên SQL Server

Với Database lớn, thực hiện thu gọn có khi mất thời gian dài, để kiểm tra xem các query còn đang chạy thì thực hiện

SELECT  *
FROM    sys.dm_exec_requests
        CROSS APPLY sys.dm_exec_sql_text(sql_handle)