SQL-2012-logo www.oostdam.infoMicrosoft SQL Server is a great product to work with. Especially after the releases of version 2005, 2008 version and now we have the 2012 available. For me, it is the standard for all kinds of databases. The Object explorer, Tuning Advisor, the Query Analyser and T-SQL statements work great, and are a joy to work with. In this version there are even more and more improvements, so thats the main reason I will publish quit a number of articles about it in the next couple of weeks.  In these articles I like to share with you, some errors and "best practices" that came up during my work on a number of occasions. So, initially set up was for my own references, now I share it with you. Enjoy these articles!! Due to the fact that a big percentage of visitors of this page are international based, this article is in the english language. This article handles the shrinking of the MSDB database in case someone did not configure the maintenance-plan correctly. ( or just has never been aware of this functionality).


The problem with the MSDB database :
Sure, your monitoring will alert when a disk is getting out of free space. Mostly your first liners will solve this by removing some temp files etc. etc. but eventually the problem will become larger and it will end up to you as a DBA to solve the issue with the large MSDB database, once they found out that it is that database that keeps on growing. Often this will be on a production server which cannot have a maintenance window and should be always online. There is no default job to solve this, and even a basic shrink will not be possible. The cause is often found in the sysmaintplan_logdetail table, which stores the history of the SQL Agent Server activities and can create thousands and thousands of rows in the MSDB database. Below is the method I personally use for this kind of actions. Please be aware that you should always be able to recover everything, so make (extra) (full) backups before you start doing things like this. Having that said, were now going to solve this issue. You will be the hero of the department!

Open your SSMS and start a new, blank query. I found these lines from Jeremy on the internet, that displays the actual size of the tables within this database. This is great stuff, you should store this on your laptop for future use on other databases.

-- Start of SQLQuery to display Content of the MSDB database
SELECT object_name(i.object_id) as objectName,
i.[name] as indexName,
sum(a.total_pages) as totalPages,
sum(a.used_pages) as usedPages,
sum(a.data_pages) as dataPages,
(sum(a.total_pages) * 8 ) / 1024 as totalSpaceMB,
(sum ( a.used_pages) * 8 ) / 1024 as usedSpaceMB,
(sum(a.data_pages) * 8 ) / 1024 as dataSpaceMB
FROM sys.indexes i
INNER JOIN sys.partitions p
ON i.object_id = p.object_idAND i.index_id = p.index_id
INNER JOIN sys.allocation_units a
ON p.partition_id = a.container_id



Now, you will see a result something like underneath,...

Sysmaintplan_LogDetail picture www.oostdam.info article 365








It is obvious, the problem in the MSDB database resides indeed in the sysmaintplan_logdetail table..... You might have have tried the stored_procedure called SP_MAINTPLAN_DELETE_LOG, but is you ran out of space, it will not work, and also it is possible that it is not present when your trying to call it. Dammnn..  that suchs....  So, how are we going to approach and solve this?  Did you make your backups Yet?

Open a new query box and select the MSDB database, just to be sure.

-- Query to Alter and Truncate the particular table, adjust the names if it is other than Sysmaintplan_logdetail
ALTER TABLE [dbo].[sysmaintplan_log] DROP CONSTRAINT [FK_sysmaintplan_log_subplan_id];
ALTER TABLE [dbo].[sysmaintplan_logdetail] DROP CONSTRAINT [FK_sysmaintplan_log_detail_task_id];
Truncate table msdb.dbo.sysmaintplan_logdetail;
Truncate table msdb.dbo.sysmaintplan_log;
ALTER TABLE [dbo].[sysmaintplan_log] WITH CHECK ADD CONSTRAINT [FK_sysmaintplan_log_subplan_id] FOREIGN KEY([subplan_id]) REFERENCES [dbo].[sysmaintplan_subplans] ([subplan_id]);
ALTER TABLE [dbo].[sysmaintplan_logdetail] WITH CHECK ADD CONSTRAINT [FK_sysmaintplan_log_detail_task_id] FOREIGN KEY([task_detail_id]) REFERENCES [dbo].[sysmaintplan_log] ([task_detail_id]) ON DELETE CASCADE;



So, we altered and truncated the logs, and that data, now we are going to shrink it. In this case we will use:

USE MSDB
GO
DBCC SHRINKFILE(MSDBLog, 512)
GO
DBCC SHRINKFILE(MSDBData, 512)
GO

So,.. Done that, did that, whats next?

You can verify through your explorer that a large amount of diskspace has been reclaimed. 
So far, so good for now,...  But where not done yet,..
SQL doesn't like databases without indexes and statistics.
The underneath query will rebuild all existing indexes and statistics. 


--The next query will rebuild all the indexes…
USE MSDB
GO
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GO
-- UPDATE STATISTICS
EXEC sp_updatestats
EXEC sp_helpdb @dbname= 'MSDB'


And we are done for now!


Only once you checked the whole system, you should contact your SQL DBA. Let him create an additional maintenance step or plan that will remove the Agent Browsers history details from your MSDB database on a regular basis. This can be done by selecting a default out-of-the-box setting or through a stored procedure. Please also be aware of the possibility to arrange this from your SSMS. Right click on the SQL Agent and choose properties. Find History and choose how much rows and tables you want to maximum save. Please also see the checkbox, where you will be able to set a stored history for a number of days, weeks or months.


Note:
 
- The above queries/statements will also work in earlier SQL versions.
- If you want to leave more Agent history in this database, shrinkfile the log and data with other values.


If you have any questions about this article, or have an addition to it, do not hesitate to contact me.
Have fun using SQL Server 2012 !



 

-----------------------------------------------------------------------------------------------------

Ben OostdamBen Oostdam has been working with Windows systems since 1993. Worked for several companies as a system administrator, and is currently a Senior Support Engineer and Trainer for a large company in the Netherlands specialized in System Center Solutions, Windows 7 & 8 and Windows Server products 2008 & 2012.

Disclaimer: The information contained in this website/article is for general information purposes only. The information is provided as is, by Ben Oostdam and while we endeavour to keep the information up to date and correct, we make no representations or warranties of any kind, express or implied, about the completeness, accuracy, reliability, suitability or availability with respect to the website or the information, products, services, or related graphics contained on the website for any purpose. Any reliance you place on such information is therefore strictly at your own risk. In no event will we be liable for any loss or damage including without limitation, indirect or consequential loss or damage, or any loss or damage whatsoever arising from loss of data or profits arising out of, or in connection with, the use of this website. Through this website you are sometimes able to link to other websites which are not under my control. I have no control over the nature, content and availability of those sites. The inclusion of any links does not necessarily imply a recommendation or endorse the views expressed within them. Every effort is made to keep the website up and running smoothly. However, I take no responsibility for, and will not be liable for, the website being temporarily unavailable due to technical issues beyond our control. All entries in these articles, are my individual opinion, or from co-writers and they don't necessary reflect the opinion of my employer.



 

Wednesday the 17th, July 2019. All rights reserved.. // Oostdam WebDesign