Ho to Execute a Scheduled Job from VB6

Hi ,
I have an schedueled job which runs a DTS .
This job is executing everyday at 2:00 Pm and it transfers data from oracle to the SQl server.
In some cases data in Oracle is not ready at 2:00 , sometimes it is ready at 11:00 AM so what I need is to run that Scheduled job from an exe file .
I can write a code to run the DTS jobs but the problem in this case is I will lose the History Information I have with my schedueled job.So I need to run The Scheduled job to kep the history .
I researched the Internet and all I get is how to create a job but I dont have a clue how to run it .
I'm using Visual basic 6 .
Thanx .Have you looked at...

sp_add_jobschedule|||Thanx for you reply
sp_add_jobs is creating a new job. I dont wan to creat e a new job because the reason for this application is simply keeping track of this Job's history. Otherwise I could run the DTS itself .|||OK, lets try again...

Have you looked at...

sp_add_jobschedule|||I can use sp_update_jobschedule and change the data parameters of job and let it run .
But can't I really run a Job from visual basic ?|||OK...look at

EXECUTE msdb..sp_start_job|||PERFECT !
Thanx .

History rows

I have job history for other jobs in the same server
Perhaps those other jobs execute so frequently so that you get to 1000 rows between executions of
this job?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"mecn" <mecn2002@.yahoo.com> wrote in message news:e1lCD$EGGHA.2652@.tk2msftngp13.phx.gbl...
>I have job history for other jobs in the same server
>

History rows

I have job history for other jobs in the same serverPerhaps those other jobs execute so frequently so that you get to 1000 rows between executions of
this job?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"mecn" <mecn2002@.yahoo.com> wrote in message news:e1lCD$EGGHA.2652@.tk2msftngp13.phx.gbl...
>I have job history for other jobs in the same server
>sql

History rows

I have job history for other jobs in the same serverPerhaps those other jobs execute so frequently so that you get to 1000 rows
between executions of
this job?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"mecn" <mecn2002@.yahoo.com> wrote in message news:e1lCD$EGGHA.2652@.tk2msftngp13.phx.gbl...[v
bcol=seagreen]
>I have job history for other jobs in the same server
>[/vbcol]

History of a SQL Job Deletion

Does anyone know if there is a record kept of a deleted SQL Agent Job? I
need to track down why a job is going away but I haven't been able to
figure out how to do it.
Thanks!
--
Posted via http://dbforums.comThe SQL Jobs are all stored within the msdb database. If you have been
backing up msdb you should be able to get the job back
--
HTH
Ryan Waight, MCDBA, MCSE
"MrSanity" <member40366@.dbforums.com> wrote in message
news:3390116.1063975524@.dbforums.com...
> Does anyone know if there is a record kept of a deleted SQL Agent Job? I
> need to track down why a job is going away but I haven't been able to
> figure out how to do it.
>
> Thanks!
>
> --
> Posted via http://dbforums.com|||Are you saying that some of your jobs just Disappear ?
If yes then have a look at the job properties. On the Notifications
tab there is a "Automatically delete this job - When it
fails/Succeeds/completes"
Do you have that checked ?
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org|||There is no record kept of deleted jobs in SQL Server. If your msdb database
uses either the full or bulk-logged recovery model and you have backups of
the transaction logs, you can use LogExplorer from www.lumigent.com to do
research.
If a job disappears on a regular basis, make sure that you don't have the
'automatically delete job' property set. You can find it on the
Notifications tab of the job properties in Enterprise Manager.
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"MrSanity" <member40366@.dbforums.com> wrote in message
news:3390116.1063975524@.dbforums.com...
> Does anyone know if there is a record kept of a deleted SQL Agent Job? I
> need to track down why a job is going away but I haven't been able to
> figure out how to do it.
>
> Thanks!
>
> --
> Posted via http://dbforums.comsql

History Limit

I use SQL Server Management Studio and when I View History for a Job there
seem to be a 100 line limit. Some of my jobs have more than 100 steps so
some don't show. Is there a place you can increase that limit a bit?
Thanks
John
Yes -
Check properties of SQL Server Agent and then go to History.
There you can play with your settings.
Enjoy
Immy
"John Holt" <johnh@.regionv.k12.mn.us> wrote in message
news:uAdCacj4GHA.512@.TK2MSFTNGP06.phx.gbl...
>I use SQL Server Management Studio and when I View History for a Job there
>seem to be a 100 line limit. Some of my jobs have more than 100 steps so
>some don't show. Is there a place you can increase that limit a bit?
> Thanks
> John
>
|||John Holt wrote:
> I use SQL Server Management Studio and when I View History for a Job there
> seem to be a 100 line limit. Some of my jobs have more than 100 steps so
> some don't show. Is there a place you can increase that limit a bit?
> Thanks
> John
>
Right-click on SQL Server Agent, choose Properties, go to the History tab...
Tracy McKibben
MCDBA
http://www.realsqlguy.com

History Limit

I use SQL Server Management Studio and when I View History for a Job there
seem to be a 100 line limit. Some of my jobs have more than 100 steps so
some don't show. Is there a place you can increase that limit a bit?
Thanks
JohnYes -
Check properties of SQL Server Agent and then go to History.
There you can play with your settings.
Enjoy
Immy
"John Holt" <johnh@.regionv.k12.mn.us> wrote in message
news:uAdCacj4GHA.512@.TK2MSFTNGP06.phx.gbl...
>I use SQL Server Management Studio and when I View History for a Job there
>seem to be a 100 line limit. Some of my jobs have more than 100 steps so
>some don't show. Is there a place you can increase that limit a bit?
> Thanks
> John
>|||John Holt wrote:
> I use SQL Server Management Studio and when I View History for a Job there
> seem to be a 100 line limit. Some of my jobs have more than 100 steps so
> some don't show. Is there a place you can increase that limit a bit?
> Thanks
> John
>
Right-click on SQL Server Agent, choose Properties, go to the History tab...
Tracy McKibben
MCDBA
http://www.realsqlguy.com

History Limit

I use SQL Server Management Studio and when I View History for a Job there
seem to be a 100 line limit. Some of my jobs have more than 100 steps so
some don't show. Is there a place you can increase that limit a bit?
Thanks
JohnYes -
Check properties of SQL Server Agent and then go to History.
There you can play with your settings.
Enjoy
Immy
"John Holt" <johnh@.regionv.k12.mn.us> wrote in message
news:uAdCacj4GHA.512@.TK2MSFTNGP06.phx.gbl...
>I use SQL Server Management Studio and when I View History for a Job there
>seem to be a 100 line limit. Some of my jobs have more than 100 steps so
>some don't show. Is there a place you can increase that limit a bit?
> Thanks
> John
>|||John Holt wrote:
> I use SQL Server Management Studio and when I View History for a Job there
> seem to be a 100 line limit. Some of my jobs have more than 100 steps so
> some don't show. Is there a place you can increase that limit a bit?
> Thanks
> John
>
Right-click on SQL Server Agent, choose Properties, go to the History tab...
Tracy McKibben
MCDBA
http://www.realsqlguy.com

High fragmentation with 0 rows in the table

I have a job in SQL 7 with sp3 that goes in a loop and inserts about 2 to 6
rows into an empty table and then deletes them and keeps going in the loop
for about 6 hours. If the table is not truncated before the job starts again
the following night, the job performance degrades considerably. It turns out
that after the job is done running, there are hundreds of data pages
allocated for this table with high extent fragmentation.
My question is why these pages are not de-allocated when there are no rows
in the table to begin with? If I truncate the table and add 6 rows manually,
then only one data page is allocated to the table. The table has about 6
columns with 4 columns of data type smallint, one decimal and a couple of
char(5).
Hi,
There could be several reasons. Are you commiting your transactions after
each loop? Also, the delete statement is a logged transaction, that means
everytime your script deletes the data, it is written in the transaction log
that causes space (data pages) to be allocated. Not sure how your script is
coded, so can't really be too specific.
The truncate statement is NOT logged so when you issue it, it wipes out the
table clean. That is why you are seeing this behaviour.
HTH
DeeJay Puar
MCDBA
"Adam" wrote:

> I have a job in SQL 7 with sp3 that goes in a loop and inserts about 2 to 6
> rows into an empty table and then deletes them and keeps going in the loop
> for about 6 hours. If the table is not truncated before the job starts again
> the following night, the job performance degrades considerably. It turns out
> that after the job is done running, there are hundreds of data pages
> allocated for this table with high extent fragmentation.
> My question is why these pages are not de-allocated when there are no rows
> in the table to begin with? If I truncate the table and add 6 rows manually,
> then only one data page is allocated to the table. The table has about 6
> columns with 4 columns of data type smallint, one decimal and a couple of
> char(5).
>
|||do you have any indexes on this table, specificaly a clustered one, or is
this a heap ?
and how is the "delete" done, with a 'delete tablename' ?
"Adam" <Adam@.discussions.microsoft.com> wrote in message
news:58830C32-198D-4DFF-9149-DBA76F8FB444@.microsoft.com...
>I have a job in SQL 7 with sp3 that goes in a loop and inserts about 2 to 6
> rows into an empty table and then deletes them and keeps going in the loop
> for about 6 hours. If the table is not truncated before the job starts
> again
> the following night, the job performance degrades considerably. It turns
> out
> that after the job is done running, there are hundreds of data pages
> allocated for this table with high extent fragmentation.
> My question is why these pages are not de-allocated when there are no rows
> in the table to begin with? If I truncate the table and add 6 rows
> manually,
> then only one data page is allocated to the table. The table has about 6
> columns with 4 columns of data type smallint, one decimal and a couple of
> char(5).
>
|||On Tue, 13 Dec 2005 15:12:23 -0500, "David J. Cartwright"
<davidcartwright@.hotmail.com> wrote:
>do you have any indexes on this table, specificaly a clustered one, or is
>this a heap ?
>and how is the "delete" done, with a 'delete tablename' ?
What he said.
Would a simple "truncate table" be a quick fix?
(not that it's so hard to defrag anyway)
J.
|||I am not 100% if the app is doing a commit after each delete but even if that
was not the case, after the final commit at the end of the 6th hour,
shouldn't the empty pages get deallocated?
"DeeJay Puar" wrote:
[vbcol=seagreen]
> Hi,
> There could be several reasons. Are you commiting your transactions after
> each loop? Also, the delete statement is a logged transaction, that means
> everytime your script deletes the data, it is written in the transaction log
> that causes space (data pages) to be allocated. Not sure how your script is
> coded, so can't really be too specific.
> The truncate statement is NOT logged so when you issue it, it wipes out the
> table clean. That is why you are seeing this behaviour.
> HTH
> DeeJay Puar
> MCDBA
> "Adam" wrote:
|||At the moment, there are no indexes and yes, my next plan of attack is to put
a clustered index to see how that pans out. I don't know for sure if that
will solve the problem 100% because now, the delete would also have to do
more IOs to maintain the clustered index. Also, a table with the clustered
index will get fragmented over time.
The SQL is doing "delete table".
"David J. Cartwright" wrote:

> do you have any indexes on this table, specificaly a clustered one, or is
> this a heap ?
> and how is the "delete" done, with a 'delete tablename' ?
> "Adam" <Adam@.discussions.microsoft.com> wrote in message
> news:58830C32-198D-4DFF-9149-DBA76F8FB444@.microsoft.com...
>
>
|||The current fix in fact is to truncate the table before the batch starts,
however, if SQL Server is accumulating empty pages with 0 rows in the table
then the performance starts to degrade over time while the 6 hour job is
running. If I can figure out the reason and the solution to why SQL Server is
not deallocating empty pages, then I can stop it from taking the time for
doing table scan through these empty pages and can tweek many other nightly
jobs that I am sure are facing a similar situation.
thoughts?
"jxstern" wrote:

> On Tue, 13 Dec 2005 15:12:23 -0500, "David J. Cartwright"
> <davidcartwright@.hotmail.com> wrote:
> What he said.
> Would a simple "truncate table" be a quick fix?
> (not that it's so hard to defrag anyway)
> J.
>
>
|||On Thu, 15 Dec 2005 06:21:02 -0800, "Adam"
<Adam@.discussions.microsoft.com> wrote:
>At the moment, there are no indexes and yes, my next plan of attack is to put
>a clustered index to see how that pans out.
It should.
If you delete from a table without a clustered index, SQLServer leaves
the pages deallocated until ... The Ghost wakes up and reclaims them,
which it does when it feels like it, and then it takes as long as it
takes.
Fun, huh?
Add the clustered index, if for no other reason than this.
Google the newsgroup archives or the web for more about the ghost,
there's very little about it in MSDN or BOL.
Josh

High fragmentation with 0 rows in the table

I have a job in SQL 7 with sp3 that goes in a loop and inserts about 2 to 6
rows into an empty table and then deletes them and keeps going in the loop
for about 6 hours. If the table is not truncated before the job starts again
the following night, the job performance degrades considerably. It turns out
that after the job is done running, there are hundreds of data pages
allocated for this table with high extent fragmentation.
My question is why these pages are not de-allocated when there are no rows
in the table to begin with? If I truncate the table and add 6 rows manually,
then only one data page is allocated to the table. The table has about 6
columns with 4 columns of data type smallint, one decimal and a couple of
char(5).Hi,
There could be several reasons. Are you commiting your transactions after
each loop? Also, the delete statement is a logged transaction, that means
everytime your script deletes the data, it is written in the transaction log
that causes space (data pages) to be allocated. Not sure how your script is
coded, so can't really be too specific.
The truncate statement is NOT logged so when you issue it, it wipes out the
table clean. That is why you are seeing this behaviour.
HTH
DeeJay Puar
MCDBA
"Adam" wrote:
> I have a job in SQL 7 with sp3 that goes in a loop and inserts about 2 to 6
> rows into an empty table and then deletes them and keeps going in the loop
> for about 6 hours. If the table is not truncated before the job starts again
> the following night, the job performance degrades considerably. It turns out
> that after the job is done running, there are hundreds of data pages
> allocated for this table with high extent fragmentation.
> My question is why these pages are not de-allocated when there are no rows
> in the table to begin with? If I truncate the table and add 6 rows manually,
> then only one data page is allocated to the table. The table has about 6
> columns with 4 columns of data type smallint, one decimal and a couple of
> char(5).
>|||do you have any indexes on this table, specificaly a clustered one, or is
this a heap ?
and how is the "delete" done, with a 'delete tablename' ?
"Adam" <Adam@.discussions.microsoft.com> wrote in message
news:58830C32-198D-4DFF-9149-DBA76F8FB444@.microsoft.com...
>I have a job in SQL 7 with sp3 that goes in a loop and inserts about 2 to 6
> rows into an empty table and then deletes them and keeps going in the loop
> for about 6 hours. If the table is not truncated before the job starts
> again
> the following night, the job performance degrades considerably. It turns
> out
> that after the job is done running, there are hundreds of data pages
> allocated for this table with high extent fragmentation.
> My question is why these pages are not de-allocated when there are no rows
> in the table to begin with? If I truncate the table and add 6 rows
> manually,
> then only one data page is allocated to the table. The table has about 6
> columns with 4 columns of data type smallint, one decimal and a couple of
> char(5).
>|||On Tue, 13 Dec 2005 15:12:23 -0500, "David J. Cartwright"
<davidcartwright@.hotmail.com> wrote:
>do you have any indexes on this table, specificaly a clustered one, or is
>this a heap ?
>and how is the "delete" done, with a 'delete tablename' ?
What he said.
Would a simple "truncate table" be a quick fix?
(not that it's so hard to defrag anyway)
J.|||I am not 100% if the app is doing a commit after each delete but even if that
was not the case, after the final commit at the end of the 6th hour,
shouldn't the empty pages get deallocated?
"DeeJay Puar" wrote:
> Hi,
> There could be several reasons. Are you commiting your transactions after
> each loop? Also, the delete statement is a logged transaction, that means
> everytime your script deletes the data, it is written in the transaction log
> that causes space (data pages) to be allocated. Not sure how your script is
> coded, so can't really be too specific.
> The truncate statement is NOT logged so when you issue it, it wipes out the
> table clean. That is why you are seeing this behaviour.
> HTH
> DeeJay Puar
> MCDBA
> "Adam" wrote:
> > I have a job in SQL 7 with sp3 that goes in a loop and inserts about 2 to 6
> > rows into an empty table and then deletes them and keeps going in the loop
> > for about 6 hours. If the table is not truncated before the job starts again
> > the following night, the job performance degrades considerably. It turns out
> > that after the job is done running, there are hundreds of data pages
> > allocated for this table with high extent fragmentation.
> >
> > My question is why these pages are not de-allocated when there are no rows
> > in the table to begin with? If I truncate the table and add 6 rows manually,
> > then only one data page is allocated to the table. The table has about 6
> > columns with 4 columns of data type smallint, one decimal and a couple of
> > char(5).
> >|||At the moment, there are no indexes and yes, my next plan of attack is to put
a clustered index to see how that pans out. I don't know for sure if that
will solve the problem 100% because now, the delete would also have to do
more IOs to maintain the clustered index. Also, a table with the clustered
index will get fragmented over time.
The SQL is doing "delete table".
"David J. Cartwright" wrote:
> do you have any indexes on this table, specificaly a clustered one, or is
> this a heap ?
> and how is the "delete" done, with a 'delete tablename' ?
> "Adam" <Adam@.discussions.microsoft.com> wrote in message
> news:58830C32-198D-4DFF-9149-DBA76F8FB444@.microsoft.com...
> >I have a job in SQL 7 with sp3 that goes in a loop and inserts about 2 to 6
> > rows into an empty table and then deletes them and keeps going in the loop
> > for about 6 hours. If the table is not truncated before the job starts
> > again
> > the following night, the job performance degrades considerably. It turns
> > out
> > that after the job is done running, there are hundreds of data pages
> > allocated for this table with high extent fragmentation.
> >
> > My question is why these pages are not de-allocated when there are no rows
> > in the table to begin with? If I truncate the table and add 6 rows
> > manually,
> > then only one data page is allocated to the table. The table has about 6
> > columns with 4 columns of data type smallint, one decimal and a couple of
> > char(5).
> >
>
>|||The current fix in fact is to truncate the table before the batch starts,
however, if SQL Server is accumulating empty pages with 0 rows in the table
then the performance starts to degrade over time while the 6 hour job is
running. If I can figure out the reason and the solution to why SQL Server is
not deallocating empty pages, then I can stop it from taking the time for
doing table scan through these empty pages and can tweek many other nightly
jobs that I am sure are facing a similar situation.
thoughts?
"jxstern" wrote:
> On Tue, 13 Dec 2005 15:12:23 -0500, "David J. Cartwright"
> <davidcartwright@.hotmail.com> wrote:
> >do you have any indexes on this table, specificaly a clustered one, or is
> >this a heap ?
> >and how is the "delete" done, with a 'delete tablename' ?
> What he said.
> Would a simple "truncate table" be a quick fix?
> (not that it's so hard to defrag anyway)
> J.
>
>|||On Thu, 15 Dec 2005 06:21:02 -0800, "Adam"
<Adam@.discussions.microsoft.com> wrote:
>At the moment, there are no indexes and yes, my next plan of attack is to put
>a clustered index to see how that pans out.
It should.
If you delete from a table without a clustered index, SQLServer leaves
the pages deallocated until ... The Ghost wakes up and reclaims them,
which it does when it feels like it, and then it takes as long as it
takes.
Fun, huh?
Add the clustered index, if for no other reason than this.
Google the newsgroup archives or the web for more about the ghost,
there's very little about it in MSDN or BOL.
Josh

High fragmentation with 0 rows in the table

I have a job in SQL 7 with sp3 that goes in a loop and inserts about 2 to 6
rows into an empty table and then deletes them and keeps going in the loop
for about 6 hours. If the table is not truncated before the job starts again
the following night, the job performance degrades considerably. It turns out
that after the job is done running, there are hundreds of data pages
allocated for this table with high extent fragmentation.
My question is why these pages are not de-allocated when there are no rows
in the table to begin with? If I truncate the table and add 6 rows manually,
then only one data page is allocated to the table. The table has about 6
columns with 4 columns of data type smallint, one decimal and a couple of
char(5).Hi,
There could be several reasons. Are you commiting your transactions after
each loop? Also, the delete statement is a logged transaction, that means
everytime your script deletes the data, it is written in the transaction log
that causes space (data pages) to be allocated. Not sure how your script is
coded, so can't really be too specific.
The truncate statement is NOT logged so when you issue it, it wipes out the
table clean. That is why you are seeing this behaviour.
HTH
DeeJay Puar
MCDBA
"Adam" wrote:

> I have a job in SQL 7 with sp3 that goes in a loop and inserts about 2 to
6
> rows into an empty table and then deletes them and keeps going in the loop
> for about 6 hours. If the table is not truncated before the job starts aga
in
> the following night, the job performance degrades considerably. It turns o
ut
> that after the job is done running, there are hundreds of data pages
> allocated for this table with high extent fragmentation.
> My question is why these pages are not de-allocated when there are no rows
> in the table to begin with? If I truncate the table and add 6 rows manuall
y,
> then only one data page is allocated to the table. The table has about 6
> columns with 4 columns of data type smallint, one decimal and a couple of
> char(5).
>|||do you have any indexes on this table, specificaly a clustered one, or is
this a heap ?
and how is the "delete" done, with a 'delete tablename' ?
"Adam" <Adam@.discussions.microsoft.com> wrote in message
news:58830C32-198D-4DFF-9149-DBA76F8FB444@.microsoft.com...
>I have a job in SQL 7 with sp3 that goes in a loop and inserts about 2 to 6
> rows into an empty table and then deletes them and keeps going in the loop
> for about 6 hours. If the table is not truncated before the job starts
> again
> the following night, the job performance degrades considerably. It turns
> out
> that after the job is done running, there are hundreds of data pages
> allocated for this table with high extent fragmentation.
> My question is why these pages are not de-allocated when there are no rows
> in the table to begin with? If I truncate the table and add 6 rows
> manually,
> then only one data page is allocated to the table. The table has about 6
> columns with 4 columns of data type smallint, one decimal and a couple of
> char(5).
>|||On Tue, 13 Dec 2005 15:12:23 -0500, "David J. Cartwright"
<davidcartwright@.hotmail.com> wrote:
>do you have any indexes on this table, specificaly a clustered one, or is
>this a heap ?
>and how is the "delete" done, with a 'delete tablename' ?
What he said.
Would a simple "truncate table" be a quick fix?
(not that it's so hard to defrag anyway)
J.|||I am not 100% if the app is doing a commit after each delete but even if tha
t
was not the case, after the final commit at the end of the 6th hour,
shouldn't the empty pages get deallocated?
"DeeJay Puar" wrote:
[vbcol=seagreen]
> Hi,
> There could be several reasons. Are you commiting your transactions after
> each loop? Also, the delete statement is a logged transaction, that means
> everytime your script deletes the data, it is written in the transaction l
og
> that causes space (data pages) to be allocated. Not sure how your script i
s
> coded, so can't really be too specific.
> The truncate statement is NOT logged so when you issue it, it wipes out th
e
> table clean. That is why you are seeing this behaviour.
> HTH
> DeeJay Puar
> MCDBA
> "Adam" wrote:
>|||At the moment, there are no indexes and yes, my next plan of attack is to pu
t
a clustered index to see how that pans out. I don't know for sure if that
will solve the problem 100% because now, the delete would also have to do
more IOs to maintain the clustered index. Also, a table with the clustered
index will get fragmented over time.
The SQL is doing "delete table".
"David J. Cartwright" wrote:

> do you have any indexes on this table, specificaly a clustered one, or is
> this a heap ?
> and how is the "delete" done, with a 'delete tablename' ?
> "Adam" <Adam@.discussions.microsoft.com> wrote in message
> news:58830C32-198D-4DFF-9149-DBA76F8FB444@.microsoft.com...
>
>|||The current fix in fact is to truncate the table before the batch starts,
however, if SQL Server is accumulating empty pages with 0 rows in the table
then the performance starts to degrade over time while the 6 hour job is
running. If I can figure out the reason and the solution to why SQL Server i
s
not deallocating empty pages, then I can stop it from taking the time for
doing table scan through these empty pages and can tweek many other nightly
jobs that I am sure are facing a similar situation.
thoughts?
"jxstern" wrote:

> On Tue, 13 Dec 2005 15:12:23 -0500, "David J. Cartwright"
> <davidcartwright@.hotmail.com> wrote:
> What he said.
> Would a simple "truncate table" be a quick fix?
> (not that it's so hard to defrag anyway)
> J.
>
>|||On Thu, 15 Dec 2005 06:21:02 -0800, "Adam"
<Adam@.discussions.microsoft.com> wrote:
>At the moment, there are no indexes and yes, my next plan of attack is to p
ut
>a clustered index to see how that pans out.
It should.
If you delete from a table without a clustered index, SQLServer leaves
the pages deallocated until ... The Ghost wakes up and reclaims them,
which it does when it feels like it, and then it takes as long as it
takes.
Fun, huh?
Add the clustered index, if for no other reason than this.
Google the newsgroup archives or the web for more about the ghost,
there's very little about it in MSDN or BOL.
Josh

High CPU usage after backup

We've noticed on our server that the CPU usage goes from its normal 60% to
90% during a backup job, and stays at 90% even after the job is complete.
The backup job takes 30 seconds to a minute to complete, but CPU usage stays
at 90% (of which the "sqlservr.exe" process is taking 70%). The backup job
updates the statistics and backs up the transaction log.
The "really" strange thing I've found, is that by just running Enterprise
Manager and accessing the Properties dialog of the server (I don't need to
toggle any settings), will cause CPU usage to go back down to it's normal
60%. This seems to work whether I'm doing this on the server via Remote
Desktop, or using my local Enterprise Manager to connect to the server
remotely.
Has anyone observed this occurring before?
We're running SQL Server 2000 Standard Edition SP3 (I know we need to update
this), on Windows Server 2003. Server is a Xeon 2.8 with 2gb ram.
In terms of database size, its at 1gb, with 4gb allocated. Transaction log
is at 2gb with 7gb allocated.Hi
I have not experienced this, but if this was on a SAN my guess would be a
replication issue/hardware bottleneck. Check out the perfmon counters and SAN
stats to see if there are problems.
Moving the latest service pack/patch level may be useful.
John
"Ken" wrote:
> We've noticed on our server that the CPU usage goes from its normal 60% to
> 90% during a backup job, and stays at 90% even after the job is complete.
> The backup job takes 30 seconds to a minute to complete, but CPU usage stays
> at 90% (of which the "sqlservr.exe" process is taking 70%). The backup job
> updates the statistics and backs up the transaction log.
> The "really" strange thing I've found, is that by just running Enterprise
> Manager and accessing the Properties dialog of the server (I don't need to
> toggle any settings), will cause CPU usage to go back down to it's normal
> 60%. This seems to work whether I'm doing this on the server via Remote
> Desktop, or using my local Enterprise Manager to connect to the server
> remotely.
> Has anyone observed this occurring before?
> We're running SQL Server 2000 Standard Edition SP3 (I know we need to update
> this), on Windows Server 2003. Server is a Xeon 2.8 with 2gb ram.
> In terms of database size, its at 1gb, with 4gb allocated. Transaction log
> is at 2gb with 7gb allocated.
>
>

High CPU usage after backup

We've noticed on our server that the CPU usage goes from its normal 60% to
90% during a backup job, and stays at 90% even after the job is complete.
The backup job takes 30 seconds to a minute to complete, but CPU usage stays
at 90% (of which the "sqlservr.exe" process is taking 70%). The backup job
updates the statistics and backs up the transaction log.
The "really" strange thing I've found, is that by just running Enterprise
Manager and accessing the Properties dialog of the server (I don't need to
toggle any settings), will cause CPU usage to go back down to it's normal
60%. This seems to work whether I'm doing this on the server via Remote
Desktop, or using my local Enterprise Manager to connect to the server
remotely.
Has anyone observed this occurring before?
We're running SQL Server 2000 Standard Edition SP3 (I know we need to update
this), on Windows Server 2003. Server is a Xeon 2.8 with 2gb ram.
In terms of database size, its at 1gb, with 4gb allocated. Transaction log
is at 2gb with 7gb allocated.Hi
I have not experienced this, but if this was on a SAN my guess would be a
replication issue/hardware bottleneck. Check out the perfmon counters and SA
N
stats to see if there are problems.
Moving the latest service pack/patch level may be useful.
John
"Ken" wrote:

> We've noticed on our server that the CPU usage goes from its normal 60% to
> 90% during a backup job, and stays at 90% even after the job is complete.
> The backup job takes 30 seconds to a minute to complete, but CPU usage sta
ys
> at 90% (of which the "sqlservr.exe" process is taking 70%). The backup jo
b
> updates the statistics and backs up the transaction log.
> The "really" strange thing I've found, is that by just running Enterprise
> Manager and accessing the Properties dialog of the server (I don't need to
> toggle any settings), will cause CPU usage to go back down to it's normal
> 60%. This seems to work whether I'm doing this on the server via Remote
> Desktop, or using my local Enterprise Manager to connect to the server
> remotely.
> Has anyone observed this occurring before?
> We're running SQL Server 2000 Standard Edition SP3 (I know we need to upda
te
> this), on Windows Server 2003. Server is a Xeon 2.8 with 2gb ram.
> In terms of database size, its at 1gb, with 4gb allocated. Transaction lo
g
> is at 2gb with 7gb allocated.
>
>

High CPU usage after backup

We've noticed on our server that the CPU usage goes from its normal 60% to
90% during a backup job, and stays at 90% even after the job is complete.
The backup job takes 30 seconds to a minute to complete, but CPU usage stays
at 90% (of which the "sqlservr.exe" process is taking 70%). The backup job
updates the statistics and backs up the transaction log.
The "really" strange thing I've found, is that by just running Enterprise
Manager and accessing the Properties dialog of the server (I don't need to
toggle any settings), will cause CPU usage to go back down to it's normal
60%. This seems to work whether I'm doing this on the server via Remote
Desktop, or using my local Enterprise Manager to connect to the server
remotely.
Has anyone observed this occurring before?
We're running SQL Server 2000 Standard Edition SP3 (I know we need to update
this), on Windows Server 2003. Server is a Xeon 2.8 with 2gb ram.
In terms of database size, its at 1gb, with 4gb allocated. Transaction log
is at 2gb with 7gb allocated.
Hi
I have not experienced this, but if this was on a SAN my guess would be a
replication issue/hardware bottleneck. Check out the perfmon counters and SAN
stats to see if there are problems.
Moving the latest service pack/patch level may be useful.
John
"Ken" wrote:

> We've noticed on our server that the CPU usage goes from its normal 60% to
> 90% during a backup job, and stays at 90% even after the job is complete.
> The backup job takes 30 seconds to a minute to complete, but CPU usage stays
> at 90% (of which the "sqlservr.exe" process is taking 70%). The backup job
> updates the statistics and backs up the transaction log.
> The "really" strange thing I've found, is that by just running Enterprise
> Manager and accessing the Properties dialog of the server (I don't need to
> toggle any settings), will cause CPU usage to go back down to it's normal
> 60%. This seems to work whether I'm doing this on the server via Remote
> Desktop, or using my local Enterprise Manager to connect to the server
> remotely.
> Has anyone observed this occurring before?
> We're running SQL Server 2000 Standard Edition SP3 (I know we need to update
> this), on Windows Server 2003. Server is a Xeon 2.8 with 2gb ram.
> In terms of database size, its at 1gb, with 4gb allocated. Transaction log
> is at 2gb with 7gb allocated.
>
>