?
Solved

SQL Server database creating too much unused space

Posted on 2009-05-18
9
Medium Priority
?
231 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

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 1000 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

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

764 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