SQL Server built-In monitoring components

First, It should have been determined on where/what to monitor for the current situation. right ? Yes!

And, select an appropriate tools to proceed with. It can be either from Windows Itself Or SQL Server specific

Windows Monitoring Tools:

SQL Server Tools:

Note
  • SQL Trace and SQL Server Profiler are deprecated. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
  • Use Extended Events instead

The specified module 'SqlServer' was not loaded because no valid module file was found in any module directory

When I was thinking to access/connect SQL Server using PowerShell in one of the following ways

1. .NET (System.Data.SqlClient)

2. SSMO (SQL Server Management Objects)

3. SQL Server PowerShell

I was actually trying with third method. Yes! unfortunately, It failed. Let me tell you, what I tried and how I resolved.

Open PowerShell ISE and try the below line of command


Connecting SQL from local machine

Import-Module SqlServer 





I got the Err...
Import-Module : The specified module 'SqlServer' was not loaded because no valid module file was found in any module directory.

I thought, we don't have the Module in my environment and check the available Modules named "SQLServer" finally

Get-Module -ListAvailable -Name *SQLServer*




I could not find any such a Modules

I tried to Install the Module in my environment, 

The PowerShell Gallery is the repository from Microsoft

Click and open the specific Module "SQLServer"














I Installed the Module
Install-Module -Name SqlServer -RequiredVersion 21.1.18245 -AllowClobber




Actually, The Package has 23.82 MB

Let the process download the required Package and Install



Once the Installation complete

Let me try to check the Module installed
Get-Module -ListAvailable -Name *SQLServer*






Yes! I can see the Module Installed

Let me try to Import the Module
Import-Module SqlServer





Let us get the List of Databases from my local SQL Instance
Get-SqlDatabase -ServerInstance SQLBUDDY\SQL2019






See, I got the all available Databases in my SQL Instance "SQLBUDDY\SQL2019"

Note: I am using Logged-In same credential to connect with my local SQL Server Instance

File "*.ps1" Cannot be loaded because running scripts is disabled on this system

With this title, I hope you already know what we are going to explore about...

You may get this Err, When you try to execute a PowerShell script file

Usually, This feature may have been Disabled due to security vulnerability

Try enable it and see. That's it!

Let's start here...

Let me try to execute a script file in PowerShell ISE (Integrated Scripting Environment)

Open PowerShell ISE




I have a simple script (Script1.ps1) to connect with my local SQL named Instance

I have opened the script in PowerShell ISE and Trying to execute it. But, I got the below Err








Which means, This feature has been Disabled in this environment, Let us enable it

To check with all available policies and It's status










See, All of them are Undefined

Let us enable one of them

Kindly explore all the available Policies and Scopes HERE, Before enabling it.



See, What we have enabled














Let us execute the script file again and see








Yes! Now It's working...

Post your question If you still have any Err 

Resources...

What is PowerShell?


Generate sequence number using ROW_NUMBER() without sorting the actual column

As we know about Ranking functions to return a ranking/sequence value for each row in a partition based on a specific/set of column(s). Right ?

Let me define a table and values

If Object_Id('dbo.SequenceData') Is Null
Create Table [dbo].SequenceData
(
Col1 Varchar(5)
)
Go
TRUNCATE TABLE [dbo].SequenceData
Go
Insert
[dbo].SequenceData
Select 'A' Union All
Select 'D' Union All
Select 'A' Union All
Select 'C' Union All
Select 'B'
Go

SELECT * FROM [dbo].SequenceData
Go









let me generate a sequence number for the value

SELECT ROW_NUMBER() OVER(),* FROM [dbo].SequenceData
GO

Msg 4112, Level 15, State 1, Line 31
The function 'ROW_NUMBER' must have an OVER clause with ORDER BY.

But, It's not working, since we need ORDER BY clause specified as per the syntax. right ?

So. let me include ORDER BY clause and see...

SELECT ROW_NUMBER() OVER(ORDER BY Col1),* FROM [dbo].SequenceData
GO







No..No..No, My actual column "Col1" value should not be Sorted. What should we do ?

Let us try something like this

SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),* FROM [dbo].SequenceData
GO








We have specified a virtual column in ORDER BY clause which will return always NULL. So, The actual data will not be sorted.

We get a sequence for the column value without sorting it - "AS IS"

DAC or Normally logged-in ?

How do I know whether I have logged-In as DAC or Normally logged-In ?

SELECT 'You have connected as "DAC"' [Who you are] 
FROM sys.dm_exec_sessions s join sys.endpoints  e
ON (s.endpoint_id = e.endpoint_id) 
WHERE e.name ='Dedicated Admin Connection'
AND s.session_id = @@spid
GO

1. Connect with Instance using SQLCMD by Trusted Connection

2. Paste the above script and execute it

Connect normally (Trusted Connectionusing SQLCMD :-

Connect as DAC using SQLCMD :-




'%.*ls' does not contain an identity column

Why do we get this Err ?

Are you trying to get an IDENTITY property Info of a Table? - But, The table actually doesn't have any IDENTITY on it!

Ok.

Let me create a new table without any IDENTITY on it

CREATE TABLE dbo.Table1

(
Id Int, Name Varchar(10)
)
GO

Now, I am trying to get the IDENTITY Info of the Table

DBCC CHECKIDENT('dbo.Table1')


Msg 7997, Level 16, State 1, Line 4
'Table1' does not contain an identity column.

Yeah! Actually, we don't have an IDENTITY on this table. Right!

The maximum system-generated unique value for a duplicate group was exceeded for index with partition ID %I64d. Dropping and re-creating the index may resolve this; otherwise, use another clustering key

Many of you may have encountered such a critical situation like some of the Table was working fine until just last minute without any Err and suddenly you may have faced an Err shown below so called Error 666

Msg 666, Level 16, State 2, Line 1
The maximum system-generated unique value for a duplicate group was exceeded for index with partition ID %I64d.
Dropping and re-creating the index may resolve this; otherwise, use another clustering key.

What does mean ?

According to MSDN - "Clustered indexes sort and store the data rows in the table based on their key values. There can only be one clustered index per table, because the data rows themselves can only be sorted in one order. With few exceptions, every table should have a clustered index defined on the column, or columns, that offer the following"

  • Can be used for frequently used queries.
  • Provide a high degree of uniqueness
  • Can be used in range queries
If the clustered index is not created with the UNIQUE property forced to it!. So what ?

Here is the internal thing happens - "The Database Engine automatically adds a 4-byte UNIQUIFIER column to the table. When it is required, the Database Engine automatically adds a UNIQUIFIER value to a row to make each key unique. The value starts with 0"

But, This column and it's values are used internally and cannot be seen or accessed by the users by accessing the table just like that.

You may ask now - Why do we need to worry about this situation ?

Let's have some scenario

USE [MyDatabase1]
GO

/*Table created with TWO column*/
CREATE TABLE MyTable1
(
Id INT, Names VARCHAR(20) 
)
GO

/*Clustered Non-Unique key created on Id column*/
CREATE CLUSTERED INDEX CI_Id ON MyTable1(Id)
GO

/*2 Records inserted*/
INSERT MyTable1
SELECT 1,'SQL' UNION ALL
SELECT 2,'SERVER'
GO

/*Checking the Page allocations of the Table*/
SELECT %%Lockres%% [KeyHashValue], sys.fn_physlocformatter(%%Physloc%%) [File:Page:Slot], * FROM MyTable1






/*Checking the specific page (Page ID: 232)*/
DBCC TRACEON(3604)
DBCC PAGE('MyDatabase1',1,232,3) WITH TABLERESULTS



















But, It has lot of other Information. Right. let's simplify the data and remove rest of them

/*Creating a Temp table to have the about result. So, That we can filter-it out as needed*/
CREATE TABLE #Info(ParentObject VARCHAR(50), [Object] VARCHAR(100), Field VARCHAR(50), Value VARCHAR(1000))

INSERT #Info
EXEC('DBCC PAGE(''MyDatabase1'',1,232,3) WITH TABLERESULTS')

/*We don't need Header and Buffer Info for now*/
DELETE #Info WHERE ParentObject in('BUFFER:','PAGE HEADER:')

SELECT * FROM #Info









As you can see above, There is one more Internal column has been added "UNIQUIFIER" to force the Internal Uniqueness for the Clustered Key.

And, Both the "UNIQUIFIER" record has a value as "0". Because, There is No duplicate on Clustered key column "Id". So, There is no need for Nth duplicate indication on "UNIQUIFIER" column. Right!

/*Insert 4 More records with 2 Duplicate Data on "Id" column*/
INSERT MyTable1
SELECT 3,'SQL SERVER' UNION ALL
SELECT 3,'UNIQUIFIER' UNION ALL
SELECT 3,'CLUSTERED' UNION ALL
SELECT 4,'NON-UNIQUE'
GO

TRUNCATE TABLE #Info

INSERT #Info
EXEC('DBCC PAGE(''MyDatabase1'',1,232,3) WITH TABLERESULTS')

DELETE #Info WHERE ParentObject in('BUFFER:','PAGE HEADER:')

SELECT FROM #Info WHERE Field IN ('UNIQUIFIER','Id','Names','KeyHashValue')
























Can you guess ? What just happening ? Yes! - You are right!

We have inserted 4 New records and 3 of them duplicated (Id as "3"). Right ? So, The system wants to force the Unique value on "UNIQUIFIER" internal column. So, The "UNIQUIFIER" column have Nth duplicate indication!!

/*Deleted the records which Id column has "3"*/
DELETE MyTable1 WHERE Id = 3
GO

/*Inserted 3 records again*/
INSERT MyTable1
SELECT 3,'SQL SERVER' UNION ALL
SELECT 3,'UNIQUIFIER' UNION ALL
SELECT 3,'CLUSTERED'
GO

So, Can you guess - How many physical records will be there ? 

Let's explore here

SELECT * FROM MyTable1
GO










Yeah! We have 6 Records as expected.

What would be the "UNIQUIFIER" internal value the system have ?

TRUNCATE TABLE #Info

INSERT #Info
EXEC('DBCC PAGE(''MyDatabase1'',1,232,3) WITH TABLERESULTS')

DELETE #Info WHERE ParentObject in('BUFFER:','PAGE HEADER:')

SELECT FROM #Info WHERE Field IN ('UNIQUIFIER','Id','Names','KeyHashValue')

























Yes! We just have 6 Records (Slot 0 to Slot 5) only. But, Did you see the "UNIQUIFIER" Internal column value ?

It has been Increased! Because, we already had 2 duplicate detected on "Id" column. Now, It has increased since than. It's not Reset anymore even when records deleted. That's the reason why the Internal column getting Increased even If the actual duplicate records removed/deleted!

So, I hope you would have guessed, What would happen If these kind of duplicate records DELETED and INSERTED multiple time with huge volume (Millions of transactions), The "UNIQUIFIER" internal column still increase along with the DELETED duplicated data and It's not RESET anymore as I said earlier.

Ok. Guess what ?

We will get such an Error, If we perform such a transaction as explained above without resetting the UNIQUIFIER, sometimes we can reach it's upper limit "2147483648"





















We will get such an Error If we INSERT 1 more duplicate record...

Msg 666, Level 16, State 2, Line 1
The maximum system-generated unique value for a duplicate group was exceeded for index with partition ID %I64d.
Dropping and re-creating the index may resolve this; otherwise, use another clustering key.

The goal of this post is to give you an insight on how the UNIQUIFIER works and allow you to manually check for potential issue in your environment and avoiding such an error 666 and over come the situation

How to reset the value and unblock the situation ?

1.As per the Error suggests DROP and CREATE the Index
DROP INDEX MyTable1.CI_Id
GO
CREATE CLUSTERED INDEX CI_Id ON MyTable1(Id)
GO

Note: Rebuild Index will not help us

Check with the New page ID allocated and see whether the "UNIQUIFIER" value has been reset!

2. If still the Issue not been resolved

  • Create a new table with same structure (MyTable1_New)
  • Load the Data from an existing table (Using Import wizard Or Bulk Insert)
  • Create the Clustered Index
  • Archive/Remove the Old Table once make sure everything is resolved 

Thanks for your time!

Automatic Page Repair With SQL Server Database Mirroring - Part II

Please have a look to the Part I for better understanding.

In our previous part, there was lots of steps to configure the Mirroring and Manual Failover thing. I hope you would have enjoyed that. Thanks for your time on that!!


Here we will be discussing about - How the corrupted Page will be repaired automatically by the Mirroring


Just recap about the environments participated on Mirroring. We have failover it again to being back to the below state







Let me make sure the Mirroring environments are in Sync

















Everything is in Sync


1. let me corrupt some Data Page of Principal Database "PrimaryDB1" (In PANDIAN\SQL2012)


2. Here is the Data Page we trying to corrupt

:CONNECT PANDIAN\SQL2012
SELECT %%LockRes%% [Page Allocation],* FROM PrimaryDB1.dbo.TABLE1

Ok. I try to corrupt the page id "232" (232 x 8192 = 1900544)

Disclaimer: Please don't try to corrupt the data page manually in any of the PRODUCTIONS Or other live Databases environments. It may lead to corrupt and non-operational the whole database If something goes wrong!!

3. let me use XVI32.exe tool to corrupt the Page

Actually, The plan is to open the data file(.mdf) in this tool in administrative mode to make the change on it's internal allocation bits


Let me get the File path of the Database

:CONNECT PANDIAN\SQL2012
USE PrimaryDB1
GO
Exec sp_helpfile





Normally, The database should be in OFFLINE to access the underlying Data/Log files.


But, We can't do that right now since the Database is in Mirroring. let's try once


:CONNECT PANDIAN\SQL2012

USE [Master]
GO
ALTER DATABASE PrimaryDB1 SET OFFLINE

Connecting to PANDIAN\SQL2012...

Msg 1468, Level 16, State 1, Line 10
The operation cannot be performed on database "PrimaryDB1" because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group.
Msg 5069, Level 16, State 1, Line 10
ALTER DATABASE statement failed.
Disconnecting connection from PANDIAN\SQL2012...

So, What to we do now ?


let me Stop the Mirror Now

:CONNECT PANDIAN\SQL2012
ALTER DATABASE PrimaryDB1 SET PARTNER SUSPEND



















Let me STOP the SQL Service of the Primary database Instance(PANDIAN\SQL2012)

4. Let's corrupt the page id (232) using the Tool (XVI32.exe)

Open the Tool in Administrative mode

File --> Open --> Locate the data file (.mdf)


Address --> Goto --> 1900544


Change some of the Bits


Save the File. Its' done. We just corrupted the Data page


START the SQL Service


5. Let me access the Table


:CONNECT PANDIAN\SQL2012

SELECT %%LockRes%% [Page Allocation],* FROM PrimaryDB1.dbo.TABLE1

Yeah. See, The Page ID: 232 got corrupted as shown below

Connecting to PANDIAN\SQL2012...
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xefd30b4e; actual: 0x67db0b46). It occurred during a read of page (1:232) in database ID 5 at offset 0x000000001d0000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\PrimaryDB1.mdf'.  Additional messages in the SQL Server error log or system event 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.

let me check at the suspect pages system table 

:CONNECT PANDIAN\SQL2012
SELECT * FROM msdb.dbo.suspect_pages





See, The event_type column has 2 (Bad Checksum)

For detailed Info on msdb..suspect_pages

How do we know, The Mirroring repaired the corrupted page automatically Or Not ?

We have a system VIEW sys.dm_db_mirroring_auto_page_repair which will have a row for every automatic page-repair attempt on any mirrored database on the server instance

:CONNECT PANDIAN\SQL2012
SELECT * FROM sys.dm_db_mirroring_auto_page_repair






There is no records in this system View yet which means - Mirroring not started to repair the page yet!

Let me start the Mirror now and see...

:CONNECT PANDIAN\SQL2012
ALTER DATABASE PrimaryDB1 SET PARTNER RESUME

Wait for some time and Let the Mirroring repair the corrupted page in Principal Database

6. Let me access the corrupted Table again in Principal Database

:CONNECT PANDIAN\SQL2012
SELECT %%LockRes%% [Page Allocation],* FROM PrimaryDB1.dbo.TABLE1















It's done!!! - The corrupted page got repaired and The table back to operational

Let's make sure

:CONNECT PANDIAN\SQL2012
SELECT FROM msdb.dbo.suspect_pages





See, The event_type column has 5 now (Repaired)

:CONNECT PANDIAN\SQL2012

See, The page_status column has 5 (5 = Automatic page repair succeeded and the page should be usable)

What just happening when the page corrupted at Principal source ?

According to MSDN

a) When a read error occurs on a data page in the principal/primary database, the principal/primary inserts a row in the suspect_pages table with the appropriate error status. the principal then requests a copy of the page from the mirror.

b) The request specifies the page ID and the LSN that is currently at the end of the flushed log.

c) The page is marked as restore pending. This makes it inaccessible during the automatic page-repair attempt. Attempts to access this page during the repair attempt will fail with error 829 (restore pending)

d) After receiving the page request, the mirror/secondary waits until it has redone the log up to the LSN specified in the request. Then, the mirror/secondary tries to access the page in its copy of the database. If the page can be accessed, the mirror/secondary sends the copy of the page to the principal/primary. Otherwise, the mirror/secondary returns an error to the principal/primary, and the automatic page-repair attempt fails.

7. What will happen, If the Principal and Mirror versions are different ?

If I use SQL Server 2012 for Principal and SQL Server 2014 for Mirror


It can be able to failover from Principal to Mirror (2012 to 2014)


But, 


If I try to failover back 2014 to 2012, There will be a problem with below Err 


Error: 948, Severity: 20, State: 2.

The database 'PrimaryDB1' cannot be opened because it is version 782. This server supports version 706 and earlier. A downgrade path is not supported.

Here are the list of SQL Server Database Versions