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

Aneesh RetnakaranDatabase 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

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