EM - Current Activity

I manage a data warehouse. Each month I potentially update
millions of records (only if they've changed from the
previous month). You can imagine this update process is
quite time consuming. I've got the tables spread accross 2
to 3 drives via File Groups. The table I update from is
either a data type table variable, temp table or a table
on it's on drive with a clustered index. I've got 2 gigs
of RAM for SQL (Std Edition)but a total of 4, SQL's
priority is boosted, plenty of hard drive space, 4
processors. In EM's current activity, under Physical IO I
seem to have a bottleneck. My wait times for one process
(multiple instances) can easily be in the millions. When I
use Perf Mon to view disk activity (read\write) at least
one usually 2 drives are maxed out. Any thoughts on how I
can improve performance?Liz,
Well one way is ensure the log file is on a separate Raid 1 than anything
else. When you say you have the data spread across 3 drives do you mean 3
individual physical drives or 3 drive array's? 2 or 3 physical drives are
usually not enough to get good performance out of a typical DW application.
The more drives the better. For updates it is very important to have the
log on a separate array than the data. You can also try doing the updates
in smaller batches and see if that speeds things up but you will still get
high disk activity.
--
Andrew J. Kelly
SQL Server MVP
"Liz" <anonymous@.discussions.microsoft.com> wrote in message
news:257201c3af73$a15507b0$a601280a@.phx.gbl...
> I manage a data warehouse. Each month I potentially update
> millions of records (only if they've changed from the
> previous month). You can imagine this update process is
> quite time consuming. I've got the tables spread accross 2
> to 3 drives via File Groups. The table I update from is
> either a data type table variable, temp table or a table
> on it's on drive with a clustered index. I've got 2 gigs
> of RAM for SQL (Std Edition)but a total of 4, SQL's
> priority is boosted, plenty of hard drive space, 4
> processors. In EM's current activity, under Physical IO I
> seem to have a bottleneck. My wait times for one process
> (multiple instances) can easily be in the millions. When I
> use Perf Mon to view disk activity (read\write) at least
> one usually 2 drives are maxed out. Any thoughts on how I
> can improve performance?

Elusive Backup Process

Howdy Folks,

I have recently taken over looking after this machine & the previous guardian is unavailable. I've got to a point where I'm starting to think about looking into MSDB, but am not overly keen to start reviewing this db for obvious reasons... Can anyone please offer advise ?

Thank In Advance
Suze.

Environment: Microsoft SQL Server 2000 - 8.00.760 (sp3) on Win2000
H/W Dell PowerEdge 2550

Issue: Discovered in the sql log that there is a second set of bkps occurring around 7:30am - the bkps we have setup, run at 2am & are also being logged to sql log as completing normally.

The 7:30am bkps appear to be backing up to a remote location using pipes... or at a guess an agent ?? (I don't know know what that looks like cos we don't use agents). We backup db's to disk and then let arcserve pick up those backup files later.

I assume that this has been occurring for some time, sql logs back to July 7 advise so... Unable to locate scheduled job / maintenance plan that runs at this time...

Last night I set up a trace to catch it but that hasn't helped me either, apart from telling me that its an internal process to the server, and that the process actually started at 6:50am.

SQL LOGS Entries between midnight & 8am in attached file:

Trace contents in attached zip file:Someone had the sql agent installed on this server previously. It appears that the NT backup person still had it selected in the backup as well as the backups to disk. We have now removed this & shall wait and see tonight but I guess I now know what a remote agent arcserve backup looks like.

Cheers

Editing scheduled backups and time stamping backup files

Sorry for the previous post. Here are the problems:
1. Using enterprise manager, I chose to backup a
database. I picked my backup device and checked the
schedule option, setup the time and frequency of the
backup. It worked properly. How does one go in and edit
or modify the schedule after it is created? I went to
the backup device but, it doesn't show my schedule name
or settings, in fact, it shows the schedule option
unchecked. Any ideas or is it a matter of deleting the
device and recreating it?
2. I want to have each full backup have the basic file
name but have a time stamp added into the name. It would
look something like: databasename_db_200408141830. 2004
is the year, 08 the month, 14 the day, 1830 the hour. I
have seen it done but do not know how to duplicate it.
By doing this, I can go back to previous backups if a
problem occurs. Once again, any ideas?inline...
HTH
Neil MacMurchy
Senior DBA
Regional Municipality of Niagara
"smantz" <smantz@.smhscs.org> wrote in message
news:1a3301c3fc09$ee42b440$a401280a@.phx.gbl...
> Sorry for the previous post. Here are the problems:
> 1. Using enterprise manager, I chose to backup a
> database. I picked my backup device and checked the
> schedule option, setup the time and frequency of the
> backup. It worked properly. How does one go in and edit
> or modify the schedule after it is created? I went to
> the backup device but, it doesn't show my schedule name
> or settings, in fact, it shows the schedule option
> unchecked. Any ideas or is it a matter of deleting the
> device and recreating it?
the device actually will not show anything until it has been initialized. if
you want to edit the backup job itself you will find it under the
management, SQL agent, jobs. you 'll find it there.
> 2. I want to have each full backup have the basic file
> name but have a time stamp added into the name. It would
> look something like: databasename_db_200408141830. 2004
> is the year, 08 the month, 14 the day, 1830 the hour. I
> have seen it done but do not know how to duplicate it.
> By doing this, I can go back to previous backups if a
> problem occurs. Once again, any ideas?
>
the best way to do this is to create the backup job in database management
section. here you can create a scheduled backup and it will automatically
append the date/time of the backup.|||sorry I should be a bit more specific. you should look for the database
maintenance plan wizard. it can reindex, update statistics, backup database
and tlogs, and send notifications of job completion (I like this last one,
sends me an e-mail over lunch that my tlog backup has completed successfully
or not. it does take a bit more setup for the mail portion).
I'm a huge fan of the Maint.wizards but it shouldn't be the only way you
create these jobs. give em a shot...
Neil MacMurchy
Senior DBA
Regional Municipality of Niagara
/* Check out the books on-line (BOL) for more one the above */
"Neil MacMurchy" <neilmcse@.hotmail.com> wrote in message
news:#3XtUUB$DHA.2348@.TK2MSFTNGP09.phx.gbl...
> inline...
> HTH
> Neil MacMurchy
> Senior DBA
> Regional Municipality of Niagara
> "smantz" <smantz@.smhscs.org> wrote in message
> news:1a3301c3fc09$ee42b440$a401280a@.phx.gbl...
> > Sorry for the previous post. Here are the problems:
> > 1. Using enterprise manager, I chose to backup a
> > database. I picked my backup device and checked the
> > schedule option, setup the time and frequency of the
> > backup. It worked properly. How does one go in and edit
> > or modify the schedule after it is created? I went to
> > the backup device but, it doesn't show my schedule name
> > or settings, in fact, it shows the schedule option
> > unchecked. Any ideas or is it a matter of deleting the
> > device and recreating it?
> the device actually will not show anything until it has been initialized.
if
> you want to edit the backup job itself you will find it under the
> management, SQL agent, jobs. you 'll find it there.
> >
> > 2. I want to have each full backup have the basic file
> > name but have a time stamp added into the name. It would
> > look something like: databasename_db_200408141830. 2004
> > is the year, 08 the month, 14 the day, 1830 the hour. I
> > have seen it done but do not know how to duplicate it.
> > By doing this, I can go back to previous backups if a
> > problem occurs. Once again, any ideas?
> >
> the best way to do this is to create the backup job in database management
> section. here you can create a scheduled backup and it will automatically
> append the date/time of the backup.
>

Editing scheduled backups and time stamping backup files

Sorry for the previous post. Here are the problems:
1. Using enterprise manager, I chose to backup a
database. I picked my backup device and checked the
schedule option, setup the time and frequency of the
backup. It worked properly. How does one go in and edit
or modify the schedule after it is created? I went to
the backup device but, it doesn't show my schedule name
or settings, in fact, it shows the schedule option
unchecked. Any ideas or is it a matter of deleting the
device and recreating it?
2. I want to have each full backup have the basic file
name but have a time stamp added into the name. It would
look something like: databasename_db_200408141830. 2004
is the year, 08 the month, 14 the day, 1830 the hour. I
have seen it done but do not know how to duplicate it.
By doing this, I can go back to previous backups if a
problem occurs. Once again, any ideas?inline...
HTH
Neil MacMurchy
Senior DBA
Regional Municipality of Niagara
"smantz" <smantz@.smhscs.org> wrote in message
news:1a3301c3fc09$ee42b440$a401280a@.phx.gbl...
> Sorry for the previous post. Here are the problems:
> 1. Using enterprise manager, I chose to backup a
> database. I picked my backup device and checked the
> schedule option, setup the time and frequency of the
> backup. It worked properly. How does one go in and edit
> or modify the schedule after it is created? I went to
> the backup device but, it doesn't show my schedule name
> or settings, in fact, it shows the schedule option
> unchecked. Any ideas or is it a matter of deleting the
> device and recreating it?
the device actually will not show anything until it has been initialized. if
you want to edit the backup job itself you will find it under the
management, SQL agent, jobs. you 'll find it there.

> 2. I want to have each full backup have the basic file
> name but have a time stamp added into the name. It would
> look something like: databasename_db_200408141830. 2004
> is the year, 08 the month, 14 the day, 1830 the hour. I
> have seen it done but do not know how to duplicate it.
> By doing this, I can go back to previous backups if a
> problem occurs. Once again, any ideas?
>
the best way to do this is to create the backup job in database management
section. here you can create a scheduled backup and it will automatically
append the date/time of the backup.|||sorry I should be a bit more specific. you should look for the database
maintenance plan wizard. it can reindex, update statistics, backup database
and tlogs, and send notifications of job completion (I like this last one,
sends me an e-mail over lunch that my tlog backup has completed successfully
or not. it does take a bit more setup for the mail portion).
I'm a huge fan of the Maint.wizards but it shouldn't be the only way you
create these jobs. give em a shot...
Neil MacMurchy
Senior DBA
Regional Municipality of Niagara
/* Check out the books on-line (BOL) for more one the above */
"Neil MacMurchy" <neilmcse@.hotmail.com> wrote in message
news:#3XtUUB$DHA.2348@.TK2MSFTNGP09.phx.gbl...
> inline...
> HTH
> Neil MacMurchy
> Senior DBA
> Regional Municipality of Niagara
> "smantz" <smantz@.smhscs.org> wrote in message
> news:1a3301c3fc09$ee42b440$a401280a@.phx.gbl...
> the device actually will not show anything until it has been initialized.
if
> you want to edit the backup job itself you will find it under the
> management, SQL agent, jobs. you 'll find it there.
>
> the best way to do this is to create the backup job in database management
> section. here you can create a scheduled backup and it will automatically
> append the date/time of the backup.
>