SQL Server常用脚本
1、 sp_MS_marksystemobject 将存储过程或者对象标记为系统对象：
EXEC sp_MS_marksystemobject 'dbo.sp_spaceuseddba'; --注意需要“dbo.”关键字
常用的一些系统视图
sys.dm_exec_requests SQL Server 中执行的每个请求的信息
锁定对象 ，只允许一个会话调用
exec sp_getapplock @Resource = '存储过程的名字',@LockMode = 'Exclusive', @LockOwner ='session'
2、迁移登录用户脚本：
select 'create login [' + p.name + '] ' + case when p.type in('U','G') then 'from windows ' else '' end + 'with ' + case when p.type = 'S' then 'password = ' + master.sys.fn_varbintohexstr(l.password_hash) + ' hashed, ' + 'sid = ' + master.sys.fn_varbintohexstr(l.sid) + ', check_expiration = ' + case when l.is_expiration_checked > 0 then 'ON, ' else 'OFF, ' end + 'check_policy = ' + case when l.is_policy_checked > 0 then 'ON, ' else 'OFF, ' end + case when l.credential_id > 0 then 'credential = ' + c.name + ', ' else '' end else '' end + 'default_database = ' + p.default_database_name + case when len(p.default_language_name) > 0 then ', default_language = "' + p.default_language_name +'"' else '''' end from sys.server_principals p left join sys.sql_logins l on p.principal_id = l.principal_id left join sys.credentials c on l.credential_id = c.credential_id where p.type in('S','U','G') and p.name <> 'sa'
3、查看数据库阻塞
SELECT wt.blocking_session_id AS BlockingSessesionId ,sp.program_name AS ProgramName ,COALESCE(sp.LOGINAME, sp.nt_username) AS HostName ,ec1.client_net_address AS ClientIpAddress ,db.name AS DatabaseName ,wt.wait_type AS WaitType ,ec1.connect_time AS BlockingStartTime ,wt.WAIT_DURATION_MS/1000 AS WaitDuration ,ec1.session_id AS BlockedSessionId ,h1.TEXT AS BlockedSQLText ,h2.TEXT AS BlockingSQLText FROM sys.dm_tran_locks AS tl INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id LEFT OUTER JOIN master.dbo.sysprocesses sp ON SP.spid = wt.blocking_session_id CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1 CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
4、查看当前数据库脚本运行情况
SELECT creation_time N'语句编译时间' ,last_execution_time N'上次执行时间' ,execution_count N'执行次数' ,case datediff(ss,creation_time,last_execution_time) when 0 then 0 else execution_count/datediff(ss,creation_time,last_execution_time) end N'每秒执行次数' ,total_physical_reads N'物理读取总次数' ,total_logical_reads/execution_count N'每次逻辑读次数' ,total_logical_reads N'逻辑读取总次数' ,total_logical_writes N'逻辑写入总次数' , total_worker_time/1000 N'所用的CPU总时间ms' , total_elapsed_time/1000 N'总花费时间ms' , (total_elapsed_time / execution_count)/1000 N'平均时间ms' ,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) N'执行语句'
,db_name(st.dbid) as dbname,st.objectid FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st where SUBSTRING(st.text, (qs.statement_start_offset/2) + 1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) not like '%fetch%' ORDER BY execution_count DESC;
5、处理Identity列的一些方法
DBCC CHECKIDENT (xxxxxx, NORESEED) 报告当前表的标识列
DBCC CHECKIDENT (xxxxxx, RESEED, 30) 强制将标识设置成30(如果有主键约束，后续插入可能会失败)。
在标识列插入数据(字段名称要写全)
set identity_insert xxxx on
insert into xxxx (id,a,b,c)
select id,a,b,c
from yyyyy
set identity_insert xxxx on
6、迁移tmpdb
USE master; GO ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'E:\DATA\tempdb.mdf'); GO ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'E:\DATA\templog.ldf'); GO
7、查看目前正在运行的查询
SELECT [Spid] = er.session_Id ,ecid ,[Database] = DB_NAME(sp.dbid) ,[User] = nt_username ,[Status] = er.STATUS ,[Wait] = 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) ,[Parent Query] = qt.TEXT ,Program = sp.program_name ,Hostname ,host_process_id ,client_net_address ,loginame ,kpid ,sp.nt_domain ,start_time FROM sys.dm_exec_requests er INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid inner join sys.dm_exec_connections cn on er.session_id = cn.session_id inner join sys.dm_exec_sessions se on er.session_id = se.session_id CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt WHERE er.session_id > 50 /* Ignore system spids.*/
8、查看索引使用情况
select db_name(database_id) as N'数据库名称', object_name(a.object_id) as N'表名', b.name N'索引名称', user_seeks N'用户索引查找次数', user_scans N'用户索引扫描次数', user_lookups, last_user_seek N'最后查找时间', last_user_scan N'最后扫描时间' from sys.dm_db_index_usage_stats a join sys.indexes b on a.index_id = b.index_id and a.object_id = b.object_id where database_id=db_id('gpaydb') ---改成要查看的数据库 and object_name(a.object_id) ='xxxxxx‘ order by user_seeks,user_scans desc
9、dos下查看一个目录下的文件
dir *.exe /a-d/b/s
就是查找当前目录下的所有exe文件
10、查看所有用户开启的事务，和批量删除长时间事物
SELECT es.session_id, es.login_name, es.host_name, est.text , cn.last_read, cn.last_write, es.program_name ,es.status,last_request_start_time,client_net_address,most_recent_session_id FROM sys.dm_exec_sessions es INNER JOIN sys.dm_tran_session_transactions st ON es.session_id = st.session_id INNER JOIN sys.dm_exec_connections cn ON es.session_id = cn.session_id CROSS APPLY sys.dm_exec_sql_text(cn.most_recent_sql_handle) est LEFT OUTER JOIN sys.dm_exec_requests er ON st.session_id = er.session_id AND er.session_id IS NULL select session_id,transaction_id,is_user_transaction,is_local from sys.dm_tran_session_transactions where is_user_transaction=1 declare @sessionid int ,@sqltxt varchar(max) declare mycursor cursor for select session_id from sys.dm_tran_session_transactions where is_user_transaction=1 open mycursor fetch next from mycursor into @sessionid while @@FETCH_STATUS = 0 begin --print @sessionid set @sqltxt = 'kill '+convert(varchar(5),@sessionid) print @sqltxt exec (@sqltxt) fetch next from mycursor into @sessionid end close mycursor deallocate mycursor
查询数据库备份相关的信息
;WITH CTE_BACKUP AS ( SELECT a.database_name ,CASE a.[type] WHEN 'D' THEN 'Database' WHEN 'I' THEN 'Differential Database' WHEN 'L' THEN 'Log' WHEN 'F' THEN 'File or filegroup' WHEN 'G' THEN 'Defferential file' WHEN 'P' THEN 'Partial' WHEN 'Q' THEN 'Differential partial' ELSE NULL END AS backup_type ,a.backup_start_date AS [start_date] ,a.backup_finish_date AS [end_date] ,CAST(DATEDIFF(SECOND, a.backup_start_date, a.backup_finish_date) / 60.0 AS DECIMAL(8,2)) AS duration_minute ,CAST(a.backup_size / 1024 / 1024 AS DECIMAL(18, 2)) AS backup_size_mb ,CAST(a.compressed_backup_size / 1024 / 1024 AS DECIMAL(18, 2)) AS compressed_backup_size_mb ,CAST(c.file_size / 1024 / 1024 AS DECIMAL(18, 2)) AS file_size_mb ,b.physical_device_name AS backup_path ,a.[name] AS backup_set_name ,a.backup_set_id ,a.media_set_id ,c.file_type ,c.physical_name AS db_files_path ,c.logical_name --,'--------->>>>' split --, * FROM msdb.dbo.backupset AS a LEFT JOIN msdb.dbo.backupmediafamily AS b ON b.media_set_id = a.media_set_id LEFT JOIN msdb.dbo.backupfile AS c ON c.backup_set_id = a.backup_set_id --ORDER BY backup_start_date DESC ) SELECT * FROM CTE_BACKUP WHERE -- CTE_BACKUP.backup_type = 'Log' --and CTE_BACKUP.[start_date] >= '2020-02-12' -- AND CTE_BACKUP.[start_date] < '2020-02-13' file_type = 'D'
查看数据库数据、索引和剩余空间
create table #tmp(dbname varchar(500), dbsizeMB varchar(500), unspaceMB varchar(500), reservedKB varchar(500), DataKB varchar(500),indexKB varchar(500), unsedKB varchar(500)) insert into #tmp exec (' sp_msforeachdb '' declare @dbsize bigint declare @logsize bigint declare @reservedpages bigint declare @usedpages bigint declare @pages bigint select @dbsize = sum(convert(bigint,case when status & 64 = 0 then size else 0 end)), @logsize = sum(convert(bigint,case when status & 64 <> 0 then size else 0 end)) from [?].dbo.sysfiles select @reservedpages = sum(a.total_pages), @usedpages = sum(a.used_pages), @pages = sum(CASE When it.internal_type IN (202,204,207,211,212,213,214,215,216,221,222,236) Then 0 When a.type <> 1 and p.index_id < 2 Then a.used_pages When p.index_id < 2 Then a.data_pages Else 0 END) from [?].sys.partitions p join [?].sys.allocation_units a on p.partition_id = a.container_id left join [?].sys.internal_tables it on p.object_id = it.object_id select ''''?'''' as dbname, ltrim(str((convert (dec (15,2),@dbsize) + convert (dec (15,2),@logsize))* 8192 / 1048576,15,2)) as database_sizeMB ,ltrim(str((case when @dbsize >= @reservedpages then (convert (dec (15,2),@dbsize) - convert (dec (15,2),@reservedpages)) * 8192 / 1048576 else 0 end),15,2)) as unallocated_spaceMB ,ltrim(str(@reservedpages * 8192 / 1024.,15,0)) as reservedKB ,ltrim(str(@pages * 8192 / 1024.,15,0)) as dataKB ,ltrim(str((@usedpages - @pages) * 8192 / 1024.,15,0)) as indexKB ,ltrim(str((@reservedpages - @usedpages) * 8192 / 1024.,15,0)) as unusedKB ''') select * from #tmp where dbname not in ('master','tempdb','model','msdb','YWMonitor') drop table #tmp
