Lost Sa Password for SQL Server 2008, 2008 R2, 2005

Below steps helps to reset the "sa" password if you forgot or lost without install SQL Server



1. Login into server using Administrator login
2. Open SQL Server Configuration Manager
3. Select SQL Service - Properties - Advanced -- Need to add (-m;) parameter to startup parameter of the
SQL Service, to start SQL Server in single user mode to reset the "sa" password
Note - Dont give any space after semicolon. Add -m; parameter starting itself it looks like below
-m;-dc:\program..........
4 Restart the SQL Service
5. Open Command Prompt
6. Run SQLCMD
If you have two instances running on server say SQL 2008 R2, SQL 2012, then issue command as below
7. SQLCMD  -SKALYAN\SQL2008
1>
After invoking the SQLCMD it should display 1> prompt indicates login into server with admin login


Possible Errors

You may receive the below error when you run SQLCMD from command prompt
Login failed for domain\user or user doesnot have sysadmin permissions

If you got the above error please stop the SQL Service and change the built-in account to Local System and start the service in single user mode and run the below steps

1> select @@servername
2>go

1> create login [domain\kalyan] for windows;
2> go

1> sp_addsrvrolemember 'domain\kalyan','sysadmin';
2> go


If NP (named pipes) is not enabled in your system you may encounter the below error
C:\Users\kalyan>sqlcmd
HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired.
Then Enable the Named Pipes protocol from SQL Server configuration manager then again invoke 7th step
Issue the below commands to enable / reset sa password

1> select @@servername --> Make Sure Instance name is correct
2> go
1> alter login sa with password='kalyan@'
2> go
1> exit
1> alter login sa enable
2> go

Remove the (-m;) parameter from the SQL Server startup parameters, Restart the SQL Service and then Open SSMS with SQL Authentication. Then change the sa password as you like.

Cant Uninstall SQLServer 2008 Express Tools

I recently encountered the below error while uninstallting SQL Server 2008 Express tools, because the insallation got failed and not moving forward.

SQL Server Setup has encountered the following error:
No feature were uninstalled during the setup execution. The requested features may not be installed. Please review the summary.txt logs for further details.
Error code 0x84B30002.
Follow the below steps it may help to uninstall, Before follow this procedure please make sure you have some idea about registry

1. Open Registry Editor
2. Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\
3. Browse Ids one by one and identify the GUIDs for SQL Server 2008
4. Run the below command for all SQL Server 2008 Guids one by one
From command prompt

msiexec /x "Guid"

Remove all Guids which are releated to SQL Server 2008 R2 / Express
Restart the system and verify in the control panel - program and features

Then you can proceed with a fresh copy of installation.

'syspolicy_purge_history' cannot be run because the powershell subsystem failed to load

We may face the below error while running few jobs which has powershell script associated to any of the job steps.

Error Message
'syspolicy_purge_history' cannot be run because the powershell subsystem failed to load

We can fix the issue by updating the value in one of the system table in MSDB database.
syssubsystems - It contains information about all available SQL Server agent proxy subsystems. It is stored under MSDB database.

1) Use msdb
select * from syssubsystems
2) Verify the record of powershell subsystem, need to verify two things
  a) subsystem_dll  b) agent_exe
If there is any mismatch in the dll path or exe paths, then we need to update the entries with accurate paths.
3) This is a system table, so we need to run the below command to allow updates into system tables.
EXEC sp_configure 'allow updates', 1;
reconfigure with override;
Prepare the update command / delete complete information the table.
4) To Re-Populate
exec msdb.dbo.sp_verify_subsystems 1 / Restart the SQL Server Agent Service
5) Disallow updates
EXEC sp_configure 'allow updates', 0;
reconfigure with override;

sp_MSforeachdb Examples

Without using cursor we can execute a command in all the databases or on required databases by using sp_MSforeachdb.

Examples
sp_MSforeachdb 'select ''?'''   -- List all databases that are in the instance. '?' Indicated Database Name

sp_MSforeachdb 'Use [?]; exec sp_spaceused'  -- Example to execute any stored procedure

sp_MSforeachdb 'Use [?]; select * from sysfiles'  -- Example to execute any SQL Statement

sp_MSforeachdb 'Use [?]; Create table ForEachDBTest(Id Int)'  -- Creating a table in all databases

sp_MSforeachdb 'Use [?]; select * from ForEachDBTest'   -- Selecting rows from a table through all databases.

sp_MSforeachdb 'If ''?'' like ''TempDB''    -- Conditional wise execution
begin
select * from sysfiles
end '
Below command is used to display list of databases which has Create Statistics property is set to True

EXEC sp_MSforeachdb N'IF DatabasePropertyEx(''?'', ''ISAutocreateStatistics'')=1  
begin
 Print ''?''
end'

Job Name from sp_who2 ProgramName Column

To verify the active processes we all use a command called sp_who2, It gives information about the processes that are running on the instance.

In the output there is a column called programName using which we can identify what program is invoked the process. If SQL Server Agent is invoked any job then this column is filled like below.

SQLAgent - TSQL JobStep (Job 0x63F686011DDC76FAA8593A541343A1A7E : Step 2)

Below command helps to identify the job name based on the above job step value

select * from msdb..sysjobs where
job_id = convert(uniqueidentifier,0x63F686011DDC76FAA8593A541343A1A7E)

Request to run job syspolicy_purge_history refused because the job has been suspended

I got this error due to some mistmatch in the syssubsystems table in MSDB database.

After making necessary changed to syssubsystems table I restarted the SQL Server Agent to fix the issue.

Restart the Agent and try to execute the job again.

Statistics in SQL Server

Statistics will help the optimizer to generate optimized cost based estimation plan. The purpose of the optimizer is actually generates a plan which will retreive data in a faster way. Statistics are help the optimizer to understand how many rows the table has, how many indexes that table has and estimated number of rows that is going to return, it uses all those information for estimating to generate a low cost plan. If the statistics are out of date then optimizer decision might be wrong. We need to make sure statistics are update to date to boost up the query performance.

Sp_helpstats <table_name>

Dbcc show_statistics (<table_name>, <statistics_name>)


Select * from sys.stats -- Displays information of statistics that are on Indexes

Select * from sys.stats_column – Displays columns information of the statistics

Select * from sys.columns – We can identify on which column the statistics got created.

Always make sure Auto_create statistics option to be true, unless you have specific requirement on database.

Using Create Statistics option we can create user-defined statistics on the table.

drop statistics table_name.statistics name

sp_autostats '<table_name>' -- displays last updates statistics date.

Migrating Reporting Services To SQL Server 2008

Migration -- It is defined as moving databases from one version to other version.

Steps
1. Keep Full backup of ReportServer databases.

2. Keep all configuration files ready (reporting services configuration files)

3. Backup the reporting services encryption key

4. Attach the Report Server databases / Restore ReportServer databases in newly installed server.

5. Make necessary changes in the reportserver.config file based on the existing configuration file.

6. Configure the report server and point to the new server by specifying existing reporting server databases

Preemptive and Non-Preemptive scheduling

In Preemptive scheduling priority tasks are executed first, let say a task (task1) started executing, in the meanwhile a prioritized task (task2) has invoked then task1 will moved into resumed state until the task2 is finish its work.

In NonPreemptive schedule the running task can't be resumed or interrupted until the completion. FIFO

RAID Levels

What is RAID?
Redundant Array of Independent Disks, it uses multiple harddrive to store the data. Even if one drive is failed the data is safe and accessible.

Where it uses?
RAID can be used in critical applications where you feel data is more important. This helps to controls the data loss of any business and to recover from disaster.To implement RAID we should have more than one disk

Terminology of RAID
a) Mirroring – Data will be written into multiple disks, ie., copy of data will be maintained.
b) Striping – Sequential blocks of data will be splitted into multiple disks.
c) Fault Torence – Parity information will be stored in this type of RAID mechanism, even if one disk will fail it will allow recovering the data.

Levels of RAID

RAID -0 – Data is simply written using a striping mechanism, that means sequential data blocks will be written into multiple hard drives simultaneously. It will provide huge performance, but the downside of this RAID0 is if one of the drive fails all data is lost. It offers only speed not data protection. So be careful while choosing it.
RAID -1 – This utilizes mirroring mechanism while writing data into disks, that means same data will be maitained at two places, if one drive fails we can access the data from other drives. But the downside of this mechanism is out of two drives we can make use of only one, that means the usable capacity of the drives is only 50%
RAID – 5 – It uses a mechanism called fault tolerance that data will be written into all the drives with extra information called parity bit information. This will be used to retrieve information from any one the drive fails. To implement RAID-5 it requires minimum of 3 drives, out of which 2 drives can be used to write data and 1 will be used to store parity information. 
RAID – 6 – It is similar to RAID -5 but it provides high fault tolerance, data can be written into all drives with parity information, if any of two hard drives fail we are in a position to retrieve data from the others, but it requires minimum of 4 drives and the usable capacity is only 2 drives out of 4 drives.
RAID – 10 – It’s a combination of RAID 1 and RAID 0, in this mechanism data will be mirrored and striped across the drives, the usable capacity is 50% only. It provides performance as well as data protection.

DBCC CheckPrimaryFile -- Attach Detach MDF, LDF

I have given a MDF file to you and asked you find out name of the database, Is it possible?
Yes It is possible using a command DBCC CheckPrimaryFile

DBCC CheckPrimaryFile ('Path of Mdf File', 2) -- Displays name of the database, database version, collation

DBCC CheckPrimaryFiles -- This command is very useful while you are attaching database of one server to other server.


DBCC checkprimaryfile('C:\MSSQL\DATA\TESTDB.mdf',0) -- Displays whether the file is MDF or not

DBCC checkprimaryfile('C:\MSSQL\DATA\TESTDB.mdf',2) -- Displays name of the database, version of database and collation .

DBCC checkprimaryfile('C:\MSSQL\DATA\TESTDB.mdf',1) -- Displays complete information about the database files, similar to database..sysfiles

DBCC checkprimaryfile('C:\MSSQL\DATA\TESTDB.mdf',3) -- Displays logical and physical information of the database.

Sequences in SQL Server 2012

A sequence is a user-defined object which generates a sequence of numbers, based on the specified start value and end value using increment etc., The difference between sequence and identity columns is, Identity column is bound to one particular table, whereas the sequence can be called or included in any table in the database.

Syntax

Create Sequence sequence_name as datatype start with <starting_number>
          increment by <increment_value> minvalue <number> maxvalue <number>  <[cycle | no cycle]

CREATE Sequence MySequence START WITH 1 increment BY 1 MinValue 1 MaxValue 5

To Read values from Sequence (Next Value)

SELECT NEXT VALUE FOR MySequence -- Initially it starts displaying 1 because in the start with clause we mentioned starting number as 1, till 5 it generates values, If you execute select statement even after 5 also
it generates the below error

Msg 11728, Level 16, State 1, Line 1
The sequence object 'MySequence' has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated.

We can restart the sequence after reaching its maximum value as below.
1) we can include Cycle keyword in the create sequence command or we can Alter the existing sequence

Alter sequence mysequence cycle -- It generates values from start with to maximum, once it reaches maximum it starts from start with value again, it wont end.

Alter sequence mysequence restart -- It just restart the sequence for one time again if you query after it reaches the maximum number it throws error.

Sequence can be Include in the Insert command on a single table or multiple tables
CREATE TABLE Items (id int, NAME varchar(10))
CREATE sequence Item_Sequence AS Int START WITH 1 MINVALUE 1 MAXVALUE 1200 NO CYCLE
INSERT INTO Items VALUES (NEXT VALUE FOR Item_sequence, 'Kalyan')
GO 10
SELECT * FROM Items

SELECT Name, current_value FROM sys.sequences -- To Verify the current value of the sequence

The behaviour of the sequence is same as Identity if we include that in begin tran and commit tran
We can't rollback sequence even if the batch gets rollback.

Restore HeaderOnly, VerifyOnly, FilelistOnly

Restore Headeronly  -- This command displays backup information of a particular database backup.

Restore Filelistonly  -- This command displays list of data files and log files in the database backup.

Restore VerifyOnly -- Verifies whether the backup set is valid or not, It wont verify the structure of data in the backup media.


SQL Server Syntax

RESTORE FILELISTONLY FROM DISK = 'C:\temp\TestDB.bak'

RESTORE HEADERONLY FROM DISK = 'C:\temp\TestDB.bak'

RESTORE VERIFYONLY FROM DISK = 'C:\temp\TestDB.bak' Redgate Syntax :

Redgate Syntax

EXECUTE master..sqlbackup '-SQL "restore headeronly from disk='C:\temp\TestDB.bak''"'

EXECUTE master..sqlbackup '-SQL "RESTORE filelistonly from disk=''C:\temp\TestDB.bak''"'

EXECUTE master..sqlbackup '-SQL "RESTORE verifyonly from disk=''C:\temp\TestDB.bak''"'

Transfer logins between SQL Servers

We will keep on moving databases between instances, we migrate the databases from one server to another server, or we upgrade the sql versions. All the time we need to move logins from one server to another server. Here is the Microsoft KB article which generates login script, we can directly copy the script and paste it on target server. 

