Posts Tagged ‘msdb’

So how do you drop databases?
Do you use a simple DROP DATABASE statement?
Do you detach the database and remove the files manually?
Do you use the GUI?
Run these two quick and dirty queries for me:
SELECT COUNT(*)
FROM   msdb.dbo.backupset
SELECT COUNT(*)
FROM   msdb.dbo.backupset
WHERE  database_name NOT IN (SELECT name FROM master.dbo.sysdatabases)
 

So how much of your MSDB database is filled with backup history of databases that have long since been dropped from your [...]

If you’ve ever moved MSDB and have notifications setup using DatabaseMail then you probably already know what I (re)learned today.
If you move MSDB your notifications will stop working.  Why you may ask?
It’s because the ENABLE_BROKER configuration is set to false when you move MSDB.  Now I can’t seem to find (google) any information as to [...]

So if you follow me blog you will probably remember this post from earlier today:
http://benchmarkitconsulting.com/colin-stasiuk/2009/01/21/sqlagentreaderrole-not-so-reader/
Below is the solution I’m working with to give developers access to the Job Activity Monitor but NOT the ability to create new jobs.
I know adding a role to a system database is not ideal but I’m open to other suggestions [...]

Quick… how do you give someone read only access to the Job Activity Monitor?
Why you grant them SQLAgentReaderRole access in msdb…. WRONG
http://technet.microsoft.com/en-us/library/ms188283.aspx
The SQLAgentReaderRole is a reader role for the jobs that currently exist but wait here is where the good (or bad depending on if you’re in a good mood or not) part comes in.
Me [...]