Excessively Large MDF File for DB

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Excessively Large MDF File for DB

Author

Topic

Vanny
Starting Member

9 Posts

Posted - 2008-12-29 : 08:52:51

There is an MSSQL 2005 Server that has been running for a while as the backend for a company Intranet. The database is very modest and barely grows. This same SQL Server is starting to be used by Reporting Services (2005). I am running a script to clear the database and then running an import of some extract files. The extract in total is just over 600MB and it grows by only about 5-10MB a day, even that may be an overstatement. However, the database MDF file (specific to the DB the files are being imported to) seems to grow by over 1GB a day.

Is there a way to find out what may be going wrong? Is something caching or not clearing? Even when I clear the DB of records it seems to stay the same size.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-31 : 21:31:08

quote:
Originally posted by Vanny

There is an MSSQL 2005 Server that has been running for a while as the backend for a company Intranet. The database is very modest and barely grows. This same SQL Server is starting to be used by Reporting Services (2005). I am running a script to clear the database and then running an import of some extract files. The extract in total is just over 600MB and it grows by only about 5-10MB a day, even that may be an overstatement. However, the database MDF file (specific to the DB the files are being imported to) seems to grow by over 1GB a day.

I think you are talking about LDF files.

Is there a way to find out what may be going wrong? Is something caching or not clearing? Even when I clear the DB of records it seems to stay the same size.

You need to shrink it down with DBCC shrinkfile but it will create performance issues.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-31 : 21:41:15

Assuming you are talking about the MDF...Do you have excessive indexes on the tables? Have you looked at the Disk Usage report in SSMS to see if it is using the space or if the file is just allocated at that size? Also check out the Disk usage by top tables report to see where the space is being taken up. It breaks it down by data and indexes.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

ghemant
Starting Member

22 Posts

Posted - 2009-01-06 : 07:26:31

Hi,
IMO you should confirm below things:
1. Your database is in Full Recovery Model, and you should take a T-Log backup at regular interval i.e. 30 mins (this depends upon your env)
2. If you perform any BCP/bulk insert or update activity; change recovery model to bulk logged mode take a full backup perform activity, change the recovery model to full again and take a full backup
3. See if you have Auto Growth option, it should be in fixed MB instead of percentage
4. You should also see if you have schedule index maintenance jobs to run periodically

hth

Regards
Hemantgiri S. Goswami
http://hemantgirisgoswami.blogspot.com/

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-06 : 12:06:11

quote:
Originally posted by ghemant

Hi,
IMO you should confirm below things:
1. Your database is in Full Recovery Model, and you should take a T-Log backup at regular interval i.e. 30 mins (this depends upon your env)
2. If you perform any BCP/bulk insert or update activity; change recovery model to bulk logged mode take a full backup perform activity, change the recovery model to full again and take a full backup

You don't need Full backup over here.Just take log backup before switching to Bulk-Logged Recovery model and perform Bulk-operation and switch to Full-Recovery model ,again take log backup and you should be able to recover in point in time.

3. See if you have Auto Growth option, it should be in fixed MB instead of percentage
4. You should also see if you have schedule index maintenance jobs to run periodically

hth

Regards
Hemantgiri S. Goswami
http://hemantgirisgoswami.blogspot.com/

ghemant
Starting Member

22 Posts

Posted - 2009-01-06 : 12:28:57

quote:
Originally posted by sodeep

quote:
Originally posted by ghemant

Hi,
IMO you should confirm below things:
1. Your database is in Full Recovery Model, and you should take a T-Log backup at regular interval i.e. 30 mins (this depends upon your env)
2. If you perform any BCP/bulk insert or update activity; change recovery model to bulk logged mode take a full backup perform activity, change the recovery model to full again and take a full backup

You don't need Full backup over here.Just take log backup before switching to Bulk-Logged Recovery model and perform Bulk-operation and switch to Full-Recovery model ,again take log backup and you should be able to recover in point in time.
Precisely, whenever you switch between Full/Bulk-Logged to simple or vise-versa its required to have full backup

3. See if you have Auto Growth option, it should be in fixed MB instead of percentage
4. You should also see if you have schedule index maintenance jobs to run periodically

hth

Regards
Hemantgiri S. Goswami
http://hemantgirisgoswami.blogspot.com/

Regards
Hemantgiri S. Goswami
http://hemantgirisgoswami.blogspot.com/

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-06 : 12:32:15

You don't need to take full backup for it when switching from Full to Bulk-logged and back to Full considering you have done log backup. I am not talking about simple.You can't do transaction log backup in simple recovery model.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources