Maintenance

Resolving SQL Server errors: The Primary Filegroup is full

May 6, 2021 by Nisarg Upadhyay

In this article, we are going to understand SQL Server error: The Primary filegroup is full and how we can fix this error.

Automate consistency checks of SQL database using Windows Task Scheduler

April 20, 2021 by Nisarg Upadhyay

The SQL database integrity check is one of the most crucial and important tasks of the database administrator. The database integrity task checks the structural integrity and allocation of all database objects and indexes. The integrity checks can be performed by using the DBCC CheckDB command. The CheckDB command is used to identify the corruption in the database. The command performs the following operations on the database.

Automate SQL database backups using Windows Task Scheduler

April 15, 2021 by Nisarg Upadhyay

In this article, we will learn how we can automate the backup of SQL database created in SQL Server Express edition. SQL Server Express edition is a lightweight database that has limited functionalities and resource allocation. The SQL Server Express edition does not support SQL Server Agent jobs, so it is tricky to automate various database administration tasks.

Automate rebuilding SQL Database indexes using Maintenance Plans

April 9, 2021 by Nisarg Upadhyay

Index maintenance is a vital part of a database administrator’s job. I have worked for few clients who often face performance issues in SQL Database and don’t have a dedicated DBA. I remember that one of our clients was facing performance issues. I logged in to their server and found that they did not set up any index maintenance jobs; therefore, many indexes were fragmented, causing the problem.

Move SQL databases to a different mount point: Granting permissions and moving System and User databases

March 26, 2021 by Nisarg Upadhyay

This is the 2^nd article of Move SQL databases to a different mount point series. In the previous article, Create a new mount point in Linux, I have explained how we can create a mount point in Ubuntu Linux. In this article, I am going to cover the following topics:

Move SQL databases to a different mount point: Create a new mount point in Linux

March 26, 2021 by Nisarg Upadhyay

This article is the 1^st article of the Move SQL Database to different mount point series. This article focuses on creating a new partition using the fdisk command and formatting it with an ext4 file system using the mkfs command.

Automate SQL database backups using Maintenance Plans

February 12, 2021 by Nisarg Upadhyay

In this article, we are going to learn how we can automate the backup of the SQL database using database maintenance plans. Data is one of the most important assets of any organization, and as a database administrator, it is our prime responsibility to protect it. There are various tools available that can be used to back up the data. These tools use state of the art technology to protect the data, and some of them are very costly. Instead of using these costly tools, some organizations prefer to use SQL native backups. These backups of SQL database can be automated by SQL Server Agent Jobs or Windows’ task scheduler.

Fixing Error 601: Could not continue scan with NOLOCK due to data movement

October 21, 2020 by Esat Erkec

Data consistency errors are the nightmares of database administrators (DBAs) and when we notice “Could not continue scan with NOLOCK due to data movement” explanation in any error message, we are sure of getting in trouble. In this article, we will discuss the details of this data consistency problem.

How to backup SQL databases to an FTP server using the SSIS FTP Task

August 26, 2020 by Nisarg Upadhyay

In this article, I am going to explain how we can upload the backup file to an FTP Server using the SSIS FTP task. For the demonstration, I have installed SQL Server 2017 on my workstation and restored the AdventureWorks2017 database.

Backup SQL databases to Azure using the database maintenance plan

August 6, 2020 by Nisarg Upadhyay

In this article, we are going to learn how we can back up the SQL database to Azure using a database maintenance plan. To demonstrate the process, I have restored the AdventureWorks2017 database on my workstation. I have created an Azure container named sqlbackups in my storage account.

Compress and split SQL database backups using WinRar

August 4, 2020 by Nisarg Upadhyay

Recently, we have received a strange request from our customer. They want us to set up a schedule a backup job that generates a backup of SQL database, compress the backup file in multiple compressed archive files (WinRAR files.) We tried to explain to the customer that the SQL Server native backups are capable of compressing the backup file, and it can split a large and compressed backup into multiple backup files. But they insisted us to use the WinRAR software to compress and split the backup.

Monitor the growth of SQL databases using the Default Trace

July 23, 2020 by Nisarg Upadhyay

Monitoring the growth of the SQL Database is one of the essential tasks of the SQL Server DBA. In this article, I am going to explain how we can monitor the growth of the SQL database using the default trace. First, let me explain the default trace in SQL Server.

Automating Azure SQL Database index maintenance using Elastic Job Agents

July 14, 2020 by Nisarg Upadhyay

In this article, I am going to explain how we can automate the index and statistics maintenance of Azure SQL Database using an Elastic Job Agent.

How to automate Table Partitioning in SQL Server

July 7, 2020 by Jignesh Raiyani

In this article, we will demonstrate specific ways to automate table partitioning in SQL Server. This article aims to help you avoid manual table activities of partition maintenance by automating it with T-SQL scripts and SQL Server jobs.

Refresh a SQL Database using the maintenance plan

July 2, 2020 by Nisarg Upadhyay

In this article, I am going to explain how we can refresh a SQL Database using the Maintenance plan. To demonstrate the process, I have configured two virtual machines. I have installed SQL Server 2016 on both. To refresh the SQL database on destination database server, we will generate a copy-only backup of the SQL database on source database server. I have created a network share directory. Both the source and destination server have the read-write permission on it. The backup file will be copied on a shared directory. Once the database is restored to the destination, we will delete the file from the network share directory.

Overview of the Grafana Dashboard with SQL

June 2, 2020 by Aveek Das

In this article, I am going to demonstrate how to set up Grafana Dashboard for real-time analytics or data monitoring purposes. Grafana is an open-source, cross-platform analytics service tool that can be used to fetch data from several data sources, and then the data can be visualized in a much intuitive way in the form of bars and graphs. This is quite a useful feature for the DevOps or the IT team as it can make their job easier to find abnormalities or spikes in the production environment by observing the logs generated.

SQL Server Update Statistics using database maintenance plans

April 13, 2020 by Rajendra Gupta

This article explores SQL Server Update Statistics using the database maintenance plan.

How to determine free space and file size for SQL Server databases

March 9, 2020 by Jignesh Raiyani

In this article, we will discuss the actual database file size and free space in the database file size with the help of monitoring script and shrink solutions. While creating a database initial size of the database files (Data File and Log File) can be defined by us, with the Autogrowth and MAXSIZE parameter. The initial size will be the same as the model database if by chance user forgot to define the initial size parameter while creating a database or do the same intentionally.

Different methods to attach SQL Server MDF files

February 7, 2020 by Nisarg Upadhyay

This article demonstrates different methods to attach SQL Server MDF files. First, let me explain about the database files.

Drop unwanted (secondary) SQL Server transaction log files

January 31, 2020 by Rajendra Gupta

This article explores the use of multiple SQL Server Transaction Log Files and the process of removing the secondary transaction log file.

Move a SQL database using the detach and attach method

January 29, 2020 by Rajendra Gupta

This article explores the process of moving a SQL database using the detach and attach method.

Transferring SQL Logins to the secondary replica of AG using sp_help_revlogin and SSIS Transfer Login tasks

January 14, 2020 by Nisarg Upadhyay

In this article, I am going to explain how we can transfer logins to the secondary replica of the Always On availability group using sp_help_revlogin and Transfer Logins Task of the SQL Server Integration Services package.

How to fix the “Agent XPs disabled” error

December 27, 2019 by Nisarg Upadhyay

This article explains how we can fix SQL Server error “Agent XPs Disabled”. Before we dive into troubleshooting and methods for fixing the error, let me explain about Microsoft SQL Server Agent and the Agent XPs configuration parameter.

Overview of Service Principal Name and Kerberos authentication in SQL Server

July 18, 2019 by Rajendra Gupta

This article gives an overview of Service Principal Name (SPN) for using the Kerberos authentication in SQL Server connections. We use the Kerberos authentication to authenticate windows users securely for providing access to SQL Server.

Optimizing SQL Server index strategies

July 2, 2019 by Bojan Petrovic

Index strategies overview

This article is about techniques for optimizing the SQL Server indexes strategy. It is an appendix of the SQL index overview and strategy article in which I covered different areas like what indexes actually do, how to create them, and I briefly mentioned some index design guidelines. Furthermore, I also presented an example of how to design them by tuning and optimizing queries, so I’ve really tried to cover all but there is always more when it comes to SQL Server indexes.