Hilton Als – “MOONLIGHT” UNDOES OUR EXPECTATIONS

Story Telling

Storytelling is one area we all share as humanity.

Yet it is rare that we are closed enough to reality, or in some cases things don’t turn out well or they just drag on and on, and it is impossible to claim victory or honestly package them up.

And, so it is good to return back to how Tarell Alvin McCraney & Barry Jenkins were able to emerge from their Natural Inheritance.

 

Hilton Als

Link


Did I ever imagine, during my anxious, closeted childhood, that I’d live long enough to see a movie like “Moonlight,” Barry Jenkins’s brilliant, achingly alive new work about black queerness? Did any gay man who came of age, as I did, in the era of Ronald Reagan, Margaret Thatcher, and aids, think he’d survive to see a version of his life told onscreen with such knowledge, unpredictability, and grace? Based on a story by the gay black playwright Tarell Alvin McCraney—Jenkins himself is not gay—the film is virtuosic in part because of Jenkins’s eye and in part because of the tale it tells, which begins in nineteen-eighties Miami.

Four white Miami-Dade police officers have beaten a young black man to death and been acquitted of manslaughter, setting off riots in the city’s black enclaves—Liberty City, Overtown, and elsewhere. It’s hard for a man of color walking those sun-bleached streets not to watch his back or feel that his days are numbered. That’s how Juan (the beautiful Mahershala Ali) carries himself—defensively, warily. He’s a dope dealer, so there’s that, too. He may be a boss on the streets—his black do-rag is his crown—but he’s intelligent enough to know that he’s expendable, that real power doesn’t belong to men like him. Crack is spreading through the city like a fever. Stepping out of his car, Juan asks a cranky drug runner what’s up. (Jenkins and his ardent cinematographer, James Laxton, film the car as if it were a kind of enclosed throne.) Juan, his mouth fixed in a pout—sometimes he sucks on his tongue, as if it were a pacifier—doesn’t take his eyes off the street. He can’t afford to; this situation, any situation, could be changed in an instant by a gun or a knife.

In this world, which is framed by the violence to come—because it will come—Juan sees a skinny kid running, his backpack flapping behind him. He’s being pursued by a group of boys, and he ducks into a condemned building to escape. Juan follows, entering through a blasted-out window, a symbol, perhaps, of the ruin left by the riots. Inside, in a dark, silent space, the kid stares at Juan, and Juan stares at the kid. There’s a kind of mirroring going on. Maybe Juan is looking at his past while the boy looks up at a future he didn’t know he could have. It’s a disorienting scene, not so much because of what happens as because of what doesn’t happen. Throughout the movie, Jenkins avoids what I call Negro hyperbole—the overblown clichés that are so often used to represent black American life. For instance, Juan doesn’t take that runaway kid under his wing in order to pimp him out and turn him into a drug runner; instead, he brings him home to feed him, nourish him.

Juan lives in a small, unassuming house with his soft-spoken but confident partner, Teresa (played by the singer Janelle Monáe). The couple look on as the kid eats and eats; it’s clear, though, that he’s hungry for more than food. The boy doesn’t even say his name, Chiron, until Juan nudges him: “You don’t talk much but you damn sure can eat.” The affectionate scolding makes Chiron (Alex Hibbert, a first-time actor, who couldn’t be better) sit up and take notice; it tells him that he counts. And he knows he counts even more when Juan calls him by his nickname—Little—as a way of claiming him.

“Faggot” is another name, and it’s one that Chiron hears often as he grows up. He’s an outsider at school, and at home, too. He lives in public housing with his single mother, Paula (Naomie Harris), who goes on drug binges, less to alleviate her sadness than to express her wrath—against the world and, especially, against her son, who she thinks keeps her from the world. Chiron lives for the moments when he can get away from his mother’s countless recriminations and needs, and swim in the unfamiliar waters of love with Juan and Teresa. One indelible scene shows Juan holding Chiron in his arms in a rippling blue ocean, teaching him to float—which is another way of teaching him the letting go that comes with trust, with love.

But, at the end of every outing, Teresa and Juan show their respect by returning Chiron home. No matter how awful Paula is, she is still Chiron’s mother. This gesture is one of many that Jenkins, who, like McCraney, was raised in Liberty City, understands from the inside out. Growing up in this community, Juan and Paula were taught to care for children, their own and others’. (There are no white characters in the film, and this is a radical move on Jenkins’s part. Whites would have introduced a different dynamic to “Moonlight.” Jenkins’s story is about a self-governing black society, no matter how fractured.) But drugs have made a mess of family, or the idea of family, and Paula gets in Juan’s face when he tries to stop her from using. She has a child, sure, but how can he talk when he’s the one selling drugs? It’s a vicious cycle, in which the characters are oppressed by everything but hope. Still, Juan does hope, if only for Chiron. That he is able to pluck that feeling out of the darkness of those Miami nights makes him a classically heroic figure: he knows his limitations, he knows that life is tragic, but he is still willing to dream.About thirty minutes into the film, Chiron, sitting at Juan and Teresa’s orderly table, asks what a faggot is. At the screening I attended, the entire audience froze, as did the figures onscreen. Then Chiron asks if he himself is a faggot. There’s no music in this scene; no one cries; Juan doesn’t grab a gun and try to blow the slandering universe away. Instead, he takes the word apart, and doesn’t take Chiron apart with it. He knows that Chiron is marked for misery, and how will Juan’s heart bear it, let alone Chiron’s?“Moonlight” undoes our expectations as viewers, and as human beings, too. As we watch, another movie plays in our minds, real-life footage of the many forms of damage done to black men, which can sometimes lead them to turn that hateful madness on their own kind, passing on the poison that was their inheritance. As Juan squires his fatherless friend about, we can’t help thinking, Will he abuse him? Will it happen now? Jenkins keeps the fear but not the melodrama in his film. He builds his scenes slowly, without trite dialogue or explosions. He respects our intelligence enough to let us just sit still and watch the glorious faces of his characters as they move through time. Scene follows scene with the kind of purposefulness you find in fairy tales, or in those Dickens novels about boys made and unmade by fate.Jenkins has influences—I would guess that Apichatpong Weerasethakul, Terrence Malick, and Charles Burnett are high on the list, along with Michael Roemer’s 1964 film “Nothing But a Man,” one of the first modern black love stories to avoid buffoonery and improbability—but what really gets him going here is filmmaking itself, and the story he’s telling. Directors such as Marlon Riggs and Isaac Julien explored gay black masculinity in the nineties, but they did so in essay-films, which allowed the audience a kind of built-in distance. Of course, no one in the nineties wanted to finance films about gay black men. Twenty years later, I still don’t know how Jenkins got this flick made. But he did. And it changes everything.The film is divided into three parts, titled “Little,” “Chiron,” and “Black.”
In the second part, Chiron (played now by Ashton Sanders) is a teen-ager, thin and walking with the push, resolve, and loneliness of a character for whom Billie Holiday would have given her all in a song. Like any young person, Chiron wants to be claimed bodily but is not entirely in his body. He’s growing up without much reinforcement outside Juan and Teresa’s home. Paula’s drug addiction has escalated and so has her anger. She’s a rotten baby, flailing around, as full of bile as Terrel (Patrick DeCile, in an incredible characterization), who bullies Chiron at school. So when a classmate, Kevin (Jharrel Jerome), shows Chiron something other than hostility, it feels like a kind of fantasy. Indeed, after Kevin jokes with Chiron about a girl, he dreams about Kevin having sex with her. And it’s like a dream one night when Chiron, trusting little but wanting to trust more, approaches Kevin at the beach where Juan taught him to swim.
The light-skinned Kevin has nicknamed Chiron Black, and he asks why, wondering if it’s a put-down. Kevin, who is more comfortable in his own body, says that it’s because Chiron is black; to him, it’s not an insult. This moment of confusion—about internalized self-hatred and the affection of naming—is unlike anything that’s been put onscreen before; it shows what freedom and pain can look like, all in one frame. When the boys kiss, Chiron apologizes for it, and we wince, because who among us hasn’t wanted to apologize for his presence? Intimacy makes the world, the body, feel strange. How does it make a boy who’s been rejected because of his skin color, his sexual interests, and his sensitivity feel? Kevin says, “What have you got to be sorry for?” As he works his hand down Chiron’s shorts, the camera pulls back; this is the only moment of physical intimacy in the film, and Jenkins knows that in this study of black male closeness the point isn’t to show fucking; it’s to show the stops and starts, the hesitation, and the rush that comes when one black male body finds pleasure and something like liberation in another.
Watching Sanders play Chiron at this stage of his life is rather like seeing Montgomery Clift act for the first time, or Gloria Foster in “Nothing But a Man.” There’s no accounting for talent like this. Sanders has a conjurer’s gifts, and an intuitive understanding of how the camera works—how it can push into an actor’s face and consciousness, and how the actor can push back against the intrusion by inhabiting the reality of the moment.
But the moment of love doesn’t last. When Terrel challenges Kevin about his attachment to Chiron, Kevin beats Chiron up, and then Terrel jumps on him, too. It’s “The Lord of the Flies” all over again: whale on sensitivity before it can get to you. In a bid to protect his dream of love, Chiron shows up at school one day and, wordlessly, breaks a chair over Terrel’s back. It’s every queer kid’s revenge fantasy, but what follows is every queer kid’s reality: fight back, and you’ll pay for it; the power does not belong to you.
In the third part of the film, Chiron (gorgeously played by Trevante Rhodes) is an adult, but still looking after his mother. She’s in rehab in Atlanta, and he has fulfilled his destiny by example: like Juan, he’s a drug dealer in a do-rag. But he doesn’t have a Teresa, doesn’t have anyone. He wears his sensitivity like a shroud around his now muscular body, which looks very black in the moonlight as he lies in bed, startled to have received a phone call from Kevin after many years. Rhodes’s portrayal of the grownup Chiron feels like a natural evolution from the earlier performances. The gold fronts that his Chiron wears are just another form of armor against longing, in a mouth that yearns to taste Kevin’s once again, to relive that forbidden love, for which black men sometimes punish one another. Rarely has the world taught them not to. But at times, when no one’s looking, love happens, just the same.

 

Ta-Nehisi Coates

Likely there are areas of our lives where we are a bit untrustworthy.  I pray we do get to recompense.

 

Quotes

  1. Shanika Hadge?
    • If you want to know where a culture is headed look at the art
  2. Mahershala Ali
    • Disengaging
      • Not be communicative
      • take ownership of our experience
      • We need ourselves
      • If I take time from it, I can go back to it and not be assaulted
    • Poetr y
    • Play
      • From the margins to the Center
    • Writing a film script
    • Understanding time more
      • Do not have time to do everything
      • When you have freedom and your own room, it is difficult
      • My Dad was dying at the time
      • Our efforts is not always in alignment with the desired destination
      • I had to learn that the hard way
      • I wasted a lot of time just chasing girls
        • It was too important to me
        • I had to be about chasing me
        • Was worrying “Who is she with now, she did not call me back”
      • There is nobody here that is more important than you
      • There is a handful of people that I still keep in touch with
        • And, that is because we contribute to each other
        • They are trying to be good fathers and good husbands

 

Grats

Gratitude to Rebecca Engle, Director of Theater @ Saint Mary,  & Mahershala Ali for narrating…here

And, also to all those wanting and waiting for a good story to tell.

As I watch more of the Video, it is so obvious how much Rebecca Engle has invested in teaching and training Mahershala.

And, far more endearingly, how much she wants him to be successful not just commercially, but through the story that gets to be told through him, as well.

 

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

 

DBeaver

Background

Here I am having used SQL Server Management Studio ( SSMS ) for so long, I am satisfied.

It’s GUI is versely superior to SQL 2000 Query Analyzer and it exposes just about every SQL Functionality that I use on daily basis.

 

Stumped

Spent hours trying to capture a query’s grid display unto Excel, but Excel column size was getting in the way.

And, so googled for ways to display columns as rows.

 

DBeaver

Googled and found DBeaver.

The nice thing about it is that I can export queries result set into XML or HTML.

 

Download

DBeaver is available here.

 

Export Query

Screen Shot

Data Transfer Target Type and Format

Image

Data Transfer – Extraction Settings

Image

Data Transfer – Settings – See export settings

Image

 

Data Transfer – Output – Configure export output parameters

Image

Data Transfer – Confirm – Check results

Image

 

Sample Files

HTML

XML

 

Other Functionalities

Database Diagram

Database Modeling is also easy and nice.

 

Table Structure

Properties

References

DDL

 

Data

 

Database Supported

DBeaver supports both SQL and NoSQL Databases.

For SQL databases, it relies on JDBC.

Driver Manager

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