Hello
I'm in the proces of a major revision of the maintenance plans for the SQL servers in our company, and in connection with that I would like to hear how other people are doing this.
Here's a quick rundown of the plan:
Critical DBs will be backed up on tape (daily incremental, weekly full, w. Veritas Backup Exec 9.0). Also, there will be full daily disk backups for easy quick recovery. These will be done locally, as I have bad experiences trying to backup across a network share.
DBs of medium importance will be backed up fully every day on disk. These BAK-files will then be backed up on tape, if I find it necessary.
Although I rarely restore from the tapes, I think they're nice to have in case the office burns down or who knows what.
The maintenance plans will be split into 3:
1. System DBs maint. plan
2. Critical importance DBs maint. plan
3. Medium importance DBs maint. plan
The more I think about it, the more I think I might just classify all production DBs as critical and all test DBs as medium. Maybe that would make more sense.
For all disk backups, optimization and integrity checks (and backup) will be done daily. For DBs of critical importance (eg. production DBs) Transaction log back will be done as well. What's a good schedule for this? Once every 3 hours or so? Every hour? How much burden does this operation put on the server?
I guess that's about it so far. If anyone has any suggestions or comments, I would be very pleased to hear them.
MNJFrequency of trx. log backups depends on the level of activity of action queries and recoverability requirements. In one of our databases here we're doing 15-minute trx. log dumps and the resulting file varies from 800MB to 2.5GB in size. Another database barely creates a 100K logs but we're doing dumps every 30 minutes for its point-in-time recoverability requirements. It all depends.
As per your breakdown, it looks good. But as our disaster recovery excersises shown, - it's beneficial to have your system databases backed up last. Here we're using SQLMAINT utility to run our maintenance plans (SQLMAINT -PlanName <app_db_maint_plan>). This way it's easier to sequence the steps to your likes. Also, do make sure you log all outputs, in case something goes south :)|||[i]As per your breakdown, it looks good. But as our disaster recovery excersises shown, - it's beneficial to have your system databases backed up last. Here we're using SQLMAINT utility to run our maintenance plans (SQLMAINT -PlanName <app_db_maint_plan>). This way it's easier to sequence the steps to your likes. Also, do make sure you log all outputs, in case something goes south :)
Why is it beneficial to backup the system DBs last? Since I keep system and user DBs separated into different maint. plans, I guess I can just schedule the system maint. plan to occur 15 min. after the user main. plan?
MNJ|||For one, if MSDB is backed up last, - it will contain the latest backup information of all other databases, as well as itself. This information is available when looking at the General tab of database Properties window.
As per scheduling, - as I said earlier, I have execution of all maintenance plans in one batch with SQLMAINT. If you're using Scheduled Tasks, then you can add a step with SQLMAINT -PlanName <sys_db_maint_plan> after your application databases.|||Good point with system DBs, I will take that into consideration. I suppose if I just make sure to schedule them a bit apart, it should work out ok.
MNJ
Showing posts with label maintenance. Show all posts
Showing posts with label maintenance. Show all posts
Friday, March 30, 2012
Friday, March 23, 2012
Rebuilding indexes in SQL 2000
All
Is there any way to estimate how long it would take to drop and rebuild the
indexes on a database. I have a farily tight maintenance window on a payroll
database and need to be sure that any work would be completed in time. I
assume the database size and amount of tables has a major bearing but not
sure where I would go from that info.
Thanks
Si
Simon
You can determine what tables are heavely defragmented and run rebuilt
index only on them
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:5D6C2F49-E817-4E24-893A-B108549F5331@.microsoft.com...
> All
> Is there any way to estimate how long it would take to drop and rebuild
> the
> indexes on a database. I have a farily tight maintenance window on a
> payroll
> database and need to be sure that any work would be completed in time. I
> assume the database size and amount of tables has a major bearing but not
> sure where I would go from that info.
> Thanks
> Si
|||On Feb 27, 9:37 am, Simon <S...@.discussions.microsoft.com> wrote:
> All
> Is there any way to estimate how long it would take to drop and rebuild the
> indexes on a database. I have a farily tight maintenance window on a payroll
> database and need to be sure that any work would be completed in time. I
> assume the database size and amount of tables has a major bearing but not
> sure where I would go from that info.
> Thanks
> Si
Rather than rebuild ALL of the indexes, consider rebuilding only those
that are badly fragmented. Rebuilding all of them is usually
unnecessary. Here's a script that might help you:
http://realsqlguy.blogspot.com/2007/02/smart-index-defragmentation.html
|||Simon,
there are too many variables in your case for an estimation from someone
external to work.
You could generate your own decent estimate if you restore a recent backup
onto a test server of similar specs and run the rebuild there.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||The problem I have is the database has around 600 tables, of which 350 need
rebuilding. There is also a high level of fragmentation so I thought that
Reindexing would be quicker.
"Tracy McKibben" wrote:
> On Feb 27, 9:37 am, Simon <S...@.discussions.microsoft.com> wrote:
> Rather than rebuild ALL of the indexes, consider rebuilding only those
> that are badly fragmented. Rebuilding all of them is usually
> unnecessary. Here's a script that might help you:
> http://realsqlguy.blogspot.com/2007/02/smart-index-defragmentation.html
>
Is there any way to estimate how long it would take to drop and rebuild the
indexes on a database. I have a farily tight maintenance window on a payroll
database and need to be sure that any work would be completed in time. I
assume the database size and amount of tables has a major bearing but not
sure where I would go from that info.
Thanks
Si
Simon
You can determine what tables are heavely defragmented and run rebuilt
index only on them
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:5D6C2F49-E817-4E24-893A-B108549F5331@.microsoft.com...
> All
> Is there any way to estimate how long it would take to drop and rebuild
> the
> indexes on a database. I have a farily tight maintenance window on a
> payroll
> database and need to be sure that any work would be completed in time. I
> assume the database size and amount of tables has a major bearing but not
> sure where I would go from that info.
> Thanks
> Si
|||On Feb 27, 9:37 am, Simon <S...@.discussions.microsoft.com> wrote:
> All
> Is there any way to estimate how long it would take to drop and rebuild the
> indexes on a database. I have a farily tight maintenance window on a payroll
> database and need to be sure that any work would be completed in time. I
> assume the database size and amount of tables has a major bearing but not
> sure where I would go from that info.
> Thanks
> Si
Rather than rebuild ALL of the indexes, consider rebuilding only those
that are badly fragmented. Rebuilding all of them is usually
unnecessary. Here's a script that might help you:
http://realsqlguy.blogspot.com/2007/02/smart-index-defragmentation.html
|||Simon,
there are too many variables in your case for an estimation from someone
external to work.
You could generate your own decent estimate if you restore a recent backup
onto a test server of similar specs and run the rebuild there.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||The problem I have is the database has around 600 tables, of which 350 need
rebuilding. There is also a high level of fragmentation so I thought that
Reindexing would be quicker.
"Tracy McKibben" wrote:
> On Feb 27, 9:37 am, Simon <S...@.discussions.microsoft.com> wrote:
> Rather than rebuild ALL of the indexes, consider rebuilding only those
> that are badly fragmented. Rebuilding all of them is usually
> unnecessary. Here's a script that might help you:
> http://realsqlguy.blogspot.com/2007/02/smart-index-defragmentation.html
>
Rebuilding indexes in SQL 2000
All
Is there any way to estimate how long it would take to drop and rebuild the
indexes on a database. I have a farily tight maintenance window on a payroll
database and need to be sure that any work would be completed in time. I
assume the database size and amount of tables has a major bearing but not
sure where I would go from that info.
Thanks
SiSimon
You can determine what tables are heavely defragmented and run rebuilt
index only on them
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:5D6C2F49-E817-4E24-893A-B108549F5331@.microsoft.com...
> All
> Is there any way to estimate how long it would take to drop and rebuild
> the
> indexes on a database. I have a farily tight maintenance window on a
> payroll
> database and need to be sure that any work would be completed in time. I
> assume the database size and amount of tables has a major bearing but not
> sure where I would go from that info.
> Thanks
> Si|||On Feb 27, 9:37 am, Simon <S...@.discussions.microsoft.com> wrote:
> All
> Is there any way to estimate how long it would take to drop and rebuild th
e
> indexes on a database. I have a farily tight maintenance window on a payro
ll
> database and need to be sure that any work would be completed in time. I
> assume the database size and amount of tables has a major bearing but not
> sure where I would go from that info.
> Thanks
> Si
Rather than rebuild ALL of the indexes, consider rebuilding only those
that are badly fragmented. Rebuilding all of them is usually
unnecessary. Here's a script that might help you:
http://realsqlguy.blogspot.com/2007...gmentation.html|||Simon,
there are too many variables in your case for an estimation from someone
external to work.
You could generate your own decent estimate if you restore a recent backup
onto a test server of similar specs and run the rebuild there.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||The problem I have is the database has around 600 tables, of which 350 need
rebuilding. There is also a high level of fragmentation so I thought that
Reindexing would be quicker.
"Tracy McKibben" wrote:
> On Feb 27, 9:37 am, Simon <S...@.discussions.microsoft.com> wrote:
> Rather than rebuild ALL of the indexes, consider rebuilding only those
> that are badly fragmented. Rebuilding all of them is usually
> unnecessary. Here's a script that might help you:
> http://realsqlguy.blogspot.com/2007...gmentation.html
>sql
Is there any way to estimate how long it would take to drop and rebuild the
indexes on a database. I have a farily tight maintenance window on a payroll
database and need to be sure that any work would be completed in time. I
assume the database size and amount of tables has a major bearing but not
sure where I would go from that info.
Thanks
SiSimon
You can determine what tables are heavely defragmented and run rebuilt
index only on them
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:5D6C2F49-E817-4E24-893A-B108549F5331@.microsoft.com...
> All
> Is there any way to estimate how long it would take to drop and rebuild
> the
> indexes on a database. I have a farily tight maintenance window on a
> payroll
> database and need to be sure that any work would be completed in time. I
> assume the database size and amount of tables has a major bearing but not
> sure where I would go from that info.
> Thanks
> Si|||On Feb 27, 9:37 am, Simon <S...@.discussions.microsoft.com> wrote:
> All
> Is there any way to estimate how long it would take to drop and rebuild th
e
> indexes on a database. I have a farily tight maintenance window on a payro
ll
> database and need to be sure that any work would be completed in time. I
> assume the database size and amount of tables has a major bearing but not
> sure where I would go from that info.
> Thanks
> Si
Rather than rebuild ALL of the indexes, consider rebuilding only those
that are badly fragmented. Rebuilding all of them is usually
unnecessary. Here's a script that might help you:
http://realsqlguy.blogspot.com/2007...gmentation.html|||Simon,
there are too many variables in your case for an estimation from someone
external to work.
You could generate your own decent estimate if you restore a recent backup
onto a test server of similar specs and run the rebuild there.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||The problem I have is the database has around 600 tables, of which 350 need
rebuilding. There is also a high level of fragmentation so I thought that
Reindexing would be quicker.
"Tracy McKibben" wrote:
> On Feb 27, 9:37 am, Simon <S...@.discussions.microsoft.com> wrote:
> Rather than rebuild ALL of the indexes, consider rebuilding only those
> that are badly fragmented. Rebuilding all of them is usually
> unnecessary. Here's a script that might help you:
> http://realsqlguy.blogspot.com/2007...gmentation.html
>sql
Rebuilding indexes in SQL 2000
All
Is there any way to estimate how long it would take to drop and rebuild the
indexes on a database. I have a farily tight maintenance window on a payroll
database and need to be sure that any work would be completed in time. I
assume the database size and amount of tables has a major bearing but not
sure where I would go from that info.
Thanks
SiSimon
You can determine what tables are heavely defragmented and run rebuilt
index only on them
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:5D6C2F49-E817-4E24-893A-B108549F5331@.microsoft.com...
> All
> Is there any way to estimate how long it would take to drop and rebuild
> the
> indexes on a database. I have a farily tight maintenance window on a
> payroll
> database and need to be sure that any work would be completed in time. I
> assume the database size and amount of tables has a major bearing but not
> sure where I would go from that info.
> Thanks
> Si|||On Feb 27, 9:37 am, Simon <S...@.discussions.microsoft.com> wrote:
> All
> Is there any way to estimate how long it would take to drop and rebuild the
> indexes on a database. I have a farily tight maintenance window on a payroll
> database and need to be sure that any work would be completed in time. I
> assume the database size and amount of tables has a major bearing but not
> sure where I would go from that info.
> Thanks
> Si
Rather than rebuild ALL of the indexes, consider rebuilding only those
that are badly fragmented. Rebuilding all of them is usually
unnecessary. Here's a script that might help you:
http://realsqlguy.blogspot.com/2007/02/smart-index-defragmentation.html|||Simon,
there are too many variables in your case for an estimation from someone
external to work.
You could generate your own decent estimate if you restore a recent backup
onto a test server of similar specs and run the rebuild there.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||The problem I have is the database has around 600 tables, of which 350 need
rebuilding. There is also a high level of fragmentation so I thought that
Reindexing would be quicker.
"Tracy McKibben" wrote:
> On Feb 27, 9:37 am, Simon <S...@.discussions.microsoft.com> wrote:
> > All
> >
> > Is there any way to estimate how long it would take to drop and rebuild the
> > indexes on a database. I have a farily tight maintenance window on a payroll
> > database and need to be sure that any work would be completed in time. I
> > assume the database size and amount of tables has a major bearing but not
> > sure where I would go from that info.
> >
> > Thanks
> >
> > Si
> Rather than rebuild ALL of the indexes, consider rebuilding only those
> that are badly fragmented. Rebuilding all of them is usually
> unnecessary. Here's a script that might help you:
> http://realsqlguy.blogspot.com/2007/02/smart-index-defragmentation.html
>
Is there any way to estimate how long it would take to drop and rebuild the
indexes on a database. I have a farily tight maintenance window on a payroll
database and need to be sure that any work would be completed in time. I
assume the database size and amount of tables has a major bearing but not
sure where I would go from that info.
Thanks
SiSimon
You can determine what tables are heavely defragmented and run rebuilt
index only on them
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:5D6C2F49-E817-4E24-893A-B108549F5331@.microsoft.com...
> All
> Is there any way to estimate how long it would take to drop and rebuild
> the
> indexes on a database. I have a farily tight maintenance window on a
> payroll
> database and need to be sure that any work would be completed in time. I
> assume the database size and amount of tables has a major bearing but not
> sure where I would go from that info.
> Thanks
> Si|||On Feb 27, 9:37 am, Simon <S...@.discussions.microsoft.com> wrote:
> All
> Is there any way to estimate how long it would take to drop and rebuild the
> indexes on a database. I have a farily tight maintenance window on a payroll
> database and need to be sure that any work would be completed in time. I
> assume the database size and amount of tables has a major bearing but not
> sure where I would go from that info.
> Thanks
> Si
Rather than rebuild ALL of the indexes, consider rebuilding only those
that are badly fragmented. Rebuilding all of them is usually
unnecessary. Here's a script that might help you:
http://realsqlguy.blogspot.com/2007/02/smart-index-defragmentation.html|||Simon,
there are too many variables in your case for an estimation from someone
external to work.
You could generate your own decent estimate if you restore a recent backup
onto a test server of similar specs and run the rebuild there.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||The problem I have is the database has around 600 tables, of which 350 need
rebuilding. There is also a high level of fragmentation so I thought that
Reindexing would be quicker.
"Tracy McKibben" wrote:
> On Feb 27, 9:37 am, Simon <S...@.discussions.microsoft.com> wrote:
> > All
> >
> > Is there any way to estimate how long it would take to drop and rebuild the
> > indexes on a database. I have a farily tight maintenance window on a payroll
> > database and need to be sure that any work would be completed in time. I
> > assume the database size and amount of tables has a major bearing but not
> > sure where I would go from that info.
> >
> > Thanks
> >
> > Si
> Rather than rebuild ALL of the indexes, consider rebuilding only those
> that are badly fragmented. Rebuilding all of them is usually
> unnecessary. Here's a script that might help you:
> http://realsqlguy.blogspot.com/2007/02/smart-index-defragmentation.html
>
Rebuilding Indexes
Hello all,
I need to diagnose a problem, this Sunday a regular Database Maintenance plan which is supposed to rebuild indexes took exactly 6 hours and 32 minutes. Now thats a hell lot of time and during all that process users were denied access to those tables. This is a production server. I want to know what caused that plan to run for so long and how can I avoid this to happen again plus if it ever happens again how can I make sure that atleast it doesnt lock tables. I know DBCC INDEXDEFRAG doesnt lock tables but how can I make Database Maintenance plan to run DBCC INDEXDEFRAG instead of DBCC DBREINDEX but more importantly why it took 6 hours.
Thanks allThere a lots of reasons that the process could have lasted so long. Do you have any evidence to indicate that this length of time is longer than normal (ie, what did it take last weekend and the weekend before that)?
Some things to possible look for:
1. A transaction that hung
2. Disk space (for both the Data and TLog files)
3. Disk space (for the TempDB)
4. Other jobs or indicators that ran over-long during this period
Also, look at your SQL Error Log for the time period and determine if there are any errors or warnings related to the problem. Look at you your System and Applcation Error Logs for indications that are related to the problem.
As for DBREINDEX vs. INDEXDEFRAG, I might suggest saving the former for quarterly or otherwise routine maintenance periods when you are guaranteed a "clean" box to work on (ie, no user connections, no activity). You can script off a T-SQL that will do an INDEXDEFRAG on specified tables (or all of them if you want).
Regards,
hmscott|||more data? that's usually the number one reason ;)
I need to diagnose a problem, this Sunday a regular Database Maintenance plan which is supposed to rebuild indexes took exactly 6 hours and 32 minutes. Now thats a hell lot of time and during all that process users were denied access to those tables. This is a production server. I want to know what caused that plan to run for so long and how can I avoid this to happen again plus if it ever happens again how can I make sure that atleast it doesnt lock tables. I know DBCC INDEXDEFRAG doesnt lock tables but how can I make Database Maintenance plan to run DBCC INDEXDEFRAG instead of DBCC DBREINDEX but more importantly why it took 6 hours.
Thanks allThere a lots of reasons that the process could have lasted so long. Do you have any evidence to indicate that this length of time is longer than normal (ie, what did it take last weekend and the weekend before that)?
Some things to possible look for:
1. A transaction that hung
2. Disk space (for both the Data and TLog files)
3. Disk space (for the TempDB)
4. Other jobs or indicators that ran over-long during this period
Also, look at your SQL Error Log for the time period and determine if there are any errors or warnings related to the problem. Look at you your System and Applcation Error Logs for indications that are related to the problem.
As for DBREINDEX vs. INDEXDEFRAG, I might suggest saving the former for quarterly or otherwise routine maintenance periods when you are guaranteed a "clean" box to work on (ie, no user connections, no activity). You can script off a T-SQL that will do an INDEXDEFRAG on specified tables (or all of them if you want).
Regards,
hmscott|||more data? that's usually the number one reason ;)
Tuesday, March 20, 2012
Rebuild Index Task and Sytem databases
The rebuild index task in a maintenance plan allows you to choose
system databases. I noticed that some of the system database tables
do have indexes. Should you run this task on system databases
within the maintenance plan. Is it necessary and will it do anything.
Also will it cause any issue with these databases. What about reorganize
index task or update statistics will this cause any issues and should it be run.
Lastly can you shrink a system database. For instance should you run the
shrink database task. Any help would be greatly appreciated thank you.i have never even thought about doing this. are you experiencing performance problems related to the system databases? do you have index fragmentation in the system databases? if not leave well enough alone. if it ain't broke...
if you do this, try it on your local sql install or a dev machine first and do a sanity check.|||This was an inherited system and there was a maintenance plan that did
all of these things. I was a little bit skeptical myself. I was just wondering
if anybody has tried some of these things and what it would do.
system databases. I noticed that some of the system database tables
do have indexes. Should you run this task on system databases
within the maintenance plan. Is it necessary and will it do anything.
Also will it cause any issue with these databases. What about reorganize
index task or update statistics will this cause any issues and should it be run.
Lastly can you shrink a system database. For instance should you run the
shrink database task. Any help would be greatly appreciated thank you.i have never even thought about doing this. are you experiencing performance problems related to the system databases? do you have index fragmentation in the system databases? if not leave well enough alone. if it ain't broke...
if you do this, try it on your local sql install or a dev machine first and do a sanity check.|||This was an inherited system and there was a maintenance plan that did
all of these things. I was a little bit skeptical myself. I was just wondering
if anybody has tried some of these things and what it would do.
Rebuild Index Failing
The SQL Server 2005 (9.0.2047) 'Rebuild Index' maintenance plan task is
failing.
The text file report is of very little help...
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047
Report was generated on "SQLSERVER".
Maintenance Plan: Weekly All DB Maintenance Plan
Duration: 00:00:00
Status: Succeeded.
Details:
The job history doesn't tell much either...
Date3/15/2007 8:00:00 PM
LogJob History (Weekly All DB Maintenance Plan)
Step ID0
ServerSQLSERVER
Job NameWeekly All DB Maintenance Plan
Step Name(Job outcome)
Duration04:25:27
Sql Severity0
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
The job failed. The Job was invoked by Schedule 99 (Weekly All DB
Maintenance Plan). The last step to run was step 1 (Subplan).
These symptoms are worse on (but not limited to) databases migrated from SQL
2000.
I have discovered running 'DBCC UPDATEUSAGE (DatabaseName)' allows Rebuild
Index to run. But eventually Rebuild Index will fail requiring UPDATEUSAGE
be run again.
Is there any way to get a more verbose (meaningful) task report?
What is causing this?
Is there any harm in running UPDATEUSAGE on a regular basis?
Thanks in advance,
John
John,
SP2 will give you a lot more output but there are known problems with maint
plans and checkdb. See:-
http://sqlblog.com/blogs/tibor_karaszi/archive/2007/03/08/be-aware-sp2-maint-plans-and-checkdb.aspx
Chris
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:5F4C1EBF-EE19-486C-A09C-D6B1F3614108@.microsoft.com...
> The SQL Server 2005 (9.0.2047) 'Rebuild Index' maintenance plan task is
> failing.
> The text file report is of very little help...
> Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047
> Report was generated on "SQLSERVER".
> Maintenance Plan: Weekly All DB Maintenance Plan
> Duration: 00:00:00
> Status: Succeeded.
> Details:
> The job history doesn't tell much either...
> Date 3/15/2007 8:00:00 PM
> Log Job History (Weekly All DB Maintenance Plan)
> Step ID 0
> Server SQLSERVER
> Job Name Weekly All DB Maintenance Plan
> Step Name (Job outcome)
> Duration 04:25:27
> Sql Severity 0
> Sql Message ID 0
> Operator Emailed
> Operator Net sent
> Operator Paged
> Retries Attempted 0
> Message
> The job failed. The Job was invoked by Schedule 99 (Weekly All DB
> Maintenance Plan). The last step to run was step 1 (Subplan).
>
> These symptoms are worse on (but not limited to) databases migrated from
> SQL
> 2000.
> I have discovered running 'DBCC UPDATEUSAGE (DatabaseName)' allows Rebuild
> Index to run. But eventually Rebuild Index will fail requiring
> UPDATEUSAGE
> be run again.
> Is there any way to get a more verbose (meaningful) task report?
> What is causing this?
> Is there any harm in running UPDATEUSAGE on a regular basis?
> Thanks in advance,
> John
|||Its well known that index usage counts could get out of whack on SQL 2000
but the algorithm was changed for 2005 suh that they never should. I'd
really like to know more about this - can you send me email with the error
messages you're getting from the rebuilds? (Go to the blog site below and
send email through there).
Thanks
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:5F4C1EBF-EE19-486C-A09C-D6B1F3614108@.microsoft.com...
> The SQL Server 2005 (9.0.2047) 'Rebuild Index' maintenance plan task is
> failing.
> The text file report is of very little help...
> Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047
> Report was generated on "SQLSERVER".
> Maintenance Plan: Weekly All DB Maintenance Plan
> Duration: 00:00:00
> Status: Succeeded.
> Details:
> The job history doesn't tell much either...
> Date 3/15/2007 8:00:00 PM
> Log Job History (Weekly All DB Maintenance Plan)
> Step ID 0
> Server SQLSERVER
> Job Name Weekly All DB Maintenance Plan
> Step Name (Job outcome)
> Duration 04:25:27
> Sql Severity 0
> Sql Message ID 0
> Operator Emailed
> Operator Net sent
> Operator Paged
> Retries Attempted 0
> Message
> The job failed. The Job was invoked by Schedule 99 (Weekly All DB
> Maintenance Plan). The last step to run was step 1 (Subplan).
>
> These symptoms are worse on (but not limited to) databases migrated from
> SQL
> 2000.
> I have discovered running 'DBCC UPDATEUSAGE (DatabaseName)' allows Rebuild
> Index to run. But eventually Rebuild Index will fail requiring
> UPDATEUSAGE
> be run again.
> Is there any way to get a more verbose (meaningful) task report?
> What is causing this?
> Is there any harm in running UPDATEUSAGE on a regular basis?
> Thanks in advance,
> John
|||Hi Paul,
Thank you for taking an interest in my little dilemma.
Forgive me but I don't see how to send an email from your blog. Can you
please expand on how to do it?
Thanks,
John
"Paul S. Randal [MS]" wrote:
> Its well known that index usage counts could get out of whack on SQL 2000
> but the algorithm was changed for 2005 suh that they never should. I'd
> really like to know more about this - can you send me email with the error
> messages you're getting from the rebuilds? (Go to the blog site below and
> send email through there).
> Thanks
> --
> Paul Randal
> Principal Lead Program Manager
> Microsoft SQL Server Core Storage Engine,
> http://blogs.msdn.com/sqlserverstorageengine/default.aspx
>
> "John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
> news:5F4C1EBF-EE19-486C-A09C-D6B1F3614108@.microsoft.com...
>
>
failing.
The text file report is of very little help...
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047
Report was generated on "SQLSERVER".
Maintenance Plan: Weekly All DB Maintenance Plan
Duration: 00:00:00
Status: Succeeded.
Details:
The job history doesn't tell much either...
Date3/15/2007 8:00:00 PM
LogJob History (Weekly All DB Maintenance Plan)
Step ID0
ServerSQLSERVER
Job NameWeekly All DB Maintenance Plan
Step Name(Job outcome)
Duration04:25:27
Sql Severity0
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
The job failed. The Job was invoked by Schedule 99 (Weekly All DB
Maintenance Plan). The last step to run was step 1 (Subplan).
These symptoms are worse on (but not limited to) databases migrated from SQL
2000.
I have discovered running 'DBCC UPDATEUSAGE (DatabaseName)' allows Rebuild
Index to run. But eventually Rebuild Index will fail requiring UPDATEUSAGE
be run again.
Is there any way to get a more verbose (meaningful) task report?
What is causing this?
Is there any harm in running UPDATEUSAGE on a regular basis?
Thanks in advance,
John
John,
SP2 will give you a lot more output but there are known problems with maint
plans and checkdb. See:-
http://sqlblog.com/blogs/tibor_karaszi/archive/2007/03/08/be-aware-sp2-maint-plans-and-checkdb.aspx
Chris
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:5F4C1EBF-EE19-486C-A09C-D6B1F3614108@.microsoft.com...
> The SQL Server 2005 (9.0.2047) 'Rebuild Index' maintenance plan task is
> failing.
> The text file report is of very little help...
> Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047
> Report was generated on "SQLSERVER".
> Maintenance Plan: Weekly All DB Maintenance Plan
> Duration: 00:00:00
> Status: Succeeded.
> Details:
> The job history doesn't tell much either...
> Date 3/15/2007 8:00:00 PM
> Log Job History (Weekly All DB Maintenance Plan)
> Step ID 0
> Server SQLSERVER
> Job Name Weekly All DB Maintenance Plan
> Step Name (Job outcome)
> Duration 04:25:27
> Sql Severity 0
> Sql Message ID 0
> Operator Emailed
> Operator Net sent
> Operator Paged
> Retries Attempted 0
> Message
> The job failed. The Job was invoked by Schedule 99 (Weekly All DB
> Maintenance Plan). The last step to run was step 1 (Subplan).
>
> These symptoms are worse on (but not limited to) databases migrated from
> SQL
> 2000.
> I have discovered running 'DBCC UPDATEUSAGE (DatabaseName)' allows Rebuild
> Index to run. But eventually Rebuild Index will fail requiring
> UPDATEUSAGE
> be run again.
> Is there any way to get a more verbose (meaningful) task report?
> What is causing this?
> Is there any harm in running UPDATEUSAGE on a regular basis?
> Thanks in advance,
> John
|||Its well known that index usage counts could get out of whack on SQL 2000
but the algorithm was changed for 2005 suh that they never should. I'd
really like to know more about this - can you send me email with the error
messages you're getting from the rebuilds? (Go to the blog site below and
send email through there).
Thanks
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:5F4C1EBF-EE19-486C-A09C-D6B1F3614108@.microsoft.com...
> The SQL Server 2005 (9.0.2047) 'Rebuild Index' maintenance plan task is
> failing.
> The text file report is of very little help...
> Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047
> Report was generated on "SQLSERVER".
> Maintenance Plan: Weekly All DB Maintenance Plan
> Duration: 00:00:00
> Status: Succeeded.
> Details:
> The job history doesn't tell much either...
> Date 3/15/2007 8:00:00 PM
> Log Job History (Weekly All DB Maintenance Plan)
> Step ID 0
> Server SQLSERVER
> Job Name Weekly All DB Maintenance Plan
> Step Name (Job outcome)
> Duration 04:25:27
> Sql Severity 0
> Sql Message ID 0
> Operator Emailed
> Operator Net sent
> Operator Paged
> Retries Attempted 0
> Message
> The job failed. The Job was invoked by Schedule 99 (Weekly All DB
> Maintenance Plan). The last step to run was step 1 (Subplan).
>
> These symptoms are worse on (but not limited to) databases migrated from
> SQL
> 2000.
> I have discovered running 'DBCC UPDATEUSAGE (DatabaseName)' allows Rebuild
> Index to run. But eventually Rebuild Index will fail requiring
> UPDATEUSAGE
> be run again.
> Is there any way to get a more verbose (meaningful) task report?
> What is causing this?
> Is there any harm in running UPDATEUSAGE on a regular basis?
> Thanks in advance,
> John
|||Hi Paul,
Thank you for taking an interest in my little dilemma.
Forgive me but I don't see how to send an email from your blog. Can you
please expand on how to do it?
Thanks,
John
"Paul S. Randal [MS]" wrote:
> Its well known that index usage counts could get out of whack on SQL 2000
> but the algorithm was changed for 2005 suh that they never should. I'd
> really like to know more about this - can you send me email with the error
> messages you're getting from the rebuilds? (Go to the blog site below and
> send email through there).
> Thanks
> --
> Paul Randal
> Principal Lead Program Manager
> Microsoft SQL Server Core Storage Engine,
> http://blogs.msdn.com/sqlserverstorageengine/default.aspx
>
> "John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
> news:5F4C1EBF-EE19-486C-A09C-D6B1F3614108@.microsoft.com...
>
>
Rebuild Index Failing
The SQL Server 2005 (9.0.2047) 'Rebuild Index' maintenance plan task is
failing.
The text file report is of very little help...
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047
Report was generated on "SQLSERVER".
Maintenance Plan: Weekly All DB Maintenance Plan
Duration: 00:00:00
Status: Succeeded.
Details:
The job history doesn't tell much either...
Date 3/15/2007 8:00:00 PM
Log Job History (Weekly All DB Maintenance Plan)
Step ID 0
Server SQLSERVER
Job Name Weekly All DB Maintenance Plan
Step Name (Job outcome)
Duration 04:25:27
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
The job failed. The Job was invoked by Schedule 99 (Weekly All DB
Maintenance Plan). The last step to run was step 1 (Subplan).
These symptoms are worse on (but not limited to) databases migrated from SQL
2000.
I have discovered running 'DBCC UPDATEUSAGE (DatabaseName)' allows Rebuild
Index to run. But eventually Rebuild Index will fail requiring UPDATEUSAGE
be run again.
Is there any way to get a more verbose (meaningful) task report?
What is causing this?
Is there any harm in running UPDATEUSAGE on a regular basis?
Thanks in advance,
JohnJohn,
SP2 will give you a lot more output but there are known problems with maint
plans and checkdb. See:-
http://sqlblog.com/blogs/tibor_kara...nd-checkdb.aspx
Chris
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:5F4C1EBF-EE19-486C-A09C-D6B1F3614108@.microsoft.com...
> The SQL Server 2005 (9.0.2047) 'Rebuild Index' maintenance plan task is
> failing.
> The text file report is of very little help...
> Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047
> Report was generated on "SQLSERVER".
> Maintenance Plan: Weekly All DB Maintenance Plan
> Duration: 00:00:00
> Status: Succeeded.
> Details:
> The job history doesn't tell much either...
> Date 3/15/2007 8:00:00 PM
> Log Job History (Weekly All DB Maintenance Plan)
> Step ID 0
> Server SQLSERVER
> Job Name Weekly All DB Maintenance Plan
> Step Name (Job outcome)
> Duration 04:25:27
> Sql Severity 0
> Sql Message ID 0
> Operator Emailed
> Operator Net sent
> Operator Paged
> Retries Attempted 0
> Message
> The job failed. The Job was invoked by Schedule 99 (Weekly All DB
> Maintenance Plan). The last step to run was step 1 (Subplan).
>
> These symptoms are worse on (but not limited to) databases migrated from
> SQL
> 2000.
> I have discovered running 'DBCC UPDATEUSAGE (DatabaseName)' allows Rebuild
> Index to run. But eventually Rebuild Index will fail requiring
> UPDATEUSAGE
> be run again.
> Is there any way to get a more verbose (meaningful) task report?
> What is causing this?
> Is there any harm in running UPDATEUSAGE on a regular basis?
> Thanks in advance,
> John|||Its well known that index usage counts could get out of whack on SQL 2000
but the algorithm was changed for 2005 suh that they never should. I'd
really like to know more about this - can you send me email with the error
messages you're getting from the rebuilds? (Go to the blog site below and
send email through there).
Thanks
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstor...ne/default.aspx
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:5F4C1EBF-EE19-486C-A09C-D6B1F3614108@.microsoft.com...
> The SQL Server 2005 (9.0.2047) 'Rebuild Index' maintenance plan task is
> failing.
> The text file report is of very little help...
> Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047
> Report was generated on "SQLSERVER".
> Maintenance Plan: Weekly All DB Maintenance Plan
> Duration: 00:00:00
> Status: Succeeded.
> Details:
> The job history doesn't tell much either...
> Date 3/15/2007 8:00:00 PM
> Log Job History (Weekly All DB Maintenance Plan)
> Step ID 0
> Server SQLSERVER
> Job Name Weekly All DB Maintenance Plan
> Step Name (Job outcome)
> Duration 04:25:27
> Sql Severity 0
> Sql Message ID 0
> Operator Emailed
> Operator Net sent
> Operator Paged
> Retries Attempted 0
> Message
> The job failed. The Job was invoked by Schedule 99 (Weekly All DB
> Maintenance Plan). The last step to run was step 1 (Subplan).
>
> These symptoms are worse on (but not limited to) databases migrated from
> SQL
> 2000.
> I have discovered running 'DBCC UPDATEUSAGE (DatabaseName)' allows Rebuild
> Index to run. But eventually Rebuild Index will fail requiring
> UPDATEUSAGE
> be run again.
> Is there any way to get a more verbose (meaningful) task report?
> What is causing this?
> Is there any harm in running UPDATEUSAGE on a regular basis?
> Thanks in advance,
> John|||Hi Paul,
Thank you for taking an interest in my little dilemma.
Forgive me but I don't see how to send an email from your blog. Can you
please expand on how to do it?
Thanks,
John
"Paul S. Randal [MS]" wrote:
> Its well known that index usage counts could get out of whack on SQL 2000
> but the algorithm was changed for 2005 suh that they never should. I'd
> really like to know more about this - can you send me email with the error
> messages you're getting from the rebuilds? (Go to the blog site below and
> send email through there).
> Thanks
> --
> Paul Randal
> Principal Lead Program Manager
> Microsoft SQL Server Core Storage Engine,
> http://blogs.msdn.com/sqlserverstor...ne/default.aspx
>
> "John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
> news:5F4C1EBF-EE19-486C-A09C-D6B1F3614108@.microsoft.com...
>
>
failing.
The text file report is of very little help...
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047
Report was generated on "SQLSERVER".
Maintenance Plan: Weekly All DB Maintenance Plan
Duration: 00:00:00
Status: Succeeded.
Details:
The job history doesn't tell much either...
Date 3/15/2007 8:00:00 PM
Log Job History (Weekly All DB Maintenance Plan)
Step ID 0
Server SQLSERVER
Job Name Weekly All DB Maintenance Plan
Step Name (Job outcome)
Duration 04:25:27
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
The job failed. The Job was invoked by Schedule 99 (Weekly All DB
Maintenance Plan). The last step to run was step 1 (Subplan).
These symptoms are worse on (but not limited to) databases migrated from SQL
2000.
I have discovered running 'DBCC UPDATEUSAGE (DatabaseName)' allows Rebuild
Index to run. But eventually Rebuild Index will fail requiring UPDATEUSAGE
be run again.
Is there any way to get a more verbose (meaningful) task report?
What is causing this?
Is there any harm in running UPDATEUSAGE on a regular basis?
Thanks in advance,
JohnJohn,
SP2 will give you a lot more output but there are known problems with maint
plans and checkdb. See:-
http://sqlblog.com/blogs/tibor_kara...nd-checkdb.aspx
Chris
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:5F4C1EBF-EE19-486C-A09C-D6B1F3614108@.microsoft.com...
> The SQL Server 2005 (9.0.2047) 'Rebuild Index' maintenance plan task is
> failing.
> The text file report is of very little help...
> Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047
> Report was generated on "SQLSERVER".
> Maintenance Plan: Weekly All DB Maintenance Plan
> Duration: 00:00:00
> Status: Succeeded.
> Details:
> The job history doesn't tell much either...
> Date 3/15/2007 8:00:00 PM
> Log Job History (Weekly All DB Maintenance Plan)
> Step ID 0
> Server SQLSERVER
> Job Name Weekly All DB Maintenance Plan
> Step Name (Job outcome)
> Duration 04:25:27
> Sql Severity 0
> Sql Message ID 0
> Operator Emailed
> Operator Net sent
> Operator Paged
> Retries Attempted 0
> Message
> The job failed. The Job was invoked by Schedule 99 (Weekly All DB
> Maintenance Plan). The last step to run was step 1 (Subplan).
>
> These symptoms are worse on (but not limited to) databases migrated from
> SQL
> 2000.
> I have discovered running 'DBCC UPDATEUSAGE (DatabaseName)' allows Rebuild
> Index to run. But eventually Rebuild Index will fail requiring
> UPDATEUSAGE
> be run again.
> Is there any way to get a more verbose (meaningful) task report?
> What is causing this?
> Is there any harm in running UPDATEUSAGE on a regular basis?
> Thanks in advance,
> John|||Its well known that index usage counts could get out of whack on SQL 2000
but the algorithm was changed for 2005 suh that they never should. I'd
really like to know more about this - can you send me email with the error
messages you're getting from the rebuilds? (Go to the blog site below and
send email through there).
Thanks
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstor...ne/default.aspx
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:5F4C1EBF-EE19-486C-A09C-D6B1F3614108@.microsoft.com...
> The SQL Server 2005 (9.0.2047) 'Rebuild Index' maintenance plan task is
> failing.
> The text file report is of very little help...
> Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047
> Report was generated on "SQLSERVER".
> Maintenance Plan: Weekly All DB Maintenance Plan
> Duration: 00:00:00
> Status: Succeeded.
> Details:
> The job history doesn't tell much either...
> Date 3/15/2007 8:00:00 PM
> Log Job History (Weekly All DB Maintenance Plan)
> Step ID 0
> Server SQLSERVER
> Job Name Weekly All DB Maintenance Plan
> Step Name (Job outcome)
> Duration 04:25:27
> Sql Severity 0
> Sql Message ID 0
> Operator Emailed
> Operator Net sent
> Operator Paged
> Retries Attempted 0
> Message
> The job failed. The Job was invoked by Schedule 99 (Weekly All DB
> Maintenance Plan). The last step to run was step 1 (Subplan).
>
> These symptoms are worse on (but not limited to) databases migrated from
> SQL
> 2000.
> I have discovered running 'DBCC UPDATEUSAGE (DatabaseName)' allows Rebuild
> Index to run. But eventually Rebuild Index will fail requiring
> UPDATEUSAGE
> be run again.
> Is there any way to get a more verbose (meaningful) task report?
> What is causing this?
> Is there any harm in running UPDATEUSAGE on a regular basis?
> Thanks in advance,
> John|||Hi Paul,
Thank you for taking an interest in my little dilemma.
Forgive me but I don't see how to send an email from your blog. Can you
please expand on how to do it?
Thanks,
John
"Paul S. Randal [MS]" wrote:
> Its well known that index usage counts could get out of whack on SQL 2000
> but the algorithm was changed for 2005 suh that they never should. I'd
> really like to know more about this - can you send me email with the error
> messages you're getting from the rebuilds? (Go to the blog site below and
> send email through there).
> Thanks
> --
> Paul Randal
> Principal Lead Program Manager
> Microsoft SQL Server Core Storage Engine,
> http://blogs.msdn.com/sqlserverstor...ne/default.aspx
>
> "John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
> news:5F4C1EBF-EE19-486C-A09C-D6B1F3614108@.microsoft.com...
>
>
Rebuild Index Failing
The SQL Server 2005 (9.0.2047) 'Rebuild Index' maintenance plan task is
failing.
The text file report is of very little help...
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047
Report was generated on "SQLSERVER".
Maintenance Plan: Weekly All DB Maintenance Plan
Duration: 00:00:00
Status: Succeeded.
Details:
The job history doesn't tell much either...
Date 3/15/2007 8:00:00 PM
Log Job History (Weekly All DB Maintenance Plan)
Step ID 0
Server SQLSERVER
Job Name Weekly All DB Maintenance Plan
Step Name (Job outcome)
Duration 04:25:27
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
The job failed. The Job was invoked by Schedule 99 (Weekly All DB
Maintenance Plan). The last step to run was step 1 (Subplan).
These symptoms are worse on (but not limited to) databases migrated from SQL
2000.
I have discovered running 'DBCC UPDATEUSAGE (DatabaseName)' allows Rebuild
Index to run. But eventually Rebuild Index will fail requiring UPDATEUSAGE
be run again.
Is there any way to get a more verbose (meaningful) task report?
What is causing this?
Is there any harm in running UPDATEUSAGE on a regular basis?
Thanks in advance,
JohnJohn,
SP2 will give you a lot more output but there are known problems with maint
plans and checkdb. See:-
http://sqlblog.com/blogs/tibor_karaszi/archive/2007/03/08/be-aware-sp2-maint-plans-and-checkdb.aspx
Chris
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:5F4C1EBF-EE19-486C-A09C-D6B1F3614108@.microsoft.com...
> The SQL Server 2005 (9.0.2047) 'Rebuild Index' maintenance plan task is
> failing.
> The text file report is of very little help...
> Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047
> Report was generated on "SQLSERVER".
> Maintenance Plan: Weekly All DB Maintenance Plan
> Duration: 00:00:00
> Status: Succeeded.
> Details:
> The job history doesn't tell much either...
> Date 3/15/2007 8:00:00 PM
> Log Job History (Weekly All DB Maintenance Plan)
> Step ID 0
> Server SQLSERVER
> Job Name Weekly All DB Maintenance Plan
> Step Name (Job outcome)
> Duration 04:25:27
> Sql Severity 0
> Sql Message ID 0
> Operator Emailed
> Operator Net sent
> Operator Paged
> Retries Attempted 0
> Message
> The job failed. The Job was invoked by Schedule 99 (Weekly All DB
> Maintenance Plan). The last step to run was step 1 (Subplan).
>
> These symptoms are worse on (but not limited to) databases migrated from
> SQL
> 2000.
> I have discovered running 'DBCC UPDATEUSAGE (DatabaseName)' allows Rebuild
> Index to run. But eventually Rebuild Index will fail requiring
> UPDATEUSAGE
> be run again.
> Is there any way to get a more verbose (meaningful) task report?
> What is causing this?
> Is there any harm in running UPDATEUSAGE on a regular basis?
> Thanks in advance,
> John|||Its well known that index usage counts could get out of whack on SQL 2000
but the algorithm was changed for 2005 suh that they never should. I'd
really like to know more about this - can you send me email with the error
messages you're getting from the rebuilds? (Go to the blog site below and
send email through there).
Thanks
--
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:5F4C1EBF-EE19-486C-A09C-D6B1F3614108@.microsoft.com...
> The SQL Server 2005 (9.0.2047) 'Rebuild Index' maintenance plan task is
> failing.
> The text file report is of very little help...
> Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047
> Report was generated on "SQLSERVER".
> Maintenance Plan: Weekly All DB Maintenance Plan
> Duration: 00:00:00
> Status: Succeeded.
> Details:
> The job history doesn't tell much either...
> Date 3/15/2007 8:00:00 PM
> Log Job History (Weekly All DB Maintenance Plan)
> Step ID 0
> Server SQLSERVER
> Job Name Weekly All DB Maintenance Plan
> Step Name (Job outcome)
> Duration 04:25:27
> Sql Severity 0
> Sql Message ID 0
> Operator Emailed
> Operator Net sent
> Operator Paged
> Retries Attempted 0
> Message
> The job failed. The Job was invoked by Schedule 99 (Weekly All DB
> Maintenance Plan). The last step to run was step 1 (Subplan).
>
> These symptoms are worse on (but not limited to) databases migrated from
> SQL
> 2000.
> I have discovered running 'DBCC UPDATEUSAGE (DatabaseName)' allows Rebuild
> Index to run. But eventually Rebuild Index will fail requiring
> UPDATEUSAGE
> be run again.
> Is there any way to get a more verbose (meaningful) task report?
> What is causing this?
> Is there any harm in running UPDATEUSAGE on a regular basis?
> Thanks in advance,
> John|||Hi Paul,
Thank you for taking an interest in my little dilemma.
Forgive me but I don't see how to send an email from your blog. Can you
please expand on how to do it?
Thanks,
John
"Paul S. Randal [MS]" wrote:
> Its well known that index usage counts could get out of whack on SQL 2000
> but the algorithm was changed for 2005 suh that they never should. I'd
> really like to know more about this - can you send me email with the error
> messages you're getting from the rebuilds? (Go to the blog site below and
> send email through there).
> Thanks
> --
> Paul Randal
> Principal Lead Program Manager
> Microsoft SQL Server Core Storage Engine,
> http://blogs.msdn.com/sqlserverstorageengine/default.aspx
>
> "John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
> news:5F4C1EBF-EE19-486C-A09C-D6B1F3614108@.microsoft.com...
> > The SQL Server 2005 (9.0.2047) 'Rebuild Index' maintenance plan task is
> > failing.
> >
> > The text file report is of very little help...
> > Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047
> > Report was generated on "SQLSERVER".
> > Maintenance Plan: Weekly All DB Maintenance Plan
> > Duration: 00:00:00
> > Status: Succeeded.
> > Details:
> >
> > The job history doesn't tell much either...
> >
> > Date 3/15/2007 8:00:00 PM
> > Log Job History (Weekly All DB Maintenance Plan)
> > Step ID 0
> > Server SQLSERVER
> > Job Name Weekly All DB Maintenance Plan
> > Step Name (Job outcome)
> > Duration 04:25:27
> > Sql Severity 0
> > Sql Message ID 0
> > Operator Emailed
> > Operator Net sent
> > Operator Paged
> > Retries Attempted 0
> >
> > Message
> > The job failed. The Job was invoked by Schedule 99 (Weekly All DB
> > Maintenance Plan). The last step to run was step 1 (Subplan).
> >
> >
> >
> > These symptoms are worse on (but not limited to) databases migrated from
> > SQL
> > 2000.
> >
> > I have discovered running 'DBCC UPDATEUSAGE (DatabaseName)' allows Rebuild
> > Index to run. But eventually Rebuild Index will fail requiring
> > UPDATEUSAGE
> > be run again.
> >
> > Is there any way to get a more verbose (meaningful) task report?
> >
> > What is causing this?
> >
> > Is there any harm in running UPDATEUSAGE on a regular basis?
> >
> > Thanks in advance,
> > John
>
>
failing.
The text file report is of very little help...
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047
Report was generated on "SQLSERVER".
Maintenance Plan: Weekly All DB Maintenance Plan
Duration: 00:00:00
Status: Succeeded.
Details:
The job history doesn't tell much either...
Date 3/15/2007 8:00:00 PM
Log Job History (Weekly All DB Maintenance Plan)
Step ID 0
Server SQLSERVER
Job Name Weekly All DB Maintenance Plan
Step Name (Job outcome)
Duration 04:25:27
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
The job failed. The Job was invoked by Schedule 99 (Weekly All DB
Maintenance Plan). The last step to run was step 1 (Subplan).
These symptoms are worse on (but not limited to) databases migrated from SQL
2000.
I have discovered running 'DBCC UPDATEUSAGE (DatabaseName)' allows Rebuild
Index to run. But eventually Rebuild Index will fail requiring UPDATEUSAGE
be run again.
Is there any way to get a more verbose (meaningful) task report?
What is causing this?
Is there any harm in running UPDATEUSAGE on a regular basis?
Thanks in advance,
JohnJohn,
SP2 will give you a lot more output but there are known problems with maint
plans and checkdb. See:-
http://sqlblog.com/blogs/tibor_karaszi/archive/2007/03/08/be-aware-sp2-maint-plans-and-checkdb.aspx
Chris
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:5F4C1EBF-EE19-486C-A09C-D6B1F3614108@.microsoft.com...
> The SQL Server 2005 (9.0.2047) 'Rebuild Index' maintenance plan task is
> failing.
> The text file report is of very little help...
> Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047
> Report was generated on "SQLSERVER".
> Maintenance Plan: Weekly All DB Maintenance Plan
> Duration: 00:00:00
> Status: Succeeded.
> Details:
> The job history doesn't tell much either...
> Date 3/15/2007 8:00:00 PM
> Log Job History (Weekly All DB Maintenance Plan)
> Step ID 0
> Server SQLSERVER
> Job Name Weekly All DB Maintenance Plan
> Step Name (Job outcome)
> Duration 04:25:27
> Sql Severity 0
> Sql Message ID 0
> Operator Emailed
> Operator Net sent
> Operator Paged
> Retries Attempted 0
> Message
> The job failed. The Job was invoked by Schedule 99 (Weekly All DB
> Maintenance Plan). The last step to run was step 1 (Subplan).
>
> These symptoms are worse on (but not limited to) databases migrated from
> SQL
> 2000.
> I have discovered running 'DBCC UPDATEUSAGE (DatabaseName)' allows Rebuild
> Index to run. But eventually Rebuild Index will fail requiring
> UPDATEUSAGE
> be run again.
> Is there any way to get a more verbose (meaningful) task report?
> What is causing this?
> Is there any harm in running UPDATEUSAGE on a regular basis?
> Thanks in advance,
> John|||Its well known that index usage counts could get out of whack on SQL 2000
but the algorithm was changed for 2005 suh that they never should. I'd
really like to know more about this - can you send me email with the error
messages you're getting from the rebuilds? (Go to the blog site below and
send email through there).
Thanks
--
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:5F4C1EBF-EE19-486C-A09C-D6B1F3614108@.microsoft.com...
> The SQL Server 2005 (9.0.2047) 'Rebuild Index' maintenance plan task is
> failing.
> The text file report is of very little help...
> Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047
> Report was generated on "SQLSERVER".
> Maintenance Plan: Weekly All DB Maintenance Plan
> Duration: 00:00:00
> Status: Succeeded.
> Details:
> The job history doesn't tell much either...
> Date 3/15/2007 8:00:00 PM
> Log Job History (Weekly All DB Maintenance Plan)
> Step ID 0
> Server SQLSERVER
> Job Name Weekly All DB Maintenance Plan
> Step Name (Job outcome)
> Duration 04:25:27
> Sql Severity 0
> Sql Message ID 0
> Operator Emailed
> Operator Net sent
> Operator Paged
> Retries Attempted 0
> Message
> The job failed. The Job was invoked by Schedule 99 (Weekly All DB
> Maintenance Plan). The last step to run was step 1 (Subplan).
>
> These symptoms are worse on (but not limited to) databases migrated from
> SQL
> 2000.
> I have discovered running 'DBCC UPDATEUSAGE (DatabaseName)' allows Rebuild
> Index to run. But eventually Rebuild Index will fail requiring
> UPDATEUSAGE
> be run again.
> Is there any way to get a more verbose (meaningful) task report?
> What is causing this?
> Is there any harm in running UPDATEUSAGE on a regular basis?
> Thanks in advance,
> John|||Hi Paul,
Thank you for taking an interest in my little dilemma.
Forgive me but I don't see how to send an email from your blog. Can you
please expand on how to do it?
Thanks,
John
"Paul S. Randal [MS]" wrote:
> Its well known that index usage counts could get out of whack on SQL 2000
> but the algorithm was changed for 2005 suh that they never should. I'd
> really like to know more about this - can you send me email with the error
> messages you're getting from the rebuilds? (Go to the blog site below and
> send email through there).
> Thanks
> --
> Paul Randal
> Principal Lead Program Manager
> Microsoft SQL Server Core Storage Engine,
> http://blogs.msdn.com/sqlserverstorageengine/default.aspx
>
> "John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
> news:5F4C1EBF-EE19-486C-A09C-D6B1F3614108@.microsoft.com...
> > The SQL Server 2005 (9.0.2047) 'Rebuild Index' maintenance plan task is
> > failing.
> >
> > The text file report is of very little help...
> > Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047
> > Report was generated on "SQLSERVER".
> > Maintenance Plan: Weekly All DB Maintenance Plan
> > Duration: 00:00:00
> > Status: Succeeded.
> > Details:
> >
> > The job history doesn't tell much either...
> >
> > Date 3/15/2007 8:00:00 PM
> > Log Job History (Weekly All DB Maintenance Plan)
> > Step ID 0
> > Server SQLSERVER
> > Job Name Weekly All DB Maintenance Plan
> > Step Name (Job outcome)
> > Duration 04:25:27
> > Sql Severity 0
> > Sql Message ID 0
> > Operator Emailed
> > Operator Net sent
> > Operator Paged
> > Retries Attempted 0
> >
> > Message
> > The job failed. The Job was invoked by Schedule 99 (Weekly All DB
> > Maintenance Plan). The last step to run was step 1 (Subplan).
> >
> >
> >
> > These symptoms are worse on (but not limited to) databases migrated from
> > SQL
> > 2000.
> >
> > I have discovered running 'DBCC UPDATEUSAGE (DatabaseName)' allows Rebuild
> > Index to run. But eventually Rebuild Index will fail requiring
> > UPDATEUSAGE
> > be run again.
> >
> > Is there any way to get a more verbose (meaningful) task report?
> >
> > What is causing this?
> >
> > Is there any harm in running UPDATEUSAGE on a regular basis?
> >
> > Thanks in advance,
> > John
>
>
Rebuild and Reorganize
will maintenance tasks like rebuilding and reorganizing indexes be replicated in transactional replication, or do i have to setup these management tasks on the subscribers as well?
No. These operations are not replicated.
Peng
|||so if i set these tasks up on the subscriber will it affect replication in a negative way?|||As far as I know, there should be no negative impact. Of course, you need to test on your machines to see if the extra workload affects the performance much.
Subscribe to:
Posts (Atom)