Guest blogger Cláudio Silva: Why Expand-SqlTLogResponsibly?

Hey yall! I invited one of the most active dbatools developers, Cláudio Silva, to do a guest post about his motivation for joining the team and why he built this particular command. So without further ado… here’s Cláudio :D

Hi everyone my name is Cláudio Silva, I’m a MS SQL Server DBA, and have worked with SQL Server since version 2000. Previously, I worked as SQL developer for 8 years before becoming a full time DBA. I’m also a PowerShell enthusiast!

I was invited by no one but PowerShell MVP Chrissy LeMaire (thank you Chrissy!), to share why I come up with the idea of develop this function in PowerShell and why give it away to community.

I will start with the second part. When I first discovered the dbatools project I was pleasantly surprised by such a project that is so useful. Remember when SQL Server 2005 mainstream support ended last April? Well, like many other organizations, we will have to migrate so I decide to analyse, test it and get to know the limitations of dbatools.

When I saw that is an open source project, that Chrissy was soon coming to Portugal to present about migrations at TugaIT, I thought I want to share with her my opinions and be part of the team. There are so much fun work we can develop and help the community that being help me on the last years. This is my opportunity to give back some love! And here I am.

Now, going to what really matters: why did I create a PowerShell function (Expand-SqlTLogResponsibly) to help SQL Server database transaction log to growth responsibly?

A little background

Transaction logs are one of the most important parts of every database and it has a performance side that should not be neglected.

The transaction log is composed by one or more VLF (Virtual Log Files). Having a transaction log with too many VLFs can hurt your database performance in many ways.


  • Transaction log backups may slow down
  • Database recovery can take to much time (I have seen by my own hours for recovery after database shutdown just because transaction log has more that 1000 VLFs)
  • Also can affect insert/update/delete performance.

Ok, but what is a Virtual Log File and how they are created?

When we say to SQL Server growth (or auto-growth hit) the transaction log file SQL Server is dividing that amount of space in various “logic files” (VLFs) with the same size. Here, the number of VLFs vary from the quantity of space that is being added.
Read more about it in the following link from Paul Randal (b), also note that in SQL Server 2014 this algorithm has changed (Important change to VLF creation algorithm in SQL Server 2014).

How can I see how much VLFs I have right now?

You can use the command DBCC LOGINFO (‘<database name>’) to know how many VLFs your transaction log. Each row returned is one VLF.

What have we to do to fix this value?

We have to do 2 major steps:

  1. Shrink the transaction log size
  2. Grow the file manually to a specific size
  1. We need to take into account the database RECOVERY MODEL because if database is using FULL recovery model then we will need to backup transaction log and only after that we can use the DBCC command “DBCC SHRINKFILE (‘<logfile_name>’, TRUNCATEONLY)”. After that you have to check your transaction log size and/or number of VLFs in order to know if you have to repeat this step (BACKUP/SHRINFILE)
  2. You need to use:

ALTER DATABASE <databasename> MODIFY FILE (NAME = ‘<transactionloglogicalfilename>’, SIZE = <newtotalsize>)

I see, but for the second step if I want to growth the file by 50GB (because was the size I saw before start the step 1) should I do it at once? Or should I execute many times with smaller sizes until get the 50GB?

This is a very good question. If you have too few VLFs for a 50GB log file when the VLF become available to be re-used? Or if we want to shrink the file (to growth) we can only do this by removing VLFs that are marked for reuse.

If we growth the file by 50GB (prior SQL 2014) we will get 16 VLFs of roughly 3200MB each, which means that we can’t reuse the VLF before 3200MB being reached (this could not be bad if you do not need to shrink again). That is why our function suggest to growth the file in 8GB chunks (will create 16 VLFs of roughly 512mb) until achieve the desired size (for 16GB or higher target size). Would be 8 them for 16 – 24 – 32 – 40 – 48 – 50. We have to execute the command seven times to reach the value we want.

