Solved

SQL DATABASE File

Posted on 2016-07-18
5
45 Views
Last Modified: 2016-07-20
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
0
Comment
Question by:MainSail2007
5 Comments
 
LVL 20

Expert Comment

by:Russ Suter
Comment Utility
Is it possible that it is attached to another instance of SQL Server installed on the same machine?
0
 
LVL 5

Accepted Solution

by:
Brian Chan earned 500 total points
Comment Utility
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
 
LVL 7

Expert Comment

by:Jason clark
Comment Utility
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
 

Author Comment

by:MainSail2007
Comment Utility
where do I run this code??
0
 
LVL 5

Expert Comment

by:Brian Chan
Comment Utility
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

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

762 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now