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 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 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