DBCC CHECKDB: growing

Showing posts with label growing. Show all posts

Thursday, March 8, 2012

db optimization

I have started reorgainizing(rebuilding) data and index
pages and since the tran log is still growing and there is
no more space I am wondering if I can start shrinking the
log during the process of optimization. Any view is
apreciated.
MirnaYou can back it up to truncate the committed transactions or set it to
simple mode until your done. Do not attempt to shrink it.
--
Andrew J. Kelly
SQL Server MVP
"Mirna" <anonymous@.discussions.microsoft.com> wrote in message
news:04d701c3b1be$c727c200$a401280a@.phx.gbl...
> I have started reorgainizing(rebuilding) data and index
> pages and since the tran log is still growing and there is
> no more space I am wondering if I can start shrinking the
> log during the process of optimization. Any view is
> apreciated.
> Mirna|||Everything that moved (index pages) are logged. DBCC DBREINDEX will log a lot. If that is
undesirable, consider using DBCC INDEXFEDRAG.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
<anonymous@.discussions.microsoft.com> wrote in message
news:08d501c3b29b$9b4a7910$a501280a@.phx.gbl...
> Thanks Andrew...luckily the job finished before it filled
> all the space on the drive.
> Teh log has grown to from 101 MB to 70 GB. After I
> truncated and shrinked the log the database size has
> increased by almost 10 % . Why is that ? What gets written
> to the db after the optimization ?
> Thanks,
> Mirna
> >--Original Message--
> >You can back it up to truncate the committed transactions
> or set it to
> >simple mode until your done. Do not attempt to shrink it.
> >
> >--
> >
> >Andrew J. Kelly
> >SQL Server MVP
> >
> >
> >"Mirna" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:04d701c3b1be$c727c200$a401280a@.phx.gbl...
> >> I have started reorgainizing(rebuilding) data and index
> >> pages and since the tran log is still growing and there
> is
> >> no more space I am wondering if I can start shrinking
> the
> >> log during the process of optimization. Any view is
> >> apreciated.
> >>
> >> Mirna
> >
> >
> >.
> >|||How did you shrink it? If you used DBCC SHRINKDATABASE you should try using
SHRINKFILE instead. That allows you to only affect the file (in this case
the log file) you want.
--
Andrew J. Kelly
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:08d501c3b29b$9b4a7910$a501280a@.phx.gbl...
> Thanks Andrew...luckily the job finished before it filled
> all the space on the drive.
> Teh log has grown to from 101 MB to 70 GB. After I
> truncated and shrinked the log the database size has
> increased by almost 10 % . Why is that ? What gets written
> to the db after the optimization ?
> Thanks,
> Mirna
> >--Original Message--
> >You can back it up to truncate the committed transactions
> or set it to
> >simple mode until your done. Do not attempt to shrink it.
> >
> >--
> >
> >Andrew J. Kelly
> >SQL Server MVP
> >
> >
> >"Mirna" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:04d701c3b1be$c727c200$a401280a@.phx.gbl...
> >> I have started reorgainizing(rebuilding) data and index
> >> pages and since the tran log is still growing and there
> is
> >> no more space I am wondering if I can start shrinking
> the
> >> log during the process of optimization. Any view is
> >> apreciated.
> >>
> >> Mirna
> >
> >
> >.
> >|||Good point, Andrew. Not only that DBCC DBREINDEX produces log records, shrinking a database file
also produces log records (same amount as the amount of data being moved due to the shrink).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23OFVMqtsDHA.1224@.TK2MSFTNGP09.phx.gbl...
> How did you shrink it? If you used DBCC SHRINKDATABASE you should try using
> SHRINKFILE instead. That allows you to only affect the file (in this case
> the log file) you want.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> <anonymous@.discussions.microsoft.com> wrote in message
> news:08d501c3b29b$9b4a7910$a501280a@.phx.gbl...
> > Thanks Andrew...luckily the job finished before it filled
> > all the space on the drive.
> > Teh log has grown to from 101 MB to 70 GB. After I
> > truncated and shrinked the log the database size has
> > increased by almost 10 % . Why is that ? What gets written
> > to the db after the optimization ?
> >
> > Thanks,
> > Mirna
> >
> > >--Original Message--
> > >You can back it up to truncate the committed transactions
> > or set it to
> > >simple mode until your done. Do not attempt to shrink it.
> > >
> > >--
> > >
> > >Andrew J. Kelly
> > >SQL Server MVP
> > >
> > >
> > >"Mirna" <anonymous@.discussions.microsoft.com> wrote in
> > message
> > >news:04d701c3b1be$c727c200$a401280a@.phx.gbl...
> > >> I have started reorgainizing(rebuilding) data and index
> > >> pages and since the tran log is still growing and there
> > is
> > >> no more space I am wondering if I can start shrinking
> > the
> > >> log during the process of optimization. Any view is
> > >> apreciated.
> > >>
> > >> Mirna
> > >
> > >
> > >.
> > >
>

Wednesday, March 7, 2012

db mirroring: why tr. log continue growing

hi All
I test db. mirroring.
While it's working, tr. log continues growing. while I run tr. log dumps
regulary, the size of it only goes up.
I tried dbcc shrinkfile and shrinkdatabase - it won't help.
Does anybody have this experience?Run DBCC OPENTRAN. You may have a long-running transaction.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Gene." <Gene@.discussions.microsoft.com> wrote in message
news:4B6FD7B3-D4E6-47A1-8A9D-1634AAF7076D@.microsoft.com...
hi All
I test db. mirroring.
While it's working, tr. log continues growing. while I run tr. log dumps
regulary, the size of it only goes up.
I tried dbcc shrinkfile and shrinkdatabase - it won't help.
Does anybody have this experience?|||Sounds like you have an open transaction somewhere. What does DBCC
OPENTRAN() say?
--
Andrew J. Kelly SQL MVP
"Gene." <Gene@.discussions.microsoft.com> wrote in message
news:4B6FD7B3-D4E6-47A1-8A9D-1634AAF7076D@.microsoft.com...
> hi All
> I test db. mirroring.
> While it's working, tr. log continues growing. while I run tr. log dumps
> regulary, the size of it only goes up.
> I tried dbcc shrinkfile and shrinkdatabase - it won't help.
> Does anybody have this experience?|||Guys, thank you first of all for responds.
This is the test db. Nothing is running there outside of my control.
The way I set it up is next:
backup test log... statements runs every second and I see successfull
execution of it.
Then, I run 1000 records inserts just to see how they show up on another end.
Problem is very obvious.
Interesting enough I tried to shrink file - noway. Once I stop mirrored
session and disconnect db's, shrink works immediately.
"Andrew J. Kelly" wrote:
> Sounds like you have an open transaction somewhere. What does DBCC
> OPENTRAN() say?
> --
> Andrew J. Kelly SQL MVP
> "Gene." <Gene@.discussions.microsoft.com> wrote in message
> news:4B6FD7B3-D4E6-47A1-8A9D-1634AAF7076D@.microsoft.com...
> > hi All
> > I test db. mirroring.
> > While it's working, tr. log continues growing. while I run tr. log dumps
> > regulary, the size of it only goes up.
> > I tried dbcc shrinkfile and shrinkdatabase - it won't help.
> >
> > Does anybody have this experience?
>
>|||Just because it is under your control does not mean there isn't an open
transaction. What type of mirroring are you using? Is the tran getting
committed on the mirror?
--
Andrew J. Kelly SQL MVP
"Gene." <Gene@.discussions.microsoft.com> wrote in message
news:E2CB1FF0-4EE7-4FD6-854D-C7A4E9A8D60D@.microsoft.com...
> Guys, thank you first of all for responds.
> This is the test db. Nothing is running there outside of my control.
> The way I set it up is next:
> backup test log... statements runs every second and I see successfull
> execution of it.
> Then, I run 1000 records inserts just to see how they show up on another
> end.
> Problem is very obvious.
> Interesting enough I tried to shrink file - noway. Once I stop mirrored
> session and disconnect db's, shrink works immediately.
> "Andrew J. Kelly" wrote:
>> Sounds like you have an open transaction somewhere. What does DBCC
>> OPENTRAN() say?
>> --
>> Andrew J. Kelly SQL MVP
>> "Gene." <Gene@.discussions.microsoft.com> wrote in message
>> news:4B6FD7B3-D4E6-47A1-8A9D-1634AAF7076D@.microsoft.com...
>> > hi All
>> > I test db. mirroring.
>> > While it's working, tr. log continues growing. while I run tr. log
>> > dumps
>> > regulary, the size of it only goes up.
>> > I tried dbcc shrinkfile and shrinkdatabase - it won't help.
>> >
>> > Does anybody have this experience?
>>|||Hi Andrew
I ran dbcc opentran, does not show any open transactions.
Batch inserts which I run is not inside of begin tran, commit statements.
Do you have any database currently set up with database mirroring?
When I run sp_who, I see 3 db mirror processes:
21 0 suspended sa
0 NULL DB MIRROR 0
21 1 suspended sa
0 NULL DB MIRROR 0
25 0 suspended sa
0 test DB MIRROR 0
spid25 is the one wich I recognize in charge for current mirror session.
Few days ago when I had similar setup and paused mirror session, I could not
get it back running. So I had to drop databases, endpoints. It seems like
this is left overs which I don't know how to get rid of it.
I am not sure how relevant spid 21 to the problem of growing tr. log.
When I select * from sys.endpoints, I see only 1 database mirroring session
running.
Any clue?
Log is growing
"Andrew J. Kelly" wrote:
> Just because it is under your control does not mean there isn't an open
> transaction. What type of mirroring are you using? Is the tran getting
> committed on the mirror?
> --
> Andrew J. Kelly SQL MVP
> "Gene." <Gene@.discussions.microsoft.com> wrote in message
> news:E2CB1FF0-4EE7-4FD6-854D-C7A4E9A8D60D@.microsoft.com...
> > Guys, thank you first of all for responds.
> > This is the test db. Nothing is running there outside of my control.
> > The way I set it up is next:
> > backup test log... statements runs every second and I see successfull
> > execution of it.
> > Then, I run 1000 records inserts just to see how they show up on another
> > end.
> > Problem is very obvious.
> >
> > Interesting enough I tried to shrink file - noway. Once I stop mirrored
> > session and disconnect db's, shrink works immediately.
> >
> > "Andrew J. Kelly" wrote:
> >
> >> Sounds like you have an open transaction somewhere. What does DBCC
> >> OPENTRAN() say?
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >> "Gene." <Gene@.discussions.microsoft.com> wrote in message
> >> news:4B6FD7B3-D4E6-47A1-8A9D-1634AAF7076D@.microsoft.com...
> >> > hi All
> >> > I test db. mirroring.
> >> > While it's working, tr. log continues growing. while I run tr. log
> >> > dumps
> >> > regulary, the size of it only goes up.
> >> > I tried dbcc shrinkfile and shrinkdatabase - it won't help.
> >> >
> >> > Does anybody have this experience?
> >>
> >>
> >>
>
>|||Andrew, thank you so much for asking 'what type of mirroring' I use.
My intention was to use high performance mode.
When I set it up, i forget to run last statement:
ALTER DATABASE test SET PARTNER SAFETY OFF
Once I ran it, I was able to shrinkdb.
Thank you for help, Gene.
"Andrew J. Kelly" wrote:
> Just because it is under your control does not mean there isn't an open
> transaction. What type of mirroring are you using? Is the tran getting
> committed on the mirror?
> --
> Andrew J. Kelly SQL MVP
> "Gene." <Gene@.discussions.microsoft.com> wrote in message
> news:E2CB1FF0-4EE7-4FD6-854D-C7A4E9A8D60D@.microsoft.com...
> > Guys, thank you first of all for responds.
> > This is the test db. Nothing is running there outside of my control.
> > The way I set it up is next:
> > backup test log... statements runs every second and I see successfull
> > execution of it.
> > Then, I run 1000 records inserts just to see how they show up on another
> > end.
> > Problem is very obvious.
> >
> > Interesting enough I tried to shrink file - noway. Once I stop mirrored
> > session and disconnect db's, shrink works immediately.
> >
> > "Andrew J. Kelly" wrote:
> >
> >> Sounds like you have an open transaction somewhere. What does DBCC
> >> OPENTRAN() say?
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >> "Gene." <Gene@.discussions.microsoft.com> wrote in message
> >> news:4B6FD7B3-D4E6-47A1-8A9D-1634AAF7076D@.microsoft.com...
> >> > hi All
> >> > I test db. mirroring.
> >> > While it's working, tr. log continues growing. while I run tr. log
> >> > dumps
> >> > regulary, the size of it only goes up.
> >> > I tried dbcc shrinkfile and shrinkdatabase - it won't help.
> >> >
> >> > Does anybody have this experience?
> >>
> >>
> >>
>
>

Tuesday, February 14, 2012

Db filegrowth question ?

Is there a pause to db activity( select,inserts,updates and deletes) when
the data files are growing ? Using SQL 2000Only modification for which SQL Server need to allocate new extents. The other operations are not
blocked.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Hassan" <fatima_ja@.hotmail.com> wrote in message news:eekVvP7wDHA.2528@.TK2MSFTNGP10.phx.gbl...
> Is there a pause to db activity( select,inserts,updates and deletes) when
> the data files are growing ? Using SQL 2000
>|||no at all

Thursday, March 8, 2012

db optimization

Wednesday, March 7, 2012

db mirroring: why tr. log continue growing

db mirroring: why tr. log continue growing

db mirroring: why tr. log continue growing

Tuesday, February 14, 2012

Db filegrowth question ?

DBCC CHECKDB

Blog Archive

About Me