SQL DATABASE File

I have a .MDF file stored in the SQL DATA directory and it is not listed on management studio
However when I try to copy if off or move it - the system says in use by SharePoint.  It has a date modified of today but not listed any where in the management studio
Lost on where it might be
MainSail2007Asked:
Who is Participating?
 
Brian ChanConnect With a Mentor DBACommented:
Hi MainSail2007,

To see if the data file is currently attached with any database, use the following code to identify if is listed with one of the database at the Physical File Name and Location column.

--***** Define Variables *****
declare @GB    float
declare @MB    float
declare @KB    float
declare @B     float

--***** Assign Variable Values *****
set @GB   =  (cast(8192 as float) / cast(1073741824 as float))
set @MB   =  (cast(8192 as float) / cast(1048576 as float))
set @KB   =  (cast(8192 as float) / cast(1024 as float))
set @B    =  (cast(8192 as float))

select db_name(database_id) as 'Database Name',      
       database_id as 'Database ID',
      (case type
            when 0 then 'Data File'
            when 1 then 'Log File'
            when 2 then 'Filestream File'
            when 3 then 'Other – N/A'
            when 4 then 'Full Text Catalog File'
            else ''
       end) as 'FIle Type',
       name as 'Logical File Name',
       physical_name as 'Physical File Name and Location',
     ((convert(float,size) * CONVERT(float,8)) / CONVERT(float,1024)) as 'Declared Size in MB',
      (case when (cast(size as float) * @GB) > (cast(1 as float))
            then (convert(varchar(16),(cast(size as float) * @GB)) +  ' GB')
            else (case when (cast(size as float) * @MB) > (cast(1 as float))
                       then (convert(varchar(16),(cast(size as float) * @MB)) + ' MB')
                       else (case when (cast(size as float) * @KB) > (cast(1 as float))
                                  then (convert(varchar(16),(cast(size as float) * @KB)) + ' KB')
                                  else ((convert(varchar(16),(cast(size as float) * @B))) + ' Bytes')
                             end)                 
                  end)        
       end) as 'Actual Size',       
      (case when is_percent_growth = 0                  
            then (case when (cast(growth as float) * @GB) > (cast(1 as float))
                       then (convert(varchar(16),(cast(growth as float) * @GB)) +  ' GB')
                       else (case when (cast(growth as float) * @MB) > (cast(1 as float))
                                  then  (convert(varchar(16),(cast(growth as float) * @MB)) + ' MB')
                                  else (case when (cast(growth as float) * @KB) > (cast(1 as float))
                                             then (convert(varchar(16),(cast(growth as float) * @KB)) + ' KB')
                                             else ((convert(varchar(16),(cast(growth as float) * @B))) + ' Bytes')
                                        end)
                             end)
                  end)
            when is_percent_growth = 1
            then (case growth
                       when 0 then 'N/A'
                       else (convert(varchar(16),(cast(growth as float))) + ' %')
                  end)
            else ''
       end) as 'Growth Method'
 from  master.sys.master_files
 order by database_id, type, size desc

Open in new window


Let's see what you got from the code first. :)
0
 
Russ SuterCommented:
Is it possible that it is attached to another instance of SQL Server installed on the same machine?
0
 
Jason clarkDBA FreelancerCommented:
it might be helpful for you:

declare @files table (
    db_name sysname,
    physical_name nvarchar(260)
)

insert into @files
    exec sp_MSforeachdb 'select "?", physical_name from ?.sys.database_files'

select db_name, physical_name 
    from @files

Open in new window

0
 
MainSail2007Author Commented:
where do I run this code??
0
 
Brian ChanDBACommented:
You can run these code in the management studio. Just connect to the server, and click the "New query" button on the top left corner, copy and paste the code in and execute it.
0
All Courses

From novice to tech pro — start learning today.