Configuration

SQL Server Identity Skipping

February 4, 2020 by Kevin3NF 2 Comments

My Pluralsight course for new SQL Server DBAs

SQL Server may skip 1000 numbers on an Identity column if the server crashes. Here’s why:

Too long, didn’t watch version:

SQL Caches 1000 numbers at a time to boost insert performance. In a crash and recovery, those numbers are gone.

SQL 2016 and earlier – use instance-wide trace flag 272 to turn off this behavior (performance might suffer).

SQL 2017 and later – its now a database scoped config item:

use MyDB;
go
ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF
GO

Video shows a walk-through of before and after each fix, plus a “Two guys walk into a bar” joke when I disappeared to troubleshoot a broken demo…

Thanks for reading and/or watching!

Kevin3NF

My Pluralsight course for new DBAs

Follow @Dallas_DBAs

IRL #3: SQL Server HealthCheck

June 5, 2019 by Kevin3NF Leave a Comment

IRL #3 – Slow Server

Problem:

I was recently approached by a firm outside the US for some help with his SQL Server which was “running slow.”

Background:

They don’t have a DBA, and I didn’t support international customers at the time so I told him I would donate 30 minutes of my time out of pure curiosity.

Action Taken:

I sent my HealthCheck tool along with instructions to run it once the server has been up at least 7 days.

Results:

After reviewing the gathered info for 30 minutes, these are the results I sent back as recommendations to look into:

Backups and Data on the same drive
Other databases never backed up
No CheckDB since 2011, if ever
Max Server Memory: 24,000 MB of 32GB installed
- Memory used –3.4GB
Windows Server 2008
Auto-Shrink enabled on prod databases
No alerts when bad things happen
No Operators
SQL Agent offline
Balanced Power Plan
Priority Boost enabled
TempDB on C drive, only 1 data file
10 years of backup history
PLE – 292 seconds
32-bit SQL Server 2014 (unsupported build)
I didn’t even get back any index information, as those queries timed out.

Solution:

Immediate:
- Upgrade to 64-bit O/S (assuming 32 bit here) and SQL Server
- Patch to supported build
- Turn off Priority boost
- Set up proper alerts and maintenance
- High performance power plan
Next
- Address everything else

Lessons:

Start at the outside and work your way in. The SQL version can be the biggest issue. Start at Hardware and O/S, then Instance settings, then indexing, then queries (in most cases).

All of this took less than 30 minutes to find. I can do similar (but much more thorough) work for you.

Other posts in the IRL – In Real Life series:

https://dallasdbas.com/category/sql/irl/

Thanks for reading!

Kevin3NF

Follow @Dallas_DBAs

Dallas DBAs is a Gold Sponsor of SQL Saturday Dallas

June 1, 2019

Richardson, Texas

Dallas DBAs is pleased to announce that we are upgrading our sponsorship of our “home” SQL Saturday event from Silver in 2018 to Gold level in 2019!

Why the change?

We believe in the mission, membership and management of the North Texas SQL Server User Group and want to give it as much support as possible.

Also, a Gold Sponsorship allows Lead SQL Server DBA Kevin Hill to present his session during the lunch hour:

“Your SQL Servers are Mi$ConFiguReDed”

Come to this session to learn the 5 or 6 most common mistakes that are made when SQL Server is installed. While you eat fajitas!

Even if you miss the lunch session to attend a different one, stop by our booth to grab some swag and talk to Kevin, Derek, Kaitlyn, or Jeff. We don’t do hard sell, but we will have a “SQL Saturday Show Special” to offer you.

Just for fun…here’s a picture from last year:

We will be raffling off something either really cool, or valuable, or both. Make sure you drop your SpeedPass raffle ticket in the jar and stick around all day. You must be present to win any raffle items from any sponsor.

If you are thinking of coming, make sure to register HERE. If you have already registered and are not able to come, make sure to cancel your registration and open up that spot for someone else. Thanks!

As an added bonus we have convinced Jeff from Ignis Images to come back this year to do event photography and “quick” headshots in front of the SQL Saturday backdrop! If you want a more professional shot, you need this service!

See you there!

The Dallas DBAs team

Video: Moving SQL Server Data Files

January 23, 2019 by Kevin3NF Leave a Comment

If you are like me, when you look at certain pieces of SQL Server documentation the sheer volume of information can be a little overwhelming.

ALTER DATABASE is one of those documents, due to the number of things that can be ALTERed. The documentation has been improved since I was a young newbie DBA, but still…

Anyway, here is a simple video showing me moving a LOG file from my D drive to the C drive:

If you have comments or questions, please leave them on the video, or ping me on Twitter.

Thanks for watching!

Kevin3NF

Follow @Dallas_DBAs

IRL #2: Cannot Connect to SQL Server

January 23, 2019 by Kevin3NF Leave a Comment

IRL #2 – Connectivity Issues

Problem: Application owner is trying connect to SQL Server to create a database, connection failed. Error message suggests checking Instance Name and “Allow Remote Connections” option set to True.

Background: This client has a wide variety of SQL Servers in Prod, Test and Dev. Many different versions and editions, quite a few coming from 3rd party vendors. This was in a new test environment for an application already up and running in Prod.

Resolution Attempts: