Prev: How best to find out who did what when
Next: how to logon?
From: Scott Nichol on 13 May 2010 12:06
I recently migrated a 1.4 TB database from SQL Server 2000
to 2008. More recently I did a DBCC UPDATEUSAGE on tables that
are not written to 24/7 (e.g. appended to during a nightly window but
only read from throughout the day). Now sp_spaceused shows some tables
with a significant amount of unused space. As the tables
grow, the amount of unused space is increasing, not decreasing;
SQL Server does not seem to be using this already reserved space
(or uses it but then reserves more).

1. Is there a best way to "unreserve" the unused space while
maintaining 24/7 access to a table? For tables with a CI,
I tried reorganizing all the indexes on a couple of tables.
That reduced both reserved and unused space tremendously
(although for my 569 GB table that method would produce an
unmanageable boatload of transaction log). Reorganizing
the indexes on my heaps, however, did virtually nothing.
I know I can bcp each table out and then back in, for example,
but I need to keep the data on line.

2. Is there a way to prevent SQL Server from continuously
increasing the amount of unused space on some of these tables?

Top 10 Unused

Table Reserved Unused CI
------------------ --------------- -------------- --
A 27,778,940,928 21,977,677,824 N
B 6,290,489,344 5,019,385,856 N
C 569,301,377,024 2,846,048,256 Y
D 17,194,573,824 1,956,167,680 N
E 29,214,679,040 1,654,718,464 N
F 10,290,823,168 1,258,930,176 Y
G 8,466,718,720 1,157,922,816 Y
H 6,208,151,552 603,234,304 N
I 6,244,646,912 546,095,104 Y
J 6,201,270,272 382,017,536 Y

TIA
--
Scott Nichol


From: nzrdb6 on 13 May 2010 12:16
On May 13, 5:06 pm, "Scott Nichol" <snicholn...(a)scottnichol.com>
wrote:
> I recently migrated a 1.4 TB database from SQL Server 2000
> to 2008.  More recently I did a DBCC UPDATEUSAGE on tables that
> are not written to 24/7 (e.g. appended to during a nightly window but
> only read from throughout the day).  Now sp_spaceused shows some tables
> with a significant amount of unused space.  As the tables
> grow, the amount of unused space is increasing, not decreasing;
> SQL Server does not seem to be using this already reserved space
> (or uses it but then reserves more).
>
> 1. Is there a best way to "unreserve" the unused space while
> maintaining 24/7 access to a table?  For tables with a CI,
> I tried reorganizing all the indexes on a couple of tables.
> That reduced both reserved and unused space tremendously
> (although for my 569 GB table that method would produce an
> unmanageable boatload of transaction log).  Reorganizing
> the indexes on my heaps, however, did virtually nothing.
> I know I can bcp each table out and then back in, for example,
> but I need to keep the data on line.
>
> 2. Is there a way to prevent SQL Server from continuously
> increasing the amount of unused space on some of these tables?
>
> Top 10 Unused
>
> Table                      Reserved          Unused  CI
> ------------------  ---------------  --------------  --
> A                    27,778,940,928  21,977,677,824   N
> B                     6,290,489,344   5,019,385,856   N
> C                   569,301,377,024   2,846,048,256   Y
> D                    17,194,573,824   1,956,167,680   N
> E                    29,214,679,040   1,654,718,464   N
> F                    10,290,823,168   1,258,930,176   Y
> G                     8,466,718,720   1,157,922,816   Y
> H                     6,208,151,552     603,234,304   N
> I                     6,244,646,912     546,095,104   Y
> J                     6,201,270,272     382,017,536   Y
>
> TIA
> --
> Scott Nichol

2 things to try - #1 defrag / reindex the table #2 dbcc cleantable
From: Erland Sommarskog on 13 May 2010 17:40
Scott Nichol (snicholnews(a)scottnichol.com) writes:
> 2. Is there a way to prevent SQL Server from continuously
> increasing the amount of unused space on some of these tables?
>
> Top 10 Unused
>
> Table Reserved Unused CI
> ------------------ --------------- -------------- --
> A 27,778,940,928 21,977,677,824 N
> B 6,290,489,344 5,019,385,856 N

Those two tables has an hefty amonut of unused spacd. (For the
others I would not care). Both are heaps. Adding a clustered
index is the best way to tackle this issue. Heaps are prone
to fragmentation and bad disk usage.

Note that you if you have Enterprise Edition, you can use the
ONLINE keyword when you create index, to keep the table online.




--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: How best to find out who did what when
Next: how to logon?