Reindex maintenance plan error
-
I will have a look to get something better, but not immediatly
I first try to understand. - I created an extended event session (SQL Server 2014) but I don't know what kind of events to choose.
I tried with query_pre_execution_plan / query_post_execution_plan / sql_statement_starting / sql_statement_completed, and saw nothing that inspired me
But perhaps I should choose other event types ?
I wonder if there is a possibility to filter in the session on events concerning a specified database. I did not see anything ... -
I understand that there might be several different tools outside there and I will have a look in the future.
But please - this post is about a specific problem, if you want to discuss the tools, you could do it in a post dedicated to this subject, no ? - gtrennert - Wednesday, August 23, 2017 4:11 AM
Evidently you're new to the forum concept. These discussions quite often get off of the main topic and then back around. However, I was just answering the security topic that is quite relevant to the 2 solutions provided by others. So I don't think it's entirely out of bounds. That's the thing though, you can't really control what people talk about when you post something.
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
- gtrennert - Wednesday, August 23, 2017 4:07 AM
I'm afraid you'll need to do a bit of research to work out which event types to use. I don't know off the top of my head. Alternatively, use Profiler and run a trace. Just know that (a) Profiler traces are deprecated (so you may prefer to invest the time now to learn Extended Events) and (b) Profiler uses more resources and so may have a more noticeable effect on server performance than Extended Events does.
John
- SQLRNNR - Wednesday, August 23, 2017 12:42 AM
Please share, because it would be important to know if an alternate solution to Maintenance Plans is being selected. I'd also think it would be nice to hear the other two tools in your top 3 ahead of Ola's solution and besides Minion.
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
.
OK, so no parity with Ola's solution without enabling xp_cmdshell.
All of that said, you can still have a secure server even with xp_cmdshell enabled.
The "secure server" piece of your statement needs heavy qualification.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato - KenpoDBA - Wednesday, August 23, 2017 3:55 AM
I cannot quite connect your response to my original comment so let me clarify.
- DBMail is not a "language runtime" outside the database engine, nor is SQL Agent, nor Browser, so the comparison you're trying to draw with PowerShell is inaccurate.
- I never said PowerShell was disabled by default nor did I say it was a security risk. I said it was a "language runtime" outside the engine.
- I also never explicitly stated xp_cmdshell was a security risk, although I believe it is, because I do not want to get into that. I think there are plenty of materials on the web that draw attention to the fact that great care should be taken when employing xp_cmdshell. For example, this article and the ensuing comments. Let's not rehash it here in terms of Minion, Sean. People can find the material and draw their own conclusions. Vendors that require xp_cmdshell often don't provide, in my opinion, comprehensive information qualifying the security risks associated with employing xp_cmdshell but I cannot control that. All I can do is highlight the situation so people can make informed decisions.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato - Orlando Colamatteo - Wednesday, August 23, 2017 6:31 AM
No those items aren't language runtimes. I was giving an example of other things that are off by default, that you still use, so giving that as a reason for not using PS isn't valid. And I don't honestly believe that most people have the ability to read something and make up their own mind intelligently. These are complicated topics and most people don't put enough effort into it to really know what's important. I am concerned though that you're still out there preaching against cmdshell even after the long thread in the article you linked to... again, another article by me. And I remember how impossible it was to get an answer out of you and we never did. So I'm asking you again. Get away from what you feel and tell me how cmdshell is a security risk. I don't want to hear about how far behind in the times it is, or how dumb people are for including it in their solutions. I just want a solid case of how it's a security risk, of how it lessens the security of your server. Without you giving me even on example, you have no argument. That's why I called that article 'Security Theater', because disabling cmdshell is just that, it's theater. It has no basis in reality.
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
- gtrennert - Wednesday, August 23, 2017 4:07 AM
The maintenance plan is most likely trying to enable a server config that is no longer supported. Without direct access, it would be difficult to see for sure but that is usually the case.
I would not use either of the query plan events in an xe session for this particular issue. This is maintenance plan issue specifically and not a query plan issue or an index defrag issue - given that the individual statements run fine outside of the plan.You could enable verbose logging in the maint plan and share the log file which would be more helpful. Honestly though, the general consensus is to not use the built-in maintenance plans for index maintenance.
Are you running any file system tasks in the maintenance plan? Or even do you already have logging set to output to the filesystem?
Shot in the dark would be there could be a permissions issue with the file system related tasks.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 - Orlando Colamatteo - Wednesday, August 23, 2017 6:15 AM
Erm, You do realize that Ola logs to the local server and not a central server right?
It has already been shown how the Ola solution is no more secure than using xp_cmdshell. If you believe his solution is secure, why would a solution using cmdshell be considered not secure?
I agree with Sean here, show us a legitimate security concern with xp_cmdshell that cannot be secured. Otherwise the argument against cmdshell is pretty baseless.
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 -
@jason - I checked out the linked article about "security theater" and tested what the writer claimed to be a "security hole", which was in fact an edge case that no DBA in their right mind would EVER allow. I'd be real careful about using that to defend your opinion that Ola Hallengren's Maintenance Plan isn't secure. If people are going to pick on sqlcmd, how about paying attention to the one job that comes preinstalled on SQL Server (since 2012 at least, probably 2008...I don't remember off the top of my head) that executes a sqlcmd command. If that is a security "flaw" why has nobody reported that to Microsoft and/or demanded it be removed from being installed by default? I'll tell you why--stating that sqlcmd is exploitable is like saying Microsoft is exploitable--of course it is...if misused. That's why there are DBAs and forums like this, to make sure technology is not misused.
Alan H
MCSE - Data Management and Analytics
Senior SQL Server DBA
Best way to ask a question: http://www.sqlservercentral.com/articles/Best+Practices/61537/ - SQL_Hacker - Wednesday, August 23, 2017 11:13 AM
Sorry Alan, that's not a valid argument. For starters, the example in that article is just one of many ways that could happen. And I've seen that exact scenario in many shops because many shops do in fact struggle with giving teams the ability to manage their own jobs. See, groups can't own jobs, so you have to grant individual rights sometimes. Read up on it sometime. I had to do it one time when someone needed to make changes to job steps in code for over 300 jobs. So don't tell me it's unrealistic. And you can say, just have the DBAs do it, but many many companies don't have that culture. They like app teams to be able to do their own thing. So yeah, that's a perfectly valid scenario... that and many others.
Now as well, about sqlcmd being a security flaw. That in an of itself isn't a flaw any more than cmdshell is. But it can easily be exploited and that makes it a security flaw, even more than cmdshell cause you have to be sysadmin by default to use cmdshell. MS counts on the fact that you'll secure your environment to make things like sqlcmd safe.
And if you think it can't be easily exploited then you've never had to break into a SQL box. I've seen it many times personally, and I've used it more than my fair share as well.
And we have no idea what's been reported to MS and what hasn't. Because they haven't done anything about it means that they expect you'll secure your environment, which is what securing cmdshell is all about. Don't disable it, secure your environment. Tools like sqlcmd and cmdshell don't make you less secure; they point out your existing flaws in security. Address those, and you can use the tools to your advantage.
But make no mistake... this kind of exploit against sqlcmd happens all the time, and it's very easy to even slip a comand into a job step through code. I've done it and I know Jason's done it. We've all had to do it.Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
- KenpoDBA - Wednesday, August 23, 2017 11:30 AM
Bolded the statement that makes my argument for emphasis. Just like sqlcmd, the same is true of xp_cmdshell. Of course it can be exploited if misused. But don't tell me that xp_cmdshell isn't secure and then turn right around and use a sqlcmd step in SQL Agent while thinking that it is more secure. It isn't any more secure. I would argue it is less secure. Most shops do not bother with securing against the misuse of sqlcmd steps through an agent job while they solely focus on xp_cmdshell or insist on xp_cmdshell being disabled. That is pure theatre. In short, the argument that Minion can't be used because of xp_cmdshell is weak.
Enabling xp_cmdshell just means that the DBA should go and do their job and properly secure it so it can be used.Taking it a step further, one can compromise a sql server using sqlcmd without having access to sql server. Again, this just weakens the whole security argument against xp_cmdshell and why one should use Ola over 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 - SQL_Hacker - Wednesday, August 23, 2017 11:13 AM
Are you referring to the syspolicy_purge_history job?
Sue
-
Going back to the OP, I ran that error through a translator (as I didn't know what it meant) and it is a strange one for sure.
As a thought, when you click on "Manage Connections..." (the text, not the down arrow), you should see something pop up that will have the name like "Local server connection" and "Server" should be the server you are expecting it to run against. Is the value in the Server column the name of the you are trying to run the MP on?
I've had that break on me before when migrating SQL instances to new boxes (when I migrate the system databases as well).
The other thing to look at would be the logging options. I don't think that would be the issue with this particular MP (based on the error), but that is an odd error.
For those who didn't run it through a translator:Error Message: Running the "" query failed with the following error: "". Possible causes of this failure: query issues, "ResultSet" property not defined correctly, parameters not defined correctly, or connection not established correctly.
I personally have not run across that particular propblem. But I think running a blank query shouldn't give an error.
One thing I've had to do with the reorganzie or rebuild indexes maintenance plan step is to remove the step, re-insert it and set everything up again. What options do you have set for rebuilding the index? Maybe try reducing the scope of the step. That is, try only doing it on 1 database and add more until you either get an error OR it works successfully with all databases.The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. - Sue_H - Wednesday, August 23, 2017 12:28 PM
@sue - Yes, that's the job I'm referring to. It has a step that calls sqlcmd.
Alan H
MCSE - Data Management and Analytics
Senior SQL Server DBA
Best way to ask a question: http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 15 posts - 16 through 30 (of 37 total)
You must be logged in to reply to this topic. Login to reply