MariaDB – MaxScale Set up, Failover, Switchover, Load Balancing

In this blog post, I will show you how to:

  • Setup MariaDB MaxScale
  • Perform Administration and Monitoring
  • Perform a Failover
  • Perform a Switchover

SETUP ENVIRONMENT

First, we will set up the environment. We will be having 1 master and 2 slaves. On top of those, we will have our MariaDB MaxScale server.

To make it easier to setup the environment, you may use the vagrant file below.

# -*- mode: ruby maxscale222
# vi: set ft=ruby :

# Vagrantfile API/syntax version. Don't touch unless you know what you're doing!
VAGRANTFILE_API_VERSION = "2"

Vagrant.configure(VAGRANTFILE_API_VERSION) do |config|

 #: adding ansible stuff
 config.ssh.insert_key = false

 #: maxscale box
 config.vm.define :maxscale do |maxscale|
 maxscale.vm.hostname="maxscale"
 maxscale.vm.box = "domflannery/centos7"
 maxscale.vm.network "public_network"
 end
 
  ######: MASTER / SLAVE SERVERS :######
 #: master, async && semisync replication
 
    config.vm.define :mariadb1, autostart: true do |mariadb1|
	mariadb1.vm.hostname="mariadb1"
	mariadb1.vm.box = "domflannery/centos7"
	mariadb1.vm.network "public_network"
 end

 #: slave01, async && semisync replication

   config.vm.define :mariadb2, autostart: true do |mariadb2|
   mariadb2.vm.hostname="mariadb2"
   mariadb2.vm.box = "domflannery/centos7"
   mariadb2.vm.network "public_network"
 end
 
  #: slave02, async && semisync replication

   config.vm.define :mariadb3, autostart: true do |mariadb3|
   mariadb3.vm.hostname="mariadb3"
   mariadb3.vm.box = "domflannery/centos7"
   mariadb3.vm.network "public_network"
 end
 
end

I chose public networks that is set automatically (through DHCP) for demo purposes. The VMs IP addresses will be in the same range as the host’s IP address.

Once all the virtual boxes are up, we will login to each of them to get the assigned public IP address. First, login to each server using local IP (127.0.0.1) and the respective port numbers (refer to the chart below). The login and password will be both vagrant

HostnameIP addressPort
maxscale127.0.0.12222
mariadb1127.0.0.12200
mariadb2127.0.0.12201
mariadb3127.0.0.12202

login: vagrant
password: vagrant

I got the port numbers above by looking at the logs after I issued vagrant up. Below is an example.

Get the assigned IP address using the command below (ifconfig will not work in this version of centos)

ip a

After getting the public IP addresses of each server, configure them in your putty. Give each connection sensible names. (Also, don’t include the /24 of the IP address. )

Preview(opens in a new tab)

INSTALL MARIADB

Install MariaDB in mariadb1, mariadb2, and mariadb3. I will be using MariaDB 10.4 on Centos7. If you want to use a different flavor of Linux, then refer to this link to get the correct repositories info.

Let’s create a repo file.

vim MariaDB.repo
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.4/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

Move the file to /etc/yum.repos.d/

sudo mv -vi MariaDB.repo /etc/yum.repos.d/

Once the file is in place, we are ready to install MariaDB (The capitalization of the package names is important).

sudo yum install MariaDB-server MariaDB-client -y

SETUP MARIADB REPLICATION (USING GTID)

Get the master and replicas ready.

Login to mariadb1 as root, and edit the my.cnf

vi /etc/my.cnf

Delete the existing lines; then enter the following lines below (or just copy paste them).

[client-server]
!includedir /etc/my.cnf.d

[client]
port = 3306
socket = /var/lib/mysql/mysql.sock

[mysqld]
user = mysql
# pid-file = /var/run/mysqld/mysqld.pid

# Network
port = 3306
socket = /var/lib/mysql/mysql.sock
bind-address = 0.0.0.0

