TempDB data file & Log file size

Question

Post reply

TempDB data file & Log file size

pshaship

SSCertifiable

Points: 6125
More actions
April 22, 2010 at 9:34 am

#218157

Hi,
We have MOSS 2007 SP1 databases on SQL Server 2005. We have TempDB on Separate drive (storage is on SAN) and I have created the tempDB datafile size as 15 GB & log file size as 10 GB initially.
Within one week after going production, the tempDB log file size is increased 5 GB and data file size is 11 MB only. So for TempDb, the Log file size should be as large as enough but the data file size will be small right? In my case can I reduce the data file size & increase log file size?
Please advice

Viewing 6 posts - 1 through 5 (of 5 total)

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

Angel DBA Mex Ten Centuries Points: 1085 More actions · Answer 1

in the follow link I believe that is good to understand how works the tempdb DB and how you can resolved this

good luck

http://sqlserver2000.databases.aspfaq.com/why-is-tempdb-full-and-how-can-i-prevent-this-from-happening.html

Angel Miranda Nieto
DBA Specialist, BI, MCSA SQL Server

pshaship SSCertifiable Points: 6125 More actions · Answer 2

thanks Angel,

For tempDb, we do not need to allocate large size for Data file (mdf) but need to allocate large size for Log file (ldf) right?

Because for us, the tempDB data file size is always in MB eventhough I set the initial size as 15 GB. Only Log file size is increasing!

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

A couple of other folks and I recently discovered a possible problem with the IDENTITY function when used with SELECT/INTO in the form of explosive log file growth even with databases set to the SIMPLE recovery mode (like TempDB). The work around in 2k5 is to, of course, use ROW_NUMBER() instead of IDENTITY if you don't actually need an IDENTITY column.

When I say "explosive", I mean "nuclear bomb explosive". I've not finished my testing on the problem, yet, but current testing clearly demonstrates that it just doesn't take very much for it to "run away".

Check your code for %IDENTITY(INT,% and see if you find if you get anyhits.

--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)

pshaship SSCertifiable Points: 6125 More actions · Answer 4

Thanks,

But this SQL instance has MOSS 2007 databases & unfortunately we cannot change any settings on SQL Server side according to the Share Point Technologies Products support!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

To find what may be causing the log and tempdb growth refer to this article.

http://www.sqlservercentral.com/articles/Log+growth/69476/

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events