Size of MDF file is not getting updated after adding transactional log file(s) in SQL 2022

Question

I am working on migration of SQL Server from SQL 2016 to SQL 2022.

I have a process where after database is restored (to "Standby / Read-Only" state) from Full tape (from a third vendor), I am supposed to add each hourly log file (bak) into this "Standby / Read-Only" database.

I was trying to run the T-SQL code that was working in SQL 2016 (for last 4 years) to this new environment (SQL 2022), and first error message that I got was on this part:

 EXEC master.sys.xp_cmdshell @cmd 

So, I Googled it, and ran this code:

 EXEC sp_configure 'show advanced options', 1
 GO
 RECONFIGURE
 GO
 EXEC sp_configure 'xp_cmdshell', 1
 GO
 RECONFIGURE
 GO

When I ran the code, there seems to be no error message, but I do not see log file of database increases the size.

I am putting my whole T-SQL code here for reference:

USE Master; 
GO 
SET NOCOUNT ON 
-- 1 - Variable declaration 
DECLARE @dbName sysname
DECLARE @fileName sysname
DECLARE @standby sysname
DECLARE @backupPath NVARCHAR(500) 
DECLARE @cmd NVARCHAR(500) 
DECLARE @fileList TABLE (backupFile NVARCHAR(255)) 
DECLARE @lastFullBackup NVARCHAR(500) 
DECLARE @lastDiffBackup NVARCHAR(500) 
DECLARE @backupFile NVARCHAR(500) 
-- 2 - Initialize variables 
SET @dbName = 'us_xxxx_multi_replica1' 
SET @fileName = 'LOG_us_xxxx_multi_replica' 
SET @backupPath = 'F:\Yesterday\' 
SET @standby = 'F:\us_xxxx_multi_replica_RollbackUndo_2024年09月17日_19-33-23.bak'
-- 3 - get list of files 
SET @cmd = 'DIR /b "' + @backupPath + '"'
INSERT INTO @fileList(backupFile) 
EXEC master.sys.xp_cmdshell @cmd 
-- 5 - check for log backups 
DECLARE backupFiles CURSOR FOR 
 SELECT backupFile 
 FROM @fileList 
 WHERE backupFile LIKE '%.bak' 
 AND backupFile LIKE @fileName + '%' 
OPEN backupFiles 
-- Loop through all the files for the database 
FETCH NEXT FROM backupFiles INTO @backupFile 
WHILE @@FETCH_STATUS = 0 
BEGIN 
 SET @cmd = 'RESTORE LOG [' + @dbName + '] FROM DISK = ''' 
 + @backupPath + @backupFile + ''' WITH STANDBY = ''' + @standby + ''''
 FETCH NEXT FROM backupFiles INTO @backupFile 
EXEC (@cmd)
END 
CLOSE backupFiles 
DEALLOCATE backupFiles 

Bottom is result example after processing in SQL 2022 (that I am having an issue):

 Processed 0 pages for database 'us_xxxx_multi_replica', file 'us_template_pccmulti_rrdb_replica' on file 1.
 Processed 952 pages for database 'us_xxx_multi_replica', file 'us_template_pccmulti_rrdb_replica_log' on file 1.
 System objects could not be updated in database 'us_xxxx_multi_replica' because it is read-only.
 System objects could not be updated in database 'us_xxxx_multi_replica' because it is read-only.
 RESTORE LOG successfully processed 952 pages in 1.845 seconds (4.029 MB/sec).

Bottom is result example after I ran in SQL 2016 (where there is no issue):

Processed 2336 pages for database 'us_xxx_multi_replica', file 'us_template_pccmulti_rrdb_replica_log' on file 1. 
System objects could not be updated in database 'us_xxx_multi_replica' because it is read-only. 
System objects could not be updated in database 'us_xxx_multi_replica' because it is read-only. 
RESTORE LOG successfully processed 2336 pages in 0.531 seconds (34.361 MB/sec). 
Processed 0 pages for database 'us_xxx_multi_replica', file 'us_template_pccmulti_rrdb_replica' on file 1.

What might be the issue?

BTW, when SQL 2022 was installed on this VM, there was SQL 2019 already installed (came with the VM). Our IT installed SQL 2022 manually, so I am wondering if there is some type of permission /configuration issue that was not set up correctly.

Because when I ran this T-SQL code in SQL 2019, I did not encountered the issue of

EXEC master.sys.xp_cmdshell @cmd 

from the beginning.

Update:

1. I did not find the reason why I had to take care of the issue with 'xp_cmdshell' issue in SQL 2022 (but not in SQL 2019), but it is working fine.

2. I realized that ldf file in SQL 2016 did not show increase in size, but only in increase in mdf file. So, I guess I do not have to worry about no change in ldf file size.

3. I was able to see the differences by checking the data itself.

• Sean Gallardy: I don't see anywhere in the post where you give an error or show what issue you're running into. Could you please point it out?
• Java: @SeanGallardy Thank you for your comment. The issue is that log file did not increase. Let me try again by running log file one by one.
• Sean Gallardy: You can't import data to a database that is read only. Also, please explain what you mean by "nothing was changed" and "log file did not increase". Please update the question with examples of before/after and expected outcomes.
• Sean Gallardy: In the OP your restores are working perfectly fine, but the database hasn't become read/write so it hasn't gone through the upgrade to the latest version. Other than that, the restores are working just fine. There are no issues.
• Craig: Just because you are restoring a transaction log does not inherently mean that the size of any underlying data files will increase. Both the existing data file(s) and transaction log file(s) of the database very likely have some "free" space available in them, so an increase in the amount of data in the database will consume that "free" space before SQL decides that it needs to increase actual physical files on the disk.

Answer 1

OP is you, the "original poster" (this was asked in the comment section).

It seems that you expect the mdf or ldf file (the database file) to increase and/or decrease in size as you restore log backups. Is that the case?

Also, you seem to experience that this happened on an earlier version of SQL Server, but using the same process it doesn't happen in a more recent version of SQL Server?

Furthermore, it seems like you feel that it is a problem that the size of the database file doesn't increase/decrease as you restore log backups?

If I understand your issue, as per my description above:

What you see is nothing out of the ordinary. Most likely, in your "earlier environment", the source SQL Server did a frequent shrink followed by expansion of the database file, and this was carried over when you restore the log backups.

But likely the DBA where the backups are coming from has learned that frequent grow and shrink of the database file isn't a very good thing. I.e., keep it at the size it has to be. That is why your log backups from the more recent system don't change the database file size as you restore them.

• Java: I realized that on SQL 2016, LDF file did not increase from yesterday, so I guess it only happens on MDF file.
• Java: I realized this morning, that even in SQL 2016, the size of MDF did not grow over night. I guess it is ok for MDF to not show changing in size.