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.
42
questions
0
votes
0
answers
407
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
170
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 ...
- 15.8k
0
votes
1
answer
99
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
337
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,087
1
vote
1
answer
674
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
40
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
271
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
253
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,452
-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 ...
- 33
1
vote
1
answer
3k
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
195
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
857
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 ...
- 171
0
votes
1
answer
346
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
179
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
390
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 ...
9
votes
2
answers
617
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,178
3
votes
1
answer
3k
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
7k
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
42k
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 ...
- 347
1
vote
0
answers
336
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
4k
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 ...
- 15.8k
1
vote
1
answer
89
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
189
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
26k
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
4k
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
9k
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,453
21
votes
7
answers
183k
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
980
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
28k
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
521
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
143
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