Now that I am more familiar with the concepts, why do you convert this steps into PowerShell function?

The quick answer is automate all this steps! Call the function and sit back watch it do all the work for you!

Really, just type the Instance, database and your desired target size. We will figure out the best chunk size for your needs. You would not need to choose the chunk size, not even write and call the ALTER DATABASE command 7 times (picking last example).

NOTE: If you want, you can specify the -IncrementSizeMB parameter to say the desired chunck size.

This include the backups of the transaction log?

YES! Jason Squires have contributed with code for the backup/shrinkfile part! Which means if you do your backups to file system you can say the path and the function will do ALL that work for you!

With a single command we can get up to 5 log backups plus X alter databases commands executed without any further actions needed.

Please visit the Expand-SqlTLogResponsibly dbatools page to view examples and give it a try!

If you have any doubt or want to share your thoughts, drop me a line in comments or send DM on Twitter.

Thanks for reading!


Posted in PowerShell, SQL Server

New PowerShell Commands from the SQL Server Team!

Have you seen the SQL Server Tools team’s post about the PowerShell module for SQL Server? There are TWENTY FIVE new cmdlets! TWENTY FIVE! That’s a 53% increase over the previous 46 cmdlets. It looks like the SQL Server community is well on its way to becoming proper citizens in the PowerShell world, and we’ll reach Exchange’s 700+ in no time ;)

Included in the 25 new cmdlets is a whole lot more support for SQL Server Agent. This batch was chosen because of community upvoting on the Trello boards. We literally asked for this and got it. If you haven’t participated in the design of the cmdlets yet, head over now and start upvoting.

SQL Server Agent

It’s now so much easier to work with SQL Server Agent jobs. But before we dig in, here are the five new cmdlets dedicated to SQL Server Agent.

Get-SqlAgent Returns a SQL Agent (JobServer) object that is present in the target instance of the SQL Server.
Get-SqlAgentJob Returns a SQL Agent Job object for each job that is present in the target instance of SQL Agent.
Get-SqlAgentJobHistory Returns the JobHistory present in the target instance of SQL Agent.
Get-SqlAgentSchedule Returns a JobSchedule object for each schedule that is present in the target instance of SQL Agent Job.
Get-SqlAgentJobStep Returns a SQL JobStep object for each step that is present in the target instance of SQL Agent Job.
Get-SqlAgentSchedule Returns a SQL JobSchedule object for each schedule that is present in the target instance of SQL Agent.

One of the best things about these cmdlets is that it makes failed jobs a whole lot easier to find. Note that SQL Server Management Studio 2016 does not have to be installed on the server — only your workstation. That’s because the cmdlet is built on top of SMO which is available in all versions of SQL Server since 2000.

As an aside, you may be wondering what a cmdlet is, and what’s the difference between a cmdlet and what you and I write. A cmdlet is a formally written PowerShell command that’s written in C#. I’ve never written one in my life. Instead, I write Functions. They’re both essentially the same, but a cmdlet is fancier on the back end.

Let’s see some code!

Let’s start by specifying our SQL Server name dynamically so that you can easily copy and paste from this blog. In my case, $sqlserver = “sql2012”

Now you can pipe the Agent server to the next command, but you can also explicitly specify the SQL Server in the cmdlet itself. You can also specify the -Since the get events that have occurred since a specific date. Valid values incldue Midnight, Yesterday, LastWeek and LastMonth. But you don’t have to remember this, you can just tab through the values after typing -Since.

Want to know all the jobs associated with a specific schedule? While you can do this to get the schedules.

What if you have duplicate names? Let’s pick with Out-GridView and pass that to Get-SqlAgentJob.

And finally, want to find the failed step within a failed job? Here’s one way you can do it.

In the future, we’ll have corresponding Set cmdlets that can address any issues that are found. Until then, we have these super useful cmdlets that make it way easier to work with jobs in PowerShell.

Great work, team! Want to see more? Aaron Nelson wrote and Rob Sewell wrote a lengthy article about new cmdlets. I wrote up a lil something about them, too.

Posted in PowerShell, SQL Server

The PowerShell Module Formerly Known as SQLPS

I am still so floored by the additions and enhancements made to the module formerly known as SQLPS, now known as SqlServer. Not only do the new additions bring the total number of cmdlets from 46 to 71, but they align what we voted for on the Trello board. That’s a 54% increase in the number of cmdlets! We’ll be up to 700 like Lync and them in no time ;)

So what’s new?

As you may know by now, the SQL Server Tools team introduced a ton of new cmdlets for SQL Server Agent and SQL Server logs, as well as fixing a few things with Invoke-SqlCmd and the provider.

The Tools Team also added Always Encrypted which received no votes on the Trello board, but we knew this was coming because Ken Van Hyning said “Currently we prioritize via a couple of signals. Primarily, there is our Connect and CSS channels where we get input from customers. We also have some business directives around new advancements in SQL Server, like Always Encrypted. This isn’t really the way we need to run this though.”

What’s particularly amazing is that our input actively guided the module as it was being developed! It’s really incredible to have such a direct impact on a tool that will help us with our jobs every day.

What else is new?

In addition to the new Agent, Error Log and Always Encrypted cmdlets, Invoke-SqlCmd now supports outputting to .NET DataTables/Sets/Rows. The SQL Server team also added a ConnectionString parameter for those of you who would like the full flexibility of a ConnectionString.

The SQL PowerShell provider also now properly supports the WhatIf and Confirm parameters.

What about scripts that Import-Module SQLPS?

The SQL Server Tools team really put a lot of thought into this, and SQLPS will remain for now, it just won’t get updated. In talking to the SQL Server PowerShell Engineer, Matteo Taveggia about it, he said

If you are on a clean machine (or even if you had machine with the June Refresh SSMS) and you install the upcoming version of SSMS, you will indeed get both the new SqlServer module (new location) and the old SQLPS (same old location).

I did this intentionally to smooth the transition for existing users and avoid breaking them unexpectedly. Basically, their scripts (which have Import-Module SqlPs) will continue to work – they just are not going to get the new stuff / updates. Then, when they learn about the new cmdlets and fixes, they can start migrating at their own pace.


What about the community module SQLPSX?

SQLPSX is a community module that was built to fill the gap of commands that aren’t available in SQLPS, now SqlServer. SQLPSX had a module inside called SqlServer, which presents a naming conflict. This conflict can be resolved, however, by using the -Prefix parameter.

Then the commands available within the SQLPSX module will be prefixed with an x, for instance Get-xSqlTable.

Join us on Trello and vote vote vote vote

If you’ve already joined the SQL PowerShell Community Board on Trello, get over there and vote for the next set of cmdlets. If you haven’t joined, head on over and vote. You can vote without being a member of the board, but if you’d like to join, comment here or on our comment card. While you’re at it, join the #powershell channel on the SQL Server community’s Slack.


It appears that Microsoft also really benefits from code attachments. If you have any to contribute, which would help with the development, please do attach. They make cmdlets (formalized PowerShell commands), but .ps1 totally works.

Any guesses as to what cmdlets are next? Hint: it’s up to us to decide with our votes!

Posted in PowerShell, SQL Server

My homelab <3

My first homelab ever was started as a single Packard Bell 486 which ran SuSE Linux 5.2. I used it with my @Home connection to host web, DNS and mail services. Over time, I’d move leftover computers to a data center and covered most of my colo fees with the proceeds from Google Adsense, which I added to my website back in 2004.

I had a whole mixed domain (Windows and Linux) in my quarter rack located in downtown Los Angeles. I’d moved a few times and always brought my homelab (which now consisted of HP DL360s) with me which meant tons of downtime.


After moving my servers from LA to San Jose to Austin, I opted for hosting my websites on a VPS, and kept my homelab… at home. And with that, gone were the days of impressing ladies by showing them the Internet.

hackers get crazy pull. jk thats my friend and i love my wife

My homelab

Nowadays, I have super lightweight, quiet, and fast Shuttles. Here’s what my lab looks like from SUPA FAST to decently fast.

Case Processor RAM Disk
Shuttle SH170R6 i7-6700K 4.0GHz 64GB DDR4-2400 Samsung 950 PRO 512GB M.2
Samsung 850 EVO 500GB
Seagate 4TB SSHD
Shuttle SH87R6 i7-4790K 4.0Ghz 32GB DDR3-1600 Samsung 850 EVO 500GB
1 TB Velociraptor
Shuttle SH67H3 i7 3770 3.4Ghz 32GB DDR3-1333 Samsung 100 GB SSD
1 TB Velociraptor
Seagate 4TB SSHD

Storage and performance

Yes, it’s true, I have no redundancy. Sucks but I love local storage, don’t have enough room for more disks and my janky backups work well enough.

I had a WD Green 1TB in there for a few days and did some tests; here’s how the benchmarks fared. In order: Western Digital 1TB Green Drive, Samsung 850 EVO 500GB, and Samsung 950 PRO 512GB M.2.


That’s virtualized, too! These tests were performed on my SH170R6 box with ESX and a Win10 VM with regular ol attached disks. I’m so impressed by not only M.2 but ESX as well.

For any of you following some of the work I do with SQL Server and PowerShell import performance, I was disappointed to see that the M.2 only increased the imports by about 20,000 rows per second. That suggests that a majority of the tuning I’ve got left to do is in Windows, SQL Server and on the network.


I love client side networking like VPNs and stuff, but I’m not a fan of networking in general so I keep it simple. I use Apple Airport Extremes – one in the living room that connects to the Internet and one in the storage closet that connects to the one in the living room via wireless. It’s *so* fast, even though 1 Belgian concrete wall and 2 large rooms. just said that I got 106.52 Mbps down, 10.49 Mbps up. Through a wall and over 2 rooms of wireless!

The communication between all of my VMs stay local, though, which is cool. I also have an older Cisco router but networking ain’t my bag so it’s waiting for the day that I’m interested. When I need anything more than the basics, I use a WRT54GL with dd-wrt.

Why not just use Azure?

I like to test my scripts against a variety of versions/editions and I don’t like spinning VMs up and down all the time. As for the cost; some people spend their money on golf, Polish pottery and gaming rigs. I spend mine on servers, Belgian beer and travel ;)


As you can see, I also have an old Macbook Pro with 256 SSD, 4TB HDD and 8GB RAM in the mix. It’s for photos and videos, however. And someone gave me an old silver Shuttle from like 2002, but I haven’t had the time to set it up yet.

So what runs on this?

I started using VMware products around 1999 and continue to use it to this day for virtualization. Currently, I’m running vSphere 6. I get a super cheap license through my school, Regis University, though you can also get it for $200/year if you become a member of VMUG.


Thanks to my MSDN account, I also have a whole mix of SQL Servers, some DCs, Remote Desktop Gateway, Lync, SharePoint, Exchange, FreeNAS, docker, some rando test servers, and then a few different workstations like Windows 7 with PowerShell 3 and SQL Server 2008 R2 SMO to ensure dbatools works on older systems.

The rest of my equipment is Apple (big ol Thunderbolt display, Mac Mini, Macbook Pro), which is use to RDP to my Windows and Linux machines.

Anyway, that’s my homelab. It’s lightweight which is necessary because I broke my shoulders back in 2007 and can’t carry heavy things. It’s also energy efficient, which is necessary because I live in Europe where the energy prices are crazy.

Hope you enjoyed!

Posted in Networking

It’s 2016, so why are you still using sp_help_revlogin?


This month’s T-SQL Tuesday, hosted by Michael Swart (blog | twitter), is all about SQL Server 2016.

sp_help_revlogin is now old enough to drive a car

By now in your career, you may have needed to migrate a few databases and logins. The process isn’t as bad as it could be, after all Microsoft published sp_help_revlogin over 15 years ago to help migrate logins with their passwords and SIDS. Unfortunately, there has been no notable progress since this stored procedure was first released.

Why is this an issue? Well, by using sp_help_revlogin, you are required to do the following, as demonstrated in the painfully boring 41 second video below:

  1. Find the stored procedure somewhere on your hard drive/the Internet
  2. Install sp_help_revlogin and sp_hexadecimal on the source server
  3. Execute it on the source server
  4. Copy the resulting SQL output
  5. Paste it into the query pane of the resulting server
  6. Execute

Now you’ve migrated the logins with their passwords, SIDs, and a few default properties. But you don’t have the logins’ server roles, server permission sets, database roles or database permission sets. So now you gotta find and use someone’s modified version of sp_help_revlogin, but you’re still left with manually executing the procedure against your source and destination servers.

Oh, and don’t forget different versions of SQL Server use different hashing algorithms, so you’ll need to use one of the many different versions of sp_help_revlogin if you have a mixed environment.

Let’s hope you only have one or two SQL Servers to migrate and not hundreds.

Introducing Copy-SqlLogin and Export-SqlLogin

Copy-SqlLogin and Export-SqlLogin are the PowerShell answers to performing SQL Server login migrations. These commands are not part of Microsoft’ official SQL Server module, SQLPS, but they do help illustrate how PowerShell can make our lives easier.

Want to export all of your logins to a file, complete with SID, hashed password, roles, permission sets, securables and all that? Just install the dbatools module then execute

    Export-SqlLogin -SqlServer sql2005 -FileName C:\temp\sql2005-logins.sql  

By default, Export-SqlLogin exports all logins, but you can also choose which logins you want to include or which you want to exclude by using auto-populated parameters.


Want to see sample output? Here’s sql2005-logins.sql on Gist.

Or what if you want to do a live login migration from SQL Server 2000 to SQL Server 2016? I’ve done it a ton of times (in a lab of course. SQL2k doesn’t exist in my prod ;).

    Copy-SqlLogin -Source sqlsvr2000 -Destination newsql2016  

Copy-SqlLogin works on SQL Server version 2000-2016. It takes care of the hashing algorithm differences. It works on clusters, named instances, all editions from Express to Enterprise and it copies both Windows and SQL Logins. It copies the default database, default language, password policy settings, securables, permissions sets (server & db), and roles (server & db). And it does so in just a few seconds.


If you’d like a walk-through, check out the video below where I go a bit more into depth about Copy-SqlLogin, create a login, migrate the login, then log into SQL Server as the newly migrated account.


Need to sync your login permissions for your availability groups? Sync-SqlLoginPermissions was created by request, just for that. Unlike Copy-SqlLogin, Sync-SqlLoginPermissions doesn’t add new logins. It just syncs the server & db permission sets, as well as the server & db roles and job ownership.

    Sync-SqlLoginPermissions -Source sql2005 -Destination sql2016  

Need to copy jobs for you availability groups, too? Copy-SqlJob has you covered.

In 2016, DBAs will begin to fully adopt PowerShell

The year 2016 is an especially exciting time for the SQL and PowerShell community.

It’s no secret that prior to SQL Server 2016, the SQL Server team didn’t invest much in PowerShell. SQLPS was slow, rude, and didn’t conform to standard PowerShell practices. The SQL Server community was given a module with less than 50 commands, which left us needing to create solutions of our own. Meanwhile the Lync, Exchange and SharePoint communities were bestowed with over 700 commands each.

But all of that has changed changed. Microsoft now has a dedicated SQL PowerShell engineer and the SQL Server Tools team is actively asking for community feedback. They’ve even promised new cmdlets each month! Just check out the exciting things going on with the soon-to-be-released cmdlet, Get-SqlErrorLog.

Convinced yet?

If you’ve been a fan of PowerShell, I hope the SQL Server team’s recent dedication to PowerShell renews your excitement. If you’re a PowerShell holdout, I understand. I adopted PowerShell for SharePoint long before I did PowerShell for SQL Server. I thought it was slow (have you seen the performance of Start PowerShell in SSMS prior to 2016?!) and hard to learn.

But now I see its power and it will only get more awesome now that PowerShell has a dedicated resource within the SQL Server team.

If you still need convincing, check out for some fun and powerful commands like Reset-SqlAdmin, Get-SqlServerKey, Set-SqlMaxMemory and Start-SqlMigration.

Thanks for joining me for this month’s T-SQL Tuesday!

Posted in PowerShell, SQL Server

Keeping up with SQL Server Connect Items Marked Closed as Fixed

Earlier this week, I was trying to determine people’s feelings about Microsoft Connect by looking through Twitter. A few scrolls down, and I came upon this

That gave me the idea to make a Twitter bot, which I called @closedasfixed, that searches Microsoft Connect for items recently marked as Resolved or Closed as Fixed.

Basically, it scrapes the website every 30 minutes (their feed rarely gets recompiled so is not useful) and posts one Tweet. Seems to be a hit so far, gaining nearly 50 followers in just one day.

Here’s my Scraping and Twitter code if you’d like to roll your own :)

Posted in PowerShell, SQL Server

Counting the number of occurrences of a string or file in PowerShell

I always forget how to do this, and Aleksandar Nikolić posted a really beautiful answer on

For a file:

For a string:

Posted in PowerShell

Trello is awesome for everything

The other week, I was introduced to Trello for the first time by this tweet and I fell in love.

Basically, Nick uses Trello to gauge what people would like to hear about. Later on when trying to figure out how to best organize the Microsoft Connect items about SQLPS, it occurred to me that Trello would be the perfect solution. And it is.

The SQLPS Trello board worked out so well, that when the topic of getting overdue some improvements into SSMS was brought up, Microsoft suggested that setting up another board for SQL Server Management Studio might work well too.

If you use either of these products, I strongly suggest you join us and give Microsoft your feedback. They actively participate on both boards which is just amazing.

To add to the fun, I created a PowerShell module to help easily tally the votes for any Trello board, and I put it in the PowerShell Gallery. To install and run, just run

This gives you 3 functions
– Get-SqlPsVote
– Get-TrelloVoteCount
– Get-SsmsVote

Get-SqlPsVote shows the tally from the SQLPS Trello board in a GridView. Get-SsmsVote shows the votes from the SSMS board and Get-TrelloVoteCount is a generic function that works with any public board. Here’s the one from Nick’s board:


Or, if you’re into it, check out an overview video:

In addition to community stuff, I’m also using Trello to help me sort out the design of my own module, dbatools.

So if you’re into community involvement or PowerShell module design, give Trello a shot. It’s my favorite new thing.

Posted in PowerShell, SQL Server

Migrating SQL Server?

dbatools is an awesome PowerShell module that helps you migrate entire instances with a single command.

Available from and github

SqlServer Needs You

SqlServer now has a dedicated engineer and Microsoft is asking for our input!

Upvote priorities and cmdlets now


Chrissy LeMaire

Brandon Abshire
View Brandon Abshire, MCDBA's profile on LinkedIn


Chrissy has been awarded the Microsoft MVP for her work in the PowerShell community.

Join us!

Belgian PowerShell
User Group

  SQL PASS PowerShell
Virtual User Group


Upvotes Needed

Help persuade Microsoft to open source SQL Server's PowerShell module, SQLPS.

Upvote now on Microsoft Connect