原文: SQL Server中tempdb的管理

?資料來自：

http://blogs.msdn.com/b/sqlserverstorageengine/archive/tags/tempdb/

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2008/12/21/tempdb-basics.aspx

我們推出了一系列的關(guān)于TempDB基礎(chǔ)，監(jiān)控和問題調(diào)試的博文，也建議大家閱讀有開發(fā)和測(cè)試部門給出的白皮書 http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx

???TempDB基礎(chǔ)

TempDB和其他用戶數(shù)據(jù)庫一樣以Model數(shù)據(jù)庫為模板創(chuàng)建，并且可以創(chuàng)建和查詢表格。與其他用戶數(shù)據(jù)庫不同的是，TempDB在每次SQL Server啟動(dòng)的時(shí)候都會(huì)被重新創(chuàng)建。這就意味著：

l??TempDB不需要恢復(fù)。因此它不必使用全備份或者大容量日志記錄恢復(fù)模型。事實(shí)上，TempDB是配置成簡(jiǎn)單恢復(fù)模型并且不能改變的。

l??雖然TempDB不需要被恢復(fù)，但不是說TempDB上的操作沒有日志記錄。SQL Server中每個(gè)操作都是在顯式或隱式（每個(gè)語句都是一個(gè)獨(dú)立的事務(wù)）的事務(wù)中完成，這個(gè)事務(wù)也包含了在TempDB對(duì)象上的操作。如果更新全局臨時(shí)表中的行然后回滾事務(wù)，是無法撤銷此更改的，除非我們記錄了更新之前的值。不過，也沒有必要記錄更新后的值，因?yàn)橹挥袛?shù)據(jù)庫恢復(fù)過程中需要重做事務(wù)時(shí)這個(gè)值才有用。像我之前提到的一樣，TempDB是不需要恢復(fù)的，于是沒有理由記錄更新后的值。再舉一個(gè)例子讓我們看看插入操作。在一個(gè)全局臨時(shí)表中的插入一行，實(shí)際值不會(huì)被記錄。因?yàn)镾QL Server不需要這個(gè)行值來完成撤消操作，僅僅需要適當(dāng)?shù)卦O(shè)置頁面中的偏移量，或者這個(gè)插入造成頁面被分配或者取消分配。總結(jié)起來，我們只需要記錄可能被撤銷的信息而不需要記錄可能被重做的信息。這一點(diǎn)關(guān)于日志記錄文件的變化是由SQLServer 2005開始的。更復(fù)雜的情況是，并不是TempDB中所有對(duì)象都被日志文件記錄。后文會(huì)詳細(xì)敘述。現(xiàn)在我們來看一個(gè)例子說明這一點(diǎn)

use ?tempdb

go

?

---------------

-- SETUP

---------------

drop ? table ?##t_tempdblog

go

?

-- create a global temp table. Notice, each row is

-- greater than > 1000. I did this so that we can easily

-- identify if the row is indeed logged.

create ? table ?##t_tempdblog ? ( c1? int , ?c2? char ( 1000 ))

go

?

-- load 10 rows into this table

declare ?@i? int

select ?@i? = ?0

while? ( @i? < ?10 )

begin

???? insert ? into ?##t_tempdblog? values

?????????? ( @i , ? replicate ? ( cast ? ( 'a' ? as ? varchar ), ?1000 ))

select ?@i? = ?@i + 1

end

?

--Now run the following command to see the log records

select ? top ?10 operation , context ,

?????? [log record fixed length] ,

????? [log record length]

from ? fn_dblog (null, ? null)

where ?allocunitname = 'dbo.##t_tempdblog'

order ? by ?[Log Record Length]? Desc

這是上述查詢的輸出。您將注意到的所有日志記錄都?< 100?字節(jié)，顯然插入的行沒有被記錄。

operation?????? context??? log record fixed length??? log record length

-----------------------------------------------------------------------

LOP_MODIFY_ROW? LCX_IAM??????????? 62?? ???????????????????88

LOP_MODIFY_ROW? LCX_IAM??????????? 62????????????????????? 88

LOP_FORMAT_PAGE LCX_HEAP?????????? 80????????????????????? 84

LOP_FORMAT_PAGE LCX_IAM??????????? 80????????????????????? 84

LOP_FORMAT_PAGE LCX_HEAP?????????? 80?????????? ???????????84

LOP_MODIFY_ROW? LCX_PFS??????????? 62????????????????????? 80

LOP_MODIFY_ROW? LCX_PFS??????????? 62????????????????????? 80

LOP_INSERT_ROWS LCX_HEAP?????????? 62????????????????????? 72

LOP_INSERT_ROWS LCX_HEAP?????????? 62?????????????????? ???72

LOP_INSERT_ROWS LCX_HEAP?????????? 62????????????????????? 72

于是，這就證明了TempDB的日志和其他用戶數(shù)據(jù)庫不同。將上面同樣的例子操作應(yīng)用于用戶數(shù)據(jù)庫，日志記錄會(huì)成為這個(gè)樣子：

operation???????????? context?? log record fixed length log record length

----------------------------------------------------------------------

LOP_INSERT_ROWS????? LCX_HEAP????????? 62????????????????????? 1104

LOP_INSERT_ROWS????? LCX_HEAP????????? 62????????????????????? 1104

LOP_INSERT_ROWS????? LCX_HEAP????????? 62????????????????????? 1104

LOP_INSERT_ROWS????? LCX_HEAP????? ????62????????????????????? 1104

LOP_INSERT_ROWS????? LCX_HEAP????????? 62????????????????????? 1104

LOP_INSERT_ROWS????? LCX_HEAP????????? 62????????????????????? 1104

LOP_INSERT_ROWS????? LCX_HEAP????????? 62????????????????????? 1104

LOP_INSERT_ROWS??? ??LCX_HEAP????????? 62????????????????????? 1104

LOP_INSERT_ROWS????? LCX_HEAP????????? 62????????????????????? 1101

LOP_INSERT_ROWS????? LCX_HEAP????????? 62????????????????????? 1104

這就證明了，撤銷確實(shí)會(huì)被日志記錄。實(shí)驗(yàn)時(shí)做了以下操作:

? update ?##t_tempdblog? set ?c2? =

???????????? replicate ? ( cast ? ( 'b' ? as ? varchar ), ?1000 )

之后，做這樣的查詢：

select ? top ?10 operation , context ,

?????? ?[log record fixed length] , ?[log record length]

from ? fn_dblog (null, ? null)

where ?allocunitname = 'dbo.##t_tempdblog'

order ? by ?[Log Record Length]? Desc

以下是輸出內(nèi)容，你會(huì)發(fā)現(xiàn)日志的長(zhǎng)度超過1000字節(jié)，事實(shí)上列C2更新之前的值被記錄了。

operation????????? context??? log record fixed length log record length

-----------------------------------------------------------------------

LOP_MODIFY_ROW???? LCX_HEAP?????? 62????????????????????? 1072

LOP_MODIFY_ROW???? LCX_HEAP?????? 62????????????????????? 1072

LOP_MODIFY_ROW???? LCX_HEAP?????? 62????????????????????? 1072

LOP_MODIFY_ROW???? LCX_HEAP?????? 62????????????????????? 1072

LOP_MODIFY_ROW???? LCX_HEAP?????? 62????????????????????? 1072

LOP_MODIFY_ROW???? LCX_HEAP?????? 62????????????????????? 1072

LOP_MODIFY_ROW???? LCX_HEAP?????? 62????????????????????? 1072

LOP_MODIFY_ROW???? LCX_HEAP?????? 62????????????????????? 1072

LOP_MODIFY_ROW???? LCX_HEAP?????? 62????????????????????? 1072

LOP_MODIFY_ROW???? LCX_HEAP?????? 62????????????????????? 1072

SQL Server中tempdb的管理