Sql Server 2019 Express Edition database backup file size

Darryl Hoar 141

I have a custom created c# application that backs up the Sql database.

It uses the Backup database databasename to file = 'backup file path and name' command.

It recently has been command timing out and failing to backup. Yes I can modify in the application to change the command timeout value, but I can't do that right now. Running the command in SSMS takes 32 seconds and completes.

I looked at the database mdf file size and its 990,504 KB and the ldf is 150,456 KB, so its not a massive database. The backup file created is 1,989,344KB, which is almost double the size of the mdf file. Since I want to speed up the backup (make it sub 30 seconds to complete) I was looking at reducing the size. What I can't figure out is why the .bak file is 1GB bigger than the mdf size.

any ideas or help greatly appreciated.

2 answers

Erland Sommarskog 107.2K Reputation points

2023-05-16T21:31:29.5133333+00:00

Are you always backing up to the same file path? If you do this, you are appending a backup to the file every time, unless you specify the INIT option. This could explain why that backup file is so much bigger than the database.

Another possibility is that you have FILESTREAM tables - the files in these tables are not in the mdf/ldf, but they are in the backup.
Please sign in to rate this answer.
Darryl Hoar 141 Reputation points

2023-05-16T22:07:01.35+00:00

The backup command is given a unique file name for the .bak file. So Backup myDatabase to disk = 'C:\mybaks\uniquefilename.bak'

I have no idea what filestream tables are so I'm going to have to go read up. I didn't intentionally create then we I created the database.

Darryl Hoar 141 Reputation points

2023-05-16T22:09:13.4733333+00:00

The backup command is provided a unique filename for the .bak file. So Backup myDatabase To File = 'C:\mydbbaks\uniquefilename.bak'. I have no idea what FileStream tables are, and I didn't create any when I made the database.

Erland Sommarskog 107.2K Reputation points

2023-05-17T21:24:21.8633333+00:00

It is unlikely that you would be using FILESTREAM inadvertently. Possibly you could have a FileTable, but I would not expect so. But just to clear things out, can you post a screenshot of the output from

SELECT * FROM yourdb.sys.database_files

This table is fairly wide, but we only need to see the output up to the growth column.

There is something mysterious here. The backup should not be bigger than the database, and even more importantly, backing up such a small database, should not take 32 seconds. Least of all if you are backing up to your C drive.

You can try adding WITH COMPRESSION on the backup command. This will reduce the size of the backup, but takes more CPU. But if the delay is in I/O - which is likely - this may help you to come below the 30-second limit.

Darryl Hoar 141 Reputation points

2023-05-17T21:38:25.22+00:00

here is the result of the requested query.

Darryl Hoar 141 Reputation points

2023-05-17T21:40:31.8433333+00:00

database.PNG

Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Erland Sommarskog 107.2K Reputation points

2023-05-17T21:45:41.8+00:00

The log file is quite a lot bigger than you said, 4.3 GB, that is four times the size of the data file. That is not exactly normal. Are you backing up the transaction log regularly? Alternatively, if you don't care about losing data since the most recent full backup, set the database in simple recovery.

Darryl Hoar 141 Reputation points

2023-05-17T21:49:33.4566667+00:00

The database is in simple mode. And we do the backup command as previously stated. Do I need to do a different backup of just the log file? If so, how?

Erland Sommarskog 107.2K Reputation points

2023-05-17T21:55:33.9866667+00:00

If you are in simple recovery, there is no need for log backup. It is not even possible.

What is the output of this query:

SELECT * FROM YourDB.sys.dm_db_log_space_usage

By the way, have you tried WITH COMPRESSION yet?

Darryl Hoar 141 Reputation points

2023-05-17T22:09:16.67+00:00

No I haven't yet tried compression for the backup. Trying to reduce the log file first. the results of the command are used_log_space_in_bytes 73461760 log_space_inbytessincelastbackup 73728

Erland Sommarskog 107.2K Reputation points

2023-05-18T09:05:15.9866667+00:00

That big difference between the numbers suggests that you have an open transaction somewhere or something else that prevents log truncation. What does

SELECT log_reuse_wait_desc FROM sys.databases WHERE name = 'YourDatabase'

return?

Darryl Hoar 141 Reputation points

2023-05-18T18:43:26.5666667+00:00

It returns a value of NOTHING

Erland Sommarskog 107.2K Reputation points

2023-05-18T21:06:53.4533333+00:00

That's good. You should be able to shrink your log file. Generally, we advice against shrinking, because it's pointless to shrink something that will grow again. It's only if the log has grown due to some exceptional event that shrinking is advisable. What has happened in this case, we don't know. But I can't see how you could perform an operation in a database in simple recovery that results in a log file that is four times the size of the data file.

So run this command:

USE YourDB go DBCC SHRINKFILE(YourDB_log, 150)

To shrink the log file to 150 MB. (Run sp_helpdb to find the actual name of the log file.)

However, I'm not sure that this will have any notice any difference in backup time.

Darryl Hoar 141 Reputation points

2023-05-19T19:29:52.3433333+00:00

Thanks for your help. BTW Sql Server 2019 express edition does not have backup compression. Still trying to figure out why its taking 32 seconds to backup a sub 1GB database. The Shrink worked great and now the log file is tiny.

Erland Sommarskog 107.2K Reputation points

2023-05-19T21:07:23.8266667+00:00

Try running the BACKUP command directly from SSMS to verify that it still takes 32 seconds.

Sorry for my ignorance of backup compression not being available in Express.
Sign in to comment
AniyaTang-MSFT 12,421 Reputation points Microsoft Vendor

2023-05-17T07:13:44.0466667+00:00

Hi @Darryl Hoar

As Erland said, are you appending the new backup to the same file?

You can check this link: https://stackoverflow.com/questions/4085402/why-is-sql-server-backup-so-much-larger-than-the-db-files.

Best regards,

Aniya
Please sign in to rate this answer.
Darryl Hoar 141 Reputation points

2023-05-17T12:32:45.16+00:00

If every time the backup database mydatabase to file = 'C:\mybaks\uniquename.bak' is run, the file name is unique, how can it be appending to an existing .bak file?

Jingyang Li 5,891 Reputation points

2023-05-18T20:44:48.7666667+00:00

BACKUP DATABASE databasename TO DISK

= N'C:\mybaks\uniquename.bak'

WITH FORMAT, INIT, COMPRESSION
Sign in to comment

Share via

Sql Server 2019 Express Edition database backup file size

2 answers