MS SQL SERVER DBA

Disk I/O To m easure disk I/O activity, you can use the following counters:                  1.   PhysicalDisk\Avg. Disk Queue Length Threshold : Should not be higher than the number of spindles plus two. Significance: This counter indicates the average number of both read and writes requests that were queued for the selected disk during the sample interval 2.   PhysicalDisk\Avg. Disk Read Queue Length Threshold : Should be less than two. Significance: This counter indicates the average number of read requests that were queued for the selected disk during the sample interval. 3. PhysicalDisk\Avg. Disk Write Queue Length Threshold : Should be less than two. Significance: This counter indicates the average number of write requests that were queued for the selected disk during the sample interval.   4.   PhysicalDisk\Avg. Disk sec/Read Threshold: No specific value.         Less than 10 ms – very good         Between 10-20 ms – okay         Between 20-50 m

1. LogicalDisk: Avg. Disk Queue Length:   If a logical disk gets very busy, then I/O requests have to be queued. The longer the queue, the more likely there is an I/O bottleneck . Again, be sure to select those counters for each logical drive that contains your mdf and ldf files 2. Memory: Available Mbytes : Measures how much RAM is currently unused, and so available for use by SQL Server and the OS. Generally speaking, if this drops below 5mb, this is a possible indication of a memory bottleneck . 3. Memory: Pages/sec : Measures how much paging the OS is performing. A high number may indicate a potential memory bottleneck. 4. Processor: % Processor Time: _Total : Measures the percentage of available CPUs in the computer that are busy. Generally speaking, if this number exceeds 80% for long periods of time, this may be an indicat

                                                                      Migration Pre-migration check list: 1. Take backup of all databases including system databases 2. Analyze the disk space of the target server for the new database. 3. Confirm the data and log file location for the target server. 4. Collect the information of database logins, users and their permissions. (Optional) 5. Check the database for the Orphan users. (EXEC sp_change_users_login @action='report') 6. sp_change_users_login 'Auto_Fix', userNAME, NULL 7. Check the SQL Server for any dependent objects (SQL Agent Jobs and Linked Servers) Procedure of migration and post migration check list. 1. Make a copy of the data and log file using detach. 2. Attach to target server. 3. Change the compatibility level. 4. Run DBCC upgrade usage DBCC Updateusage (“DB”) with count_rows.( Reports and corrects            pages and row count inaccuracies in the catalog views.) 5. Run DBCC CHECKDB D

Implementation Plan 1. Take backup of system registry 2. Take backup of system databases and user databases 3. Take backup of settings at SQL Server level 4. Script out logins and jobs 5. Take backup of resource database 6. Make sure that we have the existing level (services packs and cumulative updates readily available). Useful incase of rollback 7. Make a note of existing SP and patches and resource db version 8. Install SQL Server service pack 9 Verify the installation logs 10. Verify that all components and resource database are upgraded to the latest service pack level 11. Reboot the server 12. Check connectivity from within the server and outside the server also 13. Ask the application teams to check connectivity with SQL Server

 SQL Server Securities 1. Ensure the physical security of each SQL Server, preventing any unauthorized users from physically access your servers. 2. Assign the SA account a very obscure password, and never use it to log onto SQL Server. Instead, use a Windows Authentication account to access SQL Server as a sysadmin. 3. Give users the least amount of permissions they need to perform their job. 4. When possible, use Windows Authentication logins instead of SQL Server logins. 5. Use strong passwords for all SQL Server login accounts. 6. Remove user login IDs who no longer need access to SQL Server. 7. Remove user login IDs who no longer need access to SQL Server. Default remains disabled in sql 2005. 8. Avoid creating network shares on any SQL Server. 9. Ensure that your SQL Servers are behind a firewall and are not exposed directly to the Internet. 10. In SQL Server 2005 and earlier, remove the BUILTIN/Administrators group to prevent local    

DB health check includes Disk space check: exec xp_fixeddrives : it gives informations of free space left on all drives Agent jobs check: sp_help_job, sysjobhistory : it gives info about origination server name, job name, enabled status, description of job, owner, date created, date modified, last run data and time, next run date and time etc. Database size and free space left : sp_spaceused : dbneme, db size and unallocated or free space on disk. Log space details : dbcc sqlperf(logspace) : dbname, log size in MB and space used in percentage. Login failed and succeeded detais : EXEC sp_readerrorlog 0,1,'Login Succeeded or failed' Database status : sys.databases : state_description Deadlock: dbcc (1222,1) : write all deadlock details in error log file. Blocking: activity monitor or sp_who2 or sys.sysprocesses gives informations about blocking. Database uptime : script to find out it : since when database up. In

Dynamic Managment Views sys.dm_db_mirroring_connections - connection id, connect time,total bytes sent and received etc. sys.dm_db_missing_index_details – to find out missing index. sys.dm_exec_requests - Returns information about each request that is executing within SQL Server like: - transaction ID, session id, blocking process, open transactions count and many more. Select * from sys.sysprocesses to get blocking information’s.   sys.dm_os_cluster_nodes - This view returns a row for each node in the failover cluster instance configuration. If the current instance is a failover clustered instance, it returns a list of nodes on which this failover cluster instance (formerly ‘virtual server’) has been defined. If the current server instance is not a failover clustered instance, it returns an empty row set. sys.dm_os_hosts - Returns all the hosts currently registered in an instance of SQL Server.

DBCC Commands DBCC Checkdb(‘dbname’|dbid) with estimateonly |  -  Physical_only|all_errormsgs|no_infomsgs DBCC SQLPERF ( LOGSPACE ) it shows the space information of all database’s log file (log size and log space used in percentage) DBCC CHECKALLOC (‘dbname’|dbid’) checks the consistency of disk space allocation structure for specified database. This DBCC cannot be run on tempdb. DBCC CHECKCATALOG (‘dbname’|dbid’) checks the catalog consistency of database. DBCC CHECKCONSTRAINTS (tabName|tabID|constraintName|constrantID) check the consistency of constraint. DBCC CHECKFILEGROUP (FGname|FGid) checks the allocation and structural integrity of specified file group. DBCC ShowFileStats – show the name of .mdf, location ot it, total extents and used extents DBCC CHECKTABLE (tabName|tabID) checks the integrity of all pages of table DBCC CLEANTABLE (dbname | dbid, tabname | tabID) reclaims space for dropped columns in table by using ALTER TABLE DROP C

    Deadlock is a process where one user performs some operations on a single object and other user also want to perform the same operation on the same particular object, is called a Deadlock. We have to enable  the Trace on the SQL Server by the below Command.                    DBCC  TRACEON (1204, -1) Now check on the Error Logs we will be seeing the Deadlock that has been occured on the objects on the server. Resolving Deadlocks Adding & Dropping of Indexes. Adding Index hints. Modify the application to access resources in a similar pattern. Removing the activity transactions like triggers. By default triggers are transactional. Keeping transaction as short a possible. Intent shared Locks.

Features of SQL Server 2008. Activity Monitor. SQL Server Audits. Backup Compression Central Management Servers. Data Collector & Management Data warehouse Data Compression Policy based Management. Predictable Performance & Concurrency. Resource Governor. Transaparent Data Encryption. Featu res of SQL Server 2012. Always on Availability Groups. Windows Server Core Support. Column Store Indexes. User Defined Server Roles. Enhanced Auditing Features. BI Semantic Model. Sequence objects. Enhanced Powershell Support. Distributed Replay. Power View. SQL Azure Enhancement. Big Data Support.

    Some time we come to see the database are in Suspect Mode after coming to the shift and checking the databases as part of the daily routine checks. To Fix this follow the below steps. EXEC SP_RESETSTATUS <'DBNAME'>; ALTER DATABASE <'DBNAME'> SET EMERGENCY; DBCC CHECKDB('DBNAME'); ALTER DATABASE <'DBNAME'> SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DBCC CHECKDB ('DBNAME', REPAIR_ALLOW_DATA_LOSS); ALTER DATABASE <DBNAME> SET MULTIUSER; GO

Following things has to be performed on the SQL Server when we are not able to connect to the server. Check whether the OS Level ping request is working / not. Check if protocols are enabled / not. Check if they are communicating on the smae port / not. Check if the login has been created or not. Check if the login has been disabled / Expired. Check if the login has bad passoword. Check if the the authentication mod eis windows only & where the client connection to SQL Server authentication. check if the login has mapped to the database  / not. check if max connections are exceeded. check if the browser service is stopped. check browser service is running but service account assosciated with browser service has permission issues / not.

   This scenario is face by many of the DBA in production and Development servers. Following things has to be performed on the server. 1. Max size has to be set for the Log File. 2. Take a Transaction Log Backup. 3. Add one / more Transaction Log file from same drive to another drive 4. Shrink the log files. 5. Request the windows team to add more space. 6. Move LDF's from one drive to another drive. 7. Change the Recovery Model to Simple. 8. Find the Transactions and after approval kill them. 9. Truncate ( But don't almost never use). TEMPDB is Full: 1. Add one / move log file form same drive / another. 2. Shrink. 3. Request the windows team to add the space. 4. Backup the Log.      Backup LOG Tempdb to disk = 'C:\Tempdb.bak' with Truncate_only; 5. Restart the Instance.

Blocking is a process where a user is performing some operation on an object and another user also performs some operations on the same object at this time the blocking occurs.       The other user has to wait till the first user completes the operations or a lock occurs at the time. Identifying the Blocking: 1. Identifying if the blocking is really present or not.       select * from sys.sysprocesses where blocked > 0; 2. After identifying the spid's. Which spid is the culprit.       DBCC INPUTBUFFER(SPID1)      DBCC INPUTBUFFER(SPID2) 3. Identify the login which spid belongs to which application / Login / Network     Select * from sys.sysprocessess where spid=<spid1>; 4. Priority should be choosedn accoring to the time started, CPU time, Memory usag, IO usage.      sp_who2 5. If confused on priority immediately escalate the information to the respective team / lead.     Kill the process if necessary if the process is g