| Author |
Topic |
|
rohans
Posting Yak Master
194 Posts |
 Posted - 2003-10-27 : 08:56:20
|
| I used my maintainance wizard to create jobs for optimizing and backing up my databases and transaction logs. I noticed in the Jobs, under SQL server agent that I see my transaction logs as having a next run date saying: "Date and "Time not available".Why is this happening and how do I resolve it?All help is appriciated. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-10-27 : 11:36:14
|
| Has the job run before?The next run date for the schedule is filled in when the job runs so if it has never run it is unknown.Have you tried not using the maint application but coding the steps yourself?==========================================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. |
 |
|
|
rohans
Posting Yak Master
194 Posts |
Posted - 2003-10-27 : 11:42:23
|
| It has run before. I have not tried coding it myself as I am very familiar with SQL. I am willing to try coding it. How do I go about coding it? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-10-27 : 19:47:48
|
| Look in bol for the operations you want to perform.Usually backup database but most other things are there too.This is an SP that will backup all databases on a server and give you the opertunity to change/omit the frequency/retention period of individual databases and keep a record of what has happenned.http://www.nigelrivett.net/BackupAllDatabases.html==========================================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. |
|
|
|
rohans
Posting Yak Master
194 Posts |
Posted - 2004-02-17 : 10:50:21
|
| I am having the date and time are not available problem again. This time I coded the backup procedure, see belowbackup log dbName to disk='\\server\dbbackup\sql\....\dbName.trn' with init;what do you think is causing this?The category is DB maintenance owner 'sa'not sure what happening, do you think I should recreate and forget solving it.All help appreciated. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-17 : 12:35:46
|
| Have a look at msdb.dbo.sysjobschedules. Is the correct information located in there?Tara |
|
|
|
rohans
Posting Yak Master
194 Posts |
Posted - 2004-02-17 : 13:59:16
|
| Can I modify the information in this as the data is not set in it. May I set the correct date and run time?All help appreciated. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-17 : 14:01:51
|
| Do NOT modify data in the system tables. You change the schedule by modifying the job. Is the job enabled? Is the job schedule enabled? Is the job actually running when it is supposed to?When the job says Date and Time is not available, it is usually because the job is a brand new job and it has not run before.Tara |
|
|
|
rohans
Posting Yak Master
194 Posts |
Posted - 2004-02-17 : 14:11:00
|
| The job has ran before but since the weekend i am not sure what happen in house but it not working and when i modify the info it still doesn't run. yes it is enabled and scheduled. But not running when it should.All help appreciated. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-17 : 14:13:26
|
| So it says enabled = 1 in msdb.dbo.sysjobschedules for that schedule_id?Is the SQLSERVERAGENT started? If it isn't, start it and make sure it is set to automatic.Tara |
|
|
|
rohans
Posting Yak Master
194 Posts |
Posted - 2004-02-17 : 14:16:20
|
| enabled is 0 in the table. The agent is started.All help appreciated. |
|
|
|
rohans
Posting Yak Master
194 Posts |
Posted - 2004-02-17 : 14:17:59
|
| How can I be sure is the correct row I am looking at if the names are different and I don't see the job name in the table but the dates created are matching and it has the last part of the job name.?All help appreciated. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-17 : 14:21:52
|
| sysjobschedules needs to be JOINed to sysjobs in order to make sure you are seeing the correct one.Tara |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-17 : 14:22:38
|
quote:
Originally posted by rohans
enabled is 0 in the table. The agent is started.All help appreciated.
Your job schedule is not enabled then. Go to the job in EM, go to the schedule and check the box that says enabled. This is a different option than the job being enabled. Both the job and the jb schedule have to be enabled in order for the job to kick off.Tara |
|
|
|
rohans
Posting Yak Master
194 Posts |
Posted - 2004-02-17 : 14:35:12
|
| Tara, Great work I found it. Would not have known without your help. Big respect from yard.All help appreciated. |
|
|
|
|
Next run date for jobs in enterprise manger