Can I change tempdb size to a specific value and can I shrink it during our daily working hours on our production database

Question

Can I change tempdb size to a specific value and can I shrink it during our daily working hours on our production database ?. Please tell me if it has bad effect on a production database.

My database server has 4 cores and I'm using SQL Server 2008 R2 SP3. I asked this question because I noted that its size is growing suddenly. I'm afraid that it may make the hard drive full.

I frequently run below query to see if there is page contention or not. I always see an empty result, which indicates that there is no page contention, therefore I did not add additional data file to tempdb.

(SELECT session_id AS [Session],
wait_duration_ms AS [WaitTime(ms)],
resource_description AS [Type]
FROM sys.dm_os_waiting_tasks
WHERE wait_type LIKE 'PAGELATCH_%'
AND (resource_description LIKE '2:%:1'
OR resource_description LIKE '2:%:2'
OR resource_description LIKE '2:%:3')

My tempdb is located on the local C drive. I have only one data file for it.

Answer 1

Multiple things to note here. tempdb is supposed to have same number of data file as many cores are available on the server(up to 8). So, in your case it is supposed to be 4 data files and they are should be of same size so that you won't have allocation contention. This is addressed by KB 2154845.

Below is explained by Mr. Brent Ozar for configuring tempdb:

configure one volume/drive for TempDB. Divide the total space by 9, and that’s your size number. Create 8 equally sized data files and one log file, each that size. Presto, the drive is full and your TempDB is configured for easy performance.

I would stick to below script to find my bottlenecks for wait statistics:

WITH [Waits] AS
(SELECT
[wait_type],
[wait_time_ms] / 1000.0 AS [WaitS],
([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
[signal_wait_time_ms] / 1000.0 AS [SignalS],
[waiting_tasks_count] AS [WaitCount],
100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
FROM sys.dm_os_wait_stats
WHERE [wait_type] NOT IN (
N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR',
N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH',
N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE',
N'CHKPT', N'CLR_AUTO_EVENT',
N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE',
N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE',
N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD',
N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE',
N'EXECSYNC', N'FSAGENT',
N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',
N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE',
N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE',
N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP',
N'LOGMGR_QUEUE', N'ONDEMAND_TASK_QUEUE',
N'PWAIT_ALL_COMPONENTS_INITIALIZED',
N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE',
N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH',
N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP',
N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY',
N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP',
N'SLEEP_SYSTEMTASK', N'SLEEP_TASK',
N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT',
N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH',
N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS',
N'WAITFOR', N'WAITFOR_TASKSHUTDOWN',
N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN',
N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT')
AND [waiting_tasks_count] > 0
)
SELECT
MAX ([W1].[wait_type]) AS [WaitType],
CAST (MAX ([W1].[WaitS]) AS DECIMAL (16,2)) AS [Wait_S],
CAST (MAX ([W1].[ResourceS]) AS DECIMAL (16,2)) AS [Resource_S],
CAST (MAX ([W1].[SignalS]) AS DECIMAL (16,2)) AS [Signal_S],
MAX ([W1].[WaitCount]) AS [WaitCount],
CAST (MAX ([W1].[Percentage]) AS DECIMAL (5,2)) AS [Percentage],
CAST ((MAX ([W1].[WaitS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgWait_S],
CAST ((MAX ([W1].[ResourceS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgRes_S],
CAST ((MAX ([W1].[SignalS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgSig_S]
FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2]
ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum]
HAVING SUM ([W2].[Percentage]) - MAX ([W1].[Percentage]) < 95; -- percentage threshold
GO

I am not very sure if this will work on SQL Server 2008R2 as I don't have any environment with this version.

Regarding your main issue i.e. filling up of space at C drive and your only device of tempdb which is stored at the same drive can make your database corrupt as well as your operating system will stop working if all the space is used. So, I would suggest you to add three more data file of equal size and keep them at different drive and at the same time disable autogrowth option from the existing device as shown below:

Check how much of data is really used in tempdb and try to shrink that to an extent(its not a good option in normal scenario however you don't have many option since we don't want to reboot service now).

After above steps are done and you manage to survive during peak hour, you may plan to get downtime of your system for few minutes and would need to reboot your SQL service in order to move 1st data file from C drive to other drive.

You may use commands given at below links and follow steps to move data/log file of tempdb:

https://blog.sqlauthority.com/2016/06/26/moving-tempdb-new-drive-interview-question-week-077/

https://www.brentozar.com/archive/2017/11/move-tempdb-another-drive-folder/

How to Move TempDB Files to a Different Drive or Folder?

Hope above helps.

Answer 2

Your tempDB files get emptied every time you restart the MSSQL service so you CAN change the size but it's probably not a good practice. You really need to tell your Ops or management team that you need a separate drive and it can't exist on the C drive because it's a outage waiting to happen.

Ideally, you should have your tempdb files on a separate drive and not on the same drive as your OS. You want drive for OS, and ideally, different drives for your log files, mdf files and temp files.

At minimum, you should have all database, log and tempdb files off of the OS drive because if a log fills or a database grows, and it fills up the drive, it crashes.

Answer 3

Can I change tempdb size to a specific value and can I shrink it during our daily working hours on our production database ?. Please tell me if it has bad affect on production database

Yes you can increase tempdb size by adding files or by increasing the size of existing files, it will not require server restart so it's safe.

You want to have your tempdb files of equal size otherwise server will write mostly to the largest file.

It's not easy to shrink tempdb on the working server. It just won't shrink until you clear some caches that may not be a good idea, more on this here: Shrinking tempdb without restarting SQL Server

Some cite from the article:

This means that ad-hoc queries and stored procedures will have to recompile the next time you run them. Although this happens automatically, you may notice a significant performance decrease the first few times you run your procedures.

---

I noted that it's size is growing suddenly.

This means that your workload requires more tempdb space. So you should find the optimal tempdb size when it stops to grow and configure its size to the size you've found. This way the next time server restarts its tempdb will have the correct size and will not take time to grow.

If you suspect that your tempdb size is too large you want to monitor what queries spills to tempdb (hash and sort do this), you can find this events in the default trace.

Answer 4

Thanks to all for information you have given me. I executed the below steps to protect our production database:

First I moved tempdb to a new location

From Move System Databases:

1. Determine the logical file names of the tempdb database and their current location on the disk

   SELECT name, physical_name AS CurrentLocation  
   FROM sys.master_files  
   WHERE database_id = DB_ID(N'tempdb');  
   

2. Change the location of each file by using ALTER DATABASE.

   USE master;  
   GO  
   ALTER DATABASE tempdb   
   MODIFY FILE (NAME = tempdev, FILENAME = 'D:\Temp_Data\tempdb.mdf');  
   GO  
   ALTER DATABASE tempdb   
   MODIFY FILE (NAME = templog, FILENAME = 'D:\Temp_Data\templog.ldf');  
   

3. Stop and restart the instance of SQL Server.

4. Verify the file change.

   SELECT name, physical_name AS CurrentLocation, state_desc  
   FROM sys.master_files  
   WHERE database_id = DB_ID(N'tempdb');  
   

5. Delete the tempdb.mdf and templog.ldf files from the original location.

Second I added new 3 data files to meet our database server cores

From Adding Additional Data Files To The TempDB Database In SQL Server by Jack Worthen:

ALTER DATABASE tempdb MODIFY FILE (NAME='tempdev', SIZE=1GB, FILEGROWTH = 50);
GO

===========================================
/* Adding three additional files */
===========================================
USE [master];
GO
ALTER DATABASE [tempdb] ADD FILE 
    (NAME = N'tempdev2', FILENAME = N'D:\Temp_Data\tempdev2.ndf' , SIZE = 1GB , FILEGROWTH = 50);
ALTER DATABASE [tempdb] ADD FILE 
    (NAME = N'tempdev3', FILENAME = N'D:\Temp_Data\tempdev3.ndf' , SIZE = 1GB , FILEGROWTH = 50);
ALTER DATABASE [tempdb] ADD FILE 
    (NAME = N'tempdev4', FILENAME = N'D:\Temp_Data\tempdev4.ndf' , SIZE = 1GB , FILEGROWTH = 50);