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).

sp_cycle_errorlog

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
select db.name,
e.database_id,
e.key_algorithm,
e.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.