If you want to find the last time your SQL Server was restarted, the quickest way is to query the sys.dm_os_sys_info DMV for the ‘sqlserver_start_time’ column (See snapshot below)
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!
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
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)
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)