Creating a Deadlock

Previously we have looked at how to capture a deadlock, but how do we go about creating a deadlock?

I think the following is the simplest method:

1. Create two tables with a single row in each and update a row in the first table (note transaction is started with BEGIN TRAN, but not committed.)

USE [tempdb]
IF EXISTS(SELECT * FROM sys.tables WHERE [Name] = 'J1')
DROP TABLE [J1];
IF EXISTS(SELECT * FROM sys.tables WHERE [Name] = 'J2')
DROP TABLE [J2];
CREATE TABLE [J1]([ID] INT);
CREATE TABLE [J2]([ID] INT);
INSERT INTO [J1]VALUES (1);
INSERT INTO [J2]VALUES(1);
GO
BEGIN TRAN
UPDATE [J1] SET [ID] = 1
--UPDATE [J2] SET [ID] = 1

2. Open a query in a new window and run:

USE [tempdb]
BEGIN TRAN
UPDATE [J2] SET [ID] = 1
UPDATE [J1] SET [ID] = 1

3. Finally in the query window opened in step 1. run the commented code by highlighting everything after the comment marks then pressing F5.

--UPDATE [J2] SET [ID] = 1

This will result in our first window being the victim of a deadlock:

Msg 1205, Level 13, State 45, Line 13
Transaction (Process ID 53) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Note that the second query will have an open transaction, which you can either ROLLBACK or COMMIT.

In step 1 (spid 53) a lock was taken on the row of table J1
In step 2 (spid 54) a lock was taken on the row in table J2, and then it tries to take a lock on J1 but has to wait as the first query already has it locked.
In step 3 (spid 53) the first query attempts to lock the row that the second query has already locked, while holding a lock on the row that the second query wants.

ExampleDeadlock.xdl save this file to your desktop stripping off the .txt extension to leave .xdl which will allow opening in SSMS.

Samsung Gear VR2

Samsung VR for S6 Edge Plus in the UK is here

***** https://shop.samsung.com/uk/samsung-gear-vr-lite-286321/ *****

Update 2 new link
http://www.samsung.com/uk/consumer/mobile-devices/wearables/gear/SM-R322NZWABTU

Original Moan below:

There may be a few of us geeks in the UK looking for the latest VR offering from Samsung, which appears to be pre-releasing everywhere but here.

vrfocus are running updates as and when they occur. Their post linking to Amazon UK looks to be what we are waiting for although annoyingly no product details. http://www.amazon.co.uk/gp/product/B017WA5VI6

From what I can gather it the newer VR units are referenced as SM-R322NZWAxxx and then a 3 character country code. (The older headsets are SM-32x). Therefore the reference SM-R322NZWABTU looks good for the UK based on BTU being used before. I am not holding my breath for a release this year tho. Amazon quoting 1-3 Months… ho ho ho. Not exactly the best product launch from Samsung…

The new headset is “Compatible with Galaxy Note 5, S6 edge+, S6 and S6 edge.”

Note that the older headsets are prefixed as follows:
Gear VR Innovator Edition for S6 SM-R321.
Gear VR Innovator Edition for Samsung’s Galaxy Note 4 SM-R320.

Links to vrfocus articles:
http://vrfocus.com/archives/25783/amazon-launch-uk-gear-vr-pre-orders/
http://vrfocus.com/archives/25467/gear-vr-now-listed-australia-new-zealand-sweden-korea/

Links to Samsung:

Sweden
http://www.samsung.com/se/consumer/mobile-devices/wearables/gear/SM-R322NZWANEE

Germany
http://www.samsung.com/de/consumer/mobile-devices/wearables/gear/SM-R322NZWADBT

New Zealand
http://www.samsung.com/nz/consumer/mobile-devices/wearables/gear/SM-R322NZWAXNZ

Australia
http://www.samsung.com/au/consumer/mobile-phone/wearables/gear/SM-R322NZWAXSA

South Korea
http://www.samsung.com/sec/consumer/mobile-tablet/gear/gear-series/SM-R322NZWAKOO

US
http://www.samsung.com/us/mobile/wearable-tech/SM-R322NZWAXAR

Bits, Bytes and the Binary Decimal confusion

Quick Binary Bit background

In computing we use a unit of information with two values 0 or 1, which is called a bit. Now with either a 0 or 1, how do we go about representing the number 6 or 9? We could write 111111 and 111111111111, but you will see that this will require a lot of space to show larger number. If we use Binary (base-2), then the position of the 0/1 will relate to a corresponding value which in binary are 1,2,4,8,16,32,64,128 etc. Note that this is 2 to the power n where n is the position of the value starting from 0. The positions would be read from right to left, so Pos 7, Pos 6, Pos 5,Pos 4, Pos 3, Pos 2, Pos 1 and finally Pos 0. Perhaps therefore the decimal number values would be more easily understood if written as 128,64,32,16,8,4,2,1.

2 to Power 0 = 1
2 to Power 1 = 2
2 to Power 2 = 4 (2×2)
2 to Power 3 = 8 (2x2x2)
2 to Power 4 = 16(2x2x2x2)
2 to Power 5 = 32(2x2x2x2x2)
2 to Power 6 = 64(2x2x2x2x2x2)
2 to Power 7 = 128(2x2x2x2x2x2x2)

The number 6 is therefore represented by setting 1 in position 1, and position 2. Which would look like this 0 0 0 0 0 1 1 0. (Position zero is set to 0 so even number)
The number 9 is therefore represented by setting 1 in position 0, and position 3. Which would look like this 0 0 0 0 1 0 0 1. (Position zero is set to 1 so odd number)

Time for a quick Byte?

Now I have not chosen to show 8 bit positions randomly, as this group of 8 is referred to as a Byte. Where this gets complicated is that from this point on there are two ways to group the data units, which are either binary (base-2) or decimal (base-10).

As numbers get larger we assign a prefix denoting that it is to be multiplied by X to get the actual value. In decimal (base-10) a prefix is added for multiplies of 1000, in binary (base-2) however this is multiples of 1024.

So how much is a kilo byte? Well do we mean a binary or decimal?

1024 bytes = 1 binary kilo byte
1000 bytes = 1 decimal kilo byte

Confused? The easiest way to relieve this confusion is to use an appropriate prefix which denotes this difference. A multiple of 1000 in decimal is referred as k or kilo, where as in binary a multiple of 1024 is stated as Ki or kibi.

A more comprehensive list can be found online with a quick search but below is the first few. https://en.wikipedia.org/wiki/Binary_prefix

k kilo Ki kibi
M mega Mi mebi
G giga Gi gibi
T tera Ti tebi

So RAM in a computer is referred to as 4 GB or 4 Gigabytes, it would be more accurately described as 4 GiB or 4 GibiBytes as it is denoting a binary value. A 1000 BASE Ethernet card refers to decimal value, therefore the card is 1 GiBits. Storage is often labelled by hardware vendors as decimal multiples.

Useful Specifications

Here is a useful link for data transfers: https://en.wikipedia.org/wiki/Data_rate_units

USB https://en.wikipedia.org/wiki/USB
Mode Gross data rate Introduced in
Low Speed 1.5 Mbit/s USB 1.0
Full Speed 12 Mbit/s USB 1.0
High Speed 480 Mbit/s USB 2.0 (60 MB/s)
SuperSpeed 5 Gbit/s USB 3.0 (625 MB/s)
SuperSpeed+ 10 Gbit/s

PCI Express https://en.wikipedia.org/wiki/PCI_Express

v1.x: 250 MB/s (2.5 GT/s)
v2.x: 500 MB/s (5 GT/s)
v3.0: 985 MB/s (8 GT/s)
v4.0: 1969 MB/s (16 GT/s)

SATA Decimal Prefix Units https://en.wikipedia.org/wiki/Serial_ATA
SATA 1.0 – 1500 Mbit/s – 150 MB/s
SATA 2.0 – 3000 Mbit/s – 300 MB/s
SATA 3.0 – 6000 Mbit/s – 600 MB/s
SATA 3.2 – 16 Gbit/s – 1969 MB/s

Appendix: Throwing new hardware at an old server?

Is it a good idea to fully populate an R710 with 8 x expensive consumer grade SATA SSD? (This is an 11 Generation Dell PowerEdge Server from 2009.)

Well the server itself supports SATA 2.0 so that means each connected drive could potential push 300 MB/s, and most SSD will do more than that. So bottleneck one is that a modern SSD will want to go faster than SATA 2.0. So buy cheap SSD, as max we are going to start with is 300 * 8 = 2400 MB/s.

Next thing we hit is the controller card. A PCI Express x8 card with only 4 lanes wired Gen 1. 250 MB/s * 4 = 1000 MB/s.

 

Mmm, it’s really not going to take much to saturate this server using modern storage. Good idea to add SSD? No, unless you have them laying about.

Dell Documentation for the R710:

http://www.dell.com/downloads/global/products/pedge/en/server-poweredge-r710-tech-guidebook.pdf

11.1 Overview
The PowerEdge R710 has two PCI Express risers: Riser 1 and Riser 2. Each riser connects to the planar through a x16 PCI Express connector.
* Riser 1 consists of two x4 slots and a third x4 slot dedicated for internal SAS storage through the PERC 6i or SAS 6/iR.
* The default Riser 2 consists of two x8 PCI Express connectors.
* There is also an optional x16 Riser 2 that supports one x16 PCI Express card.

11.2 PCI Express Risers
The two PCI Express risers provide up to four expansion slots and one slot dedicated for the
integrated storage controller card. The slots meet the following requirements:
* Two x8 and two x4 PCI Express Gen2 slots, each connected to the IOH
* One x4 PCI Express Gen1 slot for internal storage connected to the IOH

12.4.1 SAS 6/iR
The R710 internal SAS 6/iR HBA is an expansion card that plugs into a dedicated PCI Express x8 slot (four lanes wired). It incorporates two four-channel SAS IOCs for connection to SAS or SATA hard disk drives. It is designed in a form factor that allows the same card to be used in the PowerEdge R610 and PowerEdge T610.

 

New Lab Environment 2015 and vpro VNC blackscreen

Now when I purchased nuc5i5mybe it was with the Intel® vPro Technology in mind. This technology requires no software for you to install and can be very useful for management purposes. There are lots of cool tools that can be used, but one of the most common is going to be the inbuilt vnc solution, which gives the ability see exactly what is happening and even login remotely.

Now the one stumbling block with the vnc solution which I did not initially realize is that a headless system (one without a monitor) may return a black screen when trying to logon.

https://support.realvnc.com/knowledgebase/article/View/261/2/problems-viewing-vnc-server-on-headless-devices

The solution that I found easiest was to get a EDID dongle which is basically a plugin dummy monitor device about the size of small thumb drive. If you do search for “edid headless plug” you should be able to get one for around £10 pounds or less. This device will then pretend to be a monitor and solve the issue of a black screen on the remote desktop session.

Most Recent SQL Query

Here is another query that is useful to have when you need to find out what everyone is up to. Or in some cases when we need to recover what was on someones ssms before they accidentally closed without saving work. :-)

--http://jongurgul.com/blog/most-recent-sql-query
SELECT
 DB_NAME(est.[dbid]) [DatabaseName]
,es.[session_id] [SessionID]
,est."text" [StatementText]
,es.[host_name] [ConnectionHostName]
,es.[login_name] [ConnectionLoginName]
,es.[program_name] [ConnectionProgramName]
FROM sys.dm_exec_sessions es
LEFT OUTER JOIN sys.dm_exec_connections ec ON es.[session_id] = ec.[session_id]
OUTER APPLY sys.dm_exec_sql_text(ec.[most_recent_sql_handle]) est

SQL Server Locks

Here is a simple script to get some more detailed information about locking, which I have added to over the last few years.

--http://jongurgul.com/blog/sql-server-locks
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
SELECT 
 dtl.[request_session_id] [SessionID]
,DB_NAME(dtl.[resource_database_id])  [DatabaseName]
,dtl.[request_status] [Status]
,dtl.[resource_type] [Resource]
,CASE 
WHEN [resource_type] =   'OBJECT'      THEN OBJECT_NAME(dtl.[resource_associated_entity_id],dtl.[resource_database_id])
WHEN [resource_type] =   'DATABASE'    THEN DB_NAME(dtl.[resource_database_id])
WHEN [resource_type] IN ('KEY','PAGE','RID') THEN pin.[ObjectName]
ELSE CAST(dtl.[resource_associated_entity_id] AS VARCHAR(MAX))
END [ResourceName]
,dtl.[resource_description] [ResourceDescription]
,dtl.[request_mode] [Mode]
,pin.[type_desc] [Type]
,QUOTENAME(pin.[ObjectSchemaName]) [ObjectSchemaName]
,QUOTENAME(pin.[ObjectName]) [ObjectName]
,QUOTENAME(pin.[IndexName]) COLLATE DATABASE_DEFAULT [IndexName]
,dtl.[resource_description] [ResourceDetail]
,CASE 
WHEN [resource_type] IN ('KEY','RID')
THEN N'SELECT * FROM '+QUOTENAME(DB_NAME(dtl.[resource_database_id]))
+'.'+QUOTENAME(pin.[ObjectSchemaName])
+'.'+QUOTENAME(pin.[ObjectName])
+N' WITH(NOLOCK'+
+COALESCE (',INDEX('+QUOTENAME(pin.[IndexName]) COLLATE DATABASE_DEFAULT +')','')
+') WHERE %%LOCKRES%% = '''
+RTRIM(dtl.[resource_description])+''''
WHEN [resource_type] IN ('PAGE') THEN 'DBCC PAGE('''+DB_NAME(dtl.[resource_database_id])+''','+RTRIM(REPLACE(dtl.[resource_description],':',','))+',3) WITH TABLERESULTS'
ELSE NULL
END COLLATE DATABASE_DEFAULT [Row/Page]--Performance will be poor if the table is large.
--,es.[original_login_name],es.[login_name]
FROM   
(
	SELECT i.[object_id],d.[name] [DatabaseName],d.[database_id],p.[hobt_id],i.[name] [IndexName],i.[type_desc]
	,OBJECT_SCHEMA_NAME(i.[object_id],d.[database_id]) [ObjectSchemaName]
	,OBJECT_NAME(i.[object_id],d.[database_id]) [ObjectName]
	FROM sys.partitions p 
	INNER JOIN sys.indexes i
	ON p.[object_id] = i.[object_id]
	AND p.[index_id] = i.[index_id]
	CROSS APPLY (SELECT * FROM sys.databases WHERE database_id = DB_ID()) d
		UNION ALL
		SELECT i.[object_id],d.[name] [DatabaseName],d.[database_id],p.[hobt_id],i.[name] [IndexName],i.[type_desc]
		,OBJECT_SCHEMA_NAME(i.[object_id],d.[database_id]) [ObjectSchemaName]
		,OBJECT_NAME(i.[object_id],d.[database_id]) [ObjectName]
		FROM tempdb.sys.partitions p 
		INNER JOIN tempdb.sys.indexes i
		ON p.[object_id] = i.[object_id]
		AND p.[index_id] = i.[index_id]
		CROSS APPLY (SELECT * FROM sys.databases WHERE database_id = DB_ID('tempdb')) d
) pin
RIGHT OUTER JOIN sys.dm_tran_locks dtl ON pin.[database_id] = dtl.[resource_database_id]
AND
((pin.[hobt_id] = dtl.[resource_associated_entity_id] OR pin.[object_id] = dtl.[resource_associated_entity_id]))
--LEFT OUTER JOIN sys.dm_exec_sessions es ON dtl.[request_session_id] = es.[session_id]
WHERE 1=1
AND dtl.[request_mode] <> 'Sch-S'
AND dtl.[request_mode] <> 'S' 
ORDER BY pin.[type_desc],dtl.[request_mode]

Database Corruption Week 7 Solution

Database Corruption Week 7 Solution

http://stevestedman.com/server-health/database-corruption-challenge/week-7-database-corruption-challenge/week-7-challenge-details/

1. Review Transaction log and find point at which rows were lost.

SELECT * FROM fn_dblog(NULL,NULL) lf

Transaction Name Current LSN
DELETE 00000025:00000270:0001 deleted by 0x01 (maps to sa) spid 51 at 2015/05/31  
17:57:56:287

2. Backup live transaction log/Restart Transaction log job

BACKUP LOG [CorruptionChallenge7] TO  DISK =  
N'C:\SQL_Data\TransLog_CorruptionChallenge7_6.trn'

3. Restore alternative database [CorruptionChallenge7Recovery]

USE [master]
RESTORE DATABASE [CorruptionChallenge7Recovery] FROM DISK =  
N'C:\SQL_Data\CorruptionChallenge7_1.bak'
WITH NORECOVERY,
MOVE N'CorruptionChallenge7' TO  
N'C:\SQL_DATA\CorruptionChallenge7Recovery.mdf',
MOVE N'UserObjects' TO N'C:\SQL_DATA\UserObjectsRecovery.ndf',
MOVE N'CorruptionChallenge7_log' TO  
N'C:\SQL_DATA\CorruptionChallenge7Recovery.ldf'--,REPLACE

RESTORE DATABASE [CorruptionChallenge7Recovery] FROM DISK =  
N'C:\SQL_Data\TransLog_CorruptionChallenge7_0.trn' WITH NORECOVERY
RESTORE DATABASE [CorruptionChallenge7Recovery] FROM DISK =  
N'C:\SQL_Data\TransLog_CorruptionChallenge7_1.trn' WITH NORECOVERY
--RESTORE HEADERONLY FROM DISK =  
N'C:\SQL_Data\TransLog_CorruptionChallenge7_2.trn' WITH NOUNLOAD;--not  
needed
--RESTORE HEADERONLY FROM DISK =  
N'C:\SQL_Data\TransLog_CorruptionChallenge7_3.trn' WITH NOUNLOAD;--not  
needed
--RESTORE HEADERONLY FROM DISK =  
N'C:\SQL_Data\TransLog_CorruptionChallenge7_4.trn' WITH NOUNLOAD;--not  
needed
RESTORE DATABASE [CorruptionChallenge7Recovery] FROM DISK =  
N'C:\SQL_Data\TransLog_CorruptionChallenge7_5.trn' WITH NORECOVERY
RESTORE LOG [CorruptionChallenge7Recovery] FROM DISK =  
N'C:\SQL_Data\TransLog_CorruptionChallenge7_6.trn'
WITH STOPATMARK  = 'lsn:0x00000025:00000270:0001'
--WITH STOPATMARK  = 'lsn:37:624:1'
GO

4. copy rows from recovery to live database if happy

SELECT COUNT([Record1000Id]) as check1
        ,CHECKSUM_AGG(CHECKSUM([FireDeptID])) as check2
        ,CHECKSUM_AGG(CHECKSUM([FireDeptState])) as check3
        ,CHECKSUM_AGG(CHECKSUM([AlarmDate])) as check4
        ,CHECKSUM_AGG(CHECKSUM([IncidentNumber])) as check5
        ,CHECKSUM_AGG(CHECKSUM([ExposureNumberZeroBased])) as check6
        ,CHECKSUM_AGG(CHECKSUM([RecordType])) as check7
        ,CHECKSUM_AGG(CHECKSUM([TransactionType])) as check8
        ,CHECKSUM_AGG(CHECKSUM([FireDepartmentStation])) as check9
FROM [OPEN_NFIRS].[Record1000];

check1        check2        check3        check4        check5        check6        check7        check8        check9
3010        2346        0        1643050        26216226        0        0        0        246

SET IDENTITY_INSERT [CorruptionChallenge7].[OPEN_NFIRS].[Record1000] ON
INSERT INTO [CorruptionChallenge7].[OPEN_NFIRS].[Record1000]  
(Record1000Id, FireDeptID, FireDeptState, AlarmDate, IncidentNumber,  
ExposureNumberZeroBased, RecordType, TransactionType,  
FireDepartmentStation)
SELECT Record1000Id, FireDeptID, FireDeptState, AlarmDate,  
IncidentNumber, ExposureNumberZeroBased, RecordType, TransactionType,  
FireDepartmentStation FROM  
[CorruptionChallenge7Recovery].[OPEN_NFIRS].[Record1000]
SET IDENTITY_INSERT [CorruptionChallenge7].[OPEN_NFIRS].[Record1000] OFF

Database Corruption Week 6 Solution

Database Corruption Week 6 Solution

http://stevestedman.com/server-health/database-corruption-challenge/week-6-database-corruption-challenge/week-6-challenge-details/

Check for corruption in the database, isolate to non clustered index.
Keep changes that will be lost when repairing damage. In this case there are 2 row first name values that “look” to be incorrect in the cluster. Write two simple updates to be run following disable and rebuild of non clustered index – cross checking with business that the values are indeed corrupt. Then run final check script.

USE [master]
RESTORE DATABASE [CorruptionChallenge6] FROM  DISK =  
N'C:\SQL_Data\CorruptionChallenge6.bak' WITH  FILE = 1,  NOUNLOAD,   
STATS = 5,REPLACE
GO
USE [CorruptionChallenge6]
GO
;WITH
c AS(
        SELECT [id],[FirstName],[LastName] FROM [dbo].[Customers] WITH
(INDEX([PK_Customers]))
),
nc AS(
        SELECT [id],[FirstName],[LastName] FROM [dbo].[Customers] WITH
(INDEX([ncFName]))
)
SELECT * FROM c FULL JOIN nc ON c.[id] = nc.[id] WHERE c.[FirstName] <> nc.[FirstName] OR c.[LastName] <> nc.[LastName]
--id        FirstName        LastName        id        FirstName        LastName
--252971        €ea        MULLEN        252971        Mia        MULLEN
--992        Aliwander        FISCHER        992        Alexander        FISCHER
GO
ALTER INDEX [ncFName] ON [dbo].[Customers] DISABLE 
GO
ALTER INDEX [ncFName] ON [dbo].[Customers] REBUILD
GO
DBCC CHECKDB('CorruptionChallenge6') WITH NO_INFOMSGS,ALL_ERRORMSGS
GO
SELECT COUNT(1) AS check1,
        COUNT(DISTINCT FirstName) AS check2,
        COUNT(DISTINCT MiddleName) AS check3,
        COUNT(DISTINCT LastName) AS check4,
        CHECKSUM_AGG(CHECKSUM(FirstName)) AS check5,
        CHECKSUM_AGG(CHECKSUM(MiddleName)) AS check6,
        CHECKSUM_AGG(CHECKSUM(LastName)) AS check7 FROM [dbo].[Customers];
--check1        check2        check3        check4        check5        check6        check7
--254031        19        17        879        -8736600        179        1330080960
SELECT * FROM [dbo].[Customers] WHERE [id] IN (992,252971)--Aliwander/€ea
GO
UPDATE [dbo].[Customers] SET [FirstName] = 'Mia' WHERE [id] = 252971
UPDATE [dbo].[Customers] SET [FirstName] = 'Alexander' WHERE [id] = 992
GO
SELECT COUNT(1) AS check1,
        COUNT(DISTINCT FirstName) AS check2,
        COUNT(DISTINCT MiddleName) AS check3,
        COUNT(DISTINCT LastName) AS check4,
        CHECKSUM_AGG(CHECKSUM(FirstName)) AS check5,
        CHECKSUM_AGG(CHECKSUM(MiddleName)) AS check6,
        CHECKSUM_AGG(CHECKSUM(LastName)) AS check7 FROM [dbo].[Customers];

--check1        check2        check3        check4        check5        check6        check7
--254031        17        17        879        1046491532        179        1330080960

SELECT * FROM [dbo].[Customers] WHERE [id] IN (992,252971)

Now the problem is that corruption has been corrected with what we think should be in the clustered index, based on a hunch that the data looks better. The page verify option should have caught this corruption as it is set to page checksum.

But this corruption also rewrote the page checksum ;-p

--Alternative Fix for corruption in clustered index, then fix nc as before. DO NOT USE DBCC WRITEPAGE in PRODUCTION environments!!! It is very dangerous.
/*
--FOR TEST ENVIRONMENTS ONLY--
DBCC PAGE ([CorruptionChallenge6],1,174,3) WITH TABLERESULTS
DBCC PAGE ([CorruptionChallenge6],1,1215,3) WITH TABLERESULTS
ALTER DATABASE [CorruptionChallenge6] SET SINGLE_USER
DBCC WRITEPAGE (N'CorruptionChallenge6',1,174,896,2,0x6578,0);--Byte 1426304-1426305 69 77 to 65 78
DBCC WRITEPAGE (N'CorruptionChallenge6',1,1215,3488,2,0x4D69,0);--Byte 9956768-9956769 80 65 to 4D 69
ALTER DATABASE [CorruptionChallenge6] SET MULTI_USER
*/

Database Corruption Week 5

Database Corruption Week 5

This week I managed to achieve the goal, however it was not very elegant. Plus the only way I could achieve it was to use a hex editor.

Basically I copied page 9 from the backup, and replaced it in the live version with an editor, which then enabled access to the database in emergency mode. After this I noticed that the missing rows upon repair could be found in the old backup. Both these methods I thought to be a hax. (and thought there must be a clean way to get an online database). I look forward to a prettier solution which hopefully Steve will post this week.I did at first try the method that Steve has posted as the wining solution to week 5, however I could not achieve an attached copy of the database with a missing primary mdf.

“Msg 5173, Level 16, State 1, Line 66
One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.”

The original database and details for this can be found here:

http://stevestedman.com/server-health/database-corruption-challenge/week-5-database-corruption-challenge/week-5-challenge-details/

 

Update 24 May 2015.

Well Steve posted his alternative solution and it basically mirrors mine. (doh.. should have submitted). Anyway here is link: http://stevestedman.com/2015/05/week-5-alternate-solution/

Anyway it looks like the attach solution is very much environment specific so good luck if you are trying to get it to work. Please post any tips to getting it to work, it might be worth setting up a vm and mirror exactly the environment from the winning solution.