Bad performance with SQL Application when connecting from client

Hello,
I've a problem with a application witch uses SQL Server 2000 STD. Other
applications on the same SQL instance have no problems.
Windows 2000 SBS SP4
SQL Server 2000 SP3a
- Running the application from console or TS session on the SQL server is
fast.
- Named pipes connection or tcp/ip connection no difference
- shut down other applications on the server has no results in performance
- New installed test server with SBS 2000 gives no problem
- The performance of the server like not the problem.
Could you explain your question, please?
For example, what kind of conection do you use in the app.? , etc...
"GVWHIA" <GVWHIA@.discussions.microsoft.com> wrote in message
news:A6C849B2-C78B-4E66-B69E-0A888B7BDD70@.microsoft.com...
> Hello,
> I've a problem with a application witch uses SQL Server 2000 STD. Other
> applications on the same SQL instance have no problems.
> Windows 2000 SBS SP4
> SQL Server 2000 SP3a
> - Running the application from console or TS session on the SQL server is
> fast.
> - Named pipes connection or tcp/ip connection no difference
> - shut down other applications on the server has no results in performance
> - New installed test server with SBS 2000 gives no problem
> - The performance of the server like not the problem.
|||On Tue, 7 Dec 2004 06:53:03 -0800, GVWHIA
<GVWHIA@.discussions.microsoft.com> wrote:
>I've a problem with a application witch uses SQL Server 2000 STD. Other
>applications on the same SQL instance have no problems.
Could it be examining huge amounts of data that clog the network when
run remotely?
J.

Backups Not Deleting

We're running SQL Server 2000 service pack 4. I've created a Maintenance
Plan that backs up all user databases, each to its own subfolder, every
night. The plan is supposed to delete old copies as well which it is not
doing. If I change the plan to backup only 1 database it works (old copies
deleted). When I switch it to all user databases, no more deletes. And the
sqlmaint log file does not even show that any deletes were attempted. Note
that we do have some databases offline, so those are skipped, of course, and
the job does fail.
Please help! What's the secret to get the older files to delete?
Here's the Step on the SQL Agent job:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
21EA0A98-9812-4AEA-8BC0-08AEAEAB24EC -Rpt "c:\temp\DB Maintenance
Plan14.txt" -DelTxtRpt 1WEEKS -WriteHistory -VrfyBackup -BkUpMedia
DISK -BkUpDB "D:\Backup" -DelBkUps 1DAYS -CrBkSubDir -BkExt "BAK"'
Thanks,
Krip
>I believe that deletion is done *at the end* of the job, so if anything
>fails, it bails out and no deletion is performed. You can try by first
>including only database so the whole job succeeds.
Tibor,
Thanks for the tip! I detached all our offline databases, and job finishes
without error, and deletes the old backups. Beautiful.
-Krip
|||I have also seen this behavior when the plan includes both Full and Simple
Recovery model databases.
Kevin Hill
IC3 North Texas
www.ChristianCycling.com
Please support me in the 2008 MS150:
http://www.ms150.org/dallas/donate/donate.cfm?id=208000
"Krip" <amk@.kynetix.com> wrote in message
news:7A2C4350-5AED-4F70-A22A-47E64ED68DFD@.microsoft.com...
> Tibor,
> Thanks for the tip! I detached all our offline databases, and job
> finishes without error, and deletes the old backups. Beautiful.
> -Krip
>

backup/restore vs attach/detach

Hi... I've recently had to pick up being an SQL Administrator again after
about 5 years without touching it.
I have just installed SQL Server 2005 on a new cluster and it is all up and
running and ready to test our CRM application against it.
I'm a bit non-plussed about backup/restore, vs attach/detach functionality.
I want to copy the data from our SQL Server 2000 instance to our SQL Server
2005 instance. I seem to be able to do this by detaching the database,
copying the mdf/ldf and then re-attaching the copy data to the test SQL2005
server and re-attaching the original data to the SQL2000 copy which will
remain live until we finish testing compatibility. At which point we would
then copy the live data again from the SQL2000 instance to the SQL2005
instance and the use the SQL2005 version from there on.
THe problem is that when I detach, users can't get at the data. I've got
200 some internal users and lord knows how many external users accessing this
data. It doesn't seem right that there is no way to do this without
detaching the database interrupting the users? Can't I use a
backup/restore? I've tried but can't seem to get the restore to work. Is
there compatibility difference between SQL2000 backup and SQL2005 backup? In
which case is there someway I an get a copy of all the data on the SQL2000
server to the SQL2005 Server without interrupting users on the SQL2000
server?
Sorry, might seem a basic question, but I've been out of the line of fire
for a long time on this sort of thing, so I appreciate your patience.
Thanks
Bill
A backup and restore should work just fine and be a totally on-line
operation for the 2000 db. When you say you can't get it to work what
exactly is happening? Is there an error message?
Andrew J. Kelly SQL MVP
"billd" <billd@.discussions.microsoft.com> wrote in message
news:4CD409FA-7537-4967-A9CD-8813CF813DF9@.microsoft.com...
> Hi... I've recently had to pick up being an SQL Administrator again after
> about 5 years without touching it.
> I have just installed SQL Server 2005 on a new cluster and it is all up
> and
> running and ready to test our CRM application against it.
> I'm a bit non-plussed about backup/restore, vs attach/detach
> functionality.
> I want to copy the data from our SQL Server 2000 instance to our SQL
> Server
> 2005 instance. I seem to be able to do this by detaching the database,
> copying the mdf/ldf and then re-attaching the copy data to the test
> SQL2005
> server and re-attaching the original data to the SQL2000 copy which will
> remain live until we finish testing compatibility. At which point we
> would
> then copy the live data again from the SQL2000 instance to the SQL2005
> instance and the use the SQL2005 version from there on.
> THe problem is that when I detach, users can't get at the data. I've got
> 200 some internal users and lord knows how many external users accessing
> this
> data. It doesn't seem right that there is no way to do this without
> detaching the database interrupting the users? Can't I use a
> backup/restore? I've tried but can't seem to get the restore to work. Is
> there compatibility difference between SQL2000 backup and SQL2005 backup?
> In
> which case is there someway I an get a copy of all the data on the SQL2000
> server to the SQL2005 Server without interrupting users on the SQL2000
> server?
> Sorry, might seem a basic question, but I've been out of the line of fire
> for a long time on this sort of thing, so I appreciate your patience.
> Thanks
> Bill
>
|||billd wrote:
> Hi... I've recently had to pick up being an SQL Administrator again after
> about 5 years without touching it.
> I have just installed SQL Server 2005 on a new cluster and it is all up and
> running and ready to test our CRM application against it.
> I'm a bit non-plussed about backup/restore, vs attach/detach functionality.
> I want to copy the data from our SQL Server 2000 instance to our SQL Server
> 2005 instance. I seem to be able to do this by detaching the database,
> copying the mdf/ldf and then re-attaching the copy data to the test SQL2005
> server and re-attaching the original data to the SQL2000 copy which will
> remain live until we finish testing compatibility. At which point we would
> then copy the live data again from the SQL2000 instance to the SQL2005
> instance and the use the SQL2005 version from there on.
> THe problem is that when I detach, users can't get at the data. I've got
> 200 some internal users and lord knows how many external users accessing this
> data. It doesn't seem right that there is no way to do this without
> detaching the database interrupting the users? Can't I use a
> backup/restore? I've tried but can't seem to get the restore to work. Is
> there compatibility difference between SQL2000 backup and SQL2005 backup? In
> which case is there someway I an get a copy of all the data on the SQL2000
> server to the SQL2005 Server without interrupting users on the SQL2000
> server?
> Sorry, might seem a basic question, but I've been out of the line of fire
> for a long time on this sort of thing, so I appreciate your patience.
> Thanks
> Bill
>
Hi Bill
A Backup/Restore shouldn't cause a problem, and is actually it's a more
"safe" procedure. When you detach your database, you haven't got
anything until you get it attached again. Not even your source is
available. This means that you have a potential risk that the attach
fails and in that case you've lost your database. I know it's not very
likely that it really happens, but the risk is always there.
If you use backup/restore, you'll keep your source online and working.
Then if the backup file is corrupt in one way or the other, you can
always go back and do a new backup.
As Andrew mentions, you'll have to provide some more info on what it is
that goes wrong with your restore - then it's easier to help you...;-).
Regards
Steen
|||Backup and restore is the most obvious option...
However, if you have the SQL script to create your database on the SQL 2005
server, you could create it and then use DTS to copy across the data . This
gives you the opportunity to also review some of the data types used and
swap them for the SQL 2005 types alternatives (if preferred) in the SQL
script before creating the database.
Griff
|||In addition to the other posts, you might want to read about sp_change_users_login (and also search
KB for sp_help_revlogins).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"billd" <billd@.discussions.microsoft.com> wrote in message
news:4CD409FA-7537-4967-A9CD-8813CF813DF9@.microsoft.com...
> Hi... I've recently had to pick up being an SQL Administrator again after
> about 5 years without touching it.
> I have just installed SQL Server 2005 on a new cluster and it is all up and
> running and ready to test our CRM application against it.
> I'm a bit non-plussed about backup/restore, vs attach/detach functionality.
> I want to copy the data from our SQL Server 2000 instance to our SQL Server
> 2005 instance. I seem to be able to do this by detaching the database,
> copying the mdf/ldf and then re-attaching the copy data to the test SQL2005
> server and re-attaching the original data to the SQL2000 copy which will
> remain live until we finish testing compatibility. At which point we would
> then copy the live data again from the SQL2000 instance to the SQL2005
> instance and the use the SQL2005 version from there on.
> THe problem is that when I detach, users can't get at the data. I've got
> 200 some internal users and lord knows how many external users accessing this
> data. It doesn't seem right that there is no way to do this without
> detaching the database interrupting the users? Can't I use a
> backup/restore? I've tried but can't seem to get the restore to work. Is
> there compatibility difference between SQL2000 backup and SQL2005 backup? In
> which case is there someway I an get a copy of all the data on the SQL2000
> server to the SQL2005 Server without interrupting users on the SQL2000
> server?
> Sorry, might seem a basic question, but I've been out of the line of fire
> for a long time on this sort of thing, so I appreciate your patience.
> Thanks
> Bill
>
|||Thanks Steen... I feel the same way that you do about detach/attach! It took
a really long time to detach the northwind database and I was very nervous
the whole while that it might take down the SQL engine itself.. I hate when
things take a long time... so, anyway, the failure: My SQL2000 data is on
the F: drive, which is a clustered resource. I backup up to base.bak in the
temp dir. Then I copy this to my S: drive which is the clustered resource on
my SQL2005 server. I then try to restore this base.bak file (it only has one
backup in it) I think get the error message: (grrrr, why don't then let you
cut and paste the text from error messages?)
... and as I did this, I realized... just this very minute, that I hadn't
gone to the database page and checked the overwrite database box... was the
problem, so yes I have found the answer...
Thanks to all for your help! I really only needed to know that
backup/restore should work and then could figure it out myself I guess.
restore failed for server SQLDB. (Microsoft.SQLServer.SMO)
Additional Information
System.data.sqlclient.sqlerror cannot use file:
"Steen Persson (DK)" wrote:

> billd wrote:
> Hi Bill
> A Backup/Restore shouldn't cause a problem, and is actually it's a more
> "safe" procedure. When you detach your database, you haven't got
> anything until you get it attached again. Not even your source is
> available. This means that you have a potential risk that the attach
> fails and in that case you've lost your database. I know it's not very
> likely that it really happens, but the risk is always there.
> If you use backup/restore, you'll keep your source online and working.
> Then if the backup file is corrupt in one way or the other, you can
> always go back and do a new backup.
> As Andrew mentions, you'll have to provide some more info on what it is
> that goes wrong with your restore - then it's easier to help you...;-).
> Regards
> Steen
>

Backup/Restore Versus Detach/Attach

Hi All,
when I've to deploy my applications (c# and MSDE), generally I make a backup
on th edevelopment machine of the SQL Database the on the deployment machine
I make a restore and restoring from the backup I get my database up and
running (so boiring that I've to change the data/log file path at any
restore...).
Are there any real differences if instead of backup/restore I detach the
database file from the development machine (running sql developer edition)
then copy the file on the deployment machine and then make an attach to that
file?
which one is the best way?
Thanks, Davide.
Davide Piras
Naxo Software
hi Davide,
Davide Piras wrote:
> Hi All,
> when I've to deploy my applications (c# and MSDE), generally I make a
> backup on th edevelopment machine of the SQL Database the on the
> deployment machine I make a restore and restoring from the backup I
> get my database up and running (so boiring that I've to change the
> data/log file path at any restore...).
> Are there any real differences if instead of backup/restore I detach
> the database file from the development machine (running sql developer
> edition) then copy the file on the deployment machine and then make
> an attach to that file?
> which one is the best way?
please remember to add the log file to your distribution too, as it's part
of the dabase files set... and reattaching without the log file can end in
attaching problems...
the restore scenario only requires a little more physical space as you have
to consider the backup size in addition to the database files size...
both scenarios are simple to run but you have to consider some potential
issues..
MSDE usually set the "autoclose" database property, where the full blown SQL
Server editions do not, so you'll end up with a database that does not
reflect this standard MSDE setting, but it could be a choice...
you do not inherit database settings/options/objects from the end user model
database but from yours...
this can bring up some problems regarding orphaned users you will have to
fix by use of the sp_change_users_login system stored procedure
(http://msdn.microsoft.com/library/de...ca-cz_8qzy.asp
, http://www.sqlservercentral.com/colu...okenlogins.asp )
personally I go this way, http://tinyurl.com/dyjjg , that will even help in
structure upgrade to, and can grant a database deployment based on version
control check ... and the best article I found for this kind of situation is
http://msdn.microsoft.com/msdnmag/is...baseinstaller/
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Ciao Andrea,
thanks for you help, I will read those articles BEFORE the next deployment
loop...
you know I use your DbaManager 0.12.0 as a Client User Interface?! :-)
Nice and useful Tool, thanks!
Davide.

Backup/restore Databases remotely.

Hi i've tried a few remote administration tools including Microsofts own Web Data Administrator for SQL server... but none of them seem to do what is essential to me and that is backup/restore databases to a file.
Does anyone know of a free tool that can just backup/restore databases remotely. I dont really want to have to use export/import data as things like triggers get lost when using Microsoft Web data administrator.
A free web tool written in ASP.NET or ASP would be ideal. But PHP would also be fine.
Thanks
I don't understand . Why did you say that you can not
backup/restore to a file. As long as SQL server can
access that folder, it can backup/restore to/from that
folder.
>--Original Message--
>Hi i've tried a few remote administration tools
including Microsofts own Web Data Administrator for SQL
server... but none of them seem to do what is essential
to me and that is backup/restore databases to a file.
>Does anyone know of a free tool that can just
backup/restore databases remotely. I dont really want to
have to use export/import data as things like triggers
get lost when using Microsoft Web data administrator.
>A free web tool written in ASP.NET or ASP would be
ideal. But PHP would also be fine.
>Thanks
>.
>
|||SQL server itself can backup/restore yes. But the remote web admin tool cannot.. it can only import/export data which does not include things like triggers.
See this page:
http://www.databasejournal.com/featu...0894_3330301_2
And I quote:
"Database Administration Functions missing from Web Data Administration tool
- Can't create a database backup and restore a database "
|||The article also says:
Even though these are not provided with the tool, some of
these items can be performed by using the Query tool to
submit T-SQL code to perform these tasks.
You can do backups and restores through T-SQL. In many
cases, most of what any of the tools do can be done in
T-SQL.
-Sue
On Thu, 8 Apr 2004 12:46:05 -0700, "GregD"
<anonymous@.discussions.microsoft.com> wrote:

>SQL server itself can backup/restore yes. But the remote web admin tool cannot.. it can only import/export data which does not include things like triggers.
>See this page:
>http://www.databasejournal.com/featu...0894_3330301_2
>And I quote:
>"Database Administration Functions missing from Web Data Administration tool
>- Can't create a database backup and restore a database "
|||Hi Sue,
What would the syntax be for backup/restore in TSQL? Or do you have a link where I can find this out myself?
Thanks for your help.
|||No matter found it myself on MSDN:
BACKUP DATABASE DBNameGoesHere
TO DISK = 'C:\temp\Somefilename.bak'
WITH INIT
RESTORE DATABASE DBNameGoesHere
FROM DISK = 'C:\temp\Somefilename.bak'
WITH REPLACE
|||Books Online is available within the SQL Server program group (assuming =
that you have SQL Server installed). =20
Books Online can be downloaded separately if necessary:
http://www.microsoft.com/sql/downloads/default.asp
Here is the specific link to the BOL download:
http://www.microsoft.com/sql/techinf...2000/books.asp
--=20
Keith
"GregD" <anonymous@.discussions.microsoft.com> wrote in message =
news:FBB86780-E276-4A02-B618-A7360E899FDC@.microsoft.com...
> No matter found it myself on MSDN:
>=20
> BACKUP DATABASE DBNameGoesHere
> TO DISK =3D 'C:\temp\Somefilename.bak'
> WITH INIT
>=20
> RESTORE DATABASE DBNameGoesHere
> FROM DISK =3D 'C:\temp\Somefilename.bak'
> WITH REPLACE

Backup/copy to remote server

Hello,
I've spent the afternoon reading old newsgroup posts and BOL but am
still a bit confused about the best way to approach a new backup
scheme.
My organization would like to establish a single repository for backups
from our database servers, by either backing up databases on each
server and then moving them to the new, central server in some
automated fashion, or by simply pointing to the remote server as the
backup device.

>From a network performance standpoint I think backing up locally and
then copying the resulting files to a remote destination may be the
better option, as we have several servers and at least one of the
databases residing on them results in an almost 2GB backup file (with
most of the remainder smaller than that). I'm also unsure of exactly
how to go about this.
I believe SQL Server has been installed by default under the local
system account, so I'm wondering if this will limit my options with
regard to authentication across servers.
I've read about the BACKUP command, "log shipping" and other options,
but am not sure if any match our requirements.
If anyone could shine a light down the right path for me, my blood
pressure would be grateful. ;)
vast55555@.yahoo.com wrote:
> Hello,
> I've spent the afternoon reading old newsgroup posts and BOL but am
> still a bit confused about the best way to approach a new backup
> scheme.
> My organization would like to establish a single repository for backups
> from our database servers, by either backing up databases on each
> server and then moving them to the new, central server in some
> automated fashion, or by simply pointing to the remote server as the
> backup device.
> then copying the resulting files to a remote destination may be the
> better option, as we have several servers and at least one of the
> databases residing on them results in an almost 2GB backup file (with
> most of the remainder smaller than that). I'm also unsure of exactly
> how to go about this.
> I believe SQL Server has been installed by default under the local
> system account, so I'm wondering if this will limit my options with
> regard to authentication across servers.
> I've read about the BACKUP command, "log shipping" and other options,
> but am not sure if any match our requirements.
> If anyone could shine a light down the right path for me, my blood
> pressure would be grateful. ;)
>
Create a share on your central server, configure SQL to run as a domain
user, grant that domain user permissions to the share, then use the
BACKUP command to backup directly to the UNC for that share. We move a
dozen backups around on our network each night, some of which are
approaching 200GB, there is no noticeable difference between backing up
locally and then copying vs. backing up over the network.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Hi Tracy,
Thanks for your quick response. That sounds like the way to go. One
potential problem is that this is a hosted situation--not in-house--and
I think there may be no domain, but rather isolated machines (I'm not a
network engineer by trade so I'm a bit ignorant here).
Tracy McKibben wrote:
> Create a share on your central server, configure SQL to run as a domain
> user, grant that domain user permissions to the share, then use the
> BACKUP command to backup directly to the UNC for that share. We move a
> dozen backups around on our network each night, some of which are
> approaching 200GB, there is no noticeable difference between backing up
> locally and then copying vs. backing up over the network.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||Performance wise, backing up straight to a remote file share is often faster
than backing up locally and then copying the backup file to the same file
share. But there are other factors to consider. For instance, with backing up
straight to the remote file share, if it fails towards to the end due to some
network issue, the retry may take longer than just retrying the copy
operation.
Linchi
"vast55555@.yahoo.com" wrote:

> Hello,
> I've spent the afternoon reading old newsgroup posts and BOL but am
> still a bit confused about the best way to approach a new backup
> scheme.
> My organization would like to establish a single repository for backups
> from our database servers, by either backing up databases on each
> server and then moving them to the new, central server in some
> automated fashion, or by simply pointing to the remote server as the
> backup device.
> then copying the resulting files to a remote destination may be the
> better option, as we have several servers and at least one of the
> databases residing on them results in an almost 2GB backup file (with
> most of the remainder smaller than that). I'm also unsure of exactly
> how to go about this.
> I believe SQL Server has been installed by default under the local
> system account, so I'm wondering if this will limit my options with
> regard to authentication across servers.
> I've read about the BACKUP command, "log shipping" and other options,
> but am not sure if any match our requirements.
> If anyone could shine a light down the right path for me, my blood
> pressure would be grateful. ;)
>

Backup via Maintenance Plan

Hi all,
Just a quick question. I've searched through the docs for this but am still
not certain. Can you tell me if, during a Maintenance Plan's backup
procedure, the database is still available or does the MP put it into
Single-User Mode / Offline?
Cheers,
IM
As in all backups, the database is available to all users.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Ian Murphy" <IanMurphy@.discussions.microsoft.com> wrote in message
news:E1C32915-5794-45BF-BE2F-513937DBCD1A@.microsoft.com...
Hi all,
Just a quick question. I've searched through the docs for this but am still
not certain. Can you tell me if, during a Maintenance Plan's backup
procedure, the database is still available or does the MP put it into
Single-User Mode / Offline?
Cheers,
IM
|||Many thanks Tom. That's what I thought but it's good to know for sure.
Cheers,
IM
"Tom Moreau" wrote:

> As in all backups, the database is available to all users.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Ian Murphy" <IanMurphy@.discussions.microsoft.com> wrote in message
> news:E1C32915-5794-45BF-BE2F-513937DBCD1A@.microsoft.com...
> Hi all,
> Just a quick question. I've searched through the docs for this but am still
> not certain. Can you tell me if, during a Maintenance Plan's backup
> procedure, the database is still available or does the MP put it into
> Single-User Mode / Offline?
> Cheers,
> IM
>
|||This is true but there are a few file management type operations that
cannot run while a backup is in progress:
* ALTER DATABASE (with ADD FILE or REMOVE FILE)
* DBCC SHRINKDATABASE
* DBCC SHRINKFILE
A backup won't start while these are in progress and you can't start one
of these operations while a backup is in progress.
*mike hodgson*
/ mallesons stephen jaques/
blog: http://sqlnerd.blogspot.com
Tom Moreau wrote:

>As in all backups, the database is available to all users.
>
>