Solved

Maintenace Plan

Posted on 2012-03-12
6
240 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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 …
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

929 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

18 Experts available now in Live!

Get 1:1 Help Now