Reindex maintenance plan error
-
Hello
I have created a maintenance plan (hope this is the correct translation - I'm in France) for reindexing and executed it many times.
The task is rebuilding indexes for one specific database
Since today it gives me after some seconds an error saying that I would find more information in maintenance plan historic
In this historic I find :
Error N° : -1073548784
Error Message : L'exécution de la requête « » a échoué avec l'erreur suivante : « ». Causes possibles de cet échec : problèmes liés à la requête, propriété « ResultSet » non définie correctement, paramètres non définis correctement ou connexion non établie correctement.It also shows a link to get T-SQL - there I get a bunch of instructions (51408 exactly) and here are the first lines :
USE [mydb]
GO
ALTER INDEX [PK$ndo$cachesync] ON [dbo].[$ndo$cachesync] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
USE [mydb]
GO
ALTER INDEX [PK$ndo$dbproperty] ON [dbo].[$ndo$dbproperty] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
USE [mydb]
GO
ALTER INDEX [PK$$ndo$tableconnections] ON [dbo].[$ndo$tableconnections] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
USE [mydb]
GO
ALTER INDEX [PK$ndo$tenantproperty] ON [dbo].[$ndo$tenantproperty] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
USE [mydb]
GO
ALTER INDEX [specific tables] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
and so onWhen I copy paste the TSQL instructions in a new request it executes without error.
I also tried to find informatioins in the event viewer but no other information.
How can I find out the reason of this error ?
-
Unfortunately this sort of thing happens all too frequently with the maintenance plans. Deleting the plan and recreating it usually fixes this kind of error.
A better option would be to use http://minionware.net/reindex/.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
Ok thanks for the link !
-
Ola.hallengren.com is my go-to solution for MX.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service - TheSQLGuru - Tuesday, August 22, 2017 9:43 PM
+1 for Ola's solution
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato - Orlando Colamatteo - Tuesday, August 22, 2017 11:10 PM
FWIW - that solution doesn't even make my top 3. Minion is superior to that solution. My top recommendation would always be to create your own to match your environment. So many more advantages that way. If you don't have time or knowledge and need something quickly, definitely go with Minion.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events - SQLRNNR - Tuesday, August 22, 2017 11:39 PM
To each their own. I cannot recommend Minion as a generic solution due to it requiring people to rely on a feature in the database engine that is disabled by default as well as a language runtime that sits outside the database engine. Both of these points present a barrier for adoption to many who are conscious of minimizing admin overhead and attackable surface area on their servers.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato - Orlando Colamatteo - Tuesday, August 22, 2017 11:48 PM
I have heard that argument. There are plenty of security vectors to attack from with Ola as well. As for the xp_cmdshell tho, if you don't use centralised logging, xp_cmdshell can be left off. MR just uses tsql beyond that. From a security standpoint, I would say don't be fooled by the Ola solution.
Here is some additional reading for the OP:
https://dbafromthecold.com/2015/03/16/review-of-minion-reindex/
http://www.midnightdba.com/DBARant/security-theater/ (if you are running a cmd step from sql agent, you are no more secure than running xp_cmdshell. I love the sqlcmd example there. Anybody with permissions to create/alter jobs can easily grant sysadmin to themselves or anybody else - without being in the sysadmin or securityadmin server roles.)All of that said, you can still have a secure server even with xp_cmdshell enabled.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
Deleted the plan and recreated it - same error
Is there really no way to understand whats happening ?
How can I rebuild the index now ? -
Have you tried running those 51408 statements (or some of them) manually?
John
-
yes they execute without any problem
- gtrennert - Wednesday, August 23, 2017 2:07 AM
Gosh, yes, you did say that in your original post. In that case, I agree with the others - abandon the clunky, old-school maintenance plans, and switch to something more modern, flexible and robust.
John
-
Oh really ! So what you say is that there is nothing to understand ? And I hope that the fact that it does not work does not mean that there is something wrong with my database.
-
I think there is something to understand if it's failing, yes. My point is that you might not want to put too much effort into doing so given that you can easily move to something better. There's not necessarily anything wrong with your database. If you really want to troubleshoot it, run an extended event session while the maintenance plan executes in order to capture what is happening and where the error occurs. You can also look at the statistics on each of the indexes to see whether any of them have been updated (which happens when the index is rebuilt).
John
- Orlando Colamatteo - Tuesday, August 22, 2017 11:48 PM
I'm not sure I understand what you're saying. You're saying you don't use DBMail, or the SQL Agent, or the Browser? Cause they're all disabled by default too. No wait, Agent is just manual. So I suppose you leave it set at manual because it's too much of a mgmt pain to switch to automatic? As for PS being disabled by default. PS is NOT disabled, only the running of scripts is. And I don't know any shops anymore that don't make turning on PS scripting a standard. It's not any kind of security risk to turn it on. And as for cmdshell being a security risk. Man, I'm getting tired of hearing that. Here's a blog I wrote that proves that having cmdshell on isn't any more dangerous than anything else. And it speaks about it specifically in the case of Ola vs. Minion.
http://www.midnightdba.com/DBARant/security-theater/Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
Viewing 15 posts - 1 through 15 (of 37 total)
You must be logged in to reply to this topic. Login to reply