MS SQL BackUp Database && Shrink DB Log && SP WHO LOCK

代码

/* -----------------------------------------------------------------------------------------------------------------------
名　　稱：資料庫備份
調用對像：
備註說明：
修改日志：
程式作者：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  ON

     DECLARE   @filename   VARCHAR ( 100 )
     SET   @filename = @databsename + ' _ ' + CONVERT ( VARCHAR ( 10 ), GETDATE (), 112 ) + ' .bak '
    
     SET   @todiskpath = @todiskpath + @filename
     backup     database     @databsename     to     disk = @todiskpath

     SET  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

 

 

 

 

代码

/* -----------------------------------------------------------------------------------------------------------------------
名　　稱：查看死锁情況
調用對像：
備註說明：
修改日志：
程式作者：XXXXX
----------------------------------------------------------------------------------------------------------------------- */
CREATE   PROCEDURE   [ dbo ] . [ sp_who_lock ]
AS
BEGIN
     SET  NOCOUNT  ON
    
     DECLARE   @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   @@ERROR

     INSERT   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 > 0

     IF   @@ERROR <> 0   RETURN   @@ERROR

     -- 找到臨時表的記錄數 
     SELECT   @intCountProperties   =   Count ( * ), @intCounter   =   1   FROM  #tmp_lock_who

     IF   @@ERROR <> 0   RETURN   @@ERROR

     IF   @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

 

/*-----------------------------------------------------------------------------------------------------------------------
名　　稱：
調用對像：
程式作者：鄒建的 
備註說明：
=========================================================================================================================
修改日期        修改者        修改內容
=========================================================================================================================

-----------------------------------------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[UP_LockInfo]
(
@kill_lock_spid       bit=0,                 --是否殺掉阻塞的進程,1   殺掉,   0   僅顯示 
    @show_spid_if_nolock       bit=1,           --如果沒有阻塞的進程,是否顯示正常進程資訊,1   顯示,0   不顯示 
    @dbname           sysname= ''        --如果為空,則查詢所有的庫,如果為null,則查詢當前庫,否則查詢指定庫 
)
AS
BEGIN
SET NOCOUNT ON
Declare @count int,@s nvarchar(2000),@dbid int 
If @dbname='' Set @dbid=db_id() Else SET @dbid=db_id(@dbname)

SELECT
        id=identity(int,1,1),標誌, 
        進程ID=spid,執行緒ID=kpid,塊進程ID=blocked,資料庫ID=dbid, 
        資料庫名=db_name(dbid),用戶ID=uid,用戶名=loginame,累計CPU時間=cpu, 
        登陸時間=login_time,打開事務數=open_tran,進程狀態=status, 
        工作站名=hostname,應用程式名=program_name,工作站進程ID=hostprocess, 
        功能變數名稱=nt_domain,網卡位址=net_address 
INTO #t
FROM ( 
SELECT 標誌= '阻塞的進程', 
        spid,kpid,a.blocked,dbid,uid,loginame,cpu,login_time,open_tran, 
        status,hostname,program_name,hostprocess,nt_domain,net_address, 
        s1=a.spid,s2=0 
FROM master..sysprocesses a
JOIN ( 
SELECT blocked FROM master..sysprocesses
where blocked> 0 
and(@dbid is null or dbid=@dbid) 
group by blocked 
        ) b on a.spid=b.blocked
WHERE a.blocked=0 
AND (@dbid is null or dbid=@dbid)

UNION ALL 
SELECT   '|_犧牲品_> ', 
        spid,kpid,blocked,dbid,uid,loginame,cpu,login_time,open_tran, 
        status,hostname,program_name,hostprocess,nt_domain,net_address, 
        s1=blocked,s2=spid 
FROM master..sysprocesses a   
WHERE blocked <> 0 
AND (@dbid is null or dbid=@dbid) 
    ) a
ORDER BY s1,s2 

SELECT @count=@@rowcount 

IF @count=0 and @show_spid_if_nolock=1 
begin 
insert #t 
select 標誌= '正常的進程 ', 
        spid,kpid,blocked,dbid,db_name(dbid),uid,loginame,cpu,login_time, 
        open_tran,status,hostname,program_name,hostprocess,nt_domain,net_address 
from   master..sysprocesses 
where   @dbid is null or dbid=@dbid 
order   by   spid 
set   @count=@@rowcount 
end 

if @count>0
begin
create table #t1(id int identity(1,1),a nvarchar(30),b Int,EventInfo nvarchar(255))
declare tb cursor local
for
select N'insert #t1 exec(''dbcc inputbuffer('+rtrim(進程ID)+')'')
        if @@rowcount=0 insert #t1(a) values(null)
'+case when @kill_lock_spid=1 and 標誌=N'阻塞的進程 '
then 'kill '+rtrim(進程ID) else '' end
from #t
open tb
fetch tb into @s
while @@fetch_status=0
begin
exec(@s)
fetch tb into @s
end
close tb
deallocate tb
select a.*,進程的SQL語句=b.EventInfo
from #t a join #t1 b on a.id=b.id
order by a.ID
END

SET NOCOUNT OFF
END
GO

 

 

转载于:https://www.cnblogs.com/Athrun/archive/2010/07/15/1778113.html