Hi,
How do I best update a Full Text Index on SQL2000 sp3, for Merge Replication
(Push Subscription)?
Here is the config:
Site 1:
I have one table with one column (ntext), which I have created a full text
index.
The table updated once per day, data is never updated, always insert new
rows.
The table is Read-Only.
Everyone in Site 1 is happy... full text working good.
Site2: - Branch Office
I have created a merge replication, (push subscription on Site1):
We now have a copy of this read-only database/table in site #2.
I created a full text index on it, this is also working good.
Now after 24 hours, Site 1 gets 1000 new rows of data.
Since I have a push subscription, these new 1000 rows of data will get
pushed to site 2.
How do I automatic the full text index so it will add these new rows (column
data) to the full text index?
Once again, these are not updates, always new rows of data.
I would like to avoid using the Full Text Scheduler.
If I configured a Trigger on Site 2, how would it know when the 1000 rows
where finished inserting rows?
Thanks
Russell Mangel
Las Vegas, NV
use change tracking with update index in background on the subscriber.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Russell Mangel" <russell@.tymer.net> wrote in message
news:eDrSSZHeEHA.2384@.TK2MSFTNGP09.phx.gbl...
> Hi,
> How do I best update a Full Text Index on SQL2000 sp3, for Merge
Replication
> (Push Subscription)?
> Here is the config:
> Site 1:
> I have one table with one column (ntext), which I have created a full text
> index.
> The table updated once per day, data is never updated, always insert new
> rows.
> The table is Read-Only.
> Everyone in Site 1 is happy... full text working good.
> Site2: - Branch Office
> I have created a merge replication, (push subscription on Site1):
> We now have a copy of this read-only database/table in site #2.
> I created a full text index on it, this is also working good.
> Now after 24 hours, Site 1 gets 1000 new rows of data.
> Since I have a push subscription, these new 1000 rows of data will get
> pushed to site 2.
> How do I automatic the full text index so it will add these new rows
(column
> data) to the full text index?
> Once again, these are not updates, always new rows of data.
> I would like to avoid using the Full Text Scheduler.
> If I configured a Trigger on Site 2, how would it know when the 1000 rows
> where finished inserting rows?
> Thanks
> Russell Mangel
> Las Vegas, NV
>
>
>
|||Thanks for your reply, I will try this.
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:ub1IZWPeEHA.2908@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> use change tracking with update index in background on the subscriber.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Russell Mangel" <russell@.tymer.net> wrote in message
> news:eDrSSZHeEHA.2384@.TK2MSFTNGP09.phx.gbl...
> Replication
text[vbcol=seagreen]
> (column
rows
>
2012年3月21日星期三
Full text search
Hi everyone
I am using SQL server 2000 with SP3 on Windows 2003 server.
I have scheduled a job to populate a full text index incrementally. Everytime it runs the job to populate the index table it writes some messages into event log.
Like this:
1.
Event Type : Information
Event Source : Microsoft Search
Event Category: Gatherer
Event ID : 3019
Date :8/7/2004
Time :2:21:00 PM
User : N/A
Computer : ARC-03-DEV
Description:1
The crawl on project <SQL Server SQL00002000009> has started.
2.
Event Type : Information
Event Source : Microsoft Search
Event Category: Gatherer
Event ID : 3047
Date :8/7/2004
Time :2:21:00 PM
User : N/A
Computer : ARC-03-DEV
Description:
The end of the incremental crawl for project <SQL Server SQL00002000009> has been detected. The Gatherer successfully processed 998 documents totaling 0K. It failed to filter 0 documents. 998 documents were modified. 0 URLs could not be reached or were denied access.
The event log is getting filled frequently.
Is there any way to stop this messages?
It is normal for Microsoft Search to write this message into EventLog?
Even though there is no chanage in the database it give the above messages.
Any one know the reason please help.
Quick reply is appreaciated.
Reagrda,
Atiq RahmanYes, there is a way to suppress the message, but it is a bad idea so I don't remember the details.
Yes, it is perfectly normal for the indexing service to issue those messages, even if it doesn't make any changes.
-PatP|||Hi Pat,
Thanks for the information.
I want to stop this entries getting into Eventlog. Even if it is bad, if you remember please let me know. I want to try it.
Regards,
Atiq Rahman
I am using SQL server 2000 with SP3 on Windows 2003 server.
I have scheduled a job to populate a full text index incrementally. Everytime it runs the job to populate the index table it writes some messages into event log.
Like this:
1.
Event Type : Information
Event Source : Microsoft Search
Event Category: Gatherer
Event ID : 3019
Date :8/7/2004
Time :2:21:00 PM
User : N/A
Computer : ARC-03-DEV
Description:1
The crawl on project <SQL Server SQL00002000009> has started.
2.
Event Type : Information
Event Source : Microsoft Search
Event Category: Gatherer
Event ID : 3047
Date :8/7/2004
Time :2:21:00 PM
User : N/A
Computer : ARC-03-DEV
Description:
The end of the incremental crawl for project <SQL Server SQL00002000009> has been detected. The Gatherer successfully processed 998 documents totaling 0K. It failed to filter 0 documents. 998 documents were modified. 0 URLs could not be reached or were denied access.
The event log is getting filled frequently.
Is there any way to stop this messages?
It is normal for Microsoft Search to write this message into EventLog?
Even though there is no chanage in the database it give the above messages.
Any one know the reason please help.
Quick reply is appreaciated.
Reagrda,
Atiq RahmanYes, there is a way to suppress the message, but it is a bad idea so I don't remember the details.
Yes, it is perfectly normal for the indexing service to issue those messages, even if it doesn't make any changes.
-PatP|||Hi Pat,
Thanks for the information.
I want to stop this entries getting into Eventlog. Even if it is bad, if you remember please let me know. I want to try it.
Regards,
Atiq Rahman
2012年3月9日星期五
Full Text catalogue build failure
I'm having severe problems implementing Search/Full Text Indexing service
with SQL Server 2000 (SP3). Following a successful test using a development
server (Win Svr 2003) I've attempted to roll out an application that makes
use of Full Text indexing using a production server. The development server
and production server are identical in terms of OS, SQL server, other
applications etc and I've followed the same steps that were used to implement
the Full Text Search on the developement server, on the production server.
Initially my problems started with not being able to create a Full Text
Indexiong catalogue. I've now rectified this (it was to do with the logon id
being used during the install of Search service - as part of the SQL server
install).
I now have a problem with actually populating the catalogue itself. The
Event log is issuing two errors when trying to populate the catalogue. The
first is:
"The crawl seed <MSSQL75://SQLServer/014935cb> in project <SQLServer
SQL0001900005> cannot be accessed. Error: 80040d07 - The specified URL is
excluded and will not be cataloged. The URL restriction rules may have to be
modified to include this URL."
The second is:
"The crawl on project <SQLServer SQL0001900005> cannot be started. All of
the crawl seeds have been ignored because of host, extension, or other URL
restrictions. Error: 80040d07 - The specified URL is excluded and will not be
cataloged. The URL restriction rules may have to be modified to include this
URL."
Any help with this would be greatly appreciated.
I've spent ages trawling the web to see if anybody else has had a similar
problem. The only items I found appear to point to Sharepoint services. As
far as I know SharePoint is not installed on the server in question (or is it
installed by default?) I feel a little bit out of my depth on this one!
Thanks
Kevin
Kevin,
I think I know what the problem might be. This invovles modifying the
registry so I will issue the standard disclaimer of proceed with caution
when making any registry changes.
This problem is caused because the SQL Server 2000 Protocol Handler is
missing
from the Registry. The following key is the key that should be added to the
registry
and the problem should be fixed:
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\ ProtocolHandlers]
"MSSQL75"="sqlfth75.sqlfth75.1"
Most likely you will see the following entries there, but not the one above.
"File"="MSSearch.FileHandler.1"
"Http"="MSSearch.HttpHandler.1"
"OleDb"="MSSearch.OleDbHandler.1"
Let me know if this works.
David Gerard
Microsoft SQL Server Support
|||"David Gerard" wrote:
> Let me know if this works.
>
David,
Many Many Thanks.
Added the key, restarted Full Text Search service and hey presto.
Thanks again
Kevin
with SQL Server 2000 (SP3). Following a successful test using a development
server (Win Svr 2003) I've attempted to roll out an application that makes
use of Full Text indexing using a production server. The development server
and production server are identical in terms of OS, SQL server, other
applications etc and I've followed the same steps that were used to implement
the Full Text Search on the developement server, on the production server.
Initially my problems started with not being able to create a Full Text
Indexiong catalogue. I've now rectified this (it was to do with the logon id
being used during the install of Search service - as part of the SQL server
install).
I now have a problem with actually populating the catalogue itself. The
Event log is issuing two errors when trying to populate the catalogue. The
first is:
"The crawl seed <MSSQL75://SQLServer/014935cb> in project <SQLServer
SQL0001900005> cannot be accessed. Error: 80040d07 - The specified URL is
excluded and will not be cataloged. The URL restriction rules may have to be
modified to include this URL."
The second is:
"The crawl on project <SQLServer SQL0001900005> cannot be started. All of
the crawl seeds have been ignored because of host, extension, or other URL
restrictions. Error: 80040d07 - The specified URL is excluded and will not be
cataloged. The URL restriction rules may have to be modified to include this
URL."
Any help with this would be greatly appreciated.
I've spent ages trawling the web to see if anybody else has had a similar
problem. The only items I found appear to point to Sharepoint services. As
far as I know SharePoint is not installed on the server in question (or is it
installed by default?) I feel a little bit out of my depth on this one!
Thanks
Kevin
Kevin,
I think I know what the problem might be. This invovles modifying the
registry so I will issue the standard disclaimer of proceed with caution
when making any registry changes.
This problem is caused because the SQL Server 2000 Protocol Handler is
missing
from the Registry. The following key is the key that should be added to the
registry
and the problem should be fixed:
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\ ProtocolHandlers]
"MSSQL75"="sqlfth75.sqlfth75.1"
Most likely you will see the following entries there, but not the one above.
"File"="MSSearch.FileHandler.1"
"Http"="MSSearch.HttpHandler.1"
"OleDb"="MSSearch.OleDbHandler.1"
Let me know if this works.
David Gerard
Microsoft SQL Server Support
|||"David Gerard" wrote:
> Let me know if this works.
>
David,
Many Many Thanks.
Added the key, restarted Full Text Search service and hey presto.
Thanks again
Kevin
Full Text catalogue build failure
I'm having severe problems implementing Search/Full Text Indexing service
with SQL Server 2000 (SP3). Following a successful test using a development
server (Win Svr 2003) I've attempted to roll out an application that makes
use of Full Text indexing using a production server. The development server
and production server are identical in terms of OS, SQL server, other
applications etc and I've followed the same steps that were used to implemen
t
the Full Text Search on the developement server, on the production server.
Initially my problems started with not being able to create a Full Text
Indexiong catalogue. I've now rectified this (it was to do with the logon i
d
being used during the install of Search service - as part of the SQL server
install).
I now have a problem with actually populating the catalogue itself. The
Event log is issuing two errors when trying to populate the catalogue. The
first is:
"The crawl seed <MSSQL75://SQLServer/014935cb> in project <SQLServer
SQL0001900005> cannot be accessed. Error: 80040d07 - The specified URL is
excluded and will not be cataloged. The URL restriction rules may have to be
modified to include this URL."
The second is:
"The crawl on project <SQLServer SQL0001900005> cannot be started. All of
the crawl seeds have been ignored because of host, extension, or other URL
restrictions. Error: 80040d07 - The specified URL is excluded and will not b
e
cataloged. The URL restriction rules may have to be modified to include this
URL."
Any help with this would be greatly appreciated.
I've spent ages trawling the web to see if anybody else has had a similar
problem. The only items I found appear to point to Sharepoint services. As
far as I know SharePoint is not installed on the server in question (or is i
t
installed by default?) I feel a little bit out of my depth on this one!
Thanks
KevinKevin,
I think I know what the problem might be. This invovles modifying the
registry so I will issue the standard disclaimer of proceed with caution
when making any registry changes.
This problem is caused because the SQL Server 2000 Protocol Handler is
missing
from the Registry. The following key is the key that should be added to the
registry
and the problem should be fixed:
& #91;HKEY_LOCAL_MACHINE\SOFTWARE\Microsof
t\Search\1.0\ProtocolHandlers]
"MSSQL75"="sqlfth75.sqlfth75.1"
Most likely you will see the following entries there, but not the one above.
"File"="MSSearch.FileHandler.1"
"Http"="MSSearch.HttpHandler.1"
"OleDb"="MSSearch.OleDbHandler.1"
Let me know if this works.
David Gerard
Microsoft SQL Server Support|||"David Gerard" wrote:
> Let me know if this works.
>
David,
Many Many Thanks.
Added the key, restarted Full Text Search service and hey presto.
Thanks again
Kevin
with SQL Server 2000 (SP3). Following a successful test using a development
server (Win Svr 2003) I've attempted to roll out an application that makes
use of Full Text indexing using a production server. The development server
and production server are identical in terms of OS, SQL server, other
applications etc and I've followed the same steps that were used to implemen
t
the Full Text Search on the developement server, on the production server.
Initially my problems started with not being able to create a Full Text
Indexiong catalogue. I've now rectified this (it was to do with the logon i
d
being used during the install of Search service - as part of the SQL server
install).
I now have a problem with actually populating the catalogue itself. The
Event log is issuing two errors when trying to populate the catalogue. The
first is:
"The crawl seed <MSSQL75://SQLServer/014935cb> in project <SQLServer
SQL0001900005> cannot be accessed. Error: 80040d07 - The specified URL is
excluded and will not be cataloged. The URL restriction rules may have to be
modified to include this URL."
The second is:
"The crawl on project <SQLServer SQL0001900005> cannot be started. All of
the crawl seeds have been ignored because of host, extension, or other URL
restrictions. Error: 80040d07 - The specified URL is excluded and will not b
e
cataloged. The URL restriction rules may have to be modified to include this
URL."
Any help with this would be greatly appreciated.
I've spent ages trawling the web to see if anybody else has had a similar
problem. The only items I found appear to point to Sharepoint services. As
far as I know SharePoint is not installed on the server in question (or is i
t
installed by default?) I feel a little bit out of my depth on this one!
Thanks
KevinKevin,
I think I know what the problem might be. This invovles modifying the
registry so I will issue the standard disclaimer of proceed with caution
when making any registry changes.
This problem is caused because the SQL Server 2000 Protocol Handler is
missing
from the Registry. The following key is the key that should be added to the
registry
and the problem should be fixed:
& #91;HKEY_LOCAL_MACHINE\SOFTWARE\Microsof
t\Search\1.0\ProtocolHandlers]
"MSSQL75"="sqlfth75.sqlfth75.1"
Most likely you will see the following entries there, but not the one above.
"File"="MSSearch.FileHandler.1"
"Http"="MSSearch.HttpHandler.1"
"OleDb"="MSSearch.OleDbHandler.1"
Let me know if this works.
David Gerard
Microsoft SQL Server Support|||"David Gerard" wrote:
> Let me know if this works.
>
David,
Many Many Thanks.
Added the key, restarted Full Text Search service and hey presto.
Thanks again
Kevin
2012年2月24日星期五
Full backup and Transaction Logs
Recently, with a couple of DB's on SQL 2000 Enterprise SP3, I noticed that
doing a Full backup isn't truncating or shrinking the log file. The log
file got to be over 10GB and forced the Data drive to fill up.
Is there some reason for this? I thought when full backups were successful,
they should be truncating the log file.
I can backup log with truncate_only and then issue a dbcc shrinkfile to get
it back down, but I'd like to know why full backups aren't working first.
Thanks.
Kevin A wrote:
> Recently, with a couple of DB's on SQL 2000 Enterprise SP3, I noticed that
> doing a Full backup isn't truncating or shrinking the log file. The log
> file got to be over 10GB and forced the Data drive to fill up.
> Is there some reason for this? I thought when full backups were successful,
> they should be truncating the log file.
> I can backup log with truncate_only and then issue a dbcc shrinkfile to get
> it back down, but I'd like to know why full backups aren't working first.
> Thanks.
>
Nope. See the topic "Truncating the Transaction Log" in Books Online.
If you're running in Full or Bulk-Logged recovery mode, you MUST do
transaction log backups, or the log will continue to accumulate
transactional data. Even in Simple mode, a single large transaction can
cause the log to grow.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Sorry, I just need a bit more clarification.
Are you saying, after a full backup, I need to issue the truncate command?
That doesn't always seem to shrink the Logfile. I have to manually issue
the dbcc srhinkfile.
What would be the suggested steps to take, after the full backup of the DB?
Thanks.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45A3927B.3030202@.realsqlguy.com...
> Kevin A wrote:
> Nope. See the topic "Truncating the Transaction Log" in Books Online. If
> you're running in Full or Bulk-Logged recovery mode, you MUST do
> transaction log backups, or the log will continue to accumulate
> transactional data. Even in Simple mode, a single large transaction can
> cause the log to grow.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||Hello,
FULL database backup will not clear the contents in tranasction log file.
The purpose of FULL recovery model is to perform the POIN IN TIME recovery.
In order to do this
you need to take the tranasction log backup. So when ever you perform a
transaction log backup automatically transaction log will be cleared from
the LDF file and will
leep the LDF file growth under control. Incase if you need to do a recovery
on crash/point in time you can use these log backup files. Just read the
Transaction log backup topic in books online.
Thanks
Hari
"Kevin A" <kevina@.cqlcorp.com> wrote in message
news:eh2Ecu%23MHHA.4916@.TK2MSFTNGP06.phx.gbl...
> Recently, with a couple of DB's on SQL 2000 Enterprise SP3, I noticed that
> doing a Full backup isn't truncating or shrinking the log file. The log
> file got to be over 10GB and forced the Data drive to fill up.
> Is there some reason for this? I thought when full backups were
> successful, they should be truncating the log file.
> I can backup log with truncate_only and then issue a dbcc shrinkfile to
> get it back down, but I'd like to know why full backups aren't working
> first.
> Thanks.
>
|||Hello,
1. First take a look into the recovery model you use for the database.
2. If it is FULL or BULK_LOGGED then you have schedule a transction log
backup in regular intervals [Say every 15 minutes or so]. This frequency can
be changed based on ur data growth,.
Take a look into the BACKUP LOG command to backup the transaction log or you
could use the Maintenence plan wizard to automate the transaction log
backup.
This will make sure that you LDF will not grow drastically.
To reduce the size of 10 GB currently; you may to need to truncate the Log
first and use DBCC SHRINKFILE to reduce the file size. After that to bring
back the backup chain do:-
1. A full database backup
2. Followed with transaction log backups.
THANKS
Hari
"Kevin A" <kevina@.cqlcorp.com> wrote in message
news:ez%237O%23%23MHHA.4916@.TK2MSFTNGP06.phx.gbl.. .
> Sorry, I just need a bit more clarification.
> Are you saying, after a full backup, I need to issue the truncate command?
> That doesn't always seem to shrink the Logfile. I have to manually
> issue the dbcc srhinkfile.
> What would be the suggested steps to take, after the full backup of the
> DB?
> Thanks.
>
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:45A3927B.3030202@.realsqlguy.com...
>
|||Kevin A wrote:
> Sorry, I just need a bit more clarification.
> Are you saying, after a full backup, I need to issue the truncate command?
> That doesn't always seem to shrink the Logfile. I have to manually issue
> the dbcc srhinkfile.
> What would be the suggested steps to take, after the full backup of the DB?
> Thanks.
TRUNCATING is not SHRINKING, they are two different operations.
The log file is a "journal", recording every change that takes place in
your database. If the database is in Simple recovery mode, these
journal entries are automatically removed from the log once they've
completed. In Full or Bulk-Logged mode, they remain in the log until
you perform a log backup. This gives you the ability to restore the
database to any point in time, by "playing back" those journal entries
that you've backed up, an ability that you lose with Simple mode.
For example, suppose your database is in Simple mode, and you are doing
full backups nightly at 9:00pm. You suffer a hard drive crash at
8:00pm, corrupting your database. Your only option for recovery is to
restore the backup from 9:00pm the previous night, losing 23 hours of data.
Now, suppose that same database is in Full recovery mode. You're doing
nightly full backups at 9:00pm, and log backups every 5 minutes. Your
drive fails at 8:00pm, corrupting the database. You now have the
ability to restore the previous night's full backup, followed by the log
backups that have been done since then. At most, you lost 5 minutes of
data.
The space inside the transaction log is "recycled", as committed
transactions are flushed out (via a log backup), the space that they
were using is made available for new transactions. This helps to keep
the log file size under control. If you aren't flushing them out (i.e.
TRUNCATING), the log has to grow larger to hold new transactions.
SHRINKING is a different process - it will remove any free space from
the log, physically reducing the size of the log file. You should only
do this under extreme circumstances, it should NOT be part of your daily
routine. Repeated shrinking/growing of a file (log or database) will
lead to fragmentation, which will ultimately hurt performance. If you
have the disk space, you should allow the log to grow as needed, making
sure you're doing proper backups to keep the committed transactions
flushed out.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Thank you, you put the very well... Regards, John
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45A39711.7070204@.realsqlguy.com...
> Kevin A wrote:
> TRUNCATING is not SHRINKING, they are two different operations.
> The log file is a "journal", recording every change that takes place in
> your database. If the database is in Simple recovery mode, these journal
> entries are automatically removed from the log once they've completed. In
> Full or Bulk-Logged mode, they remain in the log until you perform a log
> backup. This gives you the ability to restore the database to any point
> in time, by "playing back" those journal entries that you've backed up, an
> ability that you lose with Simple mode.
> For example, suppose your database is in Simple mode, and you are doing
> full backups nightly at 9:00pm. You suffer a hard drive crash at 8:00pm,
> corrupting your database. Your only option for recovery is to restore the
> backup from 9:00pm the previous night, losing 23 hours of data.
> Now, suppose that same database is in Full recovery mode. You're doing
> nightly full backups at 9:00pm, and log backups every 5 minutes. Your
> drive fails at 8:00pm, corrupting the database. You now have the ability
> to restore the previous night's full backup, followed by the log backups
> that have been done since then. At most, you lost 5 minutes of data.
> The space inside the transaction log is "recycled", as committed
> transactions are flushed out (via a log backup), the space that they were
> using is made available for new transactions. This helps to keep the log
> file size under control. If you aren't flushing them out (i.e.
> TRUNCATING), the log has to grow larger to hold new transactions.
> SHRINKING is a different process - it will remove any free space from the
> log, physically reducing the size of the log file. You should only do
> this under extreme circumstances, it should NOT be part of your daily
> routine. Repeated shrinking/growing of a file (log or database) will lead
> to fragmentation, which will ultimately hurt performance. If you have the
> disk space, you should allow the log to grow as needed, making sure you're
> doing proper backups to keep the committed transactions flushed out.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||once i have a full backup of the database why would i need the log files
anymore? backupexec 9 indicates they will be removed during a full backup as
i would expect, but it isn't happening on one of my servers. thx if you have
more clues.
"Tracy McKibben" wrote:
> Kevin A wrote:
> Nope. See the topic "Truncating the Transaction Log" in Books Online.
> If you're running in Full or Bulk-Logged recovery mode, you MUST do
> transaction log backups, or the log will continue to accumulate
> transactional data. Even in Simple mode, a single large transaction can
> cause the log to grow.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>
|||help wrote:
> once i have a full backup of the database why would i need the log files
> anymore? backupexec 9 indicates they will be removed during a full backup as
> i would expect, but it isn't happening on one of my servers. thx if you have
> more clues.
>
Technically, you don't need log files that predate a full backup,
however, it's good insurance to keep as many backups as you can afford
to keep. As the DBA, you're responsible for protecting the company's
data, so keep as many backups as you can.
As for why they're not being deleted by BackupExec, I have no idea. I
don't use third party backup tools like that.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
doing a Full backup isn't truncating or shrinking the log file. The log
file got to be over 10GB and forced the Data drive to fill up.
Is there some reason for this? I thought when full backups were successful,
they should be truncating the log file.
I can backup log with truncate_only and then issue a dbcc shrinkfile to get
it back down, but I'd like to know why full backups aren't working first.
Thanks.
Kevin A wrote:
> Recently, with a couple of DB's on SQL 2000 Enterprise SP3, I noticed that
> doing a Full backup isn't truncating or shrinking the log file. The log
> file got to be over 10GB and forced the Data drive to fill up.
> Is there some reason for this? I thought when full backups were successful,
> they should be truncating the log file.
> I can backup log with truncate_only and then issue a dbcc shrinkfile to get
> it back down, but I'd like to know why full backups aren't working first.
> Thanks.
>
Nope. See the topic "Truncating the Transaction Log" in Books Online.
If you're running in Full or Bulk-Logged recovery mode, you MUST do
transaction log backups, or the log will continue to accumulate
transactional data. Even in Simple mode, a single large transaction can
cause the log to grow.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Sorry, I just need a bit more clarification.
Are you saying, after a full backup, I need to issue the truncate command?
That doesn't always seem to shrink the Logfile. I have to manually issue
the dbcc srhinkfile.
What would be the suggested steps to take, after the full backup of the DB?
Thanks.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45A3927B.3030202@.realsqlguy.com...
> Kevin A wrote:
> Nope. See the topic "Truncating the Transaction Log" in Books Online. If
> you're running in Full or Bulk-Logged recovery mode, you MUST do
> transaction log backups, or the log will continue to accumulate
> transactional data. Even in Simple mode, a single large transaction can
> cause the log to grow.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||Hello,
FULL database backup will not clear the contents in tranasction log file.
The purpose of FULL recovery model is to perform the POIN IN TIME recovery.
In order to do this
you need to take the tranasction log backup. So when ever you perform a
transaction log backup automatically transaction log will be cleared from
the LDF file and will
leep the LDF file growth under control. Incase if you need to do a recovery
on crash/point in time you can use these log backup files. Just read the
Transaction log backup topic in books online.
Thanks
Hari
"Kevin A" <kevina@.cqlcorp.com> wrote in message
news:eh2Ecu%23MHHA.4916@.TK2MSFTNGP06.phx.gbl...
> Recently, with a couple of DB's on SQL 2000 Enterprise SP3, I noticed that
> doing a Full backup isn't truncating or shrinking the log file. The log
> file got to be over 10GB and forced the Data drive to fill up.
> Is there some reason for this? I thought when full backups were
> successful, they should be truncating the log file.
> I can backup log with truncate_only and then issue a dbcc shrinkfile to
> get it back down, but I'd like to know why full backups aren't working
> first.
> Thanks.
>
|||Hello,
1. First take a look into the recovery model you use for the database.
2. If it is FULL or BULK_LOGGED then you have schedule a transction log
backup in regular intervals [Say every 15 minutes or so]. This frequency can
be changed based on ur data growth,.
Take a look into the BACKUP LOG command to backup the transaction log or you
could use the Maintenence plan wizard to automate the transaction log
backup.
This will make sure that you LDF will not grow drastically.
To reduce the size of 10 GB currently; you may to need to truncate the Log
first and use DBCC SHRINKFILE to reduce the file size. After that to bring
back the backup chain do:-
1. A full database backup
2. Followed with transaction log backups.
THANKS
Hari
"Kevin A" <kevina@.cqlcorp.com> wrote in message
news:ez%237O%23%23MHHA.4916@.TK2MSFTNGP06.phx.gbl.. .
> Sorry, I just need a bit more clarification.
> Are you saying, after a full backup, I need to issue the truncate command?
> That doesn't always seem to shrink the Logfile. I have to manually
> issue the dbcc srhinkfile.
> What would be the suggested steps to take, after the full backup of the
> DB?
> Thanks.
>
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:45A3927B.3030202@.realsqlguy.com...
>
|||Kevin A wrote:
> Sorry, I just need a bit more clarification.
> Are you saying, after a full backup, I need to issue the truncate command?
> That doesn't always seem to shrink the Logfile. I have to manually issue
> the dbcc srhinkfile.
> What would be the suggested steps to take, after the full backup of the DB?
> Thanks.
TRUNCATING is not SHRINKING, they are two different operations.
The log file is a "journal", recording every change that takes place in
your database. If the database is in Simple recovery mode, these
journal entries are automatically removed from the log once they've
completed. In Full or Bulk-Logged mode, they remain in the log until
you perform a log backup. This gives you the ability to restore the
database to any point in time, by "playing back" those journal entries
that you've backed up, an ability that you lose with Simple mode.
For example, suppose your database is in Simple mode, and you are doing
full backups nightly at 9:00pm. You suffer a hard drive crash at
8:00pm, corrupting your database. Your only option for recovery is to
restore the backup from 9:00pm the previous night, losing 23 hours of data.
Now, suppose that same database is in Full recovery mode. You're doing
nightly full backups at 9:00pm, and log backups every 5 minutes. Your
drive fails at 8:00pm, corrupting the database. You now have the
ability to restore the previous night's full backup, followed by the log
backups that have been done since then. At most, you lost 5 minutes of
data.
The space inside the transaction log is "recycled", as committed
transactions are flushed out (via a log backup), the space that they
were using is made available for new transactions. This helps to keep
the log file size under control. If you aren't flushing them out (i.e.
TRUNCATING), the log has to grow larger to hold new transactions.
SHRINKING is a different process - it will remove any free space from
the log, physically reducing the size of the log file. You should only
do this under extreme circumstances, it should NOT be part of your daily
routine. Repeated shrinking/growing of a file (log or database) will
lead to fragmentation, which will ultimately hurt performance. If you
have the disk space, you should allow the log to grow as needed, making
sure you're doing proper backups to keep the committed transactions
flushed out.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Thank you, you put the very well... Regards, John
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45A39711.7070204@.realsqlguy.com...
> Kevin A wrote:
> TRUNCATING is not SHRINKING, they are two different operations.
> The log file is a "journal", recording every change that takes place in
> your database. If the database is in Simple recovery mode, these journal
> entries are automatically removed from the log once they've completed. In
> Full or Bulk-Logged mode, they remain in the log until you perform a log
> backup. This gives you the ability to restore the database to any point
> in time, by "playing back" those journal entries that you've backed up, an
> ability that you lose with Simple mode.
> For example, suppose your database is in Simple mode, and you are doing
> full backups nightly at 9:00pm. You suffer a hard drive crash at 8:00pm,
> corrupting your database. Your only option for recovery is to restore the
> backup from 9:00pm the previous night, losing 23 hours of data.
> Now, suppose that same database is in Full recovery mode. You're doing
> nightly full backups at 9:00pm, and log backups every 5 minutes. Your
> drive fails at 8:00pm, corrupting the database. You now have the ability
> to restore the previous night's full backup, followed by the log backups
> that have been done since then. At most, you lost 5 minutes of data.
> The space inside the transaction log is "recycled", as committed
> transactions are flushed out (via a log backup), the space that they were
> using is made available for new transactions. This helps to keep the log
> file size under control. If you aren't flushing them out (i.e.
> TRUNCATING), the log has to grow larger to hold new transactions.
> SHRINKING is a different process - it will remove any free space from the
> log, physically reducing the size of the log file. You should only do
> this under extreme circumstances, it should NOT be part of your daily
> routine. Repeated shrinking/growing of a file (log or database) will lead
> to fragmentation, which will ultimately hurt performance. If you have the
> disk space, you should allow the log to grow as needed, making sure you're
> doing proper backups to keep the committed transactions flushed out.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||once i have a full backup of the database why would i need the log files
anymore? backupexec 9 indicates they will be removed during a full backup as
i would expect, but it isn't happening on one of my servers. thx if you have
more clues.
"Tracy McKibben" wrote:
> Kevin A wrote:
> Nope. See the topic "Truncating the Transaction Log" in Books Online.
> If you're running in Full or Bulk-Logged recovery mode, you MUST do
> transaction log backups, or the log will continue to accumulate
> transactional data. Even in Simple mode, a single large transaction can
> cause the log to grow.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>
|||help wrote:
> once i have a full backup of the database why would i need the log files
> anymore? backupexec 9 indicates they will be removed during a full backup as
> i would expect, but it isn't happening on one of my servers. thx if you have
> more clues.
>
Technically, you don't need log files that predate a full backup,
however, it's good insurance to keep as many backups as you can afford
to keep. As the DBA, you're responsible for protecting the company's
data, so keep as many backups as you can.
As for why they're not being deleted by BackupExec, I have no idea. I
don't use third party backup tools like that.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
Full backup and Transaction Logs
Recently, with a couple of DB's on SQL 2000 Enterprise SP3, I noticed that
doing a Full backup isn't truncating or shrinking the log file. The log
file got to be over 10GB and forced the Data drive to fill up.
Is there some reason for this? I thought when full backups were successful,
they should be truncating the log file.
I can backup log with truncate_only and then issue a dbcc shrinkfile to get
it back down, but I'd like to know why full backups aren't working first.
Thanks.Kevin A wrote:
> Recently, with a couple of DB's on SQL 2000 Enterprise SP3, I noticed that
> doing a Full backup isn't truncating or shrinking the log file. The log
> file got to be over 10GB and forced the Data drive to fill up.
> Is there some reason for this? I thought when full backups were successful,
> they should be truncating the log file.
> I can backup log with truncate_only and then issue a dbcc shrinkfile to get
> it back down, but I'd like to know why full backups aren't working first.
> Thanks.
>
Nope. See the topic "Truncating the Transaction Log" in Books Online.
If you're running in Full or Bulk-Logged recovery mode, you MUST do
transaction log backups, or the log will continue to accumulate
transactional data. Even in Simple mode, a single large transaction can
cause the log to grow.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Sorry, I just need a bit more clarification.
Are you saying, after a full backup, I need to issue the truncate command?
That doesn't always seem to shrink the Logfile. I have to manually issue
the dbcc srhinkfile.
What would be the suggested steps to take, after the full backup of the DB?
Thanks.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45A3927B.3030202@.realsqlguy.com...
> Kevin A wrote:
>> Recently, with a couple of DB's on SQL 2000 Enterprise SP3, I noticed
>> that doing a Full backup isn't truncating or shrinking the log file. The
>> log file got to be over 10GB and forced the Data drive to fill up.
>> Is there some reason for this? I thought when full backups were
>> successful, they should be truncating the log file.
>> I can backup log with truncate_only and then issue a dbcc shrinkfile to
>> get it back down, but I'd like to know why full backups aren't working
>> first.
>> Thanks.
> Nope. See the topic "Truncating the Transaction Log" in Books Online. If
> you're running in Full or Bulk-Logged recovery mode, you MUST do
> transaction log backups, or the log will continue to accumulate
> transactional data. Even in Simple mode, a single large transaction can
> cause the log to grow.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Hello,
FULL database backup will not clear the contents in tranasction log file.
The purpose of FULL recovery model is to perform the POIN IN TIME recovery.
In order to do this
you need to take the tranasction log backup. So when ever you perform a
transaction log backup automatically transaction log will be cleared from
the LDF file and will
leep the LDF file growth under control. Incase if you need to do a recovery
on crash/point in time you can use these log backup files. Just read the
Transaction log backup topic in books online.
Thanks
Hari
"Kevin A" <kevina@.cqlcorp.com> wrote in message
news:eh2Ecu%23MHHA.4916@.TK2MSFTNGP06.phx.gbl...
> Recently, with a couple of DB's on SQL 2000 Enterprise SP3, I noticed that
> doing a Full backup isn't truncating or shrinking the log file. The log
> file got to be over 10GB and forced the Data drive to fill up.
> Is there some reason for this? I thought when full backups were
> successful, they should be truncating the log file.
> I can backup log with truncate_only and then issue a dbcc shrinkfile to
> get it back down, but I'd like to know why full backups aren't working
> first.
> Thanks.
>|||Hello,
1. First take a look into the recovery model you use for the database.
2. If it is FULL or BULK_LOGGED then you have schedule a transction log
backup in regular intervals [Say every 15 minutes or so]. This frequency can
be changed based on ur data growth,.
Take a look into the BACKUP LOG command to backup the transaction log or you
could use the Maintenence plan wizard to automate the transaction log
backup.
This will make sure that you LDF will not grow drastically.
To reduce the size of 10 GB currently; you may to need to truncate the Log
first and use DBCC SHRINKFILE to reduce the file size. After that to bring
back the backup chain do:-
1. A full database backup
2. Followed with transaction log backups.
THANKS
Hari
"Kevin A" <kevina@.cqlcorp.com> wrote in message
news:ez%237O%23%23MHHA.4916@.TK2MSFTNGP06.phx.gbl...
> Sorry, I just need a bit more clarification.
> Are you saying, after a full backup, I need to issue the truncate command?
> That doesn't always seem to shrink the Logfile. I have to manually
> issue the dbcc srhinkfile.
> What would be the suggested steps to take, after the full backup of the
> DB?
> Thanks.
>
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:45A3927B.3030202@.realsqlguy.com...
>> Kevin A wrote:
>> Recently, with a couple of DB's on SQL 2000 Enterprise SP3, I noticed
>> that doing a Full backup isn't truncating or shrinking the log file.
>> The log file got to be over 10GB and forced the Data drive to fill up.
>> Is there some reason for this? I thought when full backups were
>> successful, they should be truncating the log file.
>> I can backup log with truncate_only and then issue a dbcc shrinkfile to
>> get it back down, but I'd like to know why full backups aren't working
>> first.
>> Thanks.
>> Nope. See the topic "Truncating the Transaction Log" in Books Online. If
>> you're running in Full or Bulk-Logged recovery mode, you MUST do
>> transaction log backups, or the log will continue to accumulate
>> transactional data. Even in Simple mode, a single large transaction can
>> cause the log to grow.
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com
>|||Kevin A wrote:
> Sorry, I just need a bit more clarification.
> Are you saying, after a full backup, I need to issue the truncate command?
> That doesn't always seem to shrink the Logfile. I have to manually issue
> the dbcc srhinkfile.
> What would be the suggested steps to take, after the full backup of the DB?
> Thanks.
TRUNCATING is not SHRINKING, they are two different operations.
The log file is a "journal", recording every change that takes place in
your database. If the database is in Simple recovery mode, these
journal entries are automatically removed from the log once they've
completed. In Full or Bulk-Logged mode, they remain in the log until
you perform a log backup. This gives you the ability to restore the
database to any point in time, by "playing back" those journal entries
that you've backed up, an ability that you lose with Simple mode.
For example, suppose your database is in Simple mode, and you are doing
full backups nightly at 9:00pm. You suffer a hard drive crash at
8:00pm, corrupting your database. Your only option for recovery is to
restore the backup from 9:00pm the previous night, losing 23 hours of data.
Now, suppose that same database is in Full recovery mode. You're doing
nightly full backups at 9:00pm, and log backups every 5 minutes. Your
drive fails at 8:00pm, corrupting the database. You now have the
ability to restore the previous night's full backup, followed by the log
backups that have been done since then. At most, you lost 5 minutes of
data.
The space inside the transaction log is "recycled", as committed
transactions are flushed out (via a log backup), the space that they
were using is made available for new transactions. This helps to keep
the log file size under control. If you aren't flushing them out (i.e.
TRUNCATING), the log has to grow larger to hold new transactions.
SHRINKING is a different process - it will remove any free space from
the log, physically reducing the size of the log file. You should only
do this under extreme circumstances, it should NOT be part of your daily
routine. Repeated shrinking/growing of a file (log or database) will
lead to fragmentation, which will ultimately hurt performance. If you
have the disk space, you should allow the log to grow as needed, making
sure you're doing proper backups to keep the committed transactions
flushed out.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thank you, you put the very well... Regards, John
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45A39711.7070204@.realsqlguy.com...
> Kevin A wrote:
>> Sorry, I just need a bit more clarification.
>> Are you saying, after a full backup, I need to issue the truncate
>> command? That doesn't always seem to shrink the Logfile. I have to
>> manually issue the dbcc srhinkfile.
>> What would be the suggested steps to take, after the full backup of the
>> DB?
>> Thanks.
> TRUNCATING is not SHRINKING, they are two different operations.
> The log file is a "journal", recording every change that takes place in
> your database. If the database is in Simple recovery mode, these journal
> entries are automatically removed from the log once they've completed. In
> Full or Bulk-Logged mode, they remain in the log until you perform a log
> backup. This gives you the ability to restore the database to any point
> in time, by "playing back" those journal entries that you've backed up, an
> ability that you lose with Simple mode.
> For example, suppose your database is in Simple mode, and you are doing
> full backups nightly at 9:00pm. You suffer a hard drive crash at 8:00pm,
> corrupting your database. Your only option for recovery is to restore the
> backup from 9:00pm the previous night, losing 23 hours of data.
> Now, suppose that same database is in Full recovery mode. You're doing
> nightly full backups at 9:00pm, and log backups every 5 minutes. Your
> drive fails at 8:00pm, corrupting the database. You now have the ability
> to restore the previous night's full backup, followed by the log backups
> that have been done since then. At most, you lost 5 minutes of data.
> The space inside the transaction log is "recycled", as committed
> transactions are flushed out (via a log backup), the space that they were
> using is made available for new transactions. This helps to keep the log
> file size under control. If you aren't flushing them out (i.e.
> TRUNCATING), the log has to grow larger to hold new transactions.
> SHRINKING is a different process - it will remove any free space from the
> log, physically reducing the size of the log file. You should only do
> this under extreme circumstances, it should NOT be part of your daily
> routine. Repeated shrinking/growing of a file (log or database) will lead
> to fragmentation, which will ultimately hurt performance. If you have the
> disk space, you should allow the log to grow as needed, making sure you're
> doing proper backups to keep the committed transactions flushed out.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||help wrote:
> once i have a full backup of the database why would i need the log files
> anymore? backupexec 9 indicates they will be removed during a full backup as
> i would expect, but it isn't happening on one of my servers. thx if you have
> more clues.
>
Technically, you don't need log files that predate a full backup,
however, it's good insurance to keep as many backups as you can afford
to keep. As the DBA, you're responsible for protecting the company's
data, so keep as many backups as you can.
As for why they're not being deleted by BackupExec, I have no idea. I
don't use third party backup tools like that.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
doing a Full backup isn't truncating or shrinking the log file. The log
file got to be over 10GB and forced the Data drive to fill up.
Is there some reason for this? I thought when full backups were successful,
they should be truncating the log file.
I can backup log with truncate_only and then issue a dbcc shrinkfile to get
it back down, but I'd like to know why full backups aren't working first.
Thanks.Kevin A wrote:
> Recently, with a couple of DB's on SQL 2000 Enterprise SP3, I noticed that
> doing a Full backup isn't truncating or shrinking the log file. The log
> file got to be over 10GB and forced the Data drive to fill up.
> Is there some reason for this? I thought when full backups were successful,
> they should be truncating the log file.
> I can backup log with truncate_only and then issue a dbcc shrinkfile to get
> it back down, but I'd like to know why full backups aren't working first.
> Thanks.
>
Nope. See the topic "Truncating the Transaction Log" in Books Online.
If you're running in Full or Bulk-Logged recovery mode, you MUST do
transaction log backups, or the log will continue to accumulate
transactional data. Even in Simple mode, a single large transaction can
cause the log to grow.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Sorry, I just need a bit more clarification.
Are you saying, after a full backup, I need to issue the truncate command?
That doesn't always seem to shrink the Logfile. I have to manually issue
the dbcc srhinkfile.
What would be the suggested steps to take, after the full backup of the DB?
Thanks.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45A3927B.3030202@.realsqlguy.com...
> Kevin A wrote:
>> Recently, with a couple of DB's on SQL 2000 Enterprise SP3, I noticed
>> that doing a Full backup isn't truncating or shrinking the log file. The
>> log file got to be over 10GB and forced the Data drive to fill up.
>> Is there some reason for this? I thought when full backups were
>> successful, they should be truncating the log file.
>> I can backup log with truncate_only and then issue a dbcc shrinkfile to
>> get it back down, but I'd like to know why full backups aren't working
>> first.
>> Thanks.
> Nope. See the topic "Truncating the Transaction Log" in Books Online. If
> you're running in Full or Bulk-Logged recovery mode, you MUST do
> transaction log backups, or the log will continue to accumulate
> transactional data. Even in Simple mode, a single large transaction can
> cause the log to grow.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Hello,
FULL database backup will not clear the contents in tranasction log file.
The purpose of FULL recovery model is to perform the POIN IN TIME recovery.
In order to do this
you need to take the tranasction log backup. So when ever you perform a
transaction log backup automatically transaction log will be cleared from
the LDF file and will
leep the LDF file growth under control. Incase if you need to do a recovery
on crash/point in time you can use these log backup files. Just read the
Transaction log backup topic in books online.
Thanks
Hari
"Kevin A" <kevina@.cqlcorp.com> wrote in message
news:eh2Ecu%23MHHA.4916@.TK2MSFTNGP06.phx.gbl...
> Recently, with a couple of DB's on SQL 2000 Enterprise SP3, I noticed that
> doing a Full backup isn't truncating or shrinking the log file. The log
> file got to be over 10GB and forced the Data drive to fill up.
> Is there some reason for this? I thought when full backups were
> successful, they should be truncating the log file.
> I can backup log with truncate_only and then issue a dbcc shrinkfile to
> get it back down, but I'd like to know why full backups aren't working
> first.
> Thanks.
>|||Hello,
1. First take a look into the recovery model you use for the database.
2. If it is FULL or BULK_LOGGED then you have schedule a transction log
backup in regular intervals [Say every 15 minutes or so]. This frequency can
be changed based on ur data growth,.
Take a look into the BACKUP LOG command to backup the transaction log or you
could use the Maintenence plan wizard to automate the transaction log
backup.
This will make sure that you LDF will not grow drastically.
To reduce the size of 10 GB currently; you may to need to truncate the Log
first and use DBCC SHRINKFILE to reduce the file size. After that to bring
back the backup chain do:-
1. A full database backup
2. Followed with transaction log backups.
THANKS
Hari
"Kevin A" <kevina@.cqlcorp.com> wrote in message
news:ez%237O%23%23MHHA.4916@.TK2MSFTNGP06.phx.gbl...
> Sorry, I just need a bit more clarification.
> Are you saying, after a full backup, I need to issue the truncate command?
> That doesn't always seem to shrink the Logfile. I have to manually
> issue the dbcc srhinkfile.
> What would be the suggested steps to take, after the full backup of the
> DB?
> Thanks.
>
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:45A3927B.3030202@.realsqlguy.com...
>> Kevin A wrote:
>> Recently, with a couple of DB's on SQL 2000 Enterprise SP3, I noticed
>> that doing a Full backup isn't truncating or shrinking the log file.
>> The log file got to be over 10GB and forced the Data drive to fill up.
>> Is there some reason for this? I thought when full backups were
>> successful, they should be truncating the log file.
>> I can backup log with truncate_only and then issue a dbcc shrinkfile to
>> get it back down, but I'd like to know why full backups aren't working
>> first.
>> Thanks.
>> Nope. See the topic "Truncating the Transaction Log" in Books Online. If
>> you're running in Full or Bulk-Logged recovery mode, you MUST do
>> transaction log backups, or the log will continue to accumulate
>> transactional data. Even in Simple mode, a single large transaction can
>> cause the log to grow.
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com
>|||Kevin A wrote:
> Sorry, I just need a bit more clarification.
> Are you saying, after a full backup, I need to issue the truncate command?
> That doesn't always seem to shrink the Logfile. I have to manually issue
> the dbcc srhinkfile.
> What would be the suggested steps to take, after the full backup of the DB?
> Thanks.
TRUNCATING is not SHRINKING, they are two different operations.
The log file is a "journal", recording every change that takes place in
your database. If the database is in Simple recovery mode, these
journal entries are automatically removed from the log once they've
completed. In Full or Bulk-Logged mode, they remain in the log until
you perform a log backup. This gives you the ability to restore the
database to any point in time, by "playing back" those journal entries
that you've backed up, an ability that you lose with Simple mode.
For example, suppose your database is in Simple mode, and you are doing
full backups nightly at 9:00pm. You suffer a hard drive crash at
8:00pm, corrupting your database. Your only option for recovery is to
restore the backup from 9:00pm the previous night, losing 23 hours of data.
Now, suppose that same database is in Full recovery mode. You're doing
nightly full backups at 9:00pm, and log backups every 5 minutes. Your
drive fails at 8:00pm, corrupting the database. You now have the
ability to restore the previous night's full backup, followed by the log
backups that have been done since then. At most, you lost 5 minutes of
data.
The space inside the transaction log is "recycled", as committed
transactions are flushed out (via a log backup), the space that they
were using is made available for new transactions. This helps to keep
the log file size under control. If you aren't flushing them out (i.e.
TRUNCATING), the log has to grow larger to hold new transactions.
SHRINKING is a different process - it will remove any free space from
the log, physically reducing the size of the log file. You should only
do this under extreme circumstances, it should NOT be part of your daily
routine. Repeated shrinking/growing of a file (log or database) will
lead to fragmentation, which will ultimately hurt performance. If you
have the disk space, you should allow the log to grow as needed, making
sure you're doing proper backups to keep the committed transactions
flushed out.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thank you, you put the very well... Regards, John
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45A39711.7070204@.realsqlguy.com...
> Kevin A wrote:
>> Sorry, I just need a bit more clarification.
>> Are you saying, after a full backup, I need to issue the truncate
>> command? That doesn't always seem to shrink the Logfile. I have to
>> manually issue the dbcc srhinkfile.
>> What would be the suggested steps to take, after the full backup of the
>> DB?
>> Thanks.
> TRUNCATING is not SHRINKING, they are two different operations.
> The log file is a "journal", recording every change that takes place in
> your database. If the database is in Simple recovery mode, these journal
> entries are automatically removed from the log once they've completed. In
> Full or Bulk-Logged mode, they remain in the log until you perform a log
> backup. This gives you the ability to restore the database to any point
> in time, by "playing back" those journal entries that you've backed up, an
> ability that you lose with Simple mode.
> For example, suppose your database is in Simple mode, and you are doing
> full backups nightly at 9:00pm. You suffer a hard drive crash at 8:00pm,
> corrupting your database. Your only option for recovery is to restore the
> backup from 9:00pm the previous night, losing 23 hours of data.
> Now, suppose that same database is in Full recovery mode. You're doing
> nightly full backups at 9:00pm, and log backups every 5 minutes. Your
> drive fails at 8:00pm, corrupting the database. You now have the ability
> to restore the previous night's full backup, followed by the log backups
> that have been done since then. At most, you lost 5 minutes of data.
> The space inside the transaction log is "recycled", as committed
> transactions are flushed out (via a log backup), the space that they were
> using is made available for new transactions. This helps to keep the log
> file size under control. If you aren't flushing them out (i.e.
> TRUNCATING), the log has to grow larger to hold new transactions.
> SHRINKING is a different process - it will remove any free space from the
> log, physically reducing the size of the log file. You should only do
> this under extreme circumstances, it should NOT be part of your daily
> routine. Repeated shrinking/growing of a file (log or database) will lead
> to fragmentation, which will ultimately hurt performance. If you have the
> disk space, you should allow the log to grow as needed, making sure you're
> doing proper backups to keep the committed transactions flushed out.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||help wrote:
> once i have a full backup of the database why would i need the log files
> anymore? backupexec 9 indicates they will be removed during a full backup as
> i would expect, but it isn't happening on one of my servers. thx if you have
> more clues.
>
Technically, you don't need log files that predate a full backup,
however, it's good insurance to keep as many backups as you can afford
to keep. As the DBA, you're responsible for protecting the company's
data, so keep as many backups as you can.
As for why they're not being deleted by BackupExec, I have no idea. I
don't use third party backup tools like that.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
Full backup and Transaction Logs
Recently, with a couple of DB's on SQL 2000 Enterprise SP3, I noticed that
doing a Full backup isn't truncating or shrinking the log file. The log
file got to be over 10GB and forced the Data drive to fill up.
Is there some reason for this? I thought when full backups were successful,
they should be truncating the log file.
I can backup log with truncate_only and then issue a dbcc shrinkfile to get
it back down, but I'd like to know why full backups aren't working first.
Thanks.Kevin A wrote:
> Recently, with a couple of DB's on SQL 2000 Enterprise SP3, I noticed that
> doing a Full backup isn't truncating or shrinking the log file. The log
> file got to be over 10GB and forced the Data drive to fill up.
> Is there some reason for this? I thought when full backups were successfu
l,
> they should be truncating the log file.
> I can backup log with truncate_only and then issue a dbcc shrinkfile to ge
t
> it back down, but I'd like to know why full backups aren't working first.
> Thanks.
>
Nope. See the topic "Truncating the Transaction Log" in Books Online.
If you're running in Full or Bulk-Logged recovery mode, you MUST do
transaction log backups, or the log will continue to accumulate
transactional data. Even in Simple mode, a single large transaction can
cause the log to grow.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Sorry, I just need a bit more clarification.
Are you saying, after a full backup, I need to issue the truncate command?
That doesn't always seem to shrink the Logfile. I have to manually issue
the dbcc srhinkfile.
What would be the suggested steps to take, after the full backup of the DB?
Thanks.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45A3927B.3030202@.realsqlguy.com...
> Kevin A wrote:
> Nope. See the topic "Truncating the Transaction Log" in Books Online. If
> you're running in Full or Bulk-Logged recovery mode, you MUST do
> transaction log backups, or the log will continue to accumulate
> transactional data. Even in Simple mode, a single large transaction can
> cause the log to grow.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Hello,
FULL database backup will not clear the contents in tranasction log file.
The purpose of FULL recovery model is to perform the POIN IN TIME recovery.
In order to do this
you need to take the tranasction log backup. So when ever you perform a
transaction log backup automatically transaction log will be cleared from
the LDF file and will
leep the LDF file growth under control. Incase if you need to do a recovery
on crash/point in time you can use these log backup files. Just read the
Transaction log backup topic in books online.
Thanks
Hari
"Kevin A" <kevina@.cqlcorp.com> wrote in message
news:eh2Ecu%23MHHA.4916@.TK2MSFTNGP06.phx.gbl...
> Recently, with a couple of DB's on SQL 2000 Enterprise SP3, I noticed that
> doing a Full backup isn't truncating or shrinking the log file. The log
> file got to be over 10GB and forced the Data drive to fill up.
> Is there some reason for this? I thought when full backups were
> successful, they should be truncating the log file.
> I can backup log with truncate_only and then issue a dbcc shrinkfile to
> get it back down, but I'd like to know why full backups aren't working
> first.
> Thanks.
>|||Hello,
1. First take a look into the recovery model you use for the database.
2. If it is FULL or BULK_LOGGED then you have schedule a transction log
backup in regular intervals [Say every 15 minutes or so]. This frequency
can
be changed based on ur data growth,.
Take a look into the BACKUP LOG command to backup the transaction log or you
could use the Maintenence plan wizard to automate the transaction log
backup.
This will make sure that you LDF will not grow drastically.
To reduce the size of 10 GB currently; you may to need to truncate the Log
first and use DBCC SHRINKFILE to reduce the file size. After that to bring
back the backup chain do:-
1. A full database backup
2. Followed with transaction log backups.
THANKS
Hari
"Kevin A" <kevina@.cqlcorp.com> wrote in message
news:ez%237O%23%23MHHA.4916@.TK2MSFTNGP06.phx.gbl...
> Sorry, I just need a bit more clarification.
> Are you saying, after a full backup, I need to issue the truncate command?
> That doesn't always seem to shrink the Logfile. I have to manually
> issue the dbcc srhinkfile.
> What would be the suggested steps to take, after the full backup of the
> DB?
> Thanks.
>
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:45A3927B.3030202@.realsqlguy.com...
>|||Kevin A wrote:
> Sorry, I just need a bit more clarification.
> Are you saying, after a full backup, I need to issue the truncate command?
> That doesn't always seem to shrink the Logfile. I have to manually issu
e
> the dbcc srhinkfile.
> What would be the suggested steps to take, after the full backup of the DB
?
> Thanks.
TRUNCATING is not SHRINKING, they are two different operations.
The log file is a "journal", recording every change that takes place in
your database. If the database is in Simple recovery mode, these
journal entries are automatically removed from the log once they've
completed. In Full or Bulk-Logged mode, they remain in the log until
you perform a log backup. This gives you the ability to restore the
database to any point in time, by "playing back" those journal entries
that you've backed up, an ability that you lose with Simple mode.
For example, suppose your database is in Simple mode, and you are doing
full backups nightly at 9:00pm. You suffer a hard drive crash at
8:00pm, corrupting your database. Your only option for recovery is to
restore the backup from 9:00pm the previous night, losing 23 hours of data.
Now, suppose that same database is in Full recovery mode. You're doing
nightly full backups at 9:00pm, and log backups every 5 minutes. Your
drive fails at 8:00pm, corrupting the database. You now have the
ability to restore the previous night's full backup, followed by the log
backups that have been done since then. At most, you lost 5 minutes of
data.
The space inside the transaction log is "recycled", as committed
transactions are flushed out (via a log backup), the space that they
were using is made available for new transactions. This helps to keep
the log file size under control. If you aren't flushing them out (i.e.
TRUNCATING), the log has to grow larger to hold new transactions.
SHRINKING is a different process - it will remove any free space from
the log, physically reducing the size of the log file. You should only
do this under extreme circumstances, it should NOT be part of your daily
routine. Repeated shrinking/growing of a file (log or database) will
lead to fragmentation, which will ultimately hurt performance. If you
have the disk space, you should allow the log to grow as needed, making
sure you're doing proper backups to keep the committed transactions
flushed out.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thank you, you put the very well... Regards, John
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45A39711.7070204@.realsqlguy.com...
> Kevin A wrote:
> TRUNCATING is not SHRINKING, they are two different operations.
> The log file is a "journal", recording every change that takes place in
> your database. If the database is in Simple recovery mode, these journal
> entries are automatically removed from the log once they've completed. In
> Full or Bulk-Logged mode, they remain in the log until you perform a log
> backup. This gives you the ability to restore the database to any point
> in time, by "playing back" those journal entries that you've backed up, an
> ability that you lose with Simple mode.
> For example, suppose your database is in Simple mode, and you are doing
> full backups nightly at 9:00pm. You suffer a hard drive crash at 8:00pm,
> corrupting your database. Your only option for recovery is to restore the
> backup from 9:00pm the previous night, losing 23 hours of data.
> Now, suppose that same database is in Full recovery mode. You're doing
> nightly full backups at 9:00pm, and log backups every 5 minutes. Your
> drive fails at 8:00pm, corrupting the database. You now have the ability
> to restore the previous night's full backup, followed by the log backups
> that have been done since then. At most, you lost 5 minutes of data.
> The space inside the transaction log is "recycled", as committed
> transactions are flushed out (via a log backup), the space that they were
> using is made available for new transactions. This helps to keep the log
> file size under control. If you aren't flushing them out (i.e.
> TRUNCATING), the log has to grow larger to hold new transactions.
> SHRINKING is a different process - it will remove any free space from the
> log, physically reducing the size of the log file. You should only do
> this under extreme circumstances, it should NOT be part of your daily
> routine. Repeated shrinking/growing of a file (log or database) will lead
> to fragmentation, which will ultimately hurt performance. If you have the
> disk space, you should allow the log to grow as needed, making sure you're
> doing proper backups to keep the committed transactions flushed out.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||once i have a full backup of the database why would i need the log files
anymore? backupexec 9 indicates they will be removed during a full backup a
s
i would expect, but it isn't happening on one of my servers. thx if you hav
e
more clues.
"Tracy McKibben" wrote:
> Kevin A wrote:
> Nope. See the topic "Truncating the Transaction Log" in Books Online.
> If you're running in Full or Bulk-Logged recovery mode, you MUST do
> transaction log backups, or the log will continue to accumulate
> transactional data. Even in Simple mode, a single large transaction can
> cause the log to grow.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||help wrote:
> once i have a full backup of the database why would i need the log files
> anymore? backupexec 9 indicates they will be removed during a full backup
as
> i would expect, but it isn't happening on one of my servers. thx if you h
ave
> more clues.
>
Technically, you don't need log files that predate a full backup,
however, it's good insurance to keep as many backups as you can afford
to keep. As the DBA, you're responsible for protecting the company's
data, so keep as many backups as you can.
As for why they're not being deleted by BackupExec, I have no idea. I
don't use third party backup tools like that.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
doing a Full backup isn't truncating or shrinking the log file. The log
file got to be over 10GB and forced the Data drive to fill up.
Is there some reason for this? I thought when full backups were successful,
they should be truncating the log file.
I can backup log with truncate_only and then issue a dbcc shrinkfile to get
it back down, but I'd like to know why full backups aren't working first.
Thanks.Kevin A wrote:
> Recently, with a couple of DB's on SQL 2000 Enterprise SP3, I noticed that
> doing a Full backup isn't truncating or shrinking the log file. The log
> file got to be over 10GB and forced the Data drive to fill up.
> Is there some reason for this? I thought when full backups were successfu
l,
> they should be truncating the log file.
> I can backup log with truncate_only and then issue a dbcc shrinkfile to ge
t
> it back down, but I'd like to know why full backups aren't working first.
> Thanks.
>
Nope. See the topic "Truncating the Transaction Log" in Books Online.
If you're running in Full or Bulk-Logged recovery mode, you MUST do
transaction log backups, or the log will continue to accumulate
transactional data. Even in Simple mode, a single large transaction can
cause the log to grow.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Sorry, I just need a bit more clarification.
Are you saying, after a full backup, I need to issue the truncate command?
That doesn't always seem to shrink the Logfile. I have to manually issue
the dbcc srhinkfile.
What would be the suggested steps to take, after the full backup of the DB?
Thanks.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45A3927B.3030202@.realsqlguy.com...
> Kevin A wrote:
> Nope. See the topic "Truncating the Transaction Log" in Books Online. If
> you're running in Full or Bulk-Logged recovery mode, you MUST do
> transaction log backups, or the log will continue to accumulate
> transactional data. Even in Simple mode, a single large transaction can
> cause the log to grow.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Hello,
FULL database backup will not clear the contents in tranasction log file.
The purpose of FULL recovery model is to perform the POIN IN TIME recovery.
In order to do this
you need to take the tranasction log backup. So when ever you perform a
transaction log backup automatically transaction log will be cleared from
the LDF file and will
leep the LDF file growth under control. Incase if you need to do a recovery
on crash/point in time you can use these log backup files. Just read the
Transaction log backup topic in books online.
Thanks
Hari
"Kevin A" <kevina@.cqlcorp.com> wrote in message
news:eh2Ecu%23MHHA.4916@.TK2MSFTNGP06.phx.gbl...
> Recently, with a couple of DB's on SQL 2000 Enterprise SP3, I noticed that
> doing a Full backup isn't truncating or shrinking the log file. The log
> file got to be over 10GB and forced the Data drive to fill up.
> Is there some reason for this? I thought when full backups were
> successful, they should be truncating the log file.
> I can backup log with truncate_only and then issue a dbcc shrinkfile to
> get it back down, but I'd like to know why full backups aren't working
> first.
> Thanks.
>|||Hello,
1. First take a look into the recovery model you use for the database.
2. If it is FULL or BULK_LOGGED then you have schedule a transction log
backup in regular intervals [Say every 15 minutes or so]. This frequency
can
be changed based on ur data growth,.
Take a look into the BACKUP LOG command to backup the transaction log or you
could use the Maintenence plan wizard to automate the transaction log
backup.
This will make sure that you LDF will not grow drastically.
To reduce the size of 10 GB currently; you may to need to truncate the Log
first and use DBCC SHRINKFILE to reduce the file size. After that to bring
back the backup chain do:-
1. A full database backup
2. Followed with transaction log backups.
THANKS
Hari
"Kevin A" <kevina@.cqlcorp.com> wrote in message
news:ez%237O%23%23MHHA.4916@.TK2MSFTNGP06.phx.gbl...
> Sorry, I just need a bit more clarification.
> Are you saying, after a full backup, I need to issue the truncate command?
> That doesn't always seem to shrink the Logfile. I have to manually
> issue the dbcc srhinkfile.
> What would be the suggested steps to take, after the full backup of the
> DB?
> Thanks.
>
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:45A3927B.3030202@.realsqlguy.com...
>|||Kevin A wrote:
> Sorry, I just need a bit more clarification.
> Are you saying, after a full backup, I need to issue the truncate command?
> That doesn't always seem to shrink the Logfile. I have to manually issu
e
> the dbcc srhinkfile.
> What would be the suggested steps to take, after the full backup of the DB
?
> Thanks.
TRUNCATING is not SHRINKING, they are two different operations.
The log file is a "journal", recording every change that takes place in
your database. If the database is in Simple recovery mode, these
journal entries are automatically removed from the log once they've
completed. In Full or Bulk-Logged mode, they remain in the log until
you perform a log backup. This gives you the ability to restore the
database to any point in time, by "playing back" those journal entries
that you've backed up, an ability that you lose with Simple mode.
For example, suppose your database is in Simple mode, and you are doing
full backups nightly at 9:00pm. You suffer a hard drive crash at
8:00pm, corrupting your database. Your only option for recovery is to
restore the backup from 9:00pm the previous night, losing 23 hours of data.
Now, suppose that same database is in Full recovery mode. You're doing
nightly full backups at 9:00pm, and log backups every 5 minutes. Your
drive fails at 8:00pm, corrupting the database. You now have the
ability to restore the previous night's full backup, followed by the log
backups that have been done since then. At most, you lost 5 minutes of
data.
The space inside the transaction log is "recycled", as committed
transactions are flushed out (via a log backup), the space that they
were using is made available for new transactions. This helps to keep
the log file size under control. If you aren't flushing them out (i.e.
TRUNCATING), the log has to grow larger to hold new transactions.
SHRINKING is a different process - it will remove any free space from
the log, physically reducing the size of the log file. You should only
do this under extreme circumstances, it should NOT be part of your daily
routine. Repeated shrinking/growing of a file (log or database) will
lead to fragmentation, which will ultimately hurt performance. If you
have the disk space, you should allow the log to grow as needed, making
sure you're doing proper backups to keep the committed transactions
flushed out.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thank you, you put the very well... Regards, John
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45A39711.7070204@.realsqlguy.com...
> Kevin A wrote:
> TRUNCATING is not SHRINKING, they are two different operations.
> The log file is a "journal", recording every change that takes place in
> your database. If the database is in Simple recovery mode, these journal
> entries are automatically removed from the log once they've completed. In
> Full or Bulk-Logged mode, they remain in the log until you perform a log
> backup. This gives you the ability to restore the database to any point
> in time, by "playing back" those journal entries that you've backed up, an
> ability that you lose with Simple mode.
> For example, suppose your database is in Simple mode, and you are doing
> full backups nightly at 9:00pm. You suffer a hard drive crash at 8:00pm,
> corrupting your database. Your only option for recovery is to restore the
> backup from 9:00pm the previous night, losing 23 hours of data.
> Now, suppose that same database is in Full recovery mode. You're doing
> nightly full backups at 9:00pm, and log backups every 5 minutes. Your
> drive fails at 8:00pm, corrupting the database. You now have the ability
> to restore the previous night's full backup, followed by the log backups
> that have been done since then. At most, you lost 5 minutes of data.
> The space inside the transaction log is "recycled", as committed
> transactions are flushed out (via a log backup), the space that they were
> using is made available for new transactions. This helps to keep the log
> file size under control. If you aren't flushing them out (i.e.
> TRUNCATING), the log has to grow larger to hold new transactions.
> SHRINKING is a different process - it will remove any free space from the
> log, physically reducing the size of the log file. You should only do
> this under extreme circumstances, it should NOT be part of your daily
> routine. Repeated shrinking/growing of a file (log or database) will lead
> to fragmentation, which will ultimately hurt performance. If you have the
> disk space, you should allow the log to grow as needed, making sure you're
> doing proper backups to keep the committed transactions flushed out.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||once i have a full backup of the database why would i need the log files
anymore? backupexec 9 indicates they will be removed during a full backup a
s
i would expect, but it isn't happening on one of my servers. thx if you hav
e
more clues.
"Tracy McKibben" wrote:
> Kevin A wrote:
> Nope. See the topic "Truncating the Transaction Log" in Books Online.
> If you're running in Full or Bulk-Logged recovery mode, you MUST do
> transaction log backups, or the log will continue to accumulate
> transactional data. Even in Simple mode, a single large transaction can
> cause the log to grow.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||help wrote:
> once i have a full backup of the database why would i need the log files
> anymore? backupexec 9 indicates they will be removed during a full backup
as
> i would expect, but it isn't happening on one of my servers. thx if you h
ave
> more clues.
>
Technically, you don't need log files that predate a full backup,
however, it's good insurance to keep as many backups as you can afford
to keep. As the DBA, you're responsible for protecting the company's
data, so keep as many backups as you can.
As for why they're not being deleted by BackupExec, I have no idea. I
don't use third party backup tools like that.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
2012年2月19日星期日
Ful Text Search
People,
I'm working with Full-Text Indexing on SQL 2000 SP3. And like to use the Default Language for word Breaker = Portuguese.
Or something like this.
I have already tried on Technet and MSDN Web Site.
But didn't have success.
Suggestions??
Thanks a lot.See page 39
http://www.microsoft.com/israel/msdn/downloads/netweb/SISiteServer.ppt
I was searching for it some time before. And that is all I have found ;)|||I had already found this document too.
But using the Neutral word breaker I don't get Verbal functions in my language. (Portuguese Brazil).
Thanks a Lot!
I'm working with Full-Text Indexing on SQL 2000 SP3. And like to use the Default Language for word Breaker = Portuguese.
Or something like this.
I have already tried on Technet and MSDN Web Site.
But didn't have success.
Suggestions??
Thanks a lot.See page 39
http://www.microsoft.com/israel/msdn/downloads/netweb/SISiteServer.ppt
I was searching for it some time before. And that is all I have found ;)|||I had already found this document too.
But using the Neutral word breaker I don't get Verbal functions in my language. (Portuguese Brazil).
Thanks a Lot!
FTS Security Issue
Hi,
FTS will not populate the catalogs. 0 Items.
I have a new installation W2003 with SQL2K Sp3.
The SQL Server is running with a high security configuration.
All the SQL services are running under an account Domain\SQLService which is a plain old user in the domain and on the SQL Server box.
The local system account was chosen during installation. I changed to the domain user via the Enterprise Manager after the fact. SQL Server and SQL Agent run fine. BUILTIN\Administrators has been removed.
I saw a post that said changing the service account from one account and back to the original should fix the problem. It did not. I see "Login failed for NT AUTHORITY\SYSTEM" in the SQL Error Log when I try to build the catalog. I checked the account th
at is running MS Search in the Services dialog and it is still Local System. When I changed accounts on SQL Server, I changed it to myself a local admin and then back to the Domain\SQLService account. I tried changing the MS Search account via the Service
s dialog to Domain\SQLService account and now it will not start. Domain\SQLService has full control on the FTDATA directory. Any other directories or registery keys that perhaps Domain\SQLService needs access to, but is not being set?
Thanks,
Norman
Follow up...
Here are some messages from the event log:
12:14:21 PMSearch Service 7052The Search service has loaded project <SQLServer SQL0002000005>.
12:14:19 PMIndexer 7070The Search service has added project <SQL0002000005>.
12:14:19 PMIndexer 7071The Search service has removed project <SQL0002000005>.
12:14:19 PMIndexer 7042Project <SQLServer SQL0002000005> has been shut down as requested by user.
12:14:05 PMIndexer 7045The catalog was not propagated, because no new files were detected for the project <SQLServer SQL0002000005>.
12:14:05 PMGatherer 3018The end of crawl for project <SQLServer SQL0002000005> has been detected. The Gatherer successfully processed 0 documents totaling 0K. It failed to filter 1 documents. 0 URLs could not be reached or were denied access.
12:14:05 PMGatherer 3024The crawl for project <SQLServer SQL0002000005> could not be started, because no crawl seeds could be accessed. Fix the errors and try the crawl again.
12:14:05 PMGatherer 3036The crawl seed <MSSQL75://SQLServer/37fa4c37> in project <SQLServer SQL0002000005> cannot be accessed. Error: 800700e9 - No process is on the other end of the pipe. .
12:14:04 PMGatherer 3019The crawl on project <SQLServer SQL0002000005> has started.
|||Norman,
The MSSearch service requires that either the BUILTIN\Administrators login
be present or that [NT Authority\System] localsystem login have the below
rights and this is why you are seeing ("Login failed for NT
AUTHORITY\SYSTEM" ) after removing the BUILTIN\Administrators login:
exec sp_grantlogin N'NT Authority\System'
exec sp_defaultdb N'NT Authority\System', N'master'
exec sp_defaultlanguage N'NT Authority\System','us_english'
exec sp_addsrvrolemember N'NT Authority\System', sysadmin
See also KB article Q263712 "INF: How To Prevent Windows NT Administrators
From Administering a Clustered SQL Server"
at http://support.microsoft.com/default...;EN-US;q263712 for more
info.
Regards,
John
"Norman" <anonymous@.discussions.microsoft.com> wrote in message
news:C6CE845A-0975-4801-8B0B-5B02D848F336@.microsoft.com...
> Follow up...
> Here are some messages from the event log:
> 12:14:21 PM Search Service 7052 The Search service has loaded project
<SQLServer SQL0002000005>.
> 12:14:19 PM Indexer 7070 The Search service has added project
<SQL0002000005>.
> 12:14:19 PM Indexer 7071 The Search service has removed project
<SQL0002000005>.
> 12:14:19 PM Indexer 7042 Project <SQLServer SQL0002000005> has been shut
down as requested by user.
> 12:14:05 PM Indexer 7045 The catalog was not propagated, because no new
files were detected for the project <SQLServer SQL0002000005>.
> 12:14:05 PM Gatherer 3018 The end of crawl for project <SQLServer
SQL0002000005> has been detected. The Gatherer successfully processed 0
documents totaling 0K. It failed to filter 1 documents. 0 URLs could not be
reached or were denied access.
> 12:14:05 PM Gatherer 3024 The crawl for project <SQLServer SQL0002000005>
could not be started, because no crawl seeds could be accessed. Fix the
errors and try the crawl again.
> 12:14:05 PM Gatherer 3036 The crawl seed <MSSQL75://SQLServer/37fa4c37> in
project <SQLServer SQL0002000005> cannot be accessed. Error: 800700e9 - No
process is on the other end of the pipe. .
> 12:14:04 PM Gatherer 3019 The crawl on project <SQLServer SQL0002000005>
has started.
>
|||John,
No kudos to Microsoft for the high level security requiements for MS Search and not providing a KB with a work around for highly secure environments.
FYI:
Even though I found posts that indicate the contrary, the MS Search service remains Local System Account regardless of which account you put in the the SQL Service or SQL Agent at installation or after the fact via the Enterprise Manager. I tried it on se
veral SQL Server installations on W2K and W2003.
My compromise solution was to create a domain account specifically for the MS Search service and make it local admin and sa in the SQL Server. This seems to function correctly for all the test I have tried. The SQL Server service is then still locked down
as long as there are no holes to exploit via the FTS queries. (I won't hold my breath)
Norman
|||Norman,
Unfortunately, I'd have to agree with you. FYI, the "Microsoft Search"
(mssearch.exe) service should ALWAYS be started and running under the
"system" or LocalSystem account as that how it is currently designed in SQL
Server 2000. However, for SQL Server 2005 (Yukon) this has changed and the
new MSSearch service, will be able to run under the same service account as
the MSSQLServer service.
If I was in the SQL MVP program, I'd be glad to volunteer and write such a
KB article, but alas I'm not *yet* in that program...
Regards,
John
"Norman" <anonymous@.discussions.microsoft.com> wrote in message
news:F954B2E0-FCDA-4692-8F80-D10DB1F5F701@.microsoft.com...
> John,
> No kudos to Microsoft for the high level security requiements for MS
Search and not providing a KB with a work around for highly secure
environments.
> FYI:
> Even though I found posts that indicate the contrary, the MS Search
service remains Local System Account regardless of which account you put in
the the SQL Service or SQL Agent at installation or after the fact via the
Enterprise Manager. I tried it on several SQL Server installations on W2K
and W2003.
> My compromise solution was to create a domain account specifically for the
MS Search service and make it local admin and sa in the SQL Server. This
seems to function correctly for all the test I have tried. The SQL Server
service is then still locked down as long as there are no holes to exploit
via the FTS queries. (I won't hold my breath)
> Norman
FTS will not populate the catalogs. 0 Items.
I have a new installation W2003 with SQL2K Sp3.
The SQL Server is running with a high security configuration.
All the SQL services are running under an account Domain\SQLService which is a plain old user in the domain and on the SQL Server box.
The local system account was chosen during installation. I changed to the domain user via the Enterprise Manager after the fact. SQL Server and SQL Agent run fine. BUILTIN\Administrators has been removed.
I saw a post that said changing the service account from one account and back to the original should fix the problem. It did not. I see "Login failed for NT AUTHORITY\SYSTEM" in the SQL Error Log when I try to build the catalog. I checked the account th
at is running MS Search in the Services dialog and it is still Local System. When I changed accounts on SQL Server, I changed it to myself a local admin and then back to the Domain\SQLService account. I tried changing the MS Search account via the Service
s dialog to Domain\SQLService account and now it will not start. Domain\SQLService has full control on the FTDATA directory. Any other directories or registery keys that perhaps Domain\SQLService needs access to, but is not being set?
Thanks,
Norman
Follow up...
Here are some messages from the event log:
12:14:21 PMSearch Service 7052The Search service has loaded project <SQLServer SQL0002000005>.
12:14:19 PMIndexer 7070The Search service has added project <SQL0002000005>.
12:14:19 PMIndexer 7071The Search service has removed project <SQL0002000005>.
12:14:19 PMIndexer 7042Project <SQLServer SQL0002000005> has been shut down as requested by user.
12:14:05 PMIndexer 7045The catalog was not propagated, because no new files were detected for the project <SQLServer SQL0002000005>.
12:14:05 PMGatherer 3018The end of crawl for project <SQLServer SQL0002000005> has been detected. The Gatherer successfully processed 0 documents totaling 0K. It failed to filter 1 documents. 0 URLs could not be reached or were denied access.
12:14:05 PMGatherer 3024The crawl for project <SQLServer SQL0002000005> could not be started, because no crawl seeds could be accessed. Fix the errors and try the crawl again.
12:14:05 PMGatherer 3036The crawl seed <MSSQL75://SQLServer/37fa4c37> in project <SQLServer SQL0002000005> cannot be accessed. Error: 800700e9 - No process is on the other end of the pipe. .
12:14:04 PMGatherer 3019The crawl on project <SQLServer SQL0002000005> has started.
|||Norman,
The MSSearch service requires that either the BUILTIN\Administrators login
be present or that [NT Authority\System] localsystem login have the below
rights and this is why you are seeing ("Login failed for NT
AUTHORITY\SYSTEM" ) after removing the BUILTIN\Administrators login:
exec sp_grantlogin N'NT Authority\System'
exec sp_defaultdb N'NT Authority\System', N'master'
exec sp_defaultlanguage N'NT Authority\System','us_english'
exec sp_addsrvrolemember N'NT Authority\System', sysadmin
See also KB article Q263712 "INF: How To Prevent Windows NT Administrators
From Administering a Clustered SQL Server"
at http://support.microsoft.com/default...;EN-US;q263712 for more
info.
Regards,
John
"Norman" <anonymous@.discussions.microsoft.com> wrote in message
news:C6CE845A-0975-4801-8B0B-5B02D848F336@.microsoft.com...
> Follow up...
> Here are some messages from the event log:
> 12:14:21 PM Search Service 7052 The Search service has loaded project
<SQLServer SQL0002000005>.
> 12:14:19 PM Indexer 7070 The Search service has added project
<SQL0002000005>.
> 12:14:19 PM Indexer 7071 The Search service has removed project
<SQL0002000005>.
> 12:14:19 PM Indexer 7042 Project <SQLServer SQL0002000005> has been shut
down as requested by user.
> 12:14:05 PM Indexer 7045 The catalog was not propagated, because no new
files were detected for the project <SQLServer SQL0002000005>.
> 12:14:05 PM Gatherer 3018 The end of crawl for project <SQLServer
SQL0002000005> has been detected. The Gatherer successfully processed 0
documents totaling 0K. It failed to filter 1 documents. 0 URLs could not be
reached or were denied access.
> 12:14:05 PM Gatherer 3024 The crawl for project <SQLServer SQL0002000005>
could not be started, because no crawl seeds could be accessed. Fix the
errors and try the crawl again.
> 12:14:05 PM Gatherer 3036 The crawl seed <MSSQL75://SQLServer/37fa4c37> in
project <SQLServer SQL0002000005> cannot be accessed. Error: 800700e9 - No
process is on the other end of the pipe. .
> 12:14:04 PM Gatherer 3019 The crawl on project <SQLServer SQL0002000005>
has started.
>
|||John,
No kudos to Microsoft for the high level security requiements for MS Search and not providing a KB with a work around for highly secure environments.
FYI:
Even though I found posts that indicate the contrary, the MS Search service remains Local System Account regardless of which account you put in the the SQL Service or SQL Agent at installation or after the fact via the Enterprise Manager. I tried it on se
veral SQL Server installations on W2K and W2003.
My compromise solution was to create a domain account specifically for the MS Search service and make it local admin and sa in the SQL Server. This seems to function correctly for all the test I have tried. The SQL Server service is then still locked down
as long as there are no holes to exploit via the FTS queries. (I won't hold my breath)
Norman
|||Norman,
Unfortunately, I'd have to agree with you. FYI, the "Microsoft Search"
(mssearch.exe) service should ALWAYS be started and running under the
"system" or LocalSystem account as that how it is currently designed in SQL
Server 2000. However, for SQL Server 2005 (Yukon) this has changed and the
new MSSearch service, will be able to run under the same service account as
the MSSQLServer service.
If I was in the SQL MVP program, I'd be glad to volunteer and write such a
KB article, but alas I'm not *yet* in that program...
Regards,
John
"Norman" <anonymous@.discussions.microsoft.com> wrote in message
news:F954B2E0-FCDA-4692-8F80-D10DB1F5F701@.microsoft.com...
> John,
> No kudos to Microsoft for the high level security requiements for MS
Search and not providing a KB with a work around for highly secure
environments.
> FYI:
> Even though I found posts that indicate the contrary, the MS Search
service remains Local System Account regardless of which account you put in
the the SQL Service or SQL Agent at installation or after the fact via the
Enterprise Manager. I tried it on several SQL Server installations on W2K
and W2003.
> My compromise solution was to create a domain account specifically for the
MS Search service and make it local admin and sa in the SQL Server. This
seems to function correctly for all the test I have tried. The SQL Server
service is then still locked down as long as there are no holes to exploit
via the FTS queries. (I won't hold my breath)
> Norman
订阅：
博文 (Atom)