http://support.microsoft.com/kb/918992/

SP_HELP_REVLOGIN is the procedure we need to execute and copy the logins and execute on required server, automatically logins will transfer along with SIDs and passwords.

What is tempdb database?

Tempdb database is a global resource that is available to all users connected to the instance of SQL Server.

1) TempDB will cleared everytime when the SQL Server is stopped and started, It will recreate only if the file path is moved using alter database tempdb.

2) By default TempDB creates an MDF file with 8 MB and LDF file with 1 MB and grows upto 2 TB

3) Each SQL Server Instance have only one Temp DB Database.

4) We cant change the recovery model and of TempDB Database, It is always in Simple Recovery Model

5) It is not possible to drop or detach tempdb database

6) Not possible to change database options like (e.g. Database Read-Only, Auto Close, Auto Shrink Etc)

7) Like all other databases we can't backup or restore or setup mirroring for tempdb database

Below command is used to move tempdb database into new location

USE master;
GO
ALTER DATABASE TEMPDB
MODIFY FILE (NAME = tempdev, FILENAME = 'C:\Program Files\Microsoft SQL Server\Data\TEMPDB.mdf');
GO
ALTER DATABASE TEMPDB
MODIFY FILE(NAME = templog, FILENAME = 'C:\Program Files\Microsoft SQL Server\Datatemplog.ldf');

Could not obtain exclusive lock on database ‘Model’

We may faced the issue in different scenarios. Below is one common scenario

1. If we login into model database from one session, and if you tried to invoke a create database statement from another session you may encounter with the error.

Enforce case sensitive while searching

We can enforce case sensitive search on case-insensitive databases using the below command.


use kalyandb
go
select * from customers where name='KALYAN'
collate SQL_Latin1_General_CP1_CS_AS

select * from fn_helpcollations() -- displays list of collation names supported by sql server.

Select Into, Delete Into

Select Into

We can use Into option in select and delete DML command, most of us will use select into to create a dummy table with same copy of table (excluding constraints) immediately, We can create a duplicate table wth complete structure or we can create with required fields.

syntax

select * into new_tale from old_table

select col1, col2 into new_table from old_table

Delete Into   -- Using this delete into commnad we can transfer rows into another table with in a single statement.  Below is the example

create table first (id int, name varchar(10), age int)
insert into first values (1, 'kalyan', 30)
insert into first values (2, 'kumar', 31)
insert into first values (3, 'vijay', 28)
insert into first values (4, 'hari', 25)

select * from first

create table second (id int, name varchar(10))
delete from first output deleted.id, deleted.name into second where id = 1

select * from second



Temporary Table and Temporary Variable

Temporary tables are also table like user objects, It has structure, we can create indexes, we can perform Insert, Update, Delete commands, supported in transaction management and it supports everything like an ordinary user object. Only difference it is is not stored inside the database, It will store on the TempDB database. The life of temporary table is till the user disconnects the session from SQL Server, temporary table will be removed from tempdb database. There are two types of temporary tables Local Table, Global Table, only difference between those two tables is scope. Local temporary table is specific to session, if you disconnect the session then it will erased from tempdb, global temporay table the name itself indicates it is globally available to other users also. The life of global temporary variable is till the table owner disconnects the instance.

Examples for global and local temporary tables.

Local Temporary Table
Use Kalyandb
go
create table #local_table (id int, name varchar(20))
Insert into #local_table values (1, 'kalyan')
Insert into #local_table values (2, 'kumar')

Eventhough you create a table in kalyandb the object will create in tempdb because it is a temporary table.
We can verify by using the below command
select * from tempdb..sysobjects where xtype='U' * from tempdb..sysobjects where xtype='U'


Global Temporary Table

Use Kalyandb
go
create table ##global_table (id int, name varchar(20))
Insert into ##global_table values (1, 'kalyan')
Insert into ##global_table values (2, 'kumar')

Try to access the global temporary table using other session, we can able to view the table data.


Table Variable -- Like all other variables, this is also one type of variable which can be declared in the while performing T-SQL programming, the scope of table variable is only specific to that batch. The difference between temp variable and temp table is we cann't create index or statistics on temp variable, and this cannot be used in transaction management, you can't use DDL statement like alter on temp variable whereas it is possible in temp table.

Examples

declare @temp_table table (id int, name varchar(20))
insert into @temp_table values(1, 'kalyan')
insert into @temp_table values(2, 'kumar')
select * from @temp_table