Solved

To delete mdf file

Posted on 2011-03-14
12
911 Views
Last Modified: 2012-05-11
SQL Server 2005

Physical filenames
D:\datafiles\local\acc_askari.mdf
D:\datafiles\local\acc_askari_1.ldf

Files are deattached from SQL Server
SQL Server Service is stopped

Yet I am unable to delete the files.
The error is "Make sure the disk is not full or write protected and that the file is not currently in use'

a) The disk has enough free space
b) File Property-> attributes is not readonly and not hidden
c) My Computer->Manage->Share folders->open files
    There are no items.
 
Pl Guide
0
Comment
Question by:Mehram
12 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 35135292
Hope your Database is not detached and SQL Server service is not turned off.
Just connect to SQL Server via SSMS and then drop the database which should do..
0
 
LVL 9

Accepted Solution

by:
kaminda earned 400 total points
ID: 35135406
If you are sure that the file is not actually in use (Select * From sys.Master_Files Where physical_name = '<Path To File>'), it might simply be the file ownership. I have seen files owned by the SQL Server service account as well as the user account that restored/ created the database. Try right-clicking on the file and selecting Properties, go to the Security tab, click the Advanced button, select the Owner tab, select the appropriate user/ group under the “Change owner to:” section (e.g. Administrators, your account, etc.), click Apply, then OK out of the windows. Hope this helps.

David Zahner

-------------------------

Hope this will help you
0
 
LVL 7

Expert Comment

by:mkobrin
ID: 35135424
If you have enterprise manager you can delete the file by right clicking the db and selecting the option to delete, if there are users using it, it will not allow you to do this. If that is the case then select the option to detach, and it will tell you there are users connected and give you an option to disconnect them. Select the option to disconnect them and then cancel the detach. You should then be able to delete the database.
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:Mehram
ID: 35135483
Hi kaminda

Select * From sys.Master_Files
Where physical_name like '%d:\datafiles%'
and   physical_name like '%askari%'

Returns none so the file is not present in sys.master_files

<<try right-clicking on the file and selecting Properties, go to the Security tab, click the Advanced button, select the Owner tab, select the appropriate user/ group under the “Change owner to:” section (e.g. Administrators, your account, etc.), >>

Trying different ways in security tab yet not success

0
 
LVL 9

Expert Comment

by:kaminda
ID: 35136207
Does your login account displayed under owners of the file?
0
 
LVL 6

Assisted Solution

by:jonaska
jonaska earned 100 total points
ID: 35136296
If the files were created by another user (which could be the case). Than it's possible that the files are owned by that user.
Try to take ownership of the files first.
0
 

Author Comment

by:Mehram
ID: 35136464
HI kaminda:

<<Does your login account displayed under owners of the file? >> I think yes.

I have noticed that those files which I could delete have this entry in security tab and advanced option's permission tab.

SQLServer2005MSSQLUser$AVSRV1$MSSQLSERVER (ACCOUNTS\SQLServer2005MSSQLUser$AVSRV1$MSSQLSERVER)

but this entry is not present in our subject/problematic file.
0
 

Author Comment

by:Mehram
ID: 35136472
Pl note that , I login with administrator account.
0
 
LVL 9

Expert Comment

by:kaminda
ID: 35136489
Does your admin account owns the file? Anyway have you tried deleting it from SSMS UI itself?
0
 

Author Comment

by:Mehram
ID: 35136490
Hi jonaska

<<If the files were created by another user (which could be the case). Than it's possible that the files are owned by that user.
Try to take ownership of the files first.>>

The ownership subject is new to me. I want to remove every permission/ownership from the file but dont know how to do so.
0
 

Author Comment

by:Mehram
ID: 35136515
Hi experts

Yes, the problem was ownership/permisson.

I did this.

There is a button allow propagate inheritable permission etc. I clicked it and applied and then deled the file. And the file was sucessfully deleted.



0
 

Author Closing Comment

by:Mehram
ID: 35136523
Best regards.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

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…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

726 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