View Code
/*-----------------------------------------------------------------------------------------------------------------------
名 稱：查看死锁情況
調用對像：
備註說明：
修改日志：
程式作者：XXXXX
-----------------------------------------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[sp_who_lock]
AS
BEGIN
SET NOCOUNT ONDECLARE @spid int,@bl int,@intTransactionCountOnEntry int,@intRowcount int,@intCountProperties int,@intCounter int
CREATE TABLE #tmp_lock_who
(
id int identity(1,1),
spid smallint,
bl smallint
)IF @@ERROR<>0 RETURN @@ERRORINSERT INTO #tmp_lock_who(spid,bl)
SELECT 0 ,blocked
FROM (SELECT * FROM sysprocesses WHERE blocked>0 ) a
WHERE not exists(SELECT * FROM (SELECT * FROM sysprocesses WHERE blocked>0 ) b
WHERE a.blocked=spid)
UNION SELECT spid,blocked FROM sysprocesses WHERE blocked>0IF @@ERROR<>0 RETURN @@ERROR--找到臨時表的記錄數
SELECT @intCountProperties = Count(*),@intCounter = 1 FROM #tmp_lock_whoIF @@ERROR<>0 RETURN @@ERRORIF @intCountProperties=0
SELECT '現在沒有阻塞和死鎖信息' as Message--循環開始
WHILE @intCounter <= @intCountProperties
BEGIN
--取第一條記錄
SELECT @spid = spid,@bl = bl
FROM #tmp_lock_who WHERE Id = @intCounter
BEGIN
IF @spid =0
SELECT '引起數據庫死鎖的是: '+ CAST(@bl AS VARCHAR(10)) + '進程號,其執行的SQL語法如下'
ELSE
SELECT '進程號SPID：'+ CAST(@spid AS VARCHAR(10))+ '被' + '進程號SPID：'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其當前進程執行的SQL語法如下' DBCC INPUTBUFFER (@bl )
END --循環指針下移
SET @intCounter = @intCounter + 1
END DROP TABLE #tmp_lock_who RETURN 0 SET NOCOUNT OFF
END
GO
/*-----------------------------------------------------------------------------------------------------------------------
名 稱：資料庫備份
調用對像：
備註說明：
修改日志：
程式作者：XXX
EXEC UP_DataBase_Backup 'IVT','D:\DB_BAK\'
-----------------------------------------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[UP_DataBase_Backup]
(
@databsename VARCHAR(100),
@todiskpath VARCHAR(200)
)
AS
BEGIN
SET NOCOUNT ONDECLARE @filename VARCHAR(100)
SET @filename=@databsename+'_'+CONVERT(VARCHAR(10),GETDATE(),112)+'.bak'SET @todiskpath=@todiskpath+@filename
backup database @databsename to disk=@todiskpathSET NOCOUNT OFF
END
GO
/*-----------------------------------------------------------------------------------------------------------------------
名 稱：清除資料庫log
調用對像：
備註說明：注意,此存儲過程在建在master數據庫中
修改日志：
程式作者：XXXXX
-----------------------------------------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[shrink_db]
(
@db_name varchar(100)----數據庫名稱
)
AS
BEGIN
SET NOCOUNT ON----1.清空日志
dump transaction @db_name with no_log
--2.截断事务日志
backup log @db_name with no_log
--3.收缩数据库文件(如果不压缩,数据库的文件不会减小
dbcc shrinkdatabase(@db_name)
--4.设置自动收缩
--exec sp_dboption @db_name,autoshrink,true
SET NOCOUNT OFF
END
GO
此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。 原文来自 — 版权归原作者所有。