delete an old .ldf file

Question

delete an old .ldf file

sqlfriend

SSC Guru

Points: 52781
More actions
March 2, 2012 at 10:05 am

#269407

I got an alert on a log drive for one of my sql servers.
I noticed there is a log file which is huge, but I don't recall what database it belongs to.
So I did a query, select * from sys.masterfiles, but I cannot see this .ldf file in the list.
And by looking at file property, I see it is accessed a year ago, so I tried to delete it.
But I cannot delete it, it says you need permissions to delete the file.
I am the server box admininstrator, why I cannot delete it?
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

okbangas SSChampion Points: 11773 More actions · Answer 1

Please have a look at this article: Managing Transaction Logs in SQL Server[/url]. Transaction logs are crucial to SQL Server, and you really have to understand how it works.

Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

Concatenating Row Values in Transact-SQL[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

If it belongs to a database that was detached, the ownership could be solely to the SQL Server process. See if you can take ownership, if you can, see if you can rename it. If you can that means it's definitly not part of an in-use database (rename it back afterwards).

Is there only one instance on the server? If not, have you checked sys.master_files for all instances? If the ldf is definitely not listed in there then it's not part of an attached, in-use database.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sqlfriend SSC Guru Points: 52781 More actions · Answer 3

GilaMonster (3/2/2012)
If it belongs to a database that was detached, the ownership could be solely to the SQL Server process. See if you can take ownership, if you can, see if you can rename it. If you can that means it's definitly not part of an in-use database (rename it back afterwards).
Is there only one instance on the server? If not, have you checked sys.master_files for all instances? If the ldf is definitely not listed in there then it's not part of an attached, in-use database.

Thanks, these are good tips.

There is only one instance, and I think someone detached it, and after I add my login to full control of the file, I am able to either rename it and rename back. It is not in the masterfiles list either, so I can delete it successfully.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

I would personally just rename it for a little while (make sure it's not used for something siily). Is there a matching mdf anywhere?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sqlfriend SSC Guru Points: 52781 More actions · Answer 5

Yes, the mdf file is there too, and I can take ownership and I can rename it, and it is not existing in the masterfiles list

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Ask around, make sure no one wants the database, maybe attach them and take a backup of the database (and put it somewhere safe) before you delete the files.

Don't want to delete them then find out tomorrow it's actually needed in some odd circumstances.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Perry Whittle SSC Guru Points: 234013 More actions · Answer 7

sqlfriends (3/2/2012)
I am the server box admininstrator, why I cannot delete it?

being an administrator on the server does not necessarily give you full access, if the folder\file ACLs have been modified\set to a restricted user list which does not include administrators you will encounter the issue you are seeing now.

As an administrator even with no permissions on the file you will always be able to take ownership and reset the ACLs. Backup the files before you delete them just in case 😉

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

"Ya can't make an omelette without breaking just a few eggs" 😉

Jeffrey Williams SSC Guru Points: 90314 More actions · Answer 8

Perry Whittle (3/2/2012)
sqlfriends (3/2/2012)
I am the server box admininstrator, why I cannot delete it?
being an administrator on the server does not necessarily give you full access, if the folder\file ACLs have been modified\set to a restricted user list which does not include administrators you will encounter the issue you are seeing now.
As an administrator even with no permissions on the file you will always be able to take ownership and reset the ACLs. Backup the files before you delete them just in case 😉

Also - when a user detaches a database the ownership and permissions are changed (in some cases) to just that user. Ran into this several times when moving databases and just had to add myself to the permissions of the files.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs