What caused leap in space used from SQL Server 2000 to SQL Server 2005?

Question

* 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

Answer 1

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:

SELECT DISTINCT DATALENGTH(id) + DATALENGTH(runid)
+ DATALENGTH(mn1) + DATALENGTH(mn2) + DATALENGTH(mn3) 
+ DATALENGTH(mn4) + DATALENGTH(mn5) + DATALENGTH(mn6) 
+ DATALENGTH(mn7) + DATALENGTH(mn8) + DATALENGTH(mn9) 
+ DATALENGTH(mn10) + DATALENGTH(mn11) + DATALENGTH(mn12) 
FROM EachOfTheTables

It will take a bit to chug through but we may get a clue out of it.

Answer 2

0

As well, could you post the output of the queries

SELECT * 
  FROM sys.[indexes] 
 WHERE [object_id] = OBJECT_ID('name_of_big_table')

SELECT * 
  FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('name_of_big_table'), NULL, NULL, NULL)

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 ♦♦
29.2k ● 56 ● 63 ● 87

(comments are locked)

Answer 3

0

Can you give us the output for your tables based on the following query:

SELECT object_name(object_id) AS [Object Name], SUM(used_page_count) * 8192. / 1024 / 1024 AS [Pages Used in MB]
FROM sys.dm_db_partition_stats
GROUP BY object_id
ORDER BY SUM(used_page_count) DESC;

Just Edit your post or comment on it.

answered Nov 02 '09 at 06:02 PM

Blackhawk-17
10.5k ● 23 ● 29 ● 34

(comments are locked)

What caused leap in space used from SQL Server 2000 to SQL Server 2005?

Follow this question

Related Questions