--★★SQL Server 2005及以上版本數(shù)據(jù)庫批量備份存儲過程(判斷盤符、路徑，錯誤盤符返回，不存在
--路徑自動創(chuàng)建)★★★★★★★★★★★★★

USE master
GO
--1.周期性備份數(shù)據(jù)庫代碼(保留原來備份的):
--備份文件名為：原數(shù)據(jù)庫名稱+'_'+備份日期.bak
IF OBJECT_ID('sp_backupdatabase') IS NOT NULL
DROP PROC sp_backupdatabase
GO
CREATE PROC sp_backupdatabase
@path NVARCHAR(100)--路徑
AS
--路徑名格式標準化
IF RIGHT(@path,1)<>'\' SET @path=@path+'\'
--獲取文件夾信息
DECLARE @t TABLE(id INT IDENTITY,a INT,b INT,c INT)
DECLARE @fpath NVARCHAR(3)
SET @fpath=LEFT(@path,3)
INSERT @t EXEC master..xp_fileexist @fpath
INSERT @t EXEC master..xp_fileexist @path
--如果指定盤符有誤不存在，則返回錯誤提示：
IF EXISTS(SELECT 1 FROM @t WHERE id=1 AND c=0)
BEGIN
RAISERROR(N'輸入的盤符不存在，請重新輸入!',16,1)
RETURN
END
--如果不存在指定的文件夾，則創(chuàng)建:
ELSE IF EXISTS(SELECT 1 FROM @t WHERE b=0 AND id=2)
BEGIN
DECLARE @mddir NVARCHAR(100)
SET @mddir='md '+@path
EXEC master..xp_cmdshell @mddir
END
--開始備份數(shù)據(jù)庫到指定的目錄
DECLARE @s nvarchar(4000)
SELECT @s=ISNULL(@s+';','')
+N'BACKUP database ['+name+'] TO DISK = '''
+@path+name+'_'+CONVERT(NVARCHAR(8),getdate(),112)+N'.bak'''
FROM master..sysdatabases
WHERE name NOT IN('master','tempdb','model','msdb','pubs')--這里篩選不參加備份的數(shù)據(jù)庫
EXEC(@S)
GO
--調(diào)用方法：
EXEC sp_backupdatabase 'f:\Backup\tony'
/*
--返回信息：
已為數(shù)據(jù)庫 'mydb'，文件 'mydb' (位于文件 1 上)處理了 312 頁。
已為數(shù)據(jù)庫 'mydb'，文件 'mydb_log' (位于文件 1 上)處理了 1 頁。
BACKUP DATABASE 成功處理了 313 頁，花費 0.733 秒(3.336 MB/秒)。
已為數(shù)據(jù)庫 'test'，文件 'test' (位于文件 1 上)處理了 208 頁。
已為數(shù)據(jù)庫 'test'，文件 'test_log' (位于文件 1 上)處理了 1 頁。
BACKUP DATABASE 成功處理了 209 頁，花費 0.413 秒(3.951 MB/秒)。
--備份后的文件列表：
mydb_20100418.bak
test_20100418.bak
*/


--2.周期性備份數(shù)據(jù)庫代碼(自動刪除原備份文件):
--備份文件名為：原數(shù)據(jù)庫名稱.bak
IF OBJECT_ID('sp_backupdatabase') IS NOT NULL
DROP PROC sp_backupdatabase
GO
CREATE PROC sp_backupdatabase
@path NVARCHAR(100)--路徑
AS
--路徑名格式標準化
IF RIGHT(@path,1)<>'\' SET @path=@path+'\'
--獲取文件夾信息
DECLARE @t TABLE(id INT IDENTITY,a INT,b INT,c INT)
DECLARE @fpath NVARCHAR(3)
SET @fpath=LEFT(@path,3)
INSERT @t EXEC master..xp_fileexist @fpath
INSERT @t EXEC master..xp_fileexist @path
--如果指定盤符有誤不存在，則返回錯誤提示：
IF EXISTS(SELECT 1 FROM @t WHERE id=1 AND c=0)
BEGIN
RAISERROR(N'輸入的盤符不存在，請重新輸入!',16,1)
RETURN
END
--如果不存在指定的文件夾，則創(chuàng)建:
ELSE IF EXISTS(SELECT 1 FROM @t WHERE b=0 AND id=2)
BEGIN
DECLARE @mddir NVARCHAR(100)
SET @mddir='md '+@path
EXEC master..xp_cmdshell @mddir
END
--開始備份數(shù)據(jù)庫到指定的目錄
DECLARE @s nvarchar(4000)
SELECT @s=ISNULL(@s+';','')
+N'BACKUP database ['+name+'] TO DISK = '''
+@path+name+N'.bak'' WITH INIT'
FROM master..sysdatabases
WHERE name NOT IN('master','tempdb','model','msdb','pubs')--這里篩選不參加備份的數(shù)據(jù)庫
EXEC(@S)
GO
--調(diào)用方法：
EXEC sp_backupdatabase 'f:\Backup\tony2'
/*
--返回信息：
已為數(shù)據(jù)庫 'mydb'，文件 'mydb' (位于文件 1 上)處理了 312 頁。
已為數(shù)據(jù)庫 'mydb'，文件 'mydb_log' (位于文件 1 上)處理了 1 頁。
BACKUP DATABASE 成功處理了 313 頁，花費 0.599 秒(4.082 MB/秒)。
已為數(shù)據(jù)庫 'test'，文件 'test' (位于文件 1 上)處理了 208 頁。
已為數(shù)據(jù)庫 'test'，文件 'test_log' (位于文件 1 上)處理了 1 頁。
BACKUP DATABASE 成功處理了 209 頁，花費 0.351 秒(4.651 MB/秒)。
--備份后的文件列表：
mydb.bak
test.bak
*/

--★★DBCC是SQL Server提供的一組控制臺命令，功能很強大，掌握一些必要的語句，對操作數(shù)據(jù)庫有不---少幫助，歸類如下：

--一、DBCC 幫助類命令

* DBCC HELP('?')
--查詢所有的DBCC命令
* DBCC HELP('命令')
--查詢指定的DBCC命令的語法說明
* DBCC USEROPTIONS
--返回當前連接的活動(設(shè)置)的SET選項

--二、DBCC 檢查驗證類命令

* DBCC CHECKALLOG ('數(shù)據(jù)庫名稱')
--檢查指定數(shù)據(jù)庫的磁盤空間分配結(jié)構(gòu)的一致性
* DBCC CHECKCATALOG ('數(shù)據(jù)庫名稱')
--檢查指定數(shù)據(jù)庫的系統(tǒng)表內(nèi)和系統(tǒng)表間的一致性
* DBCC CHECKCONSTAINTS ('tablename')
--檢查指定表上的指定約束或所有約束的完整性
* DBCC CHECKDB
--檢查數(shù)據(jù)庫中的所有對象的分配和結(jié)構(gòu)完整性
* DBCC CHECKFILEGROUP
--檢查指定文件組中所有表在當前數(shù)據(jù)庫中的分配和結(jié)構(gòu)完整性
* DBCC CHECKTABLE
--檢查指定表或索引視圖的數(shù)據(jù)、索引及test、ntest和image頁的完整性
* DBCC CHECKIDENT
--檢查指定的當前標識值
* DBCC SQLPERF(UMSSTATS) undocumented in BOL
--可以用來檢查是否CPU使用達到瓶頸
--最關(guān)鍵的一個參考數(shù)據(jù)num runnable,表明當前有多少個線程再等待運行
--如果大于等于2,考慮CPU達到瓶頸

--三、DBCC 維護類命令

