Change SQL server database file location without taking the database offline

Changing SQL server database file location is not a frequent practice, but what happens when you require to change file location to different directory? To avoid any database interruption DBCC SHRINKFILE command with the EMPTYFILE option, will allow to perform this migration operation while the database is online. Step01: First check the database files size and available space SELECT DB_NAME() AS DbName, name AS FileName, size/128.0 AS CurrentSizeMB, size/128.0-CAST(FILEPROPERTY (name, ‘SpaceUsed’) AS INT)/128.0 AS FreeSpaceMB FROM sys.database_files; Step02: Go to database property you intend to move location, add file with new Path. Property – files – add Step03: Now use … Continue reading Change SQL server database file location without taking the database offline

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