SQL Server Basic Monitoring

In monitoring servers you should first know what to look for. Based on my experience database problems can be categorized into five categories.

1. CPU
2. Memory
3. Network
4. I/O Bottlenecks
5. Slow Queries

These are the queries we can  use to troubleshoot where the problem is.

CPU

DECLARE @BatchRequests BIGINT;

 

SELECT @BatchRequests = cntr_value

FROM sys.dm_os_performance_counters

WHERE counter_name = 'Batch Requests/sec';

 

WAITFOR DELAY '00:00:10';

 

SELECT (cntr_value - @BatchRequests) / 10 AS 'Batch Requests/sec'

FROM sys.dm_os_performance_counters

WHERE counter_name = 'Batch Requests/sec';

The Batch Requests/sec value depends on hardware used, but it should be under 1000. The recommended value for SQL Compilations/sec is less than 10% of Batch Requests/sec and for SQL Re-Compilations/sec is less than 10% of SQL Compilations/sec

Oracle How to drop all schema objects

 select 'drop '||object_type||' &owner..'|| object_name || ';' from dba_objects WHERE OWNER=UPPER('&owner') and object_type in ('TABLE','VIEW','PACKAGE','SEQUENCE', 'PROCEDURE', 'FUNCTION', 'INDEX');

ORA-00257: Archiver error. Connect AS SYSDBA only until resolved

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 150G SCOPE=BOTH

CREATE READ_ONLY USER IN ORACLE DATABASE

CREATE USER READ_ONLY IDENTIFIED BY "password";

GRANT CREATE SESSION TO READ_ONLY;

SET HEADING OFF

SET PAGES 9999 LINES 400

SPOOL TABLE_GRANTS.sql

SELECT 'GRANT READ ON ' || OWNER || '.' || TABLE_NAME || ' TO READ_ONLY;'

FROM DBA_TABLES

WHERE OWNER IN ('ZEDDBA')

ORDER BY 1;

SPOOL OFF

@TABLE_GRANTS.sql

!rm TABLE_GRANTS.sql 

9 common mistakes you are doing right now

  10 Mistakes We Are Doing

1. Poor Handshake
2. No Eye Contact
3. Being Dismissive
4. Failing to Ask Good Questions
5. Neglecting your Reputation
6. Showing signs of disinterest
7. Talking first, giving Later
8. Failing to showcase your strongest assets
9. Speaking before you listen.
10. Trying too hard

Who is doing the backup on your sql server

; WITH bus AS (

 SELECT database_name, machine_name, backup_finish_date, media_set_id, type,msdb.dbo.backupset.software_vendor_id,

 msdb.dbo.backupset.user_name,

        row_number () OVER(PARTITION BY database_name ORDER BY backup_finish_date DESC) AS rowno

 FROM   msdb.dbo.backupset

)

SELECT bus.machine_name AS ServerName, sdb.Name AS DatabaseName,

       COALESCE(convert(varchar(23), bus.backup_finish_date), 'No backup') AS BackupTime ,

      CASE bus.type 

      WHEN 'D' THEN 'Database' 

      WHEN 'L' THEN 'Log File'

      WHEN 'I' THEN 'Differential'

      END AS BackupType,software_vendor_id,user_name,

busf.physical_device_name AS Location

FROM sys.databases sdb

LEFT OUTER JOIN bus ON bus.database_name = sdb.name AND rowno <= 2

LEFT OUTER JOIN msdb.dbo.backupmediafamily busf ON busf.media_set_id = bus.media_set_id

WHERE database_id > 4

ORDER BY bus.machine_name

my smtp server

 ecphksmtp.ecp.priv