* DBCC CLEANTABLE ('db_name','table_name')
--回收Alter table drop column語句刪除可變長度列或text
* DBCC DBREINDEX
--重建指定數(shù)據(jù)庫的一個或多個索引
* DBCC INDEXDEFRAG
--對表或視圖上的索引和非聚集索引進行碎片整理
* DBCC PINTABLE (db_id,object_id)
--將表數(shù)據(jù)駐留在內(nèi)存中
--查看哪些表駐留在內(nèi)存的方法是：
select objectproperty(object_id('tablename'）,'tableispinned')
* DBCC UNPINTABLE (db_id,object_id)
--撤消駐留在內(nèi)存中的表
* DBCC SHRINKDATABASE(db_id,int)
--收縮指定數(shù)據(jù)庫的數(shù)據(jù)文件和日志文件大小
* DBCC SHRINKFILE(file_name,int)
--收縮相關(guān)數(shù)據(jù)庫的指定數(shù)據(jù)文件和日志文件大小

--四、DBCC 性能調(diào)節(jié)命令

* DBCC dllname(FREE)
sp_helpextendedproc 查看加載的擴展PROC
--在內(nèi)存中卸載指定的擴展過程動態(tài)鏈接庫（dll)
* DBCC DROPCLEANBUFFERS
--從緩沖池中刪除所有緩沖區(qū)
* DBCC FREEPROCCACHE
--從過程緩沖區(qū)刪除所有元素
* DBCC INPUTBUFFER
--顯示從客戶機發(fā)送到服務(wù)器的最后一個語句
* DBCC OPENTRAN (db_name)
--查詢某個數(shù)據(jù)庫執(zhí)行時間最久的事務(wù)，由哪個程序擁有
* DBCC SHOW_STATISTICS
--顯示指定表上的指定目標的當前分布統(tǒng)計信息
* DBCC SHOWCONTIG
--顯示指定表的數(shù)據(jù)和索引的碎片信息
* DBCC SQLPERF
(logspace) --查看各個DB的日志情況
(iostats) --查看IO情況
(threads) --查看線程消耗情況
--返回多種有用的統(tǒng)計信息
* DBCC CACHESTATS
--顯示SQL Server 2000內(nèi)存的統(tǒng)計信息
* DBCC CURSORSTATS
--顯示SQL Server 2000游標的統(tǒng)計信息
* DBCC MEMORYSTATS
--顯示SQL Server 2000內(nèi)存是如何細分的
* DBCC SQLMGRSTATS
--顯示緩沖中先讀和預讀準備的SQL語句

--五、DBCC 未公開的命令

* DBCC ERRLOG
--初始化SQL Server 2000的錯誤日志文件
* DBCC FLUSHPROCINDB (db_id)
--清除SQL Server 2000服務(wù)器內(nèi)存中的某個數(shù)據(jù)庫的存儲過程緩存內(nèi)容
* DBCC BUFFER (db_name,object_name,int(緩沖區(qū)個數(shù)))
--顯示緩沖區(qū)的頭部信息和頁面信息
* DBCC DBINFO (db_name)
--顯示數(shù)據(jù)庫的結(jié)構(gòu)信息
* DBCC DBTABLE
--顯示管理數(shù)據(jù)的表(數(shù)據(jù)字典)信息
* DBCC IND (db_name,table_name,index_id)
--查看某個索引使用的頁面信息
* DBCC REBUILDLOG
--重建SQL Server 2000事務(wù)日志文件
* DBCC LOG (db_name,3) (-1--4)
--查看某個數(shù)據(jù)庫使用的事物日志信息
* DBCC PAGE
--查看某個數(shù)據(jù)庫數(shù)據(jù)頁面信息
* DBCC PROCBUF
--顯示過程緩沖池中的緩沖區(qū)頭和存儲過程頭
* DBCC PRTIPAGE
--查看某個索引頁面的每行指向的頁面號
* DBCC PSS (user,spid,1)
--顯示當前連接到SQL Server 2000服務(wù)器的進程信息
* DBCC RESOURCE
--顯示服務(wù)器當前使用的資源情況
* DBCC TAB (db_id,object_id)
--顯示數(shù)據(jù)頁面的結(jié)構(gòu)

六、DBCC跟蹤標記

--跟蹤標記用于臨時設(shè)置服務(wù)器的特定特征或關(guān)閉特定行為，常用于診斷性能問題或調(diào)試存儲過程或復雜的---計算機系統(tǒng)
* DBCC TRACEON (3604)
--打開跟蹤標記
* DBCC TRACEOFF
--關(guān)閉跟蹤標記
* DBCC TRACESTATS
--查看跟蹤標記狀態(tài)

--七、使用 DBCC 結(jié)果集輸出

　　--許多 DBCC 命令可以產(chǎn)生表格格式的輸出（使用 WITH TABLERESULTS 選項）。該信息可裝載到
--中以便將來使用。以下顯示一個示例腳本：

　　
　　CREATE TABLE DBCCResult (

　　DBCCFlag INT,

　　Result INT

　　)



　　INSERT INTO DBCCResult

　　EXEC ('DBCC TRACESTATUS (-1) WITH NO_INFOMSGS')



　　SELECT *

　　FROM DBCCResult
--八、官方使用DBCC的建議
--1、在系統(tǒng)使用率較低時運行 CHECKDB。
--2、請確保未同時執(zhí)行其它磁盤 I/O 操作，例如磁盤備份。
--3、將 tempdb 放到單獨的磁盤系統(tǒng)或快速磁盤子系統(tǒng)中。
--4、允許 tempdb 在驅(qū)動器上有足夠的擴展空間。 使用帶有 ESTIMATE ONLY 的 DBCC
--估計 tempdb 將需要多少空間。
--5、避免運行占用大量 CPU 的查詢或批處理作業(yè)。
--6、在 DBCC 命令運行時，減少活動事務(wù)。
--7、使用 NO_INFOMSGS 選項顯著減少處理和 tempdb 的使用。
--8、考慮使用帶有 PHYSICAL_ONLY 選項的 DBCC CHECKDB 來檢查頁和記錄首部
--的物理結(jié)構(gòu)。當硬件導致的錯誤被置疑時，這個操作將執(zhí)行快速檢查。

--在發(fā)布,訂閱復制時要用服務(wù)器實名時可以這樣:

select * from sysservers (可以找到原來服務(wù)器的名稱)




exec sp_dropserver 'jmsql9' (刪除原來的服務(wù)器名)

exec sp_addserver 'jmSQL9' ,LOCAL (改為新的服務(wù)器名)

ALTER DATABASE [jm] SET SINGLE_USER (改為單用戶模式)

DBCC CHECKDB("databasename",REPAIR_REBUILD) WITH TABLOCK (修復數(shù)據(jù)庫)

DBCC CHECKTABLE("tablename",repair_rebuild) with tablock (修復表)

DBCC DBREINDEX ('t_icitem' , ' ') 修復此表所有的索引。

ALTER DATABASE [jm] SET MULTI_USER (改為多用戶模式)

REPAIR_ALLOW_DATA_LOSS：執(zhí)行由REPAIR_REBUILD 完成的所有修復，包括對行和頁進行分配和取消分配以改正分配錯誤、結(jié)構(gòu)行或頁的錯誤，以及刪除已損壞的文本對象。這些修復可能會導致一些數(shù)據(jù)丟失。修復操作可以在用戶事務(wù)下完成以允許用戶回滾所做的更改。如果回滾修復，則數(shù)據(jù)庫仍會含有錯誤，應該從備份進行恢復。如果由于所提供修復等級的緣故遺漏某個錯誤的修復，則將遺漏任何取決于該修復的修復。修復完成后，備份數(shù)據(jù)庫。

REPAIR_FAST 進行小的、不耗時的修復操作，如修復非聚集索引中的附加鍵。這些修復可以很快完成，并且不會有丟失數(shù)據(jù)的危險。

REPAIR_REBUILD 執(zhí)行由REPAIR_FAST 完成的所有修復，包括需要較長時間的修復（如重建索引），執(zhí)行這些修復時不會有丟失數(shù)據(jù)的危險。

dbcc shrinkdatabase (jm) 壓縮數(shù)據(jù)庫

本文來自CSDN博客，轉(zhuǎn)載請標明出處：http://blog.csdn.net/fredrickhu/archive/2009/10/14/4669452.aspx

--★★獲得job執(zhí)行的步驟內(nèi)容★★★★★★★★★★★★★★★★

select j.name,s.step_id,s.step_name,s.command
from msdb.dbo.sysjobs_view j join msdb.dbo.sysjobsteps s
on j.job_id=s.job_id;

--★★查看死鎖和阻塞并殺死它們★★★★★★★★★★★★★★★★

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_lockinfo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_lockinfo]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


/*--處理死鎖

查看當前進程,或死鎖進程,并能自動殺掉死進程

因為是針對死的,所以如果有死鎖進程,只能查看死鎖進程
當然,你可以通過參數(shù)控制,不管有沒有死鎖,都只查看死鎖進程

感謝: caiyunxia,jiangopen 兩位提供的參考信息

--鄒建 2004.4--*/

/*--調(diào)用示例

exec p_lockinfo
--*/
create proc p_lockinfo
@kill_lock_spid bit=1, --是否殺掉死鎖的進程,1 殺掉, 0 僅顯示
@show_spid_if_nolock bit=1 --如果沒有死鎖的進程,是否顯示正常進程信息,1 顯示,0 不顯示
as
declare @count int,@s nvarchar(1000),@i int
select id=identity(int,1,1),標志,
進程ID=spid,線程ID=kpid,塊進程ID=blocked,數(shù)據(jù)庫ID=dbid,
數(shù)據(jù)庫名=db_name(dbid),用戶ID=uid,用戶名=loginame,累計CPU時間=cpu,
登陸時間=login_time,打開事務(wù)數(shù)=open_tran, 進程狀態(tài)=status,
工作站名=hostname,應用程序名=program_name,工作站進程ID=hostprocess,
域名=nt_domain,網(wǎng)卡地址=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 group by blocked
)b on a.spid=b.blocked where a.blocked=0
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=1
from master..sysprocesses a where blocked<>0
)a order by s1,s2

select @count=@@rowcount,@i=1

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
set @count=@@rowcount
end

if @count>0
begin
create table #t1(id int identity(1,1),a nvarchar(30),b Int,EventInfo nvarchar(255))
if @kill_lock_spid=1
begin
declare @spid varchar(10),@標志 varchar(10)
while @i<=@count
begin
select @spid=進程ID,@標志=標志 from #t where id=@i
insert #t1 exec('dbcc inputbuffer('+@spid+')')
if @標志='死鎖的進程' exec('kill '+@spid)
set @i=@i+1
end
end
else
while @i<=@count
begin
select @s='dbcc inputbuffer('+cast(進程ID as varchar)+')' from #t where id=@i
insert #t1 exec(@s)
set @i=@i+1
end
select a.*,進程的SQL語句=b.EventInfo
from #t a join #t1 b on a.id=b.id
end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

----------版本2： 我在鄒建的基礎(chǔ)上改寫的(KILL 死鎖時間超過15秒的死鎖進程 ---------------

/*
exec p_lockinfo 0,1;
*/
alter proc p_lockinfo
@kill_lock_spid bit=1, --是否殺掉死鎖的進程,1 殺掉, 0 僅顯示
@show_spid_if_nolock bit=1 --如果沒有死鎖的進程,是否顯示正常進程信息,1 顯示,0 不顯示
as
declare @count int,@s nvarchar(1000),@i int
select id=identity(int,1,1),標志,
進程ID=spid,線程ID=kpid,塊進程ID=blocked,數(shù)據(jù)庫ID=dbid,
數(shù)據(jù)庫名=db_name(dbid),用戶ID=uid,用戶名=loginame,累計CPU時間=cpu,等待時間=waittime,
登陸時間=login_time,打開事務(wù)數(shù)=open_tran, 進程狀態(tài)=status,
工作站名=hostname,應用程序名=program_name,工作站進程ID=hostprocess,
域名=nt_domain,網(wǎng)卡地址=net_address
into #t from(
select 標志='死鎖的進程',
spid,kpid,a.blocked,dbid,uid,loginame,cpu,waittime,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 group by blocked
)b on a.spid=b.blocked where a.blocked=0
union all
select '|_犧牲品_>',
spid,kpid,blocked,dbid,uid,loginame,cpu,waittime,login_time,open_tran,
status,hostname,program_name,hostprocess,nt_domain,net_address,
s1=blocked,s2=1
from master..sysprocesses a where blocked<>0
)a order by s1,s2

select @count=@@rowcount,@i=1

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

if @count>0
begin
create table #t1(id int identity(1,1),a nvarchar(30),b Int,EventInfo nvarchar(4000))
if @kill_lock_spid=1
begin
declare @spid varchar(10),@標志 varchar(10), @等待時間 int
while @i<=@count
begin
select @spid=進程ID,@標志=標志, @等待時間=等待時間 from #t where id=@i
insert #t1 exec('dbcc inputbuffer('+@spid+')')
if @標志='死鎖的進程' and @等待時間 >=15000 exec('kill '+@spid)
set @i=@i+1
end
end
else
while @i<=@count
begin
select @s='dbcc inputbuffer('+cast(進程ID as varchar)+')' from #t where id=@i
insert #t1 exec(@s)
set @i=@i+1
end
select a.*,進程的SQL語句=b.EventInfo
from #t a join #t1 b on a.id=b.id
end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

最新評論