Temp file growth - Microsoft SQL Server

Dear Experts,

We recently noticed a tremendous growth of temp file in our MS SQL Server backup folder.
The file name is -02052015.  This file keep grows every now and then.  This morning I deleted the same file with 20 gb. again it got created and the size is not 13 gb.  

I couldn't delete the file without stopping the sql service.  This is something strange and I couldn't findout the reason.

Please advice.

Thanks in advance,

Tajudeen
LVL 1
Wade AdamsIT TeamAsked:
Who is Participating?

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

x
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.

Deepak ChauhanSQL Server DBACommented:
TempDB is a system database and whenever server restart a fresh TempDB database get created. You can check the database create date by executing this query , create date will be same when you last time restart the SQL service.

select name, create_date from sys.databases
where name='tempdb'

You stopped the services and delete the TempDB files from the DISK and it recreated again because in the system catalog file location entries are saved.

Check the file location using this query.

select name, create_date from sys.databases
where name='tempdb'
Wade AdamsIT TeamAuthor Commented:
Dear Deepak,

Thank you for your email.  

A temp file named -02052015 (current date) is getting created in "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup" (default backup location) and it is keep on growing.

If the issue related to tempdb database it should have been there before as well. Till last week there was no issue like this.

Expecting more suggestions.

Thanks in advance,

With Regards,

Tajudeen.
Deepak ChauhanSQL Server DBACommented:
Hi Tajudeen,

If this file was not there, seems it is recently added in the TempDB database. SQL server never add or create any file itself.

You can check how many files are linking with TempDB and location of files on disk.

Run this query in sql server management studio.

use tempdb
go
select file_id, type, type_desc, name, physical_name,state_desc
from sys.database_files.

Reason behind TempDB growth.
sorting operation.
#TempTables.
DBCC CheckDB.
DBCC DBREINDEX or similar DBCC commands with 'Sort in tempdb'.
Large resultsets involving union, cursors, temp tables, table variables.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Deepak ChauhanSQL Server DBACommented:
In addition TempDB is very critical database and any misconfiguration of database file can affect the overall server performance.
Wade AdamsIT TeamAuthor Commented:
Dear Deepak,

Thanks again for your reply.

The query is to just show the .mdf & .ldf file location of the tempdb only.
I have attached a print screen for your clarity on this.

I agree the facts that you have mentioned about the tempdb. :)

But the question here is what is  the file named "-02052015" and why it is growing ?
if the issue is related to tempdb, only the .mdf or .ldf file size should be increased.

This server is running for years with many databases.  We have not made any changes recently.
Also I checked any recent windows updates, nothing found.

Nothing has been changed.  I tried putting off the Agent services.

I am expecting serious advice from experts.

Thanks,

With Regards,

Tajudeen.
Wade AdamsIT TeamAuthor Commented:
Please find the print screen for the above message.
sql1.bmp
Deepak ChauhanSQL Server DBACommented:
I was in impression temp DB file is growing there. After checking the screen shot , it is not an issue. You use sysinternal process explorer tool to identify the file handle.
Wade AdamsIT TeamAuthor Commented:
I spot the problem by making each database offline one by one. Finally I could find the problematic database.  We are calling the application vendor to rectify the issue soon.

Thank you very much for your effort.

Cheers...

With Regards,
Tajudeen.

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
Wade AdamsIT TeamAuthor Commented:
As I mentioned, I solved the problem by making each db offline and could find the problematic db.
I could not give enough time for other experts give solution for my problem.
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 2005

From novice to tech pro — start learning today.