Hi -
I want to Schedule Daily Backup of MSSQL Databases .. How can i do it .. How can i schedule it so that it get backuped everyday automatically at desired time.
Kind RegardsYou need to read up on Maintenance Plans on BOL. How yoyur databases are setup and each applications recovery requirements in order to setup the backups properly. Also read up on Recovery Plans.|||You can use the SQL Server Agent Utility (available through Enterprise Manager) to schedule jobs to run at regular times. The Maintenance Plan wizard merely creates the jobs for you (and also provides a convenient method of grouping databases upon which to perform identical maintenance).
blindman
Showing posts with label databases. Show all posts
Showing posts with label databases. Show all posts
Wednesday, March 28, 2012
Monday, March 26, 2012
Scheduling Complete and Differential backups
I'm new to the SQL 7 scene but would like a few things clearing up with regards to scheduling backups.
Our backup time of databases has recently changed. I've scheduled in the new database backup using Enterprise Manager with the new time.
I expected the backup to take place at the new time, which it does, but when I came to restoring the database I've noticed that the old backup time is still being carried out. How do I cancel or get rid of the old backup time?
Secondly, If I do a complete backup of the database using Enterprise Manager, can I deattach the transactional log and delete the transactional log before reattaching in order to free up MG?
Thirdly, is carrying out a complete backup of the database via Enterprise Manager enough to ensure a full backup of the database should anything go wrong?
I would like help on these matters please as I need these clearing up. thanking you in advance!!!It sounds like you might have two scheduled jobs running one for the old time and one for the new time if so find the old job and delete it.
If you need to restore your database to a point in time you need the transaction log. A full backup backs up the database and the T-log together so you will only be able to restore to the last time you did the full backup.
If the transaction log is getting to large you can truncate the log which is sort of the same thing as deleting but doesn't actually get rid of the .ldf file. If you truncate the log without first backing it up you lose all those transactions from the last time the log was backed up!
If you need to restore to a point in time you need the T-log. Say you do a full backup every night and a T-log backup every hour. We will say that SQL fails on Tuesday at 1:45pm. To restore you would backup the current T-log because the next scheduled T-log backup will not occur until 2pm. and it contains the active portion of the log and you need to recover it by backing it up. You then would apply the full backup and all T-log backups in sequence up to the point of failure.
If you have SQL installed then you should have Books Online installed. You should find it in the menu. They have exhaustive information that talks about backup strategies. I suggest you take some time to read up on it it's well worth the time.
Best Regards|||[QUOTE][SIZE=1]Originally posted by mkal
It sounds like you might have two scheduled jobs running one for the old time and one for the new time if so find the old job and delete it.
Thats the thing, I'm pretty sure I deleted the old Database_bk file and created a new one in the 'MSSQL\Backup' folder but still the old backup time is there when doing a restore. I'll have another go.
As for the tranasactional backup, we currently carry out a Complete backup of a weekend and a differential backup every night which is scheduled using the Enterprise Manager but there is no option to backup the Transaction log. How is this done?
You'll have to bear with me on this one as I'm still a newbie and require a lot of patience.
Thanks v.much for the reply|||I'm still getting two lots of complete backups to restore as well as two lots of differential backups to restore having changed the times of when the backups are scheduled using Enterprise Manager.
Is there anyway I can get it so that there is only one backup scheduled?|||Deleting the backup file is not the same as deleting the job. In Enterprise Manager expand out the server you create the backups for. Click on the Management folder and then SQL Server Agent and then click on jobs. In the right hand pane you should see a list of jobs. There should be the one for your backup and possibly others. To explore what the job is doing right click on it and choose properties. You then can take a look at what's going on.
To free up space on the drive where the T-log is you can run a script see below, just replace the info between the <> (but don't include the <>) with the name of the database, name of the log file and the size expressed as an integer you want the file to become.
The following is from BOL:
If target_size is specified, DBCC SHRINKFILE attempts to shrink the file to the specified size. Used pages in the part of the file to be freed are relocated to available free space in the part of the file retained. For example, if there is a 10-MB data file, a DBCC SHRINKFILE with a target_size of 8 causes all used pages in the last 2 MB of the file to be reallocated into any available free slots in the first 8 MB of the file. DBCC SHRINKFILE does not shrink a file past the size needed to store the data in the file. For example, if 7 MB of a 10-MB data file is used, a DBCC SHRINKFILE statement with a target_size of 6 shrinks the file to only 7 MB, not 6 MB.
Backup log <DatabaseName> with truncate_only
GO
DBCC SHRINKFILE (<LogFile_Name, <targetsize>)
GO
Creating a full backup backs up the data and the T-log, so you can recover everything up to the day and time you performed the backup.
It doesn't back up the database structure i.e. tables, constraints, PK/FK. You should script your database to make sure that in the event of a major crash you can recreate the database and reload it from the backups.
To script your database use Enterprise Manager. Right click on the database you wish to script, choose 'All Tasks' and then Generate SQL Script.
Best Regards
Mike|||Cheers Mike,
When I went into the Management\SQLServerAgent\Jobs folder and sure enough I seen a whole load of jobs that had been duplicated. I have gotten rid of the ones that are now redundant and have left the jobs that run at a later time (the ones I need) in there. I'm quite sure that this will work but I will need to check in the morning after the databases have been backed up.
A big thank you mate for your help and persevering with me.|||One more thing about log backups. For this to take place you must have the recovery model of the database set to full and there must be at least one full database backup must exist.
To check the recoverty model right click on the database and choose properties. Go the options tab and where it says 'Recovery model' choose full.
Now when you right click on the database and choose 'All Tasks' and the 'Database Backup' you will see a radio button to perform a log backup.
Glad I could help.
Our backup time of databases has recently changed. I've scheduled in the new database backup using Enterprise Manager with the new time.
I expected the backup to take place at the new time, which it does, but when I came to restoring the database I've noticed that the old backup time is still being carried out. How do I cancel or get rid of the old backup time?
Secondly, If I do a complete backup of the database using Enterprise Manager, can I deattach the transactional log and delete the transactional log before reattaching in order to free up MG?
Thirdly, is carrying out a complete backup of the database via Enterprise Manager enough to ensure a full backup of the database should anything go wrong?
I would like help on these matters please as I need these clearing up. thanking you in advance!!!It sounds like you might have two scheduled jobs running one for the old time and one for the new time if so find the old job and delete it.
If you need to restore your database to a point in time you need the transaction log. A full backup backs up the database and the T-log together so you will only be able to restore to the last time you did the full backup.
If the transaction log is getting to large you can truncate the log which is sort of the same thing as deleting but doesn't actually get rid of the .ldf file. If you truncate the log without first backing it up you lose all those transactions from the last time the log was backed up!
If you need to restore to a point in time you need the T-log. Say you do a full backup every night and a T-log backup every hour. We will say that SQL fails on Tuesday at 1:45pm. To restore you would backup the current T-log because the next scheduled T-log backup will not occur until 2pm. and it contains the active portion of the log and you need to recover it by backing it up. You then would apply the full backup and all T-log backups in sequence up to the point of failure.
If you have SQL installed then you should have Books Online installed. You should find it in the menu. They have exhaustive information that talks about backup strategies. I suggest you take some time to read up on it it's well worth the time.
Best Regards|||[QUOTE][SIZE=1]Originally posted by mkal
It sounds like you might have two scheduled jobs running one for the old time and one for the new time if so find the old job and delete it.
Thats the thing, I'm pretty sure I deleted the old Database_bk file and created a new one in the 'MSSQL\Backup' folder but still the old backup time is there when doing a restore. I'll have another go.
As for the tranasactional backup, we currently carry out a Complete backup of a weekend and a differential backup every night which is scheduled using the Enterprise Manager but there is no option to backup the Transaction log. How is this done?
You'll have to bear with me on this one as I'm still a newbie and require a lot of patience.
Thanks v.much for the reply|||I'm still getting two lots of complete backups to restore as well as two lots of differential backups to restore having changed the times of when the backups are scheduled using Enterprise Manager.
Is there anyway I can get it so that there is only one backup scheduled?|||Deleting the backup file is not the same as deleting the job. In Enterprise Manager expand out the server you create the backups for. Click on the Management folder and then SQL Server Agent and then click on jobs. In the right hand pane you should see a list of jobs. There should be the one for your backup and possibly others. To explore what the job is doing right click on it and choose properties. You then can take a look at what's going on.
To free up space on the drive where the T-log is you can run a script see below, just replace the info between the <> (but don't include the <>) with the name of the database, name of the log file and the size expressed as an integer you want the file to become.
The following is from BOL:
If target_size is specified, DBCC SHRINKFILE attempts to shrink the file to the specified size. Used pages in the part of the file to be freed are relocated to available free space in the part of the file retained. For example, if there is a 10-MB data file, a DBCC SHRINKFILE with a target_size of 8 causes all used pages in the last 2 MB of the file to be reallocated into any available free slots in the first 8 MB of the file. DBCC SHRINKFILE does not shrink a file past the size needed to store the data in the file. For example, if 7 MB of a 10-MB data file is used, a DBCC SHRINKFILE statement with a target_size of 6 shrinks the file to only 7 MB, not 6 MB.
Backup log <DatabaseName> with truncate_only
GO
DBCC SHRINKFILE (<LogFile_Name, <targetsize>)
GO
Creating a full backup backs up the data and the T-log, so you can recover everything up to the day and time you performed the backup.
It doesn't back up the database structure i.e. tables, constraints, PK/FK. You should script your database to make sure that in the event of a major crash you can recreate the database and reload it from the backups.
To script your database use Enterprise Manager. Right click on the database you wish to script, choose 'All Tasks' and then Generate SQL Script.
Best Regards
Mike|||Cheers Mike,
When I went into the Management\SQLServerAgent\Jobs folder and sure enough I seen a whole load of jobs that had been duplicated. I have gotten rid of the ones that are now redundant and have left the jobs that run at a later time (the ones I need) in there. I'm quite sure that this will work but I will need to check in the morning after the databases have been backed up.
A big thank you mate for your help and persevering with me.|||One more thing about log backups. For this to take place you must have the recovery model of the database set to full and there must be at least one full database backup must exist.
To check the recoverty model right click on the database and choose properties. Go the options tab and where it says 'Recovery model' choose full.
Now when you right click on the database and choose 'All Tasks' and the 'Database Backup' you will see a radio button to perform a log backup.
Glad I could help.
Friday, March 23, 2012
Scheduling a DTS package
I am using Enterprise manager with an MSDE engine. I have created a DTS package that updates a table in one of the databases. If I right click and choose "Execute Package" it runs...No sweat. When I try to use the scheduler to run the job every hour, it always fails between 1 and 10 seconds into the job...It only returns the error "Failed During Step 1."
I'm wondering if this feature won't work with MSDE? Does anyone have any ideas?Under Management>Jobs, right click on the job in question, and select "Job History". There should be a checkbox that says "Show step details". Clicking that will expand the information. Check to see if there's more information in there.|||Probably a security issue. Wen you run the package interactively it uses the logged in user, when running from the job queue it uses the configured user account of the DTS Package.
Open your package, click Package->Properties->Logging|||Thanks for tips, the error is:
Step Error Source: Microsoft Data Transformation Services Flat File Rowset Provider
Step Error Description:Error opening datafile: The system cannot find the path specified.
Step Error code: 80004005
Step Error Help File:DTSFFile.hlp
Step Error Help Context ID:0
I checked the path and it's correct, and it finds the file when I run it manually.|||Check the server to see if the file exists in the exact same filepath as on your machine. I believe that when you run a DTS package interactively, it resolves file names locally, but when executed from the server via SQLAgent it will resolve the filepaths from the server.|||i concurr
i suck at typing in filepaths so i always set the windows explorer to display the full path in the title bar and i just copy it from there.
I'm wondering if this feature won't work with MSDE? Does anyone have any ideas?Under Management>Jobs, right click on the job in question, and select "Job History". There should be a checkbox that says "Show step details". Clicking that will expand the information. Check to see if there's more information in there.|||Probably a security issue. Wen you run the package interactively it uses the logged in user, when running from the job queue it uses the configured user account of the DTS Package.
Open your package, click Package->Properties->Logging|||Thanks for tips, the error is:
Step Error Source: Microsoft Data Transformation Services Flat File Rowset Provider
Step Error Description:Error opening datafile: The system cannot find the path specified.
Step Error code: 80004005
Step Error Help File:DTSFFile.hlp
Step Error Help Context ID:0
I checked the path and it's correct, and it finds the file when I run it manually.|||Check the server to see if the file exists in the exact same filepath as on your machine. I believe that when you run a DTS package interactively, it resolves file names locally, but when executed from the server via SQLAgent it will resolve the filepaths from the server.|||i concurr
i suck at typing in filepaths so i always set the windows explorer to display the full path in the title bar and i just copy it from there.
Scheduling a backup
Hi,
I want to schedule a backup of three databases on a daily basis.
I've written the code to run the 3 backups in TSQL and was wondering how
best to automate this procedure?
Should I put the code in an sproc and then schedule running that command
in the DTS or should I just add the TSQL into a DTS command?? Or is
there a better way of doing this?
I then prefer to use DTSRUNUI to generate the syntax and then schedule
this from the windows scheduler as opposed to the SQL SCheduler in EM
(is this the best way?)
Help would be appreciated
M3ckon
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!"m3ckon" <anonymous@.devdex.com> wrote in message
news:40cdbb60$0$25520$c397aba@.news.newsgroups.ws.. .
> Hi,
> I want to schedule a backup of three databases on a daily basis.
> I've written the code to run the 3 backups in TSQL and was wondering how
> best to automate this procedure?
> Should I put the code in an sproc and then schedule running that command
> in the DTS or should I just add the TSQL into a DTS command?? Or is
> there a better way of doing this?
> I then prefer to use DTSRUNUI to generate the syntax and then schedule
> this from the windows scheduler as opposed to the SQL SCheduler in EM
> (is this the best way?)
> Help would be appreciated
> M3ckon
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!
Putting the code in a procedure may be best, because it's simpler to manage,
and you don't get awkward problems working with EM or other tools which
allow only a very limited area for viewing code. If you're executing a DTS
package, then your geenral approach above seems OK, although the Windows
scheduler is rather limited compared to the MSSQL one, so unless there is a
very good reason not to, I would use the MSSQL scheduler.
Having said all that, using DTS as a backup mechanism is a little unusual,
unless your backups are part of a larger workflow. For simple backups,
maintenance plans are a convenient solution, although since they have some
limitations, you might have to write your own TSQL code sooner or later. But
scheduling a stored procedure in MSSQL is generally less complicated than
scheduling a DTS package, unless you need some extra functionality.
Simon|||Hi
You can do it in serveral ways, but I would control it through a SQL Agent
job. If you run the backup
See sp_add_job, sp_add_jobschedule, and sp_add_jobstep
http://msdn.microsoft.com/library/d...asp?frame=true
If you want see a backup job programmed there is a schedule option at the
bottom of the backup wizard screen in Enterprise Manager.
John
"m3ckon" <anonymous@.devdex.com> wrote in message
news:40cdbb60$0$25520$c397aba@.news.newsgroups.ws.. .
> Hi,
> I want to schedule a backup of three databases on a daily basis.
> I've written the code to run the 3 backups in TSQL and was wondering how
> best to automate this procedure?
> Should I put the code in an sproc and then schedule running that command
> in the DTS or should I just add the TSQL into a DTS command?? Or is
> there a better way of doing this?
> I then prefer to use DTSRUNUI to generate the syntax and then schedule
> this from the windows scheduler as opposed to the SQL SCheduler in EM
> (is this the best way?)
> Help would be appreciated
> M3ckon
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!
Scheduler appears to be hung
Hi,
I have a SQL Server 2000 SP3 running on Windows Server 2000. I have moved
two databases that are used continously about two months back to this server
and since then I have noticed this error coming up every 10 to 15 days.
The Scheduler 1 appears to be hung. SPID 0, ECID 0, UMS Context 0x023339C0.
Error: 17883, Severity: 1, State: 0
The Scheduler 0 appears to be hung. SPID 0, ECID 0, UMS Context 0x0233C8D8.
Error: 17883, Severity: 1, State: 0
The SQL Server just freezes and becomes non-responsive. The above error was
present for almost 8 minutes before it started responding. At one time, I
wasn't even able to do a Remote Desktop to the server to check why the server
is not responding and later on I found that it had the same error. Any
information regarding this will be highly appreciated.How many processors do you have running? This implies two, or at least one
with hyperthreading. You might try turning one off completely.
"sharman" wrote:
> Hi,
> I have a SQL Server 2000 SP3 running on Windows Server 2000. I have moved
> two databases that are used continously about two months back to this server
> and since then I have noticed this error coming up every 10 to 15 days.
> The Scheduler 1 appears to be hung. SPID 0, ECID 0, UMS Context 0x023339C0.
> Error: 17883, Severity: 1, State: 0
> The Scheduler 0 appears to be hung. SPID 0, ECID 0, UMS Context 0x0233C8D8.
> Error: 17883, Severity: 1, State: 0
> The SQL Server just freezes and becomes non-responsive. The above error was
> present for almost 8 minutes before it started responding. At one time, I
> wasn't even able to do a Remote Desktop to the server to check why the server
> is not responding and later on I found that it had the same error. Any
> information regarding this will be highly appreciated.|||For starters, just set SQL Server to use a single processor under the
instance properties. Also, you might want to apply SP4...
"sharman" wrote:
> Thanks for the information. It has 2 processors. How do I turn off
> hyperthreading and what are the implications of turning it off? Thanks.
> "James Luetkehoelter" wrote:
> > How many processors do you have running? This implies two, or at least one
> > with hyperthreading. You might try turning one off completely.
> >
> > "sharman" wrote:
> >
> > > Hi,
> > >
> > > I have a SQL Server 2000 SP3 running on Windows Server 2000. I have moved
> > > two databases that are used continously about two months back to this server
> > > and since then I have noticed this error coming up every 10 to 15 days.
> > >
> > > The Scheduler 1 appears to be hung. SPID 0, ECID 0, UMS Context 0x023339C0.
> > > Error: 17883, Severity: 1, State: 0
> > > The Scheduler 0 appears to be hung. SPID 0, ECID 0, UMS Context 0x0233C8D8.
> > > Error: 17883, Severity: 1, State: 0
> > >
> > > The SQL Server just freezes and becomes non-responsive. The above error was
> > > present for almost 8 minutes before it started responding. At one time, I
> > > wasn't even able to do a Remote Desktop to the server to check why the server
> > > is not responding and later on I found that it had the same error. Any
> > > information regarding this will be highly appreciated.|||Thanks for the information. It has 2 processors. How do I turn off
hyperthreading and what are the implications of turning it off? Thanks.
"James Luetkehoelter" wrote:
> How many processors do you have running? This implies two, or at least one
> with hyperthreading. You might try turning one off completely.
> "sharman" wrote:
> > Hi,
> >
> > I have a SQL Server 2000 SP3 running on Windows Server 2000. I have moved
> > two databases that are used continously about two months back to this server
> > and since then I have noticed this error coming up every 10 to 15 days.
> >
> > The Scheduler 1 appears to be hung. SPID 0, ECID 0, UMS Context 0x023339C0.
> > Error: 17883, Severity: 1, State: 0
> > The Scheduler 0 appears to be hung. SPID 0, ECID 0, UMS Context 0x0233C8D8.
> > Error: 17883, Severity: 1, State: 0
> >
> > The SQL Server just freezes and becomes non-responsive. The above error was
> > present for almost 8 minutes before it started responding. At one time, I
> > wasn't even able to do a Remote Desktop to the server to check why the server
> > is not responding and later on I found that it had the same error. Any
> > information regarding this will be highly appreciated.|||Hyperthreading can be diabled at the BIOS level. Most database applications
will experience an increase in performance with HT disabled. Test in your
environment.
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:0AEF20A0-A529-42BE-888B-C86BD6957F88@.microsoft.com...
> Thanks for the information. It has 2 processors. How do I turn off
> hyperthreading and what are the implications of turning it off? Thanks.
> "James Luetkehoelter" wrote:
>> How many processors do you have running? This implies two, or at least
>> one
>> with hyperthreading. You might try turning one off completely.
>> "sharman" wrote:
>> > Hi,
>> >
>> > I have a SQL Server 2000 SP3 running on Windows Server 2000. I have
>> > moved
>> > two databases that are used continously about two months back to this
>> > server
>> > and since then I have noticed this error coming up every 10 to 15 days.
>> >
>> > The Scheduler 1 appears to be hung. SPID 0, ECID 0, UMS Context
>> > 0x023339C0.
>> > Error: 17883, Severity: 1, State: 0
>> > The Scheduler 0 appears to be hung. SPID 0, ECID 0, UMS Context
>> > 0x0233C8D8.
>> > Error: 17883, Severity: 1, State: 0
>> >
>> > The SQL Server just freezes and becomes non-responsive. The above error
>> > was
>> > present for almost 8 minutes before it started responding. At one time,
>> > I
>> > wasn't even able to do a Remote Desktop to the server to check why the
>> > server
>> > is not responding and later on I found that it had the same error. Any
>> > information regarding this will be highly appreciated.|||Take a look at this excellent MS whitepaper:
http://www.microsoft.com/technet/prodtechnol/sql/2005/diagandcorrecterrs.mspx
Linchi
"sharman" wrote:
> Hi,
> I have a SQL Server 2000 SP3 running on Windows Server 2000. I have moved
> two databases that are used continously about two months back to this server
> and since then I have noticed this error coming up every 10 to 15 days.
> The Scheduler 1 appears to be hung. SPID 0, ECID 0, UMS Context 0x023339C0.
> Error: 17883, Severity: 1, State: 0
> The Scheduler 0 appears to be hung. SPID 0, ECID 0, UMS Context 0x0233C8D8.
> Error: 17883, Severity: 1, State: 0
> The SQL Server just freezes and becomes non-responsive. The above error was
> present for almost 8 minutes before it started responding. At one time, I
> wasn't even able to do a Remote Desktop to the server to check why the server
> is not responding and later on I found that it had the same error. Any
> information regarding this will be highly appreciated.
I have a SQL Server 2000 SP3 running on Windows Server 2000. I have moved
two databases that are used continously about two months back to this server
and since then I have noticed this error coming up every 10 to 15 days.
The Scheduler 1 appears to be hung. SPID 0, ECID 0, UMS Context 0x023339C0.
Error: 17883, Severity: 1, State: 0
The Scheduler 0 appears to be hung. SPID 0, ECID 0, UMS Context 0x0233C8D8.
Error: 17883, Severity: 1, State: 0
The SQL Server just freezes and becomes non-responsive. The above error was
present for almost 8 minutes before it started responding. At one time, I
wasn't even able to do a Remote Desktop to the server to check why the server
is not responding and later on I found that it had the same error. Any
information regarding this will be highly appreciated.How many processors do you have running? This implies two, or at least one
with hyperthreading. You might try turning one off completely.
"sharman" wrote:
> Hi,
> I have a SQL Server 2000 SP3 running on Windows Server 2000. I have moved
> two databases that are used continously about two months back to this server
> and since then I have noticed this error coming up every 10 to 15 days.
> The Scheduler 1 appears to be hung. SPID 0, ECID 0, UMS Context 0x023339C0.
> Error: 17883, Severity: 1, State: 0
> The Scheduler 0 appears to be hung. SPID 0, ECID 0, UMS Context 0x0233C8D8.
> Error: 17883, Severity: 1, State: 0
> The SQL Server just freezes and becomes non-responsive. The above error was
> present for almost 8 minutes before it started responding. At one time, I
> wasn't even able to do a Remote Desktop to the server to check why the server
> is not responding and later on I found that it had the same error. Any
> information regarding this will be highly appreciated.|||For starters, just set SQL Server to use a single processor under the
instance properties. Also, you might want to apply SP4...
"sharman" wrote:
> Thanks for the information. It has 2 processors. How do I turn off
> hyperthreading and what are the implications of turning it off? Thanks.
> "James Luetkehoelter" wrote:
> > How many processors do you have running? This implies two, or at least one
> > with hyperthreading. You might try turning one off completely.
> >
> > "sharman" wrote:
> >
> > > Hi,
> > >
> > > I have a SQL Server 2000 SP3 running on Windows Server 2000. I have moved
> > > two databases that are used continously about two months back to this server
> > > and since then I have noticed this error coming up every 10 to 15 days.
> > >
> > > The Scheduler 1 appears to be hung. SPID 0, ECID 0, UMS Context 0x023339C0.
> > > Error: 17883, Severity: 1, State: 0
> > > The Scheduler 0 appears to be hung. SPID 0, ECID 0, UMS Context 0x0233C8D8.
> > > Error: 17883, Severity: 1, State: 0
> > >
> > > The SQL Server just freezes and becomes non-responsive. The above error was
> > > present for almost 8 minutes before it started responding. At one time, I
> > > wasn't even able to do a Remote Desktop to the server to check why the server
> > > is not responding and later on I found that it had the same error. Any
> > > information regarding this will be highly appreciated.|||Thanks for the information. It has 2 processors. How do I turn off
hyperthreading and what are the implications of turning it off? Thanks.
"James Luetkehoelter" wrote:
> How many processors do you have running? This implies two, or at least one
> with hyperthreading. You might try turning one off completely.
> "sharman" wrote:
> > Hi,
> >
> > I have a SQL Server 2000 SP3 running on Windows Server 2000. I have moved
> > two databases that are used continously about two months back to this server
> > and since then I have noticed this error coming up every 10 to 15 days.
> >
> > The Scheduler 1 appears to be hung. SPID 0, ECID 0, UMS Context 0x023339C0.
> > Error: 17883, Severity: 1, State: 0
> > The Scheduler 0 appears to be hung. SPID 0, ECID 0, UMS Context 0x0233C8D8.
> > Error: 17883, Severity: 1, State: 0
> >
> > The SQL Server just freezes and becomes non-responsive. The above error was
> > present for almost 8 minutes before it started responding. At one time, I
> > wasn't even able to do a Remote Desktop to the server to check why the server
> > is not responding and later on I found that it had the same error. Any
> > information regarding this will be highly appreciated.|||Hyperthreading can be diabled at the BIOS level. Most database applications
will experience an increase in performance with HT disabled. Test in your
environment.
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:0AEF20A0-A529-42BE-888B-C86BD6957F88@.microsoft.com...
> Thanks for the information. It has 2 processors. How do I turn off
> hyperthreading and what are the implications of turning it off? Thanks.
> "James Luetkehoelter" wrote:
>> How many processors do you have running? This implies two, or at least
>> one
>> with hyperthreading. You might try turning one off completely.
>> "sharman" wrote:
>> > Hi,
>> >
>> > I have a SQL Server 2000 SP3 running on Windows Server 2000. I have
>> > moved
>> > two databases that are used continously about two months back to this
>> > server
>> > and since then I have noticed this error coming up every 10 to 15 days.
>> >
>> > The Scheduler 1 appears to be hung. SPID 0, ECID 0, UMS Context
>> > 0x023339C0.
>> > Error: 17883, Severity: 1, State: 0
>> > The Scheduler 0 appears to be hung. SPID 0, ECID 0, UMS Context
>> > 0x0233C8D8.
>> > Error: 17883, Severity: 1, State: 0
>> >
>> > The SQL Server just freezes and becomes non-responsive. The above error
>> > was
>> > present for almost 8 minutes before it started responding. At one time,
>> > I
>> > wasn't even able to do a Remote Desktop to the server to check why the
>> > server
>> > is not responding and later on I found that it had the same error. Any
>> > information regarding this will be highly appreciated.|||Take a look at this excellent MS whitepaper:
http://www.microsoft.com/technet/prodtechnol/sql/2005/diagandcorrecterrs.mspx
Linchi
"sharman" wrote:
> Hi,
> I have a SQL Server 2000 SP3 running on Windows Server 2000. I have moved
> two databases that are used continously about two months back to this server
> and since then I have noticed this error coming up every 10 to 15 days.
> The Scheduler 1 appears to be hung. SPID 0, ECID 0, UMS Context 0x023339C0.
> Error: 17883, Severity: 1, State: 0
> The Scheduler 0 appears to be hung. SPID 0, ECID 0, UMS Context 0x0233C8D8.
> Error: 17883, Severity: 1, State: 0
> The SQL Server just freezes and becomes non-responsive. The above error was
> present for almost 8 minutes before it started responding. At one time, I
> wasn't even able to do a Remote Desktop to the server to check why the server
> is not responding and later on I found that it had the same error. Any
> information regarding this will be highly appreciated.
Wednesday, March 21, 2012
Scheduled report for database names and sizes
Does anyone has a script which gives all databases names and there
allocated, used sizes in SQLserver2k. I want to schedule this to create a
daily report.
allocated, used sizes in SQLserver2k. I want to schedule this to create a
daily report.
Thanks,
Nasir"Nasir" <nmajeed@.prosrm.com> wrote in message
news:40056fa6$0$43848$39cecf19@.news.twtelecom.net. ..
> Does anyone has a script which gives all databases names and there
> allocated, used sizes in SQLserver2k. I want to schedule this to create a
> daily report.
> Thanks,
> Nasir
Depending on what you need, this may be good enough:
exec sp_MSforeachdb 'exec sp_helpdb ?'
and/or
exec sp_MSforeachdb 'exec ?..sp_spaceused'
Note that this will return multiple result sets, which are awkward to
process within SQL Server itself, although it should be straightforward in a
client script. sp_MSforeachdb is not documented, but you can use your own
cursor instead for production.
Simonsql
Tuesday, March 20, 2012
SCheduled Jobs
Hi All,
I have many scheduled jobs running that creates a centralized database from
20 different databases in 20 different locations.
The jobs say they were successful, but the information is not coming over
all the time. If I manually run the job in EM, it works fine. Some of the
jobs call Stored Procedures and others are DTS packages. The jobs that call
SP's have between 3 and 20 steps in them, I check each job and all steps say
they were successful.
The DTS packages delete from the destination table and insert all records
from the Source. The DTS can have up to 4 tables being transferred.
TIA,
JoeHi
It is hard to say from your description what is wrong. Adding extra debug to
your processes may help and making sure that you are checking the error
status correctly in the stored procedures may show some unexpected events.
You may also want to profile the process to view what is really being
executed. Loading the data into separate staging table will allow you to
analyse the data that has been imported.
John
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:3817E52C-788E-4AEA-96A6-11E701AA44B3@.microsoft.com...
> Hi All,
> I have many scheduled jobs running that creates a centralized database
> from
> 20 different databases in 20 different locations.
> The jobs say they were successful, but the information is not coming over
> all the time. If I manually run the job in EM, it works fine. Some of
> the
> jobs call Stored Procedures and others are DTS packages. The jobs that
> call
> SP's have between 3 and 20 steps in them, I check each job and all steps
> say
> they were successful.
> The DTS packages delete from the destination table and insert all records
> from the Source. The DTS can have up to 4 tables being transferred.
> TIA,
> Joe
>
I have many scheduled jobs running that creates a centralized database from
20 different databases in 20 different locations.
The jobs say they were successful, but the information is not coming over
all the time. If I manually run the job in EM, it works fine. Some of the
jobs call Stored Procedures and others are DTS packages. The jobs that call
SP's have between 3 and 20 steps in them, I check each job and all steps say
they were successful.
The DTS packages delete from the destination table and insert all records
from the Source. The DTS can have up to 4 tables being transferred.
TIA,
JoeHi
It is hard to say from your description what is wrong. Adding extra debug to
your processes may help and making sure that you are checking the error
status correctly in the stored procedures may show some unexpected events.
You may also want to profile the process to view what is really being
executed. Loading the data into separate staging table will allow you to
analyse the data that has been imported.
John
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:3817E52C-788E-4AEA-96A6-11E701AA44B3@.microsoft.com...
> Hi All,
> I have many scheduled jobs running that creates a centralized database
> from
> 20 different databases in 20 different locations.
> The jobs say they were successful, but the information is not coming over
> all the time. If I manually run the job in EM, it works fine. Some of
> the
> jobs call Stored Procedures and others are DTS packages. The jobs that
> call
> SP's have between 3 and 20 steps in them, I check each job and all steps
> say
> they were successful.
> The DTS packages delete from the destination table and insert all records
> from the Source. The DTS can have up to 4 tables being transferred.
> TIA,
> Joe
>
Scheduled job for a backup device stopped working
Hi all...
The are a number of real databases on our SQL Server 2000 and initially I
set out a number of Backup Devices to execute both FULL and DIFFERENTIAL
backups. My concern is with the FULL backups and the DIFFs are working just
fine. I scheduled a number of FULL backup jobs to execute each Sunday only
and at difference times, such as, the 1st database at 4 am, the next at 4:15
am, the next at 4:30 am etc…each Sunday only. Well I was monitoring them and
like clock work each of these real databases were over written as defined.
The job history shows that a FULL backup for these jobs was performed Nov
13th (when initially defined); the next showed the jobs run at Nov 20th, then
Nov 27th, then Dec 4th, then and Dec 11th. Each of .bak files had the
correct date stamp, but all jobs stopped? I expected, after returning from
holidays to see in the job history a backup for Dec 18, Dec 25, and Jan 1st.
But no, the last run was the 11th of Dec?
The definitions of one particular job (under the Management> SQL Server
Agent > Jobs definition) is as follows:
Name : Auscott Sunday overwrite BU
Under its property definition:
General Tab> enable is checked, Category is Database Maintenance, Owner is sa
Steps Tab> there are 2 Steps with the first having a Type: Transact-SQL
Script (TSQL), and Database: Auscott, and Command:
BACKUP DATABASE [Auscott] TO [Auscott_full_backup] WITH INIT, NOUNLOAD ,
NAME = N'Auscott Sunday overwrite BU', NOSKIP , STATS = 10, DESCRIPTION =
N'Sunday scheduled overwrite Backup of AUSCOTT DB', NOFORMAT DECLARE @.i INT
select @.i = position from msdb..backupset where database_name='Auscott'and
type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset
where database_name='Auscott')
RESTORE VERIFYONLY FROM [Auscott_full_backup] WITH FILE = @.
Schedules Tab> is defined as Schedules Sunday AUSCOTT job, enabled,
Recurring radio button with a description of “occurs every 1 week(s) on
Sunday, at 4:15:00 am
To my way of thinking, every thing seemed to be defined correctly using
backup devices and it wrote 5 times, every Sunday, at 4:15am but stopped?
And there was no disck space issues...so I am stumped.
Does anyone have any suggestions?
Cheers….Roger Sager
Sounds like SQL Agent is stopped. Have you checked that?
Andrew J. Kelly SQL MVP
"Rog" <Rog@.discussions.microsoft.com> wrote in message
news:2421FB88-6004-4490-8B66-6F93A2E6CA7D@.microsoft.com...
> Hi all...
> The are a number of real databases on our SQL Server 2000 and initially I
> set out a number of Backup Devices to execute both FULL and DIFFERENTIAL
> backups. My concern is with the FULL backups and the DIFFs are working
> just
> fine. I scheduled a number of FULL backup jobs to execute each Sunday
> only
> and at difference times, such as, the 1st database at 4 am, the next at
> 4:15
> am, the next at 4:30 am etc.each Sunday only. Well I was monitoring them
> and
> like clock work each of these real databases were over written as defined.
> The job history shows that a FULL backup for these jobs was performed Nov
> 13th (when initially defined); the next showed the jobs run at Nov 20th,
> then
> Nov 27th, then Dec 4th, then and Dec 11th. Each of .bak files had the
> correct date stamp, but all jobs stopped? I expected, after returning
> from
> holidays to see in the job history a backup for Dec 18, Dec 25, and Jan
> 1st.
> But no, the last run was the 11th of Dec?
> The definitions of one particular job (under the Management> SQL Server
> Agent > Jobs definition) is as follows:
> Name : Auscott Sunday overwrite BU
> Under its property definition:
> General Tab> enable is checked, Category is Database Maintenance, Owner
> is sa
> Steps Tab> there are 2 Steps with the first having a Type: Transact-SQL
> Script (TSQL), and Database: Auscott, and Command:
> BACKUP DATABASE [Auscott] TO [Auscott_full_backup] WITH INIT, NOUNLOAD
> ,
> NAME = N'Auscott Sunday overwrite BU', NOSKIP , STATS = 10, DESCRIPTION
> =
> N'Sunday scheduled overwrite Backup of AUSCOTT DB', NOFORMAT DECLARE @.i
> INT
> select @.i = position from msdb..backupset where database_name='Auscott'and
> type!='F' and backup_set_id=(select max(backup_set_id) from
> msdb..backupset
> where database_name='Auscott')
> RESTORE VERIFYONLY FROM [Auscott_full_backup] WITH FILE = @.
> Schedules Tab> is defined as Schedules Sunday AUSCOTT job, enabled,
> Recurring radio button with a description of "occurs every 1 week(s) on
> Sunday, at 4:15:00 am
> To my way of thinking, every thing seemed to be defined correctly using
> backup devices and it wrote 5 times, every Sunday, at 4:15am but stopped?
> And there was no disck space issues...so I am stumped.
> Does anyone have any suggestions?
> Cheers..Roger Sager
>
|||Check to make sure the SQL Server agent is running
Kevin Hill
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Rog" <Rog@.discussions.microsoft.com> wrote in message
news:2421FB88-6004-4490-8B66-6F93A2E6CA7D@.microsoft.com...
> Hi all...
> The are a number of real databases on our SQL Server 2000 and initially I
> set out a number of Backup Devices to execute both FULL and DIFFERENTIAL
> backups. My concern is with the FULL backups and the DIFFs are working
> just
> fine. I scheduled a number of FULL backup jobs to execute each Sunday
> only
> and at difference times, such as, the 1st database at 4 am, the next at
> 4:15
> am, the next at 4:30 am etc.each Sunday only. Well I was monitoring them
> and
> like clock work each of these real databases were over written as defined.
> The job history shows that a FULL backup for these jobs was performed Nov
> 13th (when initially defined); the next showed the jobs run at Nov 20th,
> then
> Nov 27th, then Dec 4th, then and Dec 11th. Each of .bak files had the
> correct date stamp, but all jobs stopped? I expected, after returning
> from
> holidays to see in the job history a backup for Dec 18, Dec 25, and Jan
> 1st.
> But no, the last run was the 11th of Dec?
> The definitions of one particular job (under the Management> SQL Server
> Agent > Jobs definition) is as follows:
> Name : Auscott Sunday overwrite BU
> Under its property definition:
> General Tab> enable is checked, Category is Database Maintenance, Owner
> is sa
> Steps Tab> there are 2 Steps with the first having a Type: Transact-SQL
> Script (TSQL), and Database: Auscott, and Command:
> BACKUP DATABASE [Auscott] TO [Auscott_full_backup] WITH INIT, NOUNLOAD
> ,
> NAME = N'Auscott Sunday overwrite BU', NOSKIP , STATS = 10, DESCRIPTION
> =
> N'Sunday scheduled overwrite Backup of AUSCOTT DB', NOFORMAT DECLARE @.i
> INT
> select @.i = position from msdb..backupset where database_name='Auscott'and
> type!='F' and backup_set_id=(select max(backup_set_id) from
> msdb..backupset
> where database_name='Auscott')
> RESTORE VERIFYONLY FROM [Auscott_full_backup] WITH FILE = @.
> Schedules Tab> is defined as Schedules Sunday AUSCOTT job, enabled,
> Recurring radio button with a description of "occurs every 1 week(s) on
> Sunday, at 4:15:00 am
> To my way of thinking, every thing seemed to be defined correctly using
> backup devices and it wrote 5 times, every Sunday, at 4:15am but stopped?
> And there was no disck space issues...so I am stumped.
> Does anyone have any suggestions?
> Cheers..Roger Sager
>
|||No.. the SQL Server Agent is running, plus for this scheduled jobs, there is
no end-date.
Roger
"Kevin3NF" wrote:
> Check to make sure the SQL Server agent is running
> --
> Kevin Hill
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
>
> "Rog" <Rog@.discussions.microsoft.com> wrote in message
> news:2421FB88-6004-4490-8B66-6F93A2E6CA7D@.microsoft.com...
>
>
|||Thanks Kevin...but I check the SQL Server Agent and it is definitely started
and running. Also for these jobs, there is no end-date. I have taken
several screen shots, but don't know how to upload them.
Cheers...Roger
"Kevin3NF" wrote:
> Check to make sure the SQL Server agent is running
> --
> Kevin Hill
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
>
> "Rog" <Rog@.discussions.microsoft.com> wrote in message
> news:2421FB88-6004-4490-8B66-6F93A2E6CA7D@.microsoft.com...
>
>
|||Update:
Under Enterprise Mgr> Management > Sql Server Agent > Jobs...
..
I can see the last time all these "FULL Backup / Sunday only" jobs run (ie
Dec 11th) and the next run show 8th of Jan. So it will be interesting to see
on Monday morning if these have started again?
Cheers...Roger
The are a number of real databases on our SQL Server 2000 and initially I
set out a number of Backup Devices to execute both FULL and DIFFERENTIAL
backups. My concern is with the FULL backups and the DIFFs are working just
fine. I scheduled a number of FULL backup jobs to execute each Sunday only
and at difference times, such as, the 1st database at 4 am, the next at 4:15
am, the next at 4:30 am etc…each Sunday only. Well I was monitoring them and
like clock work each of these real databases were over written as defined.
The job history shows that a FULL backup for these jobs was performed Nov
13th (when initially defined); the next showed the jobs run at Nov 20th, then
Nov 27th, then Dec 4th, then and Dec 11th. Each of .bak files had the
correct date stamp, but all jobs stopped? I expected, after returning from
holidays to see in the job history a backup for Dec 18, Dec 25, and Jan 1st.
But no, the last run was the 11th of Dec?
The definitions of one particular job (under the Management> SQL Server
Agent > Jobs definition) is as follows:
Name : Auscott Sunday overwrite BU
Under its property definition:
General Tab> enable is checked, Category is Database Maintenance, Owner is sa
Steps Tab> there are 2 Steps with the first having a Type: Transact-SQL
Script (TSQL), and Database: Auscott, and Command:
BACKUP DATABASE [Auscott] TO [Auscott_full_backup] WITH INIT, NOUNLOAD ,
NAME = N'Auscott Sunday overwrite BU', NOSKIP , STATS = 10, DESCRIPTION =
N'Sunday scheduled overwrite Backup of AUSCOTT DB', NOFORMAT DECLARE @.i INT
select @.i = position from msdb..backupset where database_name='Auscott'and
type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset
where database_name='Auscott')
RESTORE VERIFYONLY FROM [Auscott_full_backup] WITH FILE = @.
Schedules Tab> is defined as Schedules Sunday AUSCOTT job, enabled,
Recurring radio button with a description of “occurs every 1 week(s) on
Sunday, at 4:15:00 am
To my way of thinking, every thing seemed to be defined correctly using
backup devices and it wrote 5 times, every Sunday, at 4:15am but stopped?
And there was no disck space issues...so I am stumped.
Does anyone have any suggestions?
Cheers….Roger Sager
Sounds like SQL Agent is stopped. Have you checked that?
Andrew J. Kelly SQL MVP
"Rog" <Rog@.discussions.microsoft.com> wrote in message
news:2421FB88-6004-4490-8B66-6F93A2E6CA7D@.microsoft.com...
> Hi all...
> The are a number of real databases on our SQL Server 2000 and initially I
> set out a number of Backup Devices to execute both FULL and DIFFERENTIAL
> backups. My concern is with the FULL backups and the DIFFs are working
> just
> fine. I scheduled a number of FULL backup jobs to execute each Sunday
> only
> and at difference times, such as, the 1st database at 4 am, the next at
> 4:15
> am, the next at 4:30 am etc.each Sunday only. Well I was monitoring them
> and
> like clock work each of these real databases were over written as defined.
> The job history shows that a FULL backup for these jobs was performed Nov
> 13th (when initially defined); the next showed the jobs run at Nov 20th,
> then
> Nov 27th, then Dec 4th, then and Dec 11th. Each of .bak files had the
> correct date stamp, but all jobs stopped? I expected, after returning
> from
> holidays to see in the job history a backup for Dec 18, Dec 25, and Jan
> 1st.
> But no, the last run was the 11th of Dec?
> The definitions of one particular job (under the Management> SQL Server
> Agent > Jobs definition) is as follows:
> Name : Auscott Sunday overwrite BU
> Under its property definition:
> General Tab> enable is checked, Category is Database Maintenance, Owner
> is sa
> Steps Tab> there are 2 Steps with the first having a Type: Transact-SQL
> Script (TSQL), and Database: Auscott, and Command:
> BACKUP DATABASE [Auscott] TO [Auscott_full_backup] WITH INIT, NOUNLOAD
> ,
> NAME = N'Auscott Sunday overwrite BU', NOSKIP , STATS = 10, DESCRIPTION
> =
> N'Sunday scheduled overwrite Backup of AUSCOTT DB', NOFORMAT DECLARE @.i
> INT
> select @.i = position from msdb..backupset where database_name='Auscott'and
> type!='F' and backup_set_id=(select max(backup_set_id) from
> msdb..backupset
> where database_name='Auscott')
> RESTORE VERIFYONLY FROM [Auscott_full_backup] WITH FILE = @.
> Schedules Tab> is defined as Schedules Sunday AUSCOTT job, enabled,
> Recurring radio button with a description of "occurs every 1 week(s) on
> Sunday, at 4:15:00 am
> To my way of thinking, every thing seemed to be defined correctly using
> backup devices and it wrote 5 times, every Sunday, at 4:15am but stopped?
> And there was no disck space issues...so I am stumped.
> Does anyone have any suggestions?
> Cheers..Roger Sager
>
|||Check to make sure the SQL Server agent is running
Kevin Hill
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Rog" <Rog@.discussions.microsoft.com> wrote in message
news:2421FB88-6004-4490-8B66-6F93A2E6CA7D@.microsoft.com...
> Hi all...
> The are a number of real databases on our SQL Server 2000 and initially I
> set out a number of Backup Devices to execute both FULL and DIFFERENTIAL
> backups. My concern is with the FULL backups and the DIFFs are working
> just
> fine. I scheduled a number of FULL backup jobs to execute each Sunday
> only
> and at difference times, such as, the 1st database at 4 am, the next at
> 4:15
> am, the next at 4:30 am etc.each Sunday only. Well I was monitoring them
> and
> like clock work each of these real databases were over written as defined.
> The job history shows that a FULL backup for these jobs was performed Nov
> 13th (when initially defined); the next showed the jobs run at Nov 20th,
> then
> Nov 27th, then Dec 4th, then and Dec 11th. Each of .bak files had the
> correct date stamp, but all jobs stopped? I expected, after returning
> from
> holidays to see in the job history a backup for Dec 18, Dec 25, and Jan
> 1st.
> But no, the last run was the 11th of Dec?
> The definitions of one particular job (under the Management> SQL Server
> Agent > Jobs definition) is as follows:
> Name : Auscott Sunday overwrite BU
> Under its property definition:
> General Tab> enable is checked, Category is Database Maintenance, Owner
> is sa
> Steps Tab> there are 2 Steps with the first having a Type: Transact-SQL
> Script (TSQL), and Database: Auscott, and Command:
> BACKUP DATABASE [Auscott] TO [Auscott_full_backup] WITH INIT, NOUNLOAD
> ,
> NAME = N'Auscott Sunday overwrite BU', NOSKIP , STATS = 10, DESCRIPTION
> =
> N'Sunday scheduled overwrite Backup of AUSCOTT DB', NOFORMAT DECLARE @.i
> INT
> select @.i = position from msdb..backupset where database_name='Auscott'and
> type!='F' and backup_set_id=(select max(backup_set_id) from
> msdb..backupset
> where database_name='Auscott')
> RESTORE VERIFYONLY FROM [Auscott_full_backup] WITH FILE = @.
> Schedules Tab> is defined as Schedules Sunday AUSCOTT job, enabled,
> Recurring radio button with a description of "occurs every 1 week(s) on
> Sunday, at 4:15:00 am
> To my way of thinking, every thing seemed to be defined correctly using
> backup devices and it wrote 5 times, every Sunday, at 4:15am but stopped?
> And there was no disck space issues...so I am stumped.
> Does anyone have any suggestions?
> Cheers..Roger Sager
>
|||No.. the SQL Server Agent is running, plus for this scheduled jobs, there is
no end-date.
Roger
"Kevin3NF" wrote:
> Check to make sure the SQL Server agent is running
> --
> Kevin Hill
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
>
> "Rog" <Rog@.discussions.microsoft.com> wrote in message
> news:2421FB88-6004-4490-8B66-6F93A2E6CA7D@.microsoft.com...
>
>
|||Thanks Kevin...but I check the SQL Server Agent and it is definitely started
and running. Also for these jobs, there is no end-date. I have taken
several screen shots, but don't know how to upload them.
Cheers...Roger
"Kevin3NF" wrote:
> Check to make sure the SQL Server agent is running
> --
> Kevin Hill
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
>
> "Rog" <Rog@.discussions.microsoft.com> wrote in message
> news:2421FB88-6004-4490-8B66-6F93A2E6CA7D@.microsoft.com...
>
>
|||Update:
Under Enterprise Mgr> Management > Sql Server Agent > Jobs...
..
I can see the last time all these "FULL Backup / Sunday only" jobs run (ie
Dec 11th) and the next run show 8th of Jan. So it will be interesting to see
on Monday morning if these have started again?
Cheers...Roger
Scheduled job for a backup device stopped working
Hi all...
The are a number of real databases on our SQL Server 2000 and initially I
set out a number of Backup Devices to execute both FULL and DIFFERENTIAL
backups. My concern is with the FULL backups and the DIFFs are working just
fine. I scheduled a number of FULL backup jobs to execute each Sunday only
and at difference times, such as, the 1st database at 4 am, the next at 4:15
am, the next at 4:30 am etcâ?¦each Sunday only. Well I was monitoring them and
like clock work each of these real databases were over written as defined.
The job history shows that a FULL backup for these jobs was performed Nov
13th (when initially defined); the next showed the jobs run at Nov 20th, then
Nov 27th, then Dec 4th, then and Dec 11th. Each of .bak files had the
correct date stamp, but all jobs stopped' I expected, after returning from
holidays to see in the job history a backup for Dec 18, Dec 25, and Jan 1st.
But no, the last run was the 11th of Dec?
The definitions of one particular job (under the Management> SQL Server
Agent > Jobs definition) is as follows:
Name : Auscott Sunday overwrite BU
Under its property definition:
General Tab> enable is checked, Category is Database Maintenance, Owner is sa
Steps Tab> there are 2 Steps with the first having a Type: Transact-SQL
Script (TSQL), and Database: Auscott, and Command:
BACKUP DATABASE [Auscott] TO [Auscott_full_backup] WITH INIT, NOUNLOAD ,
NAME = N'Auscott Sunday overwrite BU', NOSKIP , STATS = 10, DESCRIPTION = N'Sunday scheduled overwrite Backup of AUSCOTT DB', NOFORMAT DECLARE @.i INT
select @.i = position from msdb..backupset where database_name='Auscott'and
type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset
where database_name='Auscott')
RESTORE VERIFYONLY FROM [Auscott_full_backup] WITH FILE = @.
Schedules Tab> is defined as Schedules Sunday AUSCOTT job, enabled,
Recurring radio button with a description of â'occurs every 1 week(s) on
Sunday, at 4:15:00 am
To my way of thinking, every thing seemed to be defined correctly using
backup devices and it wrote 5 times, every Sunday, at 4:15am but stopped?
And there was no disck space issues...so I am stumped.
Does anyone have any suggestions?
Cheersâ?¦.Roger SagerSounds like SQL Agent is stopped. Have you checked that?
--
Andrew J. Kelly SQL MVP
"Rog" <Rog@.discussions.microsoft.com> wrote in message
news:2421FB88-6004-4490-8B66-6F93A2E6CA7D@.microsoft.com...
> Hi all...
> The are a number of real databases on our SQL Server 2000 and initially I
> set out a number of Backup Devices to execute both FULL and DIFFERENTIAL
> backups. My concern is with the FULL backups and the DIFFs are working
> just
> fine. I scheduled a number of FULL backup jobs to execute each Sunday
> only
> and at difference times, such as, the 1st database at 4 am, the next at
> 4:15
> am, the next at 4:30 am etc.each Sunday only. Well I was monitoring them
> and
> like clock work each of these real databases were over written as defined.
> The job history shows that a FULL backup for these jobs was performed Nov
> 13th (when initially defined); the next showed the jobs run at Nov 20th,
> then
> Nov 27th, then Dec 4th, then and Dec 11th. Each of .bak files had the
> correct date stamp, but all jobs stopped' I expected, after returning
> from
> holidays to see in the job history a backup for Dec 18, Dec 25, and Jan
> 1st.
> But no, the last run was the 11th of Dec?
> The definitions of one particular job (under the Management> SQL Server
> Agent > Jobs definition) is as follows:
> Name : Auscott Sunday overwrite BU
> Under its property definition:
> General Tab> enable is checked, Category is Database Maintenance, Owner
> is sa
> Steps Tab> there are 2 Steps with the first having a Type: Transact-SQL
> Script (TSQL), and Database: Auscott, and Command:
> BACKUP DATABASE [Auscott] TO [Auscott_full_backup] WITH INIT, NOUNLOAD
> ,
> NAME = N'Auscott Sunday overwrite BU', NOSKIP , STATS = 10, DESCRIPTION
> => N'Sunday scheduled overwrite Backup of AUSCOTT DB', NOFORMAT DECLARE @.i
> INT
> select @.i = position from msdb..backupset where database_name='Auscott'and
> type!='F' and backup_set_id=(select max(backup_set_id) from
> msdb..backupset
> where database_name='Auscott')
> RESTORE VERIFYONLY FROM [Auscott_full_backup] WITH FILE = @.
> Schedules Tab> is defined as Schedules Sunday AUSCOTT job, enabled,
> Recurring radio button with a description of "occurs every 1 week(s) on
> Sunday, at 4:15:00 am
> To my way of thinking, every thing seemed to be defined correctly using
> backup devices and it wrote 5 times, every Sunday, at 4:15am but stopped?
> And there was no disck space issues...so I am stumped.
> Does anyone have any suggestions?
> Cheers..Roger Sager
>|||Check to make sure the SQL Server agent is running
--
Kevin Hill
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Rog" <Rog@.discussions.microsoft.com> wrote in message
news:2421FB88-6004-4490-8B66-6F93A2E6CA7D@.microsoft.com...
> Hi all...
> The are a number of real databases on our SQL Server 2000 and initially I
> set out a number of Backup Devices to execute both FULL and DIFFERENTIAL
> backups. My concern is with the FULL backups and the DIFFs are working
> just
> fine. I scheduled a number of FULL backup jobs to execute each Sunday
> only
> and at difference times, such as, the 1st database at 4 am, the next at
> 4:15
> am, the next at 4:30 am etc.each Sunday only. Well I was monitoring them
> and
> like clock work each of these real databases were over written as defined.
> The job history shows that a FULL backup for these jobs was performed Nov
> 13th (when initially defined); the next showed the jobs run at Nov 20th,
> then
> Nov 27th, then Dec 4th, then and Dec 11th. Each of .bak files had the
> correct date stamp, but all jobs stopped' I expected, after returning
> from
> holidays to see in the job history a backup for Dec 18, Dec 25, and Jan
> 1st.
> But no, the last run was the 11th of Dec?
> The definitions of one particular job (under the Management> SQL Server
> Agent > Jobs definition) is as follows:
> Name : Auscott Sunday overwrite BU
> Under its property definition:
> General Tab> enable is checked, Category is Database Maintenance, Owner
> is sa
> Steps Tab> there are 2 Steps with the first having a Type: Transact-SQL
> Script (TSQL), and Database: Auscott, and Command:
> BACKUP DATABASE [Auscott] TO [Auscott_full_backup] WITH INIT, NOUNLOAD
> ,
> NAME = N'Auscott Sunday overwrite BU', NOSKIP , STATS = 10, DESCRIPTION
> => N'Sunday scheduled overwrite Backup of AUSCOTT DB', NOFORMAT DECLARE @.i
> INT
> select @.i = position from msdb..backupset where database_name='Auscott'and
> type!='F' and backup_set_id=(select max(backup_set_id) from
> msdb..backupset
> where database_name='Auscott')
> RESTORE VERIFYONLY FROM [Auscott_full_backup] WITH FILE = @.
> Schedules Tab> is defined as Schedules Sunday AUSCOTT job, enabled,
> Recurring radio button with a description of "occurs every 1 week(s) on
> Sunday, at 4:15:00 am
> To my way of thinking, every thing seemed to be defined correctly using
> backup devices and it wrote 5 times, every Sunday, at 4:15am but stopped?
> And there was no disck space issues...so I am stumped.
> Does anyone have any suggestions?
> Cheers..Roger Sager
>|||No.. the SQL Server Agent is running, plus for this scheduled jobs, there is
no end-date.
Roger
"Kevin3NF" wrote:
> Check to make sure the SQL Server agent is running
> --
> Kevin Hill
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
>
> "Rog" <Rog@.discussions.microsoft.com> wrote in message
> news:2421FB88-6004-4490-8B66-6F93A2E6CA7D@.microsoft.com...
> > Hi all...
> >
> > The are a number of real databases on our SQL Server 2000 and initially I
> > set out a number of Backup Devices to execute both FULL and DIFFERENTIAL
> > backups. My concern is with the FULL backups and the DIFFs are working
> > just
> > fine. I scheduled a number of FULL backup jobs to execute each Sunday
> > only
> > and at difference times, such as, the 1st database at 4 am, the next at
> > 4:15
> > am, the next at 4:30 am etc.each Sunday only. Well I was monitoring them
> > and
> > like clock work each of these real databases were over written as defined.
> > The job history shows that a FULL backup for these jobs was performed Nov
> > 13th (when initially defined); the next showed the jobs run at Nov 20th,
> > then
> > Nov 27th, then Dec 4th, then and Dec 11th. Each of .bak files had the
> > correct date stamp, but all jobs stopped' I expected, after returning
> > from
> > holidays to see in the job history a backup for Dec 18, Dec 25, and Jan
> > 1st.
> > But no, the last run was the 11th of Dec?
> >
> > The definitions of one particular job (under the Management> SQL Server
> > Agent > Jobs definition) is as follows:
> >
> > Name : Auscott Sunday overwrite BU
> > Under its property definition:
> > General Tab> enable is checked, Category is Database Maintenance, Owner
> > is sa
> > Steps Tab> there are 2 Steps with the first having a Type: Transact-SQL
> > Script (TSQL), and Database: Auscott, and Command:
> > BACKUP DATABASE [Auscott] TO [Auscott_full_backup] WITH INIT, NOUNLOAD
> > ,
> > NAME = N'Auscott Sunday overwrite BU', NOSKIP , STATS = 10, DESCRIPTION
> > => > N'Sunday scheduled overwrite Backup of AUSCOTT DB', NOFORMAT DECLARE @.i
> > INT
> > select @.i = position from msdb..backupset where database_name='Auscott'and
> > type!='F' and backup_set_id=(select max(backup_set_id) from
> > msdb..backupset
> > where database_name='Auscott')
> > RESTORE VERIFYONLY FROM [Auscott_full_backup] WITH FILE = @.
> >
> > Schedules Tab> is defined as Schedules Sunday AUSCOTT job, enabled,
> > Recurring radio button with a description of "occurs every 1 week(s) on
> > Sunday, at 4:15:00 am
> >
> > To my way of thinking, every thing seemed to be defined correctly using
> > backup devices and it wrote 5 times, every Sunday, at 4:15am but stopped?
> > And there was no disck space issues...so I am stumped.
> >
> > Does anyone have any suggestions?
> > Cheers..Roger Sager
> >
>
>|||Thanks Kevin...but I check the SQL Server Agent and it is definitely started
and running. Also for these jobs, there is no end-date. I have taken
several screen shots, but don't know how to upload them.
Cheers...Roger
"Kevin3NF" wrote:
> Check to make sure the SQL Server agent is running
> --
> Kevin Hill
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
>
> "Rog" <Rog@.discussions.microsoft.com> wrote in message
> news:2421FB88-6004-4490-8B66-6F93A2E6CA7D@.microsoft.com...
> > Hi all...
> >
> > The are a number of real databases on our SQL Server 2000 and initially I
> > set out a number of Backup Devices to execute both FULL and DIFFERENTIAL
> > backups. My concern is with the FULL backups and the DIFFs are working
> > just
> > fine. I scheduled a number of FULL backup jobs to execute each Sunday
> > only
> > and at difference times, such as, the 1st database at 4 am, the next at
> > 4:15
> > am, the next at 4:30 am etc.each Sunday only. Well I was monitoring them
> > and
> > like clock work each of these real databases were over written as defined.
> > The job history shows that a FULL backup for these jobs was performed Nov
> > 13th (when initially defined); the next showed the jobs run at Nov 20th,
> > then
> > Nov 27th, then Dec 4th, then and Dec 11th. Each of .bak files had the
> > correct date stamp, but all jobs stopped' I expected, after returning
> > from
> > holidays to see in the job history a backup for Dec 18, Dec 25, and Jan
> > 1st.
> > But no, the last run was the 11th of Dec?
> >
> > The definitions of one particular job (under the Management> SQL Server
> > Agent > Jobs definition) is as follows:
> >
> > Name : Auscott Sunday overwrite BU
> > Under its property definition:
> > General Tab> enable is checked, Category is Database Maintenance, Owner
> > is sa
> > Steps Tab> there are 2 Steps with the first having a Type: Transact-SQL
> > Script (TSQL), and Database: Auscott, and Command:
> > BACKUP DATABASE [Auscott] TO [Auscott_full_backup] WITH INIT, NOUNLOAD
> > ,
> > NAME = N'Auscott Sunday overwrite BU', NOSKIP , STATS = 10, DESCRIPTION
> > => > N'Sunday scheduled overwrite Backup of AUSCOTT DB', NOFORMAT DECLARE @.i
> > INT
> > select @.i = position from msdb..backupset where database_name='Auscott'and
> > type!='F' and backup_set_id=(select max(backup_set_id) from
> > msdb..backupset
> > where database_name='Auscott')
> > RESTORE VERIFYONLY FROM [Auscott_full_backup] WITH FILE = @.
> >
> > Schedules Tab> is defined as Schedules Sunday AUSCOTT job, enabled,
> > Recurring radio button with a description of "occurs every 1 week(s) on
> > Sunday, at 4:15:00 am
> >
> > To my way of thinking, every thing seemed to be defined correctly using
> > backup devices and it wrote 5 times, every Sunday, at 4:15am but stopped?
> > And there was no disck space issues...so I am stumped.
> >
> > Does anyone have any suggestions?
> > Cheers..Roger Sager
> >
>
>|||Update:
Under Enterprise Mgr> Management > Sql Server Agent > Jobs...
.
I can see the last time all these "FULL Backup / Sunday only" jobs run (ie
Dec 11th) and the next run show 8th of Jan. So it will be interesting to see
on Monday morning if these have started again?
Cheers...Roger
The are a number of real databases on our SQL Server 2000 and initially I
set out a number of Backup Devices to execute both FULL and DIFFERENTIAL
backups. My concern is with the FULL backups and the DIFFs are working just
fine. I scheduled a number of FULL backup jobs to execute each Sunday only
and at difference times, such as, the 1st database at 4 am, the next at 4:15
am, the next at 4:30 am etcâ?¦each Sunday only. Well I was monitoring them and
like clock work each of these real databases were over written as defined.
The job history shows that a FULL backup for these jobs was performed Nov
13th (when initially defined); the next showed the jobs run at Nov 20th, then
Nov 27th, then Dec 4th, then and Dec 11th. Each of .bak files had the
correct date stamp, but all jobs stopped' I expected, after returning from
holidays to see in the job history a backup for Dec 18, Dec 25, and Jan 1st.
But no, the last run was the 11th of Dec?
The definitions of one particular job (under the Management> SQL Server
Agent > Jobs definition) is as follows:
Name : Auscott Sunday overwrite BU
Under its property definition:
General Tab> enable is checked, Category is Database Maintenance, Owner is sa
Steps Tab> there are 2 Steps with the first having a Type: Transact-SQL
Script (TSQL), and Database: Auscott, and Command:
BACKUP DATABASE [Auscott] TO [Auscott_full_backup] WITH INIT, NOUNLOAD ,
NAME = N'Auscott Sunday overwrite BU', NOSKIP , STATS = 10, DESCRIPTION = N'Sunday scheduled overwrite Backup of AUSCOTT DB', NOFORMAT DECLARE @.i INT
select @.i = position from msdb..backupset where database_name='Auscott'and
type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset
where database_name='Auscott')
RESTORE VERIFYONLY FROM [Auscott_full_backup] WITH FILE = @.
Schedules Tab> is defined as Schedules Sunday AUSCOTT job, enabled,
Recurring radio button with a description of â'occurs every 1 week(s) on
Sunday, at 4:15:00 am
To my way of thinking, every thing seemed to be defined correctly using
backup devices and it wrote 5 times, every Sunday, at 4:15am but stopped?
And there was no disck space issues...so I am stumped.
Does anyone have any suggestions?
Cheersâ?¦.Roger SagerSounds like SQL Agent is stopped. Have you checked that?
--
Andrew J. Kelly SQL MVP
"Rog" <Rog@.discussions.microsoft.com> wrote in message
news:2421FB88-6004-4490-8B66-6F93A2E6CA7D@.microsoft.com...
> Hi all...
> The are a number of real databases on our SQL Server 2000 and initially I
> set out a number of Backup Devices to execute both FULL and DIFFERENTIAL
> backups. My concern is with the FULL backups and the DIFFs are working
> just
> fine. I scheduled a number of FULL backup jobs to execute each Sunday
> only
> and at difference times, such as, the 1st database at 4 am, the next at
> 4:15
> am, the next at 4:30 am etc.each Sunday only. Well I was monitoring them
> and
> like clock work each of these real databases were over written as defined.
> The job history shows that a FULL backup for these jobs was performed Nov
> 13th (when initially defined); the next showed the jobs run at Nov 20th,
> then
> Nov 27th, then Dec 4th, then and Dec 11th. Each of .bak files had the
> correct date stamp, but all jobs stopped' I expected, after returning
> from
> holidays to see in the job history a backup for Dec 18, Dec 25, and Jan
> 1st.
> But no, the last run was the 11th of Dec?
> The definitions of one particular job (under the Management> SQL Server
> Agent > Jobs definition) is as follows:
> Name : Auscott Sunday overwrite BU
> Under its property definition:
> General Tab> enable is checked, Category is Database Maintenance, Owner
> is sa
> Steps Tab> there are 2 Steps with the first having a Type: Transact-SQL
> Script (TSQL), and Database: Auscott, and Command:
> BACKUP DATABASE [Auscott] TO [Auscott_full_backup] WITH INIT, NOUNLOAD
> ,
> NAME = N'Auscott Sunday overwrite BU', NOSKIP , STATS = 10, DESCRIPTION
> => N'Sunday scheduled overwrite Backup of AUSCOTT DB', NOFORMAT DECLARE @.i
> INT
> select @.i = position from msdb..backupset where database_name='Auscott'and
> type!='F' and backup_set_id=(select max(backup_set_id) from
> msdb..backupset
> where database_name='Auscott')
> RESTORE VERIFYONLY FROM [Auscott_full_backup] WITH FILE = @.
> Schedules Tab> is defined as Schedules Sunday AUSCOTT job, enabled,
> Recurring radio button with a description of "occurs every 1 week(s) on
> Sunday, at 4:15:00 am
> To my way of thinking, every thing seemed to be defined correctly using
> backup devices and it wrote 5 times, every Sunday, at 4:15am but stopped?
> And there was no disck space issues...so I am stumped.
> Does anyone have any suggestions?
> Cheers..Roger Sager
>|||Check to make sure the SQL Server agent is running
--
Kevin Hill
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Rog" <Rog@.discussions.microsoft.com> wrote in message
news:2421FB88-6004-4490-8B66-6F93A2E6CA7D@.microsoft.com...
> Hi all...
> The are a number of real databases on our SQL Server 2000 and initially I
> set out a number of Backup Devices to execute both FULL and DIFFERENTIAL
> backups. My concern is with the FULL backups and the DIFFs are working
> just
> fine. I scheduled a number of FULL backup jobs to execute each Sunday
> only
> and at difference times, such as, the 1st database at 4 am, the next at
> 4:15
> am, the next at 4:30 am etc.each Sunday only. Well I was monitoring them
> and
> like clock work each of these real databases were over written as defined.
> The job history shows that a FULL backup for these jobs was performed Nov
> 13th (when initially defined); the next showed the jobs run at Nov 20th,
> then
> Nov 27th, then Dec 4th, then and Dec 11th. Each of .bak files had the
> correct date stamp, but all jobs stopped' I expected, after returning
> from
> holidays to see in the job history a backup for Dec 18, Dec 25, and Jan
> 1st.
> But no, the last run was the 11th of Dec?
> The definitions of one particular job (under the Management> SQL Server
> Agent > Jobs definition) is as follows:
> Name : Auscott Sunday overwrite BU
> Under its property definition:
> General Tab> enable is checked, Category is Database Maintenance, Owner
> is sa
> Steps Tab> there are 2 Steps with the first having a Type: Transact-SQL
> Script (TSQL), and Database: Auscott, and Command:
> BACKUP DATABASE [Auscott] TO [Auscott_full_backup] WITH INIT, NOUNLOAD
> ,
> NAME = N'Auscott Sunday overwrite BU', NOSKIP , STATS = 10, DESCRIPTION
> => N'Sunday scheduled overwrite Backup of AUSCOTT DB', NOFORMAT DECLARE @.i
> INT
> select @.i = position from msdb..backupset where database_name='Auscott'and
> type!='F' and backup_set_id=(select max(backup_set_id) from
> msdb..backupset
> where database_name='Auscott')
> RESTORE VERIFYONLY FROM [Auscott_full_backup] WITH FILE = @.
> Schedules Tab> is defined as Schedules Sunday AUSCOTT job, enabled,
> Recurring radio button with a description of "occurs every 1 week(s) on
> Sunday, at 4:15:00 am
> To my way of thinking, every thing seemed to be defined correctly using
> backup devices and it wrote 5 times, every Sunday, at 4:15am but stopped?
> And there was no disck space issues...so I am stumped.
> Does anyone have any suggestions?
> Cheers..Roger Sager
>|||No.. the SQL Server Agent is running, plus for this scheduled jobs, there is
no end-date.
Roger
"Kevin3NF" wrote:
> Check to make sure the SQL Server agent is running
> --
> Kevin Hill
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
>
> "Rog" <Rog@.discussions.microsoft.com> wrote in message
> news:2421FB88-6004-4490-8B66-6F93A2E6CA7D@.microsoft.com...
> > Hi all...
> >
> > The are a number of real databases on our SQL Server 2000 and initially I
> > set out a number of Backup Devices to execute both FULL and DIFFERENTIAL
> > backups. My concern is with the FULL backups and the DIFFs are working
> > just
> > fine. I scheduled a number of FULL backup jobs to execute each Sunday
> > only
> > and at difference times, such as, the 1st database at 4 am, the next at
> > 4:15
> > am, the next at 4:30 am etc.each Sunday only. Well I was monitoring them
> > and
> > like clock work each of these real databases were over written as defined.
> > The job history shows that a FULL backup for these jobs was performed Nov
> > 13th (when initially defined); the next showed the jobs run at Nov 20th,
> > then
> > Nov 27th, then Dec 4th, then and Dec 11th. Each of .bak files had the
> > correct date stamp, but all jobs stopped' I expected, after returning
> > from
> > holidays to see in the job history a backup for Dec 18, Dec 25, and Jan
> > 1st.
> > But no, the last run was the 11th of Dec?
> >
> > The definitions of one particular job (under the Management> SQL Server
> > Agent > Jobs definition) is as follows:
> >
> > Name : Auscott Sunday overwrite BU
> > Under its property definition:
> > General Tab> enable is checked, Category is Database Maintenance, Owner
> > is sa
> > Steps Tab> there are 2 Steps with the first having a Type: Transact-SQL
> > Script (TSQL), and Database: Auscott, and Command:
> > BACKUP DATABASE [Auscott] TO [Auscott_full_backup] WITH INIT, NOUNLOAD
> > ,
> > NAME = N'Auscott Sunday overwrite BU', NOSKIP , STATS = 10, DESCRIPTION
> > => > N'Sunday scheduled overwrite Backup of AUSCOTT DB', NOFORMAT DECLARE @.i
> > INT
> > select @.i = position from msdb..backupset where database_name='Auscott'and
> > type!='F' and backup_set_id=(select max(backup_set_id) from
> > msdb..backupset
> > where database_name='Auscott')
> > RESTORE VERIFYONLY FROM [Auscott_full_backup] WITH FILE = @.
> >
> > Schedules Tab> is defined as Schedules Sunday AUSCOTT job, enabled,
> > Recurring radio button with a description of "occurs every 1 week(s) on
> > Sunday, at 4:15:00 am
> >
> > To my way of thinking, every thing seemed to be defined correctly using
> > backup devices and it wrote 5 times, every Sunday, at 4:15am but stopped?
> > And there was no disck space issues...so I am stumped.
> >
> > Does anyone have any suggestions?
> > Cheers..Roger Sager
> >
>
>|||Thanks Kevin...but I check the SQL Server Agent and it is definitely started
and running. Also for these jobs, there is no end-date. I have taken
several screen shots, but don't know how to upload them.
Cheers...Roger
"Kevin3NF" wrote:
> Check to make sure the SQL Server agent is running
> --
> Kevin Hill
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
>
> "Rog" <Rog@.discussions.microsoft.com> wrote in message
> news:2421FB88-6004-4490-8B66-6F93A2E6CA7D@.microsoft.com...
> > Hi all...
> >
> > The are a number of real databases on our SQL Server 2000 and initially I
> > set out a number of Backup Devices to execute both FULL and DIFFERENTIAL
> > backups. My concern is with the FULL backups and the DIFFs are working
> > just
> > fine. I scheduled a number of FULL backup jobs to execute each Sunday
> > only
> > and at difference times, such as, the 1st database at 4 am, the next at
> > 4:15
> > am, the next at 4:30 am etc.each Sunday only. Well I was monitoring them
> > and
> > like clock work each of these real databases were over written as defined.
> > The job history shows that a FULL backup for these jobs was performed Nov
> > 13th (when initially defined); the next showed the jobs run at Nov 20th,
> > then
> > Nov 27th, then Dec 4th, then and Dec 11th. Each of .bak files had the
> > correct date stamp, but all jobs stopped' I expected, after returning
> > from
> > holidays to see in the job history a backup for Dec 18, Dec 25, and Jan
> > 1st.
> > But no, the last run was the 11th of Dec?
> >
> > The definitions of one particular job (under the Management> SQL Server
> > Agent > Jobs definition) is as follows:
> >
> > Name : Auscott Sunday overwrite BU
> > Under its property definition:
> > General Tab> enable is checked, Category is Database Maintenance, Owner
> > is sa
> > Steps Tab> there are 2 Steps with the first having a Type: Transact-SQL
> > Script (TSQL), and Database: Auscott, and Command:
> > BACKUP DATABASE [Auscott] TO [Auscott_full_backup] WITH INIT, NOUNLOAD
> > ,
> > NAME = N'Auscott Sunday overwrite BU', NOSKIP , STATS = 10, DESCRIPTION
> > => > N'Sunday scheduled overwrite Backup of AUSCOTT DB', NOFORMAT DECLARE @.i
> > INT
> > select @.i = position from msdb..backupset where database_name='Auscott'and
> > type!='F' and backup_set_id=(select max(backup_set_id) from
> > msdb..backupset
> > where database_name='Auscott')
> > RESTORE VERIFYONLY FROM [Auscott_full_backup] WITH FILE = @.
> >
> > Schedules Tab> is defined as Schedules Sunday AUSCOTT job, enabled,
> > Recurring radio button with a description of "occurs every 1 week(s) on
> > Sunday, at 4:15:00 am
> >
> > To my way of thinking, every thing seemed to be defined correctly using
> > backup devices and it wrote 5 times, every Sunday, at 4:15am but stopped?
> > And there was no disck space issues...so I am stumped.
> >
> > Does anyone have any suggestions?
> > Cheers..Roger Sager
> >
>
>|||Update:
Under Enterprise Mgr> Management > Sql Server Agent > Jobs...
.
I can see the last time all these "FULL Backup / Sunday only" jobs run (ie
Dec 11th) and the next run show 8th of Jan. So it will be interesting to see
on Monday morning if these have started again?
Cheers...Roger
Scheduled job for a backup device stopped working
Hi all...
The are a number of real databases on our SQL Server 2000 and initially I
set out a number of Backup Devices to execute both FULL and DIFFERENTIAL
backups. My concern is with the FULL backups and the DIFFs are working just
fine. I scheduled a number of FULL backup jobs to execute each Sunday only
and at difference times, such as, the 1st database at 4 am, the next at 4:15
am, the next at 4:30 am etc…each Sunday only. Well I was monitoring them a
nd
like clock work each of these real databases were over written as defined.
The job history shows that a FULL backup for these jobs was performed Nov
13th (when initially defined); the next showed the jobs run at Nov 20th, the
n
Nov 27th, then Dec 4th, then and Dec 11th. Each of .bak files had the
correct date stamp, but all jobs stopped' I expected, after returning from
holidays to see in the job history a backup for Dec 18, Dec 25, and Jan 1st.
But no, the last run was the 11th of Dec?
The definitions of one particular job (under the Management> SQL Server
Agent > Jobs definition) is as follows:
Name : Auscott Sunday overwrite BU
Under its property definition:
General Tab> enable is checked, Category is Database Maintenance, Owner is
sa
Steps Tab> there are 2 Steps with the first having a Type: Transact-SQL
Script (TSQL), and Database: Auscott, and Command:
BACKUP DATABASE [Auscott] TO [Auscott_full_backup] WITH INIT, NOU
NLOAD ,
NAME = N'Auscott Sunday overwrite BU', NOSKIP , STATS = 10, DESCRIPTION =
N'Sunday scheduled overwrite Backup of AUSCOTT DB', NOFORMAT DECLARE @.i INT
select @.i = position from msdb..backupset where database_name='Auscott'and
type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset
where database_name='Auscott')
RESTORE VERIFYONLY FROM [Auscott_full_backup] WITH FILE = @.
Schedules Tab> is defined as Schedules Sunday AUSCOTT job, enabled,
Recurring radio button with a description of “occurs every 1 week(s) on
Sunday, at 4:15:00 am
To my way of thinking, every thing seemed to be defined correctly using
backup devices and it wrote 5 times, every Sunday, at 4:15am but stopped?
And there was no disck space issues...so I am stumped.
Does anyone have any suggestions?
Cheers….Roger SagerSounds like SQL Agent is stopped. Have you checked that?
Andrew J. Kelly SQL MVP
"Rog" <Rog@.discussions.microsoft.com> wrote in message
news:2421FB88-6004-4490-8B66-6F93A2E6CA7D@.microsoft.com...
> Hi all...
> The are a number of real databases on our SQL Server 2000 and initially I
> set out a number of Backup Devices to execute both FULL and DIFFERENTIAL
> backups. My concern is with the FULL backups and the DIFFs are working
> just
> fine. I scheduled a number of FULL backup jobs to execute each Sunday
> only
> and at difference times, such as, the 1st database at 4 am, the next at
> 4:15
> am, the next at 4:30 am etc.each Sunday only. Well I was monitoring them
> and
> like clock work each of these real databases were over written as defined.
> The job history shows that a FULL backup for these jobs was performed Nov
> 13th (when initially defined); the next showed the jobs run at Nov 20th,
> then
> Nov 27th, then Dec 4th, then and Dec 11th. Each of .bak files had the
> correct date stamp, but all jobs stopped' I expected, after returning
> from
> holidays to see in the job history a backup for Dec 18, Dec 25, and Jan
> 1st.
> But no, the last run was the 11th of Dec?
> The definitions of one particular job (under the Management> SQL Server
> Agent > Jobs definition) is as follows:
> Name : Auscott Sunday overwrite BU
> Under its property definition:
> General Tab> enable is checked, Category is Database Maintenance, Owner
> is sa
> Steps Tab> there are 2 Steps with the first having a Type: Transact-SQL
> Script (TSQL), and Database: Auscott, and Command:
> BACKUP DATABASE [Auscott] TO [Auscott_full_backup] WITH INIT, N
OUNLOAD
> ,
> NAME = N'Auscott Sunday overwrite BU', NOSKIP , STATS = 10, DESCRIPTION
> =
> N'Sunday scheduled overwrite Backup of AUSCOTT DB', NOFORMAT DECLARE @.i
> INT
> select @.i = position from msdb..backupset where database_name='Auscott'and
> type!='F' and backup_set_id=(select max(backup_set_id) from
> msdb..backupset
> where database_name='Auscott')
> RESTORE VERIFYONLY FROM [Auscott_full_backup] WITH FILE = @.
> Schedules Tab> is defined as Schedules Sunday AUSCOTT job, enabled,
> Recurring radio button with a description of "occurs every 1 week(s) on
> Sunday, at 4:15:00 am
> To my way of thinking, every thing seemed to be defined correctly using
> backup devices and it wrote 5 times, every Sunday, at 4:15am but stopped?
> And there was no disck space issues...so I am stumped.
> Does anyone have any suggestions?
> Cheers..Roger Sager
>|||Check to make sure the SQL Server agent is running
Kevin Hill
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Rog" <Rog@.discussions.microsoft.com> wrote in message
news:2421FB88-6004-4490-8B66-6F93A2E6CA7D@.microsoft.com...
> Hi all...
> The are a number of real databases on our SQL Server 2000 and initially I
> set out a number of Backup Devices to execute both FULL and DIFFERENTIAL
> backups. My concern is with the FULL backups and the DIFFs are working
> just
> fine. I scheduled a number of FULL backup jobs to execute each Sunday
> only
> and at difference times, such as, the 1st database at 4 am, the next at
> 4:15
> am, the next at 4:30 am etc.each Sunday only. Well I was monitoring them
> and
> like clock work each of these real databases were over written as defined.
> The job history shows that a FULL backup for these jobs was performed Nov
> 13th (when initially defined); the next showed the jobs run at Nov 20th,
> then
> Nov 27th, then Dec 4th, then and Dec 11th. Each of .bak files had the
> correct date stamp, but all jobs stopped' I expected, after returning
> from
> holidays to see in the job history a backup for Dec 18, Dec 25, and Jan
> 1st.
> But no, the last run was the 11th of Dec?
> The definitions of one particular job (under the Management> SQL Server
> Agent > Jobs definition) is as follows:
> Name : Auscott Sunday overwrite BU
> Under its property definition:
> General Tab> enable is checked, Category is Database Maintenance, Owner
> is sa
> Steps Tab> there are 2 Steps with the first having a Type: Transact-SQL
> Script (TSQL), and Database: Auscott, and Command:
> BACKUP DATABASE [Auscott] TO [Auscott_full_backup] WITH INIT, N
OUNLOAD
> ,
> NAME = N'Auscott Sunday overwrite BU', NOSKIP , STATS = 10, DESCRIPTION
> =
> N'Sunday scheduled overwrite Backup of AUSCOTT DB', NOFORMAT DECLARE @.i
> INT
> select @.i = position from msdb..backupset where database_name='Auscott'and
> type!='F' and backup_set_id=(select max(backup_set_id) from
> msdb..backupset
> where database_name='Auscott')
> RESTORE VERIFYONLY FROM [Auscott_full_backup] WITH FILE = @.
> Schedules Tab> is defined as Schedules Sunday AUSCOTT job, enabled,
> Recurring radio button with a description of "occurs every 1 week(s) on
> Sunday, at 4:15:00 am
> To my way of thinking, every thing seemed to be defined correctly using
> backup devices and it wrote 5 times, every Sunday, at 4:15am but stopped?
> And there was no disck space issues...so I am stumped.
> Does anyone have any suggestions?
> Cheers..Roger Sager
>|||No.. the SQL Server Agent is running, plus for this scheduled jobs, there is
no end-date.
Roger
"Kevin3NF" wrote:
> Check to make sure the SQL Server agent is running
> --
> Kevin Hill
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
>
> "Rog" <Rog@.discussions.microsoft.com> wrote in message
> news:2421FB88-6004-4490-8B66-6F93A2E6CA7D@.microsoft.com...
>
>|||Thanks Kevin...but I check the SQL Server Agent and it is definitely started
and running. Also for these jobs, there is no end-date. I have taken
several screen shots, but don't know how to upload them.
Cheers...Roger
"Kevin3NF" wrote:
> Check to make sure the SQL Server agent is running
> --
> Kevin Hill
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
>
> "Rog" <Rog@.discussions.microsoft.com> wrote in message
> news:2421FB88-6004-4490-8B66-6F93A2E6CA7D@.microsoft.com...
>
>|||Update:
Under Enterprise Mgr> Management > Sql Server Agent > Jobs...
.
I can see the last time all these "FULL Backup / Sunday only" jobs run (ie
Dec 11th) and the next run show 8th of Jan. So it will be interesting to see
on Monday morning if these have started again?
Cheers...Roger
The are a number of real databases on our SQL Server 2000 and initially I
set out a number of Backup Devices to execute both FULL and DIFFERENTIAL
backups. My concern is with the FULL backups and the DIFFs are working just
fine. I scheduled a number of FULL backup jobs to execute each Sunday only
and at difference times, such as, the 1st database at 4 am, the next at 4:15
am, the next at 4:30 am etc…each Sunday only. Well I was monitoring them a
nd
like clock work each of these real databases were over written as defined.
The job history shows that a FULL backup for these jobs was performed Nov
13th (when initially defined); the next showed the jobs run at Nov 20th, the
n
Nov 27th, then Dec 4th, then and Dec 11th. Each of .bak files had the
correct date stamp, but all jobs stopped' I expected, after returning from
holidays to see in the job history a backup for Dec 18, Dec 25, and Jan 1st.
But no, the last run was the 11th of Dec?
The definitions of one particular job (under the Management> SQL Server
Agent > Jobs definition) is as follows:
Name : Auscott Sunday overwrite BU
Under its property definition:
General Tab> enable is checked, Category is Database Maintenance, Owner is
sa
Steps Tab> there are 2 Steps with the first having a Type: Transact-SQL
Script (TSQL), and Database: Auscott, and Command:
BACKUP DATABASE [Auscott] TO [Auscott_full_backup] WITH INIT, NOU
NLOAD ,
NAME = N'Auscott Sunday overwrite BU', NOSKIP , STATS = 10, DESCRIPTION =
N'Sunday scheduled overwrite Backup of AUSCOTT DB', NOFORMAT DECLARE @.i INT
select @.i = position from msdb..backupset where database_name='Auscott'and
type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset
where database_name='Auscott')
RESTORE VERIFYONLY FROM [Auscott_full_backup] WITH FILE = @.
Schedules Tab> is defined as Schedules Sunday AUSCOTT job, enabled,
Recurring radio button with a description of “occurs every 1 week(s) on
Sunday, at 4:15:00 am
To my way of thinking, every thing seemed to be defined correctly using
backup devices and it wrote 5 times, every Sunday, at 4:15am but stopped?
And there was no disck space issues...so I am stumped.
Does anyone have any suggestions?
Cheers….Roger SagerSounds like SQL Agent is stopped. Have you checked that?
Andrew J. Kelly SQL MVP
"Rog" <Rog@.discussions.microsoft.com> wrote in message
news:2421FB88-6004-4490-8B66-6F93A2E6CA7D@.microsoft.com...
> Hi all...
> The are a number of real databases on our SQL Server 2000 and initially I
> set out a number of Backup Devices to execute both FULL and DIFFERENTIAL
> backups. My concern is with the FULL backups and the DIFFs are working
> just
> fine. I scheduled a number of FULL backup jobs to execute each Sunday
> only
> and at difference times, such as, the 1st database at 4 am, the next at
> 4:15
> am, the next at 4:30 am etc.each Sunday only. Well I was monitoring them
> and
> like clock work each of these real databases were over written as defined.
> The job history shows that a FULL backup for these jobs was performed Nov
> 13th (when initially defined); the next showed the jobs run at Nov 20th,
> then
> Nov 27th, then Dec 4th, then and Dec 11th. Each of .bak files had the
> correct date stamp, but all jobs stopped' I expected, after returning
> from
> holidays to see in the job history a backup for Dec 18, Dec 25, and Jan
> 1st.
> But no, the last run was the 11th of Dec?
> The definitions of one particular job (under the Management> SQL Server
> Agent > Jobs definition) is as follows:
> Name : Auscott Sunday overwrite BU
> Under its property definition:
> General Tab> enable is checked, Category is Database Maintenance, Owner
> is sa
> Steps Tab> there are 2 Steps with the first having a Type: Transact-SQL
> Script (TSQL), and Database: Auscott, and Command:
> BACKUP DATABASE [Auscott] TO [Auscott_full_backup] WITH INIT, N
OUNLOAD
> ,
> NAME = N'Auscott Sunday overwrite BU', NOSKIP , STATS = 10, DESCRIPTION
> =
> N'Sunday scheduled overwrite Backup of AUSCOTT DB', NOFORMAT DECLARE @.i
> INT
> select @.i = position from msdb..backupset where database_name='Auscott'and
> type!='F' and backup_set_id=(select max(backup_set_id) from
> msdb..backupset
> where database_name='Auscott')
> RESTORE VERIFYONLY FROM [Auscott_full_backup] WITH FILE = @.
> Schedules Tab> is defined as Schedules Sunday AUSCOTT job, enabled,
> Recurring radio button with a description of "occurs every 1 week(s) on
> Sunday, at 4:15:00 am
> To my way of thinking, every thing seemed to be defined correctly using
> backup devices and it wrote 5 times, every Sunday, at 4:15am but stopped?
> And there was no disck space issues...so I am stumped.
> Does anyone have any suggestions?
> Cheers..Roger Sager
>|||Check to make sure the SQL Server agent is running
Kevin Hill
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Rog" <Rog@.discussions.microsoft.com> wrote in message
news:2421FB88-6004-4490-8B66-6F93A2E6CA7D@.microsoft.com...
> Hi all...
> The are a number of real databases on our SQL Server 2000 and initially I
> set out a number of Backup Devices to execute both FULL and DIFFERENTIAL
> backups. My concern is with the FULL backups and the DIFFs are working
> just
> fine. I scheduled a number of FULL backup jobs to execute each Sunday
> only
> and at difference times, such as, the 1st database at 4 am, the next at
> 4:15
> am, the next at 4:30 am etc.each Sunday only. Well I was monitoring them
> and
> like clock work each of these real databases were over written as defined.
> The job history shows that a FULL backup for these jobs was performed Nov
> 13th (when initially defined); the next showed the jobs run at Nov 20th,
> then
> Nov 27th, then Dec 4th, then and Dec 11th. Each of .bak files had the
> correct date stamp, but all jobs stopped' I expected, after returning
> from
> holidays to see in the job history a backup for Dec 18, Dec 25, and Jan
> 1st.
> But no, the last run was the 11th of Dec?
> The definitions of one particular job (under the Management> SQL Server
> Agent > Jobs definition) is as follows:
> Name : Auscott Sunday overwrite BU
> Under its property definition:
> General Tab> enable is checked, Category is Database Maintenance, Owner
> is sa
> Steps Tab> there are 2 Steps with the first having a Type: Transact-SQL
> Script (TSQL), and Database: Auscott, and Command:
> BACKUP DATABASE [Auscott] TO [Auscott_full_backup] WITH INIT, N
OUNLOAD
> ,
> NAME = N'Auscott Sunday overwrite BU', NOSKIP , STATS = 10, DESCRIPTION
> =
> N'Sunday scheduled overwrite Backup of AUSCOTT DB', NOFORMAT DECLARE @.i
> INT
> select @.i = position from msdb..backupset where database_name='Auscott'and
> type!='F' and backup_set_id=(select max(backup_set_id) from
> msdb..backupset
> where database_name='Auscott')
> RESTORE VERIFYONLY FROM [Auscott_full_backup] WITH FILE = @.
> Schedules Tab> is defined as Schedules Sunday AUSCOTT job, enabled,
> Recurring radio button with a description of "occurs every 1 week(s) on
> Sunday, at 4:15:00 am
> To my way of thinking, every thing seemed to be defined correctly using
> backup devices and it wrote 5 times, every Sunday, at 4:15am but stopped?
> And there was no disck space issues...so I am stumped.
> Does anyone have any suggestions?
> Cheers..Roger Sager
>|||No.. the SQL Server Agent is running, plus for this scheduled jobs, there is
no end-date.
Roger
"Kevin3NF" wrote:
> Check to make sure the SQL Server agent is running
> --
> Kevin Hill
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
>
> "Rog" <Rog@.discussions.microsoft.com> wrote in message
> news:2421FB88-6004-4490-8B66-6F93A2E6CA7D@.microsoft.com...
>
>|||Thanks Kevin...but I check the SQL Server Agent and it is definitely started
and running. Also for these jobs, there is no end-date. I have taken
several screen shots, but don't know how to upload them.
Cheers...Roger
"Kevin3NF" wrote:
> Check to make sure the SQL Server agent is running
> --
> Kevin Hill
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
>
> "Rog" <Rog@.discussions.microsoft.com> wrote in message
> news:2421FB88-6004-4490-8B66-6F93A2E6CA7D@.microsoft.com...
>
>|||Update:
Under Enterprise Mgr> Management > Sql Server Agent > Jobs...
.
I can see the last time all these "FULL Backup / Sunday only" jobs run (ie
Dec 11th) and the next run show 8th of Jan. So it will be interesting to see
on Monday morning if these have started again?
Cheers...Roger
Scheduled job for a backup device stopped
Hi all...
The are a number of real databases on our SQL Server 2000 and initially I
set out a number of Backup Devices to execute both FULL and DIFFERENTIAL
backups. My concern is with the FULL backups and the DIFFs are working just
fine. I scheduled a number of FULL backup jobs to execute each Sunday only
and at difference times, such as, the 1st database at 4 am, the next at 4:15
am, the next at 4:30 am etc…each Sunday only. Well I was monitoring them and
like clock work each of these real databases were over written as defined.
The job history shows that a FULL backup for these jobs was performed Nov
13th (when initially defined); the next showed the jobs run at Nov 20th, then
Nov 27th, then Dec 4th, then and Dec 11th. Each of .bak files had the
correct date stamp, but all jobs stopped? I expected, after returning from
holidays to see in the job history a backup for Dec 18, Dec 25, and Jan 1st.
But no, the last run was the 11th of Dec?
The definitions of one particular job (under the Management> SQL Server
Agent > Jobs definition) is as follows:
Name : Auscott Sunday overwrite BU
Under its property definition:
General Tab> enable is checked, Category is Database Maintenance, Owner is sa
Steps Tab> there are 2 Steps with the first having a Type: Transact-SQL
Script (TSQL), and Database: Auscott, and Command:
BACKUP DATABASE [Auscott] TO [Auscott_full_backup] WITH INIT, NOUNLOAD ,
NAME = N'Auscott Sunday overwrite BU', NOSKIP , STATS = 10, DESCRIPTION =
N'Sunday scheduled overwrite Backup of AUSCOTT DB', NOFORMAT DECLARE @.i INT
select @.i = position from msdb..backupset where database_name='Auscott'and
type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset
where database_name='Auscott')
RESTORE VERIFYONLY FROM [Auscott_full_backup] WITH FILE = @.
Schedules Tab> is defined as Schedules Sunday AUSCOTT job, enabled,
Recurring radio button with a description of “occurs every 1 week(s) on
Sunday, at 4:15:00 am
To my way of thinking, every thing seemed to be defined correctly using
backup devices and it wrote 5 times, every Sunday, at 4:15am but stopped?
And there was no disck space issues...so I am stumped.
Does anyone have any suggestions?
Cheers….Roger Sager
PS - We are on SQL Server 2000 SP4
Sorry folks...this is a duplicate entry. Please see the other one with many
postings.
Roger Sager
|||Hi ,
Nice job but u can set a database maintinance plan which will delete ur
0ne week old backup files.
If u want to keep those backups then write a job to copy them to other
location on network and delete from the original server so sever will
not run out of disk space.
By the way on which system u are doing backup is't ur production server
if yes then u do ur back on the network derive
So in case if something happens ur still safe.
From
Doller
The are a number of real databases on our SQL Server 2000 and initially I
set out a number of Backup Devices to execute both FULL and DIFFERENTIAL
backups. My concern is with the FULL backups and the DIFFs are working just
fine. I scheduled a number of FULL backup jobs to execute each Sunday only
and at difference times, such as, the 1st database at 4 am, the next at 4:15
am, the next at 4:30 am etc…each Sunday only. Well I was monitoring them and
like clock work each of these real databases were over written as defined.
The job history shows that a FULL backup for these jobs was performed Nov
13th (when initially defined); the next showed the jobs run at Nov 20th, then
Nov 27th, then Dec 4th, then and Dec 11th. Each of .bak files had the
correct date stamp, but all jobs stopped? I expected, after returning from
holidays to see in the job history a backup for Dec 18, Dec 25, and Jan 1st.
But no, the last run was the 11th of Dec?
The definitions of one particular job (under the Management> SQL Server
Agent > Jobs definition) is as follows:
Name : Auscott Sunday overwrite BU
Under its property definition:
General Tab> enable is checked, Category is Database Maintenance, Owner is sa
Steps Tab> there are 2 Steps with the first having a Type: Transact-SQL
Script (TSQL), and Database: Auscott, and Command:
BACKUP DATABASE [Auscott] TO [Auscott_full_backup] WITH INIT, NOUNLOAD ,
NAME = N'Auscott Sunday overwrite BU', NOSKIP , STATS = 10, DESCRIPTION =
N'Sunday scheduled overwrite Backup of AUSCOTT DB', NOFORMAT DECLARE @.i INT
select @.i = position from msdb..backupset where database_name='Auscott'and
type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset
where database_name='Auscott')
RESTORE VERIFYONLY FROM [Auscott_full_backup] WITH FILE = @.
Schedules Tab> is defined as Schedules Sunday AUSCOTT job, enabled,
Recurring radio button with a description of “occurs every 1 week(s) on
Sunday, at 4:15:00 am
To my way of thinking, every thing seemed to be defined correctly using
backup devices and it wrote 5 times, every Sunday, at 4:15am but stopped?
And there was no disck space issues...so I am stumped.
Does anyone have any suggestions?
Cheers….Roger Sager
PS - We are on SQL Server 2000 SP4
Sorry folks...this is a duplicate entry. Please see the other one with many
postings.
Roger Sager
|||Hi ,
Nice job but u can set a database maintinance plan which will delete ur
0ne week old backup files.
If u want to keep those backups then write a job to copy them to other
location on network and delete from the original server so sever will
not run out of disk space.
By the way on which system u are doing backup is't ur production server
if yes then u do ur back on the network derive
So in case if something happens ur still safe.
From
Doller
Scheduled job for a backup device stopped
Hi all...
The are a number of real databases on our SQL Server 2000 and initially I
set out a number of Backup Devices to execute both FULL and DIFFERENTIAL
backups. My concern is with the FULL backups and the DIFFs are working just
fine. I scheduled a number of FULL backup jobs to execute each Sunday only
and at difference times, such as, the 1st database at 4 am, the next at 4:15
am, the next at 4:30 am etc…each Sunday only. Well I was monitoring them a
nd
like clock work each of these real databases were over written as defined.
The job history shows that a FULL backup for these jobs was performed Nov
13th (when initially defined); the next showed the jobs run at Nov 20th, the
n
Nov 27th, then Dec 4th, then and Dec 11th. Each of .bak files had the
correct date stamp, but all jobs stopped' I expected, after returning from
holidays to see in the job history a backup for Dec 18, Dec 25, and Jan 1st.
But no, the last run was the 11th of Dec?
The definitions of one particular job (under the Management> SQL Server
Agent > Jobs definition) is as follows:
Name : Auscott Sunday overwrite BU
Under its property definition:
General Tab> enable is checked, Category is Database Maintenance, Owner is
sa
Steps Tab> there are 2 Steps with the first having a Type: Transact-SQL
Script (TSQL), and Database: Auscott, and Command:
BACKUP DATABASE [Auscott] TO [Auscott_full_backup] WITH INIT, NOU
NLOAD ,
NAME = N'Auscott Sunday overwrite BU', NOSKIP , STATS = 10, DESCRIPTION =
N'Sunday scheduled overwrite Backup of AUSCOTT DB', NOFORMAT DECLARE @.i INT
select @.i = position from msdb..backupset where database_name='Auscott'and
type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset
where database_name='Auscott')
RESTORE VERIFYONLY FROM [Auscott_full_backup] WITH FILE = @.
Schedules Tab> is defined as Schedules Sunday AUSCOTT job, enabled,
Recurring radio button with a description of “occurs every 1 week(s) on
Sunday, at 4:15:00 am
To my way of thinking, every thing seemed to be defined correctly using
backup devices and it wrote 5 times, every Sunday, at 4:15am but stopped?
And there was no disck space issues...so I am stumped.
Does anyone have any suggestions?
Cheers….Roger Sager
PS - We are on SQL Server 2000 SP4Sorry folks...this is a duplicate entry. Please see the other one with many
postings.
Roger Sager|||Hi ,
Nice job but u can set a database maintinance plan which will delete ur
0ne week old backup files.
If u want to keep those backups then write a job to copy them to other
location on network and delete from the original server so sever will
not run out of disk space.
By the way on which system u are doing backup is't ur production server
if yes then u do ur back on the network derive
So in case if something happens ur still safe.
From
Doller
The are a number of real databases on our SQL Server 2000 and initially I
set out a number of Backup Devices to execute both FULL and DIFFERENTIAL
backups. My concern is with the FULL backups and the DIFFs are working just
fine. I scheduled a number of FULL backup jobs to execute each Sunday only
and at difference times, such as, the 1st database at 4 am, the next at 4:15
am, the next at 4:30 am etc…each Sunday only. Well I was monitoring them a
nd
like clock work each of these real databases were over written as defined.
The job history shows that a FULL backup for these jobs was performed Nov
13th (when initially defined); the next showed the jobs run at Nov 20th, the
n
Nov 27th, then Dec 4th, then and Dec 11th. Each of .bak files had the
correct date stamp, but all jobs stopped' I expected, after returning from
holidays to see in the job history a backup for Dec 18, Dec 25, and Jan 1st.
But no, the last run was the 11th of Dec?
The definitions of one particular job (under the Management> SQL Server
Agent > Jobs definition) is as follows:
Name : Auscott Sunday overwrite BU
Under its property definition:
General Tab> enable is checked, Category is Database Maintenance, Owner is
sa
Steps Tab> there are 2 Steps with the first having a Type: Transact-SQL
Script (TSQL), and Database: Auscott, and Command:
BACKUP DATABASE [Auscott] TO [Auscott_full_backup] WITH INIT, NOU
NLOAD ,
NAME = N'Auscott Sunday overwrite BU', NOSKIP , STATS = 10, DESCRIPTION =
N'Sunday scheduled overwrite Backup of AUSCOTT DB', NOFORMAT DECLARE @.i INT
select @.i = position from msdb..backupset where database_name='Auscott'and
type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset
where database_name='Auscott')
RESTORE VERIFYONLY FROM [Auscott_full_backup] WITH FILE = @.
Schedules Tab> is defined as Schedules Sunday AUSCOTT job, enabled,
Recurring radio button with a description of “occurs every 1 week(s) on
Sunday, at 4:15:00 am
To my way of thinking, every thing seemed to be defined correctly using
backup devices and it wrote 5 times, every Sunday, at 4:15am but stopped?
And there was no disck space issues...so I am stumped.
Does anyone have any suggestions?
Cheers….Roger Sager
PS - We are on SQL Server 2000 SP4Sorry folks...this is a duplicate entry. Please see the other one with many
postings.
Roger Sager|||Hi ,
Nice job but u can set a database maintinance plan which will delete ur
0ne week old backup files.
If u want to keep those backups then write a job to copy them to other
location on network and delete from the original server so sever will
not run out of disk space.
By the way on which system u are doing backup is't ur production server
if yes then u do ur back on the network derive
So in case if something happens ur still safe.
From
Doller
Scheduled job for a backup device stopped
Hi all...
The are a number of real databases on our SQL Server 2000 and initially I
set out a number of Backup Devices to execute both FULL and DIFFERENTIAL
backups. My concern is with the FULL backups and the DIFFs are working just
fine. I scheduled a number of FULL backup jobs to execute each Sunday only
and at difference times, such as, the 1st database at 4 am, the next at 4:15
am, the next at 4:30 am etcâ?¦each Sunday only. Well I was monitoring them and
like clock work each of these real databases were over written as defined.
The job history shows that a FULL backup for these jobs was performed Nov
13th (when initially defined); the next showed the jobs run at Nov 20th, then
Nov 27th, then Dec 4th, then and Dec 11th. Each of .bak files had the
correct date stamp, but all jobs stopped' I expected, after returning from
holidays to see in the job history a backup for Dec 18, Dec 25, and Jan 1st.
But no, the last run was the 11th of Dec?
The definitions of one particular job (under the Management> SQL Server
Agent > Jobs definition) is as follows:
Name : Auscott Sunday overwrite BU
Under its property definition:
General Tab> enable is checked, Category is Database Maintenance, Owner is sa
Steps Tab> there are 2 Steps with the first having a Type: Transact-SQL
Script (TSQL), and Database: Auscott, and Command:
BACKUP DATABASE [Auscott] TO [Auscott_full_backup] WITH INIT, NOUNLOAD ,
NAME = N'Auscott Sunday overwrite BU', NOSKIP , STATS = 10, DESCRIPTION = N'Sunday scheduled overwrite Backup of AUSCOTT DB', NOFORMAT DECLARE @.i INT
select @.i = position from msdb..backupset where database_name='Auscott'and
type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset
where database_name='Auscott')
RESTORE VERIFYONLY FROM [Auscott_full_backup] WITH FILE = @.
Schedules Tab> is defined as Schedules Sunday AUSCOTT job, enabled,
Recurring radio button with a description of â'occurs every 1 week(s) on
Sunday, at 4:15:00 am
To my way of thinking, every thing seemed to be defined correctly using
backup devices and it wrote 5 times, every Sunday, at 4:15am but stopped?
And there was no disck space issues...so I am stumped.
Does anyone have any suggestions?
Cheersâ?¦.Roger Sager
PS - We are on SQL Server 2000 SP4Sorry folks...this is a duplicate entry. Please see the other one with many
postings.
Roger Sager|||Hi ,
Nice job but u can set a database maintinance plan which will delete ur
0ne week old backup files.
If u want to keep those backups then write a job to copy them to other
location on network and delete from the original server so sever will
not run out of disk space.
By the way on which system u are doing backup is't ur production server
if yes then u do ur back on the network derive
So in case if something happens ur still safe.
From
Doller
The are a number of real databases on our SQL Server 2000 and initially I
set out a number of Backup Devices to execute both FULL and DIFFERENTIAL
backups. My concern is with the FULL backups and the DIFFs are working just
fine. I scheduled a number of FULL backup jobs to execute each Sunday only
and at difference times, such as, the 1st database at 4 am, the next at 4:15
am, the next at 4:30 am etcâ?¦each Sunday only. Well I was monitoring them and
like clock work each of these real databases were over written as defined.
The job history shows that a FULL backup for these jobs was performed Nov
13th (when initially defined); the next showed the jobs run at Nov 20th, then
Nov 27th, then Dec 4th, then and Dec 11th. Each of .bak files had the
correct date stamp, but all jobs stopped' I expected, after returning from
holidays to see in the job history a backup for Dec 18, Dec 25, and Jan 1st.
But no, the last run was the 11th of Dec?
The definitions of one particular job (under the Management> SQL Server
Agent > Jobs definition) is as follows:
Name : Auscott Sunday overwrite BU
Under its property definition:
General Tab> enable is checked, Category is Database Maintenance, Owner is sa
Steps Tab> there are 2 Steps with the first having a Type: Transact-SQL
Script (TSQL), and Database: Auscott, and Command:
BACKUP DATABASE [Auscott] TO [Auscott_full_backup] WITH INIT, NOUNLOAD ,
NAME = N'Auscott Sunday overwrite BU', NOSKIP , STATS = 10, DESCRIPTION = N'Sunday scheduled overwrite Backup of AUSCOTT DB', NOFORMAT DECLARE @.i INT
select @.i = position from msdb..backupset where database_name='Auscott'and
type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset
where database_name='Auscott')
RESTORE VERIFYONLY FROM [Auscott_full_backup] WITH FILE = @.
Schedules Tab> is defined as Schedules Sunday AUSCOTT job, enabled,
Recurring radio button with a description of â'occurs every 1 week(s) on
Sunday, at 4:15:00 am
To my way of thinking, every thing seemed to be defined correctly using
backup devices and it wrote 5 times, every Sunday, at 4:15am but stopped?
And there was no disck space issues...so I am stumped.
Does anyone have any suggestions?
Cheersâ?¦.Roger Sager
PS - We are on SQL Server 2000 SP4Sorry folks...this is a duplicate entry. Please see the other one with many
postings.
Roger Sager|||Hi ,
Nice job but u can set a database maintinance plan which will delete ur
0ne week old backup files.
If u want to keep those backups then write a job to copy them to other
location on network and delete from the original server so sever will
not run out of disk space.
By the way on which system u are doing backup is't ur production server
if yes then u do ur back on the network derive
So in case if something happens ur still safe.
From
Doller
Monday, March 12, 2012
Scheduled DTS Does Not Work Across Servers
Hi,
I was able to successfully create a scheduled DTS job transferring
objects between two databases that Are On The Same Server.
But was unable to have a scheduled DTS job (transferring objects between
different servers)run automatically.
If I execute it manually, it works ok; but not if it's scheduled.
The agent on all servers is started up.
Would very much appreciate your help.
Thank you very much
Mike
*** Sent via Developersdex http://www.codecomments.com ***
We may need more security information on this one but looks like this is a
permissions problem.
Supposing that you are using Windows authentication, when you execute the
package you most likely have access to both servers. But when you schedule
it, the user running the job maybe does not have access to the other server.
Hope this helps,
Benjamin Nevarez
Database Administrator
"mike" wrote:
> Hi,
> I was able to successfully create a scheduled DTS job transferring
> objects between two databases that Are On The Same Server.
> But was unable to have a scheduled DTS job (transferring objects between
> different servers)run automatically.
> If I execute it manually, it works ok; but not if it's scheduled.
> The agent on all servers is started up.
> Would very much appreciate your help.
> Thank you very much
>
> Mike
> *** Sent via Developersdex http://www.codecomments.com ***
>
|||Purrfect! It worked!
Thank you very much
Mike
*** Sent via Developersdex http://www.codecomments.com ***
I was able to successfully create a scheduled DTS job transferring
objects between two databases that Are On The Same Server.
But was unable to have a scheduled DTS job (transferring objects between
different servers)run automatically.
If I execute it manually, it works ok; but not if it's scheduled.
The agent on all servers is started up.
Would very much appreciate your help.
Thank you very much
Mike
*** Sent via Developersdex http://www.codecomments.com ***
We may need more security information on this one but looks like this is a
permissions problem.
Supposing that you are using Windows authentication, when you execute the
package you most likely have access to both servers. But when you schedule
it, the user running the job maybe does not have access to the other server.
Hope this helps,
Benjamin Nevarez
Database Administrator
"mike" wrote:
> Hi,
> I was able to successfully create a scheduled DTS job transferring
> objects between two databases that Are On The Same Server.
> But was unable to have a scheduled DTS job (transferring objects between
> different servers)run automatically.
> If I execute it manually, it works ok; but not if it's scheduled.
> The agent on all servers is started up.
> Would very much appreciate your help.
> Thank you very much
>
> Mike
> *** Sent via Developersdex http://www.codecomments.com ***
>
|||Purrfect! It worked!
Thank you very much
Mike
*** Sent via Developersdex http://www.codecomments.com ***
Scheduled DTS Does Not Work Across Servers
Hi,
I was able to successfully create a scheduled DTS job transferring
objects between two databases that Are On The Same Server.
But was unable to have a scheduled DTS job (transferring objects between
different servers)run automatically.
If I execute it manually, it works ok; but not if it's scheduled.
The agent on all servers is started up.
Would very much appreciate your help.
Thank you very much
Mike
*** Sent via Developersdex http://www.codecomments.com ***We may need more security information on this one but looks like this is a
permissions problem.
Supposing that you are using Windows authentication, when you execute the
package you most likely have access to both servers. But when you schedule
it, the user running the job maybe does not have access to the other server.
Hope this helps,
Benjamin Nevarez
Database Administrator
"mike" wrote:
> Hi,
> I was able to successfully create a scheduled DTS job transferring
> objects between two databases that Are On The Same Server.
> But was unable to have a scheduled DTS job (transferring objects between
> different servers)run automatically.
> If I execute it manually, it works ok; but not if it's scheduled.
> The agent on all servers is started up.
> Would very much appreciate your help.
> Thank you very much
>
> Mike
> *** Sent via Developersdex http://www.codecomments.com ***
>|||Purrfect! It worked!
Thank you very much
Mike
*** Sent via Developersdex http://www.codecomments.com ***
I was able to successfully create a scheduled DTS job transferring
objects between two databases that Are On The Same Server.
But was unable to have a scheduled DTS job (transferring objects between
different servers)run automatically.
If I execute it manually, it works ok; but not if it's scheduled.
The agent on all servers is started up.
Would very much appreciate your help.
Thank you very much
Mike
*** Sent via Developersdex http://www.codecomments.com ***We may need more security information on this one but looks like this is a
permissions problem.
Supposing that you are using Windows authentication, when you execute the
package you most likely have access to both servers. But when you schedule
it, the user running the job maybe does not have access to the other server.
Hope this helps,
Benjamin Nevarez
Database Administrator
"mike" wrote:
> Hi,
> I was able to successfully create a scheduled DTS job transferring
> objects between two databases that Are On The Same Server.
> But was unable to have a scheduled DTS job (transferring objects between
> different servers)run automatically.
> If I execute it manually, it works ok; but not if it's scheduled.
> The agent on all servers is started up.
> Would very much appreciate your help.
> Thank you very much
>
> Mike
> *** Sent via Developersdex http://www.codecomments.com ***
>|||Purrfect! It worked!
Thank you very much
Mike
*** Sent via Developersdex http://www.codecomments.com ***
Scheduled DTS Does Not Work Across Servers
Hi,
I was able to successfully create a scheduled DTS job transferring
objects between two databases that Are On The Same Server.
But was unable to have a scheduled DTS job (transferring objects between
different servers)run automatically.
If I execute it manually, it works ok; but not if it's scheduled.
The agent on all servers is started up.
Would very much appreciate your help.
Thank you very much
Mike
*** Sent via Developersdex http://www.developersdex.com ***We may need more security information on this one but looks like this is a
permissions problem.
Supposing that you are using Windows authentication, when you execute the
package you most likely have access to both servers. But when you schedule
it, the user running the job maybe does not have access to the other server.
Hope this helps,
Benjamin Nevarez
Database Administrator
"mike" wrote:
> Hi,
> I was able to successfully create a scheduled DTS job transferring
> objects between two databases that Are On The Same Server.
> But was unable to have a scheduled DTS job (transferring objects between
> different servers)run automatically.
> If I execute it manually, it works ok; but not if it's scheduled.
> The agent on all servers is started up.
> Would very much appreciate your help.
> Thank you very much
>
> Mike
> *** Sent via Developersdex http://www.developersdex.com ***
>|||Purrfect! It worked!
Thank you very much
Mike
*** Sent via Developersdex http://www.developersdex.com ***
I was able to successfully create a scheduled DTS job transferring
objects between two databases that Are On The Same Server.
But was unable to have a scheduled DTS job (transferring objects between
different servers)run automatically.
If I execute it manually, it works ok; but not if it's scheduled.
The agent on all servers is started up.
Would very much appreciate your help.
Thank you very much
Mike
*** Sent via Developersdex http://www.developersdex.com ***We may need more security information on this one but looks like this is a
permissions problem.
Supposing that you are using Windows authentication, when you execute the
package you most likely have access to both servers. But when you schedule
it, the user running the job maybe does not have access to the other server.
Hope this helps,
Benjamin Nevarez
Database Administrator
"mike" wrote:
> Hi,
> I was able to successfully create a scheduled DTS job transferring
> objects between two databases that Are On The Same Server.
> But was unable to have a scheduled DTS job (transferring objects between
> different servers)run automatically.
> If I execute it manually, it works ok; but not if it's scheduled.
> The agent on all servers is started up.
> Would very much appreciate your help.
> Thank you very much
>
> Mike
> *** Sent via Developersdex http://www.developersdex.com ***
>|||Purrfect! It worked!
Thank you very much
Mike
*** Sent via Developersdex http://www.developersdex.com ***
Friday, March 9, 2012
Scheduled backup silently fails
I am running a SQL Server 2000 installation with several databases. Each
database and log is backed-up using a maintenance plan.
database and log is backed-up using a maintenance plan.
The scheduled maintance plan for the latest database does not run, but
displays no error. There is no entry in the job history. The same thing
happens when I try to run the individual jobs from Enterprise Manager.
I've checked the database recovery model (full), the location of the backup
files (same as the other databases), and just about everything else I can
think of. The scheduled maintenance plan for every other database runs as
it should.
What am I missing?
Thanks
Iain"Iain Hosking" <iimfedupwithspamhosking@.thespamfreeweather.com.au> wrote in
message news:401e53e7$0$79389$7b628d20@.titanium.syd.ntt.ne t.au...
> I am running a SQL Server 2000 installation with several databases. Each
> database and log is backed-up using a maintenance plan.
> The scheduled maintance plan for the latest database does not run, but
> displays no error. There is no entry in the job history. The same thing
> happens when I try to run the individual jobs from Enterprise Manager.
> I've checked the database recovery model (full), the location of the
backup
> files (same as the other databases), and just about everything else I can
> think of. The scheduled maintenance plan for every other database runs as
> it should.
> What am I missing?
Is SQL Agent service running? This service is required
to be running for the above automation.
Additionally, I would disassociate the backups and the
maintenance plans by creating each as separate tasks,
the former being far more critical than the latter.
Pete Brown
Winluck P/L
Falls Creek
Australia|||Hi,
A wild guess is you're looking at the job history via EM. You're
right clicking on the sql agent jobs and select view history. The
view history says job successful. But when you check the view step
details, you see that some of the job steps failed?? If that is the
case you'll need to query the system tables. A script to do that is
located at http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=30388.|||Mountain Man
Thanks for the answer. Yes, SQL Server Agent is running. Every other
database is being backed up according to the plan.
I was hoping there was just one thing I missed with this database, but as I
say, I've checked the obvious (data recovery model, backup file names).
Do you advocate setting up a manual backup plan rather than using
Microsoft's Maintenance Plan wizard? The attraction of the wizard was that
it timestamped the files, and was basically quick to set up (there are 13
databases so far). I attended a 1-week DBA course and this seemed to be the
approved procedure. It's worked fine up to this point.
Cheers
Iain
"mountain man" <hobbit@.southern_seaweed.com.op> wrote in message
news:iGyTb.40024$Wa.4755@.news-server.bigpond.net.au...
> "Iain Hosking" <iimfedupwithspamhosking@.thespamfreeweather.com.au> wrote
in
> message news:401e53e7$0$79389$7b628d20@.titanium.syd.ntt.ne t.au...
> > I am running a SQL Server 2000 installation with several databases. Each
> > database and log is backed-up using a maintenance plan.
> > The scheduled maintance plan for the latest database does not run, but
> > displays no error. There is no entry in the job history. The same thing
> > happens when I try to run the individual jobs from Enterprise Manager.
> > I've checked the database recovery model (full), the location of the
> backup
> > files (same as the other databases), and just about everything else I
can
> > think of. The scheduled maintenance plan for every other database runs
as
> > it should.
> > What am I missing?
>
> Is SQL Agent service running? This service is required
> to be running for the above automation.
> Additionally, I would disassociate the backups and the
> maintenance plans by creating each as separate tasks,
> the former being far more critical than the latter.
>
>
> Pete Brown
> Winluck P/L
> Falls Creek
> Australia
>
>
>|||Iain Hosking (iimfedupwithspamhosking@.thespamfreeweather.com.au ) writes:
> I am running a SQL Server 2000 installation with several databases. Each
> database and log is backed-up using a maintenance plan.
> The scheduled maintance plan for the latest database does not run, but
> displays no error. There is no entry in the job history. The same thing
> happens when I try to run the individual jobs from Enterprise Manager.
> I've checked the database recovery model (full), the location of the
> backup files (same as the other databases), and just about everything
> else I can think of. The scheduled maintenance plan for every other
> database runs as it should.
Well, these may be really stupid suggestions, but check that the job
is enabled, and that the schedule is enabled.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Iain Hosking" <iimfedupwithspamhosking@.thespamfreeweather.com.au> wrote in
message news:401ed1e6$0$79387$7b628d20@.titanium.syd.ntt.ne t.au...
...[trim]...
> Do you advocate setting up a manual backup plan rather than using
> Microsoft's Maintenance Plan wizard?
Generally yes, for a number of reasons:
1) Although the maintenance tasks can be very important
the backup is super-critical and should have IMO a dedicated
separate task in the production task queue.
2) Generally the schedules for maintenance and backups
are different cycles and thus they need to separately scheduled.
OTOH the maintenance wizard is fine for most production
sites in their early phases of operations -- prior to any
full automation. (ie: large task queue)
Good luck,
Pete Brown
Falls Creek
Australia
> "mountain man" <hobbit@.southern_seaweed.com.op> wrote in message
> news:iGyTb.40024$Wa.4755@.news-server.bigpond.net.au...
> > "Iain Hosking" <iimfedupwithspamhosking@.thespamfreeweather.com.au> wrote
> in
> > message news:401e53e7$0$79389$7b628d20@.titanium.syd.ntt.ne t.au...
> > > I am running a SQL Server 2000 installation with several databases.
Each
> > > database and log is backed-up using a maintenance plan.
> > > > The scheduled maintance plan for the latest database does not run, but
> > > displays no error. There is no entry in the job history. The same
thing
> > > happens when I try to run the individual jobs from Enterprise Manager.
> > > > I've checked the database recovery model (full), the location of the
> > backup
> > > files (same as the other databases), and just about everything else I
> can
> > > think of. The scheduled maintenance plan for every other database
runs
> as
> > > it should.
> > > > What am I missing?
> > Is SQL Agent service running? This service is required
> > to be running for the above automation.
> > Additionally, I would disassociate the backups and the
> > maintenance plans by creating each as separate tasks,
> > the former being far more critical than the latter.
> > Pete Brown
> > Winluck P/L
> > Falls Creek
> > Australia|||What user is SQL Server running under?
What user is SQL Agent running under?
Who is the owner of the database?
Are all of the above the same?
If not, check the other databases for this information and change this
one to match.
If so, does the user have permissions at the location of the backup
files?
On Tue, 3 Feb 2004 00:42:59 +1100, "Iain Hosking"
<iimfedupwithspamhosking@.thespamfreeweather.com.au> wrote:
>I am running a SQL Server 2000 installation with several databases. Each
>database and log is backed-up using a maintenance plan.
>The scheduled maintance plan for the latest database does not run, but
>displays no error. There is no entry in the job history. The same thing
>happens when I try to run the individual jobs from Enterprise Manager.
>I've checked the database recovery model (full), the location of the backup
>files (same as the other databases), and just about everything else I can
>think of. The scheduled maintenance plan for every other database runs as
>it should.
>What am I missing?
>Thanks
>Iain
Wednesday, March 7, 2012
Schedule mail of DB sizes
Wonder if anyone can help me out here.
I'm trying to set up a job to run overnight that mails me the size of all the databases on SQL Server.
The way I'm getting the size of the db's is by running the following in SQL Query Analyzer...
EXEC sp_MSforeachdb @.command1="print '?' select cast(name as varchar(32)), round(size * 8 / 1.024,3) from ?..sysfiles"
Can anyone suggest a way that I can export the results of this to a text file that can then be mailed to me as part of a scheduled job.
Or can anyone suggest a better/easier way of doing this??
Thanks in advance. :)exec master..xp_sendmail @.recipients = 'recipients '
,@.message = 'message'
,@.query = select cast(name as varchar(128)) "LogicalFileName", (size * 8 / 1024) "Size in MB" from master..sysaltfiles'|||... I'm afraid I've not explained myself properly :(
SQL Mail is not set up on this server, so I need a file creating which I can then ftp to a location from which it can be mailed.
And I'm afraid it's not as simple as setting up SQL Mail, it has to be sent from a different box.
Any suggestions?
Thanks|||Look into isql or osql to execute the query and store results in a file|||Create an DTS package, two data sources, one as your SQL Serv db, one as "Output Text", then create a "transfer" task between the SQL serv and the text file. Edit the transfer task and set the source as "execute sql" and than select the "exec ... " string you are using now.
Then schedule your DTS package as you like it...|||no need for dts here .. simple osql or isql will do the task.|||Thanks Enigma.
Just looking into osql now and that seems to do the trick.
I'm trying to set up a job to run overnight that mails me the size of all the databases on SQL Server.
The way I'm getting the size of the db's is by running the following in SQL Query Analyzer...
EXEC sp_MSforeachdb @.command1="print '?' select cast(name as varchar(32)), round(size * 8 / 1.024,3) from ?..sysfiles"
Can anyone suggest a way that I can export the results of this to a text file that can then be mailed to me as part of a scheduled job.
Or can anyone suggest a better/easier way of doing this??
Thanks in advance. :)exec master..xp_sendmail @.recipients = 'recipients '
,@.message = 'message'
,@.query = select cast(name as varchar(128)) "LogicalFileName", (size * 8 / 1024) "Size in MB" from master..sysaltfiles'|||... I'm afraid I've not explained myself properly :(
SQL Mail is not set up on this server, so I need a file creating which I can then ftp to a location from which it can be mailed.
And I'm afraid it's not as simple as setting up SQL Mail, it has to be sent from a different box.
Any suggestions?
Thanks|||Look into isql or osql to execute the query and store results in a file|||Create an DTS package, two data sources, one as your SQL Serv db, one as "Output Text", then create a "transfer" task between the SQL serv and the text file. Edit the transfer task and set the source as "execute sql" and than select the "exec ... " string you are using now.
Then schedule your DTS package as you like it...|||no need for dts here .. simple osql or isql will do the task.|||Thanks Enigma.
Just looking into osql now and that seems to do the trick.
Saturday, February 25, 2012
schedule database growth for midnight.
Hi Everybody
My MSSQL 2000 8.00.818 database stores large amounts of binary data
and can be gigabytes in size. When the databases automatically grow,
they can take a while. This causes web pages which are waiting for
results from queries, to fail. Growth has been set to 1% to decrease
grow time. But I want to know if it is possible to set a schedule to
run at night, to make sure there is at least 10% free space. Can this
be done?
Why don't you just increase the database size by a LOT once, anticipating
long-term growth, instead of trying to predict reactionary growth?
"geist3" <jcockrell@.gmail.com> wrote in message
news:1184679861.142847.322970@.z28g2000prd.googlegr oups.com...
> Hi Everybody
> My MSSQL 2000 8.00.818 database stores large amounts of binary data
> and can be gigabytes in size. When the databases automatically grow,
> they can take a while. This causes web pages which are waiting for
> results from queries, to fail. Growth has been set to 1% to decrease
> grow time. But I want to know if it is possible to set a schedule to
> run at night, to make sure there is at least 10% free space. Can this
> be done?
>
|||I agree 100% with Aaron in that you should make it as large as you think it
needs to be for quite some time. In any case you can schedule growth by
using a standard SQL Agent job and ALTER DATABASE command. And don't set the
auto growth to a %. Use a fixed amount in MB or GB that you know can grow in
well under the timeout period. A percentage is useless as the size gets
larger each time the db grows and you no longer have control in how long
each one takes.
Andrew J. Kelly SQL MVP
"geist3" <jcockrell@.gmail.com> wrote in message
news:1184679861.142847.322970@.z28g2000prd.googlegr oups.com...
> Hi Everybody
> My MSSQL 2000 8.00.818 database stores large amounts of binary data
> and can be gigabytes in size. When the databases automatically grow,
> they can take a while. This causes web pages which are waiting for
> results from queries, to fail. Growth has been set to 1% to decrease
> grow time. But I want to know if it is possible to set a schedule to
> run at night, to make sure there is at least 10% free space. Can this
> be done?
>
|||Put me down for third on the "be proactive" side. Just make the darn
database HUGE once. This will also avoid disk file fragmentation and lead
to better performance in the long run, as well as providing free space so
that index maintenance can lay data back down in sequential order - again
improving performance.
TheSQLGuru
President
Indicium Resources, Inc.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ORghqCIyHHA.4276@.TK2MSFTNGP05.phx.gbl...
>I agree 100% with Aaron in that you should make it as large as you think it
>needs to be for quite some time. In any case you can schedule growth by
>using a standard SQL Agent job and ALTER DATABASE command. And don't set
>the auto growth to a %. Use a fixed amount in MB or GB that you know can
>grow in well under the timeout period. A percentage is useless as the size
>gets larger each time the db grows and you no longer have control in how
>long each one takes.
> --
> Andrew J. Kelly SQL MVP
> "geist3" <jcockrell@.gmail.com> wrote in message
> news:1184679861.142847.322970@.z28g2000prd.googlegr oups.com...
>
My MSSQL 2000 8.00.818 database stores large amounts of binary data
and can be gigabytes in size. When the databases automatically grow,
they can take a while. This causes web pages which are waiting for
results from queries, to fail. Growth has been set to 1% to decrease
grow time. But I want to know if it is possible to set a schedule to
run at night, to make sure there is at least 10% free space. Can this
be done?
Why don't you just increase the database size by a LOT once, anticipating
long-term growth, instead of trying to predict reactionary growth?
"geist3" <jcockrell@.gmail.com> wrote in message
news:1184679861.142847.322970@.z28g2000prd.googlegr oups.com...
> Hi Everybody
> My MSSQL 2000 8.00.818 database stores large amounts of binary data
> and can be gigabytes in size. When the databases automatically grow,
> they can take a while. This causes web pages which are waiting for
> results from queries, to fail. Growth has been set to 1% to decrease
> grow time. But I want to know if it is possible to set a schedule to
> run at night, to make sure there is at least 10% free space. Can this
> be done?
>
|||I agree 100% with Aaron in that you should make it as large as you think it
needs to be for quite some time. In any case you can schedule growth by
using a standard SQL Agent job and ALTER DATABASE command. And don't set the
auto growth to a %. Use a fixed amount in MB or GB that you know can grow in
well under the timeout period. A percentage is useless as the size gets
larger each time the db grows and you no longer have control in how long
each one takes.
Andrew J. Kelly SQL MVP
"geist3" <jcockrell@.gmail.com> wrote in message
news:1184679861.142847.322970@.z28g2000prd.googlegr oups.com...
> Hi Everybody
> My MSSQL 2000 8.00.818 database stores large amounts of binary data
> and can be gigabytes in size. When the databases automatically grow,
> they can take a while. This causes web pages which are waiting for
> results from queries, to fail. Growth has been set to 1% to decrease
> grow time. But I want to know if it is possible to set a schedule to
> run at night, to make sure there is at least 10% free space. Can this
> be done?
>
|||Put me down for third on the "be proactive" side. Just make the darn
database HUGE once. This will also avoid disk file fragmentation and lead
to better performance in the long run, as well as providing free space so
that index maintenance can lay data back down in sequential order - again
improving performance.
TheSQLGuru
President
Indicium Resources, Inc.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ORghqCIyHHA.4276@.TK2MSFTNGP05.phx.gbl...
>I agree 100% with Aaron in that you should make it as large as you think it
>needs to be for quite some time. In any case you can schedule growth by
>using a standard SQL Agent job and ALTER DATABASE command. And don't set
>the auto growth to a %. Use a fixed amount in MB or GB that you know can
>grow in well under the timeout period. A percentage is useless as the size
>gets larger each time the db grows and you no longer have control in how
>long each one takes.
> --
> Andrew J. Kelly SQL MVP
> "geist3" <jcockrell@.gmail.com> wrote in message
> news:1184679861.142847.322970@.z28g2000prd.googlegr oups.com...
>
schedule database growth for midnight.
Hi Everybody
My MSSQL 2000 8.00.818 database stores large amounts of binary data
and can be gigabytes in size. When the databases automatically grow,
they can take a while. This causes web pages which are waiting for
results from queries, to fail. Growth has been set to 1% to decrease
grow time. But I want to know if it is possible to set a schedule to
run at night, to make sure there is at least 10% free space. Can this
be done?Why don't you just increase the database size by a LOT once, anticipating
long-term growth, instead of trying to predict reactionary growth?
"geist3" <jcockrell@.gmail.com> wrote in message
news:1184679861.142847.322970@.z28g2000prd.googlegroups.com...
> Hi Everybody
> My MSSQL 2000 8.00.818 database stores large amounts of binary data
> and can be gigabytes in size. When the databases automatically grow,
> they can take a while. This causes web pages which are waiting for
> results from queries, to fail. Growth has been set to 1% to decrease
> grow time. But I want to know if it is possible to set a schedule to
> run at night, to make sure there is at least 10% free space. Can this
> be done?
>|||I agree 100% with Aaron in that you should make it as large as you think it
needs to be for quite some time. In any case you can schedule growth by
using a standard SQL Agent job and ALTER DATABASE command. And don't set the
auto growth to a %. Use a fixed amount in MB or GB that you know can grow in
well under the timeout period. A percentage is useless as the size gets
larger each time the db grows and you no longer have control in how long
each one takes.
Andrew J. Kelly SQL MVP
"geist3" <jcockrell@.gmail.com> wrote in message
news:1184679861.142847.322970@.z28g2000prd.googlegroups.com...
> Hi Everybody
> My MSSQL 2000 8.00.818 database stores large amounts of binary data
> and can be gigabytes in size. When the databases automatically grow,
> they can take a while. This causes web pages which are waiting for
> results from queries, to fail. Growth has been set to 1% to decrease
> grow time. But I want to know if it is possible to set a schedule to
> run at night, to make sure there is at least 10% free space. Can this
> be done?
>|||Put me down for third on the "be proactive" side. Just make the darn
database HUGE once. This will also avoid disk file fragmentation and lead
to better performance in the long run, as well as providing free space so
that index maintenance can lay data back down in sequential order - again
improving performance.
TheSQLGuru
President
Indicium Resources, Inc.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ORghqCIyHHA.4276@.TK2MSFTNGP05.phx.gbl...
>I agree 100% with Aaron in that you should make it as large as you think it
>needs to be for quite some time. In any case you can schedule growth by
>using a standard SQL Agent job and ALTER DATABASE command. And don't set
>the auto growth to a %. Use a fixed amount in MB or GB that you know can
>grow in well under the timeout period. A percentage is useless as the size
>gets larger each time the db grows and you no longer have control in how
>long each one takes.
> --
> Andrew J. Kelly SQL MVP
> "geist3" <jcockrell@.gmail.com> wrote in message
> news:1184679861.142847.322970@.z28g2000prd.googlegroups.com...
>
My MSSQL 2000 8.00.818 database stores large amounts of binary data
and can be gigabytes in size. When the databases automatically grow,
they can take a while. This causes web pages which are waiting for
results from queries, to fail. Growth has been set to 1% to decrease
grow time. But I want to know if it is possible to set a schedule to
run at night, to make sure there is at least 10% free space. Can this
be done?Why don't you just increase the database size by a LOT once, anticipating
long-term growth, instead of trying to predict reactionary growth?
"geist3" <jcockrell@.gmail.com> wrote in message
news:1184679861.142847.322970@.z28g2000prd.googlegroups.com...
> Hi Everybody
> My MSSQL 2000 8.00.818 database stores large amounts of binary data
> and can be gigabytes in size. When the databases automatically grow,
> they can take a while. This causes web pages which are waiting for
> results from queries, to fail. Growth has been set to 1% to decrease
> grow time. But I want to know if it is possible to set a schedule to
> run at night, to make sure there is at least 10% free space. Can this
> be done?
>|||I agree 100% with Aaron in that you should make it as large as you think it
needs to be for quite some time. In any case you can schedule growth by
using a standard SQL Agent job and ALTER DATABASE command. And don't set the
auto growth to a %. Use a fixed amount in MB or GB that you know can grow in
well under the timeout period. A percentage is useless as the size gets
larger each time the db grows and you no longer have control in how long
each one takes.
Andrew J. Kelly SQL MVP
"geist3" <jcockrell@.gmail.com> wrote in message
news:1184679861.142847.322970@.z28g2000prd.googlegroups.com...
> Hi Everybody
> My MSSQL 2000 8.00.818 database stores large amounts of binary data
> and can be gigabytes in size. When the databases automatically grow,
> they can take a while. This causes web pages which are waiting for
> results from queries, to fail. Growth has been set to 1% to decrease
> grow time. But I want to know if it is possible to set a schedule to
> run at night, to make sure there is at least 10% free space. Can this
> be done?
>|||Put me down for third on the "be proactive" side. Just make the darn
database HUGE once. This will also avoid disk file fragmentation and lead
to better performance in the long run, as well as providing free space so
that index maintenance can lay data back down in sequential order - again
improving performance.
TheSQLGuru
President
Indicium Resources, Inc.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ORghqCIyHHA.4276@.TK2MSFTNGP05.phx.gbl...
>I agree 100% with Aaron in that you should make it as large as you think it
>needs to be for quite some time. In any case you can schedule growth by
>using a standard SQL Agent job and ALTER DATABASE command. And don't set
>the auto growth to a %. Use a fixed amount in MB or GB that you know can
>grow in well under the timeout period. A percentage is useless as the size
>gets larger each time the db grows and you no longer have control in how
>long each one takes.
> --
> Andrew J. Kelly SQL MVP
> "geist3" <jcockrell@.gmail.com> wrote in message
> news:1184679861.142847.322970@.z28g2000prd.googlegroups.com...
>
Subscribe to:
Posts (Atom)