TempDB Shrinking

Question

TempDB Shrinking

Viewing 13 posts - 16 through 27 (of 27 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Vivek29 (3/9/2010)
Who said to run it production server? I am giving an alternative to shrink tempdb without restarting server as asked in the very first post.This will help I guess:cool:

Your suggestion will not help with reducing the size of tempdb. That will clear the cache held in memory.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

sqldba4u Hall of Fame Points: 3414 More actions · Answer 2

It will.I witnessed it.See my post..I said after clearing cache, perform shrink operation.:smooooth:

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

Vivek29 (3/9/2010)
It will.I witnessed it.See my post..I said after clearing cache, perform shrink operation.:smooooth:

But why would you need to clear the cache (RAM) to shrink tempdb (disk)? Makes no sense as far as I can see.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

Vivek29 (3/9/2010)
It will.I witnessed it.See my post..I said after clearing cache, perform shrink operation.:smooooth:

I am somewhat skeptical about that.

Freesystemcache:

Releases all unused cache entries from all caches. The SQL Server 2005 Database Engine proactively cleans up unused cache entries in the background to make memory available for current entries. However, you can use this command to manually remove unused entries from all caches.

DBCC FREESESSIONCACHE

Flushes the distributed query connection cache used by distributed queries against an instance of Microsoft SQL Server.

I might bite on the freesessioncache if the premise is that a distributed transaction is causing the log growth. But, you must still find out what is causing the log growth. One should not run these commands in a production environment unless in a scheduled outage.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

sqldba4u Hall of Fame Points: 3414 More actions · Answer 5

Yes, DBCC FREESESSIONCACHE can be used in case of distributed environment.Agree, not to use in Production environment unless you have a maintenance window.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

A 32GB TempDB just isn't necessary IF the code is well written. You need to find out what is using that much space. Normally, it'll be a reporting query that has the word DISTINCT or maybe GROUP BY in it that has a lot of joins and someone doesn't know their data and have caused an accidental partial cross-join. Find the bad query first before you do anything else.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

GTR SSCrazy Eights Points: 8007 More actions · Answer 7

gupta1282 (3/9/2010)
Hi All
Our TempDB mdf file size is 32617 MB and Unallocated Space showing 32607 MB
but size of mdf file is not reducing after shrinking.
Why This happening ? How to regain this huges amount of space
Thanks
Ghanshyam

I might bite on the freesessioncache if the premise is that a distributed transaction is causing the log growth. But, you must still find out what is causing the log growth. One should not run these commands in a production environment unless in a scheduled outage.

OP is talking about mdf not ldf file, i would just first check initial mdf file size, then

DBCC shrinkdatabase with notruncate first to compact pages

DBCC Shrinkdatabase with truncateonly option to free disk for OS

As Lynn said monitor growth of the file and go from there.

EnjoY!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

GTR (3/9/2010)
gupta1282 (3/9/2010)
Hi All
Our TempDB mdf file size is 32617 MB and Unallocated Space showing 32607 MB
but size of mdf file is not reducing after shrinking.
Why This happening ? How to regain this huges amount of space
Thanks
Ghanshyam
I might bite on the freesessioncache if the premise is that a distributed transaction is causing the log growth. But, you must still find out what is causing the log growth. One should not run these commands in a production environment unless in a scheduled outage.
OP is talking about mdf not ldf file, i would just first check initial mdf file size, then

You're right. I meant tempdb growth and not log growth.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Just one point...

http://support.microsoft.com/kb/307487

From said article:

IMPORTANT: If you run DBCC SHRINKDATABASE, no other activity can be occurring with the tempdb database.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

GTR (3/9/2010)
DBCC shrinkdatabase with notruncate first to compact pages
DBCC Shrinkdatabase with truncateonly option to free disk for OS

What's the reason for doing them separately when running Shrinkdatabase without options does both?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Deepak Prasad Sahu Ten Centuries Points: 1160 More actions · Answer 11

Hi,

If there is huge growth in tempdb size then,i suggest rebooting the server wont help much..so i think,we should findout why tempdb is growing so much and important is to see what time it is growing..and after that we can run profiler at that particular time and analyse the trace to findout which query is causing the tempdb to grow.

To see what time the tempdb is growing,we can schedule a job to run every 10 min,that will capture the space utilisation on tempdb.

Thanks,

Deepak.:-)

SQLQuest29 SSCrazy Eights Points: 8264 More actions · Answer 12

dpsahu30 (3/11/2010)
Hi,
If there is huge growth in tempdb size then,i suggest rebooting the server wont help much..so i think,we should findout why tempdb is growing so much and important is to see what time it is growing..and after that we can run profiler at that particular time and analyse the trace to findout which query is causing the tempdb to grow.
To see what time the tempdb is growing,we can schedule a job to run every 10 min,that will capture the space utilisation on tempdb.
Thanks,
Deepak.:-)

I think Jason has a good post of how to do this:

http://www.sqlservercentral.com/articles/Log+growth/69476/

HTH,

\\K

______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂

SQLRNNR SSC Guru Points: 281334 More actions · Answer 13

SQL_Quest-825434 (3/12/2010)
I think Jason has a good post of how to do this:
http://www.sqlservercentral.com/articles/Log+growth/69476/
HTH,
\\K

Thanks, I think it is a good solution too 😉

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events