显示标签为“drive”的博文。显示所有博文
显示标签为“drive”的博文。显示所有博文

2012年3月9日星期五

Full Text catalogue best drive to build on

We have a server with 2 drives. Drive 1 has our swap file, operating
system and SQL transaction log writing to it.
Drive 2 has our SQL database and flat file dumps (db backup)
We can't install a 3rd drive pair as there is not physical room in the
server. Which would be the better drive to have the FT catalogues built
on for best effeciency and speed of search?
We have 14GB of RAM on this box and our FT catalogue is only about
0.5GB. Would any of the catalogue stay memory resident or will there be
a lot of disk reads occuring?
Cheers
Julia
*** Sent via Developersdex http://www.codecomments.com ***
Try your second drive which stored the backups.
The caching mechanism used by SQL FTS is the file system cache. Frequently
requested catalog pages will be in the file system cache. Other pages will
be flushed to disk.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Julia" <julia.nicholas@.nospamgmail.com> wrote in message
news:exZZSLStFHA.2212@.TK2MSFTNGP15.phx.gbl...
> We have a server with 2 drives. Drive 1 has our swap file, operating
> system and SQL transaction log writing to it.
> Drive 2 has our SQL database and flat file dumps (db backup)
> We can't install a 3rd drive pair as there is not physical room in the
> server. Which would be the better drive to have the FT catalogues built
> on for best effeciency and speed of search?
> We have 14GB of RAM on this box and our FT catalogue is only about
> 0.5GB. Would any of the catalogue stay memory resident or will there be
> a lot of disk reads occuring?
> Cheers
> Julia
>
> *** Sent via Developersdex http://www.codecomments.com ***

Full text catalog problem

I have a database which has (god knows how) ended up with a Full text catalog which is believes is on the E: drive of the machine. The problem is that machine only has C: drive.

If I try and rebuid the catalog (sp_fulltext_catalog 'documents','rebuild') I get an error saying that e:\... doesn't exist.

If I try and drop the catalog (sp_fulltext_catalog 'documents', 'drop'), I can't because it contains a full-text index.

If I try and drop remove the full text index from the table (sp_fulltext_table 'tbl_upload','drop'), I can't because Full-text catalog has been lost. Use sp_fulltext_catalog to rebuild and to repopulate it.

HELP

Does anybody know how to either drop it so that I can recreate it or how to update it so that it looks at a disk that the machine actually has?I updated the sysfulltextcatalogs table and set the path to null in there, this caused the system to then use the servers default path. I had tried to do this but was fooled by the fact that Enterprise manager claimed the table was empty whereas when I did a select from it from Query Analyser it returned the data.

2012年2月26日星期日

full drives full trans log version 7

What does one do?
I tried "backup log mb with no_log" to truncate the file. I've now
started a shrinkdatabase. I can't backup since the drive is full. I
can't disconnect because it's 7.
How does one actually reduce the log file size?
thx
md
PS isn't simple logging supposed to avoid this problem?
PSS is it possible to set a DTS to not log?
*** Sent via Developersdex http://www.codecomments.com ***See if this helps:
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/kb/256650/EN-US/
AMB
"M D" wrote:

> What does one do?
> I tried "backup log mb with no_log" to truncate the file. I've now
> started a shrinkdatabase. I can't backup since the drive is full. I
> can't disconnect because it's 7.
> How does one actually reduce the log file size?
> thx
> md
> PS isn't simple logging supposed to avoid this problem?
> PSS is it possible to set a DTS to not log?
> *** Sent via Developersdex http://www.codecomments.com ***
>|||Hi,
Since you dont require the transaction log backup you could truncate the log
first
BACKUP LOG DBNAME with TRUNCATE_ONLY
After that check the log usage by executing DBCC SQLPERF(LOGSPACE)
If the usage is very low than SHRINK the LDF file using DBCC
SHRINKFILE('logical ldf name',size)
Please refer the link Alejandro pointed out.
Thanks
Hari
SQL Server MVP
"M D" <mardukes@.aol.com> wrote in message
news:enQLS9rZFHA.2664@.TK2MSFTNGP15.phx.gbl...
> What does one do?
> I tried "backup log mb with no_log" to truncate the file. I've now
> started a shrinkdatabase. I can't backup since the drive is full. I
> can't disconnect because it's 7.
> How does one actually reduce the log file size?
> thx
> md
> PS isn't simple logging supposed to avoid this problem?
> PSS is it possible to set a DTS to not log?
> *** Sent via Developersdex http://www.codecomments.com ***|||I am new to this site, but would suggest a DB backup after you truncate the
log file, just to be safe.
"Hari Prasad" wrote:

> Hi,
> Since you dont require the transaction log backup you could truncate the l
og
> first
> BACKUP LOG DBNAME with TRUNCATE_ONLY
> After that check the log usage by executing DBCC SQLPERF(LOGSPACE)
> If the usage is very low than SHRINK the LDF file using DBCC
> SHRINKFILE('logical ldf name',size)
> Please refer the link Alejandro pointed out.
> Thanks
> Hari
> SQL Server MVP
> "M D" <mardukes@.aol.com> wrote in message
> news:enQLS9rZFHA.2664@.TK2MSFTNGP15.phx.gbl...
>
>|||Also run a DBCC OPENTRAN agains the database in question to make sure there
are no pending transactions to be completed.
The trasaction log architecture is different in SS7.0 versus SS2K and can be
quite difficult to shrink, even when using BACKUP LOG ... WITH NO_LOG or
WITH TRUNCATE_ONLY, which are synonomous by the way.
Usually, you will have to kill all processes but your own within the
database you are concerned with.
Execute sp_detach_db.
Delete the Transaction log file(s) for the database in question.
Execute sp_attach_single_file_db.
The new transaction log will be the size of the transaction log for the
model database.
Also know that regardless of Recovery Model, which didn't exist in
SS7.0-there were database options: 'trunc. log on chkpt.' and/or 'select
into/bulk copy'-EVERY TRANSACTION IS WRITTEN TO THE TRANSACTION LOG. There
is no way to stop this. The recovery settings just tell SQL Server what to
do with "COMPLETED" transactions within the transaction log file if they
have not been backed up yet.
If you are using 'trunc. log on chkpt.', then completed transactions are
automatically purged on the next CHECKPOINT process. This allows you to
write over that freed up space with the next transaction. However, your
transaction log will grow to support the largest single active transaction
or concurrent set of transactions.
If transactions are open, not completed or rolled back, then new
transactions can not overwrite those regardless of the recovery settings, or
db options, as the case may be.
Sincerely,
Anthony Thomas
"NetByTelDBA" <NetByTelDBA@.discussions.microsoft.com> wrote in message
news:8E5D6182-C9B9-4999-BBD7-5074AA5BF78A@.microsoft.com...
I am new to this site, but would suggest a DB backup after you truncate the
log file, just to be safe.
"Hari Prasad" wrote:

> Hi,
> Since you dont require the transaction log backup you could truncate the
log
> first
> BACKUP LOG DBNAME with TRUNCATE_ONLY
> After that check the log usage by executing DBCC SQLPERF(LOGSPACE)
> If the usage is very low than SHRINK the LDF file using DBCC
> SHRINKFILE('logical ldf name',size)
> Please refer the link Alejandro pointed out.
> Thanks
> Hari
> SQL Server MVP
> "M D" <mardukes@.aol.com> wrote in message
> news:enQLS9rZFHA.2664@.TK2MSFTNGP15.phx.gbl...
>
>

2012年2月24日星期五

Full and Differential Backups

Does the full and differential backups have to be in the same location or can I do a once a week full backup on one drive and everyday differential backups on a different drive?

Thanks

Hi,

if you store your backup local, it's still better to have them on the same drive - by watching your systemmonitor.

But it doesn' matter wehre you store your backups.

|||

As such there is no restriction. you can have your full and differential back any where. If you are taking the backup in local machine , your backup is not fully fault tollerent. YOu should have either a network path or tape drive to store the backup.

Madhu

|||

Another thought is to do your backups to a local disk (which one makes no difference) and then archive this of to secondary storage (network share from another machine, tape, etc.)

The reason for this is that backups and restores to/from a local disk will be the fastest method. So, for your backups, and in the event of a restore due to a single bad disk (or single bad decision Smile ) these will be optimized. Then you can add a layer of fault-tolerance by copying them off to secondary storage which will be protected in the event of a complete disaster.