on 02-17-2009 8:33 AM
Hi All,
We are using SQL Server 2005 .The tempdb size has grown and second thing is that sqlser.exe as checked in Task Manager is taking around 41% CPU and memory utilization is also high.
Can anybody let me know how can I clean up tempdb ,as we don't want to add one more log/data file.
Also ,what should be done to fine tune the SQL performance.
Hope to hear from you soon.
Thanks in Advance
=============
Ajay
HI Ajay,
Use the following command and let me know the result.
dbcc shrinkfile('tempdb.dbf',10);
Rgds,
SK
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thanks Rohit for the valuable information.
Indeed the tempdb has lot of free space now.
I am not good with commands ,even the command given earlier ,I am not able to run that.
Do you have any doc/link related to how to use the commands to operate SQL db ,that will be of great help.
Thanks again.
Regards
Ajay
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Just open the SQL Server studio,connect to the database and in the query analyzer
run the command,it will work fine
You can refer to the following link for more commands:
Also you can run the commands through SQL Server 2005 Command Line Tool u201CSQLCMDu201D
you can use the following link for that
Let me know if this helps
Rohit
Thanks Rohit.
The MS SQL pfd for tuning is nice.
Subhadip
Can you ps let me know if I need to run the command from DB directory etc.
Can I use SQL studio for the dbcc ,we are running dccc check data base integrity ,may shrink file dbcc is also present there.
Will read teh guide ,but anyou tell me in case why sql.exe is using lot of cpu/memory .We are running multiple instance on the same host ,all using SQL as db.
Meanwhile ,we have restarted the system as well.
Regards
Ajay
Edited by: Ajay Sandal on Feb 17, 2009 3:20 PM
Edited by: Ajay Sandal on Feb 17, 2009 3:21 PM
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello Ajay,
You can not clean Temp DB as such,
It will get free whenever you start and stop the system.
SAP recommends for temp DB that it should be at least twice the size of largest table.
regarding performance tuning its a very generic question,can u be very specific in what areas of database u want tuning.
Also microsoft has released "
SAP with Microsoft SQL Server 2005:
Best Practices for High Availability, Maximum Performance, and Scalability
SQL Server Technical Article"
It is very useful,i have that woth myself but it is available on site also.I will try to get u the link and more info.
Rohit
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello AJay,
Regarding Temp DB,please go through the following articles,they are very useful
1. http://msdn.microsoft.com/en-us/library/ms190768.aspx
2. http://msdn.microsoft.com/en-us/library/ms175527.aspx-Temp DB perfromance
For SAP,Microsoft has release a paper,this is the link:
Let me know of any questions
Rohit
User | Count |
---|---|
101 | |
13 | |
13 | |
11 | |
11 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.