You can't drop the msdb database as stated in the docs (emphasis mine):
The following operations cannot be performed on the msdb database:
Changing collation. The default collation is the server collation.
Dropping the database.
Dropping the guest user from the database.
Enabling change data capture.
Participating in ...
A StackOverflow answer has a possible explanation:
Each time the SQL Agent starts, it puts a new row in syssessions and
subsequently any jobs run will get that session_id in sysjobactivity.
For your jobs that have a null ...
You need to query the list of databases first (it's in sys.databases), then join it to your query:
WITH LastBackUp AS
Position = ROW_NUMBER() OVER( PARTITION BY bs.database_name ORDER BY bs.backup_start_date DESC )
The sysdac_instances view is defined as follows:
CREATE VIEW [dbo].[sysdac_instances]
-- this must be locked down because we use instance_id visability as a security gate
when (dbo.fn_sysdac_is_currentuser_sa() = 1) then dac_instances.instance_id
when sd.owner_sid = SUSER_SID() then dac_instances....
From your reply to my question, then it does appear to be too much backup history. For a one off cleardown, something along these lines will do it:
DECLARE @days DATETIME
SET @days = DATEADD(day,-30,current_timestamp)
EXEC sp_delete_backuphistory @days
That example gets rid of everything over 30 days old, so you may need to change the -30 ...
Microsoft enabling a feature does not mean it will be useful for everyone. For systems using some of the features can mean relying on information stored in MSDB. In those cases Query Store can be useful.
Here are few articles about the usage and tuning of MSDB database objects.
msdb Database from books online.
MSDB Performance Tuning by Geoff N. Hiten
Backups for SQL Server can be scheduled in different way and we do not know how it is set up in your case.That is why we cannot tell you where to look. Most common scenarios are:
SQL Server Agent Job
3rd party tool
If you run the script mentioned by @Kin in the comment section, it will show that your databases are most likely taking ...
Because back then design controls were much looser than they are today, and the person who was responsible for designing this table thought it would be easier to deal with ints than with separate datetime columns (remember this was back before we had separate date and time data types, and also when the prevailing logic was that dealing with integers was much ...
@SqlWorldWide answered the "why [msdb]" part of the question so I won't duplicate that here. But to answer the "why not [master], [model], [tempdb]" part of the question:
[tempdb] is temporary storage and by its very nature would not seem to ever benefit from either automated optimization or the ability to provide historical analysis. If Query Store tracks ...
As covered in other answers here, do not attempt to delete the entire msdb database.
You might want to drop certain history records that are stored in the msdb database, and then shrink the database if you need to save space. Be aware, I'm not advising you do this since the database will most certainly need to grow again unless you closely manage space ...
Because of where the information is stored in msdb, part of the information is obtained when the Job Properties window is initially opened, then more information is queried when you actually click on the Notifications page. SSMS uses an INSERT/EXEC on the sp_help_ stored procedures to obtain the operator information, which is pretty unwieldy... so ...
Information about databases involved in log shipping are contained in several system tables in the msdb database. Looking at these tables will help identify whether you've properly reconfigured log shipping.
This first query shows databases that are configured as primaries, where the database no longer exists on the SQL Server instance:
While @Max is correct about a) the root cause of this error (collation of restored msdb database does not match the server's default collation, which is used to set the collation of the name field in master.sys.sysdbreg -- the actual source of the name field in sys.databases), and b) that ideally you would rebuild msdb to have a collation matching the server'...
The fastest way is to restore the MSDB database, but if it's your first time doing that, here's an easier shortcut.
Restore the MSDB backup onto an existing (working) database server, but use a different database name than MSDB. The restore should go quickly (because MSDB is typically very small), and you can then verify that your objects are in there.
But, when this job runs, the log file of MSDB grows large (20 - 40 GB).
The problem might be that the job that you are running is not cleaning up msdb in batches. A pseudo code will be as follows :
-- select top size as per batch to delete
-- since you are in simple recovery, perform `CHECKPOINT`
-- repeat until all the delete is complete.
Refer to my ...
You can check the database mail log by going to management -> right-click database mail -> select view database mail log. Any errors should be in here, for example:
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2014-05-16T02:21:47). Exception Message: Cannot send mails to mail ...
The dbo.sysjobs and dbo.sysoperators views provide you the details. Some simple code (cleaned up from coding errors in first post.):
SELECT j.name AS JobName,
j.notify_level_email, e.name AS EmailOperator,
j.notify_level_netsend, n.name AS NetSendOperator,
j.notify_level_page, p.name AS PagerOperator,
Yes, it is safe to cancel that operation (but you need to be patient and let it finish rolling back - do not panic and stop the SQL Server service or reboot the server; all that will do is make the rollback start over).
While you're waiting, let's address your underlying problem.
Outside of a scenario where you manually increase the size to prepare for ...
You could try dynamically generating TSQL to delete the specific step. Here is an example:
'exec msdb.dbo.sp_delete_jobstep @job_id=''' + convert(varchar(50), a.job_id) + '''' + ', @step_id = ' + convert(varchar(30), b.step_id) as CmdToExec ,
FROM msdb.dbo.sysjobs a
INNER JOIN msdb.dbo....
You can use this query:
FROM msdb.dbo.sysalerts AS Ale
INNER JOIN msdb.dbo.sysnotifications AS Noti ON Ale.id = Noti.alert_id
INNER JOIN msdb.dbo.sysoperators AS Ope ON Noti.operator_id = ope.id
From here, the sql_severity column is the "severity of any SQL Server error."
When this column has the value 0, that means either no message was returned (and it's therefore a magic value), or a message with severity 0 was returned. I would argue this is a bad table design, as the column does not allow NULL.
It's almost the same thing with the ...
I tried to use the SP sp_delete_backuphistory but this filled my MSDB transaction log and almost killed the server...
Are there any other ways to clear this and shrink the file without causing any damage?
Do it in batches.
If your msdb is huge, then after running the script, to release unused space, I would recommend you to shrink your msdb (Yes ...
Who knows! Just add something like
and sja.start_execution_date >= DATEADD(dd, -1, GETDATE())
This is my current answer in my environment. This works for me as the greater script this is a part of runs on a daily basis. It does feel like a better answer or explanation should be available.
You could try just rebuilding the table:
ALTER TABLE dbo.sysjobstepslogs REBUILD;
If you're on Enterprise (or Evaluation or Developer) you can do:
ALTER TABLE dbo.sysjobstepslogs REBUILD WITH (ONLINE = ON);
The table only has a single clustered index (PK_SomeAwfulGUID-basedName) and this rebuild operation should deallocate all of the pages that might ...
While you can't control if the backup happens, could you ask the third party to change how the backup is taken? Using the COPY_ONLY option stops backups from interrupting other backup sequences, so if they add that to their process it will stop interfering with your's. See https://msdn.microsoft.com/en-gb/library/ms191495(v=sql.120).aspx for more info.
Violation of PRIMARY KEY constraint 'PK__backupse__21F79AAB0E391C95'. Cannot insert duplicate key in object 'dbo.backupset'. The duplicate key value is (60979).
This might happen if someone have done a reseed on the dbo.backupset.
From table definition :
PK__backupse* --> clustered, unique, primary key located on PRIMARY --> backup_set_id
To fix that ...
Here is a query I use to get job information. I have also used SQLJobVis in the past but their website appears to be down at the moment
with jobs as(
select a.name,a.[description], a.enabled, case c.freq_type -- Daily, weekly, Monthly
when 1 then 'Once'
when 4 then 'Daily'
when 8 then 'Wk ' -- ...
In general collations can be persuaded to match one another. See my comments at:
Contained DB Collation error
Perhaps the comments on collation, particulary the CATALOG_DEFAULT, may provide you some assistance:
The database collation is retained, but is only used as the default collation for user data.
A new keyword, CATALOG_DEFAULT, is available in the ...
This can be accomplished without giving the application nearly full control over SQL Agent jobs. Instead of creating the User in msdb, you create a Certificate in both the current DB and in msdb, and a few extra steps associated with the Certificate. The steps to do this are shown in the working example below:
Initial Setup and Test 1: