April 20, 2009 at 9:23 am
We have seen our database for an online application growing significantly (for us) in the past six months. Yesterday I spent several hours creating an archive database and moving 2 GB of archived data over to that and then deleting it from the main database. I also went through some changes tables and deleted several GB of old data that is no longer needed.
The primary database is showing as 33GB in size and did not decrease in size after deleting the unneeded data yesterday. The full backup that was created last night is less than 27GB in size, so it appears that my efforts were on the right track.
The question is - why did the primary database not go down in size after deleting the data? How do I get it down to the same size as the backups?
April 20, 2009 at 9:50 am
I think I would leave my database as 32 GB, so it will not have to regrow as you fill it up again.
April 20, 2009 at 10:25 am
You have to understand how a SQL Server database works. It doesn't store data like a text file or Word file that only stores what's in it.
Growing a db is relatively expensive. Your database should contain
- your data
- space to allow the database to grow for xx time.
xx is how often you want or need to monitor this. I'd say that at a minimum, your database should have enough space to handle your data needs for a month. Lots of people leave space for a quarter or 6 months. Grow it when you need to, don't let it autogrow.
April 20, 2009 at 10:34 am
So in effect - your work on making space was correct - you did in fact make your database smaller. Only the datafile still at 32GB. New data will use this space without having to grow the datafile.
April 20, 2009 at 10:44 am
Steve and leepozdol -
OK - that makes sense. How do I find out how much growth space I have in the database? I have searched the BOL and looked at my database setup and don't see any kind of growth/autogrow settings. I must be searching using the wrong terminology - so if you could point me in the right direction I would like to learn more about this so that I understand it better.
Thanks!
April 20, 2009 at 10:55 am
if I understand your question correctly - in ent mgr, right mouse click on database, then properties, then datafiles. You can control how your datafile grow here. You can also do it from t-sql - alter database.
April 20, 2009 at 11:05 am
Thanks! My properties show:
File Growth
By Percent - 10%
Maximum File Size:
Unrestricted file growth
Are these reasonable settings?
April 20, 2009 at 11:16 am
I like using the size rather than percent. Using percent can cause problems as it gets bigger. But as Steve says... monitor your database, as the free space gets smaller, grow it manually at a time of your choosing, instead of auto grow. I like auto grow as a safety valve. But would rather grow it when database is not being used much. I think alter database can be used to grow the file as well.
April 20, 2009 at 11:21 am
Thanks! This has given me a lot of good information to research and understand.
I gave up on getting 'time' to learn about my database because of overscheduling at work, but now learning the hard way that I will have to find time to keep our databases running well in the long run. My guess is whoever created the database never thought long term about maintenance and growth.
April 20, 2009 at 11:23 am
I would also go with a size rather than percentage. If you don't limit growth, you might also fill the disk. Limiting doesn't stop the issue, but it lets you know and it allows you to respond to it, give more space to the db, and then finding the issue.
I used to monitor backup sizes, since they're close to data sizes. Comparing that to the size of the DB lets me know roughly how much free space I need. Comparing backup to backup also lets me know data growth.
April 21, 2009 at 6:54 am
I run the following once a week to "monitor" my metrics (not sure where I found the script but I'm guessing here!). I can then get a baseline of each week and make the appropriate decisions based on that data (SQL2000, sp4).
CREATE PROCEDURE usp_DBGrowthRate
AS
SELECT SD.NAME AS DBNAME,
MF.NAME AS FILENAME,
MF.DBID,
FILEID,
SIZE
INTO #TEMPDBSIZE2
FROM MASTER..SYSDATABASES SD
JOIN MASTER..SYSALTFILES MF
ON SD.DBID = MF.DBID
ORDER BY MF.DBID,
SD.NAME
IF EXISTS (SELECT DISTINCT DBNAME
FROM #TEMPDBSIZE2
WHERE DBNAME IN (SELECT DISTINCT DBNAME
FROM DBGROWTHRATE))
AND CONVERT(VARCHAR(10),GETDATE(),101) > (SELECT DISTINCT CONVERT(VARCHAR(10),MAX(METRICDATE),101) AS METRICDATE
FROM DBGROWTHRATE)
BEGIN
INSERT INTO DBO.DBGROWTHRATE
(DBNAME,
DBID,
NUMPAGES,
ORIGSIZE,
CURSIZE,
GROWTHAMT,
METRICDATE)
(SELECT TDS.DBNAME,
TDS.DBID,
SUM(TDS.SIZE) AS NUMPAGES,
DGR.CURSIZE AS ORIGSIZE,
CONVERT(DECIMAL(10,2),(((SUM(CONVERT(DECIMAL(10,2),TDS.SIZE)) * 8000) / 1024) / 1024)) AS CURSIZE,
CONVERT(VARCHAR(100),(CONVERT(DECIMAL(10,2),(((SUM(CONVERT(DECIMAL(10,2),TDS.SIZE)) * 8000) / 1024) / 1024)) - DGR.CURSIZE)) + ' MB' AS GROWTHAMT,
GETDATE() AS METRICDATE
FROM #TEMPDBSIZE2 TDS
JOIN DBGROWTHRATE DGR
ON TDS.DBID = DGR.DBID
WHERE DBGROWTHID = (SELECT DISTINCT MAX(DBGROWTHID)
FROM DBGROWTHRATE
WHERE DBID = DGR.DBID)
GROUP BY TDS.DBID,TDS.DBNAME,DGR.CURSIZE)
END
ELSE
IF NOT EXISTS (SELECT DISTINCT DBNAME
FROM #TEMPDBSIZE2
WHERE DBNAME IN (SELECT DISTINCT DBNAME
FROM DBGROWTHRATE))
BEGIN
INSERT INTO DBO.DBGROWTHRATE
(DBNAME,
DBID,
NUMPAGES,
ORIGSIZE,
CURSIZE,
GROWTHAMT,
METRICDATE)
(SELECT TDS.DBNAME,
TDS.DBID,
SUM(TDS.SIZE) AS NUMPAGES,
CONVERT(DECIMAL(10,2),(((SUM(CONVERT(DECIMAL(10,2),TDS.SIZE)) * 8000) / 1024) / 1024)) AS ORIGSIZE,
CONVERT(DECIMAL(10,2),(((SUM(CONVERT(DECIMAL(10,2),TDS.SIZE)) * 8000) / 1024) / 1024)) AS CURSIZE,
'0.00 MB' AS GROWTHAMT,
GETDATE() AS METRICDATE
FROM #TEMPDBSIZE2 TDS
WHERE TDS.DBID NOT IN (SELECT DISTINCT DBID
FROM DBGROWTHRATE
WHERE DBNAME = TDS.DBID)
GROUP BY TDS.DBID,TDS.DBNAME)
END
GO
-- You can't be late until you show up.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply