MSDE regualr maintenance - help

Hi folks
New to MSDE...
1. What is the best options to use when doing backups?
option 1. OSQL -U sa -P password -S server -Q "BACKUP DATABASE db1 TO DISK
= 'c:\Backups\backup1.bck'"
option 2. OSQL -U sa -P password -S server -Q "BACKUP DATABASE db1 TO DISK =
'C:\backups\backup1.bck' WITH FORMAT, STATS"
2. Should I also be doing a reindex? I would like to do a batch sql job (
like above backup)
USE DB1
GO
sp_msforeachtable @.command1 = 'DBCC DBREINDEX ([?])'
3. Should I do a Database check if yes, before or after backup...
dbcc checkdb ('database')
4. Would also like to get information DB size - just to mkae sure its stay's
under the 2GB limit.
Someone recommend the followinf - but not sure what they all do...
Save the following commands to a file named "WhatsUpMaint.bat":
osql -E -D WhatsUp -Q "DBCC SHRINKDATABASE (WhatsUp)"
osql -E -D WhatsUp -Q "EXEC sp_MsForEachTable 'DBCC DBREINDEX (''?'', '''',
0)'"
osql -E -D WhatsUp -Q "EXEC sp_createstats"
osql -E -D WhatsUp -Q "EXEC sp_updatestats"
osql -E -D WhatsUp -Q "EXEC sp_MsForEachTable 'sp_recompile ''?'''"
osql -E -D WhatsUp -Q "DBCC UPDATEUSAGE ('WhatsUp')"
osql -E -D WhatsUp -Q "EXEC sp_cycle_errorlog"
hi Danny,
Danny wrote:
> 1. What is the best options to use when doing backups?
> option 1. OSQL -U sa -P password -S server -Q "BACKUP DATABASE db1
> TO DISK = 'c:\Backups\backup1.bck'"
> option 2. OSQL -U sa -P password -S server -Q "BACKUP DATABASE db1 TO
> DISK = 'C:\backups\backup1.bck' WITH FORMAT, STATS"
the "bad thing" of this batch is that it includes sa's password in clear
text... please do not code a cmd/bat file this way :D
create a "backup" login with appropriate permissions and hardcode it's
credentials, not "sa" ones :D
WITH FORMAT specifies a new media set will be created.. so this depends on
your actual requirements.. if you want to "append" your current backup to
existing ones, do not specify it, else, do it..
STATS, on the other hand, specify to output backup percentage to the
standard output..
if not specified you usually have an output like
BACKUP DATABASE pubs TO DISK = 'c:\pus.bak';
--<--
Processed 248 pages for database 'pubs', file 'pubs' on file 4.
Processed 1 pages for database 'pubs', file 'pubs_log' on file 4.
BACKUP DATABASE successfully processed 249 pages in 0.073 seconds (27.942
MB/sec).
where
BACKUP DATABASE pubs TO DISK = 'c:\pus.bak'
WITH STATS;
--<--
12 percent processed.
22 percent processed.
32 percent processed.
41 percent processed.
51 percent processed.
61 percent processed.
70 percent processed.
80 percent processed.
93 percent processed.
Processed 248 pages for database 'pubs', file 'pubs' on file 5.
100 percent processed.
Processed 1 pages for database 'pubs', file 'pubs_log' on file 5.
BACKUP DATABASE successfully processed 249 pages in 0.069 seconds (29.562
MB/sec).
if you do not specify a percent value, SQL Server uses a default of 10%..

> 2. Should I also be doing a reindex? I would like to do a batch sql
> job ( like above backup)
> USE DB1
> GO
> sp_msforeachtable @.command1 = 'DBCC DBREINDEX ([?])'
this statement uses an undocumented stored procedure, sp_msforeachtable, to
rebuild all indexes of each table object.. this obviously imply
"rebuilding" the whole table with clustered index.. so this implies both
time and file related implications... have a look at
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
for futher info, but, to our interest, reindexing is usually a "good thing"
to do :D

> 3. Should I do a Database check if yes, before or after backup...
> dbcc checkdb ('database')
you can go pre-backup

> 4. Would also like to get information DB size - just to mkae sure its
> stay's under the 2GB limit.
sorry, did not understand your requirement...

> Save the following commands to a file named "WhatsUpMaint.bat":
> osql -E -D WhatsUp -Q "DBCC SHRINKDATABASE (WhatsUp)"
about shrinking a database you can have a look at Tibor Karaszi's (SQL
Server MVP) article at http://www.karaszi.com/SQLServer/info_dont_shrink.asp
about the trusted connection you use here... -E indicates to connect as the
current logged in account.. and, as the statements you are executing require
high permissions in the instance context, this means the current interactive
Windows account is quite an admin... as this sounds to be a scheduled batch,
I'd opt for other credentials in order not to require to log in as such a
super man :D

> osql -E -D WhatsUp -Q "EXEC sp_MsForEachTable 'DBCC DBREINDEX (''?'',
> '''', 0)'"
> osql -E -D WhatsUp -Q "EXEC sp_createstats"
to my head this is not required as you usually set this at database level so
that statistics always are created and updated..

> osql -E -D WhatsUp -Q "EXEC sp_updatestats"
but this can be maintained as statistics are "rebuilt" when a certain
treshhold of changes is reached, so you can force it..

> osql -E -D WhatsUp -Q "EXEC sp_MsForEachTable 'sp_recompile ''?'''"
not correct.. this one is available for stored procedures only and not for
base tables...
causing the procedures to be recompiled at next execution time will
obviously imply penalties in their execution... I do usually not perform
that one..

> osql -E -D WhatsUp -Q "DBCC UPDATEUSAGE ('WhatsUp')"
this one re-syncs catalog's stats.. I do usually not do that if not
required.. but it does not hurt..

> osql -E -D WhatsUp -Q "EXEC sp_cycle_errorlog"
this one changes the current log to a new created one... I really do not do
that if not required in order to have a troubleshoot log to archive so some
reason.. but it does not imply penaltie/problems at all..
at the end of the game, I'd suggest to "log" the results of your
maintenances outputting them to a text file you can later inspect, send by
mail or whatever..
just add the
>filePathAndName.txt
parameter to your 1st oSql call to initilize your file and add[vbcol=seagreen]
parameter to the other ones, causing the results to be appended to that
file...
regards
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
-- remove DMO to reply