?
Solved

To delete mdf file

Posted on 2011-03-14
12
Medium Priority
?
930 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
[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
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 1600 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
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance 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 400 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

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

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

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…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
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.
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…
Suggested Courses

771 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