Maintenace Plan

We have maintenace plan which runs each day at 10 pm which includes full backup of all the user database and transaction log backup scheduled at same time. Is it gud practise to do so. does it necessary to perform log backup after full backup?

This is only t-log backup we have at the same time with full backup . but the order of backup in full then log.

my another question is  in the above maintenace palne  my full backup get  sucessed yesteday but it t-log backup have been failing form last 3 days. Moreover, my full backup was also failed  day before yesterday and 2 days before alone with t-log backup. Because of that my f: contains only few MB left which is the log file location for all the user database. In such
situation what should by my approach to increase the disk space ?

Thanks in advance
manojbabaAsked:
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.

Moomin83Commented:
Hi Manojbaba

I would start by checking if all the data on the F: drive is relevant. Clear out or archive info that is not in use / relevant.
Secondly, follow the link below. It has the best practices for various versions of SQL as you do not specify the version you are using.
SQL Best Practices

Regards
Robert

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
jogosCommented:
Link to best practices is usefull

I guess your backup is failing because of lack of space, but you can check that in the errorlog or your server eventlog why backup failled.
Use the cleanup task to remove old backups. But be carefull, when backup fails you don't want to delete old backups while you might delete the last backups you have without having a new one. So one failure can get your backupdrive filled more .... causing probably space problems
So it's very important to act quickly on a failled backup and monitor the free space of your backup-drive. Now take  Moomin83's advice and manualy clear irrelavant content.


A t-log backup  you don't take in the same run as your full backup. A t-log backup you take every 15 minutes, every 1 hour or every 4 hours  (depending on your wish) to be able to have a point in time recovery  but not being stuck with a gigantic tlog-file.  And in this frequecy you try to prevent it from running at the same time as your full backup.
manojbabaAuthor Commented:
Thanks for the reply the drive is dedicated to the log file only. I don;t know why the t-log backup is scheduled just after full log backup one time only. When I check the windows log
information it says the owner of(  domain account ) of job  daily backup (job name ) don;t have server access.

But i am wondering why it gives the messages like this . This job get succeed just a day before (I mean the full backup not the log backup).

Database connectivity is in windows authentication but i don;t about the owner of maintenance and when i see the sql server agent job it runs under the window account as well having sysadmin previleages.

I
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

manojbabaAuthor Commented:
hi jogos  and moomin83 my backup drive is in the nas drive there will be  enough space   and the  drive which is running out space is drive for the database  log file only.
Because my log backup has been failed  for last 4 days  and  the drive space is almost filled. But when i see the application log it says that job is failed because the  job owner(domain/user ) doesn;t have permission for the database.

But when i see the owner of the  job ( backup)  it has different name than  what  error is showing.The job owner name which i saw in the database has sysadmin right .

Here I would like add that the backup jobs are created in maintenace plan in sql 2008.

When I query the sys.server_principal there is  no login which was  shown in the  in the application log.I don;t know how to find out how that owner shown in the application log is associsted in the  job . I don;t have any clue . Can you suggests me some ways to investigate.

Thanks
jogosCommented:
The account that runs sql*agent service
manojbabaAuthor Commented:
It was solved because there was issue with the owner of the job the guy who created the job has left the company  the job was failing because of that. May be log backup is taking at the time of full back and one time. just to truncate the log but I am not sure about this backup plan., But it was like this . I just keep it like this .


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.