Shriniking tempdb without restart Server?

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?

more ▼

asked Mar 12, 2014 at 03:24 AM in Default

avatar image

520 23 31 35

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

Thankx Grant Frichey.

I have analysed the blocking chain see the attachment for your ref:

alt text

My doubt is After solve those blocking can I back tempdb to normal original size of below 1 GB? Now it size is 23GB.

blocking.png (57.5 kB)
more ▼

answered Mar 12, 2014 at 11:49 AM

avatar image

520 23 31 35

That wait is usually an indication of problems on a client machine, like your app is waiting for someone to click something before it completes a transaction, that sort of thing.

Yes, you should be able to shrink tempdb. Not sure I'd go all the way back to 1gb and I really hate the idea of storing it on the same drive as your OS, but yes, you can shrink it back down.

However, you must identify why you're getting this issue, because otherwise, it'll come up again and grow tempdb again and put you right back in the same spot.

Mar 12, 2014 at 12:24 PM Grant Fritchey ♦♦

Are you sure you want to mark your answer as the correct one? Others who visit the site may be confused.

Mar 12, 2014 at 03:02 PM Blackhawk-17
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Mar 12, 2014 at 09:11 AM

avatar image

Grant Fritchey ♦♦
137k 20 46 81

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Mar 12, 2014 at 12:26 PM

avatar image

12.1k 30 36 42

@Blackhawk-17 Even i Killed that process also tempdb size not reduce from 23GB

How to reduce tempdb size?

Mar 12, 2014 at 01:49 PM sqlqa

In SSMS right-click on the dB, select Tasks => Shrink => Database (or go granular and do it file by file), just like any other dB.

Mar 12, 2014 at 01:54 PM Blackhawk-17
(comments are locked)
10|1200 characters needed characters left

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:

alt text

alt text

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?

more ▼

answered Mar 12, 2014 at 10:29 AM

avatar image

520 23 31 35

I can't see all the data in those pictures. You don't have deadlocks. Deadlocks give you errors. You have blocking and blocked processes. You need to identify the head of the blocking chain, the first process that is causing all the blocking. Then you need to understand what that query is doing before you try to kill it. If you just kill it and it's in the middle of a giant transaction, you may be facing a rollback which causes additional hits to tempdb, so be very careful using kill.

Mar 12, 2014 at 10:44 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Mar 12, 2014 at 03:24 AM

Seen: 846 times

Last Updated: Mar 12, 2014 at 03:02 PM

Copyright 2018 Redgate Software. Privacy Policy