sql server语句

1.sqlserver查询锁语句 ：

IF EXISTS(SELECT * FROM master.sys.sysprocesses WHERE spid
IN (SELECT blocked FROM master.sys.sysprocesses))
–确定有进程被其他的进程锁住
SELECT
DISTINCT ‘进程ID’ = STR(a.spid, 4)
,‘进程ID状态’ = CONVERT(CHAR(10), a.status)
,‘登入帐号’=SUBSTRING(SUSER_SNAME(sid),1,30)
,‘工作站名称’ = CONVERT(CHAR(10), a.hostname)
,‘执行命令的用户’ = CONVERT(CHAR(10), SUSER_NAME(a.uid))
,‘是否被锁住’=CONVERT(char(3),blocked)
,‘数据库名’ = CONVERT(CHAR(10), DB_NAME(a.dbid))
,‘正在执行的命令’ = CONVERT(CHAR(16), a.cmd)
,‘登录名’ = a.loginame
,‘执行语句’ = b.text
,‘等待型态’ = a.waittype
FROM master…sysprocesses a CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) b
–列出锁住别人(在别的进程中 blocked字段出现的值)，但自己未被锁住(blocked=0)
WHERE spid IN (SELECT blocked FROM master.sys.sysprocesses)
AND blocked=0
ELSE
SELECT ‘No Blocked Session(s)’
–a.status = suspended,a.blocked(阻塞者id)
–DBCC INPUTBUFFER (阻塞者id);
–就可以看到语句了或者join

2.查询一天内慢查询语句：

SELECT TOP 10 OBJECT_NAME(qt.objectid, qt.dbId) AS procName,
DB_NAME(qt.dbId) AS [db_name],
qt.text AS SQL_Full,
SUBSTRING(
qt.text,
(qs.statement_start_offset / 2) + 1,
(
(
CASE statement_end_offset
WHEN -1 THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset
END
- qs.statement_start_offset
) / 2
) + 1
) AS SQL_Part --统计对应的部分语句
,
qs.creation_time,
qs.last_execution_time,
qs.execution_count,
qs.last_elapsed_time / 1000000 AS lastElapsedSeconds,
qs.last_worker_time / 1000000 AS lastCpuSeconds,
CAST(
qs.total_elapsed_time / 1000000.0 / (
CASE
WHEN qs.execution_count = 0 THEN -1
ELSE qs.execution_count
END
) AS DECIMAL(28, 2)
) AS avgDurationSeconds,
CAST(qs.last_logical_reads AS BIGINT) * 1.0 / (1024 * 1024) * 8060 AS
lastLogicReadsMB,
qs.last_logical_reads,
qs.plan_handle
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS p
WHERE qs.last_execution_time >= CONVERT(CHAR(10),GETDATE(),120)+’ 08:00’ --今天1点之后的慢SQL
AND qs.last_elapsed_time >= 10 * 1000 * 1000 --只取执行时间大于 10 秒的记录
AND qt.[text] NOT LIKE ‘%Proc_DBA%’
ORDER BY
qs.last_worker_time DESC

3.查询备份进度：

SELECT DB_NAME(er.[database_id]) [DatabaseName],
er.[command] AS [CommandType],
er.[percent_complete], er.start_time,
CONVERT(DECIMAL(5, 2), er.[percent_complete]) AS [Complete_Percent]
,CONVERT(DECIMAL(38, 2), er.[total_elapsed_time] / 60000.00) AS [ElapsedTime_m]
,CONVERT(DECIMAL(38, 2), er.[estimated_completion_time] / 60000.00) AS [EstimatedCompletionTime_m]
FROM sys.dm_exec_requests AS er
WHERE --DB_NAME(er.[database_id]) in (‘xxxx’) and
er.[command] in ( ‘RESTORE DATABASE’ ,‘BACKUP DATABASE’)
order by er.start_time desc

4.sqlserver2016取用户登录名sid（用户辅助节点）
select * from sys.sql_logins查询出来的sid而并不是
sp_helplogins查找的sid

5.数据库处于in recovery处理方法：
SELECT databasepropertyex(‘DATABASENAME’, ‘STATUS’)
RESTORE DATABASE DATABASENAME WITH RECOVERY;

alter database [DATABASENAME] set online
ALTER DATABASE DATABASENAME SET SINGLE_USER WITH NO_WAIT;
ALTER DATABASE DATABASENAME SET EMERGENCY;
DBCC checkdb (DATABASENAME, REPAIR_ALLOW_DATA_LOSS);
ALTER DATABASE DATABASENAME SET online;
ALTER DATABASE DATABASENAME SET Multi_USER WITH NO_WAIT;