SQLServer常⽤SQL语句（收藏版）
1. sqlserver查看实例级别的信息，使⽤SERVERPROPERTY函数
select SERVERPROPERTY ('propertyname')
2. 查看实例级别的某个参数XX的配置
select * figurations where name='XX'
3. 更改实例级别的某个参数XX的值
sp_configure 'XX','0'
RECONFIGURE WITH OVERRIDE
sp_configure显⽰或更改当前服务器的全局配置设置。
RECONFIGURE表⽰SQL Server不⽤重新启动就⽴即⽣效 。
使⽤sp_configure更改设置时，请使⽤RECONFIGURE语句使更改⽴即⽣效，否则更改将在SQL Server重新启动后⽣效。
RECONFIGURE后⾯加WITH OVERRIDE表⽰不管这个值是不是符合要求都会⽣效，⽐如recovery interval的范围值是10--60对应
效，则必须加上WITH OVERRIDE。
4. sqlserver没有系统表可以查询所有数据库下⾯对象，以下只能在当前数据库下⾯查
select * from sys.all_objects --查询当前数据库的所有架构范围的对象
select * from sys.sysobjects --查询当前数据库的所有对象
--sys.all_objects、sys.sysobjects 这种的视图，在每个数据库的系统视图下⾯都有
select * from sys.databases --在当前数据库下可以查询到所有数据库信息，包含是否on状态
select * from sys.sysdatabases --在当前数据库下可以查询到所有数据库信息，不包含是否on状态，
这个系统视图会在后续的版本中删除
--sys.databases、sys.sysdatabases这种的视图，在每个数据库的系统视图下⾯都有
sys.processes --没有这个视图
select * from sys.sysprocesses --在当前数据库下可以查询所有正在SQL Server 实例上运⾏的进程的相关信息，也就是所有数据库上的线程，这个系统视图会在后续的5. 全局系统视图、单个数据库系统视图
sys.database_files --每个存储在数据库本⾝中的数据库⽂件在表中占⽤⼀⾏。这是⼀个基于每个数据库的视图。
sys.master_files --master 数据库中的每个⽂件对应⼀⾏。这是⼀个系统范围视图。
--sys.database_files、sys.master_files这种的视图，在每个数据库的系统视图下⾯都有
6. ⼀些只存在msdb的系统表，⽽⾮系统视图
dbo.backupset
dbo.log_shipping_secondary
dbo.sysjobs
dbo.sysjobhistory
--这些系统表只存在msdb数据库，使⽤的时候必须加上msdb前缀
7. sp_lock、sp_who、sp_who2、sp_helptext等⼀些系统存储过程存在于每个数据库中
8. 报告有关锁的信息，会显⽰实例⾥⾯的所有数据库的锁信息、堵塞信息
sp_lock
9. 提供有关当前⽤户、 会话和进程的实例中的信息，可以看到会话的状态running、SUSPENDED、sleeping、rollback，sp_who2通过CPUTime、DiskIO可以判断对应的transaction是否很⼤
sp_who
sp_who2
sp_who2 active (可选参数LoginName, 或active代表活动会话数)
CPUTime (进程占⽤的总CPU时间)
DiskIO (进程对磁盘读的总次数)
LastBatch (客户最后⼀次调⽤存储过程或者执⾏查询的时间)
ProgramName (⽤来初始化连接的应⽤程序名称，或者主机名)
10. 查看某个存储过程的内容
sp_helptext pro_name
11.显⽰某个线程号发送到sqlserver数据库的最后⼀个语句
DBCC INPUTBUFFER
12.假设查询到249被锁给堵塞了，查询被堵塞的SQL语句
DBCC INPUTBUFFER (249)
13. 查看某个数据库中是否存在活动事务，有活动事务就⼀定会写⽇志
DBCC OPENTRAN (dbname)
14. 监视⽇志空间
DBCC SQLPERF (LOGSPACE)
15. 查⽆法重⽤⽇志中的空间的原因(⽇志⽆法截断导致⽇志⽂件越来越⼤，但是可⽤空间很⼩，⽆法收缩)
select name,log_reuse_wait_desc from sys.databases
16. 查看虚拟⽇志⽂件信息
DBCC LOGINFO
结果有多少⾏，代表有多少虚拟⽇志⽂件，活动的虚拟⽇志⽂件的状态(status)为2
17. 修复msdb数据库，⽐如ssms页⾯sql server agent丢失或看不了job view history等功能，说明msdb坏了，需要修复
dbcc checkdb (msdb);
18. 在您当前连接到的 SQL Server 数据库中⽣成⼀个⼿动检查点
CHECKPOINT [ checkpoint_duration ]
--checkpoint_duration表⽰以秒为单位指定⼿动检查点完成所需的时间，⼀般不使⽤这个参数，让数据库⾃⼰控制19. 查看数据库各种设置
select name,State,user_access,is_read_only,recovery_model from sys.databases
20. 查看某个数据库中是否存在会话
select DB_NAME(dbid),* from sys.sysprocesses where dbid=db_id('dbname')
21. 查询当前阻塞的所有请求
select * from sys.sysprocesses where blocked>0
或
source_type,db_source_database_id),t1.resource_associated_entity_quest_mode, t1.request_session_id,t2.blocking_session_id,t2.wait_duration_ms
FROM sys.dm_tran_locks as t1
INNER JOIN sys.dm_os_waiting_tasks as t2
ON t1.lock_owner_address = t2.resource_address;
或
select A.SPID as 被阻塞进程,a.CMD AS 正在执⾏的操作,b.spid AS 阻塞进程号,b.cmd AS 阻塞进程正在执⾏的操作from master..sysprocesses a,master..sysprocesses b
where a.blocked<>0 and a.blocked= b.spid
或
SELECT session_Id,spid,ecid,DB_NAME (sp.dbid),nt_username,er.status,wait_type,
[Individual Query] =SUBSTRING (qt.text,er.statement_start_offset / 2,
( CASE
WHEN er.statement_end_offset = -1
THEN
LEN (CONVERT (NVARCHAR (MAX), qt.text)) * 2
ELSE
er.statement_end_offset
END
- er.statement_start_offset)
/ 2),
<,program_name,Hostname,nt_domain,start_time
FROM sys.dm_exec_requests er
INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
CROSS APPLY sys.dm_exec_sql_text (er.sql_handle) AS qt
WHERE session_Id > 50 /* Ignore system spids.*/
AND sp.blocked>0 AND session_Id NOT IN (@@SPID)
或
SELECT session_id ,status ,blocking_session_id
,wait_type ,wait_time ,wait_resource
,transaction_id
FROM sys.dm_exec_requests
WHERE status = N'suspended';
-
-sys.dm_exec_requests返回SQL Server 中正在执⾏的每个请求的信息
22. 查看哪些表被锁了，以及这些表被哪个进程锁了
select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableName
from sys.dm_tran_locks where resource_type='OBJECT' ORDER BY request_session_id ASC
23. 查询某个job是否被堵塞
常用的sql查询语句有哪些select * from msdb.dbo.sysjobs where name='jobname'
select a.program_name,a.* from master..sysprocesses a where a.program_name like '%0D1CE57E8AC5%'
--把第⼀个语句查询到的job_id代⼊第⼆个语句的program_name
24. 检查SQL Agent是否开启
IF EXISTS (
SELECT TOP 1 1
FROM sys.sysprocesses
WHERE program_name = 'SQLAgent - Generic Refresher'
)
SELECT 'Running'
ELSE
SELECT 'Not Running'
25. 查看活动线程执⾏的sql语句，并⽣成批量杀掉的语句
select 'KILL '+CAST(a.spid AS NVARCHAR(100)) AS KillCmd,REPLACE(hostname,' ','') as hostname ,replace(program_name,' ','') as program_name ,REPLACE(loginame, ' ', '') AS loginame, db_name(a.dbid) AS DBname,spid,blocked,waittime/1000 as waittime
,a.status,,'''','''') as sqlmessage,cpu
from sys.sysprocesses as a with(nolock)
cross apply sys.dm_exec_sql_text(sql_handle) as b
where a.status<>'sleeping' AND a.spid<>@@SPID
26. 查看备份进度
SELECT DB_NAME(database_id) AS Exec_DB
,percent_complete
,CASE WHEN estimated_completion_time < 36000000
THEN '0' ELSE '' END + RTRIM(estimated_completion_time/1000/3600)
+ ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%3600/60), 2)
+ ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%60), 2) AS [Time Remaining]
,b.text as tsql
,*
FROM SYS.DM_EXEC_REQUESTS
cross apply sys.dm_exec_sql_text(sql_handle) as b
WHERE command LIKE 'Backup%' --and database_id=db_id('cardorder')
--OR command LIKE 'RESTORE%'
ORDER BY 2 DESC
27. 查看恢复进度
SELECT DB_NAME(database_id) AS Exec_DB
,percent_complete
,CASE WHEN estimated_completion_time < 36000000
THEN '0' ELSE '' END + RTRIM(estimated_completion_time/1000/3600)
+ ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%3600/60), 2)
+ ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%60), 2) AS [Time Remaining]
,b.text as tsql
,*
FROM SYS.DM_EXEC_REQUESTS
cross apply sys.dm_exec_sql_text(sql_handle) as b
WHERE command LIKE 'RESTORE%' --and database_id=db_id('cardorder')
--OR command LIKE 'RESTORE%'
ORDER BY 2 DESC
28. 查看数据库的最近备份信息
SELECT database_name,type,MAX(backup_finish_date) AS backup_finish_date FROM msdb.dbo.backupset GROUP BY database_name,type ORDER B 备注：D 表⽰全备份，i 表⽰差异备份，L 表⽰⽇志备份
29. 查看数据库的历史备份记录，并⽣成restore语句
SELECT
CONVERT(CHAR(100),SERVERPROPERTY('Servername'))AS Server,
bs.database_name,
bs.backup_start_date,
bs.backup_finish_date,
pe
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
END AS backup_type,
bs.backup_size,
bmf.logical_device_name,
bmf.physical_device_name,
bs.name AS backupset_name,
bs.description,
'RESTORE DATABASE ['+bs.database_name+'] FROM DISK=N'''
+bmf.physical_device_name+ '''WITH NORECOVERY;'
FROM msdb.dbo.backupmediafamily bmf
INNER JOIN msdb.dbo.backupset bs
dia_set_dia_set_id
WHERE bs.backup_start_date>DATEADD(DAY,-1,GETDATE())
ORDER BY bs.backup_finish_date
30. 查询XX库从YYYY-MM-DD⽇期开始的⽇志备份记录，并⽣成restore log的语句
SELECT TOP 1000
S.database_name [Database],
CASE [S].[type]
WHEN 'L'
THEN N'RESTORE LOG ' + QUOTENAME(S.database_name) + N' FROM DISK = ''' + F.physical_device_name + N''' WITH NORECOVERY;'
END [LogRestore],
F.physical_device_name,
S.[Type],
S.backup_start_date,
S.backup_finish_date
FROM msdb.dbo.backupmediafamily F
INNER JOIN msdb.dbo.backupset S
dia_set_id = F.media_set_id
WHERE S.database_name = 'XX' AND
31. 查询always on状态是否正常
select dc.database_name, d.synchronization_health_desc, d.synchronization_state_desc, d.database_state_desc from sys.dm_hadr_database_replica_st 32. 查看mirror镜像信息

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。