Wednesday, March 30, 2016

Only Transaction Log Backup can free space in the Transaction Log in Full and Bulk-Logged Recovery Models

This post is about well known fact that only way to claim back space Transaction Log in Full and Bulk-Logged Recovery Models is to do a Transaction Log Backup.

Paul Randall has a wonderful post about that topic: "Misconceptions around the log and log backups: how to convince yourself".
Will quote him: "There’s still a widely held misconception that when properly in the FULL or BULK_LOGGED recovery models that full or differential backups can truncate the log. No. It *NEVER* happens."

His post was published almost SEVEN years ago, but there are still a lot of people who thinks differently. There are thousands of databases where transaction log is hundred times bigger than the data itself.
Millions of petabytes of unnecessary data around the world are sitting uselessly on companies' hard drives bringing revenue to SAN distributors.

Will do it, as always, in steps:

What is the problem?

Will repeat it again and again: If your database in Full or Bulk-Logged Recovery Models, all transactions, recorded to the Log file, won't go away and will sit there forever, until Transaction Log Backup will be performed.

What is wrong with that?

At first, growing log file will eat your disk space. If your database is small and has almost no activity, you can live without even knowing about the problem for couple of years and when your database will stop accepting any new transactions because of Full Disk issue you will be running and screaming trying to figure out how to fix the issue.
At second, Log file will grow in small increments. By default, Log file auto-growth increment is 10%. It will take 100! File growth events until you reach size of 10Gb from it's initial size of 0.75 Mb. That creates huge file fragmentation not only on a drive, but also it creates internal Log File fragmentation.
In your firs 10Gb will be about 800 Virtual Log Files (VLFs). I've seen the case when Database's Log file had 200K VLFs!
At third, in case of disaster you would have to backup whole your Transaction Log, which will take a lot of extra time for the old Gigabytes of log data while your production system is down.

Why that was happened?

Every new database you create is just a copy of Model database. Model database, by default, is in Full Recovery Model.Very few people are changing recovery model to Simple or scheduling a log backup job right after database creation.

How to prevent that?

The easiest thing is to switch Model database on the server to Simple Recovery Model. then all new databases will be in Simple mode.
In a case, when customer requires you to have a database in Full Recovery Model, everybody must understand what does it mean, why that is necessary, and how to use it for your advantage.
You also have to develop the RESTORE strategy, which will include Transaction Log backup and you won't have a problem of growing transaction log at all.

To justify my post will provide a little demo:

Script #1. Create a test database. Switch it to the Full Recovery Model and do an initial Full Database Backup.
use Master;
IF EXISTS (SELECT TOP 1 1 FROM sys.databases WHERE name = 'TestLogBackup')
  DROP DATABASE TestLogBackup;
BACKUP DATABASE [TestLogBackup] TO  DISK = N'C:\SS2014\Backup\TestLogBackup_1_Full.bak' 

Script #2. Reporting script. We will use it multiple times during the demo:
USE TestLogBackup;
SELECT name AS [Database Name], create_date, recovery_model_desc FROM sys.databases 
WHERE name = 'TestLogBackup';
;WITH DbData as (
SELECT name, 
 file_id as "File Id", 
 physical_name as "Physical Name",
 CAST(CAST(ROUND(Size/128.,3) as DECIMAL(16,3)) as VARCHAR(16)) AS SizeMB,
 CAST(CAST(ROUND(FILEPROPERTY(name, 'SpaceUsed')/128.,3) as DECIMAL(16,3)) as VARCHAR(16)) AS UsedSpaceMB
FROM sys.database_files with (nolock) 
SELECT name AS [File Name], [File Id],
 RIGHT(SPACE(16) + CASE WHEN Len(SizeMB) > 7 
  THEN CASE WHEN Len(SizeMB) > 10 
  THEN LEFT(SizeMB, LEN(SizeMB) - 10) + ',' + SUBSTRING(SizeMB, LEN(SizeMB) - 10, 3) + ',' + RIGHT(SizeMB, 7)
  ELSE LEFT(SizeMB, LEN(SizeMB) - 7) + ',' + RIGHT(SizeMB, 7) END ELSE SizeMB END, 16) as SizeMB,
 RIGHT(SPACE(16) + CASE WHEN Len(UsedSpaceMB) > 7 
  THEN CASE WHEN Len(UsedSpaceMB) > 10 
  THEN LEFT(UsedSpaceMB, LEN(UsedSpaceMB) - 10) + ',' + SUBSTRING(UsedSpaceMB, LEN(UsedSpaceMB) - 10, 3) + ',' + RIGHT(UsedSpaceMB, 7)
  ELSE LEFT(UsedSpaceMB, LEN(UsedSpaceMB) - 7) + ',' + RIGHT(UsedSpaceMB, 7) END ELSE UsedSpaceMB END, 16) as UsedSpaceMB,
 CASE is_percent_growth WHEN 0 THEN CAST(growth/128 as VARCHAR) + ' Mb'
    ELSE CAST(growth as VARCHAR) + ' %' END as AutoGrowth
That script produces following result sets:
1. Verify that Database in Full Recovery Model;
2. Provide File size and Used space size for Data and Log files;
3. Gives current list of VLFs from the Log file.
Here is what I have:

Script #3. Generating some data and transaction activity:
SELECT TOP 1000 REPLICATE('A',8000) AS Column_1
INTO tbl_Test_01 FROM sys.messages;
Run Script #2 to see how files have been change:
As you can see Used Space have increased in both files; Log file has been grown and number of VLFs have been increased.

Script #4. First test: Will try to decrease Used Space in Transaction Log by doing Differential Database Backup:
BACKUP DATABASE [TestLogBackup] TO DISK = N'C:\SS2014\Backup\TestLogBackup_2_Diff.bak' 
By running Script #2 you can verify that Used Space in Transaction Log not even shrunk, but even increased a little bit:

Script #5. Second test: Will try to decrease Used Space in Transaction Log by doing Full Database Backup:
BACKUP DATABASE [TestLogBackup] TO  DISK = N'C:\SS2014\Backup\TestLogBackup_3_Full.bak' 
Run Script #2 again and verify that this does not help:

Script #6. Third test: Will try to decrease Used Space in Transaction Log by doing Transaction Log Backup:
BACKUP LOG [TestLogBackup] 
TO DISK = N'C:\SS2014\Backup\TestLogBackup_4_Log.bak' 

That Worked!!! Used Space in Transaction Log has shrunk:

So,if you did not know before, now you know: The first line of defense against growing Log file is having Model database in Simple Recovery Model.
The second line of defense is implementation of well planned RESTORE strategy with regular Transaction Log backups.

Thursday, March 24, 2016


DBCC CHECKDB is very resource intensive and long operation, but it is extremely important to perform against your databases.
Kendra Little has a wonderful blog about that topic: DBCC CheckDB: Frequently Asked Questions.

In my post I won't talk about "Importance", "Necessity" and "Resume changing events" associated with DBCC CHECKDB. I do not want to talk about job security at all, I want to talk about professional etiquette.

The DBA in an organization is the last line of defense for the company's data. Only you can protect it from loss and corruption. And I hope along with making regular verified backups you are doing regular DBCC CHECKDB against all of your databases.

So, will draw the line: that operation is important and DBA must do that.
Now, the question is, how to make it less painful.

Option 1.

Run DBCC CHECKDB against secondary server. That can be a mirror, secondary AlwaysOn server or any other working copy of your environment. That is pretty easy to set and produces good consistent results. You always can switch replicas and do the checkup against the primary one.
Cons: You must pay additional licensing. If your primary server has 64 Cores, you have to pay for additional 64 SQL Server core licenses on the secondary, where you perform DBCC CHECKDB. So, it is extremely expensive option.

Option 2.

Run DBCC CHECKDB against restored copy of the database. That is much cheaper option. You are using one, not very expensive server, not only to verify data integrity for multiple databases from several production servers, but in parallel with that you also verify quality of your backups.
That is wonderful option, but requires dedicated server and advanced procedure with a lot of moving parts.

Option 3.

Use DBCC CHECKFILEGROUP. That is almost the same as to use DBCC CHECKDB, but you can perform it against only one file group at the time. That means if you have huge partitioned database with "Sliding Window" partitioning structure, you need to run DBCC CHECKFILEGROUP only against your Primary group and the most recent/active group. There is no necessity to run it against old and well backed up data, which you can restore at any time in the case of corruption.
If you do not use "Sliding Window", you have an option to redistribute your checkup operation over time and do only one partition at a time to reduce overall pressure on the system.
In order to use that option, you have to have a partitioning in your huge database.

Option 4.

Split DBCC CHECKDB execution over its constituencies.
If you take a look at DBCC CHECKDB command description in MSDN you can find that this command contains three major parts: DBCC CHECKALLOC, DBCC CHECKCATALOG and DBCC CHECKTABLE. To reduce the pressure on your system you can run these portions separately. Even more, you can run DBCC CHECKTABLE against every table and even every index on your server.
Looks like the great idea: you simply load a service table with requests and service broker executes those DBCC checkups at any idle time of your server.
That approach looks so good that I decided to test it.

Option 4. Testing.

Test #1. I've created a script to collect all tables and indexes on a server. That script collected the data and in parallel run DBCC CHECKALLOC and DBCC CHECKCATALOG against each database on the server. As the second step it looped against the list of indexes and run DBCC CHECKTABLE against each of them.
Test #2. I've created another script just to loop over list of databases and run DBCC CHECKDB against each of them.

Option 4. Testing Results.

On my workstation Test #1 finished in 37 minutes and 23 seconds. Test #2 finished in just 7 minutes and 7 seconds.
The five times difference makes no sense of split DBCC CHECKDB on its constituencies. Even worse,
besides of doing DBCC CHECKALLOC, DBCC CHECKCATALOG and DBCC CHECKTABLE, it also validates the Service Broker data in the database and link-level consistency between table metadata and file system directories and files the filestream.

As the result of my research I can conclude that Option 4 is not really an option anymore.

Option 4.

*During the preparation of this post I also used materials from Paul Randal's blog post "Importance of how you run consistency checks"

Tuesday, March 22, 2016

Installing Polybase for SQL Server 2016 RC1

I've mention in my prior post about the problem of installation of Polybase:

Now I decided to go to Oracle web site and download Java Driver.

After upgrading My SQL Server 2016 up to RC1 I went to "Add features to an existing installation" section and checked "PolyBase Query Service":

As expected, I've got the same error as with Community Review version:

Then I went to Oracle Java driver download page:

I've reviewed Oracle License Agreement  (very recommend do the same before the download).

Then I've loaded two 64bit driver versions for Windows: jre-7u80-windows-x64.exe and jre-7u80-windows-x64.tar.gz.

If you are too picky (as me) and hate going to Oracle's web site you can download these files from my repository, but warn you, I can't guarantee that some hackers will replace them by infected copies:
Just in case will provide file sizes:
jre-7u80-windows-x64.exe - 31,239,592 bytes.
jre-7u80-windows-x64.tar.gz - 43,796,480 bytes.

Then I've started the installation:

Changed the driver installation folder:

And started installation:

After Installation was done,

I went back to SQL Server installation and clicked "Re-Run" checkup for "Feature Rules".
New screan immediately poped-up:

I clicked Next, and on the next screen changed PolyBase Engine Services to "manual" startup:

And finally, everything was successfully installed:

Now have to find the time to research and test the new feature.

Monday, March 21, 2016

SQL Server CPU utilization in Graphical form

Finally I can present something useful I've made with the help of my Spatial Data knowledge.
I will use Spatial Data to represent SQL Server CPU utilization.

How many times you connected to a SQL Server instance with complains to its performance?

What would you do the first?
I know that some people, instead of connecting to the instance open Remote Desktop Connection, which utilizes very important resources at the most critical time of the server, and simply run PerfMon.

I'd say it is not a bad idea, but it would be better to get these data remotely. Even better, if you look not at live counter, but load the file with counter's data, that would give you the historical perspective of your counters.
However, you must have these counters set. If you want to know how to do it, see my earlier post: "How to Automate collecting Perfmon Counters of Interest".

What if you did not know that and did not set your performance counters? Are you completely lost?

Not completely.
Benjamin Nevarez described in his blog on how to "Get CPU Utilization Data from SQL Server" and he inspired me to draw a diagram of the CPU utilization.

I've slightly changed his script and got the following:
WITH BR_Data as (
 SELECT timestamp, CONVERT(XML, record) as record
 FROM sys.dm_os_ring_buffers
 WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' and record like '%<SystemHealth>%'
), Extracted_XML as (
 SELECT timestamp, record.value('(./Record/@id)[1]', 'int') as record_id,
  record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'bigint') as SystemIdle,
  record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'bigint') as SQLCPU
), CPU_Data as (
 SELECT record_id, ROW_NUMBER() OVER(ORDER BY record_id) as rn,
    dateadd(ms, -1 * ((SELECT ms_ticks  FROM sys.dm_os_sys_info) - [timestamp]), GETDATE()) as EventTime,
    SQLCPU, SystemIdle, 100 - SystemIdle - SQLCPU as OtherCPU
 FROM Extracted_XML )
SELECT @gc = CAST((SELECT  CAST(d1.rn as VARCHAR) + ' ' + CAST(d1.SQLCPU as VARCHAR) + ',' FROM CPU_Data as d1 ORDER BY d1.rn FOR XML PATH('')) as VARCHAR(MAX)),
@gi = CAST((SELECT  CAST(d1.rn as VARCHAR) + ' ' + CAST(d1.OtherCPU as VARCHAR) + ',' FROM CPU_Data as d1 ORDER BY d1.rn FOR XML PATH('')) as VARCHAR(MAX))

SELECT CAST('LINESTRING(' + LEFT(@gc,LEN(@gc)-1) + ')' as GEOMETRY), 'SQL CPU %' as Measure
SELECT CAST('LINESTRING(' + LEFT(@gi,LEN(@gi)-1) + ')' as GEOMETRY), 'Other CPU %'; 
I've got following as a result:
Then I've selected the second tab and got my SQL Server CPU utilization diagram:

That diagram provides you about 260 last minutes of Server CPU usage and measured in percents.

As you can see my SQL Server is mostly doing nothing and only during that blog-post writing in the last half and hour or so it is heavily running test scripts to over-utilize my CPU, but it still barely goes more than 60% of CPU (Blue line).

The Red line represents all other processes besides of SQL Server and you can tell if anything else from outside is impacting your performance.

Hope you'll like it.

Sunday, March 20, 2016

SQL Saturday #486 in Richmond, VA.

It looks like I cover other SQL Saturday events much better than I cover our SQL Saturday in Baltimore, which is scheduled for April 30st.
Some people are asking me "Why do you do this?". That is probably because I want those people who have missed "that" SQL Saturday be jealous.

I'm very sorry to Stratford University. I did not make a picture of their wonderful facility because rain started later that day, but facility was very good and very convenient for SQL Saturday event.

I've started my SQL-photography session right after lunch.

Sponsors' tables:

Sponsor's sessions

B@B brought their CEOs to talk to the public. Such a wonderful idea for sponsors.

Speakers' room

SQL Games

Guess what it is? That is "Hierarchy"

Registration desk

Control center.

Greatest prize of the event. Drone with a camera.

Demonstration of features

Couple of failings.

And Success!!! Finally went to the skies.

SQL Games continued


Guess what it is. That is "Autog-routh"

That is a "Drop"


"Page split"

Socializing in the cafeteria

Drone is still flying.

And scaring people

My consultation costs $1000/hour

Starting after lunch sessions.
Jana Sattainathan with his "Small Scale Data Collection Using PowerShell - Going NSA Style!"

Mark Wilkinson with "Indexing 101"

Jamie Wick and "SQL Backups: Choosing the right solution for your environment"

Mark Hudson: "Introducing Azure Machine Learning"

Speakers in preparation

Azhee and "Getting to know U-SQL" (Did I spell it correctly?)

Gigi Bell: "Dealing With Difficult Managers"

William Wolf in preparation for his session about "Service Broker"

Chris Bell is ready to talk about "Optimizing Protected Indexes"

Tom Staab with "Introduction to Partitioning"

Brad McKuhen explaining "SQL Auditing"

Kevin Feasel in preparation for "Big Data, Small Data, and Everything In Between"

Tom Norman: "Beginning Azure"

Justin Randall: "Building Your Yellow Brick Road: Directing Your Path to a Successful Career"

Doug Purnell will start his professional development session in a moment.

Rob Volk. Last preparations for "The SQL - Revenge"

Raffle Time!!!

Dough making t-Shirt Bomb

Who is the first timer? Where you've been the last four years?

The first winner!

First set of prizes are paper books and electronic books.

Drawing has been done by Stratford University representative.

Starting Sponsors' Prizes

The most wishful prize of the event:

VACO representative is explaining how do you have to land a Drone without crashing your career.

Another wonderful prize - 23 inch monitor.

Ravi Kumar did not show up for a gift card and Rob took it.

Biggest prize of the event - Very well tested Laser Printer.

Brad got the very last prize.