Full Text Searching

I am trying to run a full text serach on one field, a Varchar 2000.
say the field contains:
(before you break the seal of your new product box, please be careful to read all the instructions) ...for example
I search for keywords that may be in this field
Like:
product box
seal
instructions
and this row is included in the result set

but I would like to leave out words like all pronouns and 'a' and 'I' ...words that aren't going to matter to the search.

Does someone know where I can stgart in doing this full text searching?

Thanks,
EricWell I still haven't found much on this
Got the following articles
http://www.freevbcode.com/ShowCode.asp?ID=4224
(zip file is empty)
and http://www.microsoft.com/sql/evaluation/features/fulltext.asp (just says nothing really)

Do anyone know how this full text search works... an example perhaps?

Would be greatly appreciated.
Thanks,
Eric|||Can someone tell me please where this is wrong?
sSQL.Append("and (sr.description_of_problem = isnull(@.description, FREETEXT(sr.description_of_problem, @.description)) or sr.description_of_problem is null) ")|||look into"noise words" and"filters" in the full text search problem.

Full Text Search 'Near'

using SQL server 2005 I have a table which has full text search index enabled

when I run a search using the 'Near' option it returns rows which meet the criteria, how ever the criteria are not 'near' each other they are several hundred words apart.

What is the problem

How near is near

thanks in advance

Glyn

Hi,

The NEAR operator is used to find words which are in close proximity to one another. For example, consider the following three rows in a table:

TextValue

==========

The product being used is SQL Server 2005 Enterprise Edition

We are using full text search in SQL Server 2005 Enterprise Edition

We are performing a test with full text search in SQL Server 2005 Enterprise Edition

Now if I have a query of the following type:

select TextValue

from dbo.sample_tbl

where contains (TextValue, 'search NEAR Server')

This will return the 2nd and the 3rd rows as both the words are approximately close to each other. The NEAR operator returns those rows which have both the words approximately close to each other. When several proximity terms are chained, all proximity terms must be near each other.

|||

Thanks but the search I have is not working

I have a text string

'FTS_STRING_SAMPLE1 can control instantiation to suit the system environment. Relying on delayed instantiation minimizes the amount of memory required by the application, although it might trigger many server requests when properties are referenced.

Instance classes, objects that represent real database objects, can exist in three levels of instantiation. These are minimal-instantiated (only the minimal required properties are read in one block), partially instantiated (all the properties that use a relatively large amount of memory are read in one block), and fully instantiated. Un-instantiated and fully instantiated are the traditional states of instantiation. The partially instantiated state increases efficiency because a partially instantiated object does not contain values for the full set of object properties. Partial instantiation is the default state for an object that is not directly referenced. When one of these properties is referenced, a fault is generated that prompts a full instantiation of the object FTS_STRING_SAMPLE2.'

SELECT *
FROM FTS_TEST_TABLE
WHERE CONTAINS(FTS_TEST_TEXT, 'FTS_STRING_SAMPLE1 NEAR FTS_STRING_SAMPLE2');

It returns the row, however we believe it shouldn't

Glyn

Full Text Query Starts Slow

I've created a FullTextCatalog on one of my Databases and added a full text index on one of my text columns.

The first time I run a query against the data it takes roughly 40-45 seconds to return data. After that it blazes and runs in under a second. If I don't query it for 20-30 minutes, it will take 40-45 seconds again and then fly until the next break.

Is there a configuration setting somewhere that I'm missing on this? Currently the Index is only about 5MB so it should take that long to read in when I'm querying it.

I don't think it has anything to do with size because the actual return can contain anywhere from 10-80K rows and the speed is about the same.

I'm using Standard edition on a 2003 Standard Server if that plays into the potential problem at all. We're currently downloading and installing the new Service Pack to see if that fixes it, but for some reason I'm not holding my breath. I'm assuming that there is something we need to change in the configuration.

Let me know if I'm missing any pertinent information. Thanks.http://www.sql-server-performance.com/full_text_search.asp & http://www.sql-server-performance.com/tb_search_optimization.asp for your action & information|||I have verified that nothing is being populated at the time I'm running these queries, so the first one should be the problem. We'll be loading 500,000 records per day once in the morning. I have the Catalog set to manual population, so that I re-populate after the load has completed.

In 2005 I didn't think that it had to go out to the MS Search searvice? I thought it was now all contained within SQL Server. In any regards, this is happening exactly the same way regard less of the query or amount of data returned. A call just to ContainsTable() with no other joins that returns 3 or 70.000 rows takes roughly the same amount of time.sql

full text problem

I have created a catalog from a table that contains only english with <html> tags. When i run a query against it i get

Server: Msg 7619, Level 16, State 1, Line 1
Langauge database/cache file could not be found.

I have read that setting full text to use the neutral word breaker may solve this problem, question is "How do i set it to use the neutral word breaker"

ThanksThe following articles may be helpful:

Article q246701 (http://support.microsoft.com/default.aspx?scid=kb;EN-US;q246701)
Article q271818 (http://support.microsoft.com/default.aspx?scid=kb;EN-US;q271818)

Full text issue

Hello,

I run a music related website (www.LowestCostMusic.com) and I'm using the SQL2000 full text search engine.

If you go to the site via this link... It would be searching on the phrase 'the way it is'

http://www.lowestcostmusic.com/search2.asp?search=the+way+it+is

Which is all noise words... yet could be a title of some sheet music that we carry. I need to know how to build the SQL full text catalog to allow this search to work properly... Any ideas? Thanks!

-MattMatt,

Thanks for using Microsoft SQL Server 2000 and Full-Text Search on your web site. This should help:

You need to update your noise words list and then repopulate your Full-Text index in order to accept searches on titles such as "the way it is" . You may find your noise word files under:

\Program Files\Microsoft SQL Server\MSSQL$INSTANCENAME\Ftdata\SQLServer\Config\ noise.enu (for US English)

You may add words to be excluded from the index, and likewise remove words that you would like included.

Once you are satisfied with changes, restart MSSearch service and you will need to repopulate your catalogs.

This should fix your problem right away.

All the best,
--andrew

Full text index query plans

I have a table with a full-text index on a given column. If I run the
following:
declare @.s varchar(100)
select @.s = 'fast'
select * from tblHannahRES where contains(RES_SER_TI, @.s)
select * from tblHannahRES where contains(RES_SER_TI, 'fast')
on my SQL2000/SP4 box I get substantially different access times, which
is unlike what I would get with a normal index i.e. performing the SQL
below (where the index is on ResID) yields identical plans and access
times:
declare @.i int
select @.i = 1463440
select * from tblHannahRES where ResID = @.i
select * from tblHannahRES where ResID = 1463440
With the full-text indices I get the plans shown below and the second
query is consistenly 6X faster than the first.
One thing I notice is that the remote scan itself costs 0.36 vs 0.06
though both generate the same number of rows. the second thing to
notice is that the first case incurs an extra sorting step (which costs
about 0.02 points). I'm not sure why this should be the case. Can
anyone elucidate on the issue here?
TIA - e
select * from tblHannahRES where contains(RES_SER_TI, @.s)
|--Nested Loops(Inner Join, OUTER
REFERENCES:([FULLTEXT:tblHannahRES].[KEY]) WITH PREFETCH)
|--Sort(ORDER BY:([FULLTEXT:tblHannahRES].[KEY] ASC))
| |--Remote Scan(OBJECT:(CONTAINS))
|--Clustered Index
S(OBJECT:([Merlin].[dbo].[tblHannahRES].[PK_tblHannahRES]),
SEEK:([tblHannahRES].[ResID]=[FULLTEXT:tblHannahRES].[KEY]) ORDERED
FORWARD)
select * from tblHannahRES where contains(RES_SER_TI, 'fast')
|--Nested Loops(Inner Join, OUTER
REFERENCES:([FULLTEXT:tblHannahRES].[KEY]) WITH PREFETCH)
|--Remote Scan(OBJECT:(CONTAINS))
|--Clustered Index
S(OBJECT:([Merlin].[dbo].[tblHannahRES].[PK_tblHannahRES]),
SEEK:([tblHannahRES].[ResID]=[FULLTEXT:tblHannahRES].[KEY]) ORDERED
FORWARD)thoughts anyone?
ekkis wrote:
> I have a table with a full-text index on a given column. If I run the
> following:
> declare @.s varchar(100)
> select @.s = 'fast'
> select * from tblHannahRES where contains(RES_SER_TI, @.s)
> select * from tblHannahRES where contains(RES_SER_TI, 'fast')
> on my SQL2000/SP4 box I get substantially different access times, which
> is unlike what I would get with a normal index i.e. performing the SQL
> below (where the index is on ResID) yields identical plans and access
> times:
> declare @.i int
> select @.i = 1463440
> select * from tblHannahRES where ResID = @.i
> select * from tblHannahRES where ResID = 1463440
> With the full-text indices I get the plans shown below and the second
> query is consistenly 6X faster than the first.
> One thing I notice is that the remote scan itself costs 0.36 vs 0.06
> though both generate the same number of rows. the second thing to
> notice is that the first case incurs an extra sorting step (which costs
> about 0.02 points). I'm not sure why this should be the case. Can
> anyone elucidate on the issue here?
> TIA - e
> select * from tblHannahRES where contains(RES_SER_TI, @.s)
> |--Nested Loops(Inner Join, OUTER
> REFERENCES:([FULLTEXT:tblHannahRES].[KEY]) WITH PREFETCH)
> |--Sort(ORDER BY:([FULLTEXT:tblHannahRES].[KEY] ASC))
> | |--Remote Scan(OBJECT:(CONTAINS))
> |--Clustered Index
> S(OBJECT:([Merlin].[dbo].[tblHannahRES].[PK_tblHannahRES]),
> SEEK:([tblHannahRES].[ResID]=[FULLTEXT:tblHannahRES].[KEY]) ORDERED
> FORWARD)
> select * from tblHannahRES where contains(RES_SER_TI, 'fast')
> |--Nested Loops(Inner Join, OUTER
> REFERENCES:([FULLTEXT:tblHannahRES].[KEY]) WITH PREFETCH)
> |--Remote Scan(OBJECT:(CONTAINS))
> |--Clustered Index
> S(OBJECT:([Merlin].[dbo].[tblHannahRES].[PK_tblHannahRES]),
> SEEK:([tblHannahRES].[ResID]=[FULLTEXT:tblHannahRES].[KEY]) ORDERED
> FORWARD)

Full text help.. anyone?

Can anyone explain me how to configure full text to run on
a windows 2003 cluster (2 machines 1 sql node)?
Claudia,
Sorry for the late reply, however, assuming you're installing SQL Server
2000 Enterprise Edition on Windows 2003 Advance or Enterprise servers, Full
Text Search is installed by default. If you're using SQL Server 7.0, then
Full-Text Search is not supported in a clustered environment. Without more
info on what you're looking for, a series of steps, or troubleshooting a
specific error, it's kinda hard to provide relevant info. However, the below
KB articles should give you a good starting point...
KB Articles:
817301 PRB: Microsoft Search Full-Text Catalog Population Fails When You
Upgrade SQL Server 2000 Failover Cluster to SQL Server 2000 Service Pack 3
http://support.microsoft.com/default...&Product=sql2k
812666 How to recover a failed full-text search resource on a clustered
instance of SQL Server 2000
http://support.microsoft.com/default...&Product=sql2k
304282 (Q304282) PRB: Full Text Resource Fails to Come Online on a Cluster
with "Event ID 1069 :SQL Cluster Resource 'Full Text' failed" Error Message
http://support.microsoft.com/default...&Product=sql2k
296890 (Q296890) PRB: SQL Server 2000 Fails to Install on a Cluster If
Service Startup Account Contains a Space
http://support.microsoft.com/default...&Product=sql2k
810056 INF: You Must Use Resource-Specific Registry Keys for SQL Server
Cluster Resources
http://support.microsoft.com/default...&Product=sql2k
295732 INF: Creating Databases or Changing Disk File Locations on a Shared
Cluster Drive on Which SQL Server 2000 was not Originally Installed
http://support.microsoft.com/default...&Product=sql2k
260758 INF: Frequently Asked Questions - SQL Server 2000 - Failover
Clustering
http://support.microsoft.com/default...&Product=sql2k
814035 FIX: A Full-Text Population Fails After You Apply SQL Server 2000
Service Pack 3
http://support.microsoft.com/default...&Product=sql2k
811168 INF: SQL Server 2000 Service Pack 3 or Service Pack 3a
Pre-Installation Checklist for SQL Server 2000 Virtual Server for Failover
Clustering
http://support.microsoft.com/default...&Product=sql2k
243218 Installation order for SQL Server 2000 Enterprise Edition on
Microsoft Cluster Server
http://support.microsoft.com/default...&Product=sql2k
Regards,
John
"Claudia" <anonymous@.discussions.microsoft.com> wrote in message
news:2fbc101c46da9$5bedfac0$a401280a@.phx.gbl...
> Can anyone explain me how to configure full text to run on
> a windows 2003 cluster (2 machines 1 sql node)?
>

Full Text hangs Enterprice manager

hi,

I am using MS SQL Server 2000 SP3a on windows server 2000.

I have 4 FT indexs on 4 of my tables.
They are schedules to run every day once. For the last couple of days the jobs are hanged.

When I tried to open Catalogs by clicking on 'Full Text Catalogs' enterprice manager hangs( not responding). I had to go to taskmanager and then click on end task to re-open the enterprise manager.

Also when I right click on one of the tables enabled with FT indexing
Enterprise manager hangs up again.

If I cannt open FTCatalogs how can I rebuild them.

please respond,
mpkcontd..to my previous post

Also in my ErrorLog file i see the following messages,

2007-03-30 06:20:04.08 spid56 WARNING: EC 237815d8, 0 waited 187500 sec. on latch ad8c88. Not a BUF latch.
2007-03-30 06:20:04.08 spid56 Waiting for type 0x4, current count 0xa, current owning EC 0x59E49550.

thanx in adv,
mpk|||you can try rebuilding them using t-sql. see books online for syntax.|||Are you trying to do this while the database is not in single user mode...sorry, never have done full text myself

full text body search not finding all results

I am running SQL 2005 SP1 and I have a database I'm using for GFI Mail
Archiver. When I run queries against the database for a specific word i.e
"testing", only 2 results show up when there should be more results.
I know the data is there because I can see it, it's just that when I do a
search it doesn't find it. I have full text-catalog enabled on the database.
I've tried deleting and re-creating the catalog but still the same thing.
The question is are they really there or are they being indexed.
Use a like to verify that it is really there. If it is then it is a problem
with it being indexed. Review the gatherer logs to see if it indexes the
rows correctly.
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
"Gilbert" <Gilbert@.discussions.microsoft.com> wrote in message
news:1813FDF8-E095-4784-B9B8-707C8309B1C9@.microsoft.com...
>I am running SQL 2005 SP1 and I have a database I'm using for GFI Mail
> Archiver. When I run queries against the database for a specific word i.e
> "testing", only 2 results show up when there should be more results.
> I know the data is there because I can see it, it's just that when I do a
> search it doesn't find it. I have full text-catalog enabled on the
> database.
>
> I've tried deleting and re-creating the catalog but still the same thing.
|||That's what my problem is, it's not being indexed correctly.
How do I fix that?
"Hilary Cotter" wrote:

> The question is are they really there or are they being indexed.
> Use a like to verify that it is really there. If it is then it is a problem
> with it being indexed. Review the gatherer logs to see if it indexes the
> rows correctly.
> --
> 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
>
> "Gilbert" <Gilbert@.discussions.microsoft.com> wrote in message
> news:1813FDF8-E095-4784-B9B8-707C8309B1C9@.microsoft.com...
>
>
|||Can you see if there are any errors in the gatherer logs? If not can you
send some of the problem docs to me offline or post them here? Send me the
originals before they went into the database.
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
"Gilbert" <Gilbert@.discussions.microsoft.com> wrote in message
news:5FEDEB29-BE51-4733-8224-58C87AEAED5C@.microsoft.com...[vbcol=seagreen]
> That's what my problem is, it's not being indexed correctly.
> How do I fix that?
> "Hilary Cotter" wrote:
|||I'm new to SQL so I'll need some help here.
1. How do I get gatherer logs?
2. When you say problem docs, what do you mean exactly? (these are test
emails).
"Hilary Cotter" wrote:

> Can you see if there are any errors in the gatherer logs? If not can you
> send some of the problem docs to me offline or post them here? Send me the
> originals before they went into the database.
> --
> 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
>
> "Gilbert" <Gilbert@.discussions.microsoft.com> wrote in message
> news:5FEDEB29-BE51-4733-8224-58C87AEAED5C@.microsoft.com...
>
>
|||You can find the gatherer logs on a SQL 2005 server in C:\Program
Files\Microsoft SQL Server\MSSQL.X\MSSQL\LOG>
Where X is your instance name.
The gatherer logs themselves will look like this:
SQLFT0001000015.LOG
test email should be indexable in a char or varchar column. Attachments may
not be depending on how you store them. Are you storing them in varbinary or
image columns? Do you have a document type column associated with the image
or varbinary columns?
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
"Gilbert" <Gilbert@.discussions.microsoft.com> wrote in message
news:1F6F0D54-F9FB-4CC3-A8D3-E0445883AE00@.microsoft.com...[vbcol=seagreen]
> I'm new to SQL so I'll need some help here.
> 1. How do I get gatherer logs?
> 2. When you say problem docs, what do you mean exactly? (these are test
> emails).
> "Hilary Cotter" wrote:
|||Ok, I'll look at them. I assume I just open them with Notepad. What am I
looking for?
As far as your questions:
1. Not sure if they're being stored in verbinary or image columns. However
this GFI Mail Archive stores it.
2. Don't know if I have a document type column associated with the
verbinary or image columns.
"Hilary Cotter" wrote:

> You can find the gatherer logs on a SQL 2005 server in C:\Program
> Files\Microsoft SQL Server\MSSQL.X\MSSQL\LOG>
> Where X is your instance name.
> The gatherer logs themselves will look like this:
> SQLFT0001000015.LOG
> test email should be indexable in a char or varchar column. Attachments may
> not be depending on how you store them. Are you storing them in varbinary or
> image columns? Do you have a document type column associated with the image
> or varbinary columns?
> --
> 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
>
> "Gilbert" <Gilbert@.discussions.microsoft.com> wrote in message
> news:1F6F0D54-F9FB-4CC3-A8D3-E0445883AE00@.microsoft.com...
>
>
|||Can you script out your table, indexes, and full text indexes and post them
here.
Key to solving your problem is discovering how the GFI archive stores it. If
it is text, or msg you should be fine. If it is something proprietary you
will be unable to index them.
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
"Gilbert" <Gilbert@.discussions.microsoft.com> wrote in message
news:BD072397-C5D4-4B19-A8E6-80C03AF1784E@.microsoft.com...[vbcol=seagreen]
> Ok, I'll look at them. I assume I just open them with Notepad. What am I
> looking for?
> As far as your questions:
> 1. Not sure if they're being stored in verbinary or image columns.
> However
> this GFI Mail Archive stores it.
> 2. Don't know if I have a document type column associated with the
> verbinary or image columns.
> "Hilary Cotter" wrote:
|||I went to the GFI web site and notice that their product does work with SQL
FTS. Their technical support group should be able to help you through this
problem.
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
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23lkK28IEHHA.3520@.TK2MSFTNGP04.phx.gbl...
> Can you script out your table, indexes, and full text indexes and post
> them here.
> Key to solving your problem is discovering how the GFI archive stores it.
> If it is text, or msg you should be fine. If it is something proprietary
> you will be unable to index them.
> --
> 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
>
> "Gilbert" <Gilbert@.discussions.microsoft.com> wrote in message
> news:BD072397-C5D4-4B19-A8E6-80C03AF1784E@.microsoft.com...
>

Full text 4Gb memory

I have a server run std edition 2000 with FTS. The server has 4Gb of memory
is there any benefit in setting the 3Gb switch?
The /3GB switch refers to the Virtual Address Space for any process, not
just SQL Server. Whether or not that space is backed by physical ram is
dependent on the configuration of the server and other applications running
at any particular time. Let's say you have 2 GB of ram and a 4 GB swap
file. Then, it is possible for an application to have, say 1 GB of physical
ram for kernel mode processing, 1 GB of physical ram for a portion of the
user mode processing, then the application could use of to 2 GB of swap
space. The allocation of that swap space would be dependent on the /3GB
switch being enabled or not.
The same goes for physical ram. If you have 4 GB, then it is possible that
one application could be allocated 1 GB for kernel mode, and 3 GB for user
mode. The problem is that this is not the only application on the server.
If anything, the OS has to be running, mostly from critical sections of
physical ram.
Now, SQL Server 2K Standard Edition will only allocate a MAXIMUM of 2 GB for
the Buffer Pool. As SS2K SE is an application, it could use 1 GB to 2 GB
for the kernel mode requests, but the Buffer Pool is not the ONLY space SS
uses. So, by using the /3GB switch, you could dedicate a 2 GB Buffer Pool
and still allow up to 1 GB for kernel mode requests and 1 GB for MEM TO
LEAVE sections of memory, all backed by physical ram. To the extent that
other applications and the OS itself requires physical ram, you could still
maintain a 2 GB Buffer Pool backed by physical ram and allow the kernel mode
and MEM TO LEAVE regions to be swapped out to the page file.
So, yes, there could be benefits to using the /3GB switch.
However, you never said what OS version you were running on. Neither Win2K
SE nor Win2K3 SE support the /3GB boot.ini parameter. You have to be
running Win2K AS or Win2K3 EE or either of the Data Center Editions.
Sincerely,
Anthony Thomas

"Simon" <Simon@.cmg.noemail> wrote in message
news:07DDD724-0C42-4748-A82F-5C41DE2AD5D4@.microsoft.com...
I have a server run std edition 2000 with FTS. The server has 4Gb of memory
is there any benefit in setting the 3Gb switch?

Full text 4Gb memory

I have a server run std edition 2000 with FTS. The server has 4Gb of memory
is there any benefit in setting the 3Gb switch?The /3GB switch refers to the Virtual Address Space for any process, not
just SQL Server. Whether or not that space is backed by physical ram is
dependent on the configuration of the server and other applications running
at any particular time. Let's say you have 2 GB of ram and a 4 GB swap
file. Then, it is possible for an application to have, say 1 GB of physical
ram for kernel mode processing, 1 GB of physical ram for a portion of the
user mode processing, then the application could use of to 2 GB of swap
space. The allocation of that swap space would be dependent on the /3GB
switch being enabled or not.
The same goes for physical ram. If you have 4 GB, then it is possible that
one application could be allocated 1 GB for kernel mode, and 3 GB for user
mode. The problem is that this is not the only application on the server.
If anything, the OS has to be running, mostly from critical sections of
physical ram.
Now, SQL Server 2K Standard Edition will only allocate a MAXIMUM of 2 GB for
the Buffer Pool. As SS2K SE is an application, it could use 1 GB to 2 GB
for the kernel mode requests, but the Buffer Pool is not the ONLY space SS
uses. So, by using the /3GB switch, you could dedicate a 2 GB Buffer Pool
and still allow up to 1 GB for kernel mode requests and 1 GB for MEM TO
LEAVE sections of memory, all backed by physical ram. To the extent that
other applications and the OS itself requires physical ram, you could still
maintain a 2 GB Buffer Pool backed by physical ram and allow the kernel mode
and MEM TO LEAVE regions to be swapped out to the page file.
So, yes, there could be benefits to using the /3GB switch.
However, you never said what OS version you were running on. Neither Win2K
SE nor Win2K3 SE support the /3GB boot.ini parameter. You have to be
running Win2K AS or Win2K3 EE or either of the Data Center Editions.
Sincerely,
Anthony Thomas
"Simon" <Simon@.cmg.noemail> wrote in message
news:07DDD724-0C42-4748-A82F-5C41DE2AD5D4@.microsoft.com...
I have a server run std edition 2000 with FTS. The server has 4Gb of memory
is there any benefit in setting the 3Gb switch?

Full text 4Gb memory

I have a server run std edition 2000 with FTS. The server has 4Gb of memory
is there any benefit in setting the 3Gb switch?The /3GB switch refers to the Virtual Address Space for any process, not
just SQL Server. Whether or not that space is backed by physical ram is
dependent on the configuration of the server and other applications running
at any particular time. Let's say you have 2 GB of ram and a 4 GB swap
file. Then, it is possible for an application to have, say 1 GB of physical
ram for kernel mode processing, 1 GB of physical ram for a portion of the
user mode processing, then the application could use of to 2 GB of swap
space. The allocation of that swap space would be dependent on the /3GB
switch being enabled or not.
The same goes for physical ram. If you have 4 GB, then it is possible that
one application could be allocated 1 GB for kernel mode, and 3 GB for user
mode. The problem is that this is not the only application on the server.
If anything, the OS has to be running, mostly from critical sections of
physical ram.
Now, SQL Server 2K Standard Edition will only allocate a MAXIMUM of 2 GB for
the Buffer Pool. As SS2K SE is an application, it could use 1 GB to 2 GB
for the kernel mode requests, but the Buffer Pool is not the ONLY space SS
uses. So, by using the /3GB switch, you could dedicate a 2 GB Buffer Pool
and still allow up to 1 GB for kernel mode requests and 1 GB for MEM TO
LEAVE sections of memory, all backed by physical ram. To the extent that
other applications and the OS itself requires physical ram, you could still
maintain a 2 GB Buffer Pool backed by physical ram and allow the kernel mode
and MEM TO LEAVE regions to be swapped out to the page file.
So, yes, there could be benefits to using the /3GB switch.
However, you never said what OS version you were running on. Neither Win2K
SE nor Win2K3 SE support the /3GB boot.ini parameter. You have to be
running Win2K AS or Win2K3 EE or either of the Data Center Editions.
Sincerely,
Anthony Thomas
"Simon" <Simon@.cmg.noemail> wrote in message
news:07DDD724-0C42-4748-A82F-5C41DE2AD5D4@.microsoft.com...
I have a server run std edition 2000 with FTS. The server has 4Gb of memory
is there any benefit in setting the 3Gb switch?

Full Text - Trusted account

Hello,
When I run an incremental/full job for a full text
catalog on win 2k , SQL 2K sp3a cluster server, i am
getting this in the SQL error log
Login failed for user domain\servername$
We recently removed the local admin group having syadmin
access to the server.
I changed the Job owner to the SQL Startup account, even
then it is using the above login. Why is that ?
I couldnt be able to grant the login access as i am not
able to see in the list of users...
Where is this login, can i change something to fix ?
Thanks
- Saravana
can you add this account to the system administrator role?
if not exists (select * from master.dbo.syslogins where loginname = N'NT
Authority\System')
exec sp_grantlogin N'NT Authority\System'
exec sp_defaultdb N'NT Authority\System', N'master'
exec sp_defaultlanguage N'NT Authority\System', N'us_english'
GO
exec sp_addsrvrolemember N'NT Authority\System', sysadmin
GO
"SK" <anonymous@.discussions.microsoft.com> wrote in message
news:1420b01c41808$d9e0f460$a601280a@.phx.gbl...
> Hello,
> When I run an incremental/full job for a full text
> catalog on win 2k , SQL 2K sp3a cluster server, i am
> getting this in the SQL error log
> Login failed for user domain\servername$
> We recently removed the local admin group having syadmin
> access to the server.
> I changed the Job owner to the SQL Startup account, even
> then it is using the above login. Why is that ?
> I couldnt be able to grant the login access as i am not
> able to see in the list of users...
> Where is this login, can i change something to fix ?
> Thanks
> - Saravana
>

Full Model Recovery (Backup)

I run a FULL backup each morning at 12:00am, Steps are, check db, backup, zip backup and remove, shrink log file.
Along with the FULL backup I run Transaction log backups from 7:00am to 11:59pm
once an hour. Append the file. The file has grown to over 33gigs by 11:59pm. At 12:30am I zip the file and remove. This zipped file goes with the previous days Full backup.
My question is, how can I keep the transaction log trimmed? I have it set to remove inactive entries at each hour backup. Can I shrink the file after each hour backup with out damaging the previous backup?
Hi,
It seems you have got huge volume of transaction each hour, so rather than
doing the transaction log backup every 1 hour make it every 20 minutes or
so. Inthis case transaction log file will be cleared after each log backup.
This controls the growth of transaction log file(LDF). Probably as you do by
end of day after the full database backup you can shrink the file. The
shrink may not be required if you increase the transaction log backup
frequency.
Note:
I recommend you not to shrink the transaction log file each hour.
Thanks
Hari
MCDBA
"Robert_at_CBB" <Robert_at_CBB@.discussions.microsoft.com> wrote in message
news:2C16CE96-BF23-499B-A175-7F0552D66D08@.microsoft.com...
> I run a FULL backup each morning at 12:00am, Steps are, check db, backup,
zip backup and remove, shrink log file.
> Along with the FULL backup I run Transaction log backups from 7:00am to
11:59pm
> once an hour. Append the file. The file has grown to over 33gigs by
11:59pm. At 12:30am I zip the file and remove. This zipped file goes with
the previous days Full backup.
> My question is, how can I keep the transaction log trimmed? I have it set
to remove inactive entries at each hour backup. Can I shrink the file after
each hour backup with out damaging the previous backup?
|||Thanks, I guess the problem is the actual backup file growth, by the end of the night it is ove 50gigs. Would a simple model work with a differential with init option work better?
After each differential I would zip and remove the the backup to another drive.
In the event of a disaster I would restore from the Full I did at 12:00am and use the last differential made in the current day.
"Hari Prasad" wrote:

> Hi,
> It seems you have got huge volume of transaction each hour, so rather than
> doing the transaction log backup every 1 hour make it every 20 minutes or
> so. Inthis case transaction log file will be cleared after each log backup.
> This controls the growth of transaction log file(LDF). Probably as you do by
> end of day after the full database backup you can shrink the file. The
> shrink may not be required if you increase the transaction log backup
> frequency.
> Note:
> I recommend you not to shrink the transaction log file each hour.
> Thanks
> Hari
> MCDBA
>
> "Robert_at_CBB" <Robert_at_CBB@.discussions.microsoft.com> wrote in message
> news:2C16CE96-BF23-499B-A175-7F0552D66D08@.microsoft.com...
> zip backup and remove, shrink log file.
> 11:59pm
> 11:59pm. At 12:30am I zip the file and remove. This zipped file goes with
> the previous days Full backup.
> to remove inactive entries at each hour backup. Can I shrink the file after
> each hour backup with out damaging the previous backup?
>
>

Full Model Recovery (Backup)

I run a FULL backup each morning at 12:00am, Steps are, check db, backup, zi
p backup and remove, shrink log file.
Along with the FULL backup I run Transaction log backups from 7:00am to 11:5
9pm
once an hour. Append the file. The file has grown to over 33gigs by 11:59pm.
At 12:30am I zip the file and remove. This zipped file goes with the previo
us days Full backup.
My question is, how can I keep the transaction log trimmed? I have it set to
remove inactive entries at each hour backup. Can I shrink the file after ea
ch hour backup with out damaging the previous backup?Hi,
It seems you have got huge volume of transaction each hour, so rather than
doing the transaction log backup every 1 hour make it every 20 minutes or
so. Inthis case transaction log file will be cleared after each log backup.
This controls the growth of transaction log file(LDF). Probably as you do by
end of day after the full database backup you can shrink the file. The
shrink may not be required if you increase the transaction log backup
frequency.
Note:
I recommend you not to shrink the transaction log file each hour.
Thanks
Hari
MCDBA
"Robert_at_CBB" <Robert_at_CBB@.discussions.microsoft.com> wrote in message
news:2C16CE96-BF23-499B-A175-7F0552D66D08@.microsoft.com...
> I run a FULL backup each morning at 12:00am, Steps are, check db, backup,
zip backup and remove, shrink log file.
> Along with the FULL backup I run Transaction log backups from 7:00am to
11:59pm
> once an hour. Append the file. The file has grown to over 33gigs by
11:59pm. At 12:30am I zip the file and remove. This zipped file goes with
the previous days Full backup.
> My question is, how can I keep the transaction log trimmed? I have it set
to remove inactive entries at each hour backup. Can I shrink the file after
each hour backup with out damaging the previous backup?|||Thanks, I guess the problem is the actual backup file growth, by the end of
the night it is ove 50gigs. Would a simple model work with a differential wi
th init option work better?
After each differential I would zip and remove the the backup to another dri
ve.
In the event of a disaster I would restore from the Full I did at 12:00am an
d use the last differential made in the current day.
"Hari Prasad" wrote:

> Hi,
> It seems you have got huge volume of transaction each hour, so rather than
> doing the transaction log backup every 1 hour make it every 20 minutes or
> so. Inthis case transaction log file will be cleared after each log backup
.
> This controls the growth of transaction log file(LDF). Probably as you do
by
> end of day after the full database backup you can shrink the file. The
> shrink may not be required if you increase the transaction log backup
> frequency.
> Note:
> I recommend you not to shrink the transaction log file each hour.
> Thanks
> Hari
> MCDBA
>
> "Robert_at_CBB" <Robert_at_CBB@.discussions.microsoft.com> wrote in message
> news:2C16CE96-BF23-499B-A175-7F0552D66D08@.microsoft.com...
> zip backup and remove, shrink log file.
> 11:59pm
> 11:59pm. At 12:30am I zip the file and remove. This zipped file goes with
> the previous days Full backup.
> to remove inactive entries at each hour backup. Can I shrink the file afte
r
> each hour backup with out damaging the previous backup?
>
>

Full Model Recovery (Backup)

I run a FULL backup each morning at 12:00am, Steps are, check db, backup, zip backup and remove, shrink log file.
Along with the FULL backup I run Transaction log backups from 7:00am to 11:59pm
once an hour. Append the file. The file has grown to over 33gigs by 11:59pm. At 12:30am I zip the file and remove. This zipped file goes with the previous days Full backup.
My question is, how can I keep the transaction log trimmed? I have it set to remove inactive entries at each hour backup. Can I shrink the file after each hour backup with out damaging the previous backup?Hi,
It seems you have got huge volume of transaction each hour, so rather than
doing the transaction log backup every 1 hour make it every 20 minutes or
so. Inthis case transaction log file will be cleared after each log backup.
This controls the growth of transaction log file(LDF). Probably as you do by
end of day after the full database backup you can shrink the file. The
shrink may not be required if you increase the transaction log backup
frequency.
Note:
I recommend you not to shrink the transaction log file each hour.
Thanks
Hari
MCDBA
"Robert_at_CBB" <Robert_at_CBB@.discussions.microsoft.com> wrote in message
news:2C16CE96-BF23-499B-A175-7F0552D66D08@.microsoft.com...
> I run a FULL backup each morning at 12:00am, Steps are, check db, backup,
zip backup and remove, shrink log file.
> Along with the FULL backup I run Transaction log backups from 7:00am to
11:59pm
> once an hour. Append the file. The file has grown to over 33gigs by
11:59pm. At 12:30am I zip the file and remove. This zipped file goes with
the previous days Full backup.
> My question is, how can I keep the transaction log trimmed? I have it set
to remove inactive entries at each hour backup. Can I shrink the file after
each hour backup with out damaging the previous backup?|||Thanks, I guess the problem is the actual backup file growth, by the end of the night it is ove 50gigs. Would a simple model work with a differential with init option work better?
After each differential I would zip and remove the the backup to another drive.
In the event of a disaster I would restore from the Full I did at 12:00am and use the last differential made in the current day.
"Hari Prasad" wrote:
> Hi,
> It seems you have got huge volume of transaction each hour, so rather than
> doing the transaction log backup every 1 hour make it every 20 minutes or
> so. Inthis case transaction log file will be cleared after each log backup.
> This controls the growth of transaction log file(LDF). Probably as you do by
> end of day after the full database backup you can shrink the file. The
> shrink may not be required if you increase the transaction log backup
> frequency.
> Note:
> I recommend you not to shrink the transaction log file each hour.
> Thanks
> Hari
> MCDBA
>
> "Robert_at_CBB" <Robert_at_CBB@.discussions.microsoft.com> wrote in message
> news:2C16CE96-BF23-499B-A175-7F0552D66D08@.microsoft.com...
> > I run a FULL backup each morning at 12:00am, Steps are, check db, backup,
> zip backup and remove, shrink log file.
> >
> > Along with the FULL backup I run Transaction log backups from 7:00am to
> 11:59pm
> > once an hour. Append the file. The file has grown to over 33gigs by
> 11:59pm. At 12:30am I zip the file and remove. This zipped file goes with
> the previous days Full backup.
> >
> > My question is, how can I keep the transaction log trimmed? I have it set
> to remove inactive entries at each hour backup. Can I shrink the file after
> each hour backup with out damaging the previous backup?
>
>

Full and Diff Backups

If I run a full Bkp when the Log file is 10 Gig, and the backup runs for 1 hour while another 5 Gig is added in transactions to the log file, The Backup should complete leaving only the new 5 Gig of transactions while the previous 10 Gig were applied to t
he backup. Basically backing up everything upto and including the Backup statement.
Does the same action take place on the transaction log during a differential backup? Does a differential backup include every transaction on the log file upto and including the backup statement?
>> Basically backing up everything upto and including the Backup statement.
Not exactly. From Inside SQL Server 2000:
'To maintain consistency for either a full or a differential backup, SQL
Server records the current log sequence number (LSN) at the time the backup
starts and then again at the time the backup ends. This allows the backup to
also capture the relevant parts of the log. The relevant part starts with
the oldest open transaction at the time of the first recorded LSN and ends
with the second recorded LSN.'
Thus, the 5 Gig of 'new' trxs will also be present in the log, but will only
be applied upon restore if they were committed at the time the backup ended,
assuming you restore with the RECOVERY option.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Try MiniSQLBackup
"Brian" <Brian@.discussions.microsoft.com> wrote in message
news:2A52A0B3-23D5-4771-8170-3BE8CFF8330C@.microsoft.com...
> If I run a full Bkp when the Log file is 10 Gig, and the backup runs for 1
hour while another 5 Gig is added in transactions to the log file, The
Backup should complete leaving only the new 5 Gig of transactions while the
previous 10 Gig were applied to the backup. Basically backing up everything
upto and including the Backup statement.
> Does the same action take place on the transaction log during a
differential backup? Does a differential backup include every transaction on
the log file upto and including the backup statement?
>

Full and Diff Backups

If I run a full Bkp when the Log file is 10 Gig, and the backup runs for 1 h
our while another 5 Gig is added in transactions to the log file, The Backup
should complete leaving only the new 5 Gig of transactions while the previo
us 10 Gig were applied to t
he backup. Basically backing up everything upto and including the Backup sta
tement.
Does the same action take place on the transaction log during a differential
backup? Does a differential backup include every transaction on the log fil
e upto and including the backup statement?>> Basically backing up everything upto and including the Backup statement.
Not exactly. From Inside SQL Server 2000:
'To maintain consistency for either a full or a differential backup, SQL
Server records the current log sequence number (LSN) at the time the backup
starts and then again at the time the backup ends. This allows the backup to
also capture the relevant parts of the log. The relevant part starts with
the oldest open transaction at the time of the first recorded LSN and ends
with the second recorded LSN.'
Thus, the 5 Gig of 'new' trxs will also be present in the log, but will only
be applied upon restore if they were committed at the time the backup ended,
assuming you restore with the RECOVERY option.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Try MiniSQLBackup
"Brian" <Brian@.discussions.microsoft.com> wrote in message
news:2A52A0B3-23D5-4771-8170-3BE8CFF8330C@.microsoft.com...
> If I run a full Bkp when the Log file is 10 Gig, and the backup runs for 1
hour while another 5 Gig is added in transactions to the log file, The
Backup should complete leaving only the new 5 Gig of transactions while the
previous 10 Gig were applied to the backup. Basically backing up everything
upto and including the Backup statement.
> Does the same action take place on the transaction log during a
differential backup? Does a differential backup include every transaction on
the log file upto and including the backup statement?
>