SQL Server – Database Backup Compression


Yearns ago, we deployed Quest LiteSpeed to get backup compression.

We had to use 3rd party software to get backup compression.

But, that was SQL Server v2005.



Beginning with v2008 backup compression has been built in.




Wanted to get a feeling as to savings, and here is what the code looks like.



declare @numberofDays int
declare @dateCurrent datetime
declare @dateGoBack datetime

set @dateCurrent = getdate()
set @numberofDays = 7
set @dateGoBack = dateadd(day, @numberofDays *-1, @dateCurrent)

select top 3000

		, [startTime]
			= convert(varchar(30), tblBS.[backup_start_date], 100)

		, [finishTime]
			= convert(varchar(30), tblBS.[backup_finish_date], 100)

		, [durationInMins]
			= datediff(minute
						, tblBS.[backup_finish_date]

		, [backupSizeInMB]
			 = ( tblBS.[backup_size] / ( 1024 * 1024 ) )

		, [compressBackupSizeInMB] 
			= ( tblBS.[compressed_backup_size] / ( 1024 * 1024 ) )

		, [ratio]
			= cast
					( tblBS.[compressed_backup_size]
						/ tblBS.[backup_size]
					as decimal(10, 2)

		, tblBS.[type]

		, tblBS.[flags]

		, [hasBackupChecksumUsingSpecificColumn]
			= case ( tblBS.[has_backup_checksums])
					when 0 then 'No'
					else 'Yes'
		, [hasBackupChecksumUsingColumnFlags]
			= case ( tblBS.[flags] & (2 ^16) ) 
					when 16 then 'Yes'
					else 'No'
		--, tblBS.*

from   msdb.[dbo].backupset tblBS

where  tblBS.[type] in ( 'D' )

and    tblBS.[backup_start_date] >= @dateGoBack 

and     tblBS.[database_name] in ( 'xmaster', 'ppsivr')

order by
		  tblBS.[database_name] asc
		, tblBS.[backup_set_id] asc







  1. With MS SQL Server
    • Default is set to Backup Compression
      • With backup compression our file size is 25%
    • Requested Backup Checksum
      • Getting backup checksum
  2. With 3rd party software
    • No space savings
    • No Checksum validation
  3. Because we are testing the 3rd party tool, we are logging two full backups each day
    • At 6:30 PM, SQL Server backup
    • At 8 PM, the 3rd party backup
  4. Time Taken
    • Backing up our 8 GB database is taken about 3 minutes with compression
    • Without compression it is taking 1 minute


msdb.dbo.backupset – Column – Flags

One of the more interesting columns in the msdb.dbo.backupset table is the Flags column.

Though it is now deprecated, it holds some useful nuggets.

BTW, the information it contains is now exposed in individual bit columns.

Nevertheless, here is what each bit represents

 Flag Bits Bit
 has_bulk_logged_data 0
 is_snapshot  1
 is_readonly  2
 is_single_user  3
 has_backup_checksums  4



Using bit arithmetic, has shown in the SQL code above, one is able to decipher the Flags column.




Trace Flag

  1. 3023
    • If you are using backup applications or utilities that do not expose these options, you can enable the CHECKSUM option by using Trace Flag 3023 on the instance of Microsoft SQL Server.
      If Trace Flag 3023 is turned on, the CHECKSUM option is automatically enabled for the BACKUP command.
      If you do this, you do not have to rewrite all the existing backup scripts.


dbcc tracestatus with no_infomsgs


Starting with v2014, MSFT added an option through  sp_configure that can be used to setup the default checksum setting.

The option name is “backup checksum default”.


exec sp_configure 'show advanced options', 1
reconfigure with override

exec sp_configure 'backup checksum default'



To measure the impact of various backup offerings ( native versus third party) and options, we can use use the metrics listed below:


Courtesy of MSFT

  1. Windows I/O performance counters, such as the physical-disk counters
  2. The Device Throughput Bytes/sec counter of the SQLServer:Backup Device object
  3. The Backup/Restore Throughput/sec counter of the SQLServer:Databases object





  1. Transact-SQL Reference (Database Engine) System Tables (Transact-SQL)  Backup and Restore Tables (Transact-SQL) backupset



  1. Database Engine Features and Tasks>  Database Features  > BackUp and Restore of SQL Server Databases > Backup Compression


MSFT – Support

  1. How to enable the CHECKSUM option if backup utilities do not expose the option



  1. Erin Stellato
    • Trending Database Growth From Backups
  2. Xu James
    • trace flag for backup and restore operation
  3. Scott Caldwell, Remote DBA Experts
  4. Jugal Shah
    • T-SQL Script to Check the Native Compressed backup file size and Backup Compression Ratio

Using Markdown in WordPress


I have been meaning to understand and work with markdowns for a while here.



Let us setup our platform first.


Enable Markdown in Posts and Pages

From here, here is how to enable markdown in WordPress Posts and Pages:
To enable Markdown for posts and pages, go to Settings → Writing in your dashboard, and check the box labeled Use Markdown for posts and pages.
Click on Save Changes at the bottom of the page to apply. You will now be able compose posts and pages using Markdown.


And, to to enable markdown in Comments:

To enable Markdown for comments, go to Settings → Discussion in your dashboard, and check the box labeled Use Markdown for comments.
Click on Save Changes at the bottom of the page to apply.



Inline Links

To use inline links, enter the link text and surround it in square brackets.

And, enter the URL and surround in parenthesis.

Please make sure that there are no spaces between the ending square bracket and the parenthesis.

Here is a [link](http://github.com/DanielAdeniji/ "GitHub") to my GitHub repository.

Referenced Links

To use referenced links, enter the link text and surround it in square brackets.

And, enter a numeric ID to the URL you will later identify.

The numeric ID should also be enclosed in square brackets.

Later enter the Identifying ID. Enclose that in square brackets, as well.

A colon should follow and the URL should then be expressed.

A) Markdown - Basics [Link] [201]
B) Codebase - syntax-highlighting-in-markdown [Link][202]

[201]: https://daringfireball.net/projects/markdown/basics
[202]: https://support.codebasehq.com/articles/tips-tricks/syntax-highlighting-in-markdown "Dan Quinney"

Mixed List

Mixed List can be entered with numeric IDs to indicate the top list.

And, indented asterix to precede the nested list.

1. United States
     * California
     * Florida
     * New York
     * Texas
2. Canada
     * Nunavut
     * Quebec
     * Northwest Territories
     * Ontario
     * British Columbia
     * Alberta

Code Block

Code Block – C#

Code blocked can be entered within three ~ or `. The language identifier shall follow.

And, corresponding ~ ( ~~~ ) or ` ( “`) should indicate the completed code block.

public main()
    Console.WriteLine("hello world");



Sample Inline Link

Here is a link to my GitHub repository.

Referenced Links

A) Markdown – Basics Link
B) Codebase – syntax-highlighting-in-markdown Link

Mixed Lists

  1. United States
    • California
    • Florida
    • New York
    • Texas
  2. Canada
    • Nunavut
    • Quebec
    • Northwest Territories
    • Ontario
    • British Columbia
    • Alberta
  3. Australia
    • Multi-state/territorial
    • New South Wales
    • Northern Territory
    • Queensland
    • South Australia
    • Tasmania
    • Victoria
    • Western Australia

Code Block

Code Block – XML

<!--?xml version="1.0"?-->

   <book id="bk101">
      <author>Gambardella, Matthew</author>
      <title>XML Developer's Guide</title>
      <description>An in-depth look at creating applications 
      with XML.</description>
   <book id="bk102">
      <author>Ralls, Kim</author>
      <title>Midnight Rain</title>
      <description>A former architect battles corporate zombies, 
      an evil sorceress, and her own childhood to become queen 
      of the world.</description>


Code Block – C#

public main()
      Console.WriteLine("hello world");


I think if you attempt to use markdowns in WordPress posts, you will run into problem such as

  • markdown contents will be lost as you switch from Text to Visual Mode
  • There is a bit of “forciness” as to where whitespaces can go


  1. How to Write Faster, Better & Longer: The Ultimate Guide to Markdown
  2. John Gruber – Daring fireball – Markdown: Basics
  3. Codebase – Syntax highlighting in markdown
  4. WordPress – Markdown quick reference

SSH Connection to Linux Over Active Directory and Centrify


Need to start providing On call support to  some of our DB Servers that are running on Linux Hosts.

Being able to ssh to the boxes is essential.



Downloaded Putty from here.



Connection Attempt

Launched Putty.exe, thanks goodness no installation needed.

The Putty Screen is very minimal:

  1. Host Name or IP address
    • xxxx01
  2. Port
    1. 22




Connection Attempt




Using keyboard-interactive authentication.
Password :
Access denied

Active Directory

To do

Have your Active Directory support group add you to the impacted AD Group





net group [ADgroup] /domain
net group grpLinuxDBA /domain





Have Centrify Administrators do the following:

  1. Create an account for you
  2. Add you to an existing group that has access to the Linux DB Hosts
  3. Grant explicit sudo access  to created account or inherit from group membership

BTW, the Centrify product that we use is listed here.




Open Live Writer


Now that I am back commuting and have a work issued laptop, I find myself reading far less than I should.

But, writing a bit more.


Desktop Blogging Tool

I did not construct a careful, through discovery as to what the current climate looks like for desktop blogging tools.

But, always knew I wanted something that is minimalist.

My hopes has always been, that it:

  1. Would work on whichever the laptop’s OS is; in this case it is MSFT
  2. Is freely available
  3. Works Offline
  4. Though works offline, bi-directional synchs with WordPress


Open Live Writer

The tool that I am most familiar with is Live Writer.

As that tool is no longer being actively developed by MSFT, settling on Open Live Writer was not hard.



  1. Text Formatting
    • Very rich editing stencils
      • Ability to format text as Headers ( H1 thru H6 )
      • Supports selected text formatting options such as Bold, Italicize, underline, strikeout, subscript, superscript
    • List Handling ( Numbered and Bullet lists )
    • Align ( Left Align, Right Align, Center, Justify )
    • Font
      • Various fonts available in Windows and ability to specify sizes
    • Coloring
    • Block Quotes
  2. Grouping Tools
    • Table
    • Horizontal line
    • Split Post



  1. Picture
  2. Video
  3. Maps – Bing Map
  4. Tags – Supported Providers includes Technorati, Live Journal, Flickr, del,icio.us, Icerocket, 43 Things, Buzznet


Blogging Platform support

  1. Saving and Publishing
    • Post draft to blog
    • Publish post
    • Open recent post
    • Set post attributes
  2. Categories
    • Connects to Blogging Platform and downloads & avails your latest category list
  3. Tags
    • As categories are freetext they are not downloaded
    • Continue to use them Ad-hoc


Planned Features

There are features that have a bit of plumbing built in, but not yet fully baked.

Among those is support for plugins.



BTW, Open LiveWriter is available here.

SQL Server – sys.dm_exec_query_plan error when database is offline [ Msg 942 // cannot be opened because it is offline ]


One runs some query and they end up being cached.

In a little while later, take the Database offline.

Later when one runs diagnostic queries, it is possible that errors might pop up



	print '@@version ' + @@version
	;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
	, ctePlan
					= cp.plan_handle
		from   sys.dm_exec_cached_plans cp

		CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp

	select count(*)
	from   ctePlan



@@version Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64)
Jun 17 2016 19:14:09
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

Msg 942, Level 14, State 4, Line 3
Database 'ReportServer' cannot be opened because it is offline.


Problem Identification

The problem is not with sys.dm_exec_cached_plans, but sys.dm_exec_query_plan.

It appears that a database has to be online to get its query plan.



To reproduce with Microsoft’s tools:

  1. Ensure that Reporting Services is installed and configured
  2. Using the services applet, stop the Reporting Services service
  3. Take the ReportServer and ReportServerTenpdb databases offline
  4. Issue the aforementioned query

Connect Items

  1. Join sys.dm_exec_cached_plans & sys.dm_exec_query_plan :- Error Message “cannot be opened because it is offline”
    ID :- 3115135
    Date :- 12/7/2016 12:28 PM
    Type :- Bug
    Status :- Active
  2. MS Connect Broken – Unable to upload files
    ID :- 3115137
    Date :- 12/7/2016 12:51 PM
    Type :- Bug
    Status :- Active

Leonard Ravenhill


Leonard Ravenhill (June 18, 1907 – November 27, 1994) was an English Christian evangelist and author who focused on the subjects of prayer and revival. He is best known for challenging western evangelicalism (through his books and sermons) to compare itself to the early Christian Church as chronicled in the Book of Acts.

His most notable book is Why Revival Tarries which has sold over a million copies worldwide.

Among others influenced by Ravenhill were Keith Green, Ravi Zacharias, Tommy Tenney, Steve Hill, Michael L. Brown, Charles Stanley, Bill Gothard, Paul Washer, and David Wilkerson.

He was a close friend of pastor and writer A.W. Tozer.

Through his teaching and books, Ravenhill addressed the disparities he perceived between the New Testament Church and the Church in his time and called for adherence to the principles of Biblical revival.
Tozer said of Ravenhill:

To such men as this, the church owes a debt too heavy to pay. The curious thing is that she seldom tries to pay him while he lives.

Rather, the next generation builds his sepulchre and writes his biography –

as if instinctively and awkwardly to discharge an obligation the previous generation to a large extent ignored“.



  1. The greatest miracle that God can do today is to take an unholy man out of an unholy world and make him holy, then put him back into that unholy world and keep him holy in it.
  2. A man who is intimate with God is not intimidated by man.
  3. No man is greater than his prayer life. The pastor who is not praying is playing; the people who are not praying are straying. We have many organizers, but few agonizers; many players and payers, few pray-ers; many singers, few clingers; lots of pastors, few wrestlers; many fears, few tears; much fashion, little passion; many interferers, few intercessors; many writers, but few fighters. Failing here, we fail everywhere.
  4. If we displease God, does it matter whom we please? If we please Him does it matter whom we displease?
  5. You can’t live wrong and pray right.
  6. Book has become a Curse, borrow your sermon, it can’t be yours anyhow
  7. The only reason we don’t have revival is because we are willing to live without it!
  8. I am not angered that the Moral Majority boys campaign against abortion. I am angry when the same men who say, “Save OUR children” bellow “Build more and bigger bombers.” That’s right! Blast the children in other nations into eternity, or limbless misery as they lay crippled from “OUR” bombers! This does not jell.”
  9. Today’s church wants to be raptured from responsibility.
  10. Are the things you are living for worth Christ dying for?
  11. A sinning man stops praying, a praying man stops sinning
  12. God pity us that after years of writing, using mountains of paper and rivers of ink, exhausting flashy terminology about the biggest revival meetings in history, we are still faced with gross corruption in every nation, as well as with the most prayerless church age since Pentecost
  13. The Church used to be a lifeboat rescuing the perishing. Now she is a cruise ship recruiting the promising.
  14. If Jesus had preached the same message that ministers preach today, He would never have been crucified.
  15. I fear the prayers of John Knox more than all the armies of England – Queen of Scotland ( Link )
  16. If you stand before God, you will never kneel before man
  17. A christianity that is not supernatural, is superficial
  18. Faith that is going to be trusted, is gonna be tested
  19. God sends a fire on the sacrifice, not on the altar


  1. The Baptism Of Fire – Leonard Ravenhill
    For a complete list of indexed sermons and verse by verse teachings, visit https://www.TheBodyofChrist.us
    Date Published :- 2016-Feb-24
  2. The Cost of Discipleship by Leonard Ravenhill
    Powerful preaching by Leonard Ravenhill http://www.OpenAirOutreach.com
    Date Published :- 2016-Jan-12
  3. Prophet Elijah Leonard Ravenhill
    Powerful sermon by Leonard Ravenhill http://www.OpenAirOutreach.com
    Date Published :- 2016-Jan-05
    Date Added :- 2016-Dec-07
  4. Leonard Ravenhill – Moved by the Holy Spirit | Full Sermon
    Date Published :- 2016-July-20


The Baptism Of Fire – Leonard Ravenhill

  1. Nicholas Bangle?
    • I want to get back to Africa as soon as I can.
  2. God locks nations up
    • Israel
      • Under Pharaoh for 400 years
      • Between Old and New testament
  3.  Prophets?
    • Pastor denouncing Prophets
      • If that is your theory, hold on to it
      • If there are no prophets, then there are no evangelist
      • Better tear out Ephesians 4
        • And he gave the apostles, the prophets, the evangelists, the shepherds and teachers ( Ephesians 4:11 )
    • They finished with the Apostolic Dispensation
    • Calling
      • Evangelist tells you that rid of your sin or it with hurt you
      • Prophet tells you not that your sin with hurt you, but that it is hurting God
  4. Zechariah
    • He went into the Temple
    • And, the Angel said “Fear Not
    • Once in his life, he goes into the Temple and minister to God
    • It is very precise
      • If he sees the Angel on the right hand side, it means he has a message for the Individual/Him
      • And, if he sees the Angel on the left hand side, it means he has a message for the People/Nation
  5. John the Baptist
    • Came unto the Scene in a difficult day
      • John The baptist came into a totalitarian world, a sophisticated world
        • The Romans had it in their grips
        • The Greeks had it in the grips
        • The Jews had a monopoly on God, or so they thought
        • And, then God sent this man
      • The Angel said he “shall be great in the sight of God
      • I, leonard, was in a conference once and there were all these men
        • They all had advanced degrees
        • BA, BS, Doctorate degrees
        • Common fellows I am not despising your learnings
        • But, do you think the Devil is scared of all those degrees hanging in your Office
      • But, no John the Baptist shall be great in the sight of God
        • Now, that is something you can covet will all your mind and soul
      • What is the Proof that you have been filled with the Holy Ghost
        • That you a Holy Life
      • We must be close to the end time
        • No, the Bible says it is the beginning of Sorrow
      • A Baptism of Life Change
        In the fifteenth year of the rule of Caesar Tiberius—it was while Pontius Pilate was governor of Judea; Herod, ruler of Galilee; his brother Philip, ruler of Iturea and Trachonitis; Lysanias, ruler of Abilene; during the Chief-Priesthood of Annas and Caiaphas—John, Zachariah’s son, out in the desert at the time, received a message from God. He went all through the country around the Jordan River ( Luke 3:1-5 [ Message Version] )
      • Why did God put all that detail there
      • He was trying to tell you this people are not simple things, they were living in Slave conditions
      • Dominion of invading power
      • They had no rights
    • John had a man and hid him in the wilderness
  6. Moses
    • Backside of the Mountain
    • God shaped him year and year
    • For 40 years
  7. Jesus waited while John the Baptist was drawing a crowd
    • Jesus stood still with an hammering nails
      • making a coffin
  8. Man with a ministry, in the will of God, has perfect peace even if there is an earthquake outside
  9. In John 17, Jesus said that the “Time has come
    • That is the seventh time he has said that
    • Jesus knew the Divine Timetable
      • He knew when to move, and when not to move
  10. They could not hitch him on to any Political Wagon
  11. Problem with you Prophet is not that you do not know enough, it is that you know too much
  12. Difference between Evangelist and Prophet
    • Evangelist has to go looking for work
    • People go looking for Prophet
  13. I said it must be nice to move from Russia to Australia
    • You must be thrilled to be in Australia
      • Plenty of Food
      • Plenty of Clothes
    • No response
    • It must me nice to worship in a place like this
    • I will go back to Russia tomorrow, if I could get there
    • I want God
      • When we meet in secret, God is there
      • Hidden in mountains, hidden in caves
        • I felt the presence of God more than I felt in Australia
  14. I have an invitation to go a 10,000 seat auditorium in Sweden
    • But, I don’t bother to go
    • But, I came here
    • Why, because I don’t God wants me to go them
    • He told me to go to the Feeble minded and I came


Prophet Elijah Leonard Ravenhill

  1. Revival at the family level, which is the most needed today
  2. Secret to preaching
    • To him that trembles at my Word
      • Has not my hand made all these things, and so they came into being?” declares the LORD. “These are the ones I look on with favor: those who are humble and contrite in spirit, and who tremble at my word. ( Isaiah 66:2 )
    • Holy Ghost sent down from Heaven
      • Jesus never preached till he was ordained
      • Ordained by a Bishop?
      • No one can ordain another man
    • The ones in the Room in Acts
      • John 15
  3. Conditions of Preaching
    • Joel 2
      • “Yet even now,” declares the LORD, “Return to Me with all your heart, And with fasting, weeping and mourning; 13And rend your heart and not your garments.” ( Joel 2:12 )
  4. Preacher says that he has 400 filled students
    • But, nobody in town knew
    • But, then they knew when there were an hundred and twenty in town
      • At a time when about 120 disciples had gathered together, Peter got up and spoke to them.
        He said, “Brothers, what the Holy Spirit predicted through David in Scripture about Judas had to come true ( Acts 1:15 -16)
  5. If you stand before God, you will never kneel before man
  6. Separate Aaron so that they may minister to me
  7. The word of the Lord came unto me
  8. No Rain
    • Deuteronomy 11:16- 17
      • so there’s no rain and nothing grows in the fields ( Deuteronomy 11:16- 17 )
    • No one else keep their word, God you better keep yours
    • These men love God much, and they hurt when God hurts
  9. God has all things including computers
    • Now they have all things, but possess nothing
  10. False Prophets
    • Now they end up at Jezebel’s Table
  11. The God that answers with fire
  12. Prayers not answered
    • Broken vows
  13. Restitution
  14. Bonke
    • Working in Africa
    • Christ for all Nations, and Not Christ for all donations
    • If you move forward and take anymore from that woman, the Hippopotamus will take care of you
      • Remember Abraham
        • When he came back from fighting for Lot
    • Faith Promise?
  15. A christianity that is not supernatural, is superficial
  16. Faith that is going to be trusted, is gonna be tested
  17. Everybody can write a book
    • Plenty of Ahab, but just a little bit of Elijah’s
    • Anyone can write read a bible and read some references and catch every little thing that come to mind
    • But, what if I meet Ahab in the way
    • What about Roman Catholic Church, Mormonism, jehovism
    • A guru at Berkeley
      • Drews 2000 students at lunch time
    • More false priest than
      • A guru at Berkeley
        • Drews 2000 students at lunch time
    • All these guys with heresy can drew a crowd, but we can not
    • Paul said my gospel is not in words only, but with Power
  18. At lunch time, lady came up
    • Building an Altar is one thing, does not mean God will send an Altar
    • God sends a fire on the sacrifice, not on the altar
  19. If Elijah gets on TV one morning
    • Do you think they will invite him again
  20. Wise men followed a Star
    • Ended up 7 miles away from the main city
  21. Deeply disturbed by the condition of the Church
  22. Elijah
    • Ahab was after him
    • Jezebel was after him
  23. Jesus Sufferance
    • Suffer for him
      • Paul called
        • Anais called to speak to him and warn him about what he must suffer for him
    • His personal suffering
      • Demon possessed
      • Deaf
      • Blind
      • Corpse
      • Temple that has lost its glory
      • Agony of his suffering
        • 2 days later he had a stroke
      • Do not rest in your experiences
      • Generation
        • Point of downward stream
  24. Moral Majority
    • Yes, he had 5000 at the feeding
    • But, only 120 cam out to worship in the room in Acts
    • Determined to be a person with more than a knowledge of God, decide to be alone
    • Not a born in day
      • Years of quietness
  25. Lord my altar is broken
    • My prayer life
    • My devotional life
    • natural affection has gotten in the way of spiritual affection



The Cost of Discipleship by Leonard Ravenhill

  1. Paul
    • God forbid that I should boast
      • Journey
        • Left Roman where they worship the mind
        • To Corinth’s where they worship the body
      • Natural Life
        • He began his life in the historic capital of the World Tarsus
        • He ended his life in the military Capital of the World Rome
        • He went to the intellectual capital of the World ( Acts 17)
      • Yet Burdened
        • By false Gods
        • Where people worship false Gods
          • Roman Catholicism
          • Mormons
          • Jehovah Witness
  2.  Ananias
    • Glad man stayed after prayer to listen to God
  3. The Lord has appeared to make you a minister ( Acts 9 )
  4. And, then he disappeared, he went into the wilderness
    • Paul became spiritually pregnant
    • Little children for which
    • 14 apostles
      • Merciless Books
  5. Evangelical movement but it has no effect
    • Fullness of God
  6. All these boys are rushing to Washington
    • Christianity was born in Oppression
    • Yet, jesus never said anything about Slavery
    • They dare not put God’s house right, but they want to put the White House right
    • Our problems will not be solved in the Oval Office, but in God’s prayer house