December 11, 2013 at 12:58 pm
My MSDB database in production has been growing about 100 meg per day for no apparent reason. I noticed the sysjobstepslogs table seems to be the culprit. I believe this stores information when logging for a job is selected by checking "Log to Table" on scheduled Job Steps.
sysjobstepslogs only has 66 records, yet is 1.7 Gig !!! I have other SQL servers with more jobs, that run frequently, that also have "Log to Table" checked on their jobs, and their sysjobstepslogs tables are tiny.
Can anyone shed light on why this table seems to be growing in size, not records, and what can I do about it ??
Is it safe to truncate the table assuming I don't care about the current log activity in it ?
December 11, 2013 at 1:41 pm
One thing to note is that the storage field for the table's output is NVARCHAR(MAX), so it holds quite a bit of data, and every time the job runs, it logs to the table by concatenating the latest output to the existing row for the job in sysjobstepslogs (at least, to my knowledge).
If you've had this job logging to the table for quite some time, it's going to be quite bloated from holding the timespan's worth of logged messages, hence the large table size with very few rows. I don't believe there's any harm in truncating the table, as long as you're sure you're getting the data you need out of the jobs, and that there's no funky workarounds in your system that require a job to log to the table in order to pull data from it (I've seen a case where it's been done... Quite clunky, and I'm sure there was a better way).
EDIT: Doh, nearly forgot. If it turns out you do in fact need the logging data, sp_help_jobsteplog and sp_delete_jobsteplog will be useful:
The first will let you figure out properties of the job logs (such as size), and the second will allow you to delete based on those properties.
- 😀
December 11, 2013 at 2:27 pm
Thanks.
The growth seems to correspond to when I modified most of the jobs to "Log to Table". I did the same to other servers and they do not have the same msdb growth. 1 difference is that a daily backup job also does an "append" to a file, but that file is on the C drive, not in the database, so don't see why that should be involved. Still a mystery.
December 11, 2013 at 2:40 pm
Ah, my apologies. I was looking at a few old jobs that were apparently using the "append" option with the log table; by default, they shouldn't be appending. Ran a test with a dummy job several times, with the results appending to a file, and I didn't see the table grow, either in the SSMS properties or the sysjobstepslogs size column. Puzzling indeed!
- 😀
December 11, 2013 at 2:48 pm
Hm, I may have hit on something. I found an old thread here on SSC from someone having a similar problem:
Seemingly, SQL Server 2005 (and maybe further versions?) can potentially pad the Log column with extra whitespace; in the above thread, the thread starter was having an entire gigabyte of whitespace padded into the table every day! I'd suggest running this:
SELECT LOG,LEN(Log)
FROM msdb.dbo.sysjobstepslogs
See if there's an unusually long LEN value for a field that seems quite short; if so, it's probably being whitespace-padded. I haven't seen much talk about the issue, but I'll dig around a bit more.
- 😀
December 11, 2013 at 7:33 pm
Yes, I saw that too, but I have already truncated the table. I will keep an eye on it.
Thanks
P.S. I wonder if I can restore a MSDB backup as MSDB_Test to look at the original table. I assume there's no harm restoring a system table with a new name ?
December 12, 2013 at 7:31 am
I believe there shouldn't be a problem with doing that; I restored an MSDB copy on a test server (since this whole thing's gotten me kinda interested in the problem), and was able to run jobs and so on without any ill effects. Naturally, I'd recommend doing this on a test server for absolute certainty.
Good to know the problem's fixed, though. Hadn't heard of this happening before, so I was curious about what could cause this.
- 😀
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply