March 16, 2009 at 7:10 am
I've got a sql2k db server (sp 4) that was recently upgraded from server 2000 to 2003 R2 and since the upgrade we're having some tempdb growth issues. Every couple days I have to restart the db because the data files are autogrowing and filling up the drive. With the tempdb files are at their default size, there's 134GB free space on this drive. Only other thing on the drive are backup files.
Tempdb is made up of 4 data files (default size 3GB each). This is a 4 proc server with trace flag t-1118 as a startup parameter.
DB options: recovery set to simple, AWE is enabled (box has 8GB of RAM - sql configured to dynamically grab up to 6GB). No code has been added/changed since the upgrade.
Any insight would be appreciated. Thanks.
_____________________________________________________________________
- Nate
March 17, 2009 at 4:10 am
Things are being put into tempdb because SQL Server needs to put them there. The trick is finding out what is triggering this behaviour. Did the hardware also get upgraded? If so it is possible that SQL Server is choosing a different execution plan than before due to more memory or faster CPU.
If you have not refreshed all your statistics since the upgrade this may help. Also check if any indexes are fragmented and rebuild those. This may get SQL Server using the old access plans again.
Finally, are your data volumes increasing, or has a new version of any of your applications been installed. Increased number of rows in your databases will require increased space in tempdb to deal with certain queries. If the application was changed, then focus on the SQL statements that have changed - these may be the culprit.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
March 17, 2009 at 6:59 am
Hi Ed, unfortunately there were no hardware upgrades just the OS.
There have been no substantial code changes and we've run a thorough reindex/update statistics twice since the upgrade. We've also recompiled our SPs to flush out any outdated query plans.
I've got perfmon and traces set up to try and pin down any abnormal activity to the tempdb but it just seemed odd that this started happening frequently (it's also become an issue on another server with the same sql and os versions) since we upgraded.
_____________________________________________________________________
- Nate
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply