Solved

SQL Server database creating too much unused space

Posted on 2009-05-18
9
217 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
  • 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
 

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
[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

 
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 142

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

910 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now