Monday, November 23, 2015

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

Tuesday, November 17, 2015

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

Friday, November 6, 2015

Change password in remote session


How to change the password in a Remote Session when you connect the machine through Citrix or VDI 

Goto Remote session 

Press

CTRL + ALT +END 


you can find change password option.  

Click on change Password, 

Provide 
                  user name, current password, New password, confirm password  

And press symbol -->  in confirm password text box 

Wednesday, November 4, 2015

Get SQL Server Service Account using T-SQL



Identify the SQL Server Service Account using T-SQL 
You can find the service account information in Services 
Go to Start --> Run --> Services.msc 
Right click on SQL Server Services and go to Properties. 
The account information is available under Log On Tab

And also service account information stored in Windows Registry, using xp_instance_regread extended stored procedure we can read windows registry.  Registry entries are like below 
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLServer HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SQLServerAgent
Using below script also we can fetch the information   
DECLARE       @DBEngineLogin       VARCHAR(100)
DECLARE       @AgentLogin          VARCHAR(100)

EXECUTE       master.dbo.xp_instance_regread
              @rootkey      = N'KEY_LOCAL_MACHINE',
              @key          = N'SYSTEM\CurrentControlSet\Services\MSSQLServer',
              @value_name   = N'ObjectName',
              @value        = @DBEngineLogin OUTPUT

EXECUTE       master.dbo.xp_instance_regread
              @rootkey      = N'HKEY_LOCAL_MACHINE',
              @key          = N'SYSTEM\CurrentControlSet\Services\SQLServerAgent',
              @value_name   = N'ObjectName',
              @value        = @AgentLogin OUTPUT

SELECT        [DBEngineLogin] = @DBEngineLogin, [AgentLogin] = @AgentLogin
GO

Output 
in above scenario both Agent and SQL Services are running as Local System 
Using DMVS also we can fetch the information   

SELECT
      servicename
      , service_account
      , startup_type_desc
      , status_desc
      , is_clustered
FROM   sys.dm_server_services


Output :

in above scenario SQL Services is running as NT Service and Agent is running with Domain Account. 


Tuesday, October 27, 2015

Error 1068: The dependency service or group failed to start - "World Wide Web Publishing Service service"



Error : Could not start the World Wide Web Publishing Service service on the local computer.
Error 1068: The dependency service or group failed to start






Resolution : 

Step 1:   Goto Run --> services.msc to open services 

Step 2: Right click on World Wide Web Publishing Services goto properties



Step 3:  goto Dependencies tab and check what are the dependencies. 



Step 4: Check the dependencies, if any service is disabled/stopped. Enable and start the services. 






Step 5:  Once all the dependent services are started. start "World Wide Web Publishing Service" 


Tuesday, October 20, 2015

Generate Script for all Non-Cluster Indexes in a database

you can't generate script for all Non-cluster indexes (Script for only Non-cluster indexes) by right on database --> tasks --> Generate Scripts. It will generate scripts including table schema. To generate only non-clusterindexes use below script. 



using  below script you can generate script for all Non Cluster indexes in a database 




declare @EmptyString varchar(1)
select @EmptyString = ''

-- 35 is the lenght of the name field of the master.dbo.spt_values table
declare @IgnoreDuplicateKeys varchar(35),
    @Unique varchar(35),
    @IgnoreDuplicateRows varchar(35),
    @Clustered varchar(35),
    @Hypotethical varchar(35),
    @Statistics varchar(35),
    @PrimaryKey varchar(35),
    @UniqueKey varchar(35),
    @AutoCreate varchar(35),
    @StatsNoRecompute varchar(35)

select @IgnoreDuplicateKeys = name from master.dbo.spt_values 
    where type = 'I' and number = 1 --ignore duplicate keys
select @Unique = name from master.dbo.spt_values 
    where type = 'I' and number = 2 --unique
select @IgnoreDuplicateRows = name from master.dbo.spt_values 
    where type = 'I' and number = 4 --ignore duplicate rows
select @Clustered = name from master.dbo.spt_values 
    where type = 'I' and number = 16 --clustered
select @Hypotethical = name from master.dbo.spt_values 
    where type = 'I' and number = 32 --hypotethical
select @Statistics = name from master.dbo.spt_values 
    where type = 'I' and number = 64 --statistics
select @PrimaryKey = name from master.dbo.spt_values 
    where type = 'I' and number = 2048 --primary key
select @UniqueKey = name from master.dbo.spt_values 
    where type = 'I' and number = 4096 --unique key
select @AutoCreate = name from master.dbo.spt_values 
    where type = 'I' and number = 8388608 --auto create
select @StatsNoRecompute = name from master.dbo.spt_values 
    where type = 'I' and number = 16777216 --stats no recompute
select 'CREATE NONCLUSTERED INDEX ['+i.name+'] ON [dbo].['+o.name+'] ('+
  case when index_col(o.name,indid, 1) IS NULL then '' else '[' + index_col(o.name,indid, 1) + '] ASC'end +''+
  case when index_col(o.name,indid, 2)IS NULL then ''  else +',['+index_col(o.name,indid, 2)+ '] ASC'end+''+
  case when index_col(o.name,indid, 3)IS NULL then '' else +',['+index_col(o.name,indid, 3)+ '] ASC'end+''+
  case when index_col(o.name,indid, 4)IS NULL then '' else +',['+index_col(o.name,indid, 4)+ '] ASC'end+''+
  case when index_col(o.name,indid, 5)IS NULL then '' else +',['+index_col(o.name,indid, 5)+ '] ASC'end+''+
  case when index_col(o.name,indid, 6)IS NULL then '' else +',['+index_col(o.name,indid, 6)+ '] ASC'end+''+
  case when index_col(o.name,indid, 7)IS NULL then '' else +',['+index_col(o.name,indid, 7)+ '] ASC'end+''+
  case when index_col(o.name,indid, 8)IS NULL then '' else +',['+index_col(o.name,indid, 8)+ '] ASC'end+''+
  case when index_col(o.name,indid, 9)IS NULL then '' else +',['+index_col(o.name,indid, 9)+ '] ASC'end+''+
  case when index_col(o.name,indid, 10)IS NULL then '' else +',['+index_col(o.name,indid, 10)+ '] ASC'end+''+
  case when index_col(o.name,indid, 11)IS NULL then '' else +',['+index_col(o.name,indid, 11)+ '] ASC'end+''+
  case when index_col(o.name,indid, 12)IS NULL then '' else +',['+index_col(o.name,indid, 12)+ '] ASC'end+''+
  case when index_col(o.name,indid, 13)IS NULL then '' else +',['+index_col(o.name,indid, 13)+ '] ASC'end+''+
  case when index_col(o.name,indid, 14)IS NULL then '' else +',['+index_col(o.name,indid, 14)+ '] ASC'end+''+
  case when index_col(o.name,indid, 15)IS NULL then '' else +',['+index_col(o.name,indid, 15)+ '] ASC'end+''+
  case when index_col(o.name,indid, 16)IS NULL then '' else +',['+index_col(o.name,indid, 16)+ '] ASC'end+''+') ON [Primary]'  
from sysindexes i, sysobjects o
where i.id = o.id and
      indid >1 and indid < 255 --all the clustered (=1), non clusterd (>1 and <251), and text or image (=255) 
      and o.type = 'U' --user table
      --ignore the indexes for the autostat
      and (i.status & 64) = 0 --index with duplicates
      and (i.status & 8388608) = 0 --auto created index
      and (i.status & 16777216)= 0 --stats no recompute
      order by o.name


Monday, October 12, 2015

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
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

 



 

Sunday, June 14, 2015

NetFx3 error while installing SQL Server 2012 on windows 2012 server



Problem description

TITLE: Microsoft SQL Server 2012 Service Pack 1 Setup

The following error has occurred:

Error while enabling Windows feature : NetFx3, Error Code : -2146498298 , Please try enabling Windows feature : NetFx3 from Windows management tools and then run setup again. For more information on how to enable Windows features , see http://go.microsoft.com/fwlink/?linkid=227143







Solution 

Goto server manager
 --> Click on manager right side corner of window
--> Click Add Roles and Features



Blow Add Roles and features wizard window appears 


click on next


in the next screen click on Role-based or feature-based installation

and click on the next


in the Server Selection Screen select the first radio button i.e. Select a Server from the Server Pool 

In the server pool window you can see the server name 


On Server Roles page simply click on next

in Features page wizard clicked on the check box next to ".NET Framework 3.5 Features", and then clicked on the Next as shown in below image


and in final wizard click on install to add features



and installation progress as below 





Thanks & Regards
Venkat Sangu

Thursday, May 21, 2015

Install SQL Server 2012 in Stand Alone Machine


Requirements for Installing SQL Server 2012:

1.       Operating system Requirement :
You can install in below operating systems
Windows Server 2012,
Windows 8,
Windows server 2008 R2 SP1,
Windows 7 SP1.
You can also install in below operating systems but you require to install update http://support2.microsoft.com/?kbid=956250 on below operating system before initiating the installation.
Windows Vista SP2
Windows Server 2008 SP2
2.       NET Framework
.NET 4.0 is a requirement for SQL Server 2012. SQL Server installs .NET 4.0 during the feature installation step.
You must enable or install.NET 3.5 SP1, If you select to install Database Engine, Reporting Services, Master Data Services, Data Quality Services, Replication, SQL Server Management Studio on Windows Vista SP2 or Windows Server 2008 SP2 operating systems. OS wont have .NET 3.5 SP1 Framework 
3.       Windows Powershell
Powershell 2.0 is mandatory to install SQL Server 2012. If  SQL Server setup reports Powershell 2.0 is not present, you can install or enable it.
4.       Internet Explorer 7 or a later version.
5.       Hard Disk : SQL Server 2012 requires minimum of 6 GB.
6.       Memory
 Minimum :  
<![if !supportLists]>1.       <![endif]>Express : 512 MB
<![if !supportLists]>2.       <![endif]>Other  : 1 GB
       Recommended
<![if !supportLists]>                                                         i.            <![endif]>Express : 1 GB
<![if !supportLists]>                                                       ii.            <![endif]>Other  : 4 GB


Installation Steps:
Go to setup file location  click on setup.exe



Next you will see the SQL Server Installation Center  page. In Left side pane select Installation Tab


In installation page, right side pane you will found 4 option 
               
Option 1 : New SQL Server stand-alone installation or add features to existing installation 
                                Using this option you can install Stand-alone server  or add SQL Server features to an existing installation
Option 2 : New SQL Server failover cluster installation
                                Using this option you can install a SQL Server in Failover Cluster
Option 3 : Add Node to the SQL Server failover cluster
                                This option is used to add a node to an existing failover cluster
Option 4 : Upgrade from SQL Server 2005, SQL Server 2008 or SQL Server 2008 R2
                                Using this option we can upgrade the existing SQL Server 2005, SQL Server 2008, or SQL Server 2008 R2 to SQL Server 2012

Now we are performing SQL Server stand-alone installation so click on Option 1.




It will initiate the stand-alone installation process of SQL Server 2012

                               

Next screen displays you Setup Support Rules screen, click on  button to see all the rules information.

Click on Ok


Above screen will provide you the available latest updates.


Click on the Next.


In next screen you need to select weather is it new installation are adding features to an existing installed instance.

Select Perform a new installation of SQL Server 2012 radio button for fresh installation


In next screen need to select weather is it free edition or need to provide product key and click on Next


Next  is the license  terms screen. You need to accept the license terms and click on Next.

l

Select All features with defaults to install all features with default values and select Next


Select the what ever you want install in the instances. If this is the first installation you can select what ever shared features you want. If it is another instance already installed shared features are grayed out. Click on Next