Solved

SQL Server database creating too much unused space

Posted on 2009-05-18
9
222 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
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.

 

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
tempdb log keep growing 7 33
sql server query 18 38
install report service in sccm2012 3 19
Substring works but need to tweak it 14 15
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

860 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