Solved

SQL DATABASE File

Posted on 2016-07-18
5
55 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
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 500 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 7

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

863 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

21 Experts available now in Live!

Get 1:1 Help Now