Category: Blog

Why Does My Log Grow–SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

This is a great topic, and really, every DBA or admin should blog this and be sure they understand the issue.

I saw a posting from someone that said this: they kept running low on disk space where the transaction log was kept. They would shrink the log, and they had 3 log backups per day, but the log kept growing, and this was an issue. What can they do?

Let’s examine what happens: first, assume you have a trans‌‌action log that gets 2 transactions an hour. You have enough log space for 4 transactions in your log file. You back up 3 times a day (every 8 hours). Here’s your log size in transactions:

1:00am - 2 transactions
2:00am - 4 transactions‌‌‌
3:00am - 6 transactions (log grows)
4:00am - 8 transactions (log grows) 
‌5:00am‌‌ - 10 transactions (log grows)
‌5:00am‌‌ - 12 transactions (log grows)
‌5:00am‌‌ - 14 transactions (log grows)
8:00am‌‌ - log backup with 14 transactions. Log is large enough for 14 transactions
‌9:00am - 2 transactions
10:00am - 4 transactions‌‌‌
11:00am - 6 transactions 
12:00pm - 8 transactions 
1:00pm‌‌ - 10 transactions 
‌2:00pm‌‌ - 12 transactions 
‌3:00pm‌‌ - 14 transactions 
4:00pm‌‌ - log backup with 14 transactions. Log is large enough for 14 transactions
‌4:30 - you shrink the log back to 4 transaction size
5:00pm - 2 transactions
6:00pm - 4 transactions‌‌‌
7:00pm - 6 transactions (log grows)
8:00pm - 8 transactions (log grows) 
9:00pm‌‌ - 10 transactions (log grows)
‌10:00pm‌‌ - 12 transactions (log grows)
11:00pm‌‌ - 14 transactions (log grows)
12:00am‌‌ - log backup with 14 transactions. Log is large enough for 14 transactions
‌

Repeat this every day.

Now, how does this change if we run log backups more often? Let’s say we decide to run log backups every hour. Now I get:

1:00am - 2 transactions‌‌‌, log backup runs
2:00am - 2 transactions‌‌‌, log backup runs
3:00am -2 transactions‌‌‌, log backup runs
4:00am - 2 transactions‌‌‌, log backup runs
‌5:00am‌‌ - 2 transactions‌‌‌, log backup runs
‌5:00am‌‌ - 2 transactions‌‌‌, log backup runs
‌5:00am‌‌ - 2 transactions‌‌‌, log backup runs
8:00am‌‌ - 2 transactions‌‌‌, log backup runs
‌9:00am - 2 transactions, log backup runs
10:00am - 2 transactions‌‌‌, log backup runs
11:00am - 2 transactions‌‌‌, log backup runs
12:00pm - 2 transactions‌‌‌, log backup runs
1:00pm‌‌ - 2 transactions‌‌‌, log backup runs
‌2:00pm‌‌ - 2 transactions‌‌‌, log backup runs
‌3:00pm‌‌ - 2 transactions‌‌‌, log backup runs
4:00pm‌‌ - 2 transactions‌‌‌, log backup runs
5:00pm - 2 transactions‌‌‌, log backup runs
6:00pm - 2 transactions‌‌‌, log backup runs
7:00pm - 2 transactions‌‌‌, log backup runs
8:00pm - 2 transactions‌‌‌, log backup runs
9:00pm‌‌ - 2 transactions‌‌‌, log backup runs
‌10:00pm‌‌ - 2 transactions‌‌‌, log backup runs
11:00pm‌‌ - 2 transactions‌‌‌, log backup runs
12:00am‌‌ - 2 transactions‌‌‌, log backup runs

In both scenarios, the total log transaction load across the day is the same. The total log backup size is the same across the day. However, a log backup allows me to reuse the log, so I never run out of space and get growths in the second scenario.

If you aren’t sure how things work, or want to write your own blog, I would also recommend you read this: http://www.sqlservercentral.com/articles/Administration/64582/

‌‌

June 8, 2017

VSTS– Hosted Agent is the Default for Releases

In keeping with the idea I don’t want this to get out of date, this is VSTS as of Jun 1, 2017. If your screen looks different, find a different article.

I was setting up a test the other day using VSTS (Visual Studio Team Services) and once had something bite me (again). This has happened a few times this year as the service has evolved, but it is one of those things that I keep having to change.

Here’s a new Release definition, where I start with an empty process. I clicked “New Release Definition” and then selected an Empty task, hooking this up to my current CI process.

Usually I add tasks, and then create a release and execute it. Since I mostly work with databases, I’m usually releasing to a local SQL Server instance and want to use my local agents. I have separate ones running on my desktop and laptops, so I can easily demo releases in different environments.

However, when I’ve done this, usually things fail at first. If I’m not paying attention, I won’t notice my build agent looking like this:

That’s the screen showing nothing happening with my agent.

Grouping Tasks in VSTS

Earlier this year, the release process in VSTS changed to allow us to group tasks. I can now add different groups of phases, which I stumbled upon by clicking the arrow next to “Add Tasks”. This is a UI Fail because the arrow in other parts of VSTS is linked to the text to the left (like the Add environment area).

I stumbled on this one day, thinking the arrow let me add tasks, but I accidently added a new agent phase. By default, we have a single agent phase, but clicking the arrow allows more.

What’s also interesting, and not intuitive to me, is that if I click on the “Run on agent” area, I get settings to the right. This is different than the old release section, which had the properties as a global item at the top.

This is where I need to change to the agent queue, in this case, the default for my desktop.

This might not be a big deal, and I’m sure lots of people are happy to use the hosted agent. I’m always wary of costs, so I try to avoid spending compute dollars in Azure when I don’t need to, and this is a place I want to avoid spending $$.

Hopefully, after writing this, I’ll remember to set the queue for my future demos the first time. Now if I can just get the VSTS people to give me a “default” setting I can change…

June 5, 2017
Restore a BACPAC–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I needed to get the WideWorldImporters sample database for a project and noticed that there was a BACPAC available. I downloaded it and needed to restore this as a database. At least, that’s what many people would think.

However, if you go to the restore dialog, and select Device and then pick your location, there’s no filter for a .bacpac. In fact, if you choose one, it won’t restore. You’ll get the “no backupset selected” error.

How do you restore a .bacpac? Well, you import it. If you right click the databases folder in SSMS, you’ll see this:

The BACPAC is a DACPAC with data, in other words, a data-tier application with the data included. Once we click this, we get a wizard. Of course, there’s a introduction screen.

Then we get to choose the file, or import one from Windows Azure storage. I’ll limit the image here to the relevant parts. You can see below I’ve selected the file I downloaded.

Once I do that, the next screen allows me to set the database name and the paths. I shot this screen, and then changed the name, but forgot to reshoot. However, this is where you’d set the paths for files and change the name of the database.

You get to verify your settings. I know many people blow by this screen, but make sure you double check this is what you want. Notice I moved the data, but not the log. I went back and fixed things, again, forgetting to reshoot the image.

Once you click Finish, the import begins. This can take time, depending on the system you use.

However, you get detailed progress. As you can see, I get a lot of details on what happened. There’s plenty more below this.

But it worked, and I have my data.

You should practice this. It’s simple, but know how to import a BACPAC. You never know when you might get one and someone is at your desk. Having to google to import a simple database is a little embarrassing.

SQLNewBlogger

This is a simple post, one that took longer to write than setup and perform, but a good skill to showcase. If you’re looking for something to blog, create a bacpac on another instance and import it into your local SQL Server.

June 1, 2017
Quitting VIM

I saw this post from Joel Spolsky lately, noting that Stack Overflow has been helping 1 million developers exit VIM. It’s a funny post, but one that has some truth in it. I’ve gotten into VIM, almost always from git, but sometimes in Linux, and I often Google the exit.

Fortunately I’ve learned a few things and usually remember :wq.

Other do too, since I actually didn’t need to Google that during a presentation where I committed without the –m, and got into VIM. I actually remembered :wq, posted just “:wq” on Twitter, and a few people laughed, since it’s maybe the one VIM command quite a few people need to remember.

Hopefully just typing this post will tatoo that sequence into my head so I never end up reading the command list on Stack Overflow again.

If you want to learn more about VIM, here’s a cheat sheet.

May 29, 2017