Server Fault Stack Exchange
Server Fault Stack Exchange

Questions tagged [tempdb]

26 questions
11
votes
4 answers

SQL Server setup check list

What are the things I have to consider when I have to install SQL Server (2005/2008) on a new server? Storage setup TempDB Security What are some common mistakes made in that process?
splattne
  • 28,348
  • 19
  • 97
  • 147
8
votes
7 answers

My templog.ldf is huge (45gb), What if anything should I do?

I've a SQL 2005 installation and my templog.ldf file keeps growing to consume all the free space on the drive it's on. Sometimes it'll stop with a few mb free but sometimes it goes further, this being the c drive I think this behaviour may be…
Robin
  • 807
  • 3
  • 11
  • 19
5
votes
4 answers

Speeding up SQL Server temp table processing with a RAM Disk

A system we are developing consists of a Web app frontend, and a backend that does a lot of data processing using stored procedures in SQL Server 2008 R2 (please, don't ask why...). These stored procedures make heavy use of temp tables (creation,…
mramirez
  • 51
  • 1
  • 4
4
votes
1 answer

How to find out who / what is hammering SQL Server TempDB

I am using SQL Server 2012 Enterprise. The server has one availability group configured. There are several databases for different tools on the instance. The tools all belong together and therefore were rolled out togehter. I recognized a rise in…
Tobi DM
  • 53
  • 1
  • 4
3
votes
1 answer

SQL Server 2005 global slowdown

Two days ago our production server suffered a massive slowdown where the primary symptom was that an extraordinarily high number of requests were suffering SQLTimeouts. I will quickly describe our setup, what I investigated, our workaround, and will…
CWilliams
  • 31
  • 2
3
votes
3 answers

Correct settings for tempdb to avoid significant changes in free space

Every two to three days we are getting the following SCOM error: The database tempdb in SQL instance MSSQLSERVER on computer sqlserver has experienced a significant change in free space This has been happening since installing SharePoint…
Alex Angas
  • 2,007
  • 2
  • 26
  • 37
3
votes
5 answers

TempDB contention on sysmultiobjrefs SQL 2005

We've been having trouble caused by what we believe is contention within tempDB. Whenever we are having problems, our system is always waiting on one particular resource: 2:1:103, which when we look it up (using DBCC PAGE(2,1,103)) tracks back to…
Rob
  • 31
  • 4
3
votes
1 answer

What happens when tempdb can't grow any more?

I have run into a problem where a tempdb database set to have unrestricted growth has used up all the available disk space (10x its norm). I am considering setting a maximum size limit, something big but just not all the space. If a query pushes…
Iain Hoult
  • 143
  • 1
  • 7
2
votes
4 answers

SQL 2005 Standard Questions

I have a question about the tempdb files and hyperthreading. Here are the sw/hw specs. Server 2008 SP2 Standard X64 SQL 2005 Standard Sp3 x64 Dual Quad Core Xeon's E5540's (HT enabled) 12 GB Ram My first question is how many tempdb files should I…
Harris
2
votes
1 answer

Large amount of tempdb log writing, no reading

I'm running into some behaviour that appears strange to me. I'm using SQL Server 2008 Enterprise, 32-bit (quad Core 2), on Windows 7 (for testing). I have a stored procedure that uses two table variables. One gets about 2 or 3 rows inserted, the…
MichaelGG
  • 1,739
  • 8
  • 25
  • 30
2
votes
2 answers

Why did my tempdb fill up, and what was trying to shrink it?

After months of perfectly flat disk usage, my tempdb file suddenly grew by several gigs over the weekend. Nobody at the company is aware of anything that might have changed. When I checked the tempdb database, it had only a few very small tables,…
Josh
  • 743
  • 4
  • 9
  • 17
2
votes
6 answers

SQL Server 2005 tempdb files moved to invalid (SSD) drive. How to validate drive, or how to move files back?

SQL 2005 32bit Developer edition, all recent service packs Windows Server 2003 Standard 64x, all recent updates Fusion IO drive We installed a wifty new 80GB SSD card on our development server, ran ALTER TABLE on tempdb (10GB starting size) to move…
Philip Kelley
  • 253
  • 2
  • 9
1
vote
0 answers

How does Azure handle TEMPDB on the local SSD when using WSFC to support an always on availability group?

How does Azure handle TEMPDB on the local SSD (drive D:) when using WSFC to support an always on availability group? I know that the local SSD, where it's recommended to place TEMPDB, is erased during restart... and that a PowerShell script is used…
Gabe Green
  • 11
  • 1
1
vote
2 answers

Is it ok to limit the size of templog in SQL Server 2005?

I've a problem I'm investigating whereby my tempdb logfile grows out of all proportion to anything which would seem sensible. This morning it was over 10x the size of the data files. However, the database continues to function fine and the only…
Robin
  • 807
  • 3
  • 11
  • 19
1
vote
1 answer

How does the Proportional fill Algorithm in SQL 2005/2008 work?

When we setup multiple data files for tempdb I understand SQL Server uses a Proportional Fill algorithm. How does it work? Does it use the same method for user databases aswell. I have tried to understand it using the SQL Internals Viewer but was…
Chirag
  • 155
  • 8
1
2