Answer / samba shiva reddy . m

SQL Server provides a special diagnostic connection for administrators when standard connections to the server are not possible. This diagnostic connection allows an administrator to access SQL Server to execute diagnostic queries and troubleshoot problems even when SQL Server is not responding to standard connection requests.

This dedicated administrator connection (DAC) supports encryption and other security features of SQL Server. The DAC only allows changing the user context to another admin user.
Uses:
Querying dynamic management views for basic diagnostics such as sys.dm_tran_locks for the locking status, sys.dm_os_memory_cache_counters to check the health of caches, and sys.dm_exec_requests and sys.dm_exec_sessions for active sessions and requests. Avoid dynamic management views that are resource intensive (for example, sys.dm_tran_version_store scans the full version store and can cause extensive I/O) or that use complex joins. For information about performance implications, see the documentation for the specific dynamic management view.
Querying catalog views.
Basic DBCC commands such as DBCC FREEPROCCACHE, DBCC FREESYSTEMCACHE, DBCC DROPCLEANBUFFERS, and DBCC SQLPERF. Do not run resource-intensive commands such as DBCC CHECKDB, DBCC DBREINDEX, or DBCC SHRINKDATABASE.
Transact-SQL KILL <spid> command. Depending on the state of SQL Server, the KILL command might not always succeed; then the only option may be to restart SQL Server. The following are some general guidelines:
Verify that the SPID was actually killed by querying SELECT * FROM sys.dm_exec_sessions WHERE session_id = <spid>. If it returns no rows, it means the session was killed.
If the session is still there, verify whether there are tasks assigned to this session by running the query SELECT * FROM sys.dm_os_tasks WHERE session_id = <spid>. If you see the task there, most likely your session is currently being killed. Note that this may take considerable amount of time and may not succeed at all.
If there are no tasks in the sys.dm_os_tasks associated with this session, but the session remains in sys.dm_exec_sessions after executing the KILL command, it means that you do not have a worker available. Select one of the currently running tasks (a task listed in the sys.dm_os_tasks view with a sessions_id <> NULL), and kill the session associated with it to free up the worker. Note that it may not be enough to kill a single session: you may have to kill multiple ones.

SQL Server makes every attempt to make DAC connect successfully, but under extreme situations it may not be successful.
Connecting with DAC :
By default, the connection is only allowed from a client running on the server. Network connections are not permitted unless they are configured by using the sp_configure stored procedure with the remote admin connections option.

Only members of the SQL Server sysadmin role can connect using the DAC.

The DAC is available and supported through the sqlcmd command-prompt utility using a special administrator switch (-A).
You can also connect prefixing admin: to the instance name in the format sqlcmd -Sadmin:<instance_name>. You can also initiate a DAC from a SQL Server Management Studio Query Editor by connecting to admin:<instance_name>.

Hi All, I want to display all duplicate records in the table. My query has to fetch all the records which are duplicate(First Name or Last Name). Also I want the ability to also pull names where there might be a middle initial placed in the end of the first name field, (i.e., "Maria Z. " vs. "Maria") as well. Please guide me to find this. Table: ID FirstName LastName 1 Zach H Hoffman 2 Zach Hoffman 3 Troy Hoffman 4 Shawn Livermore 5 Prem S 6 Jony Hoffman H 7 Zach Modan I need the query to filter......... ID FirstName LastName 1 Zach H Hoffman 2 Zach Hoffman 3 Troy Hoffman 6 Jony Hoffman H 7 Zach Modan I hope this example will give you clear idea..... Thanks in Advance Prem

2 Answers  

---

Explain different types of locks in sql server.

0 Answers  

---

How to see existing views in ms sql server?

0 Answers  

---

What is difference between process and thread? Explain lazy writer funcationality.

2 Answers   Microsoft,

---

Can you explain what is indexed view? How to create it?

0 Answers  

---

Where are sql server usernames and passwords stored in the sql server?

0 Answers  

---

What are the lambda triggers?

0 Answers  

---

What is a table called, if it does not have neither Cluster nor Non-cluster Index?

2 Answers  

---

How do I edit a stored procedure in sql server?

0 Answers  

---

What is an indexing technique?

0 Answers  

---

Explain DBMS, RDBMS?

6 Answers  

---

how can you move the master database

1 Answers   IBM,

---