DBCC Shrinkfile

Dear All,
I have just tried to shrink a file, both in Enterprise
Manager and in Query Analyser.
Within EM it hung.
Within QA it said it shrinked it but when I looked at the
size of the file it was still the same size.
The code I used in QA was
dbcc shrinkfile (NAMEOFFILE, TRUNCATEONLY).
Can anyone gice me pointers ?
Thanks
JBy any chance, are you trying to shrink the tempdb data file? Sometimes,
that fails because there are temporary worktables in there that are still
being used, though QA would report as if the file had been shrunk.
--
Regards
Ray Mond|||No, I am shrinking a non system, non tempdb file.
Thanks
J
>--Original Message--
>By any chance, are you trying to shrink the tempdb data
file? Sometimes,
>that fails because there are temporary worktables in
there that are still
>being used, though QA would report as if the file had
been shrunk.
>--
>Regards
>Ray Mond
>
>.
>|||Hi,
Please look into the below article from MS. This will help you out in
shrinking the TX Log.
http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
Thanks
Hari
MCDBA
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:0b8601c3d9bb$08533b50$a101280a@.phx.gbl...
> Dear All,
> I have just tried to shrink a file, both in Enterprise
> Manager and in Query Analyser.
> Within EM it hung.
> Within QA it said it shrinked it but when I looked at the
> size of the file it was still the same size.
> The code I used in QA was
> dbcc shrinkfile (NAMEOFFILE, TRUNCATEONLY).
> Can anyone gice me pointers ?
> Thanks
> J|||Why don't you try DBCC SHRINKFILE(NAMEOFFILE, somesize) ? Your data might
be residing on the end of the file, which prevents any significant
truncation.
--
Regards
Ray Mond
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:07ae01c3d9c0$9415a2e0$a501280a@.phx.gbl...
> No, I am shrinking a non system, non tempdb file.
> Thanks
> J
>
> >--Original Message--
> >By any chance, are you trying to shrink the tempdb data
> file? Sometimes,
> >that fails because there are temporary worktables in
> there that are still
> >being used, though QA would report as if the file had
> been shrunk.
> >
> >--
> >Regards
> >Ray Mond
> >
> >
> >.
> >|||THe command you are using with the Truncateonly parameter does NOT move any
of the data from the end of the file to the front of the file, it only
returns any unused end portion of the file... instead try something like
dbcc shrinkfile(nameoffile, targetfilesizeinMB)
this command will move data from the end to the front , then truncate all of
the unused portion ...
--
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's
community of SQL Server professionals.
www.sqlpass.org
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:0b8601c3d9bb$08533b50$a101280a@.phx.gbl...
> Dear All,
> I have just tried to shrink a file, both in Enterprise
> Manager and in Query Analyser.
> Within EM it hung.
> Within QA it said it shrinked it but when I looked at the
> size of the file it was still the same size.
> The code I used in QA was
> dbcc shrinkfile (NAMEOFFILE, TRUNCATEONLY).
> Can anyone gice me pointers ?
> Thanks
> J|||Thanks Guys,
It worked using the dbcc shrinkfile(FileName, 4670) option
a couple of you guys sugested.
The main problem here was time. In this case it took over
20 minutes to shrink, and I was about to cancel it when it
worked ;)
Anyway thanks for that.
Question though, is there an automated way without using
DBCC Shrinkfile of moving all the data to the beginning of
the datafile before a DBCC Shrinkfile ?
Again thanks for your help
J
>--Original Message--
>THe command you are using with the Truncateonly parameter
does NOT move any
>of the data from the end of the file to the front of the
file, it only
>returns any unused end portion of the file... instead
try something like
>dbcc shrinkfile(nameoffile, targetfilesizeinMB)
>this command will move data from the end to the front ,
then truncate all of
>the unused portion ...
>--
>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's
>community of SQL Server professionals.
>www.sqlpass.org
>
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0b8601c3d9bb$08533b50$a101280a@.phx.gbl...
>> Dear All,
>> I have just tried to shrink a file, both in Enterprise
>> Manager and in Query Analyser.
>> Within EM it hung.
>> Within QA it said it shrinked it but when I looked at
the
>> size of the file it was still the same size.
>> The code I used in QA was
>> dbcc shrinkfile (NAMEOFFILE, TRUNCATEONLY).
>> Can anyone gice me pointers ?
>> Thanks
>> J
>
>.
>|||> Question though, is there an automated way without using
> DBCC Shrinkfile of moving all the data to the beginning of
> the datafile before a DBCC Shrinkfile ?
No - that's what shrink does.
Also, regarding your 20 minutes comment, the runtime of shrink is dependent
on a bunch of things, including how much you ask the file to shrink, the
distribution of free space at the start of the file, the speed of your IO
system, and most importantly, the amount of concurrent activity on the
system that may block shrink. Remember that shrink is an online operation
and so does not block concurrent activity - this means you can let it run
longer than your maintenance window as long as you can cope with the small
drop in workload throughput from having shrink working in the database.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:0a2501c3d9e1$0740c4a0$a601280a@.phx.gbl...
> Thanks Guys,
> It worked using the dbcc shrinkfile(FileName, 4670) option
> a couple of you guys sugested.
> The main problem here was time. In this case it took over
> 20 minutes to shrink, and I was about to cancel it when it
> worked ;)
> Anyway thanks for that.
> Question though, is there an automated way without using
> DBCC Shrinkfile of moving all the data to the beginning of
> the datafile before a DBCC Shrinkfile ?
> Again thanks for your help
> J
>
>
> >--Original Message--
> >THe command you are using with the Truncateonly parameter
> does NOT move any
> >of the data from the end of the file to the front of the
> file, it only
> >returns any unused end portion of the file... instead
> try something like
> >
> >dbcc shrinkfile(nameoffile, targetfilesizeinMB)
> >
> >this command will move data from the end to the front ,
> then truncate all of
> >the unused portion ...
> >
> >--
> >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's
> >community of SQL Server professionals.
> >www.sqlpass.org
> >
> >
> >"Julie" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:0b8601c3d9bb$08533b50$a101280a@.phx.gbl...
> >> Dear All,
> >>
> >> I have just tried to shrink a file, both in Enterprise
> >> Manager and in Query Analyser.
> >>
> >> Within EM it hung.
> >>
> >> Within QA it said it shrinked it but when I looked at
> the
> >> size of the file it was still the same size.
> >>
> >> The code I used in QA was
> >>
> >> dbcc shrinkfile (NAMEOFFILE, TRUNCATEONLY).
> >>
> >> Can anyone gice me pointers ?
> >>
> >> Thanks
> >> J
> >
> >
> >.
> >|||Thanks Paul
>--Original Message--
>> Question though, is there an automated way without using
>> DBCC Shrinkfile of moving all the data to the beginning
of
>> the datafile before a DBCC Shrinkfile ?
>No - that's what shrink does.
>Also, regarding your 20 minutes comment, the runtime of
shrink is dependent
>on a bunch of things, including how much you ask the file
to shrink, the
>distribution of free space at the start of the file, the
speed of your IO
>system, and most importantly, the amount of concurrent
activity on the
>system that may block shrink. Remember that shrink is an
online operation
>and so does not block concurrent activity - this means
you can let it run
>longer than your maintenance window as long as you can
cope with the small
>drop in workload throughput from having shrink working in
the database.
>Regards.
>--
>Paul Randal
>Dev Lead, Microsoft SQL Server Storage Engine
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0a2501c3d9e1$0740c4a0$a601280a@.phx.gbl...
>> Thanks Guys,
>> It worked using the dbcc shrinkfile(FileName, 4670)
option
>> a couple of you guys sugested.
>> The main problem here was time. In this case it took
over
>> 20 minutes to shrink, and I was about to cancel it when
it
>> worked ;)
>> Anyway thanks for that.
>> Question though, is there an automated way without using
>> DBCC Shrinkfile of moving all the data to the beginning
of
>> the datafile before a DBCC Shrinkfile ?
>> Again thanks for your help
>> J
>>
>>
>> >--Original Message--
>> >THe command you are using with the Truncateonly
parameter
>> does NOT move any
>> >of the data from the end of the file to the front of
the
>> file, it only
>> >returns any unused end portion of the file... instead
>> try something like
>> >
>> >dbcc shrinkfile(nameoffile, targetfilesizeinMB)
>> >
>> >this command will move data from the end to the front ,
>> then truncate all of
>> >the unused portion ...
>> >
>> >--
>> >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's
>> >community of SQL Server professionals.
>> >www.sqlpass.org
>> >
>> >
>> >"Julie" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:0b8601c3d9bb$08533b50$a101280a@.phx.gbl...
>> >> Dear All,
>> >>
>> >> I have just tried to shrink a file, both in
Enterprise
>> >> Manager and in Query Analyser.
>> >>
>> >> Within EM it hung.
>> >>
>> >> Within QA it said it shrinked it but when I looked at
>> the
>> >> size of the file it was still the same size.
>> >>
>> >> The code I used in QA was
>> >>
>> >> dbcc shrinkfile (NAMEOFFILE, TRUNCATEONLY).
>> >>
>> >> Can anyone gice me pointers ?
>> >>
>> >> Thanks
>> >> J
>> >
>> >
>> >.
>> >
>
>.
>

DBCC Shrinkfile

Dear All,
I have just tried to shrink a file, both in Enterprise
Manager and in Query Analyser.
Within EM it hung.
Within QA it said it shrinked it but when I looked at the
size of the file it was still the same size.
The code I used in QA was
dbcc shrinkfile (NAMEOFFILE, TRUNCATEONLY).
Can anyone gice me pointers ?
Thanks
JBy any chance, are you trying to shrink the tempdb data file? Sometimes,
that fails because there are temporary worktables in there that are still
being used, though QA would report as if the file had been shrunk.
Regards
Ray Mond|||No, I am shrinking a non system, non tempdb file.
Thanks
J

quote:

---

>--Original Message--
>By any chance, are you trying to shrink the tempdb data

---

file? Sometimes,

quote:

---

>that fails because there are temporary worktables in

---

there that are still

quote:

---

>being used, though QA would report as if the file had

---

been shrunk.

quote:

---

>--
>Regards
>Ray Mond
>
>.
>

---

|||Hi,
Please look into the below article from MS. This will help you out in
shrinking the TX Log.
http://support.microsoft.com/defaul...kb;en-us;272318
Thanks
Hari
MCDBA
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:0b8601c3d9bb$08533b50$a101280a@.phx.gbl...

quote:

---

> Dear All,
> I have just tried to shrink a file, both in Enterprise
> Manager and in Query Analyser.
> Within EM it hung.
> Within QA it said it shrinked it but when I looked at the
> size of the file it was still the same size.
> The code I used in QA was
> dbcc shrinkfile (NAMEOFFILE, TRUNCATEONLY).
> Can anyone gice me pointers ?
> Thanks
> J

---

|||Why don't you try DBCC SHRINKFILE(NAMEOFFILE, somesize) ? Your data might
be residing on the end of the file, which prevents any significant
truncation.
Regards
Ray Mond
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:07ae01c3d9c0$9415a2e0$a501280a@.phx.gbl...[QUOTE]
> No, I am shrinking a non system, non tempdb file.
> Thanks
> J
>
> file? Sometimes,
> there that are still
> been shrunk.|||THe command you are using with the Truncateonly parameter does NOT move any
of the data from the end of the file to the front of the file, it only
returns any unused end portion of the file... instead try something like
dbcc shrinkfile(nameoffile, targetfilesizeinMB)
this command will move data from the end to the front , then truncate all of
the unused portion ...
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's
community of SQL Server professionals.
www.sqlpass.org
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:0b8601c3d9bb$08533b50$a101280a@.phx.gbl...

quote:

---

> Dear All,
> I have just tried to shrink a file, both in Enterprise
> Manager and in Query Analyser.
> Within EM it hung.
> Within QA it said it shrinked it but when I looked at the
> size of the file it was still the same size.
> The code I used in QA was
> dbcc shrinkfile (NAMEOFFILE, TRUNCATEONLY).
> Can anyone gice me pointers ?
> Thanks
> J

---

|||Thanks Guys,
It worked using the dbcc shrinkfile(FileName, 4670) option
a couple of you guys sugested.
The main problem here was time. In this case it took over
20 minutes to shrink, and I was about to cancel it when it
worked ;)
Anyway thanks for that.
Question though, is there an automated way without using
DBCC Shrinkfile of moving all the data to the beginning of
the datafile before a DBCC Shrinkfile ?
Again thanks for your help
J

quote:

---

>--Original Message--
>THe command you are using with the Truncateonly parameter

---

does NOT move any

quote:

---

>of the data from the end of the file to the front of the

---

file, it only

quote:

---

>returns any unused end portion of the file... instead

---

try something like

quote:

---

>dbcc shrinkfile(nameoffile, targetfilesizeinMB)
>this command will move data from the end to the front ,

---

then truncate all of

quote:

---

>the unused portion ...
>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Computer Education Services Corporation (CESC),

---

Charlotte, NC

quote:

---

>www.computeredservices.com
>(Please respond only to the newsgroups.)
>I support the Professional Association of SQL Server

---

(PASS) and it's

quote:

---

>community of SQL Server professionals.
>www.sqlpass.org
>
>"Julie" <anonymous@.discussions.microsoft.com> wrote in

---

message

quote:

---

>news:0b8601c3d9bb$08533b50$a101280a@.phx.gbl...
the[QUOTE]
>
>.
>

---

|||> Question though, is there an automated way without using

quote:

---

> DBCC Shrinkfile of moving all the data to the beginning of
> the datafile before a DBCC Shrinkfile ?

---

No - that's what shrink does.
Also, regarding your 20 minutes comment, the runtime of shrink is dependent
on a bunch of things, including how much you ask the file to shrink, the
distribution of free space at the start of the file, the speed of your IO
system, and most importantly, the amount of concurrent activity on the
system that may block shrink. Remember that shrink is an online operation
and so does not block concurrent activity - this means you can let it run
longer than your maintenance window as long as you can cope with the small
drop in workload throughput from having shrink working in the database.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:0a2501c3d9e1$0740c4a0$a601280a@.phx.gbl...[QUOTE]
> Thanks Guys,
> It worked using the dbcc shrinkfile(FileName, 4670) option
> a couple of you guys sugested.
> The main problem here was time. In this case it took over
> 20 minutes to shrink, and I was about to cancel it when it
> worked ;)
> Anyway thanks for that.
> Question though, is there an automated way without using
> DBCC Shrinkfile of moving all the data to the beginning of
> the datafile before a DBCC Shrinkfile ?
> Again thanks for your help
> J
>
>
> does NOT move any
> file, it only
> try something like
> then truncate all of
> Charlotte, NC
> (PASS) and it's
> message
> the|||Thanks Paul

quote:

---

>--Original Message--
of[QUOTE]
>No - that's what shrink does.
>Also, regarding your 20 minutes comment, the runtime of

---

shrink is dependent

quote:

---

>on a bunch of things, including how much you ask the file

---

to shrink, the

quote:

---

>distribution of free space at the start of the file, the

---

speed of your IO

quote:

---

>system, and most importantly, the amount of concurrent

---

activity on the

quote:

---

>system that may block shrink. Remember that shrink is an

---

online operation

quote:

---

>and so does not block concurrent activity - this means

---

you can let it run

quote:

---

>longer than your maintenance window as long as you can

---

cope with the small

quote:

---

>drop in workload throughput from having shrink working in

---

the database.

quote:

---

>Regards.
>--
>Paul Randal
>Dev Lead, Microsoft SQL Server Storage Engine
>This posting is provided "AS IS" with no warranties, and

---

confers no rights.

quote:

---

>"Julie" <anonymous@.discussions.microsoft.com> wrote in

---

message

quote:

---

>news:0a2501c3d9e1$0740c4a0$a601280a@.phx.gbl...
option[QUOTE]
over[QUOTE]
it[QUOTE]
of[QUOTE]
parameter[QUOTE]
the[QUOTE]
Enterprise[QUOTE]
>
>.
>

---

DBCC MEMORYSTATUS

Dear all,
How can i execute DBCC MEMORYSTATUS command on Linked server or Remote
Computer.
I dont want to connect my Query Analyser to the server i want to get
information.
thanx
Doller
EXEC TIBWORK.master.dbo.sp_executesql N'DBCC MEMORYSTATUS'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"doller" <sufianarif@.gmail.com> wrote in message
news:1142663795.418452.123220@.e56g2000cwe.googlegr oups.com...
> Dear all,
> How can i execute DBCC MEMORYSTATUS command on Linked server or Remote
> Computer.
> I dont want to connect my Query Analyser to the server i want to get
> information.
> thanx
> Doller
>
|||HI Thanx

DBCC MEMORYSTATUS

Dear all,
How can i execute DBCC MEMORYSTATUS command on Linked server or Remote
Computer.
I dont want to connect my Query Analyser to the server i want to get
information.
thanx
DollerEXEC TIBWORK.master.dbo.sp_executesql N'DBCC MEMORYSTATUS'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"doller" <sufianarif@.gmail.com> wrote in message
news:1142663795.418452.123220@.e56g2000cwe.googlegroups.com...
> Dear all,
> How can i execute DBCC MEMORYSTATUS command on Linked server or Remote
> Computer.
> I dont want to connect my Query Analyser to the server i want to get
> information.
> thanx
> Doller
>|||HI Thanx

DBCC MEMORYSTATUS

Dear all,
How can i execute DBCC MEMORYSTATUS command on Linked server or Remote
Computer.
I dont want to connect my Query Analyser to the server i want to get
information.
thanx
DollerEXEC TIBWORK.master.dbo.sp_executesql N'DBCC MEMORYSTATUS'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"doller" <sufianarif@.gmail.com> wrote in message
news:1142663795.418452.123220@.e56g2000cwe.googlegroups.com...
> Dear all,
> How can i execute DBCC MEMORYSTATUS command on Linked server or Remote
> Computer.
> I dont want to connect my Query Analyser to the server i want to get
> information.
> thanx
> Doller
>|||HI Thanx

DBCC INDEXDEFRAG.. no impact?

I see an impact.
Using Profiler..Duration, I can see client query duration times go from 16ms
to 9000ms and higher immediately after executing
INDEXDEFRAG(db,tlb,index)..and back down to 16ms after the dbcc is stopped.
BOL says that there will be no impact on SELECTS.
Anyone know what could be going on? There was no activity going on other
than the SELECTs during this test.
Thx,
Don
SQL2000Did you look to see what botlenecks there were during this? How many CPU's
do you have? Is your log file on a separate drive array than the data?
What is your cache hit ratio? And by the way running profiler on a busy
system will slow things down dramatically as well, use trace instead. But
my guess is your hardware configuration is not setup to handle much in the
way of CPU or I/O.
Andrew J. Kelly SQL MVP
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:B5BBE9FB-21B1-4870-85A8-EA9B11E3B90A@.microsoft.com...
>I see an impact.
> Using Profiler..Duration, I can see client query duration times go from
> 16ms
> to 9000ms and higher immediately after executing
> INDEXDEFRAG(db,tlb,index)..and back down to 16ms after the dbcc is
> stopped.
> BOL says that there will be no impact on SELECTS.
> Anyone know what could be going on? There was no activity going on other
> than the SELECTs during this test.
> Thx,
> Don
> SQL2000
>|||Unlike dbreindex, indexdefrag allows your queries to continue while it's
defragging. But there will be cost for shuffing index pages so it's *normal*
to see degration.
A slower query is still better than a completely blocked query. ;-)
-oj
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:B5BBE9FB-21B1-4870-85A8-EA9B11E3B90A@.microsoft.com...
>I see an impact.
> Using Profiler..Duration, I can see client query duration times go from
> 16ms
> to 9000ms and higher immediately after executing
> INDEXDEFRAG(db,tlb,index)..and back down to 16ms after the dbcc is
> stopped.
> BOL says that there will be no impact on SELECTS.
> Anyone know what could be going on? There was no activity going on other
> than the SELECTs during this test.
> Thx,
> Don
> SQL2000
>|||Also, check out:
http://support.microsoft.com/?kbid=907250&SD=tech
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OrUaLeITGHA.4300@.TK2MSFTNGP14.phx.gbl...
Did you look to see what botlenecks there were during this? How many CPU's
do you have? Is your log file on a separate drive array than the data?
What is your cache hit ratio? And by the way running profiler on a busy
system will slow things down dramatically as well, use trace instead. But
my guess is your hardware configuration is not setup to handle much in the
way of CPU or I/O.
Andrew J. Kelly SQL MVP
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:B5BBE9FB-21B1-4870-85A8-EA9B11E3B90A@.microsoft.com...
>I see an impact.
> Using Profiler..Duration, I can see client query duration times go from
> 16ms
> to 9000ms and higher immediately after executing
> INDEXDEFRAG(db,tlb,index)..and back down to 16ms after the dbcc is
> stopped.
> BOL says that there will be no impact on SELECTS.
> Anyone know what could be going on? There was no activity going on other
> than the SELECTs during this test.
> Thx,
> Don
> SQL2000
>

DBCC INDEXDEFRAG.. no impact?

I see an impact.
Using Profiler..Duration, I can see client query duration times go from 16ms
to 9000ms and higher immediately after executing
INDEXDEFRAG(db,tlb,index)..and back down to 16ms after the dbcc is stopped.
BOL says that there will be no impact on SELECTS.
Anyone know what could be going on? There was no activity going on other
than the SELECTs during this test.
Thx,
Don
SQL2000Did you look to see what botlenecks there were during this? How many CPU's
do you have? Is your log file on a separate drive array than the data?
What is your cache hit ratio? And by the way running profiler on a busy
system will slow things down dramatically as well, use trace instead. But
my guess is your hardware configuration is not setup to handle much in the
way of CPU or I/O.
--
Andrew J. Kelly SQL MVP
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:B5BBE9FB-21B1-4870-85A8-EA9B11E3B90A@.microsoft.com...
>I see an impact.
> Using Profiler..Duration, I can see client query duration times go from
> 16ms
> to 9000ms and higher immediately after executing
> INDEXDEFRAG(db,tlb,index)..and back down to 16ms after the dbcc is
> stopped.
> BOL says that there will be no impact on SELECTS.
> Anyone know what could be going on? There was no activity going on other
> than the SELECTs during this test.
> Thx,
> Don
> SQL2000
>|||Unlike dbreindex, indexdefrag allows your queries to continue while it's
defragging. But there will be cost for shuffing index pages so it's *normal*
to see degration.
A slower query is still better than a completely blocked query. ;-)
--
-oj
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:B5BBE9FB-21B1-4870-85A8-EA9B11E3B90A@.microsoft.com...
>I see an impact.
> Using Profiler..Duration, I can see client query duration times go from
> 16ms
> to 9000ms and higher immediately after executing
> INDEXDEFRAG(db,tlb,index)..and back down to 16ms after the dbcc is
> stopped.
> BOL says that there will be no impact on SELECTS.
> Anyone know what could be going on? There was no activity going on other
> than the SELECTs during this test.
> Thx,
> Don
> SQL2000
>|||Also, check out:
http://support.microsoft.com/?kbid=907250&SD=tech
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OrUaLeITGHA.4300@.TK2MSFTNGP14.phx.gbl...
Did you look to see what botlenecks there were during this? How many CPU's
do you have? Is your log file on a separate drive array than the data?
What is your cache hit ratio? And by the way running profiler on a busy
system will slow things down dramatically as well, use trace instead. But
my guess is your hardware configuration is not setup to handle much in the
way of CPU or I/O.
--
Andrew J. Kelly SQL MVP
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:B5BBE9FB-21B1-4870-85A8-EA9B11E3B90A@.microsoft.com...
>I see an impact.
> Using Profiler..Duration, I can see client query duration times go from
> 16ms
> to 9000ms and higher immediately after executing
> INDEXDEFRAG(db,tlb,index)..and back down to 16ms after the dbcc is
> stopped.
> BOL says that there will be no impact on SELECTS.
> Anyone know what could be going on? There was no activity going on other
> than the SELECTs during this test.
> Thx,
> Don
> SQL2000
>

DBCC INDEXDEFRAG.. no impact?

I see an impact.
Using Profiler..Duration, I can see client query duration times go from 16ms
to 9000ms and higher immediately after executing
INDEXDEFRAG(db,tlb,index)..and back down to 16ms after the dbcc is stopped.
BOL says that there will be no impact on SELECTS.
Anyone know what could be going on? There was no activity going on other
than the SELECTs during this test.
Thx,
Don
SQL2000
Did you look to see what botlenecks there were during this? How many CPU's
do you have? Is your log file on a separate drive array than the data?
What is your cache hit ratio? And by the way running profiler on a busy
system will slow things down dramatically as well, use trace instead. But
my guess is your hardware configuration is not setup to handle much in the
way of CPU or I/O.
Andrew J. Kelly SQL MVP
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:B5BBE9FB-21B1-4870-85A8-EA9B11E3B90A@.microsoft.com...
>I see an impact.
> Using Profiler..Duration, I can see client query duration times go from
> 16ms
> to 9000ms and higher immediately after executing
> INDEXDEFRAG(db,tlb,index)..and back down to 16ms after the dbcc is
> stopped.
> BOL says that there will be no impact on SELECTS.
> Anyone know what could be going on? There was no activity going on other
> than the SELECTs during this test.
> Thx,
> Don
> SQL2000
>
|||Unlike dbreindex, indexdefrag allows your queries to continue while it's
defragging. But there will be cost for shuffing index pages so it's *normal*
to see degration.
A slower query is still better than a completely blocked query. ;-)
-oj
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:B5BBE9FB-21B1-4870-85A8-EA9B11E3B90A@.microsoft.com...
>I see an impact.
> Using Profiler..Duration, I can see client query duration times go from
> 16ms
> to 9000ms and higher immediately after executing
> INDEXDEFRAG(db,tlb,index)..and back down to 16ms after the dbcc is
> stopped.
> BOL says that there will be no impact on SELECTS.
> Anyone know what could be going on? There was no activity going on other
> than the SELECTs during this test.
> Thx,
> Don
> SQL2000
>
|||Also, check out:
http://support.microsoft.com/?kbid=907250&SD=tech
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OrUaLeITGHA.4300@.TK2MSFTNGP14.phx.gbl...
Did you look to see what botlenecks there were during this? How many CPU's
do you have? Is your log file on a separate drive array than the data?
What is your cache hit ratio? And by the way running profiler on a busy
system will slow things down dramatically as well, use trace instead. But
my guess is your hardware configuration is not setup to handle much in the
way of CPU or I/O.
Andrew J. Kelly SQL MVP
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:B5BBE9FB-21B1-4870-85A8-EA9B11E3B90A@.microsoft.com...
>I see an impact.
> Using Profiler..Duration, I can see client query duration times go from
> 16ms
> to 9000ms and higher immediately after executing
> INDEXDEFRAG(db,tlb,index)..and back down to 16ms after the dbcc is
> stopped.
> BOL says that there will be no impact on SELECTS.
> Anyone know what could be going on? There was no activity going on other
> than the SELECTs during this test.
> Thx,
> Don
> SQL2000
>

dbcc freeprocache/dropcleanbuffers - please help

sql server 2000 Enterprise sp3a
I am running a loop in Query Analyzer as follows (pseudo coded) for some
testing
that I'm doing.
declare @.var int, @.id int
set @.var = 1
while (@.var < 4)
begin
begin tran
dbcc freeproccache
dbcc dropcleanbuffers
print 'start delete at : ' + cast (getdate() as varchar)
set @.id = 10
delete from mytable where @.id = 10
print 'end delete at : ' + cast (getdate() as varchar)
rollback tran
set @.var = @.var + 1
end
I'm getting weird execution times for each delete. For instance,
the first pass shows me an execution time of around 500ms which
I expect. The 2nd pass shows 500ms also. The 3rd pass shows me
3ms. I used set statistics io on to check the reads and the number
of physical reads drops. I don't know why the dbcc commands
aren't working. Is it the transaction? Any ideas? There is no
other activity on the server... it's my personal box. Please help.You could try CHECKPOINT before the DROPCLEANBUFFER. Note the word "clean", hence adding a
checkpoint will result in all pages clean in the database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dodo Lurker" <none@.noemailplease> wrote in message
news:P9OdnXgvFv_MppLYnZ2dnUVZ_tidnZ2d@.comcast.com...
> sql server 2000 Enterprise sp3a
> I am running a loop in Query Analyzer as follows (pseudo coded) for some
> testing
> that I'm doing.
>
> declare @.var int, @.id int
> set @.var = 1
> while (@.var < 4)
> begin
> begin tran
> dbcc freeproccache
> dbcc dropcleanbuffers
> print 'start delete at : ' + cast (getdate() as varchar)
> set @.id = 10
> delete from mytable where @.id = 10
> print 'end delete at : ' + cast (getdate() as varchar)
> rollback tran
> set @.var = @.var + 1
> end
>
> I'm getting weird execution times for each delete. For instance,
> the first pass shows me an execution time of around 500ms which
> I expect. The 2nd pass shows 500ms also. The 3rd pass shows me
> 3ms. I used set statistics io on to check the reads and the number
> of physical reads drops. I don't know why the dbcc commands
> aren't working. Is it the transaction? Any ideas? There is no
> other activity on the server... it's my personal box. Please help.
>|||Hi Tibor
Thank you
I ended up taking the transaction out (the begin transaction and rollback).
What I did instead
- inserted the rows into a temp table
- performed my delete
- re-inserted the rows
I repeated the delete/re-insert within the loop
When I did this, my problem went away. What do you think the transaction
was doing or not
doing? Would it be a log cache issue?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u6ZDWR72GHA.4632@.TK2MSFTNGP03.phx.gbl...
> You could try CHECKPOINT before the DROPCLEANBUFFER. Note the word
"clean", hence adding a
> checkpoint will result in all pages clean in the database.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dodo Lurker" <none@.noemailplease> wrote in message
> news:P9OdnXgvFv_MppLYnZ2dnUVZ_tidnZ2d@.comcast.com...
> > sql server 2000 Enterprise sp3a
> >
> > I am running a loop in Query Analyzer as follows (pseudo coded) for some
> > testing
> > that I'm doing.
> >
> >
> > declare @.var int, @.id int
> >
> > set @.var = 1
> > while (@.var < 4)
> > begin
> >
> > begin tran
> >
> > dbcc freeproccache
> > dbcc dropcleanbuffers
> >
> > print 'start delete at : ' + cast (getdate() as varchar)
> > set @.id = 10
> > delete from mytable where @.id = 10
> > print 'end delete at : ' + cast (getdate() as varchar)
> >
> > rollback tran
> >
> > set @.var = @.var + 1
> > end
> >
> >
> > I'm getting weird execution times for each delete. For instance,
> > the first pass shows me an execution time of around 500ms which
> > I expect. The 2nd pass shows 500ms also. The 3rd pass shows me
> > 3ms. I used set statistics io on to check the reads and the number
> > of physical reads drops. I don't know why the dbcc commands
> > aren't working. Is it the transaction? Any ideas? There is no
> > other activity on the server... it's my personal box. Please help.
> >
> >
>

