Long Running Query in SQL server

While SQL server slow performance  the most common source are long running process, which can distress our entire instances performance  like others process, CPU, memory, disk and etc. In order to demonstrate how to capture a long running query for your database instance, with appropriate execution plan for the related process. This query will give you most expensive processes list with all possible details. SELECT r.session_id , r.start_time ,  TotalElapsedTime_ms = r.total_elapsed_time ,      r.[status] ,   r.command ,   DatabaseName = DB_Name(r.database_id) ,   r.wait_type ,      r.last_wait_type ,   r.wait_resource ,    r.cpu_time ,    r.reads ,      r.writes ,   … Continue reading Long Running Query in SQL server

SQL Server Database Restore History

  While working with databases, one of the most repeated task are database restore and refresh from production to test or dev environment. I’ve had situations where after a refresh (restore databases), our client would assume the refresh was not done or the refresh was not completed. They were not seeing the data they were expecting. Now how can you prove that you did the refresh from the most recent backup successfully? The below script will give you all restore details like restore date, which backup was restored, what sources location, and etc. use Master SELECT [rs].[destination_database_name], [rs].[restore_date], [bs].[backup_start_date], [bs].[backup_finish_date], … Continue reading SQL Server Database Restore History

Why am I getting a Backup alert

We use SQL server central management server (Master server) to manage all SQL instance centrally, in order to apply policy, update, query entire environment form one center location, other than login each server. We create all job in the master server and push to all target server, but job run individual server in different schedule. So we create one additional job (schedule job) individual server to call actual job. Many time we get backup alert through backup job, which check all backup jobs status if any backup job is not run based on schedule it throw alert. But when I … Continue reading Why am I getting a Backup alert

SQL Server Password Policy

SQL Server supports two authentication modes, Windows authentication mode Mixed mode. Windows authentication is the default, and is often referred to as integrated security because this SQL Server security model is tightly integrated with Windows. Specific Windows user and group accounts are trusted to log in to SQL Server. Windows users who have already been authenticated do not have to present additional credentials. Mixed mode supports authentication both by Windows and by SQL Server. User name and password pairs are maintained within SQL Server. The windows password policy check against password minimum length, password history (password can’t be used if … Continue reading SQL Server Password Policy

~First week at a new Environment~

    As a DBA, your first week at a new environment can be testing to say the least. Worst case scenario is that you arrive in an environment where there is no SOP (Standard Operating Procedure), best practice is unheard of, the databases are running themselves (maybe in fantasy land), and they have no idea why you were hired.  If all the mentioned are true, then all I can say is RUN! We are DBAs, not Magicians, although that depends on whom you ask. The first couple weeks are challenging if you are not prepared, but if you come … Continue reading ~First week at a new Environment~

How to Move schema data from USERS tablespace to their own tablespace

#TASK – – Move schema (ABC) data from USERS tablespace to their own tablespace It is very common for objects to be created in wrong tablespaces especially the USERS tablespace. Part of the DBA’s task is to move these objects to the right tablespace. First. you want to note all objects belonging to the schema. –Create new tablespaces to host schema data CREATE SMALLFILE TABLESPACE XXXX_ts DATAFILE ‘XXXX_01.dbf’ SIZE 32767M AUTOEXTEND ON NEXT 512M MAXSIZE 32767M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; — Add enough datafiles on the tablespace (This depends on the size of the data to … Continue reading How to Move schema data from USERS tablespace to their own tablespace


set pagesize 200 set linesize 200 col username format a30 col osuser format a20 –Archive log dest. %used col name for a45 SELECT NAME,TO_CHAR(SPACE_LIMIT, ‘999,999,999,999’) AS SPACE_LIMIT, TO_CHAR(SPACE_LIMIT – SPACE_USED + SPACE_RECLAIMABLE, ‘999,999,999,999’) AS SPACE_AVAILABLE, ROUND((SPACE_USED – SPACE_RECLAIMABLE)/SPACE_LIMIT * 100, 1) AS PERCENT_FULL FROM V$RECOVERY_FILE_DEST; –To check file consuming the most space du -sh * du -h /u01/app/oracle | grep ‘[0-9\.]\+G’ –To get difference between 2 files diff ./initinvprd2.ora /u01/app/oracle/ANTHONY/initinvprd2.ora or diff ./initPROD.ora initTEST.ora –To check cpu usage on OS mpstat 2 100 — Schema_names, tables & Size MB set pagesize 200 set linesize 200 col username format a30 col … Continue reading QUICK & EASY TO USE SCRIPTS