Change SMTP server name

                   Simple Mail Transfer Protocol or SMTP is what allows you to receive email alerts from your Instance to either yourself, your alerting system or group of people. Now, in order for SMTP to work, it must be configured with a server. Some of our Instances were still using old deprecated SMTP servers so I needed to update all the SMTP servers to the current one. I used the following script to resolve the issue across my multiple servers. FYI this script was executed via my CMS (central management server). If you have CMS  in your environment then great, if you do … Continue reading Change SMTP server name

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

QUICK & EASY TO USE SCRIPTS

— Schema_names, tables & Size MB select owner, table_name, round((num_rows*avg_row_len)/(1024*1024)) MB from all_tables where owner not like ‘SYS%’ — Exclude system tables. and num_rows > 0 — Ignore empty Tables. order by MB desc — Biggest first. ; –Schema_names, tables & Rows select owner, table_name, num_rows from all_tables where owner not like ‘SYS%’ — Exclude system tables. and num_rows > 0 — Ignore empty Tables. order by num_rows desc — Biggest first. ; –Lock statistics on those newly created tables so when stats run, these tables will be –skipped –Also, you can lock stats on tables you want to avoid … Continue reading QUICK & EASY TO USE SCRIPTS