Hello Experts ,
We are using SQL 2005, windows server 2003.
Upto Yesterday tempdb is behave normal. But suddenly i noticed that
My tempdb is Growing gradually and it size is now 23 GB
In C Drive remaining space is just 6 GB.
It more users using server. So we cant able to restart server this point of time
1) How to shrink tempdb without restart SERVER?
Can I run this DBCC SHRINKDATABASE(tempdb, ‘target_percentage_of_free_space’)
without restart Server? Impact?
2) Any script/dmv to view which statement/Query using this much usage of tempdb?
3) Why are this suddenly happening in My server? who is cause this to grow tempdb too large?
Thankx Grant Frichey.
I have analysed the blocking chain see the attachment for your ref:
My doubt is After solve those blocking can I back tempdb to normal original size of below 1 GB? Now it size is 23GB.
answered Mar 12, 2014 at 11:49 AM
You can run the shrink database on tempdb without restarting the server. The bigger issue why is it suddenly growing. I would suggest getting a copy of sp_whoisactive. It's a script that will tell you what is currently running on the system. I suspect you may have an open transaction that is running and either filling tempdb, or just holding open transactions. It could be a number of things since tempdb is the dumping ground of SQL Server, but I'd start there.
If you can't or won't use sp_whoisactive, then run a query against sys.dm_exec_requests to see which sessions are currently active, how long they've been active and if there is any blocking going on. If so determine what the blocks are. That may be another way to quickly identify the problem.
answered Mar 12, 2014 at 09:11 AM
Grant Fritchey ♦♦
Are you sure you've identified the lead blocking session? If it is the top one it may be a query from a Client that dropped its connection ungracefully. SQL Server is waiting on network completion that may never occur. And it will do so happily for a long time. As it is only a SELECT it can most likely be killed without further impact to temp dB.
As for shrinking temp dB back down, yes it can be done, but you should analyze whether 1 GB is the normal size your system uses.
answered Mar 12, 2014 at 12:26 PM
Hi grant Fritchy thankx for your valuable comments
when i ran
exec sp_WhoIsActive @get_plans = 1,@get_locks = 1
see the attachment for your ref:
tempdb_current column displaying mostly zero then how tempdb growing 23GB more space
I found one db have deadlock like suspended appears MORE IN OUTPUT OF sp_whoisactive
How to solve this situation? should I kill the process one by one?
answered Mar 12, 2014 at 10:29 AM