All Questions
8 questions
5
votes
1
answer
1k
views
how to kill a rogue spid that is already killed?
I have an obstinate spid that I cannot kill and it is preventing the transaction log of my tempdb to get truncated
this is how I found this rogue spid:
if object_id('tempdb..#OpenTranStatus','U') is ...
- 16.6k
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
3
votes
1
answer
7k
views
Why does FREEPROCCACHE let you shrink tempdb?
Somebody did something stupid and tempdb exploded in size. Alerts are firing because the drive is full. I tried shrinking it, and it would not shrink (even though shrink queries said they worked).
...
- 6,318
3
votes
1
answer
445
views
Difference size DBCC Page and DBCC Fileheader
I'm trying to read the current database size using dbcc command.
In my test I execute following commands:
DBCC Fileheader('tempdb',1) => size = 988152
DBCC Page('tempdb',1,0,3) => size = 5112
...
- 2,124
2
votes
2
answers
6k
views
Removing Tempdb .ndfs
Tempdb is configured with 8 files and we are reducing them to 4. I read many blogs where SQL Server will allow you to remove excess .ndf's if you run the 4 dbcc drop and free statements, then run the ...
- 553
4
votes
1
answer
661
views
DBCC CHECKDB Estimation Alternative
I am in the process of rolling out DBCC CHECKDB across our instances.
Each database will eventually either have a DBCC CHECKDB or DBCC CHECKDB PHYSICAL_ONLY.
The way I have been gauging whether a ...
- 344
2
votes
1
answer
1k
views
TempDB not selectable for DBCC maintenance plan
I'm trying to clean up some of our maintenance plans and noticed that our TempDB is not getting it's weekly dose of DBCC CHECKDB goodness. I've tried to go into the maintenance plan and make sure it'...
- 3,844
6
votes
2
answers
1k
views
Major Discrepancy Between SQLIO IOPS/Throughput Stats and DBCC CHECKDB Operations
I'm conducting time trials on a new SSD array running both SQLIO tests and a real-world workload of DB restores and DBCC CHECKDB calls. I'm seeing a major discrepancy between the IOPS and throughput ...
- 1,935