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?
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
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?
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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)
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)
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?
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?
ASKER
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?
Some I suppose could be removed by a shrink, are there any other ways?
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:
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.
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 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.
ASKER
After analyising and making changes suggested, thi missing space seams to be reducing. Many thanks
ASKER
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.