How to recycle tempdb without Rebooting Sql Server

_{Tuesday, May 21, 2013 6:35 PM}

We have an App server where Microsoft reporting services (report Builder 2005) is hosted and the backend Database server which is used by the reporting services.

The clients create very complex reports (usually towards month end) and run against the database causing the tempdb grow expenentially, leading to perfromance degradation and worst case space issues.Our only solution is to reboot the server.

There should be many systems which sould be having similar scenarios like mine.Can you please advise how to handle the scenario.

usually in my database tempdb should be below 15GB... but it had grown to 60GB in some instances.

_{Wednesday, May 22, 2013 9:09 AM ✅Answered}

I think your primary focus should  not be on shrinking tempDB but how to efficiently use it after reading your scenario.There is limit to which you can shrink your tempdb beyond than u cannot and it depends on query running on ur system.

Simply tempdb can become overloaded in terms of space available and excessive DDL and DML operations.

You can download whitepaer from below link for tempdb management

http://msdn.microsoft.com/en-us/library/dd672789(v=sql.100).aspx.

Below query will help u in monitoring Tempdb space.refer the doc in link.queries having group by,having,order by clause will fill up ur tempdb more.Avoid using excessive temp tables

Select

SUM (user_object_reserved_page_count)*8 as user_objects_kb,

SUM (internal_object_reserved_page_count)*8 as internal_objects_kb,

SUM (version_store_reserved_page_count)*8 as version_store_kb,

SUM (unallocated_extent_page_count)*8 as freespace_kb

From sys.dm_db_file_space_usage

Where database_id = 2

Soldier..Sir we are surrounded from all sides by enemy.. Major: Good, we can attack in any direction Thats attitude.. Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

_{Wednesday, May 22, 2013 9:42 AM ✅Answered | 2 votes}

Hallo Canadags,

I' wondering WHY you want to shrink tempdb. From my point of view it is nonsens becaus the workloads seems to NEED the allocated space. If you shrink TEMPDB (wether by reboot or DBCC SHRINKDB) your action will cause the next bottleneck because SQL Server has to increase and allocate the required space again when users run their reports.

My recommendation:

- give tempdb the required storage

- monitor tempdb for contention and distribute the required storage to multiple tempdb files
(e.g. 60 GB / 8 = 7.500 MB per file for 8 tempdb files)

can you please post the result of the following query running against tempdb:

SELECT mf.name,
        vf.NumberReads,
        vf.BytesRead,
        vf.IoStallReadMS,
        vf.NumberWrites,
        vf.BytesWritten,
        vf.IoStallWriteMS,
        vf.IoStallMS,
        vf.BytesOnDisk / POWER(1024, 2) AS  MB_Size
FROM    sys.fn_virtualfilestats(2, NULL) vf INNER JOIN sys.master_files mf
        ON (
                vf.DbId = mf.database_id AND
                vf.FileId = mf.file_id
            );

Uwe Ricken

MCSE - SQL Server 2012
MCSA - SQL Server 2012
MCITP Database Administrator 2005
MCITP Database Administrator 2008
MCITP Microsoft SQL Server 2008, Database Development

db Berater GmbH
http://www-db-berater.de
SQL Server Blog (german only)

_{Wednesday, May 22, 2013 8:55 AM}

Check out a similar thread on the same issue, where more information has been posted: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/9e194d99-381e-4ec7-ac55-58f03bdb759b

Best Regards,
Dattatrey Sindol (Datta)
**| **Blog ** | **Facebook ** | Twitter ** |
"Please mark the post as answered if it solves your problem."

_{Wednesday, May 22, 2013 9:23 AM}

Try with Shrink temp DB

ShrinkDatabase: Shrink Database (or) shrink files will be always in lower chunks… I always prefer to shrink by tempdb database files only instead of shrinking tempDB database…

IMPORTANT: One more important point is before doing shrink operations please check the OPEN TRAN on tempDB. If an active tran existed it will not allow you to shrink the tempDB

USE [tempdb]
GO
DBCC SHRINKDATABASE (N'tempdb', 5 ) --> here the value in percentage
GO

Please don’t try to shrink the entire DB, for example if the tempDB is in 50 gigs if you are trying to shrink to 5%. There might not be sufficient space on disk to perform this action

ShrinkFile:

USE [tempdb]
GO
DBCC SHRINKFILE (N'tempdev' , size_in_MB)
GO

