fbpx

DallasDBAs.com

Explaining SQL Server in plain english

Kevin3NF

Purging a SQL Server Express Database

by Leave a Comment

This is not another IRL post, but rather is more of “Help, my SQL Express database is full!” post.

Dog that barely fits in her bed
Just barely fits

I was contacted by a lawyer that was using a 3rd party application to store emails, keep track of time, etc.

The backend of the application is SQL Server Express edition, which has a hard limit of 10GB for the data file.

The biggest table by rowcount and size in the DB is (of course) the email table, which stores the email body in a ‘text‘ datatype. I don’t know what version of SQL Express he is using, but I was working with a restored copy on SQL 2016 Developer. His vendor was no help.

Steps we tried after a lot of digging and testing:

0. Initial datafile size: 9969MB

1. Purge oldest 20%

Delete [email]
Where sysid in 
  (Select top 20 percent sysid from [email] order by date);
go

File size 9600MB. Not much improvement

2. Purge by a specific date

Delete [email]
Where [date] < '2012-01-01'

File size 9500MB. Slightly better.

3. Of the remaining emails, delete by largest memo field, older than 1/1/2017:

Select top 1000 sysid, len(Cast(memo as nvarchar(max))) as size
Into #BigMemos
from [email]
Where [Date] < '2017-01-01'
Order by len(Cast(memo as nvarchar(max))) desc

Delete [email]
Where sysid in (Select sysid from #BigMemos)

Drop table #BigMemos

Still not much difference, but at this point I know the indexes and half-full pages must be in full chaos mode

4. Rebuild, then Reorganize all of the indexes on this table

Nope...can't show the statements...too much application specific naming :)

Down to 9200MB

5. Time to Shrink!

DBCC SHRINKFILE (N'MyDB', notruncate);
GO

DBCC SHRINKFILE (N'MyDB', truncateonly);
GO

-- Yes, both were required, and in this order. Your mileage may vary

This got us down to 8696 MB, which will last my new lawyer friend a solid 3-5 years.

And yes, I billed him lawyer rates for this 😉

If you need part-time DBA help, or just want to have a Senior DBA around for emergencies, Contact us!

Thanks for reading!

Kevin3NF

Group By Starting Characters

by Leave a Comment

A litter of puppies
Image by Tom Staziker from Pixabay

I was chatting with Jeff (b|t) on my team yesterday and the context escapes me but I had this thought:

“Can you Group By the beginning characters, or a subset, of a field?”

I’m not a developer, so this question never comes my way. Except yesterday.

TL;DR – Yes.

Basic Group By code, using StackOverflow public data:

Select [Location], Count(Location)
From StackOverflow..Users
Where [Location] Like 'D%'
Group By [Location]
Having Count(Location) > 1
Order By [Location]

Same code, Grouping By the first 6 characters:

Select Left([Location],6), Count(Left(Location,6))
From StackOverflow..Users
Where [Location] Like 'D%'
Group By Left([Location],6)
Having Count(Left(Location,6)) > 1
Order by Left([Location],6)

Results. Lets pretend I was looking for people in Dallas.

There is more than 1 Dallas BTW:

Results from the 2 queries

I’m sure there are better use cases…but I only spent 7 minutes on this nonsense, just to see what would happen.

When find yourself saying “I wonder if SQL Server can….”, go try it.  Learn first, Google later 🙂

Thanks for reading!

Kevin3NF

How old are those stats?

by 2 Comments

Image by angel1238812 from Pixabay

SQL Server maintains a variety of stats about all sort of performance items.

  • Index usage (or missing indexes)
  • Query performance
  • Corrupt pages
  • Disk IO performance
  • Way more than I care to list here

I was trying to troubleshoot some TerriBad * tempdb write performance…almost 3000ms per write, on a server that we recently migrated to.

Our data center vendor of course said the storage was perfect and that we should try troubleshooting SQL Server.  (Duh?)

These stats are stored in the sys.dm_io_virtual_file_stats DMF, and I found this gem under the ‘sample_ms’ column description in the result set:

“The data type is int for SQL Server 2008 through SQL Server 2014 (12.x). In these versions, the value will reset to 0 after approximately 25 days of continuous database engine uptime.”

Now, this is a SQL 2017 box, but this tells me that these stats are going to reset at some point.

When I re-ran my IO stalls query…the writes for all 8 tempdb data files are at 7ms.  The sample_ms is currently 3,097,351,985 which is about 36 days if I’ve done my math right. Server uptime is approaching 90 days.

The takeaway for you, dear reader, is to know where to find the stats but also to know what timeframe they represent.

* Thanks to Brent Ozar (b|t) for this highly amusing term 🙂

Thanks for reading!

Kevin3NF

Ola log backup job failure

by Leave a Comment

I had a log backup job that was not failing, and was also not backing up logs:

EXECUTE [DBA].[dbo].[DatabaseBackup]
@Databases = 'USER_DATABASES. model',
@Directory = 'D:\Backups\KBH-Precision_SQL2016',
@BackupType = 'LOG',
@Verify = 'Y',
@CleanupTime = NULL,
@CheckSum = 'Y',
@LogToTable = 'Y'

Older version of Ola. Newer versions give this:

Date and time: 2021-09-28 14:56:12
Server: KBH-PRECISION\SQL2016
Version: 13.0.5026.0
Edition: Developer Edition (64-bit)
Platform: Windows
Procedure: [DBA].[dbo].[DatabaseBackup]
Parameters: Removed for cleanliness
Version: 2020-12-31 18:58:56
Source: https://ola.hallengren.com
The following databases in the @Databases parameter do not exist: [USER_DATABASES. model].
Date and time: 2021-09-28 14:56:12
Completion time: 2021-09-28T14:56:12.6474426-05:00

 

Watch those typos, kids!

Thanks for reading!

Kevin3NF