Showing posts with label migrated. Show all posts
Showing posts with label migrated. Show all posts

Tuesday, March 20, 2012

Astronomical database growth - Please help

Please help! Sorry for the large post but hopefully it gives all the
information needed for you experts to help me out.
We recently migrated our SQL server databases to SQL Server 2000 SP4
from SQL Server 7.0 SP4 a couple of weeks ago by restoring the SQL 7.0
backups onto the new SQL Server 2000 servers. Ever since the upgrades,
one of our databases has been been growing astronomically (it was in
the range of 30-32 GB for the last 6 months, since the upgrades it's
grown 203GB each day so it's now over 80GB. This is a 3rd party
database and the design is not the best (i.e. uses CHAR instead of
VARCHAR for everything, not every table has a primary key or clustered
index) so I'm not sure how much those make a difference. I upgraded the
statistics after the upgrade. I talked to the vendor's support guy and
he suggested rebuilding the indexes, however looking at the results of
DBCC SHOWCONTIG for the worst and largest table (see below), it appears
that the indexes are fine, but that the actual table is not.
I've included the results of sp_spaceused and DBCC SHOWCONTIG for the
largest table and its indexes, as well as the DDL below from both the
old SQL Server 7.0 database and the new SQL Server 2000 database. As
you can see in the DDL there are no clustered indexes.
This is a 3rd party database so I'd prefer not to have to mess around
too much with its structure if possible. I am also concerned with how
long running a DBREINDEX or INDEXDEFRAG may take. I'm no expert, but
the results below appear to indicate that the indexes are not the
problem, but that the table is (take note of the substantially
different values for Avg. Bytes Per Extent, Scan Density, Extent Scan
Fragmentation, Avg. Bytes Free Per Page, and Avg. Page Density for the
table on the new server compared to the old server). Also note the 130
times increase in the "unused" column returned by sp_spaceused!!
Will running either DBCC DBREINDEX or DBCC INDEXDEFRAG improve the
space allocations? Or would adding a clustered index be more likely to
free up the unused space?
Any other ideas or hints for fixing this? If I do a DBCC DBREINDEX or
DBCC INDEXDEFRAG I'm guessing I will want to change the recovery model
to Simple so that I don't run out of transaction log space. All of our
other databases have been running fine since we upgraded to SQL 2000
and I'm almost ready to tear my hair out over this one that is growing
2-3 GB each day! My best guess is that I have maybe 7-10 days before we
will run out of drive space which will crash the whole server.
Let me know if there is any other information you'd like from me to
help troubleshoot/resolve this issue.
--RESULTS ON NEW SERVER - SQL SERVER 2000 SP4--
--DBCC SHOWCONTIG (VDATATABLE)--
DBCC SHOWCONTIG scanning 'VDATATABLE' table...
Table: 'VDATATABLE' (549576996); index ID: 0, database ID: 9
TABLE level scan performed.
- Pages Scanned........................: 1505907
- Extents Scanned.......................: 568010
- Extent Switches.......................: 568009
- Avg. Pages per Extent..................: 2.7
- Scan Density [Best Count:Actual Count]......: 33.14% [188239:568010]
- Extent Scan Fragmentation ...............: 31.36%
- Avg. Bytes Free per Page................: 2508.8
- Avg. Page Density (full)................: 69.00%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
--DBCC SHOWCONTIG (VDATATABLE, VD1)--
DBCC SHOWCONTIG scanning 'VDATATABLE' table...
Table: 'VDATATABLE' (549576996); index ID: 2, database ID: 9
LEAF level scan performed.
- Pages Scanned........................: 227513
- Extents Scanned.......................: 29856
- Extent Switches.......................: 30321
- Avg. Pages per Extent..................: 7.6
- Scan Density [Best Count:Actual Count]......: 93.79% [28440:30322]
- Logical Scan Fragmentation ..............: 0.20%
- Extent Scan Fragmentation ...............: 4.89%
- Avg. Bytes Free per Page................: 802.0
- Avg. Page Density (full)................: 90.09%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
--DBCC SHOWCONTIG (VDATATABLE, D1_1)--
DBCC SHOWCONTIG scanning 'VDATATABLE' table...
Table: 'VDATATABLE' (549576996); index ID: 6, database ID: 9
LEAF level scan performed.
- Pages Scanned........................: 228286
- Extents Scanned.......................: 30532
- Extent Switches.......................: 31324
- Avg. Pages per Extent..................: 7.5
- Scan Density [Best Count:Actual Count]......: 91.10% [28536:31325]
- Logical Scan Fragmentation ..............: 0.71%
- Extent Scan Fragmentation ...............: 6.98%
- Avg. Bytes Free per Page................: 826.7
- Avg. Page Density (full)................: 89.79%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
--sp_spaceused 'VDATATABLE'--
namerowsreserveddataindex_sizeunused
VDATATABLE29114458 42381064 KB12042872 KB5598664 KB24739528 KB
--END OF RESULTS ON NEW SERVER - SQL SERVER 2000 SP4--
----
--TABLE DDL (from new server but is the same on old server, other
than the COLLATE SQL_Latin1_General_CP1_CI_AS parts)
CREATE TABLE [dbo].[VDATATABLE] (
[PARTNO] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DATETIME] [char] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[UDL1] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[UDL2] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[BYPASS] [tinyint] NULL ,
[UDL3] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[UDL4] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[UDL5] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[UDL6] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[EVENT] [smallint] NULL ,
[CAUSE] [smallint] NULL ,
[ACTIONTAKEN] [smallint] NULL ,
[RTF] [int] NULL ,
[VFLAGS] [int] NULL ,
[DATA1] [float] NULL ,
[DATA2] [float] NULL ,
[DATA3] [float] NULL ,
[DATA4] [float] NULL ,
[DATA5] [float] NULL ,
[DATA6] [float] NULL ,
[DATA7] [float] NULL ,
[DATA8] [float] NULL ,
[SUBSIZE] [smallint] NULL
) ON [PRIMARY]
GO
CREATE UNIQUE INDEX [VD1] ON [dbo].[VDATATABLE]([PARTNO],
[DATETIME]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [D1_1] ON [dbo].[VDATATABLE]([UDL1], [DATETIME]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO
--RESULTS ON OLD SERVER - SQL SERVER 7.0 SP4--
--DBCC SHOWCONTIG (549576996) for the VDATATABLE table only--
DBCC SHOWCONTIG scanning 'VDATATABLE' table...
Table: 'VDATATABLE' (549576996); index ID: 0, database ID: 8
TABLE level scan performed.
- Pages Scanned........................: 1054982
- Extents Scanned.......................: 131977
- Extent Switches.......................: 131976
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 99.92% [131873:131977]
- Extent Scan Fragmentation ...............: 55.37%
- Avg. Bytes Free per Page................: 291.0
- Avg. Page Density (full)................: 96.40%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
--DBCC SHOWCONTIG (549576996, 2) for the VD1 index--
DBCC SHOWCONTIG scanning 'VDATATABLE' table...
Table: 'VDATATABLE' (549576996); index ID: 2, database ID: 8
LEAF level scan performed.
- Pages Scanned........................: 233772
- Extents Scanned.......................: 29276
- Extent Switches.......................: 31519
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 92.71% [29222:31520]
- Logical Scan Fragmentation ..............: 10.67%
- Extent Scan Fragmentation ...............: 20.65%
- Avg. Bytes Free per Page................: 783.1
- Avg. Page Density (full)................: 90.32%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
--DBCC SHOWCONTIG (549576996, 6) for the D1_1 index--
DBCC SHOWCONTIG scanning 'VDATATABLE' table...
Table: 'VDATATABLE' (549576996); index ID: 6, database ID: 8
LEAF level scan performed.
- Pages Scanned........................: 234618
- Extents Scanned.......................: 29389
- Extent Switches.......................: 32244
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 90.95% [29328:32245]
- Logical Scan Fragmentation ..............: 9.92%
- Extent Scan Fragmentation ...............: 26.84%
- Avg. Bytes Free per Page................: 809.5
- Avg. Page Density (full)................: 90.00%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
--sp_spaceused 'VDATATABLE'--
namerowsreserveddataindex_sizeunused
VDATATABLE28484584 14317992 KB8427624 KB5701432 KB188936 KB
--END OF RESULTS ON OLD SERVER - SQL SERVER 7.0 SP4--
Peter,
Is it the data file that is growing or the transaction log? Are there any
new DTS jobs, BCPs, or other mass data imports? How many nonclustered
indexes do you have for this table 5+? Yes the table (heap) is fragmented
and adding a clustered index will likely help in space used and performance
(will require double the space of the table to create). No I would not put
the database recovery model to SIMPLE unless you never perform transaction
log backups (you'll loose the ability to recover those committed
transactions in case the database goes down). As for index suggestions,
please see:
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
HTH
Jerry
The table (heap) structure is fragmented. Yes a clustered index will
probably help some in space and more than likely will help in performance as
well.
"Peter" <peter_kwas@.yahoo.ca> wrote in message
news:1128700307.363409.272000@.g44g2000cwa.googlegr oups.com...
> Please help! Sorry for the large post but hopefully it gives all the
> information needed for you experts to help me out.
> We recently migrated our SQL server databases to SQL Server 2000 SP4
> from SQL Server 7.0 SP4 a couple of weeks ago by restoring the SQL 7.0
> backups onto the new SQL Server 2000 servers. Ever since the upgrades,
> one of our databases has been been growing astronomically (it was in
> the range of 30-32 GB for the last 6 months, since the upgrades it's
> grown 203GB each day so it's now over 80GB. This is a 3rd party
> database and the design is not the best (i.e. uses CHAR instead of
> VARCHAR for everything, not every table has a primary key or clustered
> index) so I'm not sure how much those make a difference. I upgraded the
> statistics after the upgrade. I talked to the vendor's support guy and
> he suggested rebuilding the indexes, however looking at the results of
> DBCC SHOWCONTIG for the worst and largest table (see below), it appears
> that the indexes are fine, but that the actual table is not.
> I've included the results of sp_spaceused and DBCC SHOWCONTIG for the
> largest table and its indexes, as well as the DDL below from both the
> old SQL Server 7.0 database and the new SQL Server 2000 database. As
> you can see in the DDL there are no clustered indexes.
> This is a 3rd party database so I'd prefer not to have to mess around
> too much with its structure if possible. I am also concerned with how
> long running a DBREINDEX or INDEXDEFRAG may take. I'm no expert, but
> the results below appear to indicate that the indexes are not the
> problem, but that the table is (take note of the substantially
> different values for Avg. Bytes Per Extent, Scan Density, Extent Scan
> Fragmentation, Avg. Bytes Free Per Page, and Avg. Page Density for the
> table on the new server compared to the old server). Also note the 130
> times increase in the "unused" column returned by sp_spaceused!!
> Will running either DBCC DBREINDEX or DBCC INDEXDEFRAG improve the
> space allocations? Or would adding a clustered index be more likely to
> free up the unused space?
> Any other ideas or hints for fixing this? If I do a DBCC DBREINDEX or
> DBCC INDEXDEFRAG I'm guessing I will want to change the recovery model
> to Simple so that I don't run out of transaction log space. All of our
> other databases have been running fine since we upgraded to SQL 2000
> and I'm almost ready to tear my hair out over this one that is growing
> 2-3 GB each day! My best guess is that I have maybe 7-10 days before we
> will run out of drive space which will crash the whole server.
> Let me know if there is any other information you'd like from me to
> help troubleshoot/resolve this issue.
> ----
> --RESULTS ON NEW SERVER - SQL SERVER 2000 SP4--
> ----
> --DBCC SHOWCONTIG (VDATATABLE)--
> DBCC SHOWCONTIG scanning 'VDATATABLE' table...
> Table: 'VDATATABLE' (549576996); index ID: 0, database ID: 9
> TABLE level scan performed.
> - Pages Scanned........................: 1505907
> - Extents Scanned.......................: 568010
> - Extent Switches.......................: 568009
> - Avg. Pages per Extent..................: 2.7
> - Scan Density [Best Count:Actual Count]......: 33.14% [188239:568010]
> - Extent Scan Fragmentation ...............: 31.36%
> - Avg. Bytes Free per Page................: 2508.8
> - Avg. Page Density (full)................: 69.00%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> --DBCC SHOWCONTIG (VDATATABLE, VD1)--
> DBCC SHOWCONTIG scanning 'VDATATABLE' table...
> Table: 'VDATATABLE' (549576996); index ID: 2, database ID: 9
> LEAF level scan performed.
> - Pages Scanned........................: 227513
> - Extents Scanned.......................: 29856
> - Extent Switches.......................: 30321
> - Avg. Pages per Extent..................: 7.6
> - Scan Density [Best Count:Actual Count]......: 93.79% [28440:30322]
> - Logical Scan Fragmentation ..............: 0.20%
> - Extent Scan Fragmentation ...............: 4.89%
> - Avg. Bytes Free per Page................: 802.0
> - Avg. Page Density (full)................: 90.09%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> --DBCC SHOWCONTIG (VDATATABLE, D1_1)--
> DBCC SHOWCONTIG scanning 'VDATATABLE' table...
> Table: 'VDATATABLE' (549576996); index ID: 6, database ID: 9
> LEAF level scan performed.
> - Pages Scanned........................: 228286
> - Extents Scanned.......................: 30532
> - Extent Switches.......................: 31324
> - Avg. Pages per Extent..................: 7.5
> - Scan Density [Best Count:Actual Count]......: 91.10% [28536:31325]
> - Logical Scan Fragmentation ..............: 0.71%
> - Extent Scan Fragmentation ...............: 6.98%
> - Avg. Bytes Free per Page................: 826.7
> - Avg. Page Density (full)................: 89.79%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> --sp_spaceused 'VDATATABLE'--
> name rows reserved data index_size unused
> VDATATABLE 29114458 42381064 KB 12042872 KB 5598664 KB 24739528 KB
>
> ----
> --END OF RESULTS ON NEW SERVER - SQL SERVER 2000 SP4--
> ----
> ----
> --TABLE DDL (from new server but is the same on old server, other
> than the COLLATE SQL_Latin1_General_CP1_CI_AS parts)
> ----
> CREATE TABLE [dbo].[VDATATABLE] (
> [PARTNO] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [DATETIME] [char] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [UDL1] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [UDL2] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [BYPASS] [tinyint] NULL ,
> [UDL3] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [UDL4] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [UDL5] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [UDL6] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [EVENT] [smallint] NULL ,
> [CAUSE] [smallint] NULL ,
> [ACTIONTAKEN] [smallint] NULL ,
> [RTF] [int] NULL ,
> [VFLAGS] [int] NULL ,
> [DATA1] [float] NULL ,
> [DATA2] [float] NULL ,
> [DATA3] [float] NULL ,
> [DATA4] [float] NULL ,
> [DATA5] [float] NULL ,
> [DATA6] [float] NULL ,
> [DATA7] [float] NULL ,
> [DATA8] [float] NULL ,
> [SUBSIZE] [smallint] NULL
> ) ON [PRIMARY]
> GO
> CREATE UNIQUE INDEX [VD1] ON [dbo].[VDATATABLE]([PARTNO],
> [DATETIME]) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [D1_1] ON [dbo].[VDATATABLE]([UDL1], [DATETIME]) WITH
> FILLFACTOR = 90 ON [PRIMARY]
> GO
>
> --RESULTS ON OLD SERVER - SQL SERVER 7.0 SP4--
> --DBCC SHOWCONTIG (549576996) for the VDATATABLE table only--
> DBCC SHOWCONTIG scanning 'VDATATABLE' table...
> Table: 'VDATATABLE' (549576996); index ID: 0, database ID: 8
> TABLE level scan performed.
> - Pages Scanned........................: 1054982
> - Extents Scanned.......................: 131977
> - Extent Switches.......................: 131976
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.92% [131873:131977]
> - Extent Scan Fragmentation ...............: 55.37%
> - Avg. Bytes Free per Page................: 291.0
> - Avg. Page Density (full)................: 96.40%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> --DBCC SHOWCONTIG (549576996, 2) for the VD1 index--
> DBCC SHOWCONTIG scanning 'VDATATABLE' table...
> Table: 'VDATATABLE' (549576996); index ID: 2, database ID: 8
> LEAF level scan performed.
> - Pages Scanned........................: 233772
> - Extents Scanned.......................: 29276
> - Extent Switches.......................: 31519
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 92.71% [29222:31520]
> - Logical Scan Fragmentation ..............: 10.67%
> - Extent Scan Fragmentation ...............: 20.65%
> - Avg. Bytes Free per Page................: 783.1
> - Avg. Page Density (full)................: 90.32%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> --DBCC SHOWCONTIG (549576996, 6) for the D1_1 index--
> DBCC SHOWCONTIG scanning 'VDATATABLE' table...
> Table: 'VDATATABLE' (549576996); index ID: 6, database ID: 8
> LEAF level scan performed.
> - Pages Scanned........................: 234618
> - Extents Scanned.......................: 29389
> - Extent Switches.......................: 32244
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 90.95% [29328:32245]
> - Logical Scan Fragmentation ..............: 9.92%
> - Extent Scan Fragmentation ...............: 26.84%
> - Avg. Bytes Free per Page................: 809.5
> - Avg. Page Density (full)................: 90.00%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> --sp_spaceused 'VDATATABLE'--
> name rows reserved data index_size unused
> VDATATABLE 28484584 14317992 KB 8427624 KB 5701432 KB 188936 KB
> ----
> --END OF RESULTS ON OLD SERVER - SQL SERVER 7.0 SP4--
> ----
>
|||Just the data file is growing. There are no new DTS/BCP or any other
kind of mass imports. There are only the two unclustered indexes, but
looking at the results of DBCC SHOWCONTIG for them I don't believe that
they are the problem. I was only thinking of temporarily changing the
recovery mode to simple while creating/recreating/defragmenting the
indexes as I've read that creating/recreating/defragmenting indexes
will basically write all of the data from the table into the
transaction log, at least in some cases. After I get the free space
back I would change the recovery mode back to Bulk Logged.
Will adding a clustered index get rid of the vast majority of the extra
"unused" space (as reported by sp_spaceused) as that is what I believe
is showing the problem the best? Also, can users still access the table
while a clustered index is being created?
I've already read through that link.
Thanks,
Peter
|||Peter,
Check your growth settings for the data file...may be set too high. If
there is a ton of extra space in the data file (i.e., its not consumed by
data) try DBCC SHRINKFILE for the data file. If you create a clustered
index no users can access the data while the clustered index is being built.
HTH
Jerry
"Peter" <peter_kwas@.yahoo.ca> wrote in message
news:1128702181.479150.179770@.g14g2000cwa.googlegr oups.com...
> Just the data file is growing. There are no new DTS/BCP or any other
> kind of mass imports. There are only the two unclustered indexes, but
> looking at the results of DBCC SHOWCONTIG for them I don't believe that
> they are the problem. I was only thinking of temporarily changing the
> recovery mode to simple while creating/recreating/defragmenting the
> indexes as I've read that creating/recreating/defragmenting indexes
> will basically write all of the data from the table into the
> transaction log, at least in some cases. After I get the free space
> back I would change the recovery mode back to Bulk Logged.
> Will adding a clustered index get rid of the vast majority of the extra
> "unused" space (as reported by sp_spaceused) as that is what I believe
> is showing the problem the best? Also, can users still access the table
> while a clustered index is being created?
> I've already read through that link.
> Thanks,
> Peter
>
|||The growth settings are set to the same as they were on the old
server...autogrow by 10% with unlimited growth. If I look in the
taskpad in Enterprise Manager, the "used" part of the data file has
grown astronomically with the same growth as the physical database
file. Judging by the results of sp_spaceused, it appears that the extra
space is being reserved, but not used, at the table level, thus showing
it as "used" in the in the taskpad in EM.
Any ballpark guesses of how long it will take to create a clustered
index on this table (server is a quad 700MHz with 2GB RAM with RAID10)?
Thanks,
Peter
|||Peter,
10% of 80GB is 8GB growth increments. Have you run a DBCC SHRINKFILE? How
long will it take to create the clustered index? It depends - don't have an
exact answer for you. Also, remember that you'll need extra space in the
data file to do this as well as in the t-log. Also remember that when the
clustered index is built the nonclustered indexes will be rebuilt as well to
replace the ROWID with the CI KEY. I'd test this on a test box first to get
an estimation of the time required.
HTH
Jerry
"Peter" <peter_kwas@.yahoo.ca> wrote in message
news:1128703499.709063.125620@.o13g2000cwo.googlegr oups.com...
> The growth settings are set to the same as they were on the old
> server...autogrow by 10% with unlimited growth. If I look in the
> taskpad in Enterprise Manager, the "used" part of the data file has
> grown astronomically with the same growth as the physical database
> file. Judging by the results of sp_spaceused, it appears that the extra
> space is being reserved, but not used, at the table level, thus showing
> it as "used" in the in the taskpad in EM.
> Any ballpark guesses of how long it will take to create a clustered
> index on this table (server is a quad 700MHz with 2GB RAM with RAID10)?
> Thanks,
> Peter
>
|||I understand that it will grow in 8GB increments when it's 80GB,
however, the table/database is still growing...it has continued to grow
by 10% every couple of days ever since the SQL Server 2000 upgrade. It
seems like it is never actually using the "unused" space, but just
keeps expanding the data file and reserved space for the table.
The "unused" column in sp_spaceused appears to be growing by the exact
same rate that the database file grows, minus the negligible amount of
data that is being inserted. i.e. the rowcount has only increased by
672,000 rows (of a total 29,157,311 rows) since the upgrades, but the
database size has grown by 50GB and unused space reported by
sp_spaceused has grown from roughly 200,000 KB to almost 25GB!!!
It's sounding like adding a clustered index will likely be the most
beneficial, I'm just really hoping that fixes the problem
permanently...and that it doesn't take a week to run.
Thanks,
Peter
Posted by Chasidy at 9:58 PM 0 comments
Labels: , , , , , , , , , , ,

Astronomical database growth - Please help

Please help! Sorry for the large post but hopefully it gives all the
information needed for you experts to help me out.
We recently migrated our SQL server databases to SQL Server 2000 SP4
from SQL Server 7.0 SP4 a couple of weeks ago by restoring the SQL 7.0
backups onto the new SQL Server 2000 servers. Ever since the upgrades,
one of our databases has been been growing astronomically (it was in
the range of 30-32 GB for the last 6 months, since the upgrades it's
grown 203GB each day so it's now over 80GB. This is a 3rd party
database and the design is not the best (i.e. uses CHAR instead of
VARCHAR for everything, not every table has a primary key or clustered
index) so I'm not sure how much those make a difference. I upgraded the
statistics after the upgrade. I talked to the vendor's support guy and
he suggested rebuilding the indexes, however looking at the results of
DBCC SHOWCONTIG for the worst and largest table (see below), it appears
that the indexes are fine, but that the actual table is not.
I've included the results of sp_spaceused and DBCC SHOWCONTIG for the
largest table and its indexes, as well as the DDL below from both the
old SQL Server 7.0 database and the new SQL Server 2000 database. As
you can see in the DDL there are no clustered indexes.
This is a 3rd party database so I'd prefer not to have to mess around
too much with its structure if possible. I am also concerned with how
long running a DBREINDEX or INDEXDEFRAG may take. I'm no expert, but
the results below appear to indicate that the indexes are not the
problem, but that the table is (take note of the substantially
different values for Avg. Bytes Per Extent, Scan Density, Extent Scan
Fragmentation, Avg. Bytes Free Per Page, and Avg. Page Density for the
table on the new server compared to the old server). Also note the 130
times increase in the "unused" column returned by sp_spaceused!!
Will running either DBCC DBREINDEX or DBCC INDEXDEFRAG improve the
space allocations? Or would adding a clustered index be more likely to
free up the unused space?
Any other ideas or hints for fixing this' If I do a DBCC DBREINDEX or
DBCC INDEXDEFRAG I'm guessing I will want to change the recovery model
to Simple so that I don't run out of transaction log space. All of our
other databases have been running fine since we upgraded to SQL 2000
and I'm almost ready to tear my hair out over this one that is growing
2-3 GB each day! My best guess is that I have maybe 7-10 days before we
will run out of drive space which will crash the whole server.
Let me know if there is any other information you'd like from me to
help troubleshoot/resolve this issue.
----
--RESULTS ON NEW SERVER - SQL SERVER 2000 SP4--
----
--DBCC SHOWCONTIG (VDATATABLE)--
DBCC SHOWCONTIG scanning 'VDATATABLE' table...
Table: 'VDATATABLE' (549576996); index ID: 0, database ID: 9
TABLE level scan performed.
- Pages Scanned........................: 1505907
- Extents Scanned.......................: 568010
- Extent Switches.......................: 568009
- Avg. Pages per Extent..................: 2.7
- Scan Density [Best Count:Actual Count]......: 33.14% [188239:5680
10]
- Extent Scan Fragmentation ...............: 31.36%
- Avg. Bytes Free per Page................: 2508.8
- Avg. Page Density (full)................: 69.00%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
--DBCC SHOWCONTIG (VDATATABLE, VD1)--
DBCC SHOWCONTIG scanning 'VDATATABLE' table...
Table: 'VDATATABLE' (549576996); index ID: 2, database ID: 9
LEAF level scan performed.
- Pages Scanned........................: 227513
- Extents Scanned.......................: 29856
- Extent Switches.......................: 30321
- Avg. Pages per Extent..................: 7.6
- Scan Density [Best Count:Actual Count]......: 93.79% [28440:30322
]
- Logical Scan Fragmentation ..............: 0.20%
- Extent Scan Fragmentation ...............: 4.89%
- Avg. Bytes Free per Page................: 802.0
- Avg. Page Density (full)................: 90.09%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
--DBCC SHOWCONTIG (VDATATABLE, D1_1)--
DBCC SHOWCONTIG scanning 'VDATATABLE' table...
Table: 'VDATATABLE' (549576996); index ID: 6, database ID: 9
LEAF level scan performed.
- Pages Scanned........................: 228286
- Extents Scanned.......................: 30532
- Extent Switches.......................: 31324
- Avg. Pages per Extent..................: 7.5
- Scan Density [Best Count:Actual Count]......: 91.10% [28536:31325
]
- Logical Scan Fragmentation ..............: 0.71%
- Extent Scan Fragmentation ...............: 6.98%
- Avg. Bytes Free per Page................: 826.7
- Avg. Page Density (full)................: 89.79%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
--sp_spaceused 'VDATATABLE'--
name rows reserved data index_size un
used
VDATATABLE 29114458 42381064 KB 12042872 KB 5598664 KB 24739528 KB
----
--END OF RESULTS ON NEW SERVER - SQL SERVER 2000 SP4--
----
----
--TABLE DDL (from new server but is the same on old server, other
than the COLLATE SQL_Latin1_General_CP1_CI_AS parts)
----
CREATE TABLE [dbo].[VDATATABLE] (
[PARTNO] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DATETIME] [char] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[UDL1] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[UDL2] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[BYPASS] [tinyint] NULL ,
[UDL3] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[UDL4] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[UDL5] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[UDL6] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[EVENT] [smallint] NULL ,
[CAUSE] [smallint] NULL ,
[ACTIONTAKEN] [smallint] NULL ,
[RTF] [int] NULL ,
[VFLAGS] [int] NULL ,
[DATA1] [float] NULL ,
[DATA2] [float] NULL ,
[DATA3] [float] NULL ,
[DATA4] [float] NULL ,
[DATA5] [float] NULL ,
[DATA6] [float] NULL ,
[DATA7] [float] NULL ,
[DATA8] [float] NULL ,
[SUBSIZE] [smallint] NULL
) ON [PRIMARY]
GO
CREATE UNIQUE INDEX [VD1] ON [dbo].[VDATATABLE]([PARTNO],
[DATETIME]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [D1_1] ON [dbo].[VDATATABLE]([UDL1], [DATE
TIME]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO
---
--RESULTS ON OLD SERVER - SQL SERVER 7.0 SP4--
---
--DBCC SHOWCONTIG (549576996) for the VDATATABLE table only--
DBCC SHOWCONTIG scanning 'VDATATABLE' table...
Table: 'VDATATABLE' (549576996); index ID: 0, database ID: 8
TABLE level scan performed.
- Pages Scanned........................: 1054982
- Extents Scanned.......................: 131977
- Extent Switches.......................: 131976
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 99.92% [131873:1319
77]
- Extent Scan Fragmentation ...............: 55.37%
- Avg. Bytes Free per Page................: 291.0
- Avg. Page Density (full)................: 96.40%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
--DBCC SHOWCONTIG (549576996, 2) for the VD1 index--
DBCC SHOWCONTIG scanning 'VDATATABLE' table...
Table: 'VDATATABLE' (549576996); index ID: 2, database ID: 8
LEAF level scan performed.
- Pages Scanned........................: 233772
- Extents Scanned.......................: 29276
- Extent Switches.......................: 31519
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 92.71% [29222:31520
]
- Logical Scan Fragmentation ..............: 10.67%
- Extent Scan Fragmentation ...............: 20.65%
- Avg. Bytes Free per Page................: 783.1
- Avg. Page Density (full)................: 90.32%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
--DBCC SHOWCONTIG (549576996, 6) for the D1_1 index--
DBCC SHOWCONTIG scanning 'VDATATABLE' table...
Table: 'VDATATABLE' (549576996); index ID: 6, database ID: 8
LEAF level scan performed.
- Pages Scanned........................: 234618
- Extents Scanned.......................: 29389
- Extent Switches.......................: 32244
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 90.95% [29328:32245
]
- Logical Scan Fragmentation ..............: 9.92%
- Extent Scan Fragmentation ...............: 26.84%
- Avg. Bytes Free per Page................: 809.5
- Avg. Page Density (full)................: 90.00%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
--sp_spaceused 'VDATATABLE'--
name rows reserved data index_size un
used
VDATATABLE 28484584 14317992 KB 8427624 KB 5701432 KB 188936 KB
----
--END OF RESULTS ON OLD SERVER - SQL SERVER 7.0 SP4--
----Peter,
Is it the data file that is growing or the transaction log? Are there any
new DTS jobs, BCPs, or other mass data imports? How many nonclustered
indexes do you have for this table 5+? Yes the table (heap) is fragmented
and adding a clustered index will likely help in space used and performance
(will require double the space of the table to create). No I would not put
the database recovery model to SIMPLE unless you never perform transaction
log backups (you'll loose the ability to recover those committed
transactions in case the database goes down). As for index suggestions,
please see:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
HTH
Jerry
The table (heap) structure is fragmented. Yes a clustered index will
probably help some in space and more than likely will help in performance as
well.
"Peter" <peter_kwas@.yahoo.ca> wrote in message
news:1128700307.363409.272000@.g44g2000cwa.googlegroups.com...
> Please help! Sorry for the large post but hopefully it gives all the
> information needed for you experts to help me out.
> We recently migrated our SQL server databases to SQL Server 2000 SP4
> from SQL Server 7.0 SP4 a couple of weeks ago by restoring the SQL 7.0
> backups onto the new SQL Server 2000 servers. Ever since the upgrades,
> one of our databases has been been growing astronomically (it was in
> the range of 30-32 GB for the last 6 months, since the upgrades it's
> grown 203GB each day so it's now over 80GB. This is a 3rd party
> database and the design is not the best (i.e. uses CHAR instead of
> VARCHAR for everything, not every table has a primary key or clustered
> index) so I'm not sure how much those make a difference. I upgraded the
> statistics after the upgrade. I talked to the vendor's support guy and
> he suggested rebuilding the indexes, however looking at the results of
> DBCC SHOWCONTIG for the worst and largest table (see below), it appears
> that the indexes are fine, but that the actual table is not.
> I've included the results of sp_spaceused and DBCC SHOWCONTIG for the
> largest table and its indexes, as well as the DDL below from both the
> old SQL Server 7.0 database and the new SQL Server 2000 database. As
> you can see in the DDL there are no clustered indexes.
> This is a 3rd party database so I'd prefer not to have to mess around
> too much with its structure if possible. I am also concerned with how
> long running a DBREINDEX or INDEXDEFRAG may take. I'm no expert, but
> the results below appear to indicate that the indexes are not the
> problem, but that the table is (take note of the substantially
> different values for Avg. Bytes Per Extent, Scan Density, Extent Scan
> Fragmentation, Avg. Bytes Free Per Page, and Avg. Page Density for the
> table on the new server compared to the old server). Also note the 130
> times increase in the "unused" column returned by sp_spaceused!!
> Will running either DBCC DBREINDEX or DBCC INDEXDEFRAG improve the
> space allocations? Or would adding a clustered index be more likely to
> free up the unused space?
> Any other ideas or hints for fixing this' If I do a DBCC DBREINDEX or
> DBCC INDEXDEFRAG I'm guessing I will want to change the recovery model
> to Simple so that I don't run out of transaction log space. All of our
> other databases have been running fine since we upgraded to SQL 2000
> and I'm almost ready to tear my hair out over this one that is growing
> 2-3 GB each day! My best guess is that I have maybe 7-10 days before we
> will run out of drive space which will crash the whole server.
> Let me know if there is any other information you'd like from me to
> help troubleshoot/resolve this issue.
> ----
> --RESULTS ON NEW SERVER - SQL SERVER 2000 SP4--
> ----
> --DBCC SHOWCONTIG (VDATATABLE)--
> DBCC SHOWCONTIG scanning 'VDATATABLE' table...
> Table: 'VDATATABLE' (549576996); index ID: 0, database ID: 9
> TABLE level scan performed.
> - Pages Scanned........................: 1505907
> - Extents Scanned.......................: 568010
> - Extent Switches.......................: 568009
> - Avg. Pages per Extent..................: 2.7
> - Scan Density [Best Count:Actual Count]......: 33.14% [188239:56
8010]
> - Extent Scan Fragmentation ...............: 31.36%
> - Avg. Bytes Free per Page................: 2508.8
> - Avg. Page Density (full)................: 69.00%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> --DBCC SHOWCONTIG (VDATATABLE, VD1)--
> DBCC SHOWCONTIG scanning 'VDATATABLE' table...
> Table: 'VDATATABLE' (549576996); index ID: 2, database ID: 9
> LEAF level scan performed.
> - Pages Scanned........................: 227513
> - Extents Scanned.......................: 29856
> - Extent Switches.......................: 30321
> - Avg. Pages per Extent..................: 7.6
> - Scan Density [Best Count:Actual Count]......: 93.79% [28440:303
22]
> - Logical Scan Fragmentation ..............: 0.20%
> - Extent Scan Fragmentation ...............: 4.89%
> - Avg. Bytes Free per Page................: 802.0
> - Avg. Page Density (full)................: 90.09%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> --DBCC SHOWCONTIG (VDATATABLE, D1_1)--
> DBCC SHOWCONTIG scanning 'VDATATABLE' table...
> Table: 'VDATATABLE' (549576996); index ID: 6, database ID: 9
> LEAF level scan performed.
> - Pages Scanned........................: 228286
> - Extents Scanned.......................: 30532
> - Extent Switches.......................: 31324
> - Avg. Pages per Extent..................: 7.5
> - Scan Density [Best Count:Actual Count]......: 91.10% [28536:313
25]
> - Logical Scan Fragmentation ..............: 0.71%
> - Extent Scan Fragmentation ...............: 6.98%
> - Avg. Bytes Free per Page................: 826.7
> - Avg. Page Density (full)................: 89.79%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> --sp_spaceused 'VDATATABLE'--
> name rows reserved data index_size unused
> VDATATABLE 29114458 42381064 KB 12042872 KB 5598664 KB 24739528 KB
>
> ----
> --END OF RESULTS ON NEW SERVER - SQL SERVER 2000 SP4--
> ----
> ----
> --TABLE DDL (from new server but is the same on old server, other
> than the COLLATE SQL_Latin1_General_CP1_CI_AS parts)
> ----
> CREATE TABLE [dbo].[VDATATABLE] (
> [PARTNO] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
> [DATETIME] [char] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NU
LL ,
> [UDL1] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [UDL2] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [BYPASS] [tinyint] NULL ,
> [UDL3] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [UDL4] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [UDL5] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [UDL6] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [EVENT] [smallint] NULL ,
> [CAUSE] [smallint] NULL ,
> [ACTIONTAKEN] [smallint] NULL ,
> [RTF] [int] NULL ,
> [VFLAGS] [int] NULL ,
> [DATA1] [float] NULL ,
> [DATA2] [float] NULL ,
> [DATA3] [float] NULL ,
> [DATA4] [float] NULL ,
> [DATA5] [float] NULL ,
> [DATA6] [float] NULL ,
> [DATA7] [float] NULL ,
> [DATA8] [float] NULL ,
> [SUBSIZE] [smallint] NULL
> ) ON [PRIMARY]
> GO
> CREATE UNIQUE INDEX [VD1] ON [dbo].[VDATATABLE]([PARTNO]
,
> [DATETIME]) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [D1_1] ON [dbo].[VDATATABLE]([UDL1], [DA
TETIME]) WITH
> FILLFACTOR = 90 ON [PRIMARY]
> GO
>
> ---
> --RESULTS ON OLD SERVER - SQL SERVER 7.0 SP4--
> ---
> --DBCC SHOWCONTIG (549576996) for the VDATATABLE table only--
> DBCC SHOWCONTIG scanning 'VDATATABLE' table...
> Table: 'VDATATABLE' (549576996); index ID: 0, database ID: 8
> TABLE level scan performed.
> - Pages Scanned........................: 1054982
> - Extents Scanned.......................: 131977
> - Extent Switches.......................: 131976
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.92% [131873:13
1977]
> - Extent Scan Fragmentation ...............: 55.37%
> - Avg. Bytes Free per Page................: 291.0
> - Avg. Page Density (full)................: 96.40%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> --DBCC SHOWCONTIG (549576996, 2) for the VD1 index--
> DBCC SHOWCONTIG scanning 'VDATATABLE' table...
> Table: 'VDATATABLE' (549576996); index ID: 2, database ID: 8
> LEAF level scan performed.
> - Pages Scanned........................: 233772
> - Extents Scanned.......................: 29276
> - Extent Switches.......................: 31519
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 92.71% [29222:315
20]
> - Logical Scan Fragmentation ..............: 10.67%
> - Extent Scan Fragmentation ...............: 20.65%
> - Avg. Bytes Free per Page................: 783.1
> - Avg. Page Density (full)................: 90.32%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> --DBCC SHOWCONTIG (549576996, 6) for the D1_1 index--
> DBCC SHOWCONTIG scanning 'VDATATABLE' table...
> Table: 'VDATATABLE' (549576996); index ID: 6, database ID: 8
> LEAF level scan performed.
> - Pages Scanned........................: 234618
> - Extents Scanned.......................: 29389
> - Extent Switches.......................: 32244
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 90.95% [29328:322
45]
> - Logical Scan Fragmentation ..............: 9.92%
> - Extent Scan Fragmentation ...............: 26.84%
> - Avg. Bytes Free per Page................: 809.5
> - Avg. Page Density (full)................: 90.00%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> --sp_spaceused 'VDATATABLE'--
> name rows reserved data index_size unused
> VDATATABLE 28484584 14317992 KB 8427624 KB 5701432 KB 188936 KB
> ----
> --END OF RESULTS ON OLD SERVER - SQL SERVER 7.0 SP4--
> ----
>|||Just the data file is growing. There are no new DTS/BCP or any other
kind of mass imports. There are only the two unclustered indexes, but
looking at the results of DBCC SHOWCONTIG for them I don't believe that
they are the problem. I was only thinking of temporarily changing the
recovery mode to simple while creating/recreating/defragmenting the
indexes as I've read that creating/recreating/defragmenting indexes
will basically write all of the data from the table into the
transaction log, at least in some cases. After I get the free space
back I would change the recovery mode back to Bulk Logged.
Will adding a clustered index get rid of the vast majority of the extra
"unused" space (as reported by sp_spaceused) as that is what I believe
is showing the problem the best? Also, can users still access the table
while a clustered index is being created?
I've already read through that link.
Thanks,
Peter|||Peter,
Check your growth settings for the data file...may be set too high. If
there is a ton of extra space in the data file (i.e., its not consumed by
data) try DBCC SHRINKFILE for the data file. If you create a clustered
index no users can access the data while the clustered index is being built.
HTH
Jerry
"Peter" <peter_kwas@.yahoo.ca> wrote in message
news:1128702181.479150.179770@.g14g2000cwa.googlegroups.com...
> Just the data file is growing. There are no new DTS/BCP or any other
> kind of mass imports. There are only the two unclustered indexes, but
> looking at the results of DBCC SHOWCONTIG for them I don't believe that
> they are the problem. I was only thinking of temporarily changing the
> recovery mode to simple while creating/recreating/defragmenting the
> indexes as I've read that creating/recreating/defragmenting indexes
> will basically write all of the data from the table into the
> transaction log, at least in some cases. After I get the free space
> back I would change the recovery mode back to Bulk Logged.
> Will adding a clustered index get rid of the vast majority of the extra
> "unused" space (as reported by sp_spaceused) as that is what I believe
> is showing the problem the best? Also, can users still access the table
> while a clustered index is being created?
> I've already read through that link.
> Thanks,
> Peter
>|||The growth settings are set to the same as they were on the old
server...autogrow by 10% with unlimited growth. If I look in the
taskpad in Enterprise Manager, the "used" part of the data file has
grown astronomically with the same growth as the physical database
file. Judging by the results of sp_spaceused, it appears that the extra
space is being reserved, but not used, at the table level, thus showing
it as "used" in the in the taskpad in EM.
Any ballpark guesses of how long it will take to create a clustered
index on this table (server is a quad 700MHz with 2GB RAM with RAID10)?
Thanks,
Peter|||Peter,
10% of 80GB is 8GB growth increments. Have you run a DBCC SHRINKFILE? How
long will it take to create the clustered index? It depends - don't have an
exact answer for you. Also, remember that you'll need extra space in the
data file to do this as well as in the t-log. Also remember that when the
clustered index is built the nonclustered indexes will be rebuilt as well to
replace the ROWID with the CI KEY. I'd test this on a test box first to get
an estimation of the time required.
HTH
Jerry
"Peter" <peter_kwas@.yahoo.ca> wrote in message
news:1128703499.709063.125620@.o13g2000cwo.googlegroups.com...
> The growth settings are set to the same as they were on the old
> server...autogrow by 10% with unlimited growth. If I look in the
> taskpad in Enterprise Manager, the "used" part of the data file has
> grown astronomically with the same growth as the physical database
> file. Judging by the results of sp_spaceused, it appears that the extra
> space is being reserved, but not used, at the table level, thus showing
> it as "used" in the in the taskpad in EM.
> Any ballpark guesses of how long it will take to create a clustered
> index on this table (server is a quad 700MHz with 2GB RAM with RAID10)?
> Thanks,
> Peter
>|||I understand that it will grow in 8GB increments when it's 80GB,
however, the table/database is still growing...it has continued to grow
by 10% every couple of days ever since the SQL Server 2000 upgrade. It
seems like it is never actually using the "unused" space, but just
keeps expanding the data file and reserved space for the table.
The "unused" column in sp_spaceused appears to be growing by the exact
same rate that the database file grows, minus the negligible amount of
data that is being inserted. i.e. the rowcount has only increased by
672,000 rows (of a total 29,157,311 rows) since the upgrades, but the
database size has grown by 50GB and unused space reported by
sp_spaceused has grown from roughly 200,000 KB to almost 25GB!!!
It's sounding like adding a clustered index will likely be the most
beneficial, I'm just really hoping that fixes the problem
permanently...and that it doesn't take a week to run.
Thanks,
Peter
Posted by Chasidy at 9:57 PM 0 comments
Labels: , , , , , , , , , , ,

Astronomical database growth - Please help

Please help! Sorry for the large post but hopefully it gives all the
information needed for you experts to help me out.
We recently migrated our SQL server databases to SQL Server 2000 SP4
from SQL Server 7.0 SP4 a couple of weeks ago by restoring the SQL 7.0
backups onto the new SQL Server 2000 servers. Ever since the upgrades,
one of our databases has been been growing astronomically (it was in
the range of 30-32 GB for the last 6 months, since the upgrades it's
grown 203GB each day so it's now over 80GB. This is a 3rd party
database and the design is not the best (i.e. uses CHAR instead of
VARCHAR for everything, not every table has a primary key or clustered
index) so I'm not sure how much those make a difference. I upgraded the
statistics after the upgrade. I talked to the vendor's support guy and
he suggested rebuilding the indexes, however looking at the results of
DBCC SHOWCONTIG for the worst and largest table (see below), it appears
that the indexes are fine, but that the actual table is not.
I've included the results of sp_spaceused and DBCC SHOWCONTIG for the
largest table and its indexes, as well as the DDL below from both the
old SQL Server 7.0 database and the new SQL Server 2000 database. As
you can see in the DDL there are no clustered indexes.
This is a 3rd party database so I'd prefer not to have to mess around
too much with its structure if possible. I am also concerned with how
long running a DBREINDEX or INDEXDEFRAG may take. I'm no expert, but
the results below appear to indicate that the indexes are not the
problem, but that the table is (take note of the substantially
different values for Avg. Bytes Per Extent, Scan Density, Extent Scan
Fragmentation, Avg. Bytes Free Per Page, and Avg. Page Density for the
table on the new server compared to the old server). Also note the 130
times increase in the "unused" column returned by sp_spaceused!!
Will running either DBCC DBREINDEX or DBCC INDEXDEFRAG improve the
space allocations? Or would adding a clustered index be more likely to
free up the unused space?
Any other ideas or hints for fixing this' If I do a DBCC DBREINDEX or
DBCC INDEXDEFRAG I'm guessing I will want to change the recovery model
to Simple so that I don't run out of transaction log space. All of our
other databases have been running fine since we upgraded to SQL 2000
and I'm almost ready to tear my hair out over this one that is growing
2-3 GB each day! My best guess is that I have maybe 7-10 days before we
will run out of drive space which will crash the whole server.
Let me know if there is any other information you'd like from me to
help troubleshoot/resolve this issue.
----
--RESULTS ON NEW SERVER - SQL SERVER 2000 SP4--
----
--DBCC SHOWCONTIG (VDATATABLE)--
DBCC SHOWCONTIG scanning 'VDATATABLE' table...
Table: 'VDATATABLE' (549576996); index ID: 0, database ID: 9
TABLE level scan performed.
- Pages Scanned........................: 1505907
- Extents Scanned.......................: 568010
- Extent Switches.......................: 568009
- Avg. Pages per Extent..................: 2.7
- Scan Density [Best Count:Actual Count]......: 33.14% [188239:568010]
- Extent Scan Fragmentation ...............: 31.36%
- Avg. Bytes Free per Page................: 2508.8
- Avg. Page Density (full)................: 69.00%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
--DBCC SHOWCONTIG (VDATATABLE, VD1)--
DBCC SHOWCONTIG scanning 'VDATATABLE' table...
Table: 'VDATATABLE' (549576996); index ID: 2, database ID: 9
LEAF level scan performed.
- Pages Scanned........................: 227513
- Extents Scanned.......................: 29856
- Extent Switches.......................: 30321
- Avg. Pages per Extent..................: 7.6
- Scan Density [Best Count:Actual Count]......: 93.79% [28440:30322]
- Logical Scan Fragmentation ..............: 0.20%
- Extent Scan Fragmentation ...............: 4.89%
- Avg. Bytes Free per Page................: 802.0
- Avg. Page Density (full)................: 90.09%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
--DBCC SHOWCONTIG (VDATATABLE, D1_1)--
DBCC SHOWCONTIG scanning 'VDATATABLE' table...
Table: 'VDATATABLE' (549576996); index ID: 6, database ID: 9
LEAF level scan performed.
- Pages Scanned........................: 228286
- Extents Scanned.......................: 30532
- Extent Switches.......................: 31324
- Avg. Pages per Extent..................: 7.5
- Scan Density [Best Count:Actual Count]......: 91.10% [28536:31325]
- Logical Scan Fragmentation ..............: 0.71%
- Extent Scan Fragmentation ...............: 6.98%
- Avg. Bytes Free per Page................: 826.7
- Avg. Page Density (full)................: 89.79%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
--sp_spaceused 'VDATATABLE'--
name rows reserved data index_size unused
VDATATABLE 29114458 42381064 KB 12042872 KB 5598664 KB 24739528 KB
----
--END OF RESULTS ON NEW SERVER - SQL SERVER 2000 SP4--
----
----
--TABLE DDL (from new server but is the same on old server, other
than the COLLATE SQL_Latin1_General_CP1_CI_AS parts)
----
CREATE TABLE [dbo].[VDATATABLE] (
[PARTNO] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DATETIME] [char] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[UDL1] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[UDL2] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[BYPASS] [tinyint] NULL ,
[UDL3] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[UDL4] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[UDL5] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[UDL6] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[EVENT] [smallint] NULL ,
[CAUSE] [smallint] NULL ,
[ACTIONTAKEN] [smallint] NULL ,
[RTF] [int] NULL ,
[VFLAGS] [int] NULL ,
[DATA1] [float] NULL ,
[DATA2] [float] NULL ,
[DATA3] [float] NULL ,
[DATA4] [float] NULL ,
[DATA5] [float] NULL ,
[DATA6] [float] NULL ,
[DATA7] [float] NULL ,
[DATA8] [float] NULL ,
[SUBSIZE] [smallint] NULL
) ON [PRIMARY]
GO
CREATE UNIQUE INDEX [VD1] ON [dbo].[VDATATABLE]([PARTNO],
[DATETIME]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [D1_1] ON [dbo].[VDATATABLE]([UDL1], [DATETIME]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO
---
--RESULTS ON OLD SERVER - SQL SERVER 7.0 SP4--
---
--DBCC SHOWCONTIG (549576996) for the VDATATABLE table only--
DBCC SHOWCONTIG scanning 'VDATATABLE' table...
Table: 'VDATATABLE' (549576996); index ID: 0, database ID: 8
TABLE level scan performed.
- Pages Scanned........................: 1054982
- Extents Scanned.......................: 131977
- Extent Switches.......................: 131976
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 99.92% [131873:131977]
- Extent Scan Fragmentation ...............: 55.37%
- Avg. Bytes Free per Page................: 291.0
- Avg. Page Density (full)................: 96.40%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
--DBCC SHOWCONTIG (549576996, 2) for the VD1 index--
DBCC SHOWCONTIG scanning 'VDATATABLE' table...
Table: 'VDATATABLE' (549576996); index ID: 2, database ID: 8
LEAF level scan performed.
- Pages Scanned........................: 233772
- Extents Scanned.......................: 29276
- Extent Switches.......................: 31519
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 92.71% [29222:31520]
- Logical Scan Fragmentation ..............: 10.67%
- Extent Scan Fragmentation ...............: 20.65%
- Avg. Bytes Free per Page................: 783.1
- Avg. Page Density (full)................: 90.32%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
--DBCC SHOWCONTIG (549576996, 6) for the D1_1 index--
DBCC SHOWCONTIG scanning 'VDATATABLE' table...
Table: 'VDATATABLE' (549576996); index ID: 6, database ID: 8
LEAF level scan performed.
- Pages Scanned........................: 234618
- Extents Scanned.......................: 29389
- Extent Switches.......................: 32244
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 90.95% [29328:32245]
- Logical Scan Fragmentation ..............: 9.92%
- Extent Scan Fragmentation ...............: 26.84%
- Avg. Bytes Free per Page................: 809.5
- Avg. Page Density (full)................: 90.00%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
--sp_spaceused 'VDATATABLE'--
name rows reserved data index_size unused
VDATATABLE 28484584 14317992 KB 8427624 KB 5701432 KB 188936 KB
----
--END OF RESULTS ON OLD SERVER - SQL SERVER 7.0 SP4--
----Peter,
Is it the data file that is growing or the transaction log? Are there any
new DTS jobs, BCPs, or other mass data imports? How many nonclustered
indexes do you have for this table 5+? Yes the table (heap) is fragmented
and adding a clustered index will likely help in space used and performance
(will require double the space of the table to create). No I would not put
the database recovery model to SIMPLE unless you never perform transaction
log backups (you'll loose the ability to recover those committed
transactions in case the database goes down). As for index suggestions,
please see:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
HTH
Jerry
The table (heap) structure is fragmented. Yes a clustered index will
probably help some in space and more than likely will help in performance as
well.
"Peter" <peter_kwas@.yahoo.ca> wrote in message
news:1128700307.363409.272000@.g44g2000cwa.googlegroups.com...
> Please help! Sorry for the large post but hopefully it gives all the
> information needed for you experts to help me out.
> We recently migrated our SQL server databases to SQL Server 2000 SP4
> from SQL Server 7.0 SP4 a couple of weeks ago by restoring the SQL 7.0
> backups onto the new SQL Server 2000 servers. Ever since the upgrades,
> one of our databases has been been growing astronomically (it was in
> the range of 30-32 GB for the last 6 months, since the upgrades it's
> grown 203GB each day so it's now over 80GB. This is a 3rd party
> database and the design is not the best (i.e. uses CHAR instead of
> VARCHAR for everything, not every table has a primary key or clustered
> index) so I'm not sure how much those make a difference. I upgraded the
> statistics after the upgrade. I talked to the vendor's support guy and
> he suggested rebuilding the indexes, however looking at the results of
> DBCC SHOWCONTIG for the worst and largest table (see below), it appears
> that the indexes are fine, but that the actual table is not.
> I've included the results of sp_spaceused and DBCC SHOWCONTIG for the
> largest table and its indexes, as well as the DDL below from both the
> old SQL Server 7.0 database and the new SQL Server 2000 database. As
> you can see in the DDL there are no clustered indexes.
> This is a 3rd party database so I'd prefer not to have to mess around
> too much with its structure if possible. I am also concerned with how
> long running a DBREINDEX or INDEXDEFRAG may take. I'm no expert, but
> the results below appear to indicate that the indexes are not the
> problem, but that the table is (take note of the substantially
> different values for Avg. Bytes Per Extent, Scan Density, Extent Scan
> Fragmentation, Avg. Bytes Free Per Page, and Avg. Page Density for the
> table on the new server compared to the old server). Also note the 130
> times increase in the "unused" column returned by sp_spaceused!!
> Will running either DBCC DBREINDEX or DBCC INDEXDEFRAG improve the
> space allocations? Or would adding a clustered index be more likely to
> free up the unused space?
> Any other ideas or hints for fixing this' If I do a DBCC DBREINDEX or
> DBCC INDEXDEFRAG I'm guessing I will want to change the recovery model
> to Simple so that I don't run out of transaction log space. All of our
> other databases have been running fine since we upgraded to SQL 2000
> and I'm almost ready to tear my hair out over this one that is growing
> 2-3 GB each day! My best guess is that I have maybe 7-10 days before we
> will run out of drive space which will crash the whole server.
> Let me know if there is any other information you'd like from me to
> help troubleshoot/resolve this issue.
> ----
> --RESULTS ON NEW SERVER - SQL SERVER 2000 SP4--
> ----
> --DBCC SHOWCONTIG (VDATATABLE)--
> DBCC SHOWCONTIG scanning 'VDATATABLE' table...
> Table: 'VDATATABLE' (549576996); index ID: 0, database ID: 9
> TABLE level scan performed.
> - Pages Scanned........................: 1505907
> - Extents Scanned.......................: 568010
> - Extent Switches.......................: 568009
> - Avg. Pages per Extent..................: 2.7
> - Scan Density [Best Count:Actual Count]......: 33.14% [188239:568010]
> - Extent Scan Fragmentation ...............: 31.36%
> - Avg. Bytes Free per Page................: 2508.8
> - Avg. Page Density (full)................: 69.00%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> --DBCC SHOWCONTIG (VDATATABLE, VD1)--
> DBCC SHOWCONTIG scanning 'VDATATABLE' table...
> Table: 'VDATATABLE' (549576996); index ID: 2, database ID: 9
> LEAF level scan performed.
> - Pages Scanned........................: 227513
> - Extents Scanned.......................: 29856
> - Extent Switches.......................: 30321
> - Avg. Pages per Extent..................: 7.6
> - Scan Density [Best Count:Actual Count]......: 93.79% [28440:30322]
> - Logical Scan Fragmentation ..............: 0.20%
> - Extent Scan Fragmentation ...............: 4.89%
> - Avg. Bytes Free per Page................: 802.0
> - Avg. Page Density (full)................: 90.09%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> --DBCC SHOWCONTIG (VDATATABLE, D1_1)--
> DBCC SHOWCONTIG scanning 'VDATATABLE' table...
> Table: 'VDATATABLE' (549576996); index ID: 6, database ID: 9
> LEAF level scan performed.
> - Pages Scanned........................: 228286
> - Extents Scanned.......................: 30532
> - Extent Switches.......................: 31324
> - Avg. Pages per Extent..................: 7.5
> - Scan Density [Best Count:Actual Count]......: 91.10% [28536:31325]
> - Logical Scan Fragmentation ..............: 0.71%
> - Extent Scan Fragmentation ...............: 6.98%
> - Avg. Bytes Free per Page................: 826.7
> - Avg. Page Density (full)................: 89.79%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> --sp_spaceused 'VDATATABLE'--
> name rows reserved data index_size unused
> VDATATABLE 29114458 42381064 KB 12042872 KB 5598664 KB 24739528 KB
>
> ----
> --END OF RESULTS ON NEW SERVER - SQL SERVER 2000 SP4--
> ----
> ----
> --TABLE DDL (from new server but is the same on old server, other
> than the COLLATE SQL_Latin1_General_CP1_CI_AS parts)
> ----
> CREATE TABLE [dbo].[VDATATABLE] (
> [PARTNO] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [DATETIME] [char] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [UDL1] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [UDL2] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [BYPASS] [tinyint] NULL ,
> [UDL3] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [UDL4] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [UDL5] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [UDL6] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [EVENT] [smallint] NULL ,
> [CAUSE] [smallint] NULL ,
> [ACTIONTAKEN] [smallint] NULL ,
> [RTF] [int] NULL ,
> [VFLAGS] [int] NULL ,
> [DATA1] [float] NULL ,
> [DATA2] [float] NULL ,
> [DATA3] [float] NULL ,
> [DATA4] [float] NULL ,
> [DATA5] [float] NULL ,
> [DATA6] [float] NULL ,
> [DATA7] [float] NULL ,
> [DATA8] [float] NULL ,
> [SUBSIZE] [smallint] NULL
> ) ON [PRIMARY]
> GO
> CREATE UNIQUE INDEX [VD1] ON [dbo].[VDATATABLE]([PARTNO],
> [DATETIME]) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [D1_1] ON [dbo].[VDATATABLE]([UDL1], [DATETIME]) WITH
> FILLFACTOR = 90 ON [PRIMARY]
> GO
>
> ---
> --RESULTS ON OLD SERVER - SQL SERVER 7.0 SP4--
> ---
> --DBCC SHOWCONTIG (549576996) for the VDATATABLE table only--
> DBCC SHOWCONTIG scanning 'VDATATABLE' table...
> Table: 'VDATATABLE' (549576996); index ID: 0, database ID: 8
> TABLE level scan performed.
> - Pages Scanned........................: 1054982
> - Extents Scanned.......................: 131977
> - Extent Switches.......................: 131976
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.92% [131873:131977]
> - Extent Scan Fragmentation ...............: 55.37%
> - Avg. Bytes Free per Page................: 291.0
> - Avg. Page Density (full)................: 96.40%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> --DBCC SHOWCONTIG (549576996, 2) for the VD1 index--
> DBCC SHOWCONTIG scanning 'VDATATABLE' table...
> Table: 'VDATATABLE' (549576996); index ID: 2, database ID: 8
> LEAF level scan performed.
> - Pages Scanned........................: 233772
> - Extents Scanned.......................: 29276
> - Extent Switches.......................: 31519
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 92.71% [29222:31520]
> - Logical Scan Fragmentation ..............: 10.67%
> - Extent Scan Fragmentation ...............: 20.65%
> - Avg. Bytes Free per Page................: 783.1
> - Avg. Page Density (full)................: 90.32%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> --DBCC SHOWCONTIG (549576996, 6) for the D1_1 index--
> DBCC SHOWCONTIG scanning 'VDATATABLE' table...
> Table: 'VDATATABLE' (549576996); index ID: 6, database ID: 8
> LEAF level scan performed.
> - Pages Scanned........................: 234618
> - Extents Scanned.......................: 29389
> - Extent Switches.......................: 32244
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 90.95% [29328:32245]
> - Logical Scan Fragmentation ..............: 9.92%
> - Extent Scan Fragmentation ...............: 26.84%
> - Avg. Bytes Free per Page................: 809.5
> - Avg. Page Density (full)................: 90.00%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> --sp_spaceused 'VDATATABLE'--
> name rows reserved data index_size unused
> VDATATABLE 28484584 14317992 KB 8427624 KB 5701432 KB 188936 KB
> ----
> --END OF RESULTS ON OLD SERVER - SQL SERVER 7.0 SP4--
> ----
>|||Just the data file is growing. There are no new DTS/BCP or any other
kind of mass imports. There are only the two unclustered indexes, but
looking at the results of DBCC SHOWCONTIG for them I don't believe that
they are the problem. I was only thinking of temporarily changing the
recovery mode to simple while creating/recreating/defragmenting the
indexes as I've read that creating/recreating/defragmenting indexes
will basically write all of the data from the table into the
transaction log, at least in some cases. After I get the free space
back I would change the recovery mode back to Bulk Logged.
Will adding a clustered index get rid of the vast majority of the extra
"unused" space (as reported by sp_spaceused) as that is what I believe
is showing the problem the best? Also, can users still access the table
while a clustered index is being created?
I've already read through that link.
Thanks,
Peter|||Peter,
Check your growth settings for the data file...may be set too high. If
there is a ton of extra space in the data file (i.e., its not consumed by
data) try DBCC SHRINKFILE for the data file. If you create a clustered
index no users can access the data while the clustered index is being built.
HTH
Jerry
"Peter" <peter_kwas@.yahoo.ca> wrote in message
news:1128702181.479150.179770@.g14g2000cwa.googlegroups.com...
> Just the data file is growing. There are no new DTS/BCP or any other
> kind of mass imports. There are only the two unclustered indexes, but
> looking at the results of DBCC SHOWCONTIG for them I don't believe that
> they are the problem. I was only thinking of temporarily changing the
> recovery mode to simple while creating/recreating/defragmenting the
> indexes as I've read that creating/recreating/defragmenting indexes
> will basically write all of the data from the table into the
> transaction log, at least in some cases. After I get the free space
> back I would change the recovery mode back to Bulk Logged.
> Will adding a clustered index get rid of the vast majority of the extra
> "unused" space (as reported by sp_spaceused) as that is what I believe
> is showing the problem the best? Also, can users still access the table
> while a clustered index is being created?
> I've already read through that link.
> Thanks,
> Peter
>|||The growth settings are set to the same as they were on the old
server...autogrow by 10% with unlimited growth. If I look in the
taskpad in Enterprise Manager, the "used" part of the data file has
grown astronomically with the same growth as the physical database
file. Judging by the results of sp_spaceused, it appears that the extra
space is being reserved, but not used, at the table level, thus showing
it as "used" in the in the taskpad in EM.
Any ballpark guesses of how long it will take to create a clustered
index on this table (server is a quad 700MHz with 2GB RAM with RAID10)?
Thanks,
Peter|||Peter,
10% of 80GB is 8GB growth increments. Have you run a DBCC SHRINKFILE? How
long will it take to create the clustered index? It depends - don't have an
exact answer for you. Also, remember that you'll need extra space in the
data file to do this as well as in the t-log. Also remember that when the
clustered index is built the nonclustered indexes will be rebuilt as well to
replace the ROWID with the CI KEY. I'd test this on a test box first to get
an estimation of the time required.
HTH
Jerry
"Peter" <peter_kwas@.yahoo.ca> wrote in message
news:1128703499.709063.125620@.o13g2000cwo.googlegroups.com...
> The growth settings are set to the same as they were on the old
> server...autogrow by 10% with unlimited growth. If I look in the
> taskpad in Enterprise Manager, the "used" part of the data file has
> grown astronomically with the same growth as the physical database
> file. Judging by the results of sp_spaceused, it appears that the extra
> space is being reserved, but not used, at the table level, thus showing
> it as "used" in the in the taskpad in EM.
> Any ballpark guesses of how long it will take to create a clustered
> index on this table (server is a quad 700MHz with 2GB RAM with RAID10)?
> Thanks,
> Peter
>|||I understand that it will grow in 8GB increments when it's 80GB,
however, the table/database is still growing...it has continued to grow
by 10% every couple of days ever since the SQL Server 2000 upgrade. It
seems like it is never actually using the "unused" space, but just
keeps expanding the data file and reserved space for the table.
The "unused" column in sp_spaceused appears to be growing by the exact
same rate that the database file grows, minus the negligible amount of
data that is being inserted. i.e. the rowcount has only increased by
672,000 rows (of a total 29,157,311 rows) since the upgrades, but the
database size has grown by 50GB and unused space reported by
sp_spaceused has grown from roughly 200,000 KB to almost 25GB!!!
It's sounding like adding a clustered index will likely be the most
beneficial, I'm just really hoping that fixes the problem
permanently...and that it doesn't take a week to run.
Thanks,
Petersql
Posted by Chasidy at 9:57 PM 0 comments
Labels: , , , , , , , , , ,

Monday, February 13, 2012

ASP.NET Report Viewer Version 9.0 problem.

I recently migrated my development server from Windows Server 2003 \ IIS 6 \
.NET Framework 2 and SQL 2005 to Windows Server 2008 \ IIS 7 \ .NET Framework
3.5 and SQL 2005 SP2. SQL install also includes Reporting Services. On my dev
workstation I have upgraded to from vs2005 to vs2008 and installed the
correct AjaxToolKit 3.5 dll.
I have a web project that uses SQL reporting services and Report Viewer 8.0
which used to work ok before the migration. I have replaced the Report Viewer
8.0 control within my web project to the new vs2008 Report Viewer 9.0 and
replaced all references (web.config etc) from 8.0 to 9.0. I have also
installed the Report Viewer 9.0 download on my development server &
workstation and rebooted.
When I now run my project the report viewer control does not render my
report (or even display the default icons for the report control such as
print icon etc).
I get a client side javascript error in IE7 stating â'Error: this.Controller
is null or not an objectâ'. If I try the site on FireFox the report displays
â'404 File or Directory not foundâ' within the report viewer.
Im running the report within a user control. Please find code for the uc
containing the report viewer and associated data objects below...
<%@. Control Language="C#" AutoEventWireup="true"
CodeFile="Reporting_MainSite.ascx.cs" Inherits="NPApplets_Reporting_MainSite"
%>
<%@. Register Assembly="Microsoft.ReportViewer.WebForms, Version=9.0.0.0,
Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"
Namespace="Microsoft.Reporting.WebForms" TagPrefix="rsweb" %>
<rsweb:ReportViewer ID="ReportViewer1" runat="server" Font-Names="Verdana"
Font-Size="8pt" Height="325px" Width="100%" BackColor="#95B7F3">
<LocalReport ReportPath="Report_SiteTraffic.rdlc"
EnableExternalImages="True">
<DataSources>
<rsweb:ReportDataSource DataSourceId="ObjectDataSource1"
Name="DataSet1_Report_LastMonth" />
<rsweb:ReportDataSource DataSourceId="ObjectDataSource2"
Name="DataSet2_Report_Last12Months" />
<rsweb:ReportDataSource DataSourceId="ObjectDataSource3"
Name="DataSet3_Report_Last12MonthsUBS" />
<rsweb:ReportDataSource DataSourceId="ObjectDataSource4"
Name="DataSet4_Report_SinceLaunch" />
</DataSources>
</LocalReport>
</rsweb:ReportViewer>
<asp:ObjectDataSource ID="ObjectDataSource4" runat="server"
SelectMethod="GetData"
TypeName="DataSet4TableAdapters.Report_SinceLaunchTableAdapter"
OldValuesParameterFormatString="original_{0}">
<SelectParameters>
<asp:ControlParameter ControlID="lbWondersiteToReportOn"
Name="Wondersite_FullName"
PropertyName="Text" Type="String" />
</SelectParameters>
</asp:ObjectDataSource>
<asp:ObjectDataSource ID="ObjectDataSource3" runat="server"
SelectMethod="GetData"
TypeName="DataSet3TableAdapters.Report_Last12MonthsUBSTableAdapter"
OldValuesParameterFormatString="original_{0}">
<SelectParameters>
<asp:ControlParameter ControlID="lbWondersiteToReportOn"
Name="Wondersite_FullName"
PropertyName="Text" Type="String" />
</SelectParameters>
</asp:ObjectDataSource>
<asp:ObjectDataSource ID="ObjectDataSource2" runat="server"
SelectMethod="GetData"
TypeName="DataSet2TableAdapters.Report_Last12MonthsTableAdapter"
OldValuesParameterFormatString="original_{0}" >
<SelectParameters>
<asp:ControlParameter ControlID="lbWondersiteToReportOn"
Name="Wondersite_FullName"
PropertyName="Text" Type="String" />
</SelectParameters>
</asp:ObjectDataSource>
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
SelectMethod="GetData"
TypeName="DataSet1TableAdapters.Report_LastMonthTableAdapter"
OldValuesParameterFormatString="original_{0}" >
<SelectParameters>
<asp:ControlParameter ControlID="lbWondersiteToReportOn"
Name="Wondersite_FullName"
PropertyName="Text" Type="String" />
</SelectParameters>
</asp:ObjectDataSource>
<asp:Label ID="lbWondersiteToReportOn" runat="server"
Visible="False"></asp:Label>
Within VS2008 designer the Report Viewer control shows the controls icons
correctly (print icon etc).
I have 4 data objects that my report uses. If I try and modify the data
source objects via the Smart Tag > Configure Data Source I get the following
error...
"The type 'DataSet4TableAdapters.Report_SinceLaunchTableAdapter' could not
be loaded. If the type is located in the App_Code folder, please check that
it compiles. If the type is located in a compiled assembly, pleaee check that
the assembly is referenced by the project.
The type "DataSet4TableAdapetrs.Report_SinceLaunchTableAdapter' could not be
foundâ'"
I have checked my DataSet.xsd files located in my App_Code directory and
everything compiles ok.
Im now stumped as what to try next. Any help, advice or pointers would be
very much appreciated.
Thanks in advance,
Paul.Managed to track down a fix...
Open Internet Information Services (IIS) Manager and select your Web
application.
Under IIS area, double-click on Handler Mappings icon.
At the Action pane on your right, click on Add Managed Handler.
At the Add Managed Handler dialog, enter the following:
Request path: Reserved.ReportViewerWebControl.axd
Type: Microsoft.Reporting.WebForms.HttpHandler
Name: Reserved-ReportViewerWebControl-axd
Click OK.
http://otkfounder.blogspot.com/2007/11/solving-reportviewer-rendering-issue-on.html
Paul.
"Paul Hale" wrote:
> I recently migrated my development server from Windows Server 2003 \ IIS 6 \
> .NET Framework 2 and SQL 2005 to Windows Server 2008 \ IIS 7 \ .NET Framework
> 3.5 and SQL 2005 SP2. SQL install also includes Reporting Services. On my dev
> workstation I have upgraded to from vs2005 to vs2008 and installed the
> correct AjaxToolKit 3.5 dll.
> I have a web project that uses SQL reporting services and Report Viewer 8.0
> which used to work ok before the migration. I have replaced the Report Viewer
> 8.0 control within my web project to the new vs2008 Report Viewer 9.0 and
> replaced all references (web.config etc) from 8.0 to 9.0. I have also
> installed the Report Viewer 9.0 download on my development server &
> workstation and rebooted.
> When I now run my project the report viewer control does not render my
> report (or even display the default icons for the report control such as
> print icon etc).
> I get a client side javascript error in IE7 stating â'Error: this.Controller
> is null or not an objectâ'. If I try the site on FireFox the report displays
> â'404 File or Directory not foundâ' within the report viewer.
> Im running the report within a user control. Please find code for the uc
> containing the report viewer and associated data objects below...
> <%@. Control Language="C#" AutoEventWireup="true"
> CodeFile="Reporting_MainSite.ascx.cs" Inherits="NPApplets_Reporting_MainSite"
> %>
> <%@. Register Assembly="Microsoft.ReportViewer.WebForms, Version=9.0.0.0,
> Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"
> Namespace="Microsoft.Reporting.WebForms" TagPrefix="rsweb" %>
> <rsweb:ReportViewer ID="ReportViewer1" runat="server" Font-Names="Verdana"
> Font-Size="8pt" Height="325px" Width="100%" BackColor="#95B7F3">
> <LocalReport ReportPath="Report_SiteTraffic.rdlc"
> EnableExternalImages="True">
> <DataSources>
> <rsweb:ReportDataSource DataSourceId="ObjectDataSource1"
> Name="DataSet1_Report_LastMonth" />
> <rsweb:ReportDataSource DataSourceId="ObjectDataSource2"
> Name="DataSet2_Report_Last12Months" />
> <rsweb:ReportDataSource DataSourceId="ObjectDataSource3"
> Name="DataSet3_Report_Last12MonthsUBS" />
> <rsweb:ReportDataSource DataSourceId="ObjectDataSource4"
> Name="DataSet4_Report_SinceLaunch" />
> </DataSources>
> </LocalReport>
> </rsweb:ReportViewer>
> <asp:ObjectDataSource ID="ObjectDataSource4" runat="server"
> SelectMethod="GetData"
> TypeName="DataSet4TableAdapters.Report_SinceLaunchTableAdapter"
> OldValuesParameterFormatString="original_{0}">
> <SelectParameters>
> <asp:ControlParameter ControlID="lbWondersiteToReportOn"
> Name="Wondersite_FullName"
> PropertyName="Text" Type="String" />
> </SelectParameters>
> </asp:ObjectDataSource>
> <asp:ObjectDataSource ID="ObjectDataSource3" runat="server"
> SelectMethod="GetData"
> TypeName="DataSet3TableAdapters.Report_Last12MonthsUBSTableAdapter"
> OldValuesParameterFormatString="original_{0}">
> <SelectParameters>
> <asp:ControlParameter ControlID="lbWondersiteToReportOn"
> Name="Wondersite_FullName"
> PropertyName="Text" Type="String" />
> </SelectParameters>
> </asp:ObjectDataSource>
> <asp:ObjectDataSource ID="ObjectDataSource2" runat="server"
> SelectMethod="GetData"
> TypeName="DataSet2TableAdapters.Report_Last12MonthsTableAdapter"
> OldValuesParameterFormatString="original_{0}" >
> <SelectParameters>
> <asp:ControlParameter ControlID="lbWondersiteToReportOn"
> Name="Wondersite_FullName"
> PropertyName="Text" Type="String" />
> </SelectParameters>
> </asp:ObjectDataSource>
> <asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
> SelectMethod="GetData"
> TypeName="DataSet1TableAdapters.Report_LastMonthTableAdapter"
> OldValuesParameterFormatString="original_{0}" >
> <SelectParameters>
> <asp:ControlParameter ControlID="lbWondersiteToReportOn"
> Name="Wondersite_FullName"
> PropertyName="Text" Type="String" />
> </SelectParameters>
> </asp:ObjectDataSource>
> <asp:Label ID="lbWondersiteToReportOn" runat="server"
> Visible="False"></asp:Label>
> Within VS2008 designer the Report Viewer control shows the controls icons
> correctly (print icon etc).
> I have 4 data objects that my report uses. If I try and modify the data
> source objects via the Smart Tag > Configure Data Source I get the following
> error...
> "The type 'DataSet4TableAdapters.Report_SinceLaunchTableAdapter' could not
> be loaded. If the type is located in the App_Code folder, please check that
> it compiles. If the type is located in a compiled assembly, pleaee check that
> the assembly is referenced by the project.
> The type "DataSet4TableAdapetrs.Report_SinceLaunchTableAdapter' could not be
> foundâ'"
> I have checked my DataSet.xsd files located in my App_Code directory and
> everything compiles ok.
> Im now stumped as what to try next. Any help, advice or pointers would be
> very much appreciated.
> Thanks in advance,
> Paul.
>
Posted by Chasidy at 11:37 PM 0 comments
Labels: , , , , , , , , , , , , , ,
Subscribe to: Posts (Atom)