Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Thursday, August 11, 2016 12:32 AM
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.
I tried DBCC CHECKDB (msdb) WITH ALL_ERRORMSGS, NO_INFOMSGS
and raised error
Msg 945, Level 14, State 2, Line 1
Database 'msdb' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
Please suggest me how we can rectify this
Thursday, August 11, 2016 4:48 AM ✅Answered
See articles:
How to Fix a Corrupt MSDB SQL Server Database
How to repair corrupted msdb database in Microsoft SQL server 2008 R2
Kalman Toth Database & OLAP/AI Architect SQL Server 2016 Database Design
New Book : Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016
Thursday, August 11, 2016 12:34 AM
1) If possible add more hard drive space either by removing of unnecessary files from hard drive or add new hard drive with larger size.
2) Check if the database is set to Autogrow on.
3) Check if the account which is trying to access the database has enough permission to perform operation.
4) Make sure that .mdf and .ldf file are not marked as read only on operating system file system level
Please click Mark As Answer if my post helped.
Thursday, August 11, 2016 12:45 AM
"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.
"
The message is very clear, we need to do DBCC CHECKDB, if you are able to open query window please do DBCC CHECKDB for MSDB DB, as some transactions are still not properly committed "incorrect checksum (expected: 0x2411d175; actual: 0x2411dfd1). It occurred during a read of page (2:0) in database ID 4 at offset 0000000000000000 ". and do with recover option like REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD.
More details on below link: https://msdn.microsoft.com/en-us/library/ms176064.aspx?f=255&MSPPError=-2147217396
If this doesn't work out then please check latest backup of MSDB and try restoring it.
Santosh Singh
Thursday, August 11, 2016 1:08 AM
Dear Santosh,
DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS has done. But that also raised error.
Thursday, August 11, 2016 4:38 AM
It’s looks like It’s an disk memory issue, So check space first. For more information: http://www.blog.sqlrecoverytool.com/fix-sql-error-945.html
Thursday, August 11, 2016 7:30 AM
Error: 824, Severity: 24, State: 2.
>>https://support.microsoft.com/en-in/kb/2015756
Error: 5105, Severity: 16, State: 1.
File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\MSDBLog.ldf" may be incorrect.
>>Verify you have right accesses for your sql service account on the DATA folder & also verify the right path too here for the MSDBLog.ldf.
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.
>>This might be your device i/o issues,try to see if you can reboot if no impact & then see.
if this does not works then ensure you have the valid latest backups that can be restore(note that restoring of msdb based on your latest backup so the changes after made might not be appear here).
The other soultions you can try as other said.
Thursday, August 11, 2016 1:45 PM
Do you see some recovery % update in SQL Server log and if yes what is that?
It seems DB is not in accessible state as you have mentioned there are 80 GB free space and service A/C has full access on the folder, if not please double check or assign one more time. Some time these are being changed unknowingly too.
Since DB has some transaction still not committed so it is saying with location too.
Try restoring with latest backup where you could have all jobs and configurations intact with some query window with force and see.
You can also even try rebuilding MSDB but that would be required if backup\restore fails.
With SQL Server 2012, even you can fix such errors in log files.
Please share how it goes.
Santosh Singh
Friday, August 12, 2016 3:18 AM
Thanks a lot Kalman Toth.
Since my msdb file was corrupted, I have to rebuild MSDB database and fortunately there was no jobs or alerts etc.... I tried the steps from following link and others were also informative.
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.
Wednesday, March 21, 2018 5:30 PM
Thanks a lot...
It's works for me as well.
"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"
Finally I have recovered the MSDB database.
Once again thanks for your help!
Regards
Venkat S.P.