Cach thu hep c s d liu tempdb trong SQL Server

D?ch tiu ? D?ch tiu ?
ID c?a bi: 307487 - Xem s?n ph?m m bi ny p d?ng vo.
Bung t?t c? | Thu g?n t?t c?

? Trang ny

TM T?T

Bai vit nay thao lun ba phng phap ma ban co th s dung thu hep c s d liu tempdb n kich thc nho hn kich thc c cu hinh sau cung. Phng phap th nht cho phep ban kim soat hoan toan kich thc cua cac tp tempdb nhng yu cu ban phai khi ng lai SQL Server. Phng phap th hai thu hep toan b tempdb, co mt s han ch, co th bao gm vic khi ng lai SQL Server. Phng phap th ba cho phep ban thu hep cac tp rieng le trong tempdb. Hai phng phap sau cung yu cu rng khng c co hoat ng nao xay ra trong c s d liu tempdb trong qua trinh hoat ng thu hep.

Chu y Nu ban ang s dung SQL Server 2005, ban cung co th ap dung nhng phng phap nay. Tuy nhin, ban nn s dung SQL Server Management Studio thay vi Enterprise Manager va Query Analyzer tin hanh nhng hoat ng nay. Chu y thm rng SQL Server management studio trong phin ban 2005 khng cho bit kich thc chinh xac cua cac tp tempdb sau hoat ng thu hep. Gia tri 'Khng gian c phn b hin tai lun c ly t sys.master_files DMV va gia tri nay khng c cp nht sau khi xay ra hoat ng thu hep cho c s d liu tempdb. tim kich c chinh xac cua cac tp tempdb sau hoat ng thu hep, hay thc thi lnh sau trong management studio:
use tempdb
chon (size*8) lam FileSizeKB t sys.database_files

Chu y SQL Server 2008 khng bi anh hng bi s c nay (GUI hin thi kich thc khng chinh xac.)

Thng tin Tempdb

Tempdb la khng gian lam vic tam thi. Cung vi cac ng dung khac, SQL Server s dung tempdb cho:
  • Lu tr cac bang tam thi c tao ro rang.
  • Cac bang lam vic lu g kt qua trung gian c tao ra trong qua trinh x ly va sp xp truy vn.
  • Con tro tinh c cu th hoa.
SQL Server ghi lai u thng tin trong nht ky giao dich tempdb quay lai mt giao dich nhng khng thc hin lai giao dich trong qua trinh truy vn c s d liu. Tinh nng nay lam tng hiu sut cua cu lnh INSERT trong tempdb. Ngoai ra, khng cn ghi nht ky thng tin thc hin lai bt ky giao dich nao vi tempdb c tao lai mi ln ban khi ng lai SQL Server; do o, khng co bt ky giao dich nao chuyn n hoc quay lai. Khi SQL Server khi ng, tempdb c tao lai bng cach s dung ban sao cua c s d liu mu va c thit lp lai v kich thc c cu hinh sau cung.

Theo mc inh, c s d liu tempdb c cu hinh t phat trin khi cn; do o, c s d liu nay co th phat trin theo thi gian n kich thc ln hn mong mun. Vic khi ng n gian SQL Server thit lp lai kich thc cua tempdb v kich thc c cu hinh sau cung. Kich thc c cu hinh la kich thc ro rang sau cung c thit lp bng hoat ng thay i kich thc tp nh ALTER DATABASE vi tuy chon MODIFY FILE hoc cu lnh DBCC SHRINKFILE. Bai vit nay trinh bay ba phng phap ma ban co th s dung thu hep tempdb n kich thc nho hn kich thc c cu hinh.


Phng phap 1 Thu hep Tempdb

Phng phap nay yu cu ban khi ng lai SQL Server.

  1. Dng SQL Server. M du nhc lnh, ri khi ng SQL Server bng cach nhp lnh sau:

    sqlservr -c -f

    Tham s -c va -f khin SQL Server khi ng trong ch cu hinh ti thiu vi kich thc tempdb bng 1 MB cho tp d liu va 0,5 MB cho tp nht ky.

    CHU Y: Nu ban s dung SQL Server co tn nh vi du, ban phai thay i thanh th muc phu hp (Program Files\Microsoft SQL Server\MSSQL$instance name\Binn) va s dung khoa chuyn i -s (-s%instance_name%).
  2. Kt ni n SQL Server bng Query Analyzer, ri chay lnh Transact-SQL sau:
       ALTER DATABASE tempdb MODIFY FILE
       (NAME = 'tempdev', SIZE = target_size_in_MB) 
       --Kich thc muc tiu mong mun cho tp d liu
    
       ALTER DATABASE tempdb MODIFY FILE
       (NAME = 'templog', SIZE = target_size_in_MB)
       --Kich thc muc tiu mong mun cho tp nht ky
    					
  3. Dng SQL Server bng cach nhn Ctrl-C tai ca s du nhc lnh, khi ng lai SQL Server nh mt dich vu, ri xac thc kich thc cua cac tp Tempdb.mdf va Templog.ldf.
Mt han ch cua phng phap nay la chi hoat ng trn cac tp logic tempdb mc inh, tempdev va templog. Nu cac tp b sung c thm vao tempdb, ban co th thu hep chung sau khi khi ng lai SQL Server nh mt dich vu. Tt ca cac tp tempdb u c tao lai trong khi khi ng; do o, cac tp u trng va co th xoa c. xoa cac tp b sung trong tempdb, hay s dung lnh ALTER DATABASE vi tuy chon REMOVE FILE.

Phng phap 2 Thu hep Tempdb

S dung lnh DBCC SHRINKDATABASE thu hep toan b c s d liu tempdb. DBCC SHRINKDATABASE nhn c tham s target_percent, la phn trm khng gian trng mong mun con lai trong tp c s d liu sau khi c s d liu c thu hep. Nu ban s dung DBCC SHRINKDATABASE, ban co th phai khi ng lai SQL Server.

QUAN TRONG: Nu ban chay DBCC SHRINKDATABASE, khng co hoat ng nao khac co th xay ra vi c s d liu tempdb. am bao rng cac qua trinh khac khng th s dung tempdb trong khi chay DBCC SHRINKDATABASE, ban phai khi ng SQL Server trong ch mt ngi dung. bit thm thng tin, hay tham khao phn Anh hng cua vic Thc thi DBCC SHRINKDATABASE hoc DBCCSHRINKFILE Trong khi Tempdb ang c S dung trong bai vit nay.
  1. Xac inh khng gian ang c s dung trong tempdb bng cach s dung quy trinh c lu tr sp_spaceused. Sau o, tinh toan phn trm khng gian trng con lai s dung lam tham s cho DBCC SHRINKDATABASE; vic tinh toan nay da trn kich thc c s d liu mong mun.

    Chu y Trong mt s trng hp, ban co th phai thc thi sp_spaceused @updateusage=true tinh toan lai khng gian c s dung va co c bao cao cp nht. Tham khao Sach Trc tuyn v SQL Server bit thm thng tin v quy trinh c lu tr sp_spaceused.

    Xem xet vi du nay:
    Gia s rng tempdb co hai tp, tp d liu chinh (Tempdb.mdf), co kich thc 100 MB va tp nht ky (Tempdb.ldf), co kich thc 30 MB. Gia s rng sp_spaceused bao cao rng tp d liu chinh cha 60 MB d liu. ng thi, gia s rng ban mun thu hep tp d liu chinh xung con 80 MB. Tinh toan phn trm khng gian trng mong mun con lai sau khi thu hep, 80 MB - 60 MB = 20 MB. By gi, chia 20 MB cho 80 MB = 25% va y la target_percent cua ban. Tp nht ky giao dich c thu hep tng ng, lai 25% hoc 20 MB khng gian trng sau khi c s d liu c thu hep.
  2. Kt ni n SQL Server bng Query Analyzer, ri chay lnh Transact-SQL sau:
       dbcc shrinkdatabase (tempdb, 'target percent') 
       -- Lnh nay thu hep toan b c s d liu tempdb
    					
Co cac han ch i vi vic s dung lnh DBCC SHRINKDATABASE trn c s d liu tempdb. Kich thc muc tiu cho tp d liu va tp nht ky khng th nho hn kich thc c chi inh khi c s d liu c tao ra hoc kich thc sau cung c thit lp ro rang bng hoat ng thay i kich thc tp, chng han nh ALTER DATABASE vi tuy chon MODIFY FILE hoc lnh DBCC SHRINKFILE. Mt han ch khac cua DBCC SHRINKDATABASE la vic tinh toan tham s target_percentage va s phu thuc vao khng gian hin tai c s dung.



Phng phap 3 Thu hep Tempdb

S dung lnh DBCC SHRINKFILE thu hep cac tp tempdb ring le. DBCC SHRINKFILE cung cp tinh linh hoat cao hn DBCC SHRINKDATABASE vi ban co th s dung trn mt tp c s d liu duy nht ma khng anh hng n cac tp khac thuc cung c s d liu o. DBCC SHRINKFILE nhn c tham s kich thc muc tiu, la kich thc cui cung mong mun cho tp c s d liu.

QUAN TRONG: Ban phai chay lnh DBCC SHRINKFILE trong khi khng co hoat ng nao xay ra trong c s d liu tempdb. am bao rng cac qua trinh khac khng th s dung tempdb trong khi DBCC SHRINKFILE thc thi, ban phai khi ng lai SQL Server trong ch mt ngi dung. bit thm thng tin v DBCC SHRINKFILE, hay xem phn Anh hng cua vic Thc thi DBCC SHRINKDATABASE hoc DBCCSHRINKFILE Trong khi Tempdb ang c S dung trong bai vit nay.
  1. Xac inh kich thc mong mun cho tp d liu chinh (tempdb.mdf), tp nht ky (templog.ldf), va/hoc cac tp b sung c thm vao tempdb. am bao rng khng gian c s dung trong cac tp nho hn hoc bng kich thc muc tiu mong mun.
  2. Kt ni vi SQL Server bng Query Analyzer, ri chay lnh Transact-SQL sau cho cac tp c s d liu cu th ma ban cn thu hep:
       use tempdb
       go
    
       dbcc shrinkfile (tempdev, 'target size in MB')
       go
       -- lnh nay thu hep tp d liu c ban
    
       dbcc shrinkfile (templog, 'target size in MB')
       go
       -- lnh nay thu hep tp nht ky, xem oan cui cung.
    						
Mt li th cua DBCC SHRINKFILE la no co th giam kich thc cua tp n kich thc nho hn kich thc ban u. Ban co th a ra DBCC SHRINKFILE trn bt ky tp d liu hoc nht ky nao. Mt han ch cua DBCC SHRINKFILE la ban khng th lam cho c s d liu nho hn kich thc cua c s d liu mu.

Trong SQL Server 7.0, thu hep nht ky giao dich la mt hoat ng bi tri hoan va ban phai a ra vic ct xen va sao lu nht ky tr giup hoat ng thu hep trong c s d liu. Tuy nhin, theo mc inh, tempdb co tuy chon trunc log on chkpt c t la BT; do o, ban khng cn a ra vic ct xen nht ky cho c s d liu o. bit thm thng tin v cach thu hep nht ky giao dich c s d liu trong SQL Server 7.0, hay bm vao s bai vit bn di xem bai vit trong C s Kin thc Microsoft:
256650 INF: Cach Thu hep Nht ky Giao dich SQL Server 7.0

Anh hng cua vic Thc thi DBCC SHRINKDATABASE hoc DBCCSHRINKFILE Trong khi Tempdb ang c S dung

Nu tempdb ang c s dung va ban c gng thu hep bng cach s dung lnh DBCC SHRINKDATABASE hoc DBCC SHRINKFILE, ban co th nhn c nhiu li khng nht quan tng t vi loai sau va hoat ng thu hep co th tht bai:
May chu: Thng bao 2501, Mc 16, Trang thai 1, Dong 1 Khng th tim thy bang co tn '1525580473'. Kim tra sysobjects.
-hoc-
May chu: Thng bao 8909, Mc 16, Trang thai 1, Dong 0 Bang bi hong: ID i tng 1, ID chi muc 0, ID trang %S_PGID. PageId trong tiu trang = %S_PGID.
Mc du li 2501 co th khng phai la du hiu cua bt ky hong hoc nao trong tempdb, no khin hoat ng thu hep tht bai. Mt khac, li 8909 khng th biu thi hong hoc trong c s d liu tempdb. Khi ng lai SQL Server tao lai tempdb va xoa cac li khng nht quan. Tuy nhin, hay nh rng co th co cac ly do khac cho cac li hong hoc d liu vt ly nh li 8909 va nhng li bao gm s c h thng u vao/u ra phu.

THAM KHAO

Sach Trc tuyn v SQL Server; chu : "DBCC SHRINKFILE"; "DBCC SHRINKDATABASE"

Thu?c tnh

ID c?a bi: 307487 - L?n xem xt sau cng: 17 Thang Chin 2011 - Xem xt l?i: 2.0
p d?ng
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 64-bit Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Workgroup Edition
T? kha:
kbsqlsetup kbhowtomaster KB307487

Cung cp Phan hi

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com