Voice of the DBA

Author: way0utwest

  • How does data file size relate to log file size?–#SQLNewBlogger

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

    The other day I saw someone asking about a way to determine which databases have a log file larger than a data file. It’s an interesting query, but not necessarily an issue. I did query as to what their thinking was, and this was more a first step to investigation rather than an alert or concern. That’s good, because that’s what this query is.

    I’ll write a query to check this in another post, but here I wanted to just discuss the meaning of data file size and log file size.

    Data File Size

    In most databases, you likely have a single .mdf file. Some might have more with other .ndf files, and others could have Filestream/MOT objects as well. The file sizes here are a combination of two things.

    1. your data
    2. free space allocated to the database for data in tables and objects, but not used.

    These files make up your data, and are roughly the equivalent of the characters in a Word file, plus any whitespace you’ve added. This isn’t quite right as Word doesn’t pad out some allocation, but it’s similar. If I create an 8MB file for my database, I use 8MB on disk. This whether I’ve added a 1 table with 1 character, 1MB, or 7.999MB of data.

    Log File Size

    The log captures transactions, or the changes to my data. This includes inserts, updates, and deletes. The size of the log file is an indication of a couple things.

    1. workload
    2. log backups

    The more activity in my database, the more log data I’ll capture. A busy database could have a lot of activity, as each change needs to be recorded. If I have a lot of inserts, I’ll grow my data file and my log file. If I have a lot of updates, I grow my log file, but may or may not grow my data file. If I have a lot of deletes, I grow the log file, but the data file remains the same, though I would have the option to shrink it.

    Side Note: DO NOT USE AUTOSHRINK

    The second influencing factor is the log backup frequency. If I generate 24MB of log records every day, do I need a 24MB log file? Not necessarily.

    I could run log backups every hour (24 a day), which would mean I only need a log file to contain the largest amount of activity that occurs during any hour. If my log gets even levels activity every hour, I’d need roughly 1MB of log file space, which would then be marked for reuse after every hourly log backup. Of course, I’d want some padding so maybe 2MB is enough.

    If I get 4MB of log record generation every hour during the business day and none outside those hours, maybe I need a 4 or 5 MB log file.

    If I’m in simple mode, I need a log file big enough to contain my largest transaction x2 (to allow for rollback).

    Is the Log File Larger than the Data File Normal?

    I wouldn’t say this is common, but it’s perfectly normal, if you follow a certain pattern in your database. If your workload consists mostly of updates or deletes, and few inserts, you might have a need for a large log file. This would also mean the level of activity is larger than your data size, and that you don’t have frequent enough log backups to allow for a lower size.

    Have I seen this? Yes.

    I managed a database for our other sysops that powered our anti-virus application. This tracked the activity for all workstations and servers. The number of nodes was relatively fixed (small additions and deletions every day or so), but the number of transactions was high. We had reporting every 30 minutes, which was really changing statuses for the nodes, so constant update activity. To prevent the server from being overloaded, and because the data was mostly replaceable, we only backed up the log every 4 or 6 hours.

    In this case, the change activity was higher than the data size, so we had a large log and a slightly smaller data size. Not common, but it happens.

    SQLNewblogger

    This is a great post for everyone to write. Explain what you understand and think about data size v log size, use your own words, and examples from your career.

  • The Security Weak Link

    I saw this cartoon, which I think is great: Data Security. It’s from John Klossner, and it perfectly shows that humans are, and probably always will be, a weak link.

    I look at this in two ways. First, we need to account for and accept that our users will make mistakes, so we need to have security in place, but also monitoring that detects issues. The second item is that this could be a privileged user, so the less we need privileged users to actually access and do things, as in manually changes or deployments, and the more we require them to “submit” changes that are audited, the better off we are.

  • Self-Healing ETL

    The process of building ETL flows is cumbersome and time consuming. Many consultants have made a lot of money by building ETL processes that manage the movement of data in and out of databases, including to data warehouses. There is a lot of importance placed on consistent, quick, and accurate data loading. In fact, one of the most popular job needs these days is still for ETL developers. Even as data science grows and becomes popular, quite a bit of the work in data science is data preparation, which often involves ETL, or ELT.

    One of the struggles with an ETL process is the tedious nature of building lots of flows that are very similar. There may be a wide variety of ways to get data to move, especially as we have a crazy set of inconsistent formats in which data appears, but once we have the flow, we often repeat that for many different files or tables. I think Biml makes this better, but it’s still not simple, and there can be plenty of issues that still arise as the source inputs change.

    There was an interesting article that describes the use of AI to assist in ETL flows. While there is some basic matching up of source and target data right now, what if AI would add a few things. The idea of looking at the data you’ve matched and suggesting alterations that can potentially help with data quality is interesting. The system would have to learn what issues occur over time and what good data looks like, but perhaps this could even extend when the source data gets slightly mangled in formatting. Perhaps detecting fixed width fields that have grown and shifted all columns would be possible, and even adjust the data flow.

    I am actually more interested in detecting PII data and alerting developers, or even operations people that the ETL process needs to be changed or secured in some way. AI should be able as well to help Operations people with scheduling, even pausing or moving ETL work to other machines to better scale the flow. My view would be that any AI system ought to be looking for anomalies and detecting those, as pattern recognition is one thing AI is good at.

    I’d think this would be more of an alert capability as I’m not sure I trust an AI system to actually adjust data. ETL flows are often too important to have data mangled in an unexpected way. Perhaps that’s too cautious, as that’s what developers do all the time. They mandle data, and we find ways to recover. Ultimately, I think that having lots of logging on what a developer or AI system does is more important. This might be especially true if voice commands are used to build the flows. I could see lots of ambiguities and mistakes from business analysts that are trying to describe how to build an ETL flow.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 4.2MB) podcast or subscribe to the feed at iTunes and Libsyn.

  • Republish – Normal Data

    Busy today at the InfoSec conference, so republishing Normal Data.