?
Solved

SQL DATABASE File

Posted on 2016-07-18
5
Medium Priority
?
71 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 20

Expert Comment

by:Russ Suter
ID: 41717906
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 2000 total points
ID: 41718172
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 10

Expert Comment

by:Jason clark
ID: 41718205
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
ID: 41719492
where do I run this code??
0
 
LVL 5

Expert Comment

by:Brian Chan
ID: 41720023
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

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