FTE locks the table

I have a table with Full Text index on it, the table has at the most 300
records.
The database is on the live server and gets hit a lot.
Every morning when i come to work the table is inaccessible, meaning any
query that i issue to this table runs indefinitely, Select, update, Insert
all just hang.
The only way for me to fix this is manually End The Full Text Sql Windows
Task, in task manager. As soon as i end the task it restarts immediately and
everything is working fine after.
Is there a patch for Sql 2005 to fix this, did anyone experience something
like that?Full-text indexing will cause some locking as it needs to create some level
of consistency with what is in the catalogs and what is in the database. Are
you using change tracking? If not you really should. You also might want to
set your resource usage to something smaller.
One more point, it is possible that other non-optimal procs or ad hoq sql
statements are themselves not tuned and are causing overall performance
problems manifesting itself in FTI locking.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Mike Kansky" <MikeKansky@.discussions.microsoft.com> wrote in message
news:206A8F61-21C9-475B-9095-C6708D29A09C@.microsoft.com...
>I have a table with Full Text index on it, the table has at the most 300
> records.
> The database is on the live server and gets hit a lot.
> Every morning when i come to work the table is inaccessible, meaning any
> query that i issue to this table runs indefinitely, Select, update, Insert
> all just hang.
> The only way for me to fix this is manually End The Full Text Sql Windows
> Task, in task manager. As soon as i end the task it restarts immediately
> and
> everything is working fine after.
> Is there a patch for Sql 2005 to fix this, did anyone experience something
> like that?
>

ft search error in win2k3 sp1 with sql 2k sp4?

I just setup my server with win2k3 sp1 and ALL the latest patches, plus
sql 2004 standard sp4.
I keep getting this error on a table with 39000 records where I am
running a basic wildcard CONTAINS fulltext query for "cer*"
Server: Msg 7643, Level 16, State 1, Line 1
Your search generated too many results. Please perform a more specific
search.
There should be about 30000 records.
I noticed on technet that this was a problem pre sp2 in sql, but was
since patched. Has the bug recently been re-introduced in some combo
of win2k3 and sql patches?
Phadke,
Yes, there was some additional logic added in SP3 that generates the below
error message. SQL FTS is optimized for returning 2000 results per query,
primarily for performance reasons. You may want to re-write your query using
CONTAINSTABLE and use the Top_N_by_Rank parameter to limit the results to
2000 or use a similar TOP restriction in the select list.
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
<phadke@.gmail.com> wrote in message
news:1129632193.545575.218860@.g47g2000cwa.googlegr oups.com...
>I just setup my server with win2k3 sp1 and ALL the latest patches, plus
> sql 2004 standard sp4.
> I keep getting this error on a table with 39000 records where I am
> running a basic wildcard CONTAINS fulltext query for "cer*"
> Server: Msg 7643, Level 16, State 1, Line 1
> Your search generated too many results. Please perform a more specific
> search.
> There should be about 30000 records.
> I noticed on technet that this was a problem pre sp2 in sql, but was
> since patched. Has the bug recently been re-introduced in some combo
> of win2k3 and sql patches?
>
|||John,
Just to clarify, is this a hard limit of 2000, because I am able to see
the result set for queries that return a con query with 17-18k records
with no problem.
I saw another technote that the problem limit was at approx 20k
records:
http://support.microsoft.com/default...;en-us;Q296456
Thanks for the info,
Aditya
|||Also, is this limitation common to sql 2005?
|||Ok, now I am really confused...I ran a fulltext query for "c*" on a
database on another server. This database has 2.4 Million Records.
This server is Win2k3 (no SP) and SQL 2k SP3.
When I run the query, I get 900,000 records back without an error
message.
|||Aditya,
Yes, this is the KB article that I couldn't find early this morning when in
a rush to get off to work...
Still for performance reasons, it might be best to use Top_N_Rank with
CONTAINSTABLE or is there a specific reason you want to return this many
rows?
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
<phadke@.gmail.com> wrote in message
news:1129660216.247521.205300@.g47g2000cwa.googlegr oups.com...
> John,
> Just to clarify, is this a hard limit of 2000, because I am able to see
> the result set for queries that return a con query with 17-18k records
> with no problem.
> I saw another technote that the problem limit was at approx 20k
> records:
> http://support.microsoft.com/default...;en-us;Q296456
> Thanks for the info,
> Aditya
>
|||Aditya,
I need to confirm this with a large table in order to generate the large
results. However, I would suspect that it is still in SQL 2005 until proved
otherwise...
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Aditya Phadke" <phadke@.gmail.com> wrote in message
news:1129660966.529955.176010@.g44g2000cwa.googlegr oups.com...
> Also, is this limitation common to sql 2005?
>
|||Aditya,
Different servers can behavior differently for FTS results based upon the OS
platform (different wordbreakers) and due to different noise word file
contents. In this case, and on this server is the single letter "C" removed
from the language-specific noise word file (noise.enu = US English) for the
specific FT-enable column's "Language for Word Breaker"?
As they say the devil is in the details... ;-)
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Aditya Phadke" <phadke@.gmail.com> wrote in message
news:1129672239.794380.257680@.o13g2000cwo.googlegr oups.com...
> Ok, now I am really confused...I ran a fulltext query for "c*" on a
> database on another server. This database has 2.4 Million Records.
> This server is Win2k3 (no SP) and SQL 2k SP3.
> When I run the query, I get 900,000 records back without an error
> message.
>

Frustrating Newbie Parameter Question

Is it possible to create a parameter where a NULL value returns ALL records
and entering a value returns the filtered data? I am filtering a date field
"ConvYear" (i.e. 2001, 2002, 2003,etc..) and I want to give the end user a
choice of filtering the dataset with a paramater or leaving it blank and
returning records with all years.
There must be an easy way to do this. I am aware of placing an iif
statement in the dataset paramater
(i.e. =iif(Parameters!Parameter1.Value = "",""," WHERE odConvYear = " +
Parameters!Parameter1.Value)),
but if the parameter is empty, how will this return ALL records? I am
confused and would appreciate any help.
My query looks like this...
SELECT OrderDetail.*
FROM OrderDetail
WHERE (fkCategoryID = 4) AND (ConvYear = ?)
Thanks!Two points, unless you have to use a filter, don't (hard for me to tell if
you are using a filter or not). A filter first retrieves all the data
defined in the dataset and then filters it. So for instance with what you
have below, you should use that query as the definition for your dataset.
Also note that for a query parameter you use @.whateveryouwanttocallit if you
are going against SQL Server and ? if going against oledb or odbc datasourc.
OK, the trick here is to use like. Then for your parameters have an All that
returns a %. To test this out first try have your labels and values be a
list that you input on the report parameters screen (you can also use a
Union query to do this as well). What I like about this solution is you
don't have to use the generic query screen plus you don't lose having a
field list. Anyway, give it a try.
SELECT OrderDetail.* FROM OrderDetail WHERE (fkCategoryID =4) AND (ConvYear like ?)
Bruce L-C
"Brian" <bcovington@.hotmail.com> wrote in message
news:%23vYpB7znEHA.2024@.TK2MSFTNGP09.phx.gbl...
> Is it possible to create a parameter where a NULL value returns ALL
records
> and entering a value returns the filtered data? I am filtering a date
field
> "ConvYear" (i.e. 2001, 2002, 2003,etc..) and I want to give the end user
a
> choice of filtering the dataset with a paramater or leaving it blank and
> returning records with all years.
> There must be an easy way to do this. I am aware of placing an iif
> statement in the dataset paramater
> (i.e. =iif(Parameters!Parameter1.Value = "",""," WHERE odConvYear = " +
> Parameters!Parameter1.Value)),
> but if the parameter is empty, how will this return ALL records? I am
> confused and would appreciate any help.
> My query looks like this...
> SELECT OrderDetail.*
> FROM OrderDetail
> WHERE (fkCategoryID = 4) AND (ConvYear = ?)
> Thanks!
>
>
>
>|||This works great! Would this be a good solution if I wanted say 5 optional
parameters in a report, or is the LIKE clause too slow? I have about 20,000
records to search on, but the report isn't run very often.
Also, I have seen others discuss using dynamic SQL with iff statements to
obtain the same results. Would this possibly be a more efficient solution
than the LIKE clause?
Thanks,
Brian
"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:%23qKTNF0nEHA.1152@.TK2MSFTNGP10.phx.gbl...
> Two points, unless you have to use a filter, don't (hard for me to tell if
> you are using a filter or not). A filter first retrieves all the data
> defined in the dataset and then filters it. So for instance with what you
> have below, you should use that query as the definition for your dataset.
> Also note that for a query parameter you use @.whateveryouwanttocallit if
> you
> are going against SQL Server and ? if going against oledb or odbc
> datasourc.
> OK, the trick here is to use like. Then for your parameters have an All
> that
> returns a %. To test this out first try have your labels and values be a
> list that you input on the report parameters screen (you can also use a
> Union query to do this as well). What I like about this solution is you
> don't have to use the generic query screen plus you don't lose having a
> field list. Anyway, give it a try.
> SELECT OrderDetail.* FROM OrderDetail WHERE (fkCategoryID => 4) AND (ConvYear like ?)
> Bruce L-C
> "Brian" <bcovington@.hotmail.com> wrote in message
> news:%23vYpB7znEHA.2024@.TK2MSFTNGP09.phx.gbl...
>> Is it possible to create a parameter where a NULL value returns ALL
> records
>> and entering a value returns the filtered data? I am filtering a date
> field
>> "ConvYear" (i.e. 2001, 2002, 2003,etc..) and I want to give the end user
> a
>> choice of filtering the dataset with a paramater or leaving it blank and
>> returning records with all years.
>> There must be an easy way to do this. I am aware of placing an iif
>> statement in the dataset paramater
>> (i.e. =iif(Parameters!Parameter1.Value = "",""," WHERE odConvYear = " +
>> Parameters!Parameter1.Value)),
>> but if the parameter is empty, how will this return ALL records? I am
>> confused and would appreciate any help.
>> My query looks like this...
>> SELECT OrderDetail.*
>> FROM OrderDetail
>> WHERE (fkCategoryID = 4) AND (ConvYear = ?)
>> Thanks!
>>
>>
>>
>|||I prefer the method I showed you because it is soooo much easier to develop,
you get the list of fields, you can use the normal query designer. Whether
it is slow or not depends on the database you are going against. If it is
SQL Server 2000 and you have 20,000 records it won't even breath hard.
20,000 is nothing. I suggest trying it this way, if performance is a problem
you can always go to dynamic sql.
Bruce L-C
"Brian" <bcovington@.hotmail.com> wrote in message
news:eb2du30nEHA.2024@.TK2MSFTNGP09.phx.gbl...
> This works great! Would this be a good solution if I wanted say 5
optional
> parameters in a report, or is the LIKE clause too slow? I have about
20,000
> records to search on, but the report isn't run very often.
> Also, I have seen others discuss using dynamic SQL with iff statements to
> obtain the same results. Would this possibly be a more efficient solution
> than the LIKE clause?
> Thanks,
> Brian
>
>
> "Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:%23qKTNF0nEHA.1152@.TK2MSFTNGP10.phx.gbl...
> > Two points, unless you have to use a filter, don't (hard for me to tell
if
> > you are using a filter or not). A filter first retrieves all the data
> > defined in the dataset and then filters it. So for instance with what
you
> > have below, you should use that query as the definition for your
dataset.
> >
> > Also note that for a query parameter you use @.whateveryouwanttocallit if
> > you
> > are going against SQL Server and ? if going against oledb or odbc
> > datasourc.
> >
> > OK, the trick here is to use like. Then for your parameters have an All
> > that
> > returns a %. To test this out first try have your labels and values be a
> > list that you input on the report parameters screen (you can also use a
> > Union query to do this as well). What I like about this solution is you
> > don't have to use the generic query screen plus you don't lose having a
> > field list. Anyway, give it a try.
> > SELECT OrderDetail.* FROM OrderDetail WHERE (fkCategoryID
=> > 4) AND (ConvYear like ?)
> >
> > Bruce L-C
> >
> > "Brian" <bcovington@.hotmail.com> wrote in message
> > news:%23vYpB7znEHA.2024@.TK2MSFTNGP09.phx.gbl...
> >> Is it possible to create a parameter where a NULL value returns ALL
> > records
> >> and entering a value returns the filtered data? I am filtering a date
> > field
> >> "ConvYear" (i.e. 2001, 2002, 2003,etc..) and I want to give the end
user
> > a
> >> choice of filtering the dataset with a paramater or leaving it blank
and
> >> returning records with all years.
> >>
> >> There must be an easy way to do this. I am aware of placing an iif
> >> statement in the dataset paramater
> >> (i.e. =iif(Parameters!Parameter1.Value = "",""," WHERE odConvYear = " +
> >> Parameters!Parameter1.Value)),
> >> but if the parameter is empty, how will this return ALL records? I am
> >> confused and would appreciate any help.
> >>
> >> My query looks like this...
> >>
> >> SELECT OrderDetail.*
> >> FROM OrderDetail
> >> WHERE (fkCategoryID = 4) AND (ConvYear = ?)
> >>
> >> Thanks!
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >
> >
>|||Bruce,
Your time was very much appreciated. Thank you!
Brian
"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:ueS7sG1nEHA.3216@.tk2msftngp13.phx.gbl...
>I prefer the method I showed you because it is soooo much easier to
>develop,
> you get the list of fields, you can use the normal query designer. Whether
> it is slow or not depends on the database you are going against. If it is
> SQL Server 2000 and you have 20,000 records it won't even breath hard.
> 20,000 is nothing. I suggest trying it this way, if performance is a
> problem
> you can always go to dynamic sql.
> Bruce L-C
> "Brian" <bcovington@.hotmail.com> wrote in message
> news:eb2du30nEHA.2024@.TK2MSFTNGP09.phx.gbl...
>> This works great! Would this be a good solution if I wanted say 5
> optional
>> parameters in a report, or is the LIKE clause too slow? I have about
> 20,000
>> records to search on, but the report isn't run very often.
>> Also, I have seen others discuss using dynamic SQL with iff statements to
>> obtain the same results. Would this possibly be a more efficient
>> solution
>> than the LIKE clause?
>> Thanks,
>> Brian
>>
>>
>> "Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> news:%23qKTNF0nEHA.1152@.TK2MSFTNGP10.phx.gbl...
>> > Two points, unless you have to use a filter, don't (hard for me to tell
> if
>> > you are using a filter or not). A filter first retrieves all the data
>> > defined in the dataset and then filters it. So for instance with what
> you
>> > have below, you should use that query as the definition for your
> dataset.
>> >
>> > Also note that for a query parameter you use @.whateveryouwanttocallit
>> > if
>> > you
>> > are going against SQL Server and ? if going against oledb or odbc
>> > datasourc.
>> >
>> > OK, the trick here is to use like. Then for your parameters have an All
>> > that
>> > returns a %. To test this out first try have your labels and values be
>> > a
>> > list that you input on the report parameters screen (you can also use a
>> > Union query to do this as well). What I like about this solution is you
>> > don't have to use the generic query screen plus you don't lose having a
>> > field list. Anyway, give it a try.
>> > SELECT OrderDetail.* FROM OrderDetail WHERE
>> > (fkCategoryID
> =>> > 4) AND (ConvYear like ?)
>> >
>> > Bruce L-C
>> >
>> > "Brian" <bcovington@.hotmail.com> wrote in message
>> > news:%23vYpB7znEHA.2024@.TK2MSFTNGP09.phx.gbl...
>> >> Is it possible to create a parameter where a NULL value returns ALL
>> > records
>> >> and entering a value returns the filtered data? I am filtering a date
>> > field
>> >> "ConvYear" (i.e. 2001, 2002, 2003,etc..) and I want to give the end
> user
>> > a
>> >> choice of filtering the dataset with a paramater or leaving it blank
> and
>> >> returning records with all years.
>> >>
>> >> There must be an easy way to do this. I am aware of placing an iif
>> >> statement in the dataset paramater
>> >> (i.e. =iif(Parameters!Parameter1.Value = "",""," WHERE odConvYear = "
>> >> +
>> >> Parameters!Parameter1.Value)),
>> >> but if the parameter is empty, how will this return ALL records? I am
>> >> confused and would appreciate any help.
>> >>
>> >> My query looks like this...
>> >>
>> >> SELECT OrderDetail.*
>> >> FROM OrderDetail
>> >> WHERE (fkCategoryID = 4) AND (ConvYear = ?)
>> >>
>> >> Thanks!
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >
>> >
>>
>|||We've used a statement in the where clause like :
where (ssn.Item_Number = IsNull(@.Stock_Code, ssn.Item_Number))
which returns all items if the sp parameter is null of uses the parameter
value to filter.
Julian
"Brian" <bcovington@.hotmail.com> wrote in message
news:uJyf4T1nEHA.132@.TK2MSFTNGP09.phx.gbl...
> Bruce,
> Your time was very much appreciated. Thank you!
> Brian
>
> "Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:ueS7sG1nEHA.3216@.tk2msftngp13.phx.gbl...
> >I prefer the method I showed you because it is soooo much easier to
> >develop,
> > you get the list of fields, you can use the normal query designer.
Whether
> > it is slow or not depends on the database you are going against. If it
is
> > SQL Server 2000 and you have 20,000 records it won't even breath hard.
> > 20,000 is nothing. I suggest trying it this way, if performance is a
> > problem
> > you can always go to dynamic sql.
> >
> > Bruce L-C
> >
> > "Brian" <bcovington@.hotmail.com> wrote in message
> > news:eb2du30nEHA.2024@.TK2MSFTNGP09.phx.gbl...
> >> This works great! Would this be a good solution if I wanted say 5
> > optional
> >> parameters in a report, or is the LIKE clause too slow? I have about
> > 20,000
> >> records to search on, but the report isn't run very often.
> >>
> >> Also, I have seen others discuss using dynamic SQL with iff statements
to
> >> obtain the same results. Would this possibly be a more efficient
> >> solution
> >> than the LIKE clause?
> >>
> >> Thanks,
> >>
> >> Brian
> >>
> >>
> >>
> >>
> >> "Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> >> news:%23qKTNF0nEHA.1152@.TK2MSFTNGP10.phx.gbl...
> >> > Two points, unless you have to use a filter, don't (hard for me to
tell
> > if
> >> > you are using a filter or not). A filter first retrieves all the data
> >> > defined in the dataset and then filters it. So for instance with what
> > you
> >> > have below, you should use that query as the definition for your
> > dataset.
> >> >
> >> > Also note that for a query parameter you use @.whateveryouwanttocallit
> >> > if
> >> > you
> >> > are going against SQL Server and ? if going against oledb or odbc
> >> > datasourc.
> >> >
> >> > OK, the trick here is to use like. Then for your parameters have an
All
> >> > that
> >> > returns a %. To test this out first try have your labels and values
be
> >> > a
> >> > list that you input on the report parameters screen (you can also use
a
> >> > Union query to do this as well). What I like about this solution is
you
> >> > don't have to use the generic query screen plus you don't lose having
a
> >> > field list. Anyway, give it a try.
> >> > SELECT OrderDetail.* FROM OrderDetail WHERE
> >> > (fkCategoryID
> > => >> > 4) AND (ConvYear like ?)
> >> >
> >> > Bruce L-C
> >> >
> >> > "Brian" <bcovington@.hotmail.com> wrote in message
> >> > news:%23vYpB7znEHA.2024@.TK2MSFTNGP09.phx.gbl...
> >> >> Is it possible to create a parameter where a NULL value returns ALL
> >> > records
> >> >> and entering a value returns the filtered data? I am filtering a
date
> >> > field
> >> >> "ConvYear" (i.e. 2001, 2002, 2003,etc..) and I want to give the end
> > user
> >> > a
> >> >> choice of filtering the dataset with a paramater or leaving it blank
> > and
> >> >> returning records with all years.
> >> >>
> >> >> There must be an easy way to do this. I am aware of placing an iif
> >> >> statement in the dataset paramater
> >> >> (i.e. =iif(Parameters!Parameter1.Value = "",""," WHERE odConvYear ="
> >> >> +
> >> >> Parameters!Parameter1.Value)),
> >> >> but if the parameter is empty, how will this return ALL records? I
am
> >> >> confused and would appreciate any help.
> >> >>
> >> >> My query looks like this...
> >> >>
> >> >> SELECT OrderDetail.*
> >> >> FROM OrderDetail
> >> >> WHERE (fkCategoryID = 4) AND (ConvYear = ?)
> >> >>
> >> >> Thanks!
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>|||Excellent tip! The visual designer messes up statements like:
(@.ItemNo Is Null OR ItemNumber = @.ItemNo)sql

From 1 field to another

Hi, how can I copy the records from some table and field to the same table
but another field?
Example: Table: Customer->Address field
to Table: Customer->AddressBkp field
Select inside Insert ? how?
Thanks!
> Select inside Insert ? how?
Try the example below and map the other columns as desired too:
INSERT INTO dbo,Customer (AddressBkp)
SELECT Address FROM dbo,Customer
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"Paulo" <prbspfc@.uol.com.br> wrote in message
news:%23BmusD9bIHA.4684@.TK2MSFTNGP06.phx.gbl...
> Hi, how can I copy the records from some table and field to the same table
> but another field?
> Example: Table: Customer->Address field
> to Table: Customer->AddressBkp field
> Select inside Insert ? how?
> Thanks!
>
|||I think you need to do an update:
UPDATE Customer
SET AddressBkp = Address
Note that this query does not have a WHERE clause, so all rows in the
Customer table will be updated.
HTH,
Plamen Ratchev
http://www.SQLStudio.com

From 1 field to another

Hi, how can I copy the records from some table and field to the same table
but another field?
Example: Table: Customer->Address field
to Table: Customer->AddressBkp field
Select inside Insert ? how?
Thanks!> Select inside Insert ? how?
Try the example below and map the other columns as desired too:
INSERT INTO dbo,Customer (AddressBkp)
SELECT Address FROM dbo,Customer
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"Paulo" <prbspfc@.uol.com.br> wrote in message
news:%23BmusD9bIHA.4684@.TK2MSFTNGP06.phx.gbl...
> Hi, how can I copy the records from some table and field to the same table
> but another field?
> Example: Table: Customer->Address field
> to Table: Customer->AddressBkp field
> Select inside Insert ? how?
> Thanks!
>|||I think you need to do an update:
UPDATE Customer
SET AddressBkp = Address
Note that this query does not have a WHERE clause, so all rows in the
Customer table will be updated.
HTH,
Plamen Ratchev
http://www.SQLStudio.com

