Dear all,
I've build a FullText Indexing at Enterprise Manager. However, when I run
the following query, it does not have any result return:
select u_documenttype, u_document, u_filename from doclib where
freetext(u_document, ' "category" or "eis" ')
Does anyone have any idea about my situation?
Thanks
Best Regards,
Eilsa
One more thing, the "u_document" is an image type
thanks
"Eilsa" <asd@.asd.asd> wrote in message
news:OYvWvh1eEHA.2352@.TK2MSFTNGP09.phx.gbl...
> Dear all,
> I've build a FullText Indexing at Enterprise Manager. However, when I run
> the following query, it does not have any result return:
> select u_documenttype, u_document, u_filename from doclib where
> freetext(u_document, ' "category" or "eis" ')
> Does anyone have any idea about my situation?
>
> Thanks
> Best Regards,
> Eilsa
>
|||Eilsa,
Yes, I do... Could you post the full output of the following SQL code?
use <your_database_name_here>
go
SELECT @.@.language
SELECT @.@.version
EXEC sp_help doclib
go
Additionally, what is the language of the text in the documents (MS Word
files) that you are storing in column "u_document" and how did you import or
upload these files to SQL Server? Both questions as well as the datatype and
size of the file extension column in your table doclib will provide clues to
answer your question.
Regards,
John
"Eilsa" <asd@.asd.asd> wrote in message
news:OTAVJx1eEHA.2352@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> One more thing, the "u_document" is an image type
> thanks
> "Eilsa" <asd@.asd.asd> wrote in message
> news:OYvWvh1eEHA.2352@.TK2MSFTNGP09.phx.gbl...
run
>
|||us_english
(1 row(s) affected)
Microsoft SQL Server 2000-8.00.194(IntelX86)
Aug620000:57:48
Copyright(c)1988-2000 Microsoft Corporation
DeveloperEditionon Windows NT5 (Build2195:00:00 Service Pack 4)
(1 row(s) affected)
NameOwnerTypeCreated_datetime
DocFiledbouser table8/9/2004 06:23.5
Column_nameTypeComputedLengthPrecScaleNullableTrimTrailingBlanksFixedLenNullInSourceCollation
------
File_IDintno4100no(n/a)(n/a)NULL
Fileimageno16yes(n/a)(n/a)NULL
FileTypecharno3yesnoyesSQL_Latin1_General_CP1_CI_AS
IdentitySeedIncrementNot ForReplication
No identity column defined.NULLNULLNULL
RowGuidCol
No rowguidcol column defined.
Data_located_on_filegroup
PRIMARY
index_nameindex_descriptionindex_keys
-----
PK_File clustered, unique,primary key located on PRIMARYFile_ID
constraint_typeconstraint_namedelete_actionupdate_actionstatus_enabledstatus_for_replicationconstraint_keys
------
PRIMARYKEY(clustered)PK_File(n/a)(n/a)(n/a)(n/a)File_ID
No foreign keysreference this table.
Table is referenced by views
"John Kane" <jt-kane@.comcast.net> wrote in message news:<OcV90A2eEHA.3016@.tk2msftngp13.phx.gbl>...[vbcol=seagreen]
> Eilsa,
> Yes, I do... Could you post the full output of the following SQL code?
> use <your_database_name_here>
> go
> SELECT @.@.language
> SELECT @.@.version
> EXEC sp_help doclib
> go
> Additionally, what is the language of the text in the documents (MS Word
> files) that you are storing in column "u_document" and how did you import or
> upload these files to SQL Server? Both questions as well as the datatype and
> size of the file extension column in your table doclib will provide clues to
> answer your question.
> Regards,
> John
>
> "Eilsa" <asd@.asd.asd> wrote in message
> news:OTAVJx1eEHA.2352@.TK2MSFTNGP09.phx.gbl...
> run
|||Thanks, Bob,
You're using SQL Server 2000 (RTM) Developer Edition on Windows 2000. You're
table DocFile and your file extension column is FileType char(3) and
Nullable. However, I still need additional info, specifically what is the
language of the text in the documents (MS Word files) that you are storing
in column "u_document" and how did you import or upload these files to SQL
Server?
I've attached a SQL script file (Import_FTS_Images.sql) that uses TextCopy
to import documents as well as html pages into a test table FTSTable that
also has a file extension column (ExtCol) defined as char(3) and Nullable
and this configuration as worked for years with US_English documents. Test
your document upload into this configuration and then run a Full Population
and test FTS queries. If you still have a problem getting the expected
results, please post the exact SQL query as well as the exact sample data
contained in the document/html pages.
Thanks,
John
"Bob" <kcsc1@.hotpop.com> wrote in message
news:ef3b388e.0408100822.603f67f2@.posting.google.c om...
> us_english
> (1 row(s) affected)
> Microsoft SQL Server 2000-8.00.194 (Intel X86)
> Aug 6 2000 0:57:48
> Copyright (c) 1988-2000 Microsoft Corporation
> Developer Edition on Windows NT5 (Build 2195:00:00 Service Pack 4)
> (1 row(s) affected)
> Name Owner Type Created_datetime
> DocFile dbo user table 8/9/2004 06:23.5
>
> Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks
FixedLenNullInSource Collation
> ----
> File_ID int no 4 10 0 no (n/a) (n/a) NULL
> File image no 16 yes (n/a) (n/a) NULL
> FileType char no 3 yes no yes SQL_Latin1_General_CP1_CI_AS
>
> Identity Seed Increment Not For Replication
> ----
--
> No identity column defined. NULL NULL NULL
>
> RowGuidCol
> --
> No rowguidcol column defined.
>
> Data_located_on_filegroup
> --
> PRIMARY
>
> index_name index_description index_keys
> ----
--
> PK_File clustered, unique,primary key located on PRIMARY File_ID
>
> constraint_type constraint_name delete_action update_action status_enabled
status_for_replication constraint_keys
> ----
> PRIMARY KEY (clustered) PK_File (n/a) (n/a) (n/a) (n/a) File_ID
>
> No foreign keys reference this table.
> Table is referenced by views
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
news:<OcV90A2eEHA.3016@.tk2msftngp13.phx.gbl>...[vbcol=seagreen]
import or[vbcol=seagreen]
and[vbcol=seagreen]
clues to[vbcol=seagreen]
I[vbcol=seagreen]
begin 666 Import_FTS_Images.sql
M#0IU<V4@.<'5B<PT*9V\-"FEF(&5X:7-T<R H<V5L96-T("H@.9G)O;2!S>7-O
M8FIE8W1S('=H97)E(&ED(#T@.;V)J96-T7VED*"=&5%-486)L92<I*0T*("!D
M<F]P('1A8FQE($944U1A8FQE#0IG;PT*#0I#4D5!5$4@.5$%"3$4@.1 E135&%B
M;&4@.* T*("!+97E#;VP):6YT($E$14Y42519("@.Q+#$I($Y/5"!.54Q,( T*
M(" @.($-/3E-44D%)3E0@.1E135&%B;&5?2418(%!224U!4ED@.2T59($-,55-4
M15)%1"P-"B @.5&5X=$-O; ET97AT($Y53$PL#0H@.($EM86=E0V]L"6EM86=E
M($Y53$PL#0H@.($5X=$-O; EC:&%R*#,I($Y53$PL("TM(&-A;B!B92!E:71H
M97(@.<WES;F%M92!O<B!C:&%R*#,I#0H@.(%1I;653=&%M<$-O; ET:6UE<W1A
M;7 @.3E5,3" -"BD@.3TX@.6U!224U!4EE=#0IG;PT*#0H-"BTM($EN<V5R="!D
M871A+BXN("A.;W1E.B!);FET86QI>FEN9R!)34%'12!C;VQU; 6X@.=VET:" P
M>$9&1D9&1D9&(&9O<B!U<V4@.=VET:"!497AT0V]P>2YE>&4I#0I)3E-%4E0@.
M1E135&%B;&4@.=F%L=65S*"=497-T(%1%6%0@.1&%T82!F;W(@.<F]W(#$G+" P
M>$9&1D9&1D9&+" G9&]C)RP@.3E5,3"D-"DE.4T525"!&5%-486)L92!V86QU
M97,H)U1E<W0@.5$585"!$871A(&9O<B!R;W<@.,B<L(#!X1D9&1 D9&1D8L("=X
M;',G+"!.54Q,*0T*24Y315)4($944U1A8FQE('9A;'5E<R@.G5 &5S="!415A4
M($1A=&$@.9F]R(')O=R S)RP@.,'A&1D9&1D9&1BP@.)W!P="<L($Y53$PI#0I)
M3E-%4E0@.1E135&%B;&4@.=F%L=65S*"=497-T(%1%6%0@.1&%T82!F;W(@.<F]W
M(#0G+" P>$9&1D9&1D9&+" G='AT)RP@.3E5,3"D-"DE.4T525"!&5%-486)L
M92!V86QU97,H)U1E<W0@.5$585"!$871A(&9O<B!R;W<@.-2<L(#!X1D9&1D9&
M1D8L("=H=&TG+"!.54Q,*0T*24Y315)4($944U1A8FQE('9A; '5E<R@.G5&5S
M="!415A4($1A=&$@.9F]R(')O=R V)RP@.,'A&1D9&1D9&1BP@.)W!D9B<L($Y5
M3$PI#0I)3E-%4E0@.1E135&%B;&4@.=F%L=65S*"=497-T(%1%6%0@.1&%T82!F
M;W(@.<F]W(#<G+" P>$9&1D9&1D9&+" G>&UL)RP@.3E5,3"D-"F=O#0H-"@.T*
M+2T@.4V5L96-T(&1A=&$-"E-%3$5#5" J(&9R;VT@.1E135&%B;&4-"F=O#0H-
M"@.T*9&5C;&%R92! <75E<GD@.=F%R8VAA<B@.R,# I#0HM+2!);G-E<G0@.2%1-
M3%]F:6QE+FAT;2!I;G1O(%)O=R U("$A#0HM+2!.3U1%.B!%;G-U<F4@.=&AE
M(&-O<G)E8W0@.<&%T:"!F;W(@.=&5X=&-O<'DN97AE(2$-"G-E="! <75E<GD@.
M/2 G1#I<35-344PX,%Q-4U-13"1344PX,%Q":6YN7'1E>'1C;W!Y("]S("<K
M0$!S97)V97)N86UE*R<@.+W4@.<V$@.+W @.+V0@.<'5B<R O="!&5%-486)L92 O
M8R!);6%G94-O;" O9B!$.EQ344Q&:6QE<UQ3:&EL;VA</$A434Q?9FEL93XN
M:'1M("]I("]K(#4P,# @.+W<@.(G=H97)E($ME>4-O;#TU(B<-"G!R:6YT($!Q
M=65R>0T*97AE8R!M87-T97(N+GAP7V-M9'-H96QL($!Q=65R>0T*9V\-"@.T*
M9&5C;&%R92! <75E<GD@.=F%R8VAA<B@.R,# I#0HM+2!);G-E<G0@.35-?5V]R
M9"YD;V,@.:6YT;R!2;W<@.,2 A(0T*<V5T($!Q=65R>2 ]("=$.EQ-4U-13#@.P
M7$U34U%,)%-13#@.P7$)I;FY<=&5X=&-O<'D@.+W,@.)RM 0'-E<G9E<FYA;64K
M)R O=2!S82 O<" O9"!P=6)S("]T($944U1A8FQE("]C($EM86=E0V]L("]F
M($0Z7$I+86YE7$1O8W-</$U37U=O<F0^+F1O8R O:2 O:R U,# P("]W(")W
M:&5R92!+97E#;VP],2(G#0IP<FEN="! <75E<GD-"F5X96,@.;6%S=&5R+BYX
M<%]C;61S:&5L;"! <75E<GD-"F=O#0H-"BTM(%-E;&5C="!D871A#0I314Q%
M0U0@.*B!F<F]M($944U1A8FQE#0IG;PT*#0H-"BTM($9422 -"G5S92!P=6)S
M#0IG;PT*97AE8R!S<%]F=6QL=&5X=%]D871A8F%S92 G96YA8FQE)R -"F=O
M#0IE>&5C('-P7V9U;&QT97AT7W-E<G9I8V4@.)V-L96%N7W5P)PT*#0HM+2!$
M<F]P($9422P@.:68@.;F5C97-S87)Y+BXN#0IE>&5C('-P7V9U;&QT97AT7W1A
M8FQE("=&5%-486)L92<L)V1R;W G#0IE>&5C('-P7V9U;&QT97AT7T-A=&%L
M;V<@.)T944T-A=&%L;V<G+"=D<F]P)PT*#0H-"F5X96,@.<W!?9G5L;'1E>'1?
M8V%T86QO9R G1E130V%T86QO9R<L)V-R96%T92<-"F5X96,@.<W!?9G5L;'1E
M>'1?=&%B;&4@.)T944U1A8FQE)RPG8W)E871E)RPG1E130V%T8 6QO9R<L)T94
M4U1A8FQE7TE$6"<-"F5X96,@.<W!?9G5L;'1E>'1?8V]L=6UN("=&5%-486)L
M92<L)TEM86=E0V]L)RPG861D)RP@.,'@.P-# Y+" G17AT0V]L)R -"F5X96,@.
M<W!?9G5L;'1E>'1?8V]L=6UN("=&5%-486)L92<L)U1E>'1#;VPG+"=A9&0G
M( T*97AE8R!S<%]F=6QL=&5X=%]T86)L92 G1E135&%B;&4G+" G86-T:79A
M=&4G(" -"F=O#0H-"BTM(%-T87)T($94($EN9&5X:6YG+BXN#0IE>&5C('-P
M7V9U;&QT97AT7V-A=&%L;V<@.)T944T-A=&%L;V<G+"=S=&%R=%]F=6QL)PT*
M9V\-"BTM(%=A:70@.9F]R($94($EN9&5X:6YG('1O(&-O;7!L971E(&%N9"!C
M:&5C:R!.5"]7:6XR2R!!<'!L:6-A=&EO;B!L;V<@.9F]R('-U8V-E<W,O97)R
M;W)S+BX-"@.T*#0IS96QE8W0@.*B!F<F]M($944U1A8FQE#0IG;PT*+2T@.4V5A
M<F-H(&9O<B!S96%R8VA?=V]R9%]H97)E(&EN($A434P@.9FEL92XN#0IS96QE
M8W0@.2V5Y0V]L+"!);6%G94-O;" @.9G)O;2!&5%-486)L92!W:&5R92!C;VYT
M86EN<R@.J+"<\<V5A<F-H7W=O<F1?:&5R93XG*2!O<F1E<B!B>2!+97E#;VP@.
M#0IG;PT*+2T@.4V5A<F-H(&9O<B!S96%R8VA?=V]R9%]H97)E(&EN("Y$3T,@.
M9FEL92XN+@.T*<V5L96-T($ME>4-O;"P@.26UA9V5#;VP@.9G)O;2!&5%-486)L
M92!W:&5R92!C;VYT86EN<R@.J+"<\<V5A<F-H7W=O<F1?:&5R93XG*2!O<F1E
M<B!B>2!+97E#;VP@.#0IG;PT*#0H-"BTM($-O;F9I<FT@.1E0@.4')O<&5R=&EE
M<RXN+@.T*=7-E('!U8G,-"F=O#0IS<%]H96QP7V9U;&QT97AT7V-A=&%L;V=S
M("=&5%-#871A;&]G)R -"F=O#0IS<%]H96QP7V9U;&QT97AT7W1A8FQE<R G
M1E130V%T86QO9R<@.( T*9V\-"G-P7VAE;'!?9G5L;'1E>'1?8V]L=6UN<R G
M1E135&%B;&4G#0IG;PT*4T5,14-4(&9U;&QT97AT8V%T86QO9W!R;W!E<G1Y
M*"=&5%-#871A;&]G)RP@.)U!O<'5L871E4W1A='5S)RD@.#0IG;PT*#0HM+2!2
M96UO=F4@.1E0@.26YD97AE<R F($-A=&%L;V<@.)B!T86)L92XN#0H-"F5X96,@.
M<W!?9G5L;'1E>'1?=&%B;&4@.)T944U1A8FQE)RPG9')O<"<-"@.T*97AE8R!S
M<%]F=6QL=&5X=%]#871A;&]G("=&5%-#871A;&]G)RPG9')O<"<-"@.T*9')O
4<"!T86)L92!&5%-486)L90T*#0H`
`
end
2012年3月27日星期二
full text search weighting on different columns
Hi,
I've not used FT much, but I've successfully created a CONTAINSTABLE query
on a database that contains names and addresses, using a wildcard for the
field names.
Can anyone please point me in the right direction for info on how to
"weight" one field over another? For example - if someone searches for
"John", the system needs to rank "John Smith" in the "name" column over "10
St John St" in the "address1" column.
Thanks in advance,
Dunc
Duncan,
You must join two or more CONTAINSTABLE from the same table or multiple
tables and then use the appropriate weight for each predicate, for example
using the Northwind table Employees and two FT-enabled columns:
SELECT e.LastName, e.FirstName, e.Title, e.Notes
from Employees AS e,
containstable(Employees, Notes, 'ISABOUT (BA weight (.2) )', 10) as A,
containstable(Employees, Title, 'ISABOUT (Sales weight (.5) )', 15) as
B
where
A.[KEY] = e.EmployeeID and
B.[KEY] = e.EmployeeID
Regards,
John
"Duncan Welch" <dunc@.ntpcl.f9.co.uk> wrote in message
news:OqE2ZoWWEHA.2636@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I've not used FT much, but I've successfully created a CONTAINSTABLE query
> on a database that contains names and addresses, using a wildcard for the
> field names.
> Can anyone please point me in the right direction for info on how to
> "weight" one field over another? For example - if someone searches for
> "John", the system needs to rank "John Smith" in the "name" column over
"10
> St John St" in the "address1" column.
> Thanks in advance,
> Dunc
>
I've not used FT much, but I've successfully created a CONTAINSTABLE query
on a database that contains names and addresses, using a wildcard for the
field names.
Can anyone please point me in the right direction for info on how to
"weight" one field over another? For example - if someone searches for
"John", the system needs to rank "John Smith" in the "name" column over "10
St John St" in the "address1" column.
Thanks in advance,
Dunc
Duncan,
You must join two or more CONTAINSTABLE from the same table or multiple
tables and then use the appropriate weight for each predicate, for example
using the Northwind table Employees and two FT-enabled columns:
SELECT e.LastName, e.FirstName, e.Title, e.Notes
from Employees AS e,
containstable(Employees, Notes, 'ISABOUT (BA weight (.2) )', 10) as A,
containstable(Employees, Title, 'ISABOUT (Sales weight (.5) )', 15) as
B
where
A.[KEY] = e.EmployeeID and
B.[KEY] = e.EmployeeID
Regards,
John
"Duncan Welch" <dunc@.ntpcl.f9.co.uk> wrote in message
news:OqE2ZoWWEHA.2636@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I've not used FT much, but I've successfully created a CONTAINSTABLE query
> on a database that contains names and addresses, using a wildcard for the
> field names.
> Can anyone please point me in the right direction for info on how to
> "weight" one field over another? For example - if someone searches for
> "John", the system needs to rank "John Smith" in the "name" column over
"10
> St John St" in the "address1" column.
> Thanks in advance,
> Dunc
>
2012年3月26日星期一
full text search on multiple tables yielding one rank
Is it possible to do full text search on multiple tables and having one rank
for all searches? Ive read a lot of articles that gives me code samples on
doing FTS on multi tables but they gave me multiple ranks for each search.
Without consolidating all of your data in the multiple tables into a child
table there is no way to do this with a meaningful rank.
Hilary Cotter
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
"surigaonon" <surigaonon@.discussions.microsoft.com> wrote in message
news:28625427-A482-40B3-991B-BC7C35501801@.microsoft.com...
> Is it possible to do full text search on multiple tables and having one
> rank
> for all searches? Ive read a lot of articles that gives me code samples on
> doing FTS on multi tables but they gave me multiple ranks for each search.
sql
for all searches? Ive read a lot of articles that gives me code samples on
doing FTS on multi tables but they gave me multiple ranks for each search.
Without consolidating all of your data in the multiple tables into a child
table there is no way to do this with a meaningful rank.
Hilary Cotter
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
"surigaonon" <surigaonon@.discussions.microsoft.com> wrote in message
news:28625427-A482-40B3-991B-BC7C35501801@.microsoft.com...
> Is it possible to do full text search on multiple tables and having one
> rank
> for all searches? Ive read a lot of articles that gives me code samples on
> doing FTS on multi tables but they gave me multiple ranks for each search.
sql
2012年3月21日星期三
Full Text Search - a few questions from a semi-newbie
I've been learning a lot about full text searching over the last few
days while working on a large scale project (well, large for me atleast
- currently almost 4 million records with near-future expansion to
about 10 million).
I still have a few unanswered questions though. First, let me give my
environment info: Single CPU Server (1Ghz), 1 GB RAM (expanding to 2GB
tomorrow), Windows 2000 server, SQL 2000 Standard Edition. Database is
on mirrored SCSI drive, Full Text Data is on new 80GB IDE drive
(couldn't fit another SCSI). The site is a merchant affiliate, which
pulls in data from Linkshare merchants and then allows for searching
globally, by category, and by merchant.
During population (full or incremental) searching on the index with
CONTAINS queries takes forever. I would expect the performance to be
affected during population, but is there anything I can do to avoid
this headache?
Here's my current situation with this: I had about 2.2 million records
in my table yesterday, and my index finally finished populating - the
index is on 3 varchar fields, one is 255 length, one is 500 and one is
2000. My queries were coming back super fast at this point. I then
turned on "Change Tracking". Not sure if this was a "bad" time to do
this. Possibly should have been done up front, but nothing I've seen
online indicates this can't be done at any point. Thinking that the
index would then update smoothly as I imported data, I proceeded to
import another 1.5 million records. (FYI, new data imports would
optimally be nightly and size would range from 2,000 to 2,000,000
records)
It is now 18 hours later and my catalog says "incremental population in
progress" and is only at about 2.23 million records. It is moving
incredibly slow. I know that it moved faster yesterday when load was
reduced later at night, so I am hoping that it finishes before the
morning.
So basically here's my questions:
1. Will my future updates index quickly with change tracking?
2. How does change tracking work? Does it re-index ANY row called
with ANY UPDATE/INSERT/DELETE statement? My import updates all
existing records. It doesn't check for changes, just assumes it needs
the latest data and updates the record. I can adjust this if
necessary. (any suggestions would be great too)
3. Is there any way to do an alternate search, or does anyone else do
something to avoid searching the indexed data while it's being
populated so it doesn't take 3 minutes to search on my website?
4. I've heard that change tracking is better than scheduled
incremental population. Would I be better off in my scenario with the
scheduled population? I would need a timestamp column right? And
would have to populate that field for all existing rows, right?
5. Any additional insight would be great. I've gotten some great
info from this forum and others, but sometimes you just need to "hear
it" yourself to get it, ya know?
Thanks in advance,
Joe Potenza
bigjoepo@.gmail.com
You queries may be slow due to locking which occurs during the population
process. Try to use the nolock hint on your queries. Use change tracking to
offer faster indexing times.
Starting change tracking can kick off another incremental or full population
depending on whether a timestamp column exists on this table..
To answer your questions
1) if you are doing singleton updates, change tracking should offer best
performance. So your future DML should progress much faster. If a large
portion of your table changes, i.e. 90% you will probably find that a full
population works best.
2) Change tracking will only resubmit for indexing rows which have the
column full text indexed modified.
3) After a full population is done, the old index should be used. I am
wondering if perhaps your performance problem isn't related to the number of
rows you are returning. You might want to limit this to 100 rows and order
them by rand desc using the ContainsTable or FreeTextTable predicates.
4) To reduce locking you might be better of scheduling your change tracking
updates as opposed to using change tracking with update index in background.
This is almost always more efficient than incremental populations. A
timestamp column should be on your table to perform an incremental
population, otherwise a full population will be done. If you have an full
population completed and you enable change tracking and you have a timestamp
column, IIRC no incremental population will be required. If no timestamp
column exists or if a full population has not completed, a full population
will be run.
5) have you checked out the following kb articles?
http://support.microsoft.com/?scid=h...te+paper.as p
or
http://msdn.microsoft.com/library/de...tml/sp04f9.asp
Hilary Cotter
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
"bigjoepo" <bigjoepo@.gmail.com> wrote in message
news:1126822878.985027.268380@.z14g2000cwz.googlegr oups.com...
> I've been learning a lot about full text searching over the last few
> days while working on a large scale project (well, large for me atleast
> - currently almost 4 million records with near-future expansion to
> about 10 million).
> I still have a few unanswered questions though. First, let me give my
> environment info: Single CPU Server (1Ghz), 1 GB RAM (expanding to 2GB
> tomorrow), Windows 2000 server, SQL 2000 Standard Edition. Database is
> on mirrored SCSI drive, Full Text Data is on new 80GB IDE drive
> (couldn't fit another SCSI). The site is a merchant affiliate, which
> pulls in data from Linkshare merchants and then allows for searching
> globally, by category, and by merchant.
> During population (full or incremental) searching on the index with
> CONTAINS queries takes forever. I would expect the performance to be
> affected during population, but is there anything I can do to avoid
> this headache?
> Here's my current situation with this: I had about 2.2 million records
> in my table yesterday, and my index finally finished populating - the
> index is on 3 varchar fields, one is 255 length, one is 500 and one is
> 2000. My queries were coming back super fast at this point. I then
> turned on "Change Tracking". Not sure if this was a "bad" time to do
> this. Possibly should have been done up front, but nothing I've seen
> online indicates this can't be done at any point. Thinking that the
> index would then update smoothly as I imported data, I proceeded to
> import another 1.5 million records. (FYI, new data imports would
> optimally be nightly and size would range from 2,000 to 2,000,000
> records)
> It is now 18 hours later and my catalog says "incremental population in
> progress" and is only at about 2.23 million records. It is moving
> incredibly slow. I know that it moved faster yesterday when load was
> reduced later at night, so I am hoping that it finishes before the
> morning.
> So basically here's my questions:
> 1. Will my future updates index quickly with change tracking?
> 2. How does change tracking work? Does it re-index ANY row called
> with ANY UPDATE/INSERT/DELETE statement? My import updates all
> existing records. It doesn't check for changes, just assumes it needs
> the latest data and updates the record. I can adjust this if
> necessary. (any suggestions would be great too)
> 3. Is there any way to do an alternate search, or does anyone else do
> something to avoid searching the indexed data while it's being
> populated so it doesn't take 3 minutes to search on my website?
> 4. I've heard that change tracking is better than scheduled
> incremental population. Would I be better off in my scenario with the
> scheduled population? I would need a timestamp column right? And
> would have to populate that field for all existing rows, right?
> 5. Any additional insight would be great. I've gotten some great
> info from this forum and others, but sometimes you just need to "hear
> it" yourself to get it, ya know?
>
> Thanks in advance,
> Joe Potenza
> bigjoepo@.gmail.com
>
days while working on a large scale project (well, large for me atleast
- currently almost 4 million records with near-future expansion to
about 10 million).
I still have a few unanswered questions though. First, let me give my
environment info: Single CPU Server (1Ghz), 1 GB RAM (expanding to 2GB
tomorrow), Windows 2000 server, SQL 2000 Standard Edition. Database is
on mirrored SCSI drive, Full Text Data is on new 80GB IDE drive
(couldn't fit another SCSI). The site is a merchant affiliate, which
pulls in data from Linkshare merchants and then allows for searching
globally, by category, and by merchant.
During population (full or incremental) searching on the index with
CONTAINS queries takes forever. I would expect the performance to be
affected during population, but is there anything I can do to avoid
this headache?
Here's my current situation with this: I had about 2.2 million records
in my table yesterday, and my index finally finished populating - the
index is on 3 varchar fields, one is 255 length, one is 500 and one is
2000. My queries were coming back super fast at this point. I then
turned on "Change Tracking". Not sure if this was a "bad" time to do
this. Possibly should have been done up front, but nothing I've seen
online indicates this can't be done at any point. Thinking that the
index would then update smoothly as I imported data, I proceeded to
import another 1.5 million records. (FYI, new data imports would
optimally be nightly and size would range from 2,000 to 2,000,000
records)
It is now 18 hours later and my catalog says "incremental population in
progress" and is only at about 2.23 million records. It is moving
incredibly slow. I know that it moved faster yesterday when load was
reduced later at night, so I am hoping that it finishes before the
morning.
So basically here's my questions:
1. Will my future updates index quickly with change tracking?
2. How does change tracking work? Does it re-index ANY row called
with ANY UPDATE/INSERT/DELETE statement? My import updates all
existing records. It doesn't check for changes, just assumes it needs
the latest data and updates the record. I can adjust this if
necessary. (any suggestions would be great too)
3. Is there any way to do an alternate search, or does anyone else do
something to avoid searching the indexed data while it's being
populated so it doesn't take 3 minutes to search on my website?
4. I've heard that change tracking is better than scheduled
incremental population. Would I be better off in my scenario with the
scheduled population? I would need a timestamp column right? And
would have to populate that field for all existing rows, right?
5. Any additional insight would be great. I've gotten some great
info from this forum and others, but sometimes you just need to "hear
it" yourself to get it, ya know?
Thanks in advance,
Joe Potenza
bigjoepo@.gmail.com
You queries may be slow due to locking which occurs during the population
process. Try to use the nolock hint on your queries. Use change tracking to
offer faster indexing times.
Starting change tracking can kick off another incremental or full population
depending on whether a timestamp column exists on this table..
To answer your questions
1) if you are doing singleton updates, change tracking should offer best
performance. So your future DML should progress much faster. If a large
portion of your table changes, i.e. 90% you will probably find that a full
population works best.
2) Change tracking will only resubmit for indexing rows which have the
column full text indexed modified.
3) After a full population is done, the old index should be used. I am
wondering if perhaps your performance problem isn't related to the number of
rows you are returning. You might want to limit this to 100 rows and order
them by rand desc using the ContainsTable or FreeTextTable predicates.
4) To reduce locking you might be better of scheduling your change tracking
updates as opposed to using change tracking with update index in background.
This is almost always more efficient than incremental populations. A
timestamp column should be on your table to perform an incremental
population, otherwise a full population will be done. If you have an full
population completed and you enable change tracking and you have a timestamp
column, IIRC no incremental population will be required. If no timestamp
column exists or if a full population has not completed, a full population
will be run.
5) have you checked out the following kb articles?
http://support.microsoft.com/?scid=h...te+paper.as p
or
http://msdn.microsoft.com/library/de...tml/sp04f9.asp
Hilary Cotter
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
"bigjoepo" <bigjoepo@.gmail.com> wrote in message
news:1126822878.985027.268380@.z14g2000cwz.googlegr oups.com...
> I've been learning a lot about full text searching over the last few
> days while working on a large scale project (well, large for me atleast
> - currently almost 4 million records with near-future expansion to
> about 10 million).
> I still have a few unanswered questions though. First, let me give my
> environment info: Single CPU Server (1Ghz), 1 GB RAM (expanding to 2GB
> tomorrow), Windows 2000 server, SQL 2000 Standard Edition. Database is
> on mirrored SCSI drive, Full Text Data is on new 80GB IDE drive
> (couldn't fit another SCSI). The site is a merchant affiliate, which
> pulls in data from Linkshare merchants and then allows for searching
> globally, by category, and by merchant.
> During population (full or incremental) searching on the index with
> CONTAINS queries takes forever. I would expect the performance to be
> affected during population, but is there anything I can do to avoid
> this headache?
> Here's my current situation with this: I had about 2.2 million records
> in my table yesterday, and my index finally finished populating - the
> index is on 3 varchar fields, one is 255 length, one is 500 and one is
> 2000. My queries were coming back super fast at this point. I then
> turned on "Change Tracking". Not sure if this was a "bad" time to do
> this. Possibly should have been done up front, but nothing I've seen
> online indicates this can't be done at any point. Thinking that the
> index would then update smoothly as I imported data, I proceeded to
> import another 1.5 million records. (FYI, new data imports would
> optimally be nightly and size would range from 2,000 to 2,000,000
> records)
> It is now 18 hours later and my catalog says "incremental population in
> progress" and is only at about 2.23 million records. It is moving
> incredibly slow. I know that it moved faster yesterday when load was
> reduced later at night, so I am hoping that it finishes before the
> morning.
> So basically here's my questions:
> 1. Will my future updates index quickly with change tracking?
> 2. How does change tracking work? Does it re-index ANY row called
> with ANY UPDATE/INSERT/DELETE statement? My import updates all
> existing records. It doesn't check for changes, just assumes it needs
> the latest data and updates the record. I can adjust this if
> necessary. (any suggestions would be great too)
> 3. Is there any way to do an alternate search, or does anyone else do
> something to avoid searching the indexed data while it's being
> populated so it doesn't take 3 minutes to search on my website?
> 4. I've heard that change tracking is better than scheduled
> incremental population. Would I be better off in my scenario with the
> scheduled population? I would need a timestamp column right? And
> would have to populate that field for all existing rows, right?
> 5. Any additional insight would be great. I've gotten some great
> info from this forum and others, but sometimes you just need to "hear
> it" yourself to get it, ya know?
>
> Thanks in advance,
> Joe Potenza
> bigjoepo@.gmail.com
>
2012年3月19日星期一
Full Text Indexing security
I've seen a few posts where it's been said that to get FTI set-up you should
run :
exec sp_grantlogin N'NT Authority\System'
exec sp_defaultdb N'NT Authority\System', N'master'
exec sp_defaultlanguage N'NT Authority\System','us_english'
exec sp_addsrvrolemember N'NT Authority\System', sysadmin
My question is, is there any way at all to run FTI without either the
Builtin or Nt authority accounts as sysadmin? It just strikes me as odd that
what is essentially a data read requires such high level permissions. It
means either the network guys in my company who don't know SQL but are admins
of the box becomes SQL admins, or the other services on the box can in theory
gain admin rights. Are there no registry keys, OS permissions or anything
else which can get this working without SA permissions? We try to secure
boxes as much as we can at the server and OS level and then do the same
within SQL. We've build policies specifically so that the domain account
running sql isn't an admin on its box, so ideally we'd avoid giving out sql
admin rights.
Thanks
The NT Authority\System account is a system account, in otherwords there is
no user attached to it, no user can logon using this account and it has no
shell.
By removing the BuiltIn\admins no admin will be able to administer SQL
Server.
AFAIK - there are no registry tweaks that will help with this.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"DaveK" <DaveK@.discussions.microsoft.com> wrote in message
news:0F6FEC09-9336-4F36-8EF2-D81D7164BA16@.microsoft.com...
> I've seen a few posts where it's been said that to get FTI set-up you
should
> run :
> exec sp_grantlogin N'NT Authority\System'
> exec sp_defaultdb N'NT Authority\System', N'master'
> exec sp_defaultlanguage N'NT Authority\System','us_english'
> exec sp_addsrvrolemember N'NT Authority\System', sysadmin
> My question is, is there any way at all to run FTI without either the
> Builtin or Nt authority accounts as sysadmin? It just strikes me as odd
that
> what is essentially a data read requires such high level permissions. It
> means either the network guys in my company who don't know SQL but are
admins
> of the box becomes SQL admins, or the other services on the box can in
theory
> gain admin rights. Are there no registry keys, OS permissions or anything
> else which can get this working without SA permissions? We try to secure
> boxes as much as we can at the server and OS level and then do the same
> within SQL. We've build policies specifically so that the domain account
> running sql isn't an admin on its box, so ideally we'd avoid giving out
sql
> admin rights.
> Thanks
>
>
>
>
run :
exec sp_grantlogin N'NT Authority\System'
exec sp_defaultdb N'NT Authority\System', N'master'
exec sp_defaultlanguage N'NT Authority\System','us_english'
exec sp_addsrvrolemember N'NT Authority\System', sysadmin
My question is, is there any way at all to run FTI without either the
Builtin or Nt authority accounts as sysadmin? It just strikes me as odd that
what is essentially a data read requires such high level permissions. It
means either the network guys in my company who don't know SQL but are admins
of the box becomes SQL admins, or the other services on the box can in theory
gain admin rights. Are there no registry keys, OS permissions or anything
else which can get this working without SA permissions? We try to secure
boxes as much as we can at the server and OS level and then do the same
within SQL. We've build policies specifically so that the domain account
running sql isn't an admin on its box, so ideally we'd avoid giving out sql
admin rights.
Thanks
The NT Authority\System account is a system account, in otherwords there is
no user attached to it, no user can logon using this account and it has no
shell.
By removing the BuiltIn\admins no admin will be able to administer SQL
Server.
AFAIK - there are no registry tweaks that will help with this.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"DaveK" <DaveK@.discussions.microsoft.com> wrote in message
news:0F6FEC09-9336-4F36-8EF2-D81D7164BA16@.microsoft.com...
> I've seen a few posts where it's been said that to get FTI set-up you
should
> run :
> exec sp_grantlogin N'NT Authority\System'
> exec sp_defaultdb N'NT Authority\System', N'master'
> exec sp_defaultlanguage N'NT Authority\System','us_english'
> exec sp_addsrvrolemember N'NT Authority\System', sysadmin
> My question is, is there any way at all to run FTI without either the
> Builtin or Nt authority accounts as sysadmin? It just strikes me as odd
that
> what is essentially a data read requires such high level permissions. It
> means either the network guys in my company who don't know SQL but are
admins
> of the box becomes SQL admins, or the other services on the box can in
theory
> gain admin rights. Are there no registry keys, OS permissions or anything
> else which can get this working without SA permissions? We try to secure
> boxes as much as we can at the server and OS level and then do the same
> within SQL. We've build policies specifically so that the domain account
> running sql isn't an admin on its box, so ideally we'd avoid giving out
sql
> admin rights.
> Thanks
>
>
>
>
full text indexing not starting automatically
Hello,
I've just started using full-text with SQL server 2000 (its version
8.00.760) . This is for a client, I'm a free-lancer.
I got it set up and running and am feeding in records. I want to have it
re-index every 5 minutes (I'm adding a batch of 100 new records or so every
5 minutes) These are news articles so i'm using an incremental update; the
old data will never change.
If I manually launch it by right-clicking the catalog icon and select "Start
Incremental Population" it works. I set up a schedule to run every 10
minutes but it doesn't automatically run. There doesn't appear to be any way
to mess that up; the dialog boxes for a scheduled item are crystal clear.
The 'enabled' checkbox is checked, the Job Type is 'Incremental
population', The 'Schedule frequency' is Recurring with the text:
Occurs every 1 day(s), every 10 minute(s) between 12:00:00 AM and 11:59:00
PM
What am I missing?
thanks,
John Mott
Use change tracking. Right click on the table a second time, select Change
Tracking. Then Right click again and select Update Index in Background
Hilary Cotter
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
"John Mott" <johnmott59@.hotmail.com> wrote in message
news:e2ZqybvrFHA.4040@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I've just started using full-text with SQL server 2000 (its version
> 8.00.760) . This is for a client, I'm a free-lancer.
> I got it set up and running and am feeding in records. I want to have it
> re-index every 5 minutes (I'm adding a batch of 100 new records or so
every
> 5 minutes) These are news articles so i'm using an incremental update; the
> old data will never change.
> If I manually launch it by right-clicking the catalog icon and select
"Start
> Incremental Population" it works. I set up a schedule to run every 10
> minutes but it doesn't automatically run. There doesn't appear to be any
way
> to mess that up; the dialog boxes for a scheduled item are crystal clear.
> The 'enabled' checkbox is checked, the Job Type is 'Incremental
> population', The 'Schedule frequency' is Recurring with the text:
> Occurs every 1 day(s), every 10 minute(s) between 12:00:00 AM and 11:59:00
> PM
> What am I missing?
> thanks,
> John Mott
>
|||Done, it looks like its churning. Thank you. I read about the types of
changes but I'm not clear on the difference between change tracking and
incremental update. Could you please explain the difference and why change
tracking is the appropriate choice for an application that only adds new
data?
Thank you for your response.
John Mott
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23KoriuvrFHA.3596@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Use change tracking. Right click on the table a second time, select Change
> Tracking. Then Right click again and select Update Index in Background
> --
> Hilary Cotter
> 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
> "John Mott" <johnmott59@.hotmail.com> wrote in message
> news:e2ZqybvrFHA.4040@.TK2MSFTNGP14.phx.gbl...
> every
the[vbcol=seagreen]
> "Start
> way
clear.[vbcol=seagreen]
11:59:00
>
|||A full population extracts each row and indexes it.
An incremental population done on a table which does not have a timestamp
data type column on it will perform a full population under the covers.
An incremental population done on a table which does have a timestamp data
type column on it will result in each row being extracted and only the new
rows are reindexed. This full extraction has to be done so the indexer can
figure out which rows have been deleted, inserted, and modified. Note that
even on a row where the columns you are full text indexing have not been
modified (an integer column perhaps has been modified) the row will be
flagged for re-indexing.
Change tracking will initially require a full population (or an incremental
population if a full population has already being done). After this only
rows in which the columns you are full text indexing are modified will be
extracted and indexed.
By default this will only occur when you issue a sp_fulltext_table
'TableName','update_index', however with update index in background the
indexing process normally completes within seconds resulting in near real
time indexing.
Hilary Cotter
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
"John Mott" <johnmott59@.hotmail.com> wrote in message
news:eZMjz4vrFHA.3988@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Done, it looks like its churning. Thank you. I read about the types of
> changes but I'm not clear on the difference between change tracking and
> incremental update. Could you please explain the difference and why change
> tracking is the appropriate choice for an application that only adds new
> data?
> Thank you for your response.
> John Mott
>
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23KoriuvrFHA.3596@.TK2MSFTNGP15.phx.gbl...
Change[vbcol=seagreen]
it[vbcol=seagreen]
> the
any
> clear.
> 11:59:00
>
|||Wow. That is extremely illuminating. I intentionally put a timestamp column
and a small (4 byte) primary key field in the table to help the indexer, but
if its still checking each row on an incremental update that will get
expensive; this table will have millions of records.
Thank you very much for the explanation.
john
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:Owqz8BwrFHA.3604@.tk2msftngp13.phx.gbl...
> A full population extracts each row and indexes it.
> An incremental population done on a table which does not have a timestamp
> data type column on it will perform a full population under the covers.
> An incremental population done on a table which does have a timestamp data
> type column on it will result in each row being extracted and only the new
> rows are reindexed. This full extraction has to be done so the indexer can
> figure out which rows have been deleted, inserted, and modified. Note that
> even on a row where the columns you are full text indexing have not been
> modified (an integer column perhaps has been modified) the row will be
> flagged for re-indexing.
> Change tracking will initially require a full population (or an
incremental[vbcol=seagreen]
> population if a full population has already being done). After this only
> rows in which the columns you are full text indexing are modified will be
> extracted and indexed.
> By default this will only occur when you issue a sp_fulltext_table
> 'TableName','update_index', however with update index in background the
> indexing process normally completes within seconds resulting in near real
> time indexing.
> --
> Hilary Cotter
> 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
> "John Mott" <johnmott59@.hotmail.com> wrote in message
> news:eZMjz4vrFHA.3988@.TK2MSFTNGP10.phx.gbl...
change[vbcol=seagreen]
> Change
have[vbcol=seagreen]
> it
so[vbcol=seagreen]
update;[vbcol=seagreen]
select[vbcol=seagreen]
10
> any
>
|||the size of the pk is irrelevant in the indexing process (unless the pk is
huge or more than one col). A small pk does help with the regular (ie non
full-text) indexes.
Hilary Cotter
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
"John Mott" <johnmott59@.hotmail.com> wrote in message
news:%23MgdHTwrFHA.2348@.tk2msftngp13.phx.gbl...
> Wow. That is extremely illuminating. I intentionally put a timestamp
> column
> and a small (4 byte) primary key field in the table to help the indexer,
> but
> if its still checking each row on an incremental update that will get
> expensive; this table will have millions of records.
> Thank you very much for the explanation.
> john
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:Owqz8BwrFHA.3604@.tk2msftngp13.phx.gbl...
> incremental
> change
> have
> so
> update;
> select
> 10
>
I've just started using full-text with SQL server 2000 (its version
8.00.760) . This is for a client, I'm a free-lancer.
I got it set up and running and am feeding in records. I want to have it
re-index every 5 minutes (I'm adding a batch of 100 new records or so every
5 minutes) These are news articles so i'm using an incremental update; the
old data will never change.
If I manually launch it by right-clicking the catalog icon and select "Start
Incremental Population" it works. I set up a schedule to run every 10
minutes but it doesn't automatically run. There doesn't appear to be any way
to mess that up; the dialog boxes for a scheduled item are crystal clear.
The 'enabled' checkbox is checked, the Job Type is 'Incremental
population', The 'Schedule frequency' is Recurring with the text:
Occurs every 1 day(s), every 10 minute(s) between 12:00:00 AM and 11:59:00
PM
What am I missing?
thanks,
John Mott
Use change tracking. Right click on the table a second time, select Change
Tracking. Then Right click again and select Update Index in Background
Hilary Cotter
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
"John Mott" <johnmott59@.hotmail.com> wrote in message
news:e2ZqybvrFHA.4040@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I've just started using full-text with SQL server 2000 (its version
> 8.00.760) . This is for a client, I'm a free-lancer.
> I got it set up and running and am feeding in records. I want to have it
> re-index every 5 minutes (I'm adding a batch of 100 new records or so
every
> 5 minutes) These are news articles so i'm using an incremental update; the
> old data will never change.
> If I manually launch it by right-clicking the catalog icon and select
"Start
> Incremental Population" it works. I set up a schedule to run every 10
> minutes but it doesn't automatically run. There doesn't appear to be any
way
> to mess that up; the dialog boxes for a scheduled item are crystal clear.
> The 'enabled' checkbox is checked, the Job Type is 'Incremental
> population', The 'Schedule frequency' is Recurring with the text:
> Occurs every 1 day(s), every 10 minute(s) between 12:00:00 AM and 11:59:00
> PM
> What am I missing?
> thanks,
> John Mott
>
|||Done, it looks like its churning. Thank you. I read about the types of
changes but I'm not clear on the difference between change tracking and
incremental update. Could you please explain the difference and why change
tracking is the appropriate choice for an application that only adds new
data?
Thank you for your response.
John Mott
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23KoriuvrFHA.3596@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Use change tracking. Right click on the table a second time, select Change
> Tracking. Then Right click again and select Update Index in Background
> --
> Hilary Cotter
> 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
> "John Mott" <johnmott59@.hotmail.com> wrote in message
> news:e2ZqybvrFHA.4040@.TK2MSFTNGP14.phx.gbl...
> every
the[vbcol=seagreen]
> "Start
> way
clear.[vbcol=seagreen]
11:59:00
>
|||A full population extracts each row and indexes it.
An incremental population done on a table which does not have a timestamp
data type column on it will perform a full population under the covers.
An incremental population done on a table which does have a timestamp data
type column on it will result in each row being extracted and only the new
rows are reindexed. This full extraction has to be done so the indexer can
figure out which rows have been deleted, inserted, and modified. Note that
even on a row where the columns you are full text indexing have not been
modified (an integer column perhaps has been modified) the row will be
flagged for re-indexing.
Change tracking will initially require a full population (or an incremental
population if a full population has already being done). After this only
rows in which the columns you are full text indexing are modified will be
extracted and indexed.
By default this will only occur when you issue a sp_fulltext_table
'TableName','update_index', however with update index in background the
indexing process normally completes within seconds resulting in near real
time indexing.
Hilary Cotter
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
"John Mott" <johnmott59@.hotmail.com> wrote in message
news:eZMjz4vrFHA.3988@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Done, it looks like its churning. Thank you. I read about the types of
> changes but I'm not clear on the difference between change tracking and
> incremental update. Could you please explain the difference and why change
> tracking is the appropriate choice for an application that only adds new
> data?
> Thank you for your response.
> John Mott
>
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23KoriuvrFHA.3596@.TK2MSFTNGP15.phx.gbl...
Change[vbcol=seagreen]
it[vbcol=seagreen]
> the
any
> clear.
> 11:59:00
>
|||Wow. That is extremely illuminating. I intentionally put a timestamp column
and a small (4 byte) primary key field in the table to help the indexer, but
if its still checking each row on an incremental update that will get
expensive; this table will have millions of records.
Thank you very much for the explanation.
john
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:Owqz8BwrFHA.3604@.tk2msftngp13.phx.gbl...
> A full population extracts each row and indexes it.
> An incremental population done on a table which does not have a timestamp
> data type column on it will perform a full population under the covers.
> An incremental population done on a table which does have a timestamp data
> type column on it will result in each row being extracted and only the new
> rows are reindexed. This full extraction has to be done so the indexer can
> figure out which rows have been deleted, inserted, and modified. Note that
> even on a row where the columns you are full text indexing have not been
> modified (an integer column perhaps has been modified) the row will be
> flagged for re-indexing.
> Change tracking will initially require a full population (or an
incremental[vbcol=seagreen]
> population if a full population has already being done). After this only
> rows in which the columns you are full text indexing are modified will be
> extracted and indexed.
> By default this will only occur when you issue a sp_fulltext_table
> 'TableName','update_index', however with update index in background the
> indexing process normally completes within seconds resulting in near real
> time indexing.
> --
> Hilary Cotter
> 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
> "John Mott" <johnmott59@.hotmail.com> wrote in message
> news:eZMjz4vrFHA.3988@.TK2MSFTNGP10.phx.gbl...
change[vbcol=seagreen]
> Change
have[vbcol=seagreen]
> it
so[vbcol=seagreen]
update;[vbcol=seagreen]
select[vbcol=seagreen]
10
> any
>
|||the size of the pk is irrelevant in the indexing process (unless the pk is
huge or more than one col). A small pk does help with the regular (ie non
full-text) indexes.
Hilary Cotter
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
"John Mott" <johnmott59@.hotmail.com> wrote in message
news:%23MgdHTwrFHA.2348@.tk2msftngp13.phx.gbl...
> Wow. That is extremely illuminating. I intentionally put a timestamp
> column
> and a small (4 byte) primary key field in the table to help the indexer,
> but
> if its still checking each row on an incremental update that will get
> expensive; this table will have millions of records.
> Thank you very much for the explanation.
> john
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:Owqz8BwrFHA.3604@.tk2msftngp13.phx.gbl...
> incremental
> change
> have
> so
> update;
> select
> 10
>
2012年3月11日星期日
Full Text Indexing Limitations
Can anyone confirm for me that full text indexing will flake out once
reaching around 256 folders in the MSSQL\FTDATA directory? I've had a
larger number in this folder on a constant basis over the last year and have
had an occasional problem with the error of "
update sysobjects set ftcatid = 0 where ftcatid <> 0" along with other
errors when moving DBs to and from my staging servers.
I developed a script a couple of years ago that got me to the point where I
could delete and re-create these indexes when necessary and since they work
pretty regularly with my 500+, 1000+ folders in this directory I find it
hard to believe that 256 is the limit and was wondering what other
experiences there are out there.
Yes, I've tried probably every fix including manually reinstalling Full
Text, checking permissions, etc etc from MSDN articles out there so no need
to send them my way. I'm just asking for experiences here before calling
PSS.
Thanks.
Adam P. Cassidy
can you define what you mean by flake out?
Ideally you will have as few catalogs as possible. I'll try to repro this.
Hilary Cotter
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
"Adam Patrick Cassidy" <apcassidy@.aol.com> wrote in message
news:eIkMKERKHHA.2028@.TK2MSFTNGP03.phx.gbl...
> Can anyone confirm for me that full text indexing will flake out once
> reaching around 256 folders in the MSSQL\FTDATA directory? I've had a
> larger number in this folder on a constant basis over the last year and
> have had an occasional problem with the error of "
> update sysobjects set ftcatid = 0 where ftcatid <> 0" along with other
> errors when moving DBs to and from my staging servers.
> I developed a script a couple of years ago that got me to the point where
> I could delete and re-create these indexes when necessary and since they
> work pretty regularly with my 500+, 1000+ folders in this directory I find
> it hard to believe that 256 is the limit and was wondering what other
> experiences there are out there.
> Yes, I've tried probably every fix including manually reinstalling Full
> Text, checking permissions, etc etc from MSDN articles out there so no
> need to send them my way. I'm just asking for experiences here before
> calling PSS.
> Thanks.
> Adam P. Cassidy
>
|||My apologies Hilary, I see that the error that I pasted was actually some
code I was working with! My definition of "flake out" would be getting the
regular guest of "An unknown full text failure (80004005) occurred in
function EnumCatalogs on full-text catalog ''." on one staging server and
most recently and never before seen by me as of last week "The Microsoft
Search service cannot be administered under the present user account" on a
second staging server.
I'm running SQL 2000 on both and within the last year I've had to reload two
servers from the ground up (windows installs included) in order to resolve
the first error message. What I'm trying to figure out is, what additional
troubleshooting I can do to:
Determine what caused the problem versus searching the msdn/technet articles
for this message only to find what seems to be multiple guesses as to what I
can check that has caused the problem (registry, permissions, accounts used,
manual reinstall, etc) when none of these things (in my opinion) would ever
be changed. I've yet to find any of these resolving my problem, including
the manual reinstall of MSSearch. I've always found nothing changed or
missing from the MSDN/Technet articles and therefore only resolved, for a
number of months at a time, the problem by reinstalling.
Currently that is my planned track for this upcoming weekend (what an
exciting time I'll have). There needs to be a more specific error message
for these full text index issues in my opinion - esp. when you find articles
that list 4-5 solutions to the same error.
Adam
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23iFOPDSKHHA.3952@.TK2MSFTNGP02.phx.gbl...
> can you define what you mean by flake out?
> Ideally you will have as few catalogs as possible. I'll try to repro this.
> --
> Hilary Cotter
> 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
>
> "Adam Patrick Cassidy" <apcassidy@.aol.com> wrote in message
> news:eIkMKERKHHA.2028@.TK2MSFTNGP03.phx.gbl...
>
reaching around 256 folders in the MSSQL\FTDATA directory? I've had a
larger number in this folder on a constant basis over the last year and have
had an occasional problem with the error of "
update sysobjects set ftcatid = 0 where ftcatid <> 0" along with other
errors when moving DBs to and from my staging servers.
I developed a script a couple of years ago that got me to the point where I
could delete and re-create these indexes when necessary and since they work
pretty regularly with my 500+, 1000+ folders in this directory I find it
hard to believe that 256 is the limit and was wondering what other
experiences there are out there.
Yes, I've tried probably every fix including manually reinstalling Full
Text, checking permissions, etc etc from MSDN articles out there so no need
to send them my way. I'm just asking for experiences here before calling
PSS.
Thanks.
Adam P. Cassidy
can you define what you mean by flake out?
Ideally you will have as few catalogs as possible. I'll try to repro this.
Hilary Cotter
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
"Adam Patrick Cassidy" <apcassidy@.aol.com> wrote in message
news:eIkMKERKHHA.2028@.TK2MSFTNGP03.phx.gbl...
> Can anyone confirm for me that full text indexing will flake out once
> reaching around 256 folders in the MSSQL\FTDATA directory? I've had a
> larger number in this folder on a constant basis over the last year and
> have had an occasional problem with the error of "
> update sysobjects set ftcatid = 0 where ftcatid <> 0" along with other
> errors when moving DBs to and from my staging servers.
> I developed a script a couple of years ago that got me to the point where
> I could delete and re-create these indexes when necessary and since they
> work pretty regularly with my 500+, 1000+ folders in this directory I find
> it hard to believe that 256 is the limit and was wondering what other
> experiences there are out there.
> Yes, I've tried probably every fix including manually reinstalling Full
> Text, checking permissions, etc etc from MSDN articles out there so no
> need to send them my way. I'm just asking for experiences here before
> calling PSS.
> Thanks.
> Adam P. Cassidy
>
|||My apologies Hilary, I see that the error that I pasted was actually some
code I was working with! My definition of "flake out" would be getting the
regular guest of "An unknown full text failure (80004005) occurred in
function EnumCatalogs on full-text catalog ''." on one staging server and
most recently and never before seen by me as of last week "The Microsoft
Search service cannot be administered under the present user account" on a
second staging server.
I'm running SQL 2000 on both and within the last year I've had to reload two
servers from the ground up (windows installs included) in order to resolve
the first error message. What I'm trying to figure out is, what additional
troubleshooting I can do to:
Determine what caused the problem versus searching the msdn/technet articles
for this message only to find what seems to be multiple guesses as to what I
can check that has caused the problem (registry, permissions, accounts used,
manual reinstall, etc) when none of these things (in my opinion) would ever
be changed. I've yet to find any of these resolving my problem, including
the manual reinstall of MSSearch. I've always found nothing changed or
missing from the MSDN/Technet articles and therefore only resolved, for a
number of months at a time, the problem by reinstalling.
Currently that is my planned track for this upcoming weekend (what an
exciting time I'll have). There needs to be a more specific error message
for these full text index issues in my opinion - esp. when you find articles
that list 4-5 solutions to the same error.
Adam
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23iFOPDSKHHA.3952@.TK2MSFTNGP02.phx.gbl...
> can you define what you mean by flake out?
> Ideally you will have as few catalogs as possible. I'll try to repro this.
> --
> Hilary Cotter
> 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
>
> "Adam Patrick Cassidy" <apcassidy@.aol.com> wrote in message
> news:eIkMKERKHHA.2028@.TK2MSFTNGP03.phx.gbl...
>
2012年3月9日星期五
Full Text in SQL 2005 vs SQL Express
Does full text work the same in both of these? From what I've read, it
appears SQl Express only searches in text columns...
I have a client who needs to search in a a column which contains imported
Word docs, etc. (varbinary(max)). Can this be done in the Express version or
not?
Thanks,
Tom
Hello Tom,
According to this MSDN article, SQL Express did not support to full-text
query the varbinary(max) column. But the SQL Express with Advanced Services
support.
http://msdn2.microsoft.com/en-us/library/ms143761.aspx
You may try to use that Edition.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
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.
|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
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.
appears SQl Express only searches in text columns...
I have a client who needs to search in a a column which contains imported
Word docs, etc. (varbinary(max)). Can this be done in the Express version or
not?
Thanks,
Tom
Hello Tom,
According to this MSDN article, SQL Express did not support to full-text
query the varbinary(max) column. But the SQL Express with Advanced Services
support.
http://msdn2.microsoft.com/en-us/library/ms143761.aspx
You may try to use that Edition.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
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.
|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
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.
2012年3月7日星期三
Full Text Catalog not appearing
Hi
I've got SQLServer 2000 (think it's the developer version) installed on my
Win2000 box.
I've just added the full text catalogue component to my SQLServer
However, when I connect to it using Enterprise Manager, under each database
I only see the options:
Diagrams
Tables
Views
Stored Procedures
Users
Roles
Rules
Defaults
User Defined Data Types
User Defined Functions
When I connect to another server, I also see the option
Full-text catalogs
What is it that I need to do to make this option appear?
Thanks
Griff
Okay - found that I require Server (workstation doesn't support this)
I've got SQLServer 2000 (think it's the developer version) installed on my
Win2000 box.
I've just added the full text catalogue component to my SQLServer
However, when I connect to it using Enterprise Manager, under each database
I only see the options:
Diagrams
Tables
Views
Stored Procedures
Users
Roles
Rules
Defaults
User Defined Data Types
User Defined Functions
When I connect to another server, I also see the option
Full-text catalogs
What is it that I need to do to make this option appear?
Thanks
Griff
Okay - found that I require Server (workstation doesn't support this)
Full Text - Index Population - HDD Access
Hi,
I'm indexing a single field on a table with 10 million rows in it, its
currently taking about 48 hours to build.
I've been looking into performance bottlenecks on the system and see that
this delay is being mainly caused by MSSearch writing massive amounts of
data to the FT Index location. Since the finished index is only about 400mb
I can only imagine that the population process is rewriting the data again
and again to the disk as it is updated.
Is this a fair assumption? If so is there anyway around it, maybe to tell
MSSearch to use ram until the population is build then write the index to
disk?
Thanks
Michael
No.
There is the resource usage setting which may increase performance during
the indexing process (sp_fulltext_service 'catalogname','resource_usgae',5)
The more resources you can spend to improve the indexing process the better,
normally you get the best bang for your buck by investing in the disk
subsystem. For instance placing your catalogs on a RAID 10, 1, or 01 array.
01 is not considered to be a good choice as it does not offer the the
protection 10 does.
Also your database should be raid 5 depending on the level of write activity
on it.
What type of content are you indexing. You will get best performance by
indexing text content as opposed to indexing documents in their native
format (doc, xls, pdf, etc).
Please review the full text white paper on SQL FTS performance at:
http://support.microsoft.com/support...er.asp?GSSNB=1
Save this document as a zip and extract it.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Mike Davies" <michael.davies@.synsoft.co.uk> wrote in message
news:ezLfihZmEHA.3824@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm indexing a single field on a table with 10 million rows in it, its
> currently taking about 48 hours to build.
> I've been looking into performance bottlenecks on the system and see that
> this delay is being mainly caused by MSSearch writing massive amounts of
> data to the FT Index location. Since the finished index is only about
400mb
> I can only imagine that the population process is rewriting the data again
> and again to the disk as it is updated.
> Is this a fair assumption? If so is there anyway around it, maybe to tell
> MSSearch to use ram until the population is build then write the index to
> disk?
> Thanks
> Michael
>
|||Mike
What hardware is it running on?
Is the Full-Text Index on the same physical drive/lun as the database? It
should be possible to increase IO throughput by placing the Full-Text Index
on its own drive.
Are you using SCSI disks? If so are you using the faster 15k RPM disks as
apposed to 10k RPM disks.
How much RAM does the machine have?
How many processors and what spec are they?
Is SQL Server configured to leave some RAM free for the Operating System and
other Applications, i.e. if you have 2GB RAM is SQL configured to use max
1.5GB?
You might want to try SQL Server 2005 Beta 2. I understand that Full-Text
Search is much faster at indexing.
http://msdn.microsoft.com/library/en...asp?frame=true
Rich
"Hilary Cotter" wrote:
> No.
> There is the resource usage setting which may increase performance during
> the indexing process (sp_fulltext_service 'catalogname','resource_usgae',5)
> The more resources you can spend to improve the indexing process the better,
> normally you get the best bang for your buck by investing in the disk
> subsystem. For instance placing your catalogs on a RAID 10, 1, or 01 array.
> 01 is not considered to be a good choice as it does not offer the the
> protection 10 does.
> Also your database should be raid 5 depending on the level of write activity
> on it.
> What type of content are you indexing. You will get best performance by
> indexing text content as opposed to indexing documents in their native
> format (doc, xls, pdf, etc).
> Please review the full text white paper on SQL FTS performance at:
> http://support.microsoft.com/support...er.asp?GSSNB=1
> Save this document as a zip and extract it.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Mike Davies" <michael.davies@.synsoft.co.uk> wrote in message
> news:ezLfihZmEHA.3824@.TK2MSFTNGP12.phx.gbl...
> 400mb
>
>
|||Mike,
Yes, it is a fair assumption. You should review the SQL Server 2000 BOL
title "Full-text Search Recommendations" as well as the FTS Deployment white
paper. Are there workarounds? Yes. You can ensure that the disk drive that
your FT Catalog resides is separate from your database files as well as very
fast (>10,000 RPMs) and configured as RAID0 or RAID10. The Full Text
Indexing process is primarily I/O bound, but can also consume much memory
and CPU usage from time-to-time during Shadow Merges and at the end of the
process due to Master Merge.
Regards,
John
"Mike Davies" <michael.davies@.synsoft.co.uk> wrote in message
news:ezLfihZmEHA.3824@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm indexing a single field on a table with 10 million rows in it, its
> currently taking about 48 hours to build.
> I've been looking into performance bottlenecks on the system and see that
> this delay is being mainly caused by MSSearch writing massive amounts of
> data to the FT Index location. Since the finished index is only about
400mb
> I can only imagine that the population process is rewriting the data again
> and again to the disk as it is updated.
> Is this a fair assumption? If so is there anyway around it, maybe to tell
> MSSearch to use ram until the population is build then write the index to
> disk?
> Thanks
> Michael
>
|||Hi Rich,
It's running on a single P4 Prescott at 3Ghz with 4GB of RAM. SQL Server
currently has access to all system RAM but is sat at approx 1.7Gb during the
indexing process.
Both the FullText index and the SQL database are on separate physical SATA
drives, however because of an apparent incompatibility issue with Windows
and running the two SATA drives on their own bus they are both running
through the standard IDE bus, I imagine this is not doing the performance
any favours.
Thanks for the 2005 tip, I'll give that a try.
Can you offer any other suggestions to the setup?
Thanks
Mike
"Richard Yeo" <RichardYeo@.discussions.microsoft.com> wrote in message
news:692389A4-ED05-42C7-B645-982E7E9CE989@.microsoft.com...
> Mike
> What hardware is it running on?
> Is the Full-Text Index on the same physical drive/lun as the database? It
> should be possible to increase IO throughput by placing the Full-Text
Index
> on its own drive.
> Are you using SCSI disks? If so are you using the faster 15k RPM disks as
> apposed to 10k RPM disks.
> How much RAM does the machine have?
> How many processors and what spec are they?
> Is SQL Server configured to leave some RAM free for the Operating System
and
> other Applications, i.e. if you have 2GB RAM is SQL configured to use max
> 1.5GB?
> You might want to try SQL Server 2005 Beta 2. I understand that Full-Text
> Search is much faster at indexing.
>
http://msdn.microsoft.com/library/en...asp?frame=true[vbcol=seagreen]
> Rich
>
> "Hilary Cotter" wrote:
during[vbcol=seagreen]
'catalogname','resource_usgae',5)[vbcol=seagreen]
better,[vbcol=seagreen]
array.[vbcol=seagreen]
activity[vbcol=seagreen]
http://support.microsoft.com/support...er.asp?GSSNB=1[vbcol=seagreen]
that[vbcol=seagreen]
of[vbcol=seagreen]
again[vbcol=seagreen]
tell[vbcol=seagreen]
to[vbcol=seagreen]
|||Mike
Have you implemented the /3GB switch in the OS boot.ini? If not by default
2GB is set aside for apps and 2GB for OS. By using this switch 3GB is set
aside for apps and 1GB for OS. This probably explains why SQL Server is only
using 1.7GB RAM. It would probably be using more if you had the /3GB switch
set. Try setting /3GB switch, instruct SQL Server to use Physical RAM, and
set max memory for SQL Server to be 2.5GB. This will leave 0.5GB for other
apps including MSSearch (I think), file system caching, etc.
Even if you don't have the budget for a 2 proc SQL license I would always
recommend purchasing a second physical processor and configuring SQL Server
to only use one of the processors. This leaves the second processor free for
other applications, OS, remote administration.
You might want to try the manufacturers web-site regarding BIOS / firmware
updates for your SATA / IDE issue.
Rich
"Mike Davies" wrote:
> Hi Rich,
> It's running on a single P4 Prescott at 3Ghz with 4GB of RAM. SQL Server
> currently has access to all system RAM but is sat at approx 1.7Gb during the
> indexing process.
> Both the FullText index and the SQL database are on separate physical SATA
> drives, however because of an apparent incompatibility issue with Windows
> and running the two SATA drives on their own bus they are both running
> through the standard IDE bus, I imagine this is not doing the performance
> any favours.
> Thanks for the 2005 tip, I'll give that a try.
> Can you offer any other suggestions to the setup?
> Thanks
> Mike
>
> "Richard Yeo" <RichardYeo@.discussions.microsoft.com> wrote in message
> news:692389A4-ED05-42C7-B645-982E7E9CE989@.microsoft.com...
> Index
> and
> http://msdn.microsoft.com/library/en...asp?frame=true
> during
> 'catalogname','resource_usgae',5)
> better,
> array.
> activity
> http://support.microsoft.com/support...er.asp?GSSNB=1
> that
> of
> again
> tell
> to
>
>
|||Mike,
Rich, while the /3GB switch can help SQL Server 2000, it has no affect on
the "Microsoft Search" (mssearch.exe) FT Search engine as it can only use up
to a max of 512MB of physical RAM. However, you must set the resource_usage
value to 5 (dedicated) via sp_fulltext_service 'resource_usage', 5 and you
must have at least 512Mb of RAM reserved for the MSSearch service that is
NOT used by the OS or by SQL Server.
If you can purchase an additional CPU, you can set the MSSearch service's to
use one CPU and then use sp_configure to set the affinity of SQL Server to
that cpu to avoid cpu contention during heavy MSSearch CPU usage via the
following method:
You can launch the Tskmgr.exe from the AT command on the multi-proc server
where sql server resides, as follows:
at <current_time+1min> /interactive taskmgr.exe
when it launches, you can then set "cpu affinity" for the MSSearch service
to a cpu or set of cpu's not being used by SQL Server. Then you would use
sp_configure to set SQL Server's cpu affinity to the other cpu's, thus
preventing the cpu usage of mssearch from affecting your sql server
processing. Note, this only works on multi-processor servers.
Regards,
John
"Richard Yeo" <RichardYeo@.discussions.microsoft.com> wrote in message
news:2AEB4663-795F-48F6-BA68-DEAA0D1C4BD8@.microsoft.com...
> Mike
> Have you implemented the /3GB switch in the OS boot.ini? If not by default
> 2GB is set aside for apps and 2GB for OS. By using this switch 3GB is set
> aside for apps and 1GB for OS. This probably explains why SQL Server is
only
> using 1.7GB RAM. It would probably be using more if you had the /3GB
switch
> set. Try setting /3GB switch, instruct SQL Server to use Physical RAM, and
> set max memory for SQL Server to be 2.5GB. This will leave 0.5GB for other
> apps including MSSearch (I think), file system caching, etc.
> Even if you don't have the budget for a 2 proc SQL license I would always
> recommend purchasing a second physical processor and configuring SQL
Server
> to only use one of the processors. This leaves the second processor free
for[vbcol=seagreen]
> other applications, OS, remote administration.
> You might want to try the manufacturers web-site regarding BIOS / firmware
> updates for your SATA / IDE issue.
> Rich
>
> "Mike Davies" wrote:
Server[vbcol=seagreen]
the[vbcol=seagreen]
SATA[vbcol=seagreen]
Windows[vbcol=seagreen]
performance[vbcol=seagreen]
It[vbcol=seagreen]
as[vbcol=seagreen]
System[vbcol=seagreen]
max[vbcol=seagreen]
Full-Text[vbcol=seagreen]
http://msdn.microsoft.com/library/en...asp?frame=true[vbcol=seagreen]
disk[vbcol=seagreen]
the[vbcol=seagreen]
by[vbcol=seagreen]
native[vbcol=seagreen]
http://support.microsoft.com/support...er.asp?GSSNB=1[vbcol=seagreen]
its[vbcol=seagreen]
see[vbcol=seagreen]
amounts[vbcol=seagreen]
about[vbcol=seagreen]
data[vbcol=seagreen]
to[vbcol=seagreen]
index[vbcol=seagreen]
|||"Richard Yeo" <RichardYeo@.discussions.microsoft.com> wrote in message
news:2AEB4663-795F-48F6-BA68-DEAA0D1C4BD8@.microsoft.com...
> Mike
>
<SNIP>
> Even if you don't have the budget for a 2 proc SQL license I would always
> recommend purchasing a second physical processor and configuring SQL
> Server
> to only use one of the processors. This leaves the second processor free
> for
> other applications, OS, remote administration.
>
</SNIP>
Please be careful about that because, in general, you have to purchase a
processor license for every processor that the OS can see, whether that
processor is configured to run SQL Server or not. For more information,
please refer to the SQL Server 2000 Licensing FAQ web page at
http://www.microsoft.com/sql/howtobuy/faq.asp. The third question under
Licensing is reproduced below for your benefit.
Q. Do I have to license all of the processors in a server?
A. You only have to acquire licenses for processors that are
accessible to any copy of the operating system upon which SQL Server 2000 is
installed.
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
begin 666 ts.gif
J1TE&.#EA`0`!`( ``````/___R'Y! $`````+ `````!``$```(!1 `[
`
end
|||"Richard Yeo" <RichardYeo@.discussions.microsoft.com> wrote in message
news:2AEB4663-795F-48F6-BA68-DEAA0D1C4BD8@.microsoft.com...
> Mike
> Have you implemented the /3GB switch in the OS boot.ini? If not by default
> 2GB is set aside for apps and 2GB for OS. By using this switch 3GB is set
> aside for apps and 1GB for OS. This probably explains why SQL Server is
only
> using 1.7GB RAM. It would probably be using more if you had the /3GB
switch
> set. Try setting /3GB switch, instruct SQL Server to use Physical RAM, and
> set max memory for SQL Server to be 2.5GB. This will leave 0.5GB for other
> apps including MSSearch (I think), file system caching, etc.
> Even if you don't have the budget for a 2 proc SQL license I would always
> recommend purchasing a second physical processor and configuring SQL
Server
> to only use one of the processors. This leaves the second processor free
for
> other applications, OS, remote administration.
Unless thigns have changed, last I knew, MS didn't allow that.
|||Stephen
Thanks for clarifying that.
Obviously there is a difference between what the product is capable of and
what the licensing folk think the product is or should be capable of.
In this instance I feel the Product Team got it right. In the real world
people often have to run more than one application on a server and should not
be forced to buy additional licenses.
With the move in the industry to consolidate this could a lot of unnecessary
expense.
Lets hope this license issue is addressed in the next release of SQL Server
2005.
Rich
"Stephen Dybing [MSFT]" wrote:
> "Richard Yeo" <RichardYeo@.discussions.microsoft.com> wrote in message
> news:2AEB4663-795F-48F6-BA68-DEAA0D1C4BD8@.microsoft.com...
> <SNIP>
> </SNIP>
> Please be careful about that because, in general, you have to purchase a
> processor license for every processor that the OS can see, whether that
> processor is configured to run SQL Server or not. For more information,
> please refer to the SQL Server 2000 Licensing FAQ web page at
> http://www.microsoft.com/sql/howtobuy/faq.asp. The third question under
> Licensing is reproduced below for your benefit.
> Q. Do I have to license all of the processors in a server?
> A. You only have to acquire licenses for processors that are
> accessible to any copy of the operating system upon which SQL Server 2000 is
> installed.
>
>
> --
> Sincerely,
> Stephen Dybing
> This posting is provided "AS IS" with no warranties, and confers no rights
I'm indexing a single field on a table with 10 million rows in it, its
currently taking about 48 hours to build.
I've been looking into performance bottlenecks on the system and see that
this delay is being mainly caused by MSSearch writing massive amounts of
data to the FT Index location. Since the finished index is only about 400mb
I can only imagine that the population process is rewriting the data again
and again to the disk as it is updated.
Is this a fair assumption? If so is there anyway around it, maybe to tell
MSSearch to use ram until the population is build then write the index to
disk?
Thanks
Michael
No.
There is the resource usage setting which may increase performance during
the indexing process (sp_fulltext_service 'catalogname','resource_usgae',5)
The more resources you can spend to improve the indexing process the better,
normally you get the best bang for your buck by investing in the disk
subsystem. For instance placing your catalogs on a RAID 10, 1, or 01 array.
01 is not considered to be a good choice as it does not offer the the
protection 10 does.
Also your database should be raid 5 depending on the level of write activity
on it.
What type of content are you indexing. You will get best performance by
indexing text content as opposed to indexing documents in their native
format (doc, xls, pdf, etc).
Please review the full text white paper on SQL FTS performance at:
http://support.microsoft.com/support...er.asp?GSSNB=1
Save this document as a zip and extract it.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Mike Davies" <michael.davies@.synsoft.co.uk> wrote in message
news:ezLfihZmEHA.3824@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm indexing a single field on a table with 10 million rows in it, its
> currently taking about 48 hours to build.
> I've been looking into performance bottlenecks on the system and see that
> this delay is being mainly caused by MSSearch writing massive amounts of
> data to the FT Index location. Since the finished index is only about
400mb
> I can only imagine that the population process is rewriting the data again
> and again to the disk as it is updated.
> Is this a fair assumption? If so is there anyway around it, maybe to tell
> MSSearch to use ram until the population is build then write the index to
> disk?
> Thanks
> Michael
>
|||Mike
What hardware is it running on?
Is the Full-Text Index on the same physical drive/lun as the database? It
should be possible to increase IO throughput by placing the Full-Text Index
on its own drive.
Are you using SCSI disks? If so are you using the faster 15k RPM disks as
apposed to 10k RPM disks.
How much RAM does the machine have?
How many processors and what spec are they?
Is SQL Server configured to leave some RAM free for the Operating System and
other Applications, i.e. if you have 2GB RAM is SQL configured to use max
1.5GB?
You might want to try SQL Server 2005 Beta 2. I understand that Full-Text
Search is much faster at indexing.
http://msdn.microsoft.com/library/en...asp?frame=true
Rich
"Hilary Cotter" wrote:
> No.
> There is the resource usage setting which may increase performance during
> the indexing process (sp_fulltext_service 'catalogname','resource_usgae',5)
> The more resources you can spend to improve the indexing process the better,
> normally you get the best bang for your buck by investing in the disk
> subsystem. For instance placing your catalogs on a RAID 10, 1, or 01 array.
> 01 is not considered to be a good choice as it does not offer the the
> protection 10 does.
> Also your database should be raid 5 depending on the level of write activity
> on it.
> What type of content are you indexing. You will get best performance by
> indexing text content as opposed to indexing documents in their native
> format (doc, xls, pdf, etc).
> Please review the full text white paper on SQL FTS performance at:
> http://support.microsoft.com/support...er.asp?GSSNB=1
> Save this document as a zip and extract it.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Mike Davies" <michael.davies@.synsoft.co.uk> wrote in message
> news:ezLfihZmEHA.3824@.TK2MSFTNGP12.phx.gbl...
> 400mb
>
>
|||Mike,
Yes, it is a fair assumption. You should review the SQL Server 2000 BOL
title "Full-text Search Recommendations" as well as the FTS Deployment white
paper. Are there workarounds? Yes. You can ensure that the disk drive that
your FT Catalog resides is separate from your database files as well as very
fast (>10,000 RPMs) and configured as RAID0 or RAID10. The Full Text
Indexing process is primarily I/O bound, but can also consume much memory
and CPU usage from time-to-time during Shadow Merges and at the end of the
process due to Master Merge.
Regards,
John
"Mike Davies" <michael.davies@.synsoft.co.uk> wrote in message
news:ezLfihZmEHA.3824@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm indexing a single field on a table with 10 million rows in it, its
> currently taking about 48 hours to build.
> I've been looking into performance bottlenecks on the system and see that
> this delay is being mainly caused by MSSearch writing massive amounts of
> data to the FT Index location. Since the finished index is only about
400mb
> I can only imagine that the population process is rewriting the data again
> and again to the disk as it is updated.
> Is this a fair assumption? If so is there anyway around it, maybe to tell
> MSSearch to use ram until the population is build then write the index to
> disk?
> Thanks
> Michael
>
|||Hi Rich,
It's running on a single P4 Prescott at 3Ghz with 4GB of RAM. SQL Server
currently has access to all system RAM but is sat at approx 1.7Gb during the
indexing process.
Both the FullText index and the SQL database are on separate physical SATA
drives, however because of an apparent incompatibility issue with Windows
and running the two SATA drives on their own bus they are both running
through the standard IDE bus, I imagine this is not doing the performance
any favours.
Thanks for the 2005 tip, I'll give that a try.
Can you offer any other suggestions to the setup?
Thanks
Mike
"Richard Yeo" <RichardYeo@.discussions.microsoft.com> wrote in message
news:692389A4-ED05-42C7-B645-982E7E9CE989@.microsoft.com...
> Mike
> What hardware is it running on?
> Is the Full-Text Index on the same physical drive/lun as the database? It
> should be possible to increase IO throughput by placing the Full-Text
Index
> on its own drive.
> Are you using SCSI disks? If so are you using the faster 15k RPM disks as
> apposed to 10k RPM disks.
> How much RAM does the machine have?
> How many processors and what spec are they?
> Is SQL Server configured to leave some RAM free for the Operating System
and
> other Applications, i.e. if you have 2GB RAM is SQL configured to use max
> 1.5GB?
> You might want to try SQL Server 2005 Beta 2. I understand that Full-Text
> Search is much faster at indexing.
>
http://msdn.microsoft.com/library/en...asp?frame=true[vbcol=seagreen]
> Rich
>
> "Hilary Cotter" wrote:
during[vbcol=seagreen]
'catalogname','resource_usgae',5)[vbcol=seagreen]
better,[vbcol=seagreen]
array.[vbcol=seagreen]
activity[vbcol=seagreen]
http://support.microsoft.com/support...er.asp?GSSNB=1[vbcol=seagreen]
that[vbcol=seagreen]
of[vbcol=seagreen]
again[vbcol=seagreen]
tell[vbcol=seagreen]
to[vbcol=seagreen]
|||Mike
Have you implemented the /3GB switch in the OS boot.ini? If not by default
2GB is set aside for apps and 2GB for OS. By using this switch 3GB is set
aside for apps and 1GB for OS. This probably explains why SQL Server is only
using 1.7GB RAM. It would probably be using more if you had the /3GB switch
set. Try setting /3GB switch, instruct SQL Server to use Physical RAM, and
set max memory for SQL Server to be 2.5GB. This will leave 0.5GB for other
apps including MSSearch (I think), file system caching, etc.
Even if you don't have the budget for a 2 proc SQL license I would always
recommend purchasing a second physical processor and configuring SQL Server
to only use one of the processors. This leaves the second processor free for
other applications, OS, remote administration.
You might want to try the manufacturers web-site regarding BIOS / firmware
updates for your SATA / IDE issue.
Rich
"Mike Davies" wrote:
> Hi Rich,
> It's running on a single P4 Prescott at 3Ghz with 4GB of RAM. SQL Server
> currently has access to all system RAM but is sat at approx 1.7Gb during the
> indexing process.
> Both the FullText index and the SQL database are on separate physical SATA
> drives, however because of an apparent incompatibility issue with Windows
> and running the two SATA drives on their own bus they are both running
> through the standard IDE bus, I imagine this is not doing the performance
> any favours.
> Thanks for the 2005 tip, I'll give that a try.
> Can you offer any other suggestions to the setup?
> Thanks
> Mike
>
> "Richard Yeo" <RichardYeo@.discussions.microsoft.com> wrote in message
> news:692389A4-ED05-42C7-B645-982E7E9CE989@.microsoft.com...
> Index
> and
> http://msdn.microsoft.com/library/en...asp?frame=true
> during
> 'catalogname','resource_usgae',5)
> better,
> array.
> activity
> http://support.microsoft.com/support...er.asp?GSSNB=1
> that
> of
> again
> tell
> to
>
>
|||Mike,
Rich, while the /3GB switch can help SQL Server 2000, it has no affect on
the "Microsoft Search" (mssearch.exe) FT Search engine as it can only use up
to a max of 512MB of physical RAM. However, you must set the resource_usage
value to 5 (dedicated) via sp_fulltext_service 'resource_usage', 5 and you
must have at least 512Mb of RAM reserved for the MSSearch service that is
NOT used by the OS or by SQL Server.
If you can purchase an additional CPU, you can set the MSSearch service's to
use one CPU and then use sp_configure to set the affinity of SQL Server to
that cpu to avoid cpu contention during heavy MSSearch CPU usage via the
following method:
You can launch the Tskmgr.exe from the AT command on the multi-proc server
where sql server resides, as follows:
at <current_time+1min> /interactive taskmgr.exe
when it launches, you can then set "cpu affinity" for the MSSearch service
to a cpu or set of cpu's not being used by SQL Server. Then you would use
sp_configure to set SQL Server's cpu affinity to the other cpu's, thus
preventing the cpu usage of mssearch from affecting your sql server
processing. Note, this only works on multi-processor servers.
Regards,
John
"Richard Yeo" <RichardYeo@.discussions.microsoft.com> wrote in message
news:2AEB4663-795F-48F6-BA68-DEAA0D1C4BD8@.microsoft.com...
> Mike
> Have you implemented the /3GB switch in the OS boot.ini? If not by default
> 2GB is set aside for apps and 2GB for OS. By using this switch 3GB is set
> aside for apps and 1GB for OS. This probably explains why SQL Server is
only
> using 1.7GB RAM. It would probably be using more if you had the /3GB
switch
> set. Try setting /3GB switch, instruct SQL Server to use Physical RAM, and
> set max memory for SQL Server to be 2.5GB. This will leave 0.5GB for other
> apps including MSSearch (I think), file system caching, etc.
> Even if you don't have the budget for a 2 proc SQL license I would always
> recommend purchasing a second physical processor and configuring SQL
Server
> to only use one of the processors. This leaves the second processor free
for[vbcol=seagreen]
> other applications, OS, remote administration.
> You might want to try the manufacturers web-site regarding BIOS / firmware
> updates for your SATA / IDE issue.
> Rich
>
> "Mike Davies" wrote:
Server[vbcol=seagreen]
the[vbcol=seagreen]
SATA[vbcol=seagreen]
Windows[vbcol=seagreen]
performance[vbcol=seagreen]
It[vbcol=seagreen]
as[vbcol=seagreen]
System[vbcol=seagreen]
max[vbcol=seagreen]
Full-Text[vbcol=seagreen]
http://msdn.microsoft.com/library/en...asp?frame=true[vbcol=seagreen]
disk[vbcol=seagreen]
the[vbcol=seagreen]
by[vbcol=seagreen]
native[vbcol=seagreen]
http://support.microsoft.com/support...er.asp?GSSNB=1[vbcol=seagreen]
its[vbcol=seagreen]
see[vbcol=seagreen]
amounts[vbcol=seagreen]
about[vbcol=seagreen]
data[vbcol=seagreen]
to[vbcol=seagreen]
index[vbcol=seagreen]
|||"Richard Yeo" <RichardYeo@.discussions.microsoft.com> wrote in message
news:2AEB4663-795F-48F6-BA68-DEAA0D1C4BD8@.microsoft.com...
> Mike
>
<SNIP>
> Even if you don't have the budget for a 2 proc SQL license I would always
> recommend purchasing a second physical processor and configuring SQL
> Server
> to only use one of the processors. This leaves the second processor free
> for
> other applications, OS, remote administration.
>
</SNIP>
Please be careful about that because, in general, you have to purchase a
processor license for every processor that the OS can see, whether that
processor is configured to run SQL Server or not. For more information,
please refer to the SQL Server 2000 Licensing FAQ web page at
http://www.microsoft.com/sql/howtobuy/faq.asp. The third question under
Licensing is reproduced below for your benefit.
Q. Do I have to license all of the processors in a server?
A. You only have to acquire licenses for processors that are
accessible to any copy of the operating system upon which SQL Server 2000 is
installed.
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
begin 666 ts.gif
J1TE&.#EA`0`!`( ``````/___R'Y! $`````+ `````!``$```(!1 `[
`
end
|||"Richard Yeo" <RichardYeo@.discussions.microsoft.com> wrote in message
news:2AEB4663-795F-48F6-BA68-DEAA0D1C4BD8@.microsoft.com...
> Mike
> Have you implemented the /3GB switch in the OS boot.ini? If not by default
> 2GB is set aside for apps and 2GB for OS. By using this switch 3GB is set
> aside for apps and 1GB for OS. This probably explains why SQL Server is
only
> using 1.7GB RAM. It would probably be using more if you had the /3GB
switch
> set. Try setting /3GB switch, instruct SQL Server to use Physical RAM, and
> set max memory for SQL Server to be 2.5GB. This will leave 0.5GB for other
> apps including MSSearch (I think), file system caching, etc.
> Even if you don't have the budget for a 2 proc SQL license I would always
> recommend purchasing a second physical processor and configuring SQL
Server
> to only use one of the processors. This leaves the second processor free
for
> other applications, OS, remote administration.
Unless thigns have changed, last I knew, MS didn't allow that.
|||Stephen
Thanks for clarifying that.
Obviously there is a difference between what the product is capable of and
what the licensing folk think the product is or should be capable of.
In this instance I feel the Product Team got it right. In the real world
people often have to run more than one application on a server and should not
be forced to buy additional licenses.
With the move in the industry to consolidate this could a lot of unnecessary
expense.
Lets hope this license issue is addressed in the next release of SQL Server
2005.
Rich
"Stephen Dybing [MSFT]" wrote:
> "Richard Yeo" <RichardYeo@.discussions.microsoft.com> wrote in message
> news:2AEB4663-795F-48F6-BA68-DEAA0D1C4BD8@.microsoft.com...
> <SNIP>
> </SNIP>
> Please be careful about that because, in general, you have to purchase a
> processor license for every processor that the OS can see, whether that
> processor is configured to run SQL Server or not. For more information,
> please refer to the SQL Server 2000 Licensing FAQ web page at
> http://www.microsoft.com/sql/howtobuy/faq.asp. The third question under
> Licensing is reproduced below for your benefit.
> Q. Do I have to license all of the processors in a server?
> A. You only have to acquire licenses for processors that are
> accessible to any copy of the operating system upon which SQL Server 2000 is
> installed.
>
>
> --
> Sincerely,
> Stephen Dybing
> This posting is provided "AS IS" with no warranties, and confers no rights
2012年2月26日星期日
Full recovery backup
Hi,
I've just discovered the 'Recovery model options' in properties page of a
table in Enterprise manager.
I checked books on line which stated that Full recovery mode ' can recover
from any point in time'. Are they referring to backup/restores? If so how can
this be done at any point in time. Otherwise, what are they referring to?
Thanks for your time in answering this question
Ant
Hello,
Point in time:-
A point in time recovery is restoring a database to a specified date and
time. When you have completed a point in time recovery, your database
will be in the state it was at the specific date and time you identified
when restoring your database. A point in time recovery is a method to
recover
your database to any point in time since the last database backup
Take a look into below URL for datails:-
http://www.databasejournal.com/features/mssql/article.php/3530616
http://www.devx.com/getHelpOn/10MinuteSolution/16532/1954?pf=true
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:DBC63D60-27F0-4B94-9E27-39C3D3A3355D@.microsoft.com...
> Hi,
> I've just discovered the 'Recovery model options' in properties page of a
> table in Enterprise manager.
> I checked books on line which stated that Full recovery mode ' can recover
> from any point in time'. Are they referring to backup/restores? If so how
> can
> this be done at any point in time. Otherwise, what are they referring to?
> Thanks for your time in answering this question
> Ant
|||Thank you for your time,
When I had a look at the restore dialog, the Point in time check box was
disabled. I checked the proertiers to find that it was set to full recovery
mode. Why would the check box not be enabled?
Thanks very much for your assistance
Ant
"Hari Prasad" wrote:
> Hello,
> Point in time:-
> A point in time recovery is restoring a database to a specified date and
> time. When you have completed a point in time recovery, your database
> will be in the state it was at the specific date and time you identified
> when restoring your database. A point in time recovery is a method to
> recover
> your database to any point in time since the last database backup
> Take a look into below URL for datails:-
> http://www.databasejournal.com/features/mssql/article.php/3530616
> http://www.devx.com/getHelpOn/10MinuteSolution/16532/1954?pf=true
>
> Thanks
> Hari
>
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:DBC63D60-27F0-4B94-9E27-39C3D3A3355D@.microsoft.com...
>
>
|||Hello,
That will get enabled onky when you restore a transaction log backup over a
non recovered database.
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:1A36B0F1-0D04-404F-8E16-2CF68903BE29@.microsoft.com...[vbcol=seagreen]
> Thank you for your time,
> When I had a look at the restore dialog, the Point in time check box was
> disabled. I checked the proertiers to find that it was set to full
> recovery
> mode. Why would the check box not be enabled?
> Thanks very much for your assistance
> Ant
> "Hari Prasad" wrote:
|||On Feb 5, 9:26 pm, Ant <A...@.discussions.microsoft.com> wrote:
> Hi,
> I've just discovered the 'Recovery model options' in properties page of a
> table in Enterprise manager.
> I checked books on line which stated that Full recovery mode ' can recover
> from any point in time'. Are they referring to backup/restores? If so how can
> this be done at any point in time. Otherwise, what are they referring to?
> Thanks for your time in answering this question
> Ant
Point-in-time recovery makes use of full backups and transaction log
backups to allow you to restore a database to virtually any point in
time. Consider the following backup scenario:
9:00pm - full backup
10:00pm - log backup
11:00pm - log backup
12:00am - log backup
1:00am - log backup
...
...
...
3:00pm - log backup
You discover that at 2:30pm, a malicious user updated a large portion
of your data with junk, and management has decided it's not safe to
continue with the data as-is, and they ask you to restore the database
to some point prior to the user's update. Without the log backups,
your only recourse would be to restore the 9:00pm full backup, losing
the entire day's activity.
However, with the log backups, you can restore the database literally
up to the minute of the bad update. You first restore the full
backup, specifying WITH NORECOVERY in the restore command. You then
restore the log backups from 10:00pm through 2:00pm, specifying WITH
NORECOVERY for each restore. Finally, you restore the 3:00pm log
backup, this time using the STOPAT clause on the restore command.
With STOPAT, you specify a specific time that you want the restore to
stop at. In this case, you tell it to STOPAT 2:29pm, just prior to
the bad update.
Does that help?
|||Brilliant! that makes it clear.
Thank you for your answer.
Ant
"Tracy McKibben" wrote:
> On Feb 5, 9:26 pm, Ant <A...@.discussions.microsoft.com> wrote:
> Point-in-time recovery makes use of full backups and transaction log
> backups to allow you to restore a database to virtually any point in
> time. Consider the following backup scenario:
> 9:00pm - full backup
> 10:00pm - log backup
> 11:00pm - log backup
> 12:00am - log backup
> 1:00am - log backup
> ...
> ...
> ...
> 3:00pm - log backup
> You discover that at 2:30pm, a malicious user updated a large portion
> of your data with junk, and management has decided it's not safe to
> continue with the data as-is, and they ask you to restore the database
> to some point prior to the user's update. Without the log backups,
> your only recourse would be to restore the 9:00pm full backup, losing
> the entire day's activity.
> However, with the log backups, you can restore the database literally
> up to the minute of the bad update. You first restore the full
> backup, specifying WITH NORECOVERY in the restore command. You then
> restore the log backups from 10:00pm through 2:00pm, specifying WITH
> NORECOVERY for each restore. Finally, you restore the 3:00pm log
> backup, this time using the STOPAT clause on the restore command.
> With STOPAT, you specify a specific time that you want the restore to
> stop at. In this case, you tell it to STOPAT 2:29pm, just prior to
> the bad update.
> Does that help?
>
I've just discovered the 'Recovery model options' in properties page of a
table in Enterprise manager.
I checked books on line which stated that Full recovery mode ' can recover
from any point in time'. Are they referring to backup/restores? If so how can
this be done at any point in time. Otherwise, what are they referring to?
Thanks for your time in answering this question
Ant
Hello,
Point in time:-
A point in time recovery is restoring a database to a specified date and
time. When you have completed a point in time recovery, your database
will be in the state it was at the specific date and time you identified
when restoring your database. A point in time recovery is a method to
recover
your database to any point in time since the last database backup
Take a look into below URL for datails:-
http://www.databasejournal.com/features/mssql/article.php/3530616
http://www.devx.com/getHelpOn/10MinuteSolution/16532/1954?pf=true
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:DBC63D60-27F0-4B94-9E27-39C3D3A3355D@.microsoft.com...
> Hi,
> I've just discovered the 'Recovery model options' in properties page of a
> table in Enterprise manager.
> I checked books on line which stated that Full recovery mode ' can recover
> from any point in time'. Are they referring to backup/restores? If so how
> can
> this be done at any point in time. Otherwise, what are they referring to?
> Thanks for your time in answering this question
> Ant
|||Thank you for your time,
When I had a look at the restore dialog, the Point in time check box was
disabled. I checked the proertiers to find that it was set to full recovery
mode. Why would the check box not be enabled?
Thanks very much for your assistance
Ant
"Hari Prasad" wrote:
> Hello,
> Point in time:-
> A point in time recovery is restoring a database to a specified date and
> time. When you have completed a point in time recovery, your database
> will be in the state it was at the specific date and time you identified
> when restoring your database. A point in time recovery is a method to
> recover
> your database to any point in time since the last database backup
> Take a look into below URL for datails:-
> http://www.databasejournal.com/features/mssql/article.php/3530616
> http://www.devx.com/getHelpOn/10MinuteSolution/16532/1954?pf=true
>
> Thanks
> Hari
>
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:DBC63D60-27F0-4B94-9E27-39C3D3A3355D@.microsoft.com...
>
>
|||Hello,
That will get enabled onky when you restore a transaction log backup over a
non recovered database.
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:1A36B0F1-0D04-404F-8E16-2CF68903BE29@.microsoft.com...[vbcol=seagreen]
> Thank you for your time,
> When I had a look at the restore dialog, the Point in time check box was
> disabled. I checked the proertiers to find that it was set to full
> recovery
> mode. Why would the check box not be enabled?
> Thanks very much for your assistance
> Ant
> "Hari Prasad" wrote:
|||On Feb 5, 9:26 pm, Ant <A...@.discussions.microsoft.com> wrote:
> Hi,
> I've just discovered the 'Recovery model options' in properties page of a
> table in Enterprise manager.
> I checked books on line which stated that Full recovery mode ' can recover
> from any point in time'. Are they referring to backup/restores? If so how can
> this be done at any point in time. Otherwise, what are they referring to?
> Thanks for your time in answering this question
> Ant
Point-in-time recovery makes use of full backups and transaction log
backups to allow you to restore a database to virtually any point in
time. Consider the following backup scenario:
9:00pm - full backup
10:00pm - log backup
11:00pm - log backup
12:00am - log backup
1:00am - log backup
...
...
...
3:00pm - log backup
You discover that at 2:30pm, a malicious user updated a large portion
of your data with junk, and management has decided it's not safe to
continue with the data as-is, and they ask you to restore the database
to some point prior to the user's update. Without the log backups,
your only recourse would be to restore the 9:00pm full backup, losing
the entire day's activity.
However, with the log backups, you can restore the database literally
up to the minute of the bad update. You first restore the full
backup, specifying WITH NORECOVERY in the restore command. You then
restore the log backups from 10:00pm through 2:00pm, specifying WITH
NORECOVERY for each restore. Finally, you restore the 3:00pm log
backup, this time using the STOPAT clause on the restore command.
With STOPAT, you specify a specific time that you want the restore to
stop at. In this case, you tell it to STOPAT 2:29pm, just prior to
the bad update.
Does that help?
|||Brilliant! that makes it clear.
Thank you for your answer.
Ant
"Tracy McKibben" wrote:
> On Feb 5, 9:26 pm, Ant <A...@.discussions.microsoft.com> wrote:
> Point-in-time recovery makes use of full backups and transaction log
> backups to allow you to restore a database to virtually any point in
> time. Consider the following backup scenario:
> 9:00pm - full backup
> 10:00pm - log backup
> 11:00pm - log backup
> 12:00am - log backup
> 1:00am - log backup
> ...
> ...
> ...
> 3:00pm - log backup
> You discover that at 2:30pm, a malicious user updated a large portion
> of your data with junk, and management has decided it's not safe to
> continue with the data as-is, and they ask you to restore the database
> to some point prior to the user's update. Without the log backups,
> your only recourse would be to restore the 9:00pm full backup, losing
> the entire day's activity.
> However, with the log backups, you can restore the database literally
> up to the minute of the bad update. You first restore the full
> backup, specifying WITH NORECOVERY in the restore command. You then
> restore the log backups from 10:00pm through 2:00pm, specifying WITH
> NORECOVERY for each restore. Finally, you restore the 3:00pm log
> backup, this time using the STOPAT clause on the restore command.
> With STOPAT, you specify a specific time that you want the restore to
> stop at. In this case, you tell it to STOPAT 2:29pm, just prior to
> the bad update.
> Does that help?
>
Full population very slow
I've got SQL2000 Enterprise edition running on windows 2000 advanced server,
we've recently upgraded from SQL sp2 to sp3a, this caused a catalog rebuild
but full population is taking forever. The column with Full text search
enabled is of image type, there are 2.5 million record in the table, about
7GB in size.
I've started full population 2 days ago and it's still showing "Population
in progress", when I view the property page of the catalog, I can see ~
2300000 items, catalog size: 190MB, unique key count: 362586. It appears to
be processing 1000 items per minute.
The server has 8 CPUs, 8GB RAM with /PAE and /3GB enabled, heavy duty SAN
disks.
mssdmn.exe is the most active process, it's using between 30-40 CPU on one
processor only, disk utilization is minimal and plenty of physical memory
available for use. This process is using 6 threads, 10MB RAM, mssearch.exe is
using 50MB RAM and 16 threads.
Is there any way of speeding up population?
Thanks,
Ad
set sp_fulltext_service 'resource_usage', 5
"Ad" <Ad@.discussions.microsoft.com> wrote in message
news:B94E0F3A-7655-4D8A-8605-EA1BC43E6329@.microsoft.com...
> I've got SQL2000 Enterprise edition running on windows 2000 advanced
> server,
> we've recently upgraded from SQL sp2 to sp3a, this caused a catalog
> rebuild
> but full population is taking forever. The column with Full text search
> enabled is of image type, there are 2.5 million record in the table, about
> 7GB in size.
> I've started full population 2 days ago and it's still showing "Population
> in progress", when I view the property page of the catalog, I can see ~
> 2300000 items, catalog size: 190MB, unique key count: 362586. It appears
> to
> be processing 1000 items per minute.
> The server has 8 CPUs, 8GB RAM with /PAE and /3GB enabled, heavy duty SAN
> disks.
> mssdmn.exe is the most active process, it's using between 30-40 CPU on one
> processor only, disk utilization is minimal and plenty of physical memory
> available for use. This process is using 6 threads, 10MB RAM, mssearch.exe
> is
> using 50MB RAM and 16 threads.
> Is there any way of speeding up population?
> Thanks,
> Ad
>
|||Ad,
There are a couple of thing you can do to improve the FT Indexing
performance without stopping & re-locating the FT Catalog. You can set the
MSSearch service's to use one CPU and then use sp_configure to set the
affinity of SQL Server to that cpu to avoid cpu contention during heavy
MSSearch CPU usage via the following method - launch the Tskmgr.exe from the
AT command on the multi-proc server where sql server resides, as follows:
at <current_time+1min> /interactive taskmgr.exe
when it launches, you can then set "cpu affinity" for the MSSearch service
to a cpu or set of cpu's not being used by SQL Server. Then you would use
sp_configure to set SQL Server's cpu affinity to the other cpu's, thus
preventing the cpu usage of mssearch from affecting your sql server
processing. Note, this only works on multi-processor servers and is a
secured method for achieving this feature.
Additionally, as your server has lots of RAM, you can use
sp_fulltext_service 'resource_usage', 5 to set the MSSearch service to 5
(dedicated), and it will use the max amount of RAM (512Mb), but only if the
RAM is not being used by either the OS or by SQL Server.
Finally, I'd highly recommend that you review all the resources "SQL Server
2000 Full-Text Search Resources and Links" at:
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.entry
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Ad" <Ad@.discussions.microsoft.com> wrote in message
news:B94E0F3A-7655-4D8A-8605-EA1BC43E6329@.microsoft.com...
> I've got SQL2000 Enterprise edition running on windows 2000 advanced
server,
> we've recently upgraded from SQL sp2 to sp3a, this caused a catalog
rebuild
> but full population is taking forever. The column with Full text search
> enabled is of image type, there are 2.5 million record in the table, about
> 7GB in size.
> I've started full population 2 days ago and it's still showing
"Population
> in progress", when I view the property page of the catalog, I can see ~
> 2300000 items, catalog size: 190MB, unique key count: 362586. It appears
to
> be processing 1000 items per minute.
> The server has 8 CPUs, 8GB RAM with /PAE and /3GB enabled, heavy duty SAN
> disks.
> mssdmn.exe is the most active process, it's using between 30-40 CPU on one
> processor only, disk utilization is minimal and plenty of physical memory
> available for use. This process is using 6 threads, 10MB RAM, mssearch.exe
is
> using 50MB RAM and 16 threads.
> Is there any way of speeding up population?
> Thanks,
> Ad
>
|||sp_fulltext_service 'resource_usage', 5 , did not make any difference, does
it need stop and re-start of MSSearch service?
Thanks,
Ad
|||Ad,
I didn't think it would make much difference. However, where you get the
biggest bang for your buck, is ensuring that the FT Catalog is on a separate
disk drive from your database files. Can you stop the Full Population, drop
and re-create the FT Catalog on separate disk array or better yet, on a
separate disk controller? See SQL 2000 BOL title "Full-text Search
Recommendations" for more info on this.
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Ad" <Ad@.discussions.microsoft.com> wrote in message
news:B72E38AF-4FFF-4F60-9F84-BF56213AC220@.microsoft.com...
> sp_fulltext_service 'resource_usage', 5 , did not make any difference,
does
> it need stop and re-start of MSSearch service?
> Thanks,
> Ad
>
>
|||The FT Catalog is on a dedicated SAN disk which is almost idle, in fact the
whole disk is cached. So there are plenty of resources on the server that FT
can't utilize, this leads me to believe FT has scalability issues.
Regards,
Ad
"John Kane" wrote:
> Ad,
> I didn't think it would make much difference. However, where you get the
> biggest bang for your buck, is ensuring that the FT Catalog is on a separate
> disk drive from your database files. Can you stop the Full Population, drop
> and re-create the FT Catalog on separate disk array or better yet, on a
> separate disk controller? See SQL 2000 BOL title "Full-text Search
> Recommendations" for more info on this.
> Regards,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Ad" <Ad@.discussions.microsoft.com> wrote in message
> news:B72E38AF-4FFF-4F60-9F84-BF56213AC220@.microsoft.com...
> does
>
>
we've recently upgraded from SQL sp2 to sp3a, this caused a catalog rebuild
but full population is taking forever. The column with Full text search
enabled is of image type, there are 2.5 million record in the table, about
7GB in size.
I've started full population 2 days ago and it's still showing "Population
in progress", when I view the property page of the catalog, I can see ~
2300000 items, catalog size: 190MB, unique key count: 362586. It appears to
be processing 1000 items per minute.
The server has 8 CPUs, 8GB RAM with /PAE and /3GB enabled, heavy duty SAN
disks.
mssdmn.exe is the most active process, it's using between 30-40 CPU on one
processor only, disk utilization is minimal and plenty of physical memory
available for use. This process is using 6 threads, 10MB RAM, mssearch.exe is
using 50MB RAM and 16 threads.
Is there any way of speeding up population?
Thanks,
Ad
set sp_fulltext_service 'resource_usage', 5
"Ad" <Ad@.discussions.microsoft.com> wrote in message
news:B94E0F3A-7655-4D8A-8605-EA1BC43E6329@.microsoft.com...
> I've got SQL2000 Enterprise edition running on windows 2000 advanced
> server,
> we've recently upgraded from SQL sp2 to sp3a, this caused a catalog
> rebuild
> but full population is taking forever. The column with Full text search
> enabled is of image type, there are 2.5 million record in the table, about
> 7GB in size.
> I've started full population 2 days ago and it's still showing "Population
> in progress", when I view the property page of the catalog, I can see ~
> 2300000 items, catalog size: 190MB, unique key count: 362586. It appears
> to
> be processing 1000 items per minute.
> The server has 8 CPUs, 8GB RAM with /PAE and /3GB enabled, heavy duty SAN
> disks.
> mssdmn.exe is the most active process, it's using between 30-40 CPU on one
> processor only, disk utilization is minimal and plenty of physical memory
> available for use. This process is using 6 threads, 10MB RAM, mssearch.exe
> is
> using 50MB RAM and 16 threads.
> Is there any way of speeding up population?
> Thanks,
> Ad
>
|||Ad,
There are a couple of thing you can do to improve the FT Indexing
performance without stopping & re-locating the FT Catalog. You can set the
MSSearch service's to use one CPU and then use sp_configure to set the
affinity of SQL Server to that cpu to avoid cpu contention during heavy
MSSearch CPU usage via the following method - launch the Tskmgr.exe from the
AT command on the multi-proc server where sql server resides, as follows:
at <current_time+1min> /interactive taskmgr.exe
when it launches, you can then set "cpu affinity" for the MSSearch service
to a cpu or set of cpu's not being used by SQL Server. Then you would use
sp_configure to set SQL Server's cpu affinity to the other cpu's, thus
preventing the cpu usage of mssearch from affecting your sql server
processing. Note, this only works on multi-processor servers and is a
secured method for achieving this feature.
Additionally, as your server has lots of RAM, you can use
sp_fulltext_service 'resource_usage', 5 to set the MSSearch service to 5
(dedicated), and it will use the max amount of RAM (512Mb), but only if the
RAM is not being used by either the OS or by SQL Server.
Finally, I'd highly recommend that you review all the resources "SQL Server
2000 Full-Text Search Resources and Links" at:
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.entry
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Ad" <Ad@.discussions.microsoft.com> wrote in message
news:B94E0F3A-7655-4D8A-8605-EA1BC43E6329@.microsoft.com...
> I've got SQL2000 Enterprise edition running on windows 2000 advanced
server,
> we've recently upgraded from SQL sp2 to sp3a, this caused a catalog
rebuild
> but full population is taking forever. The column with Full text search
> enabled is of image type, there are 2.5 million record in the table, about
> 7GB in size.
> I've started full population 2 days ago and it's still showing
"Population
> in progress", when I view the property page of the catalog, I can see ~
> 2300000 items, catalog size: 190MB, unique key count: 362586. It appears
to
> be processing 1000 items per minute.
> The server has 8 CPUs, 8GB RAM with /PAE and /3GB enabled, heavy duty SAN
> disks.
> mssdmn.exe is the most active process, it's using between 30-40 CPU on one
> processor only, disk utilization is minimal and plenty of physical memory
> available for use. This process is using 6 threads, 10MB RAM, mssearch.exe
is
> using 50MB RAM and 16 threads.
> Is there any way of speeding up population?
> Thanks,
> Ad
>
|||sp_fulltext_service 'resource_usage', 5 , did not make any difference, does
it need stop and re-start of MSSearch service?
Thanks,
Ad
|||Ad,
I didn't think it would make much difference. However, where you get the
biggest bang for your buck, is ensuring that the FT Catalog is on a separate
disk drive from your database files. Can you stop the Full Population, drop
and re-create the FT Catalog on separate disk array or better yet, on a
separate disk controller? See SQL 2000 BOL title "Full-text Search
Recommendations" for more info on this.
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Ad" <Ad@.discussions.microsoft.com> wrote in message
news:B72E38AF-4FFF-4F60-9F84-BF56213AC220@.microsoft.com...
> sp_fulltext_service 'resource_usage', 5 , did not make any difference,
does
> it need stop and re-start of MSSearch service?
> Thanks,
> Ad
>
>
|||The FT Catalog is on a dedicated SAN disk which is almost idle, in fact the
whole disk is cached. So there are plenty of resources on the server that FT
can't utilize, this leads me to believe FT has scalability issues.
Regards,
Ad
"John Kane" wrote:
> Ad,
> I didn't think it would make much difference. However, where you get the
> biggest bang for your buck, is ensuring that the FT Catalog is on a separate
> disk drive from your database files. Can you stop the Full Population, drop
> and re-create the FT Catalog on separate disk array or better yet, on a
> separate disk controller? See SQL 2000 BOL title "Full-text Search
> Recommendations" for more info on this.
> Regards,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Ad" <Ad@.discussions.microsoft.com> wrote in message
> news:B72E38AF-4FFF-4F60-9F84-BF56213AC220@.microsoft.com...
> does
>
>
Full Log Setting
I've confused myself. If you're doing log backups and you
have of course the database option set to FULL, then how
can your logs ever shrink? I understand that after the
log backup happens technically the logs can shrink w/o
risk of losing data in the logs. But what actually
shrinks these logs? Autoshrink won't do it, will it? Do
you have run something manuall?
Please refer to Tibor's link
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
Jeff Duncan
MCDBA, MCSE+I
"bzkdfl" <anonymous@.discussions.microsoft.com> wrote in message
news:8ed101c432de$bb0a7610$a101280a@.phx.gbl...
> I've confused myself. If you're doing log backups and you
> have of course the database option set to FULL, then how
> can your logs ever shrink? I understand that after the
> log backup happens technically the logs can shrink w/o
> risk of losing data in the logs. But what actually
> shrinks these logs? Autoshrink won't do it, will it? Do
> you have run something manuall?
>
|||Thx for the link, but I'm getting more confused (which is
probably a good sign):
If I'm doing log backups and have FULL on, can I ever get
these logs shrunk w/o using Tibor's specialized shrink
code?
>--Original Message--
>Please refer to Tibor's link
>http://www.karaszi.com/sqlserver/info_dont_shrink.asp
>
>--
>Jeff Duncan
>MCDBA, MCSE+I
>"bzkdfl" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:8ed101c432de$bb0a7610$a101280a@.phx.gbl...
you[vbcol=seagreen]
Do
>
>.
>
|||There's no shrink code on that page. The code is only there to prove that when you shrink a data file, the
same amount of data is logged to the tlog.
If you do both full and log backup, then the log backups will empty the files. To shrink the files, you need
to either have autoshrink, or execute DBCC SHRINKFILE or DBCC SHRINKDB. But please read the section about
virtual log files and the log file has to be empty at the end.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
<anonymous@.discussions.microsoft.com> wrote in message news:905a01c432ed$d176ac70$a301280a@.phx.gbl...[vbcol=seagreen]
> Thx for the link, but I'm getting more confused (which is
> probably a good sign):
> If I'm doing log backups and have FULL on, can I ever get
> these logs shrunk w/o using Tibor's specialized shrink
> code?
> message
> you
> Do
have of course the database option set to FULL, then how
can your logs ever shrink? I understand that after the
log backup happens technically the logs can shrink w/o
risk of losing data in the logs. But what actually
shrinks these logs? Autoshrink won't do it, will it? Do
you have run something manuall?
Please refer to Tibor's link
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
Jeff Duncan
MCDBA, MCSE+I
"bzkdfl" <anonymous@.discussions.microsoft.com> wrote in message
news:8ed101c432de$bb0a7610$a101280a@.phx.gbl...
> I've confused myself. If you're doing log backups and you
> have of course the database option set to FULL, then how
> can your logs ever shrink? I understand that after the
> log backup happens technically the logs can shrink w/o
> risk of losing data in the logs. But what actually
> shrinks these logs? Autoshrink won't do it, will it? Do
> you have run something manuall?
>
|||Thx for the link, but I'm getting more confused (which is
probably a good sign):
If I'm doing log backups and have FULL on, can I ever get
these logs shrunk w/o using Tibor's specialized shrink
code?
>--Original Message--
>Please refer to Tibor's link
>http://www.karaszi.com/sqlserver/info_dont_shrink.asp
>
>--
>Jeff Duncan
>MCDBA, MCSE+I
>"bzkdfl" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:8ed101c432de$bb0a7610$a101280a@.phx.gbl...
you[vbcol=seagreen]
Do
>
>.
>
|||There's no shrink code on that page. The code is only there to prove that when you shrink a data file, the
same amount of data is logged to the tlog.
If you do both full and log backup, then the log backups will empty the files. To shrink the files, you need
to either have autoshrink, or execute DBCC SHRINKFILE or DBCC SHRINKDB. But please read the section about
virtual log files and the log file has to be empty at the end.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
<anonymous@.discussions.microsoft.com> wrote in message news:905a01c432ed$d176ac70$a301280a@.phx.gbl...[vbcol=seagreen]
> Thx for the link, but I'm getting more confused (which is
> probably a good sign):
> If I'm doing log backups and have FULL on, can I ever get
> these logs shrunk w/o using Tibor's specialized shrink
> code?
> message
> you
> Do
2012年2月24日星期五
full and diff backup - sql2005
hello...
I've got problem with backups on sql2005
script make backup at saturday with full backup and at monday script
runs diff backup. On one of few sqlservers2005 diff backups doesn't
run because because error: "a current database backup does not
exist" . But I know full backup was executed corectly on saturday.
In backup file , that is used for diff backup exists record for
saturday's full backup. But server can't recognize full backup for
db.
When I run full backup again, it run sucessfully and diff backup
too.
This error is only on one server (named instance), other servers are
ok.
Any hints for this mystery ?
Thanks for any help.
Well, try doing a manual full and see if it resolves.
I'm not sure if the backup is looking at logs in msdb, or if it writes a
flag, but a manual full will deal with both. In short, I wouldn't put too
much energy into it, unless the simple solution doesn't work ...
<oto.pakozdy@.gmail.com> wrote in message
news:2398bfcd-fa45-4a4f-ade4-915159aebbea@.d21g2000prf.googlegroups.com...
> hello...
> I've got problem with backups on sql2005
> script make backup at saturday with full backup and at monday script
> runs diff backup. On one of few sqlservers2005 diff backups doesn't
> run because because error: "a current database backup does not
> exist" . But I know full backup was executed corectly on saturday.
> In backup file , that is used for diff backup exists record for
> saturday's full backup. But server can't recognize full backup for
> db.
> When I run full backup again, it run sucessfully and diff backup
> too.
> This error is only on one server (named instance), other servers are
> ok.
> Any hints for this mystery ?
> Thanks for any help.
|||On 27. Nov, 16:57 h., "Jay" <s...@.nospam.org> wrote:
> Well, try doing a manual full and see if it resolves.
> I'm not sure if the backup is looking at logs in msdb, or if it writes a
> flag, but a manual full will deal with both. In short, I wouldn't put too
> much energy into it, unless the simple solution doesn't work ...
> <oto.pako...@.gmail.com> wrote in message
> news:2398bfcd-fa45-4a4f-ade4-915159aebbea@.d21g2000prf.googlegroups.com...
>
>
>
>
> - Zobrazi citovan text -
Now I altered db_backupoperator role for MSDB by user running backup
script. soi I will see if it help.
|||I usually run this to get the history of backups for a specific database.
Try it and see what information it returns. Chage database name and pay
atention to the type column.
select backup_set_id, bs.media_set_id, database_creation_date,
backup_start_date, backup_finish_date,
type, database_name, physical_device_name
from backupset bs join backupmediafamily bmf
on bs.media_set_id = bmf.media_set_id
where database_name = 'your_db'
-- and type <> 'L'
order by backup_start_date desc
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"oto.pakozdy@.gmail.com" wrote:
> hello...
> I've got problem with backups on sql2005
> script make backup at saturday with full backup and at monday script
> runs diff backup. On one of few sqlservers2005 diff backups doesn't
> run because because error: "a current database backup does not
> exist" . But I know full backup was executed corectly on saturday.
> In backup file , that is used for diff backup exists record for
> saturday's full backup. But server can't recognize full backup for
> db.
> When I run full backup again, it run sucessfully and diff backup
> too.
> This error is only on one server (named instance), other servers are
> ok.
> Any hints for this mystery ?
> Thanks for any help.
>
|||I found solution on google
Problem is MSSQL SP2 changed startup state SQL Server VSS Writer from
manual to started . So when NTbackup run backup it breaks backup
chain because vss make own db backups.
So when I turn off SQL VSS Writer everyting works fine.
ntbackup backup files without backuping db and sql backup chain
full,diff is not break.
Thank for your hints.
|||Hello, I think i meet the same problem. but you said you have find the
resolution on google. Could you please give me the link that describe the
problem?
Thanks,
Gil
"oto.pakozdy@.gmail.com" wrote:
> I found solution on google
> Problem is MSSQL SP2 changed startup state SQL Server VSS Writer from
> manual to started . So when NTbackup run backup it breaks backup
> chain because vss make own db backups.
> So when I turn off SQL VSS Writer everyting works fine.
> ntbackup backup files without backuping db and sql backup chain
> full,diff is not break.
>
> Thank for your hints.
>
I've got problem with backups on sql2005
script make backup at saturday with full backup and at monday script
runs diff backup. On one of few sqlservers2005 diff backups doesn't
run because because error: "a current database backup does not
exist" . But I know full backup was executed corectly on saturday.
In backup file , that is used for diff backup exists record for
saturday's full backup. But server can't recognize full backup for
db.
When I run full backup again, it run sucessfully and diff backup
too.
This error is only on one server (named instance), other servers are
ok.
Any hints for this mystery ?
Thanks for any help.
Well, try doing a manual full and see if it resolves.
I'm not sure if the backup is looking at logs in msdb, or if it writes a
flag, but a manual full will deal with both. In short, I wouldn't put too
much energy into it, unless the simple solution doesn't work ...
<oto.pakozdy@.gmail.com> wrote in message
news:2398bfcd-fa45-4a4f-ade4-915159aebbea@.d21g2000prf.googlegroups.com...
> hello...
> I've got problem with backups on sql2005
> script make backup at saturday with full backup and at monday script
> runs diff backup. On one of few sqlservers2005 diff backups doesn't
> run because because error: "a current database backup does not
> exist" . But I know full backup was executed corectly on saturday.
> In backup file , that is used for diff backup exists record for
> saturday's full backup. But server can't recognize full backup for
> db.
> When I run full backup again, it run sucessfully and diff backup
> too.
> This error is only on one server (named instance), other servers are
> ok.
> Any hints for this mystery ?
> Thanks for any help.
|||On 27. Nov, 16:57 h., "Jay" <s...@.nospam.org> wrote:
> Well, try doing a manual full and see if it resolves.
> I'm not sure if the backup is looking at logs in msdb, or if it writes a
> flag, but a manual full will deal with both. In short, I wouldn't put too
> much energy into it, unless the simple solution doesn't work ...
> <oto.pako...@.gmail.com> wrote in message
> news:2398bfcd-fa45-4a4f-ade4-915159aebbea@.d21g2000prf.googlegroups.com...
>
>
>
>
> - Zobrazi citovan text -
Now I altered db_backupoperator role for MSDB by user running backup
script. soi I will see if it help.
|||I usually run this to get the history of backups for a specific database.
Try it and see what information it returns. Chage database name and pay
atention to the type column.
select backup_set_id, bs.media_set_id, database_creation_date,
backup_start_date, backup_finish_date,
type, database_name, physical_device_name
from backupset bs join backupmediafamily bmf
on bs.media_set_id = bmf.media_set_id
where database_name = 'your_db'
-- and type <> 'L'
order by backup_start_date desc
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"oto.pakozdy@.gmail.com" wrote:
> hello...
> I've got problem with backups on sql2005
> script make backup at saturday with full backup and at monday script
> runs diff backup. On one of few sqlservers2005 diff backups doesn't
> run because because error: "a current database backup does not
> exist" . But I know full backup was executed corectly on saturday.
> In backup file , that is used for diff backup exists record for
> saturday's full backup. But server can't recognize full backup for
> db.
> When I run full backup again, it run sucessfully and diff backup
> too.
> This error is only on one server (named instance), other servers are
> ok.
> Any hints for this mystery ?
> Thanks for any help.
>
|||I found solution on google
Problem is MSSQL SP2 changed startup state SQL Server VSS Writer from
manual to started . So when NTbackup run backup it breaks backup
chain because vss make own db backups.
So when I turn off SQL VSS Writer everyting works fine.
ntbackup backup files without backuping db and sql backup chain
full,diff is not break.
Thank for your hints.
|||Hello, I think i meet the same problem. but you said you have find the
resolution on google. Could you please give me the link that describe the
problem?
Thanks,
Gil
"oto.pakozdy@.gmail.com" wrote:
> I found solution on google
> Problem is MSSQL SP2 changed startup state SQL Server VSS Writer from
> manual to started . So when NTbackup run backup it breaks backup
> chain because vss make own db backups.
> So when I turn off SQL VSS Writer everyting works fine.
> ntbackup backup files without backuping db and sql backup chain
> full,diff is not break.
>
> Thank for your hints.
>
订阅：
博文 (Atom)