Hi,
Does anyone have experience with Full Text catalogs on large tables? We have a full text catalog on a table with about 30 million rows. Acording to BOL, once you get over 1 million you'll need to make some adjustements. Our system works, but we randomly get the following errors with ad hoc queries. The server has 8GB of RAM and 4 3GHz processors. Does anyone have experience working with a table this big? Any suggetions as to what could cause these errors? The only thing I could find was BUG#: 469483 on MS's site, but we're not using any OR clauses
Thanks
Here's the errors...
(query)
SELECT * FROM acc_results ar
WHERE CONTAINS(finding_text, 'cell')
#1
Server: Msg 7619, Level 16, State 1, Line 1
Execution of a full-text operation failed. Not enough storage is available to process this command.
#2
Server: Msg 7342, Level 16, State 1, Line 1
Unexpected NULL value returned for column '[FULLTEXT:acc_results].KEY' from the OLE DB provider 'Full-text Search Engine'. This column cannot be NULL.
OLE DB error trace [Non-interface error: Unexpected NULL value returned for the column: ProviderName='Full-text Search Engine', TableName='[FULLTEXT:acc_results]', ColumnName='KEY'].
#3
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Full-text Search Engine' reported an error.
[OLE/DB provider returned message: Not enough storage is available to process this command.]
OLE DB error trace [OLE/DB Provider 'Full-text Search Engine' IRowset::GetNextRows returned 0x80004005: ].just taking #1 and #3 at face value (I know nothing is what it seems), I would imagine you need more space on your hard drive.
2012年3月19日星期一
Full Text Max Size?
Hi,
Does anyone have experience with Full Text catalogs on large tables? We
have a full text catalog on a table with about 30 million rows. Acording
to BOL, once you get over 1 million you'll need to make some
adjustements. Our system works, but often we get the following errors
with ad hoc queries. The server has 8GB of RAM and 4 3GHz processors.
Does anyone have experience working with a table this big? Any
suggetions as to what could cause these errors? The only thing I could
find was BUG#: 469483 on MS's site, but we're not using any OR clauses
Thanks
Here's the errors...
(query)
SELECT * FROM acc_results ar
WHERE CONTAINS(finding_text, 'cell')
#1
Server: Msg 7619, Level 16, State 1, Line 1
Execution of a full-text operation failed. Not enough storage is
available to process this command.
#2
Server: Msg 7342, Level 16, State 1, Line 1
Unexpected NULL value returned for column '[FULLTEXT:acc_results].KEY'
from the OLE DB provider 'Full-text Search Engine'. This column cannot
be NULL.
OLE DB error trace [Non-interface error: Unexpected NULL value returned
for the column: ProviderName='Full-text Search Engine',
TableName='[FULLTEXT:acc_results]', ColumnName='KEY'].
#3
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Full-text Search Engine' reported an error.
[OLE/DB provider returned message: Not enough storage is available to
process this command.]
OLE DB error trace [OLE/DB Provider 'Full-text Search Engine'
IRowset::GetNextRows returned 0x80004005: ].
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Not enough storage is a common OS related error - relating to available
memory I think. Can you do things like surf on this machine, map network
drives, etc? I have heard of a 35 million row SQL FTS search solution -
IIRC.
SQL FTS 2005 has been tested to 2 billion rows.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Charlie Pyne" <cpyne@.partners.org> wrote in message
news:%23SjvpQb%23EHA.3988@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Does anyone have experience with Full Text catalogs on large tables? We
> have a full text catalog on a table with about 30 million rows. Acording
> to BOL, once you get over 1 million you'll need to make some
> adjustements. Our system works, but often we get the following errors
> with ad hoc queries. The server has 8GB of RAM and 4 3GHz processors.
> Does anyone have experience working with a table this big? Any
> suggetions as to what could cause these errors? The only thing I could
> find was BUG#: 469483 on MS's site, but we're not using any OR clauses
> Thanks
> Here's the errors...
> (query)
> SELECT * FROM acc_results ar
> WHERE CONTAINS(finding_text, 'cell')
> #1
> Server: Msg 7619, Level 16, State 1, Line 1
> Execution of a full-text operation failed. Not enough storage is
> available to process this command.
>
> #2
> Server: Msg 7342, Level 16, State 1, Line 1
> Unexpected NULL value returned for column '[FULLTEXT:acc_results].KEY'
> from the OLE DB provider 'Full-text Search Engine'. This column cannot
> be NULL.
> OLE DB error trace [Non-interface error: Unexpected NULL value returned
> for the column: ProviderName='Full-text Search Engine',
> TableName='[FULLTEXT:acc_results]', ColumnName='KEY'].
>
> #3
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'Full-text Search Engine' reported an error.
> [OLE/DB provider returned message: Not enough storage is available to
> process this command.]
> OLE DB error trace [OLE/DB Provider 'Full-text Search Engine'
> IRowset::GetNextRows returned 0x80004005: ].
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Everything else seems to work fine. What is IIRC? I searched on it,
but got a bunch of different results back.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||IIRC = If I Recall Correctly.
Ok, can you review your application log for events from MSSearch saying
master merge paused.
Also is this a cluster?
"Charlie Pyne" <cpyne@.partners.org> wrote in message
news:elauz0b%23EHA.3368@.TK2MSFTNGP10.phx.gbl...
> Everything else seems to work fine. What is IIRC? I searched on it,
> but got a bunch of different results back.
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Charlie,
Yes, I do. First of all, what is the exact version of SQL Server (7.0 or
2000) and on what OS platform is it deployed? Could you post the full output
of -- SELECT @.@.version -- as this is VERY helpful information in
troubleshooting such SQL FTS performance issues!
Secondly, and depending upon what your is your SQL Server version and SP
level as well as the exact query that your are getting these errors, you may
want to checkout KB article 818883 "PRB: You may receive error 7619 when you
run a full-text query that contains many OR operators in the search string"
at http://support.microsoft.com/default...b;en-us;818883 as one of
the symptoms is error: "Not enough storage is available to complete this
operation" as this occurs on SQL Server 2000 SP3 and when your CONTAINS
query contains many logical OR operators.
However, because of the larger number of rows (30 million), in your
FT-enable table, you may have encountered additional problems related to the
Master Merge. Specifically, you should review your server's Application
Event log for "Microsoft Search" or MssCi source events - errors, warnings
AND informational for any related Master Merge paused conditions.
Furthermore, you should review the Performance Monitor (Perfmon) counters
for Microsoft Gatherer: Reason to back off, Microsoft Gatherer Projects:
Gatherer Paused Flag, and Microsoft Search Indexer Catalogs: Merge Progress
to determine if there is an existing issue with the Master Merge process.
Depending upon your reply, I can provide you with more specific
recommendations.
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Charlie Pyne" <cpyne@.partners.org> wrote in message
news:#SjvpQb#EHA.3988@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Does anyone have experience with Full Text catalogs on large tables? We
> have a full text catalog on a table with about 30 million rows. Acording
> to BOL, once you get over 1 million you'll need to make some
> adjustements. Our system works, but often we get the following errors
> with ad hoc queries. The server has 8GB of RAM and 4 3GHz processors.
> Does anyone have experience working with a table this big? Any
> suggetions as to what could cause these errors? The only thing I could
> find was BUG#: 469483 on MS's site, but we're not using any OR clauses
> Thanks
> Here's the errors...
> (query)
> SELECT * FROM acc_results ar
> WHERE CONTAINS(finding_text, 'cell')
> #1
> Server: Msg 7619, Level 16, State 1, Line 1
> Execution of a full-text operation failed. Not enough storage is
> available to process this command.
>
> #2
> Server: Msg 7342, Level 16, State 1, Line 1
> Unexpected NULL value returned for column '[FULLTEXT:acc_results].KEY'
> from the OLE DB provider 'Full-text Search Engine'. This column cannot
> be NULL.
> OLE DB error trace [Non-interface error: Unexpected NULL value returned
> for the column: ProviderName='Full-text Search Engine',
> TableName='[FULLTEXT:acc_results]', ColumnName='KEY'].
>
> #3
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'Full-text Search Engine' reported an error.
> [OLE/DB provider returned message: Not enough storage is available to
> process this command.]
> OLE DB error trace [OLE/DB Provider 'Full-text Search Engine'
> IRowset::GetNextRows returned 0x80004005: ].
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Charlie,
A quick follow-up... I think you may benefit from this KB article:
303459 (Q303459) "INF: How to Improve the Performance of FTS Queries for
Large Tables" http://support.microsoft.com/?id=303459 and make changes to
the MaxPropStoreCachedSize registry key value as well as use the Top_N_Rank
parameter in all of your SQL FTS queries, per KB article: 240833 (Q240833)
"FIX: Full-Text Search Performance Improved via Support for TOP"
http://support.microsoft.com//defaul...b;EN-US;240833
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"John Kane" <jt-kane@.comcast.net> wrote in message
news:u3WGySe#EHA.2016@.TK2MSFTNGP15.phx.gbl...
> Charlie,
> Yes, I do. First of all, what is the exact version of SQL Server (7.0 or
> 2000) and on what OS platform is it deployed? Could you post the full
output
> of -- SELECT @.@.version -- as this is VERY helpful information in
> troubleshooting such SQL FTS performance issues!
> Secondly, and depending upon what your is your SQL Server version and SP
> level as well as the exact query that your are getting these errors, you
may
> want to checkout KB article 818883 "PRB: You may receive error 7619 when
you
> run a full-text query that contains many OR operators in the search
string"
> at http://support.microsoft.com/default...b;en-us;818883 as one
of
> the symptoms is error: "Not enough storage is available to complete this
> operation" as this occurs on SQL Server 2000 SP3 and when your CONTAINS
> query contains many logical OR operators.
> However, because of the larger number of rows (30 million), in your
> FT-enable table, you may have encountered additional problems related to
the
> Master Merge. Specifically, you should review your server's Application
> Event log for "Microsoft Search" or MssCi source events - errors, warnings
> AND informational for any related Master Merge paused conditions.
> Furthermore, you should review the Performance Monitor (Perfmon) counters
> for Microsoft Gatherer: Reason to back off, Microsoft Gatherer
Projects:
> Gatherer Paused Flag, and Microsoft Search Indexer Catalogs: Merge
Progress
> to determine if there is an existing issue with the Master Merge process.
> Depending upon your reply, I can provide you with more specific
> recommendations.
> Thanks,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Charlie Pyne" <cpyne@.partners.org> wrote in message
> news:#SjvpQb#EHA.3988@.TK2MSFTNGP11.phx.gbl...
>
|||Hi John,
Thanks for the reply. I'm not at work so I can't run @.@.version at this
time, but I can tell you that this server is SQL 2000 Enterprise Edition
with SP3a on Win2k Advanced Server with SP3.
We do have the MaxPropStoreCachedSize parameter in place and currently
it is set to 2000. The size of those two files listed in the article is
> 2GB. One thing I noticed last night when testing this against our test
server, (same hardware/os/sql), is the errors don't seem to happen when
this parameter is removed. The mssearch.exe process stays around 100MB
and the query completes successfully. With this key in place, the
memory used by mssearch grows to around 1 or 1.5GB and then we get the
"not enough storage" error. Mssearch.exe then just sits there holding
that amount of ram until we stop/start the search service.
I haven't had a chance to experiment with this yet, but someone else
thought that both the /3GB and /PAE switches that are currently present
could be causing problems. SQL is configured to only use 4GB of the 8GB
total, but for some reason performance of non-full text queries take a
major hit when we remove /3GB. WE've tried making the same 4GB
available to SQL with just the /PAE switch, but it doesn't seem to work
as well.
I'll check for the event log errors that you mentioned next week.
Thanks for your help. Please let me know if you have any other ideas.
Charlie
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||You're welcome, Charlie,
When you get to work, also check the server's Application event log for
"Microsoft Search" or MssCi source events - errors, warnings AND
informational for any related Master Merge paused conditions as this where
you'll find the true cause of what is causing the MSSearch service to appear
to hang. Additionally, you may want to start with smaller numbers for
setting the MaxPropStoreCachedSize value, say 500 MB to 1000 (or 1GB) and
then test until you get the failure as tuning this parameter is not an exact
science.
No, using the /3GB and /PAE switches is not the problem as the MSSearch
service with the resource_usage level set to 5 (dedicated) can only use a
max. of 512MB of RAM, if and only if this memory is available and not used
by any other process. Furthermore, the MSSearch service cannot use the
memory above the 2Gb limit.
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Charlie Pyne" <cpyne@.partners.org> wrote in message
news:uVSXfgj#EHA.600@.TK2MSFTNGP09.phx.gbl...
> Hi John,
> Thanks for the reply. I'm not at work so I can't run @.@.version at this
> time, but I can tell you that this server is SQL 2000 Enterprise Edition
> with SP3a on Win2k Advanced Server with SP3.
> We do have the MaxPropStoreCachedSize parameter in place and currently
> it is set to 2000. The size of those two files listed in the article is
> server, (same hardware/os/sql), is the errors don't seem to happen when
> this parameter is removed. The mssearch.exe process stays around 100MB
> and the query completes successfully. With this key in place, the
> memory used by mssearch grows to around 1 or 1.5GB and then we get the
> "not enough storage" error. Mssearch.exe then just sits there holding
> that amount of ram until we stop/start the search service.
> I haven't had a chance to experiment with this yet, but someone else
> thought that both the /3GB and /PAE switches that are currently present
> could be causing problems. SQL is configured to only use 4GB of the 8GB
> total, but for some reason performance of non-full text queries take a
> major hit when we remove /3GB. WE've tried making the same 4GB
> available to SQL with just the /PAE switch, but it doesn't seem to work
> as well.
> I'll check for the event log errors that you mentioned next week.
> Thanks for your help. Please let me know if you have any other ideas.
> Charlie
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Hi John,
I don't see any errors related to Master Merge. I do see this error a
bunch of times during our initial full population...
The entry <MSSQL75://SQLSERVER/286302EC/00000342> in the hash map on
project <SQLServer SQL0000600005> cannot be updated. Error: 8007054e - A
description for this error could not be found.
MS had told us to set MaxPropStoreCachedSize based on the size of these
two files...
00000001.ps1 = 775MB
00000001.ps2 = 2.26GB
If I set this to 256, the problem seems to go away. With 256 set, I've
let the SELECT * query that we've been using to reproduce the error run
for about 10 minutes before I cancel it. With 2000 set, we'll get the
"not enough storage" error after about 2 minutes.
We also have the priorty of the service set to the default of 3. I
haven't found much in terms of how this should be set other than
1=background and 5=dedicated. Do you have any suggestions? This is a
system that is used by doctors to save case notes and query historical
notes using full text and standard sql queries. We'd like to maximize
the performance of full text, but we don't want to have a negative
effect on regular SQL. We'd also like to cut down on the time needed
for full & incremental rebuilds. Fulls now take 1 week and incrementals
take about 1 day. From what I've read, we should be using change
tracking. We plan on doing this as soon as we have this current problem
resolved. Any other suggestions beyond this?
Thanks for your help
Charlie
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Hi Charlie,
With SQL Server 2000 FTS you're somewhat limited with tables of this scale.
You could try to horizontally partition the table into multiple smaller
tables (say partition on a date range or some other column specific range of
values) and the FT Index the smaller tables using separate FT Catalogs. I'd
also highly recommend that you use CONTAINSTABLE or FREETEXTTABLE with the
Top_N_Rank value (see KB article Q240833) when running FTS SELECT * queries
against the larger FT Catalog. I'd also highly recommend that you look at
SQL Server 2005 as many of these limitations go away with Yukon FTS.
If you're 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. However, this will not fix the error
caused by large *.ps* files, as does setting the MaxPropStoreCachedSize
registry key value.
Once you've resolved this issue, use Change tracking with Update Index in
background, but be aware that an automatic Incremental or Full Population
will be run when you set this feature, so the MSSearch service can re-sync
up the FT Catalog with the SQL table data.
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Charlie Pyne" <cpyne@.partners.org> wrote in message
news:OeQeKOY$EHA.4092@.TK2MSFTNGP09.phx.gbl...
> Hi John,
> I don't see any errors related to Master Merge. I do see this error a
> bunch of times during our initial full population...
> The entry <MSSQL75://SQLSERVER/286302EC/00000342> in the hash map on
> project <SQLServer SQL0000600005> cannot be updated. Error: 8007054e - A
> description for this error could not be found.
> MS had told us to set MaxPropStoreCachedSize based on the size of these
> two files...
> 00000001.ps1 = 775MB
> 00000001.ps2 = 2.26GB
> If I set this to 256, the problem seems to go away. With 256 set, I've
> let the SELECT * query that we've been using to reproduce the error run
> for about 10 minutes before I cancel it. With 2000 set, we'll get the
> "not enough storage" error after about 2 minutes.
> We also have the priorty of the service set to the default of 3. I
> haven't found much in terms of how this should be set other than
> 1=background and 5=dedicated. Do you have any suggestions? This is a
> system that is used by doctors to save case notes and query historical
> notes using full text and standard sql queries. We'd like to maximize
> the performance of full text, but we don't want to have a negative
> effect on regular SQL. We'd also like to cut down on the time needed
> for full & incremental rebuilds. Fulls now take 1 week and incrementals
> take about 1 day. From what I've read, we should be using change
> tracking. We plan on doing this as soon as we have this current problem
> resolved. Any other suggestions beyond this?
> Thanks for your help
> Charlie
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
Does anyone have experience with Full Text catalogs on large tables? We
have a full text catalog on a table with about 30 million rows. Acording
to BOL, once you get over 1 million you'll need to make some
adjustements. Our system works, but often we get the following errors
with ad hoc queries. The server has 8GB of RAM and 4 3GHz processors.
Does anyone have experience working with a table this big? Any
suggetions as to what could cause these errors? The only thing I could
find was BUG#: 469483 on MS's site, but we're not using any OR clauses
Thanks
Here's the errors...
(query)
SELECT * FROM acc_results ar
WHERE CONTAINS(finding_text, 'cell')
#1
Server: Msg 7619, Level 16, State 1, Line 1
Execution of a full-text operation failed. Not enough storage is
available to process this command.
#2
Server: Msg 7342, Level 16, State 1, Line 1
Unexpected NULL value returned for column '[FULLTEXT:acc_results].KEY'
from the OLE DB provider 'Full-text Search Engine'. This column cannot
be NULL.
OLE DB error trace [Non-interface error: Unexpected NULL value returned
for the column: ProviderName='Full-text Search Engine',
TableName='[FULLTEXT:acc_results]', ColumnName='KEY'].
#3
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Full-text Search Engine' reported an error.
[OLE/DB provider returned message: Not enough storage is available to
process this command.]
OLE DB error trace [OLE/DB Provider 'Full-text Search Engine'
IRowset::GetNextRows returned 0x80004005: ].
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Not enough storage is a common OS related error - relating to available
memory I think. Can you do things like surf on this machine, map network
drives, etc? I have heard of a 35 million row SQL FTS search solution -
IIRC.
SQL FTS 2005 has been tested to 2 billion rows.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Charlie Pyne" <cpyne@.partners.org> wrote in message
news:%23SjvpQb%23EHA.3988@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Does anyone have experience with Full Text catalogs on large tables? We
> have a full text catalog on a table with about 30 million rows. Acording
> to BOL, once you get over 1 million you'll need to make some
> adjustements. Our system works, but often we get the following errors
> with ad hoc queries. The server has 8GB of RAM and 4 3GHz processors.
> Does anyone have experience working with a table this big? Any
> suggetions as to what could cause these errors? The only thing I could
> find was BUG#: 469483 on MS's site, but we're not using any OR clauses
> Thanks
> Here's the errors...
> (query)
> SELECT * FROM acc_results ar
> WHERE CONTAINS(finding_text, 'cell')
> #1
> Server: Msg 7619, Level 16, State 1, Line 1
> Execution of a full-text operation failed. Not enough storage is
> available to process this command.
>
> #2
> Server: Msg 7342, Level 16, State 1, Line 1
> Unexpected NULL value returned for column '[FULLTEXT:acc_results].KEY'
> from the OLE DB provider 'Full-text Search Engine'. This column cannot
> be NULL.
> OLE DB error trace [Non-interface error: Unexpected NULL value returned
> for the column: ProviderName='Full-text Search Engine',
> TableName='[FULLTEXT:acc_results]', ColumnName='KEY'].
>
> #3
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'Full-text Search Engine' reported an error.
> [OLE/DB provider returned message: Not enough storage is available to
> process this command.]
> OLE DB error trace [OLE/DB Provider 'Full-text Search Engine'
> IRowset::GetNextRows returned 0x80004005: ].
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Everything else seems to work fine. What is IIRC? I searched on it,
but got a bunch of different results back.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||IIRC = If I Recall Correctly.
Ok, can you review your application log for events from MSSearch saying
master merge paused.
Also is this a cluster?
"Charlie Pyne" <cpyne@.partners.org> wrote in message
news:elauz0b%23EHA.3368@.TK2MSFTNGP10.phx.gbl...
> Everything else seems to work fine. What is IIRC? I searched on it,
> but got a bunch of different results back.
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Charlie,
Yes, I do. First of all, what is the exact version of SQL Server (7.0 or
2000) and on what OS platform is it deployed? Could you post the full output
of -- SELECT @.@.version -- as this is VERY helpful information in
troubleshooting such SQL FTS performance issues!
Secondly, and depending upon what your is your SQL Server version and SP
level as well as the exact query that your are getting these errors, you may
want to checkout KB article 818883 "PRB: You may receive error 7619 when you
run a full-text query that contains many OR operators in the search string"
at http://support.microsoft.com/default...b;en-us;818883 as one of
the symptoms is error: "Not enough storage is available to complete this
operation" as this occurs on SQL Server 2000 SP3 and when your CONTAINS
query contains many logical OR operators.
However, because of the larger number of rows (30 million), in your
FT-enable table, you may have encountered additional problems related to the
Master Merge. Specifically, you should review your server's Application
Event log for "Microsoft Search" or MssCi source events - errors, warnings
AND informational for any related Master Merge paused conditions.
Furthermore, you should review the Performance Monitor (Perfmon) counters
for Microsoft Gatherer: Reason to back off, Microsoft Gatherer Projects:
Gatherer Paused Flag, and Microsoft Search Indexer Catalogs: Merge Progress
to determine if there is an existing issue with the Master Merge process.
Depending upon your reply, I can provide you with more specific
recommendations.
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Charlie Pyne" <cpyne@.partners.org> wrote in message
news:#SjvpQb#EHA.3988@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Does anyone have experience with Full Text catalogs on large tables? We
> have a full text catalog on a table with about 30 million rows. Acording
> to BOL, once you get over 1 million you'll need to make some
> adjustements. Our system works, but often we get the following errors
> with ad hoc queries. The server has 8GB of RAM and 4 3GHz processors.
> Does anyone have experience working with a table this big? Any
> suggetions as to what could cause these errors? The only thing I could
> find was BUG#: 469483 on MS's site, but we're not using any OR clauses
> Thanks
> Here's the errors...
> (query)
> SELECT * FROM acc_results ar
> WHERE CONTAINS(finding_text, 'cell')
> #1
> Server: Msg 7619, Level 16, State 1, Line 1
> Execution of a full-text operation failed. Not enough storage is
> available to process this command.
>
> #2
> Server: Msg 7342, Level 16, State 1, Line 1
> Unexpected NULL value returned for column '[FULLTEXT:acc_results].KEY'
> from the OLE DB provider 'Full-text Search Engine'. This column cannot
> be NULL.
> OLE DB error trace [Non-interface error: Unexpected NULL value returned
> for the column: ProviderName='Full-text Search Engine',
> TableName='[FULLTEXT:acc_results]', ColumnName='KEY'].
>
> #3
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'Full-text Search Engine' reported an error.
> [OLE/DB provider returned message: Not enough storage is available to
> process this command.]
> OLE DB error trace [OLE/DB Provider 'Full-text Search Engine'
> IRowset::GetNextRows returned 0x80004005: ].
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Charlie,
A quick follow-up... I think you may benefit from this KB article:
303459 (Q303459) "INF: How to Improve the Performance of FTS Queries for
Large Tables" http://support.microsoft.com/?id=303459 and make changes to
the MaxPropStoreCachedSize registry key value as well as use the Top_N_Rank
parameter in all of your SQL FTS queries, per KB article: 240833 (Q240833)
"FIX: Full-Text Search Performance Improved via Support for TOP"
http://support.microsoft.com//defaul...b;EN-US;240833
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"John Kane" <jt-kane@.comcast.net> wrote in message
news:u3WGySe#EHA.2016@.TK2MSFTNGP15.phx.gbl...
> Charlie,
> Yes, I do. First of all, what is the exact version of SQL Server (7.0 or
> 2000) and on what OS platform is it deployed? Could you post the full
output
> of -- SELECT @.@.version -- as this is VERY helpful information in
> troubleshooting such SQL FTS performance issues!
> Secondly, and depending upon what your is your SQL Server version and SP
> level as well as the exact query that your are getting these errors, you
may
> want to checkout KB article 818883 "PRB: You may receive error 7619 when
you
> run a full-text query that contains many OR operators in the search
string"
> at http://support.microsoft.com/default...b;en-us;818883 as one
of
> the symptoms is error: "Not enough storage is available to complete this
> operation" as this occurs on SQL Server 2000 SP3 and when your CONTAINS
> query contains many logical OR operators.
> However, because of the larger number of rows (30 million), in your
> FT-enable table, you may have encountered additional problems related to
the
> Master Merge. Specifically, you should review your server's Application
> Event log for "Microsoft Search" or MssCi source events - errors, warnings
> AND informational for any related Master Merge paused conditions.
> Furthermore, you should review the Performance Monitor (Perfmon) counters
> for Microsoft Gatherer: Reason to back off, Microsoft Gatherer
Projects:
> Gatherer Paused Flag, and Microsoft Search Indexer Catalogs: Merge
Progress
> to determine if there is an existing issue with the Master Merge process.
> Depending upon your reply, I can provide you with more specific
> recommendations.
> Thanks,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Charlie Pyne" <cpyne@.partners.org> wrote in message
> news:#SjvpQb#EHA.3988@.TK2MSFTNGP11.phx.gbl...
>
|||Hi John,
Thanks for the reply. I'm not at work so I can't run @.@.version at this
time, but I can tell you that this server is SQL 2000 Enterprise Edition
with SP3a on Win2k Advanced Server with SP3.
We do have the MaxPropStoreCachedSize parameter in place and currently
it is set to 2000. The size of those two files listed in the article is
> 2GB. One thing I noticed last night when testing this against our test
server, (same hardware/os/sql), is the errors don't seem to happen when
this parameter is removed. The mssearch.exe process stays around 100MB
and the query completes successfully. With this key in place, the
memory used by mssearch grows to around 1 or 1.5GB and then we get the
"not enough storage" error. Mssearch.exe then just sits there holding
that amount of ram until we stop/start the search service.
I haven't had a chance to experiment with this yet, but someone else
thought that both the /3GB and /PAE switches that are currently present
could be causing problems. SQL is configured to only use 4GB of the 8GB
total, but for some reason performance of non-full text queries take a
major hit when we remove /3GB. WE've tried making the same 4GB
available to SQL with just the /PAE switch, but it doesn't seem to work
as well.
I'll check for the event log errors that you mentioned next week.
Thanks for your help. Please let me know if you have any other ideas.
Charlie
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||You're welcome, Charlie,
When you get to work, also check the server's Application event log for
"Microsoft Search" or MssCi source events - errors, warnings AND
informational for any related Master Merge paused conditions as this where
you'll find the true cause of what is causing the MSSearch service to appear
to hang. Additionally, you may want to start with smaller numbers for
setting the MaxPropStoreCachedSize value, say 500 MB to 1000 (or 1GB) and
then test until you get the failure as tuning this parameter is not an exact
science.
No, using the /3GB and /PAE switches is not the problem as the MSSearch
service with the resource_usage level set to 5 (dedicated) can only use a
max. of 512MB of RAM, if and only if this memory is available and not used
by any other process. Furthermore, the MSSearch service cannot use the
memory above the 2Gb limit.
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Charlie Pyne" <cpyne@.partners.org> wrote in message
news:uVSXfgj#EHA.600@.TK2MSFTNGP09.phx.gbl...
> Hi John,
> Thanks for the reply. I'm not at work so I can't run @.@.version at this
> time, but I can tell you that this server is SQL 2000 Enterprise Edition
> with SP3a on Win2k Advanced Server with SP3.
> We do have the MaxPropStoreCachedSize parameter in place and currently
> it is set to 2000. The size of those two files listed in the article is
> server, (same hardware/os/sql), is the errors don't seem to happen when
> this parameter is removed. The mssearch.exe process stays around 100MB
> and the query completes successfully. With this key in place, the
> memory used by mssearch grows to around 1 or 1.5GB and then we get the
> "not enough storage" error. Mssearch.exe then just sits there holding
> that amount of ram until we stop/start the search service.
> I haven't had a chance to experiment with this yet, but someone else
> thought that both the /3GB and /PAE switches that are currently present
> could be causing problems. SQL is configured to only use 4GB of the 8GB
> total, but for some reason performance of non-full text queries take a
> major hit when we remove /3GB. WE've tried making the same 4GB
> available to SQL with just the /PAE switch, but it doesn't seem to work
> as well.
> I'll check for the event log errors that you mentioned next week.
> Thanks for your help. Please let me know if you have any other ideas.
> Charlie
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Hi John,
I don't see any errors related to Master Merge. I do see this error a
bunch of times during our initial full population...
The entry <MSSQL75://SQLSERVER/286302EC/00000342> in the hash map on
project <SQLServer SQL0000600005> cannot be updated. Error: 8007054e - A
description for this error could not be found.
MS had told us to set MaxPropStoreCachedSize based on the size of these
two files...
00000001.ps1 = 775MB
00000001.ps2 = 2.26GB
If I set this to 256, the problem seems to go away. With 256 set, I've
let the SELECT * query that we've been using to reproduce the error run
for about 10 minutes before I cancel it. With 2000 set, we'll get the
"not enough storage" error after about 2 minutes.
We also have the priorty of the service set to the default of 3. I
haven't found much in terms of how this should be set other than
1=background and 5=dedicated. Do you have any suggestions? This is a
system that is used by doctors to save case notes and query historical
notes using full text and standard sql queries. We'd like to maximize
the performance of full text, but we don't want to have a negative
effect on regular SQL. We'd also like to cut down on the time needed
for full & incremental rebuilds. Fulls now take 1 week and incrementals
take about 1 day. From what I've read, we should be using change
tracking. We plan on doing this as soon as we have this current problem
resolved. Any other suggestions beyond this?
Thanks for your help
Charlie
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Hi Charlie,
With SQL Server 2000 FTS you're somewhat limited with tables of this scale.
You could try to horizontally partition the table into multiple smaller
tables (say partition on a date range or some other column specific range of
values) and the FT Index the smaller tables using separate FT Catalogs. I'd
also highly recommend that you use CONTAINSTABLE or FREETEXTTABLE with the
Top_N_Rank value (see KB article Q240833) when running FTS SELECT * queries
against the larger FT Catalog. I'd also highly recommend that you look at
SQL Server 2005 as many of these limitations go away with Yukon FTS.
If you're 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. However, this will not fix the error
caused by large *.ps* files, as does setting the MaxPropStoreCachedSize
registry key value.
Once you've resolved this issue, use Change tracking with Update Index in
background, but be aware that an automatic Incremental or Full Population
will be run when you set this feature, so the MSSearch service can re-sync
up the FT Catalog with the SQL table data.
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Charlie Pyne" <cpyne@.partners.org> wrote in message
news:OeQeKOY$EHA.4092@.TK2MSFTNGP09.phx.gbl...
> Hi John,
> I don't see any errors related to Master Merge. I do see this error a
> bunch of times during our initial full population...
> The entry <MSSQL75://SQLSERVER/286302EC/00000342> in the hash map on
> project <SQLServer SQL0000600005> cannot be updated. Error: 8007054e - A
> description for this error could not be found.
> MS had told us to set MaxPropStoreCachedSize based on the size of these
> two files...
> 00000001.ps1 = 775MB
> 00000001.ps2 = 2.26GB
> If I set this to 256, the problem seems to go away. With 256 set, I've
> let the SELECT * query that we've been using to reproduce the error run
> for about 10 minutes before I cancel it. With 2000 set, we'll get the
> "not enough storage" error after about 2 minutes.
> We also have the priorty of the service set to the default of 3. I
> haven't found much in terms of how this should be set other than
> 1=background and 5=dedicated. Do you have any suggestions? This is a
> system that is used by doctors to save case notes and query historical
> notes using full text and standard sql queries. We'd like to maximize
> the performance of full text, but we don't want to have a negative
> effect on regular SQL. We'd also like to cut down on the time needed
> for full & incremental rebuilds. Fulls now take 1 week and incrementals
> take about 1 day. From what I've read, we should be using change
> tracking. We plan on doing this as soon as we have this current problem
> resolved. Any other suggestions beyond this?
> Thanks for your help
> Charlie
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
2012年2月26日星期日
Full or Diff backup impact on Log size?
I have a question regarding FUll and differential backup.
We we take full or diff back up, does it create lot of logs ie. Does full or diff backup has any impact on log size?
Thanks
No, not in my experience.|||The backup itself will not add much to the log, but it will hold up log truncation for the duration of the backup so that if there is a lot of other activity being logged by other users during the backujp, the log can become increasingly full. Once the backup is done, the log should be able to truncate as usual (for SIMPLE recovery mode.)|||Thanks for the replies.So taking full or diff backup does not create additional logs?. It will be just the logs of incoming transactions (not any additional logs because of full or diff backup)?
Thanks
|||That's correct.
Full Database backup size.....
When I take the full backup of the database the size of the backup file
varies.
Here is the scenario.
When I took for the first day, the size of the database was 1.5gb
Next day full back up size was 1.6gb(it had today's business data)
Third day full bakup is 1.4gb (it had the third day business data)
None of my application deletes the data from the above database.
Is it not the size of the backup file should increase as the data in the
database grows?
Is there any other factor (like shrinking etc....) is affecting the size of
the backup file.
SQL2K.
Just curious.
Thanks,
AllenThe size of the backup can also fluctuate based on non-data space being
consumed by the database. For example, if the data device is 15gb, 10gb in
use by data and 5gb free and/or the log is 5gb, with 2gb in use and 3gb free
,
the overall backup size will be different/larger then if there was no
unused/white-space in the DBs devices.
"AllenHubatka" wrote:
> When I take the full backup of the database the size of the backup file
> varies.
> Here is the scenario.
> When I took for the first day, the size of the database was 1.5gb
> Next day full back up size was 1.6gb(it had today's business data)
> Third day full bakup is 1.4gb (it had the third day business data)
> None of my application deletes the data from the above database.
> Is it not the size of the backup file should increase as the data in the
> database grows?
> Is there any other factor (like shrinking etc....) is affecting the size
of
> the backup file.
> SQL2K.
> Just curious.
> Thanks,
> Allen
>
>|||if the Dboption you have chosen Is it Full or Simple and If set to
simple The Log file Will be Shrunk when it Reaches more than 70% on a
checkpoint and also if you have checked autoShrink option both the
data and log file will be shrink ,its a background operation which
happens when u r server isnt busy enough.
Thanks,
Saradhi|||The database backup process goes to the Tlog to capture all of the
transactions since the backup began... which can increase the size...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"AllenHubatka" <AllenHubtka_67@.hotmail.com> wrote in message
news:OrGCxLZqFHA.2596@.TK2MSFTNGP09.phx.gbl...
> When I take the full backup of the database the size of the backup file
> varies.
> Here is the scenario.
> When I took for the first day, the size of the database was 1.5gb
> Next day full back up size was 1.6gb(it had today's business data)
> Third day full bakup is 1.4gb (it had the third day business data)
> None of my application deletes the data from the above database.
> Is it not the size of the backup file should increase as the data in the
> database grows?
> Is there any other factor (like shrinking etc....) is affecting the size
> of
> the backup file.
> SQL2K.
> Just curious.
> Thanks,
> Allen
>
varies.
Here is the scenario.
When I took for the first day, the size of the database was 1.5gb
Next day full back up size was 1.6gb(it had today's business data)
Third day full bakup is 1.4gb (it had the third day business data)
None of my application deletes the data from the above database.
Is it not the size of the backup file should increase as the data in the
database grows?
Is there any other factor (like shrinking etc....) is affecting the size of
the backup file.
SQL2K.
Just curious.
Thanks,
AllenThe size of the backup can also fluctuate based on non-data space being
consumed by the database. For example, if the data device is 15gb, 10gb in
use by data and 5gb free and/or the log is 5gb, with 2gb in use and 3gb free
,
the overall backup size will be different/larger then if there was no
unused/white-space in the DBs devices.
"AllenHubatka" wrote:
> When I take the full backup of the database the size of the backup file
> varies.
> Here is the scenario.
> When I took for the first day, the size of the database was 1.5gb
> Next day full back up size was 1.6gb(it had today's business data)
> Third day full bakup is 1.4gb (it had the third day business data)
> None of my application deletes the data from the above database.
> Is it not the size of the backup file should increase as the data in the
> database grows?
> Is there any other factor (like shrinking etc....) is affecting the size
of
> the backup file.
> SQL2K.
> Just curious.
> Thanks,
> Allen
>
>|||if the Dboption you have chosen Is it Full or Simple and If set to
simple The Log file Will be Shrunk when it Reaches more than 70% on a
checkpoint and also if you have checked autoShrink option both the
data and log file will be shrink ,its a background operation which
happens when u r server isnt busy enough.
Thanks,
Saradhi|||The database backup process goes to the Tlog to capture all of the
transactions since the backup began... which can increase the size...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"AllenHubatka" <AllenHubtka_67@.hotmail.com> wrote in message
news:OrGCxLZqFHA.2596@.TK2MSFTNGP09.phx.gbl...
> When I take the full backup of the database the size of the backup file
> varies.
> Here is the scenario.
> When I took for the first day, the size of the database was 1.5gb
> Next day full back up size was 1.6gb(it had today's business data)
> Third day full bakup is 1.4gb (it had the third day business data)
> None of my application deletes the data from the above database.
> Is it not the size of the backup file should increase as the data in the
> database grows?
> Is there any other factor (like shrinking etc....) is affecting the size
> of
> the backup file.
> SQL2K.
> Just curious.
> Thanks,
> Allen
>
Full Database backup size.....
When I take the full backup of the database the size of the backup file
varies.
Here is the scenario.
When I took for the first day, the size of the database was 1.5gb
Next day full back up size was 1.6gb(it had today's business data)
Third day full bakup is 1.4gb (it had the third day business data)
None of my application deletes the data from the above database.
Is it not the size of the backup file should increase as the data in the
database grows?
Is there any other factor (like shrinking etc....) is affecting the size of
the backup file.
SQL2K.
Just curious.
Thanks,
AllenThe size of the backup can also fluctuate based on non-data space being
consumed by the database. For example, if the data device is 15gb, 10gb in
use by data and 5gb free and/or the log is 5gb, with 2gb in use and 3gb free,
the overall backup size will be different/larger then if there was no
unused/white-space in the DBs devices.
"AllenHubatka" wrote:
> When I take the full backup of the database the size of the backup file
> varies.
> Here is the scenario.
> When I took for the first day, the size of the database was 1.5gb
> Next day full back up size was 1.6gb(it had today's business data)
> Third day full bakup is 1.4gb (it had the third day business data)
> None of my application deletes the data from the above database.
> Is it not the size of the backup file should increase as the data in the
> database grows?
> Is there any other factor (like shrinking etc....) is affecting the size of
> the backup file.
> SQL2K.
> Just curious.
> Thanks,
> Allen
>
>|||if the Dboption you have chosen Is it Full or Simple and If set to
simple The Log file Will be Shrunk when it Reaches more than 70% on a
checkpoint and also if you have checked autoShrink option both the
data and log file will be shrink ,its a background operation which
happens when u r server isnt busy enough.
Thanks,
Saradhi|||The database backup process goes to the Tlog to capture all of the
transactions since the backup began... which can increase the size...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"AllenHubatka" <AllenHubtka_67@.hotmail.com> wrote in message
news:OrGCxLZqFHA.2596@.TK2MSFTNGP09.phx.gbl...
> When I take the full backup of the database the size of the backup file
> varies.
> Here is the scenario.
> When I took for the first day, the size of the database was 1.5gb
> Next day full back up size was 1.6gb(it had today's business data)
> Third day full bakup is 1.4gb (it had the third day business data)
> None of my application deletes the data from the above database.
> Is it not the size of the backup file should increase as the data in the
> database grows?
> Is there any other factor (like shrinking etc....) is affecting the size
> of
> the backup file.
> SQL2K.
> Just curious.
> Thanks,
> Allen
>
varies.
Here is the scenario.
When I took for the first day, the size of the database was 1.5gb
Next day full back up size was 1.6gb(it had today's business data)
Third day full bakup is 1.4gb (it had the third day business data)
None of my application deletes the data from the above database.
Is it not the size of the backup file should increase as the data in the
database grows?
Is there any other factor (like shrinking etc....) is affecting the size of
the backup file.
SQL2K.
Just curious.
Thanks,
AllenThe size of the backup can also fluctuate based on non-data space being
consumed by the database. For example, if the data device is 15gb, 10gb in
use by data and 5gb free and/or the log is 5gb, with 2gb in use and 3gb free,
the overall backup size will be different/larger then if there was no
unused/white-space in the DBs devices.
"AllenHubatka" wrote:
> When I take the full backup of the database the size of the backup file
> varies.
> Here is the scenario.
> When I took for the first day, the size of the database was 1.5gb
> Next day full back up size was 1.6gb(it had today's business data)
> Third day full bakup is 1.4gb (it had the third day business data)
> None of my application deletes the data from the above database.
> Is it not the size of the backup file should increase as the data in the
> database grows?
> Is there any other factor (like shrinking etc....) is affecting the size of
> the backup file.
> SQL2K.
> Just curious.
> Thanks,
> Allen
>
>|||if the Dboption you have chosen Is it Full or Simple and If set to
simple The Log file Will be Shrunk when it Reaches more than 70% on a
checkpoint and also if you have checked autoShrink option both the
data and log file will be shrink ,its a background operation which
happens when u r server isnt busy enough.
Thanks,
Saradhi|||The database backup process goes to the Tlog to capture all of the
transactions since the backup began... which can increase the size...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"AllenHubatka" <AllenHubtka_67@.hotmail.com> wrote in message
news:OrGCxLZqFHA.2596@.TK2MSFTNGP09.phx.gbl...
> When I take the full backup of the database the size of the backup file
> varies.
> Here is the scenario.
> When I took for the first day, the size of the database was 1.5gb
> Next day full back up size was 1.6gb(it had today's business data)
> Third day full bakup is 1.4gb (it had the third day business data)
> None of my application deletes the data from the above database.
> Is it not the size of the backup file should increase as the data in the
> database grows?
> Is there any other factor (like shrinking etc....) is affecting the size
> of
> the backup file.
> SQL2K.
> Just curious.
> Thanks,
> Allen
>
Full Database backup size.....
When I take the full backup of the database the size of the backup file
varies.
Here is the scenario.
When I took for the first day, the size of the database was 1.5gb
Next day full back up size was 1.6gb(it had today's business data)
Third day full bakup is 1.4gb (it had the third day business data)
None of my application deletes the data from the above database.
Is it not the size of the backup file should increase as the data in the
database grows?
Is there any other factor (like shrinking etc....) is affecting the size of
the backup file.
SQL2K.
Just curious.
Thanks,
Allen
The size of the backup can also fluctuate based on non-data space being
consumed by the database. For example, if the data device is 15gb, 10gb in
use by data and 5gb free and/or the log is 5gb, with 2gb in use and 3gb free,
the overall backup size will be different/larger then if there was no
unused/white-space in the DBs devices.
"AllenHubatka" wrote:
> When I take the full backup of the database the size of the backup file
> varies.
> Here is the scenario.
> When I took for the first day, the size of the database was 1.5gb
> Next day full back up size was 1.6gb(it had today's business data)
> Third day full bakup is 1.4gb (it had the third day business data)
> None of my application deletes the data from the above database.
> Is it not the size of the backup file should increase as the data in the
> database grows?
> Is there any other factor (like shrinking etc....) is affecting the size of
> the backup file.
> SQL2K.
> Just curious.
> Thanks,
> Allen
>
>
|||if the Dboption you have chosen Is it Full or Simple and If set to
simple The Log file Will be Shrunk when it Reaches more than 70% on a
checkpoint and also if you have checked autoShrink option both the
data and log file will be shrink ,its a background operation which
happens when u r server isnt busy enough.
Thanks,
Saradhi
|||The database backup process goes to the Tlog to capture all of the
transactions since the backup began... which can increase the size...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"AllenHubatka" <AllenHubtka_67@.hotmail.com> wrote in message
news:OrGCxLZqFHA.2596@.TK2MSFTNGP09.phx.gbl...
> When I take the full backup of the database the size of the backup file
> varies.
> Here is the scenario.
> When I took for the first day, the size of the database was 1.5gb
> Next day full back up size was 1.6gb(it had today's business data)
> Third day full bakup is 1.4gb (it had the third day business data)
> None of my application deletes the data from the above database.
> Is it not the size of the backup file should increase as the data in the
> database grows?
> Is there any other factor (like shrinking etc....) is affecting the size
> of
> the backup file.
> SQL2K.
> Just curious.
> Thanks,
> Allen
>
varies.
Here is the scenario.
When I took for the first day, the size of the database was 1.5gb
Next day full back up size was 1.6gb(it had today's business data)
Third day full bakup is 1.4gb (it had the third day business data)
None of my application deletes the data from the above database.
Is it not the size of the backup file should increase as the data in the
database grows?
Is there any other factor (like shrinking etc....) is affecting the size of
the backup file.
SQL2K.
Just curious.
Thanks,
Allen
The size of the backup can also fluctuate based on non-data space being
consumed by the database. For example, if the data device is 15gb, 10gb in
use by data and 5gb free and/or the log is 5gb, with 2gb in use and 3gb free,
the overall backup size will be different/larger then if there was no
unused/white-space in the DBs devices.
"AllenHubatka" wrote:
> When I take the full backup of the database the size of the backup file
> varies.
> Here is the scenario.
> When I took for the first day, the size of the database was 1.5gb
> Next day full back up size was 1.6gb(it had today's business data)
> Third day full bakup is 1.4gb (it had the third day business data)
> None of my application deletes the data from the above database.
> Is it not the size of the backup file should increase as the data in the
> database grows?
> Is there any other factor (like shrinking etc....) is affecting the size of
> the backup file.
> SQL2K.
> Just curious.
> Thanks,
> Allen
>
>
|||if the Dboption you have chosen Is it Full or Simple and If set to
simple The Log file Will be Shrunk when it Reaches more than 70% on a
checkpoint and also if you have checked autoShrink option both the
data and log file will be shrink ,its a background operation which
happens when u r server isnt busy enough.
Thanks,
Saradhi
|||The database backup process goes to the Tlog to capture all of the
transactions since the backup began... which can increase the size...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"AllenHubatka" <AllenHubtka_67@.hotmail.com> wrote in message
news:OrGCxLZqFHA.2596@.TK2MSFTNGP09.phx.gbl...
> When I take the full backup of the database the size of the backup file
> varies.
> Here is the scenario.
> When I took for the first day, the size of the database was 1.5gb
> Next day full back up size was 1.6gb(it had today's business data)
> Third day full bakup is 1.4gb (it had the third day business data)
> None of my application deletes the data from the above database.
> Is it not the size of the backup file should increase as the data in the
> database grows?
> Is there any other factor (like shrinking etc....) is affecting the size
> of
> the backup file.
> SQL2K.
> Just curious.
> Thanks,
> Allen
>
full database backup
Question 1
3 GHZ CPU (Intel pentium 4) single cpu + 2 GB Memory + SCSI HDD
Database size 10 GB - How long will full database backup take if the
backup is writing a file to the hard disk (separate hard disk)
Question 2
during this full backup are users and application able to access the
database
for example
a) select records
b) insert , update, delete records
or is the database backup causing the database to be exclusively
locked up ?
Thanks in advance"newbiegca_sqlsever2000" <newbiegca_sql2000@.sbcglobal.net> wrote in message
news:d11c4aba.0409192309.414e6edb@.posting.google.c om...
> Question 1
> 3 GHZ CPU (Intel pentium 4) single cpu + 2 GB Memory + SCSI HDD
> Database size 10 GB - How long will full database backup take if the
> backup is writing a file to the hard disk (separate hard disk)
No idea - just try it and see. You can backup to multiple files/devices at
once if you need to speed things up, although this will probably only help
if the devices are physically independent drives. See "Using Multiple Media
or Devices" in Books Online.
> Question 2
> during this full backup are users and application able to access the
> database
> for example
> a) select records
> b) insert , update, delete records
> or is the database backup causing the database to be exclusively
> locked up ?
> Thanks in advance
A backup is an online operation, and users can continue to access the
database, including modifying data, while the backup is running (see "Backup
Restrictions" in Books Online). You might also want to review the whole
"Backing Up and Restoring Databases" topic for more detailed information on
backups, recovery models etc.
Simon|||Thanks Simon
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
2012年2月19日星期日
FTS Index Size
Are there any references that help with estimating the size of a FTS index, or the whole catalog? I'm trying to do some capacity planning, and can't find anything to help me guess how much disk I'll need.
Unfornitually I haven't seen anything out there on how to do capacity planning for FTS.|||This is surprising to hear, as I thought FTS was a first-class, well-supported feature.
|||
Unfornitually I haven't seen anything out there on how to do capacity planning for FTS.|||This is surprising to hear, as I thought FTS was a first-class, well-supported feature.
|||
MikeBlaszczak wrote:
This is surprising to hear, as I thought FTS was a first-class, well-supported feature.
Hi Mike,
Nope, it hasn't been a first-class index in the same perspective as clustred or non-clustred index. However, that will change with the next release of SQL Server 2008, codename Katmai. I've long ago advocated this while a FTE, but alas it has taken many years to occur.
Regards,
John
John T. Kane
Search Evangelist, Intellisearch
email: john.kane@.intellisearch.no
I'm not worried about it being a first-class index. I'm worried about it being a first-class feature. That is: something that's well-supported and usable in enterprise applications. If there's no documentation about how to do capacity planning for a deployment, it's hard for me to think that FTS is a first-class feature.
FTS Index Size
Are there any references that help with estimating the size of a FTS index, or the whole catalog? I'm trying to do some capacity planning, and can't find anything to help me guess how much disk I'll need.
Unfornitually I haven't seen anything out there on how to do capacity planning for FTS.|||This is surprising to hear, as I thought FTS was a first-class, well-supported feature.
|||
Unfornitually I haven't seen anything out there on how to do capacity planning for FTS.|||This is surprising to hear, as I thought FTS was a first-class, well-supported feature.
|||
This is surprising to hear, as I thought FTS was a first-class, well-supported feature.
Hi Mike,
Nope, it hasn't been a first-class index in the same perspective as clustred or non-clustred index. However, that will change with the next release of SQL Server 2008, codename Katmai. I've long ago advocated this while a FTE, but alas it has taken many years to occur.
Regards,
John
John T. Kane
Search Evangelist, Intellisearch
email: john.kane@.intellisearch.no
I'm not worried about it being a first-class index. I'm worried about it being a first-class feature. That is: something that's well-supported and usable in enterprise applications. If there's no documentation about how to do capacity planning for a deployment, it's hard for me to think that FTS is a first-class feature.
fts catalog size
I am new to using FTS for searching, so please forgive the ignorance if there is an obvious answer. I've set up a table specifically to hold searchable data rather than have a number of table joins for searching. I've imported about 1/4 of the data into the table and set up a catalog on the table. The catalog is currently 62 mb. Is there a suggested limit to a catalog size for FTS and if so, is there a way to set up a catalog so that when it reaches this size, it splits off and creates a 2nd catalog? Thanks.No you can't throttle the size, nor can you have another catalog created dynamically. Note that a full text index for a table can't span catalogs, ie one index one catalog. -- Hilary Cotter Director of Text Mining and Database Strategy RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. This posting is my own and doesn't necessarily represent RelevantNoise's positions, strategies or opinions. Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com wrote in message news:ec61e74c-e1b9-4f40-a99c-0c7ada543225@.discussions.microsoft.com... I am new to using FTS for searching, so please forgive the ignorance if there is an obvious answer. I've set up a table specifically to hold searchable data rather than have a number of table joins for searching. I've imported about 1/4 of the data into the table and set up a catalog on the table. The catalog is currently 62 mb. Is there a suggested limit to a catalog size for FTS and if so, is there a way to set up a catalog so that when it reaches this size, it splits off and creates a 2nd catalog? Thanks.
订阅：
博文 (Atom)