Tuesday 25 March 2014

How to change Schema from DBO to user created schema for all objects

Move all database objects from DBO schema to user created (PRD in below queries) schema.

Recently i got a request to change the dbo schema to user created schema PRD.

So please find the below steps to do the changes.

Ex: PRD is the schema name
Please take database backup before doing the schema changes.
Step1:
Please check if user defined Schema already exists:
select*fromsys.schemas
ifuser defined schema doesn’t exist in your database.
CREATESCHEMA [PRD] AUTHORIZATION [DBUser]
 
·         PRD is user choice schema name
 
·         DBuser is nothing but login who will be default owner.
 
 
Step2:
Query to check no of objects having schema  PRD or DBO.
selectcount(1)as dbo_count
fromsys.objects
whereschema_id=schema_id('dbo')
andtypein('U','V','P','FN')
selectcount(1)as [PRD]
fromsys.objects
whereschema_id=schema_id('[PRD]')
andtypein('U','V','P','FN')
 
Step3:
Please make sure DB user has default schema PRD
ALTERUSER [DBUser]
WITHDEFAULT_SCHEMA= [PRD]
Step4:
If you are moving the all objects havingDBO schema  toPRD schema.
Script1:
Below Script is used to change schema for only objects
declare @theSchema sysname
declare @oneObj sysname, @aSQL nvarchar(1000)
set @theSchema =quotename('PRD')
-- migration of user-defined types in [dbo]
declare objCur cursorfor
selectquotename([name])
fromsys.objects
where (schema_id=schema_id('dbo')orschema_id=schema_id(@theSchema))
andtypein('R','D')-- Rules and Defaults
open objCur
fetchfrom objCur into @oneObj
while@@fetch_status=0 begin
set @aSQL ='alter schema '+@theSchema+' transfer [dbo].'+@oneObj
print @aSQL
execsp_executeSQL@aSQL
fetchnextfrom objCur into @oneObj
end
close objCur
deallocate objCur

declare @oneType varchar(50)
declare typCur cursorfor
select name fromsys.types
where is_user_defined = 1
andschema_id<>schema_id(@theSchema)
print'Beginning migration'
open typCur
fetchfrom typCur into @oneType
while@@fetch_status= 0 begin
exec('alter schema '+ @theSchema +' transfer TYPE::'+@oneType)
print'Moved '+@oneType
fetchnextfrom typCur into @oneType
end
close typCur
deallocate typCur
-- confirm by examining the schema_id field
select*fromsys.typeswhere is_user_defined = 1

Script2:
Below Script is used to change schema for only objects

declare @theSchema sysname
declare @oneObj sysname, @aSQL nvarchar(1000)
set @theSchema =quotename('PRM')
-- migration of user-defined objects in [dbo]
declare objCur cursorfor
selectquotename([name])
fromsys.objects
where (schema_id=schema_id('dbo')orschema_id=schema_id(@theSchema))
andtypein('U','V','P','FN')
open objCur
fetchfrom objCur into @oneObj
while@@fetch_status=0 begin
set @aSQL ='alter schema '+@theSchema+' transfer [dbo].'+@oneObj
print @aSQL
execsp_executeSQL@aSQL
fetchnextfrom objCur into @oneObj
end
close objCur
deallocate objCur
-- confirm by looking at the former and current schemas
select*fromsys.objects
whereschema_id=schema_id(@theSchema)
orschema_id=schema_id('dbo')
orderbyschema_id

To verify if still any objects are not moved from DBO schema to user PRD schema, please use the below query.
select*fromsys.objects
whereschema_id=schema_id(quotename('PRM'))
orschema_id=schema_id('dbo')
orderbyschema_id

Please use the below query to move the objects that have not moved by above query.
alterschemaPRDtransfer [dbo].objectname




Sunday 23 March 2014

Importance of DAC on SQL Server

Importance of DAC in SQL server .especially when SQL server is not responding or unable to connect to SQL server and SQL services are running...

DAC  (Dedicated Admin Connection) help us to connect and run basic troubleshooting queries in cases of serious performance problems.

Only members of the SQL Server sysadmin role can connect using the DAC.
Normally DAC allows database administrators to connect to a SQL Server Instance and to execute T-SQL commands to troubleshoot and fix issues rather than rebooting the SQL Server which could lead to database corruption or other problems. By default, the remote Dedicated Administrator Connection feature is disabled in SQL Server 2005 and later versions. It’s a good practice to enable the DAC feature once the SQL Server 2005 or SQL Server 2008 is installed on every instance as this will help you troubleshoot issues when regular connections are not responding. However, only one dedicated administrator connection is allowed at a time on SQL Server 2005 and later versions. 

By default DAC connection uses Port No..

By default, the DAC only listens on the loop-back IP address (127.0.0.1), port 1434. If TCP port 1434 is not available, a TCP port is dynamically assigned when the Database Engine starts up. When more than one instance of SQL Server is installed on a computer, check the error log for the TCP port number.
How to enable Remote DAC:

Using GUI:

Description: Description: DAC tree

This will open up View Facets window as shown in the snippet below. Here you need to select Surface Area Configuration facet as highlighted and then select the option as “True” for RemoteDacEnabled.
Description: Description: DAC settings

Enable Remote DAC using CMD:

Use master
GO
/* 0 = Allow Local Connection, 1 = Allow Remote Connections*/
sp_configure 'remote admin connections', 1
GO
RECONFIGURE
GO
Using DAC with SQLCMD

You need to be a member of the sysadmin fixed server role in order to use this feature. The “-A” switch is used to specify that the user is trying to connect to SQL Server using the Dedicated Administrator Connection.
The syntax to use DAC is mentioned below.
SQLCMD –S [SQL Server Name] –U [User Name] –P [Password] –A

Using DAC with GUI:

You need to specify “ADMIN:” before the SQL Server Instance name when trying to connect to an SQL Server Instance to using DAC feature as shown in the snippet below.
Description: Description: DAC login

Example queries to check the performance of the server and session being used and start troubleshooting and fix issues from SQL end.

-- Locking Information
SELECT * FROM sys.dm_tran_locks
GO
-- Cache Status
SELECT * FROM sys.dm_os_memory_cache_counters
GO
-- Active Sessions
SELECT * FROM sys.dm_exec_sessions
GO
-- Requests Status
SELECT * FROM sys.dm_exec_requests
GO


Please find the restrictions and more details in the below link.


My Friend added some more point to below info:

Just to add few things….

1>     DAC is always enabled by default  for the any instance of the SQL server, it’s just not enable for remote connection.
Means you can connect to any instance of SQL server using DAC if you are able to connect to the physical windows server (Using RDP).

Description: cid:image001.png@01CEA7D8.9AD92660
For local server the command for DAC is SQLCMD –A or SQLCMD  -S ServerName\InstanceName -A

If you want to connect to any remote server then  'remote admin connections' value should be 1 in SP_Configure.

And you can connect using SQLCMD –S ServerName –A

Note: No Need to provide any credential as long as your windows account is part of sysadmin in the instance, as SQLCMD uses Integrated authentication and DAC connection is only possible with sysadmin account.

2>     DAC is Mainly used when there is no any means to connect to the server (when SQL server is refusing all other connection). Because for all other cases you can troubleshoot using your normal connection.

3>     Please use DAC only through command prompt, so that it does not waste any resources (specially RAM) that is the whole point of giving the separate connection to the instance.
Since there is special thread running for this connection and the thread uses very less resource.

Note: Don’t use any resource intensive query like “ select * ” while using the DAC, it may throw errors depending on availability of the physical resources.   
             Only one thread assigned for DAC hence only one connection is allowed at any point of time in any instance, please close the DAC connection as soon as you finished with your work.

Let me know if this helps…….

Query to find page locks disabled for all indexes

Generally, reorganize job fails due to page locks are not enabled,  even though if we enable for specific  index, there may be chance of page lock disabled for other indexes, so we need to make sure all page locks should be enabled if we run reorganize indexes.

So please find the below query to find the list of indexes that page locks are disabled.

SELECT OBJECT_SCHEMA_NAME(object_id) AS object_schema,
       OBJECT_NAME(object_id)        AS object_name,
       name                          AS index_name
FROM   sys.indexes
WHERE  OBJECT_SCHEMA_NAME(object_id)<>'sys' and allow_page_locks = 1
 
So going furthur make sure enable page locks for all indexes, if you are planning for weekly reorganize.
 
 

Tuesday 18 March 2014

Outdated Stats Report for all databases in single report

Here is the Script to generate a report of all Outdated Stats in a server and send the report Via mail to your inbox.

You can automate this in SQL job just by adding Reciepients and DB Mail profiles as parameter

SP will send details Report of Outdates Stats to your Mail, You can Schdeule this job or run on demand to fetch the data.

Based on the report you can analyze the data on weekly basis and set weekly Reindex and Update Stats plans(Column Stats and Index Stats)
__________________________________________________________________________


Step1:

Create an SP using Script 1.

Description: SP will fetch the data (Outdates Stats info)from all the databases and send mail to reciepients  

Step2:

Sample Query to get Outdatad Stats report

exec [USP_OutdatedStat_Report] 'hanuman.devineni@gmail.com','DBMailProfile'

--Parameter 1 : Recipient to which you want to send mail

--Parameter 2 : DB Mail Profile Name


Script 1 to create the Outdated Stats Report.

/****** Object:  StoredProcedure [dbo].[USP_OutdatedStat_Report]    Script Date: 03/18/2014 08:33:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[USP_OutdatedStat_Report_V1]
 @To varchar(100),
 @profile varchar(50)
 AS
/***************************************************************************
-- PROGRAM: OutdatedStatistics Index report
-- CREATED: 02/04/2013   AUTHOR:  Hanuman D
-- EXAMPLE: EXEC the script to get the Outdated Stats{object name}
-- DESC   : Script will  get the Outdated Stats report mail to Reciepients based on requirement
--              

***************************************************************************************************/
Set Nocount On
/*-- Query to Check for the temporary table if  exists drops and recreates */
If exists (select * from tempdb.sys.all_objects where name like '#Temp%' )
drop table #Temp
create table #Temp
(DatabaseName varchar(200),ObjectName varchar(200),indexName varchar(200),StatsUpdated Datetime,Daysold int)

/*-- Insert records of Outdated Stats indexes from each user database to temperorary table */
insert into #Temp (DatabaseName,ObjectName,indexName,StatsUpdated,Daysold)
exec master.sys.sp_MSforeachdb ' USE [?]
 IF DB_ID(''?'') > 4
SELECT db_name() as DatabaseName,OBJECT_NAME(A.object_id) AS Object_Name, A.name AS index_name, STATS_DATE
(A.OBJECT_ID, index_id)AS StatsUpdated ,
DATEDIFF(d,STATS_DATE(A.OBJECT_ID, index_id),getdate()) DaysOld
FROM sys.indexes A
INNER JOIN sys.tables B ON A.object_id = B.object_id
WHERE A.name IS NOT NULL
and b.NAME not like ''x%''
and b.NAME not like ''at%''
and b.NAME not like ''bj%''
and b.NAME not like ''ej%''
and b.NAME not like ''jd%''
and b.NAME not like ''%test%''
and b.NAME not like ''%bak%''
and b.NAME not like ''%_201%''
and (DATEDIFF(d,STATS_DATE(A.OBJECT_ID, index_id),getdate())>3)
ORDER BY StatsUpdated DESC
 '

/*Query to delete the data for readonly databases*/
delete from #Temp where DatabaseName in (select name from sys.databases where is_read_only <> 0)
/*-- Query to Capture information and send mail in HTML format*/

DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
DECLARE @Subject NVARCHAR(300)
DECLARE @rowcount int
SET @Subject='Outdated Stats Report for Server '+(select @@servername)+''
SELECT @rowcount = COUNT(*) FROM #Temp
IF (@rowcount  > 0)
BEGIN
SET @xml = CAST(( SELECT [DatabaseName] AS 'td','',[ObjectName] AS 'td','',
       [indexName] AS 'td','',[StatsUpdated] AS 'td','',[Daysold] AS 'td'
FROM  #Temp Where databasename not like '%FP%' and databasename not like '%reportserver%' and databasename not like
'%_test%' and databasename <>'_Testdata' and databasename <>'profiler' and databasename not like '%FYE%' ORDER BY
DatabaseName
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

SET @body =
N'<H3>Oudated Stats on MMI server '+ (select td = (select @@servername))+'</H3>'+
N'<html><body><table border = 1>
<tr>
<th> DatabaseName </th> <th> ObjectName </th> <th> indexName</th><th> StatsUpdated</th><th>Daysold</th></tr>'   

SET @body = @body + @xml +'</table></body></html>'

EXEC msdb.dbo.sp_send_dbmail
@profile_name = @profile, -- replace with your SQL Database Mail Profile
@body = @body,
@body_format ='HTML',
@recipients = @To, -- replace with your email address
@subject = @Subject ;
END
ELSE
BEGIN
  
   Set @body='NO Fragmented indexes on '+(select @@servername)+' server'
  
   EXEC msdb.dbo.sp_send_dbmail 
@profile_name = @Profile, -- replace with your SQL Database Mail Profile  
@body = @body, 
@body_format ='HTML', 
@recipients = @To, -- replace with your email address 
@subject = @Subject ;
END


GO

 

Fragmenation Report of all databases in a single report

Here is the Script to generate a report of all fragmenatation indexes in a server and send the report Via mail to your inbox.

You can automate this in SQL job just by adding Reciepients and DB Mail profiles as parameter

SP will send details Report of Fragmented indexes to your Mail, You can Schdeule this job or run on demand to fetch the data.

Based on the report you can analyze the data on weekly basis and set weekly Reindex and Reorganize plans
__________________________________________________________________________


Step1:

Create an SP using Script 1.

Description: SP will fetch the data (Fragmenated Indexes info)from all the databases and send mail to reciepients  

Step2:

Sample Query to get Fragmentation report

exec [USP_Fragmentation_Report] 'hanuman.devineni@gmail.com','DBMailProfile'

--Parameter 1 : Recipient to which you want to send mail

--Parameter 2 : DB Mail Profile Name

Script 1 to create an SP below:


/****** Object:  StoredProcedure [dbo].[USP_Fragmentation_Report]    Script Date: 03/18/2014 03:09:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE PROCEDURE [dbo].[USP_Fragmentation_Report] 

 @To varchar(100),
 @profile varchar(50)

 AS
/*************************************************************************** 
-- PROGRAM: Fragmentation Index report 
-- CREATED: 12/18/2013   AUTHOR:  Hanuman D 
-- EXAMPLE: EXEC the script to get the Fragmented indexes {object name} 
-- DESC   : Script will send Defragmented report mail to Reciepients
--                
   
***************************************************************************************************/ 
 
Set Nocount On 
/*-- Query to Check for the temporary table if  exists drops and recreates */ 
 
If exists (select * from tempdb.sys.all_objects where name like '#Temp%' )  
drop table #Temp  
create table #Temp  
(DatabaseName varchar(50),ObjectName varchar(100),Index_id int, indexName varchar(100),avg_fragmentation_percent
int,IndexType varchar(50),Action_Required varchar(20) default 'NA')  
 
 
/*-- Insert records of fragmented indexes from each user database to temperorary table */ 
insert into #Temp (DatabaseName,ObjectName,Index_id, indexName,avg_fragmentation_percent,IndexType)  
 
exec master.sys.sp_MSforeachdb ' USE [?]  
 IF DB_ID(''?'') > 4 
SELECT db_name() as DatabaseName, OBJECT_NAME (a.object_id) as ObjectName,   
  
a.index_id, b.name as IndexName,   
  
round(avg_fragmentation_in_percent,0) asavg_fragmentation_in_percent, index_type_desc  
  
-- , record_count, avg_page_space_used_in_percent --(null in limited)  
  
FROM sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) AS a  
  
JOIN sys.indexes AS b   
  
ON a.object_id = b.object_id AND a.index_id = b.index_id  
  
WHERE b.index_id <> 0 and avg_fragmentation_in_percent >50 '  
  
/*  Update the Action column as Reorganize or Reindex based on Fragmentation % */ 
update #Temp  
set Action_Required ='Rebuild'  
where avg_fragmentation_percent >40   
 
 /* Removed as we are getting only for fragmented indexes > 50 
update #Temp  
set Action_Required ='Reorganize'  
where avg_fragmentation_percent <30 and avg_fragmentation_percent >14  
 
*/ 
 
/*Query to exclude the data for readonly databases*/ 
 
delete from #Temp where DatabaseName in (select name from sys.databases where is_read_only <> 0) 
 
/*-- Query to Capture information and send mail in HTML format*/ 
 
 
DECLARE @xml NVARCHAR(MAX) 
DECLARE @body NVARCHAR(MAX) 
DECLARE @Subject NVARCHAR(300)
DECLARE @rowcount int
 
 
SET @Subject='Fragmentation Index Report for Server '+(select @@servername)+'' 
/* Query to check count of temp table and send mail if greater than 0  */
SELECT @rowcount = COUNT(*) FROM #Temp
IF (@rowcount  > 0)
BEGIN
    
SET @xml = CAST(( SELECT [DatabaseName] AS 'td','',[ObjectName] AS 'td','', 
       [Index_id] AS 'td','', [indexName] AS 'td','',[avg_fragmentation_percent] AS 'td','',[IndexType] 'td','',
[Action_Required] AS 'td' 
FROM  #Temp Where databasename not like '%FP%' and databasename not like '%reportserver%' and databasename not like
'%_test%' and databasename <>'TestData' and databasename <>'profiler' and databasename not like '%FYE%'
ORDER BY DatabaseName 
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX)) 
 
 
SET @body = 
N'<H4>Fragmented Indexes on MMI server '+ (select td = (select @@servername))+'>50%</H4>'+ 
N'<html><body><table border = 1> <FONT SIZE=2> 
<tr bgcolor=#F8F8FD> 
<th> <FONT SIZE=2>DatabaseName </FONT></th> <th><FONT SIZE=2> ObjectName </FONT></th> <th><FONT SIZE=2>
Index_id</FONT></th> <th><FONT SIZE=2> IndexName</FONT></th><th><FONT SIZE=2>
avg_fragmentation_percent</FONT></th><th><FONT SIZE=2> IndexType </FONT></th>
<th><FONT SIZE=2>Action_Required</FONT></th></tr>'     
 
 
SET @body = @body + @xml +'</FONT></table></body></html>' 
 
 
EXEC msdb.dbo.sp_send_dbmail 
@profile_name = @Profile, -- replaces with your SQL Database Mail Profile  
@body = @body, 
@body_format ='HTML', 
@recipients = @To, -- replaces with your email address 
@subject = @Subject ; 
 
END
ELSE
BEGIN
  
   Set @body='NO Fragmented indexes on '+(select @@servername)+' server'
  
   EXEC msdb.dbo.sp_send_dbmail 
@profile_name = @Profile, -- replaces with your SQL Database Mail Profile  
@body = @body, 
@body_format ='HTML', 
@recipients = @To, -- replaces with your email address 
@subject = @Subject ;
END