Solved

To delete mdf file

Posted on 2011-03-14
12
865 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
 

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

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…
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…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
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…

758 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

22 Experts available now in Live!

Get 1:1 Help Now