Automatic log decresing space used size in AdventureWorks

Hello all, I was making test with AdventureWorks about monitoring the log size and I run script:
-
/*
** This script is used to fill the Northwind transaction
*/

USE AdventureWorks

WHILE 1 = 1
BEGIN
UPDATE Production.Product
SET Name = Name
WAITFOR DELAY '000:00:00:999' --simulates a somewhat realistic OLTP environment
END-

I used a NEW AdventureWorks, just that I changed the recovery model to FULL RECOVERY MODEL.

By default Adventure Works use a log initial file size of 2 MB with automatic increment of 16MB.

The problem is that the log starts to increase his size 0.7 MB, 1.4 MB ... 1.8 MB and later automatically the log used size decrease to 0.5MB... and the process repeat again. Why is the log decreasing his space used size?, it should increase becouse is configurated whit automatic increment.

I support my test with performance monitor, my results are (please see picture):
http://www.compuaulas.com/performance2.gif

1. There is not log file growths, witch is strange becouse the log should growth!! and the log size didn't change his size.

2. The LOG SPACE USED changed. It's starts to growth but suddenly the space used decrease automatically (See Fuchsia line in the picture).

3. After a while, the log started to growth but instead still growing, TRUNCATIONS started to occur, and it's shouldn't be happening.

4. There is not file shrinking.

please help!!, thank you a lot.

Hi !

I found the same problem in SQL 2000. I used log truncations and log used space (kb) counters to see what was happening. I also used DBCC loginfo to see VLFs, but I still have no solution for that...

[]'s

Dennes

|||

Hi!

I found the answer.

The recovery model full is useless until we made a full backup. SQL Server knows that and keep using recovery model simple until that.

[]'s

Dennes

Automatic log decresing space used size in AdventureWorks

Hello all, I was making test with AdventureWorks about monitoring the log size and I run script:
-
/*
** This script is used to fill the Northwind transaction
*/

USE AdventureWorks

WHILE 1 = 1
BEGIN
UPDATE Production.Product
SET Name = Name
WAITFOR DELAY '000:00:00:999' --simulates a somewhat realistic OLTP environment
END-

I used a NEW AdventureWorks, just that I changed the recovery model to FULL RECOVERY MODEL.

By default Adventure Works use a log initial file size of 2 MB with automatic increment of 16MB.

The problem is that the log starts to increase his size 0.7 MB, 1.4 MB ... 1.8 MB and later automatically the log used size decrease to 0.5MB... and the process repeat again. Why is the log decreasing his space used size?, it should increase becouse is configurated whit automatic increment.

I support my test with performance monitor, my results are (please see picture):
http://www.compuaulas.com/performance2.gif

1. There is not log file growths, witch is strange becouse the log should growth!! and the log size didn't change his size.

2. The LOG SPACE USED changed. It's starts to growth but suddenly the space used decrease automatically (See Fuchsia line in the picture).

3. After a while, the log started to growth but instead still growing, TRUNCATIONS started to occur, and it's shouldn't be happening.

4. There is not file shrinking.

please help!!, thank you a lot.

Hi !

I found the same problem in SQL 2000. I used log truncations and log used space (kb) counters to see what was happening. I also used DBCC loginfo to see VLFs, but I still have no solution for that...

[]'s

Dennes

|||

Hi!

I found the answer.

The recovery model full is useless until we made a full backup. SQL Server knows that and keep using recovery model simple until that.

[]'s

Dennes

Autogrow problem

We are seeing an issue with the autogrow setting on some of our production
databases. Occasionally we will see a database fill up, processes trying to
write to the db will show page latch waits and the database will not grow for
up to five hours. The last time it occured, the database only needed to grow
400MB but it did not for hours. Once it did grow most of the writes which
had been waiting did not occur. Their is plenty of space on the disk and the
server was not at capacity. Any ideas?
Thank You.Autogrow is a necessary evil...you should be maintaining your data and log
file sizes based on the expected amount of transactions and altering them
during maintenance hours. Autogrow ought to be a failsafe only due to the
performance impact...
--
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
"Len Gustafson" <LenGustafson@.discussions.microsoft.com> wrote in message
news:E6CD323D-2F34-42F6-8EF4-926D2FC69730@.microsoft.com...
> We are seeing an issue with the autogrow setting on some of our production
> databases. Occasionally we will see a database fill up, processes trying
> to
> write to the db will show page latch waits and the database will not grow
> for
> up to five hours. The last time it occured, the database only needed to
> grow
> 400MB but it did not for hours. Once it did grow most of the writes which
> had been waiting did not occur. Their is plenty of space on the disk and
> the
> server was not at capacity. Any ideas?
> Thank You.

