SQL Script Kiểm tra

Những câu lệnh T-sql kiểm tra hệ thống cần thiết

0. KIỂM TRA TRẠNG THÁI DATABASES

SELECT * FROM sys.databases 

1.ĐƯỜNG DẪN THƯ MỤC FILE DATABASE

SELECT 

    mdf.database_id, 

    mdf.name, 

    mdf.physical_name as data_file, 

    ldf.physical_name as log_file, 

    db_size = CAST((mdf.size * 8.0)/1024 AS DECIMAL(8,2)), 

    log_size = CAST((ldf.size * 8.0 / 1024) AS DECIMAL(8,2))

FROM (SELECT * FROM sys.master_files WHERE type_desc = 'ROWS' ) mdf

JOIN (SELECT * FROM sys.master_files WHERE type_desc = 'LOG' ) ldf

ON mdf.database_id = ldf.database_id

2.KIỂM TRA DUNG LƯỢNG CÁC TABLE TRONG DATABASE

USE [YourDBName] -- Thay đổi tên Database cần kiểm tra
GO
SELECT
s.Name AS SchemaName,
t.Name AS TableName,
p.rows AS RowCounts,
CAST(ROUND((SUM(a.used_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Used_MB,
CAST(ROUND((SUM(a.total_pages) - SUM(a.used_pages)) / 128.00, 2) AS NUMERIC(36, 2)) AS Unused_MB,
CAST(ROUND((SUM(a.total_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Total_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
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
GROUP BY t.Name, s.Name, p.Rows
ORDER BY s.Name, t.Name
GO

3.KIỂM TRA CÁC KẾT NỐI TỚI DATABASE

-- This script returns the Database, Number of open connections and logged-in user credentials

SELECT DB_NAME(dbid) as DBName, COUNT(dbid) as NumberOfConnections,loginame as LoginName
FROM sys.sysprocesses
WHERE dbid > 0
GROUP BY dbid, loginame

-- This script returns the status, login name and host name for the database you specify.

SELECT spid, status, loginame, hostname, blocked, db_name(dbid), cmd
FROM sys.sysprocesses
WHERE db_name(dbid) = 'databasename'



-- SQL Server 2005 introduced Dynamic Management Views, in this case we can just check if the is_user_process is 1 instead of checking for the value of spid.
-- This will work in SQL Server 2005 and 2008.

SELECT count(*) as ConnectionCount
FROM sys.dm_exec_sessions
WHERE is_user_process = 1


--If you want both system and user information:

SELECT count(*) as ConnectionCount,
CASE when is_user_process =1 then 'UserProcess' else 'System Process' end
FROM sys.dm_exec_sessions
GROUP by is_user_process


--Also, can look at the sys.dm_exec_connections Dynamic Management Views. This view only shows user connections.

SELECT  count(*)  
FROM sys.dm_exec_connections

4.ĐÓNG KẾT NỐI ỨNG DỤNG TỚI DATABASE

USE master;
DECLARE @kill varchar(8000);
SET @kill = ''; 
 
SELECT @kill = @kill 'kill ' CONVERT(varchar(5), spid) ';' 
FROM master..sysprocesses 
WHERE dbid = db_id('Tên database') --db_id('Tên database') lấy id của process database cần kill
 
EXEC(@kill);  --thực thi câu sql động -> kill id
 
--- Sau khi kill connection ta se drop được database dễ dàng
DROP DATABASE [Tên database]
GO

5.KIỂM TRA NGÀY TẠO DATABASE

--Cách 1:
EXEC sp_helpdb 'DTECH'
GO
--Cách 2:
SELECT      create_date
FROM        sys.databases
WHERE       name = 'DTECH'
GO
-- Hoặc 2.1
SELECT      create_date,name
FROM        sys.databases
WHERE       year(create_date)=2023
order by create_date desc

--Cách 3:
DBCC TRACEON(3604)
GO
DBCC PAGE('SqlAndMe', 1, 9, 3)
GO

6.KIỂM TRA NGÀY CUỐI CÙNG TÁC ĐỘNG VÀO DATABASE

--Nhìn qua ngày thay đổi cuối cùng của Table
SELECT name [TableName],
       Create_date [CreateDate],
       modify_date [LastUpdate]
FROM sys.all_objects
WHERE type = 'U'
ORDER BY modify_date DESC;

7.THÔNG TIN DATABASE NGUỒN ĐÍCH BACKUP RESTORE

SELECT 
   [rs].[destination_database_name], -- Database đích
   [bs].[database_name] as [source_database_name], --Database nguồn
   [rs].[restore_date],  -- Thời gian khôi phục
   [bs].[backup_start_date], -- Thời gian bắt đầu sao lưu
   [bs].[backup_finish_date], -- Thời gian kết thúc sao lưu
   [bmf].[physical_device_name] as [backup_file_used_for_restore] -- Đường dẫn tên tệp sao lưu
FROM msdb..restorehistory rs
INNER JOIN msdb..backupset bs ON [rs].[backup_set_id] = [bs].[backup_set_id]
INNER JOIN msdb..backupmediafamily bmf ON [bs].[media_set_id] = [bmf].[media_set_id] 

8.KIỂM TRA NGÀY TẠO, NGÀY SỬA CUỐI CÙNG VÀO TABLE

Use DTECH
GO
SELECT
        [name]
       ,create_date
       ,modify_date
FROM
        sys.tables

9.KIỂM TRA SO SÁNH CẤU TRÚC TABLE Ở 2 DATABASE

-- Thay thế các giá trị trong ngoặc vuông [] bằng tên thực tế
DECLARE @TableName NVARCHAR(128) = 'DuLieuKeToan';
DECLARE @SourceDB NVARCHAR(128) = 'DB_NGUON'; -- <== Tên Database nguồn
DECLARE @DestDB NVARCHAR(128) = 'DB_DICH';   -- <== Tên Database đích

-- Xây dựng các câu lệnh SQL động
DECLARE @SourceSQL NVARCHAR(MAX);
DECLARE @DestSQL NVARCHAR(MAX);

SET @SourceSQL = N'
SELECT 
    COLUMN_NAME, 
    DATA_TYPE, 
    CHARACTER_MAXIMUM_LENGTH, 
    NUMERIC_PRECISION, 
    NUMERIC_SCALE
FROM [' + @SourceDB + '].INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName';

SET @DestSQL = N'
SELECT 
    COLUMN_NAME, 
    DATA_TYPE, 
    CHARACTER_MAXIMUM_LENGTH, 
    NUMERIC_PRECISION, 
    NUMERIC_SCALE
FROM [' + @DestDB + '].INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName';

-- Tạo bảng tạm để lưu kết quả
IF OBJECT_ID('tempdb..#SourceSchema') IS NOT NULL DROP TABLE #SourceSchema;
CREATE TABLE #SourceSchema (COLUMN_NAME NVARCHAR(128), DATA_TYPE NVARCHAR(128), CHARACTER_MAXIMUM_LENGTH INT, NUMERIC_PRECISION TINYINT, NUMERIC_SCALE INT);
INSERT INTO #SourceSchema EXEC sp_executesql @SourceSQL, N'@TableName NVARCHAR(128)', @TableName;

IF OBJECT_ID('tempdb..#DestSchema') IS NOT NULL DROP TABLE #DestSchema;
CREATE TABLE #DestSchema (COLUMN_NAME NVARCHAR(128), DATA_TYPE NVARCHAR(128), CHARACTER_MAXIMUM_LENGTH INT, NUMERIC_PRECISION TINYINT, NUMERIC_SCALE INT);
INSERT INTO #DestSchema EXEC sp_executesql @DestSQL, N'@TableName NVARCHAR(128)', @TableName;


-- So sánh và hiển thị kết quả
SELECT 
    COALESCE(nguon.COLUMN_NAME, dich.COLUMN_NAME) AS TenCot,
    nguon.DATA_TYPE AS KieuDuLieu_Nguon,
    dich.DATA_TYPE AS KieuDuLieu_Dich,
    nguon.CHARACTER_MAXIMUM_LENGTH AS DoRong_Nguon,
    dich.CHARACTER_MAXIMUM_LENGTH AS DoRong_Dich,
    nguon.NUMERIC_PRECISION AS DoChinhXac_Nguon,
    dich.NUMERIC_PRECISION AS DoChinhXac_Dich,
    nguon.NUMERIC_SCALE AS SoThapPhan_Nguon,
    dich.NUMERIC_SCALE AS SoThapPhan_Dich,
    CASE 
        WHEN dich.COLUMN_NAME IS NULL THEN N'Cột chỉ tồn tại ở NGUỒN'
        WHEN nguon.COLUMN_NAME IS NULL THEN N'Cột chỉ tồn tại ở ĐÍCH'
        WHEN nguon.DATA_TYPE <> dich.DATA_TYPE THEN N'Khác kiểu dữ liệu'
        WHEN ISNULL(nguon.CHARACTER_MAXIMUM_LENGTH, -1) <> ISNULL(dich.CHARACTER_MAXIMUM_LENGTH, -1) THEN N'Khác độ rộng chuỗi'
        WHEN ISNULL(CAST(nguon.NUMERIC_PRECISION AS INT), -1) <> ISNULL(CAST(dich.NUMERIC_PRECISION AS INT), -1) 
             OR ISNULL(nguon.NUMERIC_SCALE, -1) <> ISNULL(dich.NUMERIC_SCALE, -1) THEN N'Khác độ chính xác/thập phân số'
        ELSE ''
    END AS GhiChu
FROM #SourceSchema AS nguon
FULL OUTER JOIN #DestSchema AS dich ON nguon.COLUMN_NAME = dich.COLUMN_NAME
WHERE 
    dich.COLUMN_NAME IS NULL 
    OR nguon.COLUMN_NAME IS NULL
    OR nguon.DATA_TYPE <> dich.DATA_TYPE
    OR ISNULL(nguon.CHARACTER_MAXIMUM_LENGTH, -1) <> ISNULL(dich.CHARACTER_MAXIMUM_LENGTH, -1)
    -- SỬA LỖI Ở ĐÂY: Chuyển sang INT trước khi so sánh với -1
    OR ISNULL(CAST(nguon.NUMERIC_PRECISION AS INT), -1) <> ISNULL(CAST(dich.NUMERIC_PRECISION AS INT), -1)
    OR ISNULL(nguon.NUMERIC_SCALE, -1) <> ISNULL(dich.NUMERIC_SCALE, -1);

-- Dọn dẹp bảng tạm
DROP TABLE #SourceSchema;
DROP TABLE #DestSchema;