It's like MS hates us...

So all I'm trying to do is a JOIN against a text file. It's a simple CSV,
nothing fancy. The only "oddity", one is shares with _every_ other file I
have to work with, is that it has two header rows, one that has the filename
and date, and a second that has the column headers.
So I'm trying ot use OPENROWSET. This is basically impossible. The ODBC
driver (actually most of them) has a many-year-old bug that makes
FirstRowHasNames=0 not work, so it keeps trying to use the row with the
filename as the columns. This results in the columns being name
"thefilename", "todaysdate", "NoName", "NoName" etc. And no, you can't SELECT
on a column called "NoName". Sweeeeet!
This would be easy to work-around if there was some way to refer to columns
by their ordinal position, but of course, there isn't. And since it's a temp
table, there's no catalog entry, so you can't query the resultset to see what
its made of and try to get the-column-name-for-today.
I also tried the OLE DB provider instead of the ODBC one, but every attempt
results in:
[OLE/DB provider returned message: Could not find installable ISAM.]
No, this problem is not caused by any of the bugs and/or problems listed on
the MS page.
*sigh*
Maury
Maury,
Here is a kludge of an idea, provided you can get it implemented.
1. Put a one-line file on disk with the column header names that you want,
such as:
FileName, FileDate, UserName, SQLLogin, etc
2. Get a process to create your file to be imported by concatenating your
header file with the file to be imported. (Essentially):
COPY MyHDR.CSV+MyData.CSV MyImport.CSV
3. You read MyImport.CSV which has your header line concatenated on the
front with the column names you will use, then all the rest is data.
RLF
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:2D91F485-2104-4FE0-BB47-2A9104477B8D@.microsoft.com...
> So all I'm trying to do is a JOIN against a text file. It's a simple CSV,
> nothing fancy. The only "oddity", one is shares with _every_ other file I
> have to work with, is that it has two header rows, one that has the
> filename
> and date, and a second that has the column headers.
> So I'm trying ot use OPENROWSET. This is basically impossible. The ODBC
> driver (actually most of them) has a many-year-old bug that makes
> FirstRowHasNames=0 not work, so it keeps trying to use the row with the
> filename as the columns. This results in the columns being name
> "thefilename", "todaysdate", "NoName", "NoName" etc. And no, you can't
> SELECT
> on a column called "NoName". Sweeeeet!
> This would be easy to work-around if there was some way to refer to
> columns
> by their ordinal position, but of course, there isn't. And since it's a
> temp
> table, there's no catalog entry, so you can't query the resultset to see
> what
> its made of and try to get the-column-name-for-today.
> I also tried the OLE DB provider instead of the ODBC one, but every
> attempt
> results in:
> [OLE/DB provider returned message: Could not find installable ISAM.]
> No, this problem is not caused by any of the bugs and/or problems listed
> on
> the MS page.
> *sigh*
> Maury
|||Why don't you bulk insert the file into a working table, setting FIRSTROW=3,
and then join against that?
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:2D91F485-2104-4FE0-BB47-2A9104477B8D@.microsoft.com...
> So all I'm trying to do is a JOIN against a text file. It's a simple CSV,
> nothing fancy. The only "oddity", one is shares with _every_ other file I
> have to work with, is that it has two header rows, one that has the
> filename
> and date, and a second that has the column headers.
> So I'm trying ot use OPENROWSET. This is basically impossible. The ODBC
> driver (actually most of them) has a many-year-old bug that makes
> FirstRowHasNames=0 not work, so it keeps trying to use the row with the
> filename as the columns. This results in the columns being name
> "thefilename", "todaysdate", "NoName", "NoName" etc. And no, you can't
> SELECT
> on a column called "NoName". Sweeeeet!
> This would be easy to work-around if there was some way to refer to
> columns
> by their ordinal position, but of course, there isn't. And since it's a
> temp
> table, there's no catalog entry, so you can't query the resultset to see
> what
> its made of and try to get the-column-name-for-today.
> I also tried the OLE DB provider instead of the ODBC one, but every
> attempt
> results in:
> [OLE/DB provider returned message: Could not find installable ISAM.]
> No, this problem is not caused by any of the bugs and/or problems listed
> on
> the MS page.
> *sigh*
> Maury
|||Have you tried to create a link server to .TXT file and operate with it as a
table?
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:2D91F485-2104-4FE0-BB47-2A9104477B8D@.microsoft.com...
> So all I'm trying to do is a JOIN against a text file. It's a simple CSV,
> nothing fancy. The only "oddity", one is shares with _every_ other file I
> have to work with, is that it has two header rows, one that has the
> filename
> and date, and a second that has the column headers.
> So I'm trying ot use OPENROWSET. This is basically impossible. The ODBC
> driver (actually most of them) has a many-year-old bug that makes
> FirstRowHasNames=0 not work, so it keeps trying to use the row with the
> filename as the columns. This results in the columns being name
> "thefilename", "todaysdate", "NoName", "NoName" etc. And no, you can't
> SELECT
> on a column called "NoName". Sweeeeet!
> This would be easy to work-around if there was some way to refer to
> columns
> by their ordinal position, but of course, there isn't. And since it's a
> temp
> table, there's no catalog entry, so you can't query the resultset to see
> what
> its made of and try to get the-column-name-for-today.
> I also tried the OLE DB provider instead of the ODBC one, but every
> attempt
> results in:
> [OLE/DB provider returned message: Could not find installable ISAM.]
> No, this problem is not caused by any of the bugs and/or problems listed
> on
> the MS page.
> *sigh*
> Maury
|||On May 8, 3:49 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> Why don't you bulk insert the file into a working table, setting FIRSTROW=3,
> and then join against that?
Only works on 2005. I just went through a hair-raising upgrade to
2000SP3 in order to fix a single bug (interior inner joins on derived
tables), so I'm hesitant to move to 2005 at this point.
Maury
|||On May 9, 2:36 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Have you tried to create a link server to .TXT file and operate with it as a
> table?
Yes, this approach suffers from the same bugs. Not surprising really,
I'm sure it all forks through the same code.
It would be really nice if I could get the OLE DB approach working,
because it *seems* that it doesn't have the FirstRow bug, and I'm also
pretty familiar with the OLE DB model and feel pretty confident using
it. But I just can't figure out what's wrong. It's also odd that this
doesn't work:
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=O:\temp;Extended
Properties=""text;HDR=No;FMT=Delimited"";"
This connection works fine on my workstation, but not the SQL Server.
I assume this newer ACE driver gets installed with some other piece of
software (Office 2003?).
Maury
|||>> Why don't you bulk insert the file into a working table, setting
> Only works on 2005.
BULK INSERT only works on 2005? What are you talking about?

It's like MS hates us...

So all I'm trying to do is a JOIN against a text file. It's a simple CSV,
nothing fancy. The only "oddity", one is shares with _every_ other file I
have to work with, is that it has two header rows, one that has the filename
and date, and a second that has the column headers.
So I'm trying ot use OPENROWSET. This is basically impossible. The ODBC
driver (actually most of them) has a many-year-old bug that makes
FirstRowHasNames=0 not work, so it keeps trying to use the row with the
filename as the columns. This results in the columns being name
"thefilename", "todaysdate", "NoName", "NoName" etc. And no, you can't SELEC
T
on a column called "NoName". Sweeeeet!
This would be easy to work-around if there was some way to refer to columns
by their ordinal position, but of course, there isn't. And since it's a temp
table, there's no catalog entry, so you can't query the resultset to see wha
t
its made of and try to get the-column-name-for-today.
I also tried the OLE DB provider instead of the ODBC one, but every attempt
results in:
[OLE/DB provider returned message: Could not find installable ISAM.]
No, this problem is not caused by any of the bugs and/or problems listed on
the MS page.
*sigh*
MauryMaury,
Here is a kludge of an idea, provided you can get it implemented.
1. Put a one-line file on disk with the column header names that you want,
such as:
FileName, FileDate, UserName, SQLLogin, etc
2. Get a process to create your file to be imported by concatenating your
header file with the file to be imported. (Essentially):
COPY MyHDR.CSV+MyData.CSV MyImport.CSV
3. You read MyImport.CSV which has your header line concatenated on the
front with the column names you will use, then all the rest is data.
RLF
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:2D91F485-2104-4FE0-BB47-2A9104477B8D@.microsoft.com...
> So all I'm trying to do is a JOIN against a text file. It's a simple CSV,
> nothing fancy. The only "oddity", one is shares with _every_ other file I
> have to work with, is that it has two header rows, one that has the
> filename
> and date, and a second that has the column headers.
> So I'm trying ot use OPENROWSET. This is basically impossible. The ODBC
> driver (actually most of them) has a many-year-old bug that makes
> FirstRowHasNames=0 not work, so it keeps trying to use the row with the
> filename as the columns. This results in the columns being name
> "thefilename", "todaysdate", "NoName", "NoName" etc. And no, you can't
> SELECT
> on a column called "NoName". Sweeeeet!
> This would be easy to work-around if there was some way to refer to
> columns
> by their ordinal position, but of course, there isn't. And since it's a
> temp
> table, there's no catalog entry, so you can't query the resultset to see
> what
> its made of and try to get the-column-name-for-today.
> I also tried the OLE DB provider instead of the ODBC one, but every
> attempt
> results in:
> [OLE/DB provider returned message: Could not find installable ISAM.]
> No, this problem is not caused by any of the bugs and/or problems listed
> on
> the MS page.
> *sigh*
> Maury|||Why don't you bulk insert the file into a working table, setting FIRSTROW=3,
and then join against that?
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:2D91F485-2104-4FE0-BB47-2A9104477B8D@.microsoft.com...
> So all I'm trying to do is a JOIN against a text file. It's a simple CSV,
> nothing fancy. The only "oddity", one is shares with _every_ other file I
> have to work with, is that it has two header rows, one that has the
> filename
> and date, and a second that has the column headers.
> So I'm trying ot use OPENROWSET. This is basically impossible. The ODBC
> driver (actually most of them) has a many-year-old bug that makes
> FirstRowHasNames=0 not work, so it keeps trying to use the row with the
> filename as the columns. This results in the columns being name
> "thefilename", "todaysdate", "NoName", "NoName" etc. And no, you can't
> SELECT
> on a column called "NoName". Sweeeeet!
> This would be easy to work-around if there was some way to refer to
> columns
> by their ordinal position, but of course, there isn't. And since it's a
> temp
> table, there's no catalog entry, so you can't query the resultset to see
> what
> its made of and try to get the-column-name-for-today.
> I also tried the OLE DB provider instead of the ODBC one, but every
> attempt
> results in:
> [OLE/DB provider returned message: Could not find installable ISAM.]
> No, this problem is not caused by any of the bugs and/or problems listed
> on
> the MS page.
> *sigh*
> Maury|||Have you tried to create a link server to .TXT file and operate with it as a
table?
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:2D91F485-2104-4FE0-BB47-2A9104477B8D@.microsoft.com...
> So all I'm trying to do is a JOIN against a text file. It's a simple CSV,
> nothing fancy. The only "oddity", one is shares with _every_ other file I
> have to work with, is that it has two header rows, one that has the
> filename
> and date, and a second that has the column headers.
> So I'm trying ot use OPENROWSET. This is basically impossible. The ODBC
> driver (actually most of them) has a many-year-old bug that makes
> FirstRowHasNames=0 not work, so it keeps trying to use the row with the
> filename as the columns. This results in the columns being name
> "thefilename", "todaysdate", "NoName", "NoName" etc. And no, you can't
> SELECT
> on a column called "NoName". Sweeeeet!
> This would be easy to work-around if there was some way to refer to
> columns
> by their ordinal position, but of course, there isn't. And since it's a
> temp
> table, there's no catalog entry, so you can't query the resultset to see
> what
> its made of and try to get the-column-name-for-today.
> I also tried the OLE DB provider instead of the ODBC one, but every
> attempt
> results in:
> [OLE/DB provider returned message: Could not find installable ISAM.]
> No, this problem is not caused by any of the bugs and/or problems listed
> on
> the MS page.
> *sigh*
> Maury|||On May 8, 3:49 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> Why don't you bulk insert the file into a working table, setting FIRSTROW=
3,
> and then join against that?
Only works on 2005. I just went through a hair-raising upgrade to
2000SP3 in order to fix a single bug (interior inner joins on derived
tables), so I'm hesitant to move to 2005 at this point.
Maury|||On May 9, 2:36 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Have you tried to create a link server to .TXT file and operate with it as
a
> table?
Yes, this approach suffers from the same bugs. Not surprising really,
I'm sure it all forks through the same code.
It would be really nice if I could get the OLE DB approach working,
because it *seems* that it doesn't have the FirstRow bug, and I'm also
pretty familiar with the OLE DB model and feel pretty confident using
it. But I just can't figure out what's wrong. It's also odd that this
doesn't work:
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=O:\temp;Extended
Properties=""text;HDR=No;FMT=Delimited"";"
This connection works fine on my workstation, but not the SQL Server.
I assume this newer ACE driver gets installed with some other piece of
software (Office 2003?).
Maury|||>> Why don't you bulk insert the file into a working table, setting
> Only works on 2005.
BULK INSERT only works on 2005? What are you talking about?sql