DBCC DBREINDEX and diskspace

I have a large table that is fragmented and causing poor query
performance. I want to use DBCC DBREINDEX to defragment them. I used
it previously and noticed that I almost ran out of drive space. Then
the files compacted down to a managebale size. The table has grown
again and I fear that it might run out of drive space.
What happens if DBCC DBREINDEX maxes out the drive space. Does it
partially created the index? Does it just not run because it knows it
doesnt have enough space to complete? Or does it just use as much as
it can use without running out of space?It will fail and the index rebuild will be rolled back. You need 1.2X the
size of the data in your table in free space in order to use DBREINDEX. If
you cannot get more space, consider using DBCC INDEXDEFRAG.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"dramos" <dan.ramos@.gartner.com> wrote in message
news:641f8b7.0404190802.2e6ca0e1@.posting.google.com...
I have a large table that is fragmented and causing poor query
performance. I want to use DBCC DBREINDEX to defragment them. I used
it previously and noticed that I almost ran out of drive space. Then
the files compacted down to a managebale size. The table has grown
again and I fear that it might run out of drive space.
What happens if DBCC DBREINDEX maxes out the drive space. Does it
partially created the index? Does it just not run because it knows it
doesnt have enough space to complete? Or does it just use as much as
it can use without running out of space?|||This helps me out alot:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER procedure admin_DBCCDBReindex
as
declare @.Tablename varchar(128),@.db varchar(128),@.exec varchar(256)
set nocount on
set @.db = (select db_name())
declare @.Table table(TableName varchar(128))
insert into @.Table
select so.name
from sysobjects so
where so.name not like 'sys%' and
so.name not like 'dt%' and
so.name not like 'tmp%' and
so.xtype = 'u'
order by so.name
declare mycur cursor for (select TableName from @.Table)
open mycur
fetch next from mycur into @.TableName
while @.@.fetch_status = 0
begin
set @.exec = 'dbcc dbreindex (''' + @.db + '.' + 'dbo.' + @.TableName + ''')'
exec (@.exec)
BACKUP LOG [tmpReindex] TO [tmp]
WITH INIT , NOUNLOAD , NAME = N'tmp log backup', NOSKIP , STATS = 10,
NOFORMAT
fetch next from mycur into @.TableName
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
"dramos" <dan.ramos@.gartner.com> wrote in message
news:641f8b7.0404190802.2e6ca0e1@.posting.google.com...
> I have a large table that is fragmented and causing poor query
> performance. I want to use DBCC DBREINDEX to defragment them. I used
> it previously and noticed that I almost ran out of drive space. Then
> the files compacted down to a managebale size. The table has grown
> again and I fear that it might run out of drive space.
> What happens if DBCC DBREINDEX maxes out the drive space. Does it
> partially created the index? Does it just not run because it knows it
> doesnt have enough space to complete? Or does it just use as much as
> it can use without running out of space?|||As Tom suggests, consider DBCC INDEXDEFRAG. There's a whitepaper at
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
that explains the difference between the two, how to choose between them,
performance comparison etc.
Let us know if you have any more questions after reading the whitepaper.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"dramos" <dan.ramos@.gartner.com> wrote in message
news:641f8b7.0404190802.2e6ca0e1@.posting.google.com...
> I have a large table that is fragmented and causing poor query
> performance. I want to use DBCC DBREINDEX to defragment them. I used
> it previously and noticed that I almost ran out of drive space. Then
> the files compacted down to a managebale size. The table has grown
> again and I fear that it might run out of drive space.
> What happens if DBCC DBREINDEX maxes out the drive space. Does it
> partially created the index? Does it just not run because it knows it
> doesnt have enough space to complete? Or does it just use as much as
> it can use without running out of space?

DBCC DBREINDEX

I am trying to do something very simple, that is to
reindex table in a database with the following command in
SQL Query Analyser;
dbcc DBREINDEX (activitylog, '', 0)
I get the following message:
Server: Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'activitylog'.
Check sysobjects.
I know the table activitylog exists.
Please help !Perhaps incorrect owner? Make sure you include the owner...
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Amar" <amar.pabla@.wanneroo.wa.gov.au> wrote in message
news:1adbe01c387e8$ec52aef0$a601280a@.phx.gbl...
> I am trying to do something very simple, that is to
> reindex table in a database with the following command in
> SQL Query Analyser;
> dbcc DBREINDEX (activitylog, '', 0)
> I get the following message:
> Server: Msg 2501, Level 16, State 1, Line 1
> Could not find a table or object named 'activitylog'.
> Check sysobjects.
> I know the table activitylog exists.
> Please help !
>|||Amar
Try
use dbname
dbcc DBREINDEX (activitylog, '', 0)
If that does not work try
dbcc DBREINDEX (dbname.tableowner.activitylog, '', 0)
Obviouslt substituting your dbname and tableowner where
applicable.
Hope this helps
John

dbcc checktable(syslogs)

In SQL Server 6.5, after I run the following command, it has error in the
result. How can I fix the Table Corrupt problem ?
Thanks a lot !
Query :
dbcc checktable(syslogs)
go
checkpoint
go
Result :
Checking syslogs
Msg 2578, Level 16, State 1
The first page 764720 in Sysindexes for table 'syslogs' has previous page #
764721 in its page header. The previous page # should be NULL. Please check
Sysindexes.
Msg 2503, Level 16, State 1
Table Corrupt: Page linkage is not consistent; check the following pages:
(current page#=764720; page# pointing to this page=0; previous page#
indicated in this page=764721)
DBCC execution completed. If DBCC printed error messages, see your System
Administrator.You could try just emptying the log (but please consider your backup strategy when doing this).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Danny" <.> wrote in message news:ughx2LS0EHA.3596@.TK2MSFTNGP12.phx.gbl...
> In SQL Server 6.5, after I run the following command, it has error in the
> result. How can I fix the Table Corrupt problem ?
> Thanks a lot !
> Query :
> dbcc checktable(syslogs)
> go
> checkpoint
> go
> Result :
> Checking syslogs
> Msg 2578, Level 16, State 1
> The first page 764720 in Sysindexes for table 'syslogs' has previous page #
> 764721 in its page header. The previous page # should be NULL. Please check
> Sysindexes.
> Msg 2503, Level 16, State 1
> Table Corrupt: Page linkage is not consistent; check the following pages:
> (current page#=764720; page# pointing to this page=0; previous page#
> indicated in this page=764721)
> DBCC execution completed. If DBCC printed error messages, see your System
> Administrator.
>

dbcc checktable(syslogs)

In SQL Server 6.5, after I run the following command, it has error in the
result. How can I fix the Table Corrupt problem ?
Thanks a lot !
Query :
dbcc checktable(syslogs)
go
checkpoint
go
Result :
Checking syslogs
Msg 2578, Level 16, State 1
The first page 764720 in Sysindexes for table 'syslogs' has previous page #
764721 in its page header. The previous page # should be NULL. Please check
Sysindexes.
Msg 2503, Level 16, State 1
Table Corrupt: Page linkage is not consistent; check the following pages:
(current page#=764720; page# pointing to this page=0; previous page#
indicated in this page=764721)
DBCC execution completed. If DBCC printed error messages, see your System
Administrator.
You could try just emptying the log (but please consider your backup strategy when doing this).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Danny" <.> wrote in message news:ughx2LS0EHA.3596@.TK2MSFTNGP12.phx.gbl...
> In SQL Server 6.5, after I run the following command, it has error in the
> result. How can I fix the Table Corrupt problem ?
> Thanks a lot !
> Query :
> dbcc checktable(syslogs)
> go
> checkpoint
> go
> Result :
> Checking syslogs
> Msg 2578, Level 16, State 1
> The first page 764720 in Sysindexes for table 'syslogs' has previous page #
> 764721 in its page header. The previous page # should be NULL. Please check
> Sysindexes.
> Msg 2503, Level 16, State 1
> Table Corrupt: Page linkage is not consistent; check the following pages:
> (current page#=764720; page# pointing to this page=0; previous page#
> indicated in this page=764721)
> DBCC execution completed. If DBCC printed error messages, see your System
> Administrator.
>

dbcc checktable(syslogs)

In SQL Server 6.5, after I run the following command, it has error in the
result. How can I fix the Table Corrupt problem ?
Thanks a lot !
Query :
dbcc checktable(syslogs)
go
checkpoint
go
Result :
Checking syslogs
Msg 2578, Level 16, State 1
The first page 764720 in Sysindexes for table 'syslogs' has previous page #
764721 in its page header. The previous page # should be NULL. Please check
Sysindexes.
Msg 2503, Level 16, State 1
Table Corrupt: Page linkage is not consistent; check the following pages:
(current page#=764720; page# pointing to this page=0; previous page#
indicated in this page=764721)
DBCC execution completed. If DBCC printed error messages, see your System
Administrator.You could try just emptying the log (but please consider your backup strateg
y when doing this).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Danny" <.> wrote in message news:ughx2LS0EHA.3596@.TK2MSFTNGP12.phx.gbl...
> In SQL Server 6.5, after I run the following command, it has error in the
> result. How can I fix the Table Corrupt problem ?
> Thanks a lot !
> Query :
> dbcc checktable(syslogs)
> go
> checkpoint
> go
> Result :
> Checking syslogs
> Msg 2578, Level 16, State 1
> The first page 764720 in Sysindexes for table 'syslogs' has previous page
#
> 764721 in its page header. The previous page # should be NULL. Please chec
k
> Sysindexes.
> Msg 2503, Level 16, State 1
> Table Corrupt: Page linkage is not consistent; check the following pages:
> (current page#=764720; page# pointing to this page=0; previous page#
> indicated in this page=764721)
> DBCC execution completed. If DBCC printed error messages, see your System
> Administrator.
>

DBCC CheckIdent message value to application

Hi,
I'm trying to get the value that DBCC CheckIdent is returning, I could
see the message in the query analizer, but I'm unable to get this value into
my application since its not a results set or return value its just a
message, is there a way to accomplish it?, I want to give a way in my
application for the end user to view the next "Identity value" and they
should be able to change it, but I can't get that value.
Thanks in advance
Shloma Baum| I'm trying to get the value that DBCC CheckIdent is returning, I could
| see the message in the query analizer, but I'm unable to get this value
into
| my application since its not a results set or return value its just a
| message, is there a way to accomplish it?, I want to give a way in my
| application for the end user to view the next "Identity value" and they
| should be able to change it, but I can't get that value.
--
A workaround would be to use OSQL to pipe the result of DBCC CheckIdent
into a textfile and then get the application to read that file.
Another workaround is to select the @.@.identity into a variable immediately
after an insert operation.
But what you're trying to accomplish does not scale too well. In a busy
data entry environment, there's a high probability that you will insert
duplicate values.
Hope this helps,
--
Eric Cárdenas
SQL Server support

DBCC CHECKIDENT call never returns

I am calling DBCC CHECKIDENT from a VB App through ADO. The call is not
returning, even after a day. If I do the same call through Query Analyser it
takes about a minute to run.
Can anyone think of anything that might affect the use of DBCC CHECKIDENT. I
cant check for deadlocks, because the Current Activity tab in Enterprise
Manager is locked up, presumably because of a deadlock of some kind...
Thanks in advance,
Fred Forsyth.
Development Manager
The reason EM is "locked up" is certainly not due to a deadlock. SQL Server
automatically resolves deadlocks in a few seconds. It might be due to
blocking though. What permissions does the vb app have? To run DBCC
CHECKIDENT you must be sa or dbo.
Andrew J. Kelly SQL MVP
"Fred Forsyth" <fred.forsyth.nospam@.nospam.rcp.co.uk> wrote in message
news:%23nkjGb6BGHA.4076@.TK2MSFTNGP14.phx.gbl...
>I am calling DBCC CHECKIDENT from a VB App through ADO. The call is not
>returning, even after a day. If I do the same call through Query Analyser
>it takes about a minute to run.
> Can anyone think of anything that might affect the use of DBCC CHECKIDENT.
> I cant check for deadlocks, because the Current Activity tab in Enterprise
> Manager is locked up, presumably because of a deadlock of some kind...
> Thanks in advance,
> Fred Forsyth.
> Development Manager
>
|||Fred Forsyth (fred.forsyth.nospam@.nospam.rcp.co.uk) writes:
> I am calling DBCC CHECKIDENT from a VB App through ADO. The call is not
> returning, even after a day. If I do the same call through Query
> Analyser it takes about a minute to run.
> Can anyone think of anything that might affect the use of DBCC
> CHECKIDENT. I cant check for deadlocks, because the Current Activity tab
> in Enterprise Manager is locked up, presumably because of a deadlock of
> some kind...
Current Activity gets block if some process has create tables within a
transaction that still is open.
Use sp_who or sp_who2 to check for blocking. Check the Blk or BlkBy column.
If this column has a non-zero value, the spid on this row, is blocked by
spid in the Blk column.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
|||Some further investigation has shown that the VB process is blocked
waiting on a checkpoint to complete, and that there is a system process
that is attempting to checkpoint the database but is blocked by the VB
process. (I can this through select * from sysprocesses).
I suspect that the reason for the need to checkpoint is that the I
removed the log files from the databases, and then run the dbcc command
on them, and that this is what is causing the problems. I think my test
case is at fault, because in the real world the original log files
would be there (they are very big, hence the reason I removed them).
|||The log files are pretty important and it is never a good idea to remove
them.
Andrew J. Kelly SQL MVP
<fredforsyth@.gmail.com> wrote in message
news:1135870204.499678.310830@.f14g2000cwb.googlegr oups.com...
> Some further investigation has shown that the VB process is blocked
> waiting on a checkpoint to complete, and that there is a system process
> that is attempting to checkpoint the database but is blocked by the VB
> process. (I can this through select * from sysprocesses).
> I suspect that the reason for the need to checkpoint is that the I
> removed the log files from the databases, and then run the dbcc command
> on them, and that this is what is causing the problems. I think my test
> case is at fault, because in the real world the original log files
> would be there (they are very big, hence the reason I removed them).
>
|||(fredforsyth@.gmail.com) writes:
> Some further investigation has shown that the VB process is blocked
> waiting on a checkpoint to complete, and that there is a system process
> that is attempting to checkpoint the database but is blocked by the VB
> process. (I can this through select * from sysprocesses).
> I suspect that the reason for the need to checkpoint is that the I
> removed the log files from the databases,
You did what? Never, never do that again! If you remove the log file,
you are gambling with your database. There is a fair chance that you
will have to remove the data file as well, because it will not be
accessibe.

> I think my test case is at fault, because in the real world the original
> log files would be there (they are very big, hence the reason I removed
> them).
You can shrink a log file with DBCC SHRINKFILE, but don't do this with a
production database, unless the log file has grown because of some
one-off operation.
For a production database, you should also backup the transaction log
regulary. If you don't care about up-to-the-point recovery, you can set
the database in simple recovery mode.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Dbcc Checkdb

Hi All,

I have a job set up that runs the dbcc checkdb and puts the results into a table. Then I run a query against this table to check the results of dbcc checkdb. If there are errors, I get a page that lets me know that there are some problems. When I ran this job on my production server, the job failed stating that there are problems with data integrity. So I copied this db to a dev server and recreated the job just like I have in prod. The job completed successfully withou any errors. Can anybody tell me what to look at to figure out why it failed on the prod server?

Thanks.Can you post the errors from the production server? Also, which version of SQL Server is this?|||right click on the job. go to view job history. hit the checkbox for show job details. go to the step that is failing and get the error message.|||There are no error messages. When I run dbcc checkdb, I also put results in the text file. The text file was created but it was empty. I checked view job history and each step in this job was successfull. I am on SQL 2k.

This is a script that does the steps I mentioned before:

DECLARE @.checkdbcmd varchar(255)
DECLARE @.bcpcmd varchar(255)

CREATE TABLE ##Result (Check_Log VARCHAR(1000))

SET @.checkdbcmd = 'osql -S server -d TestDB -U user -P password
-Q "DBCC CHECKDB"'
SET @.bcpcmd = 'bcp "Select * from ##Result" queryout "S:\Test\IntegrityForTestDB.txt" -S server -U user -P password
-c'

INSERT ##Result(Check_Log)
EXEC master..xp_cmdshell @.checkdbcmd

EXEC master..xp_cmdshell @.bcpcmd

IF EXISTS (SELECT * FROM ##Result
WHERE Check_Log = 'CHECKDB found 0 allocation errors and 0 consistency errors in database')
RETURN
ELSE
EXEC master..xp_cmdshell 'mailsend -f test@.test.com -d test -smtp test -t test@.test.com -sub "DBCC CheckDB Error For TestDB" -m C:\Scripts\TestStuff\BkupMsg.txt ', no_output

drop table ##Result|||I fixed the problem, the server name was incorrect.

Thanks.