• Skip to main content
  • Skip to primary sidebar

DallasDBAs.com

SQL Server Database Consulting

  • Services
  • Pocket DBA®
  • Blog
  • Testimonials
  • Contact
  • About

SQL

SQL Server Errorlog: Not Just for Errors!

July 2, 2025 by Kevin3NF Leave a Comment

A Goldmine of Clues You Might Be Ignoring

SQL Server’s Error Logs often get overlooked, until something breaks. But if you know how to use them, they can tell you what went wrong before you’re knee-deep in outage mode.

Whether you’re a sysadmin who inherited SQL or an MSP juggling multiple environments, understanding the ErrorLog is low effort, high return.

Let’s decode the basics.

What Is the SQL Server Error Log?

It’s not a crash dump. Its not even just errors. The errorlog is a plain text file that tracks:

  • Startup and shutdown events
  • Login successes and failures (settable)
  • Backup and restore activity
  • Deadlocks (if enabled)
  • Corruption errors
  • Memory dump ingo
  • Full data and log file warnings
  • And more!

 

It’s like a flight recorder for SQL Server. If something bad happens, it probably shows up here.

How Many Logs Are There? And Where Are They?

By default, SQL Server keeps:

  • 7 error logs (Errorlog (current) through Errorlog.6)
  • Each gets recycled when the SQL Server service restarts or the logs are manually cycled
  • Located in the same folder as your SQL Server binaries (or wherever SQL was installed)
    • If you can’t find them see this short video: How to find the SQL Server Errorlog
    • Also, the location is the -e startup parameter in SQL Sever Configuration Manager

 

You can view them in SQL Server Management Studio under Management > SQL Server Logs or directly from the file system.

Changing the Retention Settings

Seven logs may not be enough if you cycle the service frequently. Here’s how to increase retention:

USE [master];
GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'ErrorLogSizeInKb', REG_DWORD, 0
GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 25
GO

If you don’t like registry edits, use SSMS:

Management>>SQL Server Logs>>Right-click: Configure. Check the “Limit the number of error log…” box, change the “Maximum number of error log files” to your preference and click OK

 

You can also cycle the log manually without restarting SQL Server:

EXEC sp_cycle_errorlog;

 

Do this regularly (e.g., weekly) via SQL Agent job so each log stays readable and relevant.

I like weekly cycling, 25 retention so I have 6 months of reasonably sized log files.

Finding the Signal in the Noise (Filtering)

The error log can be…wordy. Thankfully, SSMS lets you filter by multiple options:

 

For command-line lovers:

EXEC sp_readerrorlog 0, 1, 'Login failed';

This searches the current log for login failures.

Want to scan older logs? Change the first parameter:

EXEC sp_readerrorlog 3, 1, 'backup';  -- Searches the 4th oldest log

(xp_readerrolog exists, but is undocumented. Use sp_readerrorlog instead)

More Filtering:

If you can, log only failed Logins, not failed and successful. Your industry may require other setups.

Use trace flag 3226 to NOT log successful transaction log backups. Those are already being logged in the msdb tables and job history.

 

The Bottom Line: Set It and Watch It

  • Check retention: 7 logs may not be enough
  • Automate log cycling so logs don’t get so big they are unreadable
  • Use filters to quickly find useful info

Some monitoring tools read the logs for you, but you should still know how

 

 

Filed Under: SQL, SSMS, Tools, Troubleshooting Tagged With: syndicated

SQL Server Editions: What’s the Difference and Why It Matters

June 25, 2025 by Kevin3NF Leave a Comment

Understanding the Limits Before You Hit Them

If you’re responsible for a SQL Server instance but don’t live and breathe licensing, you’re not alone.

Many IT managers and sysadmins inherit a SQL Server setup only to find out later that they’re boxed in by the edition they didn’t choose. And by the time those limits show up, it’s often during a crisis. I’ve seen this many times over the years.

Let’s break down the main SQL Server editions: Express, Web, Standard, and Enterprise as well as what makes each one different. Not just in features, but in what they let you do… or stop you from doing.

 

SQL Server Express: Free, But with Hard Limits

This is Microsoft’s free entry-level edition, often bundled with lightweight apps or used by developers.

  • Max database size: 10 GB per database
  • Max memory usage (SQL engine): 1.4 GB
  • Max CPU usage: lesser of 1 physical socket, or 4 cores
  • Agent jobs: Not supported – this is a big deal

 

Use case: OK for dev/test, very small apps, or teaching environments.

Gotchas: You’ll quickly hit resource limits on memory or database size. Backup and maintenance automation via SQL Server Agent isn’t available. You have to script around it, and schedule jobs externally.

 

SQL Server Web: Affordable for Hosting, Limited for Business Use

Designed for web hosting providers – only available through certain Service Provider License Agreement partners.

  • Max DB size: No enforced limit
  • Max memory usage: 64 GB for DB engine
  • Max CPU usage: lesser of 4 sockets or 16 cores
  • SQL Agent is available for job scheduling
  • Key limitations: No advanced high availability (e.g., no AGs), no TDE (encryption), limited BI and performance features

 

Use case: Public-facing websites and web apps hosted via service providers.

Gotchas: Often confused with Standard Edition, but it’s not intended for in-house business workloads.

 

SQL Server Standard: The Most Common, and Commonly Misunderstood

This edition powers a majority of small and mid-size businesses.

  • Max DB size: No enforced limit (limited only by hardware)
  • Max memory usage: 128 GB for the SQL engine (other components like SSRS have their own limits)
  • Max CPU usage: lesser of 4 sockets or 24 cores (configuring your VM setup is critical here)
  • Includes: Backup compression, basic availability features (e.g., basic Always-On AG with 1 DB), TDE (SQL 2019+)
  • SQL Server Failover Clustering on 2 nodes is available
  • SQL Agent is available for job scheduling

 

Use case: OLTP workloads, departmental databases, general business apps.

Gotchas: Hit memory and CPU limits faster than expected in busy environments. Lacks advanced features like partitioning, online index rebuilds, and many encryption/performance options.

 

SQL Server Enterprise: All the Bells and Whistles

The top-tier edition. If you’re asking “Can SQL Server do this?” Enterprise almost always says yes.

  • Max DB size: No enforced limit
  • Max memory usage: OS limit (can use terabytes)
  • Max CPU usage: OS limit (can use 64+ cores)
  • Includes: Online index operations, table partitioning, in-memory OLTP, Always On with multiple DBs, TDE, columnstore indexes, and more
  • SQL Agent is available for job scheduling

 

Use case: High-performance, mission-critical systems. Often used with HA/DR needs, heavy workloads, and large-scale BI.

Gotchas: Very expensive. Licensing is per core, with a 4-core minimum per physical server or VM.

 

SQL Server Developer: Enterprise for non-production usage.

SQL 2025 might have a Dev edition coming with Standard Edition compatibility. As of this writing 2025 is in public preview

 

So… Which Edition Do You Have?

You can find out by running this query:

SELECT
    SERVERPROPERTY('Edition') AS Edition,
    SERVERPROPERTY('ProductVersion') AS ProductVersion,
    SERVERPROPERTY('EngineEdition') AS EngineEdition;

 

The Bottom Line:

Don’t wait until a restore fails or CPU caps out to figure out what edition you’re on. Knowing your edition helps you plan capacity, budget smarter, and avoid downtime when the server starts groaning.

Filed Under: Configuration, Install, SQL Tagged With: syndicated

SQL Server in the Cloud – Are DBAs Still Needed?

June 18, 2025 by Kevin3NF Leave a Comment

Things your cloud vendor may not tell you

 

Here’s a common theme I hear from small IT teams:

“Our SQL Server is in the cloud now. We don’t need a DBA.”

Not quite.

When you’re running SQL Server on cloud virtual machines like Azure SQL VM or AWS EC2, you’re still responsible for a lot of the same challenges you faced on-prem. The difference? Now you’re paying cloud rates for the same headaches. But you don’t have to deal with hardware and some of the other infrastructure hassles.

Let’s bust some of the top myths.

Cloud VM Myths That Can Hurt Your SQL Server

Myth 1: The Cloud Auto-Scales SQL Server for Me

  • Cloud VMs don’t automatically adjust CPU, memory, or storage. If you don’t right-size your SQL Server, you may be paying too much or running into performance bottlenecks.
  • Even with the right mix of vCPUs and Memory, you may be getting IOPS/Throughput throttling to the disks.
  • The VM size needs to be in the right family as well. For database workloads, look for “Memory Optimized” options, especially for reporting workloads.

 

Myth 2: Cloud Patching Means I’m Always Safe

  • Azure and AWS offer patching options for the O/S, but they don’t know your business hours or your workloads. A patch could restart your SQL Server at the worst possible time.
  • Azure (and AWS?) will not auto-patch your SQL Server unless you turn it on via the SQL Server IaaS Agent extension. Is this really a good idea for your system? Are patches automatically supported by your software vendors?
    • The IaaS extension will only apply “Important or Critical” patches.
  • As always, test all patches before applying to production.

 

Myth 3: Backups are Handled Automatically in the Cloud

  • Cloud VMs give you the option to back up SQL Server. They don’t configure backups or validate them for your RTO/RPO needs.
  • A DBA ensures backups run properly, are tested, and can meet your recovery goals.

 

Myth 4: The Cloud Handles SQL Performance

  • The cloud gives you CPU, memory, and storage but it doesn’t tune your indexes, optimize queries, or troubleshoot blocking.
    • Some level of auto-tuning exists in the PaaS offering (Azure SQL DB), but I’ve seen it go hilariously wrong.
  • A DBA does that. Without them, you may see slow performance over time, higher costs, and user/customer frustration.

 

Myth 5: SQL Server Licensing is Simpler in the Cloud

  • Simple? Maybe. Cheaper? Probably not, even if you get it right.
  • Cloud billing for SQL Server licensing can be complex, and mistakes add up fast.
  • Overpaying for cores “just in case” is a silly way to spend. Start smaller and add resources, better disks, etc. as you determine your actual workload.
  • This is one of the key reasons the Memory Optimized VMs exist…VMs are priced based more on vCPU count than anything else. Get that core count down by having a DBA tune your system periodically.

 

The Bottom Line:

SQL Server in the cloud can be a game changer. Wandering into the vendor’s portal or dashboard and blindly picking “something” is not the best approach. Get input from independent cloud experts and have a DBA review things from time to time.


Could Your SQL Server Survive a Failure Without Major Downtime?

Take the Quiz!

Find out how bullet-proof your DR plan really is!


 

SQL TidBits:

Check your SQL Server uptime:

SELECT 
   sqlserver_start_time 
FROM 
   sys.dm_os_sys_info;

Thanks for reading!

–Kevin

Filed Under: AWS, Azure, Cloud, SQL Tagged With: syndicated

SQL Server Agent Jobs: Set It and Forget It?

June 11, 2025 by Kevin3NF Leave a Comment

Useful tips to make your jobs more stable

Too many IT teams treat SQL Server Agent jobs like a coffee timer “Set it and forget it!”

Unfortunately, that mindset only works if everything else is perfect forever. Whether its backup jobs failing silently, index maintenance running on the wrong replica, or nobody getting alerts when things break, unattended SQL Agent jobs are one of the sneakiest ways to rack up technical debt. Let’s dig into what DBAs and non-DBAs alike need to keep an eye on to avoid job-related headaches.

SQL Agent Jobs Are Not “Fire and Forget”

Just because a job is scheduled doesn’t mean it’s working.
Agent jobs can fail, silently skip steps (especially when new steps are added), hang indefinitely, or run at the wrong time (local vs. UTC) and nobody notices until something downstream explodes.

  • Why Job Monitoring Matters
    • An ETL job that fails silently doesn’t just impact reports – it can compromise business decisions. Visibility is key.
    • CheckDB job failing due to corruption found? That needs IMMEDIATE attention.
  • Common Signs of Neglect:
    • Look for jobs with old failure messages, mismatched schedules, or jobs that haven’t run successfully in days or weeks.
  • Real-world Examples
    • One client had a nightly report load job fail every third Wednesday because of a bad parameter. Nobody noticed for months until the CFO asked why sales numbers looked off.
    • Differential backup jobs were failing due to running on an AG secondary. DAYS of potential data loss

Job Ownership and Permissions

SQL Agent jobs need a stable, valid owner. If that account gets disabled or removed, some job steps might fail, as SQL Server checks for valid ownership every time a job runs. The job owner or team name should be in the job description field.

  • Default Ownership Pitfalls
    Too often, developers or DBAs create jobs under their own accounts (this is default behavior). When those folks leave the company, chaos follows.
  • Best Practice:
    Assign ownership to a dedicated Agent service account or ‘sa’, depending on your security policy.
  • What Can Go Wrong?
    DBA leaves the company (even on the best of terms), sysadmin disables her account, BLAM! SQL Agent jobs start failing all over the place the next time they run. Probably while you are at your kid’s soccer game.

Logging and Notifications: Built-In but Underused

SQL Agent offers good logging and alerting options. You just have to turn them on.

  • Enable Job History and Step Logging
    Make sure each step writes output so you can diagnose problems quickly. Even the bare minimum “Log to Table” in the job step Advanced menu is better than nothing.  Watch out for the “Append output to existing entry in table” option – that can blow out msdb in a hurry.
  • Set Up Alerts for Failures
    Database Mail + an Operator = Quick alerts when something breaks. Useless if the emails go to the wrong person or team though.
  • Pro Tip
    Use job steps that retry on failure with brief wait times. And yes, that includes your t-log backup jobs. Check the job logic so you don’t accidentally repeat something that worked.

AG-Aware Jobs: Run Only on the Primary

In an Availability Group, not all jobs belong on all nodes.

  • Why This Matters
    Write-heavy jobs, like index rebuilds, update stats, or differential backups must run on the Primary replica.
  • Implementation Tip
    Add a check step at the beginning of critical jobs. If not on the Primary, gracefully exit.
  • How to Check If You’re on the Primary
    Use sys.fn_hadr_is_primary_replica in a preliminary job step to verify before proceeding.

    • -- Check if the local replica is the Primary
      IF NOT EXISTS (
          SELECT 1
          FROM sys.dm_hadr_availability_replica_states ars
          JOIN sys.availability_replicas ar ON ars.replica_id = ar.replica_id
          WHERE ars.role_desc = 'PRIMARY'
            AND ar.replica_server_name = @@SERVERNAME
      )
      BEGIN
          PRINT 'This is not the primary replica. Skipping job step.';
          RETURN;
      END
      
      -- If we get here, we're on the Primary
      PRINT 'This is the Primary replica. Continuing job logic...';
      
      BEGIN
          -- Your actual job logic goes here
          -- Example:
          Select getdate();
      END

Don’t Ignore Long-Running or Hanging Jobs

A job that’s still running isn’t necessarily still working.

  • Symptoms of a Hung Job
    No errors, but the runtime is wildly longer than average.
  • Root Causes
    Blocking, deadlocks, external API hangs, or backup file locks can all cause jobs to stall.
  • Solutions
    Implement a max run time policy per job or use third-party monitoring to alert on outliers.

Could Your SQL Server Survive a Failure Without Major Downtime?

Take the Quiz!

Find out how bullet-proof your DR plan really is!


SQL TidBits:

-- This is code from the internet, test before using
-- https://www.sqlservercentral.com/forums/topic/script-to-find-long-running-job

SELECT jobs.name AS [Job_Name]
    , CONVERT(VARCHAR(30),ja.start_execution_date,121) AS [Start_execution_date]
    , ja.stop_execution_date
    , [Duration_secs]
FROM msdb.dbo.sysjobs jobs
LEFT JOIN 
   (
       SELECT *
       FROM msdb.dbo.sysjobactivity
       WHERE session_id = (SELECT MAX(session_id) FROM msdb.dbo.syssessions) AND
           start_execution_date IS NOT NULL AND
           stop_execution_date IS NULL
   ) AS ja ON ja.job_id = jobs.job_id
CROSS APPLY 
   (
       SELECT DATEDIFF(ss,ja.start_execution_date,GETDATE()) AS [Duration_secs]
   ) AS ca1
WHERE 1=1
    and jobs.name Not in ('test') -- set this up to include/exclude jobs that should/should not be alerted on
    AND Duration_secs > 300 -- 5 minutes

Thanks for reading!

–Kevin

Filed Under: Agent, SQL Tagged With: syndicated

SQL Server TempDB: The Most Overlooked Bottleneck

June 4, 2025 by Kevin3NF Leave a Comment

Configuration, Performance, and Unnecessary Usage

TempDB is the SQL Server equivalent of a junk drawer – everyone uses it, nobody monitors it, and eventually it becomes a bottleneck you can’t ignore.

Whether it’s poorly configured from the start or getting hammered by bad execution plans, TempDB often becomes the silent killer of performance. The good news? A few targeted changes can make a big impact.

TempDB Configuration:

  • File Count: “one file per core” is outdated. Only go past 8 if you have verified PFS or SGAM contention exists
  • File Size and Growth: Pre-size your files to avoid autogrow OR ensure Instant File Initialization is enabled
  • Trace Flags 1117 & 1118: relevant for SQL Server 2014 and older, not in newer versions
  • Placement: Should TempDB be on its own disk? Ideally, yes. Fast disk, Fastest RAID, separate controller/path to the drives

 

Identifying TempDB Bottlenecks and Stalls

  • Read/Write Latency: Query sys.dm_io_virtual_file_stats to see stats on ms/read and ms/write. Write is usually worse.
  • Spills: Look for queries that are spilling out to tempdb due to poorly estimated memory grants
  • Monitoring Tools: I use exec sp_blitzcache @Sortorder = ‘Spills’ for this (part of the First Responder Kit)

 

Reducing Traffic TO TempDB

  • Avoid Unnecessary Sorts and Spills: Bad execution plans are a TempDB killer, and unneeded sorts make it worse
  • Test the Use of CTEs, Table Variables, and #Temp Tables: Test your code with the different types of temp objects, rather than just blindly using the easiest one. Nobody cares for 10 rows. They ALL care about 10 Million rows!
  • Version Store Traffic: If you are using RCSI on multiple databases, one transaction left open can blow out tempdb.
Select
   db_name(database_id)
   , reserved_page_count
   , reserved_space_kb/1024/1024 as [GB Used]
From
   sys.dm_tran_version_store_space_usage
Order by
   reserved_space_kb desc
  • Cursors: Some (all?) cursors live in tempdb. At some point a cursor gets too be to be efficient and you’ll want to re-code for speed, as well as tempdb space

 

TempDB Best Practices and Quick Wins

  • Multiple Files with Equal Size: Equal size is key…autogrow all files is on by default since 2016.
  • Instant File Initialization: Saves time on growth events.
  • Regular Review: TempDB isn’t “set it and forget it”. Schedule health checks, just like for the rest of your SQL Server

 

Bonus Content: What’s in your tempdb right now?

-- Determining the amount of free space in tempdb
SELECT SUM(unallocated_extent_page_count) AS [tempdb free pages],
  (SUM(unallocated_extent_page_count)*1.0/128) AS [tempdb free space in MB]
FROM tempdb.sys.dm_db_file_space_usage;

-- Determining the amount of space used by the version store
SELECT SUM(version_store_reserved_page_count) AS [version store pages used],
  (SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB]
FROM tempdb.sys.dm_db_file_space_usage;

-- Determining the amount of space used by internal objects
SELECT SUM(internal_object_reserved_page_count) AS [internal object pages used],
  (SUM(internal_object_reserved_page_count)*1.0/128) AS [internal object space in MB]
FROM tempdb.sys.dm_db_file_space_usage;

-- Determining the amount of space used by user objects
SELECT SUM(user_object_reserved_page_count) AS [user object pages used],
  (SUM(user_object_reserved_page_count)*1.0/128) AS [user object space in MB]
FROM tempdb.sys.dm_db_file_space_usage;

Thanks for reading!

 

Filed Under: Configuration, Performance, SQL Tagged With: syndicated

SQL Server Logins vs. Users: Why You Really Need to Know the Difference

May 28, 2025 by Kevin3NF Leave a Comment

The Two-Layer Model Explained

“We added them to the database, but they still can’t connect.”

Sound familiar? That’s the kind of confusion SQL Server’s two-layer security model creates when people don’t understand the difference between a login and a user.

Let’s clear that up, because getting it wrong causes broken access, orphaned users, and frustrated help desk calls.

Two Layers: One to Get In, One to Do Anything

SQL Server controls access at two separate levels:

  1. Login (Server-Level)
  • Exists at the SQL Server instance level
  • Allows someone or something to connect to the server
  • Can be:
    • SQL login (username + password inside SQL)
    • Windows login (domain account)
    • Windows group (preferred for manageability)

But a login alone doesn’t grant access to databases.

  1. User (Database-Level)
  • Exists inside a specific database
  • Grants access to that database and defines roles/permissions
  • Must be mapped to a login, unless you’re using a contained database

So:
Login = authentication (Can you connect?)
User = authorization (What can you do inside the database?)

What Happens When They Don’t Match?

Here’s a common scenario:

You restore a database from Prod to Test. A user exists in the database, but the matching login doesn’t exist on the new server.

Result? You’ve got an orphaned user.

SQL Server sees the user in the database, but there’s no login at the instance level with the same SID. The connection fails—even though everything “looks right.”

How to Fix Orphaned Users

Run this at the database level to find all orphaned users:

EXEC sp_change_users_login 'Report';

(Deprecated, but still handy in older versions.)

For newer systems, use:

Use MyDatabase;
GO

SELECT dp.name AS UserName
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid
WHERE dp.type IN ('S', 'U') AND sp.sid IS NULL;

Use this command to remap a user to an existing login:

ALTER USER [username] WITH LOGIN = [loginname];

Or…this is a process I’ve been using for 20 years:

Transfer logins and passwords between instances – SQL Server | Microsoft Learn

Why You Should Care

  • Granting a login access to the server doesn’t give them database access.
  • Creating a user in a database without a login won’t allow connections.
  • Orphaned users silently break apps after restores or migrations.
  • Unused users or logins clutter security and increase audit risk.

 

Best Practices

  • Use AD groups as logins whenever possible. Much easier to manage access for teams.
  • Don’t grant permissions to logins directly. Always go through database users and roles.
  • When dropping users or logins, clean up both sides: DROP USER and DROP LOGIN.
  • Document mappings so you know which logins tie to which databases.

 

Database Emergencies Cost Thousands Per Minute

When SQL Server fails, you need expert help fast.
For a limited time: First month of Pocket DBA® FREE with a 6-month plan.

• Senior DBAs available 24/7 • 1-hour SLA • Proactive problem prevention

Don’t wait for a disaster. Be prepared.
CLAIM FREE MONTH
Limited availability.


Thanks for reading!

–Kevin

Filed Under: EntryLevel, Security, SQL Tagged With: syndicated

  • Go to page 1
  • Go to page 2
  • Go to page 3
  • Interim pages omitted …
  • Go to page 34
  • Go to Next Page »

Primary Sidebar

Search

Sign up for blogs, DBA availability and more!

Home Blog About Privacy Policy
  • Home-draft
  • Blog
  • About Us

Copyright © 2025 · WordPress · Log in

 

Loading Comments...