# Data directory
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp

# Connections
max_connections = 100
connect_timeout = 5
wait_timeout = 600
max_allowed_packet = 16M
thread_cache_size = 128
sort_buffer_size = 4M
bulk_insert_buffer_size = 16M
tmp_table_size = 32M

# Query Cache
query_cache_limit = 128K
query_cache_size = 64M
query_cache_type = 1
log_warnings = 2

# Slow log
slow_query_log
slow_query_log_file = /var/lib/mysql/mariadb-slow.log
long_query_time = 1
log_slow_verbosity = query_plan,explain

# Replication
server-id=1
gtid_strict_mode=1
log-bin = /var/lib/mysql/bin-mariadb.log
expire-logs-days=8
sync_binlog = 1
slave_compressed_protocol
transaction-isolation = READ-COMMITTED
binlog_format = row


# Error log
log_error = mariadb_error.log

# Deadlocks
innodb_print_all_deadlocks = ON

# Performance Schema
performance_schema=on

# User Statistics
userstat = 1

# Denies naming the connection
skip_name_resolve

Do the same (edit config file) in mariadb2 and mariadb3, but change the server_id to a different value for each server.

After editing the my.cnf file on all 3 servers, start mariadb service

systemctl start mariadb

STARTING UP REPLICATION

Create a user in the master, and grant “replication slave” to the user. The replica is going to use this user connection to read the binary logs on the master and then put those into the relay logs on the replica.

CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* to 'repl'@'%';

In a real-world production environment, you would create a user that can only connect from the IP address of your slaves, but for the sake of demonstration, we’ll just use the wild card character (%)

Point your slaves to the master by executed the commands below.

change master to
master_host='192.168.1.220',
master_user='repl',
master_password='password',
master_use_gtid=slave_pos;

Run the command below to start the slave

start slave;

Check slave status

show slave status \G

Both Slave_IO_Running and Slave_SQL_Running parameters should be Yes.

Execute on Master (only) to grant privileges to maxuser. This user will be used by maxscale.

grant reload, show databases, super, replication slave, replication client on *.* to 'maxuser'@'localhost' identified by 'P@$$w0rd';
grant reload, super, replication slave, replication client ON *.* TO 'repl'@'%';
grant select on mysql.user to 'maxuser'@'localhost';
grant select on mysql.db to 'maxuser'@'localhost';
grant select on mysql.tables_priv to 'maxuser'@'localhost';
grant select on mysql.roles_mapping to 'maxuser'@'localhost';
grant select on mysql.columns_priv to 'maxuser'@'localhost';
grant select on mysql.proxies_priv to 'maxuser'@'localhost';
flush privileges;"

grant reload, show databases, super, replication slave, replication client on *.* to 'maxuser'@'' identified by 'P@$$w0rd';
grant reload, super, replication slave, replication client ON *.* TO 'repuser'@'%';
grant select on mysql.user to 'maxuser'@'';
grant select on mysql.db to 'maxuser'@'';
grant select on mysql.tables_priv to 'maxuser'@'';
grant select on mysql.roles_mapping to 'maxuser'@'';
grant select on mysql.columns_priv to 'maxuser'@'';
grant select on mysql.proxies_priv to 'maxuser'@'';
flush privileges;"

SET UP MAXSCALE

Create maxscale ID

useradd -m -d /home/maxscale -u 6603 -s /bin/bash -c "MaxScale Admin Functional ID" -U maxscale

Set password for maxscale ID

echo 'password' | passwd --stdin maxscale

Execute MariaDB Package Repository Setup

The command below will setup 3 different repositories in a single repository configuration file. The repositories are the following:

  • MariaDB Repository
  • MariaDB MaxScale Repository
  • MariaDB Tools Repository

You may read about the setup and usage of the command from this link.

curl -LsS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash

Install MariaDB Client

yum install MariaDB-common MariaDB-compat MariaDB-client -y

Install MaxScale

yum install maxscale -y

Generate Encrypted Password.

You can read about encrypting passwords from this link.

# Generate .secrets file
rm -f /var/lib/maxscale/.secrets
maxkeys /var/lib/maxscale

# Generate encrypted password
ls -altr /var/lib/maxscale/.secrets
chown maxscale:maxscale /var/lib/maxscale/.secrets

maxpasswd  /var/lib/maxscale/ password
42535F5E5FBC62D068623CD69F62CC00E231737DFC7D51FBEEC0D81D33D7E99A

maxpasswd  /var/lib/maxscale/ password2
A650AB2DC74FE3F609FE6D16BED6A38D757184B532B2A54FA9FCF63C36627B73

Configure maxscale.cnf file

vim /etc/maxscale.cnf.d/maxscale.cnf
[mariadb1]
type=server
address=192.168.1.220
port=3306
protocol=mariadbbackend

[mariadb2]
type=server
address=192.168.1.133
port=3306
protocol=mariadbbackend

[mariadb3]
type=server
address=192.168.1.208
port=3306
protocol=mariadbbackend

[MySQLMonitor]
type=monitor
module=mariadbmon
servers=mariadb1,mariadb2,mariadb3
user=repl
password=42535F5E5FBC62D068623CD69F62CC00E231737DFC7D51FBEEC0D81D33D7E99A
replication_user=repl
replication_password=42535F5E5FBC62D068623CD69F62CC00E231737DFC7D51FBEEC0D81D33D7E99A
monitor_interval=5000
auto_failover=true
auto_rejoin=true
enforce_read_only_slaves=1
backend_connect_timeout=10
backend_read_timeout=10
backend_write_timeout=10

[ReadWriteSplitService]
type=service
router=readwritesplit
servers=mariadb1,mariadb2,mariadb3
user=maxuser
password=6C620E5D01B63D33023313BCD5DD6EC4F60B619F2B7F4B9D18E034428BEDA178
master_failure_mode=fail_instantly
max_slave_replication_lag=1

[ReadWriteMasterService]
type=service
router=readconnroute
servers=mariadb1,mariadb2,mariadb3
user=maxuser
password=A650AB2DC74FE3F609FE6D16BED6A38D757184B532B2A54FA9FCF63C36627B73
router_options=master

[ReadOnlySlaveService]
type=service
router=readconnroute
servers=mariadb1,mariadb2,mariadb3
user=maxuser
password=A650AB2DC74FE3F609FE6D16BED6A38D757184B532B2A54FA9FCF63C36627B73
router_options=slave

[ReadWriteSplitListener]
type=listener
service=ReadWriteSplitService
protocol=mariadbclient
address=0.0.0.0
port=4006

[ReadWriteMasterListener]
type=listener
service=ReadWriteMasterService
protocol=mariadbclient
address=0.0.0.0
port=4007

[ReadOnlySlaveListener]
type=listener
service=ReadOnlySlaveService
protocol=mariadbclient
address=0.0.0.0
port=4008

Change permissions and owner of config files

chown maxscale:maxscale /etc/maxscale.cnf.d/maxscale.cnf
chmod 640 /etc/maxscale.cnf.d/maxscale.cnf

You may read about the MariaDB Monitor, ReadConnRoute, and ReadWriteSplit from MariaDB’s website.

Verify that MaxScale service is enabled.

systemctl is-enabled maxscale.service

Start MaxScale service.

systemctl start maxscale.service

You check the maxscale log at the path below.

tail -100 /var/log/maxscale/maxscale.log

MONITORING MAXSCALE

In your maxscale server, run the command below to check list of servers being monitored.

maxctrl list servers

FAILOVER TEST

Let’s monitor the servers continuously every second. I suggest you open a new session of your maxscale server, and run the command below.

watch -n1 maxctrl list servers

Go to mariadb1, then stop MariaDB service.

systemctl stop mariadb

After a few seconds, you will see that mariadb2 has been promoted to master.

Let’s bring up mariadb1.

systemctl start mariadb

Monitor the servers, and you will see that they are all up and running.

SWITCHOVER

Let’s make mariadb1 server the master again. We will be doing a switchover, but before we proceed, we have to verify the monitor that we are going to use.

maxctrl list monitors

In our case, it is MariaDB-Monitor.

Now, we are ready to do the switchover. Here is the command to perform it.

maxctrl call command mariadbmon switchover MariaDB-Monitor mariadb1 mariadb2

Here is the explanation of the command above.

call command indicates that it is a module command that is to be invoked.
mariadbmon indicates the module whose command we want to invoke.
switchover is the command we want to invoke.
MariaDB-Monitor is the first argument to the command, the name of the monitor as specified in the configuration file.
mariadb1 the name of the server we want to make into master
mariadb2 current master.

We now have successfully switched over the master role back to mariadb1

LOAD BALANCING TEST

In this demo, we will be using the maxscale server as our client.

In the previous section, we’ve already installed MariaDB client. Verify that is installed successfully.

rpm -qa | grep -i MariaDB

In mariadb1 (master), create an app user.

CREATE USER 'app_user'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'app_user'@'%' WITH GRANT OPTION;

To connect to our servers via maxscale, we use the connection string below. Essentially, we have to indicate the maxscale IP address. My maxscale IP address happens to be 192.168.1.87. We also have to include the Read/Write listener port that is 4006 as indicated in the maxscale config file (/etc/maxscale.cnf)

Execute the command below in your maxscale server.

mysql -u app_user -p -h192.168.1.87 -P4006

To know which server you are connect to, run the following statement.

SELECT @@hostname;

If we keep on executing this statement repeatedly, we will notice that we will be connected to our slaves: mariadb2 and mariadb3. This is load balancing at work. MaxScale knows that we are executing a read statement; hence it is redirecting the traffic to our read-only replicas.

Once we start a transaction, MaxScale now recognizes that we are about to update a record; hence the connection will be redirected to the master node only.

START TRANSACTION;

The connection will remain in master until we commit the transaction.

COMMIT;

After we commit the transaction, the connection will again be redirected to the read-only replicas.

This is also called the Read Write Splitting.

MaxScale analyze statements, and send where appropriate. Write statements to master; and Read statements to some slaves.

That is all for this blog post.

Cheers!

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter

SQL SERVER – Memory Settings Formula (Best Practice)

Here are the formulas for setting memory in SQL Server.

  • Single-instance dedicated Max server memory formula: physical memory – 4G (or 10% whichever is greater to leave some GBs for the OS).
  • Single-instance shared Max server memory formula: physical memory – 4G ( or 10% whichever is greater to leave some GBs for the OS ) – (memory for other services).

Use the sys.configurations view to query server configuration options including Max server memory(MB) and Min server memory (MB).

SELECT
	SERVERPROPERTY('InstanceName') AS myinstance,
	*
FROM	
	sys.configurations
WHERE name IN
	(
		'max server memory (MB)',
		'min server memory (MB)'
	)
GO

Use the sys.dm_os_sys_info view to query the total amount of physical memory in the server, number of schedulers, last server startup time, and if SQL Server runs in a virtual or physical environment

SELECT physical_memory_kb / 1024 AS Physical_Memory_MB
FROM sys.dm_os_sys_info

Use the sp_configure system stored procedure to set server configuration options to prevent potential UI data entry errors.

sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'max server memory (MB)', <Value>
GO
RECONFIGURE
GO

Cheers!

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter

SQL SERVER – Database Instant File Initialization

Instant file initialization is a setting that can make a positive impact at instance level on SQL Server I/O performance.

All I/O operations are done via the OS and the storage layer. For example:

  • Claiming disk space for creating a new database file
  • Autogrowing a database file
  • Restoring a database from backup

Whenever SQL Server needs to allocate space for these operations, it will first fill the space it needs with zeros. This zeroing phase, or zero initialization, can take a significant amount of time, depending on storage performance and size of the disk space to zeroed. Therefore, it can adversely impact file creation, autogrow, and database restore performance.

How to Turn-On Instant File Initialization

SQL Server doesn’t have a setting or checkbox to enable Instant File Initialization.

Instead, it detects whether or not the service account it’s running under has the Perform Volume Maintenance Tasks permission in the Windows Security Policy. You can find and edit this policy by running secpol.msc (Local Security Policy) in Windows.

  1. Expand the Local Policies Folder.
  2. Click on User Rights Assignment.
  3. Go down to the “Perform Volume Maintenance Tasks” option and double click it.
  4. Add your SQL Server Service account and click OK out of the dialog.

After granting this policy to the service account, you’ll need to restart the SQL Server service in order for the policy to take effect and for SQL Server to start using Initial File Initialization.

Cheers!

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter

SQL SERVER – TSQL Script to Change the Compatibility Level of all Databases

I’ve just migrated 8 of my demo databases from SQL 2014 to SQL 2017, and I want to change the compatibility level (from 120 to 140) of all those databases via TSQL. Here is a script to do just that.

BEGIN
	SET NOCOUNT ON;

	DECLARE @rc INT = 0, @i INT = 1, @v_cmd NVARCHAR(MAX);

	DECLARE @cmds TABLE(RowNum INT NOT NULL IDENTITY(1,1) PRIMARY KEY, Cmd NVARCHAR(MAX) NOT NULL);

	INSERT INTO @cmds
	SELECT 'ALTER DATABASE ' + name + ' SET COMPATIBILITY_LEVEL = 140' FROM sys.databases 
	WHERE compatibility_level = 120

	SET @rc = @@ROWCOUNT;
	RAISERROR('The COMPATIBILITY_LEVEL of %d databases will be changed to 140', 10, 1, @rc) WITH NOWAIT;

	WHILE @i <= @rc
	BEGIN
		SELECT @v_cmd = cmd FROM @cmds WHERE RowNum = @i;

		PRINT @v_cmd;
		EXEC(@v_cmd);

		SET @i += 1;
	END;
END



Cheers!

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter

SQL SERVER – How to Fix a Database in Recovery Pending State

A SQL database will be marked with different states if one or more of its core files are in a inconsistent state. The type of state will depend on how serious the damage is. Here are some of the states:

  • Online – occurs when one of the data files is damaged during an execution of a query or some other operation.
  • Suspect – occurs when a database cannot be recovered during startup
  • Recovery Pending – occurs when SQL Server knows that recovery of the database is to be done, but something is obstructing before starting it. This state is different from the suspect state as it cannot be declared that database recovery will fail, because it has not started yet
Database marked as ‘Recovery Pending’

Here a script to check the current state of all your databases in a SQL instance.

SELECT name, state_desc from sys.databases
GO

There are several reasons why a database is marked as ‘Recovery Pending

  • The database was not cleanly shutdown. There could be one or more transactions active at that time, resulting in the deletion of active transaction log file.
  • To overcome server performance issues, A user could have tried moving the log files to a new drive, but in the process, corrupted the files instead.
  • Insufficient memory or disk space that prevented database recovery from getting started.

Let’s Fix It

ALTER DATABASE TestDB SET EMERGENCY;
GO
ALTER DATABASE TestDB set single_user
GO
DBCC CHECKDB (OptimalTestDb, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;
GO
ALTER DATABASE OptimalTestDb set multi_user
GO

Cheers!

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter

SQL SERVER – Resolving database in SUSPECT mode

SQL Server runs in different modes, and can be in a specific state at a given time.

The modes are:

  • ONLINE
  • OFFLINE
  • RESTORING
  • RECOVERING
  • RECOVERY PENDING
  • SUSPECT
  • EMERGENCY

In this blog post, we will talk about how to deal with a database that is in SUSPECT mode.

Here are some of the reasons why SQL Server marks a Database as Suspect:

  • Database file corruption
  • Unavailable database files
  • Improper shutdown of SQL Server database Server
  • Failure to open the device where the data or the log file resides
  • SQL Server crash

How to Recover SQL Server from SUSPECT Mode

1.Turn off the suspect flag on the database and set it to EMERGENCY

EXEC sp_resetstatus 'YourDBName'; 
ALTER DATABASE YourDBName SET EMERGENCY

2. Perform a consistency check

DBCC CHECKDB YourDBName

3. Bring the database into the Single User mode and roll back the previous transactions

ALTER DATABASE YourDBName SET SINGLE_USER WITH ROLLBACK IMMEDIATE

4. Take a complete backup of the database

5. Attempt the Database Repair allowing some data loss

DBCC CHECKDB ('YourDBName', REPAIR_ALLOW_DATA_LOSS)

6. Bring the database into the Multi-User mode

ALTER DATABASE YourDBName SET MULTI_USER

7. Refresh the database server and verify the connectivity of the database

Cheers!

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter

SQL SERVER – Understanding ACID

Concurrent operation has big implications for data integrity. In order to guarantee integrity, a relational database system must comply with the ACID principle. This is normally implemented through transactions.

In ACID, A stands for Atomicity, and that means when data is modified by some transaction, either all of its data modifications are performed or none of them are performed.

The C in ACID stands for Consistency. When a data modification transaction completes, all data must be in a consistent state. All constraints must be satisfied, and all internal structures must be correct.

I is all about Isolation. Modifications made by one transaction must be isolated from those made by other concurrent transactions. This also implies that if you redo the same operations with the same starting data, the results will always be the same.

The D in ACID stands for Durability. When a transaction is complete the results are stored permanently in the system and persist even if a system failure occurs. Now in order to get you set up, so that you can follow along with all the demos, let me introduce you to the tools I’ll be using.

Cheers!

SQL SERVER – Deadlock on ALTER DATABASE to MULTI_USER mode

I was trying to alter the database to MULTI_USER mode, but faced this error.

Msg 1205, Level 13, State 68, Line 10 Transaction (Process ID 79) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. Msg 5069, Level 16, State 1, Line 10 ALTER DATABASE statement failed.

Instinctively, you would run sp_who2, and then try to kill the processes connected to that particular database. But what if the SPID is less than 50? You tried to kill it, but you get the the following error.

Msg 6107, Level 14, State 1, Line 1
Only user processes can be killed.

Basically, it says that you cannot kill a system process.

So, to resolve this problem, here is the secret Ninja move. Simply execute the command below.

SET DEADLOCK_PRIORITY HIGH 
ALTER DATABASE YourDBName SET MULTI_USER WITH ROLLBACK IMMEDIATE

Cheers!

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter

SQL SERVER – Merge Table Partitions

Here are the scripts that I use to merge table partitions.

First, let’s check the partition information of a particular table (Indicate the table name in the

SELECT  pf.name AS pf_name ,
            ps.name AS partition_scheme_name ,
            p.partition_number ,
            ds.name AS partition_filegroup ,
            OBJECT_NAME(si.object_id) AS object_name ,
            rv.value AS range_value ,
            SUM(CASE WHEN si.index_id IN ( 1, 0 ) THEN p.rows
                     ELSE 0
                END) AS num_rows
    FROM    sys.destination_data_spaces AS dds
            JOIN sys.data_spaces AS ds ON dds.data_space_id = ds.data_space_id
            JOIN sys.partition_schemes AS ps ON dds.partition_scheme_id = ps.data_space_id
            JOIN sys.partition_functions AS pf ON ps.function_id = pf.function_id
            LEFT JOIN sys.partition_range_values AS rv ON pf.function_id = rv.function_id
                                                          AND dds.destination_id = CASE pf.boundary_value_on_right
                                                                                     WHEN 0 THEN rv.boundary_id
                                                                                     ELSE rv.boundary_id + 1
                                                                                   END
            LEFT JOIN sys.indexes AS si ON dds.partition_scheme_id = si.data_space_id
            LEFT JOIN sys.partitions AS p ON si.object_id = p.object_id
                                             AND si.index_id = p.index_id
                                             AND dds.destination_id = p.partition_number
            LEFT JOIN sys.dm_db_partition_stats AS dbps ON p.object_id = dbps.object_id
                                                           AND p.partition_id = dbps.partition_id
	WHERE pf.name = 'TableName'
    GROUP BY ds.name ,
            p.partition_number ,
            pf.name ,
            pf.type_desc ,
            pf.fanout ,
            pf.boundary_value_on_right ,
            ps.name ,
            si.object_id ,
            rv.value
	ORDER BY 
		p.partition_number
GO

Below is a screenshot of a partitioned table information in one of our environments.

Next, let’s merge the empty partitions. I will merge the empty partitions on the left side only, because I will be needing the partitions on the right side for future growth of my database

Below is the script to merge empty partitions

ALTER PARTITION FUNCTION functionName()
MERGE RANGE(boundaryPoint)


Cheers!

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter

SQL SERVER – Get Table Partition Information

Here is a script that retrieves table partition information

SELECT
	pf.name as PartitionFunction,
	ds.name AS PartitionScheme, 
	p.partition_number AS PartitionNumber, 
	CASE pf.boundary_value_on_right WHEN 1 THEN 'RIGHT' ELSE 'LEFT' END AS PartitionFunctionRange, 
	CONVERT(BIGINT, prv_left.value) AS LowerBoundaryValue, 
	CONVERT(BIGINT, prv_right.value) AS UpperBoundaryValue, 
	fg.name AS FileGroupName,
	p.[row_count] as TotalRows,
	CONVERT(DECIMAL(12,2), p.reserved_page_count*8/1024.0) as ReservedSpaceMB,
	CONVERT(DECIMAL(12,2), p.used_page_count*8/1024.0) as UsedSpaceMB
FROM
	sys.dm_db_partition_stats AS p (NOLOCK)
	INNER JOIN sys.indexes AS i (NOLOCK) ON i.[object_id] = p.[object_id] AND i.index_id = p.index_id
	INNER JOIN sys.data_spaces AS ds (NOLOCK) ON ds.data_space_id = i.data_space_id
	INNER JOIN sys.partition_schemes AS ps (NOLOCK) ON ps.data_space_id = ds.data_space_id
	INNER JOIN sys.partition_functions AS pf (NOLOCK) ON pf.function_id = ps.function_id
	INNER JOIN sys.destination_data_spaces AS dds2 (NOLOCK) ON dds2.partition_scheme_id = ps.data_space_id AND dds2.destination_id = p.partition_number
	INNER JOIN sys.filegroups AS fg (NOLOCK) ON fg.data_space_id = dds2.data_space_id
	LEFT OUTER JOIN sys.partition_range_values AS prv_left (NOLOCK) ON ps.function_id = prv_left.function_id AND prv_left.boundary_id = p.partition_number - 1
	LEFT OUTER JOIN sys.partition_range_values AS prv_right (NOLOCK) ON ps.function_id = prv_right.function_id AND prv_right.boundary_id = p.partition_number
WHERE
	OBJECTPROPERTY(p.[object_id], 'IsMSShipped') = 0 AND
	-- OBJECT_NAME(p.[object_id]) = 'partitioned_table' AND
	p.index_id=1
ORDER BY p.partition_number;	

Cheers!