Archive for the ‘SQL General’ Category

Recyle Error Log

May 23, 2011 Comments off

If you not often restart your server then your SQL Server error log files get filled up and it will take more time to load on your SSMS and sometime SSMS tale long time to respond

Let see how we can recycle the Error log. SQL Server Error log will be automatically recycle when you recycle the SQL Server or the Server .however we will not be recycle or reboot the server very often.

SQL Server has DBCC Error log command to automatically Recycle the Error Log. however we cannot run this manually so, we need to create a Automatic job to Recycle the SQL Server Error Log.

To Configure the Error Log we need to Run the following command on SQL Server 2005 or Later.

xp_instance_regwriteN’HKEY_LOCAL_MACHINE’,N’Software\Microsoft\MSSQLServer\MSSQLServer’,N’NumErrorLogs’, REG_DWORD, 6

We can limit the number of error log file by specifying number from 6 – 99 so we can have maximum of 99 files.

Categories: SQL General

Dedicated administrator connections are not supported. (Microsoft.SqlServer.Management.SqlStudio.Explorer)

December 9, 2010 Comments off

When trying to open DAC using SSMS you will get Dedicated administrator connections are not supported. (Microsoft.SqlServer.Management.SqlStudio.Explorer). if you get this error message then you are trying to open DAC connection using the Object explorer. Only one DAC connection can be opened so we will not be able to use object explorer to open DAC. 

There are two ways to open DAC connection

1.SSMS>New>Database Engine Query > ADMIN:Servername (on the server name)

2.SQLCMD -S Servername -E -A

Categories: SQL General

Rename SQL Server HOST Name on Stand-Alone Server

November 13, 2010 4 comments

When we change the SQL Server host name, we don’t need to reinstall the SQL Server to take the new name to affect.

once you change the SQL Server HOST name on Stand-Alone machine Replication,Mirroring,Wont work so,before rename the SQL Server Host Name need to considered the following.

  1.   If your server is involved replication,Logshipping then, you need to re-configure the replication ,log shipping otherwise it wont work
  2.  If you have mirroring configured for 2005 and later version you need to turn off mirroring and need to re-establish the mirroring
  3.  If the server is cluster then need to follow the different steps
  4.  If you have reporting service configured on the server after the rename the service wont be available for more information please Click Microsoft Guide

Steps to be followed.

  1. SELECT  @@SERVERNAME  — Give you the Existing server Name present in the metadata. it will be the old server name.

Remove the Old Server Name from the Metadata.

2.  Sp_dropserver ‘Old_Server_Name’

Add the renamed server name

3. Sp_Addserver ‘New_server_Name’ , ‘Local’

if you don’t Specify the ‘Local’ then You will Get @@servername as NULL.

4. Restart the SQL Services.

5. SELECT @@SERVERNAME    — Will give you the modified Server Name in the Step No.3

 Note : This Solution is Only for the Stand-Alone Machine

Categories: SQL General

What is DeadLock?

September 30, 2010 1 comment

When the two user processes have locks on separate objects and each user wants a lock on the other’s process then deadlock will occur.

For EX: User 1 has Lock on object “History” whereas User 2 has Lock on “HistoryDetails” and User 2 wants Lock on “History” and user 1 wants Lock on “HistoryDetails”.   such case SQL Server will end up with deadlock by choosing the user who will be a deadlock victim.

When the Lock Manager detected Deadlock by using deadlock detection algorithm the following things will happen by SQL Server once the Deadlock detected.
                1. Lock manager will choose one of the user process as a deadlock victim 
                2.Lock Manager will initiate 1205 error message to the client
                3.Lock Manager will kill the Victim Process and free up the resources and allow the other  process to continue.

Categories: SQL General
%d bloggers like this: