Top posts
-
How to apply service pack on Active / Active cluster Nodes?
How to change the sql server service account in a cluster environment? Ans: Method 1: (No failover required) 1. Freeze the service group on active node from cluster administrator and then restart the service. Method2: 1. Offline the SQL resources 2. Update...
-
What is the common trace flags used with SQL Server?
What is the common trace flags used with SQL Server? Deadlock Information: 1204, 1205, 1222 Network Database files: 1807 Log Record for Connections: 4013 Skip Startup Stored Procedures: 4022 Disable Locking Hints: 8755 Forces uniform extent allocations...
-
Can we install SQL Server using a configure file?
Can we install SQL Server using a configure file? Ans: Yes! We can prepare a configuration file. While installing SQL Server the path to the configuration file is specified in the “Ready to Install” page in the configuration file path section. Cancel...
-
Consider a situation where publisher database log file has been increasing and there there is just few MB available on disk. As an experienced professional how do you react to this situation? Remember no disk space available and also we can’t create a new log file on other drive
Consider a situation where publisher database log file has been increasing and there there is just few MB available on disk. As an experienced professional how do you react to this situation? Remember no disk space available and also we can’t create a...
-
What are the main events and columns helpful in troubleshooting performance issues using profiler?
What are the main events and columns helpful in troubleshooting performance issues using profiler? Ans: Events: Event Group: Performance Event: ShowPlan_ALL (BinaryData column must be selected) Event: ShowPlan_XML Event Group: T-SQL Event: SQL:BatchStarted...
-
E&Y Position Sr. SQL DBA
1. Can we truncate a table which is participating in transactional replication? 2. How to identify log filling issue and can we issue shrink with truncate on publisher database? 3. How to filter the nested stored procedure or a command from profiler?...
-
How to recover a database that is in suspect stage?
How to recover a database that is in suspect stage? ALTER DATABASE test_db SET EMERGENCY After you execute this statement SQL Server will shutdown the database and restart it without recovering it. This will allow you to view / query database objects,...
-
How to change the sql server service account in a cluster environment?
How to change the sql server service account in a cluster environment? Ans: Method 1: (No failover required) 1. Freeze the service group on active node from cluster administrator and then restart the service. Method2: 1. Offline the SQL resources 2. Update...
-
PRAGMA RESTRICT_REFEREN
PRAGMA RESTRICT_REFERENCES Oracle PRAGMA RESTRICT_REFERENCES uses to control the side effects of PL/SQL Subprograms. Every PL/SQL Subprograms must follow some rules in terms of transaction control and security. What is PRAGMA PRAGMA is an instruction...
-
SQL SERVER – CASE Statement in ORDER BY Clause – ORDER BY using Variable
USE AdventureWorks GO DECLARE @OrderBy VARCHAR(10) DECLARE @OrderByDirection VARCHAR(1) SET @OrderBy = 'State' ----Other options Postal for PostalCode, ---- State for StateProvinceID, City for City SET @OrderByDirection = 'D' ----Other options A for ascending,...
-
LAG function Oracle
LAG function Oracle by sqlandplsql LAG function used to find previous row value within the same table. It is an example of analytical function. Syntax :- LAG(column, context, default) over partition clause context – number of rows to go backward. Default...
-
What is performance tuning?
'Performance tuning' is the term used to improve the performance of a query..that is, quickening the query execution time...a query that involves a large no of db tables may take around 20-30 min to execute for ex... In such case, the developer shud fine...
-
What are the cursor attributes used in PL/SQL?
What are the cursor attributes used in PL/SQL? %ISOPEN: it checks whether the cursor is open or not. %ROWCOUNT: returns the number of rows affected by DML operations: INSERT,DELETE,UPDATE,SELECT. %FOUND: it checks whether cursor has fetched any row. If...
-
DECODE and CASE.
Differ between DECODE and CASE. DECODE and CASE statements are very similar, but CASE is extended version of DECODE. DECODE does not allow Decision making statements in its place. select decode(totalsal=12000,’high’,10000,’medium’) as decode_tesr from...
-
CA TECHNOLOGIES SQL DBA
1. How to find the tempdb contention? 2. What is the sparse file? 3. What is the redo file in log shipping? 4. What are the queues in sql server? 5. How to capture a trace from production without any impact on performance? 6. How to capture the long running...
-
Deleting Duplicate Rows
Deleting Duplicate Rows in Oracle CREATE TABLE tbl_test( SER_NO NUMBER, FST_NM VARCHAR2(30), DEPTID NUMBER, CMNT VARCHAR2(30)); INSERT INTO tbl_test VALUES(1, 'aaaaa', 2004, 'xxx'); INSERT INTO tbl_test VALUES(2, 'bbbbb', 2005, 'yyy'); INSERT INTO tbl_test...
-
INDEXES IN ORACLE
Indexes Indexes Indexes are optional structures associated with tables. Indexes can be created to increase the performance of data retrieval. Just as the index in a manual helps you quickly locate specific information, an Oracle index provides an access...
-
Oracle Database Administrator (DBA) Questions
1. What is an Oracle Instance? 2. What information is stored in Control File? 3. When you start an Oracle DB which file is accessed first? 4. What is the Job of SMON, PMON processes? 5. What is Instance Recovery? 6. What is written in Redo Log Files?...
-
Top Ten Mistakes Found in Oracle Systems
Top Ten Mistakes Found in Oracle Systems 1. Bad Connection Management 2. Bad Use of Cursors and the Shared Pool 3. Bad SQL 4. Use of Nonstandard Initialization Parameters 5. Getting Database I/O Wrong 6. Redo Log Setup Problems 7. Serialization of data...
-
Database Tuning Tips
Database Tuning Tips Do as little as possible - Focus on simplyfing the processes instead of achiving a theoretical design perfection. Avoid multiple IO to the database - bundle queries to a procedure and call thay in ur app. again in procedures reduce...
-
SQL Tuning Tips
SQL Tuning Tips Avoid un-necessary sorts - use union all, use compute stats while creating index. Hint when needed. Avoid Cartesian products Avoid full table scans on large tables Avoid joining too many tables Use SQL standards and conventions to reduce...
-
RAISE_APPLICATION_ERROR in oracle
RAISE_APPLICATION_ERROR? The RAISE_APPLICATION_ERROR is a procedure defined by Oracle that allows the developer to raise an exception and associate an error number and message with the procedure other than just Oracle errors. Raising an Application Error...
-
How to Restore Oracle Database using RMAN (with Examples)
How to Restore Oracle Database using RMAN (with Examples) As a Linux sysadmin, you might recover a system from backup, which may include Oracle Database. So, it is essential for all admins to understand how to restore oracle database from backup. Typically,...
-
Fixed Server Roles in SQL Server
You can execute the below query and find the fixed and user defined server roles. SELECT name, principal_id, type, type_desc, is_fixed_role, owning_principal_id FROM sys.server_principals WHERE type = 'R' There are eight types of fixed server roles in...
-
How to generate index fragmentation report for all the indexes?
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'),...