原文: 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

我們推出了一系列的關于TempDB基礎，監控和問題調試的博文，也建議大家閱讀有開發和測試部門給出的白皮書 http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx

???TempDB基礎

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

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

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

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?字節，顯然插入的行沒有被記錄。

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的日志和其他用戶數據庫不同。將上面同樣的例子操作應用于用戶數據庫，日志記錄會成為這個樣子：

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

這就證明了，撤銷確實會被日志記錄。實驗時做了以下操作:

? 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

以下是輸出內容，你會發現日志的長度超過1000字節，事實上列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的管理