ORACLE AND MSSQLSERVER

Sript performs the same function as the one above but it displays failure date, job name, step id, step name and error message. SELECT DISTINCT CAST(CONVERT(datetime,CAST(run_date AS char(8)),101) AS char(11)) AS 'Failure Date', SUBSTRING(T2.name,1,40)...

Bulk Exceptions Using BULK COLLECT in PL/SQL blocks and procedures can dramatically speed array processing, but it can, if the DBA isn't prepared, 'hide' any errors that occur in the bulk processing list. A 'plain vanilla' EXCEPTION handler may not report...

The Transact SQL language allow you to use various system stored procedures like: sp_tables, sp_table_privileges, sp_stored_procedures, sp_cursor, sp_executesql, sp_rename, sp_lock, sp_execute, sp_help. Sp_addextendedproperty Sp_columns Sp_column_privileges...

xecute the following Microsoft SQL Server T-SQL database administration script in SSMS Query Editor to REBUILD all indexes in the AdventureWorks2008 sample database. -- SQL Server 2008 script to REBUILD all indexes for all tables USE AdventureWorks2008;...

The following Microsoft SQL Server T-SQL queries will generate index fragmentation report for SQL Server 2005 / 2008 and SQL Server 2000 versions. -- Index fragmentation in a database - QUICK SYNTAX SELECT * FROM sys.dm_db_index_physical_stats(DB_ID('AdventureWorks2008'),...

Fragmentation: Storing data non-contiguously on disk or page is known as fragmentation. 2 types: 1. Internal fragmentation 2. Extrenal fragmentation 1. Internal fragmentation: When records are stored non-contiguously inside the page, then it is called...

SQL Server Architecture SQL Server architecture is mainly divided into different components i.e. SNI Protocol Layer, Relational Engine, Storage Engine, Buffer Pool. Majorly classified as two main engines: Relational Engine and the Storage engine. SNI...

AAFiles: There 3 types of file are there. 1. .MDF [(Master Data File, Main Data File, Primary Data File] 2. .LDF 3. .NDF 1. Primary data file (.MDF) --Primary data files The primary data file is the starting point of the database --Also primary data file...

CheckPoint Lazy Writer 1. Flush dirty pages to Disk 1. Flush dirty pages to disk. 2. Flush only Data pages to disk 2. Check for available memory and removed Buffer pool (execution plan/compile plan/ Data pages /Memory objects) 3. Default, Occurs approximately...

TRACE FLAGS: -d : fully qualified path of datafile -e : error file -l : log file -c: quick start of instance than regular process -m : sinle user mode -s : sqlserver.exe -n : start instance name then the information will not trace in event viewer -t :refereing...