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.
All the other log backup taken after that huge lock backup has the security tabs as shown in the second screenshot below.
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.
Best Answer
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.