Category Archives: SQL Server

Dedicated Admin Connection (DAC)

What is DAC?
DAC is short for Dedicated Administrator connection to SQL Server – it is used for troubleshoooting by those who are SAs in for exceptional situations where a regular login will not meet the needs.

When is DAC typically needed?
In situations that require serious troubleshooting by an administrator.

How do I connect to SQL Server using DAC?
I decided to figure this out the hard way instead of googling for solutions. I only knew that i had to use admin:mylogin on ssms (I picked the ssms option over SQLCMD which is also another way to try) and decided to try it. I clicked on object explorer, prefixed my login with ‘Admin:’ and connected. After all, all connections go via Object Explorer right? No…I was faced with error as below –

dac6

I was using the wrong method to connect – the right way is to use ‘Database Engine’ query icon, the one to the right of ‘New Query’ on SSMS toolbar. I tried again using that method (it gives you the same login window as object explorer did). Below was the next error.

dac5
Now I realized that I had RDP-ed into the machine and did not have remote admin connections enabled. So the next step  was enabling those connections, as below. This configuration is  apparently needed on clustered servers also – and may not be needed if you are on a local machine.

EXEC sp_configure ‘remote admin connections’, 1;
GO
RECONFIGURE
GO

Now I was done and connected fine.

What can I do with DAC?
I was able to do a sp_who2, kill a runaway process, and run some dmv queries. I was not able to do a backup/restore.  This link from MSDN has a list of diagnostic and troubleshooting queries that can be run (various DBCC commands and querying DMVs). It is important to remember that it not the best option to run regular maintenence – only DBCC commands that relate to troubleshooting specific and rare issues.

What is the database I connect to with DAC?
It is the default database associated with the login. You can access any database after connecting with the use statement. To connect directly to the master you can use the sqlcmd line sqlcmd –A –d master (it assumes your windows login has access to master as SA).

What are some other miscellaneous facts to know regarding DAC?
It has limited resources, so should always be used for lightweight operations.
It cannot make use of parellelism.
The port number that DAC listens on is written to the error log during start up and is 1434 by default.
You can have only one DAC connection open at a time.(Even if the
other admin is a different person).

There is a great post by my friend and SQL Server MVP Kendra Little’s blog on the same subject – which  is also a worthy read. She also gives us an interesting DMV based query to find out if there are other DAC users if you are not allowed to get in.

Knowing about DAC might save your bacon in unexpected situations.I consider enabling it to be added to my list of standards for configuring a server.

What it took to stump the optimizer

Sometime ago I was working with a Microsoft support technician on a ticket related to some database issues on SCOM. The technician wanted to run a stored procedure to clear out some of the backlog we were seeing with a batch job.

When he ran the procedure (via Query Analyser) – we encountered an error as below. Basically an error that is wee bit rare, the optimizer telling us that it is stumped and the query is too big for it to process. The technician moved on to other ways of resolving the issue as fixing this problem involved touching some procedures that came with the product and was beyond the scope of our call. But after we were done, I was intrigued to find out what caused the optimizer to give up.scomoptimiser

I went to Line 177 of the specified stored procedure and found a statement that looked pretty small on the outside:

DELETE dbo.[CategoryOverride]

FROM dbo.[CategoryOverride] CO

JOIN #BaseManagedEntitiesToPurge BMETP

ON CO.[InstanceContext] = BMETP.[BaseManagedEntityId]

The table CategoryOverride had about 300 rows..and  on checking what went into the temp table – it wasn’t all that large either, only around 100 rows. Looking at the estimated plan from the optimizer gave me nothing – a perfectly ordinary plan with an equijoin on a clustered index. So what was causing the problem?

I looked to see if the table CategoryOverride had any triggers on it. None. Then I went on to looking at keys. The table had one foreign key defined on it.

scomo1

I decided to script this key and see what it showed me.

ALTER TABLE [dbo].[CategoryOverride] WITH CHECK ADD CONSTRAINT [FK_CategoryOverride_ManagementPack] FOREIGN KEY([ManagementPackId])

REFERENCES [dbo].[ManagementPack] ([ManagementPackId])

ON DELETE CASCADE

GO

There was my culprit – an ON DELETE CASCADE. So many other tables is this touching? A quick look at the dependancies on this table showed me no less than 14. For one record to be deleted – there were dependancies on 14 tables. I picked one record on the main table and counted how many dependant records were there to clean up. It was about 6000. So the delete statement was far from a benign straightforward delete – it had a huge amount of data to clean up and the optimizer could not generate a plan across 14 tables some of them having more cascade deletes on them.

Moral of the story – never assume the simplicity of a statement based on looking at it, or what the query optimizer tells you. The truth may be much deeper. If I had the choice of fixing it I would remove the cascade delete and go on to cleaning up data step-by-step in each of the dependant tables.

Tail of the log has not been backed up

This is the second post in my attempt to blog one little thing every day. Today I ran into this message when trying to restore a database over an existing database. I forgot to check the box that says ‘overwrite existing database’ and got this rather informative error. I love this error message since it tells exactly what the user should do

‘The tail of the log of the database <mydatabase< has not been backed up – Meaning there are transactions in the log that need to be backed up.

‘USE BACKUPLOG WITH NORECOVERY’ if you want to backup the log and it contains work you don’t want to lose. ‘- It tells exactly what should user do in case he needs the older copy.

‘USE WITH REPLACE OR WITH STOPAT CLAUSE OF THE RESTORE STATEMENT TO JUST OVERWRITE THE CONTENTS OF THE LOG’ – in other words if am using the GUI I better check the box to tell sQL Server to overwrite the existing database, if that is what I intend to do.

This message tells me what is wrong and also evaluates the two possibilities – I want to keep or discard the old copy of the database, and tells me what to do in both situations. It is an excellent example of user friendly error messages.

blogpost

 

 

SQL Server 2012 – Changes to set up

I plan to write a series of posts while learning more on SQL Server 2012. The changes to set up as I observed while installing SQL Server 2012 RC0 are :

1 Pre requisites include Powershell 2.0 and .NET Framework 3.5
2 If you have Windows 7/Windows Server 8/Office 2010 it expects Service Pack 1 to be installed (does not even proceed until pre requisites if not).

The install is a slipstream process – by that all the necessary pre requisites are part of the media, there is no need to go to separate sites to download/install.
3 There is no reboot required upon install (I only tested it as standalone, with all other programs closed – this may not be true in a cluster install).

4 32 bit install is still supported.
5 Support for Itanium appears to have been removed.

Acknowledgement:

I was inspired to learn more and write by Aaron Bertrand’s presentation on New Features at the PASS conference 2011(http://sqlblog.com/blogs/aaron_bertrand/archive/2011/08/07/sql-server-v-next-denali-changes-to-system-objects-in-ctp3.aspx)