SQLServerAgent could not be started (reason: Unable to connect to server '(local)'; SQLServerAgent cannot start).

If SQL Server agent not started and if you find below error in event viewer

Error Details

 Log Name             :         Application

Source                  :         SQLSERVERAGENT

Date                      :         8/8/2017 3:49:43 PM

Event ID               :         103

Task Category      :         Service Control

Level                    :         Error

Keywords             :      Classic

User                      :          N/A

Computer             :      VmSQLServer

Description           :

SQLServerAgent could not be started (reason: Unable to connect to server '(local)'; SQLServerAgent cannot start).

Solution 

Provide instance in  in "ServerHost" under

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.MSSQLSERVER\SQLServerAgent

Goto run
type regedit
expand HKEY_LOCAL_MACHINE --> SOFTWARE --> Microsoft --> Microsoft SQL Server
click on SQLServerAgent in Microsoft SQL Server
In right side pane double click on ServerHost and provide instance name under  value data

Change compatibility level

Using below statements you can change comparability level of  all user databases  with a single click. Comparability level is decided bases on sql server product level.

SQL Server	Comparability Level	SQL Version
SQL 2016	130
SQL 2014	120	12
SQL 2012	110	11
SQL 2008	100	10
SQL 2005	90	9
SQL 2000	80	8

use master;

go

DECLARE @SQLVer varchar(10);

-- select the compatibility level based in sql server version

select @SQLVer = CASE  SUBSTRING(convert(varchar,SERVERPROPERTY('productversion')),1,2)

when 12 then '120'

when 11 then '110'

when 10 then '100'

when 9. then '90'

when 8. then '80'

end

DECLARE UserDatabases_CTE_Cursor Cursor

FOR

-- filer user database names from sysdatabases

select name as DatabaseName from sys.sysdatabases where ([dbid] > 4)

OPEN UserDatabases_CTE_Cursor

DECLARE @dbName varchar(100);

DECLARE @compatQuery varchar(500);

Fetch NEXT FROM UserDatabases_CTE_Cursor INTO @dbName

While (@@FETCH_STATUS <> -1)

BEGIN

-- set database compatibility level

set @compatQuery =  'ALTER DATABASE [' + @dbName + '] SET COMPATIBILITY_LEVEL = ' + @SQLVer

-- Execute compatability script

EXEC (@compatQuery)

-- Get next database

Fetch NEXT FROM UserDatabases_CTE_Cursor INTO @dbName

END

CLOSE UserDatabases_CTE_Cursor

DEALLOCATE UserDatabases_CTE_Cursor

GO

Installing and configuring SQL Server 2012 Performance Dashboard Reports

Installing and configuring SQL Server 2012 Performance Dashboard Reports

The SQL Server 2012 Performance Dashboard Reports are Reporting Services report files designed to be used with the Custom Reports feature of SQL Server Management Studio. The reports allow a database administrator to quickly identify whether there is a current bottleneck on their system, and if a bottleneck is present, capture additional diagnostic data that may be necessary to resolve the problem.

The information captured in the reports is retrieved from SQL Server's dynamic management views. There is no additional tracing or data capture required, which means the information is always available and this is a very inexpensive means of monitoring your server.

Common performance problems that the dashboard reports may help to resolve include:

1. Ø  CPU bottlenecks (and what queries are consuming the most CPU)
2. Ø  IO bottlenecks (and what queries are performing the most IO)
3. Ø  Index recommendations generated by the query optimizer (missing indexes)
4. Ø  Blocking
5. Ø  Latch contention

To install this add-on please go through below link

http://www.microsoft.com/en-us/download/details.aspx?id=29063

Download & copy SQLServer2012_PerformanceDashboard.msi file into server where you want to install.

 

Double click on downloaded file to install SQL Server 2012 performance dashboard reports

 

Clink on run

Click on Next

 

Select I accept the terms in the license agreement  

And click on next

Enter your name and company. Click on Next button.

If you want change the default installation location click on Browse and provide appropriate location

Click on Next

Dash board report will install files in above location

Click on Install button

Click on finish button

To load reports – right click on server name and expand reports and click on Custom Reports

Select the .rdl file from below location shown as in below images

 

Click on Run button

It will give above error. You need to execute the setup.sql files located in installation directory.

Open the file in Query Analyser

 

Execute the script

 

After successful execution  please load the report by selecting .rdl file. It will show the report as below

 

 

Script to get the SQL Logins information

Login type descriptions

S = SQL Server user(SQL_USER)

U = Windows user(WINDOWS_USER)

G = Windows group(WINDOWS_GROUP)

A = Application role(APPLICATION_ROLE)

R = Database role(DATABASE_ROLE)

C = Certificate mapped(CERTIFICATE_MAPPED_USER)

K = Asymmetric key mapped(ASSYMETRIC_KEY_MAPPED_USER)

Using below script you can list all Login Accounts in a SQL Server

SELECT name AS Login_Name, type_desc AS Account_Type

FROM sys.server_principals 

WHERE TYPE IN ('U', 'S', 'G')

ORDER BY name, type_desc

Using below script you can list all  SQL Login Accounts (Not windows logins)  only in a SQL Server 

SELECT name

FROM sys.server_principals 

WHERE TYPE = 'S'

ORDER BY name, type_desc

Using below script you can list all Windows Login Accounts (Not SQL Logins)  only in a SQL Server 

SELECT name

FROM sys.server_principals 

WHERE TYPE = 'U'

Using below script you can list all Windows Group Login  (Not SQL Logins)  only in a SQL Server 

SELECT name

FROM sys.server_principals 

WHERE TYPE = 'G'

Table Space used and row count

Using below query you can find out the table space in a database and rows count of the table. 

Please change/add your tables list in WHERE condition  

SELECT T.NAME AS TableName, P.rows AS RowCounts, SUM(a.total_pages) * 8 AS TotalSpaceKB,  SUM(a.used_pages) * 8 AS UsedSpaceKB, (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB 

 FROM  sys.tables T

 INNER JOIN      

    sys.indexes I ON t.OBJECT_ID = i.object_id

 INNER JOIN 

    sys.partitions P ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id

 INNER JOIN 

    sys.allocation_units A ON p.partition_id = a.container_id

 LEFT OUTER JOIN 

    sys.schemas S ON t.schema_id = s.schema_id

 WHERE 

    T.NAME in ('Table1','Table2','Table3','Table4') 

    AND T.is_ms_shipped = 0

    AND I.OBJECT_ID > 255 

 GROUP BY 

    T.Name, S.Name, P.Rows

 ORDER BY 

    T.Name

 you can find using SSMS as shown in below. Right click on the database and click on the Reports --> select Standard Reports --> and select Disk Usage by Table. 

and results shown like below

Script to disable/enable all triggers (Database Level) in a database & Enable or Disable Server level triggers (SERVER LEVEL)

Use below script to disable or enable all triggers in MyDatabase  (Database level) 

To disable all triggers, you can use following statement

use MyDatabase

sp_msforeachtable 'ALTER TABLE ? DISABLE TRIGGER all'

To enable all triggers, you can use following statement

use MyDatabase

sp_msforeachtable 'ALTER TABLE ? ENABLE TRIGGER all'

The following example disables all DDL triggers that were created at the server scope. (Server Level Triggers) 

USE master

GO

DISABLE Trigger ALL ON ALL SERVER;

GO

USE master

GO

ENABLE Trigger ALL ON ALL SERVER;

GO

How to Find a SQL Server Database Object in entire sql server

Script to find the weather an object is exist in entire sql server or not, if yes in which database it is exit. Below is the  better why to find a SQL Server object, such as a table, a procedure, or a trigger, would be to query the sysobjects system table in each and every  local database. Using below query we are searching for UdfUserAccessLevel

Declare @SqlStmt nvarchar(500)

/* drop the temporary -tblTempDBObjects table if already exists which store all objects information */

If Object_Id('tempdb..#tblTempDBObjects') is Not Null

Drop table #tblTempDBObjects

/* create temporary tblTempDBObjects table in temp db*/

Create TABLE tempdb..#tblTempDBObjects (

dbName sysname,

objName varchar(250),

objtype char(2)

)

/*assign string value of sql statement to insert all objects to variable */

Select @SqlStmt = 'sp_msforeachdb ''Insert tempdb..#tblTempDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects'''

Exec sp_executesql @SqlStmt

/* searching for equired oj=bject in temptable --- UdfUserAccessLevel */

Select * From tempdb..#tblTempDBObjects Where objName like '%UdfUserAccessLevel%'

RETURN

Above statement will give below resultset and it contain database name, object and object type.

How to copy non-clustered indexes to subscriber in snapshot replication

In snapshot replication by default Non-cluster indexes are not copied to subscriber below is the work around for copying Non-cluster indexes to subscriber in Snapshot replication.

Step 1:

  - Login into the Publisher server

  -  Expand the Replication folder and Local Publications

  -  Right click on the publisher and select properties

Step 2:

                In Publication Properties window select Articles Tab à Select the Article Properties

               

Step 3:

  -  In Popup select Set Properties of All Table articles to copy all objects Non-Cluster indexes

  -  Select Set Properties of Highlighted table article to change the option to copy Non-cluster indexes of specific table

Step 4: 

                In Properties for All Tables Articles window change the below highlighted property from False to True.

               

In next run of snapshot will copy all noncluster indexes to subscriber.

Operating System Requirements to install SQL Server 2012

SQL Server 2012 edition	Operating system
SQL Server 2012 x64 Enterprise, Business Intelligence, and Web	Windows Server 2008 R2 SP1 Datacenter, Enterprise, Standard, and Web or above Windows Server 2008 SP2 Datacenter, Enterprise, Standard, and Web or above
SQL Server 2012 x86 Enterprise, Business Intelligence, and Web	Windows Server 2008 R2 SP1 Datacenter, Enterprise, Standard, and Web or above Windows Server 2008 (x64 and x86) SP2 Datacenter, Enterprise,Standard, and Web or above
SQL Server 2012 x64 Standard	Windows Server 2008 R2 SP1 Datacenter, Enterprise, Standard,Foundation, and Web or above Windows Server 2008 SP2 Datacenter, Enterprise, Standard, and Web or above Windows 7 SP1 x64 Ultimate, Enterprise, and Professional Windows Vista SP2 x64 Ultimate, Enterprise, and Business
SQL Server 2012 x86 Standard	Windows Server 2008 R2 SP1 Datacenter, Enterprise, Standard,Foundation, and Web or above Windows Server 2008 (x64 and x86) SP2 Datacenter, Enterprise,Standard, and Web or above Windows 7 SP1 (x64 and x86) Ultimate, Enterprise, and Professional Windows Vista SP2 (x64 and x86) Ultimate, Enterprise, and Business
SQL Server 2012 x64 Developer, Express, Express with Tools, and Express with Advanced Services	Windows Server 2008 R2 SP1 Datacenter, Enterprise, Standard,Foundation, and Web or above Windows Server 2008 (x64) SP2 Datacenter, Enterprise, Standard,and Web or above Windows 7 SP1 (x64) Ultimate, Enterprise, Professional, Home Premium, and Home Basic Windows Vista SP2 (x64)
SQL Server 2012 x86 Developer, Express, Express with Tools, and Express with Advanced Services	Windows Server 2008 R2 SP1 Datacenter, Enterprise, Standard,Foundation, and Web or above Windows Server 2008 (x64 and x86) SP2 Datacenter, Enterprise,Standard, and Web or above Windows 7 SP1 (x64 and x86) Ultimate, Enterprise, Professional, Home Premium, and Home Basic Windows Vista SP2 (x64 and x86) Ultimate, Enterprise, Business, Home Premium, and Home Basic