Dear All,
I have made a replication between Server A and B. It work well
One date, the customer need to renew all data in one table. So I run a
script to delete the data then try to import such data from another outside
server. I used Sql server enterprise tool to import data,I make it
successful. But after runed the replication. I found the data have been
deleted in the table in Server B. The customer complain me that they can not
retrieve data from Server B. It make me sad.
So I just want to ask whethe the Sql server enterprise tool import
function can not triger to replication?
Regards
Jackson Chan
You don't mention the replication type, but my guess is that it is merge. If
so, if you bulk insert the rows and choose the defaults, then FIRE_TRIGGERS
is false and consequently the rows are not added to MSmerge_contents. In
this case, you need to run sp_addtabletocontents to include the rows then
resynchronise.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Showing posts with label run. Show all posts
Showing posts with label run. Show all posts
Tuesday, March 27, 2012
Thursday, March 22, 2012
Delete 1 month old records
I need to set up a job which will run the last day of every month to
keep the data in the table for the current month only and delete
everyhthing else (older than 1 month records would be deleted). see the
sample case:
create table #t (a datetime)
insert into #t values (getdate())
insert into #t values ('9/1/2005')
insert into #t values ('8/20/2005')
insert into #t values ('8/8/2005')
insert into #t values ('7/3/2005')
insert into #t values ('6/1/2005')
insert into #t values ('4/1/2004')
delete from #t
where a < DATEADD(MONTH,-1,CURRENT_TIMESTAMP)
My final result in this case would be only two records (9/2005).
Eeverything else should be deleted. My DELETE stament is not working bc
of the time poartion I guess. Can you correct this SQL?
Thanks for your help.
*** Sent via Developersdex http://www.examnotes.net ***delete from #t
where a < cast(month(current_timestamp) as varchar) + '-1-' +
cast(year(current_timestamp) as varchar)
--Brian
(Please reply to the newsgroups only.)
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:O90arSyrFHA.1204@.TK2MSFTNGP15.phx.gbl...
>I need to set up a job which will run the last day of every month to
> keep the data in the table for the current month only and delete
> everyhthing else (older than 1 month records would be deleted). see the
> sample case:
> create table #t (a datetime)
> insert into #t values (getdate())
> insert into #t values ('9/1/2005')
> insert into #t values ('8/20/2005')
> insert into #t values ('8/8/2005')
> insert into #t values ('7/3/2005')
> insert into #t values ('6/1/2005')
> insert into #t values ('4/1/2004')
>
> delete from #t
> where a < DATEADD(MONTH,-1,CURRENT_TIMESTAMP)
> My final result in this case would be only two records (9/2005).
> Eeverything else should be deleted. My DELETE stament is not working bc
> of the time poartion I guess. Can you correct this SQL?
> Thanks for your help.
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Try:
delete from #t
where a < convert (char (8), DATEADD(MONTH,-1,CURRENT_TIMESTAMP) , 112)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:O90arSyrFHA.1204@.TK2MSFTNGP15.phx.gbl...
I need to set up a job which will run the last day of every month to
keep the data in the table for the current month only and delete
everyhthing else (older than 1 month records would be deleted). see the
sample case:
create table #t (a datetime)
insert into #t values (getdate())
insert into #t values ('9/1/2005')
insert into #t values ('8/20/2005')
insert into #t values ('8/8/2005')
insert into #t values ('7/3/2005')
insert into #t values ('6/1/2005')
insert into #t values ('4/1/2004')
delete from #t
where a < DATEADD(MONTH,-1,CURRENT_TIMESTAMP)
My final result in this case would be only two records (9/2005).
Eeverything else should be deleted. My DELETE stament is not working bc
of the time poartion I guess. Can you correct this SQL?
Thanks for your help.
*** Sent via Developersdex http://www.examnotes.net ***|||Try,
delete #t
where a < cast(convert(varchar(6), getdate(), 112) + '01' as datetime)
AMB
"Test Test" wrote:
> I need to set up a job which will run the last day of every month to
> keep the data in the table for the current month only and delete
> everyhthing else (older than 1 month records would be deleted). see the
> sample case:
> create table #t (a datetime)
> insert into #t values (getdate())
> insert into #t values ('9/1/2005')
> insert into #t values ('8/20/2005')
> insert into #t values ('8/8/2005')
> insert into #t values ('7/3/2005')
> insert into #t values ('6/1/2005')
> insert into #t values ('4/1/2004')
>
> delete from #t
where a < DATEADD(MONTH,-1,CURRENT_TIMESTAMP)
> My final result in this case would be only two records (9/2005).
> Eeverything else should be deleted. My DELETE stament is not working bc
> of the time poartion I guess. Can you correct this SQL?
> Thanks for your help.
>
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Oops, gotta chop off that month part:
delete from #t
where a < convert (char (6), DATEADD(MONTH,-1,CURRENT_TIMESTAMP) , 112) +
'01'
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23dwJ3cyrFHA.3440@.TK2MSFTNGP10.phx.gbl...
Try:
delete from #t
where a < convert (char (8), DATEADD(MONTH,-1,CURRENT_TIMESTAMP) , 112)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:O90arSyrFHA.1204@.TK2MSFTNGP15.phx.gbl...
I need to set up a job which will run the last day of every month to
keep the data in the table for the current month only and delete
everyhthing else (older than 1 month records would be deleted). see the
sample case:
create table #t (a datetime)
insert into #t values (getdate())
insert into #t values ('9/1/2005')
insert into #t values ('8/20/2005')
insert into #t values ('8/8/2005')
insert into #t values ('7/3/2005')
insert into #t values ('6/1/2005')
insert into #t values ('4/1/2004')
delete from #t
where a < DATEADD(MONTH,-1,CURRENT_TIMESTAMP)
My final result in this case would be only two records (9/2005).
Eeverything else should be deleted. My DELETE stament is not working bc
of the time poartion I guess. Can you correct this SQL?
Thanks for your help.
*** Sent via Developersdex http://www.examnotes.net ***|||It works! Thanks for all your help!
*** Sent via Developersdex http://www.examnotes.net ***
keep the data in the table for the current month only and delete
everyhthing else (older than 1 month records would be deleted). see the
sample case:
create table #t (a datetime)
insert into #t values (getdate())
insert into #t values ('9/1/2005')
insert into #t values ('8/20/2005')
insert into #t values ('8/8/2005')
insert into #t values ('7/3/2005')
insert into #t values ('6/1/2005')
insert into #t values ('4/1/2004')
delete from #t
where a < DATEADD(MONTH,-1,CURRENT_TIMESTAMP)
My final result in this case would be only two records (9/2005).
Eeverything else should be deleted. My DELETE stament is not working bc
of the time poartion I guess. Can you correct this SQL?
Thanks for your help.
*** Sent via Developersdex http://www.examnotes.net ***delete from #t
where a < cast(month(current_timestamp) as varchar) + '-1-' +
cast(year(current_timestamp) as varchar)
--Brian
(Please reply to the newsgroups only.)
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:O90arSyrFHA.1204@.TK2MSFTNGP15.phx.gbl...
>I need to set up a job which will run the last day of every month to
> keep the data in the table for the current month only and delete
> everyhthing else (older than 1 month records would be deleted). see the
> sample case:
> create table #t (a datetime)
> insert into #t values (getdate())
> insert into #t values ('9/1/2005')
> insert into #t values ('8/20/2005')
> insert into #t values ('8/8/2005')
> insert into #t values ('7/3/2005')
> insert into #t values ('6/1/2005')
> insert into #t values ('4/1/2004')
>
> delete from #t
> where a < DATEADD(MONTH,-1,CURRENT_TIMESTAMP)
> My final result in this case would be only two records (9/2005).
> Eeverything else should be deleted. My DELETE stament is not working bc
> of the time poartion I guess. Can you correct this SQL?
> Thanks for your help.
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Try:
delete from #t
where a < convert (char (8), DATEADD(MONTH,-1,CURRENT_TIMESTAMP) , 112)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:O90arSyrFHA.1204@.TK2MSFTNGP15.phx.gbl...
I need to set up a job which will run the last day of every month to
keep the data in the table for the current month only and delete
everyhthing else (older than 1 month records would be deleted). see the
sample case:
create table #t (a datetime)
insert into #t values (getdate())
insert into #t values ('9/1/2005')
insert into #t values ('8/20/2005')
insert into #t values ('8/8/2005')
insert into #t values ('7/3/2005')
insert into #t values ('6/1/2005')
insert into #t values ('4/1/2004')
delete from #t
where a < DATEADD(MONTH,-1,CURRENT_TIMESTAMP)
My final result in this case would be only two records (9/2005).
Eeverything else should be deleted. My DELETE stament is not working bc
of the time poartion I guess. Can you correct this SQL?
Thanks for your help.
*** Sent via Developersdex http://www.examnotes.net ***|||Try,
delete #t
where a < cast(convert(varchar(6), getdate(), 112) + '01' as datetime)
AMB
"Test Test" wrote:
> I need to set up a job which will run the last day of every month to
> keep the data in the table for the current month only and delete
> everyhthing else (older than 1 month records would be deleted). see the
> sample case:
> create table #t (a datetime)
> insert into #t values (getdate())
> insert into #t values ('9/1/2005')
> insert into #t values ('8/20/2005')
> insert into #t values ('8/8/2005')
> insert into #t values ('7/3/2005')
> insert into #t values ('6/1/2005')
> insert into #t values ('4/1/2004')
>
> delete from #t
where a < DATEADD(MONTH,-1,CURRENT_TIMESTAMP)
> My final result in this case would be only two records (9/2005).
> Eeverything else should be deleted. My DELETE stament is not working bc
> of the time poartion I guess. Can you correct this SQL?
> Thanks for your help.
>
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Oops, gotta chop off that month part:
delete from #t
where a < convert (char (6), DATEADD(MONTH,-1,CURRENT_TIMESTAMP) , 112) +
'01'
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23dwJ3cyrFHA.3440@.TK2MSFTNGP10.phx.gbl...
Try:
delete from #t
where a < convert (char (8), DATEADD(MONTH,-1,CURRENT_TIMESTAMP) , 112)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:O90arSyrFHA.1204@.TK2MSFTNGP15.phx.gbl...
I need to set up a job which will run the last day of every month to
keep the data in the table for the current month only and delete
everyhthing else (older than 1 month records would be deleted). see the
sample case:
create table #t (a datetime)
insert into #t values (getdate())
insert into #t values ('9/1/2005')
insert into #t values ('8/20/2005')
insert into #t values ('8/8/2005')
insert into #t values ('7/3/2005')
insert into #t values ('6/1/2005')
insert into #t values ('4/1/2004')
delete from #t
where a < DATEADD(MONTH,-1,CURRENT_TIMESTAMP)
My final result in this case would be only two records (9/2005).
Eeverything else should be deleted. My DELETE stament is not working bc
of the time poartion I guess. Can you correct this SQL?
Thanks for your help.
*** Sent via Developersdex http://www.examnotes.net ***|||It works! Thanks for all your help!
*** Sent via Developersdex http://www.examnotes.net ***
Delete + Log file
A) When i run a delete against a table that has 50 million records based
upon a where clause , what entries does the T log file hold ? Does it log 50
million delete statements along with 50 million inserts just incase it needs
to rollback.
B) Also if there is a clustered index on the coulimn thats part of the where
clause, what does the Tlog file hold ?
C) If there was a clustered index but not part of the column in the where
clause, what does the Tlog contain ?
D) During the time the delete is occuring, does it go ahead and start
deleting entries from the data pages in the data files or does it first log
entries in the Log file and then deletes ?
E) Finally if i did a backup log during the time the delete is occuring (
Say i noticed that the table with (nolock) option was decrementing ) and
then restored the logs on another database , will part of the deletes be
reflected on the other database if i specify the (nolock) option since the
delete did not complete
I am just trying to understand what entries the TLog contains.. I would
appreciate if you could provide answers to all the 5 parts. I am using SQL
2000
Thank youIf you want to see what is in the tran log you can run
this before you do a log backup:
select * from ::fn_dblog(null,null)
I will attempt to answer your questions
a) It logs 50 million delete statements (if you need to
delete everything out of a table and don't need the
ability to rollback, you might use truncate table as it is
faster because it is minimally logged)
b)I don't believe it matters if there is a clustered index
or not in regards to the T-log
c)I don't believe it matters if there is a clustered index
or not in regards to the T-log
d) Everything hits the log first, it hits the data when
the T-log does a checkpoint
e)That one I'm not sure on, would have to test that one or
maybe someone has tried this before that reads these
newsgroups, I would guess that if you tried backing up the
log during this transaction, it would wait until the tran
was finished, selecting from it with a nolog would only
show you, not the T-log, the data while in the transaction.
Again, not sure on that one.
HTH
Ray Higdon MCSE, MCDBA, CCNA
>--Original Message--
>A) When i run a delete against a table that has 50
million records based
>upon a where clause , what entries does the T log file
hold ? Does it log 50
>million delete statements along with 50 million inserts
just incase it needs
>to rollback.
>
>B) Also if there is a clustered index on the coulimn
thats part of the where
>clause, what does the Tlog file hold ?
>C) If there was a clustered index but not part of the
column in the where
>clause, what does the Tlog contain ?
>D) During the time the delete is occuring, does it go
ahead and start
>deleting entries from the data pages in the data files or
does it first log
>entries in the Log file and then deletes ?
>E) Finally if i did a backup log during the time the
delete is occuring (
>Say i noticed that the table with (nolock) option was
decrementing ) and
>then restored the logs on another database , will part of
the deletes be
>reflected on the other database if i specify the (nolock)
option since the
>delete did not complete
>I am just trying to understand what entries the TLog
contains.. I would
>appreciate if you could provide answers to all the 5
parts. I am using SQL
>2000
>Thank you
>
>.
>|||--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it community
of SQL Server professionals.
www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OuJAVQ#VDHA.392@.TK2MSFTNGP11.phx.gbl...
> A) When i run a delete against a table that has 50 million records based
> upon a where clause , what entries does the T log file hold ? Does it log
50
> million delete statements along with 50 million inserts just incase it
needs
> to rollback.
>
The log holds a copy of the record which was deleted...
> B) Also if there is a clustered index on the coulimn thats part of the
where
> clause, what does the Tlog file hold ?
NO change, the log has the copy of the deleted record.
> C) If there was a clustered index but not part of the column in the where
> clause, what does the Tlog contain ?
no change.
> D) During the time the delete is occuring, does it go ahead and start
> deleting entries from the data pages in the data files or does it first
log
> entries in the Log file and then deletes ?
>
Logging occurs first.
> E) Finally if i did a backup log during the time the delete is occuring (
> Say i noticed that the table with (nolock) option was decrementing ) and
> then restored the logs on another database , will part of the deletes be
> reflected on the other database if i specify the (nolock) option since the
> delete did not complete
>
After the restore, and recovery has run either all of the records will be
present or none of them...Each statement is a transaction.
> I am just trying to understand what entries the TLog contains.. I would
> appreciate if you could provide answers to all the 5 parts. I am using SQL
> 2000
> Thank you
>
>|||Thanks so to answer 5, where you said
"After the restore, and recovery has run either all of the records will be
present or none of them...Each statement is a transaction."
What if i restored log with standby mode so users can read from this standby
database, will i see some deletes in effect with (nolock)
Thanks once again to you all
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:eTPtkTCWDHA.2008@.TK2MSFTNGP11.phx.gbl...
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it
community
> of SQL Server professionals.
> www.sqlpass.org
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:OuJAVQ#VDHA.392@.TK2MSFTNGP11.phx.gbl...
> > A) When i run a delete against a table that has 50 million records based
> > upon a where clause , what entries does the T log file hold ? Does it
log
> 50
> > million delete statements along with 50 million inserts just incase it
> needs
> > to rollback.
> >
> The log holds a copy of the record which was deleted...
> >
> > B) Also if there is a clustered index on the coulimn thats part of the
> where
> > clause, what does the Tlog file hold ?
> NO change, the log has the copy of the deleted record.
>
> >
> > C) If there was a clustered index but not part of the column in the
where
> > clause, what does the Tlog contain ?
> no change.
> >
> > D) During the time the delete is occuring, does it go ahead and start
> > deleting entries from the data pages in the data files or does it first
> log
> > entries in the Log file and then deletes ?
> >
> Logging occurs first.
> > E) Finally if i did a backup log during the time the delete is occuring
(
> > Say i noticed that the table with (nolock) option was decrementing ) and
> > then restored the logs on another database , will part of the deletes be
> > reflected on the other database if i specify the (nolock) option since
the
> > delete did not complete
> >
> After the restore, and recovery has run either all of the records will be
> present or none of them...Each statement is a transaction.
> > I am just trying to understand what entries the TLog contains.. I would
> > appreciate if you could provide answers to all the 5 parts. I am using
SQL
> > 2000
> >
> > Thank you
> >
> >
> >
>sql
upon a where clause , what entries does the T log file hold ? Does it log 50
million delete statements along with 50 million inserts just incase it needs
to rollback.
B) Also if there is a clustered index on the coulimn thats part of the where
clause, what does the Tlog file hold ?
C) If there was a clustered index but not part of the column in the where
clause, what does the Tlog contain ?
D) During the time the delete is occuring, does it go ahead and start
deleting entries from the data pages in the data files or does it first log
entries in the Log file and then deletes ?
E) Finally if i did a backup log during the time the delete is occuring (
Say i noticed that the table with (nolock) option was decrementing ) and
then restored the logs on another database , will part of the deletes be
reflected on the other database if i specify the (nolock) option since the
delete did not complete
I am just trying to understand what entries the TLog contains.. I would
appreciate if you could provide answers to all the 5 parts. I am using SQL
2000
Thank youIf you want to see what is in the tran log you can run
this before you do a log backup:
select * from ::fn_dblog(null,null)
I will attempt to answer your questions
a) It logs 50 million delete statements (if you need to
delete everything out of a table and don't need the
ability to rollback, you might use truncate table as it is
faster because it is minimally logged)
b)I don't believe it matters if there is a clustered index
or not in regards to the T-log
c)I don't believe it matters if there is a clustered index
or not in regards to the T-log
d) Everything hits the log first, it hits the data when
the T-log does a checkpoint
e)That one I'm not sure on, would have to test that one or
maybe someone has tried this before that reads these
newsgroups, I would guess that if you tried backing up the
log during this transaction, it would wait until the tran
was finished, selecting from it with a nolog would only
show you, not the T-log, the data while in the transaction.
Again, not sure on that one.
HTH
Ray Higdon MCSE, MCDBA, CCNA
>--Original Message--
>A) When i run a delete against a table that has 50
million records based
>upon a where clause , what entries does the T log file
hold ? Does it log 50
>million delete statements along with 50 million inserts
just incase it needs
>to rollback.
>
>B) Also if there is a clustered index on the coulimn
thats part of the where
>clause, what does the Tlog file hold ?
>C) If there was a clustered index but not part of the
column in the where
>clause, what does the Tlog contain ?
>D) During the time the delete is occuring, does it go
ahead and start
>deleting entries from the data pages in the data files or
does it first log
>entries in the Log file and then deletes ?
>E) Finally if i did a backup log during the time the
delete is occuring (
>Say i noticed that the table with (nolock) option was
decrementing ) and
>then restored the logs on another database , will part of
the deletes be
>reflected on the other database if i specify the (nolock)
option since the
>delete did not complete
>I am just trying to understand what entries the TLog
contains.. I would
>appreciate if you could provide answers to all the 5
parts. I am using SQL
>2000
>Thank you
>
>.
>|||--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it community
of SQL Server professionals.
www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OuJAVQ#VDHA.392@.TK2MSFTNGP11.phx.gbl...
> A) When i run a delete against a table that has 50 million records based
> upon a where clause , what entries does the T log file hold ? Does it log
50
> million delete statements along with 50 million inserts just incase it
needs
> to rollback.
>
The log holds a copy of the record which was deleted...
> B) Also if there is a clustered index on the coulimn thats part of the
where
> clause, what does the Tlog file hold ?
NO change, the log has the copy of the deleted record.
> C) If there was a clustered index but not part of the column in the where
> clause, what does the Tlog contain ?
no change.
> D) During the time the delete is occuring, does it go ahead and start
> deleting entries from the data pages in the data files or does it first
log
> entries in the Log file and then deletes ?
>
Logging occurs first.
> E) Finally if i did a backup log during the time the delete is occuring (
> Say i noticed that the table with (nolock) option was decrementing ) and
> then restored the logs on another database , will part of the deletes be
> reflected on the other database if i specify the (nolock) option since the
> delete did not complete
>
After the restore, and recovery has run either all of the records will be
present or none of them...Each statement is a transaction.
> I am just trying to understand what entries the TLog contains.. I would
> appreciate if you could provide answers to all the 5 parts. I am using SQL
> 2000
> Thank you
>
>|||Thanks so to answer 5, where you said
"After the restore, and recovery has run either all of the records will be
present or none of them...Each statement is a transaction."
What if i restored log with standby mode so users can read from this standby
database, will i see some deletes in effect with (nolock)
Thanks once again to you all
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:eTPtkTCWDHA.2008@.TK2MSFTNGP11.phx.gbl...
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it
community
> of SQL Server professionals.
> www.sqlpass.org
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:OuJAVQ#VDHA.392@.TK2MSFTNGP11.phx.gbl...
> > A) When i run a delete against a table that has 50 million records based
> > upon a where clause , what entries does the T log file hold ? Does it
log
> 50
> > million delete statements along with 50 million inserts just incase it
> needs
> > to rollback.
> >
> The log holds a copy of the record which was deleted...
> >
> > B) Also if there is a clustered index on the coulimn thats part of the
> where
> > clause, what does the Tlog file hold ?
> NO change, the log has the copy of the deleted record.
>
> >
> > C) If there was a clustered index but not part of the column in the
where
> > clause, what does the Tlog contain ?
> no change.
> >
> > D) During the time the delete is occuring, does it go ahead and start
> > deleting entries from the data pages in the data files or does it first
> log
> > entries in the Log file and then deletes ?
> >
> Logging occurs first.
> > E) Finally if i did a backup log during the time the delete is occuring
(
> > Say i noticed that the table with (nolock) option was decrementing ) and
> > then restored the logs on another database , will part of the deletes be
> > reflected on the other database if i specify the (nolock) option since
the
> > delete did not complete
> >
> After the restore, and recovery has run either all of the records will be
> present or none of them...Each statement is a transaction.
> > I am just trying to understand what entries the TLog contains.. I would
> > appreciate if you could provide answers to all the 5 parts. I am using
SQL
> > 2000
> >
> > Thank you
> >
> >
> >
>sql
Delay with XP
Hi all,
Desperately hoping you can help, we run pentagon2000 on an
SQL Server, which runs fine on W2K PC's, but on XP
Workstations, when the Pentagon 2000 Software data is
being added or modified from a station, and it sends the
data to the SQL Server the status states in Task manager
as "Not Responding" while sending the data and waits for
the response from the SQL Server to continue its
processes. The issue I have is that although a delay of a
few seconds is acceptable and expected, on the XP
WStations it can be 20-30 seconds, in extreme cases even
longer. this is making the system unusable. Is there
anything obvious, or even obscure anyone knows of that
might be causing this.
Thanks
Ian
Ian Broome wrote:
> Hi all,
> Desperately hoping you can help, we run pentagon2000 on an
> SQL Server, which runs fine on W2K PC's, but on XP
> Workstations, when the Pentagon 2000 Software data is
> being added or modified from a station, and it sends the
> data to the SQL Server the status states in Task manager
> as "Not Responding" while sending the data and waits for
> the response from the SQL Server to continue its
> processes. The issue I have is that although a delay of a
> few seconds is acceptable and expected, on the XP
> WStations it can be 20-30 seconds, in extreme cases even
> longer. this is making the system unusable. Is there
> anything obvious, or even obscure anyone knows of that
> might be causing this.
> Thanks
> Ian
What network library are you using to connect to SQL Server? If TCP/IP
try using the IP address instead of the server name. Or try setting up a
Connection Alias on the client for both named pipes and TCP/IP and see
if that makes a difference.
Also, make sure you have the correct MDAC installed on the WinXP
clients.
The "Not Responding" does not mean the application has necessarily
stopped responding. It could either be waiting for a response from SQL
Server as you mentioned or it could be so busy processing, it won't let
the OS know that that it is alive.
Have you tried running some queries from Query Analyzer from an XP
client to see if the response you get (Select the Show Client Statistics
option) is the same as a Win 2K client.
David G.
|||[vbcol=seagreen]
>--Original Message--
>Ian Broome wrote:
an[vbcol=seagreen]
a
>What network library are you using to connect to SQL
Server? If TCP/IP
>try using the IP address instead of the server name. Or
try setting up a
>Connection Alias on the client for both named pipes and
TCP/IP and see
>if that makes a difference.
>Also, make sure you have the correct MDAC installed on
the WinXP
>clients.
>The "Not Responding" does not mean the application has
necessarily
>stopped responding. It could either be waiting for a
response from SQL
>Server as you mentioned or it could be so busy
processing, it won't let
>the OS know that that it is alive.
>Have you tried running some queries from Query Analyzer
from an XP
>client to see if the response you get (Select the Show
Client Statistics
>option) is the same as a Win 2K client.
>
>--
>David G.
>.
>
Thanks David,
Ok first off, im afraid im not SQL friendly at all, we are
a network company brought in to resolve multiple issues
which we have but this one remains. In response to your
queries:
We are using TCP/IP, with regard to using the IP rather
than the server name, im assuming you mean within the CFG
file in BDE administrator? after that you lost me im
afriad regarding connection alias.
re: the correct MDAC installed for XP, do you know what it
should be? Ive applied SP3 for SQL and SP1 for XP.
re: Query Analyzer, ive looked this up but it seems to be
a tool I have to purchase and then run queries from,
unfortunately above my head, unless you know of an
alternative way of testing this.
Thankyou for your reply, much appreciated.
Ian
|||Ian wrote:
> Thanks David,
> Ok first off, im afraid im not SQL friendly at all, we are
> a network company brought in to resolve multiple issues
> which we have but this one remains. In response to your
> queries:
> We are using TCP/IP, with regard to using the IP rather
> than the server name, im assuming you mean within the CFG
> file in BDE administrator? after that you lost me im
> afriad regarding connection alias.
> re: the correct MDAC installed for XP, do you know what it
> should be? Ive applied SP3 for SQL and SP1 for XP.
> re: Query Analyzer, ive looked this up but it seems to be
> a tool I have to purchase and then run queries from,
> unfortunately above my head, unless you know of an
> alternative way of testing this.
> Thankyou for your reply, much appreciated.
> Ian
Query Analyzer is a client tool that comes with SQL Server. There is no
charge. If you have WinXP SP1 installed, you should be fine.
It sounds like you need a DBA to do some performance testing to see
where the problem is.
David G.
Desperately hoping you can help, we run pentagon2000 on an
SQL Server, which runs fine on W2K PC's, but on XP
Workstations, when the Pentagon 2000 Software data is
being added or modified from a station, and it sends the
data to the SQL Server the status states in Task manager
as "Not Responding" while sending the data and waits for
the response from the SQL Server to continue its
processes. The issue I have is that although a delay of a
few seconds is acceptable and expected, on the XP
WStations it can be 20-30 seconds, in extreme cases even
longer. this is making the system unusable. Is there
anything obvious, or even obscure anyone knows of that
might be causing this.
Thanks
Ian
Ian Broome wrote:
> Hi all,
> Desperately hoping you can help, we run pentagon2000 on an
> SQL Server, which runs fine on W2K PC's, but on XP
> Workstations, when the Pentagon 2000 Software data is
> being added or modified from a station, and it sends the
> data to the SQL Server the status states in Task manager
> as "Not Responding" while sending the data and waits for
> the response from the SQL Server to continue its
> processes. The issue I have is that although a delay of a
> few seconds is acceptable and expected, on the XP
> WStations it can be 20-30 seconds, in extreme cases even
> longer. this is making the system unusable. Is there
> anything obvious, or even obscure anyone knows of that
> might be causing this.
> Thanks
> Ian
What network library are you using to connect to SQL Server? If TCP/IP
try using the IP address instead of the server name. Or try setting up a
Connection Alias on the client for both named pipes and TCP/IP and see
if that makes a difference.
Also, make sure you have the correct MDAC installed on the WinXP
clients.
The "Not Responding" does not mean the application has necessarily
stopped responding. It could either be waiting for a response from SQL
Server as you mentioned or it could be so busy processing, it won't let
the OS know that that it is alive.
Have you tried running some queries from Query Analyzer from an XP
client to see if the response you get (Select the Show Client Statistics
option) is the same as a Win 2K client.
David G.
|||[vbcol=seagreen]
>--Original Message--
>Ian Broome wrote:
an[vbcol=seagreen]
a
>What network library are you using to connect to SQL
Server? If TCP/IP
>try using the IP address instead of the server name. Or
try setting up a
>Connection Alias on the client for both named pipes and
TCP/IP and see
>if that makes a difference.
>Also, make sure you have the correct MDAC installed on
the WinXP
>clients.
>The "Not Responding" does not mean the application has
necessarily
>stopped responding. It could either be waiting for a
response from SQL
>Server as you mentioned or it could be so busy
processing, it won't let
>the OS know that that it is alive.
>Have you tried running some queries from Query Analyzer
from an XP
>client to see if the response you get (Select the Show
Client Statistics
>option) is the same as a Win 2K client.
>
>--
>David G.
>.
>
Thanks David,
Ok first off, im afraid im not SQL friendly at all, we are
a network company brought in to resolve multiple issues
which we have but this one remains. In response to your
queries:
We are using TCP/IP, with regard to using the IP rather
than the server name, im assuming you mean within the CFG
file in BDE administrator? after that you lost me im
afriad regarding connection alias.
re: the correct MDAC installed for XP, do you know what it
should be? Ive applied SP3 for SQL and SP1 for XP.
re: Query Analyzer, ive looked this up but it seems to be
a tool I have to purchase and then run queries from,
unfortunately above my head, unless you know of an
alternative way of testing this.
Thankyou for your reply, much appreciated.
Ian
|||Ian wrote:
> Thanks David,
> Ok first off, im afraid im not SQL friendly at all, we are
> a network company brought in to resolve multiple issues
> which we have but this one remains. In response to your
> queries:
> We are using TCP/IP, with regard to using the IP rather
> than the server name, im assuming you mean within the CFG
> file in BDE administrator? after that you lost me im
> afriad regarding connection alias.
> re: the correct MDAC installed for XP, do you know what it
> should be? Ive applied SP3 for SQL and SP1 for XP.
> re: Query Analyzer, ive looked this up but it seems to be
> a tool I have to purchase and then run queries from,
> unfortunately above my head, unless you know of an
> alternative way of testing this.
> Thankyou for your reply, much appreciated.
> Ian
Query Analyzer is a client tool that comes with SQL Server. There is no
charge. If you have WinXP SP1 installed, you should be fine.
It sounds like you need a DBA to do some performance testing to see
where the problem is.
David G.
delay when running with DTexec.exe
Hi,
when I try to run my package with dtexec.exe, it starts fine but in the process it package calles another subpackage and at the time there is big delay before start processing the subpackage.
the subpackage has been setup so executionoutofprocess pramater has been set to true.
any idea what migth be the problem.
I have to metion even when I run this with in the visual studio still I have a big delay.
cheers|||
when I try to run my package with dtexec.exe, it starts fine but in the process it package calles another subpackage and at the time there is big delay before start processing the subpackage.
the subpackage has been setup so executionoutofprocess pramater has been set to true.
any idea what migth be the problem.
I have to metion even when I run this with in the visual studio still I have a big delay.
cheersWhat is a "big" delay? SSIS has to validate the package and its connections/metadata before executing.|||it takes normally between 30 sec to one min. for it to start the sub-package. is there any way to reduce this delay by pre-validation
Thanks|||Try setting DelayValidation = True on your connection managers, sources and destinations.|||
From another thread on this forum:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=739674&SiteID=1
Usually, executing using DTEXEC /F File.DTSX is the fastest way, but gives the mininum logging. Varios logging options for DTEXEC add functionality but may affect performance, although I've never seen 10x times slowdown because of it.
I have tested myself and I can tell the speed improves but just too litle.
|||Is there any way to do the validation once and then cache it. instead of SSIS vaidating it each time I run as it has got a big overhead.cheers|||
Kolf wrote:
Is there any way to do the validation once and then cache it. instead of SSIS vaidating it each time I run as it has got a big overhead.
cheers
Validation HAS to happen. And unless you set DelayValidation=TRUE a task will get validated when the package spins up and immediately pror to the task executing. There isn't alot of point in caching the results of the first Validation because that would negate the point of doing the second validation.
Does that answer the question?
-Jamie
Monday, March 19, 2012
Defragment disk
Hi
Is it acceptable practice to run Disk Defragmenter on a Disk when SQLServer
is installed and running?
(using Win2000, SQLServer 2000)
ThanksSure, as long as you exclude the SQL Server database files (perhaps the tool
will not even try on open files). If it is a dedicated SQL Server and you
don't overuse autogrow, then there shouldn't really be any need for this.
--
Tibor Karaszi
"GriffithsJ" <GriffithsJ_520@.hotmail.com> wrote in message
news:eQ0ykPfoDHA.1632@.TK2MSFTNGP10.phx.gbl...
> Hi
> Is it acceptable practice to run Disk Defragmenter on a Disk when
SQLServer
> is installed and running?
> (using Win2000, SQLServer 2000)
> Thanks
>|||How does one exclude these from the defragmenter?
I guess what I need to know is will anything bad happen if the data & log
files are defragmented? Is it best to switch off SQLServer first?|||> How does one exclude these from the defragmenter?
You have to check that with your defrag tool. If you are using the built-in
defrag tool, I suggest you check this in a Windows group.
> I guess what I need to know is will anything bad happen if the data & log
> files are defragmented? Is it best to switch off SQLServer first?
Probably, until you got info from the defrag tool vendor that the tool does
not touch locked files. SQL Server does not itself have a problem with the
files being defragged, but you don't want the risk of having two tools
working against the same file(s) at the same time.
--
Tibor Karaszi
"GriffithsJ" <GriffithsJ_520@.hotmail.com> wrote in message
news:OR6%23BSgoDHA.424@.TK2MSFTNGP10.phx.gbl...
> How does one exclude these from the defragmenter?
> I guess what I need to know is will anything bad happen if the data & log
> files are defragmented? Is it best to switch off SQLServer first?
>|||There is no requirement that SQL database devices be excluded from being
defragmented. There is also no requirement that SQL services first be
stopped prior to defragmenting. Microsoft's defrag APIs fully support
defragmenting SQL database devices online.
- Greg/Raxco Software
Microsoft MVP - Windows File System
Disclaimer: I work for Raxco Software, the maker of PerfectDisk - a
commercial defrag utility, as a systems engineer in the support department.
Want to email me? Delete ntloader.
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:Iqspb.32155$mU6.91387@.newsb.telia.net...
> > How does one exclude these from the defragmenter?
> You have to check that with your defrag tool. If you are using the
built-in
> defrag tool, I suggest you check this in a Windows group.
>
> > I guess what I need to know is will anything bad happen if the data &
log
> > files are defragmented? Is it best to switch off SQLServer first?
> Probably, until you got info from the defrag tool vendor that the tool
does
> not touch locked files. SQL Server does not itself have a problem with the
> files being defragged, but you don't want the risk of having two tools
> working against the same file(s) at the same time.
> --
> Tibor Karaszi
>
> "GriffithsJ" <GriffithsJ_520@.hotmail.com> wrote in message
> news:OR6%23BSgoDHA.424@.TK2MSFTNGP10.phx.gbl...
> > How does one exclude these from the defragmenter?
> >
> > I guess what I need to know is will anything bad happen if the data &
log
> > files are defragmented? Is it best to switch off SQLServer first?
> >
> >
>|||Thanks for the info, Greg! Good to know.
I was trying to get that kind of info from another defrag manufacturer, but
never got a response to my question. Hence my careful approach. :-)
--
Tibor Karaszi
"Greg Hayes/Raxco Software" <ghayesntloader@.raxco.com> wrote in message
news:%23EXhy9uoDHA.2216@.TK2MSFTNGP12.phx.gbl...
> There is no requirement that SQL database devices be excluded from being
> defragmented. There is also no requirement that SQL services first be
> stopped prior to defragmenting. Microsoft's defrag APIs fully support
> defragmenting SQL database devices online.
> - Greg/Raxco Software
> Microsoft MVP - Windows File System
> Disclaimer: I work for Raxco Software, the maker of PerfectDisk - a
> commercial defrag utility, as a systems engineer in the support
department.
> Want to email me? Delete ntloader.
>
> "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:Iqspb.32155$mU6.91387@.newsb.telia.net...
> > > How does one exclude these from the defragmenter?
> >
> > You have to check that with your defrag tool. If you are using the
> built-in
> > defrag tool, I suggest you check this in a Windows group.
> >
> >
> > > I guess what I need to know is will anything bad happen if the data &
> log
> > > files are defragmented? Is it best to switch off SQLServer first?
> >
> > Probably, until you got info from the defrag tool vendor that the tool
> does
> > not touch locked files. SQL Server does not itself have a problem with
the
> > files being defragged, but you don't want the risk of having two tools
> > working against the same file(s) at the same time.
> > --
> > Tibor Karaszi
> >
> >
> > "GriffithsJ" <GriffithsJ_520@.hotmail.com> wrote in message
> > news:OR6%23BSgoDHA.424@.TK2MSFTNGP10.phx.gbl...
> > > How does one exclude these from the defragmenter?
> > >
> > > I guess what I need to know is will anything bad happen if the data &
> log
> > > files are defragmented? Is it best to switch off SQLServer first?
> > >
> > >
> >
> >
>|||Thanks everyone...
Griff
Is it acceptable practice to run Disk Defragmenter on a Disk when SQLServer
is installed and running?
(using Win2000, SQLServer 2000)
ThanksSure, as long as you exclude the SQL Server database files (perhaps the tool
will not even try on open files). If it is a dedicated SQL Server and you
don't overuse autogrow, then there shouldn't really be any need for this.
--
Tibor Karaszi
"GriffithsJ" <GriffithsJ_520@.hotmail.com> wrote in message
news:eQ0ykPfoDHA.1632@.TK2MSFTNGP10.phx.gbl...
> Hi
> Is it acceptable practice to run Disk Defragmenter on a Disk when
SQLServer
> is installed and running?
> (using Win2000, SQLServer 2000)
> Thanks
>|||How does one exclude these from the defragmenter?
I guess what I need to know is will anything bad happen if the data & log
files are defragmented? Is it best to switch off SQLServer first?|||> How does one exclude these from the defragmenter?
You have to check that with your defrag tool. If you are using the built-in
defrag tool, I suggest you check this in a Windows group.
> I guess what I need to know is will anything bad happen if the data & log
> files are defragmented? Is it best to switch off SQLServer first?
Probably, until you got info from the defrag tool vendor that the tool does
not touch locked files. SQL Server does not itself have a problem with the
files being defragged, but you don't want the risk of having two tools
working against the same file(s) at the same time.
--
Tibor Karaszi
"GriffithsJ" <GriffithsJ_520@.hotmail.com> wrote in message
news:OR6%23BSgoDHA.424@.TK2MSFTNGP10.phx.gbl...
> How does one exclude these from the defragmenter?
> I guess what I need to know is will anything bad happen if the data & log
> files are defragmented? Is it best to switch off SQLServer first?
>|||There is no requirement that SQL database devices be excluded from being
defragmented. There is also no requirement that SQL services first be
stopped prior to defragmenting. Microsoft's defrag APIs fully support
defragmenting SQL database devices online.
- Greg/Raxco Software
Microsoft MVP - Windows File System
Disclaimer: I work for Raxco Software, the maker of PerfectDisk - a
commercial defrag utility, as a systems engineer in the support department.
Want to email me? Delete ntloader.
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:Iqspb.32155$mU6.91387@.newsb.telia.net...
> > How does one exclude these from the defragmenter?
> You have to check that with your defrag tool. If you are using the
built-in
> defrag tool, I suggest you check this in a Windows group.
>
> > I guess what I need to know is will anything bad happen if the data &
log
> > files are defragmented? Is it best to switch off SQLServer first?
> Probably, until you got info from the defrag tool vendor that the tool
does
> not touch locked files. SQL Server does not itself have a problem with the
> files being defragged, but you don't want the risk of having two tools
> working against the same file(s) at the same time.
> --
> Tibor Karaszi
>
> "GriffithsJ" <GriffithsJ_520@.hotmail.com> wrote in message
> news:OR6%23BSgoDHA.424@.TK2MSFTNGP10.phx.gbl...
> > How does one exclude these from the defragmenter?
> >
> > I guess what I need to know is will anything bad happen if the data &
log
> > files are defragmented? Is it best to switch off SQLServer first?
> >
> >
>|||Thanks for the info, Greg! Good to know.
I was trying to get that kind of info from another defrag manufacturer, but
never got a response to my question. Hence my careful approach. :-)
--
Tibor Karaszi
"Greg Hayes/Raxco Software" <ghayesntloader@.raxco.com> wrote in message
news:%23EXhy9uoDHA.2216@.TK2MSFTNGP12.phx.gbl...
> There is no requirement that SQL database devices be excluded from being
> defragmented. There is also no requirement that SQL services first be
> stopped prior to defragmenting. Microsoft's defrag APIs fully support
> defragmenting SQL database devices online.
> - Greg/Raxco Software
> Microsoft MVP - Windows File System
> Disclaimer: I work for Raxco Software, the maker of PerfectDisk - a
> commercial defrag utility, as a systems engineer in the support
department.
> Want to email me? Delete ntloader.
>
> "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:Iqspb.32155$mU6.91387@.newsb.telia.net...
> > > How does one exclude these from the defragmenter?
> >
> > You have to check that with your defrag tool. If you are using the
> built-in
> > defrag tool, I suggest you check this in a Windows group.
> >
> >
> > > I guess what I need to know is will anything bad happen if the data &
> log
> > > files are defragmented? Is it best to switch off SQLServer first?
> >
> > Probably, until you got info from the defrag tool vendor that the tool
> does
> > not touch locked files. SQL Server does not itself have a problem with
the
> > files being defragged, but you don't want the risk of having two tools
> > working against the same file(s) at the same time.
> > --
> > Tibor Karaszi
> >
> >
> > "GriffithsJ" <GriffithsJ_520@.hotmail.com> wrote in message
> > news:OR6%23BSgoDHA.424@.TK2MSFTNGP10.phx.gbl...
> > > How does one exclude these from the defragmenter?
> > >
> > > I guess what I need to know is will anything bad happen if the data &
> log
> > > files are defragmented? Is it best to switch off SQLServer first?
> > >
> > >
> >
> >
>|||Thanks everyone...
Griff
Defragging Tools?
Has anyone got any top tips, or a link to a tool that can help me with
defragging the drive my SQL Server 2K files sit on?
Something that can run on a timer, or be called by scheduled tasks would be
cool.
Thanks
PeterHow about Diskeeper from Executive Software
I think you will need to stop the services to run an External(to SQL Server
defrag) as the files may be passed over because they are locked by SQL
Server itself.
Also look at internal fagmentation
http://www.sql-server-performance.com/rd_index_fragmentation.asp
--
--
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
"Peter Marshall" <texmarshall@.hotmail.com> wrote in message
news:fdbfb.295$Uc7.107@.news-binary.blueyonder.co.uk...
> Has anyone got any top tips, or a link to a tool that can help me with
> defragging the drive my SQL Server 2K files sit on?
> Something that can run on a timer, or be called by scheduled tasks would
be
> cool.
> Thanks
> Peter
>|||In addition to Allan's reply:
If this is a dedicated SQL Server then you shouldn't have to defrag (assuming you don't abuse
autogrow and shrink, of course).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Peter Marshall" <texmarshall@.hotmail.com> wrote in message
news:fdbfb.295$Uc7.107@.news-binary.blueyonder.co.uk...
> Has anyone got any top tips, or a link to a tool that can help me with
> defragging the drive my SQL Server 2K files sit on?
> Something that can run on a timer, or be called by scheduled tasks would be
> cool.
> Thanks
> Peter
>
defragging the drive my SQL Server 2K files sit on?
Something that can run on a timer, or be called by scheduled tasks would be
cool.
Thanks
PeterHow about Diskeeper from Executive Software
I think you will need to stop the services to run an External(to SQL Server
defrag) as the files may be passed over because they are locked by SQL
Server itself.
Also look at internal fagmentation
http://www.sql-server-performance.com/rd_index_fragmentation.asp
--
--
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
"Peter Marshall" <texmarshall@.hotmail.com> wrote in message
news:fdbfb.295$Uc7.107@.news-binary.blueyonder.co.uk...
> Has anyone got any top tips, or a link to a tool that can help me with
> defragging the drive my SQL Server 2K files sit on?
> Something that can run on a timer, or be called by scheduled tasks would
be
> cool.
> Thanks
> Peter
>|||In addition to Allan's reply:
If this is a dedicated SQL Server then you shouldn't have to defrag (assuming you don't abuse
autogrow and shrink, of course).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Peter Marshall" <texmarshall@.hotmail.com> wrote in message
news:fdbfb.295$Uc7.107@.news-binary.blueyonder.co.uk...
> Has anyone got any top tips, or a link to a tool that can help me with
> defragging the drive my SQL Server 2K files sit on?
> Something that can run on a timer, or be called by scheduled tasks would be
> cool.
> Thanks
> Peter
>
Defragging SQL Tables
Guys
Firstly I know exceptionally little about SQL so apologies for the newbie
question.
We run SQL 2K at work on a w2k MP box. I'm not charged with looking after
the apps, our MIS team are. We look after the infrastructure. Their users
have complained that the box is slow and after we've carried out numerous
tests to and from the box we've established the fault lies some where at the
app layer on the server.
Our MIS guys have taken to taking the box offline on Saturdays mornings and
stopping all the SQL services and running MS defrag as they believe it will
defrag the tables. I some how don't believe this will help as I thought you
needed to compact the databases/tables inside enterprise manager to get any
redundancy removed from the tables/db's
Can some one please let me know what is the correct way to defrag a SQL db
and if possible follow it up with some MSKB links as evidence
Thanks in advance
Steve
If all you have is an axe, every problem looks like hours of fun.
Steve,
See:
Microsoft SQL Server 2000 Index Defragmentations Best Practices
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
HTH
Jerry
"Steve Ray" <nochace@.all.com> wrote in message
news:gJR4f.2598$WI4.199@.newsfe4-gui.ntli.net...
> Guys
> Firstly I know exceptionally little about SQL so apologies for the newbie
> question.
> We run SQL 2K at work on a w2k MP box. I'm not charged with looking after
> the apps, our MIS team are. We look after the infrastructure. Their users
> have complained that the box is slow and after we've carried out numerous
> tests to and from the box we've established the fault lies some where at
> the app layer on the server.
> Our MIS guys have taken to taking the box offline on Saturdays mornings
> and stopping all the SQL services and running MS defrag as they believe it
> will defrag the tables. I some how don't believe this will help as I
> thought you needed to compact the databases/tables inside enterprise
> manager to get any redundancy removed from the tables/db's
> Can some one please let me know what is the correct way to defrag a SQL db
> and if possible follow it up with some MSKB links as evidence
> Thanks in advance
> Steve
> --
> If all you have is an axe, every problem looks like hours of fun.
>
|||Lookup DBCC INDEXDEFRAG in BOL
DBCC INDEXDEFRAG (DBNAME, TABLENAME, INDEXNAME)
http://sqlservercode.blogspot.com/
"Steve Ray" wrote:
> Guys
> Firstly I know exceptionally little about SQL so apologies for the newbie
> question.
> We run SQL 2K at work on a w2k MP box. I'm not charged with looking after
> the apps, our MIS team are. We look after the infrastructure. Their users
> have complained that the box is slow and after we've carried out numerous
> tests to and from the box we've established the fault lies some where at the
> app layer on the server.
> Our MIS guys have taken to taking the box offline on Saturdays mornings and
> stopping all the SQL services and running MS defrag as they believe it will
> defrag the tables. I some how don't believe this will help as I thought you
> needed to compact the databases/tables inside enterprise manager to get any
> redundancy removed from the tables/db's
> Can some one please let me know what is the correct way to defrag a SQL db
> and if possible follow it up with some MSKB links as evidence
> Thanks in advance
> Steve
> --
> If all you have is an axe, every problem looks like hours of fun.
>
>
|||... and just in case they do periodic shrink of database files:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Steve Ray" <nochace@.all.com> wrote in message news:gJR4f.2598$WI4.199@.newsfe4-gui.ntli.net...
> Guys
> Firstly I know exceptionally little about SQL so apologies for the newbie
> question.
> We run SQL 2K at work on a w2k MP box. I'm not charged with looking after
> the apps, our MIS team are. We look after the infrastructure. Their users
> have complained that the box is slow and after we've carried out numerous
> tests to and from the box we've established the fault lies some where at the
> app layer on the server.
> Our MIS guys have taken to taking the box offline on Saturdays mornings and
> stopping all the SQL services and running MS defrag as they believe it will
> defrag the tables. I some how don't believe this will help as I thought you
> needed to compact the databases/tables inside enterprise manager to get any
> redundancy removed from the tables/db's
> Can some one please let me know what is the correct way to defrag a SQL db
> and if possible follow it up with some MSKB links as evidence
> Thanks in advance
> Steve
> --
> If all you have is an axe, every problem looks like hours of fun.
>
Firstly I know exceptionally little about SQL so apologies for the newbie
question.
We run SQL 2K at work on a w2k MP box. I'm not charged with looking after
the apps, our MIS team are. We look after the infrastructure. Their users
have complained that the box is slow and after we've carried out numerous
tests to and from the box we've established the fault lies some where at the
app layer on the server.
Our MIS guys have taken to taking the box offline on Saturdays mornings and
stopping all the SQL services and running MS defrag as they believe it will
defrag the tables. I some how don't believe this will help as I thought you
needed to compact the databases/tables inside enterprise manager to get any
redundancy removed from the tables/db's
Can some one please let me know what is the correct way to defrag a SQL db
and if possible follow it up with some MSKB links as evidence
Thanks in advance
Steve
If all you have is an axe, every problem looks like hours of fun.
Steve,
See:
Microsoft SQL Server 2000 Index Defragmentations Best Practices
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
HTH
Jerry
"Steve Ray" <nochace@.all.com> wrote in message
news:gJR4f.2598$WI4.199@.newsfe4-gui.ntli.net...
> Guys
> Firstly I know exceptionally little about SQL so apologies for the newbie
> question.
> We run SQL 2K at work on a w2k MP box. I'm not charged with looking after
> the apps, our MIS team are. We look after the infrastructure. Their users
> have complained that the box is slow and after we've carried out numerous
> tests to and from the box we've established the fault lies some where at
> the app layer on the server.
> Our MIS guys have taken to taking the box offline on Saturdays mornings
> and stopping all the SQL services and running MS defrag as they believe it
> will defrag the tables. I some how don't believe this will help as I
> thought you needed to compact the databases/tables inside enterprise
> manager to get any redundancy removed from the tables/db's
> Can some one please let me know what is the correct way to defrag a SQL db
> and if possible follow it up with some MSKB links as evidence
> Thanks in advance
> Steve
> --
> If all you have is an axe, every problem looks like hours of fun.
>
|||Lookup DBCC INDEXDEFRAG in BOL
DBCC INDEXDEFRAG (DBNAME, TABLENAME, INDEXNAME)
http://sqlservercode.blogspot.com/
"Steve Ray" wrote:
> Guys
> Firstly I know exceptionally little about SQL so apologies for the newbie
> question.
> We run SQL 2K at work on a w2k MP box. I'm not charged with looking after
> the apps, our MIS team are. We look after the infrastructure. Their users
> have complained that the box is slow and after we've carried out numerous
> tests to and from the box we've established the fault lies some where at the
> app layer on the server.
> Our MIS guys have taken to taking the box offline on Saturdays mornings and
> stopping all the SQL services and running MS defrag as they believe it will
> defrag the tables. I some how don't believe this will help as I thought you
> needed to compact the databases/tables inside enterprise manager to get any
> redundancy removed from the tables/db's
> Can some one please let me know what is the correct way to defrag a SQL db
> and if possible follow it up with some MSKB links as evidence
> Thanks in advance
> Steve
> --
> If all you have is an axe, every problem looks like hours of fun.
>
>
|||... and just in case they do periodic shrink of database files:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Steve Ray" <nochace@.all.com> wrote in message news:gJR4f.2598$WI4.199@.newsfe4-gui.ntli.net...
> Guys
> Firstly I know exceptionally little about SQL so apologies for the newbie
> question.
> We run SQL 2K at work on a w2k MP box. I'm not charged with looking after
> the apps, our MIS team are. We look after the infrastructure. Their users
> have complained that the box is slow and after we've carried out numerous
> tests to and from the box we've established the fault lies some where at the
> app layer on the server.
> Our MIS guys have taken to taking the box offline on Saturdays mornings and
> stopping all the SQL services and running MS defrag as they believe it will
> defrag the tables. I some how don't believe this will help as I thought you
> needed to compact the databases/tables inside enterprise manager to get any
> redundancy removed from the tables/db's
> Can some one please let me know what is the correct way to defrag a SQL db
> and if possible follow it up with some MSKB links as evidence
> Thanks in advance
> Steve
> --
> If all you have is an axe, every problem looks like hours of fun.
>
Defragging SQL Tables
Guys
Firstly I know exceptionally little about SQL so apologies for the newbie
question.
We run SQL 2K at work on a w2k MP box. I'm not charged with looking after
the apps, our MIS team are. We look after the infrastructure. Their users
have complained that the box is slow and after we've carried out numerous
tests to and from the box we've established the fault lies some where at the
app layer on the server.
Our MIS guys have taken to taking the box offline on Saturdays mornings and
stopping all the SQL services and running MS defrag as they believe it will
defrag the tables. I some how don't believe this will help as I thought you
needed to compact the databases/tables inside enterprise manager to get any
redundancy removed from the tables/db's
Can some one please let me know what is the correct way to defrag a SQL db
and if possible follow it up with some MSKB links as evidence
Thanks in advance
Steve
If all you have is an axe, every problem looks like hours of fun.Steve,
See:
Microsoft SQL Server 2000 Index Defragmentations Best Practices
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
HTH
Jerry
"Steve Ray" <nochace@.all.com> wrote in message
news:gJR4f.2598$WI4.199@.newsfe4-gui.ntli.net...
> Guys
> Firstly I know exceptionally little about SQL so apologies for the newbie
> question.
> We run SQL 2K at work on a w2k MP box. I'm not charged with looking after
> the apps, our MIS team are. We look after the infrastructure. Their users
> have complained that the box is slow and after we've carried out numerous
> tests to and from the box we've established the fault lies some where at
> the app layer on the server.
> Our MIS guys have taken to taking the box offline on Saturdays mornings
> and stopping all the SQL services and running MS defrag as they believe it
> will defrag the tables. I some how don't believe this will help as I
> thought you needed to compact the databases/tables inside enterprise
> manager to get any redundancy removed from the tables/db's
> Can some one please let me know what is the correct way to defrag a SQL db
> and if possible follow it up with some MSKB links as evidence
> Thanks in advance
> Steve
> --
> If all you have is an axe, every problem looks like hours of fun.
>|||Lookup DBCC INDEXDEFRAG in BOL
DBCC INDEXDEFRAG (DBNAME, TABLENAME, INDEXNAME)
http://sqlservercode.blogspot.com/
"Steve Ray" wrote:
> Guys
> Firstly I know exceptionally little about SQL so apologies for the newbie
> question.
> We run SQL 2K at work on a w2k MP box. I'm not charged with looking after
> the apps, our MIS team are. We look after the infrastructure. Their users
> have complained that the box is slow and after we've carried out numerous
> tests to and from the box we've established the fault lies some where at t
he
> app layer on the server.
> Our MIS guys have taken to taking the box offline on Saturdays mornings an
d
> stopping all the SQL services and running MS defrag as they believe it wil
l
> defrag the tables. I some how don't believe this will help as I thought y
ou
> needed to compact the databases/tables inside enterprise manager to get an
y
> redundancy removed from the tables/db's
> Can some one please let me know what is the correct way to defrag a SQL db
> and if possible follow it up with some MSKB links as evidence
> Thanks in advance
> Steve
> --
> If all you have is an axe, every problem looks like hours of fun.
>
>|||... and just in case they do periodic shrink of database files:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Steve Ray" <nochace@.all.com> wrote in message news:gJR4f.2598$WI4.199@.newsfe4-gui.ntli.net.
.
> Guys
> Firstly I know exceptionally little about SQL so apologies for the newbie
> question.
> We run SQL 2K at work on a w2k MP box. I'm not charged with looking after
> the apps, our MIS team are. We look after the infrastructure. Their users
> have complained that the box is slow and after we've carried out numerous
> tests to and from the box we've established the fault lies some where at t
he
> app layer on the server.
> Our MIS guys have taken to taking the box offline on Saturdays mornings an
d
> stopping all the SQL services and running MS defrag as they believe it wil
l
> defrag the tables. I some how don't believe this will help as I thought y
ou
> needed to compact the databases/tables inside enterprise manager to get an
y
> redundancy removed from the tables/db's
> Can some one please let me know what is the correct way to defrag a SQL db
> and if possible follow it up with some MSKB links as evidence
> Thanks in advance
> Steve
> --
> If all you have is an axe, every problem looks like hours of fun.
>
Firstly I know exceptionally little about SQL so apologies for the newbie
question.
We run SQL 2K at work on a w2k MP box. I'm not charged with looking after
the apps, our MIS team are. We look after the infrastructure. Their users
have complained that the box is slow and after we've carried out numerous
tests to and from the box we've established the fault lies some where at the
app layer on the server.
Our MIS guys have taken to taking the box offline on Saturdays mornings and
stopping all the SQL services and running MS defrag as they believe it will
defrag the tables. I some how don't believe this will help as I thought you
needed to compact the databases/tables inside enterprise manager to get any
redundancy removed from the tables/db's
Can some one please let me know what is the correct way to defrag a SQL db
and if possible follow it up with some MSKB links as evidence
Thanks in advance
Steve
If all you have is an axe, every problem looks like hours of fun.Steve,
See:
Microsoft SQL Server 2000 Index Defragmentations Best Practices
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
HTH
Jerry
"Steve Ray" <nochace@.all.com> wrote in message
news:gJR4f.2598$WI4.199@.newsfe4-gui.ntli.net...
> Guys
> Firstly I know exceptionally little about SQL so apologies for the newbie
> question.
> We run SQL 2K at work on a w2k MP box. I'm not charged with looking after
> the apps, our MIS team are. We look after the infrastructure. Their users
> have complained that the box is slow and after we've carried out numerous
> tests to and from the box we've established the fault lies some where at
> the app layer on the server.
> Our MIS guys have taken to taking the box offline on Saturdays mornings
> and stopping all the SQL services and running MS defrag as they believe it
> will defrag the tables. I some how don't believe this will help as I
> thought you needed to compact the databases/tables inside enterprise
> manager to get any redundancy removed from the tables/db's
> Can some one please let me know what is the correct way to defrag a SQL db
> and if possible follow it up with some MSKB links as evidence
> Thanks in advance
> Steve
> --
> If all you have is an axe, every problem looks like hours of fun.
>|||Lookup DBCC INDEXDEFRAG in BOL
DBCC INDEXDEFRAG (DBNAME, TABLENAME, INDEXNAME)
http://sqlservercode.blogspot.com/
"Steve Ray" wrote:
> Guys
> Firstly I know exceptionally little about SQL so apologies for the newbie
> question.
> We run SQL 2K at work on a w2k MP box. I'm not charged with looking after
> the apps, our MIS team are. We look after the infrastructure. Their users
> have complained that the box is slow and after we've carried out numerous
> tests to and from the box we've established the fault lies some where at t
he
> app layer on the server.
> Our MIS guys have taken to taking the box offline on Saturdays mornings an
d
> stopping all the SQL services and running MS defrag as they believe it wil
l
> defrag the tables. I some how don't believe this will help as I thought y
ou
> needed to compact the databases/tables inside enterprise manager to get an
y
> redundancy removed from the tables/db's
> Can some one please let me know what is the correct way to defrag a SQL db
> and if possible follow it up with some MSKB links as evidence
> Thanks in advance
> Steve
> --
> If all you have is an axe, every problem looks like hours of fun.
>
>|||... and just in case they do periodic shrink of database files:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Steve Ray" <nochace@.all.com> wrote in message news:gJR4f.2598$WI4.199@.newsfe4-gui.ntli.net.
.
> Guys
> Firstly I know exceptionally little about SQL so apologies for the newbie
> question.
> We run SQL 2K at work on a w2k MP box. I'm not charged with looking after
> the apps, our MIS team are. We look after the infrastructure. Their users
> have complained that the box is slow and after we've carried out numerous
> tests to and from the box we've established the fault lies some where at t
he
> app layer on the server.
> Our MIS guys have taken to taking the box offline on Saturdays mornings an
d
> stopping all the SQL services and running MS defrag as they believe it wil
l
> defrag the tables. I some how don't believe this will help as I thought y
ou
> needed to compact the databases/tables inside enterprise manager to get an
y
> redundancy removed from the tables/db's
> Can some one please let me know what is the correct way to defrag a SQL db
> and if possible follow it up with some MSKB links as evidence
> Thanks in advance
> Steve
> --
> If all you have is an axe, every problem looks like hours of fun.
>
Defragging SQL Tables
Guys
Firstly I know exceptionally little about SQL so apologies for the newbie
question.
We run SQL 2K at work on a w2k MP box. I'm not charged with looking after
the apps, our MIS team are. We look after the infrastructure. Their users
have complained that the box is slow and after we've carried out numerous
tests to and from the box we've established the fault lies some where at the
app layer on the server.
Our MIS guys have taken to taking the box offline on Saturdays mornings and
stopping all the SQL services and running MS defrag as they believe it will
defrag the tables. I some how don't believe this will help as I thought you
needed to compact the databases/tables inside enterprise manager to get any
redundancy removed from the tables/db's
Can some one please let me know what is the correct way to defrag a SQL db
and if possible follow it up with some MSKB links as evidence
Thanks in advance
Steve
--
If all you have is an axe, every problem looks like hours of fun.Steve,
See:
Microsoft SQL Server 2000 Index Defragmentations Best Practices
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
HTH
Jerry
"Steve Ray" <nochace@.all.com> wrote in message
news:gJR4f.2598$WI4.199@.newsfe4-gui.ntli.net...
> Guys
> Firstly I know exceptionally little about SQL so apologies for the newbie
> question.
> We run SQL 2K at work on a w2k MP box. I'm not charged with looking after
> the apps, our MIS team are. We look after the infrastructure. Their users
> have complained that the box is slow and after we've carried out numerous
> tests to and from the box we've established the fault lies some where at
> the app layer on the server.
> Our MIS guys have taken to taking the box offline on Saturdays mornings
> and stopping all the SQL services and running MS defrag as they believe it
> will defrag the tables. I some how don't believe this will help as I
> thought you needed to compact the databases/tables inside enterprise
> manager to get any redundancy removed from the tables/db's
> Can some one please let me know what is the correct way to defrag a SQL db
> and if possible follow it up with some MSKB links as evidence
> Thanks in advance
> Steve
> --
> If all you have is an axe, every problem looks like hours of fun.
>|||Lookup DBCC INDEXDEFRAG in BOL
DBCC INDEXDEFRAG (DBNAME, TABLENAME, INDEXNAME)
http://sqlservercode.blogspot.com/
"Steve Ray" wrote:
> Guys
> Firstly I know exceptionally little about SQL so apologies for the newbie
> question.
> We run SQL 2K at work on a w2k MP box. I'm not charged with looking after
> the apps, our MIS team are. We look after the infrastructure. Their users
> have complained that the box is slow and after we've carried out numerous
> tests to and from the box we've established the fault lies some where at the
> app layer on the server.
> Our MIS guys have taken to taking the box offline on Saturdays mornings and
> stopping all the SQL services and running MS defrag as they believe it will
> defrag the tables. I some how don't believe this will help as I thought you
> needed to compact the databases/tables inside enterprise manager to get any
> redundancy removed from the tables/db's
> Can some one please let me know what is the correct way to defrag a SQL db
> and if possible follow it up with some MSKB links as evidence
> Thanks in advance
> Steve
> --
> If all you have is an axe, every problem looks like hours of fun.
>
>|||... and just in case they do periodic shrink of database files:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Steve Ray" <nochace@.all.com> wrote in message news:gJR4f.2598$WI4.199@.newsfe4-gui.ntli.net...
> Guys
> Firstly I know exceptionally little about SQL so apologies for the newbie
> question.
> We run SQL 2K at work on a w2k MP box. I'm not charged with looking after
> the apps, our MIS team are. We look after the infrastructure. Their users
> have complained that the box is slow and after we've carried out numerous
> tests to and from the box we've established the fault lies some where at the
> app layer on the server.
> Our MIS guys have taken to taking the box offline on Saturdays mornings and
> stopping all the SQL services and running MS defrag as they believe it will
> defrag the tables. I some how don't believe this will help as I thought you
> needed to compact the databases/tables inside enterprise manager to get any
> redundancy removed from the tables/db's
> Can some one please let me know what is the correct way to defrag a SQL db
> and if possible follow it up with some MSKB links as evidence
> Thanks in advance
> Steve
> --
> If all you have is an axe, every problem looks like hours of fun.
>
Firstly I know exceptionally little about SQL so apologies for the newbie
question.
We run SQL 2K at work on a w2k MP box. I'm not charged with looking after
the apps, our MIS team are. We look after the infrastructure. Their users
have complained that the box is slow and after we've carried out numerous
tests to and from the box we've established the fault lies some where at the
app layer on the server.
Our MIS guys have taken to taking the box offline on Saturdays mornings and
stopping all the SQL services and running MS defrag as they believe it will
defrag the tables. I some how don't believe this will help as I thought you
needed to compact the databases/tables inside enterprise manager to get any
redundancy removed from the tables/db's
Can some one please let me know what is the correct way to defrag a SQL db
and if possible follow it up with some MSKB links as evidence
Thanks in advance
Steve
--
If all you have is an axe, every problem looks like hours of fun.Steve,
See:
Microsoft SQL Server 2000 Index Defragmentations Best Practices
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
HTH
Jerry
"Steve Ray" <nochace@.all.com> wrote in message
news:gJR4f.2598$WI4.199@.newsfe4-gui.ntli.net...
> Guys
> Firstly I know exceptionally little about SQL so apologies for the newbie
> question.
> We run SQL 2K at work on a w2k MP box. I'm not charged with looking after
> the apps, our MIS team are. We look after the infrastructure. Their users
> have complained that the box is slow and after we've carried out numerous
> tests to and from the box we've established the fault lies some where at
> the app layer on the server.
> Our MIS guys have taken to taking the box offline on Saturdays mornings
> and stopping all the SQL services and running MS defrag as they believe it
> will defrag the tables. I some how don't believe this will help as I
> thought you needed to compact the databases/tables inside enterprise
> manager to get any redundancy removed from the tables/db's
> Can some one please let me know what is the correct way to defrag a SQL db
> and if possible follow it up with some MSKB links as evidence
> Thanks in advance
> Steve
> --
> If all you have is an axe, every problem looks like hours of fun.
>|||Lookup DBCC INDEXDEFRAG in BOL
DBCC INDEXDEFRAG (DBNAME, TABLENAME, INDEXNAME)
http://sqlservercode.blogspot.com/
"Steve Ray" wrote:
> Guys
> Firstly I know exceptionally little about SQL so apologies for the newbie
> question.
> We run SQL 2K at work on a w2k MP box. I'm not charged with looking after
> the apps, our MIS team are. We look after the infrastructure. Their users
> have complained that the box is slow and after we've carried out numerous
> tests to and from the box we've established the fault lies some where at the
> app layer on the server.
> Our MIS guys have taken to taking the box offline on Saturdays mornings and
> stopping all the SQL services and running MS defrag as they believe it will
> defrag the tables. I some how don't believe this will help as I thought you
> needed to compact the databases/tables inside enterprise manager to get any
> redundancy removed from the tables/db's
> Can some one please let me know what is the correct way to defrag a SQL db
> and if possible follow it up with some MSKB links as evidence
> Thanks in advance
> Steve
> --
> If all you have is an axe, every problem looks like hours of fun.
>
>|||... and just in case they do periodic shrink of database files:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Steve Ray" <nochace@.all.com> wrote in message news:gJR4f.2598$WI4.199@.newsfe4-gui.ntli.net...
> Guys
> Firstly I know exceptionally little about SQL so apologies for the newbie
> question.
> We run SQL 2K at work on a w2k MP box. I'm not charged with looking after
> the apps, our MIS team are. We look after the infrastructure. Their users
> have complained that the box is slow and after we've carried out numerous
> tests to and from the box we've established the fault lies some where at the
> app layer on the server.
> Our MIS guys have taken to taking the box offline on Saturdays mornings and
> stopping all the SQL services and running MS defrag as they believe it will
> defrag the tables. I some how don't believe this will help as I thought you
> needed to compact the databases/tables inside enterprise manager to get any
> redundancy removed from the tables/db's
> Can some one please let me know what is the correct way to defrag a SQL db
> and if possible follow it up with some MSKB links as evidence
> Thanks in advance
> Steve
> --
> If all you have is an axe, every problem looks like hours of fun.
>
Sunday, March 11, 2012
Defrag Script
Hi all,
I'm writing my own defrag script. I've set it up so under certain conditions
it will run an IndexDefrag and under others a Reindex. I wanted to post the
conditions I've set and see if anyone has any feedback that can improve them.
I have two temporary tables 1 contains the tables and indexes, and one the
ShowContig information.
The IndexDefrag test is:
UPDATE #TableIndexList
SET ReqAction = 'I'
FROM #TableIndexList AS a
INNER JOIN #FragStats AS b
ON a.ObjectID = b.ObjectID
WHERE b.Pages BETWEEN 16 AND 4096
AND ((b.LogicalFragmentation BETWEEN 20 AND 40)
OR b.AveragePageDensity < 75)
The reindex criteria is:
UPDATE #TableIndexList
SET ReqAction = 'R'
FROM #TableIndexList AS a
INNER JOIN #FragStats AS b
ON a.ObjectID = b.ObjectID
WHERE (b.Pages > 16
AND (b.LogicalFragmentation > 40
OR b.AveragePageDensity > 95))
OR (b.Pages > 4096
AND (b.LogicalFragmentation > 20
OR b.AveragePageDensity < 75))
As advised any feedback would be greatly appreciated...Have you compared the before and after results. Sometimes due to the way you
have constructed your table you will find residual fragmentation after
running such scripts will be almost the same.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"BenUK" <BenUK@.discussions.microsoft.com> wrote in message
news:AD82F542-DD91-4973-B0C1-8DAB523A7EF8@.microsoft.com...
> Hi all,
> I'm writing my own defrag script. I've set it up so under certain
> conditions
> it will run an IndexDefrag and under others a Reindex. I wanted to post
> the
> conditions I've set and see if anyone has any feedback that can improve
> them.
> I have two temporary tables 1 contains the tables and indexes, and one the
> ShowContig information.
> The IndexDefrag test is:
> UPDATE #TableIndexList
> SET ReqAction = 'I'
> FROM #TableIndexList AS a
> INNER JOIN #FragStats AS b
> ON a.ObjectID = b.ObjectID
> WHERE b.Pages BETWEEN 16 AND 4096
> AND ((b.LogicalFragmentation BETWEEN 20 AND 40)
> OR b.AveragePageDensity < 75)
> The reindex criteria is:
> UPDATE #TableIndexList
> SET ReqAction = 'R'
> FROM #TableIndexList AS a
> INNER JOIN #FragStats AS b
> ON a.ObjectID = b.ObjectID
> WHERE (b.Pages > 16
> AND (b.LogicalFragmentation > 40
> OR b.AveragePageDensity > 95))
> OR (b.Pages > 4096
> AND (b.LogicalFragmentation > 20
> OR b.AveragePageDensity < 75))
> As advised any feedback would be greatly appreciated...|||Thats interesting.. ..I'm identifying heaps for manual defrags, I'm excluding
blobs, is it a scenario where the table is busy and the IndexDefrag is
skipping the pages?
"Hilary Cotter" wrote:
> Have you compared the before and after results. Sometimes due to the way you
> have constructed your table you will find residual fragmentation after
> running such scripts will be almost the same.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> news:AD82F542-DD91-4973-B0C1-8DAB523A7EF8@.microsoft.com...
> > Hi all,
> >
> > I'm writing my own defrag script. I've set it up so under certain
> > conditions
> > it will run an IndexDefrag and under others a Reindex. I wanted to post
> > the
> > conditions I've set and see if anyone has any feedback that can improve
> > them.
> > I have two temporary tables 1 contains the tables and indexes, and one the
> > ShowContig information.
> >
> > The IndexDefrag test is:
> > UPDATE #TableIndexList
> > SET ReqAction = 'I'
> > FROM #TableIndexList AS a
> > INNER JOIN #FragStats AS b
> > ON a.ObjectID = b.ObjectID
> > WHERE b.Pages BETWEEN 16 AND 4096
> > AND ((b.LogicalFragmentation BETWEEN 20 AND 40)
> > OR b.AveragePageDensity < 75)
> >
> > The reindex criteria is:
> > UPDATE #TableIndexList
> > SET ReqAction = 'R'
> > FROM #TableIndexList AS a
> > INNER JOIN #FragStats AS b
> > ON a.ObjectID = b.ObjectID
> > WHERE (b.Pages > 16
> > AND (b.LogicalFragmentation > 40
> > OR b.AveragePageDensity > 95))
> > OR (b.Pages > 4096
> > AND (b.LogicalFragmentation > 20
> > OR b.AveragePageDensity < 75))
> >
> > As advised any feedback would be greatly appreciated...
>
>|||or maybe when there's not enough disk space to run DBCC Reindex on a large
table
"Hilary Cotter" wrote:
> Have you compared the before and after results. Sometimes due to the way you
> have constructed your table you will find residual fragmentation after
> running such scripts will be almost the same.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> news:AD82F542-DD91-4973-B0C1-8DAB523A7EF8@.microsoft.com...
> > Hi all,
> >
> > I'm writing my own defrag script. I've set it up so under certain
> > conditions
> > it will run an IndexDefrag and under others a Reindex. I wanted to post
> > the
> > conditions I've set and see if anyone has any feedback that can improve
> > them.
> > I have two temporary tables 1 contains the tables and indexes, and one the
> > ShowContig information.
> >
> > The IndexDefrag test is:
> > UPDATE #TableIndexList
> > SET ReqAction = 'I'
> > FROM #TableIndexList AS a
> > INNER JOIN #FragStats AS b
> > ON a.ObjectID = b.ObjectID
> > WHERE b.Pages BETWEEN 16 AND 4096
> > AND ((b.LogicalFragmentation BETWEEN 20 AND 40)
> > OR b.AveragePageDensity < 75)
> >
> > The reindex criteria is:
> > UPDATE #TableIndexList
> > SET ReqAction = 'R'
> > FROM #TableIndexList AS a
> > INNER JOIN #FragStats AS b
> > ON a.ObjectID = b.ObjectID
> > WHERE (b.Pages > 16
> > AND (b.LogicalFragmentation > 40
> > OR b.AveragePageDensity > 95))
> > OR (b.Pages > 4096
> > AND (b.LogicalFragmentation > 20
> > OR b.AveragePageDensity < 75))
> >
> > As advised any feedback would be greatly appreciated...
>
>
I'm writing my own defrag script. I've set it up so under certain conditions
it will run an IndexDefrag and under others a Reindex. I wanted to post the
conditions I've set and see if anyone has any feedback that can improve them.
I have two temporary tables 1 contains the tables and indexes, and one the
ShowContig information.
The IndexDefrag test is:
UPDATE #TableIndexList
SET ReqAction = 'I'
FROM #TableIndexList AS a
INNER JOIN #FragStats AS b
ON a.ObjectID = b.ObjectID
WHERE b.Pages BETWEEN 16 AND 4096
AND ((b.LogicalFragmentation BETWEEN 20 AND 40)
OR b.AveragePageDensity < 75)
The reindex criteria is:
UPDATE #TableIndexList
SET ReqAction = 'R'
FROM #TableIndexList AS a
INNER JOIN #FragStats AS b
ON a.ObjectID = b.ObjectID
WHERE (b.Pages > 16
AND (b.LogicalFragmentation > 40
OR b.AveragePageDensity > 95))
OR (b.Pages > 4096
AND (b.LogicalFragmentation > 20
OR b.AveragePageDensity < 75))
As advised any feedback would be greatly appreciated...Have you compared the before and after results. Sometimes due to the way you
have constructed your table you will find residual fragmentation after
running such scripts will be almost the same.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"BenUK" <BenUK@.discussions.microsoft.com> wrote in message
news:AD82F542-DD91-4973-B0C1-8DAB523A7EF8@.microsoft.com...
> Hi all,
> I'm writing my own defrag script. I've set it up so under certain
> conditions
> it will run an IndexDefrag and under others a Reindex. I wanted to post
> the
> conditions I've set and see if anyone has any feedback that can improve
> them.
> I have two temporary tables 1 contains the tables and indexes, and one the
> ShowContig information.
> The IndexDefrag test is:
> UPDATE #TableIndexList
> SET ReqAction = 'I'
> FROM #TableIndexList AS a
> INNER JOIN #FragStats AS b
> ON a.ObjectID = b.ObjectID
> WHERE b.Pages BETWEEN 16 AND 4096
> AND ((b.LogicalFragmentation BETWEEN 20 AND 40)
> OR b.AveragePageDensity < 75)
> The reindex criteria is:
> UPDATE #TableIndexList
> SET ReqAction = 'R'
> FROM #TableIndexList AS a
> INNER JOIN #FragStats AS b
> ON a.ObjectID = b.ObjectID
> WHERE (b.Pages > 16
> AND (b.LogicalFragmentation > 40
> OR b.AveragePageDensity > 95))
> OR (b.Pages > 4096
> AND (b.LogicalFragmentation > 20
> OR b.AveragePageDensity < 75))
> As advised any feedback would be greatly appreciated...|||Thats interesting.. ..I'm identifying heaps for manual defrags, I'm excluding
blobs, is it a scenario where the table is busy and the IndexDefrag is
skipping the pages?
"Hilary Cotter" wrote:
> Have you compared the before and after results. Sometimes due to the way you
> have constructed your table you will find residual fragmentation after
> running such scripts will be almost the same.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> news:AD82F542-DD91-4973-B0C1-8DAB523A7EF8@.microsoft.com...
> > Hi all,
> >
> > I'm writing my own defrag script. I've set it up so under certain
> > conditions
> > it will run an IndexDefrag and under others a Reindex. I wanted to post
> > the
> > conditions I've set and see if anyone has any feedback that can improve
> > them.
> > I have two temporary tables 1 contains the tables and indexes, and one the
> > ShowContig information.
> >
> > The IndexDefrag test is:
> > UPDATE #TableIndexList
> > SET ReqAction = 'I'
> > FROM #TableIndexList AS a
> > INNER JOIN #FragStats AS b
> > ON a.ObjectID = b.ObjectID
> > WHERE b.Pages BETWEEN 16 AND 4096
> > AND ((b.LogicalFragmentation BETWEEN 20 AND 40)
> > OR b.AveragePageDensity < 75)
> >
> > The reindex criteria is:
> > UPDATE #TableIndexList
> > SET ReqAction = 'R'
> > FROM #TableIndexList AS a
> > INNER JOIN #FragStats AS b
> > ON a.ObjectID = b.ObjectID
> > WHERE (b.Pages > 16
> > AND (b.LogicalFragmentation > 40
> > OR b.AveragePageDensity > 95))
> > OR (b.Pages > 4096
> > AND (b.LogicalFragmentation > 20
> > OR b.AveragePageDensity < 75))
> >
> > As advised any feedback would be greatly appreciated...
>
>|||or maybe when there's not enough disk space to run DBCC Reindex on a large
table
"Hilary Cotter" wrote:
> Have you compared the before and after results. Sometimes due to the way you
> have constructed your table you will find residual fragmentation after
> running such scripts will be almost the same.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> news:AD82F542-DD91-4973-B0C1-8DAB523A7EF8@.microsoft.com...
> > Hi all,
> >
> > I'm writing my own defrag script. I've set it up so under certain
> > conditions
> > it will run an IndexDefrag and under others a Reindex. I wanted to post
> > the
> > conditions I've set and see if anyone has any feedback that can improve
> > them.
> > I have two temporary tables 1 contains the tables and indexes, and one the
> > ShowContig information.
> >
> > The IndexDefrag test is:
> > UPDATE #TableIndexList
> > SET ReqAction = 'I'
> > FROM #TableIndexList AS a
> > INNER JOIN #FragStats AS b
> > ON a.ObjectID = b.ObjectID
> > WHERE b.Pages BETWEEN 16 AND 4096
> > AND ((b.LogicalFragmentation BETWEEN 20 AND 40)
> > OR b.AveragePageDensity < 75)
> >
> > The reindex criteria is:
> > UPDATE #TableIndexList
> > SET ReqAction = 'R'
> > FROM #TableIndexList AS a
> > INNER JOIN #FragStats AS b
> > ON a.ObjectID = b.ObjectID
> > WHERE (b.Pages > 16
> > AND (b.LogicalFragmentation > 40
> > OR b.AveragePageDensity > 95))
> > OR (b.Pages > 4096
> > AND (b.LogicalFragmentation > 20
> > OR b.AveragePageDensity < 75))
> >
> > As advised any feedback would be greatly appreciated...
>
>
Defrag Script
Hi all,
I'm writing my own defrag script. I've set it up so under certain conditions
it will run an IndexDefrag and under others a Reindex. I wanted to post the
conditions I've set and see if anyone has any feedback that can improve them
.
I have two temporary tables 1 contains the tables and indexes, and one the
ShowContig information.
The IndexDefrag test is:
UPDATE #TableIndexList
SET ReqAction = 'I'
FROM #TableIndexList AS a
INNER JOIN #FragStats AS b
ON a.ObjectID = b.ObjectID
WHERE b.Pages BETWEEN 16 AND 4096
AND ((b.LogicalFragmentation BETWEEN 20 AND 40)
OR b.AveragePageDensity < 75)
The reindex criteria is:
UPDATE #TableIndexList
SET ReqAction = 'R'
FROM #TableIndexList AS a
INNER JOIN #FragStats AS b
ON a.ObjectID = b.ObjectID
WHERE (b.Pages > 16
AND (b.LogicalFragmentation > 40
OR b.AveragePageDensity > 95))
OR (b.Pages > 4096
AND (b.LogicalFragmentation > 20
OR b.AveragePageDensity < 75))
As advised any feedback would be greatly appreciated...Have you compared the before and after results. Sometimes due to the way you
have constructed your table you will find residual fragmentation after
running such scripts will be almost the same.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"BenUK" <BenUK@.discussions.microsoft.com> wrote in message
news:AD82F542-DD91-4973-B0C1-8DAB523A7EF8@.microsoft.com...
> Hi all,
> I'm writing my own defrag script. I've set it up so under certain
> conditions
> it will run an IndexDefrag and under others a Reindex. I wanted to post
> the
> conditions I've set and see if anyone has any feedback that can improve
> them.
> I have two temporary tables 1 contains the tables and indexes, and one the
> ShowContig information.
> The IndexDefrag test is:
> UPDATE #TableIndexList
> SET ReqAction = 'I'
> FROM #TableIndexList AS a
> INNER JOIN #FragStats AS b
> ON a.ObjectID = b.ObjectID
> WHERE b.Pages BETWEEN 16 AND 4096
> AND ((b.LogicalFragmentation BETWEEN 20 AND 40)
> OR b.AveragePageDensity < 75)
> The reindex criteria is:
> UPDATE #TableIndexList
> SET ReqAction = 'R'
> FROM #TableIndexList AS a
> INNER JOIN #FragStats AS b
> ON a.ObjectID = b.ObjectID
> WHERE (b.Pages > 16
> AND (b.LogicalFragmentation > 40
> OR b.AveragePageDensity > 95))
> OR (b.Pages > 4096
> AND (b.LogicalFragmentation > 20
> OR b.AveragePageDensity < 75))
> As advised any feedback would be greatly appreciated...|||Thats interesting.. ..I'm identifying heaps for manual defrags, I'm excludin
g
blobs, is it a scenario where the table is busy and the IndexDefrag is
skipping the pages?
"Hilary Cotter" wrote:
> Have you compared the before and after results. Sometimes due to the way y
ou
> have constructed your table you will find residual fragmentation after
> running such scripts will be almost the same.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> news:AD82F542-DD91-4973-B0C1-8DAB523A7EF8@.microsoft.com...
>
>|||or maybe when there's not enough disk space to run DBCC Reindex on a large
table
"Hilary Cotter" wrote:
> Have you compared the before and after results. Sometimes due to the way y
ou
> have constructed your table you will find residual fragmentation after
> running such scripts will be almost the same.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> news:AD82F542-DD91-4973-B0C1-8DAB523A7EF8@.microsoft.com...
>
>
I'm writing my own defrag script. I've set it up so under certain conditions
it will run an IndexDefrag and under others a Reindex. I wanted to post the
conditions I've set and see if anyone has any feedback that can improve them
.
I have two temporary tables 1 contains the tables and indexes, and one the
ShowContig information.
The IndexDefrag test is:
UPDATE #TableIndexList
SET ReqAction = 'I'
FROM #TableIndexList AS a
INNER JOIN #FragStats AS b
ON a.ObjectID = b.ObjectID
WHERE b.Pages BETWEEN 16 AND 4096
AND ((b.LogicalFragmentation BETWEEN 20 AND 40)
OR b.AveragePageDensity < 75)
The reindex criteria is:
UPDATE #TableIndexList
SET ReqAction = 'R'
FROM #TableIndexList AS a
INNER JOIN #FragStats AS b
ON a.ObjectID = b.ObjectID
WHERE (b.Pages > 16
AND (b.LogicalFragmentation > 40
OR b.AveragePageDensity > 95))
OR (b.Pages > 4096
AND (b.LogicalFragmentation > 20
OR b.AveragePageDensity < 75))
As advised any feedback would be greatly appreciated...Have you compared the before and after results. Sometimes due to the way you
have constructed your table you will find residual fragmentation after
running such scripts will be almost the same.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"BenUK" <BenUK@.discussions.microsoft.com> wrote in message
news:AD82F542-DD91-4973-B0C1-8DAB523A7EF8@.microsoft.com...
> Hi all,
> I'm writing my own defrag script. I've set it up so under certain
> conditions
> it will run an IndexDefrag and under others a Reindex. I wanted to post
> the
> conditions I've set and see if anyone has any feedback that can improve
> them.
> I have two temporary tables 1 contains the tables and indexes, and one the
> ShowContig information.
> The IndexDefrag test is:
> UPDATE #TableIndexList
> SET ReqAction = 'I'
> FROM #TableIndexList AS a
> INNER JOIN #FragStats AS b
> ON a.ObjectID = b.ObjectID
> WHERE b.Pages BETWEEN 16 AND 4096
> AND ((b.LogicalFragmentation BETWEEN 20 AND 40)
> OR b.AveragePageDensity < 75)
> The reindex criteria is:
> UPDATE #TableIndexList
> SET ReqAction = 'R'
> FROM #TableIndexList AS a
> INNER JOIN #FragStats AS b
> ON a.ObjectID = b.ObjectID
> WHERE (b.Pages > 16
> AND (b.LogicalFragmentation > 40
> OR b.AveragePageDensity > 95))
> OR (b.Pages > 4096
> AND (b.LogicalFragmentation > 20
> OR b.AveragePageDensity < 75))
> As advised any feedback would be greatly appreciated...|||Thats interesting.. ..I'm identifying heaps for manual defrags, I'm excludin
g
blobs, is it a scenario where the table is busy and the IndexDefrag is
skipping the pages?
"Hilary Cotter" wrote:
> Have you compared the before and after results. Sometimes due to the way y
ou
> have constructed your table you will find residual fragmentation after
> running such scripts will be almost the same.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> news:AD82F542-DD91-4973-B0C1-8DAB523A7EF8@.microsoft.com...
>
>|||or maybe when there's not enough disk space to run DBCC Reindex on a large
table
"Hilary Cotter" wrote:
> Have you compared the before and after results. Sometimes due to the way y
ou
> have constructed your table you will find residual fragmentation after
> running such scripts will be almost the same.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> news:AD82F542-DD91-4973-B0C1-8DAB523A7EF8@.microsoft.com...
>
>
Defrag on SQL Server.....
I am periodically getting the complaints that SQL Server is slow. As a
performance improvements I decided to run the defrag utility. Never run on
this server from years. How to know that defrag need to be run? How to prove
there is an excessive physical file fragmentation on a Windows 2K?
SQL 2K with SP3 and Windows 2K with latest SP.
Thank you,
Larry
Larry,
Check out:
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
HTH
Jerry
"Larry Smith" <LSmith_Melbourne@.hotmail.com> wrote in message
news:OBRu9MgxFHA.2504@.TK2MSFTNGP10.phx.gbl...
>I am periodically getting the complaints that SQL Server is slow. As a
> performance improvements I decided to run the defrag utility. Never run on
> this server from years. How to know that defrag need to be run? How to
> prove
> there is an excessive physical file fragmentation on a Windows 2K?
> SQL 2K with SP3 and Windows 2K with latest SP.
> Thank you,
> Larry
>
|||If you refer to the file system, you wouldn't need defrag if you stay away from constantly growing
and shrinking the database files. Keep them at a stable size and increase the size only when needed.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Larry Smith" <LSmith_Melbourne@.hotmail.com> wrote in message
news:OBRu9MgxFHA.2504@.TK2MSFTNGP10.phx.gbl...
>I am periodically getting the complaints that SQL Server is slow. As a
> performance improvements I decided to run the defrag utility. Never run on
> this server from years. How to know that defrag need to be run? How to prove
> there is an excessive physical file fragmentation on a Windows 2K?
> SQL 2K with SP3 and Windows 2K with latest SP.
> Thank you,
> Larry
>
|||I saw your postings about defragging indexes and wondered if anyone
would be willing to help me out. I work for a software house that has
developed database defragmentation software for SQL Server, and we are
looking for beta-testers with DBA knowledge of SQL Server and an
awareness of fragmentation. Our software can defrag selected indexes on
a chosen table, one after the other, and has scheduling facilities
built in.
If anyone is interested in beta-testing it, I would like to hear from
you as it is close to launch. My company is willing to offer a free
copy of the software to anyone who can provide genuine and useful
feedback. Please post back if you are interested!
Regards,
Martin
Larry Smith wrote:
> I am periodically getting the complaints that SQL Server is slow. As a
> performance improvements I decided to run the defrag utility. Never run on
> this server from years. How to know that defrag need to be run? How to prove
> there is an excessive physical file fragmentation on a Windows 2K?
> SQL 2K with SP3 and Windows 2K with latest SP.
> Thank you,
> Larry
performance improvements I decided to run the defrag utility. Never run on
this server from years. How to know that defrag need to be run? How to prove
there is an excessive physical file fragmentation on a Windows 2K?
SQL 2K with SP3 and Windows 2K with latest SP.
Thank you,
Larry
Larry,
Check out:
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
HTH
Jerry
"Larry Smith" <LSmith_Melbourne@.hotmail.com> wrote in message
news:OBRu9MgxFHA.2504@.TK2MSFTNGP10.phx.gbl...
>I am periodically getting the complaints that SQL Server is slow. As a
> performance improvements I decided to run the defrag utility. Never run on
> this server from years. How to know that defrag need to be run? How to
> prove
> there is an excessive physical file fragmentation on a Windows 2K?
> SQL 2K with SP3 and Windows 2K with latest SP.
> Thank you,
> Larry
>
|||If you refer to the file system, you wouldn't need defrag if you stay away from constantly growing
and shrinking the database files. Keep them at a stable size and increase the size only when needed.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Larry Smith" <LSmith_Melbourne@.hotmail.com> wrote in message
news:OBRu9MgxFHA.2504@.TK2MSFTNGP10.phx.gbl...
>I am periodically getting the complaints that SQL Server is slow. As a
> performance improvements I decided to run the defrag utility. Never run on
> this server from years. How to know that defrag need to be run? How to prove
> there is an excessive physical file fragmentation on a Windows 2K?
> SQL 2K with SP3 and Windows 2K with latest SP.
> Thank you,
> Larry
>
|||I saw your postings about defragging indexes and wondered if anyone
would be willing to help me out. I work for a software house that has
developed database defragmentation software for SQL Server, and we are
looking for beta-testers with DBA knowledge of SQL Server and an
awareness of fragmentation. Our software can defrag selected indexes on
a chosen table, one after the other, and has scheduling facilities
built in.
If anyone is interested in beta-testing it, I would like to hear from
you as it is close to launch. My company is willing to offer a free
copy of the software to anyone who can provide genuine and useful
feedback. Please post back if you are interested!
Regards,
Martin
Larry Smith wrote:
> I am periodically getting the complaints that SQL Server is slow. As a
> performance improvements I decided to run the defrag utility. Never run on
> this server from years. How to know that defrag need to be run? How to prove
> there is an excessive physical file fragmentation on a Windows 2K?
> SQL 2K with SP3 and Windows 2K with latest SP.
> Thank you,
> Larry
Defrag on SQL Server.....
I am periodically getting the complaints that SQL Server is slow. As a
performance improvements I decided to run the defrag utility. Never run on
this server from years. How to know that defrag need to be run? How to prove
there is an excessive physical file fragmentation on a Windows 2K?
SQL 2K with SP3 and Windows 2K with latest SP.
Thank you,
LarryLarry,
Check out:
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
HTH
Jerry
"Larry Smith" <LSmith_Melbourne@.hotmail.com> wrote in message
news:OBRu9MgxFHA.2504@.TK2MSFTNGP10.phx.gbl...
>I am periodically getting the complaints that SQL Server is slow. As a
> performance improvements I decided to run the defrag utility. Never run on
> this server from years. How to know that defrag need to be run? How to
> prove
> there is an excessive physical file fragmentation on a Windows 2K?
> SQL 2K with SP3 and Windows 2K with latest SP.
> Thank you,
> Larry
>|||If you refer to the file system, you wouldn't need defrag if you stay away f
rom constantly growing
and shrinking the database files. Keep them at a stable size and increase th
e size only when needed.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Larry Smith" <LSmith_Melbourne@.hotmail.com> wrote in message
news:OBRu9MgxFHA.2504@.TK2MSFTNGP10.phx.gbl...
>I am periodically getting the complaints that SQL Server is slow. As a
> performance improvements I decided to run the defrag utility. Never run on
> this server from years. How to know that defrag need to be run? How to pro
ve
> there is an excessive physical file fragmentation on a Windows 2K?
> SQL 2K with SP3 and Windows 2K with latest SP.
> Thank you,
> Larry
>|||I saw your postings about defragging indexes and wondered if anyone
would be willing to help me out. I work for a software house that has
developed database defragmentation software for SQL Server, and we are
looking for beta-testers with DBA knowledge of SQL Server and an
awareness of fragmentation. Our software can defrag selected indexes on
a chosen table, one after the other, and has scheduling facilities
built in.
If anyone is interested in beta-testing it, I would like to hear from
you as it is close to launch. My company is willing to offer a free
copy of the software to anyone who can provide genuine and useful
feedback. Please post back if you are interested!
Regards,
Martin
Larry Smith wrote:
> I am periodically getting the complaints that SQL Server is slow. As a
> performance improvements I decided to run the defrag utility. Never run on
> this server from years. How to know that defrag need to be run? How to pro
ve
> there is an excessive physical file fragmentation on a Windows 2K?
> SQL 2K with SP3 and Windows 2K with latest SP.
> Thank you,
> Larry
performance improvements I decided to run the defrag utility. Never run on
this server from years. How to know that defrag need to be run? How to prove
there is an excessive physical file fragmentation on a Windows 2K?
SQL 2K with SP3 and Windows 2K with latest SP.
Thank you,
LarryLarry,
Check out:
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
HTH
Jerry
"Larry Smith" <LSmith_Melbourne@.hotmail.com> wrote in message
news:OBRu9MgxFHA.2504@.TK2MSFTNGP10.phx.gbl...
>I am periodically getting the complaints that SQL Server is slow. As a
> performance improvements I decided to run the defrag utility. Never run on
> this server from years. How to know that defrag need to be run? How to
> prove
> there is an excessive physical file fragmentation on a Windows 2K?
> SQL 2K with SP3 and Windows 2K with latest SP.
> Thank you,
> Larry
>|||If you refer to the file system, you wouldn't need defrag if you stay away f
rom constantly growing
and shrinking the database files. Keep them at a stable size and increase th
e size only when needed.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Larry Smith" <LSmith_Melbourne@.hotmail.com> wrote in message
news:OBRu9MgxFHA.2504@.TK2MSFTNGP10.phx.gbl...
>I am periodically getting the complaints that SQL Server is slow. As a
> performance improvements I decided to run the defrag utility. Never run on
> this server from years. How to know that defrag need to be run? How to pro
ve
> there is an excessive physical file fragmentation on a Windows 2K?
> SQL 2K with SP3 and Windows 2K with latest SP.
> Thank you,
> Larry
>|||I saw your postings about defragging indexes and wondered if anyone
would be willing to help me out. I work for a software house that has
developed database defragmentation software for SQL Server, and we are
looking for beta-testers with DBA knowledge of SQL Server and an
awareness of fragmentation. Our software can defrag selected indexes on
a chosen table, one after the other, and has scheduling facilities
built in.
If anyone is interested in beta-testing it, I would like to hear from
you as it is close to launch. My company is willing to offer a free
copy of the software to anyone who can provide genuine and useful
feedback. Please post back if you are interested!
Regards,
Martin
Larry Smith wrote:
> I am periodically getting the complaints that SQL Server is slow. As a
> performance improvements I decided to run the defrag utility. Never run on
> this server from years. How to know that defrag need to be run? How to pro
ve
> there is an excessive physical file fragmentation on a Windows 2K?
> SQL 2K with SP3 and Windows 2K with latest SP.
> Thank you,
> Larry
Defrag on SQL Server.....
I am periodically getting the complaints that SQL Server is slow. As a
performance improvements I decided to run the defrag utility. Never run on
this server from years. How to know that defrag need to be run? How to prove
there is an excessive physical file fragmentation on a Windows 2K?
SQL 2K with SP3 and Windows 2K with latest SP.
Thank you,
LarryLarry,
Check out:
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
HTH
Jerry
"Larry Smith" <LSmith_Melbourne@.hotmail.com> wrote in message
news:OBRu9MgxFHA.2504@.TK2MSFTNGP10.phx.gbl...
>I am periodically getting the complaints that SQL Server is slow. As a
> performance improvements I decided to run the defrag utility. Never run on
> this server from years. How to know that defrag need to be run? How to
> prove
> there is an excessive physical file fragmentation on a Windows 2K?
> SQL 2K with SP3 and Windows 2K with latest SP.
> Thank you,
> Larry
>|||If you refer to the file system, you wouldn't need defrag if you stay away from constantly growing
and shrinking the database files. Keep them at a stable size and increase the size only when needed.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Larry Smith" <LSmith_Melbourne@.hotmail.com> wrote in message
news:OBRu9MgxFHA.2504@.TK2MSFTNGP10.phx.gbl...
>I am periodically getting the complaints that SQL Server is slow. As a
> performance improvements I decided to run the defrag utility. Never run on
> this server from years. How to know that defrag need to be run? How to prove
> there is an excessive physical file fragmentation on a Windows 2K?
> SQL 2K with SP3 and Windows 2K with latest SP.
> Thank you,
> Larry
>|||I saw your postings about defragging indexes and wondered if anyone
would be willing to help me out. I work for a software house that has
developed database defragmentation software for SQL Server, and we are
looking for beta-testers with DBA knowledge of SQL Server and an
awareness of fragmentation. Our software can defrag selected indexes on
a chosen table, one after the other, and has scheduling facilities
built in.
If anyone is interested in beta-testing it, I would like to hear from
you as it is close to launch. My company is willing to offer a free
copy of the software to anyone who can provide genuine and useful
feedback. Please post back if you are interested!
Regards,
Martin
Larry Smith wrote:
> I am periodically getting the complaints that SQL Server is slow. As a
> performance improvements I decided to run the defrag utility. Never run on
> this server from years. How to know that defrag need to be run? How to prove
> there is an excessive physical file fragmentation on a Windows 2K?
> SQL 2K with SP3 and Windows 2K with latest SP.
> Thank you,
> Larry
performance improvements I decided to run the defrag utility. Never run on
this server from years. How to know that defrag need to be run? How to prove
there is an excessive physical file fragmentation on a Windows 2K?
SQL 2K with SP3 and Windows 2K with latest SP.
Thank you,
LarryLarry,
Check out:
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
HTH
Jerry
"Larry Smith" <LSmith_Melbourne@.hotmail.com> wrote in message
news:OBRu9MgxFHA.2504@.TK2MSFTNGP10.phx.gbl...
>I am periodically getting the complaints that SQL Server is slow. As a
> performance improvements I decided to run the defrag utility. Never run on
> this server from years. How to know that defrag need to be run? How to
> prove
> there is an excessive physical file fragmentation on a Windows 2K?
> SQL 2K with SP3 and Windows 2K with latest SP.
> Thank you,
> Larry
>|||If you refer to the file system, you wouldn't need defrag if you stay away from constantly growing
and shrinking the database files. Keep them at a stable size and increase the size only when needed.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Larry Smith" <LSmith_Melbourne@.hotmail.com> wrote in message
news:OBRu9MgxFHA.2504@.TK2MSFTNGP10.phx.gbl...
>I am periodically getting the complaints that SQL Server is slow. As a
> performance improvements I decided to run the defrag utility. Never run on
> this server from years. How to know that defrag need to be run? How to prove
> there is an excessive physical file fragmentation on a Windows 2K?
> SQL 2K with SP3 and Windows 2K with latest SP.
> Thank you,
> Larry
>|||I saw your postings about defragging indexes and wondered if anyone
would be willing to help me out. I work for a software house that has
developed database defragmentation software for SQL Server, and we are
looking for beta-testers with DBA knowledge of SQL Server and an
awareness of fragmentation. Our software can defrag selected indexes on
a chosen table, one after the other, and has scheduling facilities
built in.
If anyone is interested in beta-testing it, I would like to hear from
you as it is close to launch. My company is willing to offer a free
copy of the software to anyone who can provide genuine and useful
feedback. Please post back if you are interested!
Regards,
Martin
Larry Smith wrote:
> I am periodically getting the complaints that SQL Server is slow. As a
> performance improvements I decided to run the defrag utility. Never run on
> this server from years. How to know that defrag need to be run? How to prove
> there is an excessive physical file fragmentation on a Windows 2K?
> SQL 2K with SP3 and Windows 2K with latest SP.
> Thank you,
> Larry
Defrag file problem
SQL Server 2000, Windows Server 2003.
We have a 24GB data file with 40GB free space. However even if we stop SQL
server and run the Microsoft Defragmenter it says it cannot defrag the file.
Would I be correct in thinking that if we backed up the database, deleted
and recreated the database making it's starting size 24GB, then restored the
backup file, this would create an unfragmented file.
Any other suggestions?
Thanks,
Adrian.
Adrian
try contig.exe from sysinternals.
www.sysinternals.com
It gives you the option to defrag single files and I've used it
succesfully in the past even on open database files.
Markus
|||Markus,
Thanks. That's a useful link. I will let you know how I get on.
Adrian
"MarkusB" <m.bohse@.quest-consultants.com> wrote in message
news:1162998868.542722.265490@.i42g2000cwa.googlegr oups.com...
> Adrian
> try contig.exe from sysinternals.
> www.sysinternals.com
> It gives you the option to defrag single files and I've used it
> succesfully in the past even on open database files.
> Markus
>
We have a 24GB data file with 40GB free space. However even if we stop SQL
server and run the Microsoft Defragmenter it says it cannot defrag the file.
Would I be correct in thinking that if we backed up the database, deleted
and recreated the database making it's starting size 24GB, then restored the
backup file, this would create an unfragmented file.
Any other suggestions?
Thanks,
Adrian.
Adrian
try contig.exe from sysinternals.
www.sysinternals.com
It gives you the option to defrag single files and I've used it
succesfully in the past even on open database files.
Markus
|||Markus,
Thanks. That's a useful link. I will let you know how I get on.
Adrian
"MarkusB" <m.bohse@.quest-consultants.com> wrote in message
news:1162998868.542722.265490@.i42g2000cwa.googlegr oups.com...
> Adrian
> try contig.exe from sysinternals.
> www.sysinternals.com
> It gives you the option to defrag single files and I've used it
> succesfully in the past even on open database files.
> Markus
>
Defrag file problem
SQL Server 2000, Windows Server 2003.
We have a 24GB data file with 40GB free space. However even if we stop SQL
server and run the Microsoft Defragmenter it says it cannot defrag the file.
Would I be correct in thinking that if we backed up the database, deleted
and recreated the database making it's starting size 24GB, then restored the
backup file, this would create an unfragmented file.
Any other suggestions?
Thanks,
Adrian.Adrian
try contig.exe from sysinternals.
www.sysinternals.com
It gives you the option to defrag single files and I've used it
succesfully in the past even on open database files.
Markus|||Markus,
Thanks. That's a useful link. I will let you know how I get on.
Adrian
"MarkusB" <m.bohse@.quest-consultants.com> wrote in message
news:1162998868.542722.265490@.i42g2000cwa.googlegroups.com...
> Adrian
> try contig.exe from sysinternals.
> www.sysinternals.com
> It gives you the option to defrag single files and I've used it
> succesfully in the past even on open database files.
> Markus
>
We have a 24GB data file with 40GB free space. However even if we stop SQL
server and run the Microsoft Defragmenter it says it cannot defrag the file.
Would I be correct in thinking that if we backed up the database, deleted
and recreated the database making it's starting size 24GB, then restored the
backup file, this would create an unfragmented file.
Any other suggestions?
Thanks,
Adrian.Adrian
try contig.exe from sysinternals.
www.sysinternals.com
It gives you the option to defrag single files and I've used it
succesfully in the past even on open database files.
Markus|||Markus,
Thanks. That's a useful link. I will let you know how I get on.
Adrian
"MarkusB" <m.bohse@.quest-consultants.com> wrote in message
news:1162998868.542722.265490@.i42g2000cwa.googlegroups.com...
> Adrian
> try contig.exe from sysinternals.
> www.sysinternals.com
> It gives you the option to defrag single files and I've used it
> succesfully in the past even on open database files.
> Markus
>
Defrag file problem
SQL Server 2000, Windows Server 2003.
We have a 24GB data file with 40GB free space. However even if we stop SQL
server and run the Microsoft Defragmenter it says it cannot defrag the file.
Would I be correct in thinking that if we backed up the database, deleted
and recreated the database making it's starting size 24GB, then restored the
backup file, this would create an unfragmented file.
Any other suggestions?
Thanks,
Adrian.Adrian
try contig.exe from sysinternals.
www.sysinternals.com
It gives you the option to defrag single files and I've used it
succesfully in the past even on open database files.
Markus|||Markus,
Thanks. That's a useful link. I will let you know how I get on.
Adrian
"MarkusB" <m.bohse@.quest-consultants.com> wrote in message
news:1162998868.542722.265490@.i42g2000cwa.googlegroups.com...
> Adrian
> try contig.exe from sysinternals.
> www.sysinternals.com
> It gives you the option to defrag single files and I've used it
> succesfully in the past even on open database files.
> Markus
>
We have a 24GB data file with 40GB free space. However even if we stop SQL
server and run the Microsoft Defragmenter it says it cannot defrag the file.
Would I be correct in thinking that if we backed up the database, deleted
and recreated the database making it's starting size 24GB, then restored the
backup file, this would create an unfragmented file.
Any other suggestions?
Thanks,
Adrian.Adrian
try contig.exe from sysinternals.
www.sysinternals.com
It gives you the option to defrag single files and I've used it
succesfully in the past even on open database files.
Markus|||Markus,
Thanks. That's a useful link. I will let you know how I get on.
Adrian
"MarkusB" <m.bohse@.quest-consultants.com> wrote in message
news:1162998868.542722.265490@.i42g2000cwa.googlegroups.com...
> Adrian
> try contig.exe from sysinternals.
> www.sysinternals.com
> It gives you the option to defrag single files and I've used it
> succesfully in the past even on open database files.
> Markus
>
Friday, March 9, 2012
Definition of Object Has Changed Since it was last compiles
All I'm having a weird problem.. I have 2 stored procedures that run 98% of the time without any issue but inconsistenly through the following error.
'The definition of object 'proc name goes here' has changed since it was compiled'
We have adding 'with recompile' to the proc but we still get this error - but not consistently. The stored proc is not changing nor is the table structure of any of the objects that are being used in the sp. Any idea to trace down the why this is happening or what objecte it thinks is changing? Let me know your thoughts.
Ken
We've started experiencing this, except:- It's occurring 100% of the time on SQL 2K5, for a particular data set, but not for another data set on the same schema.
- It never occurred in SQL 2K.
- It only occurs on SQL 2K5 (w/ DB in 2K compatibility mode)
My first reaction, for our case, is that it's a broken 2K5/2K compatibility issue. We're doing something pretty shady - disabling a trigger on table B from within a trigger firing on table A. IOW:
Trigger A, Table A:
- Disable trigger B on table B
- UPDATE table B
- Re-enable trigger B
So I'm guessing SQL 2K5 is finally calling us out on this. But I'd still prefer a quick fix to rewriting the triggers. Have you had any luck with your issue?
Saturday, February 25, 2012
Defaulting Parameters based on time of day?
I need to set up some parameters which are based on the time of day. For instance, night shift vs/ day shift. If I run the report during the day, default to the day shift criteria (5 am to 5 pm), and likewise for the night shift (5 pm to 5 am). On the night shift, I also need to accomodate for the change in dates as well. Is this even possible in a single report? HELP!!
Thanks!
Does it really need to be parameters, or could you just check the time in your SQL or report code?
Subscribe to:
Posts (Atom)