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.

ThomasRushton avatar image ThomasRushton ♦♦ commented ·
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 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
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.

rakeshsingh1983 avatar image rakeshsingh1983 commented ·
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.

ThomasRushton avatar image ThomasRushton ♦♦ commented ·
...and you might want to rebuild your indexes when you're done.
0 Likes 0 ·
rakeshsingh1983 avatar image rakeshsingh1983 commented ·
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 ·
Wilfred van Dijk avatar image Wilfred van Dijk commented ·
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.