Understanding database sizes

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?
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.
Simon CrippsOwnerAsked:
Who is Participating?
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.

Simon CrippsOwnerAuthor Commented:
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.
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.
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

Simon CrippsOwnerAuthor Commented:
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?
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.
Ryan McCauleyData and Analytics ManagerCommented:
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:


It's a relatively new project, so if you have any questions or run into any issues, I'd appreciate the feedback.
Simon CrippsOwnerAuthor Commented:
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)

Ryan McCauleyData and Analytics ManagerCommented:
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?
Simon CrippsOwnerAuthor Commented:
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?
Ryan McCauleyData and Analytics ManagerCommented:
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,
         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

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.
Simon CrippsOwnerAuthor Commented:
After analyising and making changes suggested, thi missing space seams to be reducing. Many thanks
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.