Configuration

SQL Server Express Size Limit

July 2, 2020 by Kevin3NF Leave a Comment

My Pluralsight course for new SQL Server DBAs

(Needs to upgrade to a bigger bowl)

Common scenario:

Company is using SQL Server Express to save on licensing costs for a small database. Over time this database grows and is getting close to the hard 10GB limit.

I just got off the phone with a client and their client is in this situation. During the conversation, she mentioned that the DB has information for four “regions.”

They already have a purge process in place, and compression is not an Express Edition feature.

Options I gave:

Decrease the retention (her client already said no to this)
Split the data into 4 databases – 1 for each region
- Might require some re-work of applications and connection strings
Install a second Express instance and split the data between them
- Easier on their app
Convince the end customer to buy SQL Standard (1% chance this is going to happen)
Buy Standard Edition for them and eat/split the cost to keep a huge customer happy
Move to a different platform (nope…all SQL Server shop)
I saw a blog post that you can bypass 10GB by using the Master database instead
- That’s the worst idea I’ve ever read. If you do that, never call me. 🙂

If you have other legitimate things I should add to this list that Microsoft will support, please share in the comments.

Conclusion:

If your data is important, buy the proper version for your needs. Maybe that’s Standard, maybe an Azure VM and spread out the SQL licensing over many months. But don’t do crazy things to try to get around the limits.

Thanks for reading!

Kevin3NF

Follow @Dallas_DBAs | |

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

SQL Saturday Dallas 2019

May 7, 2019 by Kevin3NF Leave a Comment

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:

Try to Connect via IP address – failed
Verify Instance name – verified
Verify “Allow Remote Connections” is set to 1/True – it is
Verify SQL Browser Service Running – discovered Set to Disabled
Verify TCP/IP network protocol enabled – discovered disabled

Solution:

Enable TCP/IP for Named Instance SQLEXPRESS03
Restart Instance
Set SQL Browser Service to Automatic and start
Test logon via UDL file – success

Lessons you can benefit from:

SQL Express default install only enables Shared memory connections
Error messages are not always going to give you complete information
Have a process for testing

This entire process took about 10 minutes, including the instance restart. There are 3 other Express edition installs on this server.

Other posts in the IRL – In Real Life series:

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

Thanks for reading!

Kevin3NF

Follow @Dallas_DBAs