question

rakeshsingh1983 avatar image
rakeshsingh1983 asked

Database size increases

I Have one database and it has only two table not having more than 2 GB data then why database size is 40 GB..32 GB MDF and 9 GB LDF? Its database size increases daily almost 1 GB and its not having more transaction. Please help me .. How to decrease the size of MDF.
database size
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

How is the database being used (if at all)? Are there any system processes that load data and clean it out again? Or is it just a static system?
1 Like 1 ·
You have some answers below. This web site runs by you voting and marking the best answers. For each helpful answer below, indicate this by clicking on the thumbs up symbol next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer. This helps indicate your solution for anyone else with the same question.
0 Likes 0 ·
Wilfred van Dijk avatar image
Wilfred van Dijk answered
Are you storing binary data (like photos or documents) in this database? Check if you have columns like 'varbinary' in your tables
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Only two column has text field data type.... Is there any way to shrink the MDF file?
0 Likes 0 ·
Wilfred van Dijk avatar image
Wilfred van Dijk answered
Are you sure there's only 2GB data in your database? (and not some binary data in the text column?). If so, do this: 1. in SSMS, right click on the databasename 2. goto Tasks -> Shrink -> Files 3. Select the datafile (file type: data) 4. select the "Reorganize pages before releasing unused space" and select a minimum filesize 5. click "OK" This might take some time ...
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

...and you might want to rebuild your indexes when you're done.
0 Likes 0 ·
Thanks for reply, There is no Indexes .is there any issue comes in production database when shrink the data file? Can i shrink same for the production database??
0 Likes 0 ·
yes, you can use the same steps for production. However, do this outside office hours to prevent blocking / performance issues
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.