It's like MS hates us...

So all I'm trying to do is a JOIN against a text file. It's a simple CSV,
nothing fancy. The only "oddity", one is shares with _every_ other file I
have to work with, is that it has two header rows, one that has the filename
and date, and a second that has the column headers.
So I'm trying ot use OPENROWSET. This is basically impossible. The ODBC
driver (actually most of them) has a many-year-old bug that makes
FirstRowHasNames=0 not work, so it keeps trying to use the row with the
filename as the columns. This results in the columns being name
"thefilename", "todaysdate", "NoName", "NoName" etc. And no, you can't SELECT
on a column called "NoName". Sweeeeet!
This would be easy to work-around if there was some way to refer to columns
by their ordinal position, but of course, there isn't. And since it's a temp
table, there's no catalog entry, so you can't query the resultset to see what
its made of and try to get the-column-name-for-today.
I also tried the OLE DB provider instead of the ODBC one, but every attempt
results in:
[OLE/DB provider returned message: Could not find installable ISAM.]
No, this problem is not caused by any of the bugs and/or problems listed on
the MS page.
*sigh*
MauryMaury,
Here is a kludge of an idea, provided you can get it implemented.
1. Put a one-line file on disk with the column header names that you want,
such as:
FileName, FileDate, UserName, SQLLogin, etc
2. Get a process to create your file to be imported by concatenating your
header file with the file to be imported. (Essentially):
COPY MyHDR.CSV+MyData.CSV MyImport.CSV
3. You read MyImport.CSV which has your header line concatenated on the
front with the column names you will use, then all the rest is data.
RLF
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:2D91F485-2104-4FE0-BB47-2A9104477B8D@.microsoft.com...
> So all I'm trying to do is a JOIN against a text file. It's a simple CSV,
> nothing fancy. The only "oddity", one is shares with _every_ other file I
> have to work with, is that it has two header rows, one that has the
> filename
> and date, and a second that has the column headers.
> So I'm trying ot use OPENROWSET. This is basically impossible. The ODBC
> driver (actually most of them) has a many-year-old bug that makes
> FirstRowHasNames=0 not work, so it keeps trying to use the row with the
> filename as the columns. This results in the columns being name
> "thefilename", "todaysdate", "NoName", "NoName" etc. And no, you can't
> SELECT
> on a column called "NoName". Sweeeeet!
> This would be easy to work-around if there was some way to refer to
> columns
> by their ordinal position, but of course, there isn't. And since it's a
> temp
> table, there's no catalog entry, so you can't query the resultset to see
> what
> its made of and try to get the-column-name-for-today.
> I also tried the OLE DB provider instead of the ODBC one, but every
> attempt
> results in:
> [OLE/DB provider returned message: Could not find installable ISAM.]
> No, this problem is not caused by any of the bugs and/or problems listed
> on
> the MS page.
> *sigh*
> Maury|||Why don't you bulk insert the file into a working table, setting FIRSTROW=3,
and then join against that?
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:2D91F485-2104-4FE0-BB47-2A9104477B8D@.microsoft.com...
> So all I'm trying to do is a JOIN against a text file. It's a simple CSV,
> nothing fancy. The only "oddity", one is shares with _every_ other file I
> have to work with, is that it has two header rows, one that has the
> filename
> and date, and a second that has the column headers.
> So I'm trying ot use OPENROWSET. This is basically impossible. The ODBC
> driver (actually most of them) has a many-year-old bug that makes
> FirstRowHasNames=0 not work, so it keeps trying to use the row with the
> filename as the columns. This results in the columns being name
> "thefilename", "todaysdate", "NoName", "NoName" etc. And no, you can't
> SELECT
> on a column called "NoName". Sweeeeet!
> This would be easy to work-around if there was some way to refer to
> columns
> by their ordinal position, but of course, there isn't. And since it's a
> temp
> table, there's no catalog entry, so you can't query the resultset to see
> what
> its made of and try to get the-column-name-for-today.
> I also tried the OLE DB provider instead of the ODBC one, but every
> attempt
> results in:
> [OLE/DB provider returned message: Could not find installable ISAM.]
> No, this problem is not caused by any of the bugs and/or problems listed
> on
> the MS page.
> *sigh*
> Maury|||Have you tried to create a link server to .TXT file and operate with it as a
table?
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:2D91F485-2104-4FE0-BB47-2A9104477B8D@.microsoft.com...
> So all I'm trying to do is a JOIN against a text file. It's a simple CSV,
> nothing fancy. The only "oddity", one is shares with _every_ other file I
> have to work with, is that it has two header rows, one that has the
> filename
> and date, and a second that has the column headers.
> So I'm trying ot use OPENROWSET. This is basically impossible. The ODBC
> driver (actually most of them) has a many-year-old bug that makes
> FirstRowHasNames=0 not work, so it keeps trying to use the row with the
> filename as the columns. This results in the columns being name
> "thefilename", "todaysdate", "NoName", "NoName" etc. And no, you can't
> SELECT
> on a column called "NoName". Sweeeeet!
> This would be easy to work-around if there was some way to refer to
> columns
> by their ordinal position, but of course, there isn't. And since it's a
> temp
> table, there's no catalog entry, so you can't query the resultset to see
> what
> its made of and try to get the-column-name-for-today.
> I also tried the OLE DB provider instead of the ODBC one, but every
> attempt
> results in:
> [OLE/DB provider returned message: Could not find installable ISAM.]
> No, this problem is not caused by any of the bugs and/or problems listed
> on
> the MS page.
> *sigh*
> Maury|||On May 8, 3:49 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> Why don't you bulk insert the file into a working table, setting FIRSTROW=3,
> and then join against that?
Only works on 2005. I just went through a hair-raising upgrade to
2000SP3 in order to fix a single bug (interior inner joins on derived
tables), so I'm hesitant to move to 2005 at this point.
Maury|||On May 9, 2:36 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Have you tried to create a link server to .TXT file and operate with it as a
> table?
Yes, this approach suffers from the same bugs. Not surprising really,
I'm sure it all forks through the same code.
It would be really nice if I could get the OLE DB approach working,
because it *seems* that it doesn't have the FirstRow bug, and I'm also
pretty familiar with the OLE DB model and feel pretty confident using
it. But I just can't figure out what's wrong. It's also odd that this
doesn't work:
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=O:\temp;Extended
Properties=""text;HDR=No;FMT=Delimited"";"
This connection works fine on my workstation, but not the SQL Server.
I assume this newer ACE driver gets installed with some other piece of
software (Office 2003?).
Maury|||>> Why don't you bulk insert the file into a working table, setting
>> FIRSTROW=3,
>> and then join against that?
> Only works on 2005.
BULK INSERT only works on 2005? What are you talking about?

