In SQL Server there is a special connection that can be used to connect to the database, the remote Dedicated Admin Connection or (DAC). This… Read More »SQL Server – Dedicated Admin Connection (remote DAC)
As part of the Database Health Monitor version 2.1 release there were many new report includes, one of those was the backup status report. A… Read More »Featured Report: Backup Status Report
Being the middle of the summer in the northern hemisphere, and with the Database Corruption Challenge over, I thought I would cover something a bit lighter, and not as hard core as database corruption.
If you have been a DBA for more than a year or two you probably have some story about going on vacation, and getting the call from the office with some database problem. It might have been that someone forgot their password, or something more serious like a corrupt database. Either way, the simple or more complex issues are not why you went on vacation.
Here is a picture of a DBA on vacation in Hawaii. Notice the empty chair… They should be sitting in the sun sipping a tropical drink, but instead they are back in the hotel room with a VPN connection to the office dealing with some emergency (or perceived emergency) SQL Server issue instead of enjoying the beach.
DBA on Vacation (photo by Steve Stedman)
When you are looking for deadlocks there are, like most things in SQL Server, more than one way to find the deadlocks. This article is specifically focused on using the SQL Server ERRORLOG file located in the LOG directory for the SQL instance. If you used the defaults on SQL Server 2012, this log would be at this location:
C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\ERRORLOG
For SQL Server 2014 it would be here:
C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Log\ERRORLOG
and in similar locations for other versions of SQL Server.
A deadlock is a specific case in SQL Server and other databases where, in the simple case of two transactions, the first transaction gets stuck waiting on blocking from the second transaction, and the second transaction gets stuck waiting on blocking for the first transaction.
Imagine traffic so jammed up that one lane of travel is blocking another lane of travel, and vice versa. The deadlock in SQL Server is similar to this, but instead of traffic being blocked, it is queries and transactions being blocked.
Eventually in traffic when cars are blocking, one will give up and back out, and the other will be allowed to drive through. The one that backs out is known as the deadlock victim.
When one query is chosen as the deadlock victim, it looks something like this:
The new Joes2Pros Academy has just launched. The Academy provides an interactive classroom experience that can be accessed on your own time as you need it. This site… Read More »Introducing the Joes2Pros Academy
On my computer I run SQL Server 2012, and I use a number of test or development databases. I don’t run backups on these because… Read More »Shrinking a Log File on a Test or Development Server
Beta 2 of the Database Health Reports has just been released. The new Beta contains several bug fixes, and several new features. Here is a… Read More »Beta 2 of the Database Health Reports Released
Although clustered indexes are not required, and there are many cases where you should not use a clustered index, quite often clustered indexes can be… Read More »Finding tables that don’t have a clustered index.
In SQL Server 2012, there was a change to the dynamic management view that reports on the plan cache, so the query has changed. Here… Read More »TSQL to Determine Plan Cache Size
After rebooting a SQL Server, for whatever reason, it is always good to confirm that the system is running good. There are many things that… Read More »Latest Backups with TSQL