DBCC Shrinkfile

Hi,
I am plng to shrink a production database logfile from
14 GB to 3 GB. Does this slow down the application or does
it block users from accessing the database while i am
shrinking it ? It is an OLTP system. Also any idea how
much time shrinking a 14GB file would take ? Just
ballpark ..
TIA
MOHi MO,
Its best to update your stats on the DB and run your DBCCs first then do a
backup. The shrik should not take that long I would expect 15 min at the
outside. However, it will slow the system down and the more load from users
that is on the box the longer it will take. I would recommend shceduling
this for a slow time of the day I usually do this after 3am.
Hope that helps
John ...
"Mo" <anonymous@.discussions.microsoft.com> wrote in message
news:068901c3fd47$74bb8ea0$a601280a@.phx.gbl...
> Hi,
> I am plng to shrink a production database logfile from
> 14 GB to 3 GB. Does this slow down the application or does
> it block users from accessing the database while i am
> shrinking it ? It is an OLTP system. Also any idea how
> much time shrinking a 14GB file would take ? Just
> ballpark ..
> TIA
> MO|||Mo - there's no way to predict how long a shrink will take on a live system
as it could block waiting for a lock or spend time searching for free space
if the space usage within the database is fragmented. It also depends on how
much heap and text usage you have, the speed of your IO subsystem, the
concurrent workload (as John says below) and so on. I'd be very surprised to
see it take only 15 minutes. The best you can do is time it on a completely
quiescent system and then extrapolate.
Shrink is setup to be the deadlock victim in all cases so should not cause
deadlocks. It does not hold long-term locks so should not block although
because it does hold page locks while moving pages, and does a bunch of IO
you should expect some slowdown. On a test system setup with a TPCC
benchmark workload running at close to 100% cpu, I've seen a roughly 20%
drop in transaction throughput while shrink is running - YMMV.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"John VanderVliet" <john.vandervliet@.sjrb.ca.NOSPAM> wrote in message
news:e6MPetW$DHA.2808@.TK2MSFTNGP10.phx.gbl...
> Hi MO,
> Its best to update your stats on the DB and run your DBCCs first then do a
> backup. The shrik should not take that long I would expect 15 min at the
> outside. However, it will slow the system down and the more load from
users
> that is on the box the longer it will take. I would recommend shceduling
> this for a slow time of the day I usually do this after 3am.
> Hope that helps
> John ...
> "Mo" <anonymous@.discussions.microsoft.com> wrote in message
> news:068901c3fd47$74bb8ea0$a601280a@.phx.gbl...
> > Hi,
> > I am plng to shrink a production database logfile from
> > 14 GB to 3 GB. Does this slow down the application or does
> > it block users from accessing the database while i am
> > shrinking it ? It is an OLTP system. Also any idea how
> > much time shrinking a 14GB file would take ? Just
> > ballpark ..
> > TIA
> > MO
>

DBCC Shrinkfile

Hi,
I am plng to shrink a production database logfile from
14 GB to 3 GB. Does this slow down the application or does
it block users from accessing the database while i am
shrinking it ? It is an OLTP system. Also any idea how
much time shrinking a 14GB file would take ? Just
ballpark ..
TIA
MOHi MO,
Its best to update your stats on the DB and run your DBCCs first then do a
backup. The shrik should not take that long I would expect 15 min at the
outside. However, it will slow the system down and the more load from users
that is on the box the longer it will take. I would recommend shceduling
this for a slow time of the day I usually do this after 3am.
Hope that helps
John ...
"Mo" <anonymous@.discussions.microsoft.com> wrote in message
news:068901c3fd47$74bb8ea0$a601280a@.phx.gbl...
> Hi,
> I am plng to shrink a production database logfile from
> 14 GB to 3 GB. Does this slow down the application or does
> it block users from accessing the database while i am
> shrinking it ? It is an OLTP system. Also any idea how
> much time shrinking a 14GB file would take ? Just
> ballpark ..
> TIA
> MO|||Mo - there's no way to predict how long a shrink will take on a live system
as it could block waiting for a lock or spend time searching for free space
if the space usage within the database is fragmented. It also depends on how
much heap and text usage you have, the speed of your IO subsystem, the
concurrent workload (as John says below) and so on. I'd be very surprised to
see it take only 15 minutes. The best you can do is time it on a completely
quiescent system and then extrapolate.
Shrink is setup to be the deadlock victim in all cases so should not cause
deadlocks. It does not hold long-term locks so should not block although
because it does hold page locks while moving pages, and does a bunch of IO
you should expect some slowdown. On a test system setup with a TPCC
benchmark workload running at close to 100% cpu, I've seen a roughly 20%
drop in transaction throughput while shrink is running - YMMV.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"John VanderVliet" <john.vandervliet@.sjrb.ca.NOSPAM> wrote in message
news:e6MPetW$DHA.2808@.TK2MSFTNGP10.phx.gbl...
> Hi MO,
> Its best to update your stats on the DB and run your DBCCs first then do a
> backup. The shrik should not take that long I would expect 15 min at the
> outside. However, it will slow the system down and the more load from
users
> that is on the box the longer it will take. I would recommend shceduling
> this for a slow time of the day I usually do this after 3am.
> Hope that helps
> John ...
> "Mo" <anonymous@.discussions.microsoft.com> wrote in message
> news:068901c3fd47$74bb8ea0$a601280a@.phx.gbl...
>sql

DBCC LOG

Dear all,
I would like to do an application. As frontend VB6 and as backend, of
course, Sql2k or sql25k. Anyway my main goal is that such application might
look for information stored inside .LDF files.
Searches and so on will be done by mean DBCC commands as dbcc log and all
that sort of stuff.
I am stuck on how do I for to stored the info provided for DBCC command to a
Sql table.
It doesn't work:
insert into log_x(current_lsn, operation,,,,,,) dbcc log(mydb,-1)
Thanks for any advice or thought.
This code and information are provided "as is" without warranty of any kind.
Please post statements as well as any error message in order to understand
better your request.I'm guessing your problem might be related to the fact that dbcc log(mydb,-1
)
command returns multiple records sets. One way to get around this is to get
the output of the DBCC command to a flat file then import that flat file int
o
a table.
If you are looking for SQL Server examples check out my Website at
http://www.geocities.com/sqlserverexamples
"Enric" wrote:

> Dear all,
> I would like to do an application. As frontend VB6 and as backend, of
> course, Sql2k or sql25k. Anyway my main goal is that such application migh
t
> look for information stored inside .LDF files.
> Searches and so on will be done by mean DBCC commands as dbcc log and all
> that sort of stuff.
> I am stuck on how do I for to stored the info provided for DBCC command to
a
> Sql table.
> It doesn't work:
> insert into log_x(current_lsn, operation,,,,,,) dbcc log(mydb,-1)
> Thanks for any advice or thought.
> --
> This code and information are provided "as is" without warranty of any kin
d.
> Please post statements as well as any error message in order to understand
> better your request.

DBCC DBREINDEX - Update or Create Statistics

Hello All,
I have a 12-15 GB database that is performing poorly when we try to apply
database schema changes using our java application. The schema changes
include adding new columns to several tables that have seveal million rows ,
dropping and recreating several constraints, and changing some of the indexes
from unique to regular indexes, etc.
Are changes to the database schema a logged operation? If yes, how about
changing the database from Full recovery to Simple recovery mode before
applying schema changes? Will this help the performance?
I'am thinking of running DBCC DBREINDEX. Is DBCC DBREINDEX a logged
operation? If yes, how much free disk space do we need for both the data file
and the transaction log file? Do I need to run Update or Create Statistics
after a DBCC DBREINDEX operation?
Thank you so much,
Mitra
Mitra,
Schema changes are logged. Switching to SIMPLE recovery mode may help the
performance. DBCC DBREINDEX is logged. Space required will vary depending
on the number of records and indexes. Statistics are updated as part of a
DBCC DBREINDEX operation. For more information see the following:
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
HTH
Jerry
"mitra" <mitra@.discussions.microsoft.com> wrote in message
news:037B09E8-D868-49FC-8C0A-1922807B3B64@.microsoft.com...
> Hello All,
> I have a 12-15 GB database that is performing poorly when we try to apply
> database schema changes using our java application. The schema changes
> include adding new columns to several tables that have seveal million rows
> ,
> dropping and recreating several constraints, and changing some of the
> indexes
> from unique to regular indexes, etc.
> Are changes to the database schema a logged operation? If yes, how about
> changing the database from Full recovery to Simple recovery mode before
> applying schema changes? Will this help the performance?
> I'am thinking of running DBCC DBREINDEX. Is DBCC DBREINDEX a logged
> operation? If yes, how much free disk space do we need for both the data
> file
> and the transaction log file? Do I need to run Update or Create Statistics
> after a DBCC DBREINDEX operation?
> Thank you so much,
> Mitra

