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 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.
Zafer
Hi 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 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 need your migration stories!

Greetings:
I'm a lowly applications programmer (one of three IT employees total) in a
company that's about 40 personnel strong in management and 50 personnel
strong in production.
We have a shared drive that has about 81 Access databases in it (total of
1.25 Gigs of information) that have been made since Access 2.0 was available
all chucked full of custom software mostly using DAO for data access. All of
them are in Access 97 format. It's just a huge blob of interconnected
databases and my opinion is it's time for them to go!
So, I'm looking for migration stories. I know there are others out there who
have been in this situation and I'm wondering if you can comment a little
about your particular scenario. Was it cost effective in the end, was it
worth the hassle, tips & tricks, and how you finally managed to win your
management over to the idea of shilling out the dollars for a real DBMS.
Thanks!
CaseyYou may want to check out Russell Sinclair's book:
http://www.apress.com/book/bookDisplay.html?bID=74
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Casey" <clengacher@.smcky.com> wrote in message
news:e5i2W352DHA.1736@.TK2MSFTNGP09.phx.gbl...
Greetings:
I'm a lowly applications programmer (one of three IT employees total) in a
company that's about 40 personnel strong in management and 50 personnel
strong in production.
We have a shared drive that has about 81 Access databases in it (total of
1.25 Gigs of information) that have been made since Access 2.0 was available
all chucked full of custom software mostly using DAO for data access. All of
them are in Access 97 format. It's just a huge blob of interconnected
databases and my opinion is it's time for them to go!
So, I'm looking for migration stories. I know there are others out there who
have been in this situation and I'm wondering if you can comment a little
about your particular scenario. Was it cost effective in the end, was it
worth the hassle, tips & tricks, and how you finally managed to win your
management over to the idea of shilling out the dollars for a real DBMS.
Thanks!
Casey|||Management only hears the sound of money. Any changes you suggest must have
some impact on general productivity of the company or IT management costs.
Simplistically, if you can demonstrate that spending $20,000 on hardware and
$30,000 on development will increase productivity of 90 employees by 5%,
then there is a direct connection in the management's mind to the bottom
line and your suggested changes. If all you do is make snarky comments about
the in-efficiency of the system, then nobody will hear you. So you really
need to create a solid, justifiable business case for what you think is
obvious.
One approach is to break the problem into small enough chunks that
management is able to tolerate the risk. If you tell them you want to
consolidate the entire 81 databases into an integrated, seamless
productivity solution, it might scare the hell out of them and they'll shut
you down with little deliberation. But if you suggest a smaller prototype
project that takes a few of these databases and makes them more effecient,
then you have a proof-of-concept that can be applied to the whole mess. Once
you demonstrate some success, then the rest is easier to sell.
As an aside, I can't imagine any logical reason for 81 different databases
to manage an organization that size.And in Access, no less.
Oy!
Good luck, lowly applications programmer
Bob Castleman
SuccessWare Software
"Casey" <clengacher@.smcky.com> wrote in message
news:e5i2W352DHA.1736@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Greetings:
> I'm a lowly applications programmer (one of three IT employees total) in a
> company that's about 40 personnel strong in management and 50 personnel
> strong in production.
> We have a shared drive that has about 81 Access databases in it (total of
> 1.25 Gigs of information) that have been made since Access 2.0 was

---

available

quote:

---

> all chucked full of custom software mostly using DAO for data access. All

---

of

quote:

---

> them are in Access 97 format. It's just a huge blob of interconnected
> databases and my opinion is it's time for them to go!
> So, I'm looking for migration stories. I know there are others out there

---

who

quote:

---

> have been in this situation and I'm wondering if you can comment a little
> about your particular scenario. Was it cost effective in the end, was it
> worth the hassle, tips & tricks, and how you finally managed to win your
> management over to the idea of shilling out the dollars for a real DBMS.
> Thanks!
> Casey
>

---

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!