DBfile usage: running

Showing posts with label running. Show all posts

Thursday, March 29, 2012

dbcc shrinkfile - how long will it take

Hi,
We are shrinking a large databse using dbcc shrinkfile and it has been
running for hours. Is there any way of determining how long the job still has
left to run (the equivalent of the Oracle dynamic view v$session_longops)?.
Thanks,
AndyNot that I am aware of. I avoid this by shrinking in increments of 100MB.
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Squirrel" <Squirrel@.discussions.microsoft.com> wrote in message
news:956637B6-5C96-4F71-9630-E13702C94D78@.microsoft.com...
> Hi,
> We are shrinking a large databse using dbcc shrinkfile and it has been
> running for hours. Is there any way of determining how long the job still
> has
> left to run (the equivalent of the Oracle dynamic view
> v$session_longops)?.
> Thanks,
> Andy|||Hi,
In SQL Server we can not exactly say how the process is going to run. But as
Kevin mentioned you could try
shrinking the files by providing a lower value (500 MB or soo...). Ensure
that you do a Backup LOG command
before shrinking the tranasction log.
Thanks
Hari
Sql server MVP
"Squirrel" <Squirrel@.discussions.microsoft.com> wrote in message
news:956637B6-5C96-4F71-9630-E13702C94D78@.microsoft.com...
> Hi,
> We are shrinking a large databse using dbcc shrinkfile and it has been
> running for hours. Is there any way of determining how long the job still
> has
> left to run (the equivalent of the Oracle dynamic view
> v$session_longops)?.
> Thanks,
> Andy|||Not in SQL Server 2000. We've put progress reporting in for SQL Server 2005,
but even that is just a percentage complete with an elapsed-time based
extrapolation of the completion time. Basically, there are far too many
variables to consider to have a hope of being able to predict the run-time -
the worst ones being blocking, the starting state of the file/database, and
how much work shrink needs to do. For instance, if another process takes a
lock that shrink needs, shrink will wait forever for that lock.
Have you checked to make sure it is actually progressing and isn't blocked?
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Squirrel" <Squirrel@.discussions.microsoft.com> wrote in message
news:956637B6-5C96-4F71-9630-E13702C94D78@.microsoft.com...
> Hi,
> We are shrinking a large databse using dbcc shrinkfile and it has been
> running for hours. Is there any way of determining how long the job still
has
> left to run (the equivalent of the Oracle dynamic view
v$session_longops)?.
> Thanks,
> Andy|||Not sure if this is related to the original post but I have SQL2000 SP4
install and have tried running DBCC SHRINKDATABASE (MyDB). This results is a
lock that is not released. Ent. Mgr shows "spid 58 (blocked by 58)" the Wait
Type is "PAGEIOLATCH_SH".
I think this maybe a bug in the SP4? I haven't had this problem with a DB
shrink before.
Regards,
John
"Paul S Randal [MS]" wrote:
> Not in SQL Server 2000. We've put progress reporting in for SQL Server 2005,
> but even that is just a percentage complete with an elapsed-time based
> extrapolation of the completion time. Basically, there are far too many
> variables to consider to have a hope of being able to predict the run-time -
> the worst ones being blocking, the starting state of the file/database, and
> how much work shrink needs to do. For instance, if another process takes a
> lock that shrink needs, shrink will wait forever for that lock.
> Have you checked to make sure it is actually progressing and isn't blocked?
> Regards
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Squirrel" <Squirrel@.discussions.microsoft.com> wrote in message
> news:956637B6-5C96-4F71-9630-E13702C94D78@.microsoft.com...
> > Hi,
> > We are shrinking a large databse using dbcc shrinkfile and it has been
> > running for hours. Is there any way of determining how long the job still
> has
> > left to run (the equivalent of the Oracle dynamic view
> v$session_longops)?.
> >
> > Thanks,
> > Andy
>
>

DBCC Shrinkfile

I ran a dbcc shrinkfile after deleting data in order to take thte db down in size and it has been running for 2 days. Can I canel the command? Will the file be partially shrunk? Last year when we did this I ran a re-index command. Will I have to run a bakup or truncate log command? I have been trying to get the client to upgrade from SQL 7 but they just have not done it yet.follow up question. If I cancel how long will it take to finish the cancel operation?

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

DBCC shrinkfile

would DBCC shrinkfile cause any blocking or any hit to an OLTP environment
while its running. Ive got a lot of extra space on some data files that I
want to shrink and was wondering if its safe to do it during our peak
hours... What does it do internally ? Any locking ,etc..Using SQL 2000Yes. It issues a lot of IO and takes short term X page locks. In internal
tests we've seen up to 20% drop in transaction throughput, depending on the
exact workload and hardware configuration. This is unavoidable due to the
operations shrink has to perform.
What proportion of the database size is free-space? Consider not doing the
shrink unless you're really desperate for the disk space or you *know* the
database size won't grow again. If you shrink, the odds are that the
database will have to grow again anyway. As always, depends on your exact
workload etc etc
It is always 'safe' to do a shrink.
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uBcDfwnFEHA.2876@.TK2MSFTNGP09.phx.gbl...
> would DBCC shrinkfile cause any blocking or any hit to an OLTP environment
> while its running. Ive got a lot of extra space on some data files that I
> want to shrink and was wondering if its safe to do it during our peak
> hours... What does it do internally ? Any locking ,etc..Using SQL 2000
>

Tuesday, March 27, 2012

DBCC SHRINKDATABASE not completing

We're running SQL 2005 Enterprise with a very large database (198,893,696
KB); we had been running DBCC SHRINKDATABASE as part of daily operations,
following deletion of about 4 million records, but found that starting late
last week it is no longer completing - even after 12 hours. (It would
normally take 30 minutes). Other operational steps are running ok. We're
not seeing any error entries. Ideas? Thank you.
Hi Jeffrey
First of all, you should seriously reconsider running DBCC SHRINKDATABASE on
a daily basis. It is an incredibly resource intensive operation, that can
end up hurting as much as help. Take a look here:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
http://blogs.msdn.com/sqlserverstorageengine/archive/2007/03/28/turn-auto-shrink-off.aspx
http://blogs.msdn.com/sqlserverstorageengine/archive/2007/04/15/how-to-avoid-using-shrink-in-sql-server-2005.aspx
If you have to use DBCC SHRINKDATABASE, you can look in the
sys.dm_exec_requests view, and look at the percent_complete column to verify
that the operation is making progress, and get a rough idea how much longer
it will take.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Jeffrey Howard" <JeffreyHoward@.discussions.microsoft.com> wrote in message
news:0C88913C-6AF3-4D90-8D08-49B9F0797F02@.microsoft.com...
> We're running SQL 2005 Enterprise with a very large database (198,893,696
> KB); we had been running DBCC SHRINKDATABASE as part of daily operations,
> following deletion of about 4 million records, but found that starting
> late
> last week it is no longer completing - even after 12 hours. (It would
> normally take 30 minutes). Other operational steps are running ok. We're
> not seeing any error entries. Ideas? Thank you.
|||Why shrink today, only to have it grow again tomorrow with the 4M
insert/delete operations? Also it would seem that 4M records in a 200GB
database isn't that much anyway.
TheSQLGuru
President
Indicium Resources, Inc.
"Jeffrey Howard" <JeffreyHoward@.discussions.microsoft.com> wrote in message
news:0C88913C-6AF3-4D90-8D08-49B9F0797F02@.microsoft.com...
> We're running SQL 2005 Enterprise with a very large database (198,893,696
> KB); we had been running DBCC SHRINKDATABASE as part of daily operations,
> following deletion of about 4 million records, but found that starting
> late
> last week it is no longer completing - even after 12 hours. (It would
> normally take 30 minutes). Other operational steps are running ok. We're
> not seeing any error entries. Ideas? Thank you.

DBCC SHRINKDATABASE not completing

We're running SQL 2005 Enterprise with a very large database (198,893,696
KB); we had been running DBCC SHRINKDATABASE as part of daily operations,
following deletion of about 4 million records, but found that starting late
last week it is no longer completing - even after 12 hours. (It would
normally take 30 minutes). Other operational steps are running ok. We're
not seeing any error entries. Ideas? Thank you.Hi Jeffrey
First of all, you should seriously reconsider running DBCC SHRINKDATABASE on
a daily basis. It is an incredibly resource intensive operation, that can
end up hurting as much as help. Take a look here:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
http://blogs.msdn.com/sqlserverstor...
ff.aspx
http://blogs.msdn.com/sqlserverstor...erver-2005.aspx
If you have to use DBCC SHRINKDATABASE, you can look in the
sys.dm_exec_requests view, and look at the percent_complete column to verify
that the operation is making progress, and get a rough idea how much longer
it will take.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Jeffrey Howard" <JeffreyHoward@.discussions.microsoft.com> wrote in message
news:0C88913C-6AF3-4D90-8D08-49B9F0797F02@.microsoft.com...
> We're running SQL 2005 Enterprise with a very large database (198,893,696
> KB); we had been running DBCC SHRINKDATABASE as part of daily operations,
> following deletion of about 4 million records, but found that starting
> late
> last week it is no longer completing - even after 12 hours. (It would
> normally take 30 minutes). Other operational steps are running ok. We're
> not seeing any error entries. Ideas? Thank you.|||Why shrink today, only to have it grow again tomorrow with the 4M
insert/delete operations' Also it would seem that 4M records in a 200GB
database isn't that much anyway.
TheSQLGuru
President
Indicium Resources, Inc.
"Jeffrey Howard" <JeffreyHoward@.discussions.microsoft.com> wrote in message
news:0C88913C-6AF3-4D90-8D08-49B9F0797F02@.microsoft.com...
> We're running SQL 2005 Enterprise with a very large database (198,893,696
> KB); we had been running DBCC SHRINKDATABASE as part of daily operations,
> following deletion of about 4 million records, but found that starting
> late
> last week it is no longer completing - even after 12 hours. (It would
> normally take 30 minutes). Other operational steps are running ok. We're
> not seeing any error entries. Ideas? Thank you.

DBCC SHRINKDATABASE not completing

We're running SQL 2005 Enterprise with a very large database (198,893,696
KB); we had been running DBCC SHRINKDATABASE as part of daily operations,
following deletion of about 4 million records, but found that starting late
last week it is no longer completing - even after 12 hours. (It would
normally take 30 minutes). Other operational steps are running ok. We're
not seeing any error entries. Ideas? Thank you.Hi Jeffrey
First of all, you should seriously reconsider running DBCC SHRINKDATABASE on
a daily basis. It is an incredibly resource intensive operation, that can
end up hurting as much as help. Take a look here:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
http://blogs.msdn.com/sqlserverstorageengine/archive/2007/03/28/turn-auto-shrink-off.aspx
http://blogs.msdn.com/sqlserverstorageengine/archive/2007/04/15/how-to-avoid-using-shrink-in-sql-server-2005.aspx
If you have to use DBCC SHRINKDATABASE, you can look in the
sys.dm_exec_requests view, and look at the percent_complete column to verify
that the operation is making progress, and get a rough idea how much longer
it will take.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Jeffrey Howard" <JeffreyHoward@.discussions.microsoft.com> wrote in message
news:0C88913C-6AF3-4D90-8D08-49B9F0797F02@.microsoft.com...
> We're running SQL 2005 Enterprise with a very large database (198,893,696
> KB); we had been running DBCC SHRINKDATABASE as part of daily operations,
> following deletion of about 4 million records, but found that starting
> late
> last week it is no longer completing - even after 12 hours. (It would
> normally take 30 minutes). Other operational steps are running ok. We're
> not seeing any error entries. Ideas? Thank you.|||Why shrink today, only to have it grow again tomorrow with the 4M
insert/delete operations' Also it would seem that 4M records in a 200GB
database isn't that much anyway.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Jeffrey Howard" <JeffreyHoward@.discussions.microsoft.com> wrote in message
news:0C88913C-6AF3-4D90-8D08-49B9F0797F02@.microsoft.com...
> We're running SQL 2005 Enterprise with a very large database (198,893,696
> KB); we had been running DBCC SHRINKDATABASE as part of daily operations,
> following deletion of about 4 million records, but found that starting
> late
> last week it is no longer completing - even after 12 hours. (It would
> normally take 30 minutes). Other operational steps are running ok. We're
> not seeing any error entries. Ideas? Thank you.

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

