3

I have a .mdf file that is 10gb. This is the max file size. How can I reduce the file size? I do not need the data, I just need to be able to write new data to the database.

I have deleted a ton of rows, but the file size has not changed at all.

Any help would be appreciated!

Also, my log file grew by about 50gb when I deleted the rows from the .mdf. How do I make that smaller as well?

  • 1
    Look up shrink on msdn.microsoft.com – paparazzo Jul 19 '16 at 23:33
  • @Paparazzi I have. Most people seem to think it is a terrible thing to use, and will hurt the db in the long run. – blackandorangecat Jul 19 '16 at 23:34
  • 2
    What is your other option? Right now the db his hurt in the short run. – paparazzo Jul 19 '16 at 23:35
  • 2
    @blackandorangecat, after you delete data, the space inside the database file becomes unallocated and available for reuse by other objects. Run EXEC sp_spaceused and examine the unallocated space. It may be appropriate to execute DBCC SHRINKFILE to shink the log file if that growth was an anomaly. – Dan Guzman Jul 20 '16 at 1:09
  • 1
    Do you have express edition ?. Only shrinking can reduce file size of mdf file, deleting data would only create free space – Shanky Jul 20 '16 at 5:35
2

You have to shrink database so the deleting the data takes place Right Click on your database , Tasks => Shrink => Files In shrink action choose the second Option and enter your file ideal size You're done :)

0

If you used the DELETE command for removing data, this gets logged in the transaction log. If you had used TRUNCATE instead, this would have been much more transaction log friendly.

DBCC SHRINKFILE (DataFile, 10) GO DBCC SHRINKFILE (LogFile,1) GO

0

Shrink is bad for a production database because it messes up the existing indexes. Not a big deal if this is a test or dev database. That said I'm going to break this into two pieces.

Data file (mdf)

Think of this like a cup. You can pour data into it (inserts) or pour data out of it (deletes). Adding new data only requires additional space when it's actually full of data. Try using this:

USE db_name
GO
EXEC sp_spaceused;
GO

You'll get back two datasets with one row each and the column you are most interested here is the last one on the second row. How much space is unused. If you have a 10gb mdf and aren't only actually using 1gb then you have 9gb of room to add data to. There is no reason to shrink the db unless you need to give space back to the file system. Now if your database is actually still full then you either need to find more data to delete, or expand the size of your data file.

Log file (ldf)

This is actually going to be a bit more complicated. The log file contains information required to undo/redo transactions performed on the database. Now if the database is in SIMPLE recovery mode then you are saying "I only really need to undo a transaction if the transaction is canceled in mid process". In other words if you cancel a transaction it will roll back. In this case your log file grew in a one time large operation and if you won't be doing that again it's not a big deal to shrink that single file.

But let's say your database is in FULL recovery mode (and based on the size I'd say this is more likely). At this point the undo/redo information will be saved in the log until you run a log backup (simplifying slightly). This is so that when you do a restore you can use the log file to restore your database to a point in time. Once you have done the log backup this space can be marked for re-use and new log information will overwrite the old. Until that log backup is taken though your log file is going to get full, and start growing to make space for more log information.

The reason I expect you are in FULL recovery mode is that your log file is 5x your data file. We typically see something like that when the database is in FULL recovery and no log backups are being taken. If this is the case here is what I would do.

  • Decide if you care about point in time recovery. You may not for various business reasons, or possibly this is just a playground environment and point in time just isn't needed.
  • If you want to change to SIMPLE then alter the database to SIMPLE recovery and shrink the log file to a more reasonable size (say 5-10gb). It may grow a bit after this but shouldn't get quite so large (for this database).
  • If you decide you do want point in time recovery (most production databases do) then you need to implement regular log backups.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.