November 16, 2010 at 8:27 am
Thanks to anyone who can help. This is the default Rebuild Index maintenance plan with 'Reorganize pages with the default amount of free space'. No advanced options are set.
Error is below
When I click on View TSQL I get the additional error message.
Rebuild failed for Index 'PK_MyIndex'
How can I troubleshoot a particular index?
Executed as user: DOMAIN\User. ...0.4035.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 10:05:00 PM Progress: 2010-11-15 22:05:01.67 Source: {8DD9A8EA-8225-4002-9604-38100F124061} Executing query "DECLARE @GUID UNIQUEIDENTIFIER EXECUTE msdb..sp".: 100% complete End Progress Error: 2010-11-15 22:07:01.59 Code: 0xC0024104 Source: Reporting Task for subplan-{15F16491-11AB-4A16-8671-DC87DFA858BA} Description: The Execute method on the task returned error code 0x80131931 (Data is Null. This method or property cannot be called on Null values.). The Execute method must succeed, and indicate the result using an "out" parameter. End Error Warning: 2010-11-15 22:07:01.59 Code: 0x80019002 Source: User_Maintenance_Plan Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (2) reached the maximu... The package execution fa... The step failed.
November 16, 2010 at 9:10 am
@ chrissy
You can search for that index in the database-->tables--> indexes and then rebuild/reorganize from there.
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
November 16, 2010 at 9:25 am
SKYBVI (11/16/2010)[hrYou can search for that index in the database-->tables--> indexes and then rebuild/reorganize from there.
There are a lot of databases on this server. Is there a system table that will tell me what database and what table the index is associated with.
November 16, 2010 at 9:38 am
I found the index by going through the databases. I scripted out the rebuild and reorganize commands and both ran successfully. The table is fairly small < 1200 records.
It sounds like this is one of the maintenance plan anomolies and I should script out the re-indexing.
November 16, 2010 at 9:43 am
Now when I try to View T-SQL for the maintenance task I get the error on a different index.
Can anyone recommend a good script for this purpose?
November 16, 2010 at 9:45 am
@ chrissy
There are some tools like sql search tool, its free and downloadable(from redgate), it helps you to save a lot of time in searching.
You just have to specify the words you are looking for and it will search in every database in every objects.
That way you can save a lot of time.
Btw, maintenance plans have a lot of anomalies, thaswhy people prefer to script out all the maintenance jobs. The major problem I face from maintenance plans is not deleting hte backups regularly.
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
November 16, 2010 at 9:47 am
@ chrissy
--Script to automatically reindex all tables in a database
USE DatabaseName --Enter the name of the database you want to reindex
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
November 16, 2010 at 2:11 pm
Chrissy321 (11/16/2010)
Now when I try to View T-SQL for the maintenance task I get the error on a different index.Can anyone recommend a good script for this purpose?
The default task selects all indexes - and that is most likely not a good strategy for rebuilding. Another thing this task does not handle very well are indexes that cannot be built online (if you check that option) and indexed views.
If I know I don't have any of the above issues and I can afford the time to rebuild all indexes - I will use the task. However, most of the time I use an Execute SQL Task instead and call out to a stored procedure to perform a smart rebuild.
There are several very good scripts available. I particularly like Michelle's (SQL Fool) - look her up in Google. Ola Hallengren also has a very useful script...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 16, 2010 at 3:21 pm
Thanks you for these links, this is just what I was looking for.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply