SQL Server–Error–“Database ‘msdb’ cannot be opened due to inaccessible files or insufficient memory or disk space”

Background

Upon starting SQL Server, we received the error posted below.

Error

Image

msdb110_upgrade_20171003_1115PM (cropped up)

Textual

Msg 945

Database ‘msdb’ cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details

Troubleshooting & Remediation

Outline

  1. Preparation
    • Make a note of Service Accounts used for SQL Server Engine and SQL Server Agent
    • Identify Folder’s location and filenames for msdb data and log files
    • Identify location of msdb install files
      • instmsdb.sql
  2. Inspect
    • Review Accounts’ access
      • Review Accounts NTFS Permissions on msdb data and log files
  3. Get out the way
    • Stop MS SQL Server Engine and Agent services
  4. Get Set
    • Safe keep msdb files
      • Copy msdb data and log files to a neutral location
  5. Go!
    • Start MS SQL Server using trace files
      • Trace Flags
        • -T3608
    • Detach msdb database
      • Using SQLCMD or Management Studio, issue sp_detach_db ‘msdb’
    • Rename existing msdb data and log files
    • Create msdb database
  6. Rollout
    • Stop SQL Server
    • Remove SQL Server Trace Flags
    • Retrace Trace Flags
      • -T3608
  7. Restart SQL Server Engine
  8. Validate
    • Access msdb database

Actual Steps

Preparation

  • Make a note of Service Accounts used for SQL Server Engine and SQL Server Agent
  • Identify Folder’s location and filenames for msdb data and log files
  • Identify location of msdb install files
    • instmsdb.sql

Make Note of SQL Service Accounts

SQL Server Configuration Manager
Image

Explanation
  1. Services
    • SQL Server
      • Local System
    • SQL Server Agent
      • [domain]\svc_sql

 

Identify Folder’s location and filenames for msdb data and log files

Identify the folder where system databases ( master, model, and msdb ) are stored.

Dynamic Management Views

If SQL Server is running in normal mode or within trace flags ( -T 3605 ), please get folders and names of msdb files.

SQL
select 

          [dbid] 
            = tblSMF.database_id
            
        , [database] 
            = db_name(tblSMF.database_id)
            
        , [fileID] 
            = tblSMF.file_id            
            
        , [fileSymbolicName]
            = tblSMF.[name]
        
        , [filePhysicalName]
            = tblSMF.[physical_name]
            
        --, tblSMF.*            
        
from   sys.master_files tblSMF

where  tblSMF.[database_id] <= 4

order by
        tblSMF.database_id asc
        

Image

Explanation
  1. Database
    • msdb
      • MSDBData & MSDBLog noted

 

SQL Server Configuration Manager
Image

Explanation
  1. Startup Parameter
    • -d
      • -dE:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf

 

Identify location of msdb install files

Syntax

[InstallDriveLetter]:\Program Files\Microsoft SQL Server[Version].[InstanceName]\MSSQL\Install

Sample

C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Install

 

Inspect

Review Accounts’ access

Review Accounts NTFS Permissions on msdb data and log files
Outline

Here are the steps to take to read out the “Effective” permissions

  1. To view effective permissions on files and folders
    1. Open Windows Explorer, and then locate the file or folder for which you want to view effective permissions.
    2. Right-click the file or folder, click Properties, and then click the Security tab.
    3. Click Advanced, click the Effective Permissions tab, and then click Select button.
      • In the Select Window
        • Enter the name of a user or group, and then click OK.
    4. The selected check boxes indicate the effective permissions of the user or group for that file or folder.
Image

Get Set…

Safe keep msdb files

Copy msdb data and log files to a neutral location

Create a safe location and make copies of files to place in Safe Location.

Image

Go!

Start MS SQL Server using trace files

Trace Flags
Trace Flags Outline
  • Trace Flags
    • -T3608

 

Trace Flag ID What does it do Applicable Effective
3608 It will not start and recover any database except the MASTER system database. So none of the system databases; msdb, tempdb, model, etc will start or recover if this trace flag is used while starting SQL Server. Yes Yes
 902  Trace Flag 902 will bypass the internal upgrade script on startup No  N/A
Trace Flags Steps
SQL Server Configuration Manager
Add(ing) Trace Flag :- -T3608

Add(ed) Trace Flag :- -T3608

 

Start MS SQL Server
SQL Server Configuration Manager

 

Detach msdb
SQL

exec sp_detach_db 'msdb'

 

Rename MSDB Data & Log Files
Image

Create msdb
Command Line

sqlcmd -S . -i instmsdb.sql

SSMS
SSMS – Output

Rollout

Outline

  1. Stop SQL Server
  2. Remove SQL Server Trace Flags
  3. Retrace Trace Flags
    • -T3608

Stop MS SQL Server

Image

Trace Flags
Trace Flags Remove

remove Trace Flags

Trace Flags Remove – removed

Summary

There is a lot of very empty spaces here.

So much was left out to make this post concise and address them during more specific subject materials.

 

References

  1. SQLServerCentral.com
    • mssqlfun
      • How to rebuild MSDB database?
        Link
  2. Microsoft
    • Microsoft \ TechNet \ Windows Server
      • Access Control > Managing Permissions > Set, View, Change, or Remove Permissions on an Object
        Link
    • msdn
      • Sql Server 2012 – Cannot recover the master database
        Link
  3. Trace Flags
    • 3608
      • Paul Randal
        • How to recreate the msdb database in SQL Server 2005?
          Link
    •  902
      • Patrick Keisler, MSFT – Premier Field Engineer
        • Use Trace Flag 902 to Recover from a Cumulative Update Failure
          Posted on :- 2017-July-7th
          Link

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s