I/O bottleneck with SQL agent

Hello,

I have an SP that transfers data from one DB to another.

As I run the SP with the management studio - it takes about 15min. But when I scheduled it as an SQL agent job it ran all night and never completed.

The difference I noticed was that the % disk time (of the destination DB) was very high (~100) when I used the agent (which never happened when I ran the SP with the studio).

Only when I restart the SQL service, the disk gets back to normal % disk time.

What could be the reason for that?

My server - Windows 2003 R2 SP1 / SQL 2005 SP2

p.s - could it be related to SQL SP2 ? I didn't have this problem with SP1, but then - I had many other changes since.

Hello Haggai,

Is it possible that something else was running and blocking your process.

You need to check what else was running on the system and also need to capture the perfmon to capture the counters for disk, cpu, memory etc.

regards

Jag

|||By the way, could check SQL Server agent log as well for any errors.|||See if this helps.
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx#EFRAEsql

I want to ''Copy'' 3 tables from an existing DB into a new DB on the same SqlServer instance

Is there a quick way in Management Studio to ... i don't know ... drag and drop or maybe copy and paste or import the 3 tables? (The 2 databases are on the same instance of SqlServer 2005)

-perplexed newbie

AFAIK .. there is no drag & drop or copy paste feature for this in SSMO. If u want only data u can use select *into DB1.DBo.ObjectName from DB1.dbo.Objectname. If you want with exact schema, then script it from DB1 run it in DB2 and then copy the data using insert into statement

Madhu

|||Right click on first db->Tasks->Export data... and you will go in an easy way to copy your tables|||

ggciubuc wrote:

Right click on first db->Tasks->Export data... and you will go in an easy way to copy your tables

this will cretae a table with only data not with all constraints. To create constratin u have to script it.

Madhu

|||Yes it is and is a normal situation because in a new database in many cases, there are not same relationships between tables. To the other part these constraints can be easy rebuild with Database Diagramssql

I want to ''Copy'' 3 tables from an existing DB into a new DB on the same SqlServer instance

Is there a quick way in Management Studio to ... i don't know ... drag and drop or maybe copy and paste or import the 3 tables? (The 2 databases are on the same instance of SqlServer 2005)

-perplexed newbie

AFAIK .. there is no drag & drop or copy paste feature for this in SSMO. If u want only data u can use select *into DB1.DBo.ObjectName from DB1.dbo.Objectname. If you want with exact schema, then script it from DB1 run it in DB2 and then copy the data using insert into statement

Madhu

|||Right click on first db->Tasks->Export data... and you will go in an easy way to copy your tables|||

ggciubuc wrote:

Right click on first db->Tasks->Export data... and you will go in an easy way to copy your tables

this will cretae a table with only data not with all constraints. To create constratin u have to script it.

Madhu

|||Yes it is and is a normal situation because in a new database in many cases, there are not same relationships between tables. To the other part these constraints can be easy rebuild with Database Diagramssql

I want to ''Copy'' 3 tables from an existing DB into a new DB on the same SqlServer instance

Is there a quick way in Management Studio to ... i don't know ... drag and drop or maybe copy and paste or import the 3 tables? (The 2 databases are on the same instance of SqlServer 2005)

-perplexed newbie

AFAIK .. there is no drag & drop or copy paste feature for this in SSMO. If u want only data u can use select *into DB1.DBo.ObjectName from DB1.dbo.Objectname. If you want with exact schema, then script it from DB1 run it in DB2 and then copy the data using insert into statement

Madhu

|||Right click on first db->Tasks->Export data... and you will go in an easy way to copy your tables|||

ggciubuc wrote:

Right click on first db->Tasks->Export data... and you will go in an easy way to copy your tables

this will cretae a table with only data not with all constraints. To create constratin u have to script it.

Madhu

|||Yes it is and is a normal situation because in a new database in many cases, there are not same relationships between tables. To the other part these constraints can be easy rebuild with Database Diagrams

I want to ''Copy'' 3 tables from an existing DB into a new DB on the same SqlServer instance

Is there a quick way in Management Studio to ... i don't know ... drag and drop or maybe copy and paste or import the 3 tables? (The 2 databases are on the same instance of SqlServer 2005)

-perplexed newbie

AFAIK .. there is no drag & drop or copy paste feature for this in SSMO. If u want only data u can use select *into DB1.DBo.ObjectName from DB1.dbo.Objectname. If you want with exact schema, then script it from DB1 run it in DB2 and then copy the data using insert into statement

Madhu

|||Right click on first db->Tasks->Export data... and you will go in an easy way to copy your tables|||

ggciubuc wrote:

Right click on first db->Tasks->Export data... and you will go in an easy way to copy your tables

this will cretae a table with only data not with all constraints. To create constratin u have to script it.

Madhu

|||Yes it is and is a normal situation because in a new database in many cases, there are not same relationships between tables. To the other part these constraints can be easy rebuild with Database Diagrams

I want to ''Copy'' 3 tables from an existing DB into a new DB on the same SqlServer instance

Is there a quick way in Management Studio to ... i don't know ... drag and drop or maybe copy and paste or import the 3 tables? (The 2 databases are on the same instance of SqlServer 2005)

-perplexed newbie

AFAIK .. there is no drag & drop or copy paste feature for this in SSMO. If u want only data u can use select *into DB1.DBo.ObjectName from DB1.dbo.Objectname. If you want with exact schema, then script it from DB1 run it in DB2 and then copy the data using insert into statement

Madhu

|||Right click on first db->Tasks->Export data... and you will go in an easy way to copy your tables|||

ggciubuc wrote:

Right click on first db->Tasks->Export data... and you will go in an easy way to copy your tables

this will cretae a table with only data not with all constraints. To create constratin u have to script it.

Madhu

|||Yes it is and is a normal situation because in a new database in many cases, there are not same relationships between tables. To the other part these constraints can be easy rebuild with Database Diagrams

I want to ''Copy'' 3 tables from an existing DB into a new DB on the same SqlServer instance

Is there a quick way in Management Studio to ... i don't know ... drag and drop or maybe copy and paste or import the 3 tables? (The 2 databases are on the same instance of SqlServer 2005)

-perplexed newbie

AFAIK .. there is no drag & drop or copy paste feature for this in SSMO. If u want only data u can use select *into DB1.DBo.ObjectName from DB1.dbo.Objectname. If you want with exact schema, then script it from DB1 run it in DB2 and then copy the data using insert into statement

Madhu

|||Right click on first db->Tasks->Export data... and you will go in an easy way to copy your tables|||

ggciubuc wrote:

Right click on first db->Tasks->Export data... and you will go in an easy way to copy your tables

this will cretae a table with only data not with all constraints. To create constratin u have to script it.

Madhu

|||Yes it is and is a normal situation because in a new database in many cases, there are not same relationships between tables. To the other part these constraints can be easy rebuild with Database Diagrams

I try to create a maintenance plan I am getting the error

TITLE: Microsoft SQL Server Management Studio
Exception has been thrown by the target of an invocation. (mscorlib)
ADDITIONAL INFORMATION:
An OLE DB error 0x80004005 (Client unable to establish connection) occurred while enumerating packages. A SQL statement was issued
and failed.
An OLE DB error 0x80004005 (Client unable to establish connection) occurred while enumerating packages. A SQL statement was issued
and failed.
And evenlog message
The prelogin packet used to open the connection is structurally invalid; the connection has been closed. Please contact the vendor
of the client library. [CLIENT: <local machine>]
I am using Sql Server 2005 Developer Edition 9.0.1399.
I have searched around but cannot find.
Thank you for your help.
ZaferHi Zafer,
Welcome to use MSDN Managed Newsgroup Support.
From your description, my understanding of this issue is: When you create a
Maintenance plan in SQL Server Management Studio, you get the following
error:
An OLE DB error 0x80004005 (Client unable to establish connection) occurred
while enumerating packages. A SQL statement was issued and failed.
If I misunderstood your concern, please feel free to point it out.
Please verify that your server has a valid cert stored in
computer->personal.
On the SQL Server computer, create a Microsoft Management Console (MMC)
snap-in for the Certificates tool. To do this, follow these steps:
a. Click "Start", click "Run", type "mmc" (without the quotation marks)
in the
"Open" box, and then click "OK".
b. On the "File" menu, click "Add/Remove Snap-in".
c. Click "Add", click "Certificates", click "Add", click "Computer
account",
click "Next", click "Finish", click "Close", and then click "OK".
Also, you may check your SQL Server Service Account. If you run SQL Server
as local system, is this issue occur?
Hope this will be helpful!
Sincerely yours,
Wei Lu
Microsoft Online Partner Support
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

I think I found the solution

I recently (yesterday) installed a fresh copy of Windows XP with Service Pack 2 applied.

I proceeded with Visual Studio 2005 Express Editions installation straight away.

But I cannot install Microsoft SQL Server 2005 Express Edition x86.

I choose to install Microsoft SQL Server 2005 Express Edition x86 component along with every component of visual studio but it simply would not install.

In the end, after the installation of each complete is complete, I simply get an error message saying that Microsoft SQL Server 2005 Express Edition x86 failed to install.

What is the problem?

First take a look at the following file: %Program Files%\Microsoft SQL Server\90\Setup Bootstrap\log\summary.txt. This should point you to which component failed and the specific log file for the component. When you open the component log file search for "Value 3" (this is a Windows Installer code for the error section). Take a look at +/- 10 lines for the error.

If the summary.txt file doesn't provide any clues, take a look at: %Program Files%\Microsoft SQL Server\90\Setup Bootstrap\log\files\*_core(local).txt - open the most recent one if there are multiple. Go to the end of the file and you should find some clues as to why the installation failed.

|||

I looked. There were three errors and all of them said that installation cannot be done to a compressed drive

--

Error : SQL Server Setup cannot install files to the compressed or encrypted folder: C:\Program Files\Microsoft SQL Server\. To continue, make sure that your installation directories are not compressed or encrypted, or specify a different directory, and then run SQL Server Setup again.

--

I'll remove drive compression and try again.

Thanks for help!

|||

I am having the same problem - cannot install SQL Server Express, I looked in the file you mentioned to the other fellow and it says:

D43QNW81 : SQL Server 2005 Setup has detected incompatible components from beta versions of Visual Studio, .NET Framework, or SQL Server 2005. Use Add or Remove Programs to remove these components, and then run SQL Server 2005 Setup again. For detailed instructions on uninstalling SQL Server 2005, see the SQL Server 2005 Readme.

but I HAVE removed all components except a thing called Enterprise Library June 2005, and thaat one will not un-install without dot net framework 1.1

is that it? do I have to go ALL the way back?

any help will be much appreciated, I installed the whole works in a Virutal machine and now I am trying to install it on my base machine (because the Virtual one is too slow)

|||

David,

You can take a look at the latest *_core(local).log file to see which old component it's detecting. The log files can be found here: %Program Files%\Microsoft SQL Server\90\Setup Bootstrap\log\files

If you email me directly at dtjones@.microsoft.com I can send you a clean up script. The script isn't supported but we've had good luck with it.

Cheers,
Dan

I need to use Sql expres with reporting services

Which ctp already includes reporting services. Do you recommend me to work with the latest ctp of visual studio and sql 2005?
Or vs 2005 beta 2 with the latest sql 2005 ctp.?

ThanksSomebody there?? ?|||The particular CTP isn't important. What's important is the Edition of SQL Server you're using. In the June CTP you need to use Standard, Developer or Enterprise. Express doesn't have Reporting Services.

I need to know how to deploy reports without Visual Studio

I have reviewed the available documentation and was unable to locate a
method to deploy reports outside of Visual Studio without instantiating
the web service. We have a package we would like to bundle with some
reports.
Does anyone know an easy way to deploy reports using an .rdl file?The only way to deploy reports is through the web service. (VS uses the web
service) Look for the method CreateReport. You can always use rs.exe and
create a simple script to do the deployment.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kelly Gross" <kgross@.htp-inc.com> wrote in message
news:1103233970.824910.306870@.f14g2000cwb.googlegroups.com...
>I have reviewed the available documentation and was unable to locate a
> method to deploy reports outside of Visual Studio without instantiating
> the web service. We have a package we would like to bundle with some
> reports.
> Does anyone know an easy way to deploy reports using an .rdl file?
>|||The rs utility requires administrator permission on the target machine.
We are using an msi install which runs in the context of the local
system account. Do we have sufficient permission to run the rs utility?|||Non admins can run rs, but there is an issue on W2k that prevents this. XP
and W2k should be fine. However the local system account is an admin so you
should not see this issue (although I have not tried it and can't be 100%
sure). The problem is not a permission issue, but something with the
framework.
The user will need permission within RS. As long as local admins have
permission to publish to RS then you should have no issues.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kelly Gross" <kgross@.htp-inc.com> wrote in message
news:1103235966.097594.118890@.f14g2000cwb.googlegroups.com...
> The rs utility requires administrator permission on the target machine.
> We are using an msi install which runs in the context of the local
> system account. Do we have sufficient permission to run the rs utility?
>|||Why not use the upload file buttom of the Report Manager
(http://yourreportsite/reports) to do the deployment?
a) Upload the rdl;
b) Create the db connection manually again within the Report Manager.
Tao
"Daniel Reib [MSFT]" wrote:
> Non admins can run rs, but there is an issue on W2k that prevents this. XP
> and W2k should be fine. However the local system account is an admin so you
> should not see this issue (although I have not tried it and can't be 100%
> sure). The problem is not a permission issue, but something with the
> framework.
> The user will need permission within RS. As long as local admins have
> permission to publish to RS then you should have no issues.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Kelly Gross" <kgross@.htp-inc.com> wrote in message
> news:1103235966.097594.118890@.f14g2000cwb.googlegroups.com...
> > The rs utility requires administrator permission on the target machine.
> > We are using an msi install which runs in the context of the local
> > system account. Do we have sufficient permission to run the rs utility?
> >
>
>

I need SQL Server Management Studio!

Hi,

I have just completely installed MS SQL Server 2005 Express Edition in MS Server 2003.
After installation, I only have the Configuration Manager, Error and Usage Reporting and Surface Area Configuration, listed under Configuration Details.

SQL Server Management Studio is not accessible anywhere.
I also do not have the folder C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell in which the SQL Server Management Studio is installed.
What must I do to get this component installed?

In this article http://msdn2.microsoft.com/en-us/library/ms143219.aspx, it is also written in step 8 that there should be a "Components to Install" page. I have done the installations and uninstallations a few times, but I still do not have "Components to Install" page. In fact, after step 7, I am brought to the "Features Selection" page.

What can I do to solve this problem? Please help.

Thanks.

You'll need to download the SQL Server Express Edition Toolkit. Get it at the following page:

http://msdn2.microsoft.com/en-gb/express/bb410792.aspx

HTH!

|||Thanks, it has helped a lot!