SQL

Why is my SQL Log File Huge?

March 8, 2017 by Kevin3NF 11 Comments

Pluralsight courses for new SQL Server DBAs

Do you need our help? Or our DBA retainer service for emergencies?

HUGE Log files and how to troubleshoot:

The single most common question I have encountered in 18+ years of working with SQL Server:

Why is the .LDF file filling up my 500GB drive? I only have 100MB of data!!?!?!? Why am I getting error 9002?

For new or non-DBAs, this is a very frustrating situation without a logical reason (or so it seems). It is also very common for it to be accompanied by applications that won’t work, alerts firing for drive space issues, etc.

If you like video, I recorded my response to this question and discuss the two most common remedies. If you don’t like video, scroll down for text:

There are a number of reasons a log file can fill to extreme sizes. The most common one by far is that the database is in full recovery model, and Transaction Log backups are not happening fast enough, or not happening at all. Next to that, it could be that you had a massive transaction happen such as a huge data import, rebuild all indexes, etc. These are logged and stay there until the .ldf file is backed up (or checkpointed if you are in Simple Recovery).

Step 1: Verify recovery model

Right-click the database, go to properties, click the Options tab. You will see Full, Bulk-Logged or Simple. If you are in Full, you have the option of backing up the log…which is the best possible situation.

SQL Server Database Options

Step 2: Verify if the log is full or “empty”

Verify if the log file is actually full or not. If you are backing up and the file still grew to ridiculous size…it may have just been a one time thing and you can deal with that easily. Right-click the database, go to reports, standard reports, disk usage. This will give you 2 pie charts. Left is the data file, right is the log. If the log shows almost or completely full AND the huge size, you need to backup. If the log file is huge and mostly empty, you simply need to shrink to an acceptable size.

SQL Server Disk Usage

Step 3: Shrink the file (if empty)

Right-click the database>>Tasks>>Shrink>>Files

Choose ‘Log ‘ from the File Type drop down. Hopefully there is only one log file. If not, pick the big one. Under Shrink Action, choose an appropriate size and ‘Reorganize pages before releasing space” option, even though log file shrinks don’t actually do that. Pick a size in MB and click ok. 0 is not a good choice here.

SQL Server Shrink File

Step 4: Backup

I’m not going to go into a ton of detail here….Right-click the database>>Tasks>>Backup Change the backup type to Transaction Log and work through the rest of the steps.

If the Log Backup works, but the space is not freed (refresh the usage report), you have a different issue that these steps will not help with. Check out the “Wrapping Up” section at the bottom of this post.

If you don’t have enough room on any local, attached or network drive to create a log backup, even with compression, keep reading:

Step 5: Flip the Recovery Model (if log backup is not possible)

Warning: Doing this WILL cause you to lose point-in-time recoverability, but if you cannot backup the log, you are pretty much already there anyway.

Right-click the database>>Properties>>Options

Change the recovery model to Simple and click OK

SQL Server Recovery Model

Wait a few seconds and then go refresh the Disk Usage report. The log file will be the same size, but should be almost empty:

SQL Server Disk Usage

Step 6: Shrink the Log file

See step 3 above…

Step 7: Flip the recovery back to Full

See step 1…

Step 8: Set up recurring log backups

If you don’t know how to do this, go to Management, Maintenance Plans, right-click Maintenance Plan>>Maintenance Plan Wizard and go from there. This is well documented elsewhere.

Wrapping Up:

Hopefully, this resolved your issue but there are definitely other reasons for this issue to happen aside from a simple failure to back up. Most notably, a very large transaction in a database that is participating in SQL Replication as a publisher.

If the above methods do not work, run these two statements and go post the results in the MSDN SQL Server forums, along with a description of the issue and what you have already tried (hopefully all of the above):

Select [name],recovery_model_desc, log_reuse_wait_desc 
from sys.databases
Where [name] = 'MyDatabase' --change this

DBCC OPENTRAN --results will be in the messages section

I love comments on my post, but if you need quick help go to the forums first, or maybe even a call to Microsoft Support if the “quick hits” don’t get you the resolution you need. If this helped, please comment and share the link…

Thanks for reading!

Kevin3NF

My Pluralsight course for new SQL Server DBAs

Follow @Dallas_DBAs

The Apprentice: Detective work

March 8, 2017 by Kevin3NF Leave a Comment

SQL Server Database Training Apprentice Detective

I decided to see how much knowledge and familiarity the Apprentice has retained in the area of Database Properties.

The Setup:

I wanted to simulate a customer engaging him to “Look at the database” because it doesn’t “seem right.” I created a Sales database on his machine and mis-configured some items, taking it far away from best practices. It came on line, but had issues 🙂

The first thing he noticed and asked about was the lack of tables or other objects. My response was that the “customer” was installing a 3rd party application which has two steps: Create the Database, and then Create the Objects. They thought the results of step one were odd and called us.

What he found:

Auto-Shrink enabled (very common for 3rd party apps)
Insane file names (Logical: Bill and Log_Ted, Physical: Taco.mdf – data and Burrito.Mdf – log file)
Auto-grow for data file of 1 MB, capped at 5 MB
Auto-grow for log of 100%, unlimited

No backups had been taken, but I don’t recall if he found that or if we even discussed it. He is well aware of backups and recovery models.

This took us down a conversation of best practices, and how to rename files in a database, both Logical and Physical. What’s really fun is when you want to look up the ALTER DATABASE command to rename the physical files and the internet connection is down…so no MSDN or Google!

We used the GUI to create a script for changing the logical names, then modified that for the physical files instead. And he already knew that the actual files on disk had to be changed as well.

He did really well on this, with very little prompting. Well done!

Thanks for reading!

Kevin3NF

The Apprentice: Non-SQL stuff that SQL Server depends on

February 27, 2017 by Kevin3NF Leave a Comment

The apprentice and I gathered at my house Sunday evening for a bit of training. I gave him some homework ahead of time to go look up RAID and the most common levels.

Yep…we spent an hour standing/sitting in my kitchen discussing RAID 0/1/10/5/5+1, etc.

And Spinning disks vs. SSD

And Memory (including addresses)

And CPUs

And SAN vs. DAS vs. internal

And how SQL Server uses all of these items.

And how the costs associated with these choices vary from client to client.

And how it is perfectly acceptable to blame the storage team for anything up to and including your lunch being stolen from the office fridge. 😉

We never even started the SQL Services…went old school with pen and paper to map things out.

Short one today, thanks for reading!

Kevin3NF

The Apprentice: Top 10 list

February 19, 2017 by Kevin3NF Leave a Comment

I got to work with the Apprentice today for just over an hour, and it seemed appropriate for us to go over the Top 10 SQL functions post I put up a few days ago, since he will be using them throughout his career.

We ran several against his registered servers list and went through why you would use them and when. @@Version to verify servers are up to appropriate SP/CU level, @@Servername to verify all of them are online and responding, etc.

We spent quite a bit of time talking about implicit vs. explicit transactions and looking for issues with DBCC Opentran, including writing our own INSERT and leaving it hanging/uncommitted. This took us down a fairly interesting rabbit hole.

I think the most fun he had was when he went off and used Cast, GetDate() and DateDiff to mess around with the sample I gave in the original post to figure out how many days old he is, plus how far back he could go with GetDate() – x. (Jan 1, 1753 as it turns out). When he starts doing things “off-topic” I just sit back and watch 🙂

The second half of the list wasn’t as relevant to him as the first, but then again he’s been doing DBA stuff for total of about a week now.

For each of these we were able to go through at least the basics, which he understood. And he finally bookmarked my blog 😉

This is fun for both of us.

Thanks for reading!

Kevin3NF

Top 10 SQL Server functions every DBA should know by heart

February 17, 2017 by Kevin3NF 4 Comments

I suck at syntax. A lot.

Basic Selects and Joins I’ve got. Insert, Update or Delete a row? No problem. Beyond that I have to look it up more than I probably should. But, since I was last a SQL developer in 2001, I function just fine. I am very familiar with pretty much all of the T-SQL keywords, what they do, etc. But I just don’t use them often enough to commit them to memory.

If you are a DBA, there are many functions built into SQL Server that you MUST commit to memory. If you don’t…they will creep in there and take up residence anyway. Like Borg…they will assimilate parts of your brain. Just play along and all will be ok.

My top 10 list:

1. @@servername – I use ‘Select @@Servername’ literally every morning. I open SSMS, go to registered servers, Select my ‘Prod’ local group and run this in a query…just to see if all of my little Production server children respond. Takes 5 seconds to make sure everyone is online that should be.

2. @@version – Depending on the client I may use this every day if their servers are everything from 2005 RTM to 2014 SP-whatever. Currently, all of my servers are exactly the same. Its a little weird. But in a good way.

3. DBCC Opentran – log file filling up? Processes blocked and timing out? See if some doofus (hopefully not you) left a transaction open and went to lunch. If you are lucky you can kill it. Unlucky and you find an undistributed replicated transaction.

4. Getdate() – Not sure why, but this winds up in probably 80% of the scripts I write. Not surprisingly, it often brings along it’s little brother, DateDiff.

5. sp_Configure – If you don’t know this one, run it by itself against your test instance…you get back a ton of information about the instance. Min/Max memory, Backup Compression, etc. Basically a bunch of the stuff you see if you right-click the server instance and browse the GUI. And you can make changes here too.

6. DB_Name()/DB_Id() – When the view you are using give you the DBName and you need the ID, or vice versa…use this to flip it. Go ahead:

Select DB_Name(dbid), * from sysprocesses

6.5 Object_Name/Object_ID – same as above but for tables, indexes, etc.

7. Set NoCount On – Don’t need (53 row(s) affected) in the messages pane? Use this at the top of your query

8. Set Statistics IO On/Off – want to see a bunch of info about scan counts, logical and physical reads, etc. from your query? Use Set Statistics IO ON. For duration info, use TIME ON. Generally speaking, bigger is not better on this.

9. Min/Max/Count/Sum – “Help! I need a quick query to give us the most recent sale to each client in TX!” MAX(DateSold) is your friend. All of these count as aggregate functions, and you will need to understand GROUP BY as well. Select Count(*) from Table….google up a better way 😉

10. Cast/Convert – Need to Concatenate an INT field into a string? CAST it into a varchar():

Print 'Today is day ' + 1 + 'of the rest of your life'
--Conversion failed when converting the varchar value 'Today is day ' to data type int.

Print 'Today is day ' + cast(1 as varchar(1)) + ' of the rest of your life'
--Today is day 1 of the rest of your life

Convert is used when working with dates in one format and you need them in another. Read the linked doc and save in your favorites

Honorable mention: sp_whoisactive by Adam Machanic (b|t).

This would be top 5 on the list if Microsoft had written it. Step one in troubleshooting a performance issue, right after checking Task Manager to see if it really IS SQL Server taking up all the CPU, or some other program gone wild.

You, my dear rookie DBA need to get very comfortable with all of these. Look them up, test them, and memorize. Don’t just bookmark…memorize them. Give your brain some muscle memory.

Did I leave out something really obvious? Should it have been in my top 10? Tell me about it in the comments! Or harass me on Twitter 🙂

Thanks for reading!

Kevin3NF

Follow @Dallas_DBAs

T-SQL Tuesday: Shiny New Toys

February 14, 2017 by Kevin3NF 1 Comment

T-SQL Tuesday is a blog party started by Adam Machanic (b/t) over five years ago. The first Tuesday of each month a blogger hosts the party and suggests a topic. Anyone who is interested blogs on that topic on the second Tuesday. It can be a lot of fun and quite a challenge to blog on a topic you didn’t pick.

This month’s topic is presented by Matt Gordon (b/t), who has challenged us to write about “Fixing Old Problems with Shiny New Toys”, specifically new items introduced in SQL 2014 and up.

True to my typical post style which focuses on small shops, accidental DBAs, and junior DBAs I went looking for something that could very easily benefit people that are using the basic SQL Server features. In this case they may not even realize how limited they were.

I chose to write about Index Maintenance in the built-in Maintenance Plan portion of SQL Server.

A brief summary of the built-in Maintenance Plans is that they allow you to drag-and-drop your way to basic SQL Server maintenance items such as Backups, Index maintenance, CheckDB, Statistics updating, etc. This a tool that has been around since at least version 7 that I know of. It wasn’t always very good, and it gets a bad rap from a lot of DBAs. It has been dramatically improved over the years in flexibility and reliability.

In SQL 2014 and before, you had a choice to rebuild or reorganize indexes, and it was pretty much an all or none proposition. There were a few options, but still limited. If you wanted to get granular, you needed to roll your own code, buy a 3rd party utility, or use something like Ola Hallengren’s free scripts (known everywhere as the “Ola scripts“).

2014 Rebuild task…a few choices, but not a lot.

2014 Reorganize Index dialog…even fewer choices:

Don’t misunderstand me…its great that these options are available, especially for the SQL Server customer that doesn’t have a DBA or the budget for a 3rd party product. The biggest downside is that they will rebuild EVERY index on the selected database(s)…whether it needs it or not. This can cause significant blocking, resource usage and transaction log files that can grow way beyond what is expected.

In SQL 2016, Microsoft introduced the ability to Rebuild or Reorganize based on Fragmentation %, Size of the Index (page count) and usage:

These options allow you to rebuild at whatever levels make sense to you. Taking a hint from the Ola Scripts, I set mine to rebuild everything over 30% fragmented, and over 500 pages in size. “Used in the last xx days” is a nice feature as well. The stats sampling option is something I didn’t know was there until I started writing this!

Similarly, Index Reorganize is also now allowing you to get more granular:

These options give the small SQL shop the ability to “set it and forget it” in ways that are more intelligent than previously available.

Pro tip….do the rebuild first, so you don’t accidentally Reorganize everything over 5%, including the ones over 30%…

There are dozens of articles and blogs on Rebuild vs. Reorganize, so I won’t re-hash that here…but I will Google it for you.

Many DBAs will scoff at this and turn up their nose at SQL Maintenance Plans…but they definitely have their place. Not every shop can use a “free” product such as the Ola Scripts, or have budget for MinionWare. An additional great aspect of this is that since Microsoft wrote it, they support it!

Large shops with multiple instances, and a DBA staff…go get ’em! If you are a small shop looking to upgrade from 2008, R2, 2012…this is only available in 2016…which just runs faster, and has lots of Shiny New Toys!

Thanks for reading,

Kevin3NF