TempDB is part of MS SQL Server. It's a system database that functions as a storage area for temporary tables, internal objects and row versions.

learn more… | top users | synonyms

9
votes
1answer
2k views

SQL Server TempDB behaviour in large memory environment

Reading over In-memory SQL Server log file reminded me of a question I had a short while ago. We have a SQL Server that has 512GB of RAM, the main database is (only) 450GB. We see quite a lot of ...
4
votes
3answers
476 views

Creating new TempDB files

I would like advice on what would be the best setup for our SQL Server 2005 TempDB. At the moment our Temp DB consists of a single MDF file of 3 GB and a single log file of 120 MB on the D: drive of ...
11
votes
3answers
3k views

System disk run out of space when running heavy SQL queries on SQL Server 2012

I'm quite new to SQL Server 2012, I would be grateful if someone can help. I have restored a copy of a huge database to SQL Server 2012 and I tried to run some simple queries against it. I'm trying ...
2
votes
2answers
1k views

Tempdb autogrowth settings

Following on from a recent question that I asked on Tempdb. I was wondering how to correctly set the autogrowth property when adding and moving the tempdb files? I ask because I would like to let SQL ...
8
votes
2answers
6k views

Safely moving and creating new tempdb files

Two things I'd like to know, how do you safely move tempdb with minimal downtime? Also, how do you create new tempdb files? Is it 1 per core? So quad-core = 4 tempdb files, creating three new ones?
4
votes
1answer
1k views

823 errors on tempdb

Have a couple Fiber channel SAN-attached cluster servers which have been intermittently reporting errors like: 2012-05-09 16:31:54.13 spid74 Error: 823, Severity: 24, State: 2. 2012-05-09 ...
2
votes
2answers
724 views

Does it ever make sense to use RAM Disk to force RAM allocation for tempdb with SQL Server 2008

If I give all RAM to SQL Server, it will use it's own pattern to determine what RAM to allocate for what. If I allocate portion of RAM as RAM Disk and put tempdb into that disk it will force RAM ...
4
votes
2answers
4k views

SQL Server 2008 R2 - Set tempDB size on full disk

One of my SQL Server 2008 R2 instances had a job that went mad last night, ended up with the job auto-growing tempDB until it ran out of disk space. To compound the issue I noticed that tempDB is ...
15
votes
2answers
17k views

How to find the SQL statements which caused the tempdb grew huge?

The tempdb of an server (SQL Server 2008) increases to 500+G several times every month. Is it possilbe to find out which SQL statements cause the problems? (The problem is usually not caused by create ...
3
votes
1answer
6k views

TempDB data files don't shrink well on SQL 2008

So ideally you want to pre-size your TempDB data and log files appropriately so that this isn't a concern, but sometimes a rogue developer runs a crazy huge query on a production server during work ...
2
votes
2answers
339 views

How to quantify TEMPDB usage in relation to other databases hosted on same hardware?

Working on a project to decide if configuration 1 or 2 is the best way to organise disks for my SAN: Config1 (sql 2008): 8 disks @ raid 10 data 2 disks @ raid 1 logs 2 disks @ raid 1 tempdb 2 disks ...
2
votes
1answer
205 views

sys.syshobtcolumns Help

Does anyone have a good link to documentation regarding how the syshobtcolumns and syshobt system tables are populated. The reason for asking is that we are receiving pagelatch_sh and pageliolatch_ex ...
2
votes
1answer
667 views

Why does an AS cube rebuild use so much TempDB?

I have a 50GB SQL 2008 database (only 36 of that is data) with a 24GB Analysis Server cube that rebuilds nightly. The cube rebuilds have been using more and more of the TempDB on the SQL server each ...
3
votes
2answers
3k views

SQL Server 2005 tempdb Time out occurred while waiting for buffer latch type 4 (pagenum)

Three separate processes have failed in the last two days with very similar errors: Time-out occurred while waiting for buffer latch type 4 for page (1:1189832), database ID 2. Time-out occurred ...