SQL 2008 R2 - files using a lot of space

SQL 2008 R2 SP3 installed on Server 2008 R2

Only one database is being hosted from this server and it's stored on D:\

C:\ is filling up and we determined the following two files are the cause:

sql screenshot
We don't have a dba so I need the experts support.

Question 1:  I assume performance monitor is enabled somewhere?  Can this be disabled and Performance_Metrics.mdf be purged?
Question 2:  tempdb.mdf hasn't been written to in weeks.  What's up with that, and why is it taking up so much space?  Can it be purged?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Darrell PorterEnterprise Business Process ArchitectCommented:
By default, tempdb is placed on the same drive that SQL Server is installed on. This can impair performance as tempdb is frequently used to store temporary tables and objects. If the hard drive is being accessed for other functions, it can result in sluggish performance by the database as well as any software that is using the database. For optimum performance, tempdb should be on a SATA drive instead of an IDE drive and should not be on the same drive as the SQL Server software or the operating system (boot drive).
1. Open SQL Server Management Studio.
2. Connect to the desired server.
3. Click the New Query button.
4. Copy and paste the following into the query pane:
USE master;
 MODIFY FILE (NAME = tempdev, FILENAME = '[new location]\tempdb.mdf');
 MODIFY FILE (NAME = templog, FILENAME = '[new location]\templog.ldf');

Open in new window

5. Change [new location] in the pasted code (for both the tempdb.mdf and templog.ldf files) to the path of the new location.
6. Click Execute.
7. Go to the Control Panel and then Administrative Tools. Select Services.
8. Stop and restart SQL Server (MSSQLSERVER).
9. Go back to SQL Server Management Studio and open a new query pane.
10. Execute the following to verify that tempdb was moved to the desired location:
 SELECT name, physical_name
 FROM sys.master_files
 WHERE database_id = DB_ID('tempdb');

Open in new window

11. You should see the path to the new location in the physical_name column.
12. If the move was successful, go to the old location and delete the tempdb.mdf and tempdb.ldf files as they are no longer needed.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bmsandeAuthor Commented:
Thanks for your feedback, we will relocate tempdb to D:\, where SQL and O/S are NOT installed.  

Any idea about Performance_Metrics.mdf ?
Vitor MontalvãoMSSQL Senior EngineerCommented:
MDF, NDF and LDF are all database files. First rule for SQL Server is not to store database files in C:\ since will fill the drive fast.
I wrote an article on how to move databases. Please give a read in this article and follow the instructions to move the database to another disk.
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

bmsandeAuthor Commented:
Does anyone know how Performance_Metrics.mdf was created?  It's not a database listed on our server, and it's constantly being updated (based on modified date).  It's growing out of control.  Please help us stop/disable whatever may be causing this.

Thanks in advance.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Do you have another SQL Server instance in your server?
If not, then try to attach the database into your SQL Server instance as check if there's any error during the process.
bmsandeAuthor Commented:
We do not have another SQL instance on this server.  This is in prod, I'm afraid someone enabled performance metrics/monitor??  I'll try attaching but it's interesting the modified date keeps refreshing to a recent time.
Vitor MontalvãoMSSQL Senior EngineerCommented:
That database must be attached to another SQL Server. Did you try to attach the file in the current SQL Server instance?
Darrell PorterEnterprise Business Process ArchitectCommented:
Quite likely someone turned on SQL Performance Monitoring.  Check to ensure performance monitoring is disabled if no one is actually looking at SQL performance.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.