[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Maintenace Plan

Posted on 2012-03-12
6
Medium Priority
?
253 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:manojbaba
  • 3
  • 2
6 Comments
 
LVL 7

Accepted Solution

by:
Moomin83 earned 1000 total points
ID: 37713436
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
0
 
LVL 25

Expert Comment

by:jogos
ID: 37713485
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.
0
 

Author Comment

by:manojbaba
ID: 37717991
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
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:manojbaba
ID: 37718350
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
0
 
LVL 25

Expert Comment

by:jogos
ID: 37718679
The account that runs sql*agent service
0
 

Author Closing Comment

by:manojbaba
ID: 37757733
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
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question