SQL数据库运维全部实用命令

1. 收缩日志

如果您知道事务日志文件包含将不需要的未使用空间，则通过减少事务日志的大小，可以回收过多空间。此过程称为“收缩”日志文件。

仅当数据库处于联机状态，而且至少一个虚拟日志文件可用时，收缩才会发生。在某些情况下，直到下一个日志截断后，才能收缩日志。

1. 1. 右键数据库属性

1. 1. 更改数据库恢复模式，将模式改为简单模式

1. 1. 右键数据库，任务—>收缩—>文件

1. 1. 文件类型选择日志，勾选在释放未使用的空间前重新组织页，将文件收缩到给出的最小值。

1. 1. 将数据库恢复模式改回完整模式。

2、查看锁表

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算机资源（如CPU、RAM、I/O等）的争用外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

1. 1. 查看锁表信息

SELECT request_session_id spid,

OBJECT_NAME(resource_associated_entity_id) OBJname,

DB_NAME(resource_database_id) DBname

FROM sys.dm_tran_locks

WHERE resource_type='OBJECT'

AND OBJECT_NAME(resource_associated_entity_id) = 'CPP_CURING_PLAN'; -- 替换表名

1. 1. 根据阻塞的进程ID查询阻塞的sql语句代码

DBCC INPUTBUFFER (80)

1. 1. 杀死阻塞进程

KILL 80

3、索引碎片整理

在数据库管理系统中，索引碎片是指索引中数据分布不均匀的现象，这会导致查询效率下降，因为数据库系统需要花费更多的时间来查找数据。索引碎片通常发生在频繁进行插入、删除和更新操作的数据库表中。为了优化性能，需要定期进行索引碎片整理

索引碎片指的是数据库中索引的非连续性，这可能会导致查询变慢，增加数据库的维护成本。

1. 1. 查看表的索引碎片信息

SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,

ind.name AS IndexName,

indexstats.index_type_desc AS IndexType,

indexstats.avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats

INNER JOIN sys.indexes ind 

ON ind.object_id = indexstats.object_id

AND ind.index_id = indexstats.index_id

WHERE OBJECT_NAME(ind.OBJECT_ID) = 'JECN_USER_LOGIN_LOG' -- 替换表名

1. 1. 查看avg_fragmentation_in_percent的值

如果碎片较大（高于50），进行索引重建

ALTER INDEX [IndexName] ON [TableName] REBUILD -- 替换索引名和表名

如果碎片较小，可以进行索引重组

ALTER INDEX [IndexName] ON [TableName] REORGANIZE -- 替换索引名和表名

4、数据库分离附加

在使用SQL Server时，我们常常需要分离数据库的附加（Detach and Attach），这在数据库迁移或备份恢复等场景中非常有用

数据库迁移可以用分离附加来实现。

1. 1. 分离
      1. 右键数据库—>任务—>分离

1. 1. 1. 删除会话连接，点击确定

1. 1. 1. 数据库分离后，列表里已经没有demo数据库了，然后将分离后的数据库文件迁移到指定目录

1. 1. 附加
      1. 右键数据库—>附加

1. 1. 1. 点击添加，在新目录下选择demo.mdf文件，点击确定

至此，数据库demo

已迁移到新路径

1、连接信息查询

SELECT  session_id AS SPID,
    login_name AS Login,
    host_name AS Host,
    program_name AS Application,
    status,
    last_request_end_time AS LastActivity
FROM sys.dm_exec_sessions
WHERE is_user_process = 1;

按用户数统计：

SELECT 
    login_name AS Login,
    COUNT(*) AS ConnectionCount
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
GROUP BY login_name
ORDER BY ConnectionCount DESC;

2. 查询活跃会话

SELECT 
    r.session_id AS SPID,
    s.login_name AS Login,
    r.status AS Status,
    r.command AS CommandType,
    t.text AS SQLText,
    r.wait_type AS WaitType,
    r.wait_time AS WaitTimeMs
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
WHERE r.status != 'background';
 

长运行查询

SELECT 
    session_id AS SPID,
    start_time AS StartTime,
    DATEDIFF(MINUTE, start_time, GETDATE()) AS DurationMin,
    command AS CommandType,
    wait_type AS WaitType
FROM sys.dm_exec_requests
WHERE status = 'running'
ORDER BY DurationMin DESC;

锁查询语句：

SELECT
    request_session_id AS SPID,
    resource_type AS ResourceType,
    resource_description AS Resource,
    request_mode AS LockMode,
    request_status AS Status
FROM sys.dm_tran_locks;

查看阻塞链：

查看最耗资源的查询

SELECT TOP 10
    qs.total_logical_reads AS LogicalReads,
    qs.total_worker_time AS WorkerTime,
    qs.execution_count AS ExecCount,
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset
          WHEN -1 THEN DATALENGTH(st.text)
         ELSE qs.statement_end_offset
         END - qs.statement_start_offset)/2) + 1) AS QueryText
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY qs.total_logical_reads DESC;

查看等待统计

SELECT TOP 10
    wait_type AS WaitType,
    waiting_tasks_count AS WaitCount,
    wait_time_ms AS WaitTimeMs,
    signal_wait_time_ms AS SignalWaitTime
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE')
ORDER BY wait_time_ms DESC;

数据库文件使用情况

SELECT 
    DB_NAME(database_id) AS DatabaseName,
    name AS LogicalName,
    type_desc AS FileType,
    size/128.0 AS SizeMB,
    FILEPROPERTY(name, 'SpaceUsed')/128.0 AS UsedMB,
    (size - FILEPROPERTY(name, 'SpaceUsed'))/128.0 AS FreeMB
FROM sys.master_files;

按表统计空间使用

EXEC sp_spaceused 'TableName';(TableName输入实际的表名）

查看内存使用

SELECT 
    type AS MemoryType,
    name AS BufferPool,
    pages_kb/1024 AS SizeMB
FROM sys.dm_os_memory_clerks
WHERE pages_kb > 0
ORDER BY pages_kb DESC;