SQLServerCentral - www.sqlservercentral.com

A community of more than 1,600,000 database professionals and growing

Featured Contents

Featured Script

The Voice of the DBA

Auto-Deleting Data

Email has been a part of my life for well over nearly thirty years. It's kind of amazing to think of a time back in high school when there weren't electronic communications. That seems foreign now, as we don't have to send physical objects or converse with voice to communicate. The world of communications have changed dramatically.

These days email is still a preferred method of communication for many people. Even if we use sites like Facebook, NextDoor, or SQLServerCentral to communicate with others, we often get notifications of changes through email. If you're like me, many of you might keep far too many emails in your mailbox, rarely removing unnecessary ones. In the era where we measure storage in dozens or hundreds of GB, or even in TB, do we bother to even manage text communications?

Some firms do require this, often for legal reasons. With the GDPR, I wouldn't be surprised to see more organizations starting to set retention policies that ensure that communications don't live forever. There are some systems that do this now, but the practice isn't ubiquitous, but maybe it will be soon. Google is redesigning Gmail, which will include Confidential Mode. Not only will there be limits on these emails, but one of the more interesting is the ability to expire an email and have it automatically deleted.

I don't love the idea of having communications disappearing, but that might be because of the way I've grown up. I don't like using Snapchat with my kids, because I don't want pictures I take to disappear. However, younger generations don't feel this way. As I wonder why I try to hold onto old communications and records, I start to wonder if the idea of expiring data is something that we should be embracing as data professionals. Do we really need sales records from a decade ago? Are recordings of web traffic valuable from the early days of SQLServerCentral? Is there really any point to holding onto much of the data we generate? 

I know that there are corporations that hold onto decades of paper records. I worked at one that had nearly a 100 years of old records, most of which might never be examined again. Likely plenty of them aren't even legible or useful at this point. They're being stored for, well, I'm not sure why. I'm sure there are plenty of writers that might come up with a detective story that requires old paper records, but I'm not sure there's practical use for this data.

I expect that we'll start to see organizations changing their record retention policies as we look to avoid more liability and risk from data breaches. Every old record, every piece of PII data that we no longer use probably needs to go. Even records for existing customers might need to be removed. I don't necessarily need to ever access the record of my first Amazon order from 1998. I'm really sure that Amazon having liability for holding my old address, which potentially could be used to validate identity, is a bad idea for both them and me.

Steve Jones from SQLServerCentral.com

Join the debate, and respond to today's editorial on the forums

The Voice of the DBA Podcast

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

The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music.

SQL Provision

NEW SQL Provision: Create, protect, & manage SQL Server database copies for compliant DevOps

Create and manage database copies effortless and keeps compliance central to the process. With SQL Provisions virtual cloning technology, databases can be created in seconds using just MB of storage, enabling business to move faster. Sensitive data can be anonymized or replaced with realistic data to ensure data is protected as it moves between environments. Download your free trial

SQL Compare

The industry standard for comparing and deploying SQL Server database schemas

Trusted by 71% of Fortune 100 companies, SQL Compare is the fastest way to compare changes, and create and deploy error-free scripts in minutes. Plus you can easily find and fix errors caused by database differences. Download your free trial

Featured Contents


Random Word Generation for Data Scrubbing

John F. Tamburo from SQLServerCentral.com

With HIPAA and GDPR requiring your careful scrubbing of data for lower environments, random word generation promises to be a huge helper. Read here how to do it. More »


Introduction to Azure SQL Managed Instance

Additional Articles from Database Journal

Microsoft currently offers two built-in methods of running production SQL Server databases in Azure. The first relies on the ability of Infrastructure-as-a-Service (IaaS) Azure virtual machines to host a variety of on-premises workloads, including SQL Server instances. The second one leverages Platform-as-a-Service (PaaS)-based Azure SQL Database. With the introduction of the Azure SQL Managed Instance service, you have a third option, which combines the benefits of its two predecessors. More »


From the SQLServerCentral Blogs - Power BI Custom Visuals Class (Module 99 – Drill-Down Donut Chart

Devin Knight from SQLServerCentral Blogs

In this module you will learn how to use the Drill-Down Donut Chart. The Drill-Down Donut Chart allows you to... More »


From the SQLServerCentral Blogs - State of the Cloud Database: April 2018

Bradley Schacht from SQLServerCentral Blogs

It’s an exciting time to be a database professional. The technology is advancing quickly, large datasets are easier to handle... More »

Question of the Day

Today's Question (by Steve Jones):

I have database with some unbalanced file sizes in a filegroup. There are three files that are 10GB and one that is 2GB. I decide to remove the 2GB file. I run this:


After doing this successfully, I decide that I should just change the file size of TheFirstFile to match the others. If I change the file size, will new data now go into all four files in a proportional amount?

Think you know the answer? Click here, and find out if you are right.

We keep track of your score to give you bragging rights against your peers.
This question is worth 1 point in this category: DBCC Shrinkfile.

We'd love to give you credit for your own question and answer.
To submit a QOTD, simply log in to the Contribution Center.


Design and configure SQL Server instances and databases in support of high-throughput applications that are mission-critical and provide consistent response times in the face of variations in user numbers and query volumes. Learn to configure SQL Server and design your databases to support a given instance and workload.

Pick up your copy of this great book today at Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Steve Jones):

I'm looking to determine some information about a file in one of my filegroups. I decide to the the FILEPROPERTY() function. Which of these are valid properties to query? (choose 3)


  • IsLogFile
  • IsPrimaryFile
  • SpaceUsed


The FILEPROPERTY() function will return vales for 4 types of information:

  • IsReadOnly
  • IsPrimaryFile
  • IsLogFile
  • SpaceUsed

Ref: FILEPROPERTY() - click here

» Discuss this question and answer on the forums

Featured Script

Updated Brute Force Search Tool

John Imel from SQLServerCentral.com




Works with SQL Server 2012 and newer versions.  This will not work in SQL Server 2008 because it makes use of concat, iif, and throw.  These could be updated to use standard string concatenation, case statements, and raiseerror instead to make it work on older versions of SQL Server, however I will not be providing that here.


Searches a database for a value in any or all tables, supports number, date, and text searches along with fuzzy searches like begins with, ends with, contains, and now includes pattern matching


Table of matches found in database with sql to pull up the data and a count of how many times it was found in a specific table and column


I had previously published another version of this SP and decided it was time to update it a bit.  First I added in pattern matching for text searches as I have found it is fairly useful if your wanting to for example find every column in the db that contains dates in text or phone numbers in text.  This makes use of patindex for this so it will allow for anything patindex allows for.  I added in the ability to get printed messages to the screen as its finding results.  This allows you to see the results as they are found and can be very helpful if your searches are taking 20 minutes to complete to get a start on some of the results as its still processing.  Errors are now by default suppressed but can also be turned on to see any columns that errored in the search including the details of the error.  The next big improvement was adding in the ability to only search specific tables, or skip specific tables.  So now this supports including or excluding tables from the search.  Really handy if you only want to search a few tables, or if you want to skip the tables with a lot of binary data in them.  It will not allow for both at the same time however as that would be rather silly to include tables in one list then exclude them in the next.  Added tons of comments into the script to allow for users to easily see whats going on in it and modify to their liking.

Why is this useful?

I spend most of my time working on transforming unknown data into our client CRM databases and over the years I have found that a brute force script can help figure out the data structure of the source database.  There are many times where I only get an example value to match on and verify I got the data transformed correctly.  This script allows me to take a random value and find it in the source database, then once found I can work on the transforms over to my system.  It also helps locate lookup tables, find every reference to some random id im looking at, and get an overall idea how data is structured.  For me I tend to use this almost daily for what I do and is why I decided to share it a few years ago.


The following examples are also included in the script as well

--STRING SEARCHES------------------------------------------------

--exact match
--returns all table columns where there is an exact match of the search value
    @SearchValue = 'test',
    @SearchType = 'text';

--returns all table columns where the search value is contained in the data
    @SearchValue = 'test',
    @SearchType = 'text',
    @IsFuzzy = 1,
    @FuzzyType = 'contains';

--begins with
--returns all table columns where the data begins with the search value
    @SearchValue = 'test',
    @SearchType = 'text',
    @IsFuzzy = 1,
    @FuzzyType = 'begins';

--ends with
--returns all table columns where the data ends with the search value
    @SearchValue = 'test',
    @SearchType = 'text',
    @IsFuzzy = 1,
    @FuzzyType = 'ends';

--returns all table columns where the pattern supplied had matches in data
    @SearchValue = '[0-9][0-9][0-9][-][0-9][0-9][0-9][0-9]',
    @SearchType = 'text',
    @IsFuzzy = 1,
    @FuzzyType = 'advanced';

--DATE SEARCH----------------------------------------------------

--returns all table columns where the date was found
    @SearchValue = '01-01-1980',
    @SearchType = 'date';

--NUMBER SEARCH--------------------------------------------------

--returns all table columns where the number was found
    @SearchValue = '1234567890',
    @SearchType = 'number';


--exact match of text in included tables only

    @SearchValue = 'test',
    @SearchType = 'text',
    @IncludeTables = 1,
    @IncludeTableList = 'table1,table2,table3';

--exact match of text excluding some tables from search

    @SearchValue = 'test',
    @SearchType = 'text',
    @ExcludeTables = 1,
    @ExcludeTableList = 'table1,table2,table3';

--search with print on

    @SearchValue = 'test',
    @SearchType = 'text',
    @PrintResults = 1;

--search with errors and print on

    @SearchValue = 'test',
    @SearchType = 'text',
    @PrintResults = 1,
    @ErrorDsp = 1;

More »

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2017 : SQL Server 2017 - Administration

Backing up multiple SQL instance and databases using 3rd party software - Any recommendations - Hello, We are looking to have a centralized software management tool for backing up SQL Server databases that can also provide...

SQL Server 2017 : SQL Server 2017 - Development

How to search for Tabs from "Find" in SSMS v17.5 - In SSMS 2012 (and possibly earlier) I could find and replace Tabs in the query window. In SSMS v17.5, SQL can't...

SQL Server 2016 : SQL Server 2016 - Administration

Identifying list of users or groups that have create table permission - Experts, Do you'll know of a way to get details on how to get the list of users/groups (we have lot...

Does SQL server 2016 supports Database mirroring like in SQL server 2008 - I am new to SQL server 2016 and requires some information. I have 2 SQL server instances located Geographically apart...

SQL Server 2016 : SQL Server 2016 - Development and T-SQL

Explanation of data type treatment - Hi, I ran into an interesting issue yesterday which i managed to resolve but couldn't explain why it worked (which really...

Strange "The datepart millisecond is not supported by date function dateadd for data type date." error - Hi, I am getting strange error 9810 "The datepart millisecond is not supported by date function dateadd for data type date.",...

Extract phone numbers from a string - Hello, I am looking to extract phone numbers with the following specifications, from a string: - 10 consecutive numbers, that can be...

Combining address fields into 1 address field - bullet proof method. - I often have to combine multiple address fields into 1 for a variety of reasons.   I can hack a calculation...

SQL Server 2014 : Development - SQL Server 2014

Change Data format - Hi Experts, We have a datetime field where date time is saved in below format. Apr 19 2018  8:57AM But  want to...

In a self-joined table, how to remove ALL records with duplicates - How to remove the rows 6,7, 12,13, 21 and 22? I intend to remove pair of records with duplicate Col3 and...

Group by month and year - Hi all, I have a date column which isn't behaving as expected...

SQL Server 2012 : SQL 2012 - General

SSIS Maitenance Question - You may know the answer - Folks: Just checking whether there is a way to do this.... Take a look at the picture attached. We have a ton...

Deleting AG. Some questions - Good Morning Experts, If i delete the Availability Group(AG) in primary replica, what will be the status of databases in primary...

SQL Server 2012 : SQL Server 2012 - T-SQL

Check constraint where all records with the same effective date add up to one - Hi Dev Masters, I had to call uncle on this one! I am trying to build a check constraint on a table...

SQL Server 2008 : T-SQL (SS2K8)

Combine rows returned from within a Cursor loop into one Result - Is it possible to combine the Rows returned from a SQL running within a Cursor? DECLARE sla_cursor CURSOR FOR select Name...

Cloud Computing : SQL Azure - Development

Is Azure a good decision for a major production application deployment? - Good morning, We are migrating databases to SQL Azure to leave the current older infrastructure.  The decisions was made to not...

Data Warehousing : Integration Services

SSIS Script task disappearing code - We have an SSIS 2016 package developed in VS 2017, that includes a C# script task. Occasionally for no apparent...

Data Warehousing : Analysis Services

Small numbers in the cube shown as zero in excel - rounding error? - Hi all, I've created a Tabular SSAS Cube recently.  Certain numbers within the cube are very small (e.g. 0.0000612762). When I connect...

Process all tabular cubes at once - Hi all We've got a few tabular cubes and we'll be adding more as time goes by. We want to be able...

SQL Server 2005 : T-SQL (SS2K5)

Updating a column with serial numbers - I want to update a table's column data with serial number i.e. first row 1, 2nd row 2, 3rd row...

This email has been sent to {user_email}. To be removed from this list, please click here.
If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com.
This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.
This transmission is ©2018 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: webmaster@sqlservercentral.com