Finding Sql database files in use

Trying to minimize downtime that will occur during a P2V of a 6 year old SQL server

The SQL server was originally setup as a SQL 2005 2-node cluster, but one of the two physical servers died last month, so only one server is left in the cluster. It's been using a very slow SAN with 20 mapped drives with a total size of 3TB

My first attempts to virtualized with vCenter converter failed because the estimated time for the P2V was around 25 days over our wan link

I'm trying to find out which of the files on the server are actually being accessed/used by the database server once it boots and SQL services starts running

If I can find this list of files, I will do a P2V only with the necessary drives , and copy the rest (non-critical) files after the P2V is complete. I believe most of the server data on the mapped drives is backup data

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.

Deepak ChauhanSQL Server DBACommented:
you can find the file database files information for the instance of SQL server from the system view (sys.master_files).

select DB_NAME(database_id) as DBNAME,database_id, file_id, type, type_desc, name, physical_name, state, state_desc,size, max_size, growth from sys.master_files.

Physical_name column has the physical file name same as located on the Disk.

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
Vitor MontalvãoMSSQL Senior EngineerCommented:
What about adding a virtual node to the cluster? Then install the SQL Server there and make a failover from Physical node to the virtual one. Now you can get rid of the Physical node and add a 2nd virtual node to the cluster. Only downtime will be during the failover.
sk391Author Commented:
Thanks deepakChauhan sounds exactly like what I'm looking for.

Vitor_montalvao, I can't do that as the new virtual nodes don't have access to the same old storage system , they use new storage
Deepak ChauhanSQL Server DBACommented:
Hi sk391,

Have you done with it or need more help?
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.