--- title: Why do you have so many housekeeping jobs? | daily.dev description: SQL Server instances often accumulate many housekeeping jobs scheduled at midnight, which can create compounded resource usage and invite processes to... canonical: https://daily.dev/posts/why-do-you-have-so-many-housekeeping-jobs--q5kep9r0k twitter:card: summary_large_image twitter:site: @dailydotdev og:type: website og:site_name: daily.dev og:title: Why do you have so many housekeeping jobs? | daily.dev og:description: SQL Server instances often accumulate many housekeeping jobs scheduled at midnight, which can create compounded resource usage and invite processes to... og:url: https://daily.dev/posts/why-do-you-have-so-many-housekeeping-jobs--q5kep9r0k og:image: https://api.daily.dev/og/posts/q5Kep9r0K.png og:image:alt: Why do you have so many housekeeping jobs? og:image:width: 1200 og:image:height: 630 og:locale: en --- > ## Documentation Index > Fetch the complete documentation index at: https://daily.dev/llms.txt > Use this file to discover all available pages before exploring further. # Why do you have so many housekeeping jobs? **[SQLBlog.org \(Aaron Bertrand\)](https://daily.dev/sources/sqlblog)** · 16 min read · 0 upvotes · 0 comments ## Summary SQL Server instances often accumulate many housekeeping jobs scheduled at midnight, which can create compounded resource usage and invite processes to interfere with each other. Aaron Bertrand recommends consolidating routine maintenance into a single sequential job with per-step day-of-week logic instead of DATENAME or DATEPART checks, which are unreliable. He suggests a watermark-date formula (using DATEDIFF modulo 7) to reliably detect weekdays. Other tips include cycling the error log weekly with 32 retained logs, cleaning Extended Events files with a PowerShell sample, batching purges instead of monthly bulk deletes, and logging job outcomes to a custom table since Agent history is bounded per job. ## Full article daily.dev links to this article rather than hosting it. Read it at the original source: ## Questions this post answers ### Why shouldn't I use DATENAME(WEEKDAY, getdate()) to check if today is a specific day in SQL Server? Because DATENAME(WEEKDAY, ...) is influenced by the @@LANGUAGE setting and DATEPART(WEEKDAY, ...) is influenced by @@DATEFIRST, so results can differ across servers with different regional settings. A safer approach computes the delta in days from a known date that falls on the target weekday, then checks if that delta is evenly divisible by 7 using DATEDIFF modulo 7. _Developers hardening scheduled SQL Server jobs against regional settings can compare approaches like this on daily.dev._ ### How many archive files can SQL Server Agent's error log rollover keep, and how do I set a longer retention for the SQL Server error log itself? SQL Server Agent's own log rollover caps at 9 archive files (10 total), so longer retention requires cycling less frequently. For the SQL Server error log itself, retention can be extended using Set-SqlErrorLog with -MaxLogCount, or by writing the NumErrorLogs value directly to the registry via sys.xp_instance_regwrite, allowing up to 32 archived logs (33 including the current one). _Teams tuning log retention policies can track practical SQL Server maintenance patterns like this on daily.dev._ ### Why does consolidating multiple SQL Server Agent job steps into one job cause history to disappear faster? Because SQL Server Agent history is bounded per job, and each step execution plus the overall job outcome generates a history row. A job with 20 reachable steps produces 21 history rows per run, so with a 100-row per-job limit, fewer than five complete executions may be retained before older step details are purged. _Admins designing job consolidation strategies can weigh trade-offs like this via daily.dev before changing schedules._ ## Similar posts on daily.dev - [Free Webcast: You Shouldn’t Use SQL Agent for Batch Processes.](https://daily.dev/posts/free-webcast-you-shouldn-t-use-sql-agent-for-batch-processes--bvbubba8n) · Brent Ozar · 0 upvotes · 0 comments - [Nobody Runs Your Cleanup Script \(And Regulators Know It\) — Daily DevOps & .NET](https://daily.dev/posts/nobody-runs-your-cleanup-script-and-regulators-know-it-daily-devops-net-svbkysk9t) · We Are .NET · 1 upvotes · 0 comments - [SQL Server Bulk Delete Batches](https://daily.dev/posts/sql-server-bulk-delete-batches-xs69je9hx) · Medium · 0 upvotes · 0 comments --- Tags: [#microsoft-sql-server](https://daily.dev/tags/microsoft-sql-server), [#powershell](https://daily.dev/tags/powershell) [View this post on daily.dev](https://daily.dev/posts/why-do-you-have-so-many-housekeeping-jobs--q5kep9r0k) ```json {"@context":"https://schema.org","@graph":[{"@type":"Organization","@id":"https://daily.dev/#organization","name":"daily.dev","url":"https://daily.dev","logo":{"@type":"ImageObject","url":"https://daily.dev/apple-touch-icon.png","width":180,"height":180},"sameAs":["https://twitter.com/dailydotdev","https://github.com/dailydotdev","https://www.linkedin.com/company/daily-dev-ltd"]},{"@type":"WebSite","@id":"https://daily.dev/#website","url":"https://daily.dev","name":"daily.dev","publisher":{"@id":"https://daily.dev/#organization"},"potentialAction":{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https://daily.dev/search?q={search_term_string}"},"query-input":"required name=search_term_string"}}]} {"@context":"https://schema.org","@type":"TechArticle","headline":"Why do you have so many housekeeping jobs?","url":"https://daily.dev/posts/why-do-you-have-so-many-housekeeping-jobs--q5kep9r0k","mainEntityOfPage":{"@type":"WebPage","@id":"https://daily.dev/posts/why-do-you-have-so-many-housekeeping-jobs--q5kep9r0k"},"datePublished":"2026-10-27T13:36:36.549Z","dateModified":"2026-10-27T13:40:13.401Z","description":"SQL Server instances often accumulate many housekeeping jobs scheduled at midnight, which can create compounded resource usage and invite processes to...","image":"https://media.daily.dev/image/upload/f_auto,q_auto/v1/posts/341142a7031b6ed7c8bf56b2b73fdcec?_a=AQAEuop","thumbnailUrl":"https://media.daily.dev/image/upload/f_auto,q_auto/v1/posts/341142a7031b6ed7c8bf56b2b73fdcec?_a=AQAEuop","isAccessibleForFree":true,"articleSection":"SQLBlog.org (Aaron Bertrand)","inLanguage":"en","publisher":{"@type":"Organization","name":"daily.dev","url":"https://daily.dev","logo":{"@type":"ImageObject","url":"https://daily.dev/apple-touch-icon.png","width":180,"height":180}},"author":{"@type":"Organization","name":"SQLBlog.org (Aaron Bertrand)","logo":"https://media.daily.dev/image/upload/s--dP89h5-p--/f_auto,q_auto/v1791720356/logos/sqlblog?_a=BAMAMicg0","url":"https://daily.dev/sources/sqlblog"},"commentCount":0,"discussionUrl":"https://daily.dev/posts/why-do-you-have-so-many-housekeeping-jobs--q5kep9r0k","interactionStatistic":[{"@type":"InteractionCounter","interactionType":{"@type":"LikeAction"},"userInteractionCount":0},{"@type":"InteractionCounter","interactionType":{"@type":"CommentAction"},"userInteractionCount":0}],"keywords":"microsoft-sql-server,powershell","timeRequired":"PT16M"} {"@context":"https://schema.org","@type":"BreadcrumbList","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https://daily.dev"},{"@type":"ListItem","position":2,"name":"SQLBlog.org (Aaron Bertrand)","item":"https://daily.dev/sources/sqlblog"},{"@type":"ListItem","position":3,"name":"Why do you have so many housekeeping jobs?"}]} {"@context":"https://schema.org","@type":"FAQPage","@id":"https://daily.dev/posts/why-do-you-have-so-many-housekeeping-jobs--q5kep9r0k#faq","mainEntity":[{"@type":"Question","name":"Why shouldn't I use DATENAME(WEEKDAY, getdate()) to check if today is a specific day in SQL Server?","acceptedAnswer":{"@type":"Answer","text":"Because DATENAME(WEEKDAY, ...) is influenced by the @@LANGUAGE setting and DATEPART(WEEKDAY, ...) is influenced by @@DATEFIRST, so results can differ across servers with different regional settings. A safer approach computes the delta in days from a known date that falls on the target weekday, then checks if that delta is evenly divisible by 7 using DATEDIFF modulo 7. Developers hardening scheduled SQL Server jobs against regional settings can compare approaches like this on daily.dev."}},{"@type":"Question","name":"How many archive files can SQL Server Agent's error log rollover keep, and how do I set a longer retention for the SQL Server error log itself?","acceptedAnswer":{"@type":"Answer","text":"SQL Server Agent's own log rollover caps at 9 archive files (10 total), so longer retention requires cycling less frequently. For the SQL Server error log itself, retention can be extended using Set-SqlErrorLog with -MaxLogCount, or by writing the NumErrorLogs value directly to the registry via sys.xp_instance_regwrite, allowing up to 32 archived logs (33 including the current one). Teams tuning log retention policies can track practical SQL Server maintenance patterns like this on daily.dev."}},{"@type":"Question","name":"Why does consolidating multiple SQL Server Agent job steps into one job cause history to disappear faster?","acceptedAnswer":{"@type":"Answer","text":"Because SQL Server Agent history is bounded per job, and each step execution plus the overall job outcome generates a history row. A job with 20 reachable steps produces 21 history rows per run, so with a 100-row per-job limit, fewer than five complete executions may be retained before older step details are purged. Admins designing job consolidation strategies can weigh trade-offs like this via daily.dev before changing schedules."}}]} ```