7

I have a big problem with the files size on my SQL Server database.

Let me put everything into context:

On an empty database I'm creating a new table:

CREATE TABLE mytable
(
    id int identity(1,1) NOT NULL, 
    description varchar(255) NOT NULL
);

=> on disk, I have datafile=8192K and logfile=8192k

I'm inserting 1'000'000 rows into my table like this:

INSERT INTO mytable(description)
    SELECT TOP (1000000) 
        'test test test test test test test test test test test'
    FROM sys.all_objects AS o1
    CROSS JOIN sys.all_objects AS o2
    CROSS JOIN sys.all_objects AS o3;

=> on disk, I have datafile=73M and logfile=376M

I'm altering the description column, changing its datatype to nvarchar:

ALTER TABLE mytable 
    ALTER COLUMN description nvarchar(255) NOT NULL;

=> on disk, I have datafile=204M and logfile=1.3G

I'm executing these ALTER SQL commands several times:

ALTER TABLE mytable 
    ALTER COLUMN description varchar(255) NOT NULL;

ALTER TABLE mytable 
    ALTER COLUMN description nvarchar(255) NOT NULL;

=> on disk, I have datafile=816M and logfile=3G

Question 1: is it normal that the database grows like this when I'm switching between 2 column's types?

Question 2: applying a Database Shrink, the log file is reset (returns to 8192K), but the datafile size still remains to 816M. Is it normal?

Are there options in a SQL Server database to do an automatic shrink or something like this? This in a test database, but in production I'm starting with a 150G database so I cannot imagine the database size after some ALTER.

I'm using SQL Server 2019.

Thanks in advance for your help.

7
  • 1
    What is the recovery mode? Full or Simple?
    – Nick.Mc
    Jul 7, 2021 at 8:06
  • Hi Nick, I've tested with both (full and simple). Examples here are using simple recovery mode. Thanks.
    – Interel
    Jul 7, 2021 at 8:33
  • That's normal behavior. There are a lot of blogs and information sources explaining how the log (and data) file works. Your first stop is probably to read about simple recovery mode. Note that in a production system, 3G is not considered much space anymore, and you would never force a database shrink, because it's just going to grow again anyway. Also note that in simple mode, the transaction log file is "recycled". It gets reused, it won't grow to an enormous size unless it can't commit data quickly enough.
    – Nick.Mc
    Jul 7, 2021 at 8:56
  • Thank you, Nick, for your quick answer. What I find hard to explain is why changing only a column type (from varchar to nvarchar and back on) will continue to grow the database size? After 10 ALTER TABLE of this type I'm expecting the database size to be more or less the same as original database size (starting from a varchar column and ending on a varchar column without any change in column's values). Thanks again for your help
    – Interel
    Jul 7, 2021 at 9:48
  • Maybe use this to see how the space is being used. stackoverflow.com/questions/3927231/…
    – Nick.Mc
    Jul 7, 2021 at 10:24

3 Answers 3

4

Changing the column type from varchar to nvarchar changes how the data is stored physically on the disks. Nvarchar being unicode needs two bytes to store each character (more or less, there are exceptions but they are out of scope here) and varchar is stored as regular 8-bit data (1 byte per character). When you change the data type the data will be copied to a new page as it has to be rewritten in the new form and therefore the database will grow.

Since your table is a heap (has no clustered index) the allocation algorithm will not update the GAM but move the data to a new page and create a pointer on the old one. To reclaim the space you will need to execute ALTER TABLE mytable REBUILD or create a clustered index on the table after which you can shrink the database file.

Paul Randall has blogged a lot about SQL Server disk structures. If you are interested. Those are highly reccomended especially the Anatomy of a page post and Hugo Kornelis can tell you the story of the Table scan from hell

One thing you will discover reading this is that it is not really a good idea to shrink your database files. You can set an option on the database to auto shrink it ALTER DATABASE Empty SET AUTO_SHRINK ON but this is really not a good idea!

If you want to make an educated guess at the size of the conversion you can calculate how big the current varchar columns will be after conversion, this will not calculate the increased space indexes on these columns will take and will only be based on the max length of the column but not on the size of the data stored in it.

select 
sum(c.max_length) as current_max_length_in_varchar,
sum(c.max_length)*2 as required_max_length_in_nvarchar
from sys.tables t
inner join sys.columns c
on c.[object_id] = t.[object_id]
where t.type = 'U'
and c.system_type_id = 167 -- Type 167 is varchar, see sys.types
1
  • Thank you for this explanation, I'll check it out.
    – Interel
    Jul 7, 2021 at 12:06
2

The problem area is the continuous data growths. The log is pretty explainable, so I'll leave that out of the below discussion.

You are left with "ghost data" because of the type change. Note that I quote the word ghost, since there is a different more formal meaning of ghost in SQL Server, and that is not what I'm referring to here.

DBCC CLEANTABLE can be your friend. https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-cleantable-transact-sql I did a test and reproduced your numbers. Thanks for the good repro, btw. Throwing a CLEANTABLE in between sorted the problem area: the data size keep growing.

It is possible that a shrink also removes this "ghost data", but you don't want to do shrink. (Do you have a constructor and a building team removing a room in your house just because you're not currently occupying it, to then have them build up that room again next time you want to read your newspaper in that room? ;-) )

I think that ALTER TABLE REBUILD might also help, but that needs free space because of the rebuild (basically copying the table, so to speak).

I don't think that the fact that the table is a heap is the problem in this case, although heaps tables has other problems, of which some has similar symptoms. I did a test with the table not being a heap (creating a clustered PK on the table) and I had the same data growth issues for the data as when it was a heap.

Below is my repro script with some numbers. I use my own sp_tableinfo to check table size (https://karaszi.com/sptableinfo-list-tables-and-space-usage).

USE master
GO

DROP DATABASE IF EXISTS x
GO

CREATE DATABASE X
GO

USE x

CREATE TABLE mytable
(
    id int identity(1,1) NOT NULL, 
    description varchar(255) NOT NULL
);

INSERT INTO mytable(description)
    SELECT TOP (1000000) 
        'test test test test test test test test test test test'
    FROM sys.all_objects AS o1
    CROSS JOIN sys.all_objects AS o2
    CROSS JOIN sys.all_objects AS o3;


EXEC sp_tableinfo
--                   : db =  73MB, log = 0.4gb, table = 68MB

ALTER TABLE mytable ALTER COLUMN description nvarchar(255) NOT NULL;

EXEC sp_tableinfo
-- Without cleantable: db = 204MB, log = 1.3gb, table = 195MB

DBCC CLEANTABLE(x, mytable)
GO
ALTER TABLE mytable ALTER COLUMN description varchar(255) NOT NULL;


EXEC sp_tableinfo 
-- Without cleantable: db = 270MB, log = 1.3gb, table = 250MB
-- With    cleantable: db = 204MB, log = 1.3gb, table = 196MB

DBCC CLEANTABLE(x, mytable)
GO
ALTER TABLE mytable ALTER COLUMN description nvarchar(255) NOT NULL;


EXEC sp_tableinfo
-- Without cleantable: db = 401MB, log = 1.7gb, table = 361MB
-- With    cleantable: db = 204MB, log = 1.5gb, table = 196MB

DBCC CLEANTABLE(x, mytable)

EXEC sp_tableinfo
-- With    cleantable: db = 204MB, log = 1.5gb, table = 196MB

ALTER TABLE mytable REBUILD

EXEC sp_tableinfo
-- Without cleantable: db = 532MB, log = 1.7gb, table = 122MB
-- With    cleantable: db = 335MB, log = 1.5gb, table = 122MB
2
  • This is the professional way I was hoping for when I suggested my workaround. Thanks! One question: is there any way to verify if I have tables in my database that could benefit from DBCC CLEANTABLE? The OP knew about the table because he was the one "causing the trouble", but what if someone has been doing it before I became the admin of the database?
    – Ronaldo
    Jul 13, 2021 at 13:30
  • 1
    Thanks Ronaldo. Interesting question. That would be a useful script. Maybe there is, if you dive into the various type of meta data (catalog views and/or dmvs). But I won't be able to dig into that at the moment, since we're on a bicycle-trip, sans computer. 😊 Jul 13, 2021 at 19:01
1

What I find hard to explain is why changing only a column type (from varchar to nvarchar and back on) will continue to grow the database size?

Log File

It's all about the process happening under the hood for SQL Server to accomplish your request. Let's say you issue a DELETE and removes rows from your table. Those rows are removed from your table, but the whole process is registered on the log, therefore, a DELETE isn't just removing data, it's also generating new data. And there are some other special situations that can also generate more data during the ALTER COLUMN as you can see on the ALTER TABLE doc:

Online alter column has similar requirements, restrictions, and functionality as online index rebuild, which includes:

  • An existing column being altered requires twice the space allocation, for the original column and for the newly created hidden column.

Here's a practical example to have an idea of the process happening in your case with the use of the undocumented function fn_dblog:

CREATE DATABASE MyLab;
GO

USE MyLab;
GO

CREATE TABLE mytable
(
    id int identity(1,1) NOT NULL, 
    description varchar(255) NOT NULL
);

INSERT INTO mytable(description)
    SELECT TOP (10) 
        'test test test test test test test test test test test'
    FROM sys.all_objects AS o1
    CROSS JOIN sys.all_objects AS o2
    CROSS JOIN sys.all_objects AS o3;

ALTER TABLE mytable 
ALTER COLUMN description nvarchar(255) NOT NULL;

SELECT [Current LSN], [Operation], [SPID], [Transaction Name], [Begin Time], [End Time] 
FROM fn_dblog(NULL, NULL);

I reduced the number of inserted rows to make our life easier. Look for the INSERT and the ALTER TABLE on the Transaction Name column. You'll notice that the INSERT generated around 37 rows of log. The ALTER TABLE generated 36. Pretty much the same, so the log size could somehow double in this process. Also, verify the Operation column to better understand what happened in each step.

If you keep repeating that change many times, your log file can get bigger than your data file.

Data File

I still couldn't figure out what's the data left behind for each time you execute the ALTER TABLE, but I notice that if you use the Import Data / Export Data Wizard and copy the table, the resulting table has the size expected for the amount of rows in the table. In an emergency you could use it to reduce the data used for the table. There must be a more professional way (edit: see Tibor Karaszi's answer) of doing it, but I couldn't find it yet.

3
  • Yes, thanks Ronarldo. You're right about it, lthe log file can double indeed, but I see solutions (shrink or other) to reduce the log size, but for now I'm worried about the data file size that I cannot reduce it. Thanks for the fn_dblog tip, didn't know its existence.
    – Interel
    Jul 7, 2021 at 12:09
  • @Interel see the edit for my answer related to the data file.
    – Ronaldo
    Jul 7, 2021 at 12:48
  • Ronaldo, I saw your answer, yes, abour data file. There is this option (export / import) but is not a production solution for me, because exporting and importing database can take maybe hours, time that our services will be down :(. Thanks for your reply
    – Interel
    Jul 7, 2021 at 13:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.