Maintain Temp.db

Source:www.mssqltips.com

 

FIX IT TEMP.DB

 

Saya mengamati ukuran dari SQL Server temp.db saya naik begitu besar sampai belasan GB. Dan setelah disusutkan menjadi sekitar 1 GB.

Solusi
The behavior described in the problem statement is SQL Server’s behavior by design. To elaborate, the tempdb size will reset itself to the last manually configured size when the SQL Server service is restarted. The tempdb database will increase in size due to auto-growth, but this last size is not retained after a SQL Server service restart.

Internally within SQL Server, the current tempdb database size and the last manually configured database size are stored in different system catalogs.

The current tempdb database size can be found by querying DMV tempdb.sys.database_files.
The last manually configured tempdb database size can be found by querying DMV sys.master_files.
The steps below simulate the scenario outlined in the problem statement on SQL Server 2016 SP1 Developer Edition.

Step 1 – Manually set tempdb database size
My initial tempdb configuration is two data files with 8 MB each and the transaction log file is 8 MB. This tempdb configuration is the result after an installation of SQL Server 2016. This configuration is treated as the last configured size unless the tempdb size is manually altered from SSMS or a T-SQL command.

The T-SQL command below will change the two tempdb database data files to 500MB each and the transaction log to 1GB.

USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N’temp2′, SIZE = 512000KB , FILEGROWTH = 1GB )
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N’tempdev’, SIZE = 512000KB , FILEGROWTH = 1GB )
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N’templog’, SIZE = 1048576KB , FILEGROWTH = 1GB )
GO
Step 2 – Check tempdb database size
Whether checking the database size from SSMS or querying the DMVs, it will report the same configured tempdb size.

tempdb
Querying DMV sys.database_files and sys.master_files will output the same result.

— configured size
SELECT
name, file_id, type_desc, size * 8 / 1024 [TempdbSizeInMB]
FROM sys.master_files
WHERE DB_NAME(database_id) = ‘tempdb’
ORDER BY type_desc DESC, file_id
GO

— current size
SELECT
name, file_id, type_desc, size * 8 / 1024 [TempdbSizeInMB]
FROM tempdb.sys.database_files
ORDER BY type_desc DESC, file_id
GO
tempdev
Step 3 – Fill up tempdb
The code below will consume about 9GB of space in the tempdb database which causes the tempdb data files to auto-grow.

CREATE TABLE #LargeTempTable (col1 char(3000) default ‘a’, col2 char(3000) default ‘b’)

SET NOCOUNT ON;
DECLARE @i INT = 1

BEGIN TRAN
WHILE @i <= 950000
BEGIN
INSERT INTO #LargeTempTable DEFAULT VALUES
SET @i += 1
END

COMMIT TRAN

DROP TABLE #LargeTempTable
Step 4 – Check tempdb size again
It is easy to use SSMS to check the current tempdb size. If you right click on tempdb and select Properties the following screen will open. The tempdb database properties page will show the current tempdb size as 4.6 GB for each of the two data files and 2 GB for the log file.

files
If you query DMV sys.databases_files, this also reports the current database size.

— current size
SELECT
name, file_id, type_desc, size * 8 / 1024 [TempdbSizeInMB]
FROM tempdb.sys.database_files
ORDER BY type_desc DESC, file_id
tempdev
To get the last manually configured tempdb database size, you need to query DMV sys.master_files. When the SQL Server service is restarted, the tempdb files will reset to these configured sizes.

Here is the query to get the sizes that will be used if tempdb is recreated.

— configured size
SELECT
name, file_id, type_desc, size * 8 / 1024 [TempdbSizeInMB]
FROM sys.master_files
WHERE DB_NAME(database_id) = ‘tempdb’
ORDER BY type_desc DESC, file_id
GO
This matches what we show in Step 2.

tempdev
Step 5 – Restart SQL Server service
After the SQL Server service is restarted, you will see the tempdb size will be reset to the last manually configured size specified in DMV sys.master_files.

tempdev
Summary
The tempdb database data files and transaction log size can be modified using SSMS or T-SQL commands. Whether the file sizes grow or are shrunk, the last manually configured size will be reflected in DMV sys.master_files.

It is essential to understand the difference between DMV reporting the current tempdb database size versus the last manually configured size. By comparing sys.master_files and sys.database_files, you can also easily tell how much tempdb has auto-grown since the last time SQL Server was started.

Iklan
Maintain Temp.db