tempdb is one of the 4 system databases that exists in all SQL Server instances. The other databases are master, model and msdb. In case of using Replication, a fifth system database named distribution will also exist. You can find all existing system databases in SQL Server Management Studio (SSMS) under the Databases / System Databases folder:
tempdb是所有SQL Server實例中存在的4個系統數據庫之一。 其他數據庫是master ， model和msdb 。 如果使用復制 ，則還將存在第五個名為distribution的系統數據庫。 您可以在SQL Server Management Studio（SSMS）中的“數據庫/系統數據庫”文件夾下找到所有現有系統數據庫：
數據庫的構成。 SQL Server uses tempdb database to store user objects, internal objects and version stores.
SQL Server使用tempdb數據庫存儲用戶對象，內部對象和版本存儲。
Version stores are used to store row versions generated from operations as online reindex, triggers or snapshot isolation.
版本存儲用于將操作生成的行版本存儲為在線重新索引，觸發器或快照隔離。
數據庫的限制訪問有哪些、 As the name indicates, internal objects are created internally by the SQL Server engine and are stored in tempdb database. These internal objects are:
顧名思義，內部對象由SQL Server引擎在內部創建，并存儲在tempdb數據庫中。 這些內部對象是：
User temporary objects can be tables, stored procedures, table variables, the value returned from a table valued function or the mapping index for online clustered index build with the SORT_IN_TEMPDB option.
用戶臨時對象可以是表，存儲過程，表變量，從表值函數返回的值或使用SORT_IN_TEMPDB選項構建的在線聚集索引的映射索引。
mysql數據庫能存多大數據？ User objects can be defined as local temporary object by using a ‘#’ as prefix in the object name (e.g. #MyTempTable) or can be defined as global temporary objects by using the prefix ‘##’ before the object name (e.g. ##MyTempTable).
可以通過在對象名稱中使用前綴“＃”將用戶對象定義為本地臨時對象（例如，＃MyTempTable），也可以通過在對象名稱之前使用前綴“ ##”將用戶對象定義為全局臨時對象（例如，## MyTempTable）。
A local temporary object is destroyed when the scope where it was created, expires or terminates. The scope can be a stored procedure or a session.
當本地臨時對象的作用域在創建，過期或終止時會被銷毀。 范圍可以是存儲過程或會話。
數據庫候選碼是什么、 A global temporary object is only destroyed when all sessions that are using it expires or terminates.
全局臨時對象僅在使用它的所有會話都到期或終止時才被銷毀。
A tempdb database was deemed to be so important that in SQL Server 2016 it has his own configuration setup screen during the SQL Server engine installation:
tempdb數據庫被認為非常重要，以至于在SQL Server 2016中，在SQL Server引擎安裝期間，它具有自己的配置設置屏幕：
視圖是數據庫的？
As can be seen, the number of files is filled automatically with the number of logical processors and it is requiring own location for the data and log files. I will next describe these configuration settings.
可以看出，文件的數量自動用邏輯處理器的數量填充，并且它需要數據和日志文件自己的位置。 接下來，我將描述這些配置設置。
It is recommended to create a data file per logical processor for the tempdb database, until a maximum of 8 data files, meaning that if your server has more than 8 processors, you should not create more than 8 data files. This is only a best practice recommendation, though, and can be tweaked if necessary. Keep in mind that too many files may increase the cost of file switching, meaning increase the overhead and that is why recommendation is not to start with more than 8 data files.
數據庫數據文件。 建議為tempdb數據庫的每個邏輯處理器創建一個數據文件，直到最多8個數據文件為止；這意味著，如果您的服務器具有8個以上的處理器，則不應創建8個以上的數據文件。 但是，這只是最佳實踐建議，如有必要，可以進行調整。 請記住，太多文件可能會增加文件切換的成本，這意味著增加了開銷，這就是為什么建議不要從8個以上的數據文件開始的原因。
Each of the multiple data files should have the same size so the SQL Server engine can apply the proportional fill optimization procedure to reduce UP latch contention. This procedure is a mechanism that will guarantee that each data file is filled in proportion to the free space that is available in the file so that all of the files fill up at about the same time.
多個數據文件中的每個文件都應具有相同的大小，以便SQL Server引擎可以應用比例填充優化過程來減少UP閂鎖爭用。 此過程是一種機制，可以確保每個數據文件都按文件中可用空間的比例進行填充，以使所有文件幾乎同時填充。
Microsoft recommends to set an auto grow value for tempdb, but based on my own experience I will not recommend it, myself. My personal recommendation is to initially set the maximum size possible for the tempdb data files (divide the disk size by the number of data files) and immediately fill the disk so you can disable the auto grow to avoid any negative impact during the file grow process.
數據庫幾種， Microsoft建議為tempdb設置一個自動增長的值，但是根據我自己的經驗，我本人不推薦這樣做。 我個人的建議是首先為tempdb數據文件設置最大可能的大小（將磁盤大小除以數據文件的數量），然后立即填充磁盤，以便可以禁用自動增長，以避免在文件增長過程中產生任何負面影響。
Shrinking files is also not a recommended operation for a tempdb database since it might change database files sizes and impact negatively on the proportional fill optimization procedure because this mechanism needs to have the same size for all data files.
對于tempdb數據庫，也不建議縮小文件，因為它可能會更改數據庫文件的大小，并對比例填充優化過程產生負面影響，因為此機制需要所有數據文件的大小相同。
tempdb is one particular database that is more difficult to estimate the needed disk space. As showed in the tempdb usage section, it stores many kinds of objects and they are not so easy to estimate. Unfortunately, during all these years working with SQL Server I couldn’t yet find the magic formula but I would recommend to provide as much disk space you can with the minimum disk size being the same size as the largest table in the SQL Server instance. It is also wise to add some safety factor to prevent the database growth.
數據庫降序、 tempdb是一個特定的數據庫，很難估計所需的磁盤空間。 如tempdb用法部分中所示，它存儲了許多類型的對象，而且估計起來并不容易。 不幸的是，在使用SQL Server的所有這些年來，我一直找不到神奇的公式，但我建議您提供盡可能多的磁盤空間，最小磁盤大小應與SQL Server實例中最大表的大小相同。 添加一些安全因素以防止數據庫增長也是明智的。
Fortunately, for the tempdb transaction log file, it is easier to estimate the necessary size. The tempdb, being a database with the recovery model set to Simple, it will be minimally logged. You can use a rule of thumb to create the transaction log file based on 20%-30% of the data size and tweak it later if needed.
幸運的是，對于tempdb事務日志文件，可以更容易地估計所需的大小。 tempdb是恢復模型設置為Simple的數據庫，它將被最小化記錄。 您可以使用經驗法則基于20％-30％的數據大小創建事務日志文件，并在需要時對其進行調整。
The space in tempdb is vital for the health of the SQL Server instance. When running out of space the SQL Server instance may become unresponsive so it is always better to reserve more space than give less for the tempdb data and log files.
數據庫字段類型有哪些、 tempdb中的空間對于SQL Server實例的運行狀況至關重要。 當空間不足時，SQL Server實例可能會變得無響應，因此，與為tempdb數據和日志文件提供較少的空間相比，保留更多的空間總是更好。
It is also good to have dedicated disks for a tempdb database to avoid a situation where other database files to consume space that might be needed later for tempdb. SSD is supported since SQL Server 2012 and can be used to store database files and because of the particularities of tempdb, as such, it is, in my opinion, the best database candidate to be stored in a SSD drive to achieve better performance results.
為tempdb數據庫提供專用磁盤也很好，以避免其他數據庫文件占用tempdb以后可能需要的空間的情況。 從SQL Server 2012開始就支持SSD，并且由于tempdb的特殊性， SSD可以用于存儲數據庫文件，因此，在我看來，它是存儲在SSD驅動器中以獲得最佳性能結果的最佳數據庫候選者。
tempdb is created every time the SQL Server instance starts, meaning that any existing object will be lost after the database recreation.
每次SQL Server實例啟動時都會創建tempdb ，這意味著在重新創建數據庫后，任何現有對象都將丟失。
By default, all users that have access to the SQL Server instance can create objects and perform queries in tempdb database, although the connect permission can be revoked from an user as it is for a regular database.
默認情況下，盡管可以像常規數據庫一樣撤消用戶的連接權限，但是所有有權訪問SQL Server實例的用戶都可以在tempdb數據庫中創建對象并執行查詢。
As in any regular database, tempdb data and log files can be moved to another location if and when needed to. The following is an example for moving 2 data files and 1 transaction log file to a new location in disk T:
與在任何常規數據庫中一樣，如果需要，可以將tempdb數據和日志文件移動到另一個位置。 以下是將2個數據文件和1個事務日志文件移動到磁盤T中的新位置的示例：
?
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'T:\TempDB\Data\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = temp2, FILENAME = 'T:\TempDB\Data\tempdb_mssql_2.ndf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'T:\TempDB\Log\templog.ldf');Since tempdb is recreated every time the SQL Server instance restarts, there is no need to copy the current files to the new location. The only thing that it needs to be done to have these changes implemented is to perform a SQL Server service restart and then the new files will be created in the new location (folder structure should exist). After that, old tempdb data and log files can be deleted from old location.
由于tempdb每次重新啟動SQL Server實例時都會重新創建，因此無需將當前文件復制到新位置。 要實現這些更改，唯一需要做的就是重啟SQL Server服務，然后將在新位置創建新文件（文件夾結構應該存在）。 之后，可以從舊位置刪除舊的tempdb數據和日志文件。
tempdb has many restrictions and some are explained below.
tempdb有很多限制，下面將對一些限制進行說明。
tempdb is the only database that cannot be backed up, meaning of course, that it cannot be restored as well.
tempdb是唯一無法備份的數據庫，這當然意味著它也無法還原。
In SSMS you will not find the context menu for Backup and Restore on tempdb and if you try to execute the backup command you will receive the error ‘Backup and restore operations are not allowed on database tempdb’:
在SSMS中，您將無法在tempdb上找到“備份和還原”的上下文菜單，如果嘗試執行backup命令，則會收到錯誤消息“ 數據庫tempdb上不允許進行備份和還原操作 ”：
tempdb recovery model is set to Simple and cannot be changed. If you try to change it, you will receive the error ‘Option ‘RECOVERY’ cannot be set in database ‘tempdb’’:
tempdb恢復模型設置為“簡單”，無法更改。 如果嘗試更改它，將收到錯誤“ 無法在數據庫'tempdb'中設置選項'RECOVERY' '：
tempdb cannot be deleted. If you try to delete it, you will receive the error ‘Cannot drop the database ‘tempdb’ because it is a system database’:
tempdb無法刪除。 如果嘗試刪除它，將會收到錯誤“由于系統數據庫數據庫'tempdb'而無法刪除 '：
tempdb cannot be set to offline. If you try to do it you will receive the error ‘Option ‘OFFLINE’ cannot be set in database ‘tempdb’’:
tempdb無法設置為脫機。 如果嘗試這樣做，將會收到錯誤“ 無法在數據庫'tempdb'中設置選項'OFFLINE' '：
A DBCC CHECKALLOC or DBCC CHECKCATALOG cannot be run on a tempdb database:
不能在tempdb數據庫上運行DBCC CHECKALLOC或DBCC CHECKCATALOG：
There are some more restrictions that are good to be known:
還有其他一些眾所周知的限制：
Next articles in this series:
本系列的下一篇文章：
翻譯自: https://www.sqlshack.com/configuration-operations-restrictions-tempdb-sql-server-system-database/
版权声明：本站所有资料均为网友推荐收集整理而来,仅供学习和研究交流使用。
工作时间：8:00-18:00
客服电话
电子邮件
admin@qq.com
扫码二维码
获取最新动态
![[vijos1162]波浪数](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
