1

I am approaching the 10 GB limit that Express has on the primary database file.

The main problem appears to be some fixed length char(500) columns that are never near that length.

I have two tables with about 2 million rows between them. These two tables add up to about 8 GB of data with the remainder being spread over another 20 tables or so. These two tables each have 2 char(500) columns.

I am testing a way to convert these columns to varchar(500) and recover the trailing spaces.

I tried this:

Alter Table Test_MAILBACKUP_RECIPIENTS
Alter Column SMTP_address varchar(500)
GO
Alter Table Test_MAILBACKUP_RECIPIENTS
Alter Column EXDN_address varchar(500)

This quickly changed the column type but obviously didn’t recover the space.

The only way I can see to do this successfully is to:

  1. Create a new table in tempdb with the varchar(500) columns,

  2. Copy the information into the temp table trimming off the trailing spaces,

  3. Drop the real table,

  4. Recreate the real table with the new varchar(500) columns,

  5. Copy the information back.

I’m open to other ideas here as I’ll have to take my application offline while this process completes?

Another thing I’m curious about is the primary key identity column. This table has a Primary Key field set as an identity. I know I have to use Set Identity_Insert on to allow the records to be inserted into the table and turn it off when I’m finished.

How will recreating a table affect new records being inserted into the table after I’m finished. Or is this just “Microsoft Magic” and I don’t need to worry about it?

Lypyrhythm
  • 324
  • 2
  • 13
David P
  • 411
  • 7
  • 21

2 Answers2

0

OK I did a SQL backup, disabled the application and tried my script anyway. I was shocked that it ran in under 2 minutes on my slow old server.

I re-enabled my application and it still works. (Yay)

Looking at the reported size of the table now it went from 1.4GB to 126Mb! So at least that has bought me some time. (I have circled the Data size in KB)

Before enter image description here

After enter image description here

My next problem is the MailBackup table which also has two char(500) columns.

It is shown as 6.7GB. I can't use the same approach as this table contains a FileStream column which has around 190gb of data and tempdb does not support FleStream as far as I know. Looks like this might be worth a new question.

David P
  • 411
  • 7
  • 21
0

The problem with you initial approach was that you converted the columns to varchar but didn't trim the existing whitespace (which is maintained after the conversion), after changing the data type of the columns to you should do:

update Test_MAILBACKUP_RECIPIENTS set
  SMTP_address=rtrim(SMTP_address), EXDN_address=rtrim(EXDN_address)

This will eliminate all trailing spaces from you table, but note that the actual disk size will be the same, as SQL Server don't shrink automatically database files, it just mark that space as unused and available for other data.

You can use this script from another question to see the actual space used by data in the DB files:

Get size of all tables in database

Usually shrinking a database is not recommended but when there is a lot of difference between used space and disk size you can do it with dbcc shrinkdatabase:

dbcc shrinkdatabase (YourDatabase, 10) -- leaving 10% of free space for new data
Alberto Martinez
  • 2,620
  • 4
  • 25
  • 28
  • Hi Alberto, That seems like exactly what I need to do with the PP4_MailBackup table. I was later thinking I should be able to do what you suggested but didn't know if I could write a column back onto itself like that. With such a large table it is hard for me to copy test data around to try stuff out. I won't need to shrink the database as I'm not short of disk space but this should solve my 10Gb limit for good. I haven't tried it yet but I'll mark your answer as the solution. Thanks! – David P Oct 20 '17 at 05:13
  • If you want to make a test first and have enough space for the temp database make a `begin transaction`, then perform the update, and check with a select that everything is OK, if not just `rollback` (or `commit` to make the changes permanent). I use a transaction most of the time to perform this type of updates just to be safe (if you perform several updates you can use `select @@trancount` to check if you already have an active transaction). – Alberto Martinez Oct 20 '17 at 19:10
  • That is a good idea. I need to learn how to do this kind of stuff. I should have enough space in the temp database unless it needs to store my filestream column in there. I'm assuming the tempdb will also have a 10Gb limit same as the primary database. – David P Oct 23 '17 at 07:17
  • I'm not sure about the size of tempdb but probably is also limited to 10GB. Regarding the [filestream](https://technet.microsoft.com/en-us/library/bb933993(v=sql.105).aspx) column it shouldn't be a problem because the binary data of that columns is stored directly in the filesystem apart from regular data (even the transactions are managed in coordination with the filesystem using the transactional capabilities of NTFS if I recall correctly) . – Alberto Martinez Oct 23 '17 at 19:49