February 18, 2010 at 1:25 am
I read many articles and in forums that often shrinking the DB is not a good practise...and I agree to it considering the impact DB may have in long run....
Somebody also suggested that its better to have dedicated disk (I consider it to be SAN) for data and log files..
But...what would be better solution to gain space again if one dont have dedicated disk? and the local disk is almost full and we badly need a space inorder to DB function properly?
I have one Instance where tempdb grows to approx 20-25 GB, the operations being performed at that time are reindexing, update stats etc..
What could be the best solution to avoid shrink of tempdb in above scenario?
How much will be impact on instance if tempdb is shrinked atleast twice in a week in above case?
Any comments/suggestions to avoid tempdb shrink are most welcome.
February 18, 2010 at 4:00 am
I'm sure someone will post to say you should have dedicated luns/drives - but real world doesn't always allow you that option, in fact I have a couple of servers where everything is on the same array/lun/drive.
The main problem with constantly expanding and shrinking files is the fragmentation that gets introduced into the file system. Severe fragmentation certainly impacts performance and can make ntfs operations slower - I find a restore is slower on a badly fragmented drive.
Ideally I size all my databases, including tempdb so that they don't autogrow. If I need a 25GB tempdb because there are database activities which require this amount of space then I will set tempdb at this size. ( subject to making sure there are no silly bits of code and such )
I'd advise getting more disks - t logs work better on seperate drives.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
February 18, 2010 at 5:01 am
Hi Colin..Thanks for your reply
I know the fragmentation impact and thats my worry at the moment....I had already suggested to have system databases on different drive(currently one user DB ~ 80 GB and all system DBs are on drive C:...to make it worst we have data and log files on same drive)
Actually I have this new client where SQL server was already installed & configured by them and my job is to give PROD support to such badly configured servers.
I am aware that conf I described above is not at all good and already suggested client to improve the conf as per MS recommendations....but you know till the things gets approved and implemented thereafter...I dont want C: drive to get fragmented (I guess its already started)
I can't increase size of tempdb log to 25 GB as disk will be almost full then with few MBs left free and only during the operations I stated it reaches 25 GB...
So as of now I need some alternative solution to shrink tempdb till the time I improve the conf...
Or want to know how safe it is to shrink tempdb twice a week?..
Further..how do I check current level of fragmentation of disk drive?
Experts....any help is highly appreciated.
February 18, 2010 at 5:48 am
You can move the TempDB to another drive. You can shrink it a couple times a week if needed. Is this a 24/7 box? If not, bounce it a couple times a week in the middle of the night.
What kind of total moron placed everything on drive C? I put nothing at all there. In fact, my drive C is only 7mb I make it as small as possible.
I install SQL in a drive, and place all the databases on different drives. I keep every box that I have exactly the same, all drive letters the same. All directory names the same, etc....
You may wan to look into why the Temp Database is needing so much space. Could be that the code can be written more efficiently, and that will take care some of your issues.
Andrew SQLDBA
February 18, 2010 at 5:54 am
Yup..its 24x7 prod box...so cant restart services very often...
There is maint plan which does tasks like update stats, reindexing etc...at that time only tempdb grows....
sp_who2 shows some INSERT command on userDB but I am unable to find the complete command though.
February 18, 2010 at 5:57 am
Read the entire of this kb article carefully. Especially the section "Effects of Execution of DBCC SHRINKDATABASE or DBCCSHRINKFILE While Tempdb Is In Use"
http://support.microsoft.com/kb/307487
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
February 18, 2010 at 6:00 am
Gail Thanks for the link..I will read it completely and will post if I have any doubts...
February 18, 2010 at 7:58 am
If you do wish to shrink tempdb issue a checkpoint and dbcc updateusage commands first.
I usually use shrinkfile and shrink to a defined size. It's not something I'd normally do on a running box, but if you have run out of disk space and you can't take the box offline then you don't have many options open.
Small C: drives are a total pain but I assume the 7MB is a typo? By default profiler and all types of other programs make use of the default temp directories - you might want to read http://sqlblogcasts.com/blogs/grumpyolddba/archive/2010/01/27/setting-environment-variables.aspx
about how to resolve this situation.
If you are going to shrink tempdb then choose a quiet moment - or you could do what I do to prove a point - linmit the growth and see what breaks < grin >
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply