All Questions
Tagged with tempdb sql-server-2008
21 questions
-1
votes
1
answer
461
views
Need best practices to maintain TempDB which is growing to 100GB now, is moving Tempdb to 35GB drive have any limitations? [closed]
Can moving TempDB to smaller drive cause failures?
How can TempDB be configure to use 35GB drive without any issues?
Please suggest if something needs to be taken care for this smooth transition of ...
2
votes
1
answer
5k
views
Unable to remove additional tempdb file
In our production DB, we only have 1 tempdb file and it had ballooned to 180GB.
I ran:
Alter Database tempdb
modify file (name='tempdev', size=30gb);
to resize the primary file, and then ran ...
- 303
1
vote
1
answer
2k
views
Shrinking TEMPDB
Is it necessary to restart SQL Server after DBCC SHRINKFILE on tempdb.mdf and tempdb.ldf or is there any other method to reduce the size of tempdb.mdf and tempdb.ldf without restarting SQL Server?
Is ...
- 331
13
votes
4
answers
68k
views
Allocate space for object 'dbo.SORT temporary run storage
DBCC CHECKDB returns:
Could not allocate space for object 'dbo.SORT temporary run storage:
140737951236096' in database 'tempdb' because the 'PRIMARY' filegroup
is full.
Create disk space ...
- 331
9
votes
1
answer
7k
views
Very high transactions per second
Our production server runs on average 4,000 transactions per second. In the past few days the average has jumped to 175,000 transactions per second. That is not a typo, it's 175K per sec.
Looking at ...
- 764
1
vote
2
answers
3k
views
Moved 'tempdb', now file 'tempdev2' does not exist
I moved the tempdb first locating the files:
use master
select name, physical_name
from sys.master_files
where database_id = DB_ID('tempdb');
GO
then I moved each file:
use master
go
alter ...
- 127
0
votes
1
answer
756
views
LDF files vs. TRN files vs. Tempdb.MDF+LDF files.:
We have a dilemma…
We have all of our MDF and LDF files in the same…\192.168.6.22\d$\Program Files\Microsoft SQL Server\DATA\Production...folder.
We have all of our TRN and BAK files on the…\192.168.6....
1
vote
0
answers
88
views
tempdb contention issue and excessive pagewrites/s
Still removing the rust from my SQL Server skills, so please bear with some stupid questions here and there...
Customer has a SQL Server 2008 R2 running in an Azure VM with 8 cores and 14GB RAM. ...
- 143
1
vote
1
answer
269
views
Tempdb files don't exist
I have a strange behaviour on one of my SQL Server's tempdb.
It has six tempdb files, according to SSMS and queries, all pointing to S directory. Each of them have ~5gb of initial size.
The weird ...
- 1,550
12
votes
2
answers
31k
views
TempDB will not shrink. No Open Transactions
I have a TempDB on SQL 2008 that has gotten very large (>40gb) and I want to shrink it down. I have used the dbcc shrinkdatabase, dbcc shrinkfile and the shrink command through Management Studio.
I ...
- 123
4
votes
1
answer
2k
views
Does MAXDOP affect the number of tempdb data files I should create?
From what I have read, best practices for configuring tempdb includes creating multiple data files. The rule of thumb as I understand it is if you should create the same number of data files as you ...
- 24.3k
1
vote
2
answers
5k
views
Why does OBJECT_ID return NULL in a computed column?
I'm trying to produce a usage report for some stored procedures in my database. The view view sys.dm_exec_procedure_stats contains some of what I need, so I decided to start here.
SQL Server keys ...
- 2,358
2
votes
1
answer
1k
views
TempInsertStateItemShort procedure on ASPState is taking more than 1 second to execute
I'm working on a .NET website who runs in load balance within more than one server and because of that I need to have my session on SQL Server.
In the last 2 weeks I've notice that the procedure ...
- 141
2
votes
0
answers
26
views
SQL Server 2008 R2 SP1 - Tempdb “Available free space” showing negative value [duplicate]
Please help as this issue is driving me nuts...
Tempdb has four data files & one log file. They are all located on the same dedicated drive.
When I right click on tempdb in SQL Server Management ...
- 611
7
votes
3
answers
11k
views
SQL Server 2008 R2 SP1 - Tempdb "Available free space" showing negative value
Please help as this issue is driving me nuts...
Tempdb has four data files and one log file. They are all located on the same dedicated drive.
When I right click on tempdb in SQL Server Management ...
- 611
1
vote
2
answers
182
views
SQL 2008 RTM Temp Tables
Today I implemented a monitoring solution on our SQL server using Cacti and the first thing i noticed is the growth in the number of temporary tables, in just 3 hours on our test system we had an ...
- 305
81
votes
4
answers
217k
views
How to identify which query is filling up the tempdb transaction log?
I would like to know how to identify the exact query or stored proc which is actually filling up the transactional log of TEMPDB database.
- 1,033
3
votes
2
answers
2k
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
...
- 255
32
votes
3
answers
84k
views
How to find the SQL statements that caused tempdb growth?
The tempdb of a server (SQL Server 2008) increases to 500GB+ several times every month. Is it possible to find out which SQL statements caused this problem? The problem is usually not caused by create ...
- 1,545
5
votes
2
answers
13k
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 ...
- 10k
2
votes
1
answer
2k
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 ...
- 10k