on 04-23-2014 7:57 AM
Hi Gurus,
We have recently upgrade the ECC 6.0 to EHP6, Now we have analyzed the
TempDB Size increasing rapidly, now TempDB size is 78.6 GB out of 100GB
disk partition, So i request to you please help to resolve this issue
or how to reduce the size automatically and manually of TempDB.
Regards
Ashwani
Ashwani,
Are you running a full DBCC CHECKDB every night? I wasn't sure from the discussion if that was happening or not. DBCC CHECKDB can use up some TempDB space while running. If you are doing this, you can reduce the frequency significantly, as you don't need to do this daily. Try turning off this job, then shrink your TempDB again, and see if the next day it has still grown or not. If not, then you've probably found your culprit. If so, or if you aren't running this daily, then you can use the suggestions from Note 1951819 (How to check which process makes the transaction log get full in SQL Server tempdb) to find the process that is filling it and causing it to autogrow. When you've found the program or job that is the cause, then you can troubleshoot/fine-tune it to use less temporary space.
Basically, for an ECC system, you shouldn't need very much TempDB space, perhaps 10-15 GB is all. BW systems use a lot of TempDB, but not ECC.
Regards,
Matt
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Ashwin,
Did you / can you try restart the server and see if it helps, below url also shows how to check the tempdb size and reduce the same.
http://dbadiaries.com/how-to-shrink-tempdb
You can use the below command to reduce it as well.
dbcc shrinkfile('tempdb.dbf',10);
Hope it helps.
Regards,
Deepanshu Sharma
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Deepanshu,
Thanks for the prompt support......
After reboot the system TempDB size reduce automatically, after reboot the system TempDB size is only 1GB,I found next day TempDB size incread the same size around 78GB.
but DBCC schedule in night. I think TempDB size increased after run the DBCC.
Please suggest the best practice to manage the TempDB Size.
Regards
Ashwani
That's a common problem.
What takes most of space, are facts generated by DBCC for logical consistency checking of data. You may choose to skip this check by:
DBCC CHECKDB('mydb') WITH physical_only
Below url will help you on that
Working with tempdb in SQL Server 2005
http://technet.microsoft.com/en-us/library/cc966545.aspx
Troubleshooting Insufficient Disk Space in tempdb(2008)-
http://msdn.microsoft.com/en-in/library/ms176029%28v=sql.100%29.aspx
Check this as well..
http://www.sqlperformance.com/2012/11/io-subsystem/minimize-impact-of-checkdb
Hope this will help.
Please take time and read and then make changes.
It will resolve your issue
Regards,
Deepanshu Sharma
User | Count |
---|---|
89 | |
10 | |
9 | |
9 | |
9 | |
6 | |
6 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.