How do you set up collation during sql installation ?

How will you take SQL Server in single user mode ?

SQL Server Configuration Manager > SQL Server Services > SQL Server (InstanceName) > Properties > Advanced > Startup Parameter >

For SQL Server 2005 and 2008

 Just put   -m;  then Save/Ok
Example:
-m;-dC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

For SQL Server  2012:
Click Startup parameter and just put -m;

Tips:
You should stop other sql server services to get connect with SQL when sql server is in single user mode

 

Microsoft SQL Server, Error: 14640

Service Broker message delivery is not enabled in this databse. Use the ALTER DATABASE statement to enable Service Broker message delivery.(Microsoft SQL Server, Error: 14640)

USE master
GO
ALTER DATABASE MSDB SET ENABLE_BROKER ;
GO

Top Secret Tips:
Delete Database Mail Profile and Account and Re-create it

How do you find Number of current connections ?

SELECT DB_NAME(dbid) as DBName,
COUNT(dbid) as NumberOfConnections, loginame as LoginName FROM  sys.sysprocesses
WHERE dbid > 0 GROUP BY dbid, loginame

How do you configure Msmdpump.dll for Analysis service ?

Install  sql_as_oledb.msi (Analysis Management Objects (olap oledb) ) can be downloaded via sql server 2012 feature pack 1 folder. Install as well as Web server.

The below configuration is needed to make the ini file  point to the OLAP server.

1. Log in to Analysis Server and copy the below files
at <drive>:\Program Files\Microsoft SQL Server\<instance>\OLAP\bin\isapi:
 MSMDPUMP.DLL,
 MSMDPUMP.INI, and
 a Resources folder

2. Log in web server and paste those files in below location

C:\inetpub\wwwroot\OLAP


In the msmdpump.ini the FQDN (Fully qualified domain name) of the Analysis Server name is to be used (because KERBEROS authentication implemented by  is using FQDNs, e.g. UAT01SQL2012.wolterskluwer.com).

More Information in below
http://msdn.microsoft.com/en-us/library/gg492140.aspx


 

Which features do we need for Analysis services ?

Instance Features:
Analysis Services
Shared Features:
Client Tools Connectivity
Client Tools Backwards Compatibility
Client Tools SDK
SQL Server Books online
Management Tools - Basic
           Management Tools - Complete
 SQL Client Connectivity SDK
Microsoft Sync Framework
             

How Data base size and path

SELECTdb.[database_id] AS 'DB ID',
db
.[name] AS 'Database Name',
af
.[name] AS 'Logical Name',
af
.[filename] AS 'Physical Location',
(((
CAST(af.[size] AS DECIMAL(18,4)) * 8192) /1024) /1024) AS 'File Size (MB)',

((((
CAST(af.[size] AS DECIMAL(18,4)) * 8192) /1024) /1024) /1024) AS 'File Size (GB)'
FROM sys.databases db INNER JOIN sys.sysaltfiles af ON db.database_id = af.dbid

WHERE [fileid] in (1,2)

===================================================
SELECT name,Size, physical_name AS current_file_location

FROM sys.master_files=============================
sp_helpdb

There are no sql server instances or shared features that can be updated on this computer

This message means - Server is already updated for that sql server by that Service Pack
"There are no sql server instances or shared features that can be updated on this computer"

How do you install 32 bit SQL ??

Options > Processor Type > x86

How can Find Fully Qualified Domain Name(FQDN) ?

Start Menu > Run > cmd > nslookup <Server_Name Or IP>
More Commands IP related:
netstat -na|find "LISTEN" netstat -an
arp -a



 

why and how do you restore MSDB Database in SQL Server ?

Why:
1. MSDB can be corrupted
2.If you restore Master Database, MSDB will be re-built . This means you will loose data in MSDB.

How:
Stop SQL server agents and other SQL services without SQL Service
then you can restore with over-write

How do you find out SQL Server installation Path ?

declare @rc int, @dir nvarchar(4000)
Exec @rc = master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\Setup',
N'SQLPath',
@dir output, 'no_output'
Select @dir AS InstallationDirectory

 

Which features do you need for SQL Client/Management Studio ?

Need to select only Shared Features
1. Client Tools Connectivity
2.Client Tools Backwards Compatibility
3.SQL Server Books Online
4.Management Tools - Basic
   Management Tools - Complete

How do you open new port and use in SQL server

How to open TCP Port 1755

1. Control Panel>System and Security>Windows Firewall>Advanced setting
2. Select inbound rules  then New Rule at the right hand corner.
Role Type: Port>Next
Specific local ports: 1755

 

How monitor SQL Cluster

Step 1:
Create a table named [CLUSTERFAILOVERMONITOR] in the master database of the SQL Server instance, with a column [PREVIOUS_ACTIVE_NODE]. Use the query below for creating the table.

create table CLUSTERFAILOVERMONITOR ( PREVIOUS_ACTIVE_NODE varchar(30) )

Step 2:

Insert the value of the currently active node in that table. Use the query below for this.

insert into CLUSTERFAILOVERMONITOR
values
(
'Node1_Name'
)
Step 3:
Create a job with code shown below in it. The job will execute on the master database, will run every minute (can be run every 5-10 seconds in SQL Server 2008) and when the failover happens it will send a notification.

After the failover has occurred, this job will change the value of PREVIOUS_ACTIVE_NODE column in the CLUSTERFAILOVERMONITOR table that we created in step1, and is therefore ready to monitor the next failover event.

Declare @var1 varchar(30) SELECT @var1= PREVIOUS_ACTIVE_NODE FROM CLUSTERFAILOVERMONITOR CREATE TABLE PHYSICALHOSTNAME ( VALUE VARCHAR(30), CURRENT_ACTIVE_NODE VARCHAR(30) ) INSERT INTO PHYSICALHOSTNAME exec master..xp_regread 'HKEY_LOCAL_Machine', 'SYSTEM\CurrentControlSet\Control\ComputerName\ComputerName\', 'ComputerName' declare @var2 varchar(30) SELECT @VAR2=CURRENT_ACTIVE_NODE FROM PHYSICALHOSTNAME if @VAR1<>@VAR2 Begin EXEC msdb..sp_send_dbmail @profile_name='DBAMail', @recipients='xyz@company.com', @subject=' Failover occurrence notification - SQLExample', @body='Cluster failover has occured for instance SQLExample. Below given are the previous and current active nodes.', @QUERY='SET NOCOUNT ON;SELECT PREVIOUS_ACTIVE_NODE FROM CLUSTERFAILOVERMONITOR;SELECT CURRENT_ACTIVE_NODE FROM PHYSICALHOSTNAME;SET NOCOUNT oFF' update CLUSTERFAILOVERMONITOR set PREVIOUS_ACTIVE_NODE=@VAR2 End DROP TABLE PHYSICALHOSTNAME

How do you find out all jobs/packages owners

-- Find out all job owners
USE msdbGo
EXEC dbo.sp_help_job
===============================
select s.name,l.name from msdb..sysjobs s left join master.sys.syslogins l on s.owner_sid = l.sid
======================================
-- Find out all package owners
select s.name,l.name from msdb..sysssispackages s
left join master.sys.syslogins l on s.ownersid = l.sid

How to choose a power plan

In the Control Panel click on Power Options, which takes you to Select a power plan page. If you do not see Power Options, type the word 'power' in the Search Control Panel box and then select Choose a power plan.

Default is Balanced

For Good Performance - Select High Performance

Daily Database Restore in Report Server

Create a job in Report Server

Job Name : Daily Database_Name Resotre

Step 01(Copy Differential/Restore/Archive):

DECLARE 

@rc int,

@FileName varchar(255),

@statement nvarchar(max),

@return int

 CREATE TABLE ##BackupFiles(

                [FileName] [varchar](255) NULL,

)

 -- Copy Full

 Insert Into ##BackupFiles(FileName)

EXEC @rc = master.dbo.xp_cmdshell 'dir \\SourceServer_Name\g$\MSSQL\100\Backups\Full\Database_Name /B'

 

IF @rc <> 0

BEGIN

RAISERROR('50005 Full Copy Failed',16, -1, @@servername )

END

 Set @FileName = (Select top 1 FileName From ##BackupFiles

Order By FileName Desc)

 Set @statement = 'DECLARE @rc int exec @rc = master.dbo.xp_cmdshell ''copy \\ SourceServer_Name \g$\MSSQL\100\Backups\Full\ Database_Name \' + @FileName + ' E:\PTCRestore\ Database_Name _backup.bak /Y /V'' IF @rc <> 0 BEGIN RAISERROR(''50005 Full Copy Failed'',16, -1, @@servername ) END'

print @statement

EXEC sp_executesql @statement,N'@return2 int OUTPUT',@return2 = @return OUTPUT

 IF @return > 0

BEGIN

RAISERROR('50005 Full Restore Failed',16, -1, @@servername )

END

 Delete From ##BackupFiles

 -- Copy Differential

 Insert Into ##BackupFiles(FileName)

EXEC @rc = master.dbo.xp_cmdshell 'dir \\ SourceServer_Name \g$\MSSQL\100\Backups\Differential\ Database_Name /B'

 IF @rc <> 0

BEGIN

RAISERROR('50005 Differential Copy Failed',16, -1, @@servername )

END

 Set @FileName = (Select top 1 FileName From ##BackupFiles

Order By FileName Desc)

 

Set @statement = 'DECLARE @rc int exec @rc = master.dbo.xp_cmdshell ''copy \\ SourceServer_Name \g$\MSSQL\100\Backups\Differential\ Database_Name \' + @FileName + ' E:\ Database_Name Restore\' + @FileName + ' /Y /V'' IF @rc <> 0 BEGIN RAISERROR(''50005 Differential Copy Failed'',16, -1, @@servername ) END'

 EXEC sp_executesql @statement,N'@return2 int OUTPUT',@return2 = @return OUTPUT

 IF @return > 0

BEGIN

RAISERROR('50005 Differential Restore Failed',16, -1, @@servername )

END

 -- Restore/Archive

 exec sp_killusers 'ptc'

 IF (select datename(dw,getdate())) = 'Sunday'

BEGIN

 RESTORE DATABASE [ptc] FROM  DISK = N'E:\ Database_Name Restore\ptc_backup.BAK' WITH  FILE = 1,  MOVE N'PTC_Data' TO N'E:\PTCRestore\ptc.MDF',  MOVE N'PTC_Log' TO N'F:\ Database_Name Restore\ptc.LDF',  NOUNLOAD,REPLACE,  STATS = 10

 

END

ELSE

BEGIN

 RESTORE DATABASE [Database_Name] FROM  DISK = N'E:\ Database_Name Restore\ Database_Name _backup.BAK' WITH  FILE = 1,  MOVE N' Database_Name _Data' TO N'E:\ Database_Name Restore\ptc.MDF',  MOVE N' Database_Name _Log' TO N'F:\ Database_Name Restore\ Database_Name.LDF',  NORECOVERY,  NOUNLOAD,REPLACE,  STATS = 10

 

Set @statement = 'RESTORE DATABASE [Database_Name] FROM  DISK = N''E:\ Database_Name Restore\' + @FileName + ''' WITH  FILE = 1,  MOVE N''PTC_Data'' TO N''E:\ Database_Name Restore\ Database_Name.MDF'',  MOVE N'' Database_Name _Log'' TO N''F:\ Database_Name Restore\ Database_Name.LDF'',  NOUNLOAD, STATS = 10'

 EXEC sp_executesql @statement,N'@return2 int OUTPUT',@return2 = @return OUTPUT

 IF @return > 0

BEGIN

RAISERROR('50005 Differential Restore Failed',16, -1, @@servername )

END

 END

 Set @statement = 'DECLARE @rc int exec @rc = master.dbo.xp_cmdshell ''move /Y E:\ Database_Name Restore\' + @FileName + ' E:\ Database_Name Restore\Archives'' IF @rc <> 0 BEGIN RAISERROR(''50005 Differential Move Failed'',16, -1, @@servername ) END'

 EXEC sp_executesql @statement,N'@return2 int OUTPUT',@return2 = @return OUTPUT

 IF @return > 0

BEGIN

RAISERROR('50005 Differential Move Failed',16, -1, @@servername )

END

 Drop Table ##BackupFiles

 ALTER DATABASE [ptc] SET READ_ONLY WITH NO_WAIT

 Step 02(CleanUp TransLog Files):

 EXEC MSDB.dbo.sp_start_job @Job_Name = 'CleanUp Translog.Subplan_1'

 Step 03(CleanUp Differential Files):

EXEC MSDB.dbo.sp_start_job @Job_Name = 'CleanUp Differential.Subplan_1'

How unlock sa account

How unlock sa account
ALTER LOGIN sa WITH PASSWORD = 'new_password' UNLOCK

Find Current Users

Find Current Users
Select login_name,COUNT(session_id)from sys.dm_exec_sessions group by login_name

How do shrink data file / log file

Shrink Datafile/Logfile:
 DBCC SHRINKFILE(datafile logical name)
DBCC SHRINKFILE(logfile logical name)

Find out Expensive queries

SELECT TOP 5

object_name(objectID)

,[Avg CPU TIME] = total_worker_time/execution_count

,execution_count

,Plan_handle

,query_plan

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle)

ORDER BY total_worker_time/execution_count DESC;

-------------------------------------

SELECT

TEXT

,query_plan

,requested_memory_kb

,granted_memory_kb

,used_memory_kb

FROM sys.dm_exec_query_memory_grants emg

CROSS APPLY sys.dm_exec_sql_text(sql_handle) 

CROSS APPLY sys.dm_exec_query_plan(emg.plan_handle)

ORDER BY emg.requested_memory_kb DESC

 

Find queries that take the most CPU overall

SELECT TOP 50

    ObjectName          = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)

    ,TextData           = qt.text

    ,DiskReads          = qs.total_physical_reads   -- The worst reads, disk reads

    ,MemoryReads        = qs.total_logical_reads    --Logical Reads are memory reads

    ,Executions         = qs.execution_count

    ,TotalCPUTime       = qs.total_worker_time

    ,AverageCPUTime     = qs.total_worker_time/qs.execution_count

    ,DiskWaitAndCPUTime = qs.total_elapsed_time

    ,MemoryWrites       = qs.max_logical_writes

    ,DateCached         = qs.creation_time

    ,DatabaseName       = DB_Name(qt.dbid)

    ,LastExecutionTime  = qs.last_execution_time

 FROM sys.dm_exec_query_stats AS qs

 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt

 ORDER BY qs.total_worker_time DESC

 

Find queries that have the highest average CPU usage:

SELECT TOP 50

    ObjectName          = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)

    ,TextData           = qt.text 

    ,DiskReads          = qs.total_physical_reads   -- The worst reads, disk reads

    ,MemoryReads        = qs.total_logical_reads    --Logical Reads are memory reads

    ,Executions         = qs.execution_count

    ,TotalCPUTime       = qs.total_worker_time

    ,AverageCPUTime     = qs.total_worker_time/qs.execution_count

    ,DiskWaitAndCPUTime = qs.total_elapsed_time

    ,MemoryWrites       = qs.max_logical_writes

    ,DateCached         = qs.creation_time

    ,DatabaseName       = DB_Name(qt.dbid)

    ,LastExecutionTime  = qs.last_execution_time

 FROM sys.dm_exec_query_stats AS qs

 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt

 ORDER BY qs.total_worker_time/qs.execution_count DESC

Database Mail Information

Select * from sysmail_mailitemsSelect * from sysmail_sentitems
Select * from sysmail_unsentitems
Select * from sysmail_logSelect * from sysmail_faileditems order by send_request_date desc

SELECT recipients,sent_status, sent_date FROM msdb.dbo.sysmail_allitems
WHERE recipients = 'sharafat_tex@yahoo.com'

How can get Database ID ?

Run below one of them

select DB_ID()

sp_helpDB

How can give permission in analysis service ?

 

How do find all sys admin role users

SELECT

SP1.name AS LoginName, SP2.name AS RoleName

FROM

sys.server_role_members SRM,

sys.server_principals SP1,

 

sys.server_principals SP2

 

WHERE

SRM.member_principal_id = SP1.principal_id

AND

SRM.role_principal_id = SP2.principal_id

How can find Trace file location ?

SELECTREVERSE(SUBSTRING(REVERSE(path),CHARINDEX('\',REVERSE(path)),LEN(path)))+'log.trc' 
FROM
sys.traces WHERE path LIKE '%\MSSQL\Log\log%.trc';
===========================================================

SELECT
* FROM ::fn_trace_getinfo(default)

EXEC
sp_trace_setstatus @traceid = 3, @status = 0; -- Stop/pause Trace

EXEC
sp_trace_setstatus @traceid = 3, @status = 2; -- Close trace and delete it from the

EXEC
sp_trace_setstatus @traceid = #, @status = 0; -- Stop/pause Trace

EXEC
sp_trace_setstatus @traceid = #, @status = 2; -- Close trace and delete it from the serv select

===============================


select


[Status] =

case
tr.[status]

when
1 THEN 'Running'

when
0 THEN 'Stopped'

end


,
[Default] =

case
tr.is_default

when
1 THEN 'System TRACE'

when
0 THEN 'User TRACE'

end


,
[login_name] = coalesce(se.login_name,se.login_name,'No reader spid')

,
[Trace Path] = coalesce(tr.[Path],tr.[Path],'OLE DB Client Side Trace')

from
sys.traces tr

left
join sys.dm_exec_sessions se on tr.reader_spid = se.session_id