tempdb æ¯ä¸ä¸ªä¸´æ¶å·¥ä½åºãé¤å
¶ä»ç¨éå¤ï¼SQL Server è¿å° tempdb ç¨äºï¼
• æ¾å¼å建ç临æ¶è¡¨çåå¨ã
• ä¿åå¨æ¥è¯¢å¤çåæåºè¿ç¨ä¸å建çä¸é´ç»æçå·¥ä½è¡¨ã
• å
·ä½åçéæå
æ ã
SQL Server å¨ tempdb äºå¡æ¥å¿ä¸è®°å½çä¿¡æ¯åªè¶³å¤ç¨äºåæ»äºå¡ï¼èä¸è¶³ä»¥ç¨äºå¨æ°æ®åºæ
éæ¢å¤è¿ç¨ä¸éæ°æ§è¡äºå¡ãè¿ä¸ç¹ç¹æé«äº tempdb ä¸ INSERT è¯å¥çæ§è½ãå¦å¤ï¼ç±äºæ¯æ¬¡éæ°å¯å¨ SQL Server æ¶é½ä¼éæ°å建 tempdbï¼æ éè®°å½ç¨äºéæ°æ§è¡ä»»ä½äºå¡çä¿¡æ¯ãå æ¤ï¼æ²¡æä»»ä½è¦åæ»æåæ»çäºå¡ãå½ SQL Server å¯å¨æ¶ï¼éè¿ä½¿ç¨ model æ°æ®åºçå¯æ¬éæ°å建 tempdbï¼å¹¶å°å
¶é置为ä¸æ¬¡é
ç½®ç大å°ã
é»è®¤æ
åµä¸ï¼tempdb æ°æ®åºé
ç½®ä¸ºæ ¹æ®éè¦èªå¨å¢é¿ï¼å æ¤ï¼æ¤æ°æ®åºå¯è½æç»å¢é¿å°å¤§äºæéç大å°ãç®åå°éæ°å¯å¨ SQL Server ä¼å° tempdb ç大å°é置为ä¸æ¬¡é
ç½®ç大å°ãé
ç½®ç大å°æ¯ç¨æ件大å°æ´æ¹æä½ï¼å¦å¸¦æ MODIFY FILE é项ç ALTER DATABASE æè
DBCC SHRINKFILE è¯å¥ï¼è®¾ç½®çä¸æ¬¡æ¾å¼å¤§å°ãæ¬æ说ææ¨å¯ä»¥ç¨æ¥å° tempdb æ¶ç¼©å°å°äºå
¶é
ç½®ç大å°çä¸ç§æ¹æ³ã
æ¶ç¼© Tempdb çæ¹æ³ 1
æ¤æ¹æ³è¦æ±æ¨éæ°å¯å¨ SQL Serverã
1. åæ¢ SQL Serverãæå¼å½ä»¤æ示符ï¼ç¶åé®å
¥ä»¥ä¸å½ä»¤å¯å¨ SQL Serverï¼
sqlservr -c -f
-c å -f åæ°ä½¿ SQL Server 以æå°é
置模å¼å¯å¨ï¼è®©æ°æ®æ件ç tempdb 大å°ä¸º 1 MBï¼æ¥å¿æ件ç tempdb 为 0.5 MBã
注æï¼å¦æä½¿ç¨ SQL Server å½åå®ä¾ï¼å¿
é¡»åæ¢å°éå½çæ件夹 (Program Files/Microsoft SQL Server/MSSQL$instance name/Binn)ï¼å¹¶ä½¿ç¨ -s å¼å
³ (-s%instance_name%)ã
2. ç¨æ¥è¯¢åæå¨è¿æ¥å° SQL Serverï¼ç¶åè¿è¡ä¸å Transact-SQL å½ä»¤ï¼ ALTER DATABASE tempdb MODIFY FILE
(NAME = 'tempdev', SIZE = target_size_in_MB)
--Desired target size for the data file
ALTER DATABASE tempdb MODIFY FILE
(NAME = 'templog', SIZE = target_size_in_MB)
--Desired target size for the log file
3. éè¿å¨å½ä»¤æ示符çªå£ä¸æ Ctrl-C åæ¢ SQL Serverï¼å° SQL Server ä½ä¸ºæå¡éæ°å¯å¨ï¼ç¶åéªè¯ Tempdb.mdf å Templog.ldf æ件ç大å°ã
æ¤æ¹æ³çå±éæ¯å®åªè½å¯¹é»è®¤ç tempdb é»è¾æ件 tempdev å templog è¿è¡æä½ãå¦æå°å
¶ä»æ件添å å°äº tempdbï¼æ¨å¯ä»¥å¨å° SQL Server ä½ä¸ºæå¡éæ°å¯å¨åæ¶ç¼©å®ä»¬ãå¨å¯å¨è¿ç¨ä¸å°éæ°å建ææ tempdb æ件ï¼å æ¤ï¼å®ä»¬æ¯ç©ºç并å¯å é¤ãè¦å é¤ tempdb ä¸çå
¶ä»æ件ï¼è¯·ä½¿ç¨å¸¦æ REMOVE FILE é项ç ALTER DATABASE å½ä»¤ã
æ¶ç¼© Tempdb çæ¹æ³ 2
ä½¿ç¨ DBCC SHRINKDATABASE å½ä»¤å° tempdb æ°æ®åºä½ä¸ºæ´ä½æ¶ç¼©ãDBCC SHRINKDATABASE æ¥æ¶åæ° target_percentï¼è¯¥åæ°æ¯æ°æ®åºæ¶ç¼©åæ°æ®åºæ件ä¸å©ä½å¯ç¨ç©ºé´çæéç¾åæ¯ãå¦æä½¿ç¨ DBCC SHRINKDATABASEï¼å¯è½å¿
é¡»éæ°å¯å¨ SQL Serverã
éè¦è¯´æï¼å¦æè¿è¡ DBCC SHRINKDATABASEï¼å tempdb æ°æ®åºä¸è½æ£å¨åçå
¶ä»æ´»å¨ãè¦ç¡®ä¿å¨è¿è¡ DBCC SHRINKDATABASE æ¶å
¶ä»è¿ç¨æ æ³ä½¿ç¨tempdbï¼å¿
须以åç¨æ·æ¨¡å¼å¯å¨ SQL Serverãæå
³æ´å¤ä¿¡æ¯ï¼è¯·åèæ¬æçå¨ä½¿ç¨ Tempdb æ¶æ§è¡ DBCC SHRINKDATABASE æ DBCCSHRINKFILE çç»æ ä¸èã
1. éè¿ä½¿ç¨ sp_spaceused åå¨è¿ç¨ç¡®å® tempdb ä¸å½å使ç¨ç空é´ãç¶åï¼è®¡ç®å©ä½å¯ç¨ç©ºé´çç¾åæ¯ï¼å®å°ç¨ä½ DBCC SHRINKDATABASE çåæ°ï¼è¯¥è®¡ç®æ¯åºäºæéæ°æ®åºå¤§å°è¿è¡çã
注æï¼å¨æäºæ
åµä¸ï¼æ¨å¯è½å¿
é¡»æ§è¡ sp_spaceused @updateusage=true æ¥éæ°è®¡ç®ä½¿ç¨ç空é´åè·å¾æ´æ°çæ¥åãæå
³ sp_spaceused åå¨è¿ç¨çæ´å¤ä¿¡æ¯ï¼è¯·åè SQL Server èæºä¸ä¹¦ã
请èè以ä¸ç¤ºä¾ï¼
åå® tempdb æ两个æ件ï¼ä¸»æ°æ®æ件 (Tempdb.mdf) åæ¥å¿æ件 (Tempdb.ldf)ï¼å
¶å¤§å°åå«ä¸º 100 MB å 30 MBãåå® sp_spaceused æ¥å主æ°æ®æ件å
å« 60 MB çæ°æ®ãè¿åå®æ¨è¦å°ä¸»æ°æ®æ件æ¶ç¼©å° 80 MBã计ç®æ¶ç¼©åå©ä½å¯ç¨ç©ºé´çæéç¾åæ¯ï¼å³ 80 MB - 60 MB = 20 MBãç°å¨ï¼ç¨ 20 MB é¤ä»¥ 80 MB = 25%ï¼è¿å°±æ¯æ¨ç target_percentãäºå¡æ¥å¿æ件å°æ®æ¤è¿è¡æ¶ç¼©ï¼ä»èå¨æ°æ®åºæ¶ç¼©åå©ä¸ 25% å³ 20 MB çå¯ç¨ç©ºé´ã
2. ç¨æ¥è¯¢åæå¨è¿æ¥å° SQL Serverï¼ç¶åè¿è¡ä¸å Transact-SQL å½ä»¤ï¼ dbcc shrinkdatabase (tempdb, 'target percent')
-- This command shrinks the tempdb database as a whole
对 tempdb æ°æ®åºä½¿ç¨ DBCC SHRINKDATABASE å½ä»¤å
·æå±éæ§ãæ°æ®æ件åæ¥å¿æ件çç®æ 大å°ä¸è½å°äºå建æ°æ®åºæ¶æå®ç大å°ï¼ä¹ä¸è½å°äºç¨æ件大å°æ´æ¹æä½ï¼å¦å¸¦æ MODIFY FILE é项ç ALTER DATABASE å½ä»¤æ DBCC SHRINKFILE å½ä»¤ï¼æ¾å¼è®¾ç½®çä¸æ¬¡å¤§å°ãDBCC SHRINKDATABASE çå¦ä¸ä¸ªéå¶æ¯target_percentage åæ°ç计ç®åå®å¯¹å½å使ç¨ç空é´çä¾èµã
温馨提示:答案为网友推荐,仅供参考