0

I got some big (1tb data,600gb) databases and I noticed that it has a lot of bad data types like varchar(max), varchar(500) for stuff that only uses 2 or 3 characters, or even a little more.

the process of changing datatypes will affect log size and locks in the tables right?

I'm assuming if I set the database to simple prior to do these changes, I will not face so much issues with space, but for the locks, it's something there theres totally going to have a downtime right?

2
  • 2
    Probably easier to write the whole table to a new one and swap it with sp_rename Mar 16, 2022 at 21:57
  • this might be relevant to your case.
    – Ronaldo
    Mar 21, 2022 at 14:24

3 Answers 3

2

I'm assuming if I set the database to simple prior to do these changes, I will not face so much issues with space

No, that's not true. Each ALTER TABLE statement will act as a single transaction. So even using the SIMPLE recovery model, you could end up with quite a lot of activity in the transaction log - potentially up to 2x the space used by that column if the tests I ran in this Q&A hold true: Fast alter column NVARCHAR(4000) to NVARCHAR(260)

So you'll want to prepare for that in terms of making sure your existing log files are large enough to account for that amount in a single transaction, or grow the log file accordingly, and also make sure you aren't going to fill the disk.

but for the locks, it's something there theres totally going to have a downtime right?

Right. Well, sort of. If the instance is running Enterprise Edition (and your table doesn't run afoul of the myriad restrictions documented here), then you can use ONLINE=ON with the ALTER COLUMN statement to limit the locking.

A Sch-M lock will still be taken at the end of the operation when the column reference is swapped, but it's not nearly as bad as the alternative. The table will be available for normal queries for basically all of the duration of the operation.

If you can't run the ALTER COLUMN online, then yes, you'll need to schedule downtime. The ALTER statement will take out a Sch-M lock on the table for the duration of the operation, which will block all normal read and write operations on the table.

0

You probably don't want to do that "in place", especially if there are multiple columns in multiple tables. Treat this as migration or a major version upgrade; prepare a new database with the desired model, then ETL your data into it in one go. There are ways to minimise downtime if needed, such as logical replication.

0

You can do this in two steps:

First you need to enable compression:

ALTER TABLE dbo.Posts REBUILD 
  WITH (DATA_COMPRESSION = ROW, ONLINE = ON);

Second you can change the data type

SET STATISTICS TIME, IO ON;

BEGIN TRAN
ALTER TABLE dbo.Posts
  ALTER COLUMN OwnerUserId varchar(3);
GO

In the link I provided there is also a video.

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.