Sunday, March 25, 2012

DBCC SHOWCONTIG results: Good/bad?

My company is running Microsoft Navision Axapta as our ERP solution.
We've had some performance problems (high disk loads). After reading
"Microsoft SQL Server 2000 Index Defragmentation Best Practices" at
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
...I decided to run DBCC SHOWCONTIG on the Axapta database.
The resulting report: http://home.c2i.net/hmhaga/sql/showcontig.htm
(500 kb text)
I'm new to administering SQL servers with large datbases and heavy
load. I need som expert opinions! Is this database heavily fragmented
or not? Should I schedule a daily index defragmentation job?
Any feedback appreciated!
H.M.Haga
'98 Subaru Impreza GT
'91 Suzuki Bandit 400
http://www.imprezadriver.com/phpBB2
http://home.c2i.net/hmhaga"Hans-Martin Haga" <hmhaga@.c2i.net> wrote in message
news:8orppvodf2rqgod40vmtgqbmc4leclcs94@.4ax.com...
> My company is running Microsoft Navision Axapta as our ERP solution.
> We've had some performance problems (high disk loads). After reading
> "Microsoft SQL Server 2000 Index Defragmentation Best Practices" at
>
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
> ...I decided to run DBCC SHOWCONTIG on the Axapta database.
> The resulting report: http://home.c2i.net/hmhaga/sql/showcontig.htm
> (500 kb text)
> I'm new to administering SQL servers with large datbases and heavy
> load. I need som expert opinions! Is this database heavily fragmented
> or not? Should I schedule a daily index defragmentation job?
> Any feedback appreciated!
>
Look to the best:actual counts. If there was no fragmentation they would be
close to the same.
However, fragmentation is not necessarily a bad thing. In an OLTP database
(loads of updates, few reports) it is good. In an OLAP database (few
updates, bags of reports) it is bad.
You really need to know why your database is running slow by analysing
output from Profiler and System Manager. There's roughly a gazillion things
that can affect performance, fragmentation being only one of them.
Does it affect just SELECT statements? INSERT/DELETE/UPDATE statements?
Both?
If your Indexes are fragmented, try rebuilding the indexes with a lower
FILL_FACTOR setting. That will cause fewer page splits, though SELECT
statements may theoretically increase as SQL will have to read more pages
per index search.
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.530 / Virus Database: 325 - Release Date: 22/10/2003|||You really don't expect us to go through all that, do you? ;-)
I suggest you run SHOWCONTIG this way:
DBCC SHOWCONTIG WITH TABLERESULTS
That gives you a resultset. I even use below method (from VB code):
INSERT INTO #tbl (...)
DBCC SHOWCONTIG WITH TABLERESULTS
You have to create the temp tables first, with proper columns, but that gives you the ability to do
SELECT with WHERE, ORDER BY etc. Easy to do an ORDER BY LogicalFragmentation DESC, for example.
General tips:
If you don't have > 500 to 1000 pages, don't worry about fragmentation.
If you have > 1 database files, use Logial Fragmentation, scan density will not report correct.
Seems you have some tables without clustered index... Any particular reason?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Hans-Martin Haga" <hmhaga@.c2i.net> wrote in message
news:8orppvodf2rqgod40vmtgqbmc4leclcs94@.4ax.com...
> My company is running Microsoft Navision Axapta as our ERP solution.
> We've had some performance problems (high disk loads). After reading
> "Microsoft SQL Server 2000 Index Defragmentation Best Practices" at
>
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
> ...I decided to run DBCC SHOWCONTIG on the Axapta database.
> The resulting report: http://home.c2i.net/hmhaga/sql/showcontig.htm
> (500 kb text)
> I'm new to administering SQL servers with large datbases and heavy
> load. I need som expert opinions! Is this database heavily fragmented
> or not? Should I schedule a daily index defragmentation job?
> Any feedback appreciated!
>
> H.M.Haga
> '98 Subaru Impreza GT
> '91 Suzuki Bandit 400
> http://www.imprezadriver.com/phpBB2
> http://home.c2i.net/hmhaga|||> Look to the best:actual counts. If there was no fragmentation they would be
> close to the same.
Just watch out if > 1 database file for the filegroup. Jumps between files will count as
fragmentation.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver|||You should read the whitepaper at
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
which explains everything you need to know about managing fragmentation.
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:#0LxzdInDHA.2416@.TK2MSFTNGP10.phx.gbl...
> You really don't expect us to go through all that, do you? ;-)
> I suggest you run SHOWCONTIG this way:
> DBCC SHOWCONTIG WITH TABLERESULTS
> That gives you a resultset. I even use below method (from VB code):
> INSERT INTO #tbl (...)
> DBCC SHOWCONTIG WITH TABLERESULTS
> You have to create the temp tables first, with proper columns, but that
gives you the ability to do
> SELECT with WHERE, ORDER BY etc. Easy to do an ORDER BY
LogicalFragmentation DESC, for example.
> General tips:
> If you don't have > 500 to 1000 pages, don't worry about fragmentation.
> If you have > 1 database files, use Logial Fragmentation, scan density
will not report correct.
> Seems you have some tables without clustered index... Any particular
reason?
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Hans-Martin Haga" <hmhaga@.c2i.net> wrote in message
> news:8orppvodf2rqgod40vmtgqbmc4leclcs94@.4ax.com...
> > My company is running Microsoft Navision Axapta as our ERP solution.
> > We've had some performance problems (high disk loads). After reading
> > "Microsoft SQL Server 2000 Index Defragmentation Best Practices" at
> >
>
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
> >
> > ...I decided to run DBCC SHOWCONTIG on the Axapta database.
> > The resulting report: http://home.c2i.net/hmhaga/sql/showcontig.htm
> > (500 kb text)
> >
> > I'm new to administering SQL servers with large datbases and heavy
> > load. I need som expert opinions! Is this database heavily fragmented
> > or not? Should I schedule a daily index defragmentation job?
> >
> > Any feedback appreciated!
> >
> >
> > H.M.Haga
> > '98 Subaru Impreza GT
> > '91 Suzuki Bandit 400
> > http://www.imprezadriver.com/phpBB2
> > http://home.c2i.net/hmhaga
>|||On Mon, 27 Oct 2003 13:39:52 +0100, "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote:
>You really don't expect us to go through all that, do you? ;-)
No... ;-)
After I read your answers, I signed up for "2072 Administering a MS
SQL Server 2000 Database" :-)
Our server has been running for years AS IS, but after we decided to
put our ERP solution on it - it suddenly requires a lot more attention
- and knowledge!
I've just started to analyze the database, currently it is in the same
state as it was when our vendor installed the solution (Axapta).
H.M.Haga
'98 Subaru Impreza GT
'91 Suzuki Bandit 400
http://www.imprezadriver.com/phpBB2
http://home.c2i.net/hmhaga|||> After I read your answers, I signed up for "2072 Administering a MS
> SQL Server 2000 Database" :-)
Teaching those courses, I just want to say that the Admin course do not deal with indexes,
fragmentation and such. The programming course does (2073). Just a heads up. :-)
Again, check out the tables with some 500 pages or more, and concentrate on those. Also, consider
why some tables doesn't have clustered indexes. That would be a good start. And read the paper that
Paul referred to. My tips...
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Hans-Martin Haga" <hmhaga@.c2i.net> wrote in message
news:dlfspv8vi4esvgf2cv2ktgv3ot1ejp9pht@.4ax.com...
> On Mon, 27 Oct 2003 13:39:52 +0100, "Tibor Karaszi"
> <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote:
> >You really don't expect us to go through all that, do you? ;-)
> No... ;-)
> After I read your answers, I signed up for "2072 Administering a MS
> SQL Server 2000 Database" :-)
> Our server has been running for years AS IS, but after we decided to
> put our ERP solution on it - it suddenly requires a lot more attention
> - and knowledge!
> I've just started to analyze the database, currently it is in the same
> state as it was when our vendor installed the solution (Axapta).
>
> H.M.Haga
> '98 Subaru Impreza GT
> '91 Suzuki Bandit 400
> http://www.imprezadriver.com/phpBB2
> http://home.c2i.net/hmhaga|||On Tue, 28 Oct 2003 12:08:46 +0100, "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote:
>Teaching those courses, I just want to say that the Admin course do not deal with indexes,
>fragmentation and such. The programming course does (2073). Just a heads up. :-)
Thanks! I've just signed 2073 as well, 2 weeks of SQL courses then :)
As for clustered index, neither the database nor the application has
been tuned or optimized in any way after the implementation - so I
guess that is up to me. Our dealer has very limited knowledge of SQL
(it's like a black box), they've put a layer of Axapta application
logic between themself and SQL! ;-)
H.M.Haga
'98 Subaru Impreza GT
'91 Suzuki Bandit 400
http://www.imprezadriver.com/phpBB2
http://home.c2i.net/hmhaga

