0

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.

  • 2
    Aside from the question, please move it from your c:\ drive asap. If your c:\ drive is full your system will crash. Additionally you could get a corrupted database. – Randi Vertongen Nov 20 '19 at 12:01
  • can i move it while working hours or it must done on off peak – Ayman Farouk Nov 20 '19 at 12:03
  • 1
    No, You can't move however you can add additional file of same size of data file at different drive and disable the growth option from the device which is stored at C drive so, it won't grow anymore and your Operating system drive will have some breather as well as your database. – Learning_DBAdmin Nov 20 '19 at 12:06
  • 1
    You can move it, but you'll have to restart your SQL Service. If this is a production instance we're talking about, consider also moving your data/log files to different drives. Your C should only contain the installation, no live data. (If your C drives fills up you're in for a bad day) Check this link for help in moving the files : blog.sqlauthority.com/2016/06/26/… – Yannick Liekens Nov 20 '19 at 12:08
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:

Tempdb disable autogrowth

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.

0

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.

0

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:

enter image description here

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.

0

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);
  • 1
    Very much sums up what we had stated in the answer. Glad to hear it worked. You should accept one of the answer which was most useful for you so that it could be useful for others if they face similar issue. – Learning_DBAdmin Nov 21 '19 at 11:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.