CODEWITHSUNDEEP
sql-serversql-server-2005tempdb
Maulin
Maulin

Reputation: 11

SQL Server 2005 TempDB Size

We are using SQL Server 2005. Recently SQL server 2005 crashed in our production environment due to large tempdb size. 1) what could be reason for large tempdb size? 2) Is there any way to look what data is there in tempdb?

Upvotes: 0

Views: 4291

Answers (4)

Fandango68
Fandango68

Reputation: 4898

use tempdb

select (size*8) as FileSizeKB from sys.database_files

Upvotes: 0

TomTom
TomTom

Reputation: 62157

2) Is there any way to look what data is there in tempdb?

No, because it is not kept there. Tempdb has very special treatment, like being dropped on every server restart.

1) what could be reason for large tempdb size?

Inefficient SQL, maintenance jobs or just the data at hand. Obviously a 800gb, 6000gb database may require more tempdb space than a 4gb online crm attempt. You dont really specify ANY size in absolute terms. What IS large? I have tempdb databases hardcoded at 64gb ony my smaller servers.

Typical SQL that goes into Tempdb are:

  • Sorts that are not solvable as part of the query (you need to store keys SOMEWHERE)
  • DISCTINCT. Needs all returned data in tempdb to find doubles.
  • Certain poerations psossibly during joins.
  • Tempdb usage (temporary tables). I just mention them becasue I often keep some hundred megabytes worth of data in them during loads and scrubbing.

In general you can find those queries by having hugh IO stats in the query log, or simply being slow.

That said, maintenance plans also go int there, but with reason. At the end, your "large" is possibly mine "not even worth mentioning tiny". It really depends what you do. Use the query trace tool to find out what takes long.

Physically Tempdb is very special in treatment - sql server does NOT write to the file if it does not have to (i.e. keeps thigns in memory). Writes to the disc are a sign of memory flowing ofer. This is different from normal db write behavior. Tempdb, IF it flows over, is best put onto a decently fast SSD... which wont normally be SO expensive because it still will be relatively small.

Use the query here to find other queries for tempdb - basicaly you are fishing in dirty water here, need to try out things until you find the culprit.

Upvotes: 1

Ryk
Ryk

Reputation: 3102

Ok tempdb is a kinda special database. Any temporary objects you use in procedures etc, is created here. So if you application uses a lot of temp tables in queries, they will all reside here, but they should clean themselves up after the connection (spid) is reset.

The other thing that can grow a tempdb is database maintenance tasks, however they will take a larger toll on the database log files.

Tempdb is also cleared every time you restart the SQL Service. It basically drops the database and re-create it. I agree with @Nic about leaving tempdb as it is, dont muck around with it, any issues with space in tempdb, usually indicates another larger problem somewhere else. More space will mask the problem, but only for so long. How much free space does your drive have that you have tempdb on?

Other thing, if not already, try and put tempdb on it's own drive, and one more if possible, have the data and the log files on their own separate drives.

So, if you dont restart your SQL Server/Service, your drive will run out of space pretty soon.,

Upvotes: 0

Nicholas Carey
Nicholas Carey

Reputation: 74365

The usual way to grow a SQL Server database — any database, not just tempdb — is to have it's data and log files set to autogrow (especially the log files). SQL Server is perfectly happy grow the log and data files until the consume all the disk space available to them.

Best practice, IMHO, is to allow limited autogrowth on the data files (put an upper bound on how big it can grow) and fix the size of the log files. You might need to do some analysis to figure out how big the log files need to be. For tempdb, especially, the recovery model should be set to simple, too.

Upvotes: 0

Related Questions