Understanding database sizes

Posted on 2011-04-27
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?
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.
Question by:Simon Cripps
    LVL 22

    Accepted Solution

    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.

    Author Comment

    by:Simon Cripps
    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.
    LVL 22

    Expert Comment

    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.

    Author Comment

    by:Simon Cripps
    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?
    LVL 22

    Expert Comment

    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.
    LVL 28

    Assisted Solution

    by:Ryan McCauley
    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.

    Author Comment

    by:Simon Cripps
    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)

    LVL 28

    Expert Comment

    by:Ryan McCauley
    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?

    Author Comment

    by:Simon Cripps
    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?
    LVL 28

    Expert Comment

    by:Ryan McCauley
    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
    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.

    Author Closing Comment

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
    Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
    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…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    737 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