Tom Moreau
3/20/2007 1:50:00 AM
If you do go the identity route, then place a clustered PK on it. That will
minimize fragmentation.
--
Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"DickChristoph" <dchristo99@yahoo.com> wrote in message
news:N9KdnaSR3519oWLYnZ2dnUVZ_tSunZ2d@comcast.com...
Hi Tom,
You may be right but at this point this 65GB database (and 65GB database
backup) are too big to easily move around the network.
My Plan is to Create a new table with a Identity Primary Key Column and then
have the application reference it through a View so if we need to make
changes to the table structure we can create a new table and have the View
Point to it. They only need to keep about two days worth of data in this
table, it is summarized by day into another table on a nightly basis.
But I am still wondering (just because I am genuinely curious), how we
could have got into this situation.
--
-Dick Christoph
"Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
news:%23CjQG8oaHHA.4220@TK2MSFTNGP03.phx.gbl...
> I'm thinking fragmentation here, since the table is a heap. Any chance
> that
> the data going into Column3 is sequential? If so, it would be a good
> clustering candidate, and perhaps fend off the fragmentation. If
> possible,
> can you take a backup of production and restore it onto another server -
> or
> onto the dame server but with a different DB name? That way, we can check
> things out without stopping prod.
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> .
> "DickChristoph" <dchristo99@yahoo.com> wrote in message
> news:49ydnd6Gh8IrqWLYnZ2dnUVZ_tmknZ2d@comcast.com...
> Hi Again,
>
> I perhaps have forgotten to mention the physical file (the MDF) is
> actually
> growing by 5GB a day. And almost all of this is in this one table.
>
> --
> -Dick Christoph
> "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
> news:%23HxpKmoaHHA.1296@TK2MSFTNGP02.phx.gbl...
>> If you can find a time when the table is less busy, then try to run the
>> DBCC
>> then. It's likely that sp_spaceused is reporting wrong info because of
>> the
>> high update activity on the table.
>>
>> --
>> Tom
>>
>> ----------------------------------------------------
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> SQL Server MVP
>> Toronto, ON Canada
>> .
>> "DickChristoph" <dchristo99@yahoo.com> wrote in message
>> news:-LydnYyeh59jsGLYnZ2dnUVZ_tSunZ2d@comcast.com...
>> Hi Tom,
>>
>> Can DBCC UPDATEUSAGE be run without locking the table? This thing runs in
>> a
>> 24/7 type environment and any attempt to delete rows results in table
>> lock
>> and the web-user's experience suffers.
>>
>>>Why do you need two uniqueidentifier columns?
>>
>> A better question might be why is neither of these unique? The app gets
>> the
>> unique identifiers and then writes them repeatedly in the table. I didn't
>> design this thing and I would never create a table without a primary key.
>>
>> --
>> -Dick Christoph
>>
>> "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
>> news:%23GK8JZoaHHA.4716@TK2MSFTNGP02.phx.gbl...
>>> Try running DBCC UPDATEUSAGE on it. I'd also look at putting at least a
>>> clustered index on it. Why do you need two uniqueidentifier columns?
>>>
>>> --
>>> Tom
>>>
>>> ----------------------------------------------------
>>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>>> SQL Server MVP
>>> Toronto, ON Canada
>>> .
>>> "DickChristoph" <dchristo99@yahoo.com> wrote in message
>>> news:ssWdnSD3bLactWLYnZ2dnUVZ_vamnZ2d@comcast.com...
>>> Hi
>>>
>>> We have a table that is growing very rapidly in size and I am wondering
>>> what
>>> is going on with it. Here is its definition (names changed to protect
>>> proprietary info)
>>>
>>> CREATE TABLE [MyTable] (
>>> [Column1] [uniqueidentifier] NULL ,
>>> [Column2] [uniqueidentifier] NOT NULL ,
>>> [Column3] [datetime] NOT NULL ,
>>> [Column4] [varchar] (50) NULL ,
>>> [Column5] [varchar] (50) NULL ,
>>> [Column6] [varchar] (50) NULL ,
>>> [Column7] [varchar] (10) NULL
>>> )
>>>
>>> If i run sp_spaceused on it I get
>>> name MyTable
>>> rows 3494888
>>> reserved 68219416 KB (68GB)
>>> data 8622176 KB (8GB)
>>> index_size 136 KB
>>> unused 59597104 KB
>>>
>>> From the table definition it would seem like the space usage should be
>>> clear
>>> under 1000 bytes per row.
>>>
>>> The table grows by about 200,000 rows a day but its size increases by
>>> around
>>> 5GB a day. (The reserved number increases by that much each day) This is
>>> on
>>> SQL 2000 SP4.
>>>
>>> Data is only being added to this table, there are no updates or deletes.
>>>
>>> There aren't any indexes or a primary key on it (yes I know it should
>>> have
>>> one). But I am wondering what might cause this behavior.
>>>
>>> Any thoughts?
>>>
>>> TIA
>>>
>>> -Dick Christoph
>>>
>>>
>>
>>
>
>