Question and learning on shrinking datafile,log file, tempdb

Question

Question and learning on shrinking datafile,log file, tempdb

Sagar-636902

SSCommitted

Points: 1651
More actions
February 10, 2010 at 9:09 am

#142163

Hi every one,
I am not full DBA but working as dba/developer.. i am trying to understand the good and bad part of shrinking files and better option. In the past year i have had so much problem on growing temp db... i have had maintainance plan to shrink every night... is that good way to do it? what if some one is using temp db while it is doing that step? what about data file? when we truncate/ delete very large tables does that leave unused space in datapage... if it does how can i handle this? and for the log file lets say i have large operation every night it has lots of insert update and delete.. which increase log about more then 15 gb of the total 800 gb database also increase the temp db size to 8 gb. I know when i run the operation( stored procedure) it has temp table which dump large number of data in single operation... what could be the better way to handle this situation.
Please help me...
Thanks
Sagar

Viewing 5 posts - 1 through 4 (of 4 total)

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

GTR SSCrazy Eights Points: 8007 More actions · Answer 1

Best practices is to place TempDB MDF and LDF files in different drive than other user databases. The reason is tempdb log file will use complete disk space some times, this happens during Index rebuild, back up databases etc. Theoretically during Index rebuild shouldn't take more 2 times the table size on TempDB, However that's not the true always. Take log back up of tempDB should free the space, if thats doesn't help re cycle SQL services will free the disk space. Set up SQL or SCOM alert on log file size growth.

Note: This happens even if you set restricted file growth of LDF file on TempDB.

EnjoY!

EnjoY!

Jeff Moden SSC Guru Points: 1003925 More actions · Answer 2

I boot my servers with more than 8GB for TempDB. 😉

Give it some room to breath and stop shrinking it. Also, as you're finding out, scheduled shrinking of TempDB is a futile task (and also has some very nasty side effects when it regrows). What you really need to do is figure out why it's growing bigger than you want.

--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)

jshailendra SSCrazy Points: 2260 More actions · Answer 3

jshailendra

SSCrazy

Points: 2260

February 10, 2010 at 10:29 pm

#1117368

GT,

you CAN'T backup tempdb 😉

SQL* SSCertifiable Points: 6638 More actions · Answer 4

[highlight=#ffff11] i have had maintainance plan to shrink every night... is that good way to do it?[/highlight]

you can do it occasionally(not as a every night job, it is better to not to do that it may breakdown log chain), since if you shrink the db the space will be given to the o.s., again when sql server needs more space then it has to allocate from o.s. so it is time taking process compare to sql server own space.

🙂