Some fun with TSQL

As a bit of fun, the following code was run in SSMS with:

  • Results to Text
  • ‘Include column headers in the result set’ unticked
SET NOCOUNT ON
DECLARE @i int;
SET @i = 1;
WHILE (@i < 20)
BEGIN
SELECT '                     '
SET @i = @i +1
END
SELECT '          *          '
SELECT '         ***         '
SELECT '        *****        '
SET @i = 1;
WHILE (@i < 20)
BEGIN
SELECT '                     '
SET @i = @i +1
END

akawn

Bytes Per Cluster setting for Linux?

Currently at this time there is no information as to what/how the Linux drives should be formatted for optimal SQL Server performance. Hopefully this information will become available prior to the scheduled product release later this year.
 
It’s generally well known that on Windows you’d typically set the Bytes Per Cluster to 65536 for SQL Server databases or 32768 for SQL Server Analysis Services.
 
This can be checked using fsutil fsinfo ntfsinfo i.e. fsutil fsinfo ntfsinfo d:
 
Below shows a C & D drive being checked with the C set to default 4096.
image1
If you are interested in how the Linux disks are formatted, you could try use the following (steps were performed on Ubuntu)
 
Firstly identify the available drives using sudo fdisk -l
image2
Secondly for each drive review the settings using sudo tune2fs -l i.e. sudo tune2fs -l /dev/sda1
image3

Hold your horses, which SNAC do you need?

Over on SQL Server Release Services, they’ve released details on the various SQL Server Native Client (SNAC) downloads currently available.
 
Talk about complexity; but hopefully it will help when you are next in need of a SNAC.
 
On a side note ‘Microsoft has announced the deprecation of the SQL Server Native Client OLE DB provider, and that SQL Server 2012 is the last version of SQL Server to support the provider’ .
 
Therefore you should be looking towards use of ODBC moving forward.

How to populate a variable using a CTE

The following table called t1 is used for the examples:
 
image1
 
If the CTE (Common Table Expression) returns a single value then you could use:

--variable
DECLARE @v char(1);

--CTE
;WITH CTE
AS
(
	SELECT c2
	FROM t1
	WHERE c1=2
)
SELECT @v = c2
FROM CTE;

--verify correct
SELECT @v AS the_value;

image2
If the CTE will return multiple values, and say you only want the first descending ordered one, then you could use:

--variable
DECLARE @v char(1);

--CTE
;WITH CTE
AS
(
	SELECT c2
	FROM t1	
)
SELECT TOP 1 @v = c2
FROM CTE
ORDER BY c2 DESC;

--verify correct
SELECT @v AS the_value;

image3
 
You can also store CTE values into table variables, but I’ll cover that in another post.

So long MCSE Server Infrastructure

As a sign of the times and the ever increasing cloud push, the MCSE: Server Infrastructure qualification has now been replaced with MCSE: Cloud Platform and Infrastructure.
 
Extract from MCSE: Server Infrastructure qualification link:
 
This certification will be withdrawn on 31 March 2017. When the certification is withdrawn, all individuals whose transcripts list this certification as active on or after 26 September 2016 will retain the active status.

T-SQL returning something from nothing

Occasionally you may need to return a value even if nothing exists.
 
In this very basic example, suppose you have an empty table i.e.

SELECT id FROM table1;

image1
Now say, you need to return the value 1 to the application if there is no data.
 
There is a multitude of ways of achieving this, and here is one:

SELECT COALESCE((SELECT id FROM table1),1) AS id;

image2

Perform Log backup for all non-simple recovery databases to NUL & then shrink the Log files

You sure can come across some interesting requests as a DBA.
 
In this case the request was to quickly perform a log backup on a Dev server for each database (50+) so a log file shrink to free disk space could be performed.
 
Some of the scenario included:

  • Point in-time recovery was not necessary
  • They didn’t want to break Always On
  • The Dev server had insufficient space for the log backups

The following did the trick to backup the log files:

DECLARE @sqlcmd nvarchar(MAX);
DECLARE @db nvarchar(256);
DECLARE item_cursor CURSOR LOCAL FAST_FORWARD FOR
SELECT name
 FROM sys.databases
 WHERE 1=1
 AND state_desc = 'ONLINE'
 AND is_read_only = 0
 AND recovery_model_desc <> 'SIMPLE'
ORDER BY name
OPEN item_cursor
FETCH NEXT FROM item_cursor INTO @db
WHILE @@FETCH_STATUS = 0 
BEGIN
 SET @sqlcmd = 'BACKUP LOG [' + @db + '] TO DISK = ''NUL'';'  
 EXEC (@sqlcmd )
 FETCH NEXT FROM item_cursor INTO @db
END
CLOSE item_cursor;  
DEALLOCATE item_cursor;

Next to reclaim the log file space the following was run:

DECLARE @shrink nvarchar(MAX);
DECLARE @db nvarchar(256);
DECLARE @file nvarchar(256);
DECLARE item_cursor CURSOR LOCAL FAST_FORWARD FOR
 SELECT DB_NAME(a.database_id),a.name
 FROM sys.master_files a
 WHERE 1=1
 AND a.type_desc = 'LOG'
 AND a.is_read_only <> 1
 AND a.database_id IN (SELECT b.database_id FROM sys.databases b WHERE b.state_desc = 'ONLINE' AND b.is_read_only <> 1)
OPEN item_cursor
FETCH NEXT FROM item_cursor INTO @db,@file
WHILE @@FETCH_STATUS = 0 
BEGIN
 SET @shrink = 'USE [' + @db + ']; DBCC SHRINKFILE(N''' + @file + ''',1) WITH NO_INFOMSGS;'  
 EXEC (@shrink)
 FETCH NEXT FROM item_cursor INTO @db,@file
END
CLOSE item_cursor;  
DEALLOCATE item_cursor;

Linux event logs file location

As a Windows DBA, the Windows Logs is the place to go for reviewing Application/System/Security events.
 
In the Linux world, the location is /var/log
image1
Once in the log folder you can list the available files using ls and to read a file you can use less filename i.e. less syslog
 
When using less to view a file, pressing h will show the many options available to you for scrolling, searching, exiting etc the file.
image2
Useful less commands include:
g – go to the start of the file
G – go to the end of the file
Spacebar – scroll a full screen
Enter – scroll line by line
/searchpattern – search forward for the pattern i.e. /fail
?searchpattern – search backwards for the pattern i.e. ?fail
q – exist less

SQL Server 2012 Mainstream Support Ending reminder

Time sure does fly and just a reminder Mainstream Support for SQL Server 2012 ends in just over 3 months on 11th July 2017.
 
Ideally any new SQL Server installs should be the newest, or as close to the newest version of SQL Server as possible, to get the maximum RoE.
 
With SQL Server 2016 SP1, a lot of Enterprise features are now included in the other editions, so is a great option. Something to be aware of though is that Microsoft is now requiring Software Assurance to be acquired for use of a DR instance. The use of one DR instance was usually included by default in earlier versions.