Automatic file grown

Hello:
I'm experiencing this problem with SQL 2000. I have a large DB (7 GB) and
fromtime to time every statement trying to insert or update data gets
blocked and dies with timeout. Looking for more detail I have found that
this happens when SQL server is growing its files. The DB is configured to
grow automatically (+100 MB) every time needed, and has no size limit.
To correct this lock, I run a INSERT statement from SQL query analizer, wait
for a minute and the statement is correctly executed, the server unlocked
and my programs continue inserting data. INSERT statements launched from
programs die with timeout and don't solve the problem.
I'd like to have more control over DB grown. Where can I see when has the DB
grown? Can I force file grown when I want, ata some fewer-work times? Where
can I find more information about this?
Thanks in advance, best regardsAuto-grow should be an emergency fail-safe only. You should actively
monitor the database sizes and grow them ahead of time in an off-peak hour.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Robert T." <r_t@.nospam.nospam> wrote in message
news:ej5%23rhqjEHA.3624@.TK2MSFTNGP10.phx.gbl...
> Hello:
> I'm experiencing this problem with SQL 2000. I have a large DB (7 GB) and
> fromtime to time every statement trying to insert or update data gets
> blocked and dies with timeout. Looking for more detail I have found that
> this happens when SQL server is growing its files. The DB is configured to
> grow automatically (+100 MB) every time needed, and has no size limit.
> To correct this lock, I run a INSERT statement from SQL query analizer,
wait
> for a minute and the statement is correctly executed, the server unlocked
> and my programs continue inserting data. INSERT statements launched from
> programs die with timeout and don't solve the problem.
> I'd like to have more control over DB grown. Where can I see when has the
DB
> grown? Can I force file grown when I want, ata some fewer-work times?
Where
> can I find more information about this?
> Thanks in advance, best regards
>|||In addition to Geoff's post, you find a couple of stored procedures at .com" target="_blank">www.dbmaint
.com (see the "free
utilities section" for jobs that can serve as inspiration...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert T." <r_t@.nospam.nospam> wrote in message news:ej5%23rhqjEHA.3624@.TK2MSFTNGP10.phx.gb
l...
> Hello:
> I'm experiencing this problem with SQL 2000. I have a large DB (7 GB) and
> fromtime to time every statement trying to insert or update data gets
> blocked and dies with timeout. Looking for more detail I have found that
> this happens when SQL server is growing its files. The DB is configured to
> grow automatically (+100 MB) every time needed, and has no size limit.
> To correct this lock, I run a INSERT statement from SQL query analizer, wa
it
> for a minute and the statement is correctly executed, the server unlocked
> and my programs continue inserting data. INSERT statements launched from
> programs die with timeout and don't solve the problem.
> I'd like to have more control over DB grown. Where can I see when has the
DB
> grown? Can I force file grown when I want, ata some fewer-work times? Wher
e
> can I find more information about this?
> Thanks in advance, best regards
>

Autoincrement

Hello
I've a table of Users with an identity key
Some records are inserted by a replication system which sends records with
key like 2-4-6-8 ...
and put them into the table with a INSERT sql
Other records are inserted via web
I need that the records inserted via web takes a key like 1-3-5-7 ...
I've set the identity seed to 1 and identity increment to 2
I've made a test
1. Inserted some record by replication system
2. If I try to insert a new record manually (by enterprise manager) the new
key is a par number instead of an odd
What's wrong?
Can you help me?Why don't you instead of doing that create another field called Origin
make it a bit when it's from the web give it a value of 1 otherwise 0
Your identity will be Old Key + 2 (that's your increment)
http://sqlservercode.blogspot.com/
"Denis" wrote:

> Hello
> I've a table of Users with an identity key
> Some records are inserted by a replication system which sends records with
> key like 2-4-6-8 ...
> and put them into the table with a INSERT sql
> Other records are inserted via web
> I need that the records inserted via web takes a key like 1-3-5-7 ...
> I've set the identity seed to 1 and identity increment to 2
> I've made a test
> 1. Inserted some record by replication system
> 2. If I try to insert a new record manually (by enterprise manager) the ne
w
> key is a par number instead of an odd
> What's wrong?
> Can you help me?
>
>|||Denis,

> What's wrong?
Is the property "not for replication" set in this identity column?
When the values are inserted from the replication, sql server takes that
number as the last identity value inserted in the table, so if the las value
was 8 then when you insert from the web using "set identity_insert t1 off"
will increment that value with the identity increment 8+2 and this will be
the next value to be inserted.
Example:
create table t1(
c1 int not null identity(1, 2)
)
go
insert into t1 default values
insert into t1 default values
insert into t1 default values
go
select
ident_seed('t1'),
ident_incr('t1'),
ident_current('t1')
go
set identity_insert t1 on
go
insert into t1(c1) values(2)
insert into t1(c1) values(4)
insert into t1(c1) values(6)
insert into t1(c1) values(8)
go
select
ident_seed('t1'),
ident_incr('t1'),
ident_current('t1')
go
set identity_insert t1 off
go
insert into t1 default values
go
select * from t1 order by c1 asc
go
drop table t1
go
AMB
"Denis" wrote:

> Hello
> I've a table of Users with an identity key
> Some records are inserted by a replication system which sends records with
> key like 2-4-6-8 ...
> and put them into the table with a INSERT sql
> Other records are inserted via web
> I need that the records inserted via web takes a key like 1-3-5-7 ...
> I've set the identity seed to 1 and identity increment to 2
> I've made a test
> 1. Inserted some record by replication system
> 2. If I try to insert a new record manually (by enterprise manager) the ne
w
> key is a par number instead of an odd
> What's wrong?
> Can you help me?
>
>