DBCC DBREINDEX - Update or Create Statistics

Hello All,
I have a 12-15 GB database that is performing poorly when we try to apply
database schema changes using our Java application. The schema changes
include adding new columns to several tables that have seveal million rows ,
dropping and recreating several constraints, and changing some of the indexe
s
from unique to regular indexes, etc.
Are changes to the database schema a logged operation? If yes, how about
changing the database from Full recovery to Simple recovery mode before
applying schema changes? Will this help the performance?
I'am thinking of running DBCC DBREINDEX. Is DBCC DBREINDEX a logged
operation? If yes, how much free disk space do we need for both the data fil
e
and the transaction log file? Do I need to run Update or Create Statistics
after a DBCC DBREINDEX operation?
Thank you so much,
MitraMitra,
Schema changes are logged. Switching to SIMPLE recovery mode may help the
performance. DBCC DBREINDEX is logged. Space required will vary depending
on the number of records and indexes. Statistics are updated as part of a
DBCC DBREINDEX operation. For more information see the following:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
HTH
Jerry
"mitra" <mitra@.discussions.microsoft.com> wrote in message
news:037B09E8-D868-49FC-8C0A-1922807B3B64@.microsoft.com...
> Hello All,
> I have a 12-15 GB database that is performing poorly when we try to apply
> database schema changes using our Java application. The schema changes
> include adding new columns to several tables that have seveal million rows
> ,
> dropping and recreating several constraints, and changing some of the
> indexes
> from unique to regular indexes, etc.
> Are changes to the database schema a logged operation? If yes, how about
> changing the database from Full recovery to Simple recovery mode before
> applying schema changes? Will this help the performance?
> I'am thinking of running DBCC DBREINDEX. Is DBCC DBREINDEX a logged
> operation? If yes, how much free disk space do we need for both the data
> file
> and the transaction log file? Do I need to run Update or Create Statistics
> after a DBCC DBREINDEX operation?
> Thank you so much,
> Mitra

DBCC DBREINDEX - Update or Create Statistics

Hello All,
I have a 12-15 GB database that is performing poorly when we try to apply
database schema changes using our java application. The schema changes
include adding new columns to several tables that have seveal million rows ,
dropping and recreating several constraints, and changing some of the indexes
from unique to regular indexes, etc.
Are changes to the database schema a logged operation? If yes, how about
changing the database from Full recovery to Simple recovery mode before
applying schema changes? Will this help the performance?
I'am thinking of running DBCC DBREINDEX. Is DBCC DBREINDEX a logged
operation? If yes, how much free disk space do we need for both the data file
and the transaction log file? Do I need to run Update or Create Statistics
after a DBCC DBREINDEX operation?
Thank you so much,
MitraMitra,
Schema changes are logged. Switching to SIMPLE recovery mode may help the
performance. DBCC DBREINDEX is logged. Space required will vary depending
on the number of records and indexes. Statistics are updated as part of a
DBCC DBREINDEX operation. For more information see the following:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
HTH
Jerry
"mitra" <mitra@.discussions.microsoft.com> wrote in message
news:037B09E8-D868-49FC-8C0A-1922807B3B64@.microsoft.com...
> Hello All,
> I have a 12-15 GB database that is performing poorly when we try to apply
> database schema changes using our java application. The schema changes
> include adding new columns to several tables that have seveal million rows
> ,
> dropping and recreating several constraints, and changing some of the
> indexes
> from unique to regular indexes, etc.
> Are changes to the database schema a logged operation? If yes, how about
> changing the database from Full recovery to Simple recovery mode before
> applying schema changes? Will this help the performance?
> I'am thinking of running DBCC DBREINDEX. Is DBCC DBREINDEX a logged
> operation? If yes, how much free disk space do we need for both the data
> file
> and the transaction log file? Do I need to run Update or Create Statistics
> after a DBCC DBREINDEX operation?
> Thank you so much,
> Mitra

DBCC CheckIdent message value to application

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