SQLMaint config advice requested (example provided)

SQLMaint config advice requested (example provided)

Post by Steve Midgle » Thu, 05 Mar 1998 04:00:00



Hi,

I'm about to start using SQLMaint, instead of manually executing all the
check tables, text, sysindex stuff that I'm scheduling now.

Here's an example of the SQLMaint parameters that I want to run:

SQLMAINT.EXE -D loopdb -CkDB -CkAl -CkTxtAl -CkCat
-UpdSts -RebldIdx 10 -BkUpDB D:\LOOPDEV\BACKUP
-BkUpDB -BkUpMedia DISK -DelBkUps 4 -Rpt
E:\LOOPDEV\BACKUP\CMD\LOG\LoopDBMaint.log

I plan to run this every night. My database is only 90 megs (including
indexes and everything), but it represents the life * of the
company, so it would be nice if I could ensure that it doesn't break, or
that if it does we can get something sensible restored.

When I run this command it takes only about 5 minutes to execute, so I
feel ok about running it every day, in terms of load on the server. Are
there any negative experiences that people can relate concerning the use
of DBMain instead of manually executing ISQL statements?

The -RebldIdx 10 statement I'm not sure is correct. Is 10% a reasonable
size? What does this mean anyway, and how should I calculate a correct
number? I stole this value from the SQL Database maintenance wizard that
I ran on a test database. My tables grow at less than 2% per day in
general, and most don't grow at all.

Insert and update speed is a very low priority for the operation of the
company and this database (select speed is much more important).

I suppose I've got a good situation in that the database activity is
reasonably low, the server isn't over loaded and the tables don't grow
very fast, but since the data are important, I'm trying to do everything
I can to ensure I have good backups and integrity.

Any advice or suggestions would be appreciated.

Best Regards,

Steve Midgley

 
 
 

SQLMaint config advice requested (example provided)

Post by Richard Waymir » Thu, 05 Mar 1998 04:00:00


This looks good.  The sqlmaint program is doing EXACTLY what you would do
manually, so there aren't really issues, other than making sure you're on at
least service pack 3.

--
Richard Waymire, MCT, MCSE+Internet, MCSD, MVP (SQLServer)
Senior Principal Instructor/Consultant, ARIS Corp.
President, Pacific NW SQLServer Users Group (http://www.veryComputer.com/)
Visit us at http://www.veryComputer.com/


>Hi,

>I'm about to start using SQLMaint, instead of manually executing all the
>check tables, text, sysindex stuff that I'm scheduling now.

>Here's an example of the SQLMaint parameters that I want to run:

>SQLMAINT.EXE -D loopdb -CkDB -CkAl -CkTxtAl -CkCat
>-UpdSts -RebldIdx 10 -BkUpDB D:\LOOPDEV\BACKUP
>-BkUpDB -BkUpMedia DISK -DelBkUps 4 -Rpt
>E:\LOOPDEV\BACKUP\CMD\LOG\LoopDBMaint.log

>I plan to run this every night. My database is only 90 megs (including
>indexes and everything), but it represents the life * of the
>company, so it would be nice if I could ensure that it doesn't break, or
>that if it does we can get something sensible restored.

>When I run this command it takes only about 5 minutes to execute, so I
>feel ok about running it every day, in terms of load on the server. Are
>there any negative experiences that people can relate concerning the use
>of DBMain instead of manually executing ISQL statements?

>The -RebldIdx 10 statement I'm not sure is correct. Is 10% a reasonable
>size? What does this mean anyway, and how should I calculate a correct
>number? I stole this value from the SQL Database maintenance wizard that
>I ran on a test database. My tables grow at less than 2% per day in
>general, and most don't grow at all.

>Insert and update speed is a very low priority for the operation of the
>company and this database (select speed is much more important).

>I suppose I've got a good situation in that the database activity is
>reasonably low, the server isn't over loaded and the tables don't grow
>very fast, but since the data are important, I'm trying to do everything
>I can to ensure I have good backups and integrity.

>Any advice or suggestions would be appreciated.

>Best Regards,

>Steve Midgley


 
 
 

SQLMaint config advice requested (example provided)

Post by Tibor Karasz » Fri, 06 Mar 1998 04:00:00


Steve, a few (very minor) notes.
See below.
--

MCSE, MCSD, MCT, SQL Server MVP
Cornerstone Sweden AB



Quote:> Hi,

> I'm about to start using SQLMaint, instead of manually executing all the
> check tables, text, sysindex stuff that I'm scheduling now.

> Here's an example of the SQLMaint parameters that I want to run:

> SQLMAINT.EXE -D loopdb -CkDB -CkAl -CkTxtAl -CkCat
> -UpdSts -RebldIdx 10 -BkUpDB D:\LOOPDEV\BACKUP
> -BkUpDB -BkUpMedia DISK -DelBkUps 4 -Rpt
> E:\LOOPDEV\BACKUP\CMD\LOG\LoopDBMaint.log

> I plan to run this every night. My database is only 90 megs (including
> indexes and everything), but it represents the life * of the
> company, so it would be nice if I could ensure that it doesn't break, or
> that if it does we can get something sensible restored.

> When I run this command it takes only about 5 minutes to execute, so I
> feel ok about running it every day, in terms of load on the server. Are
> there any negative experiences that people can relate concerning the use
> of DBMain instead of manually executing ISQL statements?

I'd e-mail the report. If possible. If you get a "DBCC error", you would
want to catch it _as early as possible_.
Note that SQLMaint will only give you error _text_ in the report, not error
number. Makes it a little bit harder to find descriptions for the errors in
BOL.

Quote:

> The -RebldIdx 10 statement I'm not sure is correct. Is 10% a reasonable
> size? What does this mean anyway, and how should I calculate a correct
> number? I stole this value from the SQL Database maintenance wizard that
> I ran on a test database. My tables grow at less than 2% per day in
> general, and most don't grow at all.

Rebuilding indexes is most significant for clustered indexes (the actual
datapases) and if you perform lagrge scans of the tables (could be partial
table scans, "range queries").
The % value really depends on activity on each table and row size. Since
update activity is low, and you perform the job every night, it seems
reasonable to leave "a bit" of space, but not too much.

My .02.

 
 
 

1. Requested operation requires an OLE DB Session object, which is not supported by the current provide

We recently experienced this error from ADO on our intranet site after
pointing the database connection paramters to a new clustered SQL Server
machine.  Database connectivity for the site (written in ASP 3.0) is
managed by a middle tier COM component residing in COM+.  On the
surface, the error implies some problem with ADO, or how the connection
or command object in ADO is being used.  The error also implies that
maybe there's an issue with the version of ADO.

Our solution ended up being the addition of an entry to the HOSTS file
(SYSTEM32\DRIVERS\ETC) on the web server which pointed to the physical
clustered machine's IP.  In contrast, the database connection parameters
pointed to the "virtual" IP for SQL Server within the clustered machine.

The details how and why this resolved the error are unclear to me, but
it did work.  Perhaps a network engineer could shed some light.

--
Posted via http://dbforums.com

2. Error:Oracle Not Available

3. pgsql/config config.guess config.sub

4. I need a Date calculation statement

5. Help with config.fx, config.fp, config.fpw files

6. DBF Backups online

7. SQL2000 OS/Log config advice

8. fatal error LNK1104: cannot open file "iiapi.lib"

9. RAID-0 config, advice needed

10. Solomon IV NT SQL 7.0 RAID Config Advice?

11. SQL server mem config advice please

12. Need some advice with simple example.