If you are not able to shrink the TEMPDB files because of any worktable (or) any objects are occupied at the end of TEMPDB file.

Please execute the below command:

Use tempDB
go
DBCC FREEPROCCACHE

Then try to shrink the file:
USE [tempdb]
GO
DBCC SHRINKFILE (N'tempdev' , size_in_MB)
GO

 Thanks,

Thanks, Satish Kumar. Please mark as this post as answered if my anser helps you to resolves your issue :)

_{Wednesday, May 22, 2013 10:38 AM}

Hi,

Please  use following steps.

USE [tempdb]

--Check Database File Size before shrink

SELECT name, size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB

FROM sys.database_files

--Shrink tempdb Log File (.ldf)

DBCC SHRINKFILE(tempdb_log,1)

BACKUP LOG tempdb WITH TRUNCATE_ONLY

DBCC SHRINKFILE(tempdb_log,1)

--Shrink tempdb Data File (.mdf)

DBCC SHRINKFILE(tempdb,1)

BACKUP LOG tempdb WITH TRUNCATE_ONLY

DBCC SHRINKFILE(tempdb,1)

--Check Database File Size after shrink

SELECT name, size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB

FROM sys.database_files

Mak

_{Wednesday, May 22, 2013 10:59 AM | 1 vote}

Hallo Mak,

with deepest respect - but what you want to do is nonsens!

1. Why do you want to shrink the log file to 1 MB!
The impact will be dramatical depending on having workloads because the ldf cannot be increased with "instant file initialization"
http://msdn.microsoft.com/en-us/library/ms175935(v=sql.105).aspx

http://www.sqlskills.com/blogs/paul/misconceptions-around-instant-file-initialization/

**2. You cannot backup a log file of a database which is running in SIMPLE mode!
Your scneario will return an error 4208
**http://msdn.microsoft.com/en-us/library/ms191164(v=sql.105).aspx3

3. IF that step would be possible you would decrease the performance of sql server enormous OR you won't have any success

- if any open transaction is at the end of the db you can run as much BACKUP LOG as you want - the filesize will not be decreased

what will happen with - possible - other tempdb-files?
Did your scenario consider the configuration of tempdb?

I'm afraid about such recommendations - independent of the possibility of the technical implementation!

Uwe Ricken

MCSE - SQL Server 2012
MCSA - SQL Server 2012
MCITP Database Administrator 2005
MCITP Database Administrator 2008
MCITP Microsoft SQL Server 2008, Database Development

db Berater GmbH
http://www-db-berater.de
SQL Server Blog (german only)

_{Wednesday, May 22, 2013 11:13 AM}

My suggestion would be to find the queries causing Tempdb to grow and try to optimize them.

1. Look for huge sort operations, these spill to Tempdb when there is not enough space in memory to accomodate the sort - An appropriately placed index can eliminate the need for a sort operation

2. Look for Hash operations, usually when these spill to Tempdb, it's a case of missing/outdated statistics. A simple update of stats on the join columns can make a huge difference here.

There are lots more operations that require Tempdb

My opinion, try to optimize the queries involved, that way you increase your performance and use less Tempdb space.

Also, make sure that Tempdb has been configured properly (Files, Space etc..) - You could find yourself dealing with page allocation issues as well.

_{Wednesday, May 22, 2013 11:18 AM | 1 vote}

Hi Canadags

1. There is no way to recycle tempdb except rebooting

2. Yo can shrink tempdb but of course (if it have some free space left inside it ) But will cause performance issue.

3. If your tempdb is growing so much , confirm you are not using snapshot isolation level (DBCC useroptions) --> snapshot isolation level cause increase in data file size.

4. For longer run you can tune your complex queries to run in small batch to avoid tempdb growing so much.

Thanks

Saurabh Sinha

http://saurabhsinhainblogs.blogspot.in/

Please click the Mark as answer button and vote as helpful if this reply solves your problem

_{Wednesday, May 22, 2013 12:58 PM}

Thank you Uwe for your recommendations.

I enquired with my DBA the possibility of splitting up the temp db as you had recommended and his reply was that it will only help if the files are spread over seperate disks (in our case we have only one disk where temp db resides)

Below is the result of the Query you had supplied

_{Wednesday, May 22, 2013 1:18 PM}

Hi Saurabh

  Thank you for your post.Snapshot isolation level is turned off on the database. In my setup users/ clients have the freedom to frame their own reports using report builder.So in reality I do not have control on the queries being created , as these are created by these reports from clients. That is my concern here - limited control over the query being created... say if it is against the transaction table with not effective filter.. (It happens often) temp db size increases drastically.

_{Wednesday, May 22, 2013 1:20 PM}

Perhaps your perspective needs an adjustment?  Your question should be:  how can I charge clients for tempdb usage (or overage)? As the others have already indicated, the primary problem is likely caused by the (lack of) report writing skills of your clients.  Given the appropriate incentive, I'm sure those skills would improve (or you would be compensated for maintaining a larger tempdb). 

As an aside - why exactly do you care about the size of tempdb?  It will be whatever it needs to be.  As long as it does not continue to increase in size until it consumes an entire disk (or its allotment), why does it matter? Uwe has posted some very good recommendations.  Perhaps a larger issue is that your organization is trying provide a service without the appropriate resources.  Being too cheap will eventually bite you - hard.

_{Wednesday, May 22, 2013 1:37 PM}

As others have said, you should not be regularly shrinking tempdb.  tempdb is growing because it needs the space.  Growing the database is expensive.  You don't want it constantly growing, shrinking, growing.  Also, a large size of tempdb does not adversely affect performance.

You need to look into why it is growing so large, and see if you can rewrite the queries which are causing the issue.

Please see:

http://technet.microsoft.com/en-us/library/cc966545.aspx

_{Wednesday, May 22, 2013 1:43 PM}

Thank you Uwe for your recommendations.

I enquired with my DBA the possibility of splitting up the temp db as you had recommended and his reply was that it will only help if the files are spread over seperate disks (in our case we have only one disk where temp db resides)

Below is the result of the Query you had supplied

Expand table

name	NumberReads	BytesRead	IoStallReadMS	NumberWrites	BytesWritten	IoStallWriteMS	IoStallMS	MB_Size
tempdev	1994019	126663901184	31276928	2123715	144565239808	108878827	140155755	15000
templog	48	2183168	374	174017	10685135872	1701705	1702079	3500

Even if it's one one disk, tempdb needs to be split into multiple files - How many files depends on the system.

Remember that if you have only one tempdb file and a high rate of temporary objects being created and destroyed, you will run into Page Allocation issues......this can cause major performance issues on your system

Each tempdb file comes with it's own GAM/SGAM/PFS pages...

_{Wednesday, May 22, 2013 1:46 PM}

Hi Scott,

  Our free disck space is limited 80GB (on the drive where db resides) out of total 250 GB. So in the event temp db size increases to 50GB or above , it will put the system at risk.We have experiende a degradation of performance when tempfile size grows beyonds say 50 GB eg: reports execution getting slower(I am yet to find the reason for it) .Normal tempdb size we expect is 20Gb or below.

Clients that we are talking about are non technical - finance people. We give suggestions when they have some issues in report building, but we cannot monitor all the reports they will be creating.Someone in their side at some point of time will end up creating a report querying the transaction table / sales  tables without proper filter... which in turn have a balooning effect on the temp db. I agree with your point that client needs to update their report writing skills ,I will certainly give this as the suggestion.But for the moment I have to do some kind of prevention measure at the database side.

_{Wednesday, May 22, 2013 1:55 PM}

Hi,
Short of doing what everyone else has suggested, e.g. get the clients to change their mindset and be more conservative with their use of temp objects, etc. you don't have any options other than to add more disk, or re-structure TempDB layout across volumes to "increase" the space available to it

Thanks, Andrew

_{Wednesday, May 22, 2013 2:04 PM}

Out of interest, what are among your top waits on the SQL Server?

Run this:

SELECT TOP 15

wait_type ,

wait_time_ms ,

signal_wait_time_ms ,

wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms ,

100.0 * wait_time_ms / SUM(wait_time_ms) OVER ( )

AS percent_total_waits ,

100.0 * signal_wait_time_ms / SUM(signal_wait_time_ms) OVER ( )

AS percent_total_signal_waits ,

100.0 * ( wait_time_ms - signal_wait_time_ms )

/ SUM(wait_time_ms) OVER ( ) AS percent_total_resource_waits

FROM sys.dm_os_wait_stats

WHERE wait_time_ms > 0 -- remove zero wait_time

AND wait_type NOT IN -- filter out additional irrelevant waits

( 'SLEEP_TASK', 'BROKER_TASK_STOP', 'BROKER_TO_FLUSH',

'SQLTRACE_BUFFER_FLUSH','CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT',

'LAZYWRITER_SLEEP', 'SLEEP_SYSTEMTASK', 'SLEEP_BPOOL_FLUSH',

'BROKER_EVENTHANDLER', 'XE_DISPATCHER_WAIT', 'FT_IFTSHC_MUTEX',

'CHECKPOINT_QUEUE', 'FT_IFTS_SCHEDULER_IDLE_WAIT',

'BROKER_TRANSMITTER', 'FT_IFTSHC_MUTEX', 'KSOURCE_WAKEUP',

'LAZYWRITER_SLEEP', 'LOGMGR_QUEUE', 'ONDEMAND_TASK_QUEUE',

'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BAD_PAGE_PROCESS',

'DBMIRROR_EVENTS_QUEUE', 'BROKER_RECEIVE_WAITFOR',

'PREEMPTIVE_OS_GETPROCADDRESS', 'PREEMPTIVE_OS_AUTHENTICATIONOPS',

'WAITFOR', 'DISPATCHER_QUEUE_SEMAPHORE', 'XE_DISPATCHER_JOIN',

'RESOURCE_QUEUE' )

ORDER BY wait_time_ms DESC

_{Wednesday, May 22, 2013 2:39 PM}

Hi SQL24,

Here is the result

_{Wednesday, May 22, 2013 2:58 PM}

Definate latch contention - This requires further investigation, download the following whitepaper: http://www.microsoft.com/en-za/download/details.aspx?id=26665

As for the high CXPacket, it's not immediately indicative of an issue - It usually just means that some queries are running in parallel,are you running parallelism on your instance?

_{Wednesday, May 22, 2013 5:14 PM}

The best thing to do in your situation is to disable the autogrow and set a fixed size to an appropriate size.

Then bad queries will recieve an error "cannot allocate tempdb space" and fail, instead of filling up the hard drive and affecting everyone.

_{Wednesday, May 22, 2013 5:15 PM}

Yes , Queries will be / are split to multiple threads hence CXPACKET wait, afetr a configurable time.Does this have any impact on the tempdb usage

_{Wednesday, May 22, 2013 10:02 PM}

  Our free disck space is limited 80GB (on the drive where db resides) out of total 250 GB. So in the event temp db size increases to 50GB or above , it will put the system at risk.We have experiende a degradation of performance when tempfile size grows beyonds say 50 GB eg: reports execution getting slower(I am yet to find the reason for it) .Normal tempdb size we expect is 20Gb or below.

I agree with Andrew. If you want to continue to provide service to your users: buy more disk.

If you are less service-oriented to the route Tom suggested: cap the size of tempdb.

But in no situation shrinking tempdb on a regular basis is defensible.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

_{Thursday, May 23, 2013 6:08 AM}

Yes , Queries will be / are split to multiple threads hence CXPACKET wait, afetr a configurable time.Does this have any impact on the tempdb usage

As far as I know, parallelism shouldn't affect tempdb.

_{Thursday, May 23, 2013 1:35 PM}

Hi Erland,

  Thank you for your reply. Our current temporary solution in place is sending an alert to DBA when tempdb grows beyond 40GB.During off peak hours they will restart the server.Is this an advisable step.

_{Thursday, May 23, 2013 1:39 PM}

Hi Tom,

   Thank you for your suggestion.If we make a cap, the problem is that temp db may fill up during the business peak hours...which will result service unavailable issues .. SEV1 tickets etc.. which will put us in a bad light.. But on the other hand I dont have much options  as well.

_{Thursday, May 23, 2013 1:52 PM}

  Thank you for your reply. Our current temporary solution in place is sending an alert to DBA when tempdb grows beyond 40GB.During off peak hours they will restart the server.Is this an advisable step.

In one word: no. Please re-read the responses in this thread. Shrinking files that will grow again is just plain bad. If you need that big tempdb - keep it that big.

Louis Jordan has a good song on this theme: What's the Use of Getting Sober, when You Are Going to Get Drunk Again.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

_{Wednesday, November 25, 2015 9:47 AM}

Please look at my scenario.

What will I do for the following condition of TempDB:

name    NumberReads    BytesRead    IoStallReadMS    NumberWrites    BytesWritten    IoStallWriteMS    IoStallMS    MB_Size
tempdev    308773    17559781376    1035732    1275047    98065653760    14830691    15866423    42679
templog    26198    1583009792    110562    106060    6277349376    496415    606977    1542