SQL Server 运维常用sql语句（二）

查看当前用户查看当前用户

1. select system_user
   

检查SQL Agent是否开启

1. IF EXISTS (
   
2. SELECT TOP 1 1
   
3. FROM sys.sysprocesses
   
4. WHERE program_name = 'SQLAgent - Generic Refresher'
   
5. )
   
6. SELECT 'Running'
   
7. ELSE
   
8. SELECT 'Not Running'
   

查看是否做了镜像

1. select
   
2.    a.database_id
   
3.    ,a.name 数据库名称
   
4.    ,case when b.mirroring_guid is null then '否' else '是' end 是否镜像
   
5.    ,b.mirroring_partner_name 镜像服务器名称
   
6. from
   
7. [sys].[databases] a
   
8. left join [sys].[database_mirroring] b on a.database_id=b.database_id
   

分离数据库

1. USE master;
   
2. EXEC sp_detach_db @dbname = 'test';#test指需要分离的数据库
   

附加数据库

1. SELECT type_desc, name, physical_name from sys.database_files;#查看物理数据库文件的位置
   
2. #使用带 FOR ATTACH 子句的 CREATE DATABASE 语句附加之前分离的test数据库
   
3. CREATE DATABASE test  
   
4.    ON (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\test.mdf'),  
   
5.    (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\test_log.ldf')  
   
6.    FOR ATTACH;
   

查看主从复制关系

1. SELECT * FROM msdb.dbo.sysjobs；
   
2. SELECT
   
3.    serverproperty('servername') AS ServerName,
   
4.    CASE
   
5.        WHEN serverproperty('servername') = '主服务器名称' THEN '主服务器'
   
6.        ELSE '从服务器'
   
7.    END AS ServerRole
   
8. #也可以通过以下语句来查询
   
9.    SELECT * FROM sys.objects WHERE name = 'MSreplication_options'
   

查看实例级别的信息

1. select SERVERPROPERTY ('test')
   

查看实例级别的某个参数allow updates的配置

1. select * from sys.configurations where name='allow updates'
   

查询当前数据库的所有架构范围的对象

1. select * from sys.all_objects
   

查询当前数据库的所有对象

1. select * from sys.sysobjects
   

在当前数据库下可以查询到所有数据库信息，包含是否on状态

1. select * from sys.databases
   

查询所有数据库信息

1. select * from sys.sysdatabases
   

查询当前数据库下所有正在SQL Server 实例上运行的进程的相关信息

1. select * from sys.sysprocesses
   

监控日志空间

1. DBCC SQLPERF (LOGSPACE)
   

查看数据库各种设置

1. select name,State,user_access,is_read_only,recovery_model from sys.databases
   

查询当前数据库是否有会话

1. select DB_NAME(dbid),* from sys.sysprocesses where dbid=db_id('test')
   

查询当前阻塞的所有请求

1. SELECT session_Id,spid,ecid,DB_NAME (sp.dbid),nt_username,er.status,wait_type,
   
2. [Individual Query] =SUBSTRING (qt.text,er.statement_start_offset / 2,
   
3. ( CASE
   
4. WHEN er.statement_end_offset = -1
   
5. THEN
   
6. LEN (CONVERT (NVARCHAR (MAX), qt.text)) * 2
   
7. ELSE
   
8. er.statement_end_offset
   
9. END
   
10. - er.statement_start_offset)
    
11. / 2),
    
12. qt.text,program_name,Hostname,nt_domain,start_time
    
13. FROM sys.dm_exec_requests er
    
14. INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
    
15. CROSS APPLY sys.dm_exec_sql_text (er.sql_handle) AS qt
    
16. WHERE session_Id > 50 /* Ignore system spids.*/
    
17. AND sp.blocked>0 AND session_Id NOT IN (@@SPID)
    

查看活动线程执行的sql语句,并生成批量杀掉的语句

1. select 'KILL '+CAST(a.spid AS NVARCHAR(100)) AS KillCmd,REPLACE(hostname,' ','') as hostname ,replace(program_name,' ','') as program_name
   
2. ,REPLACE(loginame, ' ', '') AS loginame, db_name(a.dbid) AS DBname,spid,blocked,waittime/1000 as waittime
   
3. ,a.status,Replace(b.text,'''','''') as sqlmessage,cpu
   
4. from sys.sysprocesses as a with(nolock)
   
5. cross apply sys.dm_exec_sql_text(sql_handle) as b
   
6. where a.status<>'sleeping' AND a.spid<>@@SPID
   

查看数据库的最近备份信息

1. SELECT database_name,type,MAX(backup_finish_date) AS backup_finish_date FROM msdb.dbo.backupset GROUP BY database_name,type ORDER BY database_name,type
   
2. 备注：D 表示全备份，i 表示差异备份，L 表示日志备份
   

查看备份进度

1. SELECT DB_NAME(database_id) AS Exec_DB
   
2. ,percent_complete
   
3. ,CASE WHEN estimated_completion_time < 36000000
   
4. THEN '0' ELSE '' END + RTRIM(estimated_completion_time/1000/3600)
   
5. + ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%3600/60), 2)
   
6. + ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%60), 2) AS [Time Remaining]
   
7. ,b.text as tsql
   
8. ,*
   
9. FROM SYS.DM_EXEC_REQUESTS
   
10. cross apply sys.dm_exec_sql_text(sql_handle) as b
    
11. WHERE command LIKE 'Backup%' --and database_id=db_id('cardorder')
    
12. --OR command LIKE 'RESTORE%'
    
13. ORDER BY 2 DESC
    

查询always on状态是否正常

1. select dc.database_name, d.synchronization_health_desc, d.synchronization_state_desc, d.database_state_desc from sys.dm_hadr_database_replica_states d join sys.availability_databases_cluster dc on d.group_database_id=dc.group_database_id and d.is_local=1
   

查看mirror镜像信息

1. SELECT
   
2. db_name(database_id),
   
3. mirroring_state_desc,
   
4. mirroring_role_desc,
   
5. mirroring_partner_name,
   
6. mirroring_partner_instance
   
7. FROM sys.database_mirroring
   

查看每个数据库实例的数据量大小

1. SELECT
   
2. DB_NAME(db.database_id) DatabaseName,
   
3. (CAST(mfrows.RowSize AS FLOAT)*8)/1024 RowSizeMB,
   
4. (CAST(mflog.LogSize AS FLOAT)*8)/1024 LogSizeMB,
   
5. (CAST(mfstream.StreamSize AS FLOAT)*8)/1024 StreamSizeMB,
   
6. (CAST(mftext.TextIndexSize AS FLOAT)*8)/1024 TextIndexSizeMB
   
7. FROM sys.databases db
   
8. LEFT JOIN (SELECT database_id, SUM(size) RowSize FROM sys.master_files WHERE type = 0 GROUP BY database_id, type) mfrows ON mfrows.database_id = db.database_id
   
9. LEFT JOIN (SELECT database_id, SUM(size) LogSize FROM sys.master_files WHERE type = 1 GROUP BY database_id, type) mflog ON mflog.database_id = db.database_id
   
10. LEFT JOIN (SELECT database_id, SUM(size) StreamSize FROM sys.master_files WHERE type = 2 GROUP BY database_id, type) mfstream ON mfstream.database_id = db.database_id
    
11. LEFT JOIN (SELECT database_id, SUM(size) TextIndexSize FROM sys.master_files WHERE type = 4 GROUP BY database_id, type) mftext ON mftext.database_id = db.database_id
    

查询总耗CPU最多的前3个SQL，且最近5天出现过

1. SELECT TOP 3
   
2. total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数],
   
3. qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 时间(ms)],
   
4. last_execution_time AS [最后一次执行时间],max_worker_time /1000 AS [最大执行时间(ms)],
   
5. SUBSTRING(qt.text,qs.statement_start_offset/2+1,
   
6. (CASE WHEN qs.statement_end_offset = -1
   
7. THEN DATALENGTH(qt.text)
   
8. ELSE qs.statement_end_offset END -qs.statement_start_offset)/2 + 1)
   
9. AS [使用CPU的语法], qt.text [完整语法],
   
10. qt.dbid, dbname=db_name(qt.dbid),
    
11. qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName
    
12. FROM sys.dm_exec_query_stats qs WITH(nolock)
    
13. CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qt
    
14. WHERE execution_count>1 and last_execution_time>dateadd(dd,-5,getdate())
    
15. ORDER BY total_worker_time DESC
    

查看当前最耗资源的10个SQL及其spid

1. SELECT TOP 10
   
2. session_id,request_id,start_time AS '开始时间',status AS '状态',
   
3. command AS '命令',d_sql.text AS 'sql语句', DB_NAME(database_id) AS '数据库名',
   
4. blocking_session_id AS '正在阻塞其他会话的会话ID',
   
5. wait_type AS '等待资源类型',wait_time AS '等待时间',wait_resource AS '等待的资源',
   
6. reads AS '物理读次数',writes AS '写次数',logical_reads AS '逻辑读次数',
   
7. row_count AS '返回结果行数'
   
8. FROM sys.dm_exec_requests AS d_request
   
9. CROSS APPLY
   
10. sys.dm_exec_sql_text(d_request.sql_handle) AS d_sql
    
11. WHERE session_id>50
    
12. ORDER BY cpu_time DESC
    
13. --前50号session_id一般是系统后台进程，sys.dm_exec_requests的status显示为background
    

always on

查看集群各节点的信息，包含节点成员的名称，类型，状态，拥有的投票仲裁数

1. SELECT * FROM  sys.dm_hadr_cluster_members;
   

查看集群各节点的信息，包含节点成员的名称，节点成员上的sql实例名称

1. select * from sys.dm_hadr_instance_node_map
   

查看WSFC(windows server故障转移群集)的信息，包含集群名称，仲裁类型，仲裁状态

1. SELECT * FROM SYS.dm_hadr_cluster;
   

查看AG名称

1. select * from sys.dm_hadr_name_id_map
   
2. 查看集群各节点的子网信息，包含节点成员的名称，子网段，子网掩码
   
3. SELECT * FROM  sys.dm_hadr_cluster_networks;
   

查看侦听ip

1. select * from sys.availability_group_listeners;
   

查看主从各节点的状态

复制

1. select d.is_local,dc.database_name, d.synchronization_health_desc,
   
2. d.synchronization_state_desc, d.database_state_desc
   
3. from sys.dm_hadr_database_replica_states d
   
4. join sys.availability_databases_cluster dc
   
5. on d.group_database_id=dc.group_database_id;
   

查看辅助副本(传说中的从库)延迟多少M日志量

1. select db_name(database_id),log_send_queue_size/1024 delay_M,*
   
2. from sys.dm_hadr_database_replica_states where is_primary_replica=0;
   

查看DDL操作的记录

1. select * from Sys.traces