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