Defining Data Type

Have you ever had a problem to sort values in a VARCHAR column by a different data types?
All of them are stored as a text, how to check their data types?

Here is a simple query to solve that problem:

DECLARE @table TABLE (Unknown VARCHAR(100)); INSERT INTO @table VALUES ('0'),('9'),('-27'),('257'),('   20190815'),('7855567554354') ,('0.00000000000000000000000000000000000063') ,('34.349732949394273943654684095609489063') ,('2349347923437438976567456687685.1'),('6.3E-37') ,('2019-08-15'),('23:45:17'),('20190815 23:45:09') ,('62FD6025-7775-4F28-88EB-BCF9DE33D831') ,('0x5B612D7A412D5A5D5B612D7A412D5A5D25'); SELECT [Value] = Unknown        , [BIT] = IIF(LEN(Unknown) = 1 and TRY_CAST(Unknown AS TINYINT) in (0,1),'x',Null)        , [TINYINT] = IIF(TRY_CAST(Unknown AS TINYINT) Is Not Null,'x',Null)        , [SMALLINT] = IIF(TRY_CAST(Unknown AS SMALLINT) Is Not Null,'x',Null)        , [INT] = IIF(TRY_CAST(Unknown AS INT) Is Not Null,'x',Null)        , [BIGINT] = IIF(TRY_CAST(Unknown AS BIGINT) Is Not Null,'x',Null)        , [DECIMAL] = IIF(TRY_CAST(Unknown AS DECIMAL(38,0)) Is NOT Null,'x',Null)        , [FLOAT] = IIF(TRY_CAST(Unknown AS FLOAT) Is NOT Null,'x',Null)        , [BINARY] = CASE WHEN Unknown not like '0x%' OR LEN(Unknown) % 2 = 1 THEN NULL                     ELSE IIF(Unknown LIKE '0x%[g-zG-Z]%', NULL, 'x' ) END        , [DATETIME] = IIF(TRY_CAST(Unknown AS DATETIME) Is Not Null,'x',Null)        , [UNIQUEIDENTIFIER] = IIF(TRY_CAST(Unknown AS UNIQUEIDENTIFIER) Is Not Null,'x',Null) FROM @table; GO

Here is the result:

File Auto Grow history using default trace

Why it is important?

Unpredictable "File Auto Grow" can be real bottleneck in your highly performing system.
At first, if file growing chunks are too small the create unnecessary fragmentation on you physical drive and for the log file it generates humongous number of VLFs. The fragmentation slows down all your I/O operations and huge number of VLFs can really slow down your server startup procedure.
At second, when your chunks are too big it might generate unpredictable few seconds delay during busy business hours.
So, tracking these events and fight with them is one of DBA's priorities.

Last month I had a post on how to track and possibly alert a DBA about file growth event (https://slavasql.blogspot.com/2019/07/capturing-filegrowth-event-using-job.html)

That is pretty helpful, but what if you can't set it up or want to know about the past file growth events.

In that case a DBA might use a limited option to look back in the SQL Server history.

Assuming your default trace is enabled and nobody deleted its log files.
Then you will be able to run the following script to figure out ALL historical Auto file growth events:

DECLARE @FileName NVARCHAR(MAX) = (        SELECT CAST(value as NVARCHAR(MAX))        FROM ::fn_trace_getinfo(DEFAULT)        WHERE traceid = 1 AND property = 2); SELECT @FileName = LEFT(@FileName        , LEN(@FileName)-CHARINDEX('_'        ,REVERSE(@FileName),1))               + RIGHT(@FileName,4); SELECT t.StartTime        , [Event Name] = te.name        , t.DatabaseName         , t.Filename        , [Growth, Mb] = CAST(t.IntegerData/128. as DECIMAL(18,3))        , [Duration, ms] = t.duration/1000        , t.Hostname        , t.LoginName        , t.ApplicationName FROM ::fn_trace_gettable(@FileName, DEFAULT) as t INNER JOIN sys.trace_events as te        ON t.EventClass = te.trace_event_id WHERE EventClass in (92,93) ORDER BY t.StartTime DESC; GO

Running this you are supposed to get similar output:

Limitations:

As you can guess, nothing is given for free. Default trace is capturing only "Log File Auto Grow" and
"Data File Auto Grow" (besides of many others), but does not capturing "Manual File Size" changes. Also, you can see only the increase in size, but not original or new file size. However, for simple troubleshooting that has to be more than enough.

Old and forgotten xp_availablemedia procedure

If you plan to use "xp_availablemedia" please stop right now!

It is very old and outdated procedure and it might be deprecated by Microsoft at any moment.

I am writing this post because I've spent an hour trying to decode it's output and found the method only in an ancient book "SQL Server 2000 Stored Procedures Handbook" from 2003!
You can buy it on Amazon: https://www.amazon.com/Server-Stored-Procedures-Handbook-Experts/dp/1590592875 and it still very valuable and costs more than $30.

So, giving credits to the authors Robin Dewson, Tony Bain, Chuck Hawkins & Louis Davidson for the great job.

For those who hit my post trying to figure out what "xp_availablemedia" is I'll give you the formula I've got from the book.

Here is a compare of values from xp_availablemedia output and an output from newer and recommended procedure xp_fixeddrives:

DECLARE @availablemedia TABLE (name SYSNAME, [low free] INT, [high free] INT, mediatype INT) DECLARE @fixeddrives TABLE (drive CHAR(1), mbfree INT) INSERT INTO @availablemedia EXEC master..xp_availablemedia 2; INSERT INTO @fixeddrives EXEC master..xp_fixeddrives; SELECT m.*        , [Free Gb] = CAST(4 * (IIF(m.[low free]<0,1,0)+m.[high free]) + m.[low free] / 1073741824. AS DECIMAL(19,1))        , [Free Mb] = CAST(4096 * (IIF(m.[low free]<0,1,0)+m.[high free]) + m.[low free] / 1048576. AS DECIMAL(19,0))        , [Drives Free Mb] = d.mbfree FROM @availablemedia as m INNER JOIN @fixeddrives as d        ON LEFT(m.name,1) = d.drive;

As you can see, the difference in numbers is very minor, but xp_fixeddrives does not have any hassles with additional calculations:

Here is the Book!!!

SQL Server String concatenation behavior.

Pretty recently hit very interesting problem of how SQL Server concatenates strings.

The very same SELECT clause in some cases produced VARCHAR(MAX), but in same cases it cut the result to VARCHAR(8000) ( or NVARCHAR(4000) )

After a research I've discovered following:
1. SQL Server is applying escalation of datatype to "MAX" ONLY in the case if at least one of the concatenated strings already has that type.
2. SQL Server does concatenation operation from left to right and treats the result as VARCHAR(8000) or NVARCHAR(4000) unless it hits the "MAX".

That means that the following query will return 8000 and not 8001:

SELECT LEN(REPLICATE('.',8000) + '1');

If we add to concatenated string "MAX" type value that will switch the result ONLY at the point of that "MAX" type value. So, the following script will return 8001:

SELECT LEN( REPLICATE('0',8000) + '1' + CAST('2' AS VARCHAR(MAX)) );

In order to get correct value for a string we need to have "MAX" type value BEFORE the string reaches size limit of 8000. For instance you can do following, which will return correct number of characters - 8002:

SELECT LEN( REPLICATE('0',8000) + CAST('1' AS VARCHAR(MAX))  + '2' );

So, in order to prevent any misbehavior in the future you can just put EMPTY "MAX" type value as the very first mamber of the concatenation:

SELECT LEN(CAST('' AS VARCHAR(MAX)) + REPLICATE('0',8000) + '1' + '2');

In case of use of variables the solution can be the same:

DECLARE @BigString VARCHAR(MAX); SET @BigString = ''; SELECT @BigString = @BigString + REPLICATE('0',8000) + '1' + '2'; SELECT LEN(@BigString);

However, be careful with the following solution, which WON'T produce the correct result:

DECLARE @BigString VARCHAR(MAX); SET @BigString = ''; SELECT @BigString += REPLICATE('0',8000) + '1' + '2'; SELECT LEN(@BigString);

Capturing FileGrowth Event using SQL Agent Job

Sometimes unpredictable file growth become real pain of a DBA and you want to answers for questions "What happened?" and "When it happened?"
You might capture it using Extended Events, but I want to talk about old-fashion way of capturing these kind of events: via SQL Agent Alerts and Jobs
As always, we do it in steps. Just three easy steps.

Step 1.

For capturing file growth events you need to create a log table "tbl_FileGrowthEvents" in your "DBA_Util" database:

USE [DBA_Util]; GO DROP TABLE IF EXISTS tbl_FileGrowthEvents; GO CREATE TABLE tbl_FileGrowthEvents ( FileGrowthEvent_ID INT IDENTITY(1,1) , FileGrowthEvent_DT DATETIME NOT NULL CONSTRAINT Def_FileGrowthEvents_FileGrowthEvent_DT DEFAULT GetDate() , FileType CHAR(3) NOT NULL  , DatabaseName SYSNAME NOT NULL , [FileName] SYSNAME NOT NULL , FileGrowth_XML XML NOT NULL ); GO

Step 2.

You need to create a job with a name "Alert_File_Growth_Logging". Make sure to specify the owner of that job "SA" or another system account, which have appropriate access.

Create a single step in that job called "Logging Event" and copy-paste there following script:

INSERT INTO DBA_Util.dbo.tbl_FileGrowthEvents( FileType, DatabaseName, [FileName], FileGrowth_XML) SELECT LEFT([type_desc],3), DB_NAME(database_id), [name] , '<FileGrowth_Event> <FileGrowth_Event_Type>' + [type_desc] + '</FileGrowth_Event_Type> <Post_Time>' + IsNull(CAST($(ESCAPE_NONE(WMI(PostTime))) as VARCHAR),'') + '</Post_Time> <Duration>' + IsNull(CAST($(ESCAPE_NONE(WMI(Duration))) as VARCHAR),'') + '</Duration> <Database_Name>' + DB_NAME(database_id) + '</Database_Name> <File_Name>' + [name] + '</File_Name> <Physical_Name>' + IsNull(physical_name,'') + '</Physical_Name> <File_Size_Mb>' + IsNull(CAST([size] / 128 as VARCHAR),'') + '</File_Size_Mb> <NT_Domain_Name>' + IsNull('$(ESCAPE_SQUOTE(WMI(NTDomainName)))','') + '</NT_Domain_Name> <Login_Name>' + IsNull('$(ESCAPE_SQUOTE(WMI(LoginName)))','') + '</Login_Name> <Session_Login_Name>' + IsNull('$(ESCAPE_SQUOTE(WMI(SessionLoginName)))','') + '</Session_Login_Name> <Host_Name>' + IsNull('$(ESCAPE_SQUOTE(WMI(HostName)))','') + '</Host_Name> <Application_Name>' + IsNull('$(ESCAPE_SQUOTE(WMI(ApplicationName)))','') + '</Application_Name> </FileGrowth_Event>' FROM master.sys.master_files WHERE [name] = N'$(ESCAPE_SQUOTE(WMI(FileName)))'  and database_id = $(ESCAPE_SQUOTE(WMI(DatabaseID)));

You have to get something like this:

Step 3.

Now you need to create two alerts: "Alert Data file auto growth" and "Alert Log file auto growth".
In both you Specify following:
Type: "WMI event Alert"
Namespace: "\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER"
In this case "MSSQLSERVER" is an instance name of your SQL Server.
Query for Data File Alert: SELECT * FROM DATA_FILE_AUTO_GROW
Query for Log File Alert: SELECT * FROM LOG_FILE_AUTO_GROW
You have to get this:

 On "Response" Page you have to check "Execute Job" and select previously created job "Alert_File_Growth_Logging" from the list:

  On "Options" Page you can increase "Delay between responses" to one minute.

After saving both Alerts you are ready to test.

Step 4.

If you do not have it set, you have to check  the box "Replace tokens for all job responses to alerts" inside of SQL Server Agent -> Properties -> Alert System -> Token replacement.

Test:

Create a test database and run following script for it. (just make sure database name and file names are matching yours)

use SLAVA_TEST; GO SET NOCOUNT ON GO DROP TABLE IF EXISTS tbl_Copy_Messages; GO DBCC SHRINKFILE ('Slava_Test_Data') WITH NO_INFOMSGS; GO DBCC SHRINKFILE ('Slava_Test_Log') WITH NO_INFOMSGS; GO SELECT * INTO tbl_Copy_Messages FROM sys.messages; GO

If you've done all three building steps without errors, you should have a content of your "tbl_FileGrowthEvents" table like this:

From that point you can answer which file has grown and when. So, you might correlate it to your internal processes.
If you need deeper information you can click on XML content and get more information associated with a transaction, which caused file growth event: