DBA

http://www.mssqltips.com/tip.asp?tip=1722 Problem Quite frequently I find myself in situation where I need to get detailed information on performance monitor counters. For example I need to determine which processes are consuming all CPU at certain times. I find it handy to push the performance monitor counters into SQL Server where I can query it or perhaps display it in Reporting Services. Solution The following will explain how to select counters, how to collect data, how to load the data to SQL Server and how to query the data that has been saved. Select Counters I first need to collect my data. I use TypePerf to display a list of performance monitor counters on the machine I wish to collect from. TypePerf is installed by default on all machines after Windows 2000. I use the following query to get a list of all the counters that can be used on the machine I am monitoring. Although the counters are generally the same they may be different from machine to machine.. This is run in a

http://www.mssqltips.com/tip.asp?tip=2188 This process involves three steps, each described separately below. Step One Create the Registered Server Group for the SQL Instances you intend to push this solution. I tend to do this step in three phases: Two test instances (one default instance and one named instance – you’ll see why shortly when we look at the results that are returned for queries against sys.dm_os_performance_counters for named instances.) A group of remaining non-production SQL instances since I intend to push out the solution and monitor its behavior for a couple days before I push to production servers. The production instances of SQL Server that I intend to run this process for monitoring. It should be noted that since we’re dealing with Dynamic Management Views that this solution will only work for SQL Server 2005 and newer. Only for the purposes of this article have I created the MSSQLTips registered servers group. It contains two instances: the default instance an

SQL Server 2000 Code Snippet -- Variable Declarations DECLARE @PreviousDate datetime DECLARE @Year VARCHAR(4) DECLARE @Month VARCHAR(2) DECLARE @MonthPre VARCHAR(2) DECLARE @Day VARCHAR(2) DECLARE @DayPre VARCHAR(2) DECLARE @FinalDate INT -- Initialize Variables SET @PreviousDate = DATEADD(dd, -7, GETDATE()) -- Last 7 days SET @Year = DATEPART(yyyy, @PreviousDate) SELECT @MonthPre = CONVERT(VARCHAR(2), DATEPART(mm, @PreviousDate)) SELECT @Month = RIGHT(CONVERT(VARCHAR, (@MonthPre + 1000000000)),2) SELECT @DayPre = CONVERT(VARCHAR(2), DATEPART(dd, @PreviousDate)) SELECT @Day = RIGHT(CONVERT(VARCHAR, (@DayPre + 1000000000)),2) SET @FinalDate = CAST(@Year + @Month + @Day AS INT) -- Final Logic SELECT j.[name], s.step_name, h.step_id, h.step_name, h.run_date, h.run_time, h.sql_severity, h.message, h.server FROM msdb.dbo.sysjobhistory h INNER JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id

TYPEPERF.EXE is a command line tool included with the Windows operating system that writes performance data to the command window or to a file. To get a list of counters with instances enter the following command: TYPEPERF -qx "SQLServer:Databases" | FIND "tempdb" You will see output similar to the following: \SQLServer:Databases(tempdb)\Data File(s) Size (KB) \SQLServer:Databases(tempdb)\Log File(s) Size (KB) ... C:\Documents and Settings\automatingdba>TYPEPERF -q "SQLServer:Databases" \SQLServer:Databases(*)\Data File(s) Size (KB) \SQLServer:Databases(*)\Log File(s) Size (KB) ... TYPEPERF -f CSV -o MyCounters.csv -si 15 -cf MyCounters.txt -sc 60 The above example writes the counter values to MyCounters.csv every 15 seconds. It stops after writing out the counters 60 times (i.e. 15 minutes). The list of counters in MyCounters.txt is: \SQLServer:Databases(_Total)\DBCC Logical Scan Bytes/sec \SQLServer:Databases(tempdb)\Percent Log Used ... the followi

@echo off Echo. > c:\dir\sstate.txt Echo Stopping service on all servers sc \\servername stop servicename> nul Echo Waiting 10 seconds CHOICE /N /C YN /T 10 /D Y Echo Starting service on all servers sc start service > nul sc \\servername start servicename > nul Echo Waiting 10 seconds CHOICE /N /C YN /T 10 /D Y Echo servername >> c:\dir\sstate.txt sc \\servername interrogate servicename >> c:\dir\sstate.txt if defined batch goto :EOF notepad c:\dir\sstate.txt

###this script run a query to generate an output file and ftp to remote site. export ORACLE_SID=PROD ORAENV_ASK=NO . oraenv FTP="ip.ip.ip.ip" LOGIN="ftpuser" PASS="password" sqlplus -s " / as sysdba" < set verify off set pagesize 0 set linesize 500 set head on --set markup html on set feedback off set timing off --set pagesize 0 set colsep , col channel_name format a29 col programme_name format a49 col house_media format a9 spool /folder/filename.csv SELECT statement; spool off exit EOF ftp -nv $FTP < /folder/log.log echo "############# Starting FTP to $FTP ###################" user $LOGIN $PASS type ascii lcd /folder del filename.csv put filename.csv dir filename.csv quit EOFLOG echo finished ftp

#!/bin/ksh ###################################################### # alert_log_mail.ksh # Purpose: email error msg in alert log to DBA # to use it, run thisscript.ksh instancename or ALL ################################################################################ if [ $# -eq 0 ] then echo "Usage: `basename $0` | ALL " exit else OPTION=$* fi if [ "$OPTION" = "ALL" -o "$OPTION" = "all" ] then DB_LIST=`cat /etc/oratab|grep -v ^#|grep -v agent|grep -v Apach|grep -v ^$|awk -F: '{print $1}'` else DB_LIST=$OPTION fi export DBLOG=/home/oracle/dba/logs get_msg(){ FILENAME=$1 MSG=$2 if [ -s ${DBLOG}/$(basename ${FILENAME}).mail ] then CKPOINT=`tail -1 ${DBLOG}/$(basename ${FILENAME}).mail|awk -F: '{print $1}'` else CKPOINT=0 fi FILESIZE=`cat $FILENAME|wc -l` if [ $FILESIZE -lt $CKPOINT ]; then CKPOINT=0; fi STARTPOINT=`expr ${CKPOINT} + 1 ` tail +${STARTPOINT} ${FILENAME}|hea

Recently our BPC nightly job failed intermittently, a few database mirror servers report timeout as well, I need to prove to network team that it's not an application issue, it's a network issue, so I implement the a windows scheduled task to run pathping among those servers every 1 minute. it did prove that network timeout. here is the script and sample of logs. pingnetwork.bat : echo %date% %time% pathping server1 echo %date% %time% pathping server2 echo %date% %time% pathping server3 echo %date% %time% pathping server4 pingnetwork.bat >> e:\dropit\d.txt the log d.txt looks like this " Thu 05/19/2011 3:20:15.19 E:\dba>pathping agenwi034 Tracing route to agenwi034.corp.ctv.ca [10.1.38.134] over a maximum of 30 hops: 0 agends030.corp.ctv.ca [10.1.38.130] 1 * agenwi034.corp.ctv.ca [10.1.38.134] Computing statistics for 25 seconds... Source to Here This Node/Link Hop RTT Lost/Sent = Pct Lost/Sent = Pct Address 0

The witness sqlserver sometime get disconnected, reboot it sometime fix the problem, if not, reset witness is required, here is how. to find out how many databases are using this witness, run query on witness: SELECT principal_server_name, mirror_server_name, database_name, safety_level_desc FROM sys.database_mirroring_witnesses to find out which server is disconnected, run this sql on the principle server: select mirroring_partner_name,mirroring_partner_name,mirroring_witness_name, mirroring_state_desc,mirroring_witness_state_desc, * from sys.database_mirroring if the mirroring_witness_state_desc = disconnected, then remove the witness by running the following script. let's refer this list as $DBLIST. on witness: select 'alter database '+database_name+' set witness off' FROM sys.database_mirroring_witnesses pickup the output that matching $DBLIST and save the output as o1.sql. select 'alter database '+database_name+' set witness ='+'''

goal: create database after creating control file using "alter database create controlfile to trace as 'filename';", I sometime need to replace the path of datafiles, the two awk command below helps me doing so. awk -F '/' '{ if (NR>10 && NR < 107 && ($NF ~ /dbf/ ||$NF ~ /log/) ) {print "\x27/bossdb/bnwrms/"$NF;} else if (NF==0) {} else {print $0;}}' control.sql awk –v q=“’” -F '/' '{ if (NR>10 && NR < 107 && ($NF ~ /dbf/ ||$NF ~ /log/) ) {print q "/bossdb/bnwrms/"$NF;} else if (NF==0) {} else {print $0;}}' control.sql