Resizing SQL Server Database from single data file to multiple Data file

-> Requirement is to move data from a database that has 1 data file to 4 data files.

-> Existing setup,

SQL Server : SQL Server 2017
Database Size : 2 TB
Number of Data file(s) : 1
Data file size : 1.8 TB
Log file size : 200 GB

-> Solution requirement,

Number of Data files : 4
Data File 1 Size : 650 GB
Data File 2 Size : 650 GB
Data File 3 Size : 650 GB
Data File 4 Size : 650 GB
Log file size : 200 GB

-> Below tasks were undertaken on a test server initially.

-> Production database was restored on a test server. Additional 3 data drives of size 700 GB each added.

-> Database recovery model was changed from Full to simple.

-> Added close to 3 additional data files of size 650 GB on the 3 additional drives added.

-> Executed below command on the primary data file. This command basically moves data from all user objects from primary data file to additional data files that were added. This will result in all user objects to be moved from Primary data file to secondary data files added.

USE [DATABASE_NAME]
GO
DBCC SHRINKFILE (N'PRIMARY_DATA_FILE' , EMPTYFILE)
GO

-> The above command will be very slow. In my case it took close to 13 hours to complete. While the above command was executing I used below code to check the progress,


if convert(varchar(20),SERVERPROPERTY('productversion')) like '8%'
SELECT [name], fileid, filename, [size]/128.0 AS 'Total Size in MB',
[size]/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB',
CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Used Space In MB',
(100-((([size]/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0)/([size]/128.0))*100.0)) AS 'percentage Used'
FROM sysfiles
else
SELECT @@servername as 'ServerName',db_name() as DBName,[name], file_id, physical_name, [size]/128 AS
'Total Size in MB',
[size]/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB',
CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Used Space In MB',
(100-((([size]/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0)/([size]/128.0))*100.0)) AS 'percentage Used'
FROM sys.database_files
go

-> Reviewing the output from above query, specifically “Used Space in MB” and “Percentage Used” will provide us the details whether the process is progressing.

-> I stopped the resizing query when the primary data file’s “Used space in MB” reached 471,860 MB.

-> I am stopped this in-between just to make sure I am not moving all data from primary data file and then resulting in too much new data being inserted to primary data file later.

-> Shrinked the primary data file from 1.8 TB to 650 GB.

-> There are instances where shrink can take several hours if resizing is not completed fully. In my case it completed in 3 minutes using below command,


USE [DATABASE_NAME]
GO
DBCC SHRINKFILE (N'PRIMARY_DATA_FILE' , 665600, TRUNCATEONLY)
GO

-> In case shrinking the primary data file is very slow, you should allow the resizing to complete fully. You will get below error message when it completes,

Msg 1119, Level 16, State 1, Line 20
Removing IAM page (3:5940460) failed because someone else is using the object that this IAM page belongs to.

-> You can get more details about above error message from this article.

-> Reissue the shrink command and it will complete soon.

-> Problem with this is that you will experience more writes on the primary data file than other 3 data files and this can result in sub-optimal performance.

-> Perform a reindex on the database to ensure you remove any fragmentation as a result of resizing.

-> Changed the recovery model for the database from Simple to Full and performed a full backup.

-> This method worked out well for me in the test environment.

-> This method was replicated on our production environment after 6 months. It had issues while performing on the production environment due to below reasons,

  1. The scripts to increase the data file in the production environment was a copy of the script used in Test environment.
  2. The data growth in production was not taken into account in 6 months and the additional data file size added did not cope up with the additional data added.

-> Due to above issue, behavior on Production database server was as below,

  1. When resizing was started and we were checking the progress using the above query provided. We found that Additional data files “Used Space in MB” was increasing, and “Available Space In MB” decreasing.
  2. But in primary data file “Used Space in MB” and “Available Space In MB” did not change, it was static. Expected result should be that “Available Space In MB” should be increasing and “Used Space in MB” should be decreasing.
  3. It was stopped after 10 hours. We then realized that after the shrink with empty file command was terminated, primary data file “Used Space in MB” started coming down and “Available Space In MB” increasing. This took 1 more hour and were able to see some data moved from Primary data file to secondary data file.
  4. We then increased the additional data file size appropriately and then started executing the command. It moved the required amount of data and it was working as expected. I stopped the resize at a value where data files were having same amount of data and performed an index optimize.

-> In my case I was lucky that we took downtime for a whole weekend.

-> The whole process will not be possible on a production environment in case there is not downtime allowed.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.


			

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified)

-> Client advised that they are trying to connect to a SQL Server named instance and getting below error,

Blog90_1

TITLE: Connect to Server
——————————
Cannot connect to JBAG1\IN01.
——————————
ADDITIONAL INFORMATION:
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476
——————————
BUTTONS:
OK
——————————

-> Client advised that they were able to connect to a default instance that is installed on the same server.

-> Client also advised that there were no recent network changes in the database server.

-> I wanted to make sure if SQL Services was online. In my case it was online.

-> Then I checked if Remote connection was enabled from SQL Server Instance Properties -> Connection -> “Allow remote connections to this server”. It was checked.

-> Verified if “SQL Server Browser Services” was started. It was started.

-> Checked what protocols were enabled. In my case TCP\IP and shared memory was enabled. Checked further if TCP\IP is configured to use static or dynamic port.

TCP\IP was configured with a static port of 51233.

-> Verified if there was an “inbound Rule” to allow TCP port 51233 in the firewall. It was already created.

-> Checked if there were any bad alias created in SQL Server Configuration manager or cliconfg.exe. There were none.

-> I tried executing Telnet and understood that port 51233 is open. I also got the port scan output from the client and it showed that the port was open.

-> I tried to connect to the SQL Server named instance using Servername,port instead of Servername\Instancename. The connection was successful.

-> The connection doesn’t work only if I use Servername\Instancename. I checked further and then realized that someone have changed Hide Instance to Yes in configuration manager.

Blog90_2

Blog90_3

Hide Instance : SQL Server uses the SQL Server Browser service to enumerate instances of the Database Engine installed on the computer. This enables client applications to browse for a server, and helps clients distinguish between multiple instances of the Database Engine on the same computer. Setting Hide Instance to YES will prevent the SQL Server Browser service from exposing an instance of the Database Engine to client computers that try to locate the instance by using the Browse button. Setting Hide Instance to Yes is a security best practice, but can cause issue as detailed in this post.

-> I advised this to the client and changed “Hide Instance” Setting to No and the connection went through fine. Please note that this change doesn’t require a SQL Server restart.

-> Client advised that one of their DBA’s changed this recently after a security assessment of their SQL Server instance using SQL Server vulnerability assessment report from SQL Server Management studio. Advised them that they can perform below to connect to SQL Server named instance using “Hide Instance” set to Yes,

1) Change the application connection string from Servername\Hostname to Servername,Port.

2) Create a ALIAS in SQL Server configuration manager or cliconfg.exe.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Removing IAM page %S_PGID failed because someone else is using the object that this IAM page belongs to

-> I was executing DBCC SHRINKFILE as an one time operation on a huge data file to move its data into 4 additional data files using below command,

USE [Financedb]
GO

DBCC SHRINKFILE (N'Finance_Data' , EMPTYFILE)
GO

-> I received below error when executing above command,

Msg 1119, Level 16, State 1, Line 20
Removing IAM page (3:5940460) failed because someone else is using the object that this IAM page belongs to.

-> I executed DBCC CHECKDB and there were no errors,

DBCC results for ‘Financedb‘.
.
.
CHECKDB found 0 allocation errors and 0 consistency errors in database Financedb.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

-> This is not a corruption issue. I restarted SQL Services and executed below command and it ran through.

USE [Financedb]
GO
DBCC SHRINKFILE (N'Finance_Data' , EMPTYFILE)
GO

Msg 2555, Level 16, State 1, Line 20
Cannot move all contents of file “Finance_Data” to other places to complete the emptyfile operation.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

-> Above message is due to the fact that System objects cannot be moved.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.