November 13, 2007 at 1:27 pm
Where do I look next???
I have a database that I just upgraded to SQL2005 from SQL2000 a week ago. The upgrade was performed by restore of the 2000 .bak onto a new SQL2005 server (Windows 2003 x86). This database has a 5 year history of 1GB per year growth.
On 11/5 the database was 7GB
On 11/8 the database was 12GB
on 11/12 the database was 70GB
Today it is 80GB
Logging is set to simple
Database growth is set to 10%
sp_spaceused shows
Database size 81876.38 MB
Unallocated space 2613.38 MB
Reserved 81182912 KB
Data76813928 KB
Index 2150784 KB
Unused 2218200 KB
If I total all of the table sizes as returned by this borrowed script, I have less than 6GB
SELECT Cast(Sum(i.reserved) / 128.0 AS DECIMAL(12,2)) AS MB, o.name
FROM dbo.sysindexes AS i
JOIN dbo.sysobjects AS o
ON (o.id = i.id)
GROUP BY o.name
ORDER BY Sum(i.reserved) DESC
Any Ideas???
November 13, 2007 at 1:57 pm
Try running DBCC UDPATEUSAGE() on the database to ensure the system cataolog tables are up to date.
Also, have you applied service pack 1 or 2 yet ? There was a bug prior to SP1 where autogrow settings got set to an arbitrary high value.
November 13, 2007 at 2:27 pm
Run the script on the following link to see where the space is being used.
Script to analyze table space usage:
November 13, 2007 at 2:42 pm
it could simply be a matter of having a lot of unused space, in which you could just shrink the database to reclaim the unused space. Again, I would go with the suggestion of researching what objects are taking up space and if it doesn't add up, check how much free space is in your database.
November 13, 2007 at 2:52 pm
I ran both commands, no errors and the tables still add up to about 6GB
DBCC CHECKDB with PHYSICAL_ONLY
DBCC UPDATEUSAGE (0)
No service packs yet, I plan on doing SP2 tonight.
November 13, 2007 at 3:30 pm
Thank you to 'Old Hand' for the link to the script for table size.
Of the 3 other methods to check table size, none showed the offending table.
In the moving of the data I forgot a job that deleted from 1 table, so in a weeks time this table became hugh.
November 13, 2007 at 3:45 pm
Just as a note in SQL Server 2005 you have canned reports. One of which is "disk usage by top tables".
Just right click on the database --> reports --> standard reports --> disk usage by top tables.
There are a lot of great reports in here, with lots of detail, charts and graphs. This helps when you need a different perspective, other than a result set.
November 13, 2007 at 4:22 pm
Is a table being added and later dropped perhaps by a stored proc?
Karen Gayda
MCP, MCSD, MCDBA
gaydaware.com
November 13, 2007 at 4:36 pm
hi,
When running sp_spaceused you get that about 75 G are used by data.
So, in my opinion upgrade to SP2 and check which tables are growing so fast, may be some data type have been changed to text or other huge datatypes.
Regards,
Ahmed
November 13, 2007 at 11:05 pm
Is the database being heavily used. Also you can check the properties of the database to see is they space is being reserved or is free if free then you can clean up
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
November 13, 2007 at 11:57 pm
In the moving of the data I forgot a job that deleted from 1 table, so in a weeks time this table became hugh.
So... you all set now?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply