Database 'msdb' cannot be opened due to inaccessible files or insufficient memory or disk space

Johnson T A picture Johnson T A · Aug 11, 2016 · Viewed 7.6k times · Source

I am facing this issue at one of my clients system. When we are connecting to to SQL server 2008 DB engine through SSMS, I am getting the error

Database 'msdb' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details. (Microsoft SQL Server, Error: 945)

Then also connects to Database. But clients databases are not visible. If I tried to expand 'System Database' or 'Security' or 'Management' again I am getting this error.

Hard disk has 80 gb free space, account has permissions, .mdf and .ldf files are not read only

In SQL Server configuration manager -> SQL Server properties -> Log on as -> selected 'This Account' and using account name 'admin' and its password.

They are able to do data operation through applications from other PCs. Issue is only on accessing from SSMS.

In error log we can see

2016-08-10 18:02:56.17 spid13s     Error: 824, Severity: 24, State: 2.
2016-08-10 18:02:56.17 spid13s     SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x2411d175; actual: 0x2411dfd1). It occurred during a read of page (2:0) in database ID 4 at offset 0000000000000000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\MSDBLog.ldf'.  Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
2016-08-10 18:02:56.17 spid13s     Error: 5105, Severity: 16, State: 1.
2016-08-10 18:02:56.17 spid13s     A file activation error occurred. The physical file name 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\MSDBLog.ldf' may be incorrect. Diagnose and correct additional errors, and retry the operation.
2016-08-10 18:02:56.17 spid13s     File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\MSDBLog.ldf" may be incorrect.
2016-08-10 18:02:56.17 spid13s     The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.
2016-08-10 18:02:56.17 spid36s     Error: 945, Severity: 14, State: 2.
2016-08-10 18:02:56.17 spid36s     Database 'msdb' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.
2016-08-10 18:02:56.17 spid36s     Problems recording information in the msdb..suspect_pages table were encountered. This error does not interfere with any activity except maintenance of the suspect_pages table. Check the error log for more information.

Please suggest me how we can rectify this

Answer

Johnson T A picture Johnson T A · Aug 12, 2016

Since my msdb file was corrupted and was not having system DB backups, I have to rebuild MSDB database and fortunately there was no jobs or alerts etc.... I tried the steps from following link Rebuild System Databases

Only one change at execution plan was at 6th step in Rebuild MSDB from instmsdb.sql. Since an error occurred, I stopped the SQL Server service at Command Prompt and started from Configuration Manager. Then opened SSMS , Connected to DB engine (I got an error, Clicked ok for that), then, opened the instmsdb.sql from C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Install\instmsdb.sql and executed.