Italic font, text aligned right

Hello
A textbox within a table cell is displayed strangely. The font style is
italics and the text is aligned to the right. Now the text is cut at its
right side by some pixels. So only 2/3 of the last letter is displayed.
Does someone have a solution for this problem?
Ciao,
JensI found out that it works with arial font, but it doesn't work with
Tahoma, though.
Ciao,
Jens
Jens Winter wrote:
> Hello
> A textbox within a table cell is displayed strangely. The font style is
> italics and the text is aligned to the right. Now the text is cut at its
> right side by some pixels. So only 2/3 of the last letter is displayed.
> Does someone have a solution for this problem?
> Ciao,
> Jens|||Try checking the padding parameters. This might cause the text to disappear,
if the text is exactly as long as the text box permits.
Kaisa M. Lindahl Lervik
"Jens Winter" <jwinter@.icubic-consulting.de> wrote in message
news:uUJi8g3YGHA.3516@.TK2MSFTNGP03.phx.gbl...
> Hello
> A textbox within a table cell is displayed strangely. The font style is
> italics and the text is aligned to the right. Now the text is cut at its
> right side by some pixels. So only 2/3 of the last letter is displayed.
> Does someone have a solution for this problem?
> Ciao,
> Jens|||Hello Kaisa,
I tried this too, but it didn't work.
I also tried to append some whitespaces at the end of the text. But they
get trimmed. This is another weirdness.
Anyhow, I assume it is a font issue. We will switch to Arial for the moment.
Ciao,
Jens
Kaisa M. Lindahl Lervik wrote:
> Try checking the padding parameters. This might cause the text to disappear,
> if the text is exactly as long as the text box permits.
> Kaisa M. Lindahl Lervik
> "Jens Winter" <jwinter@.icubic-consulting.de> wrote in message
> news:uUJi8g3YGHA.3516@.TK2MSFTNGP03.phx.gbl...
>> Hello
>> A textbox within a table cell is displayed strangely. The font style is
>> italics and the text is aligned to the right. Now the text is cut at its
>> right side by some pixels. So only 2/3 of the last letter is displayed.
>> Does someone have a solution for this problem?
>> Ciao,
>> Jens
>|||Sounds like maybe the size of your text box needs to be bigger to fit your
text.
"Jens Winter" wrote:
> Hello Kaisa,
> I tried this too, but it didn't work.
> I also tried to append some whitespaces at the end of the text. But they
> get trimmed. This is another weirdness.
> Anyhow, I assume it is a font issue. We will switch to Arial for the moment.
> Ciao,
> Jens
> Kaisa M. Lindahl Lervik wrote:
> > Try checking the padding parameters. This might cause the text to disappear,
> > if the text is exactly as long as the text box permits.
> >
> > Kaisa M. Lindahl Lervik
> > "Jens Winter" <jwinter@.icubic-consulting.de> wrote in message
> > news:uUJi8g3YGHA.3516@.TK2MSFTNGP03.phx.gbl...
> >> Hello
> >>
> >> A textbox within a table cell is displayed strangely. The font style is
> >> italics and the text is aligned to the right. Now the text is cut at its
> >> right side by some pixels. So only 2/3 of the last letter is displayed.
> >>
> >> Does someone have a solution for this problem?
> >>
> >> Ciao,
> >> Jens
> >
> >
>

Issues with Parameterised Report

Hi,

I want a matrix report which displays Sales Data or Discount Data (Column) Vs Year (Row). I want to have a parameter which displays a text as 'Sales', 'Discount'.

When I Select 'Sales', the report should display 'Sales Amount' Vs 'Year' and when I select 'Discount', the report should display 'Discount Data' Vs Year. I am getting all the data from OLAP Cube.

Any help is highly appreciated.

Thanks,

S Suresh

Create a string type parameter (say Parameter1) and have Available Values to be "Sales" and "Discount" (Non-queried)

In your matrix grouping (row or column), type the following expression:

IIf(Parameters!Parameter1.Value="Sales", Fields!Sales.Value, Fields!Discount.Value)

Shyam

issues with full text search (especially views and pick lists)

Hi, I am trying to implement a global full text search on our SQL
Server. Our app has several entities that are stored in the DB. I would
like to be able to search for 'John Doe' and get results in all types
of entities. Problem is:
-FT Search does not crawl views. Unfortunately, each entity type in
our system is not stored in full in one table. This is because we are
using pick lists and look ups. For instance: Industry type is stored as
a code that represents an entry in an industries table. However, all
these values are joined in a view to create all the required fields for
the entity a full text search query and index are created per table.
What would be an effective way to work around this problem? (I don't
see any solution to this problem in Yukon either.)
-Ranking: both 2000 and Yukon do not allow for merging of rankings
from several tables/queries. However, it is important to us to be able
to display results from several tables and sorted in a logical way. Any
suggestions/work arounds?
-Performance and scalability: How many rows and/or how many GB can I
have in my DB and still get good FT search query performance (less than
5 seconds)? (I need data for both 2000 and Yukon)
-Same with regards to indexing: ideally we would like to keep the
index up to date in real time. We would like to use the track changes
feature. However, our app allows many users to be logged in and
edit/delete/add entries in the DB. What is the maximum amount of DB
transactions per minute (second?) that will still allow us to keep the
index updated in real time? (I need data for both 2000 and Yukon)
-
I would greatly appreciate any suggestions/tips/info/workaround.
Thanks!
baraka,
Unfortunately, SQL Server 2000 FTS does not directly support the FT Indexing
of views. However, you can include FTS queries *in* views, but not create FT
Indexes *on* views in SQL Server 2000.
Since you're testing Yukon (SQL Server 2005), have you downloaded and
installed the June CTP version? If not, I'd highly recommend that you do so
and then create one of your views as an "indexed view" as FT Indexing an
indexed view is supported in SQL Server 2005.
Relative to merging ranking for multiple tables (in SQL 2000 or SQL 2005),
try the following example to dump them into a
temp table, the below option as a few advantages, especially as it relates
to "summing" via the MAX function the RANK value as well as using a group
by:
CREATE TABLE #FTSQueryResult (PK_ID int, Rank int)
INSERT #FTSQueryResult
SELECT PK, FTSTable.Rank FROM . . . (and the rest of your 1st query)
INSERT #FTSQueryResult
SELECT PK, FTSTable.Rank FROM . . . (and the rest of your 2nd query)
Select PK_ID, Max(Rank) as Rank From #FTSQueryResults
Group by PK_ID
In regards to performance and scalability, for SQL 2000 its less an issue
with amount of text, and more a factor of the number of rows and the
language of the text in the FT-enable columns as well as your server's
configuration - number & speed of procs, amount of RAM and speed of your
disk drives. Review SQL 2000 BOL title "Full-text Search Recommendations"
for more info! As for SQL 2005, I've worked with a customer (now in Yukon
TAP) who FT-indexed a table with 180 Million rows in 8 hours, while using
SQL 2000 on the same server and same database and FT-enable table took over
6 weeks to complete! Checkout the case study of an internal MS application
"SQL Server 2005 - 7x faster than SQL 2000 Full-Text Indexing" at
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!433.entry
I'd highly recommend that you review all of the links at "SQL Server 2000
Full-Text Search Resources and Links"
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.entry
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"baraka" <barak.benezer@.gmail.com> wrote in message
news:1122341762.314188.223230@.g14g2000cwa.googlegr oups.com...
> Hi, I am trying to implement a global full text search on our SQL
> Server. Our app has several entities that are stored in the DB. I would
> like to be able to search for 'John Doe' and get results in all types
> of entities. Problem is:
> - FT Search does not crawl views. Unfortunately, each entity type in
> our system is not stored in full in one table. This is because we are
> using pick lists and look ups. For instance: Industry type is stored as
> a code that represents an entry in an industries table. However, all
> these values are joined in a view to create all the required fields for
> the entity a full text search query and index are created per table.
> What would be an effective way to work around this problem? (I don't
> see any solution to this problem in Yukon either.)
> - Ranking: both 2000 and Yukon do not allow for merging of rankings
> from several tables/queries. However, it is important to us to be able
> to display results from several tables and sorted in a logical way. Any
> suggestions/work arounds?
> - Performance and scalability: How many rows and/or how many GB can I
> have in my DB and still get good FT search query performance (less than
> 5 seconds)? (I need data for both 2000 and Yukon)
> - Same with regards to indexing: ideally we would like to keep the
> index up to date in real time. We would like to use the track changes
> feature. However, our app allows many users to be logged in and
> edit/delete/add entries in the DB. What is the maximum amount of DB
> transactions per minute (second?) that will still allow us to keep the
> index updated in real time? (I need data for both 2000 and Yukon)
> -
> I would greatly appreciate any suggestions/tips/info/workaround.
> Thanks!
>

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
|