Thursday, March 22, 2012

DBCC SHOWCONTIG

I've been running above procedure using the "with all_indexes, fast;" option
on a table in my database. This table has 18 indexes. The results in this
procedure refer to index 1, 2, ..., 18. I don't relate to ID number as my
indexes all have english names (e.g. Account, LastName, etc.). I'm not sure
which index ID is referring to what named index. Are these ID numbers simply
the order in which they appear when I see the list in Enterprise Manager,
Manage Indexes option? Is there a way to have the DBCC SHOWCONTIG procedure
reflect the actual index names I had assigned?
Thanks, Jim
Hi Jim
What version are you using? In SQL 2005 there is a replacement for DBCC
SHOWCONTIG that allows you to filter the results and add more information to
the output. The output is much more readable, too. DBCC is not a procedure,
so there is very little you can do to modify how it works.
You cannot assume the index numbers are the same as the order the indexes
come back in the EM list. You can run this query to see what number goes
with what index: (substitute your own table name, of course)
SELECT indid, name
FROM sysindexes
WHERE id = object_id('your-table-name')
AND indexproperty(id,name, 'isStatistics') = 0
AND indexproperty(id,name, 'isHypothetical') = 0
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Jim B" <JB@.lightning.com> wrote in message
news:1BE95CD5-1977-47C9-8778-57DBA4A9F183@.microsoft.com...
> I've been running above procedure using the "with all_indexes, fast;"
> option
> on a table in my database. This table has 18 indexes. The results in
> this
> procedure refer to index 1, 2, ..., 18. I don't relate to ID number as my
> indexes all have english names (e.g. Account, LastName, etc.). I'm not
> sure
> which index ID is referring to what named index. Are these ID numbers
> simply
> the order in which they appear when I see the list in Enterprise Manager,
> Manage Indexes option? Is there a way to have the DBCC SHOWCONTIG
> procedure
> reflect the actual index names I had assigned?
> --
> Thanks, Jim
|||Kalen. Thanks for the quick reply. I have to to support sites that use
either SQL2000 or SQL2005. What would be the better option to DBCC
SHOWCONTIG that you mentioned for SQL2005. The query you gave me worked
great in both 2000 and 2005. Thanks again for help.
Thanks, Jim
"Kalen Delaney" wrote:

> Hi Jim
> What version are you using? In SQL 2005 there is a replacement for DBCC
> SHOWCONTIG that allows you to filter the results and add more information to
> the output. The output is much more readable, too. DBCC is not a procedure,
> so there is very little you can do to modify how it works.
> You cannot assume the index numbers are the same as the order the indexes
> come back in the EM list. You can run this query to see what number goes
> with what index: (substitute your own table name, of course)
> SELECT indid, name
> FROM sysindexes
> WHERE id = object_id('your-table-name')
> AND indexproperty(id,name, 'isStatistics') = 0
> AND indexproperty(id,name, 'isHypothetical') = 0
>
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Jim B" <JB@.lightning.com> wrote in message
> news:1BE95CD5-1977-47C9-8778-57DBA4A9F183@.microsoft.com...
>
>
|||Jim
In SQL 2005 there is a table valued function called
sys.dm_db_index_physical_stats. It returns a LOT of information, but you can
filter it in any way you like, and since it returns table results, you can
join it to the sys.indexes to get the index name, and you can restrict the
columns coming back to just what you need.
You should read about it in BOL first, and if you have a SQL Server Magazine
subscription, you can find a couple of articles I wrote about it on their
site.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Jim B" <JB@.lightning.com> wrote in message
news:1E20DDE1-6435-4ED1-A4F4-E7402C3B05B2@.microsoft.com...[vbcol=seagreen]
> Kalen. Thanks for the quick reply. I have to to support sites that use
> either SQL2000 or SQL2005. What would be the better option to DBCC
> SHOWCONTIG that you mentioned for SQL2005. The query you gave me worked
> great in both 2000 and 2005. Thanks again for help.
> --
> Thanks, Jim
>
> "Kalen Delaney" wrote:

DBCC showcontig

Hello,
I have a very fragmented database and am running dbcc dbreindex but
getting a very odd issue.
When running DBCC SHOWCONTIG before and after reindexing the tables,
there is no difference in the statistics. The scan fragmentation levels
(in fact all of the statistics) are exactly the same.
How can this be?
I've also run the indexdefrag command and it gives exactly the same
results before and after.
Am i doing something very wrong?
Cheers in advance,
j
Hi, can you show an output of DBCC command?
"jumblesale" <mcgants@.gmail.com> wrote in message
news:1158146780.765411.233730@.e3g2000cwe.googlegro ups.com...
> Hello,
> I have a very fragmented database and am running dbcc dbreindex but
> getting a very odd issue.
> When running DBCC SHOWCONTIG before and after reindexing the tables,
> there is no difference in the statistics. The scan fragmentation levels
> (in fact all of the statistics) are exactly the same.
> How can this be?
> I've also run the indexdefrag command and it gives exactly the same
> results before and after.
> Am i doing something very wrong?
> Cheers in advance,
> j
>
|||Hi,
Based on the SHOWCONTIG result; take a look into Scan Density, Login scan
and Extend scan fragmentation.
Read this article; this help you to analyze more:-
http://www.sql-server-performance.co...gmentation.asp
Thanks
Hari
SQL Server MVP
"jumblesale" <mcgants@.gmail.com> wrote in message
news:1158146780.765411.233730@.e3g2000cwe.googlegro ups.com...
> Hello,
> I have a very fragmented database and am running dbcc dbreindex but
> getting a very odd issue.
> When running DBCC SHOWCONTIG before and after reindexing the tables,
> there is no difference in the statistics. The scan fragmentation levels
> (in fact all of the statistics) are exactly the same.
> How can this be?
> I've also run the indexdefrag command and it gives exactly the same
> results before and after.
> Am i doing something very wrong?
> Cheers in advance,
> j
>
|||On 13 Sep 2006 04:26:20 -0700, "jumblesale" <mcgants@.gmail.com> wrote:

>Hello,
>I have a very fragmented database and am running dbcc dbreindex but
>getting a very odd issue.
>When running DBCC SHOWCONTIG before and after reindexing the tables,
>there is no difference in the statistics. The scan fragmentation levels
>(in fact all of the statistics) are exactly the same.
>How can this be?
>I've also run the indexdefrag command and it gives exactly the same
>results before and after.
>Am i doing something very wrong?
Do you have any clustered indexes on the tables involved?
J.

DBCC showcontig

Hello,
I have a very fragmented database and am running dbcc dbreindex but
getting a very odd issue.
When running DBCC SHOWCONTIG before and after reindexing the tables,
there is no difference in the statistics. The scan fragmentation levels
(in fact all of the statistics) are exactly the same.
How can this be?
I've also run the indexdefrag command and it gives exactly the same
results before and after.
Am i doing something very wrong'
Cheers in advance,
jHi, can you show an output of DBCC command?
"jumblesale" <mcgants@.gmail.com> wrote in message
news:1158146780.765411.233730@.e3g2000cwe.googlegroups.com...
> Hello,
> I have a very fragmented database and am running dbcc dbreindex but
> getting a very odd issue.
> When running DBCC SHOWCONTIG before and after reindexing the tables,
> there is no difference in the statistics. The scan fragmentation levels
> (in fact all of the statistics) are exactly the same.
> How can this be?
> I've also run the indexdefrag command and it gives exactly the same
> results before and after.
> Am i doing something very wrong'
> Cheers in advance,
> j
>|||Hi,
Based on the SHOWCONTIG result; take a look into Scan Density, Login scan
and Extend scan fragmentation.
Read this article; this help you to analyze more:-
http://www.sql-server-performance.com/rd_index_fragmentation.asp
Thanks
Hari
SQL Server MVP
"jumblesale" <mcgants@.gmail.com> wrote in message
news:1158146780.765411.233730@.e3g2000cwe.googlegroups.com...
> Hello,
> I have a very fragmented database and am running dbcc dbreindex but
> getting a very odd issue.
> When running DBCC SHOWCONTIG before and after reindexing the tables,
> there is no difference in the statistics. The scan fragmentation levels
> (in fact all of the statistics) are exactly the same.
> How can this be?
> I've also run the indexdefrag command and it gives exactly the same
> results before and after.
> Am i doing something very wrong'
> Cheers in advance,
> j
>|||On 13 Sep 2006 04:26:20 -0700, "jumblesale" <mcgants@.gmail.com> wrote:
>Hello,
>I have a very fragmented database and am running dbcc dbreindex but
>getting a very odd issue.
>When running DBCC SHOWCONTIG before and after reindexing the tables,
>there is no difference in the statistics. The scan fragmentation levels
>(in fact all of the statistics) are exactly the same.
>How can this be?
>I've also run the indexdefrag command and it gives exactly the same
>results before and after.
>Am i doing something very wrong'
Do you have any clustered indexes on the tables involved?
J.sql

DBCC showcontig

Hello,
I have a very fragmented database and am running dbcc dbreindex but
getting a very odd issue.
When running DBCC SHOWCONTIG before and after reindexing the tables,
there is no difference in the statistics. The scan fragmentation levels
(in fact all of the statistics) are exactly the same.
How can this be?
I've also run the indexdefrag command and it gives exactly the same
results before and after.
Am i doing something very wrong'
Cheers in advance,
jHi, can you show an output of DBCC command?
"jumblesale" <mcgants@.gmail.com> wrote in message
news:1158146780.765411.233730@.e3g2000cwe.googlegroups.com...
> Hello,
> I have a very fragmented database and am running dbcc dbreindex but
> getting a very odd issue.
> When running DBCC SHOWCONTIG before and after reindexing the tables,
> there is no difference in the statistics. The scan fragmentation levels
> (in fact all of the statistics) are exactly the same.
> How can this be?
> I've also run the indexdefrag command and it gives exactly the same
> results before and after.
> Am i doing something very wrong'
> Cheers in advance,
> j
>|||Hi,
Based on the SHOWCONTIG result; take a look into Scan Density, Login scan
and Extend scan fragmentation.
Read this article; this help you to analyze more:-
http://www.sql-server-performance.c...agmentation.asp
Thanks
Hari
SQL Server MVP
"jumblesale" <mcgants@.gmail.com> wrote in message
news:1158146780.765411.233730@.e3g2000cwe.googlegroups.com...
> Hello,
> I have a very fragmented database and am running dbcc dbreindex but
> getting a very odd issue.
> When running DBCC SHOWCONTIG before and after reindexing the tables,
> there is no difference in the statistics. The scan fragmentation levels
> (in fact all of the statistics) are exactly the same.
> How can this be?
> I've also run the indexdefrag command and it gives exactly the same
> results before and after.
> Am i doing something very wrong'
> Cheers in advance,
> j
>|||On 13 Sep 2006 04:26:20 -0700, "jumblesale" <mcgants@.gmail.com> wrote:

>Hello,
>I have a very fragmented database and am running dbcc dbreindex but
>getting a very odd issue.
>When running DBCC SHOWCONTIG before and after reindexing the tables,
>there is no difference in the statistics. The scan fragmentation levels
>(in fact all of the statistics) are exactly the same.
>How can this be?
>I've also run the indexdefrag command and it gives exactly the same
>results before and after.
>Am i doing something very wrong'
Do you have any clustered indexes on the tables involved?
J.

Wednesday, March 21, 2012

DBCC pintable

Hi everyone,
I'm currently running sql 2k on win2k3, all with the
latest patches in a clustered/merge replication
environment. I'm considering running the dbcc pintable
command for one small (~38MB), but very hot table in our
database. I've never used this command before so I was
looking for any direction here or any "gotchas" I should
look out for. Any advice would be appreciated. Thanks.
Leon
Leon,
If the table is small and used a lot, you're not going to get any benefit
from pinning it; it will already be in memory anyway do to its constantly
being accessed. Remember that pinning doesn't tell SQL Server to load the
data into memory; rather, it tells it to keep it in memory once it's been
loaded by something else. And SQL Server will do that anyway if requests
keep coming in for the same data.
"Leon" <anonymous@.discussions.microsoft.com> wrote in message
news:8d6001c49680$46eddaf0$a601280a@.phx.gbl...
> Hi everyone,
> I'm currently running sql 2k on win2k3, all with the
> latest patches in a clustered/merge replication
> environment. I'm considering running the dbcc pintable
> command for one small (~38MB), but very hot table in our
> database. I've never used this command before so I was
> looking for any direction here or any "gotchas" I should
> look out for. Any advice would be appreciated. Thanks.
> Leon
|||I agree 100% with Adam here. Pinning tables usually has a more negative
effect than a positive one since it will keep any data in memory even if it
has only been accessed a single time. SQL Server usually does a much better
job at managing the cache than a human can.
Andrew J. Kelly SQL MVP
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OB5TGSolEHA.3720@.TK2MSFTNGP12.phx.gbl...
> Leon,
> If the table is small and used a lot, you're not going to get any benefit
> from pinning it; it will already be in memory anyway do to its constantly
> being accessed. Remember that pinning doesn't tell SQL Server to load the
> data into memory; rather, it tells it to keep it in memory once it's been
> loaded by something else. And SQL Server will do that anyway if requests
> keep coming in for the same data.
>
> "Leon" <anonymous@.discussions.microsoft.com> wrote in message
> news:8d6001c49680$46eddaf0$a601280a@.phx.gbl...
>

DBCC pintable

Hi everyone,
I'm currently running sql 2k on win2k3, all with the
latest patches in a clustered/merge replication
environment. I'm considering running the dbcc pintable
command for one small (~38MB), but very hot table in our
database. I've never used this command before so I was
looking for any direction here or any "gotchas" I should
look out for. Any advice would be appreciated. Thanks.
LeonLeon,
If the table is small and used a lot, you're not going to get any benefit
from pinning it; it will already be in memory anyway do to its constantly
being accessed. Remember that pinning doesn't tell SQL Server to load the
data into memory; rather, it tells it to keep it in memory once it's been
loaded by something else. And SQL Server will do that anyway if requests
keep coming in for the same data.
"Leon" <anonymous@.discussions.microsoft.com> wrote in message
news:8d6001c49680$46eddaf0$a601280a@.phx.gbl...
> Hi everyone,
> I'm currently running sql 2k on win2k3, all with the
> latest patches in a clustered/merge replication
> environment. I'm considering running the dbcc pintable
> command for one small (~38MB), but very hot table in our
> database. I've never used this command before so I was
> looking for any direction here or any "gotchas" I should
> look out for. Any advice would be appreciated. Thanks.
> Leon|||I agree 100% with Adam here. Pinning tables usually has a more negative
effect than a positive one since it will keep any data in memory even if it
has only been accessed a single time. SQL Server usually does a much better
job at managing the cache than a human can.
--
Andrew J. Kelly SQL MVP
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OB5TGSolEHA.3720@.TK2MSFTNGP12.phx.gbl...
> Leon,
> If the table is small and used a lot, you're not going to get any benefit
> from pinning it; it will already be in memory anyway do to its constantly
> being accessed. Remember that pinning doesn't tell SQL Server to load the
> data into memory; rather, it tells it to keep it in memory once it's been
> loaded by something else. And SQL Server will do that anyway if requests
> keep coming in for the same data.
>
> "Leon" <anonymous@.discussions.microsoft.com> wrote in message
> news:8d6001c49680$46eddaf0$a601280a@.phx.gbl...
> > Hi everyone,
> > I'm currently running sql 2k on win2k3, all with the
> > latest patches in a clustered/merge replication
> > environment. I'm considering running the dbcc pintable
> > command for one small (~38MB), but very hot table in our
> > database. I've never used this command before so I was
> > looking for any direction here or any "gotchas" I should
> > look out for. Any advice would be appreciated. Thanks.
> >
> > Leon
>sql

dbcc opentran results

I have a database with a large (and growing) transaction log. Running
dbcc opentran yields the following two rows:
REPL_DIST_OLD_LSN (0:0:0)
REPL_NONDIST_OLD_LSN (508734:17171:1)
A search through BOL and the news groups yields no information on the
meaning of these values. The database is published nightly using
snapshot replication. Any help interpreting these values would be
greatly appreciated. My goal is to truncate the log back to a more
reasonable size. Thanks.You might want to post this to the replication group, as you are more likely to find replication experts
there.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Larry Myers" <lmyers@.swinformatics.com> wrote in message
news:73e8147a.0408190756.45c8213c@.posting.google.com...
> I have a database with a large (and growing) transaction log. Running
> dbcc opentran yields the following two rows:
> REPL_DIST_OLD_LSN (0:0:0)
> REPL_NONDIST_OLD_LSN (508734:17171:1)
> A search through BOL and the news groups yields no information on the
> meaning of these values. The database is published nightly using
> snapshot replication. Any help interpreting these values would be
> greatly appreciated. My goal is to truncate the log back to a more
> reasonable size. Thanks.|||To me, it seems to indicate that you have at least one table that is setup
for transactional replication, but the log reader is not running. Probaly
failed with an error. Could you check your distribution server to make sure
the log reader is running?
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Larry Myers" <lmyers@.swinformatics.com> wrote in message
news:73e8147a.0408190756.45c8213c@.posting.google.com...
I have a database with a large (and growing) transaction log. Running
dbcc opentran yields the following two rows:
REPL_DIST_OLD_LSN (0:0:0)
REPL_NONDIST_OLD_LSN (508734:17171:1)
A search through BOL and the news groups yields no information on the
meaning of these values. The database is published nightly using
snapshot replication. Any help interpreting these values would be
greatly appreciated. My goal is to truncate the log back to a more
reasonable size. Thanks.

Monday, March 19, 2012

DBCC Logical Scan Bytes/Sec

Hi,
I have a process data acquisition system running with SQL 2000
Enterprise Manager. I have a large hard drive Read activity. By using the
performance monitor, I found that this reading activity is clearly associate
with DBCC logical scan bytes/sec counter and is mostly twin with Physical
Hard drive read/sec.
The point is that I didn't find what trigger this scan. I observed that more
my database is growing more this scan take time. Actually the hard drive is
busy at more that 99% of the time to read and data request take more and
more time.
Is those DBCC scans are essential and how can I identify what is triggering
them?
Regards
MarcDo you have any maintenance tasks scheduled that run a DBCC CHECKDB? Use
profiler to see what commands are being run at that time.
Andrew J. Kelly SQL MVP
"marc quirion" <mquirion@.videotron.ca> wrote in message
news:I4pFe.52350$mv2.811211@.weber.videotron.net...
> Hi,
> I have a process data acquisition system running with SQL 2000
> Enterprise Manager. I have a large hard drive Read activity. By using the
> performance monitor, I found that this reading activity is clearly
> associate with DBCC logical scan bytes/sec counter and is mostly twin with
> Physical Hard drive read/sec.
> The point is that I didn't find what trigger this scan. I observed that
> more my database is growing more this scan take time. Actually the hard
> drive is busy at more that 99% of the time to read and data request take
> more and more time.
> Is those DBCC scans are essential and how can I identify what is
> triggering them?
>
> Regards
> Marc
>|||Hi Mr. Kelly, thanks for the reply.
That was a thing I was suspecting but I didn't know how to trace it. I used
SQL Profiler, like suggested, and I found many querys who use the DBCC
UPDATEUSAGE command. I think this is another disk intensive command any way
I found some with a duration over 250000 ( milliseconds I guess ).
Is there any reason to use this command frequently?
Thanks again for the help. I'll try to reach to conceptors of the DB to see
why they're using this command intensively.
Regards
Marc
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> a crit dans le message de
news: ubKtOedkFHA.2044@.TK2MSFTNGP10.phx.gbl...
> Do you have any maintenance tasks scheduled that run a DBCC CHECKDB? Use
> profiler to see what commands are being run at that time.
> --
> Andrew J. Kelly SQL MVP
>
> "marc quirion" <mquirion@.videotron.ca> wrote in message
> news:I4pFe.52350$mv2.811211@.weber.videotron.net...
>|||That command certainly would do it. There is no real reason to update it so
often. If they need that kind of accurate data that often they should
rethink what they are doing and why.
Andrew J. Kelly SQL MVP
"marc quirion" <mquirion@.videotron.ca> wrote in message
news:8vnGe.8806$nx3.312875@.wagner.videotron.net...
> Hi Mr. Kelly, thanks for the reply.
> That was a thing I was suspecting but I didn't know how to trace it. I
> used
> SQL Profiler, like suggested, and I found many querys who use the DBCC
> UPDATEUSAGE command. I think this is another disk intensive command any
> way
> I found some with a duration over 250000 ( milliseconds I guess ).
> Is there any reason to use this command frequently?
> Thanks again for the help. I'll try to reach to conceptors of the DB to
> see
> why they're using this command intensively.
> Regards
> Marc
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> a crit dans le message de
> news: ubKtOedkFHA.2044@.TK2MSFTNGP10.phx.gbl...
>

DBCC issues,,, Please help asap

Hi,
I would like to know if running - dbcc checkdb - on a database makes any
modifications at all to the database. It is being recommended to me that it
is a good idea to take close all open connects (shutdown all applications
using the SQL server) and then run dbcc on a regular basis. I am not too
keen on having to shutdown all apps.
I understand you can provide additional parameter to have dbcc fix the
database but would to know if simply running dbcc checkdb causes any
changes. Also, can you limit how much processing power it uses during the
run?
Thank you in advance for your help.Running DBCC CHECKDB (or CHECKTABLE) doesn't change anything provided that
you do not run with REPAIR option.
There is no way to limit processing power that it uses.
The reason why it is better to run DBCC CHECKDB without any user connection
is to avoid spurious error reported sometimes.
Yih-Yoon Lee
On Wed, 24 Mar 2004 09:13:46 -0800, Dragon wrote:

> Hi,
> I would like to know if running - dbcc checkdb - on a database makes any
> modifications at all to the database. It is being recommended to me that i
t
> is a good idea to take close all open connects (shutdown all applications
> using the SQL server) and then run dbcc on a regular basis. I am not too
> keen on having to shutdown all apps.
> I understand you can provide additional parameter to have dbcc fix the
> database but would to know if simply running dbcc checkdb causes any
> changes. Also, can you limit how much processing power it uses during the
> run?
> Thank you in advance for your help.|||On the Enterprise version on a multi-proc box, the DBCC command may run in
parallel. You can prevent this using documented (in BOL) and supported trace
flag 2528 - thus limiting the processing power available for use.
You do not need to shut down all connections. Very rarely a spurious error
may be reported because of the log analysis involved in producing a
transactionally consistent view of the database for checking.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Yih-Yoon Lee" <yihyoon@.hotmail.com> wrote in message
news:1p0m87330awrs.1tyv2rue7pf8k.dlg@.40tude.net...
> Running DBCC CHECKDB (or CHECKTABLE) doesn't change anything provided that
> you do not run with REPAIR option.
> There is no way to limit processing power that it uses.
> The reason why it is better to run DBCC CHECKDB without any user
connection
> is to avoid spurious error reported sometimes.
> Yih-Yoon Lee
> On Wed, 24 Mar 2004 09:13:46 -0800, Dragon wrote:
>
any
it
applications
the|||Hi Dragon.
If you do use WITH REPAIR, just make sure you back up the database first.
This is always a good practise prior to performing any maintenance
functions. As long as the database is not huge, I'll often perform a backup
before and after using repair & potentially discard the first backup if no
errors were reported..
Regards,
Greg Linwood
SQL Server MVP
"Dragon" <NoSpam_Baadil@.hotmail.com> wrote in message
news:uXwBfNcEEHA.3568@.tk2msftngp13.phx.gbl...
> Hi,
> I would like to know if running - dbcc checkdb - on a database makes any
> modifications at all to the database. It is being recommended to me that
it
> is a good idea to take close all open connects (shutdown all applications
> using the SQL server) and then run dbcc on a regular basis. I am not too
> keen on having to shutdown all apps.
> I understand you can provide additional parameter to have dbcc fix the
> database but would to know if simply running dbcc checkdb causes any
> changes. Also, can you limit how much processing power it uses during the
> run?
> Thank you in advance for your help.
>|||Thank you for your reply.
Is it true that when running DBCC checkdb it locks the table it is working
on? If this is true, then it will kill my app and it is necessary for me to
shutdown apps first.
Thank you.
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:O6Mn2pdEEHA.4080@.TK2MSFTNGP09.phx.gbl...
> On the Enterprise version on a multi-proc box, the DBCC command may run in
> parallel. You can prevent this using documented (in BOL) and supported
trace
> flag 2528 - thus limiting the processing power available for use.
> You do not need to shut down all connections. Very rarely a spurious error
> may be reported because of the log analysis involved in producing a
> transactionally consistent view of the database for checking.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Yih-Yoon Lee" <yihyoon@.hotmail.com> wrote in message
> news:1p0m87330awrs.1tyv2rue7pf8k.dlg@.40tude.net...
that
> connection
> any
that
> it
> applications
too
> the
>|||Thank you Greg for your reply. :-)
I always make 'Before' and 'After' backups and keep 'em for at least a week.
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:u9gxrjeEEHA.3672@.TK2MSFTNGP09.phx.gbl...
> Hi Dragon.
> If you do use WITH REPAIR, just make sure you back up the database first.
> This is always a good practise prior to performing any maintenance
> functions. As long as the database is not huge, I'll often perform a
backup
> before and after using repair & potentially discard the first backup if no
> errors were reported..
> Regards,
> Greg Linwood
> SQL Server MVP
> "Dragon" <NoSpam_Baadil@.hotmail.com> wrote in message
> news:uXwBfNcEEHA.3568@.tk2msftngp13.phx.gbl...
any
> it
applications
the
>|||Hi Dragon.
It won't by default, but there's a WITH TABLOCK option which will acquire a
table lock if you use it. This is written up in Books Online..
Regards,
Greg Linwood
SQL Server MVP
"Dragon" <NoSpam_Baadil@.hotmail.com> wrote in message
news:u4382sfEEHA.624@.TK2MSFTNGP10.phx.gbl...
> Thank you for your reply.
> Is it true that when running DBCC checkdb it locks the table it is working
> on? If this is true, then it will kill my app and it is necessary for me
to
> shutdown apps first.
> Thank you.
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:O6Mn2pdEEHA.4080@.TK2MSFTNGP09.phx.gbl...
in
> trace
error
> rights.
> that
makes
> that
> too
the
during
>|||I was just stating the obvious.. (:
Regards,
Greg Linwood
SQL Server MVP
"Dragon" <NoSpam_Baadil@.hotmail.com> wrote in message
news:O5cqltfEEHA.1092@.TK2MSFTNGP12.phx.gbl...
> Thank you Greg for your reply. :-)
> I always make 'Before' and 'After' backups and keep 'em for at least a
week.
>
> "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> news:u9gxrjeEEHA.3672@.TK2MSFTNGP09.phx.gbl...
first.
> backup
no
> any
that
> applications
too
> the
>

Thursday, March 29, 2012

dbcc shrinkfile - how long will it take

DBCC Shrinkfile

dbcc shrinkfile

dbcc shrinkfile

dbcc shrinkfile

DBCC shrinkfile

Tuesday, March 27, 2012

DBCC SHRINKDATABASE not completing

DBCC SHRINKDATABASE not completing

DBCC SHRINKDATABASE not completing

DBCC SHRINKDATABASE Errors Running SQL Server 2000 , SP3a

Sunday, March 25, 2012

DBCC SHOWCONTIG results: Good/bad?

Thursday, March 22, 2012

DBCC SHOWCONTIG

DBCC showcontig

DBCC showcontig

DBCC showcontig

Wednesday, March 21, 2012

DBCC pintable

DBCC pintable

dbcc opentran results

Monday, March 19, 2012

DBCC Logical Scan Bytes/Sec

DBCC issues,,, Please help asap

DBfile usage

Blog Archive

About Me