Autogrow problem

We are seeing an issue with the autogrow setting on some of our production
databases. Occasionally we will see a database fill up, processes trying to
write to the db will show page latch waits and the database will not grow for
up to five hours. The last time it occured, the database only needed to grow
400MB but it did not for hours. Once it did grow most of the writes which
had been waiting did not occur. Their is plenty of space on the disk and the
server was not at capacity. Any ideas?
Thank You.
Autogrow is a necessary evil...you should be maintaining your data and log
file sizes based on the expected amount of transactions and altering them
during maintenance hours. Autogrow ought to be a failsafe only due to the
performance impact...
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
"Len Gustafson" <LenGustafson@.discussions.microsoft.com> wrote in message
news:E6CD323D-2F34-42F6-8EF4-926D2FC69730@.microsoft.com...
> We are seeing an issue with the autogrow setting on some of our production
> databases. Occasionally we will see a database fill up, processes trying
> to
> write to the db will show page latch waits and the database will not grow
> for
> up to five hours. The last time it occured, the database only needed to
> grow
> 400MB but it did not for hours. Once it did grow most of the writes which
> had been waiting did not occur. Their is plenty of space on the disk and
> the
> server was not at capacity. Any ideas?
> Thank You.

Autogrow problem

We are seeing an issue with the autogrow setting on some of our production
databases. Occasionally we will see a database fill up, processes trying to
write to the db will show page latch waits and the database will not grow fo
r
up to five hours. The last time it occured, the database only needed to gro
w
400MB but it did not for hours. Once it did grow most of the writes which
had been waiting did not occur. Their is plenty of space on the disk and th
e
server was not at capacity. Any ideas?
Thank You.Autogrow is a necessary evil...you should be maintaining your data and log
file sizes based on the expected amount of transactions and altering them
during maintenance hours. Autogrow ought to be a failsafe only due to the
performance impact...
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
"Len Gustafson" <LenGustafson@.discussions.microsoft.com> wrote in message
news:E6CD323D-2F34-42F6-8EF4-926D2FC69730@.microsoft.com...
> We are seeing an issue with the autogrow setting on some of our production
> databases. Occasionally we will see a database fill up, processes trying
> to
> write to the db will show page latch waits and the database will not grow
> for
> up to five hours. The last time it occured, the database only needed to
> grow
> 400MB but it did not for hours. Once it did grow most of the writes which
> had been waiting did not occur. Their is plenty of space on the disk and
> the
> server was not at capacity. Any ideas?
> Thank You.

Autogenerate data

Hi,
Does any one know of a script that can autogenerate test data. Does not have
to be real intellagible data at this stage, just to fill tables.
Thanks
RobertRobert
http://vyaskn.tripod.com/code/generate_inserts.txt
http://www.karaszi.com/SQLServer/in...rate_script.asp
"Robert Bravery" <me@.u.com> wrote in message
news:uxVGMpJgGHA.5092@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Does any one know of a script that can autogenerate test data. Does not
> have
> to be real intellagible data at this stage, just to fill tables.
> Thanks
> Robert
>|||Robert, http://vyaskn.tripod.com/code/generate_inserts.txt -- is what I have
also used it in the past. Its really script.
Best Regards
Vadivel
http://vadivel.blogspot.com
"Uri Dimant" wrote:

> Robert
> http://vyaskn.tripod.com/code/generate_inserts.txt
> http://www.karaszi.com/SQLServer/in...rate_script.asp
> "Robert Bravery" <me@.u.com> wrote in message
> news:uxVGMpJgGHA.5092@.TK2MSFTNGP04.phx.gbl...
>
>

Autofill

Hi, Is there any way to copy functions from one textbox to another with auto
fill? There are cases which the programmer spends enormous time over formulas
just to change textbox names into the same formulas.>>There are cases which the programmer spends enormous time over formulas
just to change textbox names into the same formulas.
View the RDL as code (XML) and do a Find and Replace?
>L<
"Kostas Bourikas" <KostasBourikas@.discussions.microsoft.com> wrote in
message news:0B5E6D85-36BF-4E2C-994B-B5C49487A44A@.microsoft.com...
> Hi, Is there any way to copy functions from one textbox to another with
> auto
> fill? There are cases which the programmer spends enormous time over
> formulas
> just to change textbox names into the same formulas.|||Thank you but this still doesn't solve my problem...
What is the difference between imputing textbox values into visual studio or
the same into xml code (find/replace)... The effort is still remains the
same.
Microsoft should seriously consider an autofill (excel-like) option, in
future updates.
Actually surprises me that they haven't released a patch for this issue
yet... Iâ'm representing a huge amount of programmers that share my opinion.
"Lisa Slater Nicholls" wrote:
> >>There are cases which the programmer spends enormous time over formulas
> just to change textbox names into the same formulas.
> View the RDL as code (XML) and do a Find and Replace?
> >L<
> "Kostas Bourikas" <KostasBourikas@.discussions.microsoft.com> wrote in
> message news:0B5E6D85-36BF-4E2C-994B-B5C49487A44A@.microsoft.com...
> > Hi, Is there any way to copy functions from one textbox to another with
> > auto
> > fill? There are cases which the programmer spends enormous time over
> > formulas
> > just to change textbox names into the same formulas.
>|||>>
> What is the difference between imputing textbox values into visual studio
> or
> the same into xml code (find/replace)... The effort is still remains the
> same.
<<
The effort is *not* the same because you can do a Replace All in
the XML, whereas you have to do the textboxes one at a time <shrug>.
You may not want to do this but you asked me what the difference is, so
that's what it is. Look: I was trying to help you. If you don't like the
idea, that's okay.
>> Iâ'm representing a huge amount of programmers that share my opinion.
That's nice. Maybe somebody else reading the thread will like my
suggestion, then ! <s>
FWIW... I have been writing a design-time utility for the Report
Designer. It's nothing very fancy. It's just what I do instead of waiting
for Microsoft to do something.
The premise of my utility is different from what you need, but could
potentially
be extended to what you are asking for. Again, not very fancily, and
probably
*not* to everybody's satisfaction.
Here's my point:
In writing this, and in writing other utilities for reporting environments,
I
have learned that it's quite difficult to do this type of thing and meet
everybody's expectations. In your particular case, the utility would have
to be very specific about what it decided to change (especially in custom
expressions) so that it worked in all cases without harming anybody's
report in a way that they might not realize until after they had saved the
changes. This is likely the reason why MS would have hesitated to do it at
this stage in the product's life.
I don't, personally, believe this is a "bug" requiring a "patch".
It is an underimplementation, certainly. I see lots of those in this
product.
But this particular underimplementation, no matter how much it bothers you,
should not be addressed without a great deal of thought IMHO, so it will
likely take some time to do correctly. I would hope that is of concern to
you --
because a botched implementation would make you a lot unhappier than
you are right now <s>.
Please don't shoot the messenger. And please realize that I am representing
*nobody* and asserting *nothing* about what body of programmers share my
opinions. This is just what I think.
>L<
"Kostas Bourikas" <KostasBourikas@.discussions.microsoft.com> wrote in
message news:00725C4D-A1E6-44DC-B5E7-8DA741918B96@.microsoft.com...
> Thank you but this still doesn't solve my problem...
> What is the difference between imputing textbox values into visual studio
> or
> the same into xml code (find/replace)... The effort is still remains the
> same.
> Microsoft should seriously consider an autofill (excel-like) option, in
> future updates.
> Actually surprises me that they haven't released a patch for this issue
> yet... Iâ'm representing a huge amount of programmers that share my
> opinion.
>
> "Lisa Slater Nicholls" wrote:
>> >>There are cases which the programmer spends enormous time over formulas
>> just to change textbox names into the same formulas.
>> View the RDL as code (XML) and do a Find and Replace?
>> >L<
>> "Kostas Bourikas" <KostasBourikas@.discussions.microsoft.com> wrote in
>> message news:0B5E6D85-36BF-4E2C-994B-B5C49487A44A@.microsoft.com...
>> > Hi, Is there any way to copy functions from one textbox to another with
>> > auto
>> > fill? There are cases which the programmer spends enormous time over
>> > formulas
>> > just to change textbox names into the same formulas.
>>

Auto Primary Key with fill in

I want a table to have an int auto primary key, that will fill in deleted rows.
EG
1
2
4
5
8

The next primary key should be 3 (NOT 9)

This table changes often, so just adding 1 to the last number used, will fast run out of numbers.
The table is accessed by many users, so it can not be manually generated.

Using the IDENTITY property will successfully and correctly manage autonumbering. However, as you noted, deletions will leave gaps. I suggest that you use a datatype sufficient to allow both growth and allow for the deletions. A bigint datatype is a very, very large number -up to 9,223,372,036,854,775,807, I couldn't imagine that you are adding so much data to the database that a bigint would be inadequate.

It would be possible to create a 'homegrown' solution to manage this, but in my experience, with many users, that is a mistake. It would require a lot of table locking and/or data contention, placing unneeded stress on the database.

There are occassionally, real business needs for managing a numbering sequence, for example check numbers. But in those cases, deletions are usually not allowed.

|||Yes and after about 1 year or so when it flips over to 1 again, and starts over writing data.

Its a very nasty bug.

Its happened before.|||

Hi:

Please refer to the following article, and see if it would help with your concern. :-)

http://www.sqlteam.com/item.asp?ItemID=765

Thanks.

|||That changes existing data.

but can be expanded upon, to use a multi sql statement to defrag a database.

but then how to reset the auto number to the next number.

So far the only way, i can do what i want, is one of two ways

1. Run all sql through a service, that can sync me a new auto fill in number.
2. Have unacceptable down time, and run a program to defrag the tables.

auto increment

I have a table that has teh primary key set so it auto increments.
How can I make it to where if I delete a record, the next entry will fill
the spot that was vacated?
For example;
I have 5 records,
1
2
3
4
5
If I delete record 3 I will have
1
2
4
5
Then if I add another reocrd, I will have
1
2
4
5
6
I would like 6 to actually be 3 to fill in the space.
or, when I delete a record, have everythign shift locations, or at least
have teh last record fill the spot of the deleted one.Number the rows when you SELECT from the table. Here's an example from the
Pubs database:
SELECT
(SELECT COUNT(*)
FROM Authors
WHERE au_id <= A.au_id) AS id
,*
FROM Authors AS A
--
David Portas
SQL Server MVP
--|||I agree with Dave, you don't need to STORE this number, just return it when
you run your SELECT (http://www.aspfaq.com/2427). You can do this
quasi-transparently by using a view instead of a query, but the effect is
the same.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"johnfli" <john@.here.com> wrote in message
news:OzIL7j17DHA.2712@.tk2msftngp13.phx.gbl...
> I have a table that has teh primary key set so it auto increments.
> How can I make it to where if I delete a record, the next entry will fill
> the spot that was vacated?
> For example;
> I have 5 records,
> 1
> 2
> 3
> 4
> 5
> If I delete record 3 I will have
> 1
> 2
> 4
> 5
> Then if I add another reocrd, I will have
> 1
> 2
> 4
> 5
> 6
> I would like 6 to actually be 3 to fill in the space.
> or, when I delete a record, have everythign shift locations, or at least
> have teh last record fill the spot of the deleted one.
>|||Why. SQL doesn't charge by the each for auto-increment row numbers. If you
use BigInt, please call me if you use up all your numbers. I will be happy
to refill the mfree of charge.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"johnfli" <john@.here.com> wrote in message
news:OzIL7j17DHA.2712@.tk2msftngp13.phx.gbl...
> I have a table that has teh primary key set so it auto increments.
> How can I make it to where if I delete a record, the next entry will fill
> the spot that was vacated?
> For example;
> I have 5 records,
> 1
> 2
> 3
> 4
> 5
> If I delete record 3 I will have
> 1
> 2
> 4
> 5
> Then if I add another reocrd, I will have
> 1
> 2
> 4
> 5
> 6
> I would like 6 to actually be 3 to fill in the space.
> or, when I delete a record, have everythign shift locations, or at least
> have teh last record fill the spot of the deleted one.
>|||What I am doing is I have a database of the drivers for our company.
We have to do random drug tests.
I wrote a program that randomly pick numbers from 1 to the number of records
in the database.
But if a driver gets the axe, I delete him, then add another, the program
will periodicly pick the number for the driver that was deleted. I have
already changed my program to deal with that by if I get a BOF or an EOF, to
go pick another number. But the problem is, is that I may have five records
1,2,4,5,6 It will pick a number from 1 to 5, but it will never get number
6.
"Geoff N.Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:enKwjs17DHA.632@.TK2MSFTNGP12.phx.gbl...
> Why. SQL doesn't charge by the each for auto-increment row numbers. If
you
> use BigInt, please call me if you use up all your numbers. I will be happy
> to refill the mfree of charge.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "johnfli" <john@.here.com> wrote in message
> news:OzIL7j17DHA.2712@.tk2msftngp13.phx.gbl...
> > I have a table that has teh primary key set so it auto increments.
> >
> > How can I make it to where if I delete a record, the next entry will
fill
> > the spot that was vacated?
> >
> > For example;
> >
> > I have 5 records,
> > 1
> > 2
> > 3
> > 4
> > 5
> >
> > If I delete record 3 I will have
> >
> > 1
> > 2
> > 4
> > 5
> >
> > Then if I add another reocrd, I will have
> > 1
> > 2
> > 4
> > 5
> > 6
> >
> > I would like 6 to actually be 3 to fill in the space.
> > or, when I delete a record, have everythign shift locations, or at least
> > have teh last record fill the spot of the deleted one.
> >
> >
>|||Try picking a random driver like this
SELECT TOP 1 driver_name
FROM Drivers
ORDER BY NEWID()
--
David Portas
SQL Server MVP
--