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~

SQL Server Database Instant File Initialization

After building new SQL server one of the most important post configuration required to setup for increasing database performances  is Instant File Initialization (IFI)( you can also change it on existing server). When SQL server require to perform some specific operations like; Create a new database. Add new file to an existing database. Increase the size of an existing files including Auto growth Restore a database For example, when you perform any of the above tasks,  the default setting is for sql server to reserve space using zeros ‘0’, but under IFI, the space is allocated without  the initial zeros … Continue reading SQL Server Database Instant File Initialization

Remove Excessive VLFs

VLF is SQL server internal logical structures of log files. Too many VLFs can cause major performance bottle neck. Excessive VLF can cause slow restores, excessive locking, affects HA (high availability) groups and reduces transaction log backup performance. Detecting and resolving the VLF issue is very simple by pre-sizing, using MB or GB interval for sizing. Any transaction written to the log file has a start time and an end time, and during this written process if the log is backed up then the running transaction is not going to truncate. Once transaction is done, the next log backup will … Continue reading Remove Excessive VLFs

Could not allocate space for object

ERROR: “Could not allocate space for object “object_name” in database “database” because the ‘PRIMARY’ filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup”. If your databases are well managed you should have multiple data files for each database. It depends, of course, on database size and transaction volumes. Our environment follows a strict policy that each data file cannot be more than 30 GB, the reason being multiple data files increase the read/write performance. When any data file … Continue reading Could not allocate space for object

.NET Framework 3.5 Service pack 1 is required

I came across this error when installing Sql Server 2014. One of the prerequisites of sql 2014 is that .Net Framework 3.5 should already exist on the server. Unfortunately the server I was on did not have it, so I had to install it. Now contrast to the message above, I was on a Windows 2012 R2 and simply just enabling the .NET Framework feature did not work. So below are the steps I took to resolve the issue. I located in my environment the disc file for the windows server, which looks like Then I mounted it After mounting … Continue reading .NET Framework 3.5 Service pack 1 is required