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.
| Author |
Topic |
|
anderskd
Starting Member
25 Posts |
 Posted - 2008-02-07 : 12:08:40
|
| Hello,I work for a company that recently moved our data to a new sql server. We have about 50 production databases, which range in size from 500mb to 10gb. The software we use that holds the data historically grows maybe 10% a year - probably at most. The move took place just over two months ago. Recently I was going to grab a backup of one of the databases that prior to the move was about 2.5 Gbs. The backup was a whopping 17 gigs! The datafiles and log files have grown proportonatally large as well.We started doing some analysis on this and it appears that there is a large amount of unused space per row. It looks like it is affecting all tables, but the one that is the most obvious is a logging table. This table does only inserts and writes a fairly large number of small records. Usually this table is the largest number of records in the database but doesn't take up very much size. After the move the table has increased to the largest size (by far) due to the increased AVERAGE_UNUSED_BYTES_PER_ROW (from a script I grabbed off this site). In the database I'm currently looking at it went from 1.3 to 14,453 (we have copies of the dbs prior to the move). Here is the results for the SP_SPACEUSED for that table (first row is the db prior to the move, and the second is after the move).TABLE1 - moderately to light usage database..rows reserved data indexsize unused591,783 168,088 KB 102,920 KB 14,184 KB 50,984 KB604,843 817,368 KB 183,920 KB 14,464 KB 618,984 KBWe have rebuilt and defragged indexes to no avail.Does anyone have any ideas on what could be going on? It looks like this is happening for all of the databases on the new sql server. Thanks for any information or ideas someone might have on this.Kelly |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-02-07 : 21:55:32
|
| Does the table have clustered index? |
 |
|
|
anderskd
Starting Member
25 Posts |
Posted - 2008-02-08 : 12:03:04
|
| Yes it does. It has a clustered index - after we rebuild the index on this table it doesn't seem to free up any of the space.We have noticed that the tables that have text fields are growing very rapidly. The unused space is much higher than before the move to the new server.There is only a 8% or so increase in rows and an couple 1000% increase in unused and reserved space. The index size doesn't seem to be changed very much. sp_space used before the move:rows-----------reserved-------data-------------index_size-----unused3,165,610------964,976 KB---884,904 KB----72,144 KB---7,928 KBsp_space used after the move:rows-----------reserved--------data------------index_size-----unused3,368,149-----9,430,328 KB---1,976,528 KB---76,704 KB----7,377,096 KBThanks for the response, let me know if I can provide any more information on the subject.Here is the table:CREATE TABLE [EVENTLOG] ( [ID] [timestamp] NOT NULL , [EVENTTIME] [datetime] NULL , [TASKID] [smallint] NULL , [PROGNAME] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [USERID] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [WSID] [smallint] NULL , [CATEGORY] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [MSG] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL , CONSTRAINT [UPKCL_EVENTLOG] PRIMARY KEY CLUSTERED ( [ID] ) WITH FILLFACTOR = 90 ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GO |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-02-08 : 22:25:53
|
| What are sql version and sp level of new server? |
|
|
|
anderskd
Starting Member
25 Posts |
Posted - 2008-02-11 : 12:09:03
|
| The old server was Standard Edition with SP3. The new server is Enterprise Edition with SP4.The new server is on a two node active passive cluster. |
|
|
|
|
|
|
|
|
Databases growing