August 1, 2011 at 2:32 pm
1)
Lately I noticed our company developers are hampering the tempdb and every time they run process it is going up to 200 GB which is causing drives out of space. After creating data/log files on another drive the issue got fixed but still I am not sure which part of their query causing issue..
I have suggested them to rewrite the code and avoid using temp tables for high volume of data.
Is their anything else I can suggest them or I can do ?
2)We do lot of truncates on databases which is casuing fragmentation. So to avoid it I scheduled to shrink both the data and log files on recurring basis .
2a)So it is the only way or Is their any better way to do this?
2b)we have scheduled log backups which run every 5 minutes on the same database, Will the shrink files a performance hit ?
August 1, 2011 at 2:52 pm
I'd have to see what they're using tempdb for before I could suggest anything on that point. Heavy use of tempdb is common to a lot of good code, as well as a lot of bad code, since it's just plain common. So no way to judge it from what you've described thus far.
Regularly shrinking the database files is usually just a good way to slow the whole server down. It causes file fragmentation, locks during auto-growth, and a host of other issues. I've never yet seen it actually solve any real problem, but I've seen it cause a lot of problems for a lot of people.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 1, 2011 at 4:31 pm
sqlbee19 (8/1/2011)
1)Lately I noticed our company developers are hampering the tempdb and every time they run process it is going up to 200 GB which is causing drives out of space. After creating data/log files on another drive the issue got fixed but still I am not sure which part of their query causing issue..
I have suggested them to rewrite the code and avoid using temp tables for high volume of data.
Is their anything else I can suggest them or I can do ?
For Tempdb, general recommendation is to have multiple .ndf files equal to the number of CPU's to increase the performance of tempdb but might not in your case.
2)We do lot of truncates on databases which is casuing fragmentation. So to avoid it I scheduled to shrink both the data and log files on recurring basis .
Shriniking thorughly also causes fragmentaion
2a)So it is the only way or Is their any better way to do this?
Check if there is any bulk operations going on , if check recovery models for the right one that suits your environment
2b)we have scheduled log backups which run every 5 minutes on the same database, Will the shrink files a performance hit ?
Shriniking breaks up your log backups chain, when you shrink you cannot recover your server to point in time in case of failures.
August 1, 2011 at 4:33 pm
GSquared (8/1/2011)
Regularly shrinking the database files is usually just a good way to slow the whole server down. It causes file fragmentation, locks during auto-growth, and a host of other issues. I've never yet seen it actually solve any real problem, but I've seen it cause a lot of problems for a lot of people.
The above said are really true and in addition your backup chain also breaks and point in recovery is not possible in case of sudden failure.
Correct me if I'm wrong but that is what I remember.
August 1, 2011 at 5:19 pm
@SQLFRNDZ (8/1/2011)
GSquared (8/1/2011)
Regularly shrinking the database files is usually just a good way to slow the whole server down. It causes file fragmentation, locks during auto-growth, and a host of other issues. I've never yet seen it actually solve any real problem, but I've seen it cause a lot of problems for a lot of people.The above said are really true and in addition your backup chain also breaks and point in recovery is not possible in case of sudden failure.
Correct me if I'm wrong but that is what I remember.
Shrinking doesn't break the log chain (unless you do it by switching back and forth to simple / full). More to the point, why would we care about that on tempdb??
August 2, 2011 at 9:16 am
Thank you Gsquared and @SQLFRNDZ
Regularly shrinking the database files is usually just a good way to slow the whole server down. It causes file fragmentation, locks during auto-growth, and a host of other issues. I've never yet seen it actually solve any real problem, but I've seen it cause a lot of problems for a lot of people.
Q1) I have a database with size 400 GB but the space available is 200 GB.The data files have 50 % free space available. How can I get the space back without shrinking the files?
--If I have to restore this database on another server I have to make 400GB of freespace even though it only takes 200 Gb pf space so I started shrinking it. Please suggest the better way fo doing it..
Q2) Do you guys agree with Ninja's_RGR?
I agree as I did point in time restores in our environment. We do have log file shrinking processwhich runs every day at 5 am.
Ninja - I am not worried about tempdb on shrinking. The shrinking process I am taking about is for userDB where our devs are doing lot of truncates and making database space availabiltity (50%) of the toatal size.
August 2, 2011 at 9:24 am
If you truncate to delete data 5 years and older and then never keeping data beyond that period then a shrink can make sense (pretty much the only thing that does).
I'd go with shrink file but I wouldn't go much under 275 - 300 GB.
Once the shrink is done you'll have to rebuild all your indexes. Needless to say that you need a very big maintenance window to do all that.
August 2, 2011 at 9:26 am
For the restores :
http://www.red-gate.com/products/dba/sql-virtual-restore/entrypage/painkillers
NEVER shrink the log files. Unless it was uselessly bloated by the deleting of 50% of the db. You say you are doing that daily. What you should be doing if transaction log backups (15 mintutes apart is not too much). That way the file will never overgrow.
Keep in mind that when you reindex the whole db, the log file is going to grow like crazy. You'll probably have to shrink it once it's done because it'll be too big for no good reason.
August 2, 2011 at 9:43 am
@SQLFRNDZ (8/1/2011)
GSquared (8/1/2011)
Regularly shrinking the database files is usually just a good way to slow the whole server down. It causes file fragmentation, locks during auto-growth, and a host of other issues. I've never yet seen it actually solve any real problem, but I've seen it cause a lot of problems for a lot of people.The above said are really true and in addition your backup chain also breaks and point in recovery is not possible in case of sudden failure.
A shrink of the database does not break the log chain. A shrink of the log does not break the log chain.
What breaks the log chain is a switch to simple recovery.
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
August 2, 2011 at 10:01 am
Thankyou Ninja,Gilamonster.
August 2, 2011 at 10:04 am
GilaMonster (8/2/2011)
What breaks the log chain is a switch to simple recovery.
Or losing / deleting the log backups on the drive. More rare but I've seen fat fingers before ;-).
August 2, 2011 at 10:04 am
sqlbee19 (8/2/2011)
Thankyou Ninja,Gilamonster.
You're welcomed, come back anytime!
August 3, 2011 at 6:57 am
It would be so great if you could restore a database to its minimum database and log (and be able to set to simple before the restore) size - would save so much hassle restoring.
And I have used virtual restore products but with our very high throughput OLTP databases the servers prefer physical to virtual databases unfortunately. Virtual restores are great for doing compares though so nice one Redgate.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply