SQL Server – CLR – List Files in a Folder

Background

This is something that I have wanted to address for so very long.

The inability to consume a huge batch of files into SQL Server made my need even more pressing than it ever was.

 

Googled

Googled for help and found Diana Moldovan already did the work.

That work is published on MSSQLTips.com.

And, it is available here:

Introduction to SQL Server CLR table valued functions
Link

Code

C#

folderList.cs


using System;
using System.IO;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
	
    private class FileProperties
    {
        public SqlString   fileName;
		public SqlString   fullName;
		public SqlString   extension;
		
        public SqlInt64    fileSize;
		
        public SqlDateTime creationTime;
        public SqlDateTime creationTimeUtc;
		
		public SqlDateTime lastWriteTime;
		public SqlDateTime lastWriteTimeUtc;
		
		//constructor
        public FileProperties
		(
		
			  SqlString fileName
			, SqlString fullName  
			, SqlString extension
			
			, SqlInt64  fileSize
			
			, SqlDateTime creationTime
			, SqlDateTime creationTimeUtc			
			
			, SqlDateTime lastWriteTime
			, SqlDateTime lastWriteTimeUtc						
			
		)
        {
            this.fileName = fileName;
			this.fullName = fullName;
			this.extension = extension;
			
            this.fileSize = fileSize;
			
            this.creationTime = creationTime;
            this.creationTimeUtc = creationTimeUtc;			
			
            this.lastWriteTime = lastWriteTime;
            this.lastWriteTimeUtc = lastWriteTimeUtc;						
			
        } //end constructor
		
    } // end class
	
	
    //The SqlFunction attribute tells Visual Studio to register this 
    //code as a user defined function
    [Microsoft.SqlServer.Server.SqlFunction
		(
			  // see implementation below	
			  FillRowMethodName 
				= "fillRow"
				
			// DDL for recordset that will be returned to caller	
			, TableDefinition
				= @"
				
						fileName 				nvarchar(500)
					  , fullName 				nvarchar(4000)
					  , extension 				nvarchar(30)
					  
					  , fileSize 				bigint
					 
					  , creationTime 			datetime
					  , creationTimeUtc 		datetime

					  , lastWriteTime 			datetime
					  , lastWriteTimeUtc 		datetime
					  
				   "	
					
		)
	]
    public static IEnumerable BuildFileNamesArray
	(
		  string targetDirectory
		, string searchPattern
	)
    {
		
		FileInfo[] objFiles = null;
			
		ArrayList objFilePropertiesCollection = null;
            
		DirectoryInfo objDirectoryInfo = null;
		
        try
        {
		
			//instanciate array	
            objFilePropertiesCollection = new ArrayList();

			// instanciate directory info - pass along target directory
			objDirectoryInfo = new DirectoryInfo(targetDirectory);
            
			//get files whose name match pattern
			objFiles = objDirectoryInfo.GetFiles(searchPattern);
			
            //for each file in collection, add to array
			foreach (FileInfo objFileInfo in objFiles)
            {
				
				FileProperties objFileProperties = null;
				
				objFileProperties
					= new FileProperties
						(
						
							  objFileInfo.Name
							, objFileInfo.FullName
							, objFileInfo.Extension
							
							, objFileInfo.Length
							
							, objFileInfo.CreationTime
							, objFileInfo.CreationTimeUtc	
							
							, objFileInfo.LastWriteTime
							, objFileInfo.LastWriteTimeUtc
							
						);
				
                //I'm adding to the colection the properties (FileProperties) 
                //of each file I've found  
                objFilePropertiesCollection.Add( objFileProperties	);
				
            } //for each
			
			//return array
            return objFilePropertiesCollection;
			
        }
        catch (Exception)
        {
			
            return null;
        }
		
    }
	
    
	//FillRow method. The method name has been specified above as 
    //a SqlFunction attribute property
    public static void fillRow
	(
		  object objFileProperty
		  
		, out SqlString fileName
		, out SqlString fullName
		, out SqlString extension
		
		, out SqlInt64 fileSize
		
		, out SqlDateTime creationTime
		, out SqlDateTime creationTimeUtc		
		
		, out SqlDateTime lastWriteTime
		, out SqlDateTime lastWriteTimeUtc				
	)
    {
        //I'm using here the FileProperties class defined above
        FileProperties fileProp = (FileProperties)objFileProperty;
		
        fileName = fileProp.fileName;
		fullName = fileProp.fullName;
		extension = fileProp.extension;
		
        fileSize = fileProp.fileSize;
		
        creationTime = fileProp.creationTime;
        creationTimeUtc = fileProp.creationTimeUtc;		
		
        lastWriteTime = fileProp.lastWriteTime;
        lastWriteTimeUtc = fileProp.lastWriteTimeUtc;				
		
    }
	
};

properties\AssemblyInfo.cs


using System.Reflection;
using System.Runtime.CompilerServices;
using System.Runtime.InteropServices;

// General Information about an assembly is controlled through the following 
// set of attributes. Change these attribute values to modify the information
// associated with an assembly.
[assembly: AssemblyTitle("folderList")]
[assembly: AssemblyDescription("")]
[assembly: AssemblyConfiguration("")]
[assembly: AssemblyCompany("")]
[assembly: AssemblyProduct("folderList")]
[assembly: AssemblyCopyright("Copyright © Microsoft 2017")]
[assembly: AssemblyTrademark("")]
[assembly: AssemblyCulture("")]

// Setting ComVisible to false makes the types in this assembly not visible 
// to COM components. If you need to access a type in this assembly from 
// COM, set the ComVisible attribute to true on that type.
[assembly: ComVisible(false)]

// The following GUID is for the ID of the typelib if this project is exposed to COM
[assembly: Guid("2957bccd-03ed-4ff3-868d-8a07226516d7")]

// Version information for an assembly consists of the following four values:
//
// Major Version
// Minor Version 
// Build Number
// Revision
//
// You can specify all the values or you can default the Build and Revision Numbers 
// by using the '*' as shown below:
// [assembly: AssemblyVersion("1.0.*")]
[assembly: AssemblyVersion("1.0.0.0")]
[assembly: AssemblyFileVersion("1.0.0.0")]

Project File

folderList.csproj


<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="14.0" DefaultTargets="Build" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <Import Project="$(MSBuildExtensionsPath)\$(MSBuildToolsVersion)\Microsoft.Common.props" Condition="Exists('$(MSBuildExtensionsPath)\$(MSBuildToolsVersion)\Microsoft.Common.props')" />
  <PropertyGroup>
    <Configuration Condition=" '$(Configuration)' == '' ">Debug</Configuration>
    <Platform Condition=" '$(Platform)' == '' ">AnyCPU</Platform>
    <ProjectGuid>{2957BCCD-03ED-4FF3-868D-8A07226516D7}</ProjectGuid>
    <OutputType>library</OutputType>
    <AppDesignerFolder>Properties</AppDesignerFolder>
    <RootNamespace>folderList</RootNamespace>
    <AssemblyName>folderList</AssemblyName>
    <TargetFrameworkVersion>v4.5.2</TargetFrameworkVersion>
    <FileAlignment>512</FileAlignment>
    <AutoGenerateBindingRedirects>true</AutoGenerateBindingRedirects>
  </PropertyGroup>
  <PropertyGroup Condition=" '$(Configuration)|$(Platform)' == 'Debug|AnyCPU' ">
    <PlatformTarget>AnyCPU</PlatformTarget>
    <DebugSymbols>true</DebugSymbols>
    <DebugType>full</DebugType>
    <Optimize>false</Optimize>
    <OutputPath>bin\Debug\</OutputPath>
    <DefineConstants>DEBUG;TRACE</DefineConstants>
    <ErrorReport>prompt</ErrorReport>
    <WarningLevel>4</WarningLevel>
  </PropertyGroup>
  <PropertyGroup Condition=" '$(Configuration)|$(Platform)' == 'Release|AnyCPU' ">
    <PlatformTarget>AnyCPU</PlatformTarget>
    <DebugType>pdbonly</DebugType>
    <Optimize>true</Optimize>
    <OutputPath>bin\Release\</OutputPath>
    <DefineConstants>TRACE</DefineConstants>
    <ErrorReport>prompt</ErrorReport>
    <WarningLevel>4</WarningLevel>
  </PropertyGroup>
  <ItemGroup>
	
	<Reference Include="System" />
	<Reference Include="System.Configuration" />
    <Reference Include="System.Core" />
    <Reference Include="System.Xml.Linq" />
    <Reference Include="System.Data.DataSetExtensions" />
    <Reference Include="Microsoft.CSharp" />
    <Reference Include="System.Data" />
    <Reference Include="System.Net.Http" />
    <Reference Include="System.Xml" />
  
  </ItemGroup>
  
  <ItemGroup>
    <Compile Include="folderList.cs" />
    <Compile Include="Properties\AssemblyInfo.cs" />
  </ItemGroup>
  
  <ItemGroup>
    <None Include="packages.config" />
  </ItemGroup>
  
  <Import Project="$(MSBuildToolsPath)\Microsoft.CSharp.targets" />
  <!-- 
  
	  To modify your build process, add your task inside one of the targets below and uncomment it. 
      Other similar extension points exist, see Microsoft.Common.targets.
	
	  <Target Name="BeforeBuild">
	  </Target>
	  <Target Name="AfterBuild">
	  </Target>
	  
  -->
</Project>

Command Files

buildFL.cmd


set "_appFolder=C:\Program Files (x86)\MSBuild\14.0\Bin\"

"%_appFolder%\msbuild" folderList.csproj /t:Rebuild 

Compile

Task

Compile the file and if everything goes well, you will get the resultant files in the bin\Debug folder.

BTW, to compile access command line and invoke buildFL.com

 

Deploy

Let us deploy to SQL Server.

prepareDB.cmd

Outline

  1. Enable CLR at the Instance Level
  2. On the Database that you will be using the function, set TRUSTWORTHY on

Code


use [tempdb]
go

use [DBLab]
go

declare @sql varchar(600)
declare @FORMAT_SQL_SETDATABASETRUSTWORTHYON varchar(800)
declare @database sysname

set @FORMAT_SQL_SETDATABASETRUSTWORTHYON
		= ' ALTER DATABASE [%s] set TRUSTWORTHY on;'

if exists
(
	select *
	from   sys.configurations tblSC
	where  tblSC.[name] = 'clr enabled'
	and    tblSC.[value] = 0
)
begin

	print 'Enabling CLR'

	exec sp_configure 'clr enabled', 1

	reconfigure with override

	print 'CLR Enabled'

end


/*
	On current database, set trustworthy on
*/
set @database = db_name()

exec master.dbo.xp_sprintf
	  @sql output
	, @FORMAT_SQL_SETDATABASETRUSTWORTHYON
	, @database


if exists
(
	select *
	from   sys.databases tblSD
	where  tblSD.[name] = db_name()
	and    tblSD.is_trustworthy_on = 0
)
begin

	print 'Issued ' + @sql

	exec(@sql)

end
go


 

Teardown existing function & Dll

Outline

  1. Drop Table Value Function if it exists
  2. Drop Assembly/Dll if already registred

Code



if object_id('[dbo].[ITVF_getFiles]') is not null
begin

	drop FUNCTION [dbo].[ITVF_getFiles]

end
go


if exists 
(
	select * 
	from   sys.assemblies
	where  [name] = 'folderList'
)
begin

	DROP ASSEMBLY [folderList]

end

 

Create Assembly & Create Function

Outline

  • Create Assembly
  • Create Function

Code



CREATE ASSEMBLY [folderList]
AUTHORIZATION [dbo]
FROM 'C:\Personal\dadeniji\Blog\Microsoft\SQLServer\CLR\folderListing\folderList\bin\Debug\folderList.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS

GO

CREATE FUNCTION [dbo].[ITVF_getFiles]
(
	  @targetDirectory   nvarchar(255)
	, @searchPattern	 nvarchar(255)
)
RETURNS TABLE
(
	  [fileName]		 nvarchar(500)
    , fullName			 nvarchar(4000)
	, [extension]		 nvarchar(60)

	, fileSize			 bigint
	, creationTime		 datetime
	, creationTimeUtc	 datetime
	, lastWriteTime		 datetime
	, lastWriteTimeUtc   datetime
)
WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [folderList].[UserDefinedFunctions].[BuildFileNamesArray]
GO


Test

Let us test via SQL.

Test Sample – 001

Code


declare @targetDirectory   nvarchar(255)
declare @searchPattern	   nvarchar(255)

set @targetDirectory = 'C:\Microsoft\SQLServer\ExtendedEvents\LockTimeOut'
set @searchPattern = '*.xel'

select *
from   [dbo].[ITVF_getFiles]
		(
				@targetDirectory
			, @searchPattern
		)


Output

 

Version Control

Github

DanielAdeniji/SQLServerCLRFileSystemListFiles
Link

Dedicated

Dedicated to Diana Moldovan

 

References

  1. Blog
    • Diana Moldovan
      • Introduction to SQL Server CLR table valued functions
        Link
      • Converting UTC to local time
        Link
  2. Reference – MSFT
    • Docs / S​QL / Relational databases / C​LR integration / Database objects / User defined functions (​UD​F)
      • CLR Table value Function
        Link
  3. .Net File
      • How to: Get Information About Files, Folders, and Drives (C# Programming Guide)
        Link
      • FileInfo Class
        Link
  4. Q/A
    • Stack Overflow
      • Creating a CLR Table Valued Function
        Link

SQL Server – Review Database File Growth

Prelude

Database Administrators ( DBAs) who grew up on mainframe, mini, and Unix databases like to tend to their database and grow it during carefully planned schedules.

SQL Server Admins might not have that luxury as their database is supposed to be auto-managed.

 

Metadata

Let us determine whether the data and log files are left at their default, or have been adjusted to grow at tuned values.

 

Function

dbo.itvf_DatabaseFileGrowth

Code


use [master]
go

/*
	drop function [dbo].[itvf_DataDatabaseFileGrowth]
*/

if object_id('[dbo].[itvf_DatabaseFileGrowth]') is null
begin

	exec('create function itvf_DatabaseFileGrowth() returns table return select [shell] = 1/0 ')

end
go

alter function [dbo].[itvf_DatabaseFileGrowth]
(
	  @fileGrowthMBData int = 200 
	, @fileGrowthMBLog  int = 200 
)
returns table

return
	(

	/*
		sys.databases
		https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-databases-transact-sql

		Note: A database that has just come online is not necessarily ready to accept connections. 
		To identify when a database can accept connections, query the collation_name column of sys.databases or the Collation property of DATABASEPROPERTYEX. 
		The database can accept connections when the database collation returns a non-null value. 
		For Always On databases, query the database_state or database_state_desc columns of sys.dm_hadr_database_replica_states.

	*/
	with cteDatabase
	(
		  [dbid]
		, [dbname]
		, [collationName]
	)
	as
	(
		select 
				  tblSD.[database_id]
				, tblSD.[name]
				, tblSD.[collation_name]

		from   sys.databases tblSD
		where  (
						(tblSD.[is_read_only] = 0)

					and (tblSD.collation_name is not null)

			   )
	)

	select 
			  cteD.[dbname]

			, [fileSymbolicName]
				= tblMF.[name]

			, [filePhysicalName]
				= tblMF.[physical_name]

			, [fileType]
				= tblMF.[type_desc]

			, [growthIsInPercent]
				= case (is_percent_growth)
						when 1 then 'Y'
						else 'N'
				  end

			, [growth%] = 
					case
						when ( tblMF.is_percent_growth = 1) 
							then cast( tblMF.[growth] as varchar(10)) + '%'
						else null
					end

			, [growthKB] = 
					case
						when ( tblMF.is_percent_growth = 1) then null
						else (tblMF.[growth] * 8)
					end

			, [growthMB] = 
					case
						when ( tblMF.is_percent_growth = 1) then null
						else (tblMF.[growth] * 8) / ( 1024)
					end

			, [script]
				= 'ALTER DATABASE '
					+ quoteName( cteD.dbname )
					+ ' MODIFY FILE '
					+ ' ( '
					+	'	  NAME = ' + cast( tblMF.[name] as varchar(60))
					+	'	, FILEGROWTH = ' 
					+ case tblMF.[type_desc]

							when 'ROWS' 
								then cast(@fileGrowthMBData as varchar(10)) + 'MB'

							when 'LOG' 
								then cast(@fileGrowthMBLog as varchar(10)) + 'MB'
					  end
					+ ' ) '
					+ ';'

	from   cteDatabase  cteD

	inner join sys.master_files tblMF

		on cteD.[dbid] = tblMF.[database_id]

	where  tblMF.[state_desc] = 'ONLINE'

	and    tblMF.[is_read_only] = 0

	and
			(

				   -- is percent growth
				   ( tblMF.is_percent_growth = 1)


				-- Data Files
				or(
				
					 (

						/*
							If is_percent_growth = 0, growth increment is in units of 8-KB pages, rounded to the nearest 64 KB
						*/
						(
							    ( tblMF.[type_desc] = 'ROWS')
							and	( tblMF.is_percent_growth = 0)
							and ( 
									(tblMF.[growth] * 8) 
										/ 1024
								) != @fileGrowthMBData
						)

				    ) -- ROWS

				-- Log Files
				or (

						/*
							If is_percent_growth = 0, growth increment is in units of 8-KB pages, rounded to the nearest 64 KB
						*/
						(
							    ( tblMF.[type_desc] = 'LOG')
							and	( tblMF.is_percent_growth = 0)
							and ( 
									(tblMF.[growth] * 8) 
										/ 1024
								) != @fileGrowthMBLog
						)

				   ) -- LOG

				)  -- DATA OR LOG
			
			) -- end where clause

	) -- end function

go


Execute Select Against Function


	declare @fileGrowthMBData int = 200 
	declare @fileGrowthMBLog int = 200 

	set @fileGrowthMBData = 200 
	set @fileGrowthMBLog = 200

	select *
	from   [dbo].[itvf_DatabaseFileGrowth]
	(
		      @fileGrowthMBData
			, @fileGrowthMBLog
	)
	order by 
			  [dbname]
			, [fileSymbolicName]
			, [script]


Output

 

Explanation

  1. The requested size is 200MB
    • If a file is not set at that value, the script column will reflect the SQL that should be ran to align

 

Review

Review Log File

Let us review how our files have been growing thus far.

Detail

dbo.itvf_TraceFileParseDatabaseFileGrowthDetail

Code


use [master]
go

if object_id('[dbo].[itvf_TraceFileParseDatabaseFileGrowthDetail]') is null
begin

	exec('create function [dbo].[itvf_TraceFileParseDatabaseFileGrowthDetail]() returns table return select [shell] = 1/0 ')

end
go

alter function [dbo].[itvf_TraceFileParseDatabaseFileGrowthDetail]
(
)
returns table

return
	(
		SELECT TOP 100 PERCENT

			  [startTime] 
					= convert(varchar(30), ftg.StartTime, 100)

			, [eventName]
				 = te.name

			, [databaseName] = DB_NAME(ftg.databaseid)

			, [filename] 
				= ftg.[filename]

			, [growthMB]
				 = (ftg.IntegerData*8)/1024.0 

			, [durationMillisecond]
				 = (ftg.duration/1000)


	FROM ::fn_trace_gettable
		(
			  REPLACE(CONVERT(VARCHAR(1000),SERVERPROPERTY('ErrorLogFileName')), '\ERRORLOG','\log.trc')
			, DEFAULT
		) AS ftg 

	INNER JOIN sys.trace_events AS te 

		ON ftg.EventClass = te.trace_event_id  

	WHERE 
		(
			   ( ftg.EventClass = 92)  -- Date File Auto-grow
			OR ( ftg.EventClass = 93) -- Log File Auto-grow
		)

	order by
			ftg.[StartTime] desc

	) -- end function

go


 

Output

 

Explanation

  1. Because of Instant File Initialization data file growths will take less time relative to log file growths
    • Logs are cyclical and have to be zeroed

 

dbo.itvf_TraceFileParseDatabaseFileGrowthSummary

Code


use [master]
go

if object_id('[dbo].[itvf_TraceFileParseDatabaseFileGrowthSummary]') is null
begin

	exec('create function [dbo].[itvf_TraceFileParseDatabaseFileGrowthSummary]() returns table return select [shell] = 1/0 ')

end
go

alter function [dbo].[itvf_TraceFileParseDatabaseFileGrowthSummary]
(
)
returns table

return
	(

		with cte
		(
			  [startTime] 
			, [eventName]
			, [databaseName] 
			, [filename] 
			, [growthMB]
			, [durationMillisecond]

		)
		as
		(

			SELECT 
					  [startTime] = ftg.StartTime

					, [eventName]
						 = te.name

					, [databaseName] 
						= DB_NAME(ftg.databaseid)

					, [filename] 
						= ftg.[filename]

					, [growthMB]
						 = (ftg.IntegerData*8)/1024.0 

					, [durationMillisecond]
						 = (ftg.duration/1000)


			FROM ::fn_trace_gettable
			(
				  REPLACE(CONVERT(VARCHAR(1000),SERVERPROPERTY('ErrorLogFileName')), '\ERRORLOG','\log.trc')
				, DEFAULT
			) AS ftg 


			INNER JOIN sys.trace_events AS te 

				ON ftg.EventClass = te.trace_event_id  


			WHERE 
				(
					   ( ftg.EventClass = 92)  -- Date File Auto-grow
					OR ( ftg.EventClass = 93) -- Log File Auto-grow
				)

		)
		select 
				  TOP 100 PERCENT

				  [databaseName] 

				, [eventName]

				, [numberofIncrements]	
					= count(*)

				, [growthMB]
					= cast
						(
							sum([growthMB])
							as decimal(12, 2)
						)

				, [durationMillisecondAvg]
					= avg([durationMillisecond])

				, [durationMillisecondSum]
					= sum([durationMillisecond])

				, [durationSecondSum]
					= (sum([durationMillisecond]) / 1000)

				, [startTimeMin] 
					= convert
						(
							  varchar(30)
							, min([startTime])
							, 100
						)

				, [startTimeMax] 
					= convert
					  (
							  varchar(30)
							, max([startTime])
							, 100
						)

		from   cte

		group by

			  [eventName]
			, [databaseName] 

		order by
				  [growthMB] desc

	)
go


 

Output

 

Credits

Crediting Greg Larsen for the code on how to query the default trace file to identify database file growths.

 

Summary

Please review your data and log file growth settings.

It is best to have a scheduled script that checks and grows them as they near your high water marks.

Unless you are stressed for storage, please do not have AutoShrink enabled.

 

References

  1. Greg Larsen
    • SQL Server Database Growth and Autogrowth Settings
      Link

 

HangFire – SQLServer – “Lock Timeouts”

Background

When running an Application that relies on SQL Server has a backend it is a good idea to profile the application.

Profiling Tools

We use SQL Server Activity Monitor, Extended Events, and SQL Server Profiler to do so.

Activity Monitor

Image

 

Explanation

  1. Resource Waits
    • Lock = 46742
    • Network I/O = 2429
    • Backup = 1467
    • Buffer I/O = 1151
    • Logging = 705
    • Buffer Latch = 216

 

Extended Events

Live Data

Image

 

Tabulate

Field Value
 sql_text (@queues1 nvarchar(4000))
delete top (1) JQ output DELETED.Id, DELETED.JobId, DELETED.Queue
from [HangFire].JobQueue JQ with (readpast, updlock, rowlock, forceseek)
where Queue in (@queues1)

 

 

Explanation

  1. Fetch data from HangFire.JobQueue
  2. Hint
    • Hint Locks
      • readpast
        • If another session has the row locked, then skip it and go on to the next one
      • updlock
        • Request an Update Lock
      • rowlock
        • The lock granularity should be Row
      • forceseek
        • Seek rather than scan

Findings

  1. In an hour, 60 Minutes, session
    • We identified the following errors
      • error_reported=12
      • lock_timeout=78

TroubleShooting

Query

The identified query

(@queues1 nvarchar(4000))
delete top (1) JQ output DELETED.Id, DELETED.JobId, DELETED.Queue
from [HangFire].JobQueue JQ with (readpast, updlock, rowlock, forceseek)
where Queue in (@queues1)

is used to poll the Queue.

By default it runs 15 seconds.

 

Remediation

Polling Interval

Increase Pooling Interval

Configuration

app.config
   <appSettings>
   
    <add key="maxDurationInMinutes" value="60" />

    <add key="queuePollInterval" value="60" />
		
  </appSettings>

c# – Read Configuration file

Read queuePollInterval entry from App.Config file


static private void getAppConfigQueuePollInterval()
{
	
	String strQueuePollInterval;
	bool    bRC = false;
	
	
	if (ConfigurationManager.AppSettings["queuePollInterval"] != null)
	{
		
		strQueuePollInterval = ConfigurationManager.AppSettings["queuePollInterval"];
		
		try
		{
			bRC = Int32.TryParse(strQueuePollInterval, out iQueuePollInterval);
			
			Console.WriteLine("queuePollInterval :- " + strQueuePollInterval);					
			
		}
		catch			
		{
		
		}
		
	} //if (ConfigurationManager.AppSettings["queuePollInterval"] != null)
	
} //getAppConfigQueuePollInterval()

c# – Change Polling Interval from default of 15 seconds to value indicated in configuration file

Read queuePollInterval entry from App.Config file



SqlServerStorageOptions optSqlServerStorageOptions = new SqlServerStorageOptions();
{

   if (iQueuePollInterval != queuePollIntervalDefault)
   {
		
	//Documentation Configuration Using SQL Server
	//http://docs.hangfire.io/en/latest/configuration/using-sql-server.html
	//QueuePollInterval = TimeSpan.FromSeconds(15) // Default value
	optSqlServerStorageOptions.QueuePollInterval
		= TimeSpan.FromSeconds(iQueuePollInterval);
			
   }	
	
};
		
GlobalConfiguration.Configuration.UseSqlServerStorage
(
	  connectionStringHFID
	, optSqlServerStorageOptions
);

 

Results – Output

Results – Extended Events

Interpretation

We reduced our lockouts to 78 to 65.

And, all our tasks were still completed successfully.

 

Source Control

GitHub

Updated the gitHub repository and here is the URL.

 

Summary

1 test does not make a science, will run numerous tests and come back and update our post.

Changing the Polling Interval came Googling and as always test everything out.

References

  1. Hangfire keeps running SQL queries even when inactive
    Link
  2. Handling long running tasks (+ long invisibility timeout) + server restarts
    Link

HangFire – SQL Server – “Warning: Null value is eliminated by an aggregate or other SET operation.”

Background

In our first two posts, we introduced HangFire using two sample codes.

The Sample codes are :-

  1. Hangfire.io – Installation & Hello World Application
    Link
  2. Hangfire.IO – More Complex Application
    Link

 

In this Post

In this post, we will start exploring the data we captured through Extended Events.

 

Extended Event

Category :- Error Reported

Image

 

Tabulated

 Field Value
 error_number  8153
 message  Warning: Null value is eliminated by an aggregate or other SET operation.
 severity  10
 sql_text  sql_text (@count int)DECLARE @RecordsToAggregate TABLE ( [Key] NVARCHAR(100) NOT NULL, [Value] SMALLINT NOT NULL, [ExpireAt] DATETIME NULL ) SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRAN DELETE TOP (@count) C OUTPUT DELETED.[Key], DELETED.[Value], DELETED.[ExpireAt] INTO @RecordsToAggregate FROM [HangFire].[Counter] C WITH (READPAST, XLOCK, INDEX(0)) SET NOCOUNT ON ;MERGE [HangFire].[AggregatedCounter] WITH (HOLDLOCK) AS [Target] USING ( SELECT [Key], SUM([Value]) as [Value], MAX([ExpireAt]) AS [ExpireAt] FROM @RecordsToAggregate GROUP BY [Key]) AS [Source] ([Key], [Value], [ExpireAt]) ON [Target].[Key] = [Source].[Key] WHEN MATCHED THEN UPDATE SET [Target].[Value] = [Target].[Value] + [Source].[Value], [Target].[ExpireAt] = (SELECT MAX([ExpireAt]) FROM (VALUES ([Source].ExpireAt), ([Target].[ExpireAt])) AS MaxExpireAt([ExpireAt])) WHEN NOT MATCHED THEN INSERT ([Key], [Value], [ExpireAt]) VALUES ([Source].[Key], [Source].[Value], [Source].[ExpireAt]); COMMIT TRAN
 state  1
 user_defined  False

 

 

TroubleShooting

Add Test Data


set nocount on;
set XACT_ABORT on;
go

declare @commit bit
declare @strLog varchar(100)

set @commit =0
set @commit =1

begin tran

	insert into [HangFire].[Counter]
	(
		  [Key]
		, [Value]
		, [ExpireAt]
	)
	select
		1
		, rand() * 100
		, null --rand() * 1000
	union all
	select
		2
		, rand() * 100
		, null --rand() * 1000

	select top 5 *
	from   [HangFire].[Counter] C 


while (@@trancount > 0)
begin

	if (@commit =1)
	begin

		print 'commit'
		commit tran;

	end
	else
	begin

		print 'rollback'
	
		rollback tran;
	
	end

end
go


Original Code

Script


declare @count int

set @count = 10


DECLARE @RecordsToAggregate TABLE 
(  
	   [Key] NVARCHAR(100) NOT NULL
	,  [Value] SMALLINT NOT NULL
	,  [ExpireAt] DATETIME NULL 
)  
SET TRANSACTION ISOLATION LEVEL READ COMMITTED 

BEGIN TRAN  

	DELETE TOP (@count) C 
	OUTPUT DELETED.[Key], DELETED.[Value], DELETED.[ExpireAt] 
	INTO @RecordsToAggregate 
	FROM [HangFire].[Counter] C WITH (READPAST, XLOCK, INDEX(0))  
	
	SET NOCOUNT ON  
	;MERGE [HangFire].[AggregatedCounter] WITH (HOLDLOCK) AS [Target] 
	USING 
	(  
		SELECT 
				  [Key]
				, SUM([Value]) as [Value]
				, MAX([ExpireAt]) AS [ExpireAt] 

		FROM @RecordsToAggregate  

		GROUP BY 
				[Key]

	) AS [Source] 
	(
		  [Key]
		, [Value]
		, [ExpireAt]
	) 
		ON [Target].[Key] = [Source].[Key] 
		
	WHEN MATCHED THEN 
		UPDATE SET   
				   [Target].[Value] = [Target].[Value] + [Source].[Value]
				,  [Target].[ExpireAt] = 
		(
				SELECT MAX([ExpireAt]) 
				FROM 
				(
					VALUES 
					  ([Source].ExpireAt)
					, ([Target].[ExpireAt])
				) 
				AS MaxExpireAt
				(
					[ExpireAt]
				)
		) 
				
	WHEN NOT MATCHED THEN 
		INSERT ([Key], [Value], [ExpireAt]) 
		VALUES ([Source].[Key], [Source].[Value], [Source].[ExpireAt])
		;  
		
--COMMIT TRAN 
ROLLBACK TRAN

Output

Image


Textual

Warning: Null value is eliminated by an aggregate or other SET operation.

 

Revised Code

Script


set nocount on;
set XACT_ABORT on;

declare @count int

set @count = 10


DECLARE @RecordsToAggregate TABLE 
(  
	   [Key] NVARCHAR(100) NOT NULL
	,  [Value] SMALLINT NOT NULL
	,  [ExpireAt] DATETIME NULL 
)  
SET TRANSACTION ISOLATION LEVEL READ COMMITTED 

BEGIN TRAN  

	DELETE TOP (@count) C 
	OUTPUT DELETED.[Key], DELETED.[Value], DELETED.[ExpireAt] 
	INTO @RecordsToAggregate 
	FROM [HangFire].[Counter] C WITH (READPAST, XLOCK, INDEX(0))  
	
	SET NOCOUNT ON  
	;MERGE [HangFire].[AggregatedCounter] WITH (HOLDLOCK) AS [Target] 
	USING 
	(  
		SELECT 
				  [Key]
				, SUM([Value]) as [Value]
				-- dadeniji - when ExpiryAt is null, replace with 1900-01-01
				--, MAX([ExpireAt]) AS [ExpireAt] 
				, MAX(isNull([ExpireAt], '1900-01-01')) AS [ExpireAt] 

		FROM @RecordsToAggregate  

		GROUP BY 
				[Key]

	) AS [Source] 
	(
		  [Key]
		, [Value]
		, [ExpireAt]
	) 
		ON [Target].[Key] = [Source].[Key] 
		
	WHEN MATCHED THEN 
		UPDATE SET   
				   [Target].[Value] = [Target].[Value] + [Source].[Value]
				,  [Target].[ExpireAt] = 
		(
				SELECT MAX([ExpireAt]) 
				FROM 
				(
					VALUES 
					  ([Source].ExpireAt)
					, ([Target].[ExpireAt])
				) 
				AS MaxExpireAt
				(
					[ExpireAt]
				)
		) 
				
	WHEN NOT MATCHED THEN 
		INSERT ([Key], [Value], [ExpireAt]) 
		VALUES ([Source].[Key], [Source].[Value], [Source].[ExpireAt])
		;  
		
--COMMIT TRAN 
ROLLBACK TRAN

The basic change is us replacing

with

SQL Server – Database Restore Date

Background

Get a tally of last database restore dates.

 

SQL

Code


select 
		  [database]
			= tblRH.[destination_database_name]

		, [restoreType]
			= case tblRH.[restore_type]
					when 'D' then 'Database'
					when 'L' then 'Transaction'
					else 'N/A'
			  end	

		, [restoreDateAsDate]
			= tblRH.restore_date

		, [restoreDateAsString]
			= convert
				(
					  varchar(30)
					, tblRH.restore_date
					, 100
				)
			 
from   [msdb].[dbo].[restorehistory] tblRH

where tblRH.[restore_history_id]
		=
			(
				select max(tblRH_Inner.[restore_history_id])
				from   [msdb].[dbo].[restorehistory] tblRH_Inner
				where  tblRH.[destination_database_name]
						= tblRH_Inner.[destination_database_name]
				and    tblRH.[restore_type]
						= tblRH_Inner.[restore_type]
			)
		
order by
		tblRH.[destination_database_name] asc



 

Output

SQL Server – Install Date – Application & Last Service Pack

Background

It is that time of year again for our Microsoft “True Up” and we need to come clean with new Installs.

 

What is “True Up”?

Link

 

Install & Last SP Date

Let us ask each database its install date and while at it get the date the last SP was applied.

Outline

  1. Install Date
    • Install
      • When SQL Service is installed some OS Accounts are automatically provisioned, as well
        • List of OS Accounts:
          • NT SERVICE\ReportServer
          • NT SERVICE\SQLSERVERAGENT
          • NT AUTHORITY\SYSTEM
          • NT Service\MSSQLSERVER
          • NT SERVICE\Winmgmt
          • NT SERVICE\SQLWriter
  2. Last Service Pack Apply Date
    • Service Pack Applied
      • When a Service Pack is applied, new certificates are applied, as well
        • ##MS_AgentSigningCertificate##
        • ##MS_PolicySigningCertificate##
        • ##MS_SQLAuthenticatorCertificate##
        • ##MS_SQLReplicationSigningCertificate##
        • ##MS_SQLResourceSigningCertificate##
        • ##MS_SmoExtendedSigningCertificate##

 

Code



set nocount on;
set transaction isolation level read uncommitted;
go

; with cteServerPrincipalNT
(
	  [name]
	, [create_date]
)
as
(

	select 

			  tblSSP.[name]
			, tblSSP.[create_date]

	from   sys.server_principals tblSSP

	where   tblSSP.[name] in
				(
					  'NT SERVICE\ReportServer'
					, 'NT SERVICE\SQLSERVERAGENT'
					, 'NT AUTHORITY\SYSTEM'
					, 'NT Service\MSSQLSERVER'
					, 'NT SERVICE\Winmgmt'
					, 'NT SERVICE\SQLWriter'
				)

)
, cteServerPrincipalCertificate
(
	  [name]
	, [create_date]
)
as
(

	select 

			  tblSSP.[name]
			, tblSSP.[create_date]

	from   sys.server_principals tblSSP

	inner join sys.certificates tblSC
 
		on tblSSP.[name] = tblSC.[name]
 
)

select top 1

		  [sqlInstance]
			= cast(serverproperty('servername') as sysname)

		, [installDate] 
			= convert(varchar(30), cteSP.[create_date], 100)

		, [servicePackDate] 
			= convert(varchar(30), cteSPC.[create_date], 100)

from   cteServerPrincipalNT cteSP

outer apply cteServerPrincipalCertificate cteSPC

order by 
		  cteSP.[create_date] asc



Output