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

1. 收缩日志

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

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

1. 右键数据库属性

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

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

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

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

2、查看锁表

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

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. 根据阻塞的进程ID查询阻塞的sql语句代码

DBCC INPUTBUFFER (80)

1. 杀死阻塞进程

KILL 80

3、索引碎片整理

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

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

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. 查看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. 数据库分离后，列表里已经没有demo数据库了，然后将分离后的数据库文件迁移到指定目录

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;