Top posts
-
blocking SQL Server
What is blocking and how would you troubleshoot it? Blocking occurs when two or more rows are locked by one SQL connection and a second connection to the SQL server requires a conflicting on lock on those rows. This results in the second connection to...
-
Oracle PRAGMA TYPES
Pragma compiler directives instructs compiler with certain information. It is Processed at compile time not at run time. The directive restricts member sub programs to query or modify database tables and packaged variables. PRAGMA directive should be...
-
Database Performance Issues
SQL Server Wait Types – Trace and Tune Database Performance Issues It is a big challenge for database administrators to trace performance issues, tune and maintain database server effectively. I receive many queries regarding database performance issues,...
-
Backup All User and System Databases
Backup All User and System Databases DECLARE @BkpFDate VARCHAR(50) SELECT @BkpFDate = REPLACE(CONVERT(VARCHAR(50), GETDATE(), 102),'.','') + REPLACE(CONVERT(VARCHAR(50), GETDATE(), 108),':','') DECLARE Cursor_DBs CURSOR FOR SELECT name FROM master.sys.DATABASES...
-
database mirroring QUESTIONS
What is database mirroring? In which SQL Server version it was introduced? Why mirroring why not Log shipping? What are the difference between Log Shipping and mirroring? Can you explain the specific usability of mirroring, Log Shipping and Replication?...
-
mirroring Operating Modes?
In which Operations are running the Operating Modes? Asynchronous:- Under asynchronous the principle server does not wait for the response from the mirror server after sneding logbuffer. Synchronous:- Under synchronous the principle server sends the log...
-
System Triggers
System Triggers The syntax for creating a system trigger is very similar to the syntax used for creating DML trigger SQL> CREATE TABLE session_info 2 (username VARCHAR2(30), 3 logon_date DATE, 4 session_id VARCHAR2(30), 5 ip_addr VARCHAR2(30), 6 hostname...
-
SQL GROUP BY Clause
SQL GROUP BY Clause The GROUP BY clause is used to summarize data based on grouping criteria. For each group one column is returned in result set. Syntax SELECT expression1, expression2, … expressionN, aggregate function (aggregate expression) FROM Table...
-
SQL SERVER TRIGGERS
What is a Trigger A trigger is a special kind of a store procedure that executes in response to certain action on the table like insertion, deletion or updation of data. It is a database object which is bound to a table and is executed automatically....
-
Microsoft GTSC Position SQL DBA Banglore
1. We have a log shipping environment. New data file has been added at primary server, what happens and how do you resolve it? 2. See we have a view which is getting data from different tables. One day it’s starts executing infinitely. I have seen no...
-
Performance Tuning – SQL Server Part -2 –
Performance Tuning – SQL Server Part -2 – Q. What are the primary differences between an index reorganization and an index rebuild? Ans: Reorganization is an “online” operation by default; a rebuild is an “offline” operation by default Reorganization...
-
IBM SQL DBA
1. How to rebuild a master database? Ho to restore a master database? 2. Any alternative to triggers? 3. What are the counters to monitor CPU usage? 4. Top 5 performance tuning tools 5. What events need to be added to capture execution plan in sql profiller?...
-
LOG file architecture?
“Transactionlog” Logical Architecture: Each log record is identified by a unique number called LSN (Log Sequence Number). A log record contains the LSN, TransactionID to which it belongs and data modification record. Data modification record: It’s either...
-
How to apply service pack on Active / Passive cluster on 2008 and 2012?
How do you know how much memory has been allocated to sql server using AWE? Ans: We can use DBCC MEMORYSTSTUS command to know the memory allocation information. But it’s trick to understand the results. We can use a DMV called “sys.DM_OS_Memory_Clerks”....
-
UNIX / Linux Commands
Used UNIX / Linux Commands (With Examples) 1. tar command examples Create a new tar archive. $ tar cvf archive_name.tar dirname/ Extract from an existing tar archive. $ tar xvf archive_name.tar View an existing tar archive. $ tar tvf archive_name.tar...
-
SQL server Interview Questions
What is difference between DELETE and TRUNCATE commands? Delete command removes the rows from a table based on the condition that we provide with a WHERE clause. Truncate will actually remove all the rows from a table and there will be no data in the...
-
SQL SERVER PROCEDURES
Customers Table Contracts Table Syntax The syntax to create a stored procedure in SQL Server (Transact-SQL) is: CREATE { PROCEDURE | PROC } [schema_name.]procedure_name [ @parameter [type_schema_name.] datatype [ VARYING ] [ = default ] [ OUT | OUTPUT...
-
Active Directory
Active Directory: Its a framework that holds the objects. The AD network can be addressed at different levels (Forest, Tree and Domain). Active Directory || Forest || Tree || Domains || Objects (Nodes/Computers) Forest: A forest is a collection of trees...
-
Compound Triggers in Oracle 11g
Compound Triggers in Oracle 11g In Oracle 11g, the concept of compound trigger was introduced. A compound trigger is a single trigger on a table that enables you to specify actions for each of four timing points: Before the firing statement Before each...
-
FORALL
FORALL The FORALL syntax allows us to bind the contents of a collection to a single DML statement, allowing the DML to be run for each row in the collection without requiring a context switch each time. To test bulk binds using records we first create...
-
Bulk Binds in Oracle
Bulk Binds Bulk binding decreases context switch between PL/SQL engine and SQL Engine that makes code execution faster. Context switch occurs every time when PL/SQL calls SQL engine to parse, execute or fetch from cursor. Since context switching uses...
-
Deleting the duplicate records
Deleting the duplicate records The usual need is to delete all but one such row. The first thing is to know the deciding factor -out of the duplicates, which one is to be retained? The next SQL retains the row with the minimum rowid: SQL> delete from...
-
DBA Daily Activities
AS DBA Checklist The role of Database Administrator is very critical and important and DBA has to perform lots of activities and monitoring is one of the important task. A good DBA always tries to minimize his routine activities by automating tasks. The...
-
Upgrading to SQL Server 2008 Using Backup and Restore Method
SOLUTION The database backup and restore operation is an excellent method which is available to database administrators who need to upgrade a database from SQL Server 2000 or SQL Server 2005 to SQL Server 2008. Moving a database using backup and restore...
-
What is the database that has the backup and restore system tables?
The MSDB database is the database with the backup and restore system tables. Here are the backup and restore system tables and their purpose: - backupfile - contains one row for each data file or log file backed up - backupmediafamily - contains one row...