Issue with unclaimed space

Hi all,
I am not sure whether someone has experienced the same issue as I do.
I have a table which has a column with text data type and a few other
regular columns (int, datetime etc). There are inserts / deletes on this
table, but the weird thing is the table is keeping on growing, but the row
number is not (because of delete actions), for example, when the table has
10000 rows, it has 150GB, but when it has 6500 rows, it has 179GB, actualy
when I execute sp_spaceused 'mytable' to monitor, I can see the rows are
dynamically changing, i.e. sometimes more rows, sometimes fewer rows, but
the data column in the sp_spaceused result set has the value growing
continously.
My environment is SQL2K5 EE (ver 9.0.3054).
In my test environment, I cannot repeat this production issue.
Does anyone encounter the same issue before?
TIA,
Jeff_Yao
Dear Jeff,
Thank you for posting here.
As the data type in this table in question is "Text" , the size of the Text
type column may not be proportional to the number of the rows because it
depends on the data size in each field of the Text type column. For
example, if one row contains the extreme large data in the Text column, the
size of the whole table may grow even when the number of rows are less than
before. So, it is an expected behavior in some situations.
At this stage, you can first use the following sp_spaceused command to
check the whole database to see if there is some unallocated space we can
release.
use dbname
go
sp_spaceused @.updateusage = N'TRUE'
If so, please first backup the database and then run the DBCC SHRINKFILE to
shrink the data file. For more information, please refer to the following
website:
DBCC SHRINKFILE (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms189493.aspx
If anything is unclear in my post, please don't hesitate to let me know and
I will be glad to help.
Have a nice day!
Best regards,
Adams Qu, MCSE, MCDBA, MCTS
Microsoft Online Support
Microsoft Global Technical Support Center
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "jeff_yao" <jeff_yao@.community.nospam>
| Subject: Issue with unclaimed space
| Date: Tue, 19 Feb 2008 00:45:34 -0800
| Lines: 23
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
| x-mimeole: Produced By Microsoft MimeOLE V6.00.2900.3198
| X-RFC2646: Format=Flowed; Original
| Message-ID: <eAwbKPtcIHA.1376@.TK2MSFTNGP02.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: d154-5-0-112.bchsia.telus.net 154.5.0.112
| Path: TK2MSFTNGHUB02.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSF TNGP02.phx.gbl
| Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:37052
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Hi all,
|
| I am not sure whether someone has experienced the same issue as I do.
|
| I have a table which has a column with text data type and a few other
| regular columns (int, datetime etc). There are inserts / deletes on this
| table, but the weird thing is the table is keeping on growing, but the
row
| number is not (because of delete actions), for example, when the table
has
| 10000 rows, it has 150GB, but when it has 6500 rows, it has 179GB,
actualy
| when I execute sp_spaceused 'mytable' to monitor, I can see the rows are
| dynamically changing, i.e. sometimes more rows, sometimes fewer rows, but
| the data column in the sp_spaceused result set has the value growing
| continously.
| My environment is SQL2K5 EE (ver 9.0.3054).
|
| In my test environment, I cannot repeat this production issue.
|
| Does anyone encounter the same issue before?
|
| TIA,
| Jeff_Yao
|
|
|
|||Using SHRINKFILE to free up unused space that is allocated to a table
seems like an extreme and inefficient approach. The question was
posed about a specific table, and it would seem better to look for a
solution that deals with the table and not the entire database. Are
you saying that ALTER INDEX with REBUILD or REORGANIZE (and
LOB_COMPACTION = ON) would not free the unused space?
Roy Harvey
Beacon Falls, CT
On Tue, 19 Feb 2008 10:36:00 GMT, v-adamqu@.online.microsoft.com (Adams
Qu [MSFT]) wrote:

>Dear Jeff,
>Thank you for posting here.
>As the data type in this table in question is "Text" , the size of the Text
>type column may not be proportional to the number of the rows because it
>depends on the data size in each field of the Text type column. For
>example, if one row contains the extreme large data in the Text column, the
>size of the whole table may grow even when the number of rows are less than
>before. So, it is an expected behavior in some situations.
>At this stage, you can first use the following sp_spaceused command to
>check the whole database to see if there is some unallocated space we can
>release.
>use dbname
>go
>sp_spaceused @.updateusage = N'TRUE'
>If so, please first backup the database and then run the DBCC SHRINKFILE to
>shrink the data file. For more information, please refer to the following
>website:
>DBCC SHRINKFILE (Transact-SQL)
>http://msdn2.microsoft.com/en-us/library/ms189493.aspx
>If anything is unclear in my post, please don't hesitate to let me know and
>I will be glad to help.
>Have a nice day!
>Best regards,
>Adams Qu, MCSE, MCDBA, MCTS
>Microsoft Online Support
>Microsoft Global Technical Support Center
>Get Secure! - www.microsoft.com/security
>================================================= ====
>When responding to posts, please "Reply to Group" via your newsreader so
>that others may learn and benefit from your issue.
>================================================= ====
>This posting is provided "AS IS" with no warranties, and confers no rights.
>--
>| From: "jeff_yao" <jeff_yao@.community.nospam>
>| Subject: Issue with unclaimed space
>| Date: Tue, 19 Feb 2008 00:45:34 -0800
>| Lines: 23
>| X-Priority: 3
>| X-MSMail-Priority: Normal
>| X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
>| x-mimeole: Produced By Microsoft MimeOLE V6.00.2900.3198
>| X-RFC2646: Format=Flowed; Original
>| Message-ID: <eAwbKPtcIHA.1376@.TK2MSFTNGP02.phx.gbl>
>| Newsgroups: microsoft.public.sqlserver.server
>| NNTP-Posting-Host: d154-5-0-112.bchsia.telus.net 154.5.0.112
>| Path: TK2MSFTNGHUB02.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSF TNGP02.phx.gbl
>| Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:37052
>| X-Tomcat-NG: microsoft.public.sqlserver.server
>|
>| Hi all,
>|
>| I am not sure whether someone has experienced the same issue as I do.
>|
>| I have a table which has a column with text data type and a few other
>| regular columns (int, datetime etc). There are inserts / deletes on this
>| table, but the weird thing is the table is keeping on growing, but the
>row
>| number is not (because of delete actions), for example, when the table
>has
>| 10000 rows, it has 150GB, but when it has 6500 rows, it has 179GB,
>actualy
>| when I execute sp_spaceused 'mytable' to monitor, I can see the rows are
>| dynamically changing, i.e. sometimes more rows, sometimes fewer rows, but
>| the data column in the sp_spaceused result set has the value growing
>| continously.
>| My environment is SQL2K5 EE (ver 9.0.3054).
>|
>| In my test environment, I cannot repeat this production issue.
>|
>| Does anyone encounter the same issue before?
>|
>| TIA,
>| Jeff_Yao
>|
>|
>|
|||Thanks Roy and Adams for the reply.
This table has a clustered index, but after I drop and recreate it, it does
not help either. LOB_COMPACTION = ON is the default option and I did not
explicitly specify it. On the other hand, the index is based on an interger
column, so it does not suprise me if reindex does not solve the issue.
To Adams Qu [MSFT], I'd like to answer your suggestions / concerns here too.
As a dba starting with ver 6.5, I of course understand "the size of the Text
type column may not be proportional to the number of the rows", what I
observed is even when a row is deleted (with no new rows inserted), the
sp_spaceused (with @.updateusage='true') reports no decreased data size, but
as soon as a new row is inserted, sp_spaceused will show increased data
size. The puzzle here is the table size has NEVER decreased over a long time
and in terms of each row, the text column should be more or less identical
in size. I have recorded the table size for the last 30 days, see following
for details (size data are in KB)
TblName rows reserved data index_size unused LogDate
MyTable 697999 72518648 72330272 53152 135224 17-Jan-2008
MyTable 702841 75228552 75039912 53192 135448 18-Jan-2008
MyTable 706909 77616144 77427256 53208 135680 19-Jan-2008
MyTable 708712 78533968 78345048 53208 135712 20-Jan-2008
MyTable 711520 80033232 79844232 53216 135784 21-Jan-2008
MyTable 715487 82224280 82035128 53224 135928 22-Jan-2008
MyTable 722378 85085920 84896432 53248 136240 23-Jan-2008
MyTable 729030 87924840 87735112 53272 136456 24-Jan-2008
MyTable 735067 90753064 90563008 53280 136776 25-Jan-2008
MyTable 742325 93237808 93047496 53304 137008 26-Jan-2008
MyTable 745192 94787448 94596960 53312 137176 27-Jan-2008
MyTable 750447 97794424 97603648 53328 137448 28-Jan-2008
MyTable 759321 102154296 101963176 53360 137760 29-Jan-2008
MyTable 768464 106502824 106311240 53440 138144 30-Jan-2008
MyTable 777361 111264944 111072712 53480 138752 31-Jan-2008
MyTable 785317 115374392 115181664 53512 139216 1-Feb-2008
MyTable 790329 118687736 118494720 53520 139496 2-Feb-2008
MyTable 793007 120391928 120198856 53528 139544 3-Feb-2008
MyTable 797381 123205944 123012592 53544 139808 4-Feb-2008
MyTable 805268 127963832 127770056 53568 140208 5-Feb-2008
MyTable 812031 131762168 131568032 53592 140544 6-Feb-2008
MyTable 817958 135148280 134953872 53608 140800 7-Feb-2008
MyTable 823068 138176064 137981360 53624 141080 8-Feb-2008
MyTable 826878 140412488 140217496 53640 141352 9-Feb-2008
MyTable 829234 141999752 141804592 53648 141512 10-Feb-2008
MyTable 833843 145251208 145055840 53656 141712 11-Feb-2008
MyTable 840203 149432784 149237104 53688 141992 12-Feb-2008
MyTable 846171 153050064 152854104 53704 142256 13-Feb-2008
MyTable 852032 156654488 156458232 53736 142520 14-Feb-2008
MyTable 11670 159944352 159671584 82320 190448 15-Feb-2008
MyTable 11776 163975840 163674936 78920 221984 16-Feb-2008
MyTable 8573 165295776 164999280 76680 219816 17-Feb-2008
MyTable 6477 167538536 167259824 73208 205504 18-Feb-2008
MyTable 10171 170841784 170769184 824 71776 19-Feb-2008
Notice the last row, the index size is smaller, but it was a result of my
index rebuild (drop & recreate for clustered index). But on the other hand
data size keeps on growing no matter how many rows inside the table.
I think there is one solution which I will implement later when a
maintenance window comes, i.e.
1. select * into <TempTable> from MyTable
2. truncate table MyTable
3. insert into MyTable Select * from <TempTable>
4. drop table <TempTable>
5. check the MyTable size again
I come to this proposed solution because I have calculated the sum of the
datalength of the text column (stores an XML file), and it returns me only
760 MB, giving some overhead (the other four interger, datetime columns can
be omitted here), I think 8GB should be sufficient for the whole table (i.e.
760 MB x 10 = 8 GB)
If this works, I guess there must be something wrong with SQL Server engine,
and at that time I hope Adams can provide us some internal whys :-)
I will update you here once I implemented my proposal.
Thanks,
Jeff
"Roy Harvey (SQL Server MVP)" <roy_harvey@.snet.net> wrote in message
news:vuimr39kk22u54rtleji0hb3lu0jhr5k9b@.4ax.com... [vbcol=seagreen]
> Using SHRINKFILE to free up unused space that is allocated to a table
> seems like an extreme and inefficient approach. The question was
> posed about a specific table, and it would seem better to look for a
> solution that deals with the table and not the entire database. Are
> you saying that ALTER INDEX with REBUILD or REORGANIZE (and
> LOB_COMPACTION = ON) would not free the unused space?
> Roy Harvey
> Beacon Falls, CT
> On Tue, 19 Feb 2008 10:36:00 GMT, v-adamqu@.online.microsoft.com (Adams
> Qu [MSFT]) wrote:
|||Dear Jeff,
Thank you for your detail response and clarifying the issue for me.
According to my experience, there is an known issue in SQL Server that the
space is not released after you use a DELETE statement to delete data from
a table. This kind of behavior is normally caused by the row
versioning-based isolation level, or Row-level lock. If pages are not
deallocated, other objects in the database cannot reuse the pages.
If it is the situation, one method we can try to correct the space is
truncating table and re-importing the data (just as what you intend to do).
However, I also suggest trying another method that including a "TABLOCK"
hint in the DELETE statement to make a table-level lock being held (if a
row versioning-based isolation level is not enabled) to test the issue
again. For example, use a statement that is similar to the following:
DELETE FROM <TableName> WITH (TABLOCK) where <condition>
If it is convenient, you can try to delete a test row via above command and
then verify the space again via the sp_spaceused. This method can help us
to determine if the issue is caused by this factor.
Please let me know the output at your convenience and I am glad to continue
to work with you.
Have a nice day!
Best regards,
Adams Qu
MCSE, MCDBA, MCTS
Microsoft Online Support
Microsoft Global Technical Support Center
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "jeff_yao" <jeff_yao@.community.nospam>
| References: <eAwbKPtcIHA.1376@.TK2MSFTNGP02.phx.gbl>
<$Org8MucIHA.5204@.TK2MSFTNGHUB02.phx.gbl>
<vuimr39kk22u54rtleji0hb3lu0jhr5k9b@.4ax.com>
| Subject: Re: Issue with unclaimed space
| Date: Tue, 19 Feb 2008 14:57:52 -0800
| Lines: 196
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
| x-mimeole: Produced By Microsoft MimeOLE V6.00.2900.3198
| X-RFC2646: Format=Flowed; Original
| Message-ID: <#HHOdr0cIHA.148@.TK2MSFTNGP04.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: d154-5-0-112.bchsia.telus.net 154.5.0.112
| Path: TK2MSFTNGHUB02.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSF TNGP04.phx.gbl
| Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:37092
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Thanks Roy and Adams for the reply.
|
| This table has a clustered index, but after I drop and recreate it, it
does
| not help either. LOB_COMPACTION = ON is the default option and I did not
| explicitly specify it. On the other hand, the index is based on an
interger
| column, so it does not suprise me if reindex does not solve the issue.
|
| To Adams Qu [MSFT], I'd like to answer your suggestions / concerns here
too.
| As a dba starting with ver 6.5, I of course understand "the size of the
Text
| type column may not be proportional to the number of the rows", what I
| observed is even when a row is deleted (with no new rows inserted), the
| sp_spaceused (with @.updateusage='true') reports no decreased data size,
but
| as soon as a new row is inserted, sp_spaceused will show increased data
| size. The puzzle here is the table size has NEVER decreased over a long
time
| and in terms of each row, the text column should be more or less
identical
| in size. I have recorded the table size for the last 30 days, see
following
| for details (size data are in KB)
|
| TblName rows reserved data index_size unused LogDate
| MyTable 697999 72518648 72330272 53152 135224 17-Jan-2008
| MyTable 702841 75228552 75039912 53192 135448 18-Jan-2008
| MyTable 706909 77616144 77427256 53208 135680 19-Jan-2008
| MyTable 708712 78533968 78345048 53208 135712 20-Jan-2008
| MyTable 711520 80033232 79844232 53216 135784 21-Jan-2008
| MyTable 715487 82224280 82035128 53224 135928 22-Jan-2008
| MyTable 722378 85085920 84896432 53248 136240 23-Jan-2008
| MyTable 729030 87924840 87735112 53272 136456 24-Jan-2008
| MyTable 735067 90753064 90563008 53280 136776 25-Jan-2008
| MyTable 742325 93237808 93047496 53304 137008 26-Jan-2008
| MyTable 745192 94787448 94596960 53312 137176 27-Jan-2008
| MyTable 750447 97794424 97603648 53328 137448 28-Jan-2008
| MyTable 759321 102154296 101963176 53360 137760 29-Jan-2008
| MyTable 768464 106502824 106311240 53440 138144 30-Jan-2008
| MyTable 777361 111264944 111072712 53480 138752 31-Jan-2008
| MyTable 785317 115374392 115181664 53512 139216 1-Feb-2008
| MyTable 790329 118687736 118494720 53520 139496 2-Feb-2008
| MyTable 793007 120391928 120198856 53528 139544 3-Feb-2008
| MyTable 797381 123205944 123012592 53544 139808 4-Feb-2008
| MyTable 805268 127963832 127770056 53568 140208 5-Feb-2008
| MyTable 812031 131762168 131568032 53592 140544 6-Feb-2008
| MyTable 817958 135148280 134953872 53608 140800 7-Feb-2008
| MyTable 823068 138176064 137981360 53624 141080 8-Feb-2008
| MyTable 826878 140412488 140217496 53640 141352 9-Feb-2008
| MyTable 829234 141999752 141804592 53648 141512 10-Feb-2008
| MyTable 833843 145251208 145055840 53656 141712 11-Feb-2008
| MyTable 840203 149432784 149237104 53688 141992 12-Feb-2008
| MyTable 846171 153050064 152854104 53704 142256 13-Feb-2008
| MyTable 852032 156654488 156458232 53736 142520 14-Feb-2008
| MyTable 11670 159944352 159671584 82320 190448 15-Feb-2008
| MyTable 11776 163975840 163674936 78920 221984 16-Feb-2008
| MyTable 8573 165295776 164999280 76680 219816 17-Feb-2008
| MyTable 6477 167538536 167259824 73208 205504 18-Feb-2008
| MyTable 10171 170841784 170769184 824 71776 19-Feb-2008
|
|
| Notice the last row, the index size is smaller, but it was a result of my
| index rebuild (drop & recreate for clustered index). But on the other
hand
| data size keeps on growing no matter how many rows inside the table.
|
| I think there is one solution which I will implement later when a
| maintenance window comes, i.e.
|
| 1. select * into <TempTable> from MyTable
| 2. truncate table MyTable
| 3. insert into MyTable Select * from <TempTable>
| 4. drop table <TempTable>
| 5. check the MyTable size again
|
| I come to this proposed solution because I have calculated the sum of
the
| datalength of the text column (stores an XML file), and it returns me
only
| 760 MB, giving some overhead (the other four interger, datetime columns
can
| be omitted here), I think 8GB should be sufficient for the whole table
(i.e.
| 760 MB x 10 = 8 GB)
|
| If this works, I guess there must be something wrong with SQL Server
engine,
| and at that time I hope Adams can provide us some internal whys :-)
|
| I will update you here once I implemented my proposal.
|
| Thanks,
|
| Jeff
|
| "Roy Harvey (SQL Server MVP)" <roy_harvey@.snet.net> wrote in message
| news:vuimr39kk22u54rtleji0hb3lu0jhr5k9b@.4ax.com...
| > Using SHRINKFILE to free up unused space that is allocated to a table
| > seems like an extreme and inefficient approach. The question was
| > posed about a specific table, and it would seem better to look for a
| > solution that deals with the table and not the entire database. Are
| > you saying that ALTER INDEX with REBUILD or REORGANIZE (and
| > LOB_COMPACTION = ON) would not free the unused space?
| >
| > Roy Harvey
| > Beacon Falls, CT
| >
| > On Tue, 19 Feb 2008 10:36:00 GMT, v-adamqu@.online.microsoft.com (Adams
| > Qu [MSFT]) wrote:
| >
| >>Dear Jeff,
| >>
| >>Thank you for posting here.
| >>
| >>As the data type in this table in question is "Text" , the size of the
| >>Text
| >>type column may not be proportional to the number of the rows because it
| >>depends on the data size in each field of the Text type column. For
| >>example, if one row contains the extreme large data in the Text column,
| >>the
| >>size of the whole table may grow even when the number of rows are less
| >>than
| >>before. So, it is an expected behavior in some situations.
| >>
| >>At this stage, you can first use the following sp_spaceused command to
| >>check the whole database to see if there is some unallocated space we
can
| >>release.
| >>
| >>use dbname
| >>go
| >>sp_spaceused @.updateusage = N'TRUE'
| >>
| >>If so, please first backup the database and then run the DBCC
SHRINKFILE
| >>to
| >>shrink the data file. For more information, please refer to the
following
| >>website:
| >>
| >>DBCC SHRINKFILE (Transact-SQL)
| >>http://msdn2.microsoft.com/en-us/library/ms189493.aspx
| >>
| >>If anything is unclear in my post, please don't hesitate to let me know
| >>and
| >>I will be glad to help.
| >>
| >>Have a nice day!
| >>
| >>Best regards,
| >>
| >>Adams Qu, MCSE, MCDBA, MCTS
| >>Microsoft Online Support
| >>
| >>Microsoft Global Technical Support Center
| >>
| >>Get Secure! - www.microsoft.com/security
| >>================================================ =====
| >>When responding to posts, please "Reply to Group" via your newsreader so
| >>that others may learn and benefit from your issue.
| >>================================================ =====
| >>This posting is provided "AS IS" with no warranties, and confers no
| >>rights.
| >>
| >>--
| >>| From: "jeff_yao" <jeff_yao@.community.nospam>
| >>| Subject: Issue with unclaimed space
| >>| Date: Tue, 19 Feb 2008 00:45:34 -0800
| >>| Lines: 23
| >>| X-Priority: 3
| >>| X-MSMail-Priority: Normal
| >>| X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
| >>| x-mimeole: Produced By Microsoft MimeOLE V6.00.2900.3198
| >>| X-RFC2646: Format=Flowed; Original
| >>| Message-ID: <eAwbKPtcIHA.1376@.TK2MSFTNGP02.phx.gbl>
| >>| Newsgroups: microsoft.public.sqlserver.server
| >>| NNTP-Posting-Host: d154-5-0-112.bchsia.telus.net 154.5.0.112
| >>| Path: TK2MSFTNGHUB02.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSF TNGP02.phx.gbl
| >>| Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:37052
| >>| X-Tomcat-NG: microsoft.public.sqlserver.server
| >>|
| >>| Hi all,
| >>|
| >>| I am not sure whether someone has experienced the same issue as I do.
| >>|
| >>| I have a table which has a column with text data type and a few other
| >>| regular columns (int, datetime etc). There are inserts / deletes on
this
| >>| table, but the weird thing is the table is keeping on growing, but the
| >>row
| >>| number is not (because of delete actions), for example, when the table
| >>has
| >>| 10000 rows, it has 150GB, but when it has 6500 rows, it has 179GB,
| >>actualy
| >>| when I execute sp_spaceused 'mytable' to monitor, I can see the rows
are
| >>| dynamically changing, i.e. sometimes more rows, sometimes fewer rows,
| >>but
| >>| the data column in the sp_spaceused result set has the value growing
| >>| continously.
| >>| My environment is SQL2K5 EE (ver 9.0.3054).
| >>|
| >>| In my test environment, I cannot repeat this production issue.
| >>|
| >>| Does anyone encounter the same issue before?
| >>|
| >>| TIA,
| >>| Jeff_Yao
| >>|
| >>|
| >>|
|
|
|
|||Dear Jeff,
We wanted to see if the information provided was helpful. Please keep us
posted on your progress and let us know if you have any additional
questions or concerns.
We are looking forward to your response.
Have a nice day!
Best regards,
Adams Qu
MCSE, MCDBA, MCTS
Microsoft Online Support
Microsoft Global Technical Support Center
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
| X-Tomcat-ID: 112810464
| References: <eAwbKPtcIHA.1376@.TK2MSFTNGP02.phx.gbl>
<$Org8MucIHA.5204@.TK2MSFTNGHUB02.phx.gbl>
<vuimr39kk22u54rtleji0hb3lu0jhr5k9b@.4ax.com>
<#HHOdr0cIHA.148@.TK2MSFTNGP04.phx.gbl>
| MIME-Version: 1.0
| Content-Type: text/plain
| Content-Transfer-Encoding: 7bit
| From: v-adamqu@.online.microsoft.com (Adams Qu [MSFT])
| Organization: Microsoft
| Date: Wed, 20 Feb 2008 11:31:22 GMT
| Subject: Re: Issue with unclaimed space
| X-Tomcat-NG: microsoft.public.sqlserver.server
| Message-ID: <RA1ghQ7cIHA.6844@.TK2MSFTNGHUB02.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| Lines: 291
| Path: TK2MSFTNGHUB02.phx.gbl
| Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:37107
| NNTP-Posting-Host: tk5tomimport1.phx.gbl 10.201.218.19
|
| Dear Jeff,
|
| Thank you for your detail response and clarifying the issue for me.
|
| According to my experience, there is an known issue in SQL Server that
the
| space is not released after you use a DELETE statement to delete data
from
| a table. This kind of behavior is normally caused by the row
| versioning-based isolation level, or Row-level lock. If pages are not
| deallocated, other objects in the database cannot reuse the pages.
|
| If it is the situation, one method we can try to correct the space is
| truncating table and re-importing the data (just as what you intend to
do).
| However, I also suggest trying another method that including a "TABLOCK"
| hint in the DELETE statement to make a table-level lock being held (if a
| row versioning-based isolation level is not enabled) to test the issue
| again. For example, use a statement that is similar to the following:
|
| DELETE FROM <TableName> WITH (TABLOCK) where <condition>
|
| If it is convenient, you can try to delete a test row via above command
and
| then verify the space again via the sp_spaceused. This method can help us
| to determine if the issue is caused by this factor.
|
| Please let me know the output at your convenience and I am glad to
continue
| to work with you.
|
| Have a nice day!
|
| Best regards,
|
| Adams Qu
| MCSE, MCDBA, MCTS
| Microsoft Online Support
|
| Microsoft Global Technical Support Center
|
| Get Secure! - www.microsoft.com/security
| ================================================== ===
| When responding to posts, please "Reply to Group" via your newsreader so
| that others may learn and benefit from your issue.
| ================================================== ===
| This posting is provided "AS IS" with no warranties, and confers no
rights.
|
|
| --
| | From: "jeff_yao" <jeff_yao@.community.nospam>
| | References: <eAwbKPtcIHA.1376@.TK2MSFTNGP02.phx.gbl>
| <$Org8MucIHA.5204@.TK2MSFTNGHUB02.phx.gbl>
| <vuimr39kk22u54rtleji0hb3lu0jhr5k9b@.4ax.com>
| | Subject: Re: Issue with unclaimed space
| | Date: Tue, 19 Feb 2008 14:57:52 -0800
| | Lines: 196
| | X-Priority: 3
| | X-MSMail-Priority: Normal
| | X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
| | x-mimeole: Produced By Microsoft MimeOLE V6.00.2900.3198
| | X-RFC2646: Format=Flowed; Original
| | Message-ID: <#HHOdr0cIHA.148@.TK2MSFTNGP04.phx.gbl>
| | Newsgroups: microsoft.public.sqlserver.server
| | NNTP-Posting-Host: d154-5-0-112.bchsia.telus.net 154.5.0.112
| | Path: TK2MSFTNGHUB02.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSF TNGP04.phx.gbl
| | Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:37092
| | X-Tomcat-NG: microsoft.public.sqlserver.server
| |
| | Thanks Roy and Adams for the reply.
| |
| | This table has a clustered index, but after I drop and recreate it, it
| does
| | not help either. LOB_COMPACTION = ON is the default option and I did
not
| | explicitly specify it. On the other hand, the index is based on an
| interger
| | column, so it does not suprise me if reindex does not solve the issue.
| |
| | To Adams Qu [MSFT], I'd like to answer your suggestions / concerns here
| too.
| | As a dba starting with ver 6.5, I of course understand "the size of the
| Text
| | type column may not be proportional to the number of the rows", what I
| | observed is even when a row is deleted (with no new rows inserted), the
| | sp_spaceused (with @.updateusage='true') reports no decreased data size,
| but
| | as soon as a new row is inserted, sp_spaceused will show increased data
| | size. The puzzle here is the table size has NEVER decreased over a long
| time
| | and in terms of each row, the text column should be more or less
| identical
| | in size. I have recorded the table size for the last 30 days, see
| following
| | for details (size data are in KB)
| |
| | TblName rows reserved data index_size unused LogDate
| | MyTable 697999 72518648 72330272 53152 135224 17-Jan-2008
| | MyTable 702841 75228552 75039912 53192 135448 18-Jan-2008
| | MyTable 706909 77616144 77427256 53208 135680 19-Jan-2008
| | MyTable 708712 78533968 78345048 53208 135712 20-Jan-2008
| | MyTable 711520 80033232 79844232 53216 135784 21-Jan-2008
| | MyTable 715487 82224280 82035128 53224 135928 22-Jan-2008
| | MyTable 722378 85085920 84896432 53248 136240 23-Jan-2008
| | MyTable 729030 87924840 87735112 53272 136456 24-Jan-2008
| | MyTable 735067 90753064 90563008 53280 136776 25-Jan-2008
| | MyTable 742325 93237808 93047496 53304 137008 26-Jan-2008
| | MyTable 745192 94787448 94596960 53312 137176 27-Jan-2008
| | MyTable 750447 97794424 97603648 53328 137448 28-Jan-2008
| | MyTable 759321 102154296 101963176 53360 137760 29-Jan-2008
| | MyTable 768464 106502824 106311240 53440 138144 30-Jan-2008
| | MyTable 777361 111264944 111072712 53480 138752 31-Jan-2008
| | MyTable 785317 115374392 115181664 53512 139216 1-Feb-2008
| | MyTable 790329 118687736 118494720 53520 139496 2-Feb-2008
| | MyTable 793007 120391928 120198856 53528 139544 3-Feb-2008
| | MyTable 797381 123205944 123012592 53544 139808 4-Feb-2008
| | MyTable 805268 127963832 127770056 53568 140208 5-Feb-2008
| | MyTable 812031 131762168 131568032 53592 140544 6-Feb-2008
| | MyTable 817958 135148280 134953872 53608 140800 7-Feb-2008
| | MyTable 823068 138176064 137981360 53624 141080 8-Feb-2008
| | MyTable 826878 140412488 140217496 53640 141352 9-Feb-2008
| | MyTable 829234 141999752 141804592 53648 141512 10-Feb-2008
| | MyTable 833843 145251208 145055840 53656 141712 11-Feb-2008
| | MyTable 840203 149432784 149237104 53688 141992 12-Feb-2008
| | MyTable 846171 153050064 152854104 53704 142256 13-Feb-2008
| | MyTable 852032 156654488 156458232 53736 142520 14-Feb-2008
| | MyTable 11670 159944352 159671584 82320 190448 15-Feb-2008
| | MyTable 11776 163975840 163674936 78920 221984 16-Feb-2008
| | MyTable 8573 165295776 164999280 76680 219816 17-Feb-2008
| | MyTable 6477 167538536 167259824 73208 205504 18-Feb-2008
| | MyTable 10171 170841784 170769184 824 71776 19-Feb-2008
| |
| |
| | Notice the last row, the index size is smaller, but it was a result of
my
| | index rebuild (drop & recreate for clustered index). But on the other
| hand
| | data size keeps on growing no matter how many rows inside the table.
| |
| | I think there is one solution which I will implement later when a
| | maintenance window comes, i.e.
| |
| | 1. select * into <TempTable> from MyTable
| | 2. truncate table MyTable
| | 3. insert into MyTable Select * from <TempTable>
| | 4. drop table <TempTable>
| | 5. check the MyTable size again
| |
| | I come to this proposed solution because I have calculated the sum of
| the
| | datalength of the text column (stores an XML file), and it returns me
| only
| | 760 MB, giving some overhead (the other four interger, datetime columns
| can
| | be omitted here), I think 8GB should be sufficient for the whole table
| (i.e.
| | 760 MB x 10 = 8 GB)
| |
| | If this works, I guess there must be something wrong with SQL Server
| engine,
| | and at that time I hope Adams can provide us some internal whys :-)
| |
| | I will update you here once I implemented my proposal.
| |
| | Thanks,
| |
| | Jeff
| |
| | "Roy Harvey (SQL Server MVP)" <roy_harvey@.snet.net> wrote in message
| | news:vuimr39kk22u54rtleji0hb3lu0jhr5k9b@.4ax.com...
| | > Using SHRINKFILE to free up unused space that is allocated to a table
| | > seems like an extreme and inefficient approach. The question was
| | > posed about a specific table, and it would seem better to look for a
| | > solution that deals with the table and not the entire database. Are
| | > you saying that ALTER INDEX with REBUILD or REORGANIZE (and
| | > LOB_COMPACTION = ON) would not free the unused space?
| | >
| | > Roy Harvey
| | > Beacon Falls, CT
| | >
| | > On Tue, 19 Feb 2008 10:36:00 GMT, v-adamqu@.online.microsoft.com (Adams
| | > Qu [MSFT]) wrote:
| | >
| | >>Dear Jeff,
| | >>
| | >>Thank you for posting here.
| | >>
| | >>As the data type in this table in question is "Text" , the size of
the
| | >>Text
| | >>type column may not be proportional to the number of the rows because
it
| | >>depends on the data size in each field of the Text type column. For
| | >>example, if one row contains the extreme large data in the Text
column,
| | >>the
| | >>size of the whole table may grow even when the number of rows are
less
| | >>than
| | >>before. So, it is an expected behavior in some situations.
| | >>
| | >>At this stage, you can first use the following sp_spaceused command to
| | >>check the whole database to see if there is some unallocated space we
| can
| | >>release.
| | >>
| | >>use dbname
| | >>go
| | >>sp_spaceused @.updateusage = N'TRUE'
| | >>
| | >>If so, please first backup the database and then run the DBCC
| SHRINKFILE
| | >>to
| | >>shrink the data file. For more information, please refer to the
| following
| | >>website:
| | >>
| | >>DBCC SHRINKFILE (Transact-SQL)
| | >>http://msdn2.microsoft.com/en-us/library/ms189493.aspx
| | >>
| | >>If anything is unclear in my post, please don't hesitate to let me
know
| | >>and
| | >>I will be glad to help.
| | >>
| | >>Have a nice day!
| | >>
| | >>Best regards,
| | >>
| | >>Adams Qu, MCSE, MCDBA, MCTS
| | >>Microsoft Online Support
| | >>
| | >>Microsoft Global Technical Support Center
| | >>
| | >>Get Secure! - www.microsoft.com/security
| | >>================================================ =====
| | >>When responding to posts, please "Reply to Group" via your newsreader
so
| | >>that others may learn and benefit from your issue.
| | >>================================================ =====
| | >>This posting is provided "AS IS" with no warranties, and confers no
| | >>rights.
| | >>
| | >>--
| | >>| From: "jeff_yao" <jeff_yao@.community.nospam>
| | >>| Subject: Issue with unclaimed space
| | >>| Date: Tue, 19 Feb 2008 00:45:34 -0800
| | >>| Lines: 23
| | >>| X-Priority: 3
| | >>| X-MSMail-Priority: Normal
| | >>| X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
| | >>| x-mimeole: Produced By Microsoft MimeOLE V6.00.2900.3198
| | >>| X-RFC2646: Format=Flowed; Original
| | >>| Message-ID: <eAwbKPtcIHA.1376@.TK2MSFTNGP02.phx.gbl>
| | >>| Newsgroups: microsoft.public.sqlserver.server
| | >>| NNTP-Posting-Host: d154-5-0-112.bchsia.telus.net 154.5.0.112
| | >>| Path:
TK2MSFTNGHUB02.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSF TNGP02.phx.gbl
| | >>| Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:37052
| | >>| X-Tomcat-NG: microsoft.public.sqlserver.server
| | >>|
| | >>| Hi all,
| | >>|
| | >>| I am not sure whether someone has experienced the same issue as I
do.
| | >>|
| | >>| I have a table which has a column with text data type and a few
other
| | >>| regular columns (int, datetime etc). There are inserts / deletes on
| this
| | >>| table, but the weird thing is the table is keeping on growing, but
the
| | >>row
| | >>| number is not (because of delete actions), for example, when the
table
| | >>has
| | >>| 10000 rows, it has 150GB, but when it has 6500 rows, it has 179GB,
| | >>actualy
| | >>| when I execute sp_spaceused 'mytable' to monitor, I can see the
rows
| are
| | >>| dynamically changing, i.e. sometimes more rows, sometimes fewer
rows,
| | >>but
| | >>| the data column in the sp_spaceused result set has the value growing
| | >>| continously.
| | >>| My environment is SQL2K5 EE (ver 9.0.3054).
| | >>|
| | >>| In my test environment, I cannot repeat this production issue.
| | >>|
| | >>| Does anyone encounter the same issue before?
| | >>|
| | >>| TIA,
| | >>| Jeff_Yao
| | >>|
| | >>|
| | >>|
| |
| |
| |
|
|
|||Hi Adams,
Thanks for the follow-up, really appreciate it. I finally got the chance to
set up a test environment where I restored the problematic production db,
which is 1400 GB large.
So here is what I have found.
Issue: a table called MyTable with a column of ntext data type does not
decrease its size even after big chunk of deletion.
Solution:
1. select * into <tempTable> from MyTable -- MyTable is the table not
releasing space after rows are deleted
2. Truncate table MyTable
3. Insert into MyTable select * from <tempTable>
After this, when I do the sp_spaceused 'MyTable', I can find the table size
has been shunk from 14 GB to 40MB. (The table has 102 rows)
There is one interesting question to be ansered here, originally when I
first posted in this forum (Feb 19, 2008), it was about 170+GB size, but
today it is only 14GB, which is amazing to me. How does this happen? But
still 14GB is huge compared to 40MB.
After solving this issue with MyTable, I find there are other two similar
tables (each has a ntext column), let me call them MyTable2 and MyTable3,
that also have the similar issue, i.e. after huge chunk of deletes (here
huge means millions of records, i.e. 90% of the table rows), the tables
still keep their sizes when checked with sp_spaceused.
I tried to use the same solution as mentioned above, however, the two tables
are so huge, MyTable2 currently is 157GB, and MyTable3 is 330GB, so doing a
"select into" craps my test environment because tlog grows too large. I know
I can overcome this by some chunk of rows in a loop but it will take too
long, which means the solution may not be applicable to our prod
environment.
However I got some unexpected results, here are my exact steps with pseudo
scripts
1. create table tempTable (pk_id int primary key, blob_col ntext)
2. insert int tempTable (pk_id, blob_col) select pk_id, blob_col from
MyTable2
3. after 8 hrs running, my disk has no space for log file, I cancelled the
step, which took another 3 hrs
4. after fully rolled back, tempTable has no rows because the insert does
not succeed, and now I run: execute sp_spaceused 'tempTable', 'true', and
here is the result
name rows reserved data index_size unused
tempTable 0 173200 KB 162496 KB 968 KB 9736 KB
As you can see, rows = 0, while data colum is around 160MB, how can this be?
Does this also prove in some way that the table is not releasing its space
after deletion?
If I do a truncate table tempTable, then everything is reset, i.e. all
columns are 0.
So Adams, could you please provide some insights on this table behaviour?
Thanks in advance,
Jeff_Yao
"Adams Qu [MSFT]" <v-adamqu@.online.microsoft.com> wrote in message
news:h99$zKCeIHA.4200@.TK2MSFTNGHUB02.phx.gbl...
> Dear Jeff,
> We wanted to see if the information provided was helpful. Please keep us
> posted on your progress and let us know if you have any additional
> questions or concerns.
> We are looking forward to your response.
> Have a nice day!
> Best regards,
> Adams Qu
> MCSE, MCDBA, MCTS
> Microsoft Online Support
> Microsoft Global Technical Support Center
> Get Secure! - www.microsoft.com/security
> ================================================== ===
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
>
> --
> | X-Tomcat-ID: 112810464
> | References: <eAwbKPtcIHA.1376@.TK2MSFTNGP02.phx.gbl>
> <$Org8MucIHA.5204@.TK2MSFTNGHUB02.phx.gbl>
> <vuimr39kk22u54rtleji0hb3lu0jhr5k9b@.4ax.com>
> <#HHOdr0cIHA.148@.TK2MSFTNGP04.phx.gbl>
> | MIME-Version: 1.0
> | Content-Type: text/plain
> | Content-Transfer-Encoding: 7bit
> | From: v-adamqu@.online.microsoft.com (Adams Qu [MSFT])
> | Organization: Microsoft
> | Date: Wed, 20 Feb 2008 11:31:22 GMT
> | Subject: Re: Issue with unclaimed space
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> | Message-ID: <RA1ghQ7cIHA.6844@.TK2MSFTNGHUB02.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.server
> | Lines: 291
> | Path: TK2MSFTNGHUB02.phx.gbl
> | Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:37107
> | NNTP-Posting-Host: tk5tomimport1.phx.gbl 10.201.218.19
> |
> | Dear Jeff,
> |
> | Thank you for your detail response and clarifying the issue for me.
> |
> | According to my experience, there is an known issue in SQL Server that
> the
> | space is not released after you use a DELETE statement to delete data
> from
> | a table. This kind of behavior is normally caused by the row
> | versioning-based isolation level, or Row-level lock. If pages are not
> | deallocated, other objects in the database cannot reuse the pages.
> |
> | If it is the situation, one method we can try to correct the space is
> | truncating table and re-importing the data (just as what you intend to
> do).
> | However, I also suggest trying another method that including a "TABLOCK"
> | hint in the DELETE statement to make a table-level lock being held (if a
> | row versioning-based isolation level is not enabled) to test the issue
> | again. For example, use a statement that is similar to the following:
> |
> | DELETE FROM <TableName> WITH (TABLOCK) where <condition>
> |
> | If it is convenient, you can try to delete a test row via above command
> and
> | then verify the space again via the sp_spaceused. This method can help
> us
> | to determine if the issue is caused by this factor.
> |
> | Please let me know the output at your convenience and I am glad to
> continue
> | to work with you.
> |
> | Have a nice day!
> |
> | Best regards,
> |
> | Adams Qu
> | MCSE, MCDBA, MCTS
> | Microsoft Online Support
> |
> | Microsoft Global Technical Support Center
> |
> | Get Secure! - www.microsoft.com/security
> | ================================================== ===
> | When responding to posts, please "Reply to Group" via your newsreader so
> | that others may learn and benefit from your issue.
> | ================================================== ===
> | This posting is provided "AS IS" with no warranties, and confers no
> rights.
> |
> |
> | --
> | | From: "jeff_yao" <jeff_yao@.community.nospam>
> | | References: <eAwbKPtcIHA.1376@.TK2MSFTNGP02.phx.gbl>
> | <$Org8MucIHA.5204@.TK2MSFTNGHUB02.phx.gbl>
> | <vuimr39kk22u54rtleji0hb3lu0jhr5k9b@.4ax.com>
> | | Subject: Re: Issue with unclaimed space
> | | Date: Tue, 19 Feb 2008 14:57:52 -0800
> | | Lines: 196
> | | X-Priority: 3
> | | X-MSMail-Priority: Normal
> | | X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
> | | x-mimeole: Produced By Microsoft MimeOLE V6.00.2900.3198
> | | X-RFC2646: Format=Flowed; Original
> | | Message-ID: <#HHOdr0cIHA.148@.TK2MSFTNGP04.phx.gbl>
> | | Newsgroups: microsoft.public.sqlserver.server
> | | NNTP-Posting-Host: d154-5-0-112.bchsia.telus.net 154.5.0.112
> | | Path: TK2MSFTNGHUB02.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSF TNGP04.phx.gbl
> | | Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:37092
> | | X-Tomcat-NG: microsoft.public.sqlserver.server
> | |
> | | Thanks Roy and Adams for the reply.
> | |
> | | This table has a clustered index, but after I drop and recreate it, it
> | does
> | | not help either. LOB_COMPACTION = ON is the default option and I did
> not
> | | explicitly specify it. On the other hand, the index is based on an
> | interger
> | | column, so it does not suprise me if reindex does not solve the issue.
> | |
> | | To Adams Qu [MSFT], I'd like to answer your suggestions / concerns
> here
> | too.
> | | As a dba starting with ver 6.5, I of course understand "the size of
> the
> | Text
> | | type column may not be proportional to the number of the rows", what I
> | | observed is even when a row is deleted (with no new rows inserted),
> the
> | | sp_spaceused (with @.updateusage='true') reports no decreased data
> size,
> | but
> | | as soon as a new row is inserted, sp_spaceused will show increased
> data
> | | size. The puzzle here is the table size has NEVER decreased over a
> long
> | time
> | | and in terms of each row, the text column should be more or less
> | identical
> | | in size. I have recorded the table size for the last 30 days, see
> | following
> | | for details (size data are in KB)
> | |
> | | TblName rows reserved data index_size unused LogDate
> | | MyTable 697999 72518648 72330272 53152 135224 17-Jan-2008
> | | MyTable 702841 75228552 75039912 53192 135448 18-Jan-2008
> | | MyTable 706909 77616144 77427256 53208 135680 19-Jan-2008
> | | MyTable 708712 78533968 78345048 53208 135712 20-Jan-2008
> | | MyTable 711520 80033232 79844232 53216 135784 21-Jan-2008
> | | MyTable 715487 82224280 82035128 53224 135928 22-Jan-2008
> | | MyTable 722378 85085920 84896432 53248 136240 23-Jan-2008
> | | MyTable 729030 87924840 87735112 53272 136456 24-Jan-2008
> | | MyTable 735067 90753064 90563008 53280 136776 25-Jan-2008
> | | MyTable 742325 93237808 93047496 53304 137008 26-Jan-2008
> | | MyTable 745192 94787448 94596960 53312 137176 27-Jan-2008
> | | MyTable 750447 97794424 97603648 53328 137448 28-Jan-2008
> | | MyTable 759321 102154296 101963176 53360 137760 29-Jan-2008
> | | MyTable 768464 106502824 106311240 53440 138144 30-Jan-2008
> | | MyTable 777361 111264944 111072712 53480 138752 31-Jan-2008
> | | MyTable 785317 115374392 115181664 53512 139216 1-Feb-2008
> | | MyTable 790329 118687736 118494720 53520 139496 2-Feb-2008
> | | MyTable 793007 120391928 120198856 53528 139544 3-Feb-2008
> | | MyTable 797381 123205944 123012592 53544 139808 4-Feb-2008
> | | MyTable 805268 127963832 127770056 53568 140208 5-Feb-2008
> | | MyTable 812031 131762168 131568032 53592 140544 6-Feb-2008
> | | MyTable 817958 135148280 134953872 53608 140800 7-Feb-2008
> | | MyTable 823068 138176064 137981360 53624 141080 8-Feb-2008
> | | MyTable 826878 140412488 140217496 53640 141352 9-Feb-2008
> | | MyTable 829234 141999752 141804592 53648 141512 10-Feb-2008
> | | MyTable 833843 145251208 145055840 53656 141712 11-Feb-2008
> | | MyTable 840203 149432784 149237104 53688 141992 12-Feb-2008
> | | MyTable 846171 153050064 152854104 53704 142256 13-Feb-2008
> | | MyTable 852032 156654488 156458232 53736 142520 14-Feb-2008
> | | MyTable 11670 159944352 159671584 82320 190448 15-Feb-2008
> | | MyTable 11776 163975840 163674936 78920 221984 16-Feb-2008
> | | MyTable 8573 165295776 164999280 76680 219816 17-Feb-2008
> | | MyTable 6477 167538536 167259824 73208 205504 18-Feb-2008
> | | MyTable 10171 170841784 170769184 824 71776 19-Feb-2008
> | |
> | |
> | | Notice the last row, the index size is smaller, but it was a result of
> my
> | | index rebuild (drop & recreate for clustered index). But on the other
> | hand
> | | data size keeps on growing no matter how many rows inside the table.
> | |
> | | I think there is one solution which I will implement later when a
> | | maintenance window comes, i.e.
> | |
> | | 1. select * into <TempTable> from MyTable
> | | 2. truncate table MyTable
> | | 3. insert into MyTable Select * from <TempTable>
> | | 4. drop table <TempTable>
> | | 5. check the MyTable size again
> | |
> | | I come to this proposed solution because I have calculated the sum of
> | the
> | | datalength of the text column (stores an XML file), and it returns me
> | only
> | | 760 MB, giving some overhead (the other four interger, datetime
> columns
> | can
> | | be omitted here), I think 8GB should be sufficient for the whole table
> | (i.e.
> | | 760 MB x 10 = 8 GB)
> | |
> | | If this works, I guess there must be something wrong with SQL Server
> | engine,
> | | and at that time I hope Adams can provide us some internal whys :-)
> | |
> | | I will update you here once I implemented my proposal.
> | |
> | | Thanks,
> | |
> | | Jeff
> | |
> | | "Roy Harvey (SQL Server MVP)" <roy_harvey@.snet.net> wrote in message
> | | news:vuimr39kk22u54rtleji0hb3lu0jhr5k9b@.4ax.com...
> | | > Using SHRINKFILE to free up unused space that is allocated to a
> table
> | | > seems like an extreme and inefficient approach. The question was
> | | > posed about a specific table, and it would seem better to look for a
> | | > solution that deals with the table and not the entire database. Are
> | | > you saying that ALTER INDEX with REBUILD or REORGANIZE (and
> | | > LOB_COMPACTION = ON) would not free the unused space?
> | | >
> | | > Roy Harvey
> | | > Beacon Falls, CT
> | | >
> | | > On Tue, 19 Feb 2008 10:36:00 GMT, v-adamqu@.online.microsoft.com
> (Adams
> | | > Qu [MSFT]) wrote:
> | | >
> | | >>Dear Jeff,
> | | >>
> | | >>Thank you for posting here.
> | | >>
> | | >>As the data type in this table in question is "Text" , the size of
> the
> | | >>Text
> | | >>type column may not be proportional to the number of the rows
> because
> it
> | | >>depends on the data size in each field of the Text type column. For
> | | >>example, if one row contains the extreme large data in the Text
> column,
> | | >>the
> | | >>size of the whole table may grow even when the number of rows are
> less
> | | >>than
> | | >>before. So, it is an expected behavior in some situations.
> | | >>
> | | >>At this stage, you can first use the following sp_spaceused command
> to
> | | >>check the whole database to see if there is some unallocated space
> we
> | can
> | | >>release.
> | | >>
> | | >>use dbname
> | | >>go
> | | >>sp_spaceused @.updateusage = N'TRUE'
> | | >>
> | | >>If so, please first backup the database and then run the DBCC
> | SHRINKFILE
> | | >>to
> | | >>shrink the data file. For more information, please refer to the
> | following
> | | >>website:
> | | >>
> | | >>DBCC SHRINKFILE (Transact-SQL)
> | | >>http://msdn2.microsoft.com/en-us/library/ms189493.aspx
> | | >>
> | | >>If anything is unclear in my post, please don't hesitate to let me
> know
> | | >>and
> | | >>I will be glad to help.
> | | >>
> | | >>Have a nice day!
> | | >>
> | | >>Best regards,
> | | >>
> | | >>Adams Qu, MCSE, MCDBA, MCTS
> | | >>Microsoft Online Support
> | | >>
> | | >>Microsoft Global Technical Support Center
> | | >>
> | | >>Get Secure! - www.microsoft.com/security
> | | >>================================================ =====
> | | >>When responding to posts, please "Reply to Group" via your
> newsreader
> so
> | | >>that others may learn and benefit from your issue.
> | | >>================================================ =====
> | | >>This posting is provided "AS IS" with no warranties, and confers no
> | | >>rights.
> | | >>
> | | >>--
> | | >>| From: "jeff_yao" <jeff_yao@.community.nospam>
> | | >>| Subject: Issue with unclaimed space
> | | >>| Date: Tue, 19 Feb 2008 00:45:34 -0800
> | | >>| Lines: 23
> | | >>| X-Priority: 3
> | | >>| X-MSMail-Priority: Normal
> | | >>| X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
> | | >>| x-mimeole: Produced By Microsoft MimeOLE V6.00.2900.3198
> | | >>| X-RFC2646: Format=Flowed; Original
> | | >>| Message-ID: <eAwbKPtcIHA.1376@.TK2MSFTNGP02.phx.gbl>
> | | >>| Newsgroups: microsoft.public.sqlserver.server
> | | >>| NNTP-Posting-Host: d154-5-0-112.bchsia.telus.net 154.5.0.112
> | | >>| Path:
> TK2MSFTNGHUB02.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSF TNGP02.phx.gbl
> | | >>| Xref: TK2MSFTNGHUB02.phx.gbl
> microsoft.public.sqlserver.server:37052
> | | >>| X-Tomcat-NG: microsoft.public.sqlserver.server
> | | >>|
> | | >>| Hi all,
> | | >>|
> | | >>| I am not sure whether someone has experienced the same issue as I
> do.
> | | >>|
> | | >>| I have a table which has a column with text data type and a few
> other
> | | >>| regular columns (int, datetime etc). There are inserts / deletes
> on
> | this
> | | >>| table, but the weird thing is the table is keeping on growing, but
> the
> | | >>row
> | | >>| number is not (because of delete actions), for example, when the
> table
> | | >>has
> | | >>| 10000 rows, it has 150GB, but when it has 6500 rows, it has 179GB,
> | | >>actualy
> | | >>| when I execute sp_spaceused 'mytable' to monitor, I can see the
> rows
> | are
> | | >>| dynamically changing, i.e. sometimes more rows, sometimes fewer
> rows,
> | | >>but
> | | >>| the data column in the sp_spaceused result set has the value
> growing
> | | >>| continously.
> | | >>| My environment is SQL2K5 EE (ver 9.0.3054).
> | | >>|
> | | >>| In my test environment, I cannot repeat this production issue.
> | | >>|
> | | >>| Does anyone encounter the same issue before?
> | | >>|
> | | >>| TIA,
> | | >>| Jeff_Yao
> | | >>|
> | | >>|
> | | >>|
> | |
> | |
> | |
> |
> |
>
|||Dear Jeff,
Thank you for your detail response.
After checking your latest post, there are two different issues here:
1. The tempTable which has no rows is a heap table and it seems to be
growing because of lack of clustered indexes. By design, SQL Server only
releases all the pages that a heap table (no indx) uses when the following
conditions are true:
- A deletion on this table occurs.
- A table-level lock is being held.
In such case, if we do a truncate table tempTable, everything will be
reset. For more information about this topic, you can refer to the
following KB article:
Space that a table uses is not completely released after you use a DELETE
statement to delete data from the table in SQL Server
http://support.microsoft.com/kb/913399
2. The main table MyTable or MyTable2 and MyTable3 is growing because SQL
Server reserves more space for the text field. To further track the issue
and determine the root cause, would you please if the issue can still be
reproduced on the MyTable which is just rebuilt in the test environment? If
not, please let me know how the data is inserted (via SP or manually)?
Have a nice day!
Best regards,
Adams Qu, MCSE, MCDBA, MCTS
Microsoft Online Support
Microsoft Global Technical Support Center
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "jeff_yao" <jeff_yao@.community.nospam>
| References: <eAwbKPtcIHA.1376@.TK2MSFTNGP02.phx.gbl>
<$Org8MucIHA.5204@.TK2MSFTNGHUB02.phx.gbl>
<vuimr39kk22u54rtleji0hb3lu0jhr5k9b@.4ax.com>
<#HHOdr0cIHA.148@.TK2MSFTNGP04.phx.gbl>
<RA1ghQ7cIHA.6844@.TK2MSFTNGHUB02.phx.gbl>
<h99$zKCeIHA.4200@.TK2MSFTNGHUB02.phx.gbl>
| Subject: Re: Issue with unclaimed space
| Date: Tue, 11 Mar 2008 16:55:07 -0700
| Lines: 440
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
| X-RFC2646: Format=Flowed; Original
| Message-ID: <OKxZYN9gIHA.6084@.TK2MSFTNGP06.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: d154-5-0-112.bchsia.telus.net 154.5.0.112
| Path: TK2MSFTNGHUB02.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSF TNGP06.phx.gbl
| Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:38407
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Hi Adams,
|
| Thanks for the follow-up, really appreciate it. I finally got the chance
to
| set up a test environment where I restored the problematic production db,
| which is 1400 GB large.
|
| So here is what I have found.
| Issue: a table called MyTable with a column of ntext data type does not
| decrease its size even after big chunk of deletion.
|
| Solution:
| 1. select * into <tempTable> from MyTable -- MyTable is the table not
| releasing space after rows are deleted
| 2. Truncate table MyTable
| 3. Insert into MyTable select * from <tempTable>
|
| After this, when I do the sp_spaceused 'MyTable', I can find the table
size
| has been shunk from 14 GB to 40MB. (The table has 102 rows)
|
| There is one interesting question to be ansered here, originally when I
| first posted in this forum (Feb 19, 2008), it was about 170+GB size, but
| today it is only 14GB, which is amazing to me. How does this happen? But
| still 14GB is huge compared to 40MB.
|
| After solving this issue with MyTable, I find there are other two similar
| tables (each has a ntext column), let me call them MyTable2 and MyTable3,
| that also have the similar issue, i.e. after huge chunk of deletes (here
| huge means millions of records, i.e. 90% of the table rows), the tables
| still keep their sizes when checked with sp_spaceused.
|
| I tried to use the same solution as mentioned above, however, the two
tables
| are so huge, MyTable2 currently is 157GB, and MyTable3 is 330GB, so doing
a
| "select into" craps my test environment because tlog grows too large. I
know
| I can overcome this by some chunk of rows in a loop but it will take too
| long, which means the solution may not be applicable to our prod
| environment.
|
| However I got some unexpected results, here are my exact steps with
pseudo
| scripts
|
| 1. create table tempTable (pk_id int primary key, blob_col ntext)
| 2. insert int tempTable (pk_id, blob_col) select pk_id, blob_col from
| MyTable2
| 3. after 8 hrs running, my disk has no space for log file, I cancelled
the
| step, which took another 3 hrs
| 4. after fully rolled back, tempTable has no rows because the insert does
| not succeed, and now I run: execute sp_spaceused 'tempTable', 'true', and
| here is the result
|
| name rows reserved data index_size unused
| tempTable 0 173200 KB 162496 KB 968 KB 9736 KB
|
| As you can see, rows = 0, while data colum is around 160MB, how can this
be?
| Does this also prove in some way that the table is not releasing its
space
| after deletion?
|
| If I do a truncate table tempTable, then everything is reset, i.e. all
| columns are 0.
|
| So Adams, could you please provide some insights on this table behaviour?
|
| Thanks in advance,
|
| Jeff_Yao
|
| "Adams Qu [MSFT]" <v-adamqu@.online.microsoft.com> wrote in message
| news:h99$zKCeIHA.4200@.TK2MSFTNGHUB02.phx.gbl...
| > Dear Jeff,
| >
| > We wanted to see if the information provided was helpful. Please keep us
| > posted on your progress and let us know if you have any additional
| > questions or concerns.
| >
| > We are looking forward to your response.
| >
| > Have a nice day!
| >
| > Best regards,
| >
| > Adams Qu
| > MCSE, MCDBA, MCTS
| > Microsoft Online Support
| >
| > Microsoft Global Technical Support Center
| >
| > Get Secure! - www.microsoft.com/security
| > ================================================== ===
| > When responding to posts, please "Reply to Group" via your newsreader so
| > that others may learn and benefit from your issue.
| > ================================================== ===
| > This posting is provided "AS IS" with no warranties, and confers no
| > rights.
| >
| >
| >
| >
| > --
| > | X-Tomcat-ID: 112810464
| > | References: <eAwbKPtcIHA.1376@.TK2MSFTNGP02.phx.gbl>
| > <$Org8MucIHA.5204@.TK2MSFTNGHUB02.phx.gbl>
| > <vuimr39kk22u54rtleji0hb3lu0jhr5k9b@.4ax.com>
| > <#HHOdr0cIHA.148@.TK2MSFTNGP04.phx.gbl>
| > | MIME-Version: 1.0
| > | Content-Type: text/plain
| > | Content-Transfer-Encoding: 7bit
| > | From: v-adamqu@.online.microsoft.com (Adams Qu [MSFT])
| > | Organization: Microsoft
| > | Date: Wed, 20 Feb 2008 11:31:22 GMT
| > | Subject: Re: Issue with unclaimed space
| > | X-Tomcat-NG: microsoft.public.sqlserver.server
| > | Message-ID: <RA1ghQ7cIHA.6844@.TK2MSFTNGHUB02.phx.gbl>
| > | Newsgroups: microsoft.public.sqlserver.server
| > | Lines: 291
| > | Path: TK2MSFTNGHUB02.phx.gbl
| > | Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:37107
| > | NNTP-Posting-Host: tk5tomimport1.phx.gbl 10.201.218.19
| > |
| > | Dear Jeff,
| > |
| > | Thank you for your detail response and clarifying the issue for me.
| > |
| > | According to my experience, there is an known issue in SQL Server that
| > the
| > | space is not released after you use a DELETE statement to delete data
| > from
| > | a table. This kind of behavior is normally caused by the row
| > | versioning-based isolation level, or Row-level lock. If pages are not
| > | deallocated, other objects in the database cannot reuse the pages.
| > |
| > | If it is the situation, one method we can try to correct the space is
| > | truncating table and re-importing the data (just as what you intend to
| > do).
| > | However, I also suggest trying another method that including a
"TABLOCK"
| > | hint in the DELETE statement to make a table-level lock being held
(if a
| > | row versioning-based isolation level is not enabled) to test the issue
| > | again. For example, use a statement that is similar to the following:
| > |
| > | DELETE FROM <TableName> WITH (TABLOCK) where <condition>
| > |
| > | If it is convenient, you can try to delete a test row via above
command
| > and
| > | then verify the space again via the sp_spaceused. This method can
help
| > us
| > | to determine if the issue is caused by this factor.
| > |
| > | Please let me know the output at your convenience and I am glad to
| > continue
| > | to work with you.
| > |
| > | Have a nice day!
| > |
| > | Best regards,
| > |
| > | Adams Qu
| > | MCSE, MCDBA, MCTS
| > | Microsoft Online Support
| > |
| > | Microsoft Global Technical Support Center
| > |
| > | Get Secure! - www.microsoft.com/security
| > | ================================================== ===
| > | When responding to posts, please "Reply to Group" via your newsreader
so
| > | that others may learn and benefit from your issue.
| > | ================================================== ===
| > | This posting is provided "AS IS" with no warranties, and confers no
| > rights.
| > |
| > |
| > | --
| > | | From: "jeff_yao" <jeff_yao@.community.nospam>
| > | | References: <eAwbKPtcIHA.1376@.TK2MSFTNGP02.phx.gbl>
| > | <$Org8MucIHA.5204@.TK2MSFTNGHUB02.phx.gbl>
| > | <vuimr39kk22u54rtleji0hb3lu0jhr5k9b@.4ax.com>
| > | | Subject: Re: Issue with unclaimed space
| > | | Date: Tue, 19 Feb 2008 14:57:52 -0800
| > | | Lines: 196
| > | | X-Priority: 3
| > | | X-MSMail-Priority: Normal
| > | | X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
| > | | x-mimeole: Produced By Microsoft MimeOLE V6.00.2900.3198
| > | | X-RFC2646: Format=Flowed; Original
| > | | Message-ID: <#HHOdr0cIHA.148@.TK2MSFTNGP04.phx.gbl>
| > | | Newsgroups: microsoft.public.sqlserver.server
| > | | NNTP-Posting-Host: d154-5-0-112.bchsia.telus.net 154.5.0.112
| > | | Path:
TK2MSFTNGHUB02.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSF TNGP04.phx.gbl
| > | | Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:37092
| > | | X-Tomcat-NG: microsoft.public.sqlserver.server
| > | |
| > | | Thanks Roy and Adams for the reply.
| > | |
| > | | This table has a clustered index, but after I drop and recreate it,
it
| > | does
| > | | not help either. LOB_COMPACTION = ON is the default option and I did
| > not
| > | | explicitly specify it. On the other hand, the index is based on an
| > | interger
| > | | column, so it does not suprise me if reindex does not solve the
issue.
| > | |
| > | | To Adams Qu [MSFT], I'd like to answer your suggestions / concerns
| > here
| > | too.
| > | | As a dba starting with ver 6.5, I of course understand "the size of
| > the
| > | Text
| > | | type column may not be proportional to the number of the rows",
what I
| > | | observed is even when a row is deleted (with no new rows inserted),
| > the
| > | | sp_spaceused (with @.updateusage='true') reports no decreased data
| > size,
| > | but
| > | | as soon as a new row is inserted, sp_spaceused will show increased
| > data
| > | | size. The puzzle here is the table size has NEVER decreased over a
| > long
| > | time
| > | | and in terms of each row, the text column should be more or less
| > | identical
| > | | in size. I have recorded the table size for the last 30 days, see
| > | following
| > | | for details (size data are in KB)
| > | |
| > | | TblName rows reserved data index_size unused LogDate
| > | | MyTable 697999 72518648 72330272 53152 135224 17-Jan-2008
| > | | MyTable 702841 75228552 75039912 53192 135448 18-Jan-2008
| > | | MyTable 706909 77616144 77427256 53208 135680 19-Jan-2008
| > | | MyTable 708712 78533968 78345048 53208 135712 20-Jan-2008
| > | | MyTable 711520 80033232 79844232 53216 135784 21-Jan-2008
| > | | MyTable 715487 82224280 82035128 53224 135928 22-Jan-2008
| > | | MyTable 722378 85085920 84896432 53248 136240 23-Jan-2008
| > | | MyTable 729030 87924840 87735112 53272 136456 24-Jan-2008
| > | | MyTable 735067 90753064 90563008 53280 136776 25-Jan-2008
| > | | MyTable 742325 93237808 93047496 53304 137008 26-Jan-2008
| > | | MyTable 745192 94787448 94596960 53312 137176 27-Jan-2008
| > | | MyTable 750447 97794424 97603648 53328 137448 28-Jan-2008
| > | | MyTable 759321 102154296 101963176 53360 137760 29-Jan-2008
| > | | MyTable 768464 106502824 106311240 53440 138144 30-Jan-2008
| > | | MyTable 777361 111264944 111072712 53480 138752 31-Jan-2008
| > | | MyTable 785317 115374392 115181664 53512 139216 1-Feb-2008
| > | | MyTable 790329 118687736 118494720 53520 139496 2-Feb-2008
| > | | MyTable 793007 120391928 120198856 53528 139544 3-Feb-2008
| > | | MyTable 797381 123205944 123012592 53544 139808 4-Feb-2008
| > | | MyTable 805268 127963832 127770056 53568 140208 5-Feb-2008
| > | | MyTable 812031 131762168 131568032 53592 140544 6-Feb-2008
| > | | MyTable 817958 135148280 134953872 53608 140800 7-Feb-2008
| > | | MyTable 823068 138176064 137981360 53624 141080 8-Feb-2008
| > | | MyTable 826878 140412488 140217496 53640 141352 9-Feb-2008
| > | | MyTable 829234 141999752 141804592 53648 141512 10-Feb-2008
| > | | MyTable 833843 145251208 145055840 53656 141712 11-Feb-2008
| > | | MyTable 840203 149432784 149237104 53688 141992 12-Feb-2008
| > | | MyTable 846171 153050064 152854104 53704 142256 13-Feb-2008
| > | | MyTable 852032 156654488 156458232 53736 142520 14-Feb-2008
| > | | MyTable 11670 159944352 159671584 82320 190448 15-Feb-2008
| > | | MyTable 11776 163975840 163674936 78920 221984 16-Feb-2008
| > | | MyTable 8573 165295776 164999280 76680 219816 17-Feb-2008
| > | | MyTable 6477 167538536 167259824 73208 205504 18-Feb-2008
| > | | MyTable 10171 170841784 170769184 824 71776 19-Feb-2008
| > | |
| > | |
| > | | Notice the last row, the index size is smaller, but it was a result
of
| > my
| > | | index rebuild (drop & recreate for clustered index). But on the
other
| > | hand
| > | | data size keeps on growing no matter how many rows inside the table.
| > | |
| > | | I think there is one solution which I will implement later when a
| > | | maintenance window comes, i.e.
| > | |
| > | | 1. select * into <TempTable> from MyTable
| > | | 2. truncate table MyTable
| > | | 3. insert into MyTable Select * from <TempTable>
| > | | 4. drop table <TempTable>
| > | | 5. check the MyTable size again
| > | |
| > | | I come to this proposed solution because I have calculated the sum
of
| > | the
| > | | datalength of the text column (stores an XML file), and it returns
me
| > | only
| > | | 760 MB, giving some overhead (the other four interger, datetime
| > columns
| > | can
| > | | be omitted here), I think 8GB should be sufficient for the whole
table
| > | (i.e.
| > | | 760 MB x 10 = 8 GB)
| > | |
| > | | If this works, I guess there must be something wrong with SQL Server
| > | engine,
| > | | and at that time I hope Adams can provide us some internal whys :-)
| > | |
| > | | I will update you here once I implemented my proposal.
| > | |
| > | | Thanks,
| > | |
| > | | Jeff
| > | |
| > | | "Roy Harvey (SQL Server MVP)" <roy_harvey@.snet.net> wrote in message
| > | | news:vuimr39kk22u54rtleji0hb3lu0jhr5k9b@.4ax.com...
| > | | > Using SHRINKFILE to free up unused space that is allocated to a
| > table
| > | | > seems like an extreme and inefficient approach. The question was
| > | | > posed about a specific table, and it would seem better to look
for a
| > | | > solution that deals with the table and not the entire database.
Are
| > | | > you saying that ALTER INDEX with REBUILD or REORGANIZE (and
| > | | > LOB_COMPACTION = ON) would not free the unused space?
| > | | >
| > | | > Roy Harvey
| > | | > Beacon Falls, CT
| > | | >
| > | | > On Tue, 19 Feb 2008 10:36:00 GMT, v-adamqu@.online.microsoft.com
| > (Adams
| > | | > Qu [MSFT]) wrote:
| > | | >
| > | | >>Dear Jeff,
| > | | >>
| > | | >>Thank you for posting here.
| > | | >>
| > | | >>As the data type in this table in question is "Text" , the size of
| > the
| > | | >>Text
| > | | >>type column may not be proportional to the number of the rows
| > because
| > it
| > | | >>depends on the data size in each field of the Text type column.
For
| > | | >>example, if one row contains the extreme large data in the Text
| > column,
| > | | >>the
| > | | >>size of the whole table may grow even when the number of rows are
| > less
| > | | >>than
| > | | >>before. So, it is an expected behavior in some situations.
| > | | >>
| > | | >>At this stage, you can first use the following sp_spaceused
command
| > to
| > | | >>check the whole database to see if there is some unallocated
space
| > we
| > | can
| > | | >>release.
| > | | >>
| > | | >>use dbname
| > | | >>go
| > | | >>sp_spaceused @.updateusage = N'TRUE'
| > | | >>
| > | | >>If so, please first backup the database and then run the DBCC
| > | SHRINKFILE
| > | | >>to
| > | | >>shrink the data file. For more information, please refer to the
| > | following
| > | | >>website:
| > | | >>
| > | | >>DBCC SHRINKFILE (Transact-SQL)
| > | | >>http://msdn2.microsoft.com/en-us/library/ms189493.aspx
| > | | >>
| > | | >>If anything is unclear in my post, please don't hesitate to let me
| > know
| > | | >>and
| > | | >>I will be glad to help.
| > | | >>
| > | | >>Have a nice day!
| > | | >>
| > | | >>Best regards,
| > | | >>
| > | | >>Adams Qu, MCSE, MCDBA, MCTS
| > | | >>Microsoft Online Support
| > | | >>
| > | | >>Microsoft Global Technical Support Center
| > | | >>
| > | | >>Get Secure! - www.microsoft.com/security
| > | | >>================================================ =====
| > | | >>When responding to posts, please "Reply to Group" via your
| > newsreader
| > so
| > | | >>that others may learn and benefit from your issue.
| > | | >>================================================ =====
| > | | >>This posting is provided "AS IS" with no warranties, and confers
no
| > | | >>rights.
| > | | >>
| > | | >>--
| > | | >>| From: "jeff_yao" <jeff_yao@.community.nospam>
| > | | >>| Subject: Issue with unclaimed space
| > | | >>| Date: Tue, 19 Feb 2008 00:45:34 -0800
| > | | >>| Lines: 23
| > | | >>| X-Priority: 3
| > | | >>| X-MSMail-Priority: Normal
| > | | >>| X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
| > | | >>| x-mimeole: Produced By Microsoft MimeOLE V6.00.2900.3198
| > | | >>| X-RFC2646: Format=Flowed; Original
| > | | >>| Message-ID: <eAwbKPtcIHA.1376@.TK2MSFTNGP02.phx.gbl>
| > | | >>| Newsgroups: microsoft.public.sqlserver.server
| > | | >>| NNTP-Posting-Host: d154-5-0-112.bchsia.telus.net 154.5.0.112
| > | | >>| Path:
| > TK2MSFTNGHUB02.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSF TNGP02.phx.gbl
| > | | >>| Xref: TK2MSFTNGHUB02.phx.gbl
| > microsoft.public.sqlserver.server:37052
| > | | >>| X-Tomcat-NG: microsoft.public.sqlserver.server
| > | | >>|
| > | | >>| Hi all,
| > | | >>|
| > | | >>| I am not sure whether someone has experienced the same issue
as I
| > do.
| > | | >>|
| > | | >>| I have a table which has a column with text data type and a few
| > other
| > | | >>| regular columns (int, datetime etc). There are inserts /
deletes
| > on
| > | this
| > | | >>| table, but the weird thing is the table is keeping on growing,
but
| > the
| > | | >>row
| > | | >>| number is not (because of delete actions), for example, when the
| > table
| > | | >>has
| > | | >>| 10000 rows, it has 150GB, but when it has 6500 rows, it has
179GB,
| > | | >>actualy
| > | | >>| when I execute sp_spaceused 'mytable' to monitor, I can see the
| > rows
| > | are
| > | | >>| dynamically changing, i.e. sometimes more rows, sometimes fewer
| > rows,
| > | | >>but
| > | | >>| the data column in the sp_spaceused result set has the value
| > growing
| > | | >>| continously.
| > | | >>| My environment is SQL2K5 EE (ver 9.0.3054).
| > | | >>|
| > | | >>| In my test environment, I cannot repeat this production issue.
| > | | >>|
| > | | >>| Does anyone encounter the same issue before?
| > | | >>|
| > | | >>| TIA,
| > | | >>| Jeff_Yao
| > | | >>|
| > | | >>|
| > | | >>|
| > | |
| > | |
| > | |
| > |
| > |
| >
|
|
|
|||On Wed, 12 Mar 2008 09:44:25 GMT, v-adamqu@.online.microsoft.com (Adams
Qu [MSFT]) wrote:

>1. The tempTable which has no rows is a heap table and it seems to be
>growing because of lack of clustered indexes. By design, SQL Server only
>releases all the pages that a heap table (no indx) uses when the following
>conditions are true:
>- A deletion on this table occurs.
>- A table-level lock is being held.
>In such case, if we do a truncate table tempTable, everything will be
>reset. For more information about this topic, you can refer to the
>following KB article:
>Space that a table uses is not completely released after you use a DELETE
>statement to delete data from the table in SQL Server
>http://support.microsoft.com/kb/913399
Adams,
Interesting KB article. I have some questions about the first
workaround, which says:
======
Include a TABLOCK hint in the DELETE statement if a row
versioning-based isolation level is not enabled. For example, use a
statement that is similar to the following:
DELETE FROM <TableName> WITH (TABLOCK)
======
Will that work for just the rows deleted in that DELETE? Or does it
also free up space from past DELETE commands? Along the same lines,
if there is a WHERE clause that results in 0 rows being deleted, would
the space from past DELETEs be freed?
Roy Harvey
Beacon Falls, CT
|||Dear Roy,
Thank you for your question.
No, the TABLOCK hint will not free up the space from the past delete and it
only make the SQL Server releases the pages for the current deletion on a
heap table. If you experience such issue described in this KB article and
would like to ensure that the space from the past delete commands have been
freed up, we can export the data, and then TRUNCATE table, re-import the
data to workaround this problem.
Have a nice day!
Best regards,
Adams Qu
MCSE, MCDBA, MCTS
Microsoft Online Support
Microsoft Global Technical Support Center
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "Roy Harvey (SQL Server MVP)" <roy_harvey@.snet.net>
| Subject: Re: Issue with unclaimed space
| Date: Wed, 12 Mar 2008 12:50:12 -0400
| Message-ID: <k32gt31imeioibmqvjfcn0aa5pkucaocos@.4ax.com>
| References: <eAwbKPtcIHA.1376@.TK2MSFTNGP02.phx.gbl>
<$Org8MucIHA.5204@.TK2MSFTNGHUB02.phx.gbl>
<vuimr39kk22u54rtleji0hb3lu0jhr5k9b@.4ax.com>
<#HHOdr0cIHA.148@.TK2MSFTNGP04.phx.gbl>
<RA1ghQ7cIHA.6844@.TK2MSFTNGHUB02.phx.gbl>
<h99$zKCeIHA.4200@.TK2MSFTNGHUB02.phx.gbl>
<OKxZYN9gIHA.6084@.TK2MSFTNGP06.phx.gbl>
<3iGyoWChIHA.3660@.TK2MSFTNGHUB02.phx.gbl>
| X-Newsreader: Forte Agent 4.2/32.1118
| MIME-Version: 1.0
| Content-Type: text/plain; charset=us-ascii
| Content-Transfer-Encoding: 7bit
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: 64-252-45-143.adsl.snet.net 64.252.45.143
| Lines: 1
| Path: TK2MSFTNGHUB02.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSF TNGP05.phx.gbl
| Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:38456
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| On Wed, 12 Mar 2008 09:44:25 GMT, v-adamqu@.online.microsoft.com (Adams
| Qu [MSFT]) wrote:
|
| >1. The tempTable which has no rows is a heap table and it seems to be
| >growing because of lack of clustered indexes. By design, SQL Server only
| >releases all the pages that a heap table (no indx) uses when the
following
| >conditions are true:
| >
| >- A deletion on this table occurs.
| >- A table-level lock is being held.
| >
| >In such case, if we do a truncate table tempTable, everything will be
| >reset. For more information about this topic, you can refer to the
| >following KB article:
| >
| >Space that a table uses is not completely released after you use a
DELETE
| >statement to delete data from the table in SQL Server
| >http://support.microsoft.com/kb/913399
|
| Adams,
|
| Interesting KB article. I have some questions about the first
| workaround, which says:
|
| ======
| Include a TABLOCK hint in the DELETE statement if a row
| versioning-based isolation level is not enabled. For example, use a
| statement that is similar to the following:
|
| DELETE FROM <TableName> WITH (TABLOCK)
| ======
|
| Will that work for just the rows deleted in that DELETE? Or does it
| also free up space from past DELETE commands? Along the same lines,
| if there is a WHERE clause that results in 0 rows being deleted, would
| the space from past DELETEs be freed?
|
| Roy Harvey
| Beacon Falls, CT
|