Create a new maintenance plan

I have installed SQLServer 2005 from Small Business Server premium edition. The package has been upgraded to SP2 and all the fixes have been applied.

I want to create a new maintenance plan to automate my DB backup procedures using SQL Server Management Studio. I start Management Studio and right click on the management folder and I I see is "Refresh" there is no "Create New Maintenance" plan presented.

I have tried this using the sa built-in account and the server administrator account both of which have sysadmin priviledges. I have also tried it from a client machine with another sysadmin userid but they all give the same results.

I checked to see if SSIS was installed. The add/remove programs wizard for SQL Server shows Integration Services SP2 is installed but I went ahead anyway and re-installed without error.

It seems that either SSIS is not running or I my user roles are preventing the maintenance wizard from running. Is there some way that I can tell if, in fact, SSIS is properly installed and running?

Thanks.

To create or manage maintenance plans, you must be a member of the sysadmin fixed server role. Note that Object Explorer only displays maintenance plans if the user is a member of the sysadmin fixed server role.

See http://technet.microsoft.com/en-us/library/ms189036(SQL.90).aspxsql

Create a new maintenance plan

I have installed SQLServer 2005 from Small Business Server premium edition. The package has been upgraded to SP2 and all the fixes have been applied.

I want to create a new maintenance plan to automate my DB backup procedures using SQL Server Management Studio. I start Management Studio and right click on the management folder and I I see is "Refresh" there is no "Create New Maintenance" plan presented.

I have tried this using the sa built-in account and the server administrator account both of which have sysadmin priviledges. I have also tried it from a client machine with another sysadmin userid but they all give the same results.

I checked to see if SSIS was installed. The add/remove programs wizard for SQL Server shows Integration Services SP2 is installed but I went ahead anyway and re-installed without error.

It seems that either SSIS is not running or I my user roles are preventing the maintenance wizard from running. Is there some way that I can tell if, in fact, SSIS is properly installed and running?

Thanks.

To create or manage maintenance plans, you must be a member of the sysadmin fixed server role. Note that Object Explorer only displays maintenance plans if the user is a member of the sysadmin fixed server role.

See http://technet.microsoft.com/en-us/library/ms189036(SQL.90).aspx

CPU usage changed after upgrading to MS SQL Server 2000 from MS SQL Server 6.5

Hi All,

I had two Window NT 4.0 Server, say A and B... We recently upgraded from MS SQL Server 6.5 to MS SQL Server 2000 on Computer A, and for Computer B, I installed SQL Server 2000 from scratch. These two have a same RAM(512MB) and CPU speed...so the setup for hardware is almost identical...

For server A, there are around 10 connection to this SQL Server...
For server B, there are also around 10 connection to this SQL Server...

The database for SQL Server B is a copy from SQL Server A... I restored the database on SQL Server B from the backup dump of Server A...

We have a store procedure called usp_GetMemo(ID Interger) ... we feed ID number to this store procedure and return its return Description... this table has ID column and it's Non-Cluster index, allow the duplicate row... there are 1.7 million records for this table...

Here is problem... whenever i execute more than 100 times for this store procedure(usp_GetMemo(ID)) continuously, the CPU usage of Computer A is 3 times bigger than computer B...

I did run the command "DBCC DBReindex" , "Update Statistics" for this table on computer A, but did not make any difference...

Keep in mind that Computer A has been upgraded to SQL server 2000 while Computer B installed from scratch... would that make difference somehow?? Before the upgrading, the CPU usage of computer A is the same as Computer B...

Any help will be really appreciated,
Thanks alot,
Kim,while mentioning the fact that computer A was upgraded, you didn't say anything about the compatibility level of the database on that computer.|||Hi,

Thanks alot for your reply.. the compatibility level for SQL 2000 is 80

Kim,|||you're VERY welcome.

have you checked the execution plan for both databases?|||i'm still baby in SQL 2000, would you please tell me where can i checked the execution plan ?

Thanks,
KIM,|||open query analyzer (2 sessions) and press ctrl-k in both prior to executing the procedure. one of the panes will be called "Execution Plan." there you can see the difference, if any. also right-mouse click anywhere on the command pane of the query window, go to connection options, and set io and cpu statistics on.|||I did not have a chance to look at Execute plan and CPU stats because my boss give me a new task, he want to know whether necessary to run "Update Statistics" after run "DBCC DBreindex". Does "DBCC DBReindex" automaticlly update stats? You mind to answer this question for me... thanks alot...|||updating statistics is part of what dbreindex does, so no need to do it again.

CPU spike with SQL Server 2005 Express Edition

Hello,
Any help is greatly appreciated.
I've recently upgraded my MSDE 2000 to SQL Server 2005 Express Edition
on my Windows 2003 server SP1. I've noticted that running a single one
table query against the new sql engine causes the sqlservr.exe process
to consume 98% of CPU on the server. The spike usually lasts for a
20-30 seconds and I can't figure out why. The query is a simple select
query against one table with only 3000 records.
The server is a one proc machine 2.0 GHz celeron, with 1 GB of RAM.
Again, any suggestions are appreciated.
Thank you.support@.holylandmarket.com wrote:
> Hello,
> Any help is greatly appreciated.
> I've recently upgraded my MSDE 2000 to SQL Server 2005 Express Edition
> on my Windows 2003 server SP1. I've noticted that running a single one
> table query against the new sql engine causes the sqlservr.exe process
> to consume 98% of CPU on the server. The spike usually lasts for a
> 20-30 seconds and I can't figure out why. The query is a simple select
> query against one table with only 3000 records.
> The server is a one proc machine 2.0 GHz celeron, with 1 GB of RAM.
> Again, any suggestions are appreciated.
> Thank you.
>
What does the execution plan for the query look like? What is the
heaviest part of the plan?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||It looks like the index scan is the heaviest part of the plan. The
problem is I get high CPU spikes regardless of the query I run.
Again the db is very small with very little data in it. I have the
same db running on Windows XP and I don't see any CPU spikes.
Isam
Thanks.
Tracy McKibben wrote:
> support@.holylandmarket.com wrote:
> What does the execution plan for the query look like? What is the
> heaviest part of the plan?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Isam wrote:
> It looks like the index scan is the heaviest part of the plan. The
> problem is I get high CPU spikes regardless of the query I run.
> Again the db is very small with very little data in it. I have the
> same db running on Windows XP and I don't see any CPU spikes.
>
You say the spike lasts for 20-30 seconds - how long does the query run?
Do you have auto-close enabled on this database?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||The query comes back in sub second but the process is still pegged.
Auto Close is set to True on the db.
Isam
Tracy McKibben wrote:
> Isam wrote:
> You say the spike lasts for 20-30 seconds - how long does the query run?
> Do you have auto-close enabled on this database?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Isam wrote:
> The query comes back in sub second but the process is still pegged.
> Auto Close is set to True on the db.
>
Turn that off and see if the spike goes away...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I can still see it spike. Turning auto close off did not change much.
Isam
support@.holylandmarket.com wrote:
> Hello,
> Any help is greatly appreciated.
> I've recently upgraded my MSDE 2000 to SQL Server 2005 Express Edition
> on my Windows 2003 server SP1. I've noticted that running a single one
> table query against the new sql engine causes the sqlservr.exe process
> to consume 98% of CPU on the server. The spike usually lasts for a
> 20-30 seconds and I can't figure out why. The query is a simple select
> query against one table with only 3000 records.
> The server is a one proc machine 2.0 GHz celeron, with 1 GB of RAM.
> Again, any suggestions are appreciated.
> Thank you.

CPU spike with SQL Server 2005 Express Edition

Hello,

Any help is greatly appreciated.

I've recently upgraded my MSDE 2000 to SQL Server 2005 Express Edition
on my Windows 2003 server SP1. I've noticted that running a single one

table query against the new sql engine causes the sqlservr.exe process
to consume 98% of CPU on the server. The spike usually lasts for a
20-30 seconds and I can't figure out why. The query is a simple select

query against one table with only 3000 records.

The server is a one proc machine 2.0 GHz celeron, with 1 GB of RAM.

Again, any suggestions are appreciated.

Thank you.Isam wrote:

Quote:

Originally Posted by

Hello,
>
Any help is greatly appreciated.
>
>
I've recently upgraded my MSDE 2000 to SQL Server 2005 Express Edition
on my Windows 2003 server SP1. I've noticted that running a single one
>
table query against the new sql engine causes the sqlservr.exe process
to consume 98% of CPU on the server. The spike usually lasts for a
20-30 seconds and I can't figure out why. The query is a simple select
>
query against one table with only 3000 records.
>
>
The server is a one proc machine 2.0 GHz celeron, with 1 GB of RAM.
>
>
Again, any suggestions are appreciated.
>
>
Thank you.

Oddly, the first rule of computer trouble shooting would seem to apply
here: When in doubt, turn off the Microsoft product. :P

Seriously, does this happen even after restarting SQL and/or the
server?

That's about it for me. I don't know enough to really trouble shoot
this, but I can point out the obvious with the best of them. ;)

--Richard|||Isam (support@.holylandmarket.com) writes:

Quote:

Originally Posted by

I've recently upgraded my MSDE 2000 to SQL Server 2005 Express Edition
on my Windows 2003 server SP1. I've noticted that running a single one
table query against the new sql engine causes the sqlservr.exe process
to consume 98% of CPU on the server. The spike usually lasts for a
20-30 seconds and I can't figure out why. The query is a simple select
query against one table with only 3000 records.

Does the query run for 20-30 seconds, or does it complete long before
the CPU spike is over?

Care to post the query and the definition for the underlying table?

By the way, did you run UPDATE STATISTICS on all tables after the upgrade?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

CPU spike with SQL Server 2005 Express Edition

Hello,
Any help is greatly appreciated.
I've recently upgraded my MSDE 2000 to SQL Server 2005 Express Edition
on my Windows 2003 server SP1. I've noticted that running a single one
table query against the new sql engine causes the sqlservr.exe process
to consume 98% of CPU on the server. The spike usually lasts for a
20-30 seconds and I can't figure out why. The query is a simple select
query against one table with only 3000 records.
The server is a one proc machine 2.0 GHz celeron, with 1 GB of RAM.
Again, any suggestions are appreciated.
Thank you.support@.holylandmarket.com wrote:
> Hello,
> Any help is greatly appreciated.
> I've recently upgraded my MSDE 2000 to SQL Server 2005 Express Edition
> on my Windows 2003 server SP1. I've noticted that running a single one
> table query against the new sql engine causes the sqlservr.exe process
> to consume 98% of CPU on the server. The spike usually lasts for a
> 20-30 seconds and I can't figure out why. The query is a simple select
> query against one table with only 3000 records.
> The server is a one proc machine 2.0 GHz celeron, with 1 GB of RAM.
> Again, any suggestions are appreciated.
> Thank you.
>
What does the execution plan for the query look like? What is the
heaviest part of the plan?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||It looks like the index scan is the heaviest part of the plan. The
problem is I get high CPU spikes regardless of the query I run.
Again the db is very small with very little data in it. I have the
same db running on Windows XP and I don't see any CPU spikes.
Isam
Thanks.
Tracy McKibben wrote:
> support@.holylandmarket.com wrote:
> > Hello,
> >
> > Any help is greatly appreciated.
> >
> > I've recently upgraded my MSDE 2000 to SQL Server 2005 Express Edition
> > on my Windows 2003 server SP1. I've noticted that running a single one
> > table query against the new sql engine causes the sqlservr.exe process
> > to consume 98% of CPU on the server. The spike usually lasts for a
> > 20-30 seconds and I can't figure out why. The query is a simple select
> > query against one table with only 3000 records.
> >
> > The server is a one proc machine 2.0 GHz celeron, with 1 GB of RAM.
> >
> > Again, any suggestions are appreciated.
> >
> > Thank you.
> >
> What does the execution plan for the query look like? What is the
> heaviest part of the plan?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Isam wrote:
> It looks like the index scan is the heaviest part of the plan. The
> problem is I get high CPU spikes regardless of the query I run.
> Again the db is very small with very little data in it. I have the
> same db running on Windows XP and I don't see any CPU spikes.
>
You say the spike lasts for 20-30 seconds - how long does the query run?
Do you have auto-close enabled on this database?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||The query comes back in sub second but the process is still pegged.
Auto Close is set to True on the db.
Isam
Tracy McKibben wrote:
> Isam wrote:
> > It looks like the index scan is the heaviest part of the plan. The
> > problem is I get high CPU spikes regardless of the query I run.
> >
> > Again the db is very small with very little data in it. I have the
> > same db running on Windows XP and I don't see any CPU spikes.
> >
> You say the spike lasts for 20-30 seconds - how long does the query run?
> Do you have auto-close enabled on this database?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Isam wrote:
> The query comes back in sub second but the process is still pegged.
> Auto Close is set to True on the db.
>
Turn that off and see if the spike goes away...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I can still see it spike. Turning auto close off did not change much.
Isam
support@.holylandmarket.com wrote:
> Hello,
> Any help is greatly appreciated.
> I've recently upgraded my MSDE 2000 to SQL Server 2005 Express Edition
> on my Windows 2003 server SP1. I've noticted that running a single one
> table query against the new sql engine causes the sqlservr.exe process
> to consume 98% of CPU on the server. The spike usually lasts for a
> 20-30 seconds and I can't figure out why. The query is a simple select
> query against one table with only 3000 records.
> The server is a one proc machine 2.0 GHz celeron, with 1 GB of RAM.
> Again, any suggestions are appreciated.
> Thank you.