Can not open user default database; login failed

Leave a reply

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

2 Replies

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?

53 Replies

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

2 Replies

/*
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

Leave a reply

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.