SQL 2012 Backup with deleting transaction logs

Hello,
I have SQL 2012 Database setup in Full Recovery mode.
I have Maintenance task setup with backing up every day 1 Full Backup & hourly backup of transaction logs.
In the plan I have setup to remove the data older than 1 day, however the data is still sitting on disk taking lot of space.

Now, here is what I want to achieve:
The full backup to be done at midnight & transaction log backup every hour, once the next full backup kicks in  & is successfully accomplished, the older full backup & logs should be deleted from the disk.

please help.

Regards,
A
LVL 11
AcklesAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Can you post the definition for the delete data older than 1 day?
AcklesAuthor Commented:
i have it in the GUI in maintenance plan
Vitor MontalvãoMSSQL Senior EngineerCommented:
I know but will be good if you can post here a screenshot of it.
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

AcklesAuthor Commented:
Vitor MontalvãoMSSQL Senior EngineerCommented:
As I thought. That option isn't for delete old backup files but to set the expiration date of the backup. Is better you turn off that option immediately since you won't be able to use backups older than 1 day to restore.

You have a task only for delete old backups.
Vitor MontalvãoMSSQL Senior EngineerCommented:
The correct name for that task is Maintenance Cleanup Task.
Vitor MontalvãoMSSQL Senior EngineerCommented:
And here's an article that shows how do it step-by-step.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AcklesAuthor Commented:
Thanks a lot, it seems I was missing the whole thing....

However, I have a folder Backup & in it several subfolders as per the name of Databases ,
I want to select the Backup folder & wants the task deletes in all sub folders.

How to do that
Vitor MontalvãoMSSQL Senior EngineerCommented:
Use the Files based on an extension option and do not check the Include first-level subfolders. This will make to delete the files with the extension you provide (do not include the dot '.') from the specified root folder and all sub folders.
AcklesAuthor Commented:
Does the same work for transaction logs too?
With extension trn
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes. You can provide the extension that you want.
AcklesAuthor Commented:
I did as you mentioned, however it doesn't delete files in the subfolders
BAK.PNG
Trn.PNG
AcklesAuthor Commented:
see attached screenshots, it doesn't delete in subfolders....
Vitor MontalvãoMSSQL Senior EngineerCommented:
How are you running the task?
There's any error message?
Also check if there are files older than 2 days and that's include the time as well.
AcklesAuthor Commented:
I m running the task from sql agent, run one.
There are no errors, there are files older
Vitor MontalvãoMSSQL Senior EngineerCommented:
Check the logs. From here I can't help much but try to check for extra space in the folder name or file extension and also check for write permissions in the backup folders.
AcklesAuthor Commented:
The logs show job completed successfully, the problem is only in deleting the files, the job for writing backup works fine, so it can't be the problem of rights....
AcklesAuthor Commented:
Well, I see the problem is propagating the job in subfolders, when I run the same job in one specific folder, it works perfectly, however if I point it to the root folder, it executes successfully, but doesn't do anything......
Vitor MontalvãoMSSQL Senior EngineerCommented:
I see the problem is propagating the job in subfolders,
Strange, that's the behavior on the next version of SQL Server (2016). The currents one allows you to delete files from subfolders.
What if you try to specify only B:\ and then all subfolders from there?
AcklesAuthor Commented:
Doesn't work either....
Vitor MontalvãoMSSQL Senior EngineerCommented:
I was investigating and it might be permissions on subfolders. Please check this article.
AcklesAuthor Commented:
Vitor,
Just before i check, if that is the reason then why the job is doing what i want when i point it directly to the sub-folder?
Vitor MontalvãoMSSQL Senior EngineerCommented:
By the article it can happens if only some subfolders has permissions. You might tested on a subfolder with permissions.
AcklesAuthor Commented:
i checked the permissions & all is inline
Vitor MontalvãoMSSQL Senior EngineerCommented:
Don't check. Just go to the root directory and give the full permission to SQL Server account and all subfolders from there. You won't lose nothing with this and you'll be sure if this was the issue or not.
AcklesAuthor Commented:
I did as you mentioned, but no luck....
AcklesAuthor Commented:
Please see this:
http://www.experts-exchange.com/questions/28052279/SQL-Server-2012-Maintenance-Cleanup-Task-issue.html

I have permissions exactly as this, but configured in a way as Domain Account-

It has to be some sort of bug or I don't know because it's only happening when root folder is selected.
Vitor MontalvãoMSSQL Senior EngineerCommented:
They're referring to the SQL Server Agent account. Did you give full control permissions to that account on the backup folders and subfolders?
Also, you're not running the task manually, right?
AcklesAuthor Commented:
The permissions are right.
When I run the task manually it works, but only when pointed to sub folder, when I point to root folder it doesn't work
Vitor MontalvãoMSSQL Senior EngineerCommented:
I'm not sure but I think when you run it manually it uses your credentials and not the one from SQL Server Agent.
Can you schedule the job to start in the next 5 minutes (or less) and then wait to see how it behaves?
AcklesAuthor Commented:
alright, let me try
AcklesAuthor Commented:
It ran, but without any deletion, no errors reported.
Vitor MontalvãoMSSQL Senior EngineerCommented:
I was afraid of that :(
Maybe we are too addicted to this permission issue. We'll try to check any other thing else.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Ok, for my last researches this can be a bug or if not you can try to run the script instead of the job to check if something is returned.

If is a bug you need to apply the last hotfix. Which version of SQL Server are you using (SELECT @@Version)?

To run the script open the Maintenance Cleanup Task and click on the "View T-SQL" button and copy the script and paste it to a new query window and run it.
AcklesAuthor Commented:
Version: 11.0.3128 (X64)
T-SQL : EXECUTE master.dbo.xp_delete_file 0,N'B:\Log',N'TRN',N'2015-06-23T16:52:04'
Vitor MontalvãoMSSQL Senior EngineerCommented:
You have SQL Server 2012 SP1. There's already a SP2 and CU6 that you can download and apply on the instance.

EXECUTE master.dbo.xp_delete_file 0,N'B:\Log',N'TRN',N'2015-06-23T16:52:04'
Did you try to run it in SSMS?
AcklesAuthor Commented:
I can only do it over the weekend
I executed the command you mentioned & it didn't work.

Seems will have to wait till weekend.


Well, Enjoy the Sun, it's sunny outside as we both are in the same city ;)
Vitor MontalvãoMSSQL Senior EngineerCommented:
I can only do it over the weekend
To be honest I don't think will solve this issue but we should try everything (sanity check) :)

I executed the command you mentioned & it didn't work.
No errors returned?

Well, Enjoy the Sun, it's sunny outside as we both are in the same city ;)
Yes, sir ;)
AcklesAuthor Commented:
No errors
It will work, if not with this then something else, that's the whole fun of IT
Vitor MontalvãoMSSQL Senior EngineerCommented:
Oh, if you want to try, run with the '\' at the end of the folder name (B:\Log\). Also a sanity check.
AcklesAuthor Commented:
doesn't work
AcklesAuthor Commented:
I ended up making individual job for each cleanup, but your initial response is what I was looking for, if you come across anything in future re this, please share.
Thanks.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.