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