frequent index corruption

Hi All,
I have a table on SQL 2000 (SP4) with about 55k records and was not
having any problems with it for some time. Recently, I am getting
index corruption errors such as:
Could not find the index entry for RID
'3601000001001c0031002d0044004b0058002d0
03800340037003300' in index
page (1:1396706), index ID 3, database <db_name>
I've tried researching on this matter, but there seems to be no clear
explanation on how or why these are happening. I have looked at the
possibility of a hardware problem, but the same set of indexes on two
replication subscribers (from a single publisher) have the corruption.
So it would be quite unlikely that both subscribers, which are
different machines on different locations, would have the same
hardware problem at the same time. The publisher does not have this
problem.
Second, if I drop the corrupt indexes, the errors go away, and if I
recreate them the corruption is consistently present. I did the
dropping and creation a couple of times, and it is very consistent,
which is odd.
I came across KB 822747 (http://support.microsoft.com/kb/822747),
which was quite similar to what I am having since my database is on a
binary collation and the involve indexes have several unicode columns,
but I'm not sure if there is any relationship there. The issue in the
KB was said to be fixed on SP4, but I already got SP4.
DBCC does not return any error, btw.
I hope someone can enlighten me on this or point me to the right
direction.
Thanks,
AramidThis sounds like a case for MS support. I'd run through method#2 in the
stated KB and collect the result. Along with your server info (@.@.version),
it should be a very quick call. You will need a credit to open a case, but
shouldn't be charged if it's indeed a bug.
-oj
"Aramid" <aramid@.hotmail.com> wrote in message
news:ml6d82tca5vf3gr71od5sc8fj5kjp5qtkv@.
4ax.com...
> Hi All,
> I have a table on SQL 2000 (SP4) with about 55k records and was not
> having any problems with it for some time. Recently, I am getting
> index corruption errors such as:
> Could not find the index entry for RID
> '3601000001001c0031002d0044004b0058002d0
03800340037003300' in index
> page (1:1396706), index ID 3, database <db_name>
> I've tried researching on this matter, but there seems to be no clear
> explanation on how or why these are happening. I have looked at the
> possibility of a hardware problem, but the same set of indexes on two
> replication subscribers (from a single publisher) have the corruption.
> So it would be quite unlikely that both subscribers, which are
> different machines on different locations, would have the same
> hardware problem at the same time. The publisher does not have this
> problem.
> Second, if I drop the corrupt indexes, the errors go away, and if I
> recreate them the corruption is consistently present. I did the
> dropping and creation a couple of times, and it is very consistent,
> which is odd.
> I came across KB 822747 (http://support.microsoft.com/kb/822747),
> which was quite similar to what I am having since my database is on a
> binary collation and the involve indexes have several unicode columns,
> but I'm not sure if there is any relationship there. The issue in the
> KB was said to be fixed on SP4, but I already got SP4.
> DBCC does not return any error, btw.
> I hope someone can enlighten me on this or point me to the right
> direction.
> Thanks,
> Aramid

frequent index corruption

Hi All,
I have a table on SQL 2000 (SP4) with about 55k records and was not
having any problems with it for some time. Recently, I am getting
index corruption errors such as:
Could not find the index entry for RID
'3601000001001c0031002d0044004b0058002d003800340037003300' in index
page (1:1396706), index ID 3, database <db_name>
I've tried researching on this matter, but there seems to be no clear
explanation on how or why these are happening. I have looked at the
possibility of a hardware problem, but the same set of indexes on two
replication subscribers (from a single publisher) have the corruption.
So it would be quite unlikely that both subscribers, which are
different machines on different locations, would have the same
hardware problem at the same time. The publisher does not have this
problem.
Second, if I drop the corrupt indexes, the errors go away, and if I
recreate them the corruption is consistently present. I did the
dropping and creation a couple of times, and it is very consistent,
which is odd.
I came across KB 822747 (http://support.microsoft.com/kb/822747),
which was quite similar to what I am having since my database is on a
binary collation and the involve indexes have several unicode columns,
but I'm not sure if there is any relationship there. The issue in the
KB was said to be fixed on SP4, but I already got SP4.
DBCC does not return any error, btw.
I hope someone can enlighten me on this or point me to the right
direction.
Thanks,
AramidThis sounds like a case for MS support. I'd run through method#2 in the
stated KB and collect the result. Along with your server info (@.@.version),
it should be a very quick call. You will need a credit to open a case, but
shouldn't be charged if it's indeed a bug.
--
-oj
"Aramid" <aramid@.hotmail.com> wrote in message
news:ml6d82tca5vf3gr71od5sc8fj5kjp5qtkv@.4ax.com...
> Hi All,
> I have a table on SQL 2000 (SP4) with about 55k records and was not
> having any problems with it for some time. Recently, I am getting
> index corruption errors such as:
> Could not find the index entry for RID
> '3601000001001c0031002d0044004b0058002d003800340037003300' in index
> page (1:1396706), index ID 3, database <db_name>
> I've tried researching on this matter, but there seems to be no clear
> explanation on how or why these are happening. I have looked at the
> possibility of a hardware problem, but the same set of indexes on two
> replication subscribers (from a single publisher) have the corruption.
> So it would be quite unlikely that both subscribers, which are
> different machines on different locations, would have the same
> hardware problem at the same time. The publisher does not have this
> problem.
> Second, if I drop the corrupt indexes, the errors go away, and if I
> recreate them the corruption is consistently present. I did the
> dropping and creation a couple of times, and it is very consistent,
> which is odd.
> I came across KB 822747 (http://support.microsoft.com/kb/822747),
> which was quite similar to what I am having since my database is on a
> binary collation and the involve indexes have several unicode columns,
> but I'm not sure if there is any relationship there. The issue in the
> KB was said to be fixed on SP4, but I already got SP4.
> DBCC does not return any error, btw.
> I hope someone can enlighten me on this or point me to the right
> direction.
> Thanks,
> Aramid

freezing column and header in report

I have report with large records and many columns

I would like to freez header and specific column like excel

I tried to freez header it self it's worked

I tried to freez a column it self it's worked

when I tried to freez both in same report it does not work..

any help?

This should work. I assume that you are useing the Matrix type. Are there mutiple groups (rows)?
Are you specifying the

FreeTextTable Rank

Hi. I have a question - does anyone know on what criteria is the rank set by
FreeTextTable ? I created some records in the table "titles" containing the
words "drink", "much" and "abstinence" and... well... I can find no rhyme
or reason to the way this rank is established. I got the ranks 293, 266, 266
and 154. The funny thing is that a title like "No *abstinence* for me,
please !" has a higher rank than "ABSTINENCE for dummies." with the word in
capitals.
So... how exactly is this rank set ? How reliable is it ?
I'm "afraid" to filter the returns (for instance TOP N) and I am "afraid" to
set conditions like "where rank >..." (of course, I could always show all
the hits and page the datagrid).
Thanks a lot.
Alex.
After some searching, I found this (for SQL2005):
Ranking of FREETEXT
Freetext ranking is based on the OKAPI BM25 ranking formula. Each term in
the query is ranked, and the values are summed. Freetext queries will add
words to the query via inflectional generation (stemmed forms of the
original query terms); these words are treated as separate terms with no
special weighting or relationship with the words from which they were
generated. Synonyms generated from the Thesaurus feature are treated as
separate, equally weighted terms.
Rank = ?[Terms in Query] w ( ( ( k1 + 1 ) tf ) / ( K + tf ) ) * ( ( k3 + 1 )
qtf / ( k3 + qtf ) ) )
Where:
w is the Robertson-Sparck Jones weight.
Originally, w is defined as:
w = log10 ( ( ( r + 0.5 ) * ( N - n - R + r + 0.5 ) ) / ( ( R - r + 0.5 ) *
( n - r + 0.5 ) ) )
This was simplified to:
w = log10 ( ( ( r + 0.5 ) * ( N - R + r + 0.5 ) ) / ( ( R - r + 0.5 ) * (
n - r + 0.5 ) ) )
R is the number of documents marked relevant by a user. This is not
implemented in SQL Server 2005 full-text search, and thus is ignored.
r is the number of documents marked relevant by a user containing the term.
This is not implemented.
N is the number of documents with values for the property in the query.
n is the number of documents containing the term.
K is ( k1 * ( ( 1 - b ) + ( b * dl / avdl ) ) )
dl is the document length, in word occurrences.
avdl is the average document length of the property over which the query
spans, in word occurrences.
k1, b, and k3 are the constants 1.2, 0.75, and 8.0, respectively.
tf is the frequency of the term in a specific document.
qtf is the frequency of the term in the query.
So now I know :-)))) Anyway, apparently the ranking results have to be taken
with a *BIG" grain of salt.
Alex.
|||Hi Alex,
Yes, I do, but it is complex and you need to understand a bit about basic
Informational Retrieval theory. The Rank value from FREETEXTTABLE is based
upon what is known as OKAPI or BM25 that was developed by Stephen Robertson
(http://research.microsoft.com/users/robertson/). You can see some of the
formula documented in "SQL Server 2005 Full-Text Search: Internals and
Enhancements" at:
http://msdn.microsoft.com/library/de...05ftsearch.asp
Specifically, under "Ranking of FREETEXT" -
"Freetext ranking is based on the OKAPI BM25 ranking formula. Each term in
the query is ranked, and the values are summed. Freetext queries will add
words to the query via inflectional generation (stemmed forms of the
original query terms); these words are treated as separate terms with no
special weighting or relationship with the words from which they were
generated. Synonyms generated from the Thesaurus feature are treated as
separate, equally weighted terms."...
See also http://wickedsmrt.blogspot.com/2003_...t_archive.html
"From MS Newsgroups: However it was my understanding that rank is based on
this formula:
? W(i)=(K1+1) ?idf(i)?(K2+1) ?tf(i.j) /( K1?[(1-b)+b?dl
(j)/avdl])?K3(tf(i.j))
W(i) - rank from each term in the search phrase
idf(i) - iS the inverse document frequency of term i
tf(i,j) - is the term frequency for term i, in row j
K1,K2,K3 - are constants
dl - is row/column length in words
AVdl - is the average row/column length length in words"
Another factor is the number of rows and the number of unique non-noise
words per row as you must have a statistically significant number of rows
(at least 10,000) for the OKAPI BM25 Freetexttable Rank value to be
meaningful. How many rows are in your table "titles"? Also keep in mind that
the Rank values are specific to your freetext query and primarily useful for
ordering of the results. See SQL Server 2000 BOL title "Full-text Search
Recommendations" - "What is RANK and how is it determined when used with
CONTAINSTABLE and FREETEXTTABLE predicates?..." for more info.
Yes, OKAPI BM25 is very reliable, but complex. Note, that for US English,
SQL FTS is case insensitive and "ABSTINENCE" will have the same rank value
as "abstinence" with all other factors being equal. It is complex as you can
see, but what is your true objective? Could you provide the exact Freetext
query with sample data and results as well as the full output of SELECT
@.@.version ?
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"msnews.microsoft.com" <REMOVETHIScuca_macaii2000@.yahoo.com> wrote in
message news:#YEbgXDBFHA.3824@.TK2MSFTNGP10.phx.gbl...
> Hi. I have a question - does anyone know on what criteria is the rank set
by
> FreeTextTable ? I created some records in the table "titles" containing
the
> words "drink", "much" and "abstinence" and... well... I can find no
rhyme
> or reason to the way this rank is established. I got the ranks 293, 266,
266
> and 154. The funny thing is that a title like "No *abstinence* for me,
> please !" has a higher rank than "ABSTINENCE for dummies." with the word
in
> capitals.
> So... how exactly is this rank set ? How reliable is it ?
> I'm "afraid" to filter the returns (for instance TOP N) and I am "afraid"
to
> set conditions like "where rank >..." (of course, I could always show all
> the hits and page the datagrid).
> Thanks a lot.
> Alex.
>
|||Alex,
Yep, so you found (via Google?) the SQL 2005 FTS paper published in Dec
2004. While it documents SQL 2005, I *believe* that as far as the contains
and freetext ranking formula's that they hold true for SQL Server 2000 as
well. Even so, I'm surprised that for SQL Server 2005 (or for that matter
SQL 2000) that relevance feedback (R & r in the formula) was not implemented
as there are standard methods using T-SQL and feedback tables that can be
use to implement automatic relevance feedback...
Oh, and that *BIG" grain of salt, that you speak of, not necessary... You
now know the formula, and with your table's unique non-noise words, you can
calculate the rank values from your query by yourself!
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"msnews.microsoft.com" <REMOVETHIScuca_macaii2000@.yahoo.com> wrote in
message news:eAAhomDBFHA.2012@.TK2MSFTNGP15.phx.gbl...
> After some searching, I found this (for SQL2005):
> Ranking of FREETEXT
> Freetext ranking is based on the OKAPI BM25 ranking formula. Each term in
> the query is ranked, and the values are summed. Freetext queries will add
> words to the query via inflectional generation (stemmed forms of the
> original query terms); these words are treated as separate terms with no
> special weighting or relationship with the words from which they were
> generated. Synonyms generated from the Thesaurus feature are treated as
> separate, equally weighted terms.
> Rank = ?[Terms in Query] w ( ( ( k1 + 1 ) tf ) / ( K + tf ) ) * ( ( k3 +
1 )
> qtf / ( k3 + qtf ) ) )
> Where:
> w is the Robertson-Sparck Jones weight.
> Originally, w is defined as:
> w = log10 ( ( ( r + 0.5 ) * ( N - n - R + r + 0.5 ) ) / ( ( R - r + 0.5 )
*
> ( n - r + 0.5 ) ) )
> This was simplified to:
> w = log10 ( ( ( r + 0.5 ) * ( N - R + r + 0.5 ) ) / ( ( R - r + 0.5 ) * (
> n - r + 0.5 ) ) )
> R is the number of documents marked relevant by a user. This is not
> implemented in SQL Server 2005 full-text search, and thus is ignored.
> r is the number of documents marked relevant by a user containing the
term.
> This is not implemented.
> N is the number of documents with values for the property in the query.
> n is the number of documents containing the term.
> K is ( k1 * ( ( 1 - b ) + ( b * dl / avdl ) ) )
> dl is the document length, in word occurrences.
> avdl is the average document length of the property over which the query
> spans, in word occurrences.
> k1, b, and k3 are the constants 1.2, 0.75, and 8.0, respectively.
> tf is the frequency of the term in a specific document.
> qtf is the frequency of the term in the query.
> So now I know :-)))) Anyway, apparently the ranking results have to be
taken
> with a *BIG" grain of salt.
> Alex.
>
|||Hello, John. Thank you for your reply.
Yes, I understand. Unfortunately, I'm using the pubs db, and I only have
about 50 titles in the 'Titles' table.
Here are the results of my query:
------
Rank/Title/Notes/First Name/Last Name
375/We don't drink that much/Essay on abstinence./Napoleon/Borcan
266/Que c'est bon, c'est bon, c'est bon !/Essai sur les joies de boire
beaucoup. Abstinents s'abstenir (eng: abstinence)./Pisica/Rindunel
121/No *abstinence* for me, please !/A smoker's paradise. Smoke-smoke-smoke,
boy, oh, isn't it a joy ?/Alberta/Curisor
121/ABSTINENCE for dummies./An epicurian's guide./Pupu/Balacarescu
------
and this is the query, which is correct:
------
strSearch = "SELECT " & _
"SearchTable.[Rank], Titles.title as Title, Titles.notes as
Notes, Authors.au_fname as [First Name], Authors.au_lname as [Last Name]" &
_
"FROM " & _
"FREETEXTTABLE(Titles, *, '" & strText & "') as SearchTable
" & _
"INNER JOIN Titles ON SearchTable.[Key] = Titles.title_id "
& _
"INNER JOIN TitleAuthor ON Titles.title_id =
TitleAuthor.title_id " & _
"INNER JOIN Authors ON TitleAuthor.au_id = Authors.au_id " &
_
"ORDER BY SearchTable.[Rank] DESC"
------
The version is:
Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation Developer Edition on Windows
NT 5.1 (Build 2600: Service Pack 2)
Alex.
"John Kane" <jt-kane@.comcast.net> wrote in message
news:Oof9C5DBFHA.3644@.TK2MSFTNGP15.phx.gbl...
> Hi Alex,
> Yes, I do, but it is complex and you need to understand a bit about basic
> Informational Retrieval theory. The Rank value from FREETEXTTABLE is based
> upon what is known as OKAPI or BM25 that was developed by Stephen
> Robertson
> (http://research.microsoft.com/users/robertson/). You can see some of the
> formula documented in "SQL Server 2005 Full-Text Search: Internals and
> Enhancements" at:
> http://msdn.microsoft.com/library/de...05ftsearch.asp
> Specifically, under "Ranking of FREETEXT" -
> "Freetext ranking is based on the OKAPI BM25 ranking formula. Each term in
> the query is ranked, and the values are summed. Freetext queries will add
> words to the query via inflectional generation (stemmed forms of the
> original query terms); these words are treated as separate terms with no
> special weighting or relationship with the words from which they were
> generated. Synonyms generated from the Thesaurus feature are treated as
> separate, equally weighted terms."...
> See also http://wickedsmrt.blogspot.com/2003_...t_archive.html
> "From MS Newsgroups: However it was my understanding that rank is based on
> this formula:
> ? W(i)=(K1+1) ?idf(i)?(K2+1) ?tf(i.j) /( K1?[(1-b)+b?dl
> (j)/avdl])?K3(tf(i.j))
> W(i) - rank from each term in the search phrase
> idf(i) - iS the inverse document frequency of term i
> tf(i,j) - is the term frequency for term i, in row j
> K1,K2,K3 - are constants
> dl - is row/column length in words
> AVdl - is the average row/column length length in words"
> Another factor is the number of rows and the number of unique non-noise
> words per row as you must have a statistically significant number of rows
> (at least 10,000) for the OKAPI BM25 Freetexttable Rank value to be
> meaningful. How many rows are in your table "titles"? Also keep in mind
> that
> the Rank values are specific to your freetext query and primarily useful
> for
> ordering of the results. See SQL Server 2000 BOL title "Full-text Search
> Recommendations" - "What is RANK and how is it determined when used with
> CONTAINSTABLE and FREETEXTTABLE predicates?..." for more info.
> Yes, OKAPI BM25 is very reliable, but complex. Note, that for US English,
> SQL FTS is case insensitive and "ABSTINENCE" will have the same rank value
> as "abstinence" with all other factors being equal. It is complex as you
> can
> see, but what is your true objective? Could you provide the exact Freetext
> query with sample data and results as well as the full output of SELECT
> @.@.version ?
> Hope that helps!
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "msnews.microsoft.com" <REMOVETHIScuca_macaii2000@.yahoo.com> wrote in
> message news:#YEbgXDBFHA.3824@.TK2MSFTNGP10.phx.gbl...
> by
> the
> rhyme
> 266
> in
> to
>
|||You're welcome, Alex,
Yes, I thought so. While the pubs and northwind database tables are good
examples for experimenting with SQL FTS queries, they are not large enough
to be used effectively with containstable or freetextable and RANK as you
need production level table sizes to get meaningful Ranking results from the
SQL FTS queries such as the one below.
FYI, I'd recommend that you apply the latest service pack to your SQL Server
2000 (8.00.194) Developer's Edition on WinXP SP2 as the build (194) of SQL
Server 2000 that you are using has no service packs applied and you may be
open security bugs with this RTM version.
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"msnews.microsoft.com" <REMOVETHIScuca_macaii2000@.yahoo.com> wrote in
message news:O2wwSFEBFHA.3472@.TK2MSFTNGP14.phx.gbl...
> Hello, John. Thank you for your reply.
> Yes, I understand. Unfortunately, I'm using the pubs db, and I only have
> about 50 titles in the 'Titles' table.
> Here are the results of my query:
> ----

> Rank/Title/Notes/First Name/Last Name
> 375/We don't drink that much/Essay on abstinence./Napoleon/Borcan
> 266/Que c'est bon, c'est bon, c'est bon !/Essai sur les joies de boire
> beaucoup. Abstinents s'abstenir (eng: abstinence)./Pisica/Rindunel
> 121/No *abstinence* for me, please !/A smoker's paradise.
Smoke-smoke-smoke,
> boy, oh, isn't it a joy ?/Alberta/Curisor
> 121/ABSTINENCE for dummies./An epicurian's guide./Pupu/Balacarescu
> ----

> and this is the query, which is correct:
> ----

> strSearch = "SELECT " & _
> "SearchTable.[Rank], Titles.title as Title, Titles.notes
as
> Notes, Authors.au_fname as [First Name], Authors.au_lname as [Last Name]"
&
> _
> "FROM " & _
> "FREETEXTTABLE(Titles, *, '" & strText & "') as
SearchTable
> " & _
> "INNER JOIN Titles ON SearchTable.[Key] = Titles.title_id
"
> & _
> "INNER JOIN TitleAuthor ON Titles.title_id =
> TitleAuthor.title_id " & _
> "INNER JOIN Authors ON TitleAuthor.au_id = Authors.au_id "
&
> _
> "ORDER BY SearchTable.[Rank] DESC"
> ----

> The version is:
> Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48
> Copyright (c) 1988-2000 Microsoft Corporation Developer Edition on
Windows[vbcol=seagreen]
> NT 5.1 (Build 2600: Service Pack 2)
> Alex.
>
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:Oof9C5DBFHA.3644@.TK2MSFTNGP15.phx.gbl...
basic[vbcol=seagreen]
based[vbcol=seagreen]
the[vbcol=seagreen]
http://msdn.microsoft.com/library/de...05ftsearch.asp[vbcol=seagreen]
in[vbcol=seagreen]
add[vbcol=seagreen]
http://wickedsmrt.blogspot.com/2003_...t_archive.html[vbcol=seagreen]
on[vbcol=seagreen]
rows[vbcol=seagreen]
English,[vbcol=seagreen]
value[vbcol=seagreen]
Freetext[vbcol=seagreen]
set[vbcol=seagreen]
266,[vbcol=seagreen]
word[vbcol=seagreen]
"afraid"
>

Freeing Space after Deleting Rows

I deleted some records that contain blob data from a table
but realized no effect to my physical disk space.
Shrinking the table and/or database returned no positive
impact. executed sp_spaceused on the table with no effect.
Should I truncate the table to release the space?
Truncate will delete all records and that's not what is
expected.
Please give me your suggestions.
Thanks...
Stokowhat do you mean when you say 'physical disk space'? Are you talking about
OS level space at the file system level, or space reported by SQL as being
consumed by the table. Can you list exactly what output you're looking at?
It wasn't 100% clear to me from your message...
--
Brian
"stoko" <anonymous@.discussions.microsoft.com> wrote in message
news:05cf01c3fadf$2111ede0$a401280a@.phx.gbl...
> I deleted some records that contain blob data from a table
> but realized no effect to my physical disk space.
> Shrinking the table and/or database returned no positive
> impact. executed sp_spaceused on the table with no effect.
> Should I truncate the table to release the space?
> Truncate will delete all records and that's not what is
> expected.
> Please give me your suggestions.
> Thanks...
> Stoko|||I am sorry for the lack of details.
I was expecting that after deleting a blob record holding
723556kb of file, this space should be released to the os
level of my server. hence if I had 2gigs of free space, I
should now have at least 2.7 gigs. This wasn't the case --
even at sql sever level, the space is still not released.
I appreciate your immediate response.
Stoko
>--Original Message--
>what do you mean when you say 'physical disk space'? Are
you talking about
>OS level space at the file system level, or space
reported by SQL as being
>consumed by the table. Can you list exactly what output
you're looking at?
>It wasn't 100% clear to me from your message...
>--
>Brian
>
>"stoko" <anonymous@.discussions.microsoft.com> wrote in
message
>news:05cf01c3fadf$2111ede0$a401280a@.phx.gbl...
>> I deleted some records that contain blob data from a
table
>> but realized no effect to my physical disk space.
>> Shrinking the table and/or database returned no positive
>> impact. executed sp_spaceused on the table with no
effect.
>> Should I truncate the table to release the space?
>> Truncate will delete all records and that's not what is
>> expected.
>> Please give me your suggestions.
>> Thanks...
>> Stoko
>
>.
>|||take a look at dbcc shrinkfile.
Files are not shrunk as space is remove from them. This is by design since
it would create a huge performance problem...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"stoko" <anonymous@.discussions.microsoft.com> wrote in message
news:079801c3fae2$f1c7ec70$a101280a@.phx.gbl...
> I am sorry for the lack of details.
> I was expecting that after deleting a blob record holding
> 723556kb of file, this space should be released to the os
> level of my server. hence if I had 2gigs of free space, I
> should now have at least 2.7 gigs. This wasn't the case --
> even at sql sever level, the space is still not released.
> I appreciate your immediate response.
> Stoko
> >--Original Message--
> >what do you mean when you say 'physical disk space'? Are
> you talking about
> >OS level space at the file system level, or space
> reported by SQL as being
> >consumed by the table. Can you list exactly what output
> you're looking at?
> >It wasn't 100% clear to me from your message...
> >
> >--
> >
> >Brian
> >
> >
> >"stoko" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:05cf01c3fadf$2111ede0$a401280a@.phx.gbl...
> >> I deleted some records that contain blob data from a
> table
> >> but realized no effect to my physical disk space.
> >> Shrinking the table and/or database returned no positive
> >> impact. executed sp_spaceused on the table with no
> effect.
> >>
> >> Should I truncate the table to release the space?
> >> Truncate will delete all records and that's not what is
> >> expected.
> >>
> >> Please give me your suggestions.
> >> Thanks...
> >> Stoko
> >
> >
> >.
> >

Freeing space after deleting records.

I deleted some records that contain blob data from a
table but realized no effect to my physical disk space.
Shrinking the table and/or database returned no positive
impact. executed sp_spaceused on the table with no
effect.
Should I truncate the table to release the space? Truncate
will delete all records and that's not what is
expected.
I was expecting that after deleting the blob record
holding 723556kb of file, this space should be released to
the os level of my server. hence if I had 2gigs of free
space, I should now have at least 2.7 gigs. This wasn't
the case -- even at sql sever level, the space is still
not released.
I appreciate your immediate response.
StokoCheck back in the archives, a few weeks. There was some discussion about the
same topic and if my memory serves me, to free space for blobs, you need to
re.load the table. I'm not sure about the details, so, I suggest you check
the archives to make sure.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"stoko" <anonymous@.discussions.microsoft.com> wrote in message
news:077501c3faf2$b2fdc130$a501280a@.phx.gbl...
> I deleted some records that contain blob data from a
> table but realized no effect to my physical disk space.
> Shrinking the table and/or database returned no positive
> impact. executed sp_spaceused on the table with no
> effect.
> Should I truncate the table to release the space? Truncate
> will delete all records and that's not what is
> expected.
> I was expecting that after deleting the blob record
> holding 723556kb of file, this space should be released to
> the os level of my server. hence if I had 2gigs of free
> space, I should now have at least 2.7 gigs. This wasn't
> the case -- even at sql sever level, the space is still
> not released.
> I appreciate your immediate response.
> Stoko
>|||Hi,
Execute the below commands
sp_spaceused table_name,@.updateusage ='TRUE'
go
Update statistics table_name
After this check the space utilized by the table and then use DBCC
SHRINKFILE to shrink the physical file.
Thanks
Hari
MCDBA
"stoko" <anonymous@.discussions.microsoft.com> wrote in message
news:077501c3faf2$b2fdc130$a501280a@.phx.gbl...
> I deleted some records that contain blob data from a
> table but realized no effect to my physical disk space.
> Shrinking the table and/or database returned no positive
> impact. executed sp_spaceused on the table with no
> effect.
> Should I truncate the table to release the space? Truncate
> will delete all records and that's not what is
> expected.
> I was expecting that after deleting the blob record
> holding 723556kb of file, this space should be released to
> the os level of my server. hence if I had 2gigs of free
> space, I should now have at least 2.7 gigs. This wasn't
> the case -- even at sql sever level, the space is still
> not released.
> I appreciate your immediate response.
> Stoko
>

Freeing space after deleting records.

I deleted some records that contain blob data from a
table but realized no effect to my physical disk space.
Shrinking the table and/or database returned no positive
impact. executed sp_spaceused on the table with no
effect.
Should I truncate the table to release the space? Truncate
will delete all records and that's not what is
expected.
I was expecting that after deleting the blob record
holding 723556kb of file, this space should be released to
the os level of my server. hence if I had 2gigs of free
space, I should now have at least 2.7 gigs. This wasn't
the case -- even at sql sever level, the space is still
not released.
I appreciate your immediate response.
StokoCheck back in the archives, a few weeks. There was some discussion about the
same topic and if my memory serves me, to free space for blobs, you need to
re.load the table. I'm not sure about the details, so, I suggest you check
the archives to make sure.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"stoko" <anonymous@.discussions.microsoft.com> wrote in message
news:077501c3faf2$b2fdc130$a501280a@.phx.gbl...
> I deleted some records that contain blob data from a
> table but realized no effect to my physical disk space.
> Shrinking the table and/or database returned no positive
> impact. executed sp_spaceused on the table with no
> effect.
> Should I truncate the table to release the space? Truncate
> will delete all records and that's not what is
> expected.
> I was expecting that after deleting the blob record
> holding 723556kb of file, this space should be released to
> the os level of my server. hence if I had 2gigs of free
> space, I should now have at least 2.7 gigs. This wasn't
> the case -- even at sql sever level, the space is still
> not released.
> I appreciate your immediate response.
> Stoko
>|||Hi,
Execute the below commands
sp_spaceused table_name,@.updateusage ='TRUE'
go
Update statistics table_name
After this check the space utilized by the table and then use DBCC
SHRINKFILE to shrink the physical file.
Thanks
Hari
MCDBA
"stoko" <anonymous@.discussions.microsoft.com> wrote in message
news:077501c3faf2$b2fdc130$a501280a@.phx.gbl...
> I deleted some records that contain blob data from a
> table but realized no effect to my physical disk space.
> Shrinking the table and/or database returned no positive
> impact. executed sp_spaceused on the table with no
> effect.
> Should I truncate the table to release the space? Truncate
> will delete all records and that's not what is
> expected.
> I was expecting that after deleting the blob record
> holding 723556kb of file, this space should be released to
> the os level of my server. hence if I had 2gigs of free
> space, I should now have at least 2.7 gigs. This wasn't
> the case -- even at sql sever level, the space is still
> not released.
> I appreciate your immediate response.
> Stoko
>

free transaction log reader

There have been deleted several records from a table and i want to know who did it. Is there a free transactionlog reader to find out?
MarcelOriginally posted by swissis
There have been deleted several records from a table and i want to know who did it. Is there a free transactionlog reader to find out?

Marcel

try log explorer by Lumigent:
http://www.lumigent.com/products/le_sql/le_sql.htm|||I did download that one but it isn't free and the trial version only works on the pubs and northwind database.
Is there not another way to read or query the transactionlog?

free tools for creating sql script with records?

is there some freeware tools for that?
thanks!
On Thu, 9 Dec 2004 11:13:35 +0100, green_eye wrote:

>is there some freeware tools for that?
Hi green_eye,
http://vyaskn.tripod.com/code.htm#inserts
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Innovartis do a free data and schema scripter
http://www.innovartis.co.uk/Evaluation.aspx
"green_eye" wrote:

> is there some freeware tools for that?
> thanks!
>
>

free tools for creating sql script with records?

is there some freeware tools for that'
thanks!On Thu, 9 Dec 2004 11:13:35 +0100, green_eye wrote:
>is there some freeware tools for that'
Hi green_eye,
http://vyaskn.tripod.com/code.htm#inserts
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Innovartis do a free data and schema scripter
http://www.innovartis.co.uk/Evaluation.aspx
"green_eye" wrote:
> is there some freeware tools for that'
> thanks!
>
>

Free text search for 2 million records

Hi

I have a new client with an existing system that has just over 2 million business listings in one table. Each business listing is associated with one business category.

* Company Table (around 20 fields):

companyID
companyName
categoryID
state
postCode
etc.

* Category Table (5 fields)

categoryID
categoryName
etc.

We are using MSSQL 2005 Express Edition with Advanced Services

A free text search needs to be performed on the companyName and categoryName limited by region (state and or postcode).

1) What kind of response times should I expect for the free text search (I have not used the free text search before)

2) How should I index the companyName and categoryName so they are both used in a joined query? i.e. Do I just configure the free text search index on each field separately and it should work?

Any suggestions appreciated.

Best Regards

Kevan1) Impossible to tell. It highly depends on the memory, disk solution, allocated memory to SQL Server, processor, operating system configurations and more.

2) Indexing the keys used in a join (that is: used in the on clause of the join) may improve performance.|||Thanks for that.

1)

Yes, that makes sense. I was really after a very general idea of other peoples experience when dealing with free text search on a couple of million records. What sort of average response times have other people achieved with simple free text searches on this number of records. We would most likely be using a low level hardware solution on a hosted server.

2)

Thanks

Free Space available for use

If I have 4 GB database with 1 GB of free space (just
completed a DELETE of records). Will the space be
available for use by INSERTS? Previously, the db was
automatically growing and I hope that stops with the mass
delete that was done. Space is running low on the drive.
Does shrinking provide a benefit?Hi Micheal,
1 GB you cleared will be used for the new data addition (Inserts).
If you run the SHRINK command it shrinks your data files (Physical files)
and since you have mentioned the auto growth option the file will grow
depends up on the amount of inserts.
Thanks
Hari
MCDBA
"Michael Orechoff" <anonymous@.discussions.microsoft.com> wrote in message
news:05e601c39da8$b18a6710$a301280a@.phx.gbl...
> If I have 4 GB database with 1 GB of free space (just
> completed a DELETE of records). Will the space be
> available for use by INSERTS? Previously, the db was
> automatically growing and I hope that stops with the mass
> delete that was done. Space is running low on the drive.
> Does shrinking provide a benefit?|||Are you saying that the db was full, and you deleted a bunch or rows so you now have 1GB free space
in db? If so,
> Will the space be
> available for use by INSERTS?
Yes.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Michael Orechoff" <anonymous@.discussions.microsoft.com> wrote in message
news:05e601c39da8$b18a6710$a301280a@.phx.gbl...
> If I have 4 GB database with 1 GB of free space (just
> completed a DELETE of records). Will the space be
> available for use by INSERTS? Previously, the db was
> automatically growing and I hope that stops with the mass
> delete that was done. Space is running low on the drive.
> Does shrinking provide a benefit?

Free GUI For SQL Server

Is there a Free GUI for SQL server that can control or edit any and all the records in it, and the tables?You don't have Enterprise manager?

Try a Microsoft Access .ADP project. Access isn't "Free", but you may already have it.|||AdoAnywhere Browser will allow you to query your table and view database structure. http://www.adoanywhere.com

I'm putting a list together here of other tools.

http://81.130.213.94/myforum/forum_posts.asp?TID=83&PN=1&TPN=1

You can curently evaluate a "Query Analizer" tool too if you go in the thread

http://81.130.213.94/myforum/forum_posts.asp?TID=78&PN=1

Mike