Poor performance

Hi

I have the following structure:

At server, I have SQL Server 2005 with a database running in compability mode 8.0 (SQL 2000). At desktops (cliente side) I have SQL Server 2005 Express. The base on the desktops access the base on the server to a copy of data through Linked Server. Both bases are in compability mode 8.0.

This is my problem:

The copy of data using a ad-hoc query INSERT INTO LOCAL-DATABASE ... SELECT .... FROM REMOTE-DATABASE (WAN) takes much more time than if I use MSDE in the desktops.

I would like to know what problem can be cause this delay.

Thanks

Did you check 'Collation Compatible' option in the linked server options?

|||

Yes I did. This option is defined "FALSE". Is It right?

Tks

|||It should be set to TRUE.

Poor performance

Hi

I have the following structure:

At server, I have SQL Server 2005 with a database running in compability mode 8.0 (SQL 2000). At desktops (cliente side) I have SQL Server 2005 Express. The base on the desktops access the base on the server to a copy of data through Linked Server. Both bases are in compability mode 8.0.

This is my problem:

The copy of data using a ad-hoc query INSERT INTO LOCAL-DATABASE ... SELECT .... FROM REMOTE-DATABASE (WAN) takes much more time than if I use MSDE in the desktops.

I would like to know what problem can be cause this delay.

Thanks

Did you check 'Collation Compatible' option in the linked server options?

|||

Yes I did. This option is defined "FALSE". Is It right?

Tks

|||It should be set to TRUE.

point in time restore

My database is in full recovery mode. When I have created some full backups of the database, I would like try to point in time restore. Unfortunately, this option is greyed out on the restore screen. What have I done wrong?From the holy book once again

Recovering to a Point In Time

You can recover to a point in time by recovering only the transactions that occurred before a specific point in time within a transaction log backup, rather than the entire backup. By viewing the header information of each transaction log backup or the information in the backupset table in msdb, you can quickly identify which backup contains the time to which you want to restore the database. You then need only apply transaction log backups up to that point.

You cannot skip specific transactions. This would compromise the integrity of the data in the database. Any transactions that occur after the transaction you want to undo might depend on the data modified by the undone transaction.

If you do not want to restore any modifications made to the database after a specific point in time:

Restore the last database backup without recovering the database.
Apply each transaction log backup in the same sequence in which they were created.
Recover the database at the desired point in time within a transaction log backup.
This process also can be used to restore a database and transaction logs if some transaction log backups created after a point in time are missing or damaged.

Point-in-time recovery is not supported with the Bulk-Logged Recovery model. Bulk-Logged recovery only allows the database to be recovered to the end of a transaction log backup when the log backup contains bulk changes.sql

Please helpLog file will not reduce in SQL 2000 database

My Database plan is as follows:

Full Database backup every night, Full recovery mode.
T-Log backups , every 4 hours.

both are appended to media,

there are no active transactions,

but Log file will not shrink or reduce in size.

what would you suggest.

Thanks,Pls refer the below links,
http://support.microsoft.com/kb/110139 and http://support.microsoft.com/kb/873235
if you need to shrink the tran log you can perform as below,

Backup log DATABASENAME with truncate_only and then shrink the log file using DBCC shrinkfile command refer BOL for the syntax ! but it is not recommended to shrink file often

Thanxx
Deepak
|||

I guess you mean the physical LDF file does not reduce in size? While a backup of the log file truncates the contents of the log, it will not give that space back to the operating system. This is by design. In almost all cases, there is no point in shrinking the physical file every time it is backed up as it will almost certainly need to grow again in the near future.

To actually increase the size of the file, SQL Server has to request space from the OS and this is quite a "costly" operation in terms of resources. Therefore, you want to minimise the amount of times the log file has to grow and the easiest way of doing this is to keep it at its "optimum" size.ie your log file should be sized according to how large it will typically grow.

If your Transcation Log is huge (maybe due to a one off reindex or data purge) then you can physically shrink it by issuing a DBCC SHRINKFILE statement. Check Books Online for details.

HTH!