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.

The operation cannot be performed since the database ‘JBDB’ is in a replication relationship. (Microsoft SQL Server, Error: 40680)

-> I tried renaming an Azure SQL database JBDB and got below error,

TITLE: Microsoft SQL Server Management Studio
Unable to rename JBDB_old. (ObjectExplorer)

ADDITIONAL INFORMATION:
Rename failed for Database ‘JBDB’. (Microsoft.SqlServer.Smo)
For help, …..

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The operation cannot be performed since the database ‘JBDB’ is in a replication relationship. (Microsoft SQL Server, Error: 40680)

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

-> Error explains that this database is in a replication relationship. Let’s check if it is part of failover group,

-> Database JBDB is part of failover group. Lets remove this database from failover group and try renaming it.

-> Once removed from failover group. Open Azure SQL Database and click on database JBDB. Open “Geo-Replication” and stop it for JBDB database.

-> Try renaming the database and it will complete 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.