Solved

Maintenace Plan

Posted on 2012-03-12
6
245 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
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Find unused columns in a table 12 67
removing unwanted rows from an sql server ranked table 13 34
SQL Server Sum Over Multiple Tables 20 30
How come this XML node is not read? 3 25
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

685 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