{"id":433,"date":"2014-06-19T10:59:13","date_gmt":"2014-06-19T15:59:13","guid":{"rendered":"http:\/\/www.sqlfreelancer.com\/blog\/?p=433"},"modified":"2014-09-04T14:34:47","modified_gmt":"2014-09-04T19:34:47","slug":"sql-server-maintenance-plans-reporting-and-logging","status":"publish","type":"post","link":"https:\/\/www.sqlfreelancer.com\/blog\/sql-server-maintenance-plans-reporting-and-logging\/","title":{"rendered":"SQL Server Maintenance Plans Reporting and Logging"},"content":{"rendered":"<p>This post will focus on the reporting and logging option of maintenance plans.<\/p>\n<p>When a maintenance plan executes it\u2019s nice to know the results especially in case of a failure. You can view the results in a few different ways that include the following:<\/p>\n<ul>\n<li>Maintenance Plan history<\/li>\n<li>SQL Server Agent Job history<\/li>\n<li>SQL Server Error Log<\/li>\n<li>sp_readerrorlog<\/li>\n<li>Maintenance Plan Reporting and Logging<\/li>\n<\/ul>\n<h2>Maintenance Plan Reporting and Logging Options<\/h2>\n<p>The Maintenance Plan reporting and logging option is enabled by default, but a lot of DBA\u2019s and developers don\u2019t even realize it is an option, much less that it\u2019s enabled.<\/p>\n<p>To configure this option, open a maintenance plan and on the top bar beside Manage Connections\u2026. you\u2019ll notice a little chart\/paper icon. It\u2019s not hidden, but it doesn\u2019t jump out at you and that\u2019s probably why a lot of DBA\u2019s don\u2019t pay any attention.<\/p>\n<p><a href=\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2014\/06\/SQL-Server-Maintenance-Plan-1.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-434\" src=\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2014\/06\/SQL-Server-Maintenance-Plan-1.png\" alt=\"SQL Freelancer SQL Server Maintenance Plan Reporting and Logging\" width=\"572\" height=\"101\" srcset=\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2014\/06\/SQL-Server-Maintenance-Plan-1.png 572w, https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2014\/06\/SQL-Server-Maintenance-Plan-1-300x52.png 300w\" sizes=\"auto, (max-width: 572px) 100vw, 572px\" \/><\/a><\/p>\n<p>If you click the icon you\u2019ll notice there are a few options to choose from:<\/p>\n<p><a href=\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2014\/06\/SQL-Server-Maintenance-Plan-2.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-436\" src=\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2014\/06\/SQL-Server-Maintenance-Plan-2.png\" alt=\"SQL Freelancer SQL Server Maintenance Plan Reporting and Logging\" width=\"534\" height=\"545\" srcset=\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2014\/06\/SQL-Server-Maintenance-Plan-2.png 534w, https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2014\/06\/SQL-Server-Maintenance-Plan-2-293x300.png 293w\" sizes=\"auto, (max-width: 534px) 100vw, 534px\" \/><\/a><\/p>\n<p>Let\u2019s go over each one of these:<\/p>\n<h2><b>Generate a text file report<\/b><\/h2>\n<p>This option allows you to enable or disable the text file report.<\/p>\n<h2><b>Create a new file<\/b><\/h2>\n<p>This option allows you to create a new report file each time a maintenance plan is executed. Create a new file is the default option and the default folder location is the folder you specified SQL Server to use for the LOG folder. You can specify a different location if preferred.<\/p>\n<p>Here is a screenshot of Windows Explorer where a new file is created each execution:<\/p>\n<p><a href=\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2014\/06\/SQL-Server-Maintenance-Plan-3.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-437\" src=\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2014\/06\/SQL-Server-Maintenance-Plan-3.png\" alt=\"SQL Freelancer SQL Server Maintenance Plan Reporting and Logging\" width=\"618\" height=\"447\" srcset=\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2014\/06\/SQL-Server-Maintenance-Plan-3.png 618w, https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2014\/06\/SQL-Server-Maintenance-Plan-3-300x216.png 300w\" sizes=\"auto, (max-width: 618px) 100vw, 618px\" \/><\/a><\/p>\n<p>You may notice that if you run a maintenance plan throughout the day that it could quickly fill up your drive with these 1kb files. Luckily, we don\u2019t have to go in and check our file system and delete these files manually. SQL Server has a task that will automate this for us (see below).<\/p>\n<p>While we are at this point, go ahead and check one of your servers. Check the location of a maintenance plan text file and then check the folder on the server. Or maybe you\u2019ve noticed these files while browsing through your LOG folder and wasn\u2019t sure where they came from.<\/p>\n<h2><b>Append to file<\/b><\/h2>\n<p>This option allows you to create one text file and append the results to that file. This will reduce the number of files, but will increase the size of the file and it makes it more difficult to read in my opinion.<\/p>\n<p><a href=\"http:\/\/www.mssqltips.com\/sqlservertip\/3225\/sql-server-maintenance-plans-reporting-and-logging\/\" target=\"_blank\">Click here to view the rest of this post.<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>This post will focus on the reporting and logging option of maintenance plans. When a maintenance plan executes it\u2019s nice to know the results especially in case of a failure. You can view the results in a few different ways that include the following: Maintenance Plan history SQL Server Agent Job history SQL Server Error [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[91],"tags":[92],"class_list":["post-433","post","type-post","status-publish","format-standard","hentry","category-maintenance","tag-maintenance-plans"],"_links":{"self":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts\/433","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/comments?post=433"}],"version-history":[{"count":4,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts\/433\/revisions"}],"predecessor-version":[{"id":449,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts\/433\/revisions\/449"}],"wp:attachment":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/media?parent=433"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/categories?post=433"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/tags?post=433"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}