• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 243
  • Last Modified:

SQL Server database creating too much unused space

Running Sage line 500 on sql server 2000. Since Feb database has started growing quickly and sparadically. Shrinking the database does nothing unless I unload and reload the data into a re-created tables.

Database set to grow automatically by 10mb chunks. A nightly DTS package runs which deletes and re-creates about half a million rows. (these tables do not grow significantly.) Could this be causing an issue?
0
magik2008
Asked:
magik2008
  • 5
  • 3
1 Solution
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
<< Database set to grow automatically by 10mb chunks. >>

Having database autogrowth set to 10MB is not a best practice. By default it would be set to 10 MB but I would suggest you to increase it based upon the transactions involved. In your case as per the information you provided, I would suggest 500 MB.

Having smaller autogrowth creates numerous Virtual log files and it might affect some performance of server too.

<< A nightly DTS package runs which deletes and re-creates about half a million rows. >>

If this is the case, then your log files *.ldf is growing in a drastic manner. Hope you have a transaction log backup scheduled for your database. Otherwise create a maintenance plan and include transaction log backup.

An immediate step to reduce your LDF file size
1. Take a full backup of your database.
2. And Use the command below
dbcc shrinkfile ( 'Urdb_logname', 1000)

replace Urdb_logname with the log file name of your database.
0
 
magik2008Author Commented:
The transaction log is 979Mb and the database is 13832Mb. There is a database maintenance plan in place but it does not backup transaction log so I`ll look into this. I`ve updated the growth of the data file to 500 Mb and log file to 100 Mb. I`ll see what happens over the next couple of days.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Ok.. This should help you out.
Can you confirm me whether I have answered your question or Do I need to explain more on anything?
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
magik2008Author Commented:
After amending the log and data file growth I have been watching the database and it has just continued to grow and grow. Current status is:

database_name,database_size,unallocated space
cs3hws,17321.00 MB,-15.15 MB
reserved,data,index_size,unused
16237720 KB,3791264 KB,635488 KB,11810968 KB

So there is 11 Gb of free space in a database with only 3.7Gb of data. My initial reaction is this is very high but might be acceptable or normal?
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Hope the statistics provided corresponds to Indexes in your database.

What is the Fill Factor defined for indexes in your database?

Hope it is around 30 to 40 % like that because free space or unused space depends on our Fill Factor definition only.
0
 
magik2008Author Commented:
The statistics were generated by running sp_spaceused with no parameters. I have checked the server default fill factor (as tables do not seem to have any applied) and there is no fixed factor applied.
0
 
magik2008Author Commented:
I think this article may describe the problem that I am experiencing. If I`m right I`ll have a chance at fixing it.

http://support.microsoft.com/kb/924947
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
so, did you create some clustered indexes on (all) your tables, meanwhile?
that is the first step, and usually the only step, to solve this issue.
0
 
magik2008Author Commented:
As luck would have it I did create clustered indexes and this does seem to have stopped the growth and a regular shrinking is now reducing the db size, so thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now