Brent Ozar Unlimited's specialized experts focus on your goals, diagnose your tough database pains, and make Microsoft SQL Server faster and more reliable. DeveloperWorks Data Db2 Db2 Community Share. Solve. Do more. Join the Db2 Community Watch the video Community Education Downloads Support Featured IBM Db2 Direct and. Learn how to get set up for SQL Server replication, the replication options available to you, and how to avoid SQL Server issues and errors ahead of time.
SQL DBA Interview Questions with Answers. SQL DBA Interview Questions with Answers – 1. SQL DBA Interview Questions with Answers. Q. What are the common issues a SQL DBA should deal with as a part of DBA daily job?
Ans: Backup Failure. Restore Failure. Log Full Issues. Blocking Alerts. Deadlocks Alerts. TEMPDB full issues. Disk Full Issues. SQL Connectivity Issues. Access issues. Installation and Upgrade Failures.
SQL Agent Job failures. Performance Issues. Resource (Memory/IO/CPU etc.) Utilization Alerts. High- Availability and Disaster Recovery related issues.
Q. “model” system DB is down and we are trying to create a new database. Is it possible to create a new database when model DB is down?
![Microsoft Sql 2000 Transaction Replication Step Microsoft Sql 2000 Transaction Replication Step](https://tr1.cbsistatic.com/hub/i/2007/08/07/2409a55f-c3be-11e2-bc00-02911874f8c8/158285.jpg)
Ans: We can’t create a new database when model database is down. SQL Server restart will be unsuccessful when model database is down as TEMPDB creation failed.
TEMPDB is created based on model DB configurations, since model DB is down TEMPDB will not be created. Q. Which operation is faster COMMIT or ROLLBACK? Why? Ans: It’s obviously COMMIT is faster than ROLLBACK. Let me explain with an example: Let’s say we opened a transaction and updated 8. Commit: It’s completed quickly as the operation is already completed and it just marks those dirty pages as committed and when checkpoint happens all those dirty pages will be written to disk.
Rollback: The operation is already updated 8.LDF which will take time when compared to commit. . Q. What are the different ways available to insert data from a file into SQL Server database table?Ans: These are the different ways: BCPBULKINSERTOPENROWSETOPENDATASOURCEOPENQUERYLINKED SERVERIMPORT/EXPORT WIZARDSSISQ.
![Microsoft Sql 2000 Transaction Replication Step Microsoft Sql 2000 Transaction Replication Step](https://i-technet.sec.s-msft.com/dynimg/IC346198.jpg)
SQL Server articles, scripts, and discussion groups.
What is the scope of different temp objects? Ans: Local Temp Table: “CREATE TABLE #Temp. Table”Local temporary tables are visible only in the current session, and can be shared between nested stored procedure calls. Table Variable: “DECLARE TABLE @Temp. Table”The scope of a local variable is the batch, stored procedure, or statement block in which it is declared. They can be passed as parameters between procedures.
They are not subject to transactions and will retain all rows following a rollback. Derived Table: “SELECT * FROM (SELECT * FROM Customers) AS Temp. Table”Is visible to the current query only. Global Temp Table: “CREATE TABLE ##Temp. Table”This differs from a #temp table in that it is visible to all processes. When the creating process ends, the table is removed (but will wait until any current activity from other processes is done).
CTE: Common Table Expression. Example CTE: ;WITH Your. Big. CTE AS(big query here)SELECT * FROM Your. Table. 1 WHERE ID IN (SELECT ID FROM Your.
Big. CTE)UNIONSELECT * FROM Your. Table. 2 WHERE ID IN (SELECT ID FROM Your. Big. CTE)Scope is next immediate select command. Can be used multiple times within the same CTE command, even recursively, and will last for the duration of the CTE command. Q. What is the maximum limit of SQL Server instances for a standalone computer? Ans: 5. SQL Server editions. SQL Server supports 2.
Q. What is the cluster node limitation? Ans: The number of allowable nodes in the SQL Server cluster depends on your SQL Server version and your Windows Server version.
For SQL Server 2. Standard edition, you can only have two cluster nodes.
If you have SQL Server Enterprise, the limit depends on your Windows Server version, 8 cluster nodes for 2. Q. 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 the setup without actually completing the installation, to generate the INI file. File Location and Name: %programfiles%\Microsoft SQL Server\1. Setup Bootstrap\Log\< YYYYMMDD_HHMM> \Configuration.
File. ini. Q. How to install a SQL Server using configuration file? Ans: From Command prompt locate the setup. Setup. exe /Configuration. File=My. Configuration. File. INIInstead of specifying passwords inside the config file specify them explicitly as below. Setup. exe /SQLSVCPASSWORD=”************” /AGTSVCPASSWORD=”************” /ASSVCPASSWORD=”************” /ISSVCPASSWORD=”************” /RSSVCPASSWORD=”************” /Configuration. File=My. Configuration.
File. INIQ. What are the top performance counters to be monitor in Performance Monitor? Ans: Processor\%Processor Time: Monitoring CPU consumption allows you to check for a bottleneck on the server (indicated by high sustained usage). High percentage of Signal Wait: Signal wait is the time a worker spends waiting for CPU time after it has finished waiting on something else (such as a lock, a latch or some other wait). Time spent waiting on the CPU is indicative of a CPU bottleneck. Signal wait can be found by executing DBCC SQLPERF (waitstats) on SQL Server 2. SQL Server 2. 00.
Physical Disk\Avg. Disk Queue Length: Check for disk bottlenecks: if the value exceeds 2 then it is likely that a disk bottleneck exists. MSSQL$Instance: Buffer Manager\Page Life Expectancy: Page Life Expectancy is the number of seconds a page stays in the buffer cache. A low number indicates that pages are being evicted without spending much time in the cache, which reduces the effectiveness of the cache. MSSQL$Instance: Plan Cache\Cache Hit Ratio: A low Plan Cache hit ratio means that plans are not being reused.
MSSQL$Instance: General Statistics\Processes Blocked: Long blocks indicate contention for resources. Q. Task manager is not showing the correct memory usage by SQL Server.
How to identify the exact memory usage from SQL Server? Ans: To know the exact memory usage relay on column “physical_memory_in_use_kb” from DMV “sys. Using performance counters also we can find the usage. Performance object: Process.
Counter: Private Bytes. Instance: sqlservr. Performance object: Process.
Counter: Working Set. Instance: sqlservr. The Private Bytes counter measures the memory that is currently committed. The Working Set counter measures the physical memory that is currently occupied by the process.
For 6. 4- bit sql servers we can also check the current memory usage using the below performance counter. Performance object: SQL Server: Memory Manager. Counter: Total Server Memory (KB)Q. What is the option”Lock Pages in Memory”? Ans: Lock Pages in Memory is a setting that can be set on 6. Windows not to swap out SQL Server memory to disk. By default, this setting is turned off on 6.
We must be very careful in dealing with this option. One can enable this after a detailed analysis of current environment. Following issues may rise when “Lock Pages in Memory” is not turned on: SQL Server performance suddenly decreases.
Application that connects to SQL Server may encounter timeouts. The hardware running SQL Server may not respond for a short time periods. Q. 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”. Sample query to calculate total AWE memory allocated is “SELECT SUM(awe_allocated_kb) FROM sys. From 2. 00. 8 onwards we can get all memory related information using DMV “sys. Q. How to apply service pack on Active / Passive cluster on 2. Ans: 1. Freeze the service groups on Node A (active node). Confirm all SQL services are stopped on Node B. Upgrade the SQL Server 2.
Node B. 4. Reboot node B. Unfreeze the service group on node A.
Fail over the service group to Node B. After the service group comes online, freeze the service group on Node B. Confirm all SQL services are stopped on Node A.
Upgrade the SQL Server 2. Node A. 1. 0. Reboot Node A. Unfreeze the service group on node B. Fail back the service group to Node A. Q. How to apply a SP on SQL Server 2.
Active / Passive cluster? Ans: 1. Login to the Console on the target nodea. RDP to the console is ok, but a standard RDP connection is not recommended. Copy the Service Pack to a local drive on the target node. Move all instances to the target nodea. You can only install on the Active Node.
Move the Cluster Resource to the target node. Move the MSDTC Resource to the target node. Verify all users are logged out from all other nodes (RDP and Console sessions)7. Start the Service Pack installa. Use a domain account with admin rights to all servers. Ignore locked files. Reboot current servera.
You should not need to perform the install on any other nodes, nor reboot them. The service pack will update the passive nodes first.
Db. 2 - IBM Data for developers. Champion’s corner. IDUG EMEA 2. 01. 7 in Lisbon. My favorite weeks of the year are IDUG conferences. I was lucky enough to go to my first European IDUG last year, and I … Ember Crooks. DB2 Lead DBA and Delivery Manager.
Xtivia. Designs, builds, and manages DB2 databases for multiple clients.Frequent speaker and blogger.IBM Champion and IBM Gold Consultant. . Founder and principal author of db.IBM Champion, Information Management, 2.IBM Gold Consultant, 2.
DB2. Commerce. Follow Ember on Twitter@ember_crooks.