question

johnstaj avatar image
johnstaj asked

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

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
system-databasesmsdbsql_
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

looks like you have a corrupt msdb database. you can run dbcc checkdb on it as suggested. do you have a good backup from which you can restore?
2 Likes 2 ·

1 Answer

· Write an Answer
johnstaj avatar image
johnstaj answered
Hi Since my msdb file was corrupted and there was no backup of system DBs, 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][1] 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. Thanks a lot Johnson [1]: https://msdn.microsoft.com/en-us/library/dd207003(v=sql.105).aspx
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fortunate you were that there were no jobs or alerts. Start backing up the database starting now!
4 Likes 4 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.