* I reentered thequestion - as I couldnt respond to your comments in the other post I apologize greatly*
The jump in space used apparently happened on Friday night when the maintenance plan kicked off - after the checkdb portion.
Recently I moved from SQL Server 2000 to SQL Server 2005. I have several tables that have jumped from 5 gig to 45 gig. The data in these tables has not changed.
I have one table for each year... for years 2000 thru 2008 the number of rows are approx 45 mill for each table. for years 2000 thru 2004 the tables are taking up 45 gig space; for years 2005 thru 2008 the tables are taking up 5 gig space. table has the following columns: id - int not null type - char(3) not null runid - smallint not null mn1 - float not null mn2 - float not null mn3 - float not null mn4 - float not null mn5 - float not null mn6 - float not null mn7 - float not null mn8 - float not null mn9 - float not null mn10 - float not null mn11 - float not null mn12 - float not null
The following query was used to identify the space usage: CREATE TABLE #temp ( table_name sysname , row_count INT, reserved_size VARCHAR(50), data_size VARCHAR(50), index_size VARCHAR(50), unused_size VARCHAR(50)) SET NOCOUNT ON INSERT #temp EXEC sp_msforeachtable 'sp_spaceused ''?''' SELECT a.table_name, a.row_count, COUNT(*) AS col_count, a.data_size FROM #temp a INNER JOIN information_schema.columns b ON a.table_name collate database_default = b.table_name collate database_default GROUP BY a.table_name, a.row_count, a.data_size ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC DROP TABLE #temp
asked Nov 02 '09 at 05:13 PM in Default
Based on your data types we are looking at approximately 105 bytes per row.
45,000,000 * 105 = 4725000000 bytes .../1024 = 4614257.8125 KB .../1024 = 4506.1111 MB .../1024 = 4.4 GB
Which is in line with your findings of table size without indexes applied.
The larger tables appear to be allocating 1,000 bytes or so per row.
Try running the following to see if there are weird allocations:
It will take a bit to chug through but we may get a clue out of it.
answered Nov 02 '09 at 06:35 PM
As well, could you post the output of the queries
Replace 'name_of_big_table' with the name of one of the tables that has grown in size, and that will give us fragmentation info, as well as the information regarding configured fill factors for the indexes.
A rough overview of your maintenance plan would also help greatly.
answered Nov 02 '09 at 06:26 PM
Matt Whitfield ♦♦
Can you give us the output for your tables based on the following query:
Just Edit your post or comment on it.
answered Nov 02 '09 at 06:02 PM