The target database (‘JBDB’) is in an availability group and is currently accessible for connections when the application intent is set to read only

-> When trying to open a new query window for a secondary database that is part of Availability group using SQL Server Management Studio, I got below error,

TITLE: Connect to Database Engine
ADDITIONAL INFORMATION:
The target database (‘JBDB’) is in an availability group and is currently accessible for connections when the application intent is set to read only. For more information about application intent, see SQL Server Books Online. (Microsoft SQL Server, Error: 978)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=978&LinkId=20476

-> I just configured Read-Routing list on the Primary database that is part of Always On availability group. More details on Read-Routing list can be found here.

-> Below are options that can be leveraged when configuring Read-Only Access on an Availability group,

No
No user connections are allowed to secondary databases of this replica. They are not available for read access. This is the default setting.

Read-intent only
Only read-only connections are allowed to secondary databases of this replica. The secondary database(s) are all available for read access.

Yes
All connections are allowed to secondary databases of this replica, but only for read access. The secondary database(s) are all available for read access.

-> More details of these option are explained in this blog post with more clarity.

-> The error that we are experiencing is due to the fact that the Availability group is configured with “Read-intent only” option.

-> “Read-intent only” means that the Secondary Replica only accepts connections that are explicitly configured for that purpose and the problem occurs when you are trying to connect to an AG Secondary Replica database configured for Read-Intent only without explicitly using the proper parameter.

-> It is pretty clear that we are not able to make a connection to database JBDB on Server JBSAG2 directly. Let us try a connection using ApplicationIntent=ReadOnly and it should connect just fine,

-> If you don’t want to use this switch and want to directly connect to the secondary database to execute Read-Only Queries using SSMS or other application, then configuring Readabale Secondary to “Yes” instead of “Read-intent only” on Availability group properties as below will help,

Hope this helps!!!

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Power BI – The key didn’t match any rows in the table

-> I was working on an existing Power BI desktop file and was utilizing a different SQL Server database than the one on which this Power BI file was created.

-> During the initial load I got below error,

The key didn’t match any rows in the table.

-> With this error, I clicked on “Transform Data” and check for this particular object,

Expression.Error: The key didn’t match any rows in the table.
Details:
Key=
Schema=dbo
Item=tbl_DisabledIndexes
Table=[Table]

-> I happened to check if the object dbo.Tbl_DisabledIndexes was present in the underlying database,

-> It seems like the object dbo.Tbl_DisabledIndexes is not present on the database. This Power BI Report when created based on a different database should have contained this table, but the current database doesn’t have it.

-> I corrected that by creating the table dbo.Tbl_DisabledIndexes and populated the appropriate rows into that SQL Server table.

-> Once the table was available, I reloaded the report and it worked as expected this time.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Database Corruption – Repairing a SQL Server database without LDF or Log file

-> I tried attaching a MDF file without LDF file using below command and it worked fine,

-> The database JBSWiki looks fine.

-> I tried executing below query and got an error,

select * from Table1

Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x78812100; actual: 0x7881211c). It occurred during a read of page (1:320) in database ID 6 at offset 0x00000000280000 in file ‘c:\temp\JBSwiki.mdf’. Additional messages in the SQL Server error log or operating system error 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.

-> It is clear from the message that the database is corrupted. Let me execute a DBCC Checkdb and validate the results,

use [master]
GO
dbcc checkdb ('JBSWiki')

Msg 8939, Level 16, State 98, Line 3
Table error: Object ID 677577452, index ID 1, partition ID 72057594043760640, alloc unit ID 72057594050248704 (type In-row data), page (1:320). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 133129 and -4.


Msg 8928, Level 16, State 1, Line 3
Object ID 677577452, index ID 1, partition ID 72057594043760640, alloc unit ID 72057594050248704 (type In-row data): Page (1:320) could not be processed. See other errors for details.
Msg 8980, Level 16, State 1, Line 3


Table error: Object ID 677577452, index ID 1, partition ID 72057594043760640, alloc unit ID 72057594050248704 (type In-row data). Index node page (0:0), slot 0 refers to child page (1:320) and previous child (0:0), but they were not encountered.

CHECKDB found 0 allocation errors and 3 consistency errors in database ‘JBSWiki’.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (JBSWiki).

-> From the error message we can see that the corruption is on Object id 677577452 and the minimum repair level for correcting this issue using DBCC checkdb is repair_allow_data_loss. This means there will be data loss.

-> Lets check what object id 677577452 is using below query,

use [JBSWiki]
GO
select object_name(677577452 ) as 'Corrupted Table Name'

-> So we know that there is an issue on Table1 object and from the DBCC Checkdb results we can see that page 320 in File id 1 is experiencing issue. Data loss is not an option in this case.

-> The only option left is to restore the database with a valid backup. But the bad news is that there are no backups.

-> SQL Server Database recovery tools is something we can check as an option. In my case, I tried “Stellar Repair for MS SQL“.

-> Before using Stellar Repair for MS SQL tool, it is better to backup the MDF and LDF file for safety.

-> Once done make another copy of the files to use it with Stellar Repair for MS SQL tool. In my case I have placed the file in location F:\Debug,

-> Open “Stellar Repair for MS SQL” and click on “Select database”,

-> Select the corrupted MDF file and click on Repair,

-> Repair completed,

-> Once repaired, Click on Save,

-> Processing this new database is going to take time depending on the database size. The time spent is definitely worth it, as I was able to recover the whole database.

-> Once the database is recovered. Do thorough testing and make sure the database looks alright.

-> I executed DBCC CHECKDB on the new database recovered by Stellar Repair for MS SQL and it came out clean.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.