Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

MSDB sysjobstepslogs table is huge Expand / Collapse
Author
Message
Posted Wednesday, December 11, 2013 12:58 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 11, 2014 6:43 PM
Points: 2,838, Visits: 8,570
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 ?



Post #1522030
Posted Wednesday, December 11, 2013 1:41 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 3:26 PM
Points: 597, Visits: 7,195
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:

sp_help_jobsteplog
sp_delete_jobsteplog

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.




-
Post #1522046
Posted Wednesday, December 11, 2013 2:27 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 11, 2014 6:43 PM
Points: 2,838, Visits: 8,570
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.



Post #1522061
Posted Wednesday, December 11, 2013 2:40 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 3:26 PM
Points: 597, Visits: 7,195
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!



-
Post #1522067
Posted Wednesday, December 11, 2013 2:48 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 3:26 PM
Points: 597, Visits: 7,195
Hm, I may have hit on something. I found an old thread here on SSC from someone having a similar problem:

Thread

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.




-
Post #1522068
Posted Wednesday, December 11, 2013 7:33 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 11, 2014 6:43 PM
Points: 2,838, Visits: 8,570
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 ?



Post #1522124
Posted Thursday, December 12, 2013 7:31 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 3:26 PM
Points: 597, Visits: 7,195
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.




-
Post #1522312
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse