0

My database has an MDF file of 56 GB and an LDF file of 263 GB, recovery model is setting to Full.

So, I would like to shrink the LDF file because we are out of space. What specific size should I shrink my LDF file to?

enter image description here

I've read some posts that do not recommend shrinking the database because it might make it difficult/impossible to restore the database later. However, what if I did a full backup (.bak file) then I restore the Database? Would that still be a concern?

And my final question is shrinking mdf file will delete important data on mdf file?? What specific size should I shrink my mdf file to?

Regards.

2
  • Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer.
    – Community Bot
    Commented Jul 26, 2023 at 20:20
  • 2
    What's the state of you log file? do you perform transaction log backups? Commented Jul 26, 2023 at 20:40

1 Answer 1

1

First of all, check why your log file is growing

SELECT name, log_reuse_wait_desc FROM sys.databases;

If it said the reason is LOG_BACKUP, run log backup for your database before shrinking.

I've read some posts that do not recommend shrinking the database because it might make it difficult/impossible to restore the database later.

Shrinking data file (mdf) is not recommended because it's resource consuming, causes fragmentation and pretty often useless if the database is going to grow. Yes, it increase size of log and diff backups.

However, what if I did a full backup (.bak file) then I restore the Database? Would that still be a concern?

I don't think it's relevant here, however full backup before shrinking will make differential backup smaller.

is shrinking mdf file will delete important data on mdf file?

Nope.

Why do you mentioned shrinking mdf file while you have problem with the log (HEATSM.ldf)? Don't shrink whole database, shrink only log file with DBCC SHRINKFILE.

https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql?view=sql-server-ver16

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.