re-indexing and storage space

[ppyrzynski]

Hi, 

I'm in the middle of FNMS upgrade from 2022R1 to 2023R1. During the process I decided to re-index the FNMSCompliance DB. 

Re-indexing took over 23 hours. And resulted "Query completed with errors". The Messages window in SSMS shows multiple lines this:
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
then there is an error 
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 's'.
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string ')'.
And then the lines again:
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Completion time: 2023-08-02T13:24:18.8593646+02:00

So it looks to me like on overall the re-indexation went well, right?

Another question is related to Data drive space. Before running the script I had 665 GB Free space out of total 1,5 TB. Now I'm at just 137BG free space. I would think that after re-indexing there might be some increase in space used but not this much! Is it normal behavior? Can the space be somehow reclaimed?

edit: forgot to mention that the indexes has not been rebuilt for at least 3 years. 

Best, 
Paweł

[ChrisG]

I'm not sure what SQL statements were executed that resulted in the "Unclosed quotation mark after the character string" message, but a consequence of that could be that at least part of the index rebuild didn't happen.

Rebuilding indexes will generally result in increased storage space usage, as new pages need to be allocated in the database to hold the rebuilt indexes. But it is likely that you will find the database files can be shrunk to reclaim space that was used by the old indexes after a rebuild is complete. See the following Stack Exchange thread for some more discussion on this: Rebuild Index not freeing up space.

[ppyrzynski]

Thanks Chris. 
Based on what I read there it's better to leave it like this. Meaning not to force the shrink. I think I have some time now before physical drive free space will start decreasing now. The data file itself is half empty at the moment. 
Do you know if there is any way to track that statement that caused the error? 

[ChrisG]

You might be able to get insight into what causes the error message by running a SQL Profiler trace while rebuilding the indexes (if you rebuild them again).

[erwin_lindemann]

I guess you have been using the 'ReIndexAll.sql' SQL script provided with the FNMS database migration scripts, even in the current FNMS 2023 R1 release?

This SQL script uses the 'DBCC DBREINDEX' command and executes the command in loop over all tables on database.

The 'ReIndexAll.sql' script should generally work, but the 'DBCC DBREINDEX' command is deprecated. Instead of using 'DBCC DBREINDEX', using ALTER INDEX together with the REBUILD option is generally recommend.

The error message you are seeing may result from some 'strange' table names in the database that you are running the script on. The enhanced version of the script below adds some square brackets around the table names that may eventually allow you to work around the issue.

SET ARITHABORT ON 
SET QUOTED_IDENTIFIER ON 

DECLARE @cmd VARCHAR(512)

DECLARE c CURSOR FOR
SELECT	'DBCC DBREINDEX (''['+sysobjects.name+']'')'
FROM	sysobjects WHERE xtype = 'U'

OPEN c
FETCH NEXT FROM c INTO @cmd
WHILE @@FETCH_STATUS = 0
BEGIN
	EXEC(@cmd)
	FETCH NEXT FROM c INTO @cmd
END
CLOSE c

Give it a try 😀.

[ppyrzynski]

Thanks, man. 

So 'modern' version of the script should look like below, right?

SET ARITHABORT ON 
SET QUOTED_IDENTIFIER ON 

DECLARE @cmd VARCHAR(512)

DECLARE c CURSOR FOR
SELECT 'ALTER INDEX ALL ON ''['+sysobjects.name+']'' REBUILD'
FROM	sysobjects WHERE xtype = 'U'

OPEN c
FETCH NEXT FROM c INTO @cmd
WHILE @@FETCH_STATUS = 0
BEGIN
	EXEC(@cmd)
	FETCH NEXT FROM c INTO @cmd
END
CLOSE c

or did I messed up something?

I also have a question regarding running this again: last time I did, it increased the physical storage space used on the drive by 700GB. Do I need another 700 GB or more now if I'm to run it again? If so, maybe shrinking the DB prior would be a good idea? Or shrinking is never a good idea?

[alexander_frit · Accepted solution]

@ppyrzynski 
In our environment we are using a slightly customized script as described under following link under section "D. Use sys.dm_db_index_physical_stats in a script to rebuild or reorganize indexes"
sys.dm_db_index_physical_stats (Transact-SQL) - SQL Server | Microsoft Learn

[ppyrzynski]

Thanks for this Alex, I will surely use that one. But another question stays: Do I need to request storage space increase before trying that....

[erwin_lindemann]

Script is correct. This should be able to deal for example with SQL Server tables where the name contains a blank.

Additional disk space should not be required.

SQL Server is increasingly becoming better with maintaining index consistency and database sizes. There are also high quality open source scripts like the 'SQL Server Maintenance Solution' from Ola Hallengren that you might be interested in.