June 1, 2011 at 8:06 am
Hi
I had one Production server.Recently I faced space issue.When I checked the space I found that tempdb is consuming more space.It cotains 8 datafile and one Log file which already set by Client DBA team.The Primary datafile size was 70 Gb and other ndf files are 6 GB each.I was not able to shrink these files.I restarted the sql server and cleared the space.Any one can help me why tempdb grown this much and also confirm me this file setting( 8 datafile and 1 logfile) is best practice.
I found Index rebuild happened on this server.After that tempdb size not reduced.
Pls assit
June 1, 2011 at 8:22 am
Like other databases, tempdb will not shrink down automatically. It will grow as needed and remain at the larger size unless/until manually shrunk. It is not normally recommended to shrink databases for many reasons. In a special case, it may be helpful.
Tempdb is used for a lot of operations, like large sorts, temp tables, online reindexes, etc. Depending on how the TSQL is written in your system, tempdb may be used quite a lot. Also, the query execution plans may reveal operations that favor tempdb usage.
As for haveing many tempdb files, 'it depends' on what the servers biggest bottleneck is. If there is disk contention on tempdb, then either rewriting the TSQL, or adding more tempdb files can relieve that contention. If tempdb is not the bottleneck, then this step may not be necessary. Monitoring will tell you which way to go.
With such a huge tempdb file, tempdb may be your servers bottleneck. Or, it may be huge due to a massive reindex or temp table driven stored proc or something.
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
June 1, 2011 at 8:40 am
The number of files in your tempdb depends on your needs, hardware and usage. One consideration though is that it would be more common to have 8 data files and 1 log file as opposed to 7 data files and 1 log file as you have (reason being is that one method is to create 1 data file per processor core). You would only have 1 log file so that is correct.
Did you add the additional files after the primary had already grown to 70GB? These files should grow pretty much evenly in a round robin fashion - which leads me to believe there is a growth setting that is set differently between the files and/or your reindex transaction was entirely done within a single transaction. I would take a look at both of those and make adjustments as necessary. If reindex was not within a single transaction then it is possible that the reindex of a single huge table could have caused this difference.
Knowing your database and processes can really help to properly size the data and log files. You may need to readjust size and growth settings for all of your tempdb files.
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
June 1, 2011 at 9:42 am
To add to what Jason said, data files should 'fill more evenly' (proportional fill) if all data files are the exact same size. If they are lopsided in size, SQL doesn't do the best job proportionally filling the files (which negates some of the benefit since SQL's ability to distribute the load over the files may be less even). In fact, it is not exactly perfect even when they are the same file size - especially on older versions of SQL Server.
So it is likely best in your case to shrink your primary tempdb down some, and grow the other tempdb files so that all files are all the same size, ensuring that there is plenty of free space in the files so an auto grow is not likely needed in the next year.
Then, monitor. Like Jason said, you need to know your databases and their usage patterns to make the best decisions without making things worse.
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply