SQL Server 2000
I have finally been tasked and given access to the maintenance plans ...
only there aren't any (including disk defrags)! Backups are done via an
SQLagent job, but that's it.
Normally I would just whomp the defaults together and start them running,
but this is a 24x7x365 system and I'm not sure which of the jobs have the
potential to lock a table, or an index.
Advice, or links to docs that concern maintenance on a 24x7x365 system.
Also, in Management Studio the maintenance plans are under a Legacy heading
and I haven't seen where maintenance is defined in 2005. What's up with
that? Do I just need to dig deeper?
Thanks,
Jay> SQL Server 2000
> Also, in Management Studio the maintenance plans are under a Legacy heading and I haven't seen
> where maintenance is defined in 2005.
Seems you are using SSMS to connect to a 2000 instance. There are no "new style" MP's on 2000, since
they didn't exist in ... 2000. So perhaps SSMS are showing 2000 (old style) MP's under the legacy
folder, quite simply?
Be care full especially with rebuilding indexes. Rebuilding an index (the way 2000 MP's does it)
will log the table during the index rebuild. Also, DBCC CHECKDB will use resources (I'm not sure
about what level of locking it is using in 2000, but that should be documented in the 2000 Books
Online).
I believe that there's an "Operational Guideline" for 2000 available on the MS web. It could be
worth spending a few minutes with Google to try to dig it up...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <nospam@.nospam.org> wrote in message news:%23kB0DrQ5HHA.3900@.TK2MSFTNGP02.phx.gbl...
> SQL Server 2000
> I have finally been tasked and given access to the maintenance plans ... only there aren't any
> (including disk defrags)! Backups are done via an SQLagent job, but that's it.
> Normally I would just whomp the defaults together and start them running, but this is a 24x7x365
> system and I'm not sure which of the jobs have the potential to lock a table, or an index.
> Advice, or links to docs that concern maintenance on a 24x7x365 system.
> Also, in Management Studio the maintenance plans are under a Legacy heading and I haven't seen
> where maintenance is defined in 2005. What's up with that? Do I just need to dig deeper?
> Thanks,
> Jay
>|||I wasn't able to find much with an internet search, but digging in BOL I
found a lot.
Is this a good plan for 24x7 SQL Server 2000 maintenance (excluding backup)?
Am I missing anything?
Am I doing more than is needed?
Did I miss something in BOL that makes this plan risky?
Here is my intended plan: write a procedure that uses CHECKDB, SHOWCONTIG,
INDEXDEFRAG, STATS_DATE & UPDATE STATISTICS. It would be run either before,
or after the application maintenance (archiving, credit card batches, search
engine data dumps, etc).
At no time will I allow an automated program shrink anything in the Windows
filesystem (and I hope I can keep anything from Auto Growing too).
Write a single stored procedure with an outer loop on DB's from
master..sysdatabases
Start with DBCC CHECKDB('dbname') looping through all databases. According
to BOL, if you don't specify any of the check options, nothing is locked and
on the dev server, it is taking about 2-3 minutes to complete. The only
possible arguments I'm considering are NOINDEX and WITH PHYSICAL_ONLY, but
will probably go with no arguments.
Within the loop, after a success on the CHECKDB (failure would exit the loop
and complain) run a script based on the one in 2000 BOL (DBCC SHOWCONTIG)
that does an INDEXDEFRAG on the index (BOL says this is an online
operation).
Last, run the SQL from BOL to loop on when statistics were last updated:
SELECT 'Index Name' = i.name,
'Statistics Date' = STATS_DATE(i.id, i.indid)
FROM sysobjects o, sysindexes i
WHERE o.name = '_table_name_'
AND o.id = i.id
and update any old statistics.
Done.
Now, I'm not really sure how to decide what statistics are old. Under
Informix I had a counter that measured insert/update activity since the last
statistics update. Perhaps just do everything nightly?
I'm also not sure how the "Auto Create Statistics" checkbox on the database
prosperities/options tab interacts with update statistics.
Thanks,
Jay
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OmJUAXY5HHA.4712@.TK2MSFTNGP04.phx.gbl...
>> SQL Server 2000
>> Also, in Management Studio the maintenance plans are under a Legacy
>> heading and I haven't seen where maintenance is defined in 2005.
> Seems you are using SSMS to connect to a 2000 instance. There are no "new
> style" MP's on 2000, since they didn't exist in ... 2000. So perhaps SSMS
> are showing 2000 (old style) MP's under the legacy folder, quite simply?
> Be care full especially with rebuilding indexes. Rebuilding an index (the
> way 2000 MP's does it) will log the table during the index rebuild. Also,
> DBCC CHECKDB will use resources (I'm not sure about what level of locking
> it is using in 2000, but that should be documented in the 2000 Books
> Online).
> I believe that there's an "Operational Guideline" for 2000 available on
> the MS web. It could be worth spending a few minutes with Google to try to
> dig it up...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jay" <nospam@.nospam.org> wrote in message
> news:%23kB0DrQ5HHA.3900@.TK2MSFTNGP02.phx.gbl...
>> SQL Server 2000
>> I have finally been tasked and given access to the maintenance plans ...
>> only there aren't any (including disk defrags)! Backups are done via an
>> SQLagent job, but that's it.
>> Normally I would just whomp the defaults together and start them running,
>> but this is a 24x7x365 system and I'm not sure which of the jobs have the
>> potential to lock a table, or an index.
>> Advice, or links to docs that concern maintenance on a 24x7x365 system.
>> Also, in Management Studio the maintenance plans are under a Legacy
>> heading and I haven't seen where maintenance is defined in 2005. What's
>> up with that? Do I just need to dig deeper?
>> Thanks,
>> Jay
>|||It certainly seems you grasp the topic well. Some comments:
> Write a single stored procedure with an outer loop on DB's from master..sysdatabases
Watch out for exceptions. What happens if for instance CHECKDB encounters a corruption? Will it
terminate the batch so nothing further is executed? I can't answer that offhand, so thinking this
through and testing is important.
You might want to deliberately crash a database so that CHEDB fails to test this realistically. I
usually hack sysindexes (modify for instance the FirstIAM column for some row to some rubbish
value) - in a test database of course. You aren't allowed to update system tables directly by
default but read about sp_configure and you will probably figure out how to do it (I don't want to
post any more descriptive instructions considering this is a public forum).
Also, consider the client application handling of exceptions. SQL Server Agent is nice, but a TSQL
job step will actually terminate the execution on any error (as I recall it). So, you might want to
schedule a CmdExec job step instead and call OSQL.EXE for the execution of your proc.
> The only possible arguments I'm considering are NOINDEX and WITH PHYSICAL_ONLY, but will probably
> go with no arguments.
Yes, go with as complete check as you can live with. If that doesn't work for you use any of the
options, possibly conditionally based on for instance week day (like do full every Sunday). Btw,
here's a *great* blog on CHECKDB and related topics:
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
> Within the loop, after a success on the CHECKDB (failure would exit the loop and complain) run a
> script based on the one in 2000 BOL (DBCC SHOWCONTIG) that does an INDEXDEFRAG on the index (BOL
> says this is an online operation).
Yep, sounds fine. I guess you could spend lots of time with analyzing how much fragmentation really
hurts you at the table/index level and have conditional handling based on that, but that is probably
overkill. Same goes for the fillfactor value. You can't specify a fillfactor value for INDEXDEFRAG,
it will re-apply the value you specified when you created the index. If you want to read up on this
topic, check out http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx. Also,
be aware of implications of defragging regarding transaction logging too (so you don't be surprised
that the following log backup is potentially huge). And read up on recovery models so you understand
all three.
> Last, run the SQL from BOL to loop on when statistics were last updated:
> and update any old statistics.
First, be aware that if you rebuild (DBREINDEX) your indexes then the statistics that comes with the
index will also be rebuilt. This does not happen if you INDEXDEFRAG, though.
Also, the database setting "auto update statistics" means that SQL Server *will update statistics by
itself*, as they becomes stale. Now, doing a manual update doesn't hurt, especially if you have the
time to use FULLSCAN (see UPDATE STATISTICS command). In most cases, this database setting should be
left to the default value. SQL Server uses a value in sysindexes, rowmodctr (I believe) to determine
how many modifications and whether it is time to auto-update (when a plan is to be re-used or
generated). This is a rather big topic, so here comes another WP:
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx (there is one for 2000 as well, I
don't have that URL handy, but this is readable even if you aren't on 2005).
Note that the auto-create statistics option isn't the same as auto-update. Auto-create means that
the optimizer will *create* statistics on non-indexed columns when it feels that such statistics
would be useful. Such statistics are also auto-updated by the engine by the same handling as
described above (the auto-update setting).
Finally, instead of having this loop, consider creating a table, in which you have a row for each
database, and here you configure what actions are to be performed. Then you loop that table instead
of sysdatabases. I'm not saying it is better, all depends on ones particular requirements etc.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <nospam@.nospam.org> wrote in message news:ubj7n7a5HHA.3900@.TK2MSFTNGP02.phx.gbl...
>I wasn't able to find much with an internet search, but digging in BOL I found a lot.
> Is this a good plan for 24x7 SQL Server 2000 maintenance (excluding backup)?
> Am I missing anything?
> Am I doing more than is needed?
> Did I miss something in BOL that makes this plan risky?
> Here is my intended plan: write a procedure that uses CHECKDB, SHOWCONTIG, INDEXDEFRAG, STATS_DATE
> & UPDATE STATISTICS. It would be run either before, or after the application maintenance
> (archiving, credit card batches, search engine data dumps, etc).
> At no time will I allow an automated program shrink anything in the Windows filesystem (and I hope
> I can keep anything from Auto Growing too).
> Write a single stored procedure with an outer loop on DB's from master..sysdatabases
> Start with DBCC CHECKDB('dbname') looping through all databases. According to BOL, if you don't
> specify any of the check options, nothing is locked and on the dev server, it is taking about 2-3
> minutes to complete. The only possible arguments I'm considering are NOINDEX and WITH
> PHYSICAL_ONLY, but will probably go with no arguments.
> Within the loop, after a success on the CHECKDB (failure would exit the loop and complain) run a
> script based on the one in 2000 BOL (DBCC SHOWCONTIG) that does an INDEXDEFRAG on the index (BOL
> says this is an online operation).
> Last, run the SQL from BOL to loop on when statistics were last updated:
> SELECT 'Index Name' = i.name,
> 'Statistics Date' = STATS_DATE(i.id, i.indid)
> FROM sysobjects o, sysindexes i
> WHERE o.name = '_table_name_'
> AND o.id = i.id
> and update any old statistics.
> Done.
> Now, I'm not really sure how to decide what statistics are old. Under Informix I had a counter
> that measured insert/update activity since the last statistics update. Perhaps just do everything
> nightly?
> I'm also not sure how the "Auto Create Statistics" checkbox on the database prosperities/options
> tab interacts with update statistics.
> Thanks,
> Jay
>
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:OmJUAXY5HHA.4712@.TK2MSFTNGP04.phx.gbl...
>> SQL Server 2000
>> Also, in Management Studio the maintenance plans are under a Legacy heading and I haven't seen
>> where maintenance is defined in 2005.
>> Seems you are using SSMS to connect to a 2000 instance. There are no "new style" MP's on 2000,
>> since they didn't exist in ... 2000. So perhaps SSMS are showing 2000 (old style) MP's under the
>> legacy folder, quite simply?
>> Be care full especially with rebuilding indexes. Rebuilding an index (the way 2000 MP's does it)
>> will log the table during the index rebuild. Also, DBCC CHECKDB will use resources (I'm not sure
>> about what level of locking it is using in 2000, but that should be documented in the 2000 Books
>> Online).
>> I believe that there's an "Operational Guideline" for 2000 available on the MS web. It could be
>> worth spending a few minutes with Google to try to dig it up...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Jay" <nospam@.nospam.org> wrote in message news:%23kB0DrQ5HHA.3900@.TK2MSFTNGP02.phx.gbl...
>> SQL Server 2000
>> I have finally been tasked and given access to the maintenance plans ... only there aren't any
>> (including disk defrags)! Backups are done via an SQLagent job, but that's it.
>> Normally I would just whomp the defaults together and start them running, but this is a 24x7x365
>> system and I'm not sure which of the jobs have the potential to lock a table, or an index.
>> Advice, or links to docs that concern maintenance on a 24x7x365 system.
>> Also, in Management Studio the maintenance plans are under a Legacy heading and I haven't seen
>> where maintenance is defined in 2005. What's up with that? Do I just need to dig deeper?
>> Thanks,
>> Jay
>>
>|||Well now. Just my first read took 10 minutes, never mind fully comprehending
and following the links.
Thank you very much for the time you spent on the reply Tibor and rest
assured, the time a cat would spend picking apart a fish is nothing,
compared to what I'm going to do with it.
Just a couple comments for now:
I grasp the topic well because I was an Informix DBA for 15 years and it's
the same song, just a different tune.
I write all production destined code with strong error checks and find ways
to make it complain loudly is something unexpected/unwanted happens (plus a
once a week ping).
I chose INDEXDEFRAG because it is an online procedure and avoided DBREINDEX
because it is an offline procedure (24x7 after all). I do remember a rather
large discussion on this subject from about 6 months ago, but it doesn't
really matter, because if I get any scheduled maintenance, it will be
infrequent and limited.
Thanks,
Jay
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23HYDpSb5HHA.3940@.TK2MSFTNGP05.phx.gbl...
> It certainly seems you grasp the topic well. Some comments:
>> Write a single stored procedure with an outer loop on DB's from
>> master..sysdatabases
> Watch out for exceptions. What happens if for instance CHECKDB encounters
> a corruption? Will it terminate the batch so nothing further is executed?
> I can't answer that offhand, so thinking this through and testing is
> important.
> You might want to deliberately crash a database so that CHEDB fails to
> test this realistically. I usually hack sysindexes (modify for instance
> the FirstIAM column for some row to some rubbish value) - in a test
> database of course. You aren't allowed to update system tables directly by
> default but read about sp_configure and you will probably figure out how
> to do it (I don't want to post any more descriptive instructions
> considering this is a public forum).
> Also, consider the client application handling of exceptions. SQL Server
> Agent is nice, but a TSQL job step will actually terminate the execution
> on any error (as I recall it). So, you might want to schedule a CmdExec
> job step instead and call OSQL.EXE for the execution of your proc.
>
>> The only possible arguments I'm considering are NOINDEX and WITH
>> PHYSICAL_ONLY, but will probably go with no arguments.
> Yes, go with as complete check as you can live with. If that doesn't work
> for you use any of the options, possibly conditionally based on for
> instance week day (like do full every Sunday). Btw, here's a *great* blog
> on CHECKDB and related topics:
> http://blogs.msdn.com/sqlserverstorageengine/default.aspx
>
>> Within the loop, after a success on the CHECKDB (failure would exit the
>> loop and complain) run a script based on the one in 2000 BOL (DBCC
>> SHOWCONTIG) that does an INDEXDEFRAG on the index (BOL says this is an
>> online operation).
> Yep, sounds fine. I guess you could spend lots of time with analyzing how
> much fragmentation really hurts you at the table/index level and have
> conditional handling based on that, but that is probably overkill. Same
> goes for the fillfactor value. You can't specify a fillfactor value for
> INDEXDEFRAG, it will re-apply the value you specified when you created the
> index. If you want to read up on this topic, check out
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx.
> Also, be aware of implications of defragging regarding transaction logging
> too (so you don't be surprised that the following log backup is
> potentially huge). And read up on recovery models so you understand all
> three.
>
>> Last, run the SQL from BOL to loop on when statistics were last updated:
>> and update any old statistics.
> First, be aware that if you rebuild (DBREINDEX) your indexes then the
> statistics that comes with the index will also be rebuilt. This does not
> happen if you INDEXDEFRAG, though.
> Also, the database setting "auto update statistics" means that SQL Server
> *will update statistics by itself*, as they becomes stale. Now, doing a
> manual update doesn't hurt, especially if you have the time to use
> FULLSCAN (see UPDATE STATISTICS command). In most cases, this database
> setting should be left to the default value. SQL Server uses a value in
> sysindexes, rowmodctr (I believe) to determine how many modifications and
> whether it is time to auto-update (when a plan is to be re-used or
> generated). This is a rather big topic, so here comes another WP:
> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx (there
> is one for 2000 as well, I don't have that URL handy, but this is readable
> even if you aren't on 2005).
> Note that the auto-create statistics option isn't the same as auto-update.
> Auto-create means that the optimizer will *create* statistics on
> non-indexed columns when it feels that such statistics would be useful.
> Such statistics are also auto-updated by the engine by the same handling
> as described above (the auto-update setting).
> Finally, instead of having this loop, consider creating a table, in which
> you have a row for each database, and here you configure what actions are
> to be performed. Then you loop that table instead of sysdatabases. I'm not
> saying it is better, all depends on ones particular requirements etc.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jay" <nospam@.nospam.org> wrote in message
> news:ubj7n7a5HHA.3900@.TK2MSFTNGP02.phx.gbl...
>>I wasn't able to find much with an internet search, but digging in BOL I
>>found a lot.
>> Is this a good plan for 24x7 SQL Server 2000 maintenance (excluding
>> backup)?
>> Am I missing anything?
>> Am I doing more than is needed?
>> Did I miss something in BOL that makes this plan risky?
>> Here is my intended plan: write a procedure that uses CHECKDB,
>> SHOWCONTIG, INDEXDEFRAG, STATS_DATE & UPDATE STATISTICS. It would be run
>> either before, or after the application maintenance (archiving, credit
>> card batches, search engine data dumps, etc).
>> At no time will I allow an automated program shrink anything in the
>> Windows filesystem (and I hope I can keep anything from Auto Growing
>> too).
>> Write a single stored procedure with an outer loop on DB's from
>> master..sysdatabases
>> Start with DBCC CHECKDB('dbname') looping through all databases.
>> According to BOL, if you don't specify any of the check options, nothing
>> is locked and on the dev server, it is taking about 2-3 minutes to
>> complete. The only possible arguments I'm considering are NOINDEX and
>> WITH PHYSICAL_ONLY, but will probably go with no arguments.
>> Within the loop, after a success on the CHECKDB (failure would exit the
>> loop and complain) run a script based on the one in 2000 BOL (DBCC
>> SHOWCONTIG) that does an INDEXDEFRAG on the index (BOL says this is an
>> online operation).
>> Last, run the SQL from BOL to loop on when statistics were last updated:
>> SELECT 'Index Name' = i.name,
>> 'Statistics Date' = STATS_DATE(i.id, i.indid)
>> FROM sysobjects o, sysindexes i
>> WHERE o.name = '_table_name_'
>> AND o.id = i.id
>> and update any old statistics.
>> Done.
>> Now, I'm not really sure how to decide what statistics are old. Under
>> Informix I had a counter that measured insert/update activity since the
>> last statistics update. Perhaps just do everything nightly?
>> I'm also not sure how the "Auto Create Statistics" checkbox on the
>> database prosperities/options tab interacts with update statistics.
>> Thanks,
>> Jay
>>
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:OmJUAXY5HHA.4712@.TK2MSFTNGP04.phx.gbl...
>> SQL Server 2000
>> Also, in Management Studio the maintenance plans are under a Legacy
>> heading and I haven't seen where maintenance is defined in 2005.
>> Seems you are using SSMS to connect to a 2000 instance. There are no
>> "new style" MP's on 2000, since they didn't exist in ... 2000. So
>> perhaps SSMS are showing 2000 (old style) MP's under the legacy folder,
>> quite simply?
>> Be care full especially with rebuilding indexes. Rebuilding an index
>> (the way 2000 MP's does it) will log the table during the index rebuild.
>> Also, DBCC CHECKDB will use resources (I'm not sure about what level of
>> locking it is using in 2000, but that should be documented in the 2000
>> Books Online).
>> I believe that there's an "Operational Guideline" for 2000 available on
>> the MS web. It could be worth spending a few minutes with Google to try
>> to dig it up...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Jay" <nospam@.nospam.org> wrote in message
>> news:%23kB0DrQ5HHA.3900@.TK2MSFTNGP02.phx.gbl...
>> SQL Server 2000
>> I have finally been tasked and given access to the maintenance plans
>> ... only there aren't any (including disk defrags)! Backups are done
>> via an SQLagent job, but that's it.
>> Normally I would just whomp the defaults together and start them
>> running, but this is a 24x7x365 system and I'm not sure which of the
>> jobs have the potential to lock a table, or an index.
>> Advice, or links to docs that concern maintenance on a 24x7x365 system.
>> Also, in Management Studio the maintenance plans are under a Legacy
>> heading and I haven't seen where maintenance is defined in 2005. What's
>> up with that? Do I just need to dig deeper?
>> Thanks,
>> Jay
>>
>>
>|||> Thank you very much for the time you spent on the reply Tibor and rest assured, the time a cat
> would spend picking apart a fish is nothing, compared to what I'm going to do with it.
You're welcome. The links are good reading, so do check them when you have the time.
> I grasp the topic well because I was an Informix DBA for 15 years and it's the same song, just a
> different tune.
Yes, I figured that from your earlier reply and the follow-up questions etc you had. Having that
background, I doubt you will have difficulties with the links I posted.
> I chose INDEXDEFRAG because it is an online procedure and avoided DBREINDEX because it is an
> offline procedure (24x7 after all).
I figured that. I mostly wanted to mention the transaction logging aspect and differences between
the two, in conjunction with the recovery model. Often overlooked. When you go to 2005, you can play
with the ONLINE option for ALTER INDEX REBUILD (same as DBREINDEX in 2000). For instance reorganize
if you have fragmentation less than say 30%, else rebuild. This new ONLINE option isn't a panacea,
though, but it is one more tool in the box.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <nospam@.nospam.org> wrote in message news:%236s9kxb5HHA.2752@.TK2MSFTNGP06.phx.gbl...
> Well now. Just my first read took 10 minutes, never mind fully comprehending and following the
> links.
> Thank you very much for the time you spent on the reply Tibor and rest assured, the time a cat
> would spend picking apart a fish is nothing, compared to what I'm going to do with it.
> Just a couple comments for now:
> I grasp the topic well because I was an Informix DBA for 15 years and it's the same song, just a
> different tune.
> I write all production destined code with strong error checks and find ways to make it complain
> loudly is something unexpected/unwanted happens (plus a once a week ping).
> I chose INDEXDEFRAG because it is an online procedure and avoided DBREINDEX because it is an
> offline procedure (24x7 after all). I do remember a rather large discussion on this subject from
> about 6 months ago, but it doesn't really matter, because if I get any scheduled maintenance, it
> will be infrequent and limited.
> Thanks,
> Jay
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:%23HYDpSb5HHA.3940@.TK2MSFTNGP05.phx.gbl...
>> It certainly seems you grasp the topic well. Some comments:
>> Write a single stored procedure with an outer loop on DB's from master..sysdatabases
>> Watch out for exceptions. What happens if for instance CHECKDB encounters a corruption? Will it
>> terminate the batch so nothing further is executed? I can't answer that offhand, so thinking this
>> through and testing is important.
>> You might want to deliberately crash a database so that CHEDB fails to test this realistically. I
>> usually hack sysindexes (modify for instance the FirstIAM column for some row to some rubbish
>> value) - in a test database of course. You aren't allowed to update system tables directly by
>> default but read about sp_configure and you will probably figure out how to do it (I don't want
>> to post any more descriptive instructions considering this is a public forum).
>> Also, consider the client application handling of exceptions. SQL Server Agent is nice, but a
>> TSQL job step will actually terminate the execution on any error (as I recall it). So, you might
>> want to schedule a CmdExec job step instead and call OSQL.EXE for the execution of your proc.
>>
>> The only possible arguments I'm considering are NOINDEX and WITH PHYSICAL_ONLY, but will
>> probably go with no arguments.
>> Yes, go with as complete check as you can live with. If that doesn't work for you use any of the
>> options, possibly conditionally based on for instance week day (like do full every Sunday). Btw,
>> here's a *great* blog on CHECKDB and related topics:
>> http://blogs.msdn.com/sqlserverstorageengine/default.aspx
>>
>> Within the loop, after a success on the CHECKDB (failure would exit the loop and complain) run a
>> script based on the one in 2000 BOL (DBCC SHOWCONTIG) that does an INDEXDEFRAG on the index (BOL
>> says this is an online operation).
>> Yep, sounds fine. I guess you could spend lots of time with analyzing how much fragmentation
>> really hurts you at the table/index level and have conditional handling based on that, but that
>> is probably overkill. Same goes for the fillfactor value. You can't specify a fillfactor value
>> for INDEXDEFRAG, it will re-apply the value you specified when you created the index. If you want
>> to read up on this topic, check out
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx. Also, be aware of
>> implications of defragging regarding transaction logging too (so you don't be surprised that the
>> following log backup is potentially huge). And read up on recovery models so you understand all
>> three.
>>
>> Last, run the SQL from BOL to loop on when statistics were last updated:
>> and update any old statistics.
>> First, be aware that if you rebuild (DBREINDEX) your indexes then the statistics that comes with
>> the index will also be rebuilt. This does not happen if you INDEXDEFRAG, though.
>> Also, the database setting "auto update statistics" means that SQL Server *will update statistics
>> by itself*, as they becomes stale. Now, doing a manual update doesn't hurt, especially if you
>> have the time to use FULLSCAN (see UPDATE STATISTICS command). In most cases, this database
>> setting should be left to the default value. SQL Server uses a value in sysindexes, rowmodctr (I
>> believe) to determine how many modifications and whether it is time to auto-update (when a plan
>> is to be re-used or generated). This is a rather big topic, so here comes another WP:
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx (there is one for 2000 as well,
>> I don't have that URL handy, but this is readable even if you aren't on 2005).
>> Note that the auto-create statistics option isn't the same as auto-update. Auto-create means that
>> the optimizer will *create* statistics on non-indexed columns when it feels that such statistics
>> would be useful. Such statistics are also auto-updated by the engine by the same handling as
>> described above (the auto-update setting).
>> Finally, instead of having this loop, consider creating a table, in which you have a row for each
>> database, and here you configure what actions are to be performed. Then you loop that table
>> instead of sysdatabases. I'm not saying it is better, all depends on ones particular requirements
>> etc.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Jay" <nospam@.nospam.org> wrote in message news:ubj7n7a5HHA.3900@.TK2MSFTNGP02.phx.gbl...
>>I wasn't able to find much with an internet search, but digging in BOL I found a lot.
>> Is this a good plan for 24x7 SQL Server 2000 maintenance (excluding backup)?
>> Am I missing anything?
>> Am I doing more than is needed?
>> Did I miss something in BOL that makes this plan risky?
>> Here is my intended plan: write a procedure that uses CHECKDB, SHOWCONTIG, INDEXDEFRAG,
>> STATS_DATE & UPDATE STATISTICS. It would be run either before, or after the application
>> maintenance (archiving, credit card batches, search engine data dumps, etc).
>> At no time will I allow an automated program shrink anything in the Windows filesystem (and I
>> hope I can keep anything from Auto Growing too).
>> Write a single stored procedure with an outer loop on DB's from master..sysdatabases
>> Start with DBCC CHECKDB('dbname') looping through all databases. According to BOL, if you don't
>> specify any of the check options, nothing is locked and on the dev server, it is taking about
>> 2-3 minutes to complete. The only possible arguments I'm considering are NOINDEX and WITH
>> PHYSICAL_ONLY, but will probably go with no arguments.
>> Within the loop, after a success on the CHECKDB (failure would exit the loop and complain) run a
>> script based on the one in 2000 BOL (DBCC SHOWCONTIG) that does an INDEXDEFRAG on the index (BOL
>> says this is an online operation).
>> Last, run the SQL from BOL to loop on when statistics were last updated:
>> SELECT 'Index Name' = i.name,
>> 'Statistics Date' = STATS_DATE(i.id, i.indid)
>> FROM sysobjects o, sysindexes i
>> WHERE o.name = '_table_name_'
>> AND o.id = i.id
>> and update any old statistics.
>> Done.
>> Now, I'm not really sure how to decide what statistics are old. Under Informix I had a counter
>> that measured insert/update activity since the last statistics update. Perhaps just do
>> everything nightly?
>> I'm also not sure how the "Auto Create Statistics" checkbox on the database prosperities/options
>> tab interacts with update statistics.
>> Thanks,
>> Jay
>>
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> news:OmJUAXY5HHA.4712@.TK2MSFTNGP04.phx.gbl...
>> SQL Server 2000
>> Also, in Management Studio the maintenance plans are under a Legacy heading and I haven't seen
>> where maintenance is defined in 2005.
>> Seems you are using SSMS to connect to a 2000 instance. There are no "new style" MP's on 2000,
>> since they didn't exist in ... 2000. So perhaps SSMS are showing 2000 (old style) MP's under
>> the legacy folder, quite simply?
>> Be care full especially with rebuilding indexes. Rebuilding an index (the way 2000 MP's does
>> it) will log the table during the index rebuild. Also, DBCC CHECKDB will use resources (I'm not
>> sure about what level of locking it is using in 2000, but that should be documented in the 2000
>> Books Online).
>> I believe that there's an "Operational Guideline" for 2000 available on the MS web. It could be
>> worth spending a few minutes with Google to try to dig it up...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Jay" <nospam@.nospam.org> wrote in message news:%23kB0DrQ5HHA.3900@.TK2MSFTNGP02.phx.gbl...
>> SQL Server 2000
>> I have finally been tasked and given access to the maintenance plans ... only there aren't any
>> (including disk defrags)! Backups are done via an SQLagent job, but that's it.
>> Normally I would just whomp the defaults together and start them running, but this is a
>> 24x7x365 system and I'm not sure which of the jobs have the potential to lock a table, or an
>> index.
>> Advice, or links to docs that concern maintenance on a 24x7x365 system.
>> Also, in Management Studio the maintenance plans are under a Legacy heading and I haven't seen
>> where maintenance is defined in 2005. What's up with that? Do I just need to dig deeper?
>> Thanks,
>> Jay
>>
>>
>|||One point just hit me.
On the Wizard it says: "Update data optimization", but the routines I've
found only deal with indexes and statistics. Is this correct, or did I miss
one? Or, are the statistics the data portion?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uQpth3b5HHA.464@.TK2MSFTNGP02.phx.gbl...
>> Thank you very much for the time you spent on the reply Tibor and rest
>> assured, the time a cat would spend picking apart a fish is nothing,
>> compared to what I'm going to do with it.
> You're welcome. The links are good reading, so do check them when you have
> the time.
>> I grasp the topic well because I was an Informix DBA for 15 years and
>> it's the same song, just a different tune.
> Yes, I figured that from your earlier reply and the follow-up questions
> etc you had. Having that background, I doubt you will have difficulties
> with the links I posted.
>
>> I chose INDEXDEFRAG because it is an online procedure and avoided
>> DBREINDEX because it is an offline procedure (24x7 after all).
> I figured that. I mostly wanted to mention the transaction logging aspect
> and differences between the two, in conjunction with the recovery model.
> Often overlooked. When you go to 2005, you can play with the ONLINE option
> for ALTER INDEX REBUILD (same as DBREINDEX in 2000). For instance
> reorganize if you have fragmentation less than say 30%, else rebuild. This
> new ONLINE option isn't a panacea, though, but it is one more tool in the
> box.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jay" <nospam@.nospam.org> wrote in message
> news:%236s9kxb5HHA.2752@.TK2MSFTNGP06.phx.gbl...
>> Well now. Just my first read took 10 minutes, never mind fully
>> comprehending and following the links.
>> Thank you very much for the time you spent on the reply Tibor and rest
>> assured, the time a cat would spend picking apart a fish is nothing,
>> compared to what I'm going to do with it.
>> Just a couple comments for now:
>> I grasp the topic well because I was an Informix DBA for 15 years and
>> it's the same song, just a different tune.
>> I write all production destined code with strong error checks and find
>> ways to make it complain loudly is something unexpected/unwanted happens
>> (plus a once a week ping).
>> I chose INDEXDEFRAG because it is an online procedure and avoided
>> DBREINDEX because it is an offline procedure (24x7 after all). I do
>> remember a rather large discussion on this subject from about 6 months
>> ago, but it doesn't really matter, because if I get any scheduled
>> maintenance, it will be infrequent and limited.
>> Thanks,
>> Jay
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:%23HYDpSb5HHA.3940@.TK2MSFTNGP05.phx.gbl...
>> It certainly seems you grasp the topic well. Some comments:
>> Write a single stored procedure with an outer loop on DB's from
>> master..sysdatabases
>> Watch out for exceptions. What happens if for instance CHECKDB
>> encounters a corruption? Will it terminate the batch so nothing further
>> is executed? I can't answer that offhand, so thinking this through and
>> testing is important.
>> You might want to deliberately crash a database so that CHEDB fails to
>> test this realistically. I usually hack sysindexes (modify for instance
>> the FirstIAM column for some row to some rubbish value) - in a test
>> database of course. You aren't allowed to update system tables directly
>> by default but read about sp_configure and you will probably figure out
>> how to do it (I don't want to post any more descriptive instructions
>> considering this is a public forum).
>> Also, consider the client application handling of exceptions. SQL Server
>> Agent is nice, but a TSQL job step will actually terminate the execution
>> on any error (as I recall it). So, you might want to schedule a CmdExec
>> job step instead and call OSQL.EXE for the execution of your proc.
>>
>> The only possible arguments I'm considering are NOINDEX and WITH
>> PHYSICAL_ONLY, but will probably go with no arguments.
>> Yes, go with as complete check as you can live with. If that doesn't
>> work for you use any of the options, possibly conditionally based on for
>> instance week day (like do full every Sunday). Btw, here's a *great*
>> blog on CHECKDB and related topics:
>> http://blogs.msdn.com/sqlserverstorageengine/default.aspx
>>
>> Within the loop, after a success on the CHECKDB (failure would exit the
>> loop and complain) run a script based on the one in 2000 BOL (DBCC
>> SHOWCONTIG) that does an INDEXDEFRAG on the index (BOL says this is an
>> online operation).
>> Yep, sounds fine. I guess you could spend lots of time with analyzing
>> how much fragmentation really hurts you at the table/index level and
>> have conditional handling based on that, but that is probably overkill.
>> Same goes for the fillfactor value. You can't specify a fillfactor value
>> for INDEXDEFRAG, it will re-apply the value you specified when you
>> created the index. If you want to read up on this topic, check out
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx.
>> Also, be aware of implications of defragging regarding transaction
>> logging too (so you don't be surprised that the following log backup is
>> potentially huge). And read up on recovery models so you understand all
>> three.
>>
>> Last, run the SQL from BOL to loop on when statistics were last
>> updated:
>> and update any old statistics.
>> First, be aware that if you rebuild (DBREINDEX) your indexes then the
>> statistics that comes with the index will also be rebuilt. This does not
>> happen if you INDEXDEFRAG, though.
>> Also, the database setting "auto update statistics" means that SQL
>> Server *will update statistics by itself*, as they becomes stale. Now,
>> doing a manual update doesn't hurt, especially if you have the time to
>> use FULLSCAN (see UPDATE STATISTICS command). In most cases, this
>> database setting should be left to the default value. SQL Server uses a
>> value in sysindexes, rowmodctr (I believe) to determine how many
>> modifications and whether it is time to auto-update (when a plan is to
>> be re-used or generated). This is a rather big topic, so here comes
>> another WP:
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx (there
>> is one for 2000 as well, I don't have that URL handy, but this is
>> readable even if you aren't on 2005).
>> Note that the auto-create statistics option isn't the same as
>> auto-update. Auto-create means that the optimizer will *create*
>> statistics on non-indexed columns when it feels that such statistics
>> would be useful. Such statistics are also auto-updated by the engine by
>> the same handling as described above (the auto-update setting).
>> Finally, instead of having this loop, consider creating a table, in
>> which you have a row for each database, and here you configure what
>> actions are to be performed. Then you loop that table instead of
>> sysdatabases. I'm not saying it is better, all depends on ones
>> particular requirements etc.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Jay" <nospam@.nospam.org> wrote in message
>> news:ubj7n7a5HHA.3900@.TK2MSFTNGP02.phx.gbl...
>>I wasn't able to find much with an internet search, but digging in BOL I
>>found a lot.
>> Is this a good plan for 24x7 SQL Server 2000 maintenance (excluding
>> backup)?
>> Am I missing anything?
>> Am I doing more than is needed?
>> Did I miss something in BOL that makes this plan risky?
>> Here is my intended plan: write a procedure that uses CHECKDB,
>> SHOWCONTIG, INDEXDEFRAG, STATS_DATE & UPDATE STATISTICS. It would be
>> run either before, or after the application maintenance (archiving,
>> credit card batches, search engine data dumps, etc).
>> At no time will I allow an automated program shrink anything in the
>> Windows filesystem (and I hope I can keep anything from Auto Growing
>> too).
>> Write a single stored procedure with an outer loop on DB's from
>> master..sysdatabases
>> Start with DBCC CHECKDB('dbname') looping through all databases.
>> According to BOL, if you don't specify any of the check options,
>> nothing is locked and on the dev server, it is taking about 2-3 minutes
>> to complete. The only possible arguments I'm considering are NOINDEX
>> and WITH PHYSICAL_ONLY, but will probably go with no arguments.
>> Within the loop, after a success on the CHECKDB (failure would exit the
>> loop and complain) run a script based on the one in 2000 BOL (DBCC
>> SHOWCONTIG) that does an INDEXDEFRAG on the index (BOL says this is an
>> online operation).
>> Last, run the SQL from BOL to loop on when statistics were last
>> updated:
>> SELECT 'Index Name' = i.name,
>> 'Statistics Date' = STATS_DATE(i.id, i.indid)
>> FROM sysobjects o, sysindexes i
>> WHERE o.name = '_table_name_'
>> AND o.id = i.id
>> and update any old statistics.
>> Done.
>> Now, I'm not really sure how to decide what statistics are old. Under
>> Informix I had a counter that measured insert/update activity since the
>> last statistics update. Perhaps just do everything nightly?
>> I'm also not sure how the "Auto Create Statistics" checkbox on the
>> database prosperities/options tab interacts with update statistics.
>> Thanks,
>> Jay
>>
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> wrote in message news:OmJUAXY5HHA.4712@.TK2MSFTNGP04.phx.gbl...
>> SQL Server 2000
>> Also, in Management Studio the maintenance plans are under a Legacy
>> heading and I haven't seen where maintenance is defined in 2005.
>> Seems you are using SSMS to connect to a 2000 instance. There are no
>> "new style" MP's on 2000, since they didn't exist in ... 2000. So
>> perhaps SSMS are showing 2000 (old style) MP's under the legacy
>> folder, quite simply?
>> Be care full especially with rebuilding indexes. Rebuilding an index
>> (the way 2000 MP's does it) will log the table during the index
>> rebuild. Also, DBCC CHECKDB will use resources (I'm not sure about
>> what level of locking it is using in 2000, but that should be
>> documented in the 2000 Books Online).
>> I believe that there's an "Operational Guideline" for 2000 available
>> on the MS web. It could be worth spending a few minutes with Google to
>> try to dig it up...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Jay" <nospam@.nospam.org> wrote in message
>> news:%23kB0DrQ5HHA.3900@.TK2MSFTNGP02.phx.gbl...
>> SQL Server 2000
>> I have finally been tasked and given access to the maintenance plans
>> ... only there aren't any (including disk defrags)! Backups are done
>> via an SQLagent job, but that's it.
>> Normally I would just whomp the defaults together and start them
>> running, but this is a 24x7x365 system and I'm not sure which of the
>> jobs have the potential to lock a table, or an index.
>> Advice, or links to docs that concern maintenance on a 24x7x365
>> system.
>> Also, in Management Studio the maintenance plans are under a Legacy
>> heading and I haven't seen where maintenance is defined in 2005.
>> What's up with that? Do I just need to dig deeper?
>> Thanks,
>> Jay
>>
>>
>>
>|||> On the Wizard it says: "Update data optimization",
I've never thought about those words in the wizard. The wizard exposes three options. Translated
into TSQL commands (top to bottom):
DBCC DBREINDEX
UPDATE STATISTICS
DBCC SHRINKDB See http://www.karaszi.com/SQLServer/info_dont_shrink.asp to find out what I think of
this one.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <nospam@.nospam.org> wrote in message news:OJX$RTc5HHA.1992@.TK2MSFTNGP03.phx.gbl...
> One point just hit me.
> On the Wizard it says: "Update data optimization", but the routines I've found only deal with
> indexes and statistics. Is this correct, or did I miss one? Or, are the statistics the data
> portion?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:uQpth3b5HHA.464@.TK2MSFTNGP02.phx.gbl...
>> Thank you very much for the time you spent on the reply Tibor and rest assured, the time a cat
>> would spend picking apart a fish is nothing, compared to what I'm going to do with it.
>> You're welcome. The links are good reading, so do check them when you have the time.
>> I grasp the topic well because I was an Informix DBA for 15 years and it's the same song, just a
>> different tune.
>> Yes, I figured that from your earlier reply and the follow-up questions etc you had. Having that
>> background, I doubt you will have difficulties with the links I posted.
>>
>> I chose INDEXDEFRAG because it is an online procedure and avoided DBREINDEX because it is an
>> offline procedure (24x7 after all).
>> I figured that. I mostly wanted to mention the transaction logging aspect and differences between
>> the two, in conjunction with the recovery model. Often overlooked. When you go to 2005, you can
>> play with the ONLINE option for ALTER INDEX REBUILD (same as DBREINDEX in 2000). For instance
>> reorganize if you have fragmentation less than say 30%, else rebuild. This new ONLINE option
>> isn't a panacea, though, but it is one more tool in the box.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Jay" <nospam@.nospam.org> wrote in message news:%236s9kxb5HHA.2752@.TK2MSFTNGP06.phx.gbl...
>> Well now. Just my first read took 10 minutes, never mind fully comprehending and following the
>> links.
>> Thank you very much for the time you spent on the reply Tibor and rest assured, the time a cat
>> would spend picking apart a fish is nothing, compared to what I'm going to do with it.
>> Just a couple comments for now:
>> I grasp the topic well because I was an Informix DBA for 15 years and it's the same song, just a
>> different tune.
>> I write all production destined code with strong error checks and find ways to make it complain
>> loudly is something unexpected/unwanted happens (plus a once a week ping).
>> I chose INDEXDEFRAG because it is an online procedure and avoided DBREINDEX because it is an
>> offline procedure (24x7 after all). I do remember a rather large discussion on this subject from
>> about 6 months ago, but it doesn't really matter, because if I get any scheduled maintenance, it
>> will be infrequent and limited.
>> Thanks,
>> Jay
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> news:%23HYDpSb5HHA.3940@.TK2MSFTNGP05.phx.gbl...
>> It certainly seems you grasp the topic well. Some comments:
>> Write a single stored procedure with an outer loop on DB's from master..sysdatabases
>> Watch out for exceptions. What happens if for instance CHECKDB encounters a corruption? Will it
>> terminate the batch so nothing further is executed? I can't answer that offhand, so thinking
>> this through and testing is important.
>> You might want to deliberately crash a database so that CHEDB fails to test this realistically.
>> I usually hack sysindexes (modify for instance the FirstIAM column for some row to some rubbish
>> value) - in a test database of course. You aren't allowed to update system tables directly by
>> default but read about sp_configure and you will probably figure out how to do it (I don't want
>> to post any more descriptive instructions considering this is a public forum).
>> Also, consider the client application handling of exceptions. SQL Server Agent is nice, but a
>> TSQL job step will actually terminate the execution on any error (as I recall it). So, you
>> might want to schedule a CmdExec job step instead and call OSQL.EXE for the execution of your
>> proc.
>>
>> The only possible arguments I'm considering are NOINDEX and WITH PHYSICAL_ONLY, but will
>> probably go with no arguments.
>> Yes, go with as complete check as you can live with. If that doesn't work for you use any of
>> the options, possibly conditionally based on for instance week day (like do full every Sunday).
>> Btw, here's a *great* blog on CHECKDB and related topics:
>> http://blogs.msdn.com/sqlserverstorageengine/default.aspx
>>
>> Within the loop, after a success on the CHECKDB (failure would exit the loop and complain) run
>> a script based on the one in 2000 BOL (DBCC SHOWCONTIG) that does an INDEXDEFRAG on the index
>> (BOL says this is an online operation).
>> Yep, sounds fine. I guess you could spend lots of time with analyzing how much fragmentation
>> really hurts you at the table/index level and have conditional handling based on that, but that
>> is probably overkill. Same goes for the fillfactor value. You can't specify a fillfactor value
>> for INDEXDEFRAG, it will re-apply the value you specified when you created the index. If you
>> want to read up on this topic, check out
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx. Also, be aware of
>> implications of defragging regarding transaction logging too (so you don't be surprised that
>> the following log backup is potentially huge). And read up on recovery models so you understand
>> all three.
>>
>> Last, run the SQL from BOL to loop on when statistics were last updated:
>> and update any old statistics.
>> First, be aware that if you rebuild (DBREINDEX) your indexes then the statistics that comes
>> with the index will also be rebuilt. This does not happen if you INDEXDEFRAG, though.
>> Also, the database setting "auto update statistics" means that SQL Server *will update
>> statistics by itself*, as they becomes stale. Now, doing a manual update doesn't hurt,
>> especially if you have the time to use FULLSCAN (see UPDATE STATISTICS command). In most cases,
>> this database setting should be left to the default value. SQL Server uses a value in
>> sysindexes, rowmodctr (I believe) to determine how many modifications and whether it is time to
>> auto-update (when a plan is to be re-used or generated). This is a rather big topic, so here
>> comes another WP: http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx (there is
>> one for 2000 as well, I don't have that URL handy, but this is readable even if you aren't on
>> 2005).
>> Note that the auto-create statistics option isn't the same as auto-update. Auto-create means
>> that the optimizer will *create* statistics on non-indexed columns when it feels that such
>> statistics would be useful. Such statistics are also auto-updated by the engine by the same
>> handling as described above (the auto-update setting).
>> Finally, instead of having this loop, consider creating a table, in which you have a row for
>> each database, and here you configure what actions are to be performed. Then you loop that
>> table instead of sysdatabases. I'm not saying it is better, all depends on ones particular
>> requirements etc.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Jay" <nospam@.nospam.org> wrote in message news:ubj7n7a5HHA.3900@.TK2MSFTNGP02.phx.gbl...
>>I wasn't able to find much with an internet search, but digging in BOL I found a lot.
>> Is this a good plan for 24x7 SQL Server 2000 maintenance (excluding backup)?
>> Am I missing anything?
>> Am I doing more than is needed?
>> Did I miss something in BOL that makes this plan risky?
>> Here is my intended plan: write a procedure that uses CHECKDB, SHOWCONTIG, INDEXDEFRAG,
>> STATS_DATE & UPDATE STATISTICS. It would be run either before, or after the application
>> maintenance (archiving, credit card batches, search engine data dumps, etc).
>> At no time will I allow an automated program shrink anything in the Windows filesystem (and I
>> hope I can keep anything from Auto Growing too).
>> Write a single stored procedure with an outer loop on DB's from master..sysdatabases
>> Start with DBCC CHECKDB('dbname') looping through all databases. According to BOL, if you
>> don't specify any of the check options, nothing is locked and on the dev server, it is taking
>> about 2-3 minutes to complete. The only possible arguments I'm considering are NOINDEX and
>> WITH PHYSICAL_ONLY, but will probably go with no arguments.
>> Within the loop, after a success on the CHECKDB (failure would exit the loop and complain) run
>> a script based on the one in 2000 BOL (DBCC SHOWCONTIG) that does an INDEXDEFRAG on the index
>> (BOL says this is an online operation).
>> Last, run the SQL from BOL to loop on when statistics were last updated:
>> SELECT 'Index Name' = i.name,
>> 'Statistics Date' = STATS_DATE(i.id, i.indid)
>> FROM sysobjects o, sysindexes i
>> WHERE o.name = '_table_name_'
>> AND o.id = i.id
>> and update any old statistics.
>> Done.
>> Now, I'm not really sure how to decide what statistics are old. Under Informix I had a counter
>> that measured insert/update activity since the last statistics update. Perhaps just do
>> everything nightly?
>> I'm also not sure how the "Auto Create Statistics" checkbox on the database
>> prosperities/options tab interacts with update statistics.
>> Thanks,
>> Jay
>>
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> news:OmJUAXY5HHA.4712@.TK2MSFTNGP04.phx.gbl...
>>> SQL Server 2000
>>> Also, in Management Studio the maintenance plans are under a Legacy heading and I haven't
>>> seen where maintenance is defined in 2005.
>> Seems you are using SSMS to connect to a 2000 instance. There are no "new style" MP's on
>> 2000, since they didn't exist in ... 2000. So perhaps SSMS are showing 2000 (old style) MP's
>> under the legacy folder, quite simply?
>> Be care full especially with rebuilding indexes. Rebuilding an index (the way 2000 MP's does
>> it) will log the table during the index rebuild. Also, DBCC CHECKDB will use resources (I'm
>> not sure about what level of locking it is using in 2000, but that should be documented in
>> the 2000 Books Online).
>> I believe that there's an "Operational Guideline" for 2000 available on the MS web. It could
>> be worth spending a few minutes with Google to try to dig it up...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Jay" <nospam@.nospam.org> wrote in message news:%23kB0DrQ5HHA.3900@.TK2MSFTNGP02.phx.gbl...
>>> SQL Server 2000
>>>
>>> I have finally been tasked and given access to the maintenance plans ... only there aren't
>>> any (including disk defrags)! Backups are done via an SQLagent job, but that's it.
>>>
>>> Normally I would just whomp the defaults together and start them running, but this is a
>>> 24x7x365 system and I'm not sure which of the jobs have the potential to lock a table, or an
>>> index.
>>>
>>> Advice, or links to docs that concern maintenance on a 24x7x365 system.
>>>
>>> Also, in Management Studio the maintenance plans are under a Legacy heading and I haven't
>>> seen where maintenance is defined in 2005. What's up with that? Do I just need to dig
>>> deeper?
>>>
>>> Thanks,
>>> Jay
>>>
>>
>>
>>
>|||Oh, I'll read the link later, but I knew your opinion (which mirrors my own)
even before seeing the name of the asp page.
On a side note, under Informix I was a big fan of raw partitions, as you
gained about 5% (or more) in performance (almost for free) and none of the
convenience issues in Windows were present (detach mainly). Also, there were
load balancing things you could do that would be very similar to defining
DPV's on one instance (the clause is named FRAGMENT BY - Yuck! But the
functionality is FANTASTIC!).
Anyway, this provides a fixed amount of drive space allocated to the
database engine and growing/shrinking becomes a non-issue.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OhZrxUi5HHA.1184@.TK2MSFTNGP04.phx.gbl...
>> On the Wizard it says: "Update data optimization",
> I've never thought about those words in the wizard. The wizard exposes
> three options. Translated into TSQL commands (top to bottom):
> DBCC DBREINDEX
> UPDATE STATISTICS
> DBCC SHRINKDB See http://www.karaszi.com/SQLServer/info_dont_shrink.asp to
> find out what I think of this one.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jay" <nospam@.nospam.org> wrote in message
> news:OJX$RTc5HHA.1992@.TK2MSFTNGP03.phx.gbl...
>> One point just hit me.
>> On the Wizard it says: "Update data optimization", but the routines I've
>> found only deal with indexes and statistics. Is this correct, or did I
>> miss one? Or, are the statistics the data portion?
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:uQpth3b5HHA.464@.TK2MSFTNGP02.phx.gbl...
>> Thank you very much for the time you spent on the reply Tibor and rest
>> assured, the time a cat would spend picking apart a fish is nothing,
>> compared to what I'm going to do with it.
>> You're welcome. The links are good reading, so do check them when you
>> have the time.
>> I grasp the topic well because I was an Informix DBA for 15 years and
>> it's the same song, just a different tune.
>> Yes, I figured that from your earlier reply and the follow-up questions
>> etc you had. Having that background, I doubt you will have difficulties
>> with the links I posted.
>>
>> I chose INDEXDEFRAG because it is an online procedure and avoided
>> DBREINDEX because it is an offline procedure (24x7 after all).
>> I figured that. I mostly wanted to mention the transaction logging
>> aspect and differences between the two, in conjunction with the recovery
>> model. Often overlooked. When you go to 2005, you can play with the
>> ONLINE option for ALTER INDEX REBUILD (same as DBREINDEX in 2000). For
>> instance reorganize if you have fragmentation less than say 30%, else
>> rebuild. This new ONLINE option isn't a panacea, though, but it is one
>> more tool in the box.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Jay" <nospam@.nospam.org> wrote in message
>> news:%236s9kxb5HHA.2752@.TK2MSFTNGP06.phx.gbl...
>> Well now. Just my first read took 10 minutes, never mind fully
>> comprehending and following the links.
>> Thank you very much for the time you spent on the reply Tibor and rest
>> assured, the time a cat would spend picking apart a fish is nothing,
>> compared to what I'm going to do with it.
>> Just a couple comments for now:
>> I grasp the topic well because I was an Informix DBA for 15 years and
>> it's the same song, just a different tune.
>> I write all production destined code with strong error checks and find
>> ways to make it complain loudly is something unexpected/unwanted
>> happens (plus a once a week ping).
>> I chose INDEXDEFRAG because it is an online procedure and avoided
>> DBREINDEX because it is an offline procedure (24x7 after all). I do
>> remember a rather large discussion on this subject from about 6 months
>> ago, but it doesn't really matter, because if I get any scheduled
>> maintenance, it will be infrequent and limited.
>> Thanks,
>> Jay
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> wrote in message news:%23HYDpSb5HHA.3940@.TK2MSFTNGP05.phx.gbl...
>> It certainly seems you grasp the topic well. Some comments:
>> Write a single stored procedure with an outer loop on DB's from
>> master..sysdatabases
>> Watch out for exceptions. What happens if for instance CHECKDB
>> encounters a corruption? Will it terminate the batch so nothing
>> further is executed? I can't answer that offhand, so thinking this
>> through and testing is important.
>> You might want to deliberately crash a database so that CHEDB fails to
>> test this realistically. I usually hack sysindexes (modify for
>> instance the FirstIAM column for some row to some rubbish value) - in
>> a test database of course. You aren't allowed to update system tables
>> directly by default but read about sp_configure and you will probably
>> figure out how to do it (I don't want to post any more descriptive
>> instructions considering this is a public forum).
>> Also, consider the client application handling of exceptions. SQL
>> Server Agent is nice, but a TSQL job step will actually terminate the
>> execution on any error (as I recall it). So, you might want to
>> schedule a CmdExec job step instead and call OSQL.EXE for the
>> execution of your proc.
>>
>> The only possible arguments I'm considering are NOINDEX and WITH
>> PHYSICAL_ONLY, but will probably go with no arguments.
>> Yes, go with as complete check as you can live with. If that doesn't
>> work for you use any of the options, possibly conditionally based on
>> for instance week day (like do full every Sunday). Btw, here's a
>> *great* blog on CHECKDB and related topics:
>> http://blogs.msdn.com/sqlserverstorageengine/default.aspx
>>
>> Within the loop, after a success on the CHECKDB (failure would exit
>> the loop and complain) run a script based on the one in 2000 BOL
>> (DBCC SHOWCONTIG) that does an INDEXDEFRAG on the index (BOL says
>> this is an online operation).
>> Yep, sounds fine. I guess you could spend lots of time with analyzing
>> how much fragmentation really hurts you at the table/index level and
>> have conditional handling based on that, but that is probably
>> overkill. Same goes for the fillfactor value. You can't specify a
>> fillfactor value for INDEXDEFRAG, it will re-apply the value you
>> specified when you created the index. If you want to read up on this
>> topic, check out
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx.
>> Also, be aware of implications of defragging regarding transaction
>> logging too (so you don't be surprised that the following log backup
>> is potentially huge). And read up on recovery models so you understand
>> all three.
>>
>> Last, run the SQL from BOL to loop on when statistics were last
>> updated:
>> and update any old statistics.
>> First, be aware that if you rebuild (DBREINDEX) your indexes then the
>> statistics that comes with the index will also be rebuilt. This does
>> not happen if you INDEXDEFRAG, though.
>> Also, the database setting "auto update statistics" means that SQL
>> Server *will update statistics by itself*, as they becomes stale. Now,
>> doing a manual update doesn't hurt, especially if you have the time to
>> use FULLSCAN (see UPDATE STATISTICS command). In most cases, this
>> database setting should be left to the default value. SQL Server uses
>> a value in sysindexes, rowmodctr (I believe) to determine how many
>> modifications and whether it is time to auto-update (when a plan is to
>> be re-used or generated). This is a rather big topic, so here comes
>> another WP:
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
>> (there is one for 2000 as well, I don't have that URL handy, but this
>> is readable even if you aren't on 2005).
>> Note that the auto-create statistics option isn't the same as
>> auto-update. Auto-create means that the optimizer will *create*
>> statistics on non-indexed columns when it feels that such statistics
>> would be useful. Such statistics are also auto-updated by the engine
>> by the same handling as described above (the auto-update setting).
>> Finally, instead of having this loop, consider creating a table, in
>> which you have a row for each database, and here you configure what
>> actions are to be performed. Then you loop that table instead of
>> sysdatabases. I'm not saying it is better, all depends on ones
>> particular requirements etc.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Jay" <nospam@.nospam.org> wrote in message
>> news:ubj7n7a5HHA.3900@.TK2MSFTNGP02.phx.gbl...
>>I wasn't able to find much with an internet search, but digging in BOL
>>I found a lot.
>> Is this a good plan for 24x7 SQL Server 2000 maintenance (excluding
>> backup)?
>> Am I missing anything?
>> Am I doing more than is needed?
>> Did I miss something in BOL that makes this plan risky?
>> Here is my intended plan: write a procedure that uses CHECKDB,
>> SHOWCONTIG, INDEXDEFRAG, STATS_DATE & UPDATE STATISTICS. It would be
>> run either before, or after the application maintenance (archiving,
>> credit card batches, search engine data dumps, etc).
>> At no time will I allow an automated program shrink anything in the
>> Windows filesystem (and I hope I can keep anything from Auto Growing
>> too).
>> Write a single stored procedure with an outer loop on DB's from
>> master..sysdatabases
>> Start with DBCC CHECKDB('dbname') looping through all databases.
>> According to BOL, if you don't specify any of the check options,
>> nothing is locked and on the dev server, it is taking about 2-3
>> minutes to complete. The only possible arguments I'm considering are
>> NOINDEX and WITH PHYSICAL_ONLY, but will probably go with no
>> arguments.
>> Within the loop, after a success on the CHECKDB (failure would exit
>> the loop and complain) run a script based on the one in 2000 BOL
>> (DBCC SHOWCONTIG) that does an INDEXDEFRAG on the index (BOL says
>> this is an online operation).
>> Last, run the SQL from BOL to loop on when statistics were last
>> updated:
>> SELECT 'Index Name' = i.name,
>> 'Statistics Date' = STATS_DATE(i.id, i.indid)
>> FROM sysobjects o, sysindexes i
>> WHERE o.name = '_table_name_'
>> AND o.id = i.id
>> and update any old statistics.
>> Done.
>> Now, I'm not really sure how to decide what statistics are old. Under
>> Informix I had a counter that measured insert/update activity since
>> the last statistics update. Perhaps just do everything nightly?
>> I'm also not sure how the "Auto Create Statistics" checkbox on the
>> database prosperities/options tab interacts with update statistics.
>> Thanks,
>> Jay
>>
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> wrote in message news:OmJUAXY5HHA.4712@.TK2MSFTNGP04.phx.gbl...
>>> SQL Server 2000
>>> Also, in Management Studio the maintenance plans are under a Legacy
>>> heading and I haven't seen where maintenance is defined in 2005.
>>>
>>> Seems you are using SSMS to connect to a 2000 instance. There are no
>>> "new style" MP's on 2000, since they didn't exist in ... 2000. So
>>> perhaps SSMS are showing 2000 (old style) MP's under the legacy
>>> folder, quite simply?
>>>
>>> Be care full especially with rebuilding indexes. Rebuilding an index
>>> (the way 2000 MP's does it) will log the table during the index
>>> rebuild. Also, DBCC CHECKDB will use resources (I'm not sure about
>>> what level of locking it is using in 2000, but that should be
>>> documented in the 2000 Books Online).
>>>
>>> I believe that there's an "Operational Guideline" for 2000 available
>>> on the MS web. It could be worth spending a few minutes with Google
>>> to try to dig it up...
>>>
>>> --
>>> Tibor Karaszi, SQL Server MVP
>>> http://www.karaszi.com/sqlserver/default.asp
>>> http://sqlblog.com/blogs/tibor_karaszi
>>>
>>>
>>> "Jay" <nospam@.nospam.org> wrote in message
>>> news:%23kB0DrQ5HHA.3900@.TK2MSFTNGP02.phx.gbl...
>>> SQL Server 2000
>>>
>>> I have finally been tasked and given access to the maintenance
>>> plans ... only there aren't any (including disk defrags)! Backups
>>> are done via an SQLagent job, but that's it.
>>>
>>> Normally I would just whomp the defaults together and start them
>>> running, but this is a 24x7x365 system and I'm not sure which of
>>> the jobs have the potential to lock a table, or an index.
>>>
>>> Advice, or links to docs that concern maintenance on a 24x7x365
>>> system.
>>>
>>> Also, in Management Studio the maintenance plans are under a Legacy
>>> heading and I haven't seen where maintenance is defined in 2005.
>>> What's up with that? Do I just need to dig deeper?
>>>
>>> Thanks,
>>> Jay
>>>
>>>
>>
>>
>>
>>
>|||> On a side note, under Informix I was a big fan of raw partitions
You might or might not know that raw partitions are also available for SQL Server.
SQL Servers Unix heritage of SQL Server (Sybase), and before the "new architecture" we first had to
create "disk devices" and over these then create (allocate) the database. This was a bit of a mess,
but I think that the architecture might have made more sense in other OSs than Windows.
As of the new architecture (7.0), all this mess was simplified into what we have now. One drawback
is that the simplification allows for easier management, autogrow and shrink and as most nice
features, they can me miss- or over- used. And this is what we often see in the SQL Server world. So
what more experienced DBA does, on reasonably and large sized databases, are pre-allocate. No
surprise, I'm sure.
Even though raw partitions are available for SQL Server, I don't think I've even encountered anybody
using them. I vaguely recall some benchmark, talking about some 2-3 percent, but I believe that this
depends much on type of load, and also my memory might not serve me here. Perhaps raw partitions are
used in some of the benchmarks you see "out there", like TPC-C. Come to think about it, the vendors
really want to squeeze out the extreme for such benchmarks. So if you want to take the time and
study a few and *don't* see them using raw, you can be reasonably certain that it isn't worth even
remotely considering.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <nospam@.nospam.org> wrote in message news:%23oYl7jm5HHA.3916@.TK2MSFTNGP02.phx.gbl...
> Oh, I'll read the link later, but I knew your opinion (which mirrors my own) even before seeing
> the name of the asp page.
> On a side note, under Informix I was a big fan of raw partitions, as you gained about 5% (or more)
> in performance (almost for free) and none of the convenience issues in Windows were present
> (detach mainly). Also, there were load balancing things you could do that would be very similar to
> defining DPV's on one instance (the clause is named FRAGMENT BY - Yuck! But the functionality is
> FANTASTIC!).
> Anyway, this provides a fixed amount of drive space allocated to the database engine and
> growing/shrinking becomes a non-issue.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:OhZrxUi5HHA.1184@.TK2MSFTNGP04.phx.gbl...
>> On the Wizard it says: "Update data optimization",
>> I've never thought about those words in the wizard. The wizard exposes three options. Translated
>> into TSQL commands (top to bottom):
>> DBCC DBREINDEX
>> UPDATE STATISTICS
>> DBCC SHRINKDB See http://www.karaszi.com/SQLServer/info_dont_shrink.asp to find out what I think
>> of this one.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Jay" <nospam@.nospam.org> wrote in message news:OJX$RTc5HHA.1992@.TK2MSFTNGP03.phx.gbl...
>> One point just hit me.
>> On the Wizard it says: "Update data optimization", but the routines I've found only deal with
>> indexes and statistics. Is this correct, or did I miss one? Or, are the statistics the data
>> portion?
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> news:uQpth3b5HHA.464@.TK2MSFTNGP02.phx.gbl...
>> Thank you very much for the time you spent on the reply Tibor and rest assured, the time a cat
>> would spend picking apart a fish is nothing, compared to what I'm going to do with it.
>> You're welcome. The links are good reading, so do check them when you have the time.
>> I grasp the topic well because I was an Informix DBA for 15 years and it's the same song, just
>> a different tune.
>> Yes, I figured that from your earlier reply and the follow-up questions etc you had. Having
>> that background, I doubt you will have difficulties with the links I posted.
>>
>> I chose INDEXDEFRAG because it is an online procedure and avoided DBREINDEX because it is an
>> offline procedure (24x7 after all).
>> I figured that. I mostly wanted to mention the transaction logging aspect and differences
>> between the two, in conjunction with the recovery model. Often overlooked. When you go to 2005,
>> you can play with the ONLINE option for ALTER INDEX REBUILD (same as DBREINDEX in 2000). For
>> instance reorganize if you have fragmentation less than say 30%, else rebuild. This new ONLINE
>> option isn't a panacea, though, but it is one more tool in the box.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Jay" <nospam@.nospam.org> wrote in message news:%236s9kxb5HHA.2752@.TK2MSFTNGP06.phx.gbl...
>> Well now. Just my first read took 10 minutes, never mind fully comprehending and following the
>> links.
>> Thank you very much for the time you spent on the reply Tibor and rest assured, the time a cat
>> would spend picking apart a fish is nothing, compared to what I'm going to do with it.
>> Just a couple comments for now:
>> I grasp the topic well because I was an Informix DBA for 15 years and it's the same song, just
>> a different tune.
>> I write all production destined code with strong error checks and find ways to make it
>> complain loudly is something unexpected/unwanted happens (plus a once a week ping).
>> I chose INDEXDEFRAG because it is an online procedure and avoided DBREINDEX because it is an
>> offline procedure (24x7 after all). I do remember a rather large discussion on this subject
>> from about 6 months ago, but it doesn't really matter, because if I get any scheduled
>> maintenance, it will be infrequent and limited.
>> Thanks,
>> Jay
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> news:%23HYDpSb5HHA.3940@.TK2MSFTNGP05.phx.gbl...
>> It certainly seems you grasp the topic well. Some comments:
>>> Write a single stored procedure with an outer loop on DB's from master..sysdatabases
>> Watch out for exceptions. What happens if for instance CHECKDB encounters a corruption? Will
>> it terminate the batch so nothing further is executed? I can't answer that offhand, so
>> thinking this through and testing is important.
>> You might want to deliberately crash a database so that CHEDB fails to test this
>> realistically. I usually hack sysindexes (modify for instance the FirstIAM column for some
>> row to some rubbish value) - in a test database of course. You aren't allowed to update
>> system tables directly by default but read about sp_configure and you will probably figure
>> out how to do it (I don't want to post any more descriptive instructions considering this is
>> a public forum).
>> Also, consider the client application handling of exceptions. SQL Server Agent is nice, but a
>> TSQL job step will actually terminate the execution on any error (as I recall it). So, you
>> might want to schedule a CmdExec job step instead and call OSQL.EXE for the execution of your
>> proc.
>>
>>> The only possible arguments I'm considering are NOINDEX and WITH PHYSICAL_ONLY, but will
>>> probably go with no arguments.
>> Yes, go with as complete check as you can live with. If that doesn't work for you use any of
>> the options, possibly conditionally based on for instance week day (like do full every
>> Sunday). Btw, here's a *great* blog on CHECKDB and related topics:
>> http://blogs.msdn.com/sqlserverstorageengine/default.aspx
>>
>>> Within the loop, after a success on the CHECKDB (failure would exit the loop and complain)
>>> run a script based on the one in 2000 BOL (DBCC SHOWCONTIG) that does an INDEXDEFRAG on the
>>> index (BOL says this is an online operation).
>> Yep, sounds fine. I guess you could spend lots of time with analyzing how much fragmentation
>> really hurts you at the table/index level and have conditional handling based on that, but
>> that is probably overkill. Same goes for the fillfactor value. You can't specify a fillfactor
>> value for INDEXDEFRAG, it will re-apply the value you specified when you created the index.
>> If you want to read up on this topic, check out
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx. Also, be aware
>> of implications of defragging regarding transaction logging too (so you don't be surprised
>> that the following log backup is potentially huge). And read up on recovery models so you
>> understand all three.
>>
>>> Last, run the SQL from BOL to loop on when statistics were last updated:
>>> and update any old statistics.
>> First, be aware that if you rebuild (DBREINDEX) your indexes then the statistics that comes
>> with the index will also be rebuilt. This does not happen if you INDEXDEFRAG, though.
>> Also, the database setting "auto update statistics" means that SQL Server *will update
>> statistics by itself*, as they becomes stale. Now, doing a manual update doesn't hurt,
>> especially if you have the time to use FULLSCAN (see UPDATE STATISTICS command). In most
>> cases, this database setting should be left to the default value. SQL Server uses a value in
>> sysindexes, rowmodctr (I believe) to determine how many modifications and whether it is time
>> to auto-update (when a plan is to be re-used or generated). This is a rather big topic, so
>> here comes another WP: http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
>> (there is one for 2000 as well, I don't have that URL handy, but this is readable even if you
>> aren't on 2005).
>> Note that the auto-create statistics option isn't the same as auto-update. Auto-create means
>> that the optimizer will *create* statistics on non-indexed columns when it feels that such
>> statistics would be useful. Such statistics are also auto-updated by the engine by the same
>> handling as described above (the auto-update setting).
>> Finally, instead of having this loop, consider creating a table, in which you have a row for
>> each database, and here you configure what actions are to be performed. Then you loop that
>> table instead of sysdatabases. I'm not saying it is better, all depends on ones particular
>> requirements etc.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Jay" <nospam@.nospam.org> wrote in message news:ubj7n7a5HHA.3900@.TK2MSFTNGP02.phx.gbl...
>>>I wasn't able to find much with an internet search, but digging in BOL I found a lot.
>>>
>>> Is this a good plan for 24x7 SQL Server 2000 maintenance (excluding backup)?
>>> Am I missing anything?
>>> Am I doing more than is needed?
>>> Did I miss something in BOL that makes this plan risky?
>>>
>>> Here is my intended plan: write a procedure that uses CHECKDB, SHOWCONTIG, INDEXDEFRAG,
>>> STATS_DATE & UPDATE STATISTICS. It would be run either before, or after the application
>>> maintenance (archiving, credit card batches, search engine data dumps, etc).
>>>
>>> At no time will I allow an automated program shrink anything in the Windows filesystem (and
>>> I hope I can keep anything from Auto Growing too).
>>>
>>> Write a single stored procedure with an outer loop on DB's from master..sysdatabases
>>>
>>> Start with DBCC CHECKDB('dbname') looping through all databases. According to BOL, if you
>>> don't specify any of the check options, nothing is locked and on the dev server, it is
>>> taking about 2-3 minutes to complete. The only possible arguments I'm considering are
>>> NOINDEX and WITH PHYSICAL_ONLY, but will probably go with no arguments.
>>>
>>> Within the loop, after a success on the CHECKDB (failure would exit the loop and complain)
>>> run a script based on the one in 2000 BOL (DBCC SHOWCONTIG) that does an INDEXDEFRAG on the
>>> index (BOL says this is an online operation).
>>>
>>> Last, run the SQL from BOL to loop on when statistics were last updated:
>>>
>>> SELECT 'Index Name' = i.name,
>>> 'Statistics Date' = STATS_DATE(i.id, i.indid)
>>> FROM sysobjects o, sysindexes i
>>> WHERE o.name = '_table_name_'
>>> AND o.id = i.id
>>>
>>> and update any old statistics.
>>>
>>> Done.
>>>
>>> Now, I'm not really sure how to decide what statistics are old. Under Informix I had a
>>> counter that measured insert/update activity since the last statistics update. Perhaps just
>>> do everything nightly?
>>>
>>> I'm also not sure how the "Auto Create Statistics" checkbox on the database
>>> prosperities/options tab interacts with update statistics.
>>>
>>> Thanks,
>>> Jay
>>>
>>>
>>>
>>>
>>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>>> news:OmJUAXY5HHA.4712@.TK2MSFTNGP04.phx.gbl...
>>> SQL Server 2000
>>> Also, in Management Studio the maintenance plans are under a Legacy heading and I haven't
>>> seen where maintenance is defined in 2005.
>>>
>>> Seems you are using SSMS to connect to a 2000 instance. There are no "new style" MP's on
>>> 2000, since they didn't exist in ... 2000. So perhaps SSMS are showing 2000 (old style)
>>> MP's under the legacy folder, quite simply?
>>>
>>> Be care full especially with rebuilding indexes. Rebuilding an index (the way 2000 MP's
>>> does it) will log the table during the index rebuild. Also, DBCC CHECKDB will use resources
>>> (I'm not sure about what level of locking it is using in 2000, but that should be
>>> documented in the 2000 Books Online).
>>>
>>> I believe that there's an "Operational Guideline" for 2000 available on the MS web. It
>>> could be worth spending a few minutes with Google to try to dig it up...
>>>
>>> --
>>> Tibor Karaszi, SQL Server MVP
>>> http://www.karaszi.com/sqlserver/default.asp
>>> http://sqlblog.com/blogs/tibor_karaszi
>>>
>>>
>>> "Jay" <nospam@.nospam.org> wrote in message news:%23kB0DrQ5HHA.3900@.TK2MSFTNGP02.phx.gbl...
>>> SQL Server 2000
>>>
>>> I have finally been tasked and given access to the maintenance plans ... only there aren't
>>> any (including disk defrags)! Backups are done via an SQLagent job, but that's it.
>>>
>>> Normally I would just whomp the defaults together and start them running, but this is a
>>> 24x7x365 system and I'm not sure which of the jobs have the potential to lock a table, or
>>> an index.
>>>
>>> Advice, or links to docs that concern maintenance on a 24x7x365 system.
>>>
>>> Also, in Management Studio the maintenance plans are under a Legacy heading and I haven't
>>> seen where maintenance is defined in 2005. What's up with that? Do I just need to dig
>>> deeper?
>>>
>>> Thanks,
>>> Jay
>>>
>>>
>>>
>>>
>>
>>
>>
>|||Ya, I was aware of SQL Server raw partitions and didn't feel particularly
enthused about them, not sure why.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23IWwHvm5HHA.536@.TK2MSFTNGP06.phx.gbl...
>> On a side note, under Informix I was a big fan of raw partitions
> You might or might not know that raw partitions are also available for SQL
> Server.
> SQL Servers Unix heritage of SQL Server (Sybase), and before the "new
> architecture" we first had to create "disk devices" and over these then
> create (allocate) the database. This was a bit of a mess, but I think that
> the architecture might have made more sense in other OSs than Windows.
> As of the new architecture (7.0), all this mess was simplified into what
> we have now. One drawback is that the simplification allows for easier
> management, autogrow and shrink and as most nice features, they can me
> miss- or over- used. And this is what we often see in the SQL Server
> world. So what more experienced DBA does, on reasonably and large sized
> databases, are pre-allocate. No surprise, I'm sure.
> Even though raw partitions are available for SQL Server, I don't think
> I've even encountered anybody using them. I vaguely recall some benchmark,
> talking about some 2-3 percent, but I believe that this depends much on
> type of load, and also my memory might not serve me here. Perhaps raw
> partitions are used in some of the benchmarks you see "out there", like
> TPC-C. Come to think about it, the vendors really want to squeeze out the
> extreme for such benchmarks. So if you want to take the time and study a
> few and *don't* see them using raw, you can be reasonably certain that it
> isn't worth even remotely considering.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jay" <nospam@.nospam.org> wrote in message
> news:%23oYl7jm5HHA.3916@.TK2MSFTNGP02.phx.gbl...
>> Oh, I'll read the link later, but I knew your opinion (which mirrors my
>> own) even before seeing the name of the asp page.
>> On a side note, under Informix I was a big fan of raw partitions, as you
>> gained about 5% (or more) in performance (almost for free) and none of
>> the convenience issues in Windows were present (detach mainly). Also,
>> there were load balancing things you could do that would be very similar
>> to defining DPV's on one instance (the clause is named FRAGMENT BY -
>> Yuck! But the functionality is FANTASTIC!).
>> Anyway, this provides a fixed amount of drive space allocated to the
>> database engine and growing/shrinking becomes a non-issue.
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:OhZrxUi5HHA.1184@.TK2MSFTNGP04.phx.gbl...
>> On the Wizard it says: "Update data optimization",
>> I've never thought about those words in the wizard. The wizard exposes
>> three options. Translated into TSQL commands (top to bottom):
>> DBCC DBREINDEX
>> UPDATE STATISTICS
>> DBCC SHRINKDB See http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>> to find out what I think of this one.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Jay" <nospam@.nospam.org> wrote in message
>> news:OJX$RTc5HHA.1992@.TK2MSFTNGP03.phx.gbl...
>> One point just hit me.
>> On the Wizard it says: "Update data optimization", but the routines
>> I've found only deal with indexes and statistics. Is this correct, or
>> did I miss one? Or, are the statistics the data portion?
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> wrote in message news:uQpth3b5HHA.464@.TK2MSFTNGP02.phx.gbl...
>> Thank you very much for the time you spent on the reply Tibor and
>> rest assured, the time a cat would spend picking apart a fish is
>> nothing, compared to what I'm going to do with it.
>> You're welcome. The links are good reading, so do check them when you
>> have the time.
>> I grasp the topic well because I was an Informix DBA for 15 years and
>> it's the same song, just a different tune.
>> Yes, I figured that from your earlier reply and the follow-up
>> questions etc you had. Having that background, I doubt you will have
>> difficulties with the links I posted.
>>
>> I chose INDEXDEFRAG because it is an online procedure and avoided
>> DBREINDEX because it is an offline procedure (24x7 after all).
>> I figured that. I mostly wanted to mention the transaction logging
>> aspect and differences between the two, in conjunction with the
>> recovery model. Often overlooked. When you go to 2005, you can play
>> with the ONLINE option for ALTER INDEX REBUILD (same as DBREINDEX in
>> 2000). For instance reorganize if you have fragmentation less than say
>> 30%, else rebuild. This new ONLINE option isn't a panacea, though, but
>> it is one more tool in the box.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Jay" <nospam@.nospam.org> wrote in message
>> news:%236s9kxb5HHA.2752@.TK2MSFTNGP06.phx.gbl...
>> Well now. Just my first read took 10 minutes, never mind fully
>> comprehending and following the links.
>> Thank you very much for the time you spent on the reply Tibor and
>> rest assured, the time a cat would spend picking apart a fish is
>> nothing, compared to what I'm going to do with it.
>> Just a couple comments for now:
>> I grasp the topic well because I was an Informix DBA for 15 years and
>> it's the same song, just a different tune.
>> I write all production destined code with strong error checks and
>> find ways to make it complain loudly is something unexpected/unwanted
>> happens (plus a once a week ping).
>> I chose INDEXDEFRAG because it is an online procedure and avoided
>> DBREINDEX because it is an offline procedure (24x7 after all). I do
>> remember a rather large discussion on this subject from about 6
>> months ago, but it doesn't really matter, because if I get any
>> scheduled maintenance, it will be infrequent and limited.
>> Thanks,
>> Jay
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> wrote in message news:%23HYDpSb5HHA.3940@.TK2MSFTNGP05.phx.gbl...
>>> It certainly seems you grasp the topic well. Some comments:
>>>
>>> Write a single stored procedure with an outer loop on DB's from
>>> master..sysdatabases
>>>
>>> Watch out for exceptions. What happens if for instance CHECKDB
>>> encounters a corruption? Will it terminate the batch so nothing
>>> further is executed? I can't answer that offhand, so thinking this
>>> through and testing is important.
>>>
>>> You might want to deliberately crash a database so that CHEDB fails
>>> to test this realistically. I usually hack sysindexes (modify for
>>> instance the FirstIAM column for some row to some rubbish value) -
>>> in a test database of course. You aren't allowed to update system
>>> tables directly by default but read about sp_configure and you will
>>> probably figure out how to do it (I don't want to post any more
>>> descriptive instructions considering this is a public forum).
>>>
>>> Also, consider the client application handling of exceptions. SQL
>>> Server Agent is nice, but a TSQL job step will actually terminate
>>> the execution on any error (as I recall it). So, you might want to
>>> schedule a CmdExec job step instead and call OSQL.EXE for the
>>> execution of your proc.
>>>
>>>
>>> The only possible arguments I'm considering are NOINDEX and WITH
>>> PHYSICAL_ONLY, but will probably go with no arguments.
>>>
>>> Yes, go with as complete check as you can live with. If that doesn't
>>> work for you use any of the options, possibly conditionally based on
>>> for instance week day (like do full every Sunday). Btw, here's a
>>> *great* blog on CHECKDB and related topics:
>>> http://blogs.msdn.com/sqlserverstorageengine/default.aspx
>>>
>>>
>>> Within the loop, after a success on the CHECKDB (failure would exit
>>> the loop and complain) run a script based on the one in 2000 BOL
>>> (DBCC SHOWCONTIG) that does an INDEXDEFRAG on the index (BOL says
>>> this is an online operation).
>>>
>>> Yep, sounds fine. I guess you could spend lots of time with
>>> analyzing how much fragmentation really hurts you at the table/index
>>> level and have conditional handling based on that, but that is
>>> probably overkill. Same goes for the fillfactor value. You can't
>>> specify a fillfactor value for INDEXDEFRAG, it will re-apply the
>>> value you specified when you created the index. If you want to read
>>> up on this topic, check out
>>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx.
>>> Also, be aware of implications of defragging regarding transaction
>>> logging too (so you don't be surprised that the following log backup
>>> is potentially huge). And read up on recovery models so you
>>> understand all three.
>>>
>>>
>>> Last, run the SQL from BOL to loop on when statistics were last
>>> updated:
>>> and update any old statistics.
>>>
>>> First, be aware that if you rebuild (DBREINDEX) your indexes then
>>> the statistics that comes with the index will also be rebuilt. This
>>> does not happen if you INDEXDEFRAG, though.
>>> Also, the database setting "auto update statistics" means that SQL
>>> Server *will update statistics by itself*, as they becomes stale.
>>> Now, doing a manual update doesn't hurt, especially if you have the
>>> time to use FULLSCAN (see UPDATE STATISTICS command). In most cases,
>>> this database setting should be left to the default value. SQL
>>> Server uses a value in sysindexes, rowmodctr (I believe) to
>>> determine how many modifications and whether it is time to
>>> auto-update (when a plan is to be re-used or generated). This is a
>>> rather big topic, so here comes another WP:
>>> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
>>> (there is one for 2000 as well, I don't have that URL handy, but
>>> this is readable even if you aren't on 2005).
>>> Note that the auto-create statistics option isn't the same as
>>> auto-update. Auto-create means that the optimizer will *create*
>>> statistics on non-indexed columns when it feels that such statistics
>>> would be useful. Such statistics are also auto-updated by the engine
>>> by the same handling as described above (the auto-update setting).
>>>
>>> Finally, instead of having this loop, consider creating a table, in
>>> which you have a row for each database, and here you configure what
>>> actions are to be performed. Then you loop that table instead of
>>> sysdatabases. I'm not saying it is better, all depends on ones
>>> particular requirements etc.
>>> --
>>> Tibor Karaszi, SQL Server MVP
>>> http://www.karaszi.com/sqlserver/default.asp
>>> http://sqlblog.com/blogs/tibor_karaszi
>>>
>>>
>>> "Jay" <nospam@.nospam.org> wrote in message
>>> news:ubj7n7a5HHA.3900@.TK2MSFTNGP02.phx.gbl...
>>>I wasn't able to find much with an internet search, but digging in
>>>BOL I found a lot.
>>>
>>> Is this a good plan for 24x7 SQL Server 2000 maintenance (excluding
>>> backup)?
>>> Am I missing anything?
>>> Am I doing more than is needed?
>>> Did I miss something in BOL that makes this plan risky?
>>>
>>> Here is my intended plan: write a procedure that uses CHECKDB,
>>> SHOWCONTIG, INDEXDEFRAG, STATS_DATE & UPDATE STATISTICS. It would
>>> be run either before, or after the application maintenance
>>> (archiving, credit card batches, search engine data dumps, etc).
>>>
>>> At no time will I allow an automated program shrink anything in the
>>> Windows filesystem (and I hope I can keep anything from Auto
>>> Growing too).
>>>
>>> Write a single stored procedure with an outer loop on DB's from
>>> master..sysdatabases
>>>
>>> Start with DBCC CHECKDB('dbname') looping through all databases.
>>> According to BOL, if you don't specify any of the check options,
>>> nothing is locked and on the dev server, it is taking about 2-3
>>> minutes to complete. The only possible arguments I'm considering
>>> are NOINDEX and WITH PHYSICAL_ONLY, but will probably go with no
>>> arguments.
>>>
>>> Within the loop, after a success on the CHECKDB (failure would exit
>>> the loop and complain) run a script based on the one in 2000 BOL
>>> (DBCC SHOWCONTIG) that does an INDEXDEFRAG on the index (BOL says
>>> this is an online operation).
>>>
>>> Last, run the SQL from BOL to loop on when statistics were last
>>> updated:
>>>
>>> SELECT 'Index Name' = i.name,
>>> 'Statistics Date' = STATS_DATE(i.id, i.indid)
>>> FROM sysobjects o, sysindexes i
>>> WHERE o.name = '_table_name_'
>>> AND o.id = i.id
>>>
>>> and update any old statistics.
>>>
>>> Done.
>>>
>>> Now, I'm not really sure how to decide what statistics are old.
>>> Under Informix I had a counter that measured insert/update activity
>>> since the last statistics update. Perhaps just do everything
>>> nightly?
>>>
>>> I'm also not sure how the "Auto Create Statistics" checkbox on the
>>> database prosperities/options tab interacts with update statistics.
>>>
>>> Thanks,
>>> Jay
>>>
>>>
>>>
>>>
>>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>>> wrote in message news:OmJUAXY5HHA.4712@.TK2MSFTNGP04.phx.gbl...
>>>> SQL Server 2000
>>>> Also, in Management Studio the maintenance plans are under a
>>>> Legacy heading and I haven't seen where maintenance is defined in
>>>> 2005.
>>>
>>> Seems you are using SSMS to connect to a 2000 instance. There are
>>> no "new style" MP's on 2000, since they didn't exist in ... 2000.
>>> So perhaps SSMS are showing 2000 (old style) MP's under the legacy
>>> folder, quite simply?
>>>
>>> Be care full especially with rebuilding indexes. Rebuilding an
>>> index (the way 2000 MP's does it) will log the table during the
>>> index rebuild. Also, DBCC CHECKDB will use resources (I'm not sure
>>> about what level of locking it is using in 2000, but that should
>>> be documented in the 2000 Books Online).
>>>
>>> I believe that there's an "Operational Guideline" for 2000
>>> available on the MS web. It could be worth spending a few minutes
>>> with Google to try to dig it up...
>>>
>>> --
>>> Tibor Karaszi, SQL Server MVP
>>> http://www.karaszi.com/sqlserver/default.asp
>>> http://sqlblog.com/blogs/tibor_karaszi
>>>
>>>
>>> "Jay" <nospam@.nospam.org> wrote in message
>>> news:%23kB0DrQ5HHA.3900@.TK2MSFTNGP02.phx.gbl...
>>>> SQL Server 2000
>>>>
>>>> I have finally been tasked and given access to the maintenance
>>>> plans ... only there aren't any (including disk defrags)! Backups
>>>> are done via an SQLagent job, but that's it.
>>>>
>>>> Normally I would just whomp the defaults together and start them
>>>> running, but this is a 24x7x365 system and I'm not sure which of
>>>> the jobs have the potential to lock a table, or an index.
>>>>
>>>> Advice, or links to docs that concern maintenance on a 24x7x365
>>>> system.
>>>>
>>>> Also, in Management Studio the maintenance plans are under a
>>>> Legacy heading and I haven't seen where maintenance is defined in
>>>> 2005. What's up with that? Do I just need to dig deeper?
>>>>
>>>> Thanks,
>>>> Jay
>>>>
>>>
>>>
>>>
>>>
>>
>>
>>
>>
>|||Oh my, I hit a snag right out of the gate.
I wanted to execute DBCC CHECKDB and add program logic to determine
success/failure as I recalled code that put DBCC results into a temp table.
Oops, no TABLERESULTS option. So, the messages are always displayed and I
can't get them into a data structure.
When I change the database name to something that doesn't exist, the
procedure aborts (is this what the OSQL was for?) and I never get to the
line 'PRINT @.@.ERROR'.
I see the @.@.ERROR variable, but expected a SET option to change the default
behavior of immediately aborting - can't find anything.
Just a reminder: 2000.
"Jay" <nospam@.nospam.org> wrote in message
news:%236s9kxb5HHA.2752@.TK2MSFTNGP06.phx.gbl...
> Well now. Just my first read took 10 minutes, never mind fully
> comprehending and following the links.
> Thank you very much for the time you spent on the reply Tibor and rest
> assured, the time a cat would spend picking apart a fish is nothing,
> compared to what I'm going to do with it.
> Just a couple comments for now:
> I grasp the topic well because I was an Informix DBA for 15 years and it's
> the same song, just a different tune.
> I write all production destined code with strong error checks and find
> ways to make it complain loudly is something unexpected/unwanted happens
> (plus a once a week ping).
> I chose INDEXDEFRAG because it is an online procedure and avoided
> DBREINDEX because it is an offline procedure (24x7 after all). I do
> remember a rather large discussion on this subject from about 6 months
> ago, but it doesn't really matter, because if I get any scheduled
> maintenance, it will be infrequent and limited.
> Thanks,
> Jay
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:%23HYDpSb5HHA.3940@.TK2MSFTNGP05.phx.gbl...
>> It certainly seems you grasp the topic well. Some comments:
>> Write a single stored procedure with an outer loop on DB's from
>> master..sysdatabases
>> Watch out for exceptions. What happens if for instance CHECKDB encounters
>> a corruption? Will it terminate the batch so nothing further is executed?
>> I can't answer that offhand, so thinking this through and testing is
>> important.
>> You might want to deliberately crash a database so that CHEDB fails to
>> test this realistically. I usually hack sysindexes (modify for instance
>> the FirstIAM column for some row to some rubbish value) - in a test
>> database of course. You aren't allowed to update system tables directly
>> by default but read about sp_configure and you will probably figure out
>> how to do it (I don't want to post any more descriptive instructions
>> considering this is a public forum).
>> Also, consider the client application handling of exceptions. SQL Server
>> Agent is nice, but a TSQL job step will actually terminate the execution
>> on any error (as I recall it). So, you might want to schedule a CmdExec
>> job step instead and call OSQL.EXE for the execution of your proc.
>>
>> The only possible arguments I'm considering are NOINDEX and WITH
>> PHYSICAL_ONLY, but will probably go with no arguments.
>> Yes, go with as complete check as you can live with. If that doesn't work
>> for you use any of the options, possibly conditionally based on for
>> instance week day (like do full every Sunday). Btw, here's a *great* blog
>> on CHECKDB and related topics:
>> http://blogs.msdn.com/sqlserverstorageengine/default.aspx
>>
>> Within the loop, after a success on the CHECKDB (failure would exit the
>> loop and complain) run a script based on the one in 2000 BOL (DBCC
>> SHOWCONTIG) that does an INDEXDEFRAG on the index (BOL says this is an
>> online operation).
>> Yep, sounds fine. I guess you could spend lots of time with analyzing how
>> much fragmentation really hurts you at the table/index level and have
>> conditional handling based on that, but that is probably overkill. Same
>> goes for the fillfactor value. You can't specify a fillfactor value for
>> INDEXDEFRAG, it will re-apply the value you specified when you created
>> the index. If you want to read up on this topic, check out
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx.
>> Also, be aware of implications of defragging regarding transaction
>> logging too (so you don't be surprised that the following log backup is
>> potentially huge). And read up on recovery models so you understand all
>> three.
>>
>> Last, run the SQL from BOL to loop on when statistics were last updated:
>> and update any old statistics.
>> First, be aware that if you rebuild (DBREINDEX) your indexes then the
>> statistics that comes with the index will also be rebuilt. This does not
>> happen if you INDEXDEFRAG, though.
>> Also, the database setting "auto update statistics" means that SQL Server
>> *will update statistics by itself*, as they becomes stale. Now, doing a
>> manual update doesn't hurt, especially if you have the time to use
>> FULLSCAN (see UPDATE STATISTICS command). In most cases, this database
>> setting should be left to the default value. SQL Server uses a value in
>> sysindexes, rowmodctr (I believe) to determine how many modifications and
>> whether it is time to auto-update (when a plan is to be re-used or
>> generated). This is a rather big topic, so here comes another WP:
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx (there
>> is one for 2000 as well, I don't have that URL handy, but this is
>> readable even if you aren't on 2005).
>> Note that the auto-create statistics option isn't the same as
>> auto-update. Auto-create means that the optimizer will *create*
>> statistics on non-indexed columns when it feels that such statistics
>> would be useful. Such statistics are also auto-updated by the engine by
>> the same handling as described above (the auto-update setting).
>> Finally, instead of having this loop, consider creating a table, in which
>> you have a row for each database, and here you configure what actions are
>> to be performed. Then you loop that table instead of sysdatabases. I'm
>> not saying it is better, all depends on ones particular requirements etc.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Jay" <nospam@.nospam.org> wrote in message
>> news:ubj7n7a5HHA.3900@.TK2MSFTNGP02.phx.gbl...
>>I wasn't able to find much with an internet search, but digging in BOL I
>>found a lot.
>> Is this a good plan for 24x7 SQL Server 2000 maintenance (excluding
>> backup)?
>> Am I missing anything?
>> Am I doing more than is needed?
>> Did I miss something in BOL that makes this plan risky?
>> Here is my intended plan: write a procedure that uses CHECKDB,
>> SHOWCONTIG, INDEXDEFRAG, STATS_DATE & UPDATE STATISTICS. It would be run
>> either before, or after the application maintenance (archiving, credit
>> card batches, search engine data dumps, etc).
>> At no time will I allow an automated program shrink anything in the
>> Windows filesystem (and I hope I can keep anything from Auto Growing
>> too).
>> Write a single stored procedure with an outer loop on DB's from
>> master..sysdatabases
>> Start with DBCC CHECKDB('dbname') looping through all databases.
>> According to BOL, if you don't specify any of the check options, nothing
>> is locked and on the dev server, it is taking about 2-3 minutes to
>> complete. The only possible arguments I'm considering are NOINDEX and
>> WITH PHYSICAL_ONLY, but will probably go with no arguments.
>> Within the loop, after a success on the CHECKDB (failure would exit the
>> loop and complain) run a script based on the one in 2000 BOL (DBCC
>> SHOWCONTIG) that does an INDEXDEFRAG on the index (BOL says this is an
>> online operation).
>> Last, run the SQL from BOL to loop on when statistics were last updated:
>> SELECT 'Index Name' = i.name,
>> 'Statistics Date' = STATS_DATE(i.id, i.indid)
>> FROM sysobjects o, sysindexes i
>> WHERE o.name = '_table_name_'
>> AND o.id = i.id
>> and update any old statistics.
>> Done.
>> Now, I'm not really sure how to decide what statistics are old. Under
>> Informix I had a counter that measured insert/update activity since the
>> last statistics update. Perhaps just do everything nightly?
>> I'm also not sure how the "Auto Create Statistics" checkbox on the
>> database prosperities/options tab interacts with update statistics.
>> Thanks,
>> Jay
>>
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:OmJUAXY5HHA.4712@.TK2MSFTNGP04.phx.gbl...
>> SQL Server 2000
>> Also, in Management Studio the maintenance plans are under a Legacy
>> heading and I haven't seen where maintenance is defined in 2005.
>> Seems you are using SSMS to connect to a 2000 instance. There are no
>> "new style" MP's on 2000, since they didn't exist in ... 2000. So
>> perhaps SSMS are showing 2000 (old style) MP's under the legacy folder,
>> quite simply?
>> Be care full especially with rebuilding indexes. Rebuilding an index
>> (the way 2000 MP's does it) will log the table during the index
>> rebuild. Also, DBCC CHECKDB will use resources (I'm not sure about what
>> level of locking it is using in 2000, but that should be documented in
>> the 2000 Books Online).
>> I believe that there's an "Operational Guideline" for 2000 available on
>> the MS web. It could be worth spending a few minutes with Google to try
>> to dig it up...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Jay" <nospam@.nospam.org> wrote in message
>> news:%23kB0DrQ5HHA.3900@.TK2MSFTNGP02.phx.gbl...
>> SQL Server 2000
>> I have finally been tasked and given access to the maintenance plans
>> ... only there aren't any (including disk defrags)! Backups are done
>> via an SQLagent job, but that's it.
>> Normally I would just whomp the defaults together and start them
>> running, but this is a 24x7x365 system and I'm not sure which of the
>> jobs have the potential to lock a table, or an index.
>> Advice, or links to docs that concern maintenance on a 24x7x365
>> system.
>> Also, in Management Studio the maintenance plans are under a Legacy
>> heading and I haven't seen where maintenance is defined in 2005.
>> What's up with that? Do I just need to dig deeper?
>> Thanks,
>> Jay
>>
>>
>|||Never mind.
On a hunch I added WITH TABLERESULTS, which BOL doesn't say will work. But
it does.
"Jay" <nospam@.nospam.org> wrote in message
news:es7RLHn5HHA.5164@.TK2MSFTNGP05.phx.gbl...
> Oh my, I hit a snag right out of the gate.
> I wanted to execute DBCC CHECKDB and add program logic to determine
> success/failure as I recalled code that put DBCC results into a temp
> table. Oops, no TABLERESULTS option. So, the messages are always displayed
> and I can't get them into a data structure.
> When I change the database name to something that doesn't exist, the
> procedure aborts (is this what the OSQL was for?) and I never get to the
> line 'PRINT @.@.ERROR'.
> I see the @.@.ERROR variable, but expected a SET option to change the
> default behavior of immediately aborting - can't find anything.
> Just a reminder: 2000.
>
> "Jay" <nospam@.nospam.org> wrote in message
> news:%236s9kxb5HHA.2752@.TK2MSFTNGP06.phx.gbl...
>> Well now. Just my first read took 10 minutes, never mind fully
>> comprehending and following the links.
>> Thank you very much for the time you spent on the reply Tibor and rest
>> assured, the time a cat would spend picking apart a fish is nothing,
>> compared to what I'm going to do with it.
>> Just a couple comments for now:
>> I grasp the topic well because I was an Informix DBA for 15 years and
>> it's the same song, just a different tune.
>> I write all production destined code with strong error checks and find
>> ways to make it complain loudly is something unexpected/unwanted happens
>> (plus a once a week ping).
>> I chose INDEXDEFRAG because it is an online procedure and avoided
>> DBREINDEX because it is an offline procedure (24x7 after all). I do
>> remember a rather large discussion on this subject from about 6 months
>> ago, but it doesn't really matter, because if I get any scheduled
>> maintenance, it will be infrequent and limited.
>> Thanks,
>> Jay
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:%23HYDpSb5HHA.3940@.TK2MSFTNGP05.phx.gbl...
>> It certainly seems you grasp the topic well. Some comments:
>> Write a single stored procedure with an outer loop on DB's from
>> master..sysdatabases
>> Watch out for exceptions. What happens if for instance CHECKDB
>> encounters a corruption? Will it terminate the batch so nothing further
>> is executed? I can't answer that offhand, so thinking this through and
>> testing is important.
>> You might want to deliberately crash a database so that CHEDB fails to
>> test this realistically. I usually hack sysindexes (modify for instance
>> the FirstIAM column for some row to some rubbish value) - in a test
>> database of course. You aren't allowed to update system tables directly
>> by default but read about sp_configure and you will probably figure out
>> how to do it (I don't want to post any more descriptive instructions
>> considering this is a public forum).
>> Also, consider the client application handling of exceptions. SQL Server
>> Agent is nice, but a TSQL job step will actually terminate the execution
>> on any error (as I recall it). So, you might want to schedule a CmdExec
>> job step instead and call OSQL.EXE for the execution of your proc.
>>
>> The only possible arguments I'm considering are NOINDEX and WITH
>> PHYSICAL_ONLY, but will probably go with no arguments.
>> Yes, go with as complete check as you can live with. If that doesn't
>> work for you use any of the options, possibly conditionally based on for
>> instance week day (like do full every Sunday). Btw, here's a *great*
>> blog on CHECKDB and related topics:
>> http://blogs.msdn.com/sqlserverstorageengine/default.aspx
>>
>> Within the loop, after a success on the CHECKDB (failure would exit the
>> loop and complain) run a script based on the one in 2000 BOL (DBCC
>> SHOWCONTIG) that does an INDEXDEFRAG on the index (BOL says this is an
>> online operation).
>> Yep, sounds fine. I guess you could spend lots of time with analyzing
>> how much fragmentation really hurts you at the table/index level and
>> have conditional handling based on that, but that is probably overkill.
>> Same goes for the fillfactor value. You can't specify a fillfactor value
>> for INDEXDEFRAG, it will re-apply the value you specified when you
>> created the index. If you want to read up on this topic, check out
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx.
>> Also, be aware of implications of defragging regarding transaction
>> logging too (so you don't be surprised that the following log backup is
>> potentially huge). And read up on recovery models so you understand all
>> three.
>>
>> Last, run the SQL from BOL to loop on when statistics were last
>> updated:
>> and update any old statistics.
>> First, be aware that if you rebuild (DBREINDEX) your indexes then the
>> statistics that comes with the index will also be rebuilt. This does not
>> happen if you INDEXDEFRAG, though.
>> Also, the database setting "auto update statistics" means that SQL
>> Server *will update statistics by itself*, as they becomes stale. Now,
>> doing a manual update doesn't hurt, especially if you have the time to
>> use FULLSCAN (see UPDATE STATISTICS command). In most cases, this
>> database setting should be left to the default value. SQL Server uses a
>> value in sysindexes, rowmodctr (I believe) to determine how many
>> modifications and whether it is time to auto-update (when a plan is to
>> be re-used or generated). This is a rather big topic, so here comes
>> another WP:
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx (there
>> is one for 2000 as well, I don't have that URL handy, but this is
>> readable even if you aren't on 2005).
>> Note that the auto-create statistics option isn't the same as
>> auto-update. Auto-create means that the optimizer will *create*
>> statistics on non-indexed columns when it feels that such statistics
>> would be useful. Such statistics are also auto-updated by the engine by
>> the same handling as described above (the auto-update setting).
>> Finally, instead of having this loop, consider creating a table, in
>> which you have a row for each database, and here you configure what
>> actions are to be performed. Then you loop that table instead of
>> sysdatabases. I'm not saying it is better, all depends on ones
>> particular requirements etc.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Jay" <nospam@.nospam.org> wrote in message
>> news:ubj7n7a5HHA.3900@.TK2MSFTNGP02.phx.gbl...
>>I wasn't able to find much with an internet search, but digging in BOL I
>>found a lot.
>> Is this a good plan for 24x7 SQL Server 2000 maintenance (excluding
>> backup)?
>> Am I missing anything?
>> Am I doing more than is needed?
>> Did I miss something in BOL that makes this plan risky?
>> Here is my intended plan: write a procedure that uses CHECKDB,
>> SHOWCONTIG, INDEXDEFRAG, STATS_DATE & UPDATE STATISTICS. It would be
>> run either before, or after the application maintenance (archiving,
>> credit card batches, search engine data dumps, etc).
>> At no time will I allow an automated program shrink anything in the
>> Windows filesystem (and I hope I can keep anything from Auto Growing
>> too).
>> Write a single stored procedure with an outer loop on DB's from
>> master..sysdatabases
>> Start with DBCC CHECKDB('dbname') looping through all databases.
>> According to BOL, if you don't specify any of the check options,
>> nothing is locked and on the dev server, it is taking about 2-3 minutes
>> to complete. The only possible arguments I'm considering are NOINDEX
>> and WITH PHYSICAL_ONLY, but will probably go with no arguments.
>> Within the loop, after a success on the CHECKDB (failure would exit the
>> loop and complain) run a script based on the one in 2000 BOL (DBCC
>> SHOWCONTIG) that does an INDEXDEFRAG on the index (BOL says this is an
>> online operation).
>> Last, run the SQL from BOL to loop on when statistics were last
>> updated:
>> SELECT 'Index Name' = i.name,
>> 'Statistics Date' = STATS_DATE(i.id, i.indid)
>> FROM sysobjects o, sysindexes i
>> WHERE o.name = '_table_name_'
>> AND o.id = i.id
>> and update any old statistics.
>> Done.
>> Now, I'm not really sure how to decide what statistics are old. Under
>> Informix I had a counter that measured insert/update activity since the
>> last statistics update. Perhaps just do everything nightly?
>> I'm also not sure how the "Auto Create Statistics" checkbox on the
>> database prosperities/options tab interacts with update statistics.
>> Thanks,
>> Jay
>>
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> wrote in message news:OmJUAXY5HHA.4712@.TK2MSFTNGP04.phx.gbl...
>> SQL Server 2000
>> Also, in Management Studio the maintenance plans are under a Legacy
>> heading and I haven't seen where maintenance is defined in 2005.
>> Seems you are using SSMS to connect to a 2000 instance. There are no
>> "new style" MP's on 2000, since they didn't exist in ... 2000. So
>> perhaps SSMS are showing 2000 (old style) MP's under the legacy
>> folder, quite simply?
>> Be care full especially with rebuilding indexes. Rebuilding an index
>> (the way 2000 MP's does it) will log the table during the index
>> rebuild. Also, DBCC CHECKDB will use resources (I'm not sure about
>> what level of locking it is using in 2000, but that should be
>> documented in the 2000 Books Online).
>> I believe that there's an "Operational Guideline" for 2000 available
>> on the MS web. It could be worth spending a few minutes with Google to
>> try to dig it up...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Jay" <nospam@.nospam.org> wrote in message
>> news:%23kB0DrQ5HHA.3900@.TK2MSFTNGP02.phx.gbl...
>> SQL Server 2000
>> I have finally been tasked and given access to the maintenance plans
>> ... only there aren't any (including disk defrags)! Backups are done
>> via an SQLagent job, but that's it.
>> Normally I would just whomp the defaults together and start them
>> running, but this is a 24x7x365 system and I'm not sure which of the
>> jobs have the potential to lock a table, or an index.
>> Advice, or links to docs that concern maintenance on a 24x7x365
>> system.
>> Also, in Management Studio the maintenance plans are under a Legacy
>> heading and I haven't seen where maintenance is defined in 2005.
>> What's up with that? Do I just need to dig deeper?
>> Thanks,
>> Jay
>>
>>
>>
>|||> I wanted to execute DBCC CHECKDB and add program logic to determine success/failure as I recalled
> code that put DBCC results into a temp table. Oops, no TABLERESULTS option. So, the messages are
> always displayed and I can't get them into a data structure.
Some DBCCs has TABLERESULTS even though it isn't documented. But I'm hesitant to recommend using
that (if such exist on 2000 for DBCC CHECKDB) since if it isn't documented, then it isn't supported.
> When I change the database name to something that doesn't exist, the procedure aborts (is this
> what the OSQL was for?) and I never get to the line 'PRINT @.@.ERROR'.
Exception handling is a bit of a mess in TSQL. First, you have the engine behavior. Some errors will
terminate the batch (procedure). Not much you can do if that happens.
But, the Agent scheduler for TSQL jobsteps has some extra toppings where it terminates the job if
any error with severity > 10 (as I remember it). This last thing is what I suggested OSQL for.
Now, if you execute it from Query Analyzer and it terminates the batch, then OSQL won't help you.
But I'm surprised. Try below. On my machine, the PRINT statement *is* executed. Do you see something
different? Make sure you execute into text if you use Query Analyzer. Perhaps this is a change
between 2000 and 2005?
ALTER PROC p AS
DBCC CHECKDB('ds') WITH TABLERESULTS
PRINT 'Hello'
GO
EXEC p
No SET option, I'm afraid. In the end, you migtht be better off using a client language (like VB.NET
or C#.NET) for this, if you can't get the exception handling to your liking.
As for the API to use, DMO is designed particularly for admin, but I'm not sure you can get to all
the error messages that CHECKDB might return. At least this was the case when I used DMO many many
years ago. If that is the case, consider ADO.NET which should have something like an error colection
which you can loop through. Or whatever DB API you or some colleague of yours are most experienced
in (ODBC etc).
There are a couple of great error handling articles at http://www.sommarskog.se/.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <nospam@.nospam.org> wrote in message news:es7RLHn5HHA.5164@.TK2MSFTNGP05.phx.gbl...
> Oh my, I hit a snag right out of the gate.
> I wanted to execute DBCC CHECKDB and add program logic to determine success/failure as I recalled
> code that put DBCC results into a temp table. Oops, no TABLERESULTS option. So, the messages are
> always displayed and I can't get them into a data structure.
> When I change the database name to something that doesn't exist, the procedure aborts (is this
> what the OSQL was for?) and I never get to the line 'PRINT @.@.ERROR'.
> I see the @.@.ERROR variable, but expected a SET option to change the default behavior of
> immediately aborting - can't find anything.
> Just a reminder: 2000.
>
> "Jay" <nospam@.nospam.org> wrote in message news:%236s9kxb5HHA.2752@.TK2MSFTNGP06.phx.gbl...
>> Well now. Just my first read took 10 minutes, never mind fully comprehending and following the
>> links.
>> Thank you very much for the time you spent on the reply Tibor and rest assured, the time a cat
>> would spend picking apart a fish is nothing, compared to what I'm going to do with it.
>> Just a couple comments for now:
>> I grasp the topic well because I was an Informix DBA for 15 years and it's the same song, just a
>> different tune.
>> I write all production destined code with strong error checks and find ways to make it complain
>> loudly is something unexpected/unwanted happens (plus a once a week ping).
>> I chose INDEXDEFRAG because it is an online procedure and avoided DBREINDEX because it is an
>> offline procedure (24x7 after all). I do remember a rather large discussion on this subject from
>> about 6 months ago, but it doesn't really matter, because if I get any scheduled maintenance, it
>> will be infrequent and limited.
>> Thanks,
>> Jay
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> news:%23HYDpSb5HHA.3940@.TK2MSFTNGP05.phx.gbl...
>> It certainly seems you grasp the topic well. Some comments:
>> Write a single stored procedure with an outer loop on DB's from master..sysdatabases
>> Watch out for exceptions. What happens if for instance CHECKDB encounters a corruption? Will it
>> terminate the batch so nothing further is executed? I can't answer that offhand, so thinking
>> this through and testing is important.
>> You might want to deliberately crash a database so that CHEDB fails to test this realistically.
>> I usually hack sysindexes (modify for instance the FirstIAM column for some row to some rubbish
>> value) - in a test database of course. You aren't allowed to update system tables directly by
>> default but read about sp_configure and you will probably figure out how to do it (I don't want
>> to post any more descriptive instructions considering this is a public forum).
>> Also, consider the client application handling of exceptions. SQL Server Agent is nice, but a
>> TSQL job step will actually terminate the execution on any error (as I recall it). So, you might
>> want to schedule a CmdExec job step instead and call OSQL.EXE for the execution of your proc.
>>
>> The only possible arguments I'm considering are NOINDEX and WITH PHYSICAL_ONLY, but will
>> probably go with no arguments.
>> Yes, go with as complete check as you can live with. If that doesn't work for you use any of the
>> options, possibly conditionally based on for instance week day (like do full every Sunday). Btw,
>> here's a *great* blog on CHECKDB and related topics:
>> http://blogs.msdn.com/sqlserverstorageengine/default.aspx
>>
>> Within the loop, after a success on the CHECKDB (failure would exit the loop and complain) run
>> a script based on the one in 2000 BOL (DBCC SHOWCONTIG) that does an INDEXDEFRAG on the index
>> (BOL says this is an online operation).
>> Yep, sounds fine. I guess you could spend lots of time with analyzing how much fragmentation
>> really hurts you at the table/index level and have conditional handling based on that, but that
>> is probably overkill. Same goes for the fillfactor value. You can't specify a fillfactor value
>> for INDEXDEFRAG, it will re-apply the value you specified when you created the index. If you
>> want to read up on this topic, check out
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx. Also, be aware of
>> implications of defragging regarding transaction logging too (so you don't be surprised that the
>> following log backup is potentially huge). And read up on recovery models so you understand all
>> three.
>>
>> Last, run the SQL from BOL to loop on when statistics were last updated:
>> and update any old statistics.
>> First, be aware that if you rebuild (DBREINDEX) your indexes then the statistics that comes with
>> the index will also be rebuilt. This does not happen if you INDEXDEFRAG, though.
>> Also, the database setting "auto update statistics" means that SQL Server *will update
>> statistics by itself*, as they becomes stale. Now, doing a manual update doesn't hurt,
>> especially if you have the time to use FULLSCAN (see UPDATE STATISTICS command). In most cases,
>> this database setting should be left to the default value. SQL Server uses a value in
>> sysindexes, rowmodctr (I believe) to determine how many modifications and whether it is time to
>> auto-update (when a plan is to be re-used or generated). This is a rather big topic, so here
>> comes another WP: http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx (there is
>> one for 2000 as well, I don't have that URL handy, but this is readable even if you aren't on
>> 2005).
>> Note that the auto-create statistics option isn't the same as auto-update. Auto-create means
>> that the optimizer will *create* statistics on non-indexed columns when it feels that such
>> statistics would be useful. Such statistics are also auto-updated by the engine by the same
>> handling as described above (the auto-update setting).
>> Finally, instead of having this loop, consider creating a table, in which you have a row for
>> each database, and here you configure what actions are to be performed. Then you loop that table
>> instead of sysdatabases. I'm not saying it is better, all depends on ones particular
>> requirements etc.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Jay" <nospam@.nospam.org> wrote in message news:ubj7n7a5HHA.3900@.TK2MSFTNGP02.phx.gbl...
>>I wasn't able to find much with an internet search, but digging in BOL I found a lot.
>> Is this a good plan for 24x7 SQL Server 2000 maintenance (excluding backup)?
>> Am I missing anything?
>> Am I doing more than is needed?
>> Did I miss something in BOL that makes this plan risky?
>> Here is my intended plan: write a procedure that uses CHECKDB, SHOWCONTIG, INDEXDEFRAG,
>> STATS_DATE & UPDATE STATISTICS. It would be run either before, or after the application
>> maintenance (archiving, credit card batches, search engine data dumps, etc).
>> At no time will I allow an automated program shrink anything in the Windows filesystem (and I
>> hope I can keep anything from Auto Growing too).
>> Write a single stored procedure with an outer loop on DB's from master..sysdatabases
>> Start with DBCC CHECKDB('dbname') looping through all databases. According to BOL, if you don't
>> specify any of the check options, nothing is locked and on the dev server, it is taking about
>> 2-3 minutes to complete. The only possible arguments I'm considering are NOINDEX and WITH
>> PHYSICAL_ONLY, but will probably go with no arguments.
>> Within the loop, after a success on the CHECKDB (failure would exit the loop and complain) run
>> a script based on the one in 2000 BOL (DBCC SHOWCONTIG) that does an INDEXDEFRAG on the index
>> (BOL says this is an online operation).
>> Last, run the SQL from BOL to loop on when statistics were last updated:
>> SELECT 'Index Name' = i.name,
>> 'Statistics Date' = STATS_DATE(i.id, i.indid)
>> FROM sysobjects o, sysindexes i
>> WHERE o.name = '_table_name_'
>> AND o.id = i.id
>> and update any old statistics.
>> Done.
>> Now, I'm not really sure how to decide what statistics are old. Under Informix I had a counter
>> that measured insert/update activity since the last statistics update. Perhaps just do
>> everything nightly?
>> I'm also not sure how the "Auto Create Statistics" checkbox on the database
>> prosperities/options tab interacts with update statistics.
>> Thanks,
>> Jay
>>
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> news:OmJUAXY5HHA.4712@.TK2MSFTNGP04.phx.gbl...
>> SQL Server 2000
>> Also, in Management Studio the maintenance plans are under a Legacy heading and I haven't
>> seen where maintenance is defined in 2005.
>> Seems you are using SSMS to connect to a 2000 instance. There are no "new style" MP's on 2000,
>> since they didn't exist in ... 2000. So perhaps SSMS are showing 2000 (old style) MP's under
>> the legacy folder, quite simply?
>> Be care full especially with rebuilding indexes. Rebuilding an index (the way 2000 MP's does
>> it) will log the table during the index rebuild. Also, DBCC CHECKDB will use resources (I'm
>> not sure about what level of locking it is using in 2000, but that should be documented in the
>> 2000 Books Online).
>> I believe that there's an "Operational Guideline" for 2000 available on the MS web. It could
>> be worth spending a few minutes with Google to try to dig it up...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Jay" <nospam@.nospam.org> wrote in message news:%23kB0DrQ5HHA.3900@.TK2MSFTNGP02.phx.gbl...
>> SQL Server 2000
>> I have finally been tasked and given access to the maintenance plans ... only there aren't
>> any (including disk defrags)! Backups are done via an SQLagent job, but that's it.
>> Normally I would just whomp the defaults together and start them running, but this is a
>> 24x7x365 system and I'm not sure which of the jobs have the potential to lock a table, or an
>> index.
>> Advice, or links to docs that concern maintenance on a 24x7x365 system.
>> Also, in Management Studio the maintenance plans are under a Legacy heading and I haven't
>> seen where maintenance is defined in 2005. What's up with that? Do I just need to dig deeper?
>> Thanks,
>> Jay
>>
>>
>>
>|||> ALTER PROC p AS
> DBCC CHECKDB('ds') WITH TABLERESULTS
> PRINT 'Hello'
> GO
> EXEC p
"Hello" only prints on a 2005 engine, not on a 2000.
Since the majority of the time everything will be fine, I'll let it fail and
(if I can) get the engine to email me. Otherwise, I'll put something
together that expects entries on success and will complain if it doesn't see
them. I did this before and it worked great; I got everything that happened,
everything that errored and everything that should have happened, but
didn't.
FWIW, I am going to use the WITH TABLERESULTS. Dam the torpedoes and full
speed ahead!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uGar$xn5HHA.484@.TK2MSFTNGP06.phx.gbl...
>> I wanted to execute DBCC CHECKDB and add program logic to determine
>> success/failure as I recalled code that put DBCC results into a temp
>> table. Oops, no TABLERESULTS option. So, the messages are always
>> displayed and I can't get them into a data structure.
> Some DBCCs has TABLERESULTS even though it isn't documented. But I'm
> hesitant to recommend using that (if such exist on 2000 for DBCC CHECKDB)
> since if it isn't documented, then it isn't supported.
>
>> When I change the database name to something that doesn't exist, the
>> procedure aborts (is this what the OSQL was for?) and I never get to the
>> line 'PRINT @.@.ERROR'.
> Exception handling is a bit of a mess in TSQL. First, you have the engine
> behavior. Some errors will terminate the batch (procedure). Not much you
> can do if that happens.
> But, the Agent scheduler for TSQL jobsteps has some extra toppings where
> it terminates the job if any error with severity > 10 (as I remember it).
> This last thing is what I suggested OSQL for.
> Now, if you execute it from Query Analyzer and it terminates the batch,
> then OSQL won't help you. But I'm surprised. Try below. On my machine, the
> PRINT statement *is* executed. Do you see something different? Make sure
> you execute into text if you use Query Analyzer. Perhaps this is a change
> between 2000 and 2005?
> ALTER PROC p AS
> DBCC CHECKDB('ds') WITH TABLERESULTS
> PRINT 'Hello'
> GO
> EXEC p
> No SET option, I'm afraid. In the end, you migtht be better off using a
> client language (like VB.NET or C#.NET) for this, if you can't get the
> exception handling to your liking.
> As for the API to use, DMO is designed particularly for admin, but I'm not
> sure you can get to all the error messages that CHECKDB might return. At
> least this was the case when I used DMO many many years ago. If that is
> the case, consider ADO.NET which should have something like an error
> colection which you can loop through. Or whatever DB API you or some
> colleague of yours are most experienced in (ODBC etc).
> There are a couple of great error handling articles at
> http://www.sommarskog.se/.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jay" <nospam@.nospam.org> wrote in message
> news:es7RLHn5HHA.5164@.TK2MSFTNGP05.phx.gbl...
>> Oh my, I hit a snag right out of the gate.
>> I wanted to execute DBCC CHECKDB and add program logic to determine
>> success/failure as I recalled code that put DBCC results into a temp
>> table. Oops, no TABLERESULTS option. So, the messages are always
>> displayed and I can't get them into a data structure.
>> When I change the database name to something that doesn't exist, the
>> procedure aborts (is this what the OSQL was for?) and I never get to the
>> line 'PRINT @.@.ERROR'.
>> I see the @.@.ERROR variable, but expected a SET option to change the
>> default behavior of immediately aborting - can't find anything.
>> Just a reminder: 2000.
>>
>> "Jay" <nospam@.nospam.org> wrote in message
>> news:%236s9kxb5HHA.2752@.TK2MSFTNGP06.phx.gbl...
>> Well now. Just my first read took 10 minutes, never mind fully
>> comprehending and following the links.
>> Thank you very much for the time you spent on the reply Tibor and rest
>> assured, the time a cat would spend picking apart a fish is nothing,
>> compared to what I'm going to do with it.
>> Just a couple comments for now:
>> I grasp the topic well because I was an Informix DBA for 15 years and
>> it's the same song, just a different tune.
>> I write all production destined code with strong error checks and find
>> ways to make it complain loudly is something unexpected/unwanted happens
>> (plus a once a week ping).
>> I chose INDEXDEFRAG because it is an online procedure and avoided
>> DBREINDEX because it is an offline procedure (24x7 after all). I do
>> remember a rather large discussion on this subject from about 6 months
>> ago, but it doesn't really matter, because if I get any scheduled
>> maintenance, it will be infrequent and limited.
>> Thanks,
>> Jay
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:%23HYDpSb5HHA.3940@.TK2MSFTNGP05.phx.gbl...
>> It certainly seems you grasp the topic well. Some comments:
>> Write a single stored procedure with an outer loop on DB's from
>> master..sysdatabases
>> Watch out for exceptions. What happens if for instance CHECKDB
>> encounters a corruption? Will it terminate the batch so nothing further
>> is executed? I can't answer that offhand, so thinking this through and
>> testing is important.
>> You might want to deliberately crash a database so that CHEDB fails to
>> test this realistically. I usually hack sysindexes (modify for instance
>> the FirstIAM column for some row to some rubbish value) - in a test
>> database of course. You aren't allowed to update system tables directly
>> by default but read about sp_configure and you will probably figure out
>> how to do it (I don't want to post any more descriptive instructions
>> considering this is a public forum).
>> Also, consider the client application handling of exceptions. SQL
>> Server Agent is nice, but a TSQL job step will actually terminate the
>> execution on any error (as I recall it). So, you might want to schedule
>> a CmdExec job step instead and call OSQL.EXE for the execution of your
>> proc.
>>
>> The only possible arguments I'm considering are NOINDEX and WITH
>> PHYSICAL_ONLY, but will probably go with no arguments.
>> Yes, go with as complete check as you can live with. If that doesn't
>> work for you use any of the options, possibly conditionally based on
>> for instance week day (like do full every Sunday). Btw, here's a
>> *great* blog on CHECKDB and related topics:
>> http://blogs.msdn.com/sqlserverstorageengine/default.aspx
>>
>> Within the loop, after a success on the CHECKDB (failure would exit
>> the loop and complain) run a script based on the one in 2000 BOL (DBCC
>> SHOWCONTIG) that does an INDEXDEFRAG on the index (BOL says this is an
>> online operation).
>> Yep, sounds fine. I guess you could spend lots of time with analyzing
>> how much fragmentation really hurts you at the table/index level and
>> have conditional handling based on that, but that is probably overkill.
>> Same goes for the fillfactor value. You can't specify a fillfactor
>> value for INDEXDEFRAG, it will re-apply the value you specified when
>> you created the index. If you want to read up on this topic, check out
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx.
>> Also, be aware of implications of defragging regarding transaction
>> logging too (so you don't be surprised that the following log backup is
>> potentially huge). And read up on recovery models so you understand all
>> three.
>>
>> Last, run the SQL from BOL to loop on when statistics were last
>> updated:
>> and update any old statistics.
>> First, be aware that if you rebuild (DBREINDEX) your indexes then the
>> statistics that comes with the index will also be rebuilt. This does
>> not happen if you INDEXDEFRAG, though.
>> Also, the database setting "auto update statistics" means that SQL
>> Server *will update statistics by itself*, as they becomes stale. Now,
>> doing a manual update doesn't hurt, especially if you have the time to
>> use FULLSCAN (see UPDATE STATISTICS command). In most cases, this
>> database setting should be left to the default value. SQL Server uses a
>> value in sysindexes, rowmodctr (I believe) to determine how many
>> modifications and whether it is time to auto-update (when a plan is to
>> be re-used or generated). This is a rather big topic, so here comes
>> another WP:
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
>> (there is one for 2000 as well, I don't have that URL handy, but this
>> is readable even if you aren't on 2005).
>> Note that the auto-create statistics option isn't the same as
>> auto-update. Auto-create means that the optimizer will *create*
>> statistics on non-indexed columns when it feels that such statistics
>> would be useful. Such statistics are also auto-updated by the engine by
>> the same handling as described above (the auto-update setting).
>> Finally, instead of having this loop, consider creating a table, in
>> which you have a row for each database, and here you configure what
>> actions are to be performed. Then you loop that table instead of
>> sysdatabases. I'm not saying it is better, all depends on ones
>> particular requirements etc.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Jay" <nospam@.nospam.org> wrote in message
>> news:ubj7n7a5HHA.3900@.TK2MSFTNGP02.phx.gbl...
>>I wasn't able to find much with an internet search, but digging in BOL
>>I found a lot.
>> Is this a good plan for 24x7 SQL Server 2000 maintenance (excluding
>> backup)?
>> Am I missing anything?
>> Am I doing more than is needed?
>> Did I miss something in BOL that makes this plan risky?
>> Here is my intended plan: write a procedure that uses CHECKDB,
>> SHOWCONTIG, INDEXDEFRAG, STATS_DATE & UPDATE STATISTICS. It would be
>> run either before, or after the application maintenance (archiving,
>> credit card batches, search engine data dumps, etc).
>> At no time will I allow an automated program shrink anything in the
>> Windows filesystem (and I hope I can keep anything from Auto Growing
>> too).
>> Write a single stored procedure with an outer loop on DB's from
>> master..sysdatabases
>> Start with DBCC CHECKDB('dbname') looping through all databases.
>> According to BOL, if you don't specify any of the check options,
>> nothing is locked and on the dev server, it is taking about 2-3
>> minutes to complete. The only possible arguments I'm considering are
>> NOINDEX and WITH PHYSICAL_ONLY, but will probably go with no
>> arguments.
>> Within the loop, after a success on the CHECKDB (failure would exit
>> the loop and complain) run a script based on the one in 2000 BOL (DBCC
>> SHOWCONTIG) that does an INDEXDEFRAG on the index (BOL says this is an
>> online operation).
>> Last, run the SQL from BOL to loop on when statistics were last
>> updated:
>> SELECT 'Index Name' = i.name,
>> 'Statistics Date' = STATS_DATE(i.id, i.indid)
>> FROM sysobjects o, sysindexes i
>> WHERE o.name = '_table_name_'
>> AND o.id = i.id
>> and update any old statistics.
>> Done.
>> Now, I'm not really sure how to decide what statistics are old. Under
>> Informix I had a counter that measured insert/update activity since
>> the last statistics update. Perhaps just do everything nightly?
>> I'm also not sure how the "Auto Create Statistics" checkbox on the
>> database prosperities/options tab interacts with update statistics.
>> Thanks,
>> Jay
>>
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> wrote in message news:OmJUAXY5HHA.4712@.TK2MSFTNGP04.phx.gbl...
>>> SQL Server 2000
>>> Also, in Management Studio the maintenance plans are under a Legacy
>>> heading and I haven't seen where maintenance is defined in 2005.
>> Seems you are using SSMS to connect to a 2000 instance. There are no
>> "new style" MP's on 2000, since they didn't exist in ... 2000. So
>> perhaps SSMS are showing 2000 (old style) MP's under the legacy
>> folder, quite simply?
>> Be care full especially with rebuilding indexes. Rebuilding an index
>> (the way 2000 MP's does it) will log the table during the index
>> rebuild. Also, DBCC CHECKDB will use resources (I'm not sure about
>> what level of locking it is using in 2000, but that should be
>> documented in the 2000 Books Online).
>> I believe that there's an "Operational Guideline" for 2000 available
>> on the MS web. It could be worth spending a few minutes with Google
>> to try to dig it up...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Jay" <nospam@.nospam.org> wrote in message
>> news:%23kB0DrQ5HHA.3900@.TK2MSFTNGP02.phx.gbl...
>>> SQL Server 2000
>>>
>>> I have finally been tasked and given access to the maintenance plans
>>> ... only there aren't any (including disk defrags)! Backups are done
>>> via an SQLagent job, but that's it.
>>>
>>> Normally I would just whomp the defaults together and start them
>>> running, but this is a 24x7x365 system and I'm not sure which of the
>>> jobs have the potential to lock a table, or an index.
>>>
>>> Advice, or links to docs that concern maintenance on a 24x7x365
>>> system.
>>>
>>> Also, in Management Studio the maintenance plans are under a Legacy
>>> heading and I haven't seen where maintenance is defined in 2005.
>>> What's up with that? Do I just need to dig deeper?
>>>
>>> Thanks,
>>> Jay
>>>
>>
>>
>>
>>
>|||> FWIW, I am going to use the WITH TABLERESULTS. Dam the torpedoes and full speed ahead!
:-) Way to go!
(And at least you know you have unsupported code in there, in case something strange happens, which
I doubt...).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <nospam@.nospam.org> wrote in message news:%23WZin$n5HHA.2312@.TK2MSFTNGP06.phx.gbl...
>> ALTER PROC p AS
>> DBCC CHECKDB('ds') WITH TABLERESULTS
>> PRINT 'Hello'
>> GO
>> EXEC p
> "Hello" only prints on a 2005 engine, not on a 2000.
> Since the majority of the time everything will be fine, I'll let it fail and (if I can) get the
> engine to email me. Otherwise, I'll put something together that expects entries on success and
> will complain if it doesn't see them. I did this before and it worked great; I got everything that
> happened, everything that errored and everything that should have happened, but didn't.
> FWIW, I am going to use the WITH TABLERESULTS. Dam the torpedoes and full speed ahead!
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:uGar$xn5HHA.484@.TK2MSFTNGP06.phx.gbl...
>> I wanted to execute DBCC CHECKDB and add program logic to determine success/failure as I
>> recalled code that put DBCC results into a temp table. Oops, no TABLERESULTS option. So, the
>> messages are always displayed and I can't get them into a data structure.
>> Some DBCCs has TABLERESULTS even though it isn't documented. But I'm hesitant to recommend using
>> that (if such exist on 2000 for DBCC CHECKDB) since if it isn't documented, then it isn't
>> supported.
>>
>> When I change the database name to something that doesn't exist, the procedure aborts (is this
>> what the OSQL was for?) and I never get to the line 'PRINT @.@.ERROR'.
>> Exception handling is a bit of a mess in TSQL. First, you have the engine behavior. Some errors
>> will terminate the batch (procedure). Not much you can do if that happens.
>> But, the Agent scheduler for TSQL jobsteps has some extra toppings where it terminates the job if
>> any error with severity > 10 (as I remember it). This last thing is what I suggested OSQL for.
>> Now, if you execute it from Query Analyzer and it terminates the batch, then OSQL won't help you.
>> But I'm surprised. Try below. On my machine, the PRINT statement *is* executed. Do you see
>> something different? Make sure you execute into text if you use Query Analyzer. Perhaps this is a
>> change between 2000 and 2005?
>> ALTER PROC p AS
>> DBCC CHECKDB('ds') WITH TABLERESULTS
>> PRINT 'Hello'
>> GO
>> EXEC p
>> No SET option, I'm afraid. In the end, you migtht be better off using a client language (like
>> VB.NET or C#.NET) for this, if you can't get the exception handling to your liking.
>> As for the API to use, DMO is designed particularly for admin, but I'm not sure you can get to
>> all the error messages that CHECKDB might return. At least this was the case when I used DMO many
>> many years ago. If that is the case, consider ADO.NET which should have something like an error
>> colection which you can loop through. Or whatever DB API you or some colleague of yours are most
>> experienced in (ODBC etc).
>> There are a couple of great error handling articles at http://www.sommarskog.se/.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Jay" <nospam@.nospam.org> wrote in message news:es7RLHn5HHA.5164@.TK2MSFTNGP05.phx.gbl...
>> Oh my, I hit a snag right out of the gate.
>> I wanted to execute DBCC CHECKDB and add program logic to determine success/failure as I
>> recalled code that put DBCC results into a temp table. Oops, no TABLERESULTS option. So, the
>> messages are always displayed and I can't get them into a data structure.
>> When I change the database name to something that doesn't exist, the procedure aborts (is this
>> what the OSQL was for?) and I never get to the line 'PRINT @.@.ERROR'.
>> I see the @.@.ERROR variable, but expected a SET option to change the default behavior of
>> immediately aborting - can't find anything.
>> Just a reminder: 2000.
>>
>> "Jay" <nospam@.nospam.org> wrote in message news:%236s9kxb5HHA.2752@.TK2MSFTNGP06.phx.gbl...
>> Well now. Just my first read took 10 minutes, never mind fully comprehending and following the
>> links.
>> Thank you very much for the time you spent on the reply Tibor and rest assured, the time a cat
>> would spend picking apart a fish is nothing, compared to what I'm going to do with it.
>> Just a couple comments for now:
>> I grasp the topic well because I was an Informix DBA for 15 years and it's the same song, just
>> a different tune.
>> I write all production destined code with strong error checks and find ways to make it complain
>> loudly is something unexpected/unwanted happens (plus a once a week ping).
>> I chose INDEXDEFRAG because it is an online procedure and avoided DBREINDEX because it is an
>> offline procedure (24x7 after all). I do remember a rather large discussion on this subject
>> from about 6 months ago, but it doesn't really matter, because if I get any scheduled
>> maintenance, it will be infrequent and limited.
>> Thanks,
>> Jay
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> news:%23HYDpSb5HHA.3940@.TK2MSFTNGP05.phx.gbl...
>> It certainly seems you grasp the topic well. Some comments:
>> Write a single stored procedure with an outer loop on DB's from master..sysdatabases
>> Watch out for exceptions. What happens if for instance CHECKDB encounters a corruption? Will
>> it terminate the batch so nothing further is executed? I can't answer that offhand, so
>> thinking this through and testing is important.
>> You might want to deliberately crash a database so that CHEDB fails to test this
>> realistically. I usually hack sysindexes (modify for instance the FirstIAM column for some row
>> to some rubbish value) - in a test database of course. You aren't allowed to update system
>> tables directly by default but read about sp_configure and you will probably figure out how to
>> do it (I don't want to post any more descriptive instructions considering this is a public
>> forum).
>> Also, consider the client application handling of exceptions. SQL Server Agent is nice, but a
>> TSQL job step will actually terminate the execution on any error (as I recall it). So, you
>> might want to schedule a CmdExec job step instead and call OSQL.EXE for the execution of your
>> proc.
>>
>> The only possible arguments I'm considering are NOINDEX and WITH PHYSICAL_ONLY, but will
>> probably go with no arguments.
>> Yes, go with as complete check as you can live with. If that doesn't work for you use any of
>> the options, possibly conditionally based on for instance week day (like do full every
>> Sunday). Btw, here's a *great* blog on CHECKDB and related topics:
>> http://blogs.msdn.com/sqlserverstorageengine/default.aspx
>>
>> Within the loop, after a success on the CHECKDB (failure would exit the loop and complain)
>> run a script based on the one in 2000 BOL (DBCC SHOWCONTIG) that does an INDEXDEFRAG on the
>> index (BOL says this is an online operation).
>> Yep, sounds fine. I guess you could spend lots of time with analyzing how much fragmentation
>> really hurts you at the table/index level and have conditional handling based on that, but
>> that is probably overkill. Same goes for the fillfactor value. You can't specify a fillfactor
>> value for INDEXDEFRAG, it will re-apply the value you specified when you created the index. If
>> you want to read up on this topic, check out
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx. Also, be aware
>> of implications of defragging regarding transaction logging too (so you don't be surprised
>> that the following log backup is potentially huge). And read up on recovery models so you
>> understand all three.
>>
>> Last, run the SQL from BOL to loop on when statistics were last updated:
>> and update any old statistics.
>> First, be aware that if you rebuild (DBREINDEX) your indexes then the statistics that comes
>> with the index will also be rebuilt. This does not happen if you INDEXDEFRAG, though.
>> Also, the database setting "auto update statistics" means that SQL Server *will update
>> statistics by itself*, as they becomes stale. Now, doing a manual update doesn't hurt,
>> especially if you have the time to use FULLSCAN (see UPDATE STATISTICS command). In most
>> cases, this database setting should be left to the default value. SQL Server uses a value in
>> sysindexes, rowmodctr (I believe) to determine how many modifications and whether it is time
>> to auto-update (when a plan is to be re-used or generated). This is a rather big topic, so
>> here comes another WP: http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
>> (there is one for 2000 as well, I don't have that URL handy, but this is readable even if you
>> aren't on 2005).
>> Note that the auto-create statistics option isn't the same as auto-update. Auto-create means
>> that the optimizer will *create* statistics on non-indexed columns when it feels that such
>> statistics would be useful. Such statistics are also auto-updated by the engine by the same
>> handling as described above (the auto-update setting).
>> Finally, instead of having this loop, consider creating a table, in which you have a row for
>> each database, and here you configure what actions are to be performed. Then you loop that
>> table instead of sysdatabases. I'm not saying it is better, all depends on ones particular
>> requirements etc.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Jay" <nospam@.nospam.org> wrote in message news:ubj7n7a5HHA.3900@.TK2MSFTNGP02.phx.gbl...
>>I wasn't able to find much with an internet search, but digging in BOL I found a lot.
>> Is this a good plan for 24x7 SQL Server 2000 maintenance (excluding backup)?
>> Am I missing anything?
>> Am I doing more than is needed?
>> Did I miss something in BOL that makes this plan risky?
>> Here is my intended plan: write a procedure that uses CHECKDB, SHOWCONTIG, INDEXDEFRAG,
>> STATS_DATE & UPDATE STATISTICS. It would be run either before, or after the application
>> maintenance (archiving, credit card batches, search engine data dumps, etc).
>> At no time will I allow an automated program shrink anything in the Windows filesystem (and I
>> hope I can keep anything from Auto Growing too).
>> Write a single stored procedure with an outer loop on DB's from master..sysdatabases
>> Start with DBCC CHECKDB('dbname') looping through all databases. According to BOL, if you
>> don't specify any of the check options, nothing is locked and on the dev server, it is taking
>> about 2-3 minutes to complete. The only possible arguments I'm considering are NOINDEX and
>> WITH PHYSICAL_ONLY, but will probably go with no arguments.
>> Within the loop, after a success on the CHECKDB (failure would exit the loop and complain)
>> run a script based on the one in 2000 BOL (DBCC SHOWCONTIG) that does an INDEXDEFRAG on the
>> index (BOL says this is an online operation).
>> Last, run the SQL from BOL to loop on when statistics were last updated:
>> SELECT 'Index Name' = i.name,
>> 'Statistics Date' = STATS_DATE(i.id, i.indid)
>> FROM sysobjects o, sysindexes i
>> WHERE o.name = '_table_name_'
>> AND o.id = i.id
>> and update any old statistics.
>> Done.
>> Now, I'm not really sure how to decide what statistics are old. Under Informix I had a
>> counter that measured insert/update activity since the last statistics update. Perhaps just
>> do everything nightly?
>> I'm also not sure how the "Auto Create Statistics" checkbox on the database
>> prosperities/options tab interacts with update statistics.
>> Thanks,
>> Jay
>>
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> news:OmJUAXY5HHA.4712@.TK2MSFTNGP04.phx.gbl...
>>> SQL Server 2000
>>> Also, in Management Studio the maintenance plans are under a Legacy heading and I haven't
>>> seen where maintenance is defined in 2005.
>>>
>>> Seems you are using SSMS to connect to a 2000 instance. There are no "new style" MP's on
>>> 2000, since they didn't exist in ... 2000. So perhaps SSMS are showing 2000 (old style) MP's
>>> under the legacy folder, quite simply?
>>>
>>> Be care full especially with rebuilding indexes. Rebuilding an index (the way 2000 MP's does
>>> it) will log the table during the index rebuild. Also, DBCC CHECKDB will use resources (I'm
>>> not sure about what level of locking it is using in 2000, but that should be documented in
>>> the 2000 Books Online).
>>>
>>> I believe that there's an "Operational Guideline" for 2000 available on the MS web. It could
>>> be worth spending a few minutes with Google to try to dig it up...
>>>
>>> --
>>> Tibor Karaszi, SQL Server MVP
>>> http://www.karaszi.com/sqlserver/default.asp
>>> http://sqlblog.com/blogs/tibor_karaszi
>>>
>>>
>>> "Jay" <nospam@.nospam.org> wrote in message news:%23kB0DrQ5HHA.3900@.TK2MSFTNGP02.phx.gbl...
>>> SQL Server 2000
>>>
>>> I have finally been tasked and given access to the maintenance plans ... only there aren't
>>> any (including disk defrags)! Backups are done via an SQLagent job, but that's it.
>>>
>>> Normally I would just whomp the defaults together and start them running, but this is a
>>> 24x7x365 system and I'm not sure which of the jobs have the potential to lock a table, or
>>> an index.
>>>
>>> Advice, or links to docs that concern maintenance on a 24x7x365 system.
>>>
>>> Also, in Management Studio the maintenance plans are under a Legacy heading and I haven't
>>> seen where maintenance is defined in 2005. What's up with that? Do I just need to dig
>>> deeper?
>>>
>>> Thanks,
>>> Jay
>>>
>>>
>>
>>
>>
>>
>|||The idea of performing CHECKTABLE on each table to spread the load out
across a week, month, or whatever was a compelling one, so I tried to run a
test.
Unfortunately, the list needs to be in a loop/cursor and I can't reset the
database inside a procedure (can I?)
DECLARE cTables CURSOR FOR
SELECT sc.name
FROM @.DB..sysobjects sc
WHERE xtype IN ('S', 'U')
Just plain doesn't work.
Is there a way to dynamically get the table names as I loop through the
databases?
"Jay" <nospam@.nospam.org> wrote in message
news:%236s9kxb5HHA.2752@.TK2MSFTNGP06.phx.gbl...
> Well now. Just my first read took 10 minutes, never mind fully
> comprehending and following the links.
> Thank you very much for the time you spent on the reply Tibor and rest
> assured, the time a cat would spend picking apart a fish is nothing,
> compared to what I'm going to do with it.
> Just a couple comments for now:
> I grasp the topic well because I was an Informix DBA for 15 years and it's
> the same song, just a different tune.
> I write all production destined code with strong error checks and find
> ways to make it complain loudly is something unexpected/unwanted happens
> (plus a once a week ping).
> I chose INDEXDEFRAG because it is an online procedure and avoided
> DBREINDEX because it is an offline procedure (24x7 after all). I do
> remember a rather large discussion on this subject from about 6 months
> ago, but it doesn't really matter, because if I get any scheduled
> maintenance, it will be infrequent and limited.
> Thanks,
> Jay
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:%23HYDpSb5HHA.3940@.TK2MSFTNGP05.phx.gbl...
>> It certainly seems you grasp the topic well. Some comments:
>> Write a single stored procedure with an outer loop on DB's from
>> master..sysdatabases
>> Watch out for exceptions. What happens if for instance CHECKDB encounters
>> a corruption? Will it terminate the batch so nothing further is executed?
>> I can't answer that offhand, so thinking this through and testing is
>> important.
>> You might want to deliberately crash a database so that CHEDB fails to
>> test this realistically. I usually hack sysindexes (modify for instance
>> the FirstIAM column for some row to some rubbish value) - in a test
>> database of course. You aren't allowed to update system tables directly
>> by default but read about sp_configure and you will probably figure out
>> how to do it (I don't want to post any more descriptive instructions
>> considering this is a public forum).
>> Also, consider the client application handling of exceptions. SQL Server
>> Agent is nice, but a TSQL job step will actually terminate the execution
>> on any error (as I recall it). So, you might want to schedule a CmdExec
>> job step instead and call OSQL.EXE for the execution of your proc.
>>
>> The only possible arguments I'm considering are NOINDEX and WITH
>> PHYSICAL_ONLY, but will probably go with no arguments.
>> Yes, go with as complete check as you can live with. If that doesn't work
>> for you use any of the options, possibly conditionally based on for
>> instance week day (like do full every Sunday). Btw, here's a *great* blog
>> on CHECKDB and related topics:
>> http://blogs.msdn.com/sqlserverstorageengine/default.aspx
>>
>> Within the loop, after a success on the CHECKDB (failure would exit the
>> loop and complain) run a script based on the one in 2000 BOL (DBCC
>> SHOWCONTIG) that does an INDEXDEFRAG on the index (BOL says this is an
>> online operation).
>> Yep, sounds fine. I guess you could spend lots of time with analyzing how
>> much fragmentation really hurts you at the table/index level and have
>> conditional handling based on that, but that is probably overkill. Same
>> goes for the fillfactor value. You can't specify a fillfactor value for
>> INDEXDEFRAG, it will re-apply the value you specified when you created
>> the index. If you want to read up on this topic, check out
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx.
>> Also, be aware of implications of defragging regarding transaction
>> logging too (so you don't be surprised that the following log backup is
>> potentially huge). And read up on recovery models so you understand all
>> three.
>>
>> Last, run the SQL from BOL to loop on when statistics were last updated:
>> and update any old statistics.
>> First, be aware that if you rebuild (DBREINDEX) your indexes then the
>> statistics that comes with the index will also be rebuilt. This does not
>> happen if you INDEXDEFRAG, though.
>> Also, the database setting "auto update statistics" means that SQL Server
>> *will update statistics by itself*, as they becomes stale. Now, doing a
>> manual update doesn't hurt, especially if you have the time to use
>> FULLSCAN (see UPDATE STATISTICS command). In most cases, this database
>> setting should be left to the default value. SQL Server uses a value in
>> sysindexes, rowmodctr (I believe) to determine how many modifications and
>> whether it is time to auto-update (when a plan is to be re-used or
>> generated). This is a rather big topic, so here comes another WP:
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx (there
>> is one for 2000 as well, I don't have that URL handy, but this is
>> readable even if you aren't on 2005).
>> Note that the auto-create statistics option isn't the same as
>> auto-update. Auto-create means that the optimizer will *create*
>> statistics on non-indexed columns when it feels that such statistics
>> would be useful. Such statistics are also auto-updated by the engine by
>> the same handling as described above (the auto-update setting).
>> Finally, instead of having this loop, consider creating a table, in which
>> you have a row for each database, and here you configure what actions are
>> to be performed. Then you loop that table instead of sysdatabases. I'm
>> not saying it is better, all depends on ones particular requirements etc.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Jay" <nospam@.nospam.org> wrote in message
>> news:ubj7n7a5HHA.3900@.TK2MSFTNGP02.phx.gbl...
>>I wasn't able to find much with an internet search, but digging in BOL I
>>found a lot.
>> Is this a good plan for 24x7 SQL Server 2000 maintenance (excluding
>> backup)?
>> Am I missing anything?
>> Am I doing more than is needed?
>> Did I miss something in BOL that makes this plan risky?
>> Here is my intended plan: write a procedure that uses CHECKDB,
>> SHOWCONTIG, INDEXDEFRAG, STATS_DATE & UPDATE STATISTICS. It would be run
>> either before, or after the application maintenance (archiving, credit
>> card batches, search engine data dumps, etc).
>> At no time will I allow an automated program shrink anything in the
>> Windows filesystem (and I hope I can keep anything from Auto Growing
>> too).
>> Write a single stored procedure with an outer loop on DB's from
>> master..sysdatabases
>> Start with DBCC CHECKDB('dbname') looping through all databases.
>> According to BOL, if you don't specify any of the check options, nothing
>> is locked and on the dev server, it is taking about 2-3 minutes to
>> complete. The only possible arguments I'm considering are NOINDEX and
>> WITH PHYSICAL_ONLY, but will probably go with no arguments.
>> Within the loop, after a success on the CHECKDB (failure would exit the
>> loop and complain) run a script based on the one in 2000 BOL (DBCC
>> SHOWCONTIG) that does an INDEXDEFRAG on the index (BOL says this is an
>> online operation).
>> Last, run the SQL from BOL to loop on when statistics were last updated:
>> SELECT 'Index Name' = i.name,
>> 'Statistics Date' = STATS_DATE(i.id, i.indid)
>> FROM sysobjects o, sysindexes i
>> WHERE o.name = '_table_name_'
>> AND o.id = i.id
>> and update any old statistics.
>> Done.
>> Now, I'm not really sure how to decide what statistics are old. Under
>> Informix I had a counter that measured insert/update activity since the
>> last statistics update. Perhaps just do everything nightly?
>> I'm also not sure how the "Auto Create Statistics" checkbox on the
>> database prosperities/options tab interacts with update statistics.
>> Thanks,
>> Jay
>>
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:OmJUAXY5HHA.4712@.TK2MSFTNGP04.phx.gbl...
>> SQL Server 2000
>> Also, in Management Studio the maintenance plans are under a Legacy
>> heading and I haven't seen where maintenance is defined in 2005.
>> Seems you are using SSMS to connect to a 2000 instance. There are no
>> "new style" MP's on 2000, since they didn't exist in ... 2000. So
>> perhaps SSMS are showing 2000 (old style) MP's under the legacy folder,
>> quite simply?
>> Be care full especially with rebuilding indexes. Rebuilding an index
>> (the way 2000 MP's does it) will log the table during the index
>> rebuild. Also, DBCC CHECKDB will use resources (I'm not sure about what
>> level of locking it is using in 2000, but that should be documented in
>> the 2000 Books Online).
>> I believe that there's an "Operational Guideline" for 2000 available on
>> the MS web. It could be worth spending a few minutes with Google to try
>> to dig it up...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Jay" <nospam@.nospam.org> wrote in message
>> news:%23kB0DrQ5HHA.3900@.TK2MSFTNGP02.phx.gbl...
>> SQL Server 2000
>> I have finally been tasked and given access to the maintenance plans
>> ... only there aren't any (including disk defrags)! Backups are done
>> via an SQLagent job, but that's it.
>> Normally I would just whomp the defaults together and start them
>> running, but this is a 24x7x365 system and I'm not sure which of the
>> jobs have the potential to lock a table, or an index.
>> Advice, or links to docs that concern maintenance on a 24x7x365
>> system.
>> Also, in Management Studio the maintenance plans are under a Legacy
>> heading and I haven't seen where maintenance is defined in 2005.
>> What's up with that? Do I just need to dig deeper?
>> Thanks,
>> Jay
>>
>>
>|||No, you would have to resort to dynamic SQL for that. A bit of a mess. Something like (to get you
rolling):
SET @.sql = 'DECLARE c CURSOR FOR
SELECT ...
FROM ' + @.db + '.dbo.sysobjects
WHERE '
EXEC @.sql
OPEN c
...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <nospam@.nospam.org> wrote in message news:OzvtLeo5HHA.2312@.TK2MSFTNGP06.phx.gbl...
> The idea of performing CHECKTABLE on each table to spread the load out across a week, month, or
> whatever was a compelling one, so I tried to run a test.
> Unfortunately, the list needs to be in a loop/cursor and I can't reset the database inside a
> procedure (can I?)
> DECLARE cTables CURSOR FOR
> SELECT sc.name
> FROM @.DB..sysobjects sc
> WHERE xtype IN ('S', 'U')
> Just plain doesn't work.
> Is there a way to dynamically get the table names as I loop through the databases?
>
> "Jay" <nospam@.nospam.org> wrote in message news:%236s9kxb5HHA.2752@.TK2MSFTNGP06.phx.gbl...
>> Well now. Just my first read took 10 minutes, never mind fully comprehending and following the
>> links.
>> Thank you very much for the time you spent on the reply Tibor and rest assured, the time a cat
>> would spend picking apart a fish is nothing, compared to what I'm going to do with it.
>> Just a couple comments for now:
>> I grasp the topic well because I was an Informix DBA for 15 years and it's the same song, just a
>> different tune.
>> I write all production destined code with strong error checks and find ways to make it complain
>> loudly is something unexpected/unwanted happens (plus a once a week ping).
>> I chose INDEXDEFRAG because it is an online procedure and avoided DBREINDEX because it is an
>> offline procedure (24x7 after all). I do remember a rather large discussion on this subject from
>> about 6 months ago, but it doesn't really matter, because if I get any scheduled maintenance, it
>> will be infrequent and limited.
>> Thanks,
>> Jay
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> news:%23HYDpSb5HHA.3940@.TK2MSFTNGP05.phx.gbl...
>> It certainly seems you grasp the topic well. Some comments:
>> Write a single stored procedure with an outer loop on DB's from master..sysdatabases
>> Watch out for exceptions. What happens if for instance CHECKDB encounters a corruption? Will it
>> terminate the batch so nothing further is executed? I can't answer that offhand, so thinking
>> this through and testing is important.
>> You might want to deliberately crash a database so that CHEDB fails to test this realistically.
>> I usually hack sysindexes (modify for instance the FirstIAM column for some row to some rubbish
>> value) - in a test database of course. You aren't allowed to update system tables directly by
>> default but read about sp_configure and you will probably figure out how to do it (I don't want
>> to post any more descriptive instructions considering this is a public forum).
>> Also, consider the client application handling of exceptions. SQL Server Agent is nice, but a
>> TSQL job step will actually terminate the execution on any error (as I recall it). So, you might
>> want to schedule a CmdExec job step instead and call OSQL.EXE for the execution of your proc.
>>
>> The only possible arguments I'm considering are NOINDEX and WITH PHYSICAL_ONLY, but will
>> probably go with no arguments.
>> Yes, go with as complete check as you can live with. If that doesn't work for you use any of the
>> options, possibly conditionally based on for instance week day (like do full every Sunday). Btw,
>> here's a *great* blog on CHECKDB and related topics:
>> http://blogs.msdn.com/sqlserverstorageengine/default.aspx
>>
>> Within the loop, after a success on the CHECKDB (failure would exit the loop and complain) run
>> a script based on the one in 2000 BOL (DBCC SHOWCONTIG) that does an INDEXDEFRAG on the index
>> (BOL says this is an online operation).
>> Yep, sounds fine. I guess you could spend lots of time with analyzing how much fragmentation
>> really hurts you at the table/index level and have conditional handling based on that, but that
>> is probably overkill. Same goes for the fillfactor value. You can't specify a fillfactor value
>> for INDEXDEFRAG, it will re-apply the value you specified when you created the index. If you
>> want to read up on this topic, check out
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx. Also, be aware of
>> implications of defragging regarding transaction logging too (so you don't be surprised that the
>> following log backup is potentially huge). And read up on recovery models so you understand all
>> three.
>>
>> Last, run the SQL from BOL to loop on when statistics were last updated:
>> and update any old statistics.
>> First, be aware that if you rebuild (DBREINDEX) your indexes then the statistics that comes with
>> the index will also be rebuilt. This does not happen if you INDEXDEFRAG, though.
>> Also, the database setting "auto update statistics" means that SQL Server *will update
>> statistics by itself*, as they becomes stale. Now, doing a manual update doesn't hurt,
>> especially if you have the time to use FULLSCAN (see UPDATE STATISTICS command). In most cases,
>> this database setting should be left to the default value. SQL Server uses a value in
>> sysindexes, rowmodctr (I believe) to determine how many modifications and whether it is time to
>> auto-update (when a plan is to be re-used or generated). This is a rather big topic, so here
>> comes another WP: http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx (there is
>> one for 2000 as well, I don't have that URL handy, but this is readable even if you aren't on
>> 2005).
>> Note that the auto-create statistics option isn't the same as auto-update. Auto-create means
>> that the optimizer will *create* statistics on non-indexed columns when it feels that such
>> statistics would be useful. Such statistics are also auto-updated by the engine by the same
>> handling as described above (the auto-update setting).
>> Finally, instead of having this loop, consider creating a table, in which you have a row for
>> each database, and here you configure what actions are to be performed. Then you loop that table
>> instead of sysdatabases. I'm not saying it is better, all depends on ones particular
>> requirements etc.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Jay" <nospam@.nospam.org> wrote in message news:ubj7n7a5HHA.3900@.TK2MSFTNGP02.phx.gbl...
>>I wasn't able to find much with an internet search, but digging in BOL I found a lot.
>> Is this a good plan for 24x7 SQL Server 2000 maintenance (excluding backup)?
>> Am I missing anything?
>> Am I doing more than is needed?
>> Did I miss something in BOL that makes this plan risky?
>> Here is my intended plan: write a procedure that uses CHECKDB, SHOWCONTIG, INDEXDEFRAG,
>> STATS_DATE & UPDATE STATISTICS. It would be run either before, or after the application
>> maintenance (archiving, credit card batches, search engine data dumps, etc).
>> At no time will I allow an automated program shrink anything in the Windows filesystem (and I
>> hope I can keep anything from Auto Growing too).
>> Write a single stored procedure with an outer loop on DB's from master..sysdatabases
>> Start with DBCC CHECKDB('dbname') looping through all databases. According to BOL, if you don't
>> specify any of the check options, nothing is locked and on the dev server, it is taking about
>> 2-3 minutes to complete. The only possible arguments I'm considering are NOINDEX and WITH
>> PHYSICAL_ONLY, but will probably go with no arguments.
>> Within the loop, after a success on the CHECKDB (failure would exit the loop and complain) run
>> a script based on the one in 2000 BOL (DBCC SHOWCONTIG) that does an INDEXDEFRAG on the index
>> (BOL says this is an online operation).
>> Last, run the SQL from BOL to loop on when statistics were last updated:
>> SELECT 'Index Name' = i.name,
>> 'Statistics Date' = STATS_DATE(i.id, i.indid)
>> FROM sysobjects o, sysindexes i
>> WHERE o.name = '_table_name_'
>> AND o.id = i.id
>> and update any old statistics.
>> Done.
>> Now, I'm not really sure how to decide what statistics are old. Under Informix I had a counter
>> that measured insert/update activity since the last statistics update. Perhaps just do
>> everything nightly?
>> I'm also not sure how the "Auto Create Statistics" checkbox on the database
>> prosperities/options tab interacts with update statistics.
>> Thanks,
>> Jay
>>
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> news:OmJUAXY5HHA.4712@.TK2MSFTNGP04.phx.gbl...
>> SQL Server 2000
>> Also, in Management Studio the maintenance plans are under a Legacy heading and I haven't
>> seen where maintenance is defined in 2005.
>> Seems you are using SSMS to connect to a 2000 instance. There are no "new style" MP's on 2000,
>> since they didn't exist in ... 2000. So perhaps SSMS are showing 2000 (old style) MP's under
>> the legacy folder, quite simply?
>> Be care full especially with rebuilding indexes. Rebuilding an index (the way 2000 MP's does
>> it) will log the table during the index rebuild. Also, DBCC CHECKDB will use resources (I'm
>> not sure about what level of locking it is using in 2000, but that should be documented in the
>> 2000 Books Online).
>> I believe that there's an "Operational Guideline" for 2000 available on the MS web. It could
>> be worth spending a few minutes with Google to try to dig it up...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Jay" <nospam@.nospam.org> wrote in message news:%23kB0DrQ5HHA.3900@.TK2MSFTNGP02.phx.gbl...
>> SQL Server 2000
>> I have finally been tasked and given access to the maintenance plans ... only there aren't
>> any (including disk defrags)! Backups are done via an SQLagent job, but that's it.
>> Normally I would just whomp the defaults together and start them running, but this is a
>> 24x7x365 system and I'm not sure which of the jobs have the potential to lock a table, or an
>> index.
>> Advice, or links to docs that concern maintenance on a 24x7x365 system.
>> Also, in Management Studio the maintenance plans are under a Legacy heading and I haven't
>> seen where maintenance is defined in 2005. What's up with that? Do I just need to dig deeper?
>> Thanks,
>> Jay
>>
>>
>>
>|||That is what I've been trying since my last post. I have (I've tried many
things as I go the the sp_executesql man page.
DECLARE @.ParmDefinition NVARCHAR(500)
DECLARE @.VarcharVar varchar(30)
DECLARE @.DBname varchar(30)
SET @.DBname=RTRIM(CONVERT(CHAR(30), @.DB))
PRINT 'DBname [' + @.DBname + ']'
SET @.SQL = N'DECLARE cTables CURSOR FOR
SELECT [name]
FROM @.DBname.dbo.sysobjects
WHERE xtype IN (''U'')'
SET @.ParmDefinition = N'@.DBname varchar'
PRINT '[' + RTRIM(@.SQL) + ']'
EXEC sp_executesql @.SQL, @.ParmDefinition, @.DBname = @.DB
and I get:
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '.'.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eeBCuPp5HHA.5268@.TK2MSFTNGP02.phx.gbl...
> No, you would have to resort to dynamic SQL for that. A bit of a mess.
> Something like (to get you rolling):
> SET @.sql = 'DECLARE c CURSOR FOR
> SELECT ...
> FROM ' + @.db + '.dbo.sysobjects
> WHERE '
> EXEC @.sql
> OPEN c
> ...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jay" <nospam@.nospam.org> wrote in message
> news:OzvtLeo5HHA.2312@.TK2MSFTNGP06.phx.gbl...
>> The idea of performing CHECKTABLE on each table to spread the load out
>> across a week, month, or whatever was a compelling one, so I tried to run
>> a test.
>> Unfortunately, the list needs to be in a loop/cursor and I can't reset
>> the database inside a procedure (can I?)
>> DECLARE cTables CURSOR FOR
>> SELECT sc.name
>> FROM @.DB..sysobjects sc
>> WHERE xtype IN ('S', 'U')
>> Just plain doesn't work.
>> Is there a way to dynamically get the table names as I loop through the
>> databases?
>>
>> "Jay" <nospam@.nospam.org> wrote in message
>> news:%236s9kxb5HHA.2752@.TK2MSFTNGP06.phx.gbl...
>> Well now. Just my first read took 10 minutes, never mind fully
>> comprehending and following the links.
>> Thank you very much for the time you spent on the reply Tibor and rest
>> assured, the time a cat would spend picking apart a fish is nothing,
>> compared to what I'm going to do with it.
>> Just a couple comments for now:
>> I grasp the topic well because I was an Informix DBA for 15 years and
>> it's the same song, just a different tune.
>> I write all production destined code with strong error checks and find
>> ways to make it complain loudly is something unexpected/unwanted happens
>> (plus a once a week ping).
>> I chose INDEXDEFRAG because it is an online procedure and avoided
>> DBREINDEX because it is an offline procedure (24x7 after all). I do
>> remember a rather large discussion on this subject from about 6 months
>> ago, but it doesn't really matter, because if I get any scheduled
>> maintenance, it will be infrequent and limited.
>> Thanks,
>> Jay
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:%23HYDpSb5HHA.3940@.TK2MSFTNGP05.phx.gbl...
>> It certainly seems you grasp the topic well. Some comments:
>> Write a single stored procedure with an outer loop on DB's from
>> master..sysdatabases
>> Watch out for exceptions. What happens if for instance CHECKDB
>> encounters a corruption? Will it terminate the batch so nothing further
>> is executed? I can't answer that offhand, so thinking this through and
>> testing is important.
>> You might want to deliberately crash a database so that CHEDB fails to
>> test this realistically. I usually hack sysindexes (modify for instance
>> the FirstIAM column for some row to some rubbish value) - in a test
>> database of course. You aren't allowed to update system tables directly
>> by default but read about sp_configure and you will probably figure out
>> how to do it (I don't want to post any more descriptive instructions
>> considering this is a public forum).
>> Also, consider the client application handling of exceptions. SQL
>> Server Agent is nice, but a TSQL job step will actually terminate the
>> execution on any error (as I recall it). So, you might want to schedule
>> a CmdExec job step instead and call OSQL.EXE for the execution of your
>> proc.
>>
>> The only possible arguments I'm considering are NOINDEX and WITH
>> PHYSICAL_ONLY, but will probably go with no arguments.
>> Yes, go with as complete check as you can live with. If that doesn't
>> work for you use any of the options, possibly conditionally based on
>> for instance week day (like do full every Sunday). Btw, here's a
>> *great* blog on CHECKDB and related topics:
>> http://blogs.msdn.com/sqlserverstorageengine/default.aspx
>>
>> Within the loop, after a success on the CHECKDB (failure would exit
>> the loop and complain) run a script based on the one in 2000 BOL (DBCC
>> SHOWCONTIG) that does an INDEXDEFRAG on the index (BOL says this is an
>> online operation).
>> Yep, sounds fine. I guess you could spend lots of time with analyzing
>> how much fragmentation really hurts you at the table/index level and
>> have conditional handling based on that, but that is probably overkill.
>> Same goes for the fillfactor value. You can't specify a fillfactor
>> value for INDEXDEFRAG, it will re-apply the value you specified when
>> you created the index. If you want to read up on this topic, check out
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx.
>> Also, be aware of implications of defragging regarding transaction
>> logging too (so you don't be surprised that the following log backup is
>> potentially huge). And read up on recovery models so you understand all
>> three.
>>
>> Last, run the SQL from BOL to loop on when statistics were last
>> updated:
>> and update any old statistics.
>> First, be aware that if you rebuild (DBREINDEX) your indexes then the
>> statistics that comes with the index will also be rebuilt. This does
>> not happen if you INDEXDEFRAG, though.
>> Also, the database setting "auto update statistics" means that SQL
>> Server *will update statistics by itself*, as they becomes stale. Now,
>> doing a manual update doesn't hurt, especially if you have the time to
>> use FULLSCAN (see UPDATE STATISTICS command). In most cases, this
>> database setting should be left to the default value. SQL Server uses a
>> value in sysindexes, rowmodctr (I believe) to determine how many
>> modifications and whether it is time to auto-update (when a plan is to
>> be re-used or generated). This is a rather big topic, so here comes
>> another WP:
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
>> (there is one for 2000 as well, I don't have that URL handy, but this
>> is readable even if you aren't on 2005).
>> Note that the auto-create statistics option isn't the same as
>> auto-update. Auto-create means that the optimizer will *create*
>> statistics on non-indexed columns when it feels that such statistics
>> would be useful. Such statistics are also auto-updated by the engine by
>> the same handling as described above (the auto-update setting).
>> Finally, instead of having this loop, consider creating a table, in
>> which you have a row for each database, and here you configure what
>> actions are to be performed. Then you loop that table instead of
>> sysdatabases. I'm not saying it is better, all depends on ones
>> particular requirements etc.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Jay" <nospam@.nospam.org> wrote in message
>> news:ubj7n7a5HHA.3900@.TK2MSFTNGP02.phx.gbl...
>>I wasn't able to find much with an internet search, but digging in BOL
>>I found a lot.
>> Is this a good plan for 24x7 SQL Server 2000 maintenance (excluding
>> backup)?
>> Am I missing anything?
>> Am I doing more than is needed?
>> Did I miss something in BOL that makes this plan risky?
>> Here is my intended plan: write a procedure that uses CHECKDB,
>> SHOWCONTIG, INDEXDEFRAG, STATS_DATE & UPDATE STATISTICS. It would be
>> run either before, or after the application maintenance (archiving,
>> credit card batches, search engine data dumps, etc).
>> At no time will I allow an automated program shrink anything in the
>> Windows filesystem (and I hope I can keep anything from Auto Growing
>> too).
>> Write a single stored procedure with an outer loop on DB's from
>> master..sysdatabases
>> Start with DBCC CHECKDB('dbname') looping through all databases.
>> According to BOL, if you don't specify any of the check options,
>> nothing is locked and on the dev server, it is taking about 2-3
>> minutes to complete. The only possible arguments I'm considering are
>> NOINDEX and WITH PHYSICAL_ONLY, but will probably go with no
>> arguments.
>> Within the loop, after a success on the CHECKDB (failure would exit
>> the loop and complain) run a script based on the one in 2000 BOL (DBCC
>> SHOWCONTIG) that does an INDEXDEFRAG on the index (BOL says this is an
>> online operation).
>> Last, run the SQL from BOL to loop on when statistics were last
>> updated:
>> SELECT 'Index Name' = i.name,
>> 'Statistics Date' = STATS_DATE(i.id, i.indid)
>> FROM sysobjects o, sysindexes i
>> WHERE o.name = '_table_name_'
>> AND o.id = i.id
>> and update any old statistics.
>> Done.
>> Now, I'm not really sure how to decide what statistics are old. Under
>> Informix I had a counter that measured insert/update activity since
>> the last statistics update. Perhaps just do everything nightly?
>> I'm also not sure how the "Auto Create Statistics" checkbox on the
>> database prosperities/options tab interacts with update statistics.
>> Thanks,
>> Jay
>>
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> wrote in message news:OmJUAXY5HHA.4712@.TK2MSFTNGP04.phx.gbl...
>>> SQL Server 2000
>>> Also, in Management Studio the maintenance plans are under a Legacy
>>> heading and I haven't seen where maintenance is defined in 2005.
>> Seems you are using SSMS to connect to a 2000 instance. There are no
>> "new style" MP's on 2000, since they didn't exist in ... 2000. So
>> perhaps SSMS are showing 2000 (old style) MP's under the legacy
>> folder, quite simply?
>> Be care full especially with rebuilding indexes. Rebuilding an index
>> (the way 2000 MP's does it) will log the table during the index
>> rebuild. Also, DBCC CHECKDB will use resources (I'm not sure about
>> what level of locking it is using in 2000, but that should be
>> documented in the 2000 Books Online).
>> I believe that there's an "Operational Guideline" for 2000 available
>> on the MS web. It could be worth spending a few minutes with Google
>> to try to dig it up...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Jay" <nospam@.nospam.org> wrote in message
>> news:%23kB0DrQ5HHA.3900@.TK2MSFTNGP02.phx.gbl...
>>> SQL Server 2000
>>>
>>> I have finally been tasked and given access to the maintenance plans
>>> ... only there aren't any (including disk defrags)! Backups are done
>>> via an SQLagent job, but that's it.
>>>
>>> Normally I would just whomp the defaults together and start them
>>> running, but this is a 24x7x365 system and I'm not sure which of the
>>> jobs have the potential to lock a table, or an index.
>>>
>>> Advice, or links to docs that concern maintenance on a 24x7x365
>>> system.
>>>
>>> Also, in Management Studio the maintenance plans are under a Legacy
>>> heading and I haven't seen where maintenance is defined in 2005.
>>> What's up with that? Do I just need to dig deeper?
>>>
>>> Thanks,
>>> Jay
>>>
>>
>>
>>
>>
>|||SET @.SQL = N'DECLARE cTables CURSOR FOR
SELECT [name]
FROM @.DBname.dbo.sysobjects
WHERE xtype IN (''U'')'
SET @.ParmDefinition = N'@.DBname varchar'
PRINT '[' + RTRIM(@.SQL) + ']'
EXEC @.SQL
Server: Msg 203, Level 16, State 2, Procedure mDBMaintenance, Line 65
The name 'DECLARE cTables CURSOR FOR
SELECT [name]
FROM @.DBname.dbo.sysobjects
WHERE xtype IN ('U')' is not a valid identifier.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eeBCuPp5HHA.5268@.TK2MSFTNGP02.phx.gbl...
> No, you would have to resort to dynamic SQL for that. A bit of a mess.
> Something like (to get you rolling):
> SET @.sql = 'DECLARE c CURSOR FOR
> SELECT ...
> FROM ' + @.db + '.dbo.sysobjects
> WHERE '
> EXEC @.sql
> OPEN c
> ...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jay" <nospam@.nospam.org> wrote in message
> news:OzvtLeo5HHA.2312@.TK2MSFTNGP06.phx.gbl...
>> The idea of performing CHECKTABLE on each table to spread the load out
>> across a week, month, or whatever was a compelling one, so I tried to run
>> a test.
>> Unfortunately, the list needs to be in a loop/cursor and I can't reset
>> the database inside a procedure (can I?)
>> DECLARE cTables CURSOR FOR
>> SELECT sc.name
>> FROM @.DB..sysobjects sc
>> WHERE xtype IN ('S', 'U')
>> Just plain doesn't work.
>> Is there a way to dynamically get the table names as I loop through the
>> databases?
>>
>> "Jay" <nospam@.nospam.org> wrote in message
>> news:%236s9kxb5HHA.2752@.TK2MSFTNGP06.phx.gbl...
>> Well now. Just my first read took 10 minutes, never mind fully
>> comprehending and following the links.
>> Thank you very much for the time you spent on the reply Tibor and rest
>> assured, the time a cat would spend picking apart a fish is nothing,
>> compared to what I'm going to do with it.
>> Just a couple comments for now:
>> I grasp the topic well because I was an Informix DBA for 15 years and
>> it's the same song, just a different tune.
>> I write all production destined code with strong error checks and find
>> ways to make it complain loudly is something unexpected/unwanted happens
>> (plus a once a week ping).
>> I chose INDEXDEFRAG because it is an online procedure and avoided
>> DBREINDEX because it is an offline procedure (24x7 after all). I do
>> remember a rather large discussion on this subject from about 6 months
>> ago, but it doesn't really matter, because if I get any scheduled
>> maintenance, it will be infrequent and limited.
>> Thanks,
>> Jay
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:%23HYDpSb5HHA.3940@.TK2MSFTNGP05.phx.gbl...
>> It certainly seems you grasp the topic well. Some comments:
>> Write a single stored procedure with an outer loop on DB's from
>> master..sysdatabases
>> Watch out for exceptions. What happens if for instance CHECKDB
>> encounters a corruption? Will it terminate the batch so nothing further
>> is executed? I can't answer that offhand, so thinking this through and
>> testing is important.
>> You might want to deliberately crash a database so that CHEDB fails to
>> test this realistically. I usually hack sysindexes (modify for instance
>> the FirstIAM column for some row to some rubbish value) - in a test
>> database of course. You aren't allowed to update system tables directly
>> by default but read about sp_configure and you will probably figure out
>> how to do it (I don't want to post any more descriptive instructions
>> considering this is a public forum).
>> Also, consider the client application handling of exceptions. SQL
>> Server Agent is nice, but a TSQL job step will actually terminate the
>> execution on any error (as I recall it). So, you might want to schedule
>> a CmdExec job step instead and call OSQL.EXE for the execution of your
>> proc.
>>
>> The only possible arguments I'm considering are NOINDEX and WITH
>> PHYSICAL_ONLY, but will probably go with no arguments.
>> Yes, go with as complete check as you can live with. If that doesn't
>> work for you use any of the options, possibly conditionally based on
>> for instance week day (like do full every Sunday). Btw, here's a
>> *great* blog on CHECKDB and related topics:
>> http://blogs.msdn.com/sqlserverstorageengine/default.aspx
>>
>> Within the loop, after a success on the CHECKDB (failure would exit
>> the loop and complain) run a script based on the one in 2000 BOL (DBCC
>> SHOWCONTIG) that does an INDEXDEFRAG on the index (BOL says this is an
>> online operation).
>> Yep, sounds fine. I guess you could spend lots of time with analyzing
>> how much fragmentation really hurts you at the table/index level and
>> have conditional handling based on that, but that is probably overkill.
>> Same goes for the fillfactor value. You can't specify a fillfactor
>> value for INDEXDEFRAG, it will re-apply the value you specified when
>> you created the index. If you want to read up on this topic, check out
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx.
>> Also, be aware of implications of defragging regarding transaction
>> logging too (so you don't be surprised that the following log backup is
>> potentially huge). And read up on recovery models so you understand all
>> three.
>>
>> Last, run the SQL from BOL to loop on when statistics were last
>> updated:
>> and update any old statistics.
>> First, be aware that if you rebuild (DBREINDEX) your indexes then the
>> statistics that comes with the index will also be rebuilt. This does
>> not happen if you INDEXDEFRAG, though.
>> Also, the database setting "auto update statistics" means that SQL
>> Server *will update statistics by itself*, as they becomes stale. Now,
>> doing a manual update doesn't hurt, especially if you have the time to
>> use FULLSCAN (see UPDATE STATISTICS command). In most cases, this
>> database setting should be left to the default value. SQL Server uses a
>> value in sysindexes, rowmodctr (I believe) to determine how many
>> modifications and whether it is time to auto-update (when a plan is to
>> be re-used or generated). This is a rather big topic, so here comes
>> another WP:
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
>> (there is one for 2000 as well, I don't have that URL handy, but this
>> is readable even if you aren't on 2005).
>> Note that the auto-create statistics option isn't the same as
>> auto-update. Auto-create means that the optimizer will *create*
>> statistics on non-indexed columns when it feels that such statistics
>> would be useful. Such statistics are also auto-updated by the engine by
>> the same handling as described above (the auto-update setting).
>> Finally, instead of having this loop, consider creating a table, in
>> which you have a row for each database, and here you configure what
>> actions are to be performed. Then you loop that table instead of
>> sysdatabases. I'm not saying it is better, all depends on ones
>> particular requirements etc.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Jay" <nospam@.nospam.org> wrote in message
>> news:ubj7n7a5HHA.3900@.TK2MSFTNGP02.phx.gbl...
>>I wasn't able to find much with an internet search, but digging in BOL
>>I found a lot.
>> Is this a good plan for 24x7 SQL Server 2000 maintenance (excluding
>> backup)?
>> Am I missing anything?
>> Am I doing more than is needed?
>> Did I miss something in BOL that makes this plan risky?
>> Here is my intended plan: write a procedure that uses CHECKDB,
>> SHOWCONTIG, INDEXDEFRAG, STATS_DATE & UPDATE STATISTICS. It would be
>> run either before, or after the application maintenance (archiving,
>> credit card batches, search engine data dumps, etc).
>> At no time will I allow an automated program shrink anything in the
>> Windows filesystem (and I hope I can keep anything from Auto Growing
>> too).
>> Write a single stored procedure with an outer loop on DB's from
>> master..sysdatabases
>> Start with DBCC CHECKDB('dbname') looping through all databases.
>> According to BOL, if you don't specify any of the check options,
>> nothing is locked and on the dev server, it is taking about 2-3
>> minutes to complete. The only possible arguments I'm considering are
>> NOINDEX and WITH PHYSICAL_ONLY, but will probably go with no
>> arguments.
>> Within the loop, after a success on the CHECKDB (failure would exit
>> the loop and complain) run a script based on the one in 2000 BOL (DBCC
>> SHOWCONTIG) that does an INDEXDEFRAG on the index (BOL says this is an
>> online operation).
>> Last, run the SQL from BOL to loop on when statistics were last
>> updated:
>> SELECT 'Index Name' = i.name,
>> 'Statistics Date' = STATS_DATE(i.id, i.indid)
>> FROM sysobjects o, sysindexes i
>> WHERE o.name = '_table_name_'
>> AND o.id = i.id
>> and update any old statistics.
>> Done.
>> Now, I'm not really sure how to decide what statistics are old. Under
>> Informix I had a counter that measured insert/update activity since
>> the last statistics update. Perhaps just do everything nightly?
>> I'm also not sure how the "Auto Create Statistics" checkbox on the
>> database prosperities/options tab interacts with update statistics.
>> Thanks,
>> Jay
>>
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> wrote in message news:OmJUAXY5HHA.4712@.TK2MSFTNGP04.phx.gbl...
>>> SQL Server 2000
>>> Also, in Management Studio the maintenance plans are under a Legacy
>>> heading and I haven't seen where maintenance is defined in 2005.
>> Seems you are using SSMS to connect to a 2000 instance. There are no
>> "new style" MP's on 2000, since they didn't exist in ... 2000. So
>> perhaps SSMS are showing 2000 (old style) MP's under the legacy
>> folder, quite simply?
>> Be care full especially with rebuilding indexes. Rebuilding an index
>> (the way 2000 MP's does it) will log the table during the index
>> rebuild. Also, DBCC CHECKDB will use resources (I'm not sure about
>> what level of locking it is using in 2000, but that should be
>> documented in the 2000 Books Online).
>> I believe that there's an "Operational Guideline" for 2000 available
>> on the MS web. It could be worth spending a few minutes with Google
>> to try to dig it up...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Jay" <nospam@.nospam.org> wrote in message
>> news:%23kB0DrQ5HHA.3900@.TK2MSFTNGP02.phx.gbl...
>>> SQL Server 2000
>>>
>>> I have finally been tasked and given access to the maintenance plans
>>> ... only there aren't any (including disk defrags)! Backups are done
>>> via an SQLagent job, but that's it.
>>>
>>> Normally I would just whomp the defaults together and start them
>>> running, but this is a 24x7x365 system and I'm not sure which of the
>>> jobs have the potential to lock a table, or an index.
>>>
>>> Advice, or links to docs that concern maintenance on a 24x7x365
>>> system.
>>>
>>> Also, in Management Studio the maintenance plans are under a Legacy
>>> heading and I haven't seen where maintenance is defined in 2005.
>>> What's up with that? Do I just need to dig deeper?
>>>
>>> Thanks,
>>> Jay
>>>
>>
>>
>>
>>
>|||declare @.sql nvarchar(4000)
declare @.db char(6)
set @.db = 'master'
SET @.sql = '
DECLARE c CURSOR FOR
SELECT name
FROM ' + @.db + '.dbo.sysobjects
WHERE xtype = ''S''
'
EXEC @.sql
--OPEN c
(as close as I can get you example to code that will work for everyone)
returns:
Server: Msg 911, Level 16, State 1, Line 11
Could not locate entry in sysdatabases for database '
DECLARE c CURSOR FOR
SELECT name
FROM master'. No entry found with that name. Make sure that the name is
entered correctly.|||Wait, the following seems to work.
--
declare @.sql nvarchar(4000)
declare @.db varchar(60)
declare @.table sysname
set @.db = 'master'
SET @.sql = '
DECLARE c CURSOR FOR
SELECT name
FROM ' + @.db + '.dbo.sysobjects
WHERE xtype = ''U''
'
PRINT '[' + @.sql + ']'
EXEC sp_executesql @.sql
OPEN c
fetch from c into @.table
while (@.@.fetch_status = 0)
begin
print @.table
fetch from c into @.table
end
CLOSE c
DEALLOCATE c
--
"Jay" <nospam@.nospam.org> wrote in message
news:Ob3SMap5HHA.1484@.TK2MSFTNGP06.phx.gbl...
> declare @.sql nvarchar(4000)
> declare @.db char(6)
> set @.db = 'master'
> SET @.sql = '
> DECLARE c CURSOR FOR
> SELECT name
> FROM ' + @.db + '.dbo.sysobjects
> WHERE xtype = ''S''
> '
> EXEC @.sql
> --OPEN c
> (as close as I can get you example to code that will work for everyone)
> returns:
> Server: Msg 911, Level 16, State 1, Line 11
> Could not locate entry in sysdatabases for database '
> DECLARE c CURSOR FOR
> SELECT name
> FROM master'. No entry found with that name. Make sure that the name is
> entered correctly.
>|||OK, this defiantly works, though I could have sworn I tried this. Guess the
frustration is getting to me a little.
Thank you Tibor.
declare @.sql nvarchar(4000)
declare @.db varchar(60)
declare @.table sysname
--set @.db = 'master'
declare cDB cursor for
SELECT name from master..sysdatabases sdb
WHERE sdb.name NOT IN ('tempdb')
open cDB
fetch cDB into @.db
while (@.@.fetch_status = 0)
begin
PRINT ''
PRINT ''
PRINT '=========>> ' + @.DB
SET @.sql = '
DECLARE c CURSOR FOR
SELECT name
FROM ' + @.db + '.dbo.sysobjects
WHERE xtype = ''U''
'
-- PRINT '[' + @.sql + ']'
EXEC sp_executesql @.sql
OPEN c
fetch from c into @.table
while (@.@.fetch_status = 0)
begin
print @.table
fetch from c into @.table
end
CLOSE c
DEALLOCATE c
fetch cDB into @.db
end
close cDB
deallocate cDB
"Jay" <nospam@.nospam.org> wrote in message
news:Ob3SMap5HHA.1484@.TK2MSFTNGP06.phx.gbl...
> declare @.sql nvarchar(4000)
> declare @.db char(6)
> set @.db = 'master'
> SET @.sql = '
> DECLARE c CURSOR FOR
> SELECT name
> FROM ' + @.db + '.dbo.sysobjects
> WHERE xtype = ''S''
> '
> EXEC @.sql
> --OPEN c
> (as close as I can get you example to code that will work for everyone)
> returns:
> Server: Msg 911, Level 16, State 1, Line 11
> Could not locate entry in sysdatabases for database '
> DECLARE c CURSOR FOR
> SELECT name
> FROM master'. No entry found with that name. Make sure that the name is
> entered correctly.
>|||Getting there. There's just one more thing I recommend, for robustness. If you execute below and
then run your cursor you will understand:
CREATE DATABASE "my database"
So, you can have databases and tables with names that aren't standard identifiers. I recommend you
cater for that in your cursor code. A couple of ways to do that:
SELECT '"' + name + '"' from master..sysdatabases sdb
or
SELECT QUOTENAME(name) from master..sysdatabases sdb
If you don't want to do it in the SELECT which retreives the name of the database, you can of course
do it like below instead:
SET @.sql = '
DECLARE c CURSOR FOR
SELECT name
FROM ' + QUOTENAME(@.db) + '.dbo.sysobjects
WHERE xtype = ''U''
'
In SQL Server you can surround an object name (identifier) with either double-quotes (ANSI SQL
standard) or square bracket. Many SQL Server preople prefer square brackets, I prefer double quotes.
Doesn't really matter.
Also, QUOTENAME is a little bit more robust, since it will (should) handle if you have a database
named something like my"Data[base], which just slapping double quotes or square brackets around
doesn't handle. Not likely that you have that extremely strange names, of course.
And, of course, you should do the same for the inner cursor, where you pick up the table name
(depending on how you are going to use it).
Also, you might want to expand the table name to include the object owner, again, depending on how
you are going to use it. If you have for instance pete.customers, then doing something like DBCC
DBREINDEX('customers') will probably refer to dbo.customers, and either do the wrong table or fail.
You can for instance join sysobjects to sysusers.
However, if you base index defrag on output from DBCC SHOWCONTIG, I don't think you have to worry
about it, but it was a while since I read that code snippet in Books Online. Just one more thing to
think of...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <nospam@.nospam.org> wrote in message news:ulDpYjp5HHA.3940@.TK2MSFTNGP05.phx.gbl...
> OK, this defiantly works, though I could have sworn I tried this. Guess the frustration is getting
> to me a little.
> Thank you Tibor.
> declare @.sql nvarchar(4000)
> declare @.db varchar(60)
> declare @.table sysname
> --set @.db = 'master'
> declare cDB cursor for
> SELECT name from master..sysdatabases sdb
> WHERE sdb.name NOT IN ('tempdb')
> open cDB
> fetch cDB into @.db
> while (@.@.fetch_status = 0)
> begin
> PRINT ''
> PRINT ''
> PRINT '=========>> ' + @.DB
> SET @.sql = '
> DECLARE c CURSOR FOR
> SELECT name
> FROM ' + @.db + '.dbo.sysobjects
> WHERE xtype = ''U''
> '
> -- PRINT '[' + @.sql + ']'
> EXEC sp_executesql @.sql
> OPEN c
> fetch from c into @.table
> while (@.@.fetch_status = 0)
> begin
> print @.table
> fetch from c into @.table
> end
> CLOSE c
> DEALLOCATE c
> fetch cDB into @.db
> end
> close cDB
> deallocate cDB
> "Jay" <nospam@.nospam.org> wrote in message news:Ob3SMap5HHA.1484@.TK2MSFTNGP06.phx.gbl...
>> declare @.sql nvarchar(4000)
>> declare @.db char(6)
>> set @.db = 'master'
>> SET @.sql = '
>> DECLARE c CURSOR FOR
>> SELECT name
>> FROM ' + @.db + '.dbo.sysobjects
>> WHERE xtype = ''S''
>> '
>> EXEC @.sql
>> --OPEN c
>> (as close as I can get you example to code that will work for everyone)
>> returns:
>> Server: Msg 911, Level 16, State 1, Line 11
>> Could not locate entry in sysdatabases for database '
>> DECLARE c CURSOR FOR
>> SELECT name
>> FROM master'. No entry found with that name. Make sure that the name is entered correctly.
>>
>|||I had already changed the table level code to include the owner, so ya,
putting on the DB make all the sense in the world, thanks.
As to the QUOTENAME, I debated a little what to do, as I was already quoting
on the use of the variables. However, I think I like it as it provides some
cheap insurance and cleans up the code a touch. As to the problem with
spaces in an object name, I'm well aware of that issue and it was one of the
things I really hate about Windows.
The lack of the fully qualified names was never intended to be left. It goes
to how I code something when I don't really know what I'm dealing with. I
try to keep things as simple as possible. However, I always make several
cleanup passes and fully qualify anything I'm referencing before anyone else
knows I'm close to finished (with the exception of a programmer who
understands).
And shame, shame shame on you for even the slightest suggestion that
DBREINDEX would be used in an automated nightly program running on a 24x7
system :)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:usHxQuu5HHA.5184@.TK2MSFTNGP03.phx.gbl...
> Getting there. There's just one more thing I recommend, for robustness. If
> you execute below and then run your cursor you will understand:
> CREATE DATABASE "my database"
> So, you can have databases and tables with names that aren't standard
> identifiers. I recommend you cater for that in your cursor code. A couple
> of ways to do that:
> SELECT '"' + name + '"' from master..sysdatabases sdb
> or
> SELECT QUOTENAME(name) from master..sysdatabases sdb
> If you don't want to do it in the SELECT which retreives the name of the
> database, you can of course do it like below instead:
> SET @.sql = '
> DECLARE c CURSOR FOR
> SELECT name
> FROM ' + QUOTENAME(@.db) + '.dbo.sysobjects
> WHERE xtype = ''U''
> '
> In SQL Server you can surround an object name (identifier) with either
> double-quotes (ANSI SQL standard) or square bracket. Many SQL Server
> preople prefer square brackets, I prefer double quotes. Doesn't really
> matter.
> Also, QUOTENAME is a little bit more robust, since it will (should) handle
> if you have a database named something like my"Data[base], which just
> slapping double quotes or square brackets around doesn't handle. Not
> likely that you have that extremely strange names, of course.
> And, of course, you should do the same for the inner cursor, where you
> pick up the table name (depending on how you are going to use it).
> Also, you might want to expand the table name to include the object owner,
> again, depending on how you are going to use it. If you have for instance
> pete.customers, then doing something like DBCC DBREINDEX('customers') will
> probably refer to dbo.customers, and either do the wrong table or fail.
> You can for instance join sysobjects to sysusers.
> However, if you base index defrag on output from DBCC SHOWCONTIG, I don't
> think you have to worry about it, but it was a while since I read that
> code snippet in Books Online. Just one more thing to think of...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jay" <nospam@.nospam.org> wrote in message
> news:ulDpYjp5HHA.3940@.TK2MSFTNGP05.phx.gbl...
>> OK, this defiantly works, though I could have sworn I tried this. Guess
>> the frustration is getting to me a little.
>> Thank you Tibor.
>> declare @.sql nvarchar(4000)
>> declare @.db varchar(60)
>> declare @.table sysname
>> --set @.db = 'master'
>> declare cDB cursor for
>> SELECT name from master..sysdatabases sdb
>> WHERE sdb.name NOT IN ('tempdb')
>> open cDB
>> fetch cDB into @.db
>> while (@.@.fetch_status = 0)
>> begin
>> PRINT ''
>> PRINT ''
>> PRINT '=========>> ' + @.DB
>> SET @.sql = '
>> DECLARE c CURSOR FOR
>> SELECT name
>> FROM ' + @.db + '.dbo.sysobjects
>> WHERE xtype = ''U''
>> '
>> -- PRINT '[' + @.sql + ']'
>> EXEC sp_executesql @.sql
>> OPEN c
>> fetch from c into @.table
>> while (@.@.fetch_status = 0)
>> begin
>> print @.table
>> fetch from c into @.table
>> end
>> CLOSE c
>> DEALLOCATE c
>> fetch cDB into @.db
>> end
>> close cDB
>> deallocate cDB
>> "Jay" <nospam@.nospam.org> wrote in message
>> news:Ob3SMap5HHA.1484@.TK2MSFTNGP06.phx.gbl...
>> declare @.sql nvarchar(4000)
>> declare @.db char(6)
>> set @.db = 'master'
>> SET @.sql = '
>> DECLARE c CURSOR FOR
>> SELECT name
>> FROM ' + @.db + '.dbo.sysobjects
>> WHERE xtype = ''S''
>> '
>> EXEC @.sql
>> --OPEN c
>> (as close as I can get you example to code that will work for everyone)
>> returns:
>> Server: Msg 911, Level 16, State 1, Line 11
>> Could not locate entry in sysdatabases for database '
>> DECLARE c CURSOR FOR
>> SELECT name
>> FROM master'. No entry found with that name. Make sure that the name is
>> entered correctly.
>>
>>
>|||A curosity.
DBCC CHECKCATALOG and DBCC CHECKALLOC seem to refuse an owner name and will
only accept a database name.
DBCC CHECKALLOC('model.dbo') & DBCC CHECKALLOC('dbo.model') both fail
complaining that it can't find the database, while DBCC CHECKALLOC('model')
works fine.
I did this on 2005 Express, which is what I have at home.
So, I guess pulling the database owner is pointless - pulling the table
owner is still usefull.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:usHxQuu5HHA.5184@.TK2MSFTNGP03.phx.gbl...
> Getting there. There's just one more thing I recommend, for robustness. If
> you execute below and then run your cursor you will understand:
> CREATE DATABASE "my database"
> So, you can have databases and tables with names that aren't standard
> identifiers. I recommend you cater for that in your cursor code. A couple
> of ways to do that:
> SELECT '"' + name + '"' from master..sysdatabases sdb
> or
> SELECT QUOTENAME(name) from master..sysdatabases sdb
> If you don't want to do it in the SELECT which retreives the name of the
> database, you can of course do it like below instead:
> SET @.sql = '
> DECLARE c CURSOR FOR
> SELECT name
> FROM ' + QUOTENAME(@.db) + '.dbo.sysobjects
> WHERE xtype = ''U''
> '
> In SQL Server you can surround an object name (identifier) with either
> double-quotes (ANSI SQL standard) or square bracket. Many SQL Server
> preople prefer square brackets, I prefer double quotes. Doesn't really
> matter.
> Also, QUOTENAME is a little bit more robust, since it will (should) handle
> if you have a database named something like my"Data[base], which just
> slapping double quotes or square brackets around doesn't handle. Not
> likely that you have that extremely strange names, of course.
> And, of course, you should do the same for the inner cursor, where you
> pick up the table name (depending on how you are going to use it).
> Also, you might want to expand the table name to include the object owner,
> again, depending on how you are going to use it. If you have for instance
> pete.customers, then doing something like DBCC DBREINDEX('customers') will
> probably refer to dbo.customers, and either do the wrong table or fail.
> You can for instance join sysobjects to sysusers.
> However, if you base index defrag on output from DBCC SHOWCONTIG, I don't
> think you have to worry about it, but it was a while since I read that
> code snippet in Books Online. Just one more thing to think of...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jay" <nospam@.nospam.org> wrote in message
> news:ulDpYjp5HHA.3940@.TK2MSFTNGP05.phx.gbl...
>> OK, this defiantly works, though I could have sworn I tried this. Guess
>> the frustration is getting to me a little.
>> Thank you Tibor.
>> declare @.sql nvarchar(4000)
>> declare @.db varchar(60)
>> declare @.table sysname
>> --set @.db = 'master'
>> declare cDB cursor for
>> SELECT name from master..sysdatabases sdb
>> WHERE sdb.name NOT IN ('tempdb')
>> open cDB
>> fetch cDB into @.db
>> while (@.@.fetch_status = 0)
>> begin
>> PRINT ''
>> PRINT ''
>> PRINT '=========>> ' + @.DB
>> SET @.sql = '
>> DECLARE c CURSOR FOR
>> SELECT name
>> FROM ' + @.db + '.dbo.sysobjects
>> WHERE xtype = ''U''
>> '
>> -- PRINT '[' + @.sql + ']'
>> EXEC sp_executesql @.sql
>> OPEN c
>> fetch from c into @.table
>> while (@.@.fetch_status = 0)
>> begin
>> print @.table
>> fetch from c into @.table
>> end
>> CLOSE c
>> DEALLOCATE c
>> fetch cDB into @.db
>> end
>> close cDB
>> deallocate cDB
>> "Jay" <nospam@.nospam.org> wrote in message
>> news:Ob3SMap5HHA.1484@.TK2MSFTNGP06.phx.gbl...
>> declare @.sql nvarchar(4000)
>> declare @.db char(6)
>> set @.db = 'master'
>> SET @.sql = '
>> DECLARE c CURSOR FOR
>> SELECT name
>> FROM ' + @.db + '.dbo.sysobjects
>> WHERE xtype = ''S''
>> '
>> EXEC @.sql
>> --OPEN c
>> (as close as I can get you example to code that will work for everyone)
>> returns:
>> Server: Msg 911, Level 16, State 1, Line 11
>> Could not locate entry in sysdatabases for database '
>> DECLARE c CURSOR FOR
>> SELECT name
>> FROM master'. No entry found with that name. Make sure that the name is
>> entered correctly.
>>
>>
>|||> And shame, shame shame on you for even the slightest suggestion that
> DBREINDEX would be used in an automated nightly program running on a 24x7
> system :)
Down, boy, down. That was just an example of a command... ;-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"JayKon" <spam@.nospam.org> wrote in message news:eF0u1Iy5HHA.980@.TK2MSFTNGP06.phx.gbl...
>I had already changed the table level code to include the owner, so ya,
> putting on the DB make all the sense in the world, thanks.
> As to the QUOTENAME, I debated a little what to do, as I was already quoting
> on the use of the variables. However, I think I like it as it provides some
> cheap insurance and cleans up the code a touch. As to the problem with
> spaces in an object name, I'm well aware of that issue and it was one of the
> things I really hate about Windows.
> The lack of the fully qualified names was never intended to be left. It goes
> to how I code something when I don't really know what I'm dealing with. I
> try to keep things as simple as possible. However, I always make several
> cleanup passes and fully qualify anything I'm referencing before anyone else
> knows I'm close to finished (with the exception of a programmer who
> understands).
> And shame, shame shame on you for even the slightest suggestion that
> DBREINDEX would be used in an automated nightly program running on a 24x7
> system :)
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:usHxQuu5HHA.5184@.TK2MSFTNGP03.phx.gbl...
>> Getting there. There's just one more thing I recommend, for robustness. If
>> you execute below and then run your cursor you will understand:
>> CREATE DATABASE "my database"
>> So, you can have databases and tables with names that aren't standard
>> identifiers. I recommend you cater for that in your cursor code. A couple
>> of ways to do that:
>> SELECT '"' + name + '"' from master..sysdatabases sdb
>> or
>> SELECT QUOTENAME(name) from master..sysdatabases sdb
>> If you don't want to do it in the SELECT which retreives the name of the
>> database, you can of course do it like below instead:
>> SET @.sql = '
>> DECLARE c CURSOR FOR
>> SELECT name
>> FROM ' + QUOTENAME(@.db) + '.dbo.sysobjects
>> WHERE xtype = ''U''
>> '
>> In SQL Server you can surround an object name (identifier) with either
>> double-quotes (ANSI SQL standard) or square bracket. Many SQL Server
>> preople prefer square brackets, I prefer double quotes. Doesn't really
>> matter.
>> Also, QUOTENAME is a little bit more robust, since it will (should) handle
>> if you have a database named something like my"Data[base], which just
>> slapping double quotes or square brackets around doesn't handle. Not
>> likely that you have that extremely strange names, of course.
>> And, of course, you should do the same for the inner cursor, where you
>> pick up the table name (depending on how you are going to use it).
>> Also, you might want to expand the table name to include the object owner,
>> again, depending on how you are going to use it. If you have for instance
>> pete.customers, then doing something like DBCC DBREINDEX('customers') will
>> probably refer to dbo.customers, and either do the wrong table or fail.
>> You can for instance join sysobjects to sysusers.
>> However, if you base index defrag on output from DBCC SHOWCONTIG, I don't
>> think you have to worry about it, but it was a while since I read that
>> code snippet in Books Online. Just one more thing to think of...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Jay" <nospam@.nospam.org> wrote in message
>> news:ulDpYjp5HHA.3940@.TK2MSFTNGP05.phx.gbl...
>> OK, this defiantly works, though I could have sworn I tried this. Guess
>> the frustration is getting to me a little.
>> Thank you Tibor.
>> declare @.sql nvarchar(4000)
>> declare @.db varchar(60)
>> declare @.table sysname
>> --set @.db = 'master'
>> declare cDB cursor for
>> SELECT name from master..sysdatabases sdb
>> WHERE sdb.name NOT IN ('tempdb')
>> open cDB
>> fetch cDB into @.db
>> while (@.@.fetch_status = 0)
>> begin
>> PRINT ''
>> PRINT ''
>> PRINT '=========>> ' + @.DB
>> SET @.sql = '
>> DECLARE c CURSOR FOR
>> SELECT name
>> FROM ' + @.db + '.dbo.sysobjects
>> WHERE xtype = ''U''
>> '
>> -- PRINT '[' + @.sql + ']'
>> EXEC sp_executesql @.sql
>> OPEN c
>> fetch from c into @.table
>> while (@.@.fetch_status = 0)
>> begin
>> print @.table
>> fetch from c into @.table
>> end
>> CLOSE c
>> DEALLOCATE c
>> fetch cDB into @.db
>> end
>> close cDB
>> deallocate cDB
>> "Jay" <nospam@.nospam.org> wrote in message
>> news:Ob3SMap5HHA.1484@.TK2MSFTNGP06.phx.gbl...
>> declare @.sql nvarchar(4000)
>> declare @.db char(6)
>> set @.db = 'master'
>> SET @.sql = '
>> DECLARE c CURSOR FOR
>> SELECT name
>> FROM ' + @.db + '.dbo.sysobjects
>> WHERE xtype = ''S''
>> '
>> EXEC @.sql
>> --OPEN c
>> (as close as I can get you example to code that will work for everyone)
>> returns:
>> Server: Msg 911, Level 16, State 1, Line 11
>> Could not locate entry in sysdatabases for database '
>> DECLARE c CURSOR FOR
>> SELECT name
>> FROM master'. No entry found with that name. Make sure that the name is
>> entered correctly.
>>
>>
>|||> DBCC CHECKALLOC('model.dbo') & DBCC CHECKALLOC('dbo.model') both fail
<snip>
> So, I guess pulling the database owner is pointless - pulling the table owner is still usefull.
Hmm, a database doesn't have an owner in that sense. Well..., it has an owner. A login owns a
database and that login is the user dbo in the database. But a database doesn't have an owner seen
from a name resolution standpoint.
Since the owner of the database is always the user dbo, you would always have pubs.dbo and
Northwind.dbo, you couldn't have *anything else* but .dbo. One could argue that we would use
dbname.loginname instead of dbname.username, of course. But basically the purpose of a namespace is
to keep names unique, so having any type of owner in the db name doesn't add anything, since the db
name alone need to be unique in SQL Server. You cannot have two logins each owning a database of the
same name (i.e., we cannot have JayKon.pubs and Tibor.pubs or something like that). So whenever you
see "database name", it is always *only* the database name (like pubs).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"JayKon" <spam@.nospam.org> wrote in message news:OzpC2jy5HHA.600@.TK2MSFTNGP05.phx.gbl...
>A curosity.
> DBCC CHECKCATALOG and DBCC CHECKALLOC seem to refuse an owner name and will only accept a database
> name.
> DBCC CHECKALLOC('model.dbo') & DBCC CHECKALLOC('dbo.model') both fail complaining that it can't
> find the database, while DBCC CHECKALLOC('model') works fine.
> I did this on 2005 Express, which is what I have at home.
> So, I guess pulling the database owner is pointless - pulling the table owner is still usefull.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:usHxQuu5HHA.5184@.TK2MSFTNGP03.phx.gbl...
>> Getting there. There's just one more thing I recommend, for robustness. If you execute below and
>> then run your cursor you will understand:
>> CREATE DATABASE "my database"
>> So, you can have databases and tables with names that aren't standard identifiers. I recommend
>> you cater for that in your cursor code. A couple of ways to do that:
>> SELECT '"' + name + '"' from master..sysdatabases sdb
>> or
>> SELECT QUOTENAME(name) from master..sysdatabases sdb
>> If you don't want to do it in the SELECT which retreives the name of the database, you can of
>> course do it like below instead:
>> SET @.sql = '
>> DECLARE c CURSOR FOR
>> SELECT name
>> FROM ' + QUOTENAME(@.db) + '.dbo.sysobjects
>> WHERE xtype = ''U''
>> '
>> In SQL Server you can surround an object name (identifier) with either double-quotes (ANSI SQL
>> standard) or square bracket. Many SQL Server preople prefer square brackets, I prefer double
>> quotes. Doesn't really matter.
>> Also, QUOTENAME is a little bit more robust, since it will (should) handle if you have a database
>> named something like my"Data[base], which just slapping double quotes or square brackets around
>> doesn't handle. Not likely that you have that extremely strange names, of course.
>> And, of course, you should do the same for the inner cursor, where you pick up the table name
>> (depending on how you are going to use it).
>> Also, you might want to expand the table name to include the object owner, again, depending on
>> how you are going to use it. If you have for instance pete.customers, then doing something like
>> DBCC DBREINDEX('customers') will probably refer to dbo.customers, and either do the wrong table
>> or fail. You can for instance join sysobjects to sysusers.
>> However, if you base index defrag on output from DBCC SHOWCONTIG, I don't think you have to worry
>> about it, but it was a while since I read that code snippet in Books Online. Just one more thing
>> to think of...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Jay" <nospam@.nospam.org> wrote in message news:ulDpYjp5HHA.3940@.TK2MSFTNGP05.phx.gbl...
>> OK, this defiantly works, though I could have sworn I tried this. Guess the frustration is
>> getting to me a little.
>> Thank you Tibor.
>> declare @.sql nvarchar(4000)
>> declare @.db varchar(60)
>> declare @.table sysname
>> --set @.db = 'master'
>> declare cDB cursor for
>> SELECT name from master..sysdatabases sdb
>> WHERE sdb.name NOT IN ('tempdb')
>> open cDB
>> fetch cDB into @.db
>> while (@.@.fetch_status = 0)
>> begin
>> PRINT ''
>> PRINT ''
>> PRINT '=========>> ' + @.DB
>> SET @.sql = '
>> DECLARE c CURSOR FOR
>> SELECT name
>> FROM ' + @.db + '.dbo.sysobjects
>> WHERE xtype = ''U''
>> '
>> -- PRINT '[' + @.sql + ']'
>> EXEC sp_executesql @.sql
>> OPEN c
>> fetch from c into @.table
>> while (@.@.fetch_status = 0)
>> begin
>> print @.table
>> fetch from c into @.table
>> end
>> CLOSE c
>> DEALLOCATE c
>> fetch cDB into @.db
>> end
>> close cDB
>> deallocate cDB
>> "Jay" <nospam@.nospam.org> wrote in message news:Ob3SMap5HHA.1484@.TK2MSFTNGP06.phx.gbl...
>> declare @.sql nvarchar(4000)
>> declare @.db char(6)
>> set @.db = 'master'
>> SET @.sql = '
>> DECLARE c CURSOR FOR
>> SELECT name
>> FROM ' + @.db + '.dbo.sysobjects
>> WHERE xtype = ''S''
>> '
>> EXEC @.sql
>> --OPEN c
>> (as close as I can get you example to code that will work for everyone)
>> returns:
>> Server: Msg 911, Level 16, State 1, Line 11
>> Could not locate entry in sysdatabases for database '
>> DECLARE c CURSOR FOR
>> SELECT name
>> FROM master'. No entry found with that name. Make sure that the name is entered correctly.
>>
>>
>
