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.

Start and Stop Azure Virtual Machine

-> Requirement is to start and stop Azure Virtual Machine as per given schedule. I will be using “Tasks (preview)” within Azure Virtual machine to perform this. This feature utilizes Logic Apps behind the scene to accomplish the tasks.

Stop Virtual Machine using option “Tasks (preview)” – Option 1

-> From Azure portal, open the required virtual machine.

-> Select “Tasks (preview)” under “Automation” and click “Add”,

-> Select “Power off Virtual Machine” and click on “Next : Authentication”,

-> Click on Create and Sign in with the required credentials,

-> You will see below screen once signed in,

-> I have provided a schedule in such a way that the Virtual Machine will be stopped every day at 9:00 PM.

-> Click Create.

Stop Virtual Machine using option “Auto-shutdown” – Option 2

-> From Azure portal, open the required virtual machine.

-> Select “Auto-shutdown” under “Operations”. Click on Save once appropriate details are provided as in below screenshot,

-> You can use either Option 1 or 2 as defined above to automatically stop your Azure Virtual Machine.

Start Virtual Machine using option “Tasks (preview)”

-> From Azure portal, open the required virtual machine.

This image has an empty alt attribute; its file name is image-17-1024x250.png

-> Select “Tasks (preview)” under “Automation” and click “Add”,

-> Select “Start Virtual Machine” and click on “Next : Authentication”.

-> Connections are all connected already, you may have to create it if this is done for the first time,

-> I have created a schedule in such a way that the virtual machine will be started on every weekday from Monday to Friday at 8:45 AM.

-> Lets try opening “Jbswiki-Startup” in Logic Apps and check,

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.