Using BMC Control-M to Automate Tasks

My largest client recently purchased BMC Control-M to use as our enterprise scheduler. Since this product went live in our environment I have been watching it displace other schedulers to become the centralized scheduler.  As I have learned more about the capabilities of Control-M as well as becoming more alert to seeing references to the product in the market place I have become a huge fan.

Being a database professional I have become very attached to SQL Server Agent for scheduling anything SQL related.  However I know the limitations of SQL Server Agent when it comes to the SQL task being one of many factors in a much larger process.  Over the years I have seen very complex processes implemented to schedule a multi-process job. In cases where one process (non sql) creates a file that another process (sql job) ingests, manipulates, and outputs additional files that will be used by another process (non sql). Those three jobs have to run in a particular order so the IT admins have to get creative with scheduling.

What I have found with Control-M is that it can schedule each task to run in a workflow so each job becomes a task for a single process flow within Control-M.  The design of the workflows in Control-M is much like SSIS.  The major benefit with letting Control-M execute these processes is the ability for it to monitor each process, send notifications on success or failure and immediately kick off the next process in the workflow after successful completion of the previous task.

As a DBA giving up control over scheduling my jobs was an uneasy feeling, however if you are like me, many times you have a job fail due to a dependency. For me it is usually that a file my job is importing didn’t arrive.  If this job was part of a Control-M process then my job would not have ran due to the dependency failure and whomever is responsible for the file I need would be notified.

Our most recent big success with BMC Control-M for Cloud was installing the module for VMware.  We have virtual servers that need weekly reboots (mostly web servers) and using Control-M for Cloud has automated that process eliminating the need for a VMware Admin or someone in our Network Operations Center from having to issue these reboots.  A good friend of mine blogged about his experience setting that up here >>


Posted in BMC Control-M | 8 Comments

The Primary Filegroup Cannot be Backed Up as a File Backup

File Group backups are great when working with very large databases (VLDB’s) that have been partitioned.  Typically when I come across File Group backups implemented in production the database is using the full recovery model. With the full recovery model you can specify individual file groups for backup. A very common backup strategy for VLDB’s is to partition the non-changing data to secondary file groups so that only current data is being written to the primary file group.  This allows for a more efficient backup solution as well as recovery plan.  Performing file group backups allow for a piece meal restore meaning you can bring data back online in order of importance.  Backing up an individual file group can be done using syntax much like:


This TSQL statement will work if the database is utilizing the full recovery model, but what if the recovery model is in simple? In that case specifying FILEGROUP = ‘PRIMARY’ would not work.  You would get a message that states

Msg 3004, Level 16, State 1, Line 19
The primary filegroup cannot be backed up as a file backup because the database is using the SIMPLE recovery model. Consider taking a partial backup by specifying READ_WRITE_FILEGROUPS.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

How would you backup the primary file group?

In this case you would need to specify ‘READ_WRITE_FILEGROUPS’ in the statement.


If your secondary file groups are not read-only then they would also be backed up. If you are not writing data to those secondary file groups then you should consider marking them read-only.  There are added benefits to them being read-only in addition to this backup methodology.  You can read more about that here



Posted in Backups/Recovery, Query Tidbits, SQLServerPedia Syndication | Leave a comment

How to check for last SQL Server backup

As a database professional, I get asked to review the health of database environments very often. When I perform these reviews, one of the many checks I perform is reviewing backup history and making sure that the backup plans in place meet the requirements and service level agreements for the business. I have found a number of backup strategies implemented using full, differential and transaction log backups in some fashion.

In more cases then I would like to share, I have found business critical databases that are not being backed up properly. This could be in the worst case having no backups or a backup strategy that does not meet the recoverability requirement of the business.

When doing an initial check I gather many details about the environment. Regarding backups, I capture things such as recovery model, last full backup, last differential, and the last two transaction log backups. Having this information will allow me to determine what the backup strategy is and point out any recover-ability gaps.

Some examples I have found are 1) no backup’s period, 2) full backup from months ago and daily differentials. In this case the full had been purged from the system, 3) Full backup of user database in Full recovery mode with no transaction log backups, 4) Proper use of weekly full, daily differential, and schedule transaction log backups – however the schedule was set to hourly and the customer expected they would have no more than 15 minutes of data loss.  I am happy to report that I do find proper backup routines that meet the customers’ service level agreement too.

The code I like to use for this check is below.

        a.Name AS DatabaseName ,
        CONVERT(SYSNAME, DATABASEPROPERTYEX(, 'Recovery')) RecoveryModel ,
        COALESCE(( SELECT   CONVERT(VARCHAR(12), MAX(backup_finish_date), 101)
                   FROM     msdb.dbo.backupset
                   WHERE    database_name =
                            AND type = 'D'
                            AND is_copy_only = '0'
                 ), 'No Full') AS 'Full' ,
        COALESCE(( SELECT   CONVERT(VARCHAR(12), MAX(backup_finish_date), 101)
                   FROM     msdb.dbo.backupset
                   WHERE    database_name =
                            AND type = 'I'
                            AND is_copy_only = '0'
                 ), 'No Diff') AS 'Diff' ,
        COALESCE(( SELECT   CONVERT(VARCHAR(20), MAX(backup_finish_date), 120)
                   FROM     msdb.dbo.backupset
                   WHERE    database_name =
                            AND type = 'L'
                 ), 'No Log') AS 'LastLog' ,
        COALESCE(( SELECT   CONVERT(VARCHAR(20), backup_finish_date, 120)
                   FROM     ( SELECT    ROW_NUMBER() OVER ( ORDER BY backup_finish_date DESC ) AS 'rownum' ,
                              FROM      msdb.dbo.backupset
                              WHERE     database_name =
                                        AND type = 'L'
                            ) withrownum
                   WHERE    rownum = 2
                 ), 'No Log') AS 'LastLog2'
FROM    sys.databases a
        LEFT OUTER JOIN msdb.dbo.backupset b ON b.database_name =
WHERE <> 'tempdb'
        AND a.state_desc = 'online'
GROUP BY a.Name ,

Ensuring that you have backups is crucial to any check of a SQL Server instance. In addition to ensuring that backups are being created, validation of those backups is just as important. Backups are only valid if you can restore them.

When I have the opportunity to share my experiences of backup and recovery with people I always like to share about how to backup the tail end of a transaction log and how to attach a transaction log from one database to another in order to backup the tail end of the log. I have created a couple of videos on how to accomplish this that you can view using this like

Posted in Backups/Recovery, Query Tidbits, SQLServerPedia Syndication | 20 Comments

SSIS Training class by Andy Leonard




Andy Leonard (b | t | l | f) of Linchpin People, has announced a new training class called ‘From Zero to SSIS‘. The first class is being offered in Reston, VA, May 19-23 2014. Andy is one to the top guru’s of SSIS. If you are looking to improve your SSIS skills then you need to attend SSIS Training from the master!

Course Abstract:

“From Zero to SSIS! is designed to train technology professionals in the fine art of using SQL Server Integration Services (SSIS) to build data integration and Extract-Transform-Load (ETL) solutions. The course is focused around labs and emphasizes a hands-on approach.

The target audience for this training is database professionals, application developers, and business intelligence practitioners interested in acquiring or expanding their existing SSIS skill set. No experience with SQL Server or SQL Server Integration Services is required before attending this training. It is helpful (but not required) that students possess some knowledge of and experience with relational databases. SQL Server knowledge/experience will be more helpful than experience and knowledge with other technologies.”

You can find more details here, if you plan to attend the register for the event at here. Why attend an in person class?  Unlike proctored classes, this class is taught by an industry expert on the subject. Hundreds of hours have gone into perfecting this class so that you learn real world knowledge that you can immediately apply to your job.  You get hands on experience and access to a true master.

Posted in Uncategorized | Leave a comment

How to reset the SA password in SQL Server

Getting locked out of a SQL Server can happen in a number of ways.  You could inherit a server that was managed by someone that left the company and only that person had System Admin rights for example. A recent encounter I had was were a database server was built and provisioned in one active directory domain and then moved and joined to another non trusted domain. All the accounts provisioned within the server include those for the DBA admins were basically useless since they couldn’t be authenticated.  The built in SA account is locked down per policy so no one knows the password.

What do you do if you find that you are locked out of a SQL Server instance? The way I handle it is to start SQL Server in single user mode, launch SQLCMD and create a new user. I then still using SQLCMD grant the new user the system admin role.  Once I have the new user created I restart SQL Server Service and log in using the new credentials. I can then clean up the instance by granting proper access.

If this was a newly inherited server I then take the time to do a health check of the instance to make sure that everything is to our standards. (Usually this means more work)

To start SQL Server in single user mode I open a command prompt as an administrator

I type the following minus the quotes (“ “)

“net stop mssqlserver” and press enter

“net start mssqlserver /m” and press enter

“sqlcmd” and press enter

“CREATE LOGIN security WITH PASSWORD = ‘Rec0very123’ “ and press enter

“GO” and press enter

“sp_addsrvrolemember ‘security’, ‘sysadmin’ “ and press enter

“GO” and press enter

“quit” and press enter

“net stop mssqlserver” and press enter

“net start mssqlserver” and press enter

You have now stop and restarted mssql in single user mode, created a new login called ‘security’ and given the user ‘security’ system admin rights. You then stopped and started mssql to put it back in multi user mode. I have included a screen shot of me completing the task on a development workstation so you can see the syntax and steps.

At this point you can connect to the server with SQL Server Management Studio with the new user and reset the SA password and or provision the proper people access.


Posted in Uncategorized | 9 Comments

Children at Technical Conferences

I have had the pleasure of taking my son with me on a number of trips where I was a presenting at a technical conference.  My son loves to travel with me and to get to see exciting new places. I have seen a big change in him from the experiences of getting to meet my technical community and even some of my friends kids.

I must say that his first trip probably spoiled him a bit since it took 4 flights to get us to our destination and back home. I took him with me to Curacao which is a little Dutch island in the Caribbean.  We enjoyed getting to drive around the island, snorkel, swim and enjoy some great food.  I thought that would be his favorite trip.

A few months later I took him to New York City for a SQL Saturday.  For this trip I contacted the event organizers and stated I would be bringing my son and asked if others would be bringing any children as well.  My friend Robert Pearl (blog | twitter) said he was bringing his son and a couple of vendors would have their kids there too.  The nice folks in NY reserved a room dedicated for the kids to hang out in and they had a great time playing games, watching movies and shopping at the vendor booths. I was able to take my son sight seeing all over Manhattan so he could see the buildings, statues, parks and monuments he only gets to see on TV.  My son came alive on that trip.

The same year SQL Saturday Washington DC came up and I was selected to speak along with my friends Mike Walsh (blog | twitter), Andy Leonard (blog | twitter) and Brian Moran (blog | twitter). We all brought one of our little ones with us and turned the Friday before the event into a sight seeing day. Our kids bonded immediately and had a great but exhausting time together. We saw so many monuments during the day and took hundreds of photos while getting in a lot of exercise.  The day of the event the kids played xbox 360 at the Microsoft campus further embedding their friendship. This event again made room for the kids to have a private place to hang out.

Again for SQL Saturday Atlanta a few of us brought our kids to the event and worked in some sight seeing at the aquarium. The kids got to reunite, the adults got to bond some more all the while getting to share our professional life with our kids.

My most recent event was taking my son to SQL in the City in Atlanta. We had a camping trip that started that evening so it just made sense to allow my son to skip a day of school so he could come with me to SQL in the City.  It helped that he already had a Red Gate shirt from some 2013-10-11 10.39.21SWAG from a previous trip and he also wanted to meet the Scary DBA. He wanted to use red gel to spike up his mohawk and became the mascot of Red Gate for the day. I even snapped a photo of him and Grant Fritchey (blog | twitter).  When my son first got his new hair cut I snapped a picture of him and posted to facebook. He was trying his hardest to make his mean face and was wearing his Red Gate shirt. Everyone instinctively starting tagging Grant.

I urge any of you that have kids to let them tag along with you to a technical conference. My son has grown and developed so much from being involved in my technical community. He has friends all over the world now and can’t wait until he can start working in technology. All of his teachers have shared with me how much he has advanced from these opportunities. It has also been a great bonding opportunity for me and my son. He can’t wait for his next trip and I am counting down the days until I can start taking my middle daughter with me.



Posted in Uncategorized | 3 Comments

Recovering the tail end of the transaction log

Knowing a great deal about backup and recovery is key to any DBA’s career. Most of us fully understand the recovery models, recovery point objectives and recovery time objectives. What I have found from speaking at many SQL Saturday’s and User Groups is that most DBA’s do not regularly practice recovery strategies.

It is very important for a DBA to know how to recover their environments which could include recovering the master database, performing a point in time restore or possibly a piece meal restore.

In one of my sessions “Know Backup, Know Recovery” I demonstrate performing a tail log backup after deleting a my data file.  That demo gets a lot of praise, however I decided to take it a step further and demonstrate attaching a transaction log to another database and recovering the tail end of the log. I then perform a restore of my full and tlogs including the new created tail log backup. The audience has always been very impressed with that demo. My favorite response was “holy &%^#, you can do that”. I commented back that yes I can, and now you can too.

As a matter of fact, this is my Lightning talk presentation at the 2013 PASS Summit.  I gave this presentation to my User Group this week and decided that I would record it and upload it to my YouTube channel for the rest of the world to enjoy. With that being said, if you have never seen this performed, take less than 5 minutes out of your busy day and watch the video. “Ultimate Tail Log Backup and Restore”

I hope you never have to perform this in a production environment, but at least prepare yourself and know how to do it.

Posted in Backups/Recovery, Presentations, SQLServerPedia Syndication | 1 Comment

PASS Elections part two

Tim FordTim Ford - Photo - 2013 (3) is running for a seat on the PASS Board of Directors and has my vote. Yes I think we need to have a “TIM” on the Board of Directors. 🙂

I have known Tim for a few years now. We have attended numerous events together over the years and contributed to a number of panel discussions. I have always valued Tim’s opinions and found that we share many of the same views.

Tim has been involved in the SQL Community practically since its inception. Tim first began volunteering WAYYYY back in 2002. He has been around through some of the highs and the lows and is willing and desiring to jump back in and continue making a difference. I truly feel that with Tim’s history with PASS, he can bring a tremendous amount of value to our BOD.

Tim also founded SQL Cruise which has to be one of the coolest locations to get high end SQL training.

Take a few minutes and read all about Tim on his candidate profile page by <<CLICKING HERE>>

Posted in SQLServerPedia Syndication, Tibits | 2 Comments

PASS Elections

I as well as many others have a decision to make on who I am voting for the PASS Board of Directors. I haven’t made my mind up completely for all the votes I get to cast but one vote for sure is for Allen Kinsel.

Why is my mind made up for Allen you may ask, well I have known Allen since I got involved with PASS many years ago. Allen has been there to help me with all the volunteer work I have done. Allen is an awesome role model for so many others when it comes to volunteering and helping PASS grow.

I value Allen’s goals and interest of PASS. I share many of the same beliefs that he has in the direction PASS needs to be going and growing.  A major focus of Allen’s is going to be in IT. As an organization for IT professionals, we need more of an investment in our own IT to promote our organization. There has been great strides in the past couple of years and we don’t need to lose that focus. Allen can make sure that we continue to move forward.

There are many other great candidates and I am looking forward to reading each and every profile. You can see Allen’s by <<clicking here>>


Posted in SQLServerPedia Syndication, Tibits | 1 Comment

Get OS Counter Memory Available Mbytes Using TSQL

I have been working recently in making sure that max memory is configured for the hundred plus database servers I support.  In reading numerous books, blog post and attending various talks on memory management people reference the OS memory counter “Available Mbytes” a lot. Various references state that this value should be between 150 and 300.  This would ensure there is always available memory for the OS and applications to grab if needed.

By setting max memory in SQL Server that allows enough memory for the OS you can help make sure you don’t choke out the OS and cause a scenario where SQL and the OS are having to compete for memory.  If you are also running SSAS, SSRS, SSIS, or an application on the SQL Server then you would want to reserve even more memory for the OS.

After following some guidance on what should be the starting point of how much to allocate to SQL and how much to the OS, the next thing I needed to do was monitor ‘Available Mbytes’ OS memory counter to make sure I wasn’t still starving the OS. What I found in most scenarios is that I left plenty of memory on the table.  In this case I can increase the max memory size in SQL Server.

When I was sharing this with a couple of friends the debate broke out on how much did I leave.  I am a more reserved kind of person so I left around 1 GB in most cases.  Memory is cheap these days so I like to be a little extra careful, others would debate to trim it down further. I will continue to monitor the counter and will continue to tweak, but I honestly would be more concerned that I am starving SQL or have a badly tuned box if I had to trim the memory down so low for the OS to keep giving SQL more.

In doing all this, I wanted an easy way within SQL Server to get the memory counter and there didn’t appear to easy of a way to do so. What I found was I could easily grab this with Power Shell so I cheated and within SSMS I call Power Shell to return the value. I had to get a little creative with the output that I pull back in order to clean it up. It isn’t pretty but the following script works well.

I have validated this with 2005/2008/2008R2/2012

AvailMbytes VARCHAR(255)
SET @sql = ‘powershell.exe Get-Counter ”\Memory\Available MBytes”’
INSERT  #output
EXEC xp_cmdshell @sql

UPDATE  #output

SET     AvailMbytes = REPLACE(RTRIM(LTRIM(AvailMbytes)), ‘ ‘, ”)
DELETE  #output
WHERE   AvailMbytes IS NULL
OR AvailMbytes = ”
OR AvailMbytes LIKE ‘—-%’
OR AvailMbytes LIKE ‘timestamp%’
OR AvailMbytes LIKE ‘%\\%’

SELECT  AvailMbytes
FROM    #output

DROP TABLE #output

Posted in PowerShell, SQL Tuning, SQLServerPedia Syndication | 1 Comment