My Database went from 120 GB to 150 GB after Normalizing? Is there a Compact command for SQL Server? (3 Viewers)

[ions]

Hello MS Access Community,

I normalized tblManifestHeaders by removing the Generator Address fields and creating a tblGenerators lookup table. I repeated the same for Receiver Address.

I also changed all fields from nvarchar(250) to varchar(250) in tblManifestHeaders because this is a US dataset. I was expecting the database size to reduce with these optimizations but instead it grew from 120 GB to 150 GB.

Is there a compact feature in SQL Server? Any other recommendations?

Thank you

 

[The_Doc_Man]

I'm limited in my knowledge of SQL Server, but...

Have you tried this DB Console Command: DBCC SHRINKDATABASE

Supposedly that will cause SQL Server to reclaim allocated but unused space, the nearest action equivalent to the "Compact" Part of a Compact & Repair.

If that doesn't work, I must defer to my colleagues who have far more experience on the subject.

 

[xavier.batlle]

Look at:
https://learn.microsoft.com/en-us/s...bases/shrink-a-database?view=sql-server-ver17

 

[ions]

Thank you for your responses.

I ran DBCC SHRINKDATABASE (UMA); prior to posting. It doesn't reduce the database. I just re-ran it again... it ran for 30 minutes and again didn't reduce the database. See below screenshot.

I might have to re-import the entire dataset into a new Database and use Staging Tables. I have been altering the same table (no staging) up to this point doing operations such as the below on the table. Note I preceded the below with ALTER DATABASE UMA SET RECOVERY BULK_LOGGED;

ALTER TABLE dbo.tblManifestHeaders ALTER COLUMN [GENERATOR_ID]     varchar(250) NOT NULL;
ALTER TABLE dbo.tblManifestHeaders ALTER COLUMN [GENERATOR_NAME]   varchar(250) NOT NULL;
ALTER TABLE dbo.tblManifestHeaders ALTER COLUMN [DES_FACILITY_ID]  varchar(250) NOT NULL;
ALTER TABLE dbo.tblManifestHeaders ALTER COLUMN [DES_FACILITY_NAME] varchar(250) NOT NULL;

Still I don't quite understand why this is happening? How could normalizing and optimizing make the database larger and then I am not able to compact it? Below is ChatGPT's explanation

Thank you for any other advice.

 

[GPGeorge]

My first reaction would be a shrug of the shoulders. Unless you are critically short of space on the drive where the SQL Server instance is installed, it should not matter. It's not like Access where we have a hard 2GB file size limit. Even if you are using SQL Server Express, it's not that relevant since the file size limit for SSE databases is 10GB.

I would also like to see the size of the individual dbf and ldf files for this database. You did a lot of refactoring. It would not be out of the question, therefore, that the log file is a significant part of the overall growth.

For example, here are the statistics on a very small database on my local computer. Note that the size is reported as 16.00 MB, with an allocated space of 4.38 GB remaining.

However, the log file for this database is one-half of the total size! Only 8 GB of the 16 are data.

Compare your current database size to the relative sizes of the data in the mdf file and the logs in the ldf file. Given the recent restructuring, there's a good chance the ldf file accounts for much of the overall growth you see. And you can recover the space in the log file, if so. You've already tried to shrink the database, of course, but see if paying close attention to the log file can also improve things.

 

[AccessBlaster]

Or shrink, it's been a while.

 

[ions]

Hi George,

The Log file is 112.82 GB making up more than 2/3 of the database size which is 155 GB. Recommendations? I actually don't need Logging at all. I make my own backups and this is read only reporting database.

Thank you

 

[ions]

I shrunk the log file with the below code. The database is now 43 GB which I can live with. Thank you George for recommending to look into the log file.

Can I turn off logging all together? It slows things down and I don't need it while massaging reporting data.

Thank you.

-- 1) Switch to SIMPLE recovery (no log backups needed)

ALTER DATABASE UMA SET RECOVERY SIMPLE;

GO


-- 2) Force log truncation

CHECKPOINT;

GO


-- 3) Shrink the log file (e.g., 100 MB target size)

USE UMA;

DBCC SHRINKFILE (UMA_log, 100);   -- size in MB

GO

 

[BlueSpruce]

I also changed all fields from nvarchar(250) to varchar(250) in tblManifestHeaders because this is a US dataset. I

You should've stayed with nvarchar:

What is the difference between varchar and nvarchar?

Is it just that nvarchar supports multibyte characters? If that is the case, is there really any point, other than storage concerns, to using varchars?

stackoverflow.com

And why are you still usin transaction logging when I told you several days ago to disable it and delete fields and records you don't need for your application?

 

[ions]

You should've stayed with nvarchar:

What is the difference between varchar and nvarchar?

Is it just that nvarchar supports multibyte characters? If that is the case, is there really any point, other than storage concerns, to using varchars?

stackoverflow.com

Why are you still usin transaction logging when I told you several days ago to disable it and delete fields and records you don't need for your application?

I was advised to go with varchar by someone to reduce the size of the DB. After reading the StackOverflow article I'll stick with nvarchar.

I had BULK_LOGGED Recovery on. I thought that was the right choice vs. Full Recovery. I didn't know about SIMPLE Recovery until today.

Thanks BlueSpruce.