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.

Database Corruption Week 4 Solution

Database Corruption Week 4 Solution

Here is my late solution for week four. Blame the UK Bank Holiday. The original database and details for this can be found here:

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

This week was harder, but easier with the clue provided pointing you towards the right area.

USE [master]
RESTORE DATABASE [CorruptionChallenge4] FROM  DISK = N'C:\Path\CorruptionChallenge4_Corrupt.bak' 
WITH  FILE = 1,  MOVE N'CorruptionChallenge4' TO N'C:\Path\CorruptionChallenge4.mdf',  
MOVE N'UserObjects' TO N'C:\Path\CorruptionChallenge4_UserObjects.ndf',  
MOVE N'CorruptionChallenge4_log' TO N'C:\Path\CorruptionChallenge4_log.ldf',  NOUNLOAD,  STATS = 5
,
    KEEP_CDC;
GO

USE [CorruptionChallenge4];
WITH
f AS
(
SELECT [id],[FirstName] FROM [dbo].[Customers] WITH (INDEX([ncCustomerFirstname]))--(511740 row(s) affected)
),
l AS
(
SELECT [id],[LastName] FROM [dbo].[Customers] WITH (INDEX([ncCustomerLastname]))--(511740 row(s) affected)
)
SELECT f.[id],f.[FirstName],c.MiddleName,l.[LastName] INTO [tempdb].[dbo].[Customers]
FROM f 
INNER JOIN l ON l.[id] = f.[id]
INNER JOIN cdc.fn_cdc_get_net_changes_dbo_Customers(sys.fn_cdc_get_min_lsn('dbo_Customers'),sys.fn_cdc_get_max_lsn(),'all') c ON c.[id] = f.[id]
--SELECT * FROM cdc.captured_columns
--SELECT * FROM cdc.change_tables
GO
ALTER DATABASE [CorruptionChallenge4] SET SINGLE_USER
GO
DBCC CHECKTABLE ('[dbo].[Customers]',repair_allow_data_loss)--This will deallocate the pages and leave [dbo].[Customers] empty, which will cause constraint violations.
GO
DBCC CHECKDB('CorruptionChallenge4') WITH NO_INFOMSGS,ALL_ERRORMSGS;
GO
DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS
GO
ALTER TABLE [dbo].[Orders] WITH CHECK CHECK CONSTRAINT ALL--The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Orders_People". The conflict occurred in database "CorruptionChallenge4", table "dbo.Customers", column 'id'.
GO
SET IDENTITY_INSERT [dbo].[Customers] ON
	INSERT INTO [dbo].[Customers] ([id],[FirstName],[MiddleName],[LastName])
	SELECT [id],[FirstName],[MiddleName],[LastName] FROM [tempdb].[dbo].[Customers]
SET IDENTITY_INSERT [dbo].[Customers] OFF
GO
ALTER TABLE [dbo].[Orders] WITH CHECK CHECK CONSTRAINT ALL
GO
ALTER DATABASE [CorruptionChallenge4] SET MULTI_USER
GO

SELECT * FROM [dbo].[Customers] WHERE [id] IN (510900,510901)
--id	FirstName	MiddleName	LastName
--510900	Steve	M	Stedman
--510901	William	V	STARK
GO
SELECT COUNT(*)--9
FROM sys.objects
WHERE is_ms_shipped = 0;

Database Corruption Week 3 Solution

Database Corruption Week 3 Solution

Here is the solution for week three. The original database and details for this can be found here:

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

This week was kind of easy as all that is needed is to backup the transaction log with NO_TRUNCATE, and then follow a standard restore. If for some reason the database had been detached then this would have been slightly more tricky, however creating a dummy database would have only taken a few minutes more.

BACKUP LOG [CorruptionChallenge3] TO DISK = N'C:\Path\tail.trn' WITH INIT, NO_TRUNCATE;
GO
USE [master]
RESTORE DATABASE [CorruptionChallenge3] FROM  DISK = N'C:\Path\CorruptionChallenge3_Full.bak' WITH  FILE = 1,
MOVE N'CorruptionChallenge3' TO N'C:\Path\CorruptionChallenge3.mdf',
MOVE N'CorruptionChallenge3_log' TO N'C:\Path\CorruptionChallenge3_log.LDF',  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [CorruptionChallenge3] FROM  DISK = N'C:\Path\3\TransLog_CorruptionChallenge30.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [CorruptionChallenge3] FROM  DISK = N'C:\Path\TransLog_CorruptionChallenge31.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [CorruptionChallenge3] FROM  DISK = N'C:\Path\TransLog_CorruptionChallenge32.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [CorruptionChallenge3] FROM  DISK = N'C:\Path\tail.trn' WITH  FILE = 1,  NOUNLOAD,  STATS = 5
GO

Database Corruption Week 2 Solution

Database Corruption Week 2 Solution

Unfortunately I missed week 2 submissions, but here is my solution for week two. The original database and details for this can be found here:

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

 

The data corruption is confined to a single page. Page 244. There is a non clustered index available meaning that the only columns that are an issue are [Year] and [Notes], which are retrieved from the old backup file and cross checked with the contents of the corrupt page.

USE [master]
RESTORE DATABASE [CorruptionChallenge2] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\2\CorruptionChallenge2_LatestBackup.bak' 
WITH  FILE = 1, 
MOVE N'CorruptionChallenge2' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\CorruptionChallenge2.mdf',  
MOVE N'CorruptionChallenge2_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\CorruptionChallenge2_log.LDF'
GO
USE [master]
RESTORE DATABASE [CorruptionChallenge2_TwoDaysAgoBackup] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\2\CorruptionChallenge2_TwoDaysAgoBackup.bak' 
WITH  FILE = 1, 
MOVE N'CorruptionChallenge2' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\CorruptionChallenge2_TwoDaysAgoBackup.mdf',  
MOVE N'CorruptionChallenge2_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\CorruptionChallenge2_TwoDaysAgoBackup.LDF'
GO

USE [CorruptionChallenge2];
WITH 
a AS(SELECT TOP (595) * FROM [dbo].[Revenue] ORDER BY [ID] ASC),
b AS(SELECT TOP (9450) * FROM [dbo].[Revenue] ORDER BY [ID] DESC),
Good AS(SELECT *,1 [RecordStatus] FROM a UNION ALL SELECT *,1 [RecordStatus] FROM b),
PartialGood AS(
	SELECT p.[ID],p.[DepartmentID],p.[Revenue],Old.[Year],Old.[Notes],0 [RecordStatus] FROM [CorruptionChallenge2].[dbo].[Revenue] p WITH (INDEX([ncDeptIdYear]))
	LEFT JOIN [CorruptionChallenge2_TwoDaysAgoBackup].[dbo].[Revenue] Old
	ON  p.[ID] = Old.[Id]
	WHERE NOT EXISTS (SELECT * FROM [Good] WHERE [Good].[ID] = p.[ID])
)

SELECT * INTO [dbo].[RevenueLatest] FROM Good UNION ALL SELECT * FROM PartialGood
GO
SELECT * FROM  [dbo].[RevenueLatest] WHERE [RecordStatus] = 0--These records were rebuilt from data held in an old backup
DBCC PAGE('CorruptionChallenge2',1,244,1) WITH TABLERESULTS--This can be crossed checked against the damage page. It is only 12 rows, so we can check manually.
GO
TRUNCATE TABLE [Revenue] 
GO
DBCC CHECKDB('CorruptionChallenge2') WITH NO_INFOMSGS,ALL_ERRORMSGS;
GO
SET IDENTITY_INSERT [dbo].[Revenue] ON;
INSERT INTO [Revenue] ([id],[DepartmentID],[Revenue],[Year],[Notes])
SELECT [id],[DepartmentID],[Revenue],[Year],[Notes] FROM [dbo].[RevenueLatest]
SET IDENTITY_INSERT [dbo].[Revenue] OFF;
GO
EXEC [dbo].[checkCorruptionChallenge2Result]
GO

Database Corruption Week 1 Solution

Database Corruption Week 1 Solution

Here is my solution for week one. The original database and details for this can be found here:

http://stevestedman.com/2015/04/introducing-the-database-corruption-challenge-dbcc-week-1-challenge/

USE [CorruptionChallenge1]
GO
SELECT [id],[Notes] INTO #s FROM [dbo].[Revenue] s WITH (INDEX([ncBadNameForAnIndex]))
GO
DBCC DBREINDEX ('dbo.Revenue',clustId);
GO
UPDATE t SET t.[Notes] = s.[Notes] FROM [dbo].[Revenue] t
INNER JOIN #s s ON t.[id] = s.[id] WHERE t.[Notes] <> s.[Notes] OR t.[Notes] IS NULL
GO
SELECT * FROM [dbo].[Revenue]
GO
DROP TABLE #s
GO
DBCC CHECKDB('CorruptionChallenge1') WITH NO_INFOMSGS,ALL_ERRORMSGS;
GO

 

 

New Lab Environment 2015

I have recently been looking at solution for hardware for a new home lab. The primary need was a lower power solution, with plenty of ram and the ability to run hyper-v.

The solution I have chosen is a small form factor from Intel called nuc5i5mybe. The hardware purchase list is as follows:

Crucial 16GB (2x 8GB) DDR3 1600 MT/s CL11 SODIMM 204 Pin 1.35V/1.5V Memory Module Kit CT2KIT102464BF160B ***NOTE THIS IS LOW VOLTAGE RAM compatible with the board***
http://www.amazon.co.uk/gp/product/B007B5S52C
Price: £97.60

BLKNUC5I5MYHE
http://www.scan.co.uk/products/intel-nuc-core-i5-5300u-dual-core-23ghz-ddr3l-so-dimm-m2-plus-sata-iii-6gb-s-25-internal-intel-hd-55
Price: £346.98

M.2 Type 2280 500GB Fast Solid State Drive/SSD Crucial MX200
http://www.scan.co.uk/products/500gb-crucial-mx200-ssd-m2-type-2280-with-555-mb-s-read-500-mb-s-write-100k-iops-random-read-87k-iop
Price: £168.60

Total Cost: £613.18

This enables the running of several visualized guests, which so far I have two domain controllers, and one edge server allowing a site to site vpn to azure. This means my home lab can integrate seamlessly with the cloud offering from MS.