DBfile usage: following

Showing posts with label following. Show all posts

Thursday, March 29, 2012

dbcc shrinkfile

I have a DB with two transaction log files. I'd like have
one of them removed.
After running the following set of commands successfully...
backup log db_tdadatamart with truncate_only
dbcc shrinkfile ('datamartLog', EMPTYFILE)
...when I try to remove the second file using this
command...
alter database db_datamart
remove file datamartLog
...I get this error:
The file 'datamartLog' cannot be removed because it is not
empty.
Thanks in advance for your help.Run DBCC OPENTRAN to ensure there are no open transactions. And you might
want to make sure you have adone a log backup after that command as well.
--
Andrew J. Kelly SQL MVP
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:1df5a01c4546d$9c7f3f90$a001280a@.phx.gbl...
> I have a DB with two transaction log files. I'd like have
> one of them removed.
> After running the following set of commands successfully...
> backup log db_tdadatamart with truncate_only
> dbcc shrinkfile ('datamartLog', EMPTYFILE)
> ...when I try to remove the second file using this
> command...
> alter database db_datamart
> remove file datamartLog
> ...I get this error:
> The file 'datamartLog' cannot be removed because it is not
> empty.
> Thanks in advance for your help.|||'No active open transactions' were reported... still
encoutner the same problem.
Thanks.
>--Original Message--
>Run DBCC OPENTRAN to ensure there are no open
transactions. And you might
>want to make sure you have adone a log backup after that
command as well.
>--
>Andrew J. Kelly SQL MVP
>
>"Rob" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1df5a01c4546d$9c7f3f90$a001280a@.phx.gbl...
>> I have a DB with two transaction log files. I'd like
have
>> one of them removed.
>> After running the following set of commands
successfully...
>> backup log db_tdadatamart with truncate_only
>> dbcc shrinkfile ('datamartLog', EMPTYFILE)
>> ...when I try to remove the second file using this
>> command...
>> alter database db_datamart
>> remove file datamartLog
>> ...I get this error:
>> The file 'datamartLog' cannot be removed because it is
not
>> empty.
>> Thanks in advance for your help.
>
>.
>|||Did you do a Log backup? Any chance this is related?
http://support.microsoft.com/default.aspx?scid=kb;en-us;324432&Product=sql2k
--
Andrew J. Kelly SQL MVP
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:1d78101c45470$65a7c2a0$a601280a@.phx.gbl...
> 'No active open transactions' were reported... still
> encoutner the same problem.
> Thanks.
> >--Original Message--
> >Run DBCC OPENTRAN to ensure there are no open
> transactions. And you might
> >want to make sure you have adone a log backup after that
> command as well.
> >
> >--
> >Andrew J. Kelly SQL MVP
> >
> >
> >"Rob" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:1df5a01c4546d$9c7f3f90$a001280a@.phx.gbl...
> >> I have a DB with two transaction log files. I'd like
> have
> >> one of them removed.
> >>
> >> After running the following set of commands
> successfully...
> >>
> >> backup log db_tdadatamart with truncate_only
> >> dbcc shrinkfile ('datamartLog', EMPTYFILE)
> >>
> >> ...when I try to remove the second file using this
> >> command...
> >>
> >> alter database db_datamart
> >> remove file datamartLog
> >>
> >> ...I get this error:
> >>
> >> The file 'datamartLog' cannot be removed because it is
> not
> >> empty.
> >>
> >> Thanks in advance for your help.
> >
> >
> >.
> >|||I have some information about DBCC LOGINFO etc on my article regarding shrinking 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/
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:1d78101c45470$65a7c2a0$a601280a@.phx.gbl...
> 'No active open transactions' were reported... still
> encoutner the same problem.
> Thanks.
> >--Original Message--
> >Run DBCC OPENTRAN to ensure there are no open
> transactions. And you might
> >want to make sure you have adone a log backup after that
> command as well.
> >
> >--
> >Andrew J. Kelly SQL MVP
> >
> >
> >"Rob" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:1df5a01c4546d$9c7f3f90$a001280a@.phx.gbl...
> >> I have a DB with two transaction log files. I'd like
> have
> >> one of them removed.
> >>
> >> After running the following set of commands
> successfully...
> >>
> >> backup log db_tdadatamart with truncate_only
> >> dbcc shrinkfile ('datamartLog', EMPTYFILE)
> >>
> >> ...when I try to remove the second file using this
> >> command...
> >>
> >> alter database db_datamart
> >> remove file datamartLog
> >>
> >> ...I get this error:
> >>
> >> The file 'datamartLog' cannot be removed because it is
> not
> >> empty.
> >>
> >> Thanks in advance for your help.
> >
> >
> >.
> >sql

dbcc shrinkfile

Tuesday, March 27, 2012

DBCC SHRINKDATABASE Errors Running SQL Server 2000 , SP3a

I am running SQL Server 2000 SP3a on Windows 2003
I get the following error when I run DBCC SHRINKDATABASE
[Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionCheckForData
(CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
If I shrink the database using Enterprise Manager, I can shrink all the
files, including the log, except the MDF (PRIMARY) file.
I get the following error when I try to shrink this file:
Error 0 : This server has been connected.You must reconnect to perform this
operation.
I have re-booted the server , but get the same error messages.
I have run DBCC CHECKDB and DBCC CHECKFILEGROUP , and 0 errors are reported
Has anyone any ideas how to resolve this problem?
--
DuncanJTry setting single-user mode.
DuncanJ wrote:
> I am running SQL Server 2000 SP3a on Windows 2003
> I get the following error when I run DBCC SHRINKDATABASE
> [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionCheckForData
> (CheckforData()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> Connection Broken
> If I shrink the database using Enterprise Manager, I can shrink all the
> files, including the log, except the MDF (PRIMARY) file.
> I get the following error when I try to shrink this file:
> Error 0 : This server has been connected.You must reconnect to perform this
> operation.
> I have re-booted the server , but get the same error messages.
> I have run DBCC CHECKDB and DBCC CHECKFILEGROUP , and 0 errors are reported
> Has anyone any ideas how to resolve this problem?
> --
> DuncanJ

DBCC Shrinkdatabase errors

When DBCC ShrinkDatabase ('db-name') is run; the following error is returned.

File ID 1 of database ID 5 cannot be shrunk as it is either being shrunk by another process or is empty.

File ID 3 of database ID 5 cannot be shrunk as it is either being shrunk by another process or is empty.

Cannot shrink log file 2 (logmentusnet) because all logical log files are in use.

The files 1-3 are not empty so I'm assuming that another shrink is already running.

Questions:

1. If I restart the database will the errent database shrink stop?

2. How can I find and stop the errent database shrink process?

3. What's going on, and how can I recover the empty space in the database?

I am also facing the same issue.

Any workaround for this.

Regards

kokila

|||

Hy,

I am with the same problem… somebody can help?

File ID 9 of database ID 6 cannot be shrunk as it is either being shrunk by another process or is empty.

dbcc shrinkdatabase error

Hi ,
i was trying to shrink a database but i got the following error :
it's something like " logical file is in use" despite me logging in as a
single user
the statement i used : DBCC Shrinkdatabase ('dbname', 1 , truncate_only)
i have also used the EM's shrink database , though it says it completed
successfully but it did not shrink the database
kindly advise
tks & rdgsHi
What is the Error Number and Severity?
You might be getting an informational message to tell you that the DB can't
be shunk any smaller than it already is.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"maxzsim" wrote:
> Hi ,
> i was trying to shrink a database but i got the following error :
> it's something like " logical file is in use" despite me logging in as a
> single user
> the statement i used : DBCC Shrinkdatabase ('dbname', 1 , truncate_only)
> i have also used the EM's shrink database , though it says it completed
> successfully but it did not shrink the database
> kindly advise
> tks & rdgs|||Hi,
Post the exact error.
I feel that Probably there will be some active transactions. Execute DBCC
OPENTRAN('DBNAME') to identify if there is any open active trans.
If yes, wait that to complete. After that you could execute a backup LOG to
clear the transaction log and then execute DBCC SHRINKFILE to
shrink MDF and LDF seperately.
Thanks
Hari
SQL Server MVP
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:BFE5D0BA-D797-40CE-899B-246D6BA039D4@.microsoft.com...
> Hi ,
> i was trying to shrink a database but i got the following error :
> it's something like " logical file is in use" despite me logging in as a
> single user
> the statement i used : DBCC Shrinkdatabase ('dbname', 1 , truncate_only)
> i have also used the EM's shrink database , though it says it completed
> successfully but it did not shrink the database
> kindly advise
> tks & rdgs|||Hi ,
before doing the shrink database , have already did a txn log bakup and it
says 4GB is free, though the log file is still occupying 5 GB
so thought of shrinking the database/log file and return the free space back
to OS
tks & rdgs
"Hari Prasad" wrote:
> Hi,
> Post the exact error.
> I feel that Probably there will be some active transactions. Execute DBCC
> OPENTRAN('DBNAME') to identify if there is any open active trans.
> If yes, wait that to complete. After that you could execute a backup LOG to
> clear the transaction log and then execute DBCC SHRINKFILE to
> shrink MDF and LDF seperately.
> Thanks
> Hari
> SQL Server MVP
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:BFE5D0BA-D797-40CE-899B-246D6BA039D4@.microsoft.com...
> > Hi ,
> >
> > i was trying to shrink a database but i got the following error :
> > it's something like " logical file is in use" despite me logging in as a
> > single user
> >
> > the statement i used : DBCC Shrinkdatabase ('dbname', 1 , truncate_only)
> >
> > i have also used the EM's shrink database , though it says it completed
> > successfully but it did not shrink the database
> >
> > kindly advise
> > tks & rdgs
>
>|||Hi ,
I have done a txn log backup file earlier and it says that 4GB is free
tks & rdgs
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> What is the Error Number and Severity?
> You might be getting an informational message to tell you that the DB can't
> be shunk any smaller than it already is.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "maxzsim" wrote:
> > Hi ,
> >
> > i was trying to shrink a database but i got the following error :
> > it's something like " logical file is in use" despite me logging in as a
> > single user
> >
> > the statement i used : DBCC Shrinkdatabase ('dbname', 1 , truncate_only)
> >
> > i have also used the EM's shrink database , though it says it completed
> > successfully but it did not shrink the database
> >
> > kindly advise
> > tks & rdgs|||Hi
It all depends on where the "Virtual Log"/"Active Portion of the Log" is in
relation to the end of the log file.
The Active Portion may be close to the end of the file at the moment, so the
log can not be shrunk. Once the Active Portion is at the beginning of the log
file, then the log can be truncated to where it is.
Look at "virtual log files" in BOL.
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"maxzsim" wrote:
> Hi ,
> before doing the shrink database , have already did a txn log bakup and it
> says 4GB is free, though the log file is still occupying 5 GB
> so thought of shrinking the database/log file and return the free space back
> to OS
> tks & rdgs
> "Hari Prasad" wrote:
> > Hi,
> >
> > Post the exact error.
> >
> > I feel that Probably there will be some active transactions. Execute DBCC
> > OPENTRAN('DBNAME') to identify if there is any open active trans.
> > If yes, wait that to complete. After that you could execute a backup LOG to
> > clear the transaction log and then execute DBCC SHRINKFILE to
> > shrink MDF and LDF seperately.
> >
> > Thanks
> > Hari
> > SQL Server MVP
> >
> > "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> > news:BFE5D0BA-D797-40CE-899B-246D6BA039D4@.microsoft.com...
> > > Hi ,
> > >
> > > i was trying to shrink a database but i got the following error :
> > > it's something like " logical file is in use" despite me logging in as a
> > > single user
> > >
> > > the statement i used : DBCC Shrinkdatabase ('dbname', 1 , truncate_only)
> > >
> > > i have also used the EM's shrink database , though it says it completed
> > > successfully but it did not shrink the database
> > >
> > > kindly advise
> > > tks & rdgs
> >
> >
> >|||Are you trying to shrink the db file or the log file? Sounds like the log
file. You should use DBCC SHRINKFILE not database as it allows you to
choose the specific file you want to shrink and how much.
--
Andrew J. Kelly SQL MVP
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:BFE5D0BA-D797-40CE-899B-246D6BA039D4@.microsoft.com...
> Hi ,
> i was trying to shrink a database but i got the following error :
> it's something like " logical file is in use" despite me logging in as a
> single user
> the statement i used : DBCC Shrinkdatabase ('dbname', 1 , truncate_only)
> i have also used the EM's shrink database , though it says it completed
> successfully but it did not shrink the database
> kindly advise
> tks & rdgs|||Hi Andrew ,
i was trying to shrink the log file but it couldn't as per the error below
and i have also tried to shrink the database as well , hoping it'll shrink
the log file
tks & rdgs
"Andrew J. Kelly" wrote:
> Are you trying to shrink the db file or the log file? Sounds like the log
> file. You should use DBCC SHRINKFILE not database as it allows you to
> choose the specific file you want to shrink and how much.
> --
> Andrew J. Kelly SQL MVP
>
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:BFE5D0BA-D797-40CE-899B-246D6BA039D4@.microsoft.com...
> > Hi ,
> >
> > i was trying to shrink a database but i got the following error :
> > it's something like " logical file is in use" despite me logging in as a
> > single user
> >
> > the statement i used : DBCC Shrinkdatabase ('dbname', 1 , truncate_only)
> >
> > i have also used the EM's shrink database , though it says it completed
> > successfully but it did not shrink the database
> >
> > kindly advise
> > tks & rdgs
>
>|||Did you actually try DBCC SHRINKFILE? If not you should. You don't need to
be in single user mode to run this.
--
Andrew J. Kelly SQL MVP
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:A84212A5-E2B0-4CF4-9F03-D4B84FCC4E59@.microsoft.com...
> Hi Andrew ,
> i was trying to shrink the log file but it couldn't as per the error
> below
> and i have also tried to shrink the database as well , hoping it'll shrink
> the log file
> tks & rdgs
> "Andrew J. Kelly" wrote:
>> Are you trying to shrink the db file or the log file? Sounds like the
>> log
>> file. You should use DBCC SHRINKFILE not database as it allows you to
>> choose the specific file you want to shrink and how much.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
>> news:BFE5D0BA-D797-40CE-899B-246D6BA039D4@.microsoft.com...
>> > Hi ,
>> >
>> > i was trying to shrink a database but i got the following error :
>> > it's something like " logical file is in use" despite me logging in as
>> > a
>> > single user
>> >
>> > the statement i used : DBCC Shrinkdatabase ('dbname', 1 ,
>> > truncate_only)
>> >
>> > i have also used the EM's shrink database , though it says it completed
>> > successfully but it did not shrink the database
>> >
>> > kindly advise
>> > tks & rdgs
>>|||Hi Andrew ,
i did tried with the DBCC shrink file statement but got the "logical log in
use" error as per the DBCC Shrinkdatabase. however, when i changed the mode
to "Simple" recovery mode , i could somehow shrink the db
tks & rdgs
"Andrew J. Kelly" wrote:
> Did you actually try DBCC SHRINKFILE? If not you should. You don't need to
> be in single user mode to run this.
> --
> Andrew J. Kelly SQL MVP
>
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:A84212A5-E2B0-4CF4-9F03-D4B84FCC4E59@.microsoft.com...
> > Hi Andrew ,
> >
> > i was trying to shrink the log file but it couldn't as per the error
> > below
> > and i have also tried to shrink the database as well , hoping it'll shrink
> > the log file
> >
> > tks & rdgs
> >
> > "Andrew J. Kelly" wrote:
> >
> >> Are you trying to shrink the db file or the log file? Sounds like the
> >> log
> >> file. You should use DBCC SHRINKFILE not database as it allows you to
> >> choose the specific file you want to shrink and how much.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> >> news:BFE5D0BA-D797-40CE-899B-246D6BA039D4@.microsoft.com...
> >> > Hi ,
> >> >
> >> > i was trying to shrink a database but i got the following error :
> >> > it's something like " logical file is in use" despite me logging in as
> >> > a
> >> > single user
> >> >
> >> > the statement i used : DBCC Shrinkdatabase ('dbname', 1 ,
> >> > truncate_only)
> >> >
> >> > i have also used the EM's shrink database , though it says it completed
> >> > successfully but it did not shrink the database
> >> >
> >> > kindly advise
> >> > tks & rdgs
> >>
> >>
> >>
>
>|||This is because the recovery mode you were in was preventing the log being
truncated until you'd backed it up. Switching to simple mode removes that
restriction. See BOL for more info ('Shrinking the Transaction Log' is a
good place to start)
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:65ADA556-3489-4620-844E-CC0DCB0BDF3A@.microsoft.com...
> Hi Andrew ,
> i did tried with the DBCC shrink file statement but got the "logical log
> in
> use" error as per the DBCC Shrinkdatabase. however, when i changed the
> mode
> to "Simple" recovery mode , i could somehow shrink the db
> tks & rdgs
> "Andrew J. Kelly" wrote:
>> Did you actually try DBCC SHRINKFILE? If not you should. You don't need
>> to
>> be in single user mode to run this.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
>> news:A84212A5-E2B0-4CF4-9F03-D4B84FCC4E59@.microsoft.com...
>> > Hi Andrew ,
>> >
>> > i was trying to shrink the log file but it couldn't as per the error
>> > below
>> > and i have also tried to shrink the database as well , hoping it'll
>> > shrink
>> > the log file
>> >
>> > tks & rdgs
>> >
>> > "Andrew J. Kelly" wrote:
>> >
>> >> Are you trying to shrink the db file or the log file? Sounds like the
>> >> log
>> >> file. You should use DBCC SHRINKFILE not database as it allows you to
>> >> choose the specific file you want to shrink and how much.
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >>
>> >>
>> >> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
>> >> news:BFE5D0BA-D797-40CE-899B-246D6BA039D4@.microsoft.com...
>> >> > Hi ,
>> >> >
>> >> > i was trying to shrink a database but i got the following error :
>> >> > it's something like " logical file is in use" despite me logging in
>> >> > as
>> >> > a
>> >> > single user
>> >> >
>> >> > the statement i used : DBCC Shrinkdatabase ('dbname', 1 ,
>> >> > truncate_only)
>> >> >
>> >> > i have also used the EM's shrink database , though it says it
>> >> > completed
>> >> > successfully but it did not shrink the database
>> >> >
>> >> > kindly advise
>> >> > tks & rdgs
>> >>
>> >>
>> >>
>>

dbcc shrinkdatabase error

DBCC shrinkdatabase , transaction log shrink

Hi All,
Last night I created sql JOB to shrink the database.
I used the following command
dbcc shrinkdatabase('xxxx')
I scheduled it at 11:00 PM.
Today morning when I saw the job status, it says it failed.
Duration of job run was 2 hours.
In the job history it is saying as below.
Could not adjust the space allocation for file 'xxxx_data' [SQLSTATE 42000]
(Error 3140) DBCC
execution completed. If DBCC printed error messages, contact your system
administrator. [SQLSTATE
01000] (Error 2528). The step failed.
It puts the following in the eventlog.
SQL Server Scheduled Job 'Shrink xxxx Database'
(0x935A70A0A9B0A643B6336153DD61E128) - Status:
Failed - Invoked on: 2005-10-20 23:00:00 - Message: The job failed. The Job
was invoked by
Schedule 17 (Schedule1). The last step to run was step 1 (Step1).
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
This is our production database and it is being logshipped to other server.
My database recovory model set to FULL.
In DB options I unchecked auto shrink option.
I want to shirink database only in night time.
Question-1: Do you guys have any idea why this job failed?
Question-2:Usually what is the permissible value of transactionlog (.ldf)
file size?
Some times to reduce the transaction log file (.LDF) , I am doing as below.
I detach the database and rename the .ldf file name, and attach the database
with only mdf file
,SQL server creates new ldf file with minimum size. delete the old .ldf file.
Question-3: By doing this , AM I loosing some data/transactions stored in
old .ldf file?
Any kind of help is greatly appreciated.
Thanks
Kumar
Kumar,
Try doing the SHRINKDATABASE manually (not as a job) to rule out the
possible causes for you failure. If this op is just to reduce the size of
the transaction log, try DBCC SHRINKFILE instead of your current process.
Also see:
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/kb/272318/
and
Tibor's page:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
HTH
Jerry
"Kumar" <Kumar@.discussions.microsoft.com> wrote in message
news:813BD9B6-E8B8-43FA-894B-D2D3A5DF2D1B@.microsoft.com...
> Hi All,
> Last night I created sql JOB to shrink the database.
> I used the following command
> dbcc shrinkdatabase('xxxx')
> I scheduled it at 11:00 PM.
> Today morning when I saw the job status, it says it failed.
> Duration of job run was 2 hours.
> In the job history it is saying as below.
> Could not adjust the space allocation for file 'xxxx_data' [SQLSTATE
> 42000]
> (Error 3140) DBCC
> execution completed. If DBCC printed error messages, contact your system
> administrator. [SQLSTATE
> 01000] (Error 2528). The step failed.
>
> It puts the following in the eventlog.
> SQL Server Scheduled Job 'Shrink xxxx Database'
> (0x935A70A0A9B0A643B6336153DD61E128) - Status:
> Failed - Invoked on: 2005-10-20 23:00:00 - Message: The job failed. The
> Job
> was invoked by
> Schedule 17 (Schedule1). The last step to run was step 1 (Step1).
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
>
> This is our production database and it is being logshipped to other
> server.
>
> My database recovory model set to FULL.
> In DB options I unchecked auto shrink option.
> I want to shirink database only in night time.
> Question-1: Do you guys have any idea why this job failed?
>
> Question-2:Usually what is the permissible value of transactionlog (.ldf)
> file size?
> Some times to reduce the transaction log file (.LDF) , I am doing as
> below.
> I detach the database and rename the .ldf file name, and attach the
> database
> with only mdf file
> ,SQL server creates new ldf file with minimum size. delete the old .ldf
> file.
>
> Question-3: By doing this , AM I loosing some data/transactions stored in
> old .ldf file?
>
> Any kind of help is greatly appreciated.
> Thanks
> Kumar
>
>
>
>
sql

DBCC shrinkdatabase , transaction log shrink

Hi All,
Last night I created sql JOB to shrink the database.
I used the following command
dbcc shrinkdatabase('xxxx')
I scheduled it at 11:00 PM.
Today morning when I saw the job status, it says it failed.
Duration of job run was 2 hours.
In the job history it is saying as below.
Could not adjust the space allocation for file 'xxxx_data' [SQLSTATE 420
00]
(Error 3140) DBCC
execution completed. If DBCC printed error messages, contact your system
administrator. [SQLSTATE
01000] (Error 2528). The step failed.
It puts the following in the eventlog.
SQL Server Scheduled Job 'Shrink xxxx Database'
(0x935A70A0A9B0A643B6336153DD61E128) - Status:
Failed - Invoked on: 2005-10-20 23:00:00 - Message: The job failed. The Job
was invoked by
Schedule 17 (Schedule1). The last step to run was step 1 (Step1).
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
This is our production database and it is being logshipped to other server.
My database recovory model set to FULL.
In DB options I unchecked auto shrink option.
I want to shirink database only in night time.
Question-1: Do you guys have any idea why this job failed?
Question-2:Usually what is the permissible value of transactionlog (.ldf)
file size?
Some times to reduce the transaction log file (.LDF) , I am doing as below.
I detach the database and rename the .ldf file name, and attach the database
with only mdf file
,SQL server creates new ldf file with minimum size. delete the old .ldf file
.
Question-3: By doing this , AM I loosing some data/transactions stored in
old .ldf file?
Any kind of help is greatly appreciated.
Thanks
KumarKumar,
Try doing the SHRINKDATABASE manually (not as a job) to rule out the
possible causes for you failure. If this op is just to reduce the size of
the transaction log, try DBCC SHRINKFILE instead of your current process.
Also see:
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/kb/272318/
and
Tibor's page:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
HTH
Jerry
"Kumar" <Kumar@.discussions.microsoft.com> wrote in message
news:813BD9B6-E8B8-43FA-894B-D2D3A5DF2D1B@.microsoft.com...
> Hi All,
> Last night I created sql JOB to shrink the database.
> I used the following command
> dbcc shrinkdatabase('xxxx')
> I scheduled it at 11:00 PM.
> Today morning when I saw the job status, it says it failed.
> Duration of job run was 2 hours.
> In the job history it is saying as below.
> Could not adjust the space allocation for file 'xxxx_data' [SQLSTATE
> 42000]
> (Error 3140) DBCC
> execution completed. If DBCC printed error messages, contact your system
> administrator. [SQLSTATE
> 01000] (Error 2528). The step failed.
>
> It puts the following in the eventlog.
> SQL Server Scheduled Job 'Shrink xxxx Database'
> (0x935A70A0A9B0A643B6336153DD61E128) - Status:
> Failed - Invoked on: 2005-10-20 23:00:00 - Message: The job failed. The
> Job
> was invoked by
> Schedule 17 (Schedule1). The last step to run was step 1 (Step1).
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
>
> This is our production database and it is being logshipped to other
> server.
>
> My database recovory model set to FULL.
> In DB options I unchecked auto shrink option.
> I want to shirink database only in night time.
> Question-1: Do you guys have any idea why this job failed?
>
> Question-2:Usually what is the permissible value of transactionlog (.ldf)
> file size?
> Some times to reduce the transaction log file (.LDF) , I am doing as
> below.
> I detach the database and rename the .ldf file name, and attach the
> database
> with only mdf file
> ,SQL server creates new ldf file with minimum size. delete the old .ldf
> file.
>
> Question-3: By doing this , AM I loosing some data/transactions stored in
> old .ldf file?
>
> Any kind of help is greatly appreciated.
> Thanks
> Kumar
>
>
>
>

DBCC shrinkdatabase , transaction log shrink

Hi All,
Last night I created sql JOB to shrink the database.
I used the following command
dbcc shrinkdatabase('xxxx')
I scheduled it at 11:00 PM.
Today morning when I saw the job status, it says it failed.
Duration of job run was 2 hours.
In the job history it is saying as below.
Could not adjust the space allocation for file 'xxxx_data' [SQLSTATE 42000]
(Error 3140) DBCC
execution completed. If DBCC printed error messages, contact your system
administrator. [SQLSTATE
01000] (Error 2528). The step failed.
It puts the following in the eventlog.
SQL Server Scheduled Job 'Shrink xxxx Database'
(0x935A70A0A9B0A643B6336153DD61E128) - Status:
Failed - Invoked on: 2005-10-20 23:00:00 - Message: The job failed. The Job
was invoked by
Schedule 17 (Schedule1). The last step to run was step 1 (Step1).
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
This is our production database and it is being logshipped to other server.
My database recovory model set to FULL.
In DB options I unchecked auto shrink option.
I want to shirink database only in night time.
Question-1: Do you guys have any idea why this job failed?
Question-2:Usually what is the permissible value of transactionlog (.ldf)
file size?
Some times to reduce the transaction log file (.LDF) , I am doing as below.
I detach the database and rename the .ldf file name, and attach the database
with only mdf file
,SQL server creates new ldf file with minimum size. delete the old .ldf file.
Question-3: By doing this , AM I loosing some data/transactions stored in
old .ldf file?
Any kind of help is greatly appreciated.
Thanks
KumarKumar,
Try doing the SHRINKDATABASE manually (not as a job) to rule out the
possible causes for you failure. If this op is just to reduce the size of
the transaction log, try DBCC SHRINKFILE instead of your current process.
Also see:
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/kb/272318/
and
Tibor's page:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
HTH
Jerry
"Kumar" <Kumar@.discussions.microsoft.com> wrote in message
news:813BD9B6-E8B8-43FA-894B-D2D3A5DF2D1B@.microsoft.com...
> Hi All,
> Last night I created sql JOB to shrink the database.
> I used the following command
> dbcc shrinkdatabase('xxxx')
> I scheduled it at 11:00 PM.
> Today morning when I saw the job status, it says it failed.
> Duration of job run was 2 hours.
> In the job history it is saying as below.
> Could not adjust the space allocation for file 'xxxx_data' [SQLSTATE
> 42000]
> (Error 3140) DBCC
> execution completed. If DBCC printed error messages, contact your system
> administrator. [SQLSTATE
> 01000] (Error 2528). The step failed.
>
> It puts the following in the eventlog.
> SQL Server Scheduled Job 'Shrink xxxx Database'
> (0x935A70A0A9B0A643B6336153DD61E128) - Status:
> Failed - Invoked on: 2005-10-20 23:00:00 - Message: The job failed. The
> Job
> was invoked by
> Schedule 17 (Schedule1). The last step to run was step 1 (Step1).
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
>
> This is our production database and it is being logshipped to other
> server.
>
> My database recovory model set to FULL.
> In DB options I unchecked auto shrink option.
> I want to shirink database only in night time.
> Question-1: Do you guys have any idea why this job failed?
>
> Question-2:Usually what is the permissible value of transactionlog (.ldf)
> file size?
> Some times to reduce the transaction log file (.LDF) , I am doing as
> below.
> I detach the database and rename the .ldf file name, and attach the
> database
> with only mdf file
> ,SQL server creates new ldf file with minimum size. delete the old .ldf
> file.
>
> Question-3: By doing this , AM I loosing some data/transactions stored in
> old .ldf file?
>
> Any kind of help is greatly appreciated.
> Thanks
> Kumar
>
>
>
>

DBCC SHRINKDATABASE

SQL Server 2000 on Windows 2003
I want to shrink the Transaction Log file .
I run DBCC SHRINKDATABASE and I got the following message: Cannot shrink log
file 2 (IRIS_Log) because all logical log files are in use.
What should I do?
Thanks,
Dan
Hi,
You should backup the tranasction log before doing the DBCC SHRINK command.
Incase if you do not
need the transaction log backup then you can truncate the transaction log
using
Backup log <dbname> with Truncate_only
After doing eithe log backup or teuncate you could try Shrink. I recommend
you to DBCC SHRINKFILE to shrink the database files
Thanks
Hari
SQL Server MVP
"danA" <dea@.deamon.com> wrote in message
news:%23E8gYBZmFHA.1048@.tk2msftngp13.phx.gbl...
> SQL Server 2000 on Windows 2003
> I want to shrink the Transaction Log file .
> I run DBCC SHRINKDATABASE and I got the following message: Cannot shrink
> log file 2 (IRIS_Log) because all logical log files are in use.
> What should I do?
> Thanks,
> Dan
>
>
|||Many thanks Hari, it worked. Dan
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OgaKWGZmFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Hi,
> You should backup the transaction log before doing the DBCC SHRINK
> command. Incase if you do not
> need the transaction log backup then you can truncate the transaction log
> using
> Backup log <dbname> with Truncate_only
> After doing eithe log backup or teuncate you could try Shrink. I recommend
> you to DBCC SHRINKFILE to shrink the database files
> Thanks
> Hari
> SQL Server MVP
>
> "danA" <dea@.deamon.com> wrote in message
> news:%23E8gYBZmFHA.1048@.tk2msftngp13.phx.gbl...
>

DBCC SHRINKDATABASE

SQL Server 2000 on Windows 2003
I want to shrink the Transaction Log file .
I run DBCC SHRINKDATABASE and I got the following message: Cannot shrink log
file 2 (IRIS_Log) because all logical log files are in use.
What should I do?
Thanks,
DanHi,
You should backup the tranasction log before doing the DBCC SHRINK command.
Incase if you do not
need the transaction log backup then you can truncate the transaction log
using
Backup log <dbname> with Truncate_only
After doing eithe log backup or teuncate you could try Shrink. I recommend
you to DBCC SHRINKFILE to shrink the database files
Thanks
Hari
SQL Server MVP
"danA" <dea@.deamon.com> wrote in message
news:%23E8gYBZmFHA.1048@.tk2msftngp13.phx.gbl...
> SQL Server 2000 on Windows 2003
> I want to shrink the Transaction Log file .
> I run DBCC SHRINKDATABASE and I got the following message: Cannot shrink
> log file 2 (IRIS_Log) because all logical log files are in use.
> What should I do?
> Thanks,
> Dan
>
>|||Many thanks Hari, it worked. Dan
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OgaKWGZmFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Hi,
> You should backup the transaction log before doing the DBCC SHRINK
> command. Incase if you do not
> need the transaction log backup then you can truncate the transaction log
> using
> Backup log <dbname> with Truncate_only
> After doing eithe log backup or teuncate you could try Shrink. I recommend
> you to DBCC SHRINKFILE to shrink the database files
> Thanks
> Hari
> SQL Server MVP
>
> "danA" <dea@.deamon.com> wrote in message
> news:%23E8gYBZmFHA.1048@.tk2msftngp13.phx.gbl...
>> SQL Server 2000 on Windows 2003
>> I want to shrink the Transaction Log file .
>> I run DBCC SHRINKDATABASE and I got the following message: Cannot shrink
>> log file 2 (IRIS_Log) because all logical log files are in use.
>> What should I do?
>> Thanks,
>> Dan
>>
>

DBCC SHRINKDATABASE

SQL Server 2000 on Windows 2003
I want to shrink the Transaction Log file .
I run DBCC SHRINKDATABASE and I got the following message: Cannot shrink log
file 2 (IRIS_Log) because all logical log files are in use.
What should I do?
Thanks,
DanHi,
You should backup the tranasction log before doing the DBCC SHRINK command.
Incase if you do not
need the transaction log backup then you can truncate the transaction log
using
Backup log <dbname> with Truncate_only
After doing eithe log backup or teuncate you could try Shrink. I recommend
you to DBCC SHRINKFILE to shrink the database files
Thanks
Hari
SQL Server MVP
"danA" <dea@.deamon.com> wrote in message
news:%23E8gYBZmFHA.1048@.tk2msftngp13.phx.gbl...
> SQL Server 2000 on Windows 2003
> I want to shrink the Transaction Log file .
> I run DBCC SHRINKDATABASE and I got the following message: Cannot shrink
> log file 2 (IRIS_Log) because all logical log files are in use.
> What should I do?
> Thanks,
> Dan
>
>|||Many thanks Hari, it worked. Dan
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OgaKWGZmFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Hi,
> You should backup the transaction log before doing the DBCC SHRINK
> command. Incase if you do not
> need the transaction log backup then you can truncate the transaction log
> using
> Backup log <dbname> with Truncate_only
> After doing eithe log backup or teuncate you could try Shrink. I recommend
> you to DBCC SHRINKFILE to shrink the database files
> Thanks
> Hari
> SQL Server MVP
>
> "danA" <dea@.deamon.com> wrote in message
> news:%23E8gYBZmFHA.1048@.tk2msftngp13.phx.gbl...
>

Sunday, March 25, 2012

DBCC SHOWFILESTATS

Hello,
I′m testing the execution of my database backups with omniback. These
backups fails so i've made a trace and i get the following error: "Error:
7983, Severity: 14, State: 14"
"dbcc showfilestats( 1 )"
The user used to access the databases is a domain admin but in sql server i
only give him the backup operator database role.
If this user were sysadmin everything goes fine but i want to limit his
access.
Do you have any idea about this?
What does the documentation for the backup vendor say? Does it say that the login need to be
sysadmin. If not, you have a bug in their program. If it does, well...
You cannot grant permissions on this. Also, this is an undocumented command, so the backup vendor is
using it at their own risk.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"CC&JM" <CC&JM@.discussions.microsoft.com> wrote in message
news:A094D1B4-135A-492B-ADAC-2C12CB9CAC35@.microsoft.com...
> Hello,
> Im testing the execution of my database backups with omniback. These
> backups fails so i've made a trace and i get the following error: "Error:
> 7983, Severity: 14, State: 14"
> "dbcc showfilestats( 1 )"
> The user used to access the databases is a domain admin but in sql server i
> only give him the backup operator database role.
> If this user were sysadmin everything goes fine but i want to limit his
> access.
> Do you have any idea about this?
>
|||Moreover, the SQL Backup alternatives typically want to use the Virtual
Backup Device Interface API. At this time, only system admins can make calls
with this interface. Backup Operators do not.
Sincerely,
Anthony Thomas
"Tibor Karaszi" wrote:

> What does the documentation for the backup vendor say? Does it say that the login need to be
> sysadmin. If not, you have a bug in their program. If it does, well...
> You cannot grant permissions on this. Also, this is an undocumented command, so the backup vendor is
> using it at their own risk.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "CC&JM" <CC&JM@.discussions.microsoft.com> wrote in message
> news:A094D1B4-135A-492B-ADAC-2C12CB9CAC35@.microsoft.com...
>
>
|||That undocumented DBCC command is SA-only - there's no way around this.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
news:354E62F2-2E58-4D0A-BE1F-E1AA3D8262BE@.microsoft.com...
> Moreover, the SQL Backup alternatives typically want to use the Virtual
> Backup Device Interface API. At this time, only system admins can make
calls[vbcol=seagreen]
> with this interface. Backup Operators do not.
> Sincerely,
>
> Anthony Thomas
>
> "Tibor Karaszi" wrote:
the login need to be[vbcol=seagreen]
command, so the backup vendor is[vbcol=seagreen]
"Error:[vbcol=seagreen]
server i[vbcol=seagreen]
his[vbcol=seagreen]

DBCC SHOWFILESTATS

Hello,
I′m testing the execution of my database backups with omniback. These
backups fails so i've made a trace and i get the following error: "Error:
7983, Severity: 14, State: 14"
"dbcc showfilestats( 1 )"
The user used to access the databases is a domain admin but in sql server i
only give him the backup operator database role.
If this user were sysadmin everything goes fine but i want to limit his
access.
Do you have any idea about this?What does the documentation for the backup vendor say? Does it say that the
login need to be
sysadmin. If not, you have a bug in their program. If it does, well...
You cannot grant permissions on this. Also, this is an undocumented command,
so the backup vendor is
using it at their own risk.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"CC&JM" <CC&JM@.discussions.microsoft.com> wrote in message
news:A094D1B4-135A-492B-ADAC-2C12CB9CAC35@.microsoft.com...
> Hello,
> Im testing the execution of my database backups with omniback. These
> backups fails so i've made a trace and i get the following error: "Error:
> 7983, Severity: 14, State: 14"
> "dbcc showfilestats( 1 )"
> The user used to access the databases is a domain admin but in sql server
i
> only give him the backup operator database role.
> If this user were sysadmin everything goes fine but i want to limit his
> access.
> Do you have any idea about this?
>|||Moreover, the SQL Backup alternatives typically want to use the Virtual
Backup Device Interface API. At this time, only system admins can make call
s
with this interface. Backup Operators do not.
Sincerely,
Anthony Thomas
"Tibor Karaszi" wrote:

> What does the documentation for the backup vendor say? Does it say that th
e login need to be
> sysadmin. If not, you have a bug in their program. If it does, well...
> You cannot grant permissions on this. Also, this is an undocumented comman
d, so the backup vendor is
> using it at their own risk.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "CC&JM" <CC&JM@.discussions.microsoft.com> wrote in message
> news:A094D1B4-135A-492B-ADAC-2C12CB9CAC35@.microsoft.com...
>
>|||That undocumented DBCC command is SA-only - there's no way around this.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
news:354E62F2-2E58-4D0A-BE1F-E1AA3D8262BE@.microsoft.com...
> Moreover, the SQL Backup alternatives typically want to use the Virtual
> Backup Device Interface API. At this time, only system admins can make
calls[vbcol=seagreen]
> with this interface. Backup Operators do not.
> Sincerely,
>
> Anthony Thomas
>
> "Tibor Karaszi" wrote:
>
the login need to be[vbcol=seagreen]
command, so the backup vendor is[vbcol=seagreen]
"Error:[vbcol=seagreen]
server i[vbcol=seagreen]
his[vbcol=seagreen]

DBCC SHOWCONTIG and Extent Switches

All,
I executed the following statement at my production server.
DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
which provided me the following resultset. Besides "AveragePageDensity" and
"LogicalFragmentation", I paid special attention to "Extent Swithces" which
is quite high.
Is there any performance gain if I reduce th Extent Switces? How can I
lessen the value of Extent Switches.
TIA
Kay
ObjectName ObjectId IndexName
IndexId
Level Pages Rows MinimumRecordSize MaximumRecordSize AverageRecordSize
ForwardedRecords Extents ExtentSwitches AverageFreeBytes AveragePageDensity
ScanDensity BestCount ActualCount LogicalFragmentation ExtentFragmentation
package 1845581613 PK_package 1 0 527 224182 15 15 15 0 72 77 864.322
89.321 84.615 66 78 1.898 18.056
package 1845581613 package80 5 0 306 224182 9 9 9 0 42 41 37.169
99.541 92.857 39 42 1.307 16.667
package 1845581613 package69 6 0 388 224182 9 12 11.98 0 51 50 18.417
99.772 96.078 49 51 0.258 13.725
package_description 1450240717 PK_package_description 1 0 3226 169022
44 434 138.77 0 413 412 720.535 91.098 97.821 404 413 0.217 10.412
package_description 1450240717 package_description73 2 0 296 169022 12
12 12 0 42 43 101.716 98.743 84.091 37 44 1.351 14.286
package_description 1450240717 idx_package_name 3 0 1904 169022 8 370
77.871 0 249 636 1005.600 87.576 37.363 238 637 11.922 22.088
package_description 1450240717 idx_package_state 4 0 341 169022 12 12
12 0 47 129 1156.680 85.709 33.077 43 130 14.663 21.277
package_description 1450240717 idx_package_hours 5 0 396 169022 16 16
16 0 54 79 413.181 94.895 62.500 50 80 5.303 22.222
package_description 1450240717 idx_package_cost 6 0 411 169022 16 16
16 0 56 115 693.576 91.431 44.828 52 116 8.273 17.857
package_description 1450240717 idx_package_available 7 0 298 169022 12
12 12 0 42 48 155.369 98.080 77.551 38 49 2.349 16.667
package_description 1450240717 tpackage_description 255 0 114597
340665 32 8094 2076.854 0 14336 14335 1916.141 76.326 99.923 14325 14336
99.999 17.153
pass_quiz_options 1877581727 0 0 1 2 35 45 40 0 1 0 8012.000 1.013
100.000 1 1 0.000 0.000
Kay
http://www.sql-server-performance.co...showcontig.asp
"Kay" <CallDBA@.hotmail.com> wrote in message
news:OmnmxiiBGHA.3840@.TK2MSFTNGP15.phx.gbl...
> All,
>
> I executed the following statement at my production server.
> DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
> which provided me the following resultset. Besides "AveragePageDensity"
> and "LogicalFragmentation", I paid special attention to "Extent Swithces"
> which is quite high.
>
> Is there any performance gain if I reduce th Extent Switces? How can I
> lessen the value of Extent Switches.
>
> TIA
>
> Kay
>
> ObjectName ObjectId IndexName
> IndexId
> Level Pages Rows MinimumRecordSize MaximumRecordSize AverageRecordSize
> ForwardedRecords Extents ExtentSwitches AverageFreeBytes
> AveragePageDensity ScanDensity BestCount ActualCount LogicalFragmentation
> ExtentFragmentation
> package 1845581613 PK_package 1 0 527 224182 15 15 15 0 72 77 864.322
> 89.321 84.615 66 78 1.898 18.056
> package 1845581613 package80 5 0 306 224182 9 9 9 0 42 41 37.169
> 99.541 92.857 39 42 1.307 16.667
> package 1845581613 package69 6 0 388 224182 9 12 11.98 0 51 50 18.417
> 99.772 96.078 49 51 0.258 13.725
> package_description 1450240717 PK_package_description 1 0 3226 169022
> 44 434 138.77 0 413 412 720.535 91.098 97.821 404 413 0.217 10.412
> package_description 1450240717 package_description73 2 0 296 169022
> 12 12 12 0 42 43 101.716 98.743 84.091 37 44 1.351 14.286
> package_description 1450240717 idx_package_name 3 0 1904 169022 8 370
> 77.871 0 249 636 1005.600 87.576 37.363 238 637 11.922 22.088
> package_description 1450240717 idx_package_state 4 0 341 169022 12 12
> 12 0 47 129 1156.680 85.709 33.077 43 130 14.663 21.277
> package_description 1450240717 idx_package_hours 5 0 396 169022 16 16
> 16 0 54 79 413.181 94.895 62.500 50 80 5.303 22.222
> package_description 1450240717 idx_package_cost 6 0 411 169022 16 16
> 16 0 56 115 693.576 91.431 44.828 52 116 8.273 17.857
> package_description 1450240717 idx_package_available 7 0 298 169022
> 12 12 12 0 42 48 155.369 98.080 77.551 38 49 2.349 16.667
> package_description 1450240717 tpackage_description 255 0 114597
> 340665 32 8094 2076.854 0 14336 14335 1916.141 76.326 99.923 14325 14336
> 99.999 17.153
> pass_quiz_options 1877581727 0 0 1 2 35 45 40 0 1 0 8012.000 1.013
> 100.000 1 1 0.000 0.000
>
>
>
|||Based on the result set provided and viewing the clustered index, your extent
switches are fine. Extent switches is the number of times the DBCC statement
moved off an extent while it was scanning the pages in the extent. You would
expect an extent switch to happen after the whole extent had been scanned.
The most useful line of output would be the ScanDensity BestCount
ActualCount. This is your measure of fragmentation. The best count is the
ideal number of extents, where as the actual count is the actual number of
extents use to hold the data pages.
I noticed the AveragePageDensity is around 89.321, which indicates a
fillfactor of 90 set for the clustered index. In a clustered index, since the
leaf level contains the data, you can use FILLFACTOR to control how much
space to leave in the table itself. By reserving free space, you can avoid
splitting pages to make room for a new entry. NOTE that FILLFACTOR is not
maintained; it only indicates how much space is reserved with the existing
data at the time the index is built. If you need to, you can use the DBCC
DBREINDEX command to rebuild the index and reestablish the original
FILLFACTOR specified, also reducing fragmentation.
"Kay" wrote:

> All,
>
> I executed the following statement at my production server.
> DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
> which provided me the following resultset. Besides "AveragePageDensity" and
> "LogicalFragmentation", I paid special attention to "Extent Swithces" which
> is quite high.
>
> Is there any performance gain if I reduce th Extent Switces? How can I
> lessen the value of Extent Switches.
>
> TIA
>
> Kay
>
> ObjectName ObjectId IndexName
> IndexId
> Level Pages Rows MinimumRecordSize MaximumRecordSize AverageRecordSize
> ForwardedRecords Extents ExtentSwitches AverageFreeBytes AveragePageDensity
> ScanDensity BestCount ActualCount LogicalFragmentation ExtentFragmentation
> package 1845581613 PK_package 1 0 527 224182 15 15 15 0 72 77 864.322
> 89.321 84.615 66 78 1.898 18.056
> package 1845581613 package80 5 0 306 224182 9 9 9 0 42 41 37.169
> 99.541 92.857 39 42 1.307 16.667
> package 1845581613 package69 6 0 388 224182 9 12 11.98 0 51 50 18.417
> 99.772 96.078 49 51 0.258 13.725
> package_description 1450240717 PK_package_description 1 0 3226 169022
> 44 434 138.77 0 413 412 720.535 91.098 97.821 404 413 0.217 10.412
> package_description 1450240717 package_description73 2 0 296 169022 12
> 12 12 0 42 43 101.716 98.743 84.091 37 44 1.351 14.286
> package_description 1450240717 idx_package_name 3 0 1904 169022 8 370
> 77.871 0 249 636 1005.600 87.576 37.363 238 637 11.922 22.088
> package_description 1450240717 idx_package_state 4 0 341 169022 12 12
> 12 0 47 129 1156.680 85.709 33.077 43 130 14.663 21.277
> package_description 1450240717 idx_package_hours 5 0 396 169022 16 16
> 16 0 54 79 413.181 94.895 62.500 50 80 5.303 22.222
> package_description 1450240717 idx_package_cost 6 0 411 169022 16 16
> 16 0 56 115 693.576 91.431 44.828 52 116 8.273 17.857
> package_description 1450240717 idx_package_available 7 0 298 169022 12
> 12 12 0 42 48 155.369 98.080 77.551 38 49 2.349 16.667
> package_description 1450240717 tpackage_description 255 0 114597
> 340665 32 8094 2076.854 0 14336 14335 1916.141 76.326 99.923 14325 14336
> 99.999 17.153
> pass_quiz_options 1877581727 0 0 1 2 35 45 40 0 1 0 8012.000 1.013
> 100.000 1 1 0.000 0.000
>
>
>
>

DBCC SHOWCONTIG and Extent Switches

All,
I executed the following statement at my production server.
DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
which provided me the following resultset. Besides "AveragePageDensity" and
"LogicalFragmentation", I paid special attention to "Extent Swithces" which
is quite high.
Is there any performance gain if I reduce th Extent Switces? How can I
lessen the value of Extent Switches.
TIA
Kay
ObjectName ObjectId IndexName
IndexId
Level Pages Rows MinimumRecordSize MaximumRecordSize AverageRecordSize
ForwardedRecords Extents ExtentSwitches AverageFreeBytes AveragePageDensity
ScanDensity BestCount ActualCount LogicalFragmentation ExtentFragmentation
package 1845581613 PK_package 1 0 527 224182 15 15 15 0 72 77 864.322
89.321 84.615 66 78 1.898 18.056
package 1845581613 package80 5 0 306 224182 9 9 9 0 42 41 37.169
99.541 92.857 39 42 1.307 16.667
package 1845581613 package69 6 0 388 224182 9 12 11.98 0 51 50 18.417
99.772 96.078 49 51 0.258 13.725
package_description 1450240717 PK_package_description 1 0 3226 169022
44 434 138.77 0 413 412 720.535 91.098 97.821 404 413 0.217 10.412
package_description 1450240717 package_description73 2 0 296 169022 12
12 12 0 42 43 101.716 98.743 84.091 37 44 1.351 14.286
package_description 1450240717 idx_package_name 3 0 1904 169022 8 370
77.871 0 249 636 1005.600 87.576 37.363 238 637 11.922 22.088
package_description 1450240717 idx_package_state 4 0 341 169022 12 12
12 0 47 129 1156.680 85.709 33.077 43 130 14.663 21.277
package_description 1450240717 idx_package_hours 5 0 396 169022 16 16
16 0 54 79 413.181 94.895 62.500 50 80 5.303 22.222
package_description 1450240717 idx_package_cost 6 0 411 169022 16 16
16 0 56 115 693.576 91.431 44.828 52 116 8.273 17.857
package_description 1450240717 idx_package_available 7 0 298 169022 12
12 12 0 42 48 155.369 98.080 77.551 38 49 2.349 16.667
package_description 1450240717 tpackage_description 255 0 114597
340665 32 8094 2076.854 0 14336 14335 1916.141 76.326 99.923 14325 14336
99.999 17.153
pass_quiz_options 1877581727 0 0 1 2 35 45 40 0 1 0 8012.000 1.013
100.000 1 1 0.000 0.000Kay
http://www.sql-server-performance.com/dt_dbcc_showcontig.asp
"Kay" <CallDBA@.hotmail.com> wrote in message
news:OmnmxiiBGHA.3840@.TK2MSFTNGP15.phx.gbl...
> All,
>
> I executed the following statement at my production server.
> DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
> which provided me the following resultset. Besides "AveragePageDensity"
> and "LogicalFragmentation", I paid special attention to "Extent Swithces"
> which is quite high.
>
> Is there any performance gain if I reduce th Extent Switces? How can I
> lessen the value of Extent Switches.
>
> TIA
>
> Kay
>
> ObjectName ObjectId IndexName
> IndexId
> Level Pages Rows MinimumRecordSize MaximumRecordSize AverageRecordSize
> ForwardedRecords Extents ExtentSwitches AverageFreeBytes
> AveragePageDensity ScanDensity BestCount ActualCount LogicalFragmentation
> ExtentFragmentation
> package 1845581613 PK_package 1 0 527 224182 15 15 15 0 72 77 864.322
> 89.321 84.615 66 78 1.898 18.056
> package 1845581613 package80 5 0 306 224182 9 9 9 0 42 41 37.169
> 99.541 92.857 39 42 1.307 16.667
> package 1845581613 package69 6 0 388 224182 9 12 11.98 0 51 50 18.417
> 99.772 96.078 49 51 0.258 13.725
> package_description 1450240717 PK_package_description 1 0 3226 169022
> 44 434 138.77 0 413 412 720.535 91.098 97.821 404 413 0.217 10.412
> package_description 1450240717 package_description73 2 0 296 169022
> 12 12 12 0 42 43 101.716 98.743 84.091 37 44 1.351 14.286
> package_description 1450240717 idx_package_name 3 0 1904 169022 8 370
> 77.871 0 249 636 1005.600 87.576 37.363 238 637 11.922 22.088
> package_description 1450240717 idx_package_state 4 0 341 169022 12 12
> 12 0 47 129 1156.680 85.709 33.077 43 130 14.663 21.277
> package_description 1450240717 idx_package_hours 5 0 396 169022 16 16
> 16 0 54 79 413.181 94.895 62.500 50 80 5.303 22.222
> package_description 1450240717 idx_package_cost 6 0 411 169022 16 16
> 16 0 56 115 693.576 91.431 44.828 52 116 8.273 17.857
> package_description 1450240717 idx_package_available 7 0 298 169022
> 12 12 12 0 42 48 155.369 98.080 77.551 38 49 2.349 16.667
> package_description 1450240717 tpackage_description 255 0 114597
> 340665 32 8094 2076.854 0 14336 14335 1916.141 76.326 99.923 14325 14336
> 99.999 17.153
> pass_quiz_options 1877581727 0 0 1 2 35 45 40 0 1 0 8012.000 1.013
> 100.000 1 1 0.000 0.000
>
>
>|||Based on the result set provided and viewing the clustered index, your extent
switches are fine. Extent switches is the number of times the DBCC statement
moved off an extent while it was scanning the pages in the extent. You would
expect an extent switch to happen after the whole extent had been scanned.
The most useful line of output would be the ScanDensity BestCount
ActualCount. This is your measure of fragmentation. The best count is the
ideal number of extents, where as the actual count is the actual number of
extents use to hold the data pages.
I noticed the AveragePageDensity is around 89.321, which indicates a
fillfactor of 90 set for the clustered index. In a clustered index, since the
leaf level contains the data, you can use FILLFACTOR to control how much
space to leave in the table itself. By reserving free space, you can avoid
splitting pages to make room for a new entry. NOTE that FILLFACTOR is not
maintained; it only indicates how much space is reserved with the existing
data at the time the index is built. If you need to, you can use the DBCC
DBREINDEX command to rebuild the index and reestablish the original
FILLFACTOR specified, also reducing fragmentation.
"Kay" wrote:
> All,
>
> I executed the following statement at my production server.
> DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
> which provided me the following resultset. Besides "AveragePageDensity" and
> "LogicalFragmentation", I paid special attention to "Extent Swithces" which
> is quite high.
>
> Is there any performance gain if I reduce th Extent Switces? How can I
> lessen the value of Extent Switches.
>
> TIA
>
> Kay
>
> ObjectName ObjectId IndexName
> IndexId
> Level Pages Rows MinimumRecordSize MaximumRecordSize AverageRecordSize
> ForwardedRecords Extents ExtentSwitches AverageFreeBytes AveragePageDensity
> ScanDensity BestCount ActualCount LogicalFragmentation ExtentFragmentation
> package 1845581613 PK_package 1 0 527 224182 15 15 15 0 72 77 864.322
> 89.321 84.615 66 78 1.898 18.056
> package 1845581613 package80 5 0 306 224182 9 9 9 0 42 41 37.169
> 99.541 92.857 39 42 1.307 16.667
> package 1845581613 package69 6 0 388 224182 9 12 11.98 0 51 50 18.417
> 99.772 96.078 49 51 0.258 13.725
> package_description 1450240717 PK_package_description 1 0 3226 169022
> 44 434 138.77 0 413 412 720.535 91.098 97.821 404 413 0.217 10.412
> package_description 1450240717 package_description73 2 0 296 169022 12
> 12 12 0 42 43 101.716 98.743 84.091 37 44 1.351 14.286
> package_description 1450240717 idx_package_name 3 0 1904 169022 8 370
> 77.871 0 249 636 1005.600 87.576 37.363 238 637 11.922 22.088
> package_description 1450240717 idx_package_state 4 0 341 169022 12 12
> 12 0 47 129 1156.680 85.709 33.077 43 130 14.663 21.277
> package_description 1450240717 idx_package_hours 5 0 396 169022 16 16
> 16 0 54 79 413.181 94.895 62.500 50 80 5.303 22.222
> package_description 1450240717 idx_package_cost 6 0 411 169022 16 16
> 16 0 56 115 693.576 91.431 44.828 52 116 8.273 17.857
> package_description 1450240717 idx_package_available 7 0 298 169022 12
> 12 12 0 42 48 155.369 98.080 77.551 38 49 2.349 16.667
> package_description 1450240717 tpackage_description 255 0 114597
> 340665 32 8094 2076.854 0 14336 14335 1916.141 76.326 99.923 14325 14336
> 99.999 17.153
> pass_quiz_options 1877581727 0 0 1 2 35 45 40 0 1 0 8012.000 1.013
> 100.000 1 1 0.000 0.000
>
>
>
>

DBCC SHOWCONTIG and Extent Switches

All,
I executed the following statement at my production server.
DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
which provided me the following resultset. Besides "AveragePageDensity" and
"LogicalFragmentation", I paid special attention to "Extent Swithces" which
is quite high.
Is there any performance gain if I reduce th Extent Switces? How can I
lessen the value of Extent Switches.
TIA
Kay
ObjectName ObjectId IndexName
IndexId
Level Pages Rows MinimumRecordSize MaximumRecordSize AverageRecordSize
ForwardedRecords Extents ExtentSwitches AverageFreeBytes AveragePageDensity
ScanDensity BestCount ActualCount LogicalFragmentation ExtentFragmentation
package 1845581613 PK_package 1 0 527 224182 15 15 15 0 72 77 864.322
89.321 84.615 66 78 1.898 18.056
package 1845581613 package80 5 0 306 224182 9 9 9 0 42 41 37.169
99.541 92.857 39 42 1.307 16.667
package 1845581613 package69 6 0 388 224182 9 12 11.98 0 51 50 18.417
99.772 96.078 49 51 0.258 13.725
package_description 1450240717 PK_package_description 1 0 3226 169022
44 434 138.77 0 413 412 720.535 91.098 97.821 404 413 0.217 10.412
package_description 1450240717 package_description73 2 0 296 169022 12
12 12 0 42 43 101.716 98.743 84.091 37 44 1.351 14.286
package_description 1450240717 idx_package_name 3 0 1904 169022 8 370
77.871 0 249 636 1005.600 87.576 37.363 238 637 11.922 22.088
package_description 1450240717 idx_package_state 4 0 341 169022 12 12
12 0 47 129 1156.680 85.709 33.077 43 130 14.663 21.277
package_description 1450240717 idx_package_hours 5 0 396 169022 16 16
16 0 54 79 413.181 94.895 62.500 50 80 5.303 22.222
package_description 1450240717 idx_package_cost 6 0 411 169022 16 16
16 0 56 115 693.576 91.431 44.828 52 116 8.273 17.857
package_description 1450240717 idx_package_available 7 0 298 169022 12
12 12 0 42 48 155.369 98.080 77.551 38 49 2.349 16.667
package_description 1450240717 tpackage_description 255 0 114597
340665 32 8094 2076.854 0 14336 14335 1916.141 76.326 99.923 14325 14336
99.999 17.153
pass_quiz_options 1877581727 0 0 1 2 35 45 40 0 1 0 8012.000 1.013
100.000 1 1 0.000 0.000Kay
http://www.sql-server-performance.c..._showcontig.asp
"Kay" <CallDBA@.hotmail.com> wrote in message
news:OmnmxiiBGHA.3840@.TK2MSFTNGP15.phx.gbl...
> All,
>
> I executed the following statement at my production server.
> DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
> which provided me the following resultset. Besides "AveragePageDensity"
> and "LogicalFragmentation", I paid special attention to "Extent Swithces"
> which is quite high.
>
> Is there any performance gain if I reduce th Extent Switces? How can I
> lessen the value of Extent Switches.
>
> TIA
>
> Kay
>
> ObjectName ObjectId IndexName
> IndexId
> Level Pages Rows MinimumRecordSize MaximumRecordSize AverageRecordSize
> ForwardedRecords Extents ExtentSwitches AverageFreeBytes
> AveragePageDensity ScanDensity BestCount ActualCount LogicalFragmentation
> ExtentFragmentation
> package 1845581613 PK_package 1 0 527 224182 15 15 15 0 72 77 864.322
> 89.321 84.615 66 78 1.898 18.056
> package 1845581613 package80 5 0 306 224182 9 9 9 0 42 41 37.169
> 99.541 92.857 39 42 1.307 16.667
> package 1845581613 package69 6 0 388 224182 9 12 11.98 0 51 50 18.417
> 99.772 96.078 49 51 0.258 13.725
> package_description 1450240717 PK_package_description 1 0 3226 169022
> 44 434 138.77 0 413 412 720.535 91.098 97.821 404 413 0.217 10.412
> package_description 1450240717 package_description73 2 0 296 169022
> 12 12 12 0 42 43 101.716 98.743 84.091 37 44 1.351 14.286
> package_description 1450240717 idx_package_name 3 0 1904 169022 8 370
> 77.871 0 249 636 1005.600 87.576 37.363 238 637 11.922 22.088
> package_description 1450240717 idx_package_state 4 0 341 169022 12 12
> 12 0 47 129 1156.680 85.709 33.077 43 130 14.663 21.277
> package_description 1450240717 idx_package_hours 5 0 396 169022 16 16
> 16 0 54 79 413.181 94.895 62.500 50 80 5.303 22.222
> package_description 1450240717 idx_package_cost 6 0 411 169022 16 16
> 16 0 56 115 693.576 91.431 44.828 52 116 8.273 17.857
> package_description 1450240717 idx_package_available 7 0 298 169022
> 12 12 12 0 42 48 155.369 98.080 77.551 38 49 2.349 16.667
> package_description 1450240717 tpackage_description 255 0 114597
> 340665 32 8094 2076.854 0 14336 14335 1916.141 76.326 99.923 14325 14336
> 99.999 17.153
> pass_quiz_options 1877581727 0 0 1 2 35 45 40 0 1 0 8012.000 1.013
> 100.000 1 1 0.000 0.000
>
>
>|||Based on the result set provided and viewing the clustered index, your exten
t
switches are fine. Extent switches is the number of times the DBCC statement
moved off an extent while it was scanning the pages in the extent. You would
expect an extent switch to happen after the whole extent had been scanned.
The most useful line of output would be the ScanDensity BestCount
ActualCount. This is your measure of fragmentation. The best count is the
ideal number of extents, where as the actual count is the actual number of
extents use to hold the data pages.
I noticed the AveragePageDensity is around 89.321, which indicates a
fillfactor of 90 set for the clustered index. In a clustered index, since th
e
leaf level contains the data, you can use FILLFACTOR to control how much
space to leave in the table itself. By reserving free space, you can avoid
splitting pages to make room for a new entry. NOTE that FILLFACTOR is not
maintained; it only indicates how much space is reserved with the existing
data at the time the index is built. If you need to, you can use the DBCC
DBREINDEX command to rebuild the index and reestablish the original
FILLFACTOR specified, also reducing fragmentation.
"Kay" wrote:

> All,
>
> I executed the following statement at my production server.
> DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
> which provided me the following resultset. Besides "AveragePageDensity" an
d
> "LogicalFragmentation", I paid special attention to "Extent Swithces" whic
h
> is quite high.
>
> Is there any performance gain if I reduce th Extent Switces? How can I
> lessen the value of Extent Switches.
>
> TIA
>
> Kay
>
> ObjectName ObjectId IndexName
> IndexId
> Level Pages Rows MinimumRecordSize MaximumRecordSize AverageRecordSiz
e
> ForwardedRecords Extents ExtentSwitches AverageFreeBytes AveragePageDensit
y
> ScanDensity BestCount ActualCount LogicalFragmentation ExtentFragmentation
> package 1845581613 PK_package 1 0 527 224182 15 15 15 0 72 77 864.32
2
> 89.321 84.615 66 78 1.898 18.056
> package 1845581613 package80 5 0 306 224182 9 9 9 0 42 41 37.169
> 99.541 92.857 39 42 1.307 16.667
> package 1845581613 package69 6 0 388 224182 9 12 11.98 0 51 50 18.41
7
> 99.772 96.078 49 51 0.258 13.725
> package_description 1450240717 PK_package_description 1 0 3226 16902
2
> 44 434 138.77 0 413 412 720.535 91.098 97.821 404 413 0.217 10.412
> package_description 1450240717 package_description73 2 0 296 169022
12
> 12 12 0 42 43 101.716 98.743 84.091 37 44 1.351 14.286
> package_description 1450240717 idx_package_name 3 0 1904 169022 8 37
0
> 77.871 0 249 636 1005.600 87.576 37.363 238 637 11.922 22.088
> package_description 1450240717 idx_package_state 4 0 341 169022 12 1
2
> 12 0 47 129 1156.680 85.709 33.077 43 130 14.663 21.277
> package_description 1450240717 idx_package_hours 5 0 396 169022 16 1
6
> 16 0 54 79 413.181 94.895 62.500 50 80 5.303 22.222
> package_description 1450240717 idx_package_cost 6 0 411 169022 16 16
> 16 0 56 115 693.576 91.431 44.828 52 116 8.273 17.857
> package_description 1450240717 idx_package_available 7 0 298 169022
12
> 12 12 0 42 48 155.369 98.080 77.551 38 49 2.349 16.667
> package_description 1450240717 tpackage_description 255 0 114597
> 340665 32 8094 2076.854 0 14336 14335 1916.141 76.326 99.923 14325 14336
> 99.999 17.153
> pass_quiz_options 1877581727 0 0 1 2 35 45 40 0 1 0 8012.000 1.013
> 100.000 1 1 0.000 0.000
>
>
>
>sql

Wednesday, March 21, 2012

DBCC Problems with syscolumns

Hi, I have run a DBCC CHECKDB and it found 2 Errors in syscolumns. I
have run a CHECKTABLE and found the following Error:
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 3, index ID 2: Page (1:266) could not be processed. See other
errors for details.
Server: Msg 8944, Level 16, State 1, Line 1
Table Corrupt: Object ID 3, index ID 2, page (1:266), row 12. Test
(VarColOffsets + (int)(sizeof (class VarColOffset) * nVarCols) <=
(nextRec - pRec)) failed. Values are 178 and 72.
DBCC results for 'syscolumns'.
There are 6963 rows in 144 pages for object 'syscolumns'.
CHECKTABLE found 0 allocation errors and 2 consistency errors in table
'syscolumns' (object ID 3).
repair_allow_data_loss is the minimum repair level for the errors found
by DBCC CHECKTABLE (EMIS.dbo.syscolumns ).
Can anyone suggest the best way to proceed with this problem. I am a
little dubious about running REPAIR_ALLOW_DATALOSS on syscolumns, I am
unsure of what will happen if sections of syscolumns go missing. I have
run DBCC PAGE ('EMIS',1,266,1) but this has given me nothing to work
with.
Thanks in Advance,
Dave Shaw.Normally i would suggest DBREINDEX but as this is a system table you're not
allowed.
Give SP_FixIndex a try. You will need the DB in single user mode 1st.
"Dave Shaw" <Dave.Shaw1584@.NTLWorld.com> wrote in message
news:1134990883.398840.145620@.z14g2000cwz.googlegroups.com...
> Hi, I have run a DBCC CHECKDB and it found 2 Errors in syscolumns. I
> have run a CHECKTABLE and found the following Error:
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 3, index ID 2: Page (1:266) could not be processed. See other
> errors for details.
> Server: Msg 8944, Level 16, State 1, Line 1
> Table Corrupt: Object ID 3, index ID 2, page (1:266), row 12. Test
> (VarColOffsets + (int)(sizeof (class VarColOffset) * nVarCols) <=
> (nextRec - pRec)) failed. Values are 178 and 72.
> DBCC results for 'syscolumns'.
> There are 6963 rows in 144 pages for object 'syscolumns'.
> CHECKTABLE found 0 allocation errors and 2 consistency errors in table
> 'syscolumns' (object ID 3).
> repair_allow_data_loss is the minimum repair level for the errors found
> by DBCC CHECKTABLE (EMIS.dbo.syscolumns ).
> Can anyone suggest the best way to proceed with this problem. I am a
> little dubious about running REPAIR_ALLOW_DATALOSS on syscolumns, I am
> unsure of what will happen if sections of syscolumns go missing. I have
> run DBCC PAGE ('EMIS',1,266,1) but this has given me nothing to work
> with.
> Thanks in Advance,
> Dave Shaw.
>|||This article may help ...
http://www.windows2000faq.com/Artic...ArticleID=14051
"John Smith" <fsfsdf@.microsoft.com> wrote in message
news:enUNTKJBGHA.3872@.TK2MSFTNGP12.phx.gbl...
> Normally i would suggest DBREINDEX but as this is a system table you're
not
> allowed.
> Give SP_FixIndex a try. You will need the DB in single user mode 1st.
>
> "Dave Shaw" <Dave.Shaw1584@.NTLWorld.com> wrote in message
> news:1134990883.398840.145620@.z14g2000cwz.googlegroups.com...
>|||Thanks John Smith.
I shall give this a try.
Dave.

Thursday, March 29, 2012

dbcc shrinkfile

dbcc shrinkfile

dbcc shrinkfile

Tuesday, March 27, 2012

DBCC SHRINKDATABASE Errors Running SQL Server 2000 , SP3a

DBCC Shrinkdatabase errors

dbcc shrinkdatabase error

dbcc shrinkdatabase error

dbcc shrinkdatabase error

DBCC shrinkdatabase , transaction log shrink

DBCC shrinkdatabase , transaction log shrink

DBCC shrinkdatabase , transaction log shrink

DBCC SHRINKDATABASE

DBCC SHRINKDATABASE

DBCC SHRINKDATABASE

Sunday, March 25, 2012

DBCC SHOWFILESTATS

DBCC SHOWFILESTATS

DBCC SHOWCONTIG and Extent Switches

DBCC SHOWCONTIG and Extent Switches

DBCC SHOWCONTIG and Extent Switches

Wednesday, March 21, 2012

DBCC Problems with syscolumns

DBfile usage

Blog Archive

About Me