Solved

Maintenace Plan

Posted on 2012-03-12
6
235 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 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:manojbaba
Comment Utility
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
Comment Utility
The account that runs sql*agent service
0
 

Author Closing Comment

by:manojbaba
Comment Utility
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

762 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now