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.
48 questions
0
votes
2
answers
113
views
script to change the schedule of a job, it needs to find the schedule associated with the job
using Ola's backup strategy,
there is a job
databaseBackup - system databases full, as you can see on the picture below.
this job is scheduled to run once a week, on a sunday morning.
that is all good,...
- 16.6k
1
vote
1
answer
98
views
sql server query to show the running jobs - what about the steps of the job?
I have been working on a query to show me the running jobs.
I got it right.
the script is below (enjoy):
DECLARE @JOB_NAME SYSNAME=NULL
SELECT job.name
,job.job_id
,...
- 16.6k
0
votes
2
answers
56
views
how to prevent the diff backup running on days when the full backup is scheduled to run?
I have an environment that we want a full backup once a week (if we fail we can tolerate up to 2 weeks) but the diffs have to be running every day, every day but not the days when the full is ...
- 16.6k
4
votes
1
answer
364
views
MSG 14262: The specified @server_name ('<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 ...
- 87
-3
votes
1
answer
154
views
Restore of MSDB fails with different version error but the versions are the same [closed]
Trying to restore MSDB to a replacement server and getting this message:
Msg 3168, Level 16, State 1, Line 4
The backup of the system database on the device e:\sqlbackup\2024-02-27_msdb_DEV.bak ...
- 906
1
vote
2
answers
112
views
last backup - how many backup files and where they are?
I am restoring a database, this database is very big and it has 6 backup files (meaning the backup is split into 6 smaller files)
I can see an indication of that using the restore labelonly
...
- 16.6k
0
votes
0
answers
971
views
Transaction aborted when accessing versioned row in table 'dbo.sysmail_profile' in database 'msdb'
I'm having some problens with SQL Database mail, basicaly anything that I try to do gives me an error like this one below:
Transaction aborted when accessing versioned row in table 'dbo....
2
votes
1
answer
206
views
How to trim the log file that stores information about job history execution?
I was having a look at my sql server jobs and proxies on an specific server in the production environment.
I pretty much have an script to check the status of the jobs but in case I have to ...
- 16.6k
0
votes
1
answer
126
views
Recovery_model for backupset table null sqlserver
Going through https://www.sqlshack.com/understanding-database-recovery-models/
I see that there are three types of recovery models:
SIMPLE
FULL
BULK_Logged
But for the table msdb.dbo.backupset I can ...
- 99
2
votes
1
answer
453
views
Effect on msdb transaction log of running sp_delete_backuphistory for first time?
Have a server with regular db backups but strangely sp_delete_backuphistory has never been scheduled so msdb is bigger than expected. Backup history goes back 5 years in that table but only needs ...
- 1,127
1
vote
1
answer
807
views
sp_delete_backuphistory not working
I am using sql Server 2008 R2.
We are using below query for reducing the msdb size.
use msdb exec sp_delete_backuphistory '01-MAR-2020'
When i execute this query in server whose msdb size is 15GB ...
- 3,131
0
votes
1
answer
61
views
Analyst MSDB Access SQL Server 2017
i'm an accidental DBA for a company and having trouble figuring out this permissions situation.
We have a new 2017 SQL server that I would like to get our report writer / analysts access to a few of ...
2
votes
1
answer
389
views
SQL Server Installer appears to be resetting MSDB Recovery Mode to Simple
Everytime we upgrade a SQL Server installation (service pack or cumulative update), msdb is changed from FULL recovery mode to SIMPLE recovery mode.
I understand that the msdb database doesn't change ...
- 123
1
vote
1
answer
335
views
Why MSDB transaction log file grew while MSDB was in Simple recovery mode?
I have Availability Group of 3 servers, and transaction log backups for databases in Availability Group are taken on a secondary replica, each 1 minute
It was working like this for several months
...
- 5,964
-2
votes
2
answers
1k
views
remove a step from sql agent jobs
I copied a bunch of jobs from an HA server to a regular server and I need to delete only the first step( checks for the primary) on these jobs not all jobs. Any suggestion?
this is the query I used to ...
- 43
1
vote
1
answer
5k
views
Is it safe to stop the shrinking of MSDB?
In a production server (Microsoft SQL Server 2014), the file MSDBData.mdf ballooned up to more than 450 GB, eating almost all the space in the server (only 38 GB remaining).
First thing, I created ...
- 13
0
votes
1
answer
211
views
upgrade MSDB only?
good morning.
we currently are running on SQL server 2008 r2 and going to Sql server 2016. we are told we cannot do an in place upgrade and will have to restore all SSIS and jobs manually (script, ...
- 1
2
votes
1
answer
1k
views
What is the best practice for the msdb database owner?
In the course of reading up on SQL Server security best practices, I learned that databases should generally be owned by a dedicated, low-privileged login (sometimes a separate one for each DB or each ...
- 201
0
votes
1
answer
371
views
Why does msdb.dbo.backupset have entries for each database every 15 minutes?
I have 134 separate databases on a SQL Server.
Every 15 minutes an entry appears in the msdb.dbo.backupset table for each database on the server.
Why does this happen every 15 minutes?
What is ...
0
votes
1
answer
214
views
What does "_localserver_view" mean after an MSDB Database table
What is equal to the database table
[msdb]. [dbo]. [sysschedules_localserver_view], which contains the suffix _localserver_view?
I am having a permissions problem with a [msdb].[Dbo].[...
0
votes
1
answer
472
views
SQL AlwaysON. keeping Agent Job in sync, but with same JobID?
Is there a way to specify Job ID when creating a job? Or is there a way to update the Job ID of a specific Agent Job that already exists?
I have a software from a vendor that uses the Job ID to ...
9
votes
3
answers
3k
views
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 ...
10
votes
2
answers
690
views
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, ...
- 6,318
3
votes
1
answer
4k
views
Log Shipping: Bulk Remove Alerts of already deleted primary and secondary databases
I'm facing a rather strange issue with Transaction Log Shipping reports in SSMS which is showing multiple red alerts of databases that have been created and deleted while performing multiple Log ...
- 33
2
votes
3
answers
8k
views
Grant a database role in current database with permission to execute a SP in msdb
I have created a role in my current SQL Server 2012 database with db_datareader, db_datawriter, and execute permissions. A procedure in the current db calls sp_start_job in msdb.
How can I grant ...
- 35
3
votes
2
answers
48k
views
T-SQL query for date of last full backup, size and location
I'm using the below T-SQL query to determine the date of the last full database backup and also return the size and location of the backup file. My problem is that it won't return any data at all for ...
- 652
6
votes
3
answers
2k
views
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 ...
- 357
1
vote
0
answers
373
views
Database Restore info not logged to msdb..restorehistory
I have 4 databases on a secondary server two of which are using Sql Server log shipping and two of which are using a "manual log shipping" config (there are just 2 primary databases).
Both ...
- 141
7
votes
1
answer
8k
views
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 ...
- 71
6
votes
2
answers
6k
views
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 ...
- 61
1
vote
1
answer
2k
views
Moving MSDB database files fails
I'm trying to relocate system databases files.
I managed to do that with the tempdb database, but I'm stuck with msdb.
When I run:
SELECT name, physical_name AS current_file_location
FROM sys....
- 113
2
votes
1
answer
5k
views
How can I check what jobs are scheduled to be run at a specific time?
How can I check what jobs are scheduled to be run at a specific time?
I am trying to work on something,
use msdb
go
Select [job name] = jb.name
, [schedule name] = sc.name
, [next run ...
- 16.6k
1
vote
1
answer
91
views
Can a SQL Server 2005 msdb database be restored as a User database?
Can a SQL Server 2005 msdb database be restored as a User database on another instance (same Build)?
"Why?" you ask: I need a list of the mail items that existed prior to installation of two service ...
- 579
1
vote
1
answer
224
views
MS SQL Server Incremental(differential) backup interrupted due to Full backup
I have a manual full backup happening every night, post which I am taking differential backup after every hour but after three to four differential backups a full backup happened through third party ...
- 512
3
votes
1
answer
4k
views
"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, ...
- 3,131
2
votes
2
answers
27k
views
Problem sending an attachment using dbo.sp_send_dbmail
I am trying to send an attachment using dbo.sp_send_dbmail.
I can't do this
I get the error
Attachment file \VMName\ListTEAM\LCPP_CP.pdf is invalid.
Just some quick things that I have checked.
I ...
4
votes
1
answer
5k
views
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 ...
- 53
5
votes
2
answers
10k
views
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
...
- 1,463
21
votes
7
answers
190k
views
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) ...
- 329
3
votes
1
answer
2k
views
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 ...
- 191
3
votes
3
answers
4k
views
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 ...
2
votes
1
answer
1k
views
How can I join msdb.dbo.sysalerts to msdb.dbo.sysoperators?
I am using SQL Server 2008 R2 and I want to create a script of my jobs.
I need to find all objects that exist in one job.
To achieve this goal, I get all operators with this query:
SELECT * FROM ...
3
votes
2
answers
1k
views
sysjobhistory "sql_severity" codes
Where can I find out what the different "sql_severity" codes mean in the sysjobhistory table?
Eg what does 0 mean?
I've inherited a report that excludes ones with a 0 as "0 is not a real error"?
I ...
- 215
4
votes
1
answer
7k
views
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 ...
- 3,131
0
votes
3
answers
1k
views
Fixing a query that returns failed job information
I' using a SQL Server 2008 Sp3 environment.
I have a query that returns information about failed jobs from the last 10 minutes.
If a job has two schedulers (for example - a job that runs every 10 ...
- 575
3
votes
1
answer
29k
views
Database 'msdb' cannot be opened. it has been marked suspect by recovery
I'm using SQL Server 2008. I got suddenly today an error while i was accessing a database from Visual Studio. The error is:
Database 'msdb' cannot be opened. It has been marked SUSPECT by recovery. ...
- 133
7
votes
1
answer
532
views
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 ...
- 786
7
votes
1
answer
144
views
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 ...
- 319