How To Use sp_cycle_errorlog

Before I do any testing I like to execute the sp_cycle_errorlog stored procedure. That way I can quickly see the specific SQL Server log entry without having to scroll / filter through tons of log.

It’s pretty simple to use. Just run this in the query window: sp_cycle_errorlog

Whenever you execute sp_cycle_errorlog the SQL Server log closes the “Current” log and cycles it like a server restart does. So your “current” log becomes #1, #2 becomes #3, and so on. The new “Current” log will only have a couple rows that include Microsoft copyright information, authentication mode, etc. (see screenshot below).


That should make testing and searching through the log MUCH easier!

How to Check SQL Server Database Encryption Algorithm

I have enabled TDE (Transparent Data Encryption) on almost all of my production SQL Server database servers. Yesterday I was approached by the IA (Information Assurance) team and they wanted to know what was the encryption level (key length and algorithm) of one of the database servers.

I already knew it was AES-256 but IA needed a screenshot as proof. So I wrote this little query to bring back the database name, id, encryption key length and algorithm. I ran it, took a screenshot and sent it to them.

--Bring back database name, id
--key algorithm and key length
from sys.dm_database_encryption_keys e
join sys.databases db on db.database_id = e.database_id

Dirty Reads vs Phantom Reads in SQL Server

During a recent “interview” I was asked, “What two isolation levels in SQL Server will prevent phantom reads?”

I had never heard of “phantom reads” before but thought the person meant, “dirty reads.” So I replied, “READ COMMITTED and SNAPSHOT isolation levels.”

I was wrong…sort of. The interviewer said, “It’s actually Serializable and Snapshot.” As soon as the “interview” was over, I had to read up on phantom reads.

This is what I learned:

Phantom Reads: Occurs when a transaction is allowed to read data from a row that has been modified by another running transaction that has not yet committed.

Dirty Reads: Reading uncommitted modifications. When a transaction is allowed to read data from a row that has been modified by another running transaction that has not yet committed.

Below is a table that shows each isolation level and whether they allow dirty or phantom reads. (Photo taken from MSDN)

Isolation Levels Dirty Reads vs Phantom Reads

How to Rename a SQL Server Table

This morning at work I created a whole bunch of test tables with dummy data in them. After inserting all that data I realized I created the table with the wrong name. So instead of dropping and recreating everything, I used the “sp_rename” stored procedure. The syntax is as follows:

exec sp_rename ‘old Table name’, ‘new Table name’

It’s pretty simple and easy to execute.

One thing to note according to Microsoft (see image below)

sp_rename cautionIf you plan on using sp_rename make sure all your objects (stored procedures, tirggers, functions, etc.) reference the new name or they will break.