SqlServer数据库常用的SQL总结

--查询恢复、备份、和收缩数据库的进度
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 er.[command] in ( 'RESTORE DATABASE' ,'BACKUP DATABASE','DbccFilesCompact')
order by er.start_time desc

--查询跟踪计划
SELECT id,status,path,max_size,max_files,start_time,last_event_time,event_count FROM sys.traces

--查询所有表的记录数
SELECT a.name,
       b.rows
FROM sys.sysobjects AS a
    INNER JOIN sys.sysindexes AS b
        ON a.id = b.id
WHERE (b.indid IN ( 0, 1 ))
      AND (a.type = 'u')
ORDER BY rows DESC;

--跟踪阻塞SQL，可定时执行将整个阻塞过程记录用于分析阻塞链
CREATE proc [dbo].[sp_TraceBlockSQL]
as
begin
set transaction isolation level read uncommitted
DECLARE @gid varchar(36)
SET @gid = REPLACE(CAST(NEWID() AS NVARCHAR(36)),'-','')
;with process as(
SELECT  top(100) w.[session_id]  AS 'Spid' ,
w.[blocking_session_id] 'Blocked_by_spid',
w.[wait_duration_ms]  'Wait_time_ms',
w.[wait_type] 'Wait_type' ,
w.resource_description as 'Wait_resource',
s.[original_login_name] 'Login_id',
 SUBSTRING(q.text,(sp.stmt_start/2)+1,((case sp.stmt_end when -1 then DATALENGTH(q.text) else sp.stmt_end end - sp.stmt_start)/2) + 1) AS 'Query_text',
s.last_request_start_time 'Start_time',
s.[program_name]  'Client_app_name',
s.[host_name] 'Host_name',
r.[cpu_time] 'CPU' ,
r.[reads] +  r.[writes] as [Physical_IO],
db_name(q.[dbid]) as DBName,
r.[status] as [Status],
r.[command] as [CMD]
FROM     [sys].[dm_os_waiting_tasks] w
        INNER JOIN [sys].[dm_exec_sessions] s ON w.[session_id] = s.[session_id]
INNER JOIN [sys].[sysprocesses]  sp on s.[session_id] = sp.[spid]
        INNER JOIN [sys].[dm_exec_requests] r ON s.[session_id] = r.[session_id]
        CROSS APPLY [sys].[dm_exec_sql_text](r.[plan_handle]) q
                    
WHERE    w.[session_id] > 50 and w.[blocking_session_id] <>0 and sp.status <>N'sleeping'

union all
SELECT top (100) p2.spid AS 'Spid'
    , p2.blocked AS 'Blocked_by_spid'
,convert(bigint,DATEDIFF(MS,p2.last_batch,getdate())) 'Wait_time_ms' 
,p2.lastwaittype 'Wait_type'
,p2.waitresource 'Wait_resource'
    , p2.[loginame] AS 'Login_id',
   SUBSTRING(st.text,(p2.stmt_start/2)+1,((case p2.stmt_end when -1 then DATALENGTH(st.text) else p2.stmt_end end - p2.stmt_start)/2) + 1) AS 'Query_text'
,p2.last_batch 'Start_time'
,p2.program_name 'Client_app_name'
,p2.hostname 'Host_name'
    , p2.[CPU]
    , p2.[Physical_IO]
    , DB_NAME(p2.[dbid]) AS DBName
    , p2.[Status]
    , p2.[CMD]
  FROM sys.sysprocesses p2
INNER JOIN sys.sysprocesses p1 ON p2.spid = p1.blocked
CROSS APPLY sys.dm_exec_sql_text(p2.sql_handle) st
WHERE p2.blocked = 0 and (p1.status <>N'sleeping' and p2.status <>N'sleeping') or (p1.status =N'sleeping' and p1.open_tran>0) or (p2.status =N'sleeping' and p2.open_tran>0)
)
insert Base_BlockSQL
select distinct process.Spid,process.[Blocked_by_spid],process.Wait_time_ms as Wait_time_ms ,process.Wait_type,RTRIM(process.Wait_resource) as [Wait_resource],RTRIM(process.Login_id) as [Login_id],process.Query_text,process.Start_time,RTRIM(process.Client_app_name) as [Client_app_name],
RTRIM(process.Host_name) as [Host_name],RTRIM(process.cpu) as cpu
,process.[Physical_IO],process.DBName,
RTRIM(process.[Status]) as [Status],
RTRIM(process.[CMD]) as [CMD],
case when re.query_hash is not null then convert(nvarchar(100),re.query_hash,2) else substring(convert(nvarchar(100),HASHBYTES('MD5', process.Query_text),2),0,17) end as Query_hash , @gid as [uniqueid],getdate() as 'Collect_time'
from process left join sys.dm_exec_requests re on re.session_id=process.Spid      
where spid<>[Blocked_by_spid]
end

--阻塞进程及SQL
SELECT blocking_session_id '阻塞进程的ID', wait_duration_ms '等待时间(毫秒)',
(select CAST(csql.text AS varchar(255)) AS CallingSQL
from master.sys.dm_exec_requests er
WITH (NOLOCK)
       CROSS APPLY MASTER.sys.fn_get_sql (er.sql_handle) csql
where er.session_id =a.blocking_session_id) AS '阻塞SQL'
FROM sys.dm_os_waiting_tasks a
WHERE blocking_session_id IS NOT NULL
ORDER BY wait_duration_ms desc

--收缩数据库日志（此收缩执行前建议先备份数据库数据和日志）

--1、先查询数据库日志状态，如果为NOTHING代表可进行收缩操作，如果为LOG_BACKUP代表需要先执行日志备份操作
SELECT name,log_reuse_wait_desc FROM sys.databases where name='MyDB'

--2、如果为LOG_BACKUP时执行日志备份操作,备份数据库日志文件到NULL，不占用任何空间
BACKUP LOG MyDB TO DISK='NUL:' WITH NO_CHECKSUM, CONTINUE_AFTER_ERROR;

--3、如果为NOTHING代表可以收缩，此时将数据库日志文件收缩到20G
USE MyDB
DBCC SHRINKFILE ('FG_log_1', 20480);

--4、最后检查日志空间
dbcc sqlperf(logspace)

--批量生成收缩数据库日志文件SQL
DECLARE @targetsize INT=1 --收缩到指定大小单位为G，默认为1G
DECLARE @dbcount INT=0,@index INT=1,@sql NVARCHAR(MAX)='',@size INT=@targetsize*1024
DECLARE @dbname NVARCHAR(50)='',@log_reuse_wait_desc VARCHAR(50)=''
SELECT ROW_NUMBER() OVER(ORDER BY name) AS id,name,log_reuse_wait_desc,CAST('' AS NVARCHAR(MAX)) AS shrinksql INTO #dbinfo FROM sys.databases where name IN(
SELECT name FROM Master..SysDatabases 
WHERE dbid>4
)
ORDER BY name
SELECT @DBCount=COUNT(0) FROM #dbinfo
CREATE TABLE #dbfiles
(
	id INT IDENTITY(1,1) PRIMARY KEY,
	name VARCHAR(100)
)
WHILE @index<=@dbcount
BEGIN
	SET @sql=''
	TRUNCATE TABLE #dbfiles
	SELECT @dbname=name,@log_reuse_wait_desc=log_reuse_wait_desc FROM #dbinfo WHERE id=@index
	EXEC('INSERT INTO #dbfiles(name) SELECT name from  '+@dbname+'..sysfiles WHERE charindex(''LDF'',filename)>0')
	DECLARE @logcount INT=0,@current INT=1,@logname VARCHAR(100)='',@str VARCHAR(500)=''
	SELECT @logcount=COUNT(0) FROM #dbfiles
	WHILE @current<=@logcount
	BEGIN
		SELECT @logname=name FROM #dbfiles WHERE id=@current
		SET @str=@str+'DBCC SHRINKFILE ('''+@logname+''', '+CONVERT(VARCHAR(20),@size)+');'+CHAR(13)
		SET @current=@current+1
    END
	SET @sql=@sql+N'--数据库:'+@dbname+',log_reuse_wait_desc:'+@log_reuse_wait_desc+CHAR(13)
	SET @sql=@sql+'USE '+@dbname+CHAR(13)
	SET @sql=@sql+'GO'+CHAR(13)
	SET @sql=@sql+'BACKUP LOG '+@dbname+' TO DISK=''NUL:'' WITH NO_CHECKSUM, CONTINUE_AFTER_ERROR;'+CHAR(13)+@str+CHAR(13)
	--PRINT @sql
	UPDATE #dbinfo SET shrinksql=@sql WHERE id=@index
	SET @index=@index+1
END
SELECT * FROM #dbinfo
DROP TABLE #dbinfo
DROP TABLE #dbfiles

--大批量删除数据
SET ROWCOUNT 0;
DECLARE @BatchSize INT=4000,@RowCount INT=0,@CNT INT=0,@Time datetime,@StrTime varchar(50)=''
WHILE 1=1
BEGIN
	SET @RowCount=0
	DELETE TOP(@BatchSize) FROM [DB1].[dbo].[table1] WHERE ID IN(SELECT ID  FROM [DB1_History].[dbo].[table1])
	SET @RowCount=@@ROWCOUNT
	SET @CNT=@CNT+@RowCount
	SELECT @Time=GETDATE()
	SET @StrTime=CONVERT(CHAR(23), @Time, 21)
	RAISERROR (N'%s 共删除:%d',10,1,@StrTime,@CNT) WITH NOWAIT 
	IF @RowCount<@BatchSize OR @RowCount=0
	BEGIN
		BREAK;
	END
END
PRINT N'本次共删除:'+CONVERT(VARCHAR(50),@CNT)

--查看各表分区的记录数
SELECT a.partition_scheme,a.name,a.partition_number,a.filegroup,a.range_boundary,a.rows FROM (
SELECT convert(varchar(50), ps.name) as partition_scheme,
p.partition_number,
ds2.name as filegroup,tb.name,
convert(varchar(19), isnull(v.value, ''), 120) as range_boundary,
str(p.rows, 9) as rows
from sys.indexes i
join sys.partition_schemes ps on i.data_space_id = ps.data_space_id
join sys.destination_data_spaces dds
on ps.data_space_id = dds.partition_scheme_id
join sys.data_spaces ds2 on dds.data_space_id = ds2.data_space_id
join sys.partitions p on dds.destination_id = p.partition_number
and p.object_id = i.object_id and p.index_id = i.index_id
join sys.partition_functions pf on ps.function_id = pf.function_id
JOIN sys.objects tb ON tb.object_id=i.object_id
LEFT JOIN sys.Partition_Range_values v on pf.function_id = v.function_id 
AND v.boundary_id = p.partition_number - pf.boundary_value_on_right
WHERE i.index_id in (0, 1) AND ds2.name<>'PRIMARY' --AND i.object_id = object_id('MyTable')
) a
GROUP BY a.partition_scheme,a.name,a.partition_number,a.filegroup,a.range_boundary,a.rows

--添加链接服务器
EXEC sp_addlinkedserver
      @server='MyDB',--被访问的服务器别名
      @srvproduct='',--SqlServer默认不需要写
      @provider='SQLOLEDB', --不同的库都是不一样的
      @datasrc='127.0.0.1'   --要访问的服务器

EXEC sp_addlinkedsrvlogin
     'MyDB', --被访问的服务器别名
     'false',
      NULL,
     'sa', --帐号
     '123456' --密码
GO