aiDeeva.com

Author: Jugal Shah

  • Can not open user default database; login failed

    Troubleshooting Authentication Issues

    Problem:

    Can not open user default database; login failed

    Scenario:

    I have windows DBA group which has default database DB1 and has SYSADMIN rights on SQL Server, by some reasons the DB1 database is corrupted and user is unable to login.

    Solution:

    Step 1:

    Connect SQL Server using SQLCMD/OSQL as below and give the default database name as MASTER

    sqlcmd -E -d master

    Step 2: Now issue ALTER LOGIN statement and change the default database

    Use SP_DefaultDB procedure to change the default database

    sp_defaultdb (SQL Server 2000)

    Changes the default database for a login

    Syntax

    EXEC sp_defaultdb 'Victoria', 'pubs'

    (SQL Server 2005)

    ALTER LOGIN LOGINNAME WITH DEFAULT_DATABASE = MASTER

  • Index Covering

    What is Index Covering?

    Index covering means to add a non-clustered index on every column  which are used in Query. Easy solution to improve query performance

     

    Scenario: Speed Up a Query for a Table with a Clustered Index

    Consider a very typical scenario: you have a table EmployeeSalary table with a clustered index on Employee_ID column. You need to speed up a select query quickly:

    SELECT Salary_DATE, SUM(AMOUNT) FROM EmployeeSalary GROUP BY Salary_DATE

     

    The query’s execution plan is quite simple: the database engine scans the whole clustered index, and then it sorts the intermediate result set to satisfy the GROUP BY clause.

     

    Can a non-clustered index speed up the query? Definitely. Just create a non-clustered index that contains all the columns used in the query:

    CREATE  INDEX sal_amt ON dbo.EmployeeDetail(Salary_DATE, AMOUNT)

     

    Re-execute the query it will run faster than the previous query.  If you, look at the execution plan: the query accesses only the index salary_amt; it doesn’t touch the table at all. In fact, there is no need to access the table because all the columns necessary to satisfy the query are already stored in the index. This is called index covering.

  • How to change SQL Server Instance Name?

    First collect the output of the current instance configuration. You can get the instance name stored in the SQL Server metadata.

    Make sure you have backup of all the database if you are changing the production server instance name.

    	sp_helpserver
	select @@servername

    You can change the instance name using below query.
    Default Instance 

    	sp_dropserver 'old_name'
        go
        sp_addserver 'new_name','local'
	go

    Named Instance 

    	sp_dropserver 'Server Name\old_Instance_name'
        go
        sp_addserver 'ServerName\New Instance Name','local'
	go

    Verify sql server instance configuration by running below queries

    	sp_helpserver
	select @@servername

    Restart the SQL Server Services.

    	net stop MSSQLServer
	net start MSSQLServer

  • Exception Handling using Try-Catch in SQL Server 2005

    /*
    Error Handling using Try Catch Block
    */

    CREATE PROCEDURE usp_GetErrorInfo
    AS
    BEGIN
    SET NOCOUNT ON
    SELECT
    ERROR_NUMBER() AS ErrorNumber,
    ERROR_SEVERITY() AS ErrorSeverity,
    ERROR_STATE() AS ErrorState,
    ERROR_PROCEDURE() AS ErrorProcedure,
    ERROR_LINE() AS ErrorLine,
    ERROR_MESSAGE() AS ErrorMessage
    END
    GO

    /*
    TRY – CATCH in user defined stored procedure
    */

    ALTER PROCEDURE TEST
    AS
    BEGIN
    BEGIN TRY
    — Generate divide-by-zero error.
    SELECT 1/0;
    END TRY
    BEGIN CATCH
    — Execute error retrieval routine.
    EXECUTE usp_GetErrorInfo;
    END CATCH
    END
    GO

    EXECUTE TEST
    GO;

  • Replication Agents

    Replication Agents

    Replication uses a number of standalone programs, called agents, to carry out the tasks associated with tracking changes and distributing data. By default, replication agents run as jobs scheduled under SQL Server Agent, and SQL Server Agent must be running for the jobs to run. Replication agents can also be run from the command line and by applications that use Replication Management Objects (RMO). Replication agents can be administered from SQL Server Replication Monitor and SQL Server Management Studio.

    Snapshot Agent The Snapshot Agent is typically used with all types of replication. It prepares schema and initial data files of published tables and other objects, stores the snapshot files, and records information about synchronization in the distribution database. The Snapshot Agent runs at the Distributor.