SQL-Server database file size problem

疯狂奶牛

I have a database running for six months now
The database file reaches 17G
There are only more than 400,000 records in the largest table

Only a few tables have more than 100,000 data

What is the problem causing my database to become so big?

我是杀人狂

Log file
LZ can compress logs or delete logs after making multiple copies

疯狂奶牛

Not a log file. The size of the data file (TEST_Data.MDF) reaches 17G

fanfan1980

Are there text, ntext, image type fields?

疯狂奶牛

Have,
With these fields

ivanrain

I also want to know why, because I have encountered similar problems. A record is 144Byte, and a maximum of 500,000 records are generated per month. How can a data file of more than 10 gigabytes be generated in a year? The log file is limited in size, 100M, and all should have nothing to do with the log file.

fish0592

A large number of inserted data and updated records will cause the database to grow larger and larger. It is recommended to maintain the index regularly. If it is too busy, you can shrink the database regularly.

ryujilang

It may be that the index is not maintained well, but it is still strange. My database has almost 1 million records, and none of them exceeds 2G. Does your record have a huge amount of data? Or someone maliciously attacked on the Internet, for each record Injected a huge amount of data?

mczhu136

Theoretical feasible suggestions:

Execute backup log database with no_log first
Then dbcc shrinkdatabase (database)

Please be cautious in actual operation. Log information will be truncated and execution is slow.