Temp DB Size Increased.

[Former Member]

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

[Matt_Fraser]

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

[Former Member]

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

[Former Member]

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

[Former Member]

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

[Former Member]

Hi Deepanshu,

SQL2012 is running on the  SAP, So I request to you please suggest the solution accordingly...

Regards

Ashwani

[Sriram2009]

Hi Ashwani

  what is the size of over all DB?

Regards

Sriram

[Former Member]

Hi sir,

DB size is 675GB.

Regards

Ashwani

[Sriram2009]

Hi Ashwani

In your system go to transaction ST04 - MS SQL DB administration tab

Select the configuration - Database options - by default it will show the SID DB

change to Temp DB, check the Status of you Temp DB any error in below?

if possible paste the screen shot?

Regards

Sriram

[Former Member]

Hi Sir,

Please find the st04 screen shot........

Regards

Ashwani