|
|
You're in the
sql server new users
group:Scheduled backups not working.
sql server new users:
I am posting this in this group only because I don't see any other group that
is specific to the issue (i.e. Management or Backup). Anywell, I have had three seperate maintenace plans set up on our SQL 2000 Standard box since we set it up in 2001. I am completely perplexed as to why every year in mid July, the scheduled backups stop working. Every year I have to delete the existing maintenance plans and re-create them in order for them to work properly. This year, however, I am unable to get them to execute at all. I have deleted and re-created them three seperate time all to no avail. I can manually do a back up and that works fine, but the scheduled backups will not execute. Three maintenance plans are all identical except they back up at different intervals and to different locations for redundancy. Due to time constraints and a rather busy work schedule I am not able to sit down and complete a backup every time the db shoul be backed up.
Andrew,
Thanx. I am not so much worried about the July issue as I am worried about the fact that I can not get the scheduled back up to work. As far ar creating a scheduled job with a backup command.... Well I am just a measly advanced novice and am not familiar with how to do that. If yo want to give me some pointers, I would be happy to try anything at this point. I am comfortable doing anything as long as I have step by step instructions. I will try to determine how to perform the scheduled jobs, but any help in the interim would be greatly appreciated. Thanx, Paul [quoted text, click to view] "Andrew J. Kelly" wrote:
> Paul, > > That is not much to go on. There is nothing that I know of that will prevent > MP's from working each July. How are you trying to execute them and what > error messages are you getting? It sounds like you are doing a simple > backup so why not forget about the MP's and just create a scheduled job(s) > with a BACKUP command in it? > > -- > Andrew J. Kelly SQL MVP > > "Paul J Tackett" <Paul J Tackett@discussions.microsoft.com> wrote in message > news:BBC51F30-A9F4-4327-9BC2-6E3A4999074F@microsoft.com... > >I am posting this in this group only because I don't see any other group > >that > > is specific to the issue (i.e. Management or Backup). > > > > Anywell, I have had three seperate maintenace plans set up on our SQL 2000 > > Standard box since we set it up in 2001. I am completely perplexed as to > > why > > every year in mid July, the scheduled backups stop working. Every year I > > have to delete the existing maintenance plans and re-create them in order > > for > > them to work properly. > > > > This year, however, I am unable to get them to execute at all. I have > > deleted and re-created them three seperate time all to no avail. I can > > manually do a back up and that works fine, but the scheduled backups will > > not > > execute. > > > > Three maintenance plans are all identical except they back up at different > > intervals and to different locations for redundancy. > > > > Due to time constraints and a rather busy work schedule I am not able to > > sit > > down and complete a backup every time the db shoul be backed up. > > > > Any clues???? > >
Brett,
Tanx for the timely response. I did not include an expiration date as I wanted these MPs to be an ongoing, indefinite backup. Of course there is nothing in the MP history at this point because I deleted the old and created new (3x already) but the previous histor showed jobs from the previous 3 days, as I have it delete files over 4 days old. The files for the day it stopped had the red "X" indicating it was unable to complete the job and basically said as much in the notes without any helpful information. Paul [quoted text, click to view] "Brett I. Holcomb" wrote:
> Did you put an expiration date? > > Also check the maintenance plan history and see what it says. > > Paul J Tackett wrote: > > > I am posting this in this group only because I don't see any other group > > that is specific to the issue (i.e. Management or Backup). > > > > Anywell, I have had three seperate maintenace plans set up on our SQL 2000 > > Standard box since we set it up in 2001. I am completely perplexed as to > > why > > every year in mid July, the scheduled backups stop working. Every year I > > have to delete the existing maintenance plans and re-create them in order > > for them to work properly. > > > > This year, however, I am unable to get them to execute at all. I have > > deleted and re-created them three seperate time all to no avail. I can > > manually do a back up and that works fine, but the scheduled backups will > > not execute. > > > > Three maintenance plans are all identical except they back up at different > > intervals and to different locations for redundancy. > > > > Due to time constraints and a rather busy work schedule I am not able to > > sit down and complete a backup every time the db shoul be backed up. > > > > Any clues???? >
Paul,
That is not much to go on. There is nothing that I know of that will prevent MP's from working each July. How are you trying to execute them and what error messages are you getting? It sounds like you are doing a simple backup so why not forget about the MP's and just create a scheduled job(s) with a BACKUP command in it? -- Andrew J. Kelly SQL MVP [quoted text, click to view] "Paul J Tackett" <Paul J Tackett@discussions.microsoft.com> wrote in message news:BBC51F30-A9F4-4327-9BC2-6E3A4999074F@microsoft.com... >I am posting this in this group only because I don't see any other group >that > is specific to the issue (i.e. Management or Backup). > > Anywell, I have had three seperate maintenace plans set up on our SQL 2000 > Standard box since we set it up in 2001. I am completely perplexed as to > why > every year in mid July, the scheduled backups stop working. Every year I > have to delete the existing maintenance plans and re-create them in order > for > them to work properly. > > This year, however, I am unable to get them to execute at all. I have > deleted and re-created them three seperate time all to no avail. I can > manually do a back up and that works fine, but the scheduled backups will > not > execute. > > Three maintenance plans are all identical except they back up at different > intervals and to different locations for redundancy. > > Due to time constraints and a rather busy work schedule I am not able to > sit > down and complete a backup every time the db shoul be backed up. > > Any clues????
Did you put an expiration date?
Also check the maintenance plan history and see what it says. [quoted text, click to view] Paul J Tackett wrote:
> I am posting this in this group only because I don't see any other group > that is specific to the issue (i.e. Management or Backup). > > Anywell, I have had three seperate maintenace plans set up on our SQL 2000 > Standard box since we set it up in 2001. I am completely perplexed as to > why > every year in mid July, the scheduled backups stop working. Every year I > have to delete the existing maintenance plans and re-create them in order > for them to work properly. > > This year, however, I am unable to get them to execute at all. I have > deleted and re-created them three seperate time all to no avail. I can > manually do a back up and that works fine, but the scheduled backups will > not execute. > > Three maintenance plans are all identical except they back up at different > intervals and to different locations for redundancy. > > Due to time constraints and a rather busy work schedule I am not able to > sit down and complete a backup every time the db shoul be backed up. > > Any clues????
Okay,
Under the Job History it shows that the job has failed. It also gives me the following message "The job failed. Unable to determine if the owner (DRP\Administrator) of job DB Backup Job for DB Maintenance Plan 'DB Maintenance Plan2' has server access (reason: Could not obtain information about Windows NT group/user 'DRP\Administrator'. [SQLSTATE 42000] (Error 8198))." Administrator is set up in the active directory with all permissions. The DNS Server is a seperate box, and I have no other permission problems for Administrator (or any other users for that matter.) As I read the error log in MSSQL\Log, I find entries such as this for the erros when the back up should have been preparing to execute: 2006-09-07 21:29:33.18 server Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4) 2006-09-07 21:29:33.20 server Copyright (C) 1988-2002 Microsoft Corporation. 2006-09-07 21:29:33.20 server All rights reserved. 2006-09-07 21:29:33.20 server Server Process ID is 1432. 2006-09-07 21:29:33.20 server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG'. 2006-09-07 21:29:33.49 server SQL Server is starting at priority class 'normal'(2 CPUs detected). 2006-09-07 21:29:35.15 server SQL Server configured for thread mode processing. 2006-09-07 21:29:35.17 server Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks. 2006-09-07 21:29:35.70 server Attempting to initialize Distributed Transaction Coordinator. 2006-09-07 21:29:37.82 server Failed to obtain TransactionDispenserInterface: Result Code = 0x8004d01b 2006-09-07 21:29:38.53 spid3 Starting up database 'master'. 2006-09-07 21:29:42.79 server Using 'SSNETLIB.DLL' version '8.0.766'. 2006-09-07 21:29:42.79 spid5 Starting up database 'model'. 2006-09-07 21:29:42.95 spid3 Server name is 'SERVER'. 2006-09-07 21:29:43.07 spid8 Starting up database 'msdb'. 2006-09-07 21:29:43.07 spid9 Starting up database 'pubs'. 2006-09-07 21:29:43.18 spid10 Starting up database 'Northwind'. 2006-09-07 21:29:43.21 spid11 Starting up database 'Mdplus_DRP'. 2006-09-07 21:29:44.10 server SQL server listening on 192.168.50.11: 1433. 2006-09-07 21:29:44.10 server SQL server listening on 127.0.0.1: 1433. 2006-09-07 21:29:45.15 spid5 Clearing tempdb database. 2006-09-07 21:29:47.98 spid5 Starting up database 'tempdb'. 2006-09-07 21:29:48.75 spid3 Recovery complete. 2006-09-07 21:29:48.75 spid3 SQL global counter collection task is created. 2006-09-07 21:29:49.73 server SQL server listening on TCP, Shared Memory, Named Pipes. 2006-09-07 21:29:49.73 server SQL Server is ready for client connections 2006-09-07 21:29:53.95 spid53 Using 'xpsqlbot.dll' version '2000.80.194' to execute extended stored procedure 'xp_qv'. 2006-09-07 21:52:56.64 server SQL Server terminating because of system shutdown. In the log Under SQLAgent I find the following: 2006-09-07 21:29:33.18 server Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4) 2006-09-07 21:29:33.20 server Copyright (C) 1988-2002 Microsoft Corporation. 2006-09-07 21:29:33.20 server All rights reserved. 2006-09-07 21:29:33.20 server Server Process ID is 1432. 2006-09-07 21:29:33.20 server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG'. 2006-09-07 21:29:33.49 server SQL Server is starting at priority class 'normal'(2 CPUs detected). 2006-09-07 21:29:35.15 server SQL Server configured for thread mode processing. 2006-09-07 21:29:35.17 server Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks. 2006-09-07 21:29:35.70 server Attempting to initialize Distributed Transaction Coordinator. 2006-09-07 21:29:37.82 server Failed to obtain TransactionDispenserInterface: Result Code = 0x8004d01b 2006-09-07 21:29:38.53 spid3 Starting up database 'master'. 2006-09-07 21:29:42.79 server Using 'SSNETLIB.DLL' version '8.0.766'. 2006-09-07 21:29:42.79 spid5 Starting up database 'model'. 2006-09-07 21:29:42.95 spid3 Server name is 'SERVER'. 2006-09-07 21:29:43.07 spid8 Starting up database 'msdb'. 2006-09-07 21:29:43.07 spid9 Starting up database 'pubs'. 2006-09-07 21:29:43.18 spid10 Starting up database 'Northwind'. 2006-09-07 21:29:43.21 spid11 Starting up database 'Mdplus_DRP'. 2006-09-07 21:29:44.10 server SQL server listening on 192.168.50.11: 1433. 2006-09-07 21:29:44.10 server SQL server listening on 127.0.0.1: 1433. 2006-09-07 21:29:45.15 spid5 Clearing tempdb database. 2006-09-07 21:29:47.98 spid5 Starting up database 'tempdb'. 2006-09-07 21:29:48.75 spid3 Recovery complete. 2006-09-07 21:29:48.75 spid3 SQL global counter collection task is created. 2006-09-07 21:29:49.73 server SQL server listening on TCP, Shared Memory, Named Pipes. 2006-09-07 21:29:49.73 server SQL Server is ready for client connections 2006-09-07 21:29:53.95 spid53 Using 'xpsqlbot.dll' version '2000.80.194' to execute extended stored procedure 'xp_qv'. 2006-09-07 21:52:56.64 server SQL Server terminating because of system shutdown. As I stated in another post, I am an advanced novice and am only maintaing this system because my employer can not afford to outsource and I know just enough to be dangerous. Therefore what I just pasted into this message means nothing to me, but maybe you can read it and discern why it is not function ing correctly. Thanx, Paul [quoted text, click to view] "Brett I. Holcomb" wrote:
> Yes, the history you get clicking on the job is useless - they should have > given access to the log for that job. In the Enterprise Manager go to > Manager->maintenance plans and right click. Select history. In that dialog > box you can filter to see just one plan and failed status. There is > usually much more info there. Also look under the server logs in > Management. > > Some things I found. > > If you try and do a TLog backup on a simple recovery model it won't - there > isn't any Tlog. > > You may have to go to jobs and delete the existing jobs from the schedule. > > At this point I'd recommmend deleting the jobs that were related to the old > plans, delete the plans you just made and recreate just one of them again. > After you create the plan then go to jobs and run it. See what happens and > look at the logs. > > Paul J Tackett wrote: > > > Brett, > > > > Tanx for the timely response. I did not include an expiration date as I
Yes, the history you get clicking on the job is useless - they should have
given access to the log for that job. In the Enterprise Manager go to Manager->maintenance plans and right click. Select history. In that dialog box you can filter to see just one plan and failed status. There is usually much more info there. Also look under the server logs in Management. Some things I found. If you try and do a TLog backup on a simple recovery model it won't - there isn't any Tlog. You may have to go to jobs and delete the existing jobs from the schedule. At this point I'd recommmend deleting the jobs that were related to the old plans, delete the plans you just made and recreate just one of them again. After you create the plan then go to jobs and run it. See what happens and look at the logs. [quoted text, click to view] Paul J Tackett wrote:
> Brett, > > Tanx for the timely response. I did not include an expiration date as I > wanted these MPs to be an ongoing, indefinite backup. Of course there is > nothing in the MP history at this point because I deleted the old and > created new (3x already) but the previous histor showed jobs from the > previous 3 > days, as I have it delete files over 4 days old. The files for the day it > stopped had the red "X" indicating it was unable to complete the job and > basically said as much in the notes without any helpful information. > > Paul > > "Brett I. Holcomb" wrote: > >> Did you put an expiration date? >> >> Also check the maintenance plan history and see what it says. >> >> Paul J Tackett wrote: >> >> > I am posting this in this group only because I don't see any other >> > group that is specific to the issue (i.e. Management or Backup). >> > >> > Anywell, I have had three seperate maintenace plans set up on our SQL >> > 2000 >> > Standard box since we set it up in 2001. I am completely perplexed as >> > to why >> > every year in mid July, the scheduled backups stop working. Every year >> > I have to delete the existing maintenance plans and re-create them in >> > order for them to work properly. >> > >> > This year, however, I am unable to get them to execute at all. I have >> > deleted and re-created them three seperate time all to no avail. I can >> > manually do a back up and that works fine, but the scheduled backups >> > will not execute. >> > >> > Three maintenance plans are all identical except they back up at >> > different intervals and to different locations for redundancy. >> > >> > Due to time constraints and a rather busy work schedule I am not able >> > to sit down and complete a backup every time the db shoul be backed up. >> > >> > Any clues???? >> >>
Is there a DRP\Administrator setup as a user under security->logins?
[quoted text, click to view] Paul J Tackett wrote:
> Okay, > > Under the Job History it shows that the job has failed. It also gives me > the following message "The job failed. Unable to determine if the owner > (DRP\Administrator) of job DB Backup Job for DB Maintenance Plan 'DB > Maintenance Plan2' has server access (reason: Could not obtain information > about Windows NT group/user 'DRP\Administrator'. [SQLSTATE 42000] (Error > 8198))." > > Administrator is set up in the active directory with all permissions. The > DNS Server is a seperate box, and I have no other permission problems for > Administrator (or any other users for that matter.) > > As I read the error log in MSSQL\Log, I find entries such as this for the
:-(( Set up the DRP\Administrator in Security => Logins, rebooted, waited...
Still not sucessful??? [quoted text, click to view] "Brett I. Holcomb" wrote:
> Is there a DRP\Administrator setup as a user under security->logins? > > Paul J Tackett wrote: > > > Okay, > > > > Under the Job History it shows that the job has failed. It also gives me > > the following message "The job failed. Unable to determine if the owner > > (DRP\Administrator) of job DB Backup Job for DB Maintenance Plan 'DB > > Maintenance Plan2' has server access (reason: Could not obtain information > > about Windows NT group/user 'DRP\Administrator'. [SQLSTATE 42000] (Error > > 8198))." > > > > Administrator is set up in the active directory with all permissions. The > > DNS Server is a seperate box, and I have no other permission problems for > > Administrator (or any other users for that matter.) > > > > As I read the error log in MSSQL\Log, I find entries such as this for the >
Anybody else have any ideas?????
[quoted text, click to view] "Paul J Tackett" wrote:
> :-(( Set up the DRP\Administrator in Security => Logins, rebooted, waited... > Still not sucessful??? > > "Brett I. Holcomb" wrote: > > > Is there a DRP\Administrator setup as a user under security->logins? > > > > Paul J Tackett wrote: > > > > > Okay, > > > > > > Under the Job History it shows that the job has failed. It also gives me > > > the following message "The job failed. Unable to determine if the owner > > > (DRP\Administrator) of job DB Backup Job for DB Maintenance Plan 'DB > > > Maintenance Plan2' has server access (reason: Could not obtain information > > > about Windows NT group/user 'DRP\Administrator'. [SQLSTATE 42000] (Error > > > 8198))." > > > > > > Administrator is set up in the active directory with all permissions. The > > > DNS Server is a seperate box, and I have no other permission problems for > > > Administrator (or any other users for that matter.) > > > > > > As I read the error log in MSSQL\Log, I find entries such as this for the > >
And I thank you for all of your efforts. I really thought the last one might
be the key. Thanx, Paul [quoted text, click to view] "Brett I. Holcomb" wrote:
> I'm out of ideas. > > Paul J Tackett wrote: > > > Anybody else have any ideas????? > > > > "Paul J Tackett" wrote: > > > >> :-(( Set up the DRP\Administrator in Security => Logins, rebooted, > >> :waited... > >> Still not sucessful??? > >> > >> "Brett I. Holcomb" wrote: > >> > >> > Is there a DRP\Administrator setup as a user under security->logins? > >> > > >> > Paul J Tackett wrote: > >> > > >> > > Okay, > >> > > > >> > > Under the Job History it shows that the job has failed. It also > >> > > gives me > >> > > the following message "The job failed. Unable to determine if the > >> > > owner (DRP\Administrator) of job DB Backup Job for DB Maintenance > >> > > Plan 'DB Maintenance Plan2' has server access (reason: Could not > >> > > obtain information about Windows NT group/user 'DRP\Administrator'. > >> > > [SQLSTATE 42000] (Error 8198))." > >> > > > >> > > Administrator is set up in the active directory with all permissions. > >> > > The DNS Server is a seperate box, and I have no other permission > >> > > problems for Administrator (or any other users for that matter.) > >> > > > >> > > As I read the error log in MSSQL\Log, I find entries such as this for > >> > > the > >> > > >> > >
I'm out of ideas.
[quoted text, click to view] Paul J Tackett wrote:
> Anybody else have any ideas????? > > "Paul J Tackett" wrote: > >> :-(( Set up the DRP\Administrator in Security => Logins, rebooted, >> :waited... >> Still not sucessful??? >> >> "Brett I. Holcomb" wrote: >> >> > Is there a DRP\Administrator setup as a user under security->logins? >> > >> > Paul J Tackett wrote: >> > >> > > Okay, >> > > >> > > Under the Job History it shows that the job has failed. It also >> > > gives me >> > > the following message "The job failed. Unable to determine if the >> > > owner (DRP\Administrator) of job DB Backup Job for DB Maintenance >> > > Plan 'DB Maintenance Plan2' has server access (reason: Could not >> > > obtain information about Windows NT group/user 'DRP\Administrator'. >> > > [SQLSTATE 42000] (Error 8198))." >> > > >> > > Administrator is set up in the active directory with all permissions. >> > > The DNS Server is a seperate box, and I have no other permission >> > > problems for Administrator (or any other users for that matter.) >> > > >> > > As I read the error log in MSSQL\Log, I find entries such as this for >> > > the >> > >> >
Don't see what you're looking for? Try a search.
|
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |