Search results matching tag 'DBA Scripts'

SQL Server – How to troubleshoot query timeouts

Jack Vamvas — Sun, 04 Mar 2012 06:00:00 GMT

Common reasons for query time-outs are :

1) The application starts using a query not optimal for the index

2) Hardware changes\ Configuration changes

3) Increased load

If you suspect the query time-out is due to memory issue , continue reading.

Debugging a query-timeout is tricky. If it’s a Production system and users are experiencing timeouts – pressure mounts on the DBA. The application owner starts looking towards the DBA , for reasons and a solution. (and to blame)

Isolate the problem by using a systematic approach . There are multiple approaches to any problem –

A system for debugging a query-timeout. Each problem has it’s own characteristics – using this system , should give you enough ideas. Contact me: Jack Vamvas(sqlserver-dba.com) for any help

Step 1 – system memory status

Use sys.dm_os_memory_clerks, sys.dm_os_sys_info, and memory performance counters.

-- sys.dm_os_memory_clerks
select  memory_clerk_address,type,single_pages_kb,awe_allocated_kb
from  sys.dm_os_memory_clerks
--sys.dm_os_sys_info
select physical_memory_in_bytes,virtual_memory_in_bytes,bpool_commited,bpool_commited,bpool_commit_target from sys.dm_os_sys_info
--memory performance counters
"\Memory\Available MBytes" 
"\Memory\Page Faults/sec" 
"\Memory\Pages/sec" 
"\Memory\Paging File(_Total)\%Usage"

Step 2 – query execution memory reservations

select * from sys.dm_os_memory_clerks where
type = 'MEMORYCLERK_SQLQERESERVATIONS'

Step 3 – identify queries waiting memory grants

SELECT * from . sys.dm_exec_query_memory_grants

Step 4 – identify more memory-intensive queries

select session_id, command,  
status, sql_handle
from sys.dm_exec_requests

Step 5 – Analyse and fix Query from cache. Work with developers to overcome any bottlenecks

SQL Server query plans in cache

sql server scripts and managing databases

Jack Vamvas — Thu, 01 Mar 2012 06:00:00 GMT

Managing a large SQL Server inventory requires an efficient management processes.

Following on from DBA productivity and less is more , this post outlines the system I use to manage the DBA scripts across the database server environment

1) Develop a script library covering the essentials of DBA database management . Tasks include : Reindexing , statistics, defragementation, checkdb , kill user processes, kill all db connections and much more..

My current version of SQL_TOOLS has 345 scripts covering a wide range of DBA tasks

2) Create an installation T-SQL script. The purpose is to deploy the scripts onto the SQL Server Instance via command line .

An example of the script could be as follows . The script I actually use doesn’t allow the DROP DATABASE as the logon account doesn’t have DROP DATABASE privileges , to avoid any mistakes

This example script assumes you have the five scripts mentioned in the same directory :

/* SCRIPT: SQL_TOOLS_INSTALL.sql */
/* BUILD A THE SQL-TOOLS DATABASE */

-- This is the main caller for each script
SET NOCOUNT ON
GO

PRINT 'CREATING DATABASE'
IF EXISTS (SELECT 1 FROM SYS.DATABASES WHERE NAME = 'SQL_TOOLS')
DROP DATABASE SQL_TOOLS
GO
CREATE DATABASE SQL_TOOLS
GO
USE SQL_TOOLS
GO

:On Error exit

:r isp_ALTER_INDEX_main.sql
:r isp_UPDATE_STATISTICS_main.sql
:r isp_DBCC_CHECKDB.sql
:r isp_KILL_ALL_DBCONNECTIONS.sql
:r isp_KILL_USER_PROCESSES.sql

PRINT 'SQL_TOOLS DATABASE CREATE IS COMPLETE'
GO

3) Create a batch file installation script. This is the script you’ll execute from the command line – which will trigger the script in step 2.

       
An example is : 
       /* SCRIPT: SQL_TOOLS_INSTALL.bat */
       /* Excecute from the command line */
       /*input parameter1 = ”

SQLCMD -E -d master -S %1 -i "SQL_TOOLS_INSTALL.sql"
PAUSE

4) Once the scripts are deployed onto the SQL Server Instance – you have a script library available to a) create scheduled or ad-hoc tasks b) deploy secure scripts for troubleshooting , analysis and reporting

The key to this system is a) maintaining a centralised script library , test and proven on lower environments. b) rapid deployment onto a sql server environment c) Securing the scripts and limiting execution rights to relevant accounts

This approach is only one part of a strategy . Further considerations are scheduling of scripts , managing installation, disaster recovery and inventory management

SQL Server Agent Job Steps

Jack Vamvas — Tue, 28 Feb 2012 06:00:00 GMT

To list a SQL Server Agent Job Steps use the T-SQL example below.

This example displays the step id, SQL Server Agent Job name, Step name.

The code has 1 input parameter - @job_name

DECLARE @job_name VARCHAR(128)
SET @job_name = 'My SQL Server Agent Job'
SELECT js.step_id ,j.name,js.step_name FROM sysjobsteps js inner join sysjobs j 
ON js.job_id=j.job_id 
WHERE j.name = @job_name ORDER BY step_id

See Also

SQL Server agent fixed roles

SQL Agent Jobs – Schedule in seconds

Republished with author's permission. See the original post here.

DB2 – verify DB2 is available

Jack Vamvas — Mon, 13 Feb 2012 06:00:00 GMT

This article describes some methods of checking DB2 is running on a Linux Server

Method 1

Log onto the server and execute db2start as the instance owner. If the instance is running you’ll see , something like:

02/10/2012 23:36:14 0 0 SQL1026N The database manager is already active.

SQL1026N The database manager is already active.

Method 2

Log onto the server a run a netstat –a. This lists all TCP/IP connections. Look for the instance names or the ports , such as :

tcp 0 0 *:db2c_db2a *:* LISTEN

tcp 0 0 *:db2c_db2b *:* LISTEN

tcp 0 0 *:db2c_db2c *:* LISTEN

tcp 0 0 *:db2c_db2d *:* LISTEN

tcp 0 0 *:db2c_db2e *:* LISTEN

Method 3

On the Linux command line , issue the command :

ps aux | less | grep 'db2sysc'

If you see similar to this reponse – then the DB2 servers are running.

db2test 13474 0.1 11.3 8860292 3506408 ? Sl Feb01 24:49 db2sysc 0

db2dba 13823 0.0 0.9 1858144 279532 ? Sl Feb01 9:09 db2sysc 0

SQL Server – COPY TABLE from another SQL Server

Jack Vamvas — Thu, 02 Feb 2012 06:00:00 GMT

To copy a table from another SQL Server Instance is easy. Create a Linked Server on the destination server and reference the Linked Server in the SELECT statement.

1) Create linked server

/****** Object:  LinkedServer [SERVER1\SQL_SERVER_INSTANCE_1]    Script Date: 02/02/2012 09:29:33 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'SERVER1\SQL_SERVER_INSTANCE_1', @srvproduct=N'SQL Server'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SERVER1\SQL_SERVER_INSTANCE_1',@useself=N'False',@locallogin=NULL,@rmtuser=N'MY_MIGRATION',@rmtpassword='########'
GO

2) On the destination server

--SELECT * into  from   

--Example:  Using the Linked server

SELECT * INTO my_test_table_copy FROM [SERVER1\SQL_SERVER_INSTANCE_1].MY_DB.dbo.my_test_table

SQL Server – Send email using Powershell

Jack Vamvas — Tue, 31 Jan 2012 06:00:00 GMT

This article explains how to send an email through Powershell.

Very useful if you have a requirement to automate your scripts and send notifications .

The example is calling the SmtpClient Class , which is part of the .Net Framework.

You can call a Function – which makes your scripting more modular.

#variables
$emailFrom = "JackVamvas@sqlserver-dba.com"
$emailTo = "dba@sqlserver-dba.com"
$subject = "SQLServer-DBA.com: Powershell Function calling an SMTP server"
$body = "SQLServer-DBA.com : Send an email through SMTP in Powershell"
$smtpServer = "aservername"

#create a function
Function sendEmail([string]$emailFrom, [string]$emailTo, [string]$subject,[string]$body,[string]$smtpServer)
{
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
$smtp.Send($emailFrom,$emailTo,$subject,$body)
}

#call the function 
sendEmail $emailFrom $emailTo $subject $body $smtpServer

DB2 – Run script on Linux start

Jack Vamvas — Fri, 27 Jan 2012 06:00:00 GMT

A quick method to use for executing a script on Linux start.

After all the other processes are started by the init command at boot time, the script is run on Red Hat based systems:

/etc/rc.d/rc.local

Add any extra script\commands references to this file.

SQL Server – Error Logs recycle without SQL Server Restart

Jack Vamvas — Thu, 26 Jan 2012 06:00:00 GMT

If some long running error situation , the Error Log can become very big. In those situations , it can be time-consuming to search for specific Messages.

It is a good idea to recycle the SQL Server Error Logs . To create a new Error Log file without restarting SQL Server , use the sp_cycle_errorlog system stored procedure.

This closes down the current error log and cycles the error log

--execute  the sp_cycle_errorlog
EXEC sp_cycle_errorlog

DB2 security audit

Jack Vamvas — Tue, 10 Jan 2012 06:00:00 GMT

The SYSIBM.SYSDBAUTH maintains user privileges on databases

The SYSIBM.SYSPLANAUTH maintains user privileges on plans

The SYSIBM.SYSUSERAUTH maintain user privileges on the system.

This shell script collects and records these recordsets. Useful for regular DB2 security audits.

working_dir=$PWD
logfile="AUTH_`date +%d%m%y`.log"
#export working_dir
#export logfile
touch $working_dir/$logfile
>$working_dir/$logfile
echo "Current working directory $working_dir" >> $working_dir/$logfile
echo "operatation began `date`" >> $working_dir/$logfile
for i in `db2 list db directory | grep 'Database name' | awk '{print $4}'`;
do
echo "=============================="  >> $working_dir/$logfile ;
echo "getting :DB AUTH,USER AUTH,PLAN AUTH for: $i"  >> $working_dir/$logfile ;
echo "=============================="  >> $working_dir/$logfile ;
echo "checking database state"  >> $working_dir/$logfile ;
state=$(db2 get db cfg for $i | grep 'HADR database role' | awk '{print $5}');
echo "Current state is $state"  >> $working_dir/$logfile ;

if [ $state = "STANDBY"  ]
then
echo "THIS DATABASE IS THE STANDBY, THIS OPERATION SHOULD BE PERFORMED AT THE PRIMARY"  >> $working_dir/$logfile
else
echo "command : db2 connect to $i "  >> $working_dir/$logfile ;
db2  connect to $i  >> $working_dir/$logfile ;

echo "command : select * from SYSIBM.SYSDBAUTH"  >> $working_dir/$logfile ;
db2 "select * from SYSIBM.SYSDBAUTH"  >> $working_dir/$logfile;

echo "command : select * from SYSIBM.SYSPLANAUTH"  >> $working_dir/$logfile ;
db2 "select * from SYSIBM.SYSPLANAUTH"  >> $working_dir/$logfile;

echo "command : select * from SYSIBM.SYSUSERAUTH"  >> $working_dir/$logfile ;
db2 "select * from SYSIBM.SYSUSERAUTH"  >> $working_dir/$logfile;

db2 terminate 

fi

echo "=============================="  >> $working_dir/$logfile ;
echo "";
done
echo "operatation ended `date`" >> $working_dir/$logfile

DB2 – db2sysc and instance status

Jack Vamvas — Mon, 09 Jan 2012 06:00:00 GMT

For a quick check on the DB2 instance status on Linux use:

ps aux | less | grep 'db2sysc'

Lists all DB2 instances running on the server.

db2sysc is the main DB2 engine

DB2 health script

Author: Jack Vamvas(http://www.dba-db2.com)

Republished with author's permission. See the original post here.

Search results matching tag 'DBA Scripts'

SQL Server – How to troubleshoot query timeouts

Common reasons for query time-outs are :

Step 1 – system memory status

Step 2 – query execution memory reservations

Step 3 – identify queries waiting memory grants

Step 4 – identify more memory-intensive queries

Step 5 – Analyse and fix Query from cache. Work with developers to overcome any bottlenecks

See Also

sql server scripts and managing databases

See Also

SQL Server Agent Job Steps

DB2 – verify DB2 is available

Method 1

Method 2

Method 3

See Also

SQL Server – COPY TABLE from another SQL Server

See Also

SQL Server – Send email using Powershell

See Also

DB2 – Run script on Linux start

See Also

SQL Server – Error Logs recycle without SQL Server Restart

See Also

DB2 security audit

See Also

DB2 – db2sysc and instance status

Related Posts