URGENT Please regarding Scheduled Backups

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

URGENT Please regarding Scheduled Backups

Author

Topic

rikleo2001
Posting Yak Master

185 Posts

Posted - 2008-04-18 : 04:08:21

Guys,

I have set scehduled Backup process, which suppose to run every night at 210000, and a seperate job for Tlog every 15 minutes.

Schduled job won't run at all..Job ius enabled, scheduled is enabled. SQL Server service is running under local admin account.. Then Why not job runs?
No Event Log, No Job History and no Job output? They why not Run?

Surprisingly TLog is running fine..

Also if I execute job manually it runs fine..

Any help or clues?

Many Thanks

SKR

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-18 : 07:35:18

If you can run the job manually then that should appear in the history.
Change the step to select 1 (comment out the backup command) and scheule for every minute to see what happens.
Try scripting the job and look at the script.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rikleo2001
Posting Yak Master

185 Posts

Posted - 2008-04-21 : 04:34:52

Hi, Thanks for your reply.. I did it and it is recording history and excuting those steps every minute.
As soon I changed it back to 2100 EVERY day, it did the backup on 18/04 friday night, and then no full backup on saturday and Sunday, All other processes running fine (Tlog, Defrag...etc) Not the backup ones.
Looks like this job is not even kick starting? Why?

Any more ideas to resolve this issue?

Thanks

SKR

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-21 : 12:00:05

Can you script the job and post it here.

It sounds like either something is being disabled after the first run or the scheulde is being changed.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rikleo2001
Posting Yak Master

185 Posts

Posted - 2008-04-22 : 09:14:22

There is nothing special in it,
Is there any thing to do with Tlog backup are happening at the same time when Full backup is in place? I believe they are meant to run concurrently?

SKR

normant12
Starting Member

1 Post

Posted - 2008-04-22 : 09:42:38

quote:
Originally posted by rikleo2001

There is nothing special in it,
Is there any thing to do with Tlog backup are happening at the same time when Full backup is in place? I believe they are meant to run concurrently?

SKR

Check your version of Service Packs for SQL, there some issues that one of the Service packs fixed.

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-22 : 12:51:21

quote:
Originally posted by rikleo2001

There is nothing special in it,
Is there any thing to do with Tlog backup are happening at the same time when Full backup is in place? I believe they are meant to run concurrently?

SKR

Easy to check - just run the at a differnt time. But you say it's not even tryijng to run so it's not an issue with the backup but with the job.
Try scheduling the backups to run every 5 mins and see if that works.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rikleo2001
Posting Yak Master

185 Posts

Posted - 2008-04-23 : 03:01:28

I have SP2,now it is proven that it is when you try to run Tlog and full backups at the same time.
I tried to run it evey 5 minutes and it works fine. So it all comes to how Tlog process is handlening the Database switches if they occurs concurrently.

Solution what I did, created two differant schedules avoiding conflicts, and bingo.. all works last night.

Any body came across this issue? now is this a bug? or still I am missing something?

Thanks

SKR

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-23 : 07:47:32

It's odd - you say the job doesn't run at all so it can't be the backup statement within the job that's the issue.
Can't test what's happening without you posting the scripts so it's difficuolt to comment further.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-04-23 : 23:11:36

>> it is when you try to run Tlog and full backups at the same time.

You can't run two backups on a db at same time.

Ola Hallengren
Starting Member

33 Posts

Posted - 2008-04-24 : 17:29:59

>You can't run two backups on a db at same time.

I think that in SQL Server 2005 you can run a log backup and a database backup at the same time. I think that this is a change from how it works in SQL Server 2000.

"Backup Data and Logs: The former problems with log backups at the same time as data backups have been remedied. It is now possible to carry out a data backup at the same time as the corresponding log backup."

http://media.wiley.com/product_data/excerpt/57/07645775/0764577557.pdf

Ola Hallengren
http://ola.hallengren.com

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-04-24 : 21:49:32

Not true. You can start job at same time, but one of them will wait.

Ola Hallengren
Starting Member

33 Posts

Posted - 2008-05-01 : 19:31:29

I have done some tests on this. It seems like log backups can complete while the database backup is running. They do however take a longer time to run. I'm using SQL Server 2005 SP2.

Connection 1, Inserts into a table in a loop

Connection 2, Log Backups in a loop with a 1 second delay between each backup

2008-04-25 11:11:28
Processed 441 pages for database 'TestDb', file 'TestDb_log' on file 1.
BACKUP LOG successfully processed 441 pages in 0.619 seconds (5.827 MB/sec).
2008-04-25 11:11:28

2008-04-25 11:11:29
Processed 296 pages for database 'TestDb', file 'TestDb_log' on file 1.
BACKUP LOG successfully processed 296 pages in 52.446 seconds (0.046 MB/sec).
2008-04-25 11:12:22

2008-04-25 11:12:23
Processed 46 pages for database 'TestDb', file 'TestDb_log' on file 1.
BACKUP LOG successfully processed 46 pages in 50.094 seconds (0.007 MB/sec).
2008-04-25 11:13:14

2008-04-25 11:13:15
Processed 48 pages for database 'TestDb', file 'TestDb_log' on file 1.
BACKUP LOG successfully processed 48 pages in 43.417 seconds (0.009 MB/sec).
2008-04-25 11:13:59

2008-04-25 11:14:00
Processed 50 pages for database 'TestDb', file 'TestDb_log' on file 1.
BACKUP LOG successfully processed 50 pages in 31.013 seconds (0.012 MB/sec).
2008-04-25 11:14:32

2008-04-25 11:14:33
Processed 369 pages for database 'TestDb', file 'TestDb_log' on file 1.
BACKUP LOG successfully processed 369 pages in 0.433 seconds (6.970 MB/sec).
2008-04-25 11:14:33

Connection 3, A full backup

2008-04-25 11:11:29
Processed 316904 pages for database 'TestDb', file 'TestDb' on file 1.
Processed 186 pages for database 'TestDb', file 'TestDb_log' on file 1.
BACKUP DATABASE successfully processed 317090 pages in 182.725 seconds
(14.215 MB/sec).
2008-04-25 11:14:32

Ola Hallengren
http://ola.hallengren.com

Ola Hallengren
Starting Member

33 Posts

Posted - 2008-05-13 : 14:28:32

Here's the documentation on this. That confirms that full backups and log backups can run concurrently.
http://msdn.microsoft.com/en-us/library/ms189315.aspx

Ola Hallengren
http://ola.hallengren.com

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-05-14 : 22:57:32

Sounds much better than sql2k.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources