此文章为脚本整理,部分脚本可能来源网上
1. 死锁检测脚本
-- 检测死锁
select t1.resource_type [资源锁定类型],DB_NAME(resource_database_id) as 数据库名
,t1.resource_associated_entity_id 锁定对象,t1.request_mode as 等待者请求的锁定模式
,t1.request_session_id 等待者SID
,t2.wait_duration_ms 等待时间
,(select TEXT from sys.dm_exec_requests r cross apply
sys.dm_exec_sql_text(r.sql_handle) where r.session_id=t1.request_session_id) as 等待者要执行的SQL
,(select SUBSTRING(qt.text,r.statement_start_offset/2+1,
(case when r.statement_end_offset=-1 then DATALENGTH(qt.text) else r.statement_end_offset end -r.statement_start_offset)/2+1
)
from sys.dm_exec_requests r cross apply sys.dm_exec_sql_text(r.sql_handle)qt
where r.session_id=t1.request_session_id) 等待者正要执行的语句
,t2.blocking_session_id [锁定者SID]
,(select TEXT from sys.sysprocesses p cross apply
sys.dm_exec_sql_text(p.sql_handle)
where p.spid=t2.blocking_session_id
) 锁定者执行语句
from sys.dm_tran_locks t1,sys.dm_os_waiting_tasks t2
where t1.lock_owner_address=t2.resource_address
2.备份数据库脚本
-- 操作说明:直接指定待压缩的数据库运行脚本,如果未指定数据库,会提示当前为系统库,请指定数据库再执行脚本!
-- 如果需要更改备份文件的主目录,请更改下边的 set @savepath = 'D:\' ,将'D:\'改成需要设置的路径,注意最后边一定要有'\'
DECLARE @dbname nvarchar(64)
SET @dbname = (Select Name From Master..SysDataBases Where DbId=(Select Dbid From Master..SysProcesses Where Spid = @@spid))
IF @dbname = 'master'
BEGIN
PRINT '当前为系统库,请指定数据库再执行脚本!'
END
ELSE
BEGIN
PRINT '当前数据库:'+@dbname
declare @savepath varchar(255) --定义备份文件保存主目录
set @savepath = 'D:\'
declare @fileName varchar(max) --定义备份文件名变量
set @fileName = @savepath+@dbname+'-'+convert(varchar(10),getdate(),112)+'.bak' --定义当前备份文件存放路径
PRINT '开始备份数据库到文件:'+@fileName
backup database @dbname to disk=@fileName
END
3.压缩数据库空间脚本
-- 操作说明:直接指定待压缩的数据库运行脚本,如果未指定数据库,会提示当前为系统库,请指定数据库再执行脚本!
DECLARE @dbname nvarchar(64)
SET @dbname = (Select Name From Master..SysDataBases Where DbId=(Select Dbid From Master..SysProcesses Where Spid = @@spid))
IF @dbname = 'master'
BEGIN
PRINT '当前为系统库,请指定数据库再执行脚本!'
END
ELSE
BEGIN
PRINT '当前数据库:'+@dbname
DECLARE @sql nvarchar(1000)
DECLARE @filename nvarchar(64)
DECLARE @logfilename nvarchar(64)
set @dbname='['+@dbname+']'
SET @sql='USE '+@dbname
print @sql
exec sp_executesql @sql
SET @filename=(SELECT name FROM sys.database_files where file_id=1)
PRINT '数据库文件(最小化压缩):'+@filename
DBCC SHRINKFILE (@filename,1)
Declare @verstr varchar(64)
set @verstr = @@version
IF SUBSTRING(@verstr,1,25)='Microsoft SQL Server 2005'
begin
-- 截断日志
set @sql = 'DUMP TRANSACTION '+ @dbname +' WITH NO_LOG'
exec sp_executesql @sql
--DUMP TRANSACTION @dbname WITH NO_LOG
--收缩数据库
set @sql = 'DBCC SHRINKDATABASE('+ @dbname +',TRUNCATEONLY)'
exec sp_executesql @sql
--DBCC SHRINKDATABASE (@dbname,TRUNCATEONLY)
end
else
begin
SET @logfilename=(SELECT name FROM sys.database_files where file_id=2)
PRINT '日志文件(压缩到1M):'+ @logfilename
set @sql = 'ALTER DATABASE '+ @dbname+' SET RECOVERY SIMPLE WITH NO_WAIT'
exec sp_executesql @sql
set @sql = 'ALTER DATABASE '+ @dbname+' SET RECOVERY SIMPLE'
exec sp_executesql @sql
DBCC SHRINKFILE (@logfilename,1,TRUNCATEONLY)
set @sql = 'ALTER DATABASE '+ @dbname+' SET RECOVERY FULL WITH NO_WAIT'
exec sp_executesql @sql
set @sql = 'ALTER DATABASE '+ @dbname+' SET RECOVERY FULL' --还原为完全模式
exec sp_executesql @sql
end
END