Sunday 23 November 2014

Good tool to compare SQL tables data between 2 servers(TableDiff GUI)

Recently I went through good and simple application to compare SQL table data using Simple GUI and Microsoft SQL component Tablediff.exe file.


If you are not aware please use it.. its uses Microsoft component tablediff,exe file to compare the data and send you the list of objects missing in Source and destination and mismatch rows and gives you a query to insert the data or update or delete data.

Please downloaded from code.google.com  https://code.google.com/p/sqltablediff/downloads/detail?name=SqlTableDiff_1.0.zip


Please find the steps to use the simple tool,Once downloaded ,


Extract and open the folder SqlTableDiff_1.0


-->Double Click on SQLTableDiff  application (Doesn't require anything to install).












Give the source Name and Destination Server and database name,

Also you can choose SQL or windows authentication.

Select the required table name.

Then click on GO.

Out folder path can be provided or it takes downloaded folder as default.

For every execution one folder gets created and you will find 2 files, one is Report.txt and tablename.SQL.

Report.txt file contains info of row missed or mismatched in a table selected.

tablename.SQL file contains queries to insert or update or delete the data ..

SO finally tool makes easy to compare and fix the data issues especially before setting up replication, table comparison.

Please feel free to reach me if you need any assistance in using the tool.

Wednesday 21 May 2014

Query to Find Disk Alllocated and Free space on Server


Disk Space Alert and Report

Below query is useful to get an report to Mail like each drive info as in bottom of the page.


You need to replace DB Mail and Mail ID in below Script to get Report.

We can execute this job as response to any Disk Space Alert so if there disk space issue Alert will trigger job to execute and Report will be send to MailID in script replaced,

If you need to creates Disk space Alert please find the Link

http://myexperienceinmssql.blogspot.com/2014/04/sql-alert-for-disk-space-issues.html

Script requires XP_CMDShell to be enabled on the server.

Set NOcount on
declare @svrName varchar(255)
declare @sql varchar(400)
--by default it will take the current server name, we can the set the server name as well
set @svrName = @@SERVERNAME
set @sql = 'powershell.exe -c "Get-WmiObject -ComputerName ' + QUOTENAME(@svrName,'''') + ' -Class Win32_Volume -Filter ''DriveType = 3'' | select name,capacity,freespace | foreach{$_.name+''|''+$_.capacity/1048576+''%''+$_.freespace/1048576+''*''}"'
--creating a temporary table
CREATE TABLE #output
(line varchar(255))
--inserting disk name, total space and free space value in to temporary table
insert #output
EXEC xp_cmdshell @sql
--script to retrieve the values in MB from PS Script output
CREATE TABLE #DiskInfo (DriveName NVarchar(100),DiskAllocated NVarchar(100),Freespace NVarchar(100))

Insert into #DiskInfo
select rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as drivename
      ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,
      (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float),0) as DiskAllocated
      ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,
      (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float),0) as Freespace
from #output
where line like '[A-Z][:]%'

 
DECLARE @xml NVARCHAR(MAX)   
DECLARE @body NVARCHAR(MAX)   
DECLARE @Subject NVARCHAR(300)   
    
SET @Subject='Disk Space issue Alert on Server '+(select @@servername)+''   
    
SET @xml = CAST((select Drivename AS 'td','', DiskAllocated AS 'td','',Freespace AS 'td' from #DiskInfo
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))   
    
    
SET @body =   
N'<H4>Disk Space issue Report on server '+ (select td = (select @@servername))+'  </H4>'+   
N'<H5> Alert is raised because Disk free space is less than threshold values Set on the Server </H5>'+

N'<H5>Please look into this. </H5>'+   

N'<html><body><table border = 1> <FONT SIZE=2>   
<tr bgcolor=#F8F8FD>   
<th> <FONT SIZE=2>Drive Name </FONT></th> <th><FONT SIZE=2> DiskAllocated in MB </FONT></th> <th><FONT SIZE=2> Free Space in MB </FONT></th>
</tr>'

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

   
BEGIN   
EXEC msdb.dbo.sp_send_dbmail   
@profile_name = 'DB Mail', -- replace with your SQL Database Mail Profile    
@body = @body,   
@body_format ='HTML',   
@recipients = 'SQLdbaLearner@gmail.com', -- replace with your email address   
@subject = @Subject ;   
    
END   
    
GO
/*--script to retrieve the values in GB from PS Script output
select rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as drivename
      ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,
      (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float)/1024,0) as 'capacity(GB)'
      ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,
      (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float) /1024 ,0)as 'freespace(GB)'
from #output
where line like '[A-Z][:]%'
order by drivename
*/
--script to drop the temporary table
drop table #output

Drop table #DiskInfo

Ex: Mail body will be as below:

___________________________________________________________________________
Alert is raised because Disk free space is less than threshold values Set on the Server
Please look into this.
Drive Name
DiskAllocated in GB
Free Space in GB
C:\
40
8
D:\
140
46
E:\
320
9
F:\
100
80

Wednesday 9 April 2014

SQL Alert for Disk space issues

I believe creating an SQL Alert for Disk space Alerts will definetly be useful for SQL Database Admins,

Especially this is benificail if we doesn't have any monitoring tool and SQL Admins may not have access to RDP server to setup windows level alerts and check for Disk space .

Below query will create SQL Alert to notify us if disk space is less than 1 GB if you need you can change the thresould value as well , please change the highlighted value .

if you want to change it to 2 GB please replace 2 instead of 1...

5 for 5 GB thresould value.

USE [msdb]
GO

/****** Object:  Alert [Disk Less that 1GB]    Script Date: 04/09/2014 01:52:22 ******/
/****** Author:  Hanuman D           ******/

EXEC msdb.dbo.sp_add_alert @name=N'Disk Less that 1GB',
  @message_id=0,
  @severity=0,
  @enabled=1,
  @delay_between_responses=14400,
  @include_event_description_in=1,
  @notification_message=N'Hi Team,                                                                                                                                                                                                                                Disk Drive has only 1 GB , please look into PRD server.
    
     Thanks,    
     Hanuman D',
  @category_name=N'[Uncategorized]',
  @wmi_namespace=N'\\.\ROOT\CIMV2',
  @wmi_query=N'SELECT * FROM __InstanceModificationEvent WITHIN 600 WHERE TargetInstance ISA "Win32_LogicalDisk" AND TargetInstance.FreeSpace < 1000000000',
  @job_id=N'00000000-0000-0000-0000-000000000000'
GO


Please Mail me if you need any assitance in setting up the DIsk Space Alert.

Wednesday 2 April 2014

SQL DBA Check list

I have prepared check list for SQL DBA collecting information from many blogs and best practices of SQL DBA's.

Please find the below link or attached check list below.


https://drive.google.com/file/d/0BzUDdgme39I5NHJESmRYTUNpYTg/edit?usp=sharing

SQL DBA Check list.xls

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