0

I have a big SQL Server database (~1.5 TB) and i didn't take log backup of that database for last two weeks for some reason. Today i found that the .ldf file of that database became huge around 1.5 TB. So i took the transaction log backup and then shrinked the .ldf file to fix the space issue. I used the sql job(from maintenance plan) for taking transaction log backup and the transaction log backup taken after two weeks came up to 2TB. Now that the space issue is solved,i tried to delete the log backup(~2TB) but it says that i dont have permission to delete. I took another log backup which is like 200MB and i am able to delete those. But the first huge log backup i am not able to delete. I checked the permission,and the domain account using which i logged into the server belongs to admin group.

I even used the sql job to scrub old backup,but even that fails.

Only this huge log backup has the security part as shown in the screenshot.

enter image description here

All the other log backup taken after that huge lock backup has the security tabs as shown in the second screenshot below. enter image description here

Any idea what is going on.?

If i stop the sql service/sql agent service ,then can i delete this .trn file? I wanna try that sometime when no one is using the database.

I was able to delete the 2TB log backup file after i restart the server. Not sure why such a lock happened.

  • 1
    SQL Server can’t enforce file system permissions... sounds like a Windows problem related to file size or rights, regardless of the extension of the file. – Aaron Bertrand Aug 7 '19 at 16:49
  • @AaronBertrand : i don't think it has to do with the type of file like you said.I generated log backup using the sql job(from maintenance plan) and using the same job i created few more log backups(every one hour) and all those .trn files i am able to delete.The first .trn file alone i cannot delete.NETWORK SERVICE is the owner for other .trn files,the first one doesn't show the owner also. – user9516827 Aug 7 '19 at 19:52
  • I know. I said that because it might be more relevant (and you may get better help) on serverfault or superuser. It’s likely the case that if that was a 2TB text file you’d have the same problem. – Aaron Bertrand Aug 7 '19 at 19:54
1

The underlying partition was initialized using MBR (master boot record) instead of GPT (GUID partition table), and you've broken the 2TB file size limit.

I'm willing to bet that if you ever replay that log backup, it will fail because the backup would have stopped writing when it reached the 2TB boundary.

You will need to reinitialize the partition using GPT. As far as I recall, this is a destructive operation, meaning that you will lose any data on the drive, so back up all files on the D: drive (except that big one) before doing anything else.

Once it is reinitialized, format it as NTFS with 64KB block size (perfect for SQL Server backups), and turn on backup compression inside SQL Server to prevent this kind of problem in the future.

If SQL Server is using this drive for its data files as well, you will definitely need to arrange some downtime for this maintenance. Good luck.

|improve this answer|||||
0

On the security tab, click Advanced and take ownership. enter image description here

Once that is done, go back to security tab and grant your user full control, ownership does not imply full control. You should now be able to delete it.

Another option may be the get to the folder using an admin share (ie \servername\c$) and try deleting file.

|improve this answer|||||
  • Tried first method to change the owner.When i try to do that i message that "You do not have permission to view or edit this objects's permission settings." – user9516827 Aug 7 '19 at 19:07
  • Tried and got to the folder/file using the network share.But when i delete the file dissapeared and comes back when i refresh.Tried to change owner also ,but got the same message as mentioned above. – user9516827 Aug 7 '19 at 19:08
  • Other log backups generated has owner as NetworkService as i generate log backups using the sql job.So this one(one that is not getting deleted) also should have the same owner.But when i check in the security/Advance ,it doesn't display owner name – user9516827 Aug 7 '19 at 19:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.