How to get a copy of tempdb.mdf

Posted on 2011-10-18
Medium Priority
Last Modified: 2012-06-21
I want to get a copy of current tempDB.mdf, But it cannot use detach or backup methos against tempDB, while copying file, it says it is being used. How would I get it? thanks.
Question by:shirleyz8821
  • 4
  • 3
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36988759
stop sql server, and copy the file.

but why do you want to copy that file?

Author Comment

ID: 36988808
because it is growing houg and run out of space. The vender want to see the tempDB.mdf, I think when stop sql server, the tempDB will downsize to the default size which is 8M. How to get the current huge one(tempDB.mdf)?
LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 2000 total points
ID: 36988833
I see.

you cannot do that. you can only "look" into the tempdb being connected to the current db, so your vendor has to connect on your instance.
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database


Author Comment

ID: 36988851
Thanks angelIII, how to trace/track queries to find out which makes large tempDB? (sql 2000)

Author Comment

ID: 36988918
Thank you again, I am looking for a script which runs a trace (scheduled script) which will track the queries that are causing tempdb to grow. Pls advice.. wait online.

Author Comment

ID: 36989749
I re-create a ticket and give you the point to you on my first question.

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question