Execution Log Clean Up
-
The task that cleans up out of date execution log entries runs at 0200 every morning. This is not an ideal time for us because we're a 24 hour business and the most important times for are actually overnight. There have recently been complaints raised because SSRS is unavailable for a short period at 0200 and this is inconvenient for a lot of users. Is there any way to schedule this clean-up for another time? I suspect not because I've not been able to find anything and the documentation implies that this time is fixed.
On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a PhilosopherHow to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
-
I apologize for not knowing the answer, Neil, but wanted to thank you for giving me another reason to avoid SSRS. 😀
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
You cannot change the time when that process runs - but you can disable the process that removes log entries and perform that step manually.
You can then create a custom agent job - scheduled when you want it to be executed that executes the procedure dbo.ExpireExecutionLogEntries.
If there are a lot of entries to be removed and that procedure is blocking - you could write a custom procedure that batches the deletes and utilizes the index on TimeStart appropriately (standard code cannot use that index as written - which is probably why you see this blocking).
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 - Jeffrey Williams wrote:
You cannot change the time when that process runs - but you can disable the process that removes log entries and perform that step manually.
Good info, Jeffrey. Just curious, though... something is scheduling this. Is it possible that it's using Windows Scheduler, which could be changed if necessary?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
The best I can find is that it is controlled by the service and not externally managed.
Either way, if I had this issue on my servers I would create a custom procedure. The standard code is just wrong.
And they are still using ntext for columns...why?
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 - Jeffrey Williams wrote:
The best I can find is that it is controlled by the service and not externally managed.
Either way, if I had this issue on my servers I would create a custom procedure. The standard code is just wrong.
Totally agreed on the custom procedure being the best choice.
Shifiting gears a bit... I wish MS would stop mandating what needs to be done with no option for override. Another example of such is thing is that, while every DBA that's worth their salt understands the importance of "balanced files" (same size files) in TempDB, not every DBA or MVP understands that there are exceptions to every rule even for that general supposed "Best Practice" that previously required the invocation of TF 1117 to be realized in TempDB . MS did a great thing by making override options by database except they didn't provide an override for TempDB and the ARE extraordinary circumstances where at least a temporary override is absolutely necessary.
Don't even get me started about the automatic windows updates and related reboots in Windows 10. They did provide some relief in being able to schedule it but it doesn't match MY ever-changing schedule for doing such things.
Having this SSRS cleanup thing be under the control of a user-immutable service just pisses me off and is yet another reason why I say that SSRS is a four letter word. 😀
Jeffrey Williams wrote:And they are still using ntext for columns...why?
It's the same old story that MS has followed forever... "Do as we say and not as we do". It's like the semi-colon usage mandate... if you look at the SQL in system objects, they severely violate that and a hundred true "Best Practices".
It may also be because of the problem they know they built into the newer LOB datatypes that few realize... the newer LOB datatypes default to in-row where the old ones defaulted to out-of-row. Why is that a problem? I have a whole presentation on the problems that causes including unnecessarily bloated Clustered Indexes that slow down all queries (not just CI scans) that use the clustered index. Severe and totally unnecessary massive fragmentation due to in-row LOB expansive updates. Same holds true now even if you know how to force the newer LOB datatypes out of row because of the now unreserved fixed size for the pointers (I have a fix for that, as well). And then there's the problem of incredibly low page densities both due to the size of rows with in-row lobs and a thing that I call "Trapped Short Rows", which can result in huge numbers of pages having an immutable page density of only a couple percent of page fullness.
Proof positive that MS is the poster child for the old adage of "Change is inevitable... change for the better is not".
Heh... sorry for the rant but Microsoft's improvements have come at a great cost to me, the data I manage, and the servers it lives on in many areas and the thought of a user-immutable service being "in-charge" of a cleanup schedule just added fuel to my fire. Ranting about it keeps me from driving to Washington state with a truck-load of frozen pork chops. 😀
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Agreed, so let's not even get into how badly they mangled the date functions 🙂
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 - Jeffrey Williams wrote:
Agreed, so let's not even get into how badly they mangled the date functions 🙂
Or the "newer" temporal datatypes themselves. 😀
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden wrote:Jeffrey Williams wrote:
Agreed, so let's not even get into how badly they mangled the date functions 🙂
Or the "newer" temporal datatypes themselves. 😀
Well - that goes without saying...as the problems with those functions are all related to the newer data types.
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 -
Well, that took a tangent...
Thanks for your input gents. I'd done some poking about and I'd couldn't find what was calling the task anywhere. I did find the proc that was doing the delete and it fits right in with the theme of bad practice from people who should know better. It's very frustrating that something, which has the potential to cause an important system to become unavailable for an unknown length of time, can't be scheduled to a time convenient to the user. I get that most systems are quiet at 0200 but we're case-in-point that not all of them are. The Higher-Ups were asking some very pointed and not entirely unwarranted questions about what this task was doing and why we couldn't control it. "I don't fully know" and "because" weren't answers they wanted to hear.
The main reason we were getting the blocking was because of the large number of rows we initially needed to delete after turning the clean-up (back) on. My boss manually deleted the backlog in chunks on Friday and things appear to have been fine over the weekend. I'll know a bit more definitely when the reports come in a little later on.
On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a PhilosopherHow to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
- Neil Burton wrote:
My boss manually deleted the backlog in chunks on Friday and things appear to have been fine over the weekend. I'll know a bit more definitely when the reports come in a little later on.
If it were me, I'd get that code from your boss and turn it into a stored procedure that is run often enough to keep the 02:00 event from being a scheduled killer.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden wrote:Neil Burton wrote:
My boss manually deleted the backlog in chunks on Friday and things appear to have been fine over the weekend. I'll know a bit more definitely when the reports come in a little later on.
If it were me, I'd get that code from your boss and turn it into a stored procedure that is run often enough to keep the 02:00 event from being a scheduled killer.
That makes a lot of sense although everything was fine over the weekend and it looks like it's settled down now. There were no complaints and Spotlight et al said everything was good. Anecdotally, the 0200 unavailability wasn't unknown previously and was taken as a good excuse to put the kettle on, if you get my drift.
I think now we're on top of things we should be all right. It was the two and a half year backlog that caused the problem and deleting a day at time is much quicker. I'll keep the code handy though.
On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a PhilosopherHow to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
-
truly awesome rant sir I salute you and agree with every word 🙂
meant to include the quote, see next post
- Jeff Moden wrote:Jeffrey Williams wrote:
The best I can find is that it is controlled by the service and not externally managed.
Either way, if I had this issue on my servers I would create a custom procedure. The standard code is just wrong.
Totally agreed on the custom procedure being the best choice.
Shifiting gears a bit... I wish MS would stop mandating what needs to be done with no option for override. Another example of such is thing is that, while every DBA that's worth their salt understands the importance of "balanced files" (same size files) in TempDB, not every DBA or MVP understands that there are exceptions to every rule even for that general supposed "Best Practice" that previously required the invocation of TF 1117 to be realized in TempDB . MS did a great thing by making override options by database except they didn't provide an override for TempDB and the ARE extraordinary circumstances where at least a temporary override is absolutely necessary.
Don't even get me started about the automatic windows updates and related reboots in Windows 10. They did provide some relief in being able to schedule it but it doesn't match MY ever-changing schedule for doing such things.
Having this SSRS cleanup thing be under the control of a user-immutable service just pisses me off and is yet another reason why I say that SSRS is a four letter word. 😀
Jeffrey Williams wrote:And they are still using ntext for columns...why?
It's the same old story that MS has followed forever... "Do as we say and not as we do". It's like the semi-colon usage mandate... if you look at the SQL in system objects, they severely violate that and a hundred true "Best Practices".
It may also be because of the problem they know they built into the newer LOB datatypes that few realize... the newer LOB datatypes default to in-row where the old ones defaulted to out-of-row. Why is that a problem? I have a whole presentation on the problems that causes including unnecessarily bloated Clustered Indexes that slow down all queries (not just CI scans) that use the clustered index. Severe and totally unnecessary massive fragmentation due to in-row LOB expansive updates. Same holds true now even if you know how to force the newer LOB datatypes out of row because of the now unreserved fixed size for the pointers (I have a fix for that, as well). And then there's the problem of incredibly low page densities both due to the size of rows with in-row lobs and a thing that I call "Trapped Short Rows", which can result in huge numbers of pages having an immutable page density of only a couple percent of page fullness.
Proof positive that MS is the poster child for the old adage of "Change is inevitable... change for the better is not".
Heh... sorry for the rant but Microsoft's improvements have come at a great cost to me, the data I manage, and the servers it lives on in many areas and the thought of a user-immutable service being "in-charge" of a cleanup schedule just added fuel to my fire. Ranting about it keeps me from driving to Washington state with a truck-load of frozen pork chops. 😀
truly awesome rant sir I salute you and agree with every word 🙂
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply