HELP, My SCCM SQL DB Filled Up

-

Good morning friend, this is your wakeup call. The SCCM database is growing at 1 GB per minute and you’re about to be out of space. Yes, there are better ways to wake up, like hearing the waves crashing on the beach outside of your tropical hotel room.

Our DBA team let us know that the alerting we were getting was spot on, and they had tracked down one transaction in SQL that was running for a very long time. They showed us the details on that transaction which included a client name. We went with our first reaction which was to completely destroy that client install and hopefully stop the bleeding. While that worked, support later told us to just DISABLE the service on the client to retain logging data that would otherwise get deleted.

We looked at our SCCM database using the standard reports available in SSMS, specifically the Disk Usage by Top Tables report. The column for Data (KB) is what we care about the most and for dbo.Logs it showed a size just north of 700 GB. To put this into perspective, our normal for this table is around 100-150 MB per day retained.

The dbo.Logs table is maintained by the built-in maintenance task Delete Aged Log Data. According to Microsoft, you should use this task to delete from the database aged log data used for troubleshooting. This data isn't related to Configuration Manager component operations. This means that we can safely purge this down to nothing and the only impact should be that we delete data that could help us troubleshoot issues, like this issue.

So it’s 9:00 AM, our database has auto grew itself to within an inch of the drive’s capacity, and nothing in SCCM works. Let’s just go to that maintenance task and click “Run Now” to fix this. NOPE, there isn’t a run now button. You can try changing the time the task runs to a time very shortly in the future, but this doesn’t always work. Our other problem aside from the lack of a “Run Now” button was that our DB exploded by 700 GB between 5 AM and 8 AM, and this maintenance task only accepts a “days to keep” parameter equal to or higher than 1 day which would not have deleted our 700 GB of logs that were less than a day old.

We asked Copilot and ChatGPT how to manually run that task with a specific parameter for number of days, and neither of them had any brilliant answers. While this isn’t easily discoverable in any documentation I could find, maintenance tasks are just SQL stored procedures. The query from the below screenshot shows you the thousands of SQL stored procedures for you to pick from, and we stumbled across this one called spDeleteAgedLogData. Spoiler alert, most others are named very similar to their plain text display name in the maintenance tasks GUI.

FULL DISCLOSURE:  EXECUTING THIS SP IS NOT A SUPPORTED / ADVERTISED METHOD FOR RUNNING SCCM MAINTENANCE TASKS. DO THIS AT YOUR OWN RISK. TEST IN QA. MAKE SURE YOU HAVE GOOD BACKUPS. IF YOU’RE NOT COMFORTABLE WITH THE POTENTIAL CONSEQUENCES OF THIS 100 PERCENT, CONSIDER NOT TOUCHING THIS UNLESS YOU ARE TOLD TO BY MICROSOFT SUPPORT.

Our scheduled run of this Delete Aged Log Data maintenance task removes data over 7 days old, but for this issue we needed to get into the last 5 hours, which cannot be done with the 1 day minimum parameter accepted in the console GUI. Our solution was to create a new SQL query (EXEC spDeleteAgedLogData 0) which would run this SQL stored process manually and delete ALL aged log data. This was also our “Run Now” button.

Our average run time for this maintenance task was around 20-30 seconds. If you were to run this maintenance task with the dates shrunk down from 7 to 0, you would expect it to take about 3 minutes. In our case we executed it multiple times dropping the parameter by 1 from 7 to 0. When we ran it each time down to 1, we had our expected 30 second run times. When we ran it with 0 (remember we had to clear out the dbo.Logs table within the last few hours), it ran for a little over 7 hours and our dbo.Logs table shrunk from 700 GB down to about 70 MB. #WINNING

It is worth noting that if you just want to hit the “Run Now” button with whatever parameters you already have set in the GUI, you can manually do this from a quick query. The below query will set the RunNow value from 0 to 1, which trips the task to run. If you’re fast enough, you can watch the task go from 0 to 1 and then back from 1 to 0, but the key if you miss that is you’ll see the last start / completion times move from the last run to the current time where you ran it.

Run Now:

See It Run:


See It Completed:

Again a full disclaimer, if you are not comfortable with any of this, DON’T DO IT. Seek professional help from Microsoft. I’m not a DBA and there are likely tons of people out there that are laughing at this post because they know it better. On that note, if you have any tips for edits to this, please message me and I’ll make it sound smarter with your advice + I’ll give you some credit. As always, thanks for reading!


Comments

Post a Comment

Popular posts from this blog

CMPivot to check Services and start them (with a little help)

-
Image
Have you ever needed to get a really fast real-time look at if a service is running on a set of servers or workstations? Open CMPivot against a collection, type in your query, and send it. Seconds later you get real-time answers to your query for any online device. Queries for CMPivot run on 42 devices at once, until all devices you're querying have responded.  The last time my organization did server updates, I (and a trusty super awesome coworker) had to verify if a couple of SQL services were running on a small collection of servers. As we were manually checking these one by one, I came up with the idea that it would be incredibly helpful to use CMPivot.  The below query in CMPivot will return all devices where a SERVICE with the name containing SQL is NOT RUNNING and the service's start type is AUTO. In other words, if a service with SQL in the same is supposed to be automatically running and it's not, this query tells us. Service | where Name contains 'sql'
Read more

Run Scripts with Parameters in MEMCM (R.I.P. SCCM)

-
Image
How do you use Parameters in MEMCM's (R.I.P. SCCM) Run Scripts? How do you use Parameters on the Run Scripts  feature? You get three guesses, but the first two don't count. That's right, you create a script with standard PowerShell Parameters and you build it out in MEMCM. In your MEMCM console, navigate to Software Library > Overview > Scripts. If you're not familiar with Run Scripts  in MEMCM, check out my previous Blog articles on it. Click on Create Script . In the Create Script Wizard  that pops up input a Script Name , pick between PowerShell or PowerShell in your Script Language , import or paste in your script with Parameters, and click on Next . My demo script for this article ( scroll alllll the way down, it's at the bottom ) is about an 8 out of 10 on the cool factor, and about a 3 out of 10 on the usefulness factor. Long story short, the script will make a computer talk. There are two Parameters that you can input, one for "what do y
Read more

SCCM CMPivot and Run Scripts

-
Image
What is CMPivot? What are Scripts? Why do I care about either of them? I recently attended MMS 2019 and took a great session from Jason Sandys and Tom Degreef on CMPivot and Run Scripts. They dove deep into the technical aspects of both of these somewhat new "fast channel" features of SCCM. Full disclaimer: I do not know everything and I may occasionally not know as much as you. Test everything you see here on a LAB environment and make sure you fully understand it. If you have any suggestions for improving this post, please feel free to drop a comment and I'll give you some kudos for your edit. Over simplified, CMPivot is a near real-time (not milliseconds, but usually seconds, and definitely no more than a few minutes) way to query information about your systems. You can find out literally anything about any system within your reach. Scripts pairs nicely with CMPivot because it is a real-time way to run scripts against your query results, or just another old col
Read more