Gregory Larsen

Security of data is a hot topic these days.  One of the security concerns auditors have is whether or not your SQL Server data is encrypted when stored At-Rest–meaning auditors would like to see all data encrypted on the actual disk platters that store the data.   By default, SQL Server does not encrypt data in […]

When you install a SQL Server instance you have the opportunity to identify the default location for your instance database backups.  If you don’t change the default location for your installation your default backups location will be “C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup”  (default location for a SQL Server 2016 installation).   Overtime you might decide you want […]

As a DBA your #1 responsibility is to make sure that each of your SQL Server databases are backed up.  Without recent backups you will not be able to recover your database without potentially losing many days of database updates.  Therefore, you need to make sure on a daily basis that your backups are running […]

Do you have instant file initialization turned on?  If you do, then allocating your database data pages will go much faster.  Not having instant file initialization turned on slows down the process of adding pages to your database.  It slows down because prior to allocating the data pages to your database, SQL Server needs to […]

There may be times when you need to perform some maintenance against a database that requires you to keep normal database users from connecting to your database, while the maintenance is being performed.  You want to stop users from connecting right away, but you want the database to be in a state that you can […]

The other day, after I installed SQL Server Management studio 17.2, I starting getting the following error message when I tried to start my SQL Server Management Studio: Visual Studio Component Cache Our of Date After reviewing a few different posts regarding this error, I developed the following steps to resolve this problem: Step 1:  […]

Have you ever wanted to know how many pages in your database have changed since the last full backup?  If so, then you will be glad to hear that SQL Server 2017 version of the DMV sys.dm_db_file_spavce_usage has a new column named modified_extent_page_count.   This new column will show the number of pages that have changed […]

Are you using database mail to send SQL Agent job completion messages, and alerts?  If so, are you also purging the old mail items stored in msdb?  In this tip, I will show you how to purge database mail. Database mail is stored in the msdb database.  If you don’t purge it periodically your mail […]

Most shops use database mail for sending email alerts.  Additionally, you might have some applications that are sending database mail.  Database mail items are stored in the msdb database. Have you ever wondered how much database mail you have sent in the past day or week?  What about those database mail items that were not […]

The transaction log backup is stored in SQL Server proprietary format, just like the transaction log itself. Even though the transaction log backup is in a special format, Microsoft has provided us with the fn_dump_dblog() function to read transaction log backups.   This function is undocumented, so you should use care when using this function in […]

In my last tip, I showed you how to use a transaction log backup file to identify who deleted an object from a database.   In this tip, I will show you how to find out the actual table name for the object deleted.     I ran the following code to identify who delete tables from […]

If you have to restore a database there are multiple ways to accomplish a restore.  But if you wanted to restore a database from a script how might you accomplish that task quickly and accurately?  One method is to write a process that uses the metadata in the msdb database to generate a restore script. […]