Variable WMI not found…

August 1, 20161 Comment

When you are setting up alerts using WMI and if your intention is to capture the event data with sql agent job make sure you change below settings in SQL Server Agent Configuration.

dba_sqlagent

EXEC msdb.dbo.sp_set_sqlagent_properties @alert_replace_runtime_tokens = 1
GO

This will allow SQL Server Agent to replace the tokens with running values (what database changed, who changed it etc.). Read more about using Tokens in Job Steps here (make sure you read the security note in the article and understand the security risk).

Comparing sys.dm_db_index_physical_stats mode levels with Heap and Clustered Index

January 19, 2016January 19, 2016Leave a comment

Create a table with some data

drop table t3
CREATE TABLE t3 (col1 int PRIMARY KEY, col2 varchar(500)) WITH(DATA_COMPRESSION = PAGE);
GO
--BEGIN TRAN
DECLARE @idx int = (select max(isnuLL(col1,0))+1 from t3);
WHILE @idx < 1000000
BEGIN
BEGIN TRAN
    INSERT INTO t3 (col1, col2) 
    VALUES (@idx+convert(int,rand() * 100), 
    REPLICATE ('a', 100) + CAST (@idx as varchar(10)) + REPLICATE ('a', 380))
    SET @idx = @idx + 1
	COMMIT
END
--COMMIT;
GO

Check Fragmentation using sys.dm_db_index_physical_stats

SELECT 'Limited' as Mode,page_count, compressed_page_count as cm_page_count,index_type_desc
,index_depth,index_level,avg_fragmentation_in_percent,record_count
,fragment_count,object_name(object_id) as Object_name
FROM sys.dm_db_index_physical_stats (db_id(),
object_id ('t3'), null, null, 'LIMITED');

SELECT 'Sampled' as Mode,page_count, compressed_page_count as cm_page_count
,index_type_desc,index_depth,index_level,avg_fragmentation_in_percent,record_count
,fragment_count,object_name(object_id) as Object_name
FROM sys.dm_db_index_physical_stats (db_id(),
object_id ('t3'), null, null, 'SAMPLED');

SELECT 'Detailed' as Mode,page_count, compressed_page_count as cm_page_count
,index_type_desc,index_depth,index_level,avg_fragmentation_in_percent,record_count
,fragment_count,object_name(object_id) as Object_name
FROM sys.dm_db_index_physical_stats (db_id(),
object_id ('t3'), null, null, 'DETAILED');

—

Fragmentation output

Mode: Limited

Only looks at leaf level pages
Checks the logical fragmentation at leaf level (Out of Order pages)/ out-of-order extents in the leaf pages of a heap
Doesn’t look into the actual pages.

Mode: Sampled

Only looks at leaf level pages
Checks the logical fragmentation at leaf level (Out of Order pages) out-of-order extents in the leaf pages of a heap
Scans a sample of leaf pages (10 % approximately) it may randomly these pages.

Mode: Detailed

Does what limited mode normally do but scans every page in the index including the intermediate and root pages.
Check the logical fragmentation at every level of Index (Out of Order pages)/ out-of-order extents in the leaf pages of a heap
Scans the entire index.

Detailed mode is more accurate than any other mode because it scans every page of the index but this might create some performance bottle neck. It depends on where each of this mode can be used if an index with high fanout (index key length) which is directly proportional to the number of levels , it makes sense to check the fragmentation at intermediate levels using detailed mode at least once a week as part of Index Maint plan.

Terms :

Out of Order pages : An out-of-order page is a page for which the next physical page allocated to the index is not the page pointed to by the next-page pointer in the current leaf page.
Out-of-order extent: An out-of-order extent is one for which the extent that contains the current page for a heap is not physically the next extent after the extent that contains the previous page.

B-Tree Index Structure:

Heap:

Heap

Non Clustered or Clustered Index

Fix: An TLS 1.0 connection request was received but none of the cipher suite support and SSL connection request has failed

December 30, 2015Leave a comment

If you encounter below error when using SQL Server and TLS protocol in conjunction with SSL .There is setting in the Group policy under Administrative Template/ Network / SSL Configuration Setting this should be disabled or left not be configured

Error :
An TLS 1.0 connection request was received from a remote client application, but none of the cipher suites supported by the client application are supported by the server. The SSL connection request has failed.
A fatal alert was generated and sent to the remote endpoint. This may result in termination of the connection. The TLS protocol defined fatal error code is 40. The Windows SChannel error state is 1205.

Changes required to fix this.

RUN -> gpedit.msc-> Administrative Template-> Network -> SSL Configuration-> SSL Cipher Suite Order-> Disable

FIX: TDSSNIClient initialization failed with error 0x80092004,SQL Server could not spawn FRunCommunicationsManager thread

December 28, 2015June 13, 20161 Comment

How to fix

TDSSNIClient initialization failed with error 0x80092004, status code 0x80. Reason: Unable to initialize SSL support.
TDSSNIClient initialization failed with error 0x80092004, status code 0x1. Reason: Initialization failed with an infrastructure error.
Could not start the network library because of an internal error in the network library.

SQL Server could not spawn FRunCommunicationsManager thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

Make below registry changes.

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Server

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\SSL 3.0\Server

Make sure TLS 1.0 Server and SSL3.0 Server are enabled

Check these links as well

https://thedataspecialist.wordpress.com/2013/03/12/using-a-self-signed-ssl-certificate-with-sql-server/

http://kkaminsk.blogspot.com/2014/08/problems-starting-sql-server-unable-to.html

http://blogs.msdn.com/b/john_daskalakis/archive/2014/10/15/the-server-could-not-load-the-certificate-it-needs-to-initiate-an-ssl-connection-it-returned-the-following-error-0x8009030d-check-certificates-to-make-sure-they-are-valid.aspx

http://kalcik.net/2014/04/04/problems-with-the-installation-of-sql-server-2014-after-the-windows-8-1-update/

http://mssqlwiki.com/tag/and-it-is-not-possible-to-create-a-self-signed-certificate/

Great post on how to setup TLS

http://www.oceanbytes.org/2015/03/23/making-the-grade-with-ssl/

Make sure you check the certificate is setup properly.

SQL SSL Check ( Use below link to check validity of the SSL available on the SQL Server)

Zip file contains an .exe file download and run in cmd window.

https://drive.google.com/file/d/0B_77YgBEyf5mQXh4WEcxLWtCcnc/view?usp=sharing

Convert Data Table to HTML Table with PowerShell and more

December 25, 2015December 25, 20151 Comment

##function to convert any datatable to html table

function Convert-DatatablHtml ( $dt)

{ 

$html = "<!DOCTYPE html>
<html>
<head>
<style>
table, th, td {
    border: 1px solid black;
    border-collapse: collapse;
}
</style>
</head>
<body>
<table border=""1"" 

style=""width:20%"">";
$hmtl +="<tr>"
for($i = 0;$i -lt $dt.Columns.Count;$i++)
{
$html += "<td>"+$dt.Columns[$i].ColumnName+"</td>"  
}
$html +="</tr>"

for($i=0;$i -lt $dt.Rows.Count; $i++)
{
$hmtl +="<tr>"
 for($j=0; $j -lt $dt.Columns.Count; $j++)
 {
  
  $html += "<td>"+$dt.Rows[$i][$j].ToString()+"</td>"
 }
 $html +="</tr>"
}

$html += "</table>
</body>
</html>"

return $html

}

##How it works 

##creating a sample databtale to test
$dt = New-Object "System.Data.DataTable"


$dt.Columns.Add("id",[int]);
$dt.Columns.Add("value",[string]);

$dtnew = $dt.NewRow();
$dtnew.id =1;
$dtnew.value="test";
$dt.Rows.Add($dtnew)
$dtnew = $dt.NewRow();
$dtnew.id =1;
$dtnew.value="test";
$dt.Rows.Add($dtnew)

##Converting data table to html

$h = Convert-DatatablHtml -dt $dt 

$h


##writing html table to a html file 

$h |out-file "c:\temp\test.html"


##Send an email with with formated html 


Send-MailMessage  -to "to@yahoo.com" -SmtpServer "smtpserver" -body $h -Subject test -From "from@yahoo.com" -BodyAsHtml

Remove old backup files with PowerShell

December 25, 2015Leave a comment

Note: script looks at when file was last modified and also removes if any file exists in sub folders recursively.

$path="C:\app"
$NumDaysToRetain=3
$extension ="*.bak"

Get-ChildItem -Path $path -Filter $extension  -Recurse  | 
Where-Object{ $_.lastwritetime -lt (get-date).adddays(-$NumDaysToRetain)}  | 
ForEach-Object { Remove-item $_.FullName; write-host "File" $_.FullName "deleted"}

Powershell To Deploy Sql scripts in a Directory

December 4, 2015December 4, 2015Leave a comment

Source Code

[CmdletBinding()]
Param(
  [Parameter(Mandatory=$True,ValueFromPipeline=$true,HelpMessage="Enter Source Instance")]
  [ValidateScript({ IF( $_ -eq $null) 
		{$false }  
	    elseif ($_.ToString().Trim().Length -eq  0 )
		{$false}
	    else {$true}} )]
   [string]$TargetInst,
  [Parameter(Mandatory=$True,ValueFromPipeline=$true,HelpMessage="Enter Source Source")]
  [ValidateScript({ IF( $_ -eq $null) 
		{$false }  
	    elseif ($_.ToString().Trim().Length -eq  0 )
		{$false}
	    else {$true}} )]
   [string]$TargetDb,
   [Parameter(Mandatory=$True,ValueFromPipeline=$true)]
   [string]$Targetdir

)

## Add Assemblies
add-type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, 
PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop
Write-host $TargetInst $Targetdb $targetdir
$sourcedbs = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $TargetInst 
$sourcedbs.ConnectionContext.StatementTimeout = 0
$db = $sourcedbs.Databases[$TargetDB]
$files = Get-ChildItem -Path "$Targetdir"  -Recurse  | Where-Object{$_.Name -Like "*.sql"} | select FullName
Foreach($file in $files) 
{
$query= Get-Content $file.FullName | Out-String ; 
	try
	{
		$db.ExecuteNonQuery($query)
		Write-Host $file.FullName "Query Finished"     
    }
      catch [Exception]
 	{

		Write-Host $_.Exception.GetType().FullName, $_.Exception.Message
	}
}

How to use it

.\ExecuteSqlscripts.ps1 ServerName DatabaseName Directory

Note: it does a recursive check if there any folders it gets them all as well.

SQL Log : A significant part of sql server process memory has been paged out

February 9, 2015February 9, 2015Leave a comment

If you seeing this message “A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 1851 seconds. Working set (KB): #####, committed (KB): #####, memory utilization: ##%. ”

What does it mean: System is running low on memory basically windows is moving the allocated objects from memory into a paged file to deal with memory pressure.

Is SQL Server taking way too much memory? : May be not this is known behavior of windows to trim down the memory used by the current process so it can allot memory to new requests.

SQL Server has mechanism in place to use low resource memory notification it uses a function called CreateMemoryResourceNotification to create a system wide memory notification if windows detects low memory it sends out a notification with a signal 0 (available physical memory is running low ) or 1 (available physical memory is high) based on the result SQL Server reacts accordingly by performing the necessary action increasing the buffer pool or decreasing it. Note: if neither of these are signaled SQL Server maintains a constant memory foot print.

If SQL Sever has a mechanism to deal with low memory why is windows moving the allocated in memory objects to page file?

Can be many reasons for it

Windows might not be sending the notifications in appropriate instances or SQL Sever might not be receiving those events.
SQL Server might not be responding in timely manner may be too slow

When SQL Server receives low physical memory notification it will scale down its memory usage by trimming internal caches. This effect is applied for 5 seconds and then paused for 1 minute. This is to avoid any hungry/faulty application to consume all the memory making SQL Server to scale its usage continuously. If low memory conditions still exist after 1 minute pause, the effect is applied again. So if there is physical memory pressure even after SQL Server scaling its usage for 5 seconds windows will still page out SQL Server’s working set.

We can query these memory notifications in ring buffer

SELECT
CONVERT (varchar(30), GETDATE(), 121) as runtime,
DATEADD (ms, a.[Record Time] – sys.ms_ticks, GETDATE()) AS Notification_time,
a.* ,
sys.ms_ticks AS [Current Time]
FROM
(SELECT x.value(‘(//Record/ResourceMonitor/Notification)[1]’, ‘varchar(30)’) AS [Notification_type],
x.value(‘(//Record/MemoryRecord/MemoryUtilization)[1]’, ‘int’) AS [MemoryUtilization %],
x.value(‘(//Record/MemoryRecord/TotalPhysicalMemory)[1]’, ‘bigint’) AS [TotalPhysicalMemory_KB],
x.value(‘(//Record/MemoryRecord/AvailablePhysicalMemory)[1]’, ‘bigint’) AS [AvailablePhysicalMemory_KB],
x.value(‘(//Record/MemoryRecord/TotalPageFile)[1]’, ‘bigint’) AS [TotalPageFile_KB],
x.value(‘(//Record/MemoryRecord/AvailablePageFile)[1]’, ‘bigint’) AS [AvailablePageFile_KB],
x.value(‘(//Record/MemoryRecord/TotalVirtualAddressSpace)[1]’, ‘bigint’) AS [TotalVirtualAddressSpace_KB],
x.value(‘(//Record/MemoryRecord/AvailableVirtualAddressSpace)[1]’, ‘bigint’) AS [AvailableVirtualAddressSpace_KB],
x.value(‘(//Record/MemoryNode/@id)[1]’, ‘int’) AS [Node Id],
x.value(‘(//Record/MemoryNode/ReservedMemory)[1]’, ‘bigint’) AS [SQL_ReservedMemory_KB],
x.value(‘(//Record/MemoryNode/CommittedMemory)[1]’, ‘bigint’) AS [SQL_CommittedMemory_KB],
x.value(‘(//Record/@id)[1]’, ‘bigint’) AS [Record Id],
x.value(‘(//Record/@type)[1]’, ‘varchar(30)’) AS [Type],
x.value(‘(//Record/ResourceMonitor/Indicators)[1]’, ‘int’) AS [Indicators],
x.value(‘(//Record/@time)[1]’, ‘bigint’) AS [Record Time]
FROM (SELECT CAST (record as xml) FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = ‘RING_BUFFER_RESOURCE_MONITOR’) AS R(x)) a
CROSS JOIN sys.dm_os_sys_info sys
ORDER BY DATEADD (ms, a.[Record Time] – sys.ms_ticks, GETDATE())

We can also increase the LowMemoryThreshold value so the OS will notify applications such as SQL on low memory conditions much earlier and SQL Server can respond to memory pressure much early before the system is starving for memory.

How to set the LowMemoryThreshold value (in MB)?

In Regedit -> go to

HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\SessionManager\MemoryManagement

Right click on the right pane,

Select New -> select click DWORD Value -> enter LowMemoryThreshold

Double Click LowMemoryThreshold -> value (choose decimal) -> 512

System Reboot is required to take effect.

https://msdn.microsoft.com/en-us/library/windows/desktop/aa366541(v=vs.85).aspx

http://blogs.msdn.com/b/karthick_pk/archive/2012/06/22/a-significant-part-of-sql-server-process-memory-has-been-paged-out.aspx

Standard SQL Server Error log procedure

January 22, 2015Leave a comment

we spend lot of time trying to look at log files when we run any particular SQl files have a standard frame work to collect these errors and store in table, amkes the application management simple at same time provides some insights into exceptions over the time

IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'wm_ErrorLog')
DROP TABLE [wm_ErrorLog]
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'WMSp_ErrorLog_Ins_Error' AND type ='P')
DROP PROCEDURE [WMSp_ErrorLog_Ins_Error]
CREATE TABLE [wm_ErrorLog] (
    [Error_id] INT IDENTITY(1,1)
	,[Error_Number] [INT] NOT NULL
	,[Error_procedure] VARCHAR(max) NOT NULL
	,[Error_Message] [VARCHAR](MAX) NULL
	,[Error_Line] INT
	,[Error_State] INT 
	,[Error_Severity] INT 
	,[SPID] [INT] NULL
	,[Program_Name] [VARCHAR](255) NULL
	,[Client_Address] [VARCHAR](255) NULL
	,[Authentication] [VARCHAR](50) NULL
	,[Error_User_Application] [VARCHAR](100) NULL
	,[Error_Date] [DATETIME] NULL
	,[Error_User_System] [sysname] NOT NULL
	) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

SET ANSI_PADDING OFF
GO

ALTER TABLE wm_ErrorLog ADD CONSTRAINT [dfltErrorLog_error_date] DEFAULT(GETDATE())
FOR [Error_Date]
GO

ALTER TABLE wm_ErrorLog ADD CONSTRAINT [dfltErrorLog_error_user_system] DEFAULT(SUSER_SNAME())
FOR [Error_User_System]
GO

ALTER TABLE [wm_ErrorLog] ADD CONSTRAINT wmpk_wm_errorlog_id PRIMARY KEY (error_id)

GO


Create  PROCEDURE [WMSp_ErrorLog_Ins_Error] (
	 @Error_Number INT = NULL
	,@Error_procedure SYSNAME = 'Ad hoc sql'
	,@Error_Message VARCHAR(4000) = NULL
	,@UserID INT = NULL
	)
AS
BEGIN
	BEGIN TRY
		INSERT INTO wm_ErrorLog (
			[Error_Number]
			,[Error_procedure]
			,[Error_Message]
			,[Error_line]
			,[Error_state]
			,[Error_severity]
			,[SPID]
			,[Program_Name]
			,[Client_Address]
			,[Authentication]
			,[Error_User_System]
			,[Error_User_Application]
			)
		SELECT [Error_Number] = ISNULL(@Error_Number, ERROR_NUMBER())
			,[Error_procedure] = COALESCE(ERROR_PROCEDURE(),@Error_procedure)
			,[Error_Message] = ISNULL(@Error_Message, ERROR_MESSAGE())
			,[Error_line] = ERROR_LINE()
			,[Error_state] =ERROR_STATE()
			,[Error_severity] = ERROR_SEVERITY()
			,[SPID] = @@SPID
			,[Program_Name] = ses.program_name
			,[Client_Address] = con.client_net_address
			,[Authentication] = con.auth_scheme
			,[Error_User_System] = SUSER_SNAME()
			,[Error_User_Application] = @UserID
		FROM sys.dm_exec_sessions ses 
		LEFT JOIN sys.dm_exec_connections con ON con.session_id = ses.session_id
		WHERE ses.session_id= @@SPID
	END TRY

	BEGIN CATCH
		-- We even failed at the log entry so let's get basic  
		INSERT INTO wm_ErrorLog (
			ERROR_NUMBER
			,ERROR_procedure
			,ERROR_MESSAGE
			)
		VALUES (
			- 100
			,OBJECT_NAME(@@PROCID)
			,ERROR_MESSAGE()
			)
	END CATCH
END
GO

Above scripts creates two objects a table and procedure

Here is how we should plan to use

BEGIN TRY
RAISERROR(‘error’,16,1)
END TRY
BEGIN CATCH
EXECUTE [WMSp_ErrorLog_Ins_Error]
END CATCH

SELECT * FROM wm_errorlog

How to Fix :: OLE DB provider “SQLNCLI10” for linked server returned message “Query timeout expired”.

January 2, 2015Leave a comment

by default a remote query (Query using Linked Server) expires in 10 mins. We can find this value in SQL Server using below queries

SELECT * FROM sys.sysconfigures
WHERE comment ='remote query timeout'

--or 

SELECT * FROM sys.configurations
WHERE name='remote query timeout (s)'

We can find more details in MS doc

http://msdn.microsoft.com/en-us/library/ms189631.aspx

in order to change it we have to sp_configure and we don’t need to restart the database engine

sp_configure 'remote query timeout',3600
go 
reconfigure with override 
go

This will set the time out to 3600 seconds/ 1 hr

Sqljunkieshare

Category: Administration