SQL Server tempdb growing in size

SQL Server Administration
1

Hi, our clients live data files are something like 4gb in size, but theres a tempdb.mdf that continues to grow to 16GB!!!! Ive googled it & found that if you stop & start SQL server in services, it reduces this to something more normal like 50Mb. Sure enough it does exatly that

weve therefore set up a schedule to stop & start SQL server at 11pm This isnt ideal as its adding a variable in, in case something goes wrong. Ive noticed today that its now 500mb - does anyone have any ideas why is growing i size & how we stop it, other than constantly stopping & restarting SQL server please?

Many thanks

2

A large tempdb is not necessarily a indicator of a problem, unless you are running out of disk space. How are you using tempdb? Lots of large temporary tables or table variables? RCSI? Lots of queries that spill to tempdb for sorting or other reasons?

You need to get to the bottom of this. Restarting the instance is really a brute force approach and doesn't get to the bottom of the problem.

3

Though a 16GB tempdb is a bit big for a 4GB database, 16GB is still pretty tiny. Why is that size such an issue that you are causing downtime to the SQL Server (the restart)?

Start monitoring what's using it. Setup sp_WhoIsActive and have it run every 30 seconds. Here's an example: http://www.brentozar.com/responder/log-sp_whoisactive-to-a-table/

I typically do it different than that article. I just use the same table over and over again (WhoIsActive). I setup a job to run every 30 seconds, rather than the looping. I then have a job step to cleanup the data, keeping perhaps 7-14 days of data.

Once tempdb grows back out to 16GB, look at the WhoIsActive output and see what comes up for tempdb. Let us know if you'd like us to help.

4

hi, many thanks for the replies. Its just an accounts package that uses SQL (Sage 200). We noticed that the accounts package was only say 4gb, but this temp dbf had shot up from 12gb to 16gb! The clients were complaing about speed issues & my initial thoughts were that perhaps SQL was getting hammered creating this & Ive only been able to find that a stop & restart fixes it. I agree that this is a bit brutal & there isnt asny downtime as such as it is scheulded to work at 11pm. I will try your suggestion to see what is using it - many thanks for your help

5

The size of the tempdb is not a speed issue. The queries causing the larger tempdb possibly are. Implement WhoIsActive and see what you find.

6

Do you just have a single tempdb data file? If you do, you should add at least one more, in which case you split the total size exactly evenly between them. For now, I'll assume just the one file and give you commands for that. If you want to use 2 files, let me know, and I'll give you those commands as well.

Note, too, that you are slowing processing down tremendously by forcing SQL to re-grow tempdb. Instead, tempdb should be set large enough that it never needs to grow (unless something truly unexpected happens). To do that, we need to change tempdb's default size, so that it starts out that big already:

ALTER DATABASE tempdb MODIFY FILE ( NAME = tempdev, SIZE = 16GB );

Depending on snapshot options, how much grouping/sorting is being done, how temp tables / temp variables are being used, etc., you could need a tempdb that actually is larger than the db itself.