Archive for March, 2011

Script to find CPU Bottel Necks

Posted: March 30, 2011 in Sqlserver

CPU Bottlenecks

Advertisements

Please run the below code from Cmd prompt to change the server level collation for Sqlserver2008.

start /wait \\server\d$\ms_sql_install\ms_sql2008_standard\cd1\setup.exe /QUIET

/ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=Domain\adminxxxx

/SAPWD= Test /SQLCOLLATION=SQL_Latin1_General_CP1250_CI_AS

Problem

I had a scenario where one of my production sqlserver agent went into hung state due to which none of the sql jobs were running and no backups were happening though we have a NSM agent sitting on each of the production server which monitors the services and disk threshold. Since the Sql agent service was in running status and due to some reason it was not working as expected. Unless and until the service is down CA NSM agent will not trigger the event as an incident request to DBA.

Solution

I started to thing about something which monitor all my production server’s backup and send as an consolidated email for the backup status of all sqlserver’s.

I got a idea to develop something which is easy to maintain and identify the failure of backup by seeing the email.

Powershell script is a upgraded part of technet library.  I have rebuilded this script to record the output in excel sheet and send as an e-mail attachment to the DBA Team.

This scripts point you exactly which server and which database did not got backed up and from how many days backups are failing.

—————————————————————————————————————————————

### Code that can be used to Monitor all you Sql Instances Backups from One Location

 

 

#Create a new Excel object using COM

$ErrorActionPreference = “silentlycontinue”

$Excel = New-Object -ComObject Excel.Application

$Excel.visible = $False

$Excel.DisplayAlerts = $false

$ExcelWorkbooks = $Excel.Workbooks.Add()

$Sheet = $ExcelWorkbooks.Worksheets.Item(1)

#$MonitorBody = “D:\PowerShell\PScripts\Mail.htm”

#$date = get-date -uformat “%Y%m%d”

$date = ( get-date ).ToString(‘yyyy/MM/dd’)

$save = “D:\PowerShell\ExcelReports\DatabaseBackup_Report.xls”

#Counter variable for rows

$intRow = 1

#Read the contents of the Servers.txt file

#foreach ($instance in get-content “serverlist.txt”)

 

##################Loop in all your sqlserver instances#########################

foreach ($instance in get-content “D:\PowerShell\PScripts\Backupserverlist.txt”)

{

#Create column headers

$Sheet.Cells.Item($intRow,1) = “INSTANCE NAME:”

$Sheet.Cells.Item($intRow,2) = $instance

$Sheet.Cells.Item($intRow,1).Font.Bold = $True

$Sheet.Cells.Item($intRow,2).Font.Bold = $True

 

$intRow++

 

$Sheet.Cells.Item($intRow,1) = “DATABASE NAME”

$Sheet.Cells.Item($intRow,2) = “LAST FULL BACKUP”

$Sheet.Cells.Item($intRow,3) = “LAST LOG BACKUP”

$Sheet.Cells.Item($intRow,4) = “FULL BACKUP AGE(DAYS)”

$Sheet.Cells.Item($intRow,5) = “LOG BACKUP AGE(HOURS)”

 

#Format the column headers

for ($col = 1; $col –le 5; $col++)

{

$Sheet.Cells.Item($intRow,$col).Font.Bold = $True

$Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 50

$Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 36

}

 

$intRow++

#######################################################

#This script gets SQL Server database information using PowerShell

 

[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’) | out-null

 

# Create an SMO connection to the instance

$s = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) $instance

 

$dbs = $s.Databases

#Formatting using Excel

ForEach ($db in $dbs)

{

if ($db.Name -ne “tempdb”) #We do not need the backup information for the tempdb database

{

#We use Date Math to extract the number of days since the last full backup

$NumDaysSinceLastFullBackup = ((Get-Date) – $db.LastBackupDate).Days

#Here we use TotalHours to extract the total number of hours

$NumDaysSinceLastLogBackup = ((Get-Date) – $db.LastLogBackupDate).TotalHours

if($db.LastBackupDate -eq “1/1/2005 12:00 AM”)

#This date is a start of Sqlserver infra.
#This is the default dateTime value for databases that have not had any backups
{

$fullBackupDate=”Never been backed up”
$fgColor3=”red”

}

else

{

$fullBackupDate=”{0:g}” -f $db.LastBackupDate
}

$Sheet.Cells.Item($intRow, 1) = $db.Name
$Sheet.Cells.Item($intRow, 2) = $fullBackupDate
$fgColor3=”green”

#Use the .ToString() Method to convert the value of the Recovery model to string and ignore Log #backups for databases with Simple recovery model

if ($db.RecoveryModel.Tostring() -eq “SIMPLE”)
{

$logBackupDate=”N/A”
$NumDaysSinceLastLogBackup=”N/A”

}
else

{

if($db.LastLogBackupDate -eq “1/1/2011 12:00 AM”)

{
$logBackupDate=”Never been backed up”
}

else

{
$logBackupDate= “{0:g2}” -f $db.LastLogBackupDate
}

}

$Sheet.Cells.Item($intRow, 3) = $logBackupDate

#Define your service-level agreement in terms of days here.

if ($NumDaysSinceLastFullBackup -gt 0)

{

$fgColor = 3

}

else

{

$fgColor = 50

}

$Sheet.Cells.Item($intRow, 4) = $NumDaysSinceLastFullBackup
$Sheet.Cells.item($intRow, 4).Interior.ColorIndex = $fgColor
$Sheet.Cells.Item($intRow, 5) = $NumDaysSinceLastLogBackup
$intRow ++

}
}
$intRow ++
}

 
$Sheet.UsedRange.EntireColumn.AutoFit()
$ExcelWorkbooks.SaveAs($save)
$Excel.quit()
CLS

######Send Email with excel sheet as a attachment#######

$mail = New-Object System.Net.Mail.MailMessage
$att = new-object Net.Mail.Attachment($save)
$mail.From = “shamsul.hassan@gmail.com”
$mail.To.Add(“shamsul.hassan@gmail.com”)
$mail.Subject = “Database Backup Report of all Sqlserver for $date ”
$mail.Body = “This mail gives us the detail information of all the database backup of production sqlserver which are schedule to run every day. Please review the Excel report enclosed with the mail every day and fix the failed backups which is marked in Red color and make sure the Full Backup Age(DAYS) is Zero days.Thanks – Md.S.Hassan”
$mail.Attachments.Add($att)
$smtp = New-Object System.Net.Mail.SmtpClient(“smtp.us.tesco.org”)
$smtp.Credentials = New-Object System.Net.NetworkCredential(“us\DBA_mon”,”xxxxx”)
$smtp.Send($mail)

Here is the Script which monitors and triggers mail to DBA with SPID and Code which got blocked by other  session id’s in any of the sqlserver databases.

This script gives in depth information to troubleshoot the blocking sessions.

/**—Create an Sqlserver Agent Job to report Blocking events and Mail to DBA team—-**/

declare @subj varchar(max),
@cmd varchar(max),
@file varchar(100)
select    @subj = @@servername  + ‘—Encountered Blocking sessions’,
@cmd = ‘Print ”Below listed Session_Id are blocked by.
————————————–


select r.Session_id as SID,
r.Start_time,
DBName = db_name(database_id),
r.Command,
substring(st.text,
r.statement_start_offset/2+1,
(case when r.statement_end_offset = -1 then len(convert(nvarchar(max), st.text)) * 2 else r.statement_end_offset end – r.statement_start_offset)/2+1) As BlockedQuery,
r.Blocking_session_id,
s.Memory_usage,
s.Host_name,
s.Program_name,
s.Login_name
from sys.dm_exec_requests r
join sys.dm_exec_connections c on r.session_id = c.session_id
join sys.dm_exec_sessions s on s.session_id = r.session_id
cross apply sys.dm_exec_sql_text(r.sql_handle) st
where r.session_id > 50 and r.blocking_session_id>1
order by r.session_id

exec msdb.dbo.sp_send_dbmail
@profile_name = ‘sql – dba’,
@recipients = ‘shamsul.hassan@hassanszone.com’,
@subject = @subj,
@body= ”,
@query = @cmd,
@query_result_separator = ‘|’,
@query_result_no_padding = 1

/**—Create an Sqlserver Alert to trigger Blocking alerts Mail to DBA team—-**/

USE [msdb]
GO
EXEC msdb.dbo.sp_update_alert @name=N’Request Blocked Processes’,
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=0,
@database_name=N”,
@notification_message=N”,
@event_description_keyword=N”,
@performance_condition=N’SQLSERVER:General Statistics|Processes blocked||>|1′,
@wmi_namespace=N”,
@wmi_query=N”,
—        @job_id=N’bc259ae4-d96b-4146-a372-1343e91cb5de’,
@job_name=’DBA_BlockingCheck_Report’
GO

 

Requirement:

I had a requirement to monitor the Growth of all the databases of all the sqlserver instances in network and create the report for higher management.

Solution:

After trying many things and I got a simple and the best solution to get this job done.

To record all the databases growth in your network all you need is to do is follow the below steps.

You need a Master server something like DBA admin server which monitors all the sqlservers in network.

  1. Create Linked server in all your sqlserver instances in network.

For example: ABC is my Master server

1.      XYZ is my network SqlServer

2.      PQR is my another Network Sqlserver

Create Linked server ABC in XYZ and PQR network server.

2.    Create a Table in your monitoring database of Master server

3.    Create a sql agent job on all the network sqlserver to capture and insert the data in master server table.

All can be done easily if you are working with SQLserver2008 using Central Management Server(CMS)

/*****——————————-Code to Create Linked server——————————————****/

USE [master]
GO
/****** Object:  LinkedServer [ABC]    Script Date: 03/04/2011 05:05:37 ******/
EXEC master.dbo.sp_dropserver @server=N’ABC’, @droplogins=’droplogins’
GO

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N’ABC’, @srvproduct=N’SQL Server’

GO
EXEC master.dbo.sp_serveroption @server=N’ABC’, @optname=N’collation compatible’, @optvalue=N’false’
GO
EXEC master.dbo.sp_serveroption @server=N’ABC’, @optname=N’data access’, @optvalue=N’true’
GO
EXEC master.dbo.sp_serveroption @server=N’ABC’, @optname=N’dist’, @optvalue=N’false’
GO
EXEC master.dbo.sp_serveroption @server=N’ABC’, @optname=N’pub’, @optvalue=N’false’
GO
EXEC master.dbo.sp_serveroption @server=N’ABC’, @optname=N’rpc’, @optvalue=N’true’
GO
EXEC master.dbo.sp_serveroption @server=N’ABC’, @optname=N’rpc out’, @optvalue=N’true’
GO
EXEC master.dbo.sp_serveroption @server=N’ABC’, @optname=N’sub’, @optvalue=N’false’
GO
EXEC master.dbo.sp_serveroption @server=N’ABC’, @optname=N’connect timeout’, @optvalue=N’0′
GO
EXEC master.dbo.sp_serveroption @server=N’ABC’, @optname=N’collation name’, @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N’ABC’, @optname=N’lazy schema validation’, @optvalue=N’false’
GO
EXEC master.dbo.sp_serveroption @server=N’ABC’, @optname=N’query timeout’, @optvalue=N’0′
GO
EXEC master.dbo.sp_serveroption @server=N’ABC’, @optname=N’use remote collation’, @optvalue=N’true’
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N’ABC’, @locallogin = NULL , @useself = N’False’, @rmtuser = N’sysdba’, @rmtpassword = N’xxxxxx’
GO

/***——————Create Base Table in Monitoring Database of Master server———–***/
CREATE TABLE [dbo].[tblDatabaseGrowth](
[DBName] [varchar](200) NULL,
[Size] [varchar](20) NULL,
[Owner] [varchar](50) NULL,
[DBid] [int] NULL,
[Created] [datetime] NULL,
[Status] [varchar](4000) NULL,
[Compatability] [int] NULL,
[PollDate] [datetime] NULL,
[ServerName] [varchar](50) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[tblDatabaseGrowth] ADD  CONSTRAINT [DF_tblDatabaseGrowth_PollDate]  DEFAULT (getdate()) FOR [PollDate]
GO

ALTER TABLE [dbo].[tblDatabaseGrowth] ADD  DEFAULT (”XX”) FOR [ServerName]
GO

/*****—————-Code to create Sql agent job in all the network server———————- **/

USE [msdb]
GO
/****** Object:  Job [DBA_DatabaseGrowthMonitor]    Script Date: 03/04/2011 08:36:14 ******/
EXEC msdb.dbo.sp_delete_job @job_name=’DBA_DatabaseGrowthMonitor’, @delete_unused_schedule=1
GO

USE [msdb]

GO

/****** Object:  Job [DBA_DatabaseGrowthMonitor]    Script Date: 03/04/2011 08:32:15 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 03/04/2011 08:32:16 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]’ AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N’JOB’, @type=N’LOCAL’, @name=N'[Uncategorized (Local)]’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N’DBA_DatabaseGrowthMonitor’,
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N’No description available.’,
@category_name=N'[Uncategorized (Local)]’,
@owner_login_name=N’sa’, @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [DatabaseGrowthMonito]    Script Date: 03/04/2011 08:32:16 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’DatabaseGrowthMonito’,
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N’TSQL’,
@command=N’USE tempdb
GO

/****** Object:  Table [dbo].[#tblDatabaseGrowth]    Script Date: 03/04/2011 08:02:33 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[#tblDatabaseGrowth](
[DBName] [varchar](200) NULL,
[Size] [varchar](20) NULL,
[Owner] [varchar](50) NULL,
[DBid] [int] NULL,
[Created] [datetime] NULL,
[Status] [varchar](4000) NULL,
[Compatability] [int] NULL,
[PollDate] [datetime] NULL,
[ServerName] [varchar](50) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[#tblDatabaseGrowth] ADD  CONSTRAINT [DF_tblDatabaseGrowth_PollDate]  DEFAULT (getdate()) FOR [PollDate]
GO

ALTER TABLE [dbo].[#tblDatabaseGrowth] ADD  DEFAULT (”XX”) FOR [ServerName]
GO

—Insertion of SP_Helpdb data of database in temp table
Insert into #tblDatabaseGrowth
(DBname,size,owner,DBId,created,status,compatability)
exec sp_helpdb

–Setting varable to store servername which will put an update for serverName

Declare @sql nvarchar(25)
Set @sql=@@servername
Update  #tblDatabaseGrowth set ServerName=@sql where ServerName like ”%X%”
–select @sql
Select DBName,Size,Owner,DBID,Created,Status,
Compatability,PollDate,ServerName
from #tblDatabaseGrowth (nolock)

–Insert the actual data into master server by using linked server query.
Insert ABC.DBA_Admin.dbo.tblDatabaseGrowth
(DBname,size,owner,DBid,created,status,compatability,ServerName)
Select DBName,Size,Owner,DBID,Created,Status,Compatability,ServerName
from #tblDatabaseGrowth

–Drop temp table
Drop table #tblDatabaseGrowth
–Select DBName,Size,Owner,DBID,Created,Status,Compatability,PollDate,ServerName  from  dba_admin.dbo.tblDatabaseGrowth (nolock)
–truncate table dba_admin.dbo.tblDatabaseGrowth
‘,
@database_name=N’master’,
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N’DBGrowthMonitor’,
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20110303,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959
–@schedule_uid=N’22b56d17-5138-496c-a721-c3bbaad554f7′
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

———————Reporting can be done using Excel Pivot table———————————————-

All you need to do is create a sql connection in MS Excel and display the result in the form of Pivot Table