Why is table size much bigger on hosted server? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Why is table size much bigger on hosted server?

Author

Topic

davidgyoung
Starting Member

2 Posts

Posted - 2009-03-14 : 16:23:46

I have a table that uses almost twice as much space per row (353 bytes vs. 191 bytes) on my hosted server than it does on my local server. As a result, I keep running out of space on my hosted server before I can insert all the rows.

Any idea why the hosted database is using so much space? Detailed version/schema/space usage info below.

Thanks,
David

Local Database (SQL Server 2005 Express Edition)
------------------------------------------------
Collation: SQL_Latin1_General_CP1_CI_AS
EXEC sp_spaceused picture
name, rows, reserved, data, index_size, unused
picture, 418173, 142184 KB, 82472 KB, 59568 KB, 144 KB

Hosted Database (SQL Server 2005) - not all rows inserted
---------------------------------
Collation: SQL_Latin1_General_CP1_CI_AS
EXEC sp_spaceused picture
name, rows, reserved, data, index_size, unused
picture 219045, 137592 KB, 77712 KB, 50920 KB, 8960 KB

Schema:
-------
CREATE TABLE [dbo].[picture](
[id] [int] IDENTITY(1,1) NOT NULL,
[item_code] [varchar](20) NULL,
[local_item_code] [varchar](20) NULL,
[title] [varchar](255) NULL,
[artist_id] [int] NULL,
[publisher_id] [int] NULL,
[price] [float] NULL,
[dimensions] [varchar](25) NULL,
[thumbnail_image_filename] [varchar](50) NULL,
[large_image_filename] [varchar](50) NULL,
[framed_flag] [bit] NULL,
[show_flag] [bit] NULL,
[in_stock_flag] [bit] NULL,
[stock_count] [smallint] NULL,
[days_to_ship] [smallint] NULL,
[shipping_price] [float] NULL,
[description] [varchar](255) NULL,
[area] [float] NULL,
[aspect_ratio] [float] NULL,
[cost] [float] NULL,
[canvas_transfer_flag] [bit] NULL,
[updated_time] [datetime] NULL,
[created_time] [datetime] NULL,
[item_pid] [int] NULL,
CONSTRAINT [picture_primaryKey] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-14 : 16:52:00

Run DBCC Updateusage and check the size.

davidgyoung
Starting Member

2 Posts

Posted - 2009-03-15 : 11:17:55

I ran DBCC UPDATEUSAGE and it did make a tiny change, but my production database still uses almost twice as much space per record as my local copy.

Any other ideas?

Thanks,
David

DBCC UPDATEUSAGE (asf)

DBCC UPDATEUSAGE: sysindexes row updated for table 'asf_admin.picture' (index ID 11):
USED pages: Changed from (535) to (2040) pages.
RSVD pages: Changed from (536) to (2049) pages.
...

EXEC sp_spaceused picture
name, rows, reserved, data, index_size, unused
picture 219045, 137256 KB, 77456 KB, 50464 KB, 9336 KB

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-15 : 11:23:33

Seems all records are not exported...

Local Database rows 418173
Hosted Database rows 219045

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources