Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [msdb]

A system database in Microsoft SQL Server. Most commonly referenced with regard to backup history, SQL Server Agent configuration, Log Shipping, and other instance-level configuration.

From the official documentation:

The msdb database is used by SQL Server Agent for scheduling alerts and jobs and by other features such as SQL Server Management Studio, Service Broker and Database Mail.

48 questions
21
votes
7 answers

Script to see running jobs in SQL Server with Job Start Time

how to write script to see running jobs in SQL Server with Job Start Time? SELECT sj.name, sja.run_requested_date, CONVERT(VARCHAR(12), sja.stop_execution_date-sja.start_execution_date, 114) Duration FROM msdb.dbo.sysjobactivity sja INNER…
zerbug
  • 329
  • 1
  • 2
  • 4
10
votes
2 answers

What benefit is there from enabling Query Store on msdb?

Of the SQL system database (master, model, msdb, tempdb) query store can only be used on msdb. I looked and don't find any documentation about query store on msdb. While you can't see it in the GUI, it can be validated on your SQL 2016…
James Jenkins
  • 6,318
  • 6
  • 49
  • 88
9
votes
3 answers

Is it okay to delete MSDB?

I'm not a DBA, I've only googled what MSDB does it's basically a DB of SQL Agent of its Job and History, Now I'm running out of space in my cloud server and I have 1 year worth of MSDB year 2017, Is it okay to delete this or do I keep it for backup…
Terrence McGinnis
  • 101
  • 1
  • 3
7
votes
1 answer

Error in SSMS when attempting to restore a database

We have over a dozen SQL servers, one of which hosts a couple of hundred sage databases, when i right-click on the Databases node in SSMS there is a long delay then i get the following error (and the restore dialogue box doesn’t load): I presume…
HeavenCore
  • 786
  • 10
  • 26
7
votes
1 answer

sysschedules datatype choices

I am curious, why does SQL Server save the values in msdb.dbo.sysschedules for date and times as int instead of datetime. I assume the reason dates back to something in SQL Server 2000. Was it a storage capacity issue, performance, or other?
joe
  • 319
  • 2
  • 7
7
votes
1 answer

Database msdb stuck in recovery

I encountered a problem while testing recovering backups (Veritas BackUp Exec 2015, just for you to know). Main Problem is: I couldnt restore the databases - looking on the target Server where I want to restore the DBs, SSMS tells me, 'msdb'…
Tan Erdogan
  • 71
  • 1
  • 4
6
votes
3 answers

Collation conflict ... Could not use dbo.sysdac_instances

After a migration of databases to a new 2014 server with default collation Latin1_General_CI_AS, but where most of the databases have a collation of Latin1_General_BIN, an attempt to import an Excel spreadsheet is throwing the following error: I've…
YaHozna
  • 357
  • 1
  • 11
6
votes
2 answers

MSDB log file size

We have a SQL Server 2008 cluster with 60 databases. Full backup runs every night, log backup runs every 15 minutes. The MSDB database recovery model is set to simple (default). To be able to see the history of these jobs, we had to setup a job…
Lars
  • 61
  • 1
  • 2
5
votes
2 answers

Oprhaned entries in msdb..sysjobactivity

In this question: https://stackoverflow.com/questions/18445825/how-to-know-status-of-currently-running-jobs This answer was proposed: SELECT sj.name , sja.* FROM msdb.dbo.sysjobactivity AS sja INNER JOIN msdb.dbo.sysjobs AS sj ON sja.job_id =…
Paul
  • 1,453
  • 6
  • 19
  • 38
4
votes
1 answer

Huge MSDB database

I have a very large MSDB database and have been working on clearing this out and setting up maintenance tasks - something my predecessor should have done years ago! So far I've managed to truncate the sysmaintplan_logdetail and sysmaintplan_log…
U01SFA3
  • 53
  • 1
  • 5
4
votes
1 answer

Moving MSDB database

When I am trying to move msdb database from it's default location (C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA) to another drive(E:). After moving the database I am getting error as shown below.(the error I am getting while…
IT researcher
  • 3,168
  • 15
  • 59
  • 82
4
votes
1 answer

MSG 14262: The specified @server_name ('') does not exist

We have an SQL Server 2019 instance (let's call it 'sample_instance') with server collation of Turkish_CI_AS. When I try to create a job, SSMS creates the part of the script that adds the target server as following: EXEC msdb.dbo.sp_add_jobserver…
halid.sert
  • 87
  • 4
3
votes
1 answer

"Could not insert a backup or restore history/detail record in the msdb database" While restoring msdb

Today I found msdb database was in suspected mode, so I restored MSDB from my latest backup(As I have daily backup for this database). When I restore the MSDB database using Tsql i got below errors, but database restore was successful. Processed…
IT researcher
  • 3,168
  • 15
  • 59
  • 82
3
votes
1 answer

How do I reclaim reserved space in the sysjobstepslogs table

I have a 36GB msdb database in my SQL Server 2008 R2 database server. From what I have read this is past the point of being gigantic. When I look at the tables in the msdb database the sysjobstepslogs table is using 97% of the space in the…
Chuck Herrington
  • 191
  • 1
  • 8
3
votes
3 answers

Getting the information that exists in "Job properties -> Notifications" page?

I use SQL Server 2008 R2 and I need get some properties from the msdb database. I need a query to get information that exists in the Job properties -> Notifications page. In which table I can find this information?
Ardalan Shahgholi
  • 457
  • 2
  • 8
  • 20
1
2 3 4