Database user and programming tips

Database User and Programming Tips

Placing a Mark in SQL Server's Transaction Log for Fall Back 07/02/2018

How many times have you had a programmer come to you and say they want you (the DBA) to restore their database to sometime prior to when they accidentally corrupted it? If you are doing FULL transaction logging you can do a point in time recovery to restore the database to just prior to when the corruption occurred. But in order to do that you need to know exactly when the programmer corrupted the data, which in a lot of cases is not known down to the second.

Using SQL Server's Default Trace to Identify Autogrow Events in tempdb 07/02/2018

We all know that you should try to size tempdb appropriately, so it doesn’t need to autogrow shortly after starting up SQL Server. It isn’t always easy to do this. Therefore, when you first implement a new server and/or add new databases you should monitor the autogrowth events on tempdb. By monitoring the autogrowth events you can easily determine if you have sized tempdb appropriately.

Databases with MAXSIZE Set 06/04/2018

When a database grows to the MAXSIZE the database will not grow anymore and if someone tries to add more rows they will get an error saying the database is full. Greg Larsen shows you how to find all database files that have their max_size set.

Indexes That Have Not Been Used but Are Being Updated 06/04/2018

We all know indexes are important for improving your query performance, but to store and maintain indexes SQL Server requires disk space and compute resources. If you have indexes that are not being used, they are costing you valuable resources and causing your update statements to run longer. Those updates run longer because they have to maintain those unused indexes. If your application is not using an index, then there is no value in having that index. Read on to learn more.

Tables Without Clustered Indexes 05/03/2018

Do all of your tables contain clustered indexes? Some say every table should have a clustered index. Greg Larsen shares a simple script to identify those tables in your database that don’t have a clustered index.

Why Are You Dropping and Recreating Your Indexes? 05/03/2018

Why are you dropping and recreating your indexes, when you could just disable and then rebuild them? By disabling an index, you have basically turned off the index, but have allowed SQL Server to retain the index definition in the database metadata. If you want the index back all you need to do is rebuild the index.

How to Programmatically Identify When Your SQL Server Was Last Started 04/02/2018

Greg Larsen shows you a quick trick to programmatically script a way to identify when SQL Server was last started.

How to Show the Execution Plan of a Running Query 04/02/2018

By looking at a query execution plan, you can determine which steps are used to resolve the query. But what if you just want to find out what the query plans are for only the queries that are currently running?

How to Send a TDE Encrypted Backup to Someone Outside Your Organization 03/01/2018

Once you enable your database to be encrypted with Transparent Data Encryption (TDE), the physical database files, and the database backups are encrypted.  If your database and database backup are encrypted, then how can you send the encrypted backup to a person outside your organization?

What is the State of My Transparent Data Encrypted Database? 03/01/2018

When using Transparent Data Encryption, you might wonder “What is the state of my transparent data encrypted database?” Read on to learn the many different states that a transparent data encrypted database might go through.

How to Enable Transparent Data Encryption 02/01/2018

By default, SQL Server does not encrypt data in a SQL Server database in an encrypted format. When SQL Server 2008 was introduced, Microsoft implemented Transparent Data Encryption (TDE). When TDE is enabled on a database SQL Server will encrypt the database as data is written to the disk. Read on to learn more...

How to Move a TDE Encryption Key to Another SQL Server Instance 02/01/2018

If you have a database backup of a Transparent Data Encryption (TDE) enabled database, the database backup will contain encrypted data. Because the database backup contains encrypted data you can’t just restore it to any instance. You can only restore the database backup to an instance that contains the same certificate used to originally encrypt the database.

Has Your Database Been Backed up in the Last 24 Hours? 01/02/2018

As a DBA your #1 responsibility is to make sure that each of your SQL Server databases are backed up. This tip provides a couple of TSQL scripts that will identify the last backup of each database.

How to Change the Default Backup Directory 01/02/2018

When you install a SQL Server instance you have the opportunity to identify the default location for your instance database backups. Overtime you might decide you want to change the default location for database backups. This tip shows you two different ways to change the default location for your database backups.

How to Turn on Instant File Initialization 12/04/2017

Not having instant file initialization turned on slows down the process of adding pages to your database, because prior to allocating the data pages to your database, SQL Server needs to zero out the newly allocated pages. Read on to learn more...