Solved

SQL Server database creating too much unused space

Posted on 2009-05-18
9
227 Views
Last Modified: 2013-11-30
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
Comment
Question by:magik2008
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
9 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24417605
<< 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
 

Author Comment

by:magik2008
ID: 24419804
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24422220
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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 

Author Comment

by:magik2008
ID: 24431107
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24431192
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
 

Author Comment

by:magik2008
ID: 24433429
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
 

Author Comment

by:magik2008
ID: 24471439
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 24637060
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
 

Author Comment

by:magik2008
ID: 24693026
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

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question