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

xp_cmdshell help; The configuration option 'xp_cmdshell' does not exist, or it may be an advanced option.

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1

SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.

Execute below statements to enable "xp_cmdshell" and 

EXEC sp_configure 'xp_cmdshell', 1  

GO  

RECONFIGURE  

GO

After executing above statements if you are facing below error

Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51

The configuration option 'xp_cmdshell' does not exist, or it may be an advanced option.

Try below statements to enable advanced options  

EXEC sp_configure 'show advanced options', 1;  

GO  

RECONFIGURE;  

GO  

EXEC sp_configure 'xp_cmdshell', 1;  

GO  

RECONFIGURE;  

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

 

 

How to grant access on backup shared in PDW

How to grant access on backup shared in PDW

To backup the database we have to assign access permissions to a windows account that has write access to a file share on a target server.

sp_pdw_add_network_credentials '10.10.10.10','corp\adAcctLogin', 'PassWord'

To remove access to shared path  

sp_pdw_add_network_credentials '10.10.10.10','corp\adAcctLogin', 'PassWord'

To check who are all having permissions 

select * from sys.dm_pdw_network_credentials

What is Trace Flag? Enable / Disabling Trace Flag

Trace flag is a directive used to " set specific server characteristics or switch off a perticular behavious" -- is a setting that in some way or another alter the behaviour of various SQL Server functions

Trace flags are used to generate internal information regarding SQL Server's activities, primarily so that Microsoft developers can troubleshoot their code.

Trace flags are enbled in SESSION or GLOBAL levels

How to check whcih trace flags are enabled in the server globally

DBCC  TRACESTATUS(-1) 

Above command will display the status of all trace flags that are currently enabled globally.

How to check whcih trace flags are enabled in the server for current session

DBCC  TRACESTATUS()

Above command will display the list of all trace flags that are enabled for the current session 

You can enable/implement Trace Flags in several ways

• using SQL Server Configuration Manager
• using Registry Editor
• using Query Analyser

Implementing Trace Flag using SQL Configuration Manager 

This is also know as Enabling Trace Flag using –T Startup Option

Adding trace flag in Startup Parameters 

-T startup option to specify that the trace flag be set on during startup. Saparate each trace flag by semi-colon(;). -T startup option enables a trace flag globally 

You can't enable a session level trace flag  by using start up option

  

Implementing Trace Flags using Registry (REGEDIT.MSC)

This is not a recomended method. please avoid using it in production box.

• Goto Run and type regedit.msc and press endter
• Goto below location in registry
• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SQL2008\MSSQLServer\Parameters
• Add the trace flag by right click on the Parameters folder and click on New and then String Value
• Provide the name as SQLArg3 and then right click on the key value to modify the value.
• Now provide the value as -T1222 

Implementing Trace Flags using Query Analyser 

Using DBCC TRACEON command you can enable the trace flags.All trace flags can't be enabled using DBCC TRACEON e.g. DBCC TRACEON (835), this can be enabled during the server startup only.

You can enable Session Scope or Global Scope using this command

Session Scope : Using below command we can enable session level trace flag

DBCC TRACEON(1222)

Global Scope :  Using below command we can enable global level trace flag

DBCC TRACEON(1222,-1)

Disabling Trace flag

Remove from start-up parameters 

• Go to SQL Server configuration manager and to sql server properties 
• Go to Advanced Tab and click on drop box of start-up parameter and remove the parameter -T1222
• Restart SQL Server services 

Remove from Registry 

• Go to regedit.exe and 
• Browse HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SQL2008\MSSQLServer\Parameters
• Remove the string value which has -T1222
• Restart the SQL Server services 

Remove using Query Analyser 

use below command to turn off session level trace flag

DBCC TRACEOFF(1222)

use below command to turn off global level trace flag

DBCC TRACEOFF(1222,-1)

Caution : 

Do not use trace flags in production environment without testing it on non production environments, trace flags should be used under the guidance of Microsoft SQL Server support.

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

SQL Server Latest service packs

Product Version	Latest Service Pack
SQL Server 2014	n/a
SQL Server 2012	SP2
SQL Server 2008 R2	SP2
SQL Server 2008	SP3
SQL Server 2005	SP4
SQL Server 2000	SP4

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.

Validate all views in the database

There is an sp that validates the metadata and the   same sp we can use to validate

set nocount on

select ' print ''' +name +'''

exec sp_refreshview ['+name+']' + CHAR(10)+ '

go '

from sys.views

Execute  the above script it will give script to run sp_refreshview on all views. Run the scripts generated using above sql statement I will give error link below screen

DBCC CHECKIDENT

-- Create Table Script to test CHECKIDENT

CREATE TABLE DBCC_ReSeed

( ID INT IDENTITY(1,1) NOT NULL,

DateAdded DATETIME NOT NULL )

-- Inserting values to DBCC_ReSeed table

INSERT INTO DBCC_ReSeed(DateAdded) VALUES(GETDATE())

INSERT INTO DBCC_ReSeed(DateAdded) VALUES(GETDATE())

INSERT INTO DBCC_ReSeed(DateAdded) VALUES(GETDATE())

INSERT INTO DBCC_ReSeed(DateAdded) VALUES(GETDATE())

INSERT INTO DBCC_ReSeed(DateAdded) VALUES(GETDATE())

SELECT * FROM DBCC_ReSeed

--- Check the current value which produces the below output… 

DBCC CHECKIDENT ('DBCC_ReSeed', NORESEED)

---  Output like :  Checking identity information: current identity value '5', current column value '5'.

--- Now reset the identity value to 20 so that the next time when we insert data into DBCC_RdSeed table , the value will be 21…

DBCC CHECKIDENT('DBCC_ReSeed', RESEED, 20)

----- Output like :   Checking identity information: current identity value '5', current column value '20'

--  Add another row and check identity value. The row inserted will have a value of 21… 

INSERT INTO DBCC_ReSeed (DateAdded)  VALUES(GETDATE());

SELECT * FROM DBCC_ReSeed

-- Delete data in table and reset to start from 1

DELETE FROM DBCC_ReSeed

DBCC CHECKIDENT('DBCC_ReSeed')

-- After deleting data identity value didn’t reset and the value will be remain same  

---  Output like :  Checking identity information: current identity value '20', current column value '20'.

DBCC CHECKIDENT('DBCC_ReSeed', RESEED, -1)

---  Output like :  Checking identity information: current identity value '21', current column value '-1'.

INSERT INTO DBCC_ReSeed (DateAdded)  VALUES(GETDATE());

INSERT INTO DBCC_ReSeed (DateAdded)  VALUES(GETDATE());

SELECT * FROM DBCC_ReSeed

Create SQL Login

<![if !supportLists]>1.       <![endif]>CREATE a login for SQL Server  for windows authentication

      CREATE LOGIN [domainName\loginName] FROM WINDOWS;    

                                                     

<![if !supportLists]>2.       <![endif]>CREATE a login with password –( SQL Login) this will work from SQL 2000 onwards

CREATE LOGIN VenkatSangu WITH PASSWORD = 'password'

<![if !supportLists]>3.  <![endif]> CREATE a login with password and password must change when the user first time connecting to SQL Server  this will not support in older version i.e SQL Server 2000/2005

CREATE LOGIN VenkatSangu WITH PASSWORD='pwd@123pdw' MUST_CHANGE, DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON

     

To use MUST_CHANGE keyword must passwords must meet the following guidelines

<![if !supportLists]>·         <![endif]>The password does not contain the account name of the user.

<![if !supportLists]>·         <![endif]>The password is at least eight characters long.

<![if !supportLists]>·         <![endif]>The password contains characters from three of the following four categories:

<![if !supportLists]>·         <![endif]>Latin uppercase letters (A through Z)

<![if !supportLists]>·         <![endif]>Latin lowercase letters (a through z)

<![if !supportLists]>·         <![endif]>Base 10 digits (0 through 9)

<![if !supportLists]>·         <![endif]>Non-alphanumeric characters such as: exclamation point (!), dollar sign ($), number sign (#), or percent (%).

Thanks

Venkat Sangu

Shrink Database Log file in SQL Server

 

Method 1 : This script will shrink the all log files of specific database by  change the recovery modle to simple  and again it rever back the recovery model

 

DECLARE @tbl TABLE

(

      ID INT IDENTITY(1,1),

      DBNAME NVARCHAR(1000),

      [FileName] NVARCHAR(1000)

)

  

INSERT INTO @tbl

SELECT DB_NAME(DbID),st.NAme from sys.sysaltfiles  ST INNER JOIN Sys.databases SB on  ST.dbid = sb.database_id where sb.name = 'TEST2008'  AND FileName LIKE '%ldf' and sb.state = 0

 

DECLARE @MinID INT , @MaxID INT,@DBName NVARCHAR(1000),@FileName NVARCHAR(1000),@RecoveryModel NVARCHAR(1000),@SQL NVARCHAR(MAX)

 

SELECT @MinID = MIN(ID),@MaxID = MAX(ID) FROM @tbl

 

 

WHILE(@MinID <=@MaxID)

BEGIN

      SELECT @DBName = DBNAME,@FileName=[FileName] FROM @tbl where ID = @MinID

 

      SELECT      @RecoveryModel = recovery_model_desc FROM sys.databases where name = @DBName

 

            SELECT      @SQL= N'USE ['+ @DBName+']'+CHAR(10)+CHAR(10)

                  +CASE WHEN @RecoveryModel <> N'SIMPLE' THEN N'ALTER DATABASE ['+@DBName+'] SET RECOVERY SIMPLE WITH NO_WAIT'ELSE N''END+CHAR(10)+

                  +N'DBCC SHRINKFILE('+@FileName+',1)'+CHAR(10)

                  +CASE WHEN @RecoveryModel <> N'SIMPLE' THEN N'ALTER DATABASE ['+@DBName+'] SET RECOVERY '+@RecoveryModel+N' WITH NO_WAIT'+CHAR(10) ELSE N'' END

                  +CHAR(10)

      PRINT @SQL

      EXEC SP_EXECUTESQL @SQL

     

      SELECT @MinID = @MinID +1

END

 

Method 2: This script will shrink the all databases log files, depends on the size of log file by  change the recovery modle to simple  and again it rever back the recovery model

 

DECLARE @tbl TABLE

(

      ID INT IDENTITY(1,1),

      DBNAME NVARCHAR(1000),

      [FileName] NVARCHAR(1000)

)

INSERT INTO @tbl

SELECT DB_NAME(DbID),st.NAme from sys.sysaltfiles  ST INNER JOIN Sys.databases SB on  ST.dbid = sb.database_id where Size > 100000 AND FileName LIKE '%ldf' and sb.state = 0

 

DECLARE @MinID INT , @MaxID INT,@DBName NVARCHAR(1000),@FileName NVARCHAR(1000),@RecoveryModel NVARCHAR(1000),@SQL NVARCHAR(MAX)

 

SELECT @MinID = MIN(ID),@MaxID = MAX(ID) FROM @tbl

 

 

WHILE(@MinID <=@MaxID)

BEGIN

      SELECT @DBName = DBNAME,@FileName=[FileName] FROM @tbl where ID = @MinID

 

      SELECT      @RecoveryModel = recovery_model_desc FROM sys.databases where name = @DBName

 

            SELECT      @SQL= N'USE ['+ @DBName+']'+CHAR(10)+CHAR(10)

                  +CASE WHEN @RecoveryModel <> N'SIMPLE' THEN N'ALTER DATABASE ['+@DBName+'] SET RECOVERY SIMPLE WITH NO_WAIT'ELSE N''END+CHAR(10)+

                  +N'DBCC SHRINKFILE('+@FileName+',1)'+CHAR(10)

                  +CASE WHEN @RecoveryModel <> N'SIMPLE' THEN N'ALTER DATABASE ['+@DBName+'] SET RECOVERY '+@RecoveryModel+N' WITH NO_WAIT'+CHAR(10) ELSE N'' END

                  +CHAR(10)

      PRINT @SQL

      EXEC SP_EXECUTESQL @SQL

     

      SELECT @MinID = @MinID +1

END