数据库干货：SQLServer运维实用的几个脚本

SELECT s2.dbid,
       s1.sql_handle,
       (
           SELECT TOP 1
                  SUBSTRING(   s2.text,
                               statement_start_offset / 2 + 1,
                               ((CASE
                                     WHEN statement_end_offset = -1 THEN
                               (LEN(CONVERT(NVARCHAR(MAX), s2.text)) * 2)
                                     ELSE
                                         statement_end_offset
                                 END
                                ) - statement_start_offset
                               ) / 2 + 1
                           )
       ) AS 执行SQL,
       last_worker_time '最后执行总耗时(毫秒)',
       last_execution_time '最后执行时间',
       total_worker_time '所有执行总耗时(毫秒)',
       min_worker_time '执行最小耗时(毫秒)',
       max_worker_time '执行最大耗时(毫秒)',
       execution_count,
       plan_generation_num,
       total_physical_reads,
       last_physical_reads,
       min_physical_reads,
       max_physical_reads,
       total_logical_writes,
       last_logical_writes,
       min_logical_writes,
       max_logical_writes
FROM sys.dm_exec_query_stats AS s1
    CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2
WHERE s2.objectid IS NULL
ORDER BY last_worker_time DESC,
         s1.sql_handle,
         s1.statement_start_offset,
         s1.statement_end_offset;

SELECT
	t.NAME AS 表名,
	s.Name AS 架构,
	p.rows AS 记录数,
	SUM ( a.total_pages ) * 8 AS 总工占用空间KB,
	CAST ( ROUND( ( ( SUM ( a.total_pages ) * 8 ) / 1024.00 ), 2 ) AS NUMERIC ( 36, 2 ) ) AS 总共占用空间MB,
	SUM ( a.used_pages ) * 8 AS 已使用空间KB,
	CAST ( ROUND( ( ( SUM ( a.used_pages ) * 8 ) / 1024.00 ), 2 ) AS NUMERIC ( 36, 2 ) ) AS 已使用空间MB,
	( SUM ( a.total_pages ) - SUM ( a.used_pages ) ) * 8 AS 未使用空间KB,
	CAST (
		ROUND( ( ( SUM ( a.total_pages ) - SUM ( a.used_pages ) ) * 8 ) / 1024.00, 2 ) AS NUMERIC ( 36, 2 ) 
	) AS 未使用空间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 > 0 
GROUP BY	t.Name,s.Name,p.Rows 
ORDER BY 总共占用空间MB DESC;

SELECT TOP 10 
    r.session_id,
    r.request_id,
    r.start_time AS '开始时间',
    r.status AS '状态',
    r.command AS '命令',
    t.text AS 'sql语句',
    DB_NAME(r.database_id) AS '数据库名',
    r.blocking_session_id AS '正在阻塞其他会话的会话ID',
    w.wait_type AS '等待资源类型',
    r.wait_time AS '等待时间',
    r.wait_resource AS '等待的资源',
    w.waiting_tasks_count AS '当前正在进行等待的任务数',
    r.reads AS '物理读次数',
    r.writes AS '写次数',
    r.logical_reads AS '逻辑读次数',
    r.row_count AS '返回结果行数'
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
LEFT JOIN sys.dm_os_wait_stats AS w ON r.wait_type = w.wait_type
WHERE r.session_id > 50 -- 排除系统进程
ORDER BY r.cpu_time DESC;

-- 第一步查询被锁表
select request_session_id   spid,OBJECT_NAME(resource_associated_entity_id) tableName   
from   sys.dm_tran_locks where resource_type='OBJECT';
--参数说明 spid   锁表进程 ；tableName   被锁表名
-- 第二步解锁语句 需要拿到spid然后杀掉缩表进程
declare @spid  int 
Set @spid  = 57 --锁表进程
declare @sql varchar(1000)
set @sql='kill '+cast(@spid  as varchar)
exec(@sql)

USE master
ALTER DATABASE DB SET RECOVERY SIMPLE WITH NO_WAIT
ALTER DATABASE DB SET RECOVERY SIMPLE --调整为简单模式
USE DB
DBCC SHRINKFILE (N'DB_log' , 2, TRUNCATEONLY) --设置压缩后的日志大小为2M，可以自行指定
USE master
ALTER DATABASE DB SET RECOVERY FULL WITH NO_WAIT
ALTER DATABASE DB SET RECOVERY FULL --还原为完全模式