Tag Archives: MSDB

Notification for Restored Database

— by Ginger Keys

According to best practices for a SQL environment, you should never place your test or development databases on a production server. However sometimes best practices are ignored, sometimes the DBA’s advice is disregarded (ahem…), or sometimes an organization simply does not have the financial resources to spin up separate servers for testing or development.

Whatever the reason, if your production server happens to have test or development databases on it, you need to be make sure these databases are managed properly. Usually those databases get periodically restored with a copy of the production database, and you need to ensure there is enough room on the disk to accommodate all data and log files. Typically the production database will be in Full recovery mode possibly with a large transaction log file to accommodate daily transactions. The test or development database normally does not need the large transaction log file and usually does not need to be in Full recovery mode.

Instead of manually checking each database to see when it might have been restored, create a SQL Agent job that will notify you when this happens so that you can go in proactively and manage the environment.

The following statement can be inserted in the SQL Agent Job step, and will provide a basic notification in the event your databases have been restored. Create the job to execute however often you need for your environment… (I have mine running once per week and have set the parameter for [restore_date] to check the last 7 days of activity).

–create a temp table to hold your data

create table #Restores

(      [destination_database_name] nvarchar(50),

[restore_date] date,

[restore_type] nvarchar(20),

[user_name] nvarchar(50)

)

 

–insert data into your temp table from msdb.dbo.restorehistory table

;with LastRestores As

(      select [destination_database_name],

[restore_date],

CASE [restore_type]

WHEN ‘D’ THEN ‘Database’

WHEN ‘F’ THEN ‘File’

WHEN ‘G’ THEN ‘Filegroup’

WHEN ‘I’ THEN ‘Diff’

WHEN ‘L’ THEN ‘Log’

WHEN ‘V’ THEN ‘VerifyOnly’

ELSE NULL

END AS restore_type,

[user_name]

from msdb.dbo.restorehistory

where ([destination_database_name] like ‘%dev’ OR [destination_database_name] like ‘%test’)

AND [restore_date] >= (GETDATE() – 7) –change to timeframe appropriate to your environment

)

 

insert into #Restores

select [destination_database_name], [restore_date], [restore_type], [user_name]

from LastRestores

 

–if temp table returns any rows, send notification email

if (select count(*) from #Restores) > 0

begin

exec msdb.dbo.sp_send_dbmail

@profile_name = ‘DBMailProfile’,

@recipients = ‘SomeoneImportant@yourompany.com’,

@subject = ‘Test Database Restored’,

@body = ‘A test or training database has been restored on InstanceName. Please make necessary adjustments.’

end

 

–drop your temp table

if (OBJECT_ID(‘tempdb..#Restores’) is not null)

begin

drop table #Restores

end

 

This is a simplistic statement to notify you of restore activity for databases you specify. You can get much more sophisticated if necessary by sending the query output as a file or html, or by triggering an immediate notification whenever the database is restored.

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!

Large MSDB Database From sysmaintplan_logdetail Table

— By Lori Brown @SQLSupahStah

I am recycling this blog post from 2015….”Why?”, you might ask…..Well, I am still running into instances with this exact issue. Soooo, here we go….again.  Enjoy.

I recently received a panicked call from a client who had a SQL instance go down because the server’s C drive was full. As the guy looked he found that the msdb database file was 31 GB and was consuming all of the free space on the OS drive causing SQL to shut down. He cleaned up some other old files so that SQL would work again but did not know what to do about msdb.

As we looked at it together I found that the sysmaintplan_logdetail table was taking all the space in the database. The SQL Agent had been set to only keep about 10000 rows of history but for some unknown reason the table never removed history. After consulting MSDN I found this code did the trick for truncating this table.

USE msdb

GO

ALTER TABLE [dbo].[sysmaintplan_log] DROP CONSTRAINT [FK_sysmaintplan_log_subplan_id];

GO

ALTER TABLE [dbo].[sysmaintplan_logdetail] DROP CONSTRAINT [FK_sysmaintplan_log_detail_task_id];

GO

TRUNCATE TABLE msdb.dbo.sysmaintplan_logdetail;

GO

TRUNCATE TABLE msdb.dbo.sysmaintplan_log;

GO

ALTER TABLE [dbo].[sysmaintplan_log] WITH CHECK ADD CONSTRAINT [FK_sysmaintplan_log_subplan_id] FOREIGN KEY([subplan_id])

REFERENCES [dbo].[sysmaintplan_subplans] ([subplan_id]);

GO

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;

GO

 

After the table was truncated we were able to shrink the database to about 1 GB. For the record – I hate, hate, hate to shrink databases but there were no other options left to us and we had to clear out some room on the drive.

Now with the crisis averted we checked the SQL Agent settings and found that the box to remove agent history was not checked.

Blog_20150702_1

We checked it, hit OK then opened the SQL Agent properties again only to find that the box was unchecked. After doing some research I found that this is a bug that has not been resolved even in SQL 2014.   https://connect.microsoft.com/SQLServer/feedback/details/172026/ssms-vs-sqlagent-automatically-remove-agent-history-bugs Awesome, huh?!

If you check the link there is a workaround posted. I have tested it and found that it takes a super long time to run sp_purge_jobhistory and my test server only has 2 jobs that would have any history at all. So, use the workaround if you feel brave. Hopefully Microsoft will actually fix this some time. Until then, keep an eye on your msdb database size.

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at sqlrx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. We love to talk tech with anyone in our SQL family!