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