sql server數(shù)據(jù)庫日常維護規(guī)范

1、1.1.數(shù)據(jù)庫服務器負載情況維護數(shù)據(jù)庫服務器負載情況維護查看查看 cpucpu，ioio，內存使用情況，內存使用情況規(guī)則規(guī)則windows系統(tǒng):打開任務管理器，選擇進程選項卡，可以查看cpu、內存和 io 使用率。2.2.數(shù)據(jù)庫運行狀態(tài)維護數(shù)據(jù)庫運行狀態(tài)維護規(guī)則規(guī)則1.實例運行狀態(tài)使用 sql server management studio能連接說明實例運行正常。2.磁盤空間使用情況直接登陸服務器進行磁盤使用率檢查3. 查看錯誤日志:方法一：可以通過執(zhí)行該命令來查看錯誤日志信息：exec xp_readerrorlog方法二：在 sql server management studio中連接

2、該 sql server 實例，object explorer 中查找 management-sql server logs-右鍵選 view- 選擇你要看的日志信息 （sql server log or sqlserver and windows log）方法三：去安裝路徑下去找你的log 文件，我的默認實例路徑如下driver:sql server 2008 r2mssql10_50.mssqlservermssqllogerrorlog4跟蹤事件企業(yè)管理里 sql2005sql2008在上方菜單工具sql profiler，按照選擇進行跟蹤。3.3.數(shù)據(jù)庫備份情況維護數(shù)據(jù)庫備份情況維護規(guī)

3、則規(guī)則數(shù)據(jù)庫備份情況檢查：備份日志顯示:成功,說明備份成功。備份日志顯示：失敗,說明備份失敗，需要進一步查找失敗原因。每天檢查異地備份是否成功。4.4.數(shù)據(jù)庫對象的維護數(shù)據(jù)庫對象的維護規(guī)則規(guī)則1、查詢各個磁盤分區(qū)的剩余空間：exec master.dbo.xp_fixeddrives2、查詢數(shù)據(jù)庫的數(shù)據(jù)文件及日志文件的相關信息（包括文件組、當前文件大小、文件最大值、文件增長設置、文件邏輯名、文件路徑等）select * from 數(shù)據(jù)庫名.dbo.sysfiles轉換文件大小單位為 mb：select name, convert(float,size) * (8192.0/1024.0)/10

4、24. from 數(shù)據(jù)庫名.dbo.sysfiles3、查詢當前數(shù)據(jù)庫的磁盤使用情況：exec sp_spaceused、查詢數(shù)據(jù)庫服務器各數(shù)據(jù)庫日志文件的大小及利用率dbcc sqlperf(logspace)5、統(tǒng)計數(shù)據(jù)庫中每張表的大小create table tmp(namevarchar(50),rowscountint,reservedvarchar(50),datavarchar(50),index_sizevarchar(50),unusedvarchar(50);inserttmp(name, rowscount, reserved, data, index_size, unu

5、sed)execsp_msforeachtable command1=sp_spaceused ?;select * from tmp where name tmp order by namedrop table tmp ;或者select, b.rowsfromsysobjects as a inner joinsysindexes as b on a.id = b.idwhere(a.type = u) and (b.indid in (0, 1)order by b.rows desc6、檢查數(shù)據(jù)庫完整性dbcccheckdb(portal)dbcccheckdb(porta

6、l) with tablock7、數(shù)據(jù)庫重命名、修改恢復模式、修改用戶模式-數(shù)據(jù)庫重命名alter database wcmodify name = test-設置數(shù)據(jù)庫為完整恢復模式alter database testset recovery full-只允許一個用戶訪問數(shù)據(jù)庫alter database testsetsingle_userwith rollback after 10 seconds -指定多少秒后回滾事務-只有 sysadmin,dbcreator,db_owner角色的成員可以訪問數(shù)據(jù)庫alter database wcsetrestricted_userwith r

7、ollback immediate-立即回滾事務-多用戶模式alter database wcsetmulti_userwith no_wait-不等待立即改變，如不能立即完成，那么會導致執(zhí)行錯誤8、擴展數(shù)據(jù)庫：增加文件組、增加文件、修改文件大小、修改文件的邏輯名稱-添加文件組alter database testadd filegroup wc_fg8-添加數(shù)據(jù)文件alter database testadd file(name = wc_fg8,filename = d:wc_fg8.ndf,size = 1mb,maxsize = 10mb,filegrowth = 1mb)to fil

8、egroup wc_fg8-添加日志文件alter database testadd log file(name = wc_log3,filename = d:wc_fg3.ldf,size = 1mb,maxsize = 10mb,filegrowth = 100kb)-修改數(shù)據(jù)文件的大小,增長大小,最大大小alter database testmodify file(name = wc_fg8,size = 2mb,-必須大于之前的大小,否則報錯maxsize= 8mb,filegrowth = 10%)-修改數(shù)據(jù)文件或日志文件的邏輯名稱alter database testmodify

9、file(name = wc_log3,newname = wc_fg33)9、移動文件-由于在 sql server中文件組、文件不能離線,所以必須把整個數(shù)據(jù)庫設置為離線checkpointgoalter database wcset offlinego-修改文件名稱alter database wcmodify file(name = wc_fg8,filename = d:wcwc_fg8.ndf)go-把原來的文件復制到新的位置：d:wcwc_fg8.ndf-設置數(shù)據(jù)庫在線alter database wcset online10、設置默認文件組、只讀文件組-設置默認文件組alter

10、database wcmodify filegroup wc_fg8 default-設為只讀文件組-如果文件已經(jīng)是某個屬性，不能再次設置相同屬性alter database wcmodify filegroup wc_fg8 read_write11、收縮數(shù)據(jù)庫、收縮文件-收縮數(shù)據(jù)庫dbcc shrinkdatabase(test,-要收縮的數(shù)據(jù)庫名稱或數(shù)據(jù)庫id10-收縮后，數(shù)據(jù)庫文件中空間空間占用的百分比)dbcc shrinkdatabase(test,-要收縮的數(shù)據(jù)庫名稱或數(shù)據(jù)庫id10,-收縮后，數(shù)據(jù)庫文件中空閑空間占用的百分比notruncate -在收縮時，通過數(shù)據(jù)移動來騰出自

11、由空間)dbcc shrinkdatabase(test,-要收縮的數(shù)據(jù)庫名稱或數(shù)據(jù)庫 id10,-收縮后，數(shù)據(jù)庫文件中空間空間占用的百分比truncateonl y -在收縮時， 只是把文件尾部的空閑空間釋放)-收縮文件dbcc shrinkfile(wc_fg8,-要收縮的數(shù)據(jù)文件邏輯名稱7-要收縮的目標大小，以 mb 為單位)dbcc shrinkfile(wc_fg8,-要收縮的數(shù)據(jù)文件邏輯名稱emptyfile -清空文件，清空文件后，才可以刪除文件)12、刪除文件、刪除文件組1.要刪除文件，必須要先把文件上的數(shù)據(jù)刪除，或者移動到其他文件或文件組上-刪除數(shù)據(jù)后，必須要清空文件的內容d

12、bcc shrinkfile(wc_fg8,emptyfile)-刪除文件，同時也在文件系統(tǒng)底層刪除了文件alter database testremove file wc_fg82.要刪除文件組，必須先刪除所有文件-最后刪除文件組alter database testremove filegroup wc_fg813、重新組織索引alter index idx_temp_lock_id on dbo.temp_lockreorganizewith ( lob_compaction = on )use testgoselect dbcc indexdefrag(+db_name()+,+o.n

13、ame+,+ + );-,db_name(),-,-,-i.*fromsysindexesiinner join sysobjects oon i.id = o.idwhereo.xtype = uandi.indid0andcharindex(wa_sys,) = 014、重新生成索引alter index idx_temp_lock_id on dbo.temp_lockrebuild partition = allwith ( pad_index= off,statistics_norecompute = off,allow_row_locks= on,allow_page_locks= on,online = off,sort_in_tempdb = off )15、更新統(tǒng)計信息-更新表中某個的統(tǒng)計信息update statistics temp_lock(_wa_sys_00000001_07020f21)update statistics temp_lock(_wa_sys_00000001_07020f21)with sample 50 percentupdate statistics temp_lock(_wa_sys_00000001_07020f21)with resample,-使用最近的采樣速率更新