Hurting for C: Space!

First off--I know crap about managing Sql.  Let's get that out of the way.

Okay.  My (company's) sql 2005 server really needs C:

I have a D: with loads of space, but I need help in pointing things there.

Biggest bang-for-the-buck would be somehow mapping this ginormous tempdb.mdf file used by the system database, from the C: drive (where it currently is) to the D: drive.

So, all the points go to someone who can help me do that.  Get tempdb.mdf living on D: instead of C:.

I'll give some of the points, or some MORE points, if you can also help me with this wrinkle:  There are a couple of AdventureWorks.mdf files on my C: that I'd love to delete.  Unfortunately:

(1)  They're supposedly "in use" and can't be deleted.
(2)  I cannot, for the life of me, figure out which DB is using them.  I've been through the properties of every single database, and could not find one that' spointed to one of these AdventureWorks files.

Please, though, bear in mind that Job #1 would be me pointing tempdb.mdf to D:

Thanks very much!
Who is Participating?
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
Deleting the Adven db
1.Run the sp_Who2 and identify the process id which are running on that DB and KILL those process ids
2. Then delete that DB from SQL Server management studio

Moving tempdb to a different drive
brykerAuthor Commented:
sp_who2 lists 54 processes, none of which list an AventureWorks in their DBName field.

Still, those files cannot be deleted.  Reported as being in-use.
brykerAuthor Commented:
Okay, I figured out how to move tempdb.mdf.  And moved it.

Still open to any expert advice on the AW dbs, though.

Aneesh RetnakaranDatabase AdministratorCommented:
>Still, those files cannot be deleted.  Reported as being in-use.

can u stop the sql server service and delete it ? or else it may be used by some other processes
brykerAuthor Commented:
Early on, I was ready to delete it and be done with it.  But at this point, I'm starting to wonder if there's something hanging onto it that makes this more important than I'd thought.

Are there any other ways to audit what's using this database, that you can think of?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.