|
Home > Archive > microsoft.public.sqlserver.server > August 2002 > Self-Administering? Maintenance plan?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Self-Administering? Maintenance plan?
|
|
| Luther Miller 2002-07-15, 11:25 am |
| I client of mine recently ran into a problem with another application where
the SQL Server transaction logs grew so much that the hard drive became
full. I helped resolve the issue, but the client was not aware of any
regular maintenance that must be performed. This is not my area of
expertise, but I am doing some research on the subject. I couldn't find any
sections on the BOL regarding normal (daily) maintenace tasks in order avoid
such problems. Where can I find more information on this?
What would be the appropriate steps to take in order to have an
application's SQL Server database maintain itself, requiring little or no
administration on a regular basis? Is recommended, for example, to schedule
a job that does a CHECKPOINT, truncates the transaction logs, and shirnks
the database on a daily basis after backups have been made, for a relatively
small and low-transaction volume application?
In considering this, I also began wondering how MSDE handles this issue. Are
the standard SQL Server tools available through MSDE as well, or is MSDE
self-maintaining?
What is the best book on this topic?
| |
| Ron Talmage 2002-07-15, 12:25 pm |
| Luther,
See the BOL on the Simple recovery model. This model will automatically
truncate the transaction log.
The most important normal maintenance task is backup, and you can create a
SQL Agent job to do this using the Database Maintenance Plan Wizard. You can
also set the database properties in Enterprise Manager for auto-shrink and
auto-grow.
MSDE is just a SQL Server instance with some restrictions on number of users
and file sizes; you can connect to it using Enterprise Manager and apply all
the same database parameters for recovery model, backup, and file growth
that you would with a normal SQL Server instance.
Hope this helps,
Ron
--
Ron Talmage
SQL Server MVP
Luther Miller <luther@nowhere.invalid> wrote in message
news:u9aztMCLCHA.2400@tkmsftngp08...
> I client of mine recently ran into a problem with another application
where
> the SQL Server transaction logs grew so much that the hard drive became
> full. I helped resolve the issue, but the client was not aware of any
> regular maintenance that must be performed. This is not my area of
> expertise, but I am doing some research on the subject. I couldn't find
any
> sections on the BOL regarding normal (daily) maintenace tasks in order
avoid
> such problems. Where can I find more information on this?
>
> What would be the appropriate steps to take in order to have an
> application's SQL Server database maintain itself, requiring little or no
> administration on a regular basis? Is recommended, for example, to
schedule
> a job that does a CHECKPOINT, truncates the transaction logs, and shirnks
> the database on a daily basis after backups have been made, for a
relatively
> small and low-transaction volume application?
>
> In considering this, I also began wondering how MSDE handles this issue.
Are
> the standard SQL Server tools available through MSDE as well, or is MSDE
> self-maintaining?
>
> What is the best book on this topic?
>
>
| |
| Tibor Karaszi 2002-07-16, 3:25 am |
| My viewpoint:
Make sure you do regular transaction log backup. this is IMO mandatory for all but some rare
cases...
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ublic.sqlserver
"Luther Miller" <luther@nowhere.invalid> wrote in message news:u9aztMCLCHA.2400@tkmsftngp08...
> I client of mine recently ran into a problem with another application where
> the SQL Server transaction logs grew so much that the hard drive became
> full. I helped resolve the issue, but the client was not aware of any
> regular maintenance that must be performed. This is not my area of
> expertise, but I am doing some research on the subject. I couldn't find any
> sections on the BOL regarding normal (daily) maintenace tasks in order avoid
> such problems. Where can I find more information on this?
>
> What would be the appropriate steps to take in order to have an
> application's SQL Server database maintain itself, requiring little or no
> administration on a regular basis? Is recommended, for example, to schedule
> a job that does a CHECKPOINT, truncates the transaction logs, and shirnks
> the database on a daily basis after backups have been made, for a relatively
> small and low-transaction volume application?
>
> In considering this, I also began wondering how MSDE handles this issue. Are
> the standard SQL Server tools available through MSDE as well, or is MSDE
> self-maintaining?
>
> What is the best book on this topic?
>
>
| |
| John Hind 2002-07-16, 11:26 am |
| "you can connect to it using Enterprise Manager "
..... as long as you have a licenced copy of EM on that pc/network.
I'm new to this NG and all I see is endless threads regarding licence and
installation issues. When will Microsoft tidy msde up ?
I'm a devout MS follower, and I'm about to install my application using
msde, rather than Access, but the thought of installation and maintenance
scares me to death ! There are numerous FAQs and MS references, but it's
still a hard work.
Has anyone got the real "bottom line" on msde ?
Has anyone got a set of useful msde scripts ?
Come on MS, shake ya' brain...
John
"Ron Talmage" <rtalmage@prospice.com> wrote in message
news:emrmFsCLCHA.2192@tkmsftngp08...
> Luther,
>
> See the BOL on the Simple recovery model. This model will automatically
> truncate the transaction log.
> The most important normal maintenance task is backup, and you can create a
> SQL Agent job to do this using the Database Maintenance Plan Wizard. You
can
> also set the database properties in Enterprise Manager for auto-shrink and
> auto-grow.
> MSDE is just a SQL Server instance with some restrictions on number of
users
> and file sizes; you can connect to it using Enterprise Manager and apply
all
> the same database parameters for recovery model, backup, and file growth
> that you would with a normal SQL Server instance.
>
> Hope this helps,
> Ron
> --
> Ron Talmage
> SQL Server MVP
>
>
>
> Luther Miller <luther@nowhere.invalid> wrote in message
> news:u9aztMCLCHA.2400@tkmsftngp08...
> > I client of mine recently ran into a problem with another application
> where
> > the SQL Server transaction logs grew so much that the hard drive became
> > full. I helped resolve the issue, but the client was not aware of any
> > regular maintenance that must be performed. This is not my area of
> > expertise, but I am doing some research on the subject. I couldn't find
> any
> > sections on the BOL regarding normal (daily) maintenace tasks in order
> avoid
> > such problems. Where can I find more information on this?
> >
> > What would be the appropriate steps to take in order to have an
> > application's SQL Server database maintain itself, requiring little or
no
> > administration on a regular basis? Is recommended, for example, to
> schedule
> > a job that does a CHECKPOINT, truncates the transaction logs, and
shirnks
> > the database on a daily basis after backups have been made, for a
> relatively
> > small and low-transaction volume application?
> >
> > In considering this, I also began wondering how MSDE handles this issue.
> Are
> > the standard SQL Server tools available through MSDE as well, or is MSDE
> > self-maintaining?
> >
> > What is the best book on this topic?
> >
> >
>
>
| |
| Luther Miller 2002-07-17, 10:25 am |
| I'm sure you can connect to MSDE using Enterprise Manager, but does MSDE
come with Enterprise Manager? How should one schedule backups etc using
MSDE... manually with scripts, I assume? What tools does MSDE come with?
"Ron Talmage" <rtalmage@prospice.com> wrote in message
news:emrmFsCLCHA.2192@tkmsftngp08...
> Luther,
>
> See the BOL on the Simple recovery model. This model will automatically
> truncate the transaction log.
> The most important normal maintenance task is backup, and you can create a
> SQL Agent job to do this using the Database Maintenance Plan Wizard. You
can
> also set the database properties in Enterprise Manager for auto-shrink and
> auto-grow.
> MSDE is just a SQL Server instance with some restrictions on number of
users
> and file sizes; you can connect to it using Enterprise Manager and apply
all
> the same database parameters for recovery model, backup, and file growth
> that you would with a normal SQL Server instance.
>
> Hope this helps,
> Ron
> --
> Ron Talmage
> SQL Server MVP
>
>
>
> Luther Miller <luther@nowhere.invalid> wrote in message
> news:u9aztMCLCHA.2400@tkmsftngp08...
> > I client of mine recently ran into a problem with another application
> where
> > the SQL Server transaction logs grew so much that the hard drive became
> > full. I helped resolve the issue, but the client was not aware of any
> > regular maintenance that must be performed. This is not my area of
> > expertise, but I am doing some research on the subject. I couldn't find
> any
> > sections on the BOL regarding normal (daily) maintenace tasks in order
> avoid
> > such problems. Where can I find more information on this?
> >
> > What would be the appropriate steps to take in order to have an
> > application's SQL Server database maintain itself, requiring little or
no
> > administration on a regular basis? Is recommended, for example, to
> schedule
> > a job that does a CHECKPOINT, truncates the transaction logs, and
shirnks
> > the database on a daily basis after backups have been made, for a
> relatively
> > small and low-transaction volume application?
> >
> > In considering this, I also began wondering how MSDE handles this issue.
> Are
> > the standard SQL Server tools available through MSDE as well, or is MSDE
> > self-maintaining?
> >
> > What is the best book on this topic?
> >
> >
>
>
| |
| Luther Miller 2002-07-17, 10:25 am |
| How often would you consider "regular"? Also, do you mean using the BACKUP
statement, or do you mean making a backup of the physical transaction log
file? (I assume the former).
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@cornerstone.se>
wrote in message news:OvmNIKKLCHA.436@tkmsftngp11...
> My viewpoint:
>
> Make sure you do regular transaction log backup. this is IMO mandatory for
all but some rare
> cases...
>
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
>
>
> "Luther Miller" <luther@nowhere.invalid> wrote in message
news:u9aztMCLCHA.2400@tkmsftngp08...
> > I client of mine recently ran into a problem with another application
where
> > the SQL Server transaction logs grew so much that the hard drive became
> > full. I helped resolve the issue, but the client was not aware of any
> > regular maintenance that must be performed. This is not my area of
> > expertise, but I am doing some research on the subject. I couldn't find
any
> > sections on the BOL regarding normal (daily) maintenace tasks in order
avoid
> > such problems. Where can I find more information on this?
> >
> > What would be the appropriate steps to take in order to have an
> > application's SQL Server database maintain itself, requiring little or
no
> > administration on a regular basis? Is recommended, for example, to
schedule
> > a job that does a CHECKPOINT, truncates the transaction logs, and
shirnks
> > the database on a daily basis after backups have been made, for a
relatively
> > small and low-transaction volume application?
> >
> > In considering this, I also began wondering how MSDE handles this issue.
Are
> > the standard SQL Server tools available through MSDE as well, or is MSDE
> > self-maintaining?
> >
> > What is the best book on this topic?
> >
> >
>
>
| |
| Tibor Karaszi 2002-07-17, 11:25 am |
| > I'm sure you can connect to MSDE using Enterprise Manager, but does MSDE
> come with Enterprise Manager?
No.
> How should one schedule backups etc using
> MSDE... manually with scripts, I assume?
I believe that the idea is that the app that MSDE ships with should take care of this. Possibly
using DMO to create jobs etc. Or call stored procedures.
> What tools does MSDE come with?
OSQL.EXE. Not really an end-user tool :-).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ublic.sqlserver
"Luther Miller" <luther@nowhere.invalid> wrote in message news:#0krXvaLCHA.2696@tkmsftngp13...
> I'm sure you can connect to MSDE using Enterprise Manager, but does MSDE
> come with Enterprise Manager? How should one schedule backups etc using
> MSDE... manually with scripts, I assume? What tools does MSDE come with?
>
> "Ron Talmage" <rtalmage@prospice.com> wrote in message
> news:emrmFsCLCHA.2192@tkmsftngp08...
> > Luther,
> >
> > See the BOL on the Simple recovery model. This model will automatically
> > truncate the transaction log.
> > The most important normal maintenance task is backup, and you can create a
> > SQL Agent job to do this using the Database Maintenance Plan Wizard. You
> can
> > also set the database properties in Enterprise Manager for auto-shrink and
> > auto-grow.
> > MSDE is just a SQL Server instance with some restrictions on number of
> users
> > and file sizes; you can connect to it using Enterprise Manager and apply
> all
> > the same database parameters for recovery model, backup, and file growth
> > that you would with a normal SQL Server instance.
> >
> > Hope this helps,
> > Ron
> > --
> > Ron Talmage
> > SQL Server MVP
> >
> >
> >
> > Luther Miller <luther@nowhere.invalid> wrote in message
> > news:u9aztMCLCHA.2400@tkmsftngp08...
> > > I client of mine recently ran into a problem with another application
> > where
> > > the SQL Server transaction logs grew so much that the hard drive became
> > > full. I helped resolve the issue, but the client was not aware of any
> > > regular maintenance that must be performed. This is not my area of
> > > expertise, but I am doing some research on the subject. I couldn't find
> > any
> > > sections on the BOL regarding normal (daily) maintenace tasks in order
> > avoid
> > > such problems. Where can I find more information on this?
> > >
> > > What would be the appropriate steps to take in order to have an
> > > application's SQL Server database maintain itself, requiring little or
> no
> > > administration on a regular basis? Is recommended, for example, to
> > schedule
> > > a job that does a CHECKPOINT, truncates the transaction logs, and
> shirnks
> > > the database on a daily basis after backups have been made, for a
> > relatively
> > > small and low-transaction volume application?
> > >
> > > In considering this, I also began wondering how MSDE handles this issue.
> > Are
> > > the standard SQL Server tools available through MSDE as well, or is MSDE
> > > self-maintaining?
> > >
> > > What is the best book on this topic?
> > >
> > >
> >
> >
>
>
| |
| Tibor Karaszi 2002-07-17, 11:25 am |
| > How often would you consider "regular"?
For a "standard" installation, I tend to opt for db backup once a day and tlog backup each hour.
> Also, do you mean using the BACKUP
> statement, or do you mean making a backup of the physical transaction log
> file? (I assume the former).
Correct. I mean the BACKUP DATABASE and BACKUP LOG commands.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ublic.sqlserver
"Luther Miller" <luther@nowhere.invalid> wrote in message news:#pHv3vaLCHA.1632@tkmsftngp10...
> How often would you consider "regular"? Also, do you mean using the BACKUP
> statement, or do you mean making a backup of the physical transaction log
> file? (I assume the former).
>
> "Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@cornerstone.se>
> wrote in message news:OvmNIKKLCHA.436@tkmsftngp11...
> > My viewpoint:
> >
> > Make sure you do regular transaction log backup. this is IMO mandatory for
> all but some rare
> > cases...
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> http://groups.google.com/groups?oi=...ublic.sqlserver
> >
> >
> > "Luther Miller" <luther@nowhere.invalid> wrote in message
> news:u9aztMCLCHA.2400@tkmsftngp08...
> > > I client of mine recently ran into a problem with another application
> where
> > > the SQL Server transaction logs grew so much that the hard drive became
> > > full. I helped resolve the issue, but the client was not aware of any
> > > regular maintenance that must be performed. This is not my area of
> > > expertise, but I am doing some research on the subject. I couldn't find
> any
> > > sections on the BOL regarding normal (daily) maintenace tasks in order
> avoid
> > > such problems. Where can I find more information on this?
> > >
> > > What would be the appropriate steps to take in order to have an
> > > application's SQL Server database maintain itself, requiring little or
> no
> > > administration on a regular basis? Is recommended, for example, to
> schedule
> > > a job that does a CHECKPOINT, truncates the transaction logs, and
> shirnks
> > > the database on a daily basis after backups have been made, for a
> relatively
> > > small and low-transaction volume application?
> > >
> > > In considering this, I also began wondering how MSDE handles this issue.
> Are
> > > the standard SQL Server tools available through MSDE as well, or is MSDE
> > > self-maintaining?
> > >
> > > What is the best book on this topic?
> > >
> > >
> >
> >
>
>
| |
| Luther Miller 2002-08-14, 7:23 pm |
| Does MSDE come with SQLAgent for running jobs?
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@cornerstone.se> wrote in message news:<ecchj$aLCHA.1608@tkmsftngp09>...
> > I'm sure you can connect to MSDE using Enterprise Manager, but does MSDE
> > come with Enterprise Manager?
> No.
>
> > How should one schedule backups etc using
> > MSDE... manually with scripts, I assume?
> I believe that the idea is that the app that MSDE ships with should take care of this. Possibly
> using DMO to create jobs etc. Or call stored procedures.
>
> > What tools does MSDE come with?
> OSQL.EXE. Not really an end-user tool :-).
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=...ublic.sqlserver
>
>
> "Luther Miller" <luther@nowhere.invalid> wrote in message news:#0krXvaLCHA.2696@tkmsftngp13...
> > I'm sure you can connect to MSDE using Enterprise Manager, but does MSDE
> > come with Enterprise Manager? How should one schedule backups etc using
> > MSDE... manually with scripts, I assume? What tools does MSDE come with?
> >
> > "Ron Talmage" <rtalmage@prospice.com> wrote in message
> > news:emrmFsCLCHA.2192@tkmsftngp08...
> > > Luther,
> > >
> > > See the BOL on the Simple recovery model. This model will automatically
> > > truncate the transaction log.
> > > The most important normal maintenance task is backup, and you can create a
> > > SQL Agent job to do this using the Database Maintenance Plan Wizard. You
> can
> > > also set the database properties in Enterprise Manager for auto-shrink and
> > > auto-grow.
> > > MSDE is just a SQL Server instance with some restrictions on number of
> users
> > > and file sizes; you can connect to it using Enterprise Manager and apply
> all
> > > the same database parameters for recovery model, backup, and file growth
> > > that you would with a normal SQL Server instance.
> > >
> > > Hope this helps,
> > > Ron
> > > --
> > > Ron Talmage
> > > SQL Server MVP
> > >
> > >
> > >
> > > Luther Miller <luther@nowhere.invalid> wrote in message
> > > news:u9aztMCLCHA.2400@tkmsftngp08...
> > > > I client of mine recently ran into a problem with another application
> where
> > > > the SQL Server transaction logs grew so much that the hard drive became
> > > > full. I helped resolve the issue, but the client was not aware of any
> > > > regular maintenance that must be performed. This is not my area of
> > > > expertise, but I am doing some research on the subject. I couldn't find
> any
> > > > sections on the BOL regarding normal (daily) maintenace tasks in order
> avoid
> > > > such problems. Where can I find more information on this?
> > > >
> > > > What would be the appropriate steps to take in order to have an
> > > > application's SQL Server database maintain itself, requiring little or
> no
> > > > administration on a regular basis? Is recommended, for example, to
> schedule
> > > > a job that does a CHECKPOINT, truncates the transaction logs, and
> shirnks
> > > > the database on a daily basis after backups have been made, for a
> relatively
> > > > small and low-transaction volume application?
> > > >
> > > > In considering this, I also began wondering how MSDE handles this issue.
> Are
> > > > the standard SQL Server tools available through MSDE as well, or is MSDE
> > > > self-maintaining?
> > > >
> > > > What is the best book on this topic?
> > > >
> > > >
> > >
> > >
> >
> >
| |
| Tibor Karaszi 2002-08-15, 4:23 am |
| Yep.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ublic.sqlserver
"Luther Miller" <googlenews@hotmail.com> wrote in message
news:8c1abe63.0208141617.7b44c44e@posting.google.com...
> Does MSDE come with SQLAgent for running jobs?
>
>
>
> "Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@cornerstone.se> wrote in message
news:<ecchj$aLCHA.1608@tkmsftngp09>... [colo
r=darkred]
> > > I'm sure you can connect to MSDE using Enterprise Manager, but does MSDE
> > > come with Enterprise Manager?
> > No.
> >
> > > How should one schedule backups etc using
> > > MSDE... manually with scripts, I assume?
> > I believe that the idea is that the app that MSDE ships with should take care of this.[/color]
Possibly
> > using DMO to create jobs etc. Or call stored procedures.
> >
> > > What tools does MSDE come with?
> > OSQL.EXE. Not really an end-user tool :-).
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at: http://groups.google.com/groups?oi=...ublic.sqlserver
> >
> >
> > "Luther Miller" <luther@nowhere.invalid> wrote in message
news:#0krXvaLCHA.2696@tkmsftngp13... [colo
r=darkred]
> > > I'm sure you can connect to MSDE using Enterprise Manager, but does MSDE
> > > come with Enterprise Manager? How should one schedule backups etc using
> > > MSDE... manually with scripts, I assume? What tools does MSDE come with?
> > >
> > > "Ron Talmage" <rtalmage@prospice.com> wrote in message
> > > news:emrmFsCLCHA.2192@tkmsftngp08...
> > > > Luther,
> > > >
> > > > See the BOL on the Simple recovery model. This model will automatically
> > > > truncate the transaction log.
> > > > The most important normal maintenance task is backup, and you can create a
> > > > SQL Agent job to do this using the Database Maintenance Plan Wizard. You
> > can
> > > > also set the database properties in Enterprise Manager for auto-shrink and
> > > > auto-grow.
> > > > MSDE is just a SQL Server instance with some restrictions on number of
> > users
> > > > and file sizes; you can connect to it using Enterprise Manager and apply
> > all
> > > > the same database parameters for recovery model, backup, and file growth
> > > > that you would with a normal SQL Server instance.
> > > >
> > > > Hope this helps,
> > > > Ron
> > > > --
> > > > Ron Talmage
> > > > SQL Server MVP
> > > >
> > > >
> > > >
> > > > Luther Miller <luther@nowhere.invalid> wrote in message
> > > > news:u9aztMCLCHA.2400@tkmsftngp08...
> > > > > I client of mine recently ran into a problem with another application
> > where
> > > > > the SQL Server transaction logs grew so much that the hard drive became
> > > > > full. I helped resolve the issue, but the client was not aware of any
> > > > > regular maintenance that must be performed. This is not my area of
> > > > > expertise, but I am doing some research on the subject. I couldn't find
> > any
> > > > > sections on the BOL regarding normal (daily) maintenace tasks in order
> > avoid
> > > > > such problems. Where can I find more information on this?
> > > > >
> > > > > What would be the appropriate steps to take in order to have an
> > > > > application's SQL Server database maintain itself, requiring little or
> > no
> > > > > administration on a regular basis? Is recommended, for example, to
> > schedule
> > > > > a job that does a CHECKPOINT, truncates the transaction logs, and
> > shirnks
> > > > > the database on a daily basis after backups have been made, for a
> > relatively
> > > > > small and low-transaction volume application?
> > > > >
> > > > > In considering this, I also began wondering how MSDE handles this issue.
> > Are
> > > > > the standard SQL Server tools available through MSDE as well, or is MSDE
> > > > > self-maintaining?
> > > > >
> > > > > What is the best book on this topic?
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >[/color]
|
|
|
|
|