SQL Script Kiểm tra

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

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