tempdb - Restricted File Growth option

Question

tempdb - Restricted File Growth option

Colin Lam

Mr or Mrs. 500

Points: 566
More actions
January 22, 2009 at 6:56 pm

#383791

Hi,
Just hoping someone can assist us with a solution to prevent tempdb from using all our disk space.
We are running on SQL 2005 and the problem we are facing is that tempdb is on Enable Autogrowth and Unrestricted File Growth, which has caused our disk volume to be full as the file grow.
We know that restarting the server will recreate tempdb from scratch, but this is not a good option for us. We would prefer not to have to restart the server each time tempdb fills up the disk.
Does anyone know if we change from Unrestricted File Growth to Restricted File Growth (say 50GB), once tempdb hits that limit, would it automatically recycle the space ... or would it crash and burn?
If this is not a workable solution, has anyone else found a way to have tempdb recycle the space it has allocated more efficiently?
Thanks in advance.

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

happycat59 One Orange Chip Points: 29389 More actions · Answer 1

You certainly can limit the growth of tempdb however when it gets to that limit the query that is needing the space will fail.

A better option is to review the code that is being used. It may be possible to reduce the space required in tempdb although this may not be as easy as it sounds simply because SQL Server uses tempdb for work areas and sorting as well as for objects that are explicitly created in tempdb (such as # tables).

Rather than restrict the size of tempdb, you are better off ensuring that you have sufficient space in tempdb for the workload on your server. This may well require you to find additional space, perhaps on another drive and add another file to those already assigned to tempdb. This has the potential to share to I/O load which may have a positive impact on performance.

Colin Lam Mr or Mrs. 500 Points: 566 More actions · Answer 2

Thanks for your reply Happycat59.

I suppose buying more disk space is one option, but we were looking at what can be done with our current server setup.

No matter how much extra disk space is added, tempdb with its unrestricted file growth will eventually use them all.

As for optimising SQL queries, we have two third party applications that have their databases sitting on our server.

We have no control over their SQL codes, and we know one of them uses a lot of temp tables whenever they do their processing.

chandrujprem SSC Journeyman Points: 81 More actions · Answer 3

hi

but we can move the tempdb..

just --check the current location of tempdb using

select * from tempdb.sys.databse_files

--now Change the location

Use Master

Go

Alter database tempdb

Modify file(name=tempdev,file name='give file name path here'

go

Alter database tempdb

Modify file(name=templog,file name='give log name path here'

go

--recheck..table shows moved but not yet

--so restart the service now..then check ,u get both old and new files(bt we can remove original files now

regards

prem

Colin Lam Mr or Mrs. 500 Points: 566 More actions · Answer 4

Hi Prem

Thanks for your suggestion, again we are trying not to have to restart the sql server, and moving the tempdb to another drive just means it will eventually use up all of the disk space there.

Is there no way to recycle the spaces already allocated in tempdb?

aali_online SSC Veteran Points: 221 More actions · Answer 5

Try setting up a job to checkpoint tempDB and then truncate the tempDB Log. Schedule it to run every 5 minutes to start with and then adjust as necessary.

Big Data Man SSCrazy Eights Points: 8536 More actions · Answer 6

Hi Colin,

As already suggested, I would argue against restricting tempdb, as any query that requires tempdb to store in-flight data will fail, thus a more serious problem surface. If the 3rd party applications are consuming so much space and you have no control on code, I would strongly suggest you add more disk space asap, as tempdb is core to the operation of SQL Server.

I know this may not be the answer you are looking for, but its the best I can suggest to avoid a potentially bigger issue in future.

Thanks,

SQL Server Consultant

MCITP - SQL Server 2008|DBAdmin

MCITP - SQL Server 2005|DBAdmin

MCTS - SQL Server 2008

MCTS - SQL Server 2005

MCP - SQL Server 2000

Colin Lam Mr or Mrs. 500 Points: 566 More actions · Answer 7

aali_online,

Would truncating the tempdb log file affect the rate the tempdb data file grows? It seem to us that it is the data file growth that is causing the problem. Please elaborate, unless I have misunderstood the way truncating of log files work.

Thanks

Colin Lam Mr or Mrs. 500 Points: 566 More actions · Answer 8

Thanks Phil,

I suppose if increasing disk space is the only way to go then we have to look into what capacity we can add to our server.

Based on your experience as a SQL consultant, is this what all the other sites are doing; allocate x GB space for tempdb and when it gets almost to its limit, restart the SQL server to flush it out ?

aali_online SSC Veteran Points: 221 More actions · Answer 9

Colin,

I mentioned truncating the tempDB log file as I assumed it would also be growing considerably alongside the data file.

chandrujprem SSC Journeyman Points: 81 More actions · Answer 10

hi colin...

no need to restart the server...just restart the service(i.e ms sql server start and stop).then delete the old temp db files.

regards

prem

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 11

You can shrink temp DB through the GUI without bouncing the server or the service.

My question would be... what's causing it to grow to 50GB? There's definately something like a triangular join or accidental cross join going on somewhere in the code. You need to find it and fix it 'cause, whatever it is, it's also killing performance.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Big Data Man SSCrazy Eights Points: 8536 More actions · Answer 12

Hi Colin,

In an ideal world, you would have ability to work with development team to appropriately size all database storage requirements, including tempdb, but this is not the case and its down to making a reasonable forecast.

As you know, tempdb in SQL Server 2005 does a lot more in terms of providing a temporary storage location for SQL Server for things such as online index creation, temp tables and dbcc checks as a small example. Add 3rd party applications to the mix and you have a huge challenge to size correctly. In majority of environments I been involved, we normally present a completely seperate LUN for tempdb and size according to application(s) that will use SQL Server and size tempdb starting at 1GB chunks to ensure we have more than required. As a good example, I currently have a 1TB+ finance instance, which is very heavy on reporting and supports about 42 application servers and my tempdb is 80GB, but I normally have 95% free even with the reporting demand. This is way over provisioned, but due to critical nature of system, we could not allow a full tempdb to impact a global system.

In the early days of implementing this system, we had similar problems with tempdb, which was due to how the application queries were designed and fortanately able to work with vendor to improve the overall use of tempdb and SQL Server in general.

I would suggest you monitor what is happening on your system over the next few weeks or month to truely understand what is causing tempdb to grow so large. This normally indicates long-running queries that are processing lots of data, but you'll need to check what other features you may have in-directly enabled on SQL Server (e.g. on-line index builds etc..).

Here is a good link from Microsoft providing a good insignt to sizing tempdb:

http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx

Hope this helps,

Phillip Cox

SQL Server Consultant

MCITP - SQL Server 2008|DBAdmin

MCITP - SQL Server 2005|DBAdmin

MCTS - SQL Server 2008

MCTS - SQL Server 2005

MCP - SQL Server 2000

Colin Lam Mr or Mrs. 500 Points: 566 More actions · Answer 13

Thanks for all your suggestions, rather than reply separately to all of them, I hope you don't mind if I just group them together ...

aali_online

The log file don't grow as large as the datafile because we do log shipping (to our DR site) every 20 minutes, so I am assuming this keeps the log file size down.

chandrujprem

I was told restarting the service is just like restarting the SQL server...

Jeff

How do you go about shrinking the tempdb through the GUI?

We don't know exactly what is causing the tempdb to grow so big, what we do know alot of the tempdb is just empty space, but it continues to grow, hence the question, why does it not recycle itself ..

Phillip

We will try to do some monitoring, but we are not expecting much help from our vendors.

the__t Hall of Fame Points: 3260 More actions · Answer 14

Colin Lam (2/2/2009)
The log file don't grow as large as the datafile because we do log shipping (to our DR site) every 20 minutes, so I am assuming this keeps the log file size down.

That's a neat trick... how on earth are you logshipping tempdb?? 😛

"Got no time for the jibba jabba!"
-B.A. Baracus