?
Solved

Understanding database sizes

Posted on 2011-04-27
11
Medium Priority
?
370 Views
Last Modified: 2012-05-11
I have a database that is growing at an incredible rate, event though I am not increasing the number of records. Please can someone explain where the memory is leaking to?
Summary
The database has 40 tables 38 of which are smaller than 2 mb.
There are 2 large tables
The first of these tables GlobalLive which has 800,000 records, when I look at the properties of this table the size is about 2gb.
The other table Globalinput. This table starts of empty, then during an evening batch gets populated with another 800,000 records. These records match ones in GlobalLive and is used to update the GlobalLive table. At the end of the batch the Globalinput file is again emptied.
Looking at the global input table, even though it is empty, the size is about 1gb
Looking at the database with all the tables I would expect it to come to a size equal to the sum of the tables, however it is almost double that. At about 5gb.
The bigger issue comes when running this batch job the total database increase to over 10gb about 25% of which id the log file, this has a big impact on performance.
One big impact is when the GlobaInput file is having the records deleted. The idea was to clear space and to start with a fresh table each night. While the stored procedure is running to delete all the records and I look and refresh the size of the database I see it increase by GB at a time. Therefore I am thinking that there is some storage allocated to a roll back.  May this be the issue, if so how would I free up the space. I understand that some space will be allocated to indexes, which I would like to keep for performance.
I take a back up each night and would only restore to a full back up, if that helps with freeing up memory.
Please can you indicate what may be taking up this space and how I can reduce it.
0
Comment
Question by:Simon Cripps
  • 5
  • 3
  • 3
11 Comments
 
LVL 22

Accepted Solution

by:
dportas earned 1000 total points
ID: 35481839
It sounds like you are running in Full or Bulk Logged recovery mode but aren't backing up your transaction log. The log will therefore grow indefinitely. To fix this you either need to start doing log backups or choose the Simple recovery model. Don't choose Simple recovery unless you understand the implications and the fact that you are at greater risk of losing data that you can't recover.

http://msdn.microsoft.com/en-us/library/ms189275.aspx
0
 

Author Comment

by:Simon Cripps
ID: 35482046
I have MSSQL2008 R2 Express edition and the option is to set Recovery : Page Verify, which I have changed from CHECKSUM to NONE.
With regards to log backups, also as this is 2008 R2 I cant see the options in the back up database options.
I have backed up and run the batch job after making theRecovery setting and no joy.
0
 
LVL 22

Expert Comment

by:dportas
ID: 35482104
The Page Verify option has nothing to do with the recovery model. Set the recovery model using the command: "ALTER DATABASE <name> SET RECOVERY ...". First read the link in my previous answer.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:Simon Cripps
ID: 35482453
I was assuming that it was as in the link you provided there were 2 solutions. One through management studio and one through transact sql. I had a look at script database Alter to, however this was greyed out.  I have gone to properties>permissions to allow this but it does not appear to permit "alter to"
Any thoughts please?
0
 
LVL 22

Expert Comment

by:dportas
ID: 35482476
Click the "New Query" button in Management Studio Express and type the ALTER DATABASE statement in the window. I tend to avoid using the GUI for anything. Using T-SQL statements gives you much more control and you will learn more from it too.
0
 
LVL 28

Assisted Solution

by:Ryan McCauley
Ryan McCauley earned 1000 total points
ID: 35483995
It's likely set to full logging, as others have suggested - if you're having trouble changing the mode, at least confirm that's the problem. You can do this by browsing to the folder that stores your database files - in SQL Express, unless you changed it at installation, it's likely something like:

C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\

Looking in there, you should see at least two files the correspond to your database - a databasename.MDB file and a databasename.LDF file. The MDB holds your data and the LDF holds your log - which one is bigger? If the LDF file is the large one, then logging is definately your culprit.

If it's the MDB file that's bigger, I can help with that too - I've written an open-source tool that gives you a quick overview of your table and index sizes - just point it at a database and it will draw a picture of the data and index sizes. If it's the data file that's large and you're saying your tables are all small, it's possible you might have missed a table - here's the link to this tool:

http://sqlspacemap.codeplex.com/

It's a relatively new project, so if you have any questions or run into any issues, I'd appreciate the feedback.
0
 

Author Comment

by:Simon Cripps
ID: 35484509
I have looked at the database sizes and have some conflicting info. I have just shrunk the database and log and the Database is now 6.3GB down from 8GB and the Log has been reduced from 4gb to 1mb. These figures are from the properties on the SQL console and through looking at windows explorer.
Now using the SQLSPACEMAP tool, gives the results that I expect to have This shows almost the whole screen taking up with the Globallive file at 2.3GB (2.2gb data and 0.1gb of indexes), the next biggest file being 9.1mb. Therefore I would expect that the total database to be in the region of 2.5GB at the most. These table sizes match up to the vales when I look at the table properties in management studio.
I appear to have 3.3GB of wasted space that will not free up.

(by the way nice SQLMap tool, is very nice and simple. In the case of this database, one table is so significanlty larger than the rest that I just se them as small black lines to the side of the window, I therefor cannot tell which one is which. I can see the size with mouse over but not the table name. No real issue though)

0
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 35484662
I currently don't display empty space inside the file, and that's a feature a I want to add - it would definatley help diagnose where the space is that isn't being freed up. It also doesn't show the log file - also on the list of things to do.

With the information you've given, I'd also expect your total database size to be down around 2GB at most, not 6.3GB. When you try to shrink the data file, does it show that 50% of the space is unused?
0
 

Author Comment

by:Simon Cripps
ID: 35485265
Shrink database shows 860MB (13%) free. You mentioned that you would like to show free space. So this implies it is a know factor on the database a table will take up space with no data.
Some I suppose could be removed by a shrink, are there any other ways?
0
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 35485374
A shrink is the way to get rid of this space, but take care with it - you may shrink to get rid of the space, but if your data is just going to expand to fill the space again, it could result in disk fragmentation as SQL contracts and then expands the file. For that reason, I generally avoid shrinking files unless freeing up the disk space is absolutely necessary.

To see some more detail about which table is responsible for the space wasting, you might be interested in this query:

CREATE TABLE #tablesizes (
  TableName  VARCHAR(255),
  RowsCount  BIGINT,
  ReservedKB VARCHAR(255),
  DataKB     VARCHAR(255),
  IndexKB    VARCHAR(255),
  UnusedKB   VARCHAR(255)
)

INSERT INTO #tablesizes
EXEC sp_MSforeachtable @command1='EXEC sp_spaceused ''?'''


  SELECT Tablename,
         RowsCount,
         CONVERT(BIGINT, LEFT(ReservedKB, LEN(ReservedKB)-3)) AS ReservedKB,
         CONVERT(BIGINT, LEFT(DataKB, LEN(DataKB)-3)) AS DataKB,
         CONVERT(BIGINT, LEFT(IndexKB, LEN(IndexKB)-3)) AS IndexKB,
         CONVERT(BIGINT, LEFT(UnusedKB, LEN(UnusedKB)-3)) AS UnusedKB
    FROM #tablesizes
ORDER BY ReservedKB DESC

DROP TABLE #tablesizes 

Open in new window


This will give you a pretty solid summary of the space used an left empty in each table, so it might point out your culprit.
0
 

Author Closing Comment

by:Simon Cripps
ID: 35696577
After analyising and making changes suggested, thi missing space seams to be reducing. Many thanks
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

862 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