Automatical table update within a database

Hello!

We are developping a project using MS-SQLServer 7 and we need a
process for the synchronization of 3 tables together.
Inserts and updates in the table A should immediately and
automatically occur on table C, and updates on table C should also
automatically occur on table B.
We think that the solution using triggers and stored procedures is the
right choice. Could someone with knowledge on stored procedures help
us?
We need the help soon, this is quite urgent, so we’d be happy to
get an answer!
If something is not clear just ask!

Thanks in advance!
E. KellerLook at CREATE TRIGGER topic in the BOL

"E.Keller" <emmanuel.keller@.net2000.ch> wrote in message
news:4eed4cad.0401070114.65961769@.posting.google.c om...
> Hello!
> We are developping a project using MS-SQLServer 7 and we need a
> process for the synchronization of 3 tables together.
> Inserts and updates in the table A should immediately and
> automatically occur on table C, and updates on table C should also
> automatically occur on table B.
> We think that the solution using triggers and stored procedures is the
> right choice. Could someone with knowledge on stored procedures help
> us?
> We need the help soon, this is quite urgent, so we’d be happy to
> get an answer!
> If something is not clear just ask!
> Thanks in advance!
> E. Keller|||Use triggers if you cannot guarantee that the process that modifies table A
will also do the other two. If you do it all with stored procedures (my
preferred method) you can write the modification code for all three tables
inside the procs. make sure you wrap the 3 statements in a transaction so
they all complete or all roll back. Triggers automatically do the
transaction for you, but this is not always a good thing.

"E.Keller" <emmanuel.keller@.net2000.ch> wrote in message
news:4eed4cad.0401070114.65961769@.posting.google.c om...
> Hello!
> We are developping a project using MS-SQLServer 7 and we need a
> process for the synchronization of 3 tables together.
> Inserts and updates in the table A should immediately and
> automatically occur on table C, and updates on table C should also
> automatically occur on table B.
> We think that the solution using triggers and stored procedures is the
> right choice. Could someone with knowledge on stored procedures help
> us?
> We need the help soon, this is quite urgent, so we’d be happy to
> get an answer!
> If something is not clear just ask!
> Thanks in advance!
> E. Keller

Automatical Import from MySQL-Table?

Hi All!
is it possible to create a job in the SQL Enterprise Manager that regularly
imports the data from a table of a MySQL-database?
how could I achieve this aim without having to program a lot?
any hints are highly appreciated!
cheers, jens
Take a look at OPENROWSET if you don't want to create a linked server
or OPENQUERY if you do create a linked server
http://sqlservercode.blogspot.com/
|||Hi SQL,
for me a so called "linked server" sounds better, how could I create a
linked server in the MS SQL ENterprise Manager?
Cheers, Jens
"SQL" <denis.gobo@.gmail.com> schrieb im Newsbeitrag
news:1134485484.301414.309720@.g43g2000cwa.googlegr oups.com...
> Take a look at OPENROWSET if you don't want to create a linked server
> or OPENQUERY if you do create a linked server
> http://sqlservercode.blogspot.com/
>
|||Go to security/Linked Servers
Right Click on Linked Servers--> New Linked Server
Use Microsoft OLE DB Provider for ODBC Drivers
Use the Connection String
DRIVER={MySQL ODBC 3.51
Driver};SERVER=myserver.com;DATABASE=database;USER
=user;PASSWORD=password;OPTION=3
And in Provider Options select:
level zero only
Non-transacted updates (something)
Allow InProcess
the linked thing will work as
mylinkedserver...tablename
http://sqlservercode.blogspot.com/
|||Hey SQL, that worked perfectly, thanx a lot!
now I see the tables of "DatabaseXYZ" the "MyLinkedMySQLServer". But how can
I now create a job that imports all data from "Table1" of "DatabaseXYZ"
into "DatabaseABC" of my MSSQLServer?
Cheers, Jens
"SQL" <denis.gobo@.gmail.com> schrieb im Newsbeitrag
news:1134491324.820321.274410@.z14g2000cwz.googlegr oups.com...
> Go to security/Linked Servers
> Right Click on Linked Servers--> New Linked Server
> Use Microsoft OLE DB Provider for ODBC Drivers
> Use the Connection String
> DRIVER={MySQL ODBC 3.51
> Driver};SERVER=myserver.com;DATABASE=database;USER
> =user;PASSWORD=password;OPTION=3
> And in Provider Options select:
> level zero only
> Non-transacted updates (something)
> Allow InProcess
> the linked thing will work as
> mylinkedserver...tablename
>
> http://sqlservercode.blogspot.com/
>
|||Create a job and copy this code (obviously you will have to change it,
for example i don't know what your join condition is)
Then create a schedule and that should do it
insert into DatabaseABC.dbo.table1
select * from DatabaseABC.dbo.table1 ms left join
MyLinkedMySQLServer.DatabaseXYZ.Owner.Table1 my
on ms.id =my.id
where my.id is null
To Create a job go to Managment-->Sql server Agent-->Jobs
Right click --> New Job add a step paste the code
add the schedule and that should do it
http://sqlservercode.blogspot.com/

Automatical Import from MySQL-Table?

Hi All!
is it possible to create a job in the SQL Enterprise Manager that regularly
imports the data from a table of a MySQL-database?
how could I achieve this aim without having to program a lot?
any hints are highly appreciated!
cheers, jensTake a look at OPENROWSET if you don't want to create a linked server
or OPENQUERY if you do create a linked server
http://sqlservercode.blogspot.com/|||Hi SQL,
for me a so called "linked server" sounds better, how could I create a
linked server in the MS SQL ENterprise Manager?
Cheers, Jens
"SQL" <denis.gobo@.gmail.com> schrieb im Newsbeitrag
news:1134485484.301414.309720@.g43g2000cwa.googlegroups.com...
> Take a look at OPENROWSET if you don't want to create a linked server
> or OPENQUERY if you do create a linked server
> http://sqlservercode.blogspot.com/
>|||Go to security/Linked Servers
Right Click on Linked Servers--> New Linked Server
Use Microsoft OLE DB Provider for ODBC Drivers
Use the Connection String
DRIVER={MySQL ODBC 3.51
Driver};SERVER=myserver.com;DATABASE=database;USER
=user;PASSWORD=password;OPTION=3
And in Provider Options select:
level zero only
Non-transacted updates (something)
Allow InProcess
the linked thing will work as
mylinkedserver...tablename
http://sqlservercode.blogspot.com/|||Hey SQL, that worked perfectly, thanx a lot!
now I see the tables of "DatabaseXYZ" the "MyLinkedMySQLServer". But how can
I now create a job that imports all data from "Table1" of "DatabaseXYZ"
into "DatabaseABC" of my MSSQLServer?
Cheers, Jens
"SQL" <denis.gobo@.gmail.com> schrieb im Newsbeitrag
news:1134491324.820321.274410@.z14g2000cwz.googlegroups.com...
> Go to security/Linked Servers
> Right Click on Linked Servers--> New Linked Server
> Use Microsoft OLE DB Provider for ODBC Drivers
> Use the Connection String
> DRIVER={MySQL ODBC 3.51
> Driver};SERVER=myserver.com;DATABASE=database;USER
> =user;PASSWORD=password;OPTION=3
> And in Provider Options select:
> level zero only
> Non-transacted updates (something)
> Allow InProcess
> the linked thing will work as
> mylinkedserver...tablename
>
> http://sqlservercode.blogspot.com/
>|||Create a job and copy this code (obviously you will have to change it,
for example i don't know what your join condition is)
Then create a schedule and that should do it
insert into DatabaseABC.dbo.table1
select * from DatabaseABC.dbo.table1 ms left join
MyLinkedMySQLServer.DatabaseXYZ.Owner.Table1 my
on ms.id =my.id
where my.id is null
To Create a job go to Managment-->Sql server Agent-->Jobs
Right click --> New Job add a step paste the code
add the schedule and that should do it
http://sqlservercode.blogspot.com/

Automatical Import from MySQL-Table?

Hi All!
is it possible to create a job in the SQL Enterprise Manager that regularly
imports the data from a table of a MySQL-database?
how could I achieve this aim without having to program a lot?
any hints are highly appreciated!
cheers, jensTake a look at OPENROWSET if you don't want to create a linked server
or OPENQUERY if you do create a linked server
http://sqlservercode.blogspot.com/|||Hi SQL,
for me a so called "linked server" sounds better, how could I create a
linked server in the MS SQL ENterprise Manager?
Cheers, Jens
"SQL" <denis.gobo@.gmail.com> schrieb im Newsbeitrag
news:1134485484.301414.309720@.g43g2000cwa.googlegroups.com...
> Take a look at OPENROWSET if you don't want to create a linked server
> or OPENQUERY if you do create a linked server
> http://sqlservercode.blogspot.com/
>|||Go to security/Linked Servers
Right Click on Linked Servers--> New Linked Server
Use Microsoft OLE DB Provider for ODBC Drivers
Use the Connection String
DRIVER={MySQL ODBC 3.51
Driver};SERVER=myserver.com;DATABASE=database;USER
=user;PASSWORD=password;OPTION=3
And in Provider Options select:
level zero only
Non-transacted updates (something)
Allow InProcess
the linked thing will work as
mylinkedserver...tablename
http://sqlservercode.blogspot.com/|||Hey SQL, that worked perfectly, thanx a lot!
now I see the tables of "DatabaseXYZ" the "MyLinkedMySQLServer". But how can
I now create a job that imports all data from "Table1" of "DatabaseXYZ"
into "DatabaseABC" of my MSSQLServer?
Cheers, Jens
"SQL" <denis.gobo@.gmail.com> schrieb im Newsbeitrag
news:1134491324.820321.274410@.z14g2000cwz.googlegroups.com...
> Go to security/Linked Servers
> Right Click on Linked Servers--> New Linked Server
> Use Microsoft OLE DB Provider for ODBC Drivers
> Use the Connection String
> DRIVER={MySQL ODBC 3.51
> Driver};SERVER=myserver.com;DATABASE=database;USER
> =user;PASSWORD=password;OPTION=3
> And in Provider Options select:
> level zero only
> Non-transacted updates (something)
> Allow InProcess
> the linked thing will work as
> mylinkedserver...tablename
>
> http://sqlservercode.blogspot.com/
>|||Create a job and copy this code (obviously you will have to change it,
for example i don't know what your join condition is)
Then create a schedule and that should do it
insert into DatabaseABC.dbo.table1
select * from DatabaseABC.dbo.table1 ms left join
MyLinkedMySQLServer.DatabaseXYZ.Owner.Table1 my
on ms.id =my.id
where my.id is null
To Create a job go to Managment-->Sql server Agent-->Jobs
Right click --> New Job add a step paste the code
add the schedule and that should do it
http://sqlservercode.blogspot.com/

automatical grow does not work

Hi all,
we encountered problem on testing environment that database does not grow
even though it is configured to grow automatically. There is SQL Server 2000
installed on two Windows Server 2003 in cluster. Application runs on its
dedicated WinServer using ADO.NET for data manipulation. When data file is
full (or it remains only small free space ~2MB), all application's db
requests fail with timeout. When I manually enlarge data file, application
starts to work again.
Is there any known issue regarding to automatical grow of database. Does
anyone face this problem ?
eXavierHi
How big is your database?
It might take time .For example if the database is 20GB and you set a 20%
growth rate that means if the database grows it will increase by 2GB. This
may or may not be what you want.
"eXavier" <fhns@.centrum.cz> wrote in message
news:ekIyJogWFHA.2740@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> we encountered problem on testing environment that database does not grow
> even though it is configured to grow automatically. There is SQL Server
2000
> installed on two Windows Server 2003 in cluster. Application runs on its
> dedicated WinServer using ADO.NET for data manipulation. When data file is
> full (or it remains only small free space ~2MB), all application's db
> requests fail with timeout. When I manually enlarge data file, application
> starts to work again.
> Is there any known issue regarding to automatical grow of database. Does
> anyone face this problem ?
> eXavier
>|||After restore, the database is about 1.7 GB, I tried to set growing to
both - fixed amount and percentage factor with no success. When I did SQL
insert from QA - it took about 3 minutes but the file increased. I have only
60 seconds timeout on ADO connection string, may it be that timeouting of
connection prior to data are inserted could cause not performing of file
growth ?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23gBBktgWFHA.2572@.TK2MSFTNGP14.phx.gbl...
> Hi
> How big is your database?
> It might take time .For example if the database is 20GB and you set a 20%
> growth rate that means if the database grows it will increase by 2GB.
This
> may or may not be what you want.
>
>
>
> "eXavier" <fhns@.centrum.cz> wrote in message
> news:ekIyJogWFHA.2740@.TK2MSFTNGP14.phx.gbl...
grow[vbcol=seagreen]
> 2000
is[vbcol=seagreen]
application[vbcol=seagreen]
>|||Well, what is about your hardware? Do you have enough space for the database
file?
How many users connected to the database? Have you checked are there
blocking,locking?
Try to change a TimeOut command to 0
"eXavier" <fhns@.centrum.cz> wrote in message
news:OS9EcBhWFHA.3840@.tk2msftngp13.phx.gbl...
> After restore, the database is about 1.7 GB, I tried to set growing to
> both - fixed amount and percentage factor with no success. When I did SQL
> insert from QA - it took about 3 minutes but the file increased. I have
only
> 60 seconds timeout on ADO connection string, may it be that timeouting of
> connection prior to data are inserted could cause not performing of file
> growth ?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23gBBktgWFHA.2572@.TK2MSFTNGP14.phx.gbl...
20%[vbcol=seagreen]
> This
> grow
Server[vbcol=seagreen]
its[vbcol=seagreen]
file[vbcol=seagreen]
> is
> application
Does[vbcol=seagreen]
>|||First off you should never rely on Autogrow to do the work for you. You
should always have plenty of free space in the files so Autogrow never kicks
in. If a thread invokes the autogrow process and during the growth the user
connection times out it can roll back the growth that it did as well. So it
may not look like it grew when in fact it did and then shrunk again. Never
use the default of 10% on anything over a few hundred MB's. And make it just
a few MB so as you are sure it will grow in less than 30 seconds if you have
a slow disk subsystem.
Andrew J. Kelly SQL MVP
"eXavier" <fhns@.centrum.cz> wrote in message
news:ekIyJogWFHA.2740@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> we encountered problem on testing environment that database does not grow
> even though it is configured to grow automatically. There is SQL Server
> 2000
> installed on two Windows Server 2003 in cluster. Application runs on its
> dedicated WinServer using ADO.NET for data manipulation. When data file is
> full (or it remains only small free space ~2MB), all application's db
> requests fail with timeout. When I manually enlarge data file, application
> starts to work again.
> Is there any known issue regarding to automatical grow of database. Does
> anyone face this problem ?
> eXavier
>|||Thank you for your explanation. In fact, I was facing similar issue, when
transaction log was growing too fast and when it was about 2 GB, the
application also started to timeout. To avoid this I created job for
shrinking transaction log every midnight. Is it good practice to do some
file size check/grow job yet?
eXavier
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23f3AFLhWFHA.3176@.TK2MSFTNGP12.phx.gbl...
> First off you should never rely on Autogrow to do the work for you. You
> should always have plenty of free space in the files so Autogrow never
kicks
> in. If a thread invokes the autogrow process and during the growth the
user
> connection times out it can roll back the growth that it did as well. So
it
> may not look like it grew when in fact it did and then shrunk again.
Never
> use the default of 10% on anything over a few hundred MB's. And make it
just
> a few MB so as you are sure it will grow in less than 30 seconds if you
have
> a slow disk subsystem.
> --
> Andrew J. Kelly SQL MVP
>
> "eXavier" <fhns@.centrum.cz> wrote in message
> news:ekIyJogWFHA.2740@.TK2MSFTNGP14.phx.gbl...
grow[vbcol=seagreen]
is[vbcol=seagreen]
application[vbcol=seagreen]
>|||Why are you shrinking the files? You just proved to yourself that the
growth was causing problems and yet you still continue to shrink. Have a
look here:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Andrew J. Kelly SQL MVP
"eXavier" <fhns@.centrum.cz> wrote in message
news:eIfscSiWFHA.1796@.TK2MSFTNGP15.phx.gbl...
> Thank you for your explanation. In fact, I was facing similar issue, when
> transaction log was growing too fast and when it was about 2 GB, the
> application also started to timeout. To avoid this I created job for
> shrinking transaction log every midnight. Is it good practice to do some
> file size check/grow job yet?
> eXavier
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23f3AFLhWFHA.3176@.TK2MSFTNGP12.phx.gbl...
> kicks
> user
> it
> Never
> just
> have
> grow
> is
> application
>|||I shrink the transaction log because the application hangs if the file
becomes too large (~2GB). There is enough space on disk array (~40GB).
Probably it could be the same problem as with data file - timeouted
connection together with autogrow, I'll have to test it.
If I 'd set max size of log file, does SQL Server reuses the file from
beggining ? Now it looks like the log size is still increasing, appending
data at the end - this is the reason for my daily shrink. (I left default
values for auto grow - i.e. unrestricted grow by 10%, I'll change it.). What
are best practices for setting size / max siz of database/log. I expect data
file size could be 5-10 GB in my application, but don't have idea about
optimal size of transaction log and if it is better to set max size or
rather letting it grow unrestricted.
The application is highly transactional, but there are typically not much
transactions running in parallel - it processes text-based business messages
mapping them to database - several clients then operates on data typically
changing states again in DB transaction.
Do you have any tips or could you recomend some articles or books on this
topic ?
Thanks a lot
eXavier
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uwlMFsiWFHA.796@.TK2MSFTNGP09.phx.gbl...
> Why are you shrinking the files? You just proved to yourself that the
> growth was causing problems and yet you still continue to shrink. Have a
> look here:
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> --
> Andrew J. Kelly SQL MVP
>
> "eXavier" <fhns@.centrum.cz> wrote in message
> news:eIfscSiWFHA.1796@.TK2MSFTNGP15.phx.gbl...
when[vbcol=seagreen]
You[vbcol=seagreen]
So[vbcol=seagreen]
Server[vbcol=seagreen]
file[vbcol=seagreen]
>|||> the application hangs if the file
> becomes too large (~2GB
Yes, because with a 2GB log and 10% growth you are forcing SQL Server
to allocate upto 200MB in a transaction! Best policy is to assess the
correct size under test conditions, then fix the size and leave it. No
shrinking and no autogrow. Log usage should be controlled by the
frequency of log backups, not by shrinking. Shrinking achieves nothing
in a production OLTP environment.
Once you've fixed the size, monitor usage and create a new log file if
you need one rather than increase the size of the existing one. Since
your file system will already be highly fragmented you should probably
start afresh with a new file of the correct size for optimum
performance.
David Portas
SQL Server MVP
--|||> If I 'd set max size of log file, does SQL Server reuses the file from
> beggining ?
If SQL Server were to do that, you could end up in a situation where SQL Ser
ver had break your
sequence of transaction log backup files. If the db is in full recovery mode
l, the log is emptied
when you do log backup. If you don't do log backup, run in simple recovery m
ode.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"eXavier" <fhns@.centrum.cz> wrote in message news:ufn39WjWFHA.3240@.TK2MSFTNGP10.phx.gbl...[v
bcol=seagreen]
>I shrink the transaction log because the application hangs if the file
> becomes too large (~2GB). There is enough space on disk array (~40GB).
> Probably it could be the same problem as with data file - timeouted
> connection together with autogrow, I'll have to test it.
> If I 'd set max size of log file, does SQL Server reuses the file from
> beggining ? Now it looks like the log size is still increasing, appending
> data at the end - this is the reason for my daily shrink. (I left default
> values for auto grow - i.e. unrestricted grow by 10%, I'll change it.). Wh
at
> are best practices for setting size / max siz of database/log. I expect da
ta
> file size could be 5-10 GB in my application, but don't have idea about
> optimal size of transaction log and if it is better to set max size or
> rather letting it grow unrestricted.
> The application is highly transactional, but there are typically not much
> transactions running in parallel - it processes text-based business messag
es
> mapping them to database - several clients then operates on data typically
> changing states again in DB transaction.
> Do you have any tips or could you recomend some articles or books on this
> topic ?
> Thanks a lot
> eXavier
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uwlMFsiWFHA.796@.TK2MSFTNGP09.phx.gbl...
> when
> You
> So
> Server
> file
>[/vbcol]sql

automatical grow does not work

Hi all,
we encountered problem on testing environment that database does not grow
even though it is configured to grow automatically. There is SQL Server 2000
installed on two Windows Server 2003 in cluster. Application runs on its
dedicated WinServer using ADO.NET for data manipulation. When data file is
full (or it remains only small free space ~2MB), all application's db
requests fail with timeout. When I manually enlarge data file, application
starts to work again.
Is there any known issue regarding to automatical grow of database. Does
anyone face this problem ?
eXavierHi
How big is your database?
It might take time .For example if the database is 20GB and you set a 20%
growth rate that means if the database grows it will increase by 2GB. This
may or may not be what you want.
"eXavier" <fhns@.centrum.cz> wrote in message
news:ekIyJogWFHA.2740@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> we encountered problem on testing environment that database does not grow
> even though it is configured to grow automatically. There is SQL Server
2000
> installed on two Windows Server 2003 in cluster. Application runs on its
> dedicated WinServer using ADO.NET for data manipulation. When data file is
> full (or it remains only small free space ~2MB), all application's db
> requests fail with timeout. When I manually enlarge data file, application
> starts to work again.
> Is there any known issue regarding to automatical grow of database. Does
> anyone face this problem ?
> eXavier
>|||After restore, the database is about 1.7 GB, I tried to set growing to
both - fixed amount and percentage factor with no success. When I did SQL
insert from QA - it took about 3 minutes but the file increased. I have only
60 seconds timeout on ADO connection string, may it be that timeouting of
connection prior to data are inserted could cause not performing of file
growth ?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23gBBktgWFHA.2572@.TK2MSFTNGP14.phx.gbl...
> Hi
> How big is your database?
> It might take time .For example if the database is 20GB and you set a 20%
> growth rate that means if the database grows it will increase by 2GB.
This
> may or may not be what you want.
>
>
>
> "eXavier" <fhns@.centrum.cz> wrote in message
> news:ekIyJogWFHA.2740@.TK2MSFTNGP14.phx.gbl...
grow
> 2000
is
application
>|||Well, what is about your hardware? Do you have enough space for the database
file?
How many users connected to the database? Have you checked are there
blocking,locking?
Try to change a TimeOut command to 0
"eXavier" <fhns@.centrum.cz> wrote in message
news:OS9EcBhWFHA.3840@.tk2msftngp13.phx.gbl...
> After restore, the database is about 1.7 GB, I tried to set growing to
> both - fixed amount and percentage factor with no success. When I did SQL
> insert from QA - it took about 3 minutes but the file increased. I have
only
> 60 seconds timeout on ADO connection string, may it be that timeouting of
> connection prior to data are inserted could cause not performing of file
> growth ?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23gBBktgWFHA.2572@.TK2MSFTNGP14.phx.gbl...
20%
> This
> grow
Server
its
file
> is
> application
Does
>|||First off you should never rely on Autogrow to do the work for you. You
should always have plenty of free space in the files so Autogrow never kicks
in. If a thread invokes the autogrow process and during the growth the user
connection times out it can roll back the growth that it did as well. So it
may not look like it grew when in fact it did and then shrunk again. Never
use the default of 10% on anything over a few hundred MB's. And make it just
a few MB so as you are sure it will grow in less than 30 seconds if you have
a slow disk subsystem.
Andrew J. Kelly SQL MVP
"eXavier" <fhns@.centrum.cz> wrote in message
news:ekIyJogWFHA.2740@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> we encountered problem on testing environment that database does not grow
> even though it is configured to grow automatically. There is SQL Server
> 2000
> installed on two Windows Server 2003 in cluster. Application runs on its
> dedicated WinServer using ADO.NET for data manipulation. When data file is
> full (or it remains only small free space ~2MB), all application's db
> requests fail with timeout. When I manually enlarge data file, application
> starts to work again.
> Is there any known issue regarding to automatical grow of database. Does
> anyone face this problem ?
> eXavier
>|||Thank you for your explanation. In fact, I was facing similar issue, when
transaction log was growing too fast and when it was about 2 GB, the
application also started to timeout. To avoid this I created job for
shrinking transaction log every midnight. Is it good practice to do some
file size check/grow job yet?
eXavier
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23f3AFLhWFHA.3176@.TK2MSFTNGP12.phx.gbl...
> First off you should never rely on Autogrow to do the work for you. You
> should always have plenty of free space in the files so Autogrow never
kicks
> in. If a thread invokes the autogrow process and during the growth the
user
> connection times out it can roll back the growth that it did as well. So
it
> may not look like it grew when in fact it did and then shrunk again.
Never
> use the default of 10% on anything over a few hundred MB's. And make it
just
> a few MB so as you are sure it will grow in less than 30 seconds if you
have
> a slow disk subsystem.
> --
> Andrew J. Kelly SQL MVP
>
> "eXavier" <fhns@.centrum.cz> wrote in message
> news:ekIyJogWFHA.2740@.TK2MSFTNGP14.phx.gbl...
grow
is
application
>|||Why are you shrinking the files? You just proved to yourself that the
growth was causing problems and yet you still continue to shrink. Have a
look here:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Andrew J. Kelly SQL MVP
"eXavier" <fhns@.centrum.cz> wrote in message
news:eIfscSiWFHA.1796@.TK2MSFTNGP15.phx.gbl...
> Thank you for your explanation. In fact, I was facing similar issue, when
> transaction log was growing too fast and when it was about 2 GB, the
> application also started to timeout. To avoid this I created job for
> shrinking transaction log every midnight. Is it good practice to do some
> file size check/grow job yet?
> eXavier
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23f3AFLhWFHA.3176@.TK2MSFTNGP12.phx.gbl...
> kicks
> user
> it
> Never
> just
> have
> grow
> is
> application
>|||I shrink the transaction log because the application hangs if the file
becomes too large (~2GB). There is enough space on disk array (~40GB).
Probably it could be the same problem as with data file - timeouted
connection together with autogrow, I'll have to test it.
If I 'd set max size of log file, does SQL Server reuses the file from
beggining ? Now it looks like the log size is still increasing, appending
data at the end - this is the reason for my daily shrink. (I left default
values for auto grow - i.e. unrestricted grow by 10%, I'll change it.). What
are best practices for setting size / max siz of database/log. I expect data
file size could be 5-10 GB in my application, but don't have idea about
optimal size of transaction log and if it is better to set max size or
rather letting it grow unrestricted.
The application is highly transactional, but there are typically not much
transactions running in parallel - it processes text-based business messages
mapping them to database - several clients then operates on data typically
changing states again in DB transaction.
Do you have any tips or could you recomend some articles or books on this
topic ?
Thanks a lot
eXavier
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uwlMFsiWFHA.796@.TK2MSFTNGP09.phx.gbl...
> Why are you shrinking the files? You just proved to yourself that the
> growth was causing problems and yet you still continue to shrink. Have a
> look here:
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> --
> Andrew J. Kelly SQL MVP
>
> "eXavier" <fhns@.centrum.cz> wrote in message
> news:eIfscSiWFHA.1796@.TK2MSFTNGP15.phx.gbl...
when
You
So
Server
file
>|||> the application hangs if the file
> becomes too large (~2GB
Yes, because with a 2GB log and 10% growth you are forcing SQL Server
to allocate upto 200MB in a transaction! Best policy is to assess the
correct size under test conditions, then fix the size and leave it. No
shrinking and no autogrow. Log usage should be controlled by the
frequency of log backups, not by shrinking. Shrinking achieves nothing
in a production OLTP environment.
Once you've fixed the size, monitor usage and create a new log file if
you need one rather than increase the size of the existing one. Since
your file system will already be highly fragmented you should probably
start afresh with a new file of the correct size for optimum
performance.
David Portas
SQL Server MVP
--|||> If I 'd set max size of log file, does SQL Server reuses the file from
> beggining ?
If SQL Server were to do that, you could end up in a situation where SQL Ser
ver had break your
sequence of transaction log backup files. If the db is in full recovery mode
l, the log is emptied
when you do log backup. If you don't do log backup, run in simple recovery m
ode.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"eXavier" <fhns@.centrum.cz> wrote in message news:ufn39WjWFHA.3240@.TK2MSFTNGP10.phx.gbl...[
color=darkred]
>I shrink the transaction log because the application hangs if the file
> becomes too large (~2GB). There is enough space on disk array (~40GB).
> Probably it could be the same problem as with data file - timeouted
> connection together with autogrow, I'll have to test it.
> If I 'd set max size of log file, does SQL Server reuses the file from
> beggining ? Now it looks like the log size is still increasing, appending
> data at the end - this is the reason for my daily shrink. (I left default
> values for auto grow - i.e. unrestricted grow by 10%, I'll change it.). Wh
at
> are best practices for setting size / max siz of database/log. I expect da
ta
> file size could be 5-10 GB in my application, but don't have idea about
> optimal size of transaction log and if it is better to set max size or
> rather letting it grow unrestricted.
> The application is highly transactional, but there are typically not much
> transactions running in parallel - it processes text-based business messag
es
> mapping them to database - several clients then operates on data typically
> changing states again in DB transaction.
> Do you have any tips or could you recomend some articles or books on this
> topic ?
> Thanks a lot
> eXavier
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uwlMFsiWFHA.796@.TK2MSFTNGP09.phx.gbl...
> when
> You
> So
> Server
> file
>[/color]

automatical grow does not work

Hi all,
we encountered problem on testing environment that database does not grow
even though it is configured to grow automatically. There is SQL Server 2000
installed on two Windows Server 2003 in cluster. Application runs on its
dedicated WinServer using ADO.NET for data manipulation. When data file is
full (or it remains only small free space ~2MB), all application's db
requests fail with timeout. When I manually enlarge data file, application
starts to work again.
Is there any known issue regarding to automatical grow of database. Does
anyone face this problem ?
eXavier
Hi
How big is your database?
It might take time .For example if the database is 20GB and you set a 20%
growth rate that means if the database grows it will increase by 2GB. This
may or may not be what you want.
"eXavier" <fhns@.centrum.cz> wrote in message
news:ekIyJogWFHA.2740@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> we encountered problem on testing environment that database does not grow
> even though it is configured to grow automatically. There is SQL Server
2000
> installed on two Windows Server 2003 in cluster. Application runs on its
> dedicated WinServer using ADO.NET for data manipulation. When data file is
> full (or it remains only small free space ~2MB), all application's db
> requests fail with timeout. When I manually enlarge data file, application
> starts to work again.
> Is there any known issue regarding to automatical grow of database. Does
> anyone face this problem ?
> eXavier
>
|||After restore, the database is about 1.7 GB, I tried to set growing to
both - fixed amount and percentage factor with no success. When I did SQL
insert from QA - it took about 3 minutes but the file increased. I have only
60 seconds timeout on ADO connection string, may it be that timeouting of
connection prior to data are inserted could cause not performing of file
growth ?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23gBBktgWFHA.2572@.TK2MSFTNGP14.phx.gbl...
> Hi
> How big is your database?
> It might take time .For example if the database is 20GB and you set a 20%
> growth rate that means if the database grows it will increase by 2GB.
This[vbcol=seagreen]
> may or may not be what you want.
>
>
>
> "eXavier" <fhns@.centrum.cz> wrote in message
> news:ekIyJogWFHA.2740@.TK2MSFTNGP14.phx.gbl...
grow[vbcol=seagreen]
> 2000
is[vbcol=seagreen]
application
>
|||Well, what is about your hardware? Do you have enough space for the database
file?
How many users connected to the database? Have you checked are there
blocking,locking?
Try to change a TimeOut command to 0
"eXavier" <fhns@.centrum.cz> wrote in message
news:OS9EcBhWFHA.3840@.tk2msftngp13.phx.gbl...
> After restore, the database is about 1.7 GB, I tried to set growing to
> both - fixed amount and percentage factor with no success. When I did SQL
> insert from QA - it took about 3 minutes but the file increased. I have
only[vbcol=seagreen]
> 60 seconds timeout on ADO connection string, may it be that timeouting of
> connection prior to data are inserted could cause not performing of file
> growth ?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23gBBktgWFHA.2572@.TK2MSFTNGP14.phx.gbl...
20%[vbcol=seagreen]
> This
> grow
Server[vbcol=seagreen]
its[vbcol=seagreen]
file[vbcol=seagreen]
> is
> application
Does
>
|||First off you should never rely on Autogrow to do the work for you. You
should always have plenty of free space in the files so Autogrow never kicks
in. If a thread invokes the autogrow process and during the growth the user
connection times out it can roll back the growth that it did as well. So it
may not look like it grew when in fact it did and then shrunk again. Never
use the default of 10% on anything over a few hundred MB's. And make it just
a few MB so as you are sure it will grow in less than 30 seconds if you have
a slow disk subsystem.
Andrew J. Kelly SQL MVP
"eXavier" <fhns@.centrum.cz> wrote in message
news:ekIyJogWFHA.2740@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> we encountered problem on testing environment that database does not grow
> even though it is configured to grow automatically. There is SQL Server
> 2000
> installed on two Windows Server 2003 in cluster. Application runs on its
> dedicated WinServer using ADO.NET for data manipulation. When data file is
> full (or it remains only small free space ~2MB), all application's db
> requests fail with timeout. When I manually enlarge data file, application
> starts to work again.
> Is there any known issue regarding to automatical grow of database. Does
> anyone face this problem ?
> eXavier
>
|||Thank you for your explanation. In fact, I was facing similar issue, when
transaction log was growing too fast and when it was about 2 GB, the
application also started to timeout. To avoid this I created job for
shrinking transaction log every midnight. Is it good practice to do some
file size check/grow job yet?
eXavier
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23f3AFLhWFHA.3176@.TK2MSFTNGP12.phx.gbl...
> First off you should never rely on Autogrow to do the work for you. You
> should always have plenty of free space in the files so Autogrow never
kicks
> in. If a thread invokes the autogrow process and during the growth the
user
> connection times out it can roll back the growth that it did as well. So
it
> may not look like it grew when in fact it did and then shrunk again.
Never
> use the default of 10% on anything over a few hundred MB's. And make it
just
> a few MB so as you are sure it will grow in less than 30 seconds if you
have[vbcol=seagreen]
> a slow disk subsystem.
> --
> Andrew J. Kelly SQL MVP
>
> "eXavier" <fhns@.centrum.cz> wrote in message
> news:ekIyJogWFHA.2740@.TK2MSFTNGP14.phx.gbl...
grow[vbcol=seagreen]
is[vbcol=seagreen]
application
>
|||Why are you shrinking the files? You just proved to yourself that the
growth was causing problems and yet you still continue to shrink. Have a
look here:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Andrew J. Kelly SQL MVP
"eXavier" <fhns@.centrum.cz> wrote in message
news:eIfscSiWFHA.1796@.TK2MSFTNGP15.phx.gbl...
> Thank you for your explanation. In fact, I was facing similar issue, when
> transaction log was growing too fast and when it was about 2 GB, the
> application also started to timeout. To avoid this I created job for
> shrinking transaction log every midnight. Is it good practice to do some
> file size check/grow job yet?
> eXavier
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23f3AFLhWFHA.3176@.TK2MSFTNGP12.phx.gbl...
> kicks
> user
> it
> Never
> just
> have
> grow
> is
> application
>
|||I shrink the transaction log because the application hangs if the file
becomes too large (~2GB). There is enough space on disk array (~40GB).
Probably it could be the same problem as with data file - timeouted
connection together with autogrow, I'll have to test it.
If I 'd set max size of log file, does SQL Server reuses the file from
beggining ? Now it looks like the log size is still increasing, appending
data at the end - this is the reason for my daily shrink. (I left default
values for auto grow - i.e. unrestricted grow by 10%, I'll change it.). What
are best practices for setting size / max siz of database/log. I expect data
file size could be 5-10 GB in my application, but don't have idea about
optimal size of transaction log and if it is better to set max size or
rather letting it grow unrestricted.
The application is highly transactional, but there are typically not much
transactions running in parallel - it processes text-based business messages
mapping them to database - several clients then operates on data typically
changing states again in DB transaction.
Do you have any tips or could you recomend some articles or books on this
topic ?
Thanks a lot
eXavier
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uwlMFsiWFHA.796@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Why are you shrinking the files? You just proved to yourself that the
> growth was causing problems and yet you still continue to shrink. Have a
> look here:
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> --
> Andrew J. Kelly SQL MVP
>
> "eXavier" <fhns@.centrum.cz> wrote in message
> news:eIfscSiWFHA.1796@.TK2MSFTNGP15.phx.gbl...
when[vbcol=seagreen]
You[vbcol=seagreen]
So[vbcol=seagreen]
Server[vbcol=seagreen]
file
>
|||> the application hangs if the file
> becomes too large (~2GB
Yes, because with a 2GB log and 10% growth you are forcing SQL Server
to allocate upto 200MB in a transaction! Best policy is to assess the
correct size under test conditions, then fix the size and leave it. No
shrinking and no autogrow. Log usage should be controlled by the
frequency of log backups, not by shrinking. Shrinking achieves nothing
in a production OLTP environment.
Once you've fixed the size, monitor usage and create a new log file if
you need one rather than increase the size of the existing one. Since
your file system will already be highly fragmented you should probably
start afresh with a new file of the correct size for optimum
performance.
David Portas
SQL Server MVP
|||> If I 'd set max size of log file, does SQL Server reuses the file from
> beggining ?
If SQL Server were to do that, you could end up in a situation where SQL Server had break your
sequence of transaction log backup files. If the db is in full recovery model, the log is emptied
when you do log backup. If you don't do log backup, run in simple recovery mode.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"eXavier" <fhns@.centrum.cz> wrote in message news:ufn39WjWFHA.3240@.TK2MSFTNGP10.phx.gbl...
>I shrink the transaction log because the application hangs if the file
> becomes too large (~2GB). There is enough space on disk array (~40GB).
> Probably it could be the same problem as with data file - timeouted
> connection together with autogrow, I'll have to test it.
> If I 'd set max size of log file, does SQL Server reuses the file from
> beggining ? Now it looks like the log size is still increasing, appending
> data at the end - this is the reason for my daily shrink. (I left default
> values for auto grow - i.e. unrestricted grow by 10%, I'll change it.). What
> are best practices for setting size / max siz of database/log. I expect data
> file size could be 5-10 GB in my application, but don't have idea about
> optimal size of transaction log and if it is better to set max size or
> rather letting it grow unrestricted.
> The application is highly transactional, but there are typically not much
> transactions running in parallel - it processes text-based business messages
> mapping them to database - several clients then operates on data typically
> changing states again in DB transaction.
> Do you have any tips or could you recomend some articles or books on this
> topic ?
> Thanks a lot
> eXavier
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uwlMFsiWFHA.796@.TK2MSFTNGP09.phx.gbl...
> when
> You
> So
> Server
> file
>

automatical grow does not work

Hi all,
we encountered problem on testing environment that database does not grow
even though it is configured to grow automatically. There is SQL Server 2000
installed on two Windows Server 2003 in cluster. Application runs on its
dedicated WinServer using ADO.NET for data manipulation. When data file is
full (or it remains only small free space ~2MB), all application's db
requests fail with timeout. When I manually enlarge data file, application
starts to work again.
Is there any known issue regarding to automatical grow of database. Does
anyone face this problem ?
eXavierHi
How big is your database?
It might take time .For example if the database is 20GB and you set a 20%
growth rate that means if the database grows it will increase by 2GB. This
may or may not be what you want.
"eXavier" <fhns@.centrum.cz> wrote in message
news:ekIyJogWFHA.2740@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> we encountered problem on testing environment that database does not grow
> even though it is configured to grow automatically. There is SQL Server
2000
> installed on two Windows Server 2003 in cluster. Application runs on its
> dedicated WinServer using ADO.NET for data manipulation. When data file is
> full (or it remains only small free space ~2MB), all application's db
> requests fail with timeout. When I manually enlarge data file, application
> starts to work again.
> Is there any known issue regarding to automatical grow of database. Does
> anyone face this problem ?
> eXavier
>|||After restore, the database is about 1.7 GB, I tried to set growing to
both - fixed amount and percentage factor with no success. When I did SQL
insert from QA - it took about 3 minutes but the file increased. I have only
60 seconds timeout on ADO connection string, may it be that timeouting of
connection prior to data are inserted could cause not performing of file
growth ?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23gBBktgWFHA.2572@.TK2MSFTNGP14.phx.gbl...
> Hi
> How big is your database?
> It might take time .For example if the database is 20GB and you set a 20%
> growth rate that means if the database grows it will increase by 2GB.
This
> may or may not be what you want.
>
>
>
> "eXavier" <fhns@.centrum.cz> wrote in message
> news:ekIyJogWFHA.2740@.TK2MSFTNGP14.phx.gbl...
> > Hi all,
> > we encountered problem on testing environment that database does not
grow
> > even though it is configured to grow automatically. There is SQL Server
> 2000
> > installed on two Windows Server 2003 in cluster. Application runs on its
> > dedicated WinServer using ADO.NET for data manipulation. When data file
is
> > full (or it remains only small free space ~2MB), all application's db
> > requests fail with timeout. When I manually enlarge data file,
application
> > starts to work again.
> > Is there any known issue regarding to automatical grow of database. Does
> > anyone face this problem ?
> >
> > eXavier
> >
> >
>|||Well, what is about your hardware? Do you have enough space for the database
file?
How many users connected to the database? Have you checked are there
blocking,locking?
Try to change a TimeOut command to 0
"eXavier" <fhns@.centrum.cz> wrote in message
news:OS9EcBhWFHA.3840@.tk2msftngp13.phx.gbl...
> After restore, the database is about 1.7 GB, I tried to set growing to
> both - fixed amount and percentage factor with no success. When I did SQL
> insert from QA - it took about 3 minutes but the file increased. I have
only
> 60 seconds timeout on ADO connection string, may it be that timeouting of
> connection prior to data are inserted could cause not performing of file
> growth ?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23gBBktgWFHA.2572@.TK2MSFTNGP14.phx.gbl...
> > Hi
> > How big is your database?
> > It might take time .For example if the database is 20GB and you set a
20%
> > growth rate that means if the database grows it will increase by 2GB.
> This
> > may or may not be what you want.
> >
> >
> >
> >
> >
> >
> >
> > "eXavier" <fhns@.centrum.cz> wrote in message
> > news:ekIyJogWFHA.2740@.TK2MSFTNGP14.phx.gbl...
> > > Hi all,
> > > we encountered problem on testing environment that database does not
> grow
> > > even though it is configured to grow automatically. There is SQL
Server
> > 2000
> > > installed on two Windows Server 2003 in cluster. Application runs on
its
> > > dedicated WinServer using ADO.NET for data manipulation. When data
file
> is
> > > full (or it remains only small free space ~2MB), all application's db
> > > requests fail with timeout. When I manually enlarge data file,
> application
> > > starts to work again.
> > > Is there any known issue regarding to automatical grow of database.
Does
> > > anyone face this problem ?
> > >
> > > eXavier
> > >
> > >
> >
> >
>|||First off you should never rely on Autogrow to do the work for you. You
should always have plenty of free space in the files so Autogrow never kicks
in. If a thread invokes the autogrow process and during the growth the user
connection times out it can roll back the growth that it did as well. So it
may not look like it grew when in fact it did and then shrunk again. Never
use the default of 10% on anything over a few hundred MB's. And make it just
a few MB so as you are sure it will grow in less than 30 seconds if you have
a slow disk subsystem.
--
Andrew J. Kelly SQL MVP
"eXavier" <fhns@.centrum.cz> wrote in message
news:ekIyJogWFHA.2740@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> we encountered problem on testing environment that database does not grow
> even though it is configured to grow automatically. There is SQL Server
> 2000
> installed on two Windows Server 2003 in cluster. Application runs on its
> dedicated WinServer using ADO.NET for data manipulation. When data file is
> full (or it remains only small free space ~2MB), all application's db
> requests fail with timeout. When I manually enlarge data file, application
> starts to work again.
> Is there any known issue regarding to automatical grow of database. Does
> anyone face this problem ?
> eXavier
>|||Thank you for your explanation. In fact, I was facing similar issue, when
transaction log was growing too fast and when it was about 2 GB, the
application also started to timeout. To avoid this I created job for
shrinking transaction log every midnight. Is it good practice to do some
file size check/grow job yet?
eXavier
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23f3AFLhWFHA.3176@.TK2MSFTNGP12.phx.gbl...
> First off you should never rely on Autogrow to do the work for you. You
> should always have plenty of free space in the files so Autogrow never
kicks
> in. If a thread invokes the autogrow process and during the growth the
user
> connection times out it can roll back the growth that it did as well. So
it
> may not look like it grew when in fact it did and then shrunk again.
Never
> use the default of 10% on anything over a few hundred MB's. And make it
just
> a few MB so as you are sure it will grow in less than 30 seconds if you
have
> a slow disk subsystem.
> --
> Andrew J. Kelly SQL MVP
>
> "eXavier" <fhns@.centrum.cz> wrote in message
> news:ekIyJogWFHA.2740@.TK2MSFTNGP14.phx.gbl...
> > Hi all,
> > we encountered problem on testing environment that database does not
grow
> > even though it is configured to grow automatically. There is SQL Server
> > 2000
> > installed on two Windows Server 2003 in cluster. Application runs on its
> > dedicated WinServer using ADO.NET for data manipulation. When data file
is
> > full (or it remains only small free space ~2MB), all application's db
> > requests fail with timeout. When I manually enlarge data file,
application
> > starts to work again.
> > Is there any known issue regarding to automatical grow of database. Does
> > anyone face this problem ?
> >
> > eXavier
> >
> >
>|||Why are you shrinking the files? You just proved to yourself that the
growth was causing problems and yet you still continue to shrink. Have a
look here:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Andrew J. Kelly SQL MVP
"eXavier" <fhns@.centrum.cz> wrote in message
news:eIfscSiWFHA.1796@.TK2MSFTNGP15.phx.gbl...
> Thank you for your explanation. In fact, I was facing similar issue, when
> transaction log was growing too fast and when it was about 2 GB, the
> application also started to timeout. To avoid this I created job for
> shrinking transaction log every midnight. Is it good practice to do some
> file size check/grow job yet?
> eXavier
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23f3AFLhWFHA.3176@.TK2MSFTNGP12.phx.gbl...
>> First off you should never rely on Autogrow to do the work for you. You
>> should always have plenty of free space in the files so Autogrow never
> kicks
>> in. If a thread invokes the autogrow process and during the growth the
> user
>> connection times out it can roll back the growth that it did as well. So
> it
>> may not look like it grew when in fact it did and then shrunk again.
> Never
>> use the default of 10% on anything over a few hundred MB's. And make it
> just
>> a few MB so as you are sure it will grow in less than 30 seconds if you
> have
>> a slow disk subsystem.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "eXavier" <fhns@.centrum.cz> wrote in message
>> news:ekIyJogWFHA.2740@.TK2MSFTNGP14.phx.gbl...
>> > Hi all,
>> > we encountered problem on testing environment that database does not
> grow
>> > even though it is configured to grow automatically. There is SQL Server
>> > 2000
>> > installed on two Windows Server 2003 in cluster. Application runs on
>> > its
>> > dedicated WinServer using ADO.NET for data manipulation. When data file
> is
>> > full (or it remains only small free space ~2MB), all application's db
>> > requests fail with timeout. When I manually enlarge data file,
> application
>> > starts to work again.
>> > Is there any known issue regarding to automatical grow of database.
>> > Does
>> > anyone face this problem ?
>> >
>> > eXavier
>> >
>> >
>>
>|||I shrink the transaction log because the application hangs if the file
becomes too large (~2GB). There is enough space on disk array (~40GB).
Probably it could be the same problem as with data file - timeouted
connection together with autogrow, I'll have to test it.
If I 'd set max size of log file, does SQL Server reuses the file from
beggining ? Now it looks like the log size is still increasing, appending
data at the end - this is the reason for my daily shrink. (I left default
values for auto grow - i.e. unrestricted grow by 10%, I'll change it.). What
are best practices for setting size / max siz of database/log. I expect data
file size could be 5-10 GB in my application, but don't have idea about
optimal size of transaction log and if it is better to set max size or
rather letting it grow unrestricted.
The application is highly transactional, but there are typically not much
transactions running in parallel - it processes text-based business messages
mapping them to database - several clients then operates on data typically
changing states again in DB transaction.
Do you have any tips or could you recomend some articles or books on this
topic ?
Thanks a lot
eXavier
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uwlMFsiWFHA.796@.TK2MSFTNGP09.phx.gbl...
> Why are you shrinking the files? You just proved to yourself that the
> growth was causing problems and yet you still continue to shrink. Have a
> look here:
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> --
> Andrew J. Kelly SQL MVP
>
> "eXavier" <fhns@.centrum.cz> wrote in message
> news:eIfscSiWFHA.1796@.TK2MSFTNGP15.phx.gbl...
> > Thank you for your explanation. In fact, I was facing similar issue,
when
> > transaction log was growing too fast and when it was about 2 GB, the
> > application also started to timeout. To avoid this I created job for
> > shrinking transaction log every midnight. Is it good practice to do some
> > file size check/grow job yet?
> >
> > eXavier
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:%23f3AFLhWFHA.3176@.TK2MSFTNGP12.phx.gbl...
> >> First off you should never rely on Autogrow to do the work for you.
You
> >> should always have plenty of free space in the files so Autogrow never
> > kicks
> >> in. If a thread invokes the autogrow process and during the growth the
> > user
> >> connection times out it can roll back the growth that it did as well.
So
> > it
> >> may not look like it grew when in fact it did and then shrunk again.
> > Never
> >> use the default of 10% on anything over a few hundred MB's. And make it
> > just
> >> a few MB so as you are sure it will grow in less than 30 seconds if you
> > have
> >> a slow disk subsystem.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "eXavier" <fhns@.centrum.cz> wrote in message
> >> news:ekIyJogWFHA.2740@.TK2MSFTNGP14.phx.gbl...
> >> > Hi all,
> >> > we encountered problem on testing environment that database does not
> > grow
> >> > even though it is configured to grow automatically. There is SQL
Server
> >> > 2000
> >> > installed on two Windows Server 2003 in cluster. Application runs on
> >> > its
> >> > dedicated WinServer using ADO.NET for data manipulation. When data
file
> > is
> >> > full (or it remains only small free space ~2MB), all application's db
> >> > requests fail with timeout. When I manually enlarge data file,
> > application
> >> > starts to work again.
> >> > Is there any known issue regarding to automatical grow of database.
> >> > Does
> >> > anyone face this problem ?
> >> >
> >> > eXavier
> >> >
> >> >
> >>
> >>
> >
> >
>|||> the application hangs if the file
> becomes too large (~2GB
Yes, because with a 2GB log and 10% growth you are forcing SQL Server
to allocate upto 200MB in a transaction! Best policy is to assess the
correct size under test conditions, then fix the size and leave it. No
shrinking and no autogrow. Log usage should be controlled by the
frequency of log backups, not by shrinking. Shrinking achieves nothing
in a production OLTP environment.
Once you've fixed the size, monitor usage and create a new log file if
you need one rather than increase the size of the existing one. Since
your file system will already be highly fragmented you should probably
start afresh with a new file of the correct size for optimum
performance.
--
David Portas
SQL Server MVP
--|||> If I 'd set max size of log file, does SQL Server reuses the file from
> beggining ?
If SQL Server were to do that, you could end up in a situation where SQL Server had break your
sequence of transaction log backup files. If the db is in full recovery model, the log is emptied
when you do log backup. If you don't do log backup, run in simple recovery mode.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"eXavier" <fhns@.centrum.cz> wrote in message news:ufn39WjWFHA.3240@.TK2MSFTNGP10.phx.gbl...
>I shrink the transaction log because the application hangs if the file
> becomes too large (~2GB). There is enough space on disk array (~40GB).
> Probably it could be the same problem as with data file - timeouted
> connection together with autogrow, I'll have to test it.
> If I 'd set max size of log file, does SQL Server reuses the file from
> beggining ? Now it looks like the log size is still increasing, appending
> data at the end - this is the reason for my daily shrink. (I left default
> values for auto grow - i.e. unrestricted grow by 10%, I'll change it.). What
> are best practices for setting size / max siz of database/log. I expect data
> file size could be 5-10 GB in my application, but don't have idea about
> optimal size of transaction log and if it is better to set max size or
> rather letting it grow unrestricted.
> The application is highly transactional, but there are typically not much
> transactions running in parallel - it processes text-based business messages
> mapping them to database - several clients then operates on data typically
> changing states again in DB transaction.
> Do you have any tips or could you recomend some articles or books on this
> topic ?
> Thanks a lot
> eXavier
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uwlMFsiWFHA.796@.TK2MSFTNGP09.phx.gbl...
>> Why are you shrinking the files? You just proved to yourself that the
>> growth was causing problems and yet you still continue to shrink. Have a
>> look here:
>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "eXavier" <fhns@.centrum.cz> wrote in message
>> news:eIfscSiWFHA.1796@.TK2MSFTNGP15.phx.gbl...
>> > Thank you for your explanation. In fact, I was facing similar issue,
> when
>> > transaction log was growing too fast and when it was about 2 GB, the
>> > application also started to timeout. To avoid this I created job for
>> > shrinking transaction log every midnight. Is it good practice to do some
>> > file size check/grow job yet?
>> >
>> > eXavier
>> >
>> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> > news:%23f3AFLhWFHA.3176@.TK2MSFTNGP12.phx.gbl...
>> >> First off you should never rely on Autogrow to do the work for you.
> You
>> >> should always have plenty of free space in the files so Autogrow never
>> > kicks
>> >> in. If a thread invokes the autogrow process and during the growth the
>> > user
>> >> connection times out it can roll back the growth that it did as well.
> So
>> > it
>> >> may not look like it grew when in fact it did and then shrunk again.
>> > Never
>> >> use the default of 10% on anything over a few hundred MB's. And make it
>> > just
>> >> a few MB so as you are sure it will grow in less than 30 seconds if you
>> > have
>> >> a slow disk subsystem.
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >>
>> >>
>> >> "eXavier" <fhns@.centrum.cz> wrote in message
>> >> news:ekIyJogWFHA.2740@.TK2MSFTNGP14.phx.gbl...
>> >> > Hi all,
>> >> > we encountered problem on testing environment that database does not
>> > grow
>> >> > even though it is configured to grow automatically. There is SQL
> Server
>> >> > 2000
>> >> > installed on two Windows Server 2003 in cluster. Application runs on
>> >> > its
>> >> > dedicated WinServer using ADO.NET for data manipulation. When data
> file
>> > is
>> >> > full (or it remains only small free space ~2MB), all application's db
>> >> > requests fail with timeout. When I manually enlarge data file,
>> > application
>> >> > starts to work again.
>> >> > Is there any known issue regarding to automatical grow of database.
>> >> > Does
>> >> > anyone face this problem ?
>> >> >
>> >> > eXavier
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>

automatical calculation of a field value

Hi All,
I have a table "AggregatedSales" which has a column called
"AggregatedSales.YearSum".
This column should contain a sum which can be derived directly from another
table called "Sales" by summing up all values of "Sales.Price" WHERE
"Sales.Year" = 2005.
Is it possible to have MS SQL Server automatically calculate the value for
"AggregatedSales.YearSum" when a new data record is inserted? I would like
to add a formula to the column "AggregatedSales.YearSum" that calculates the
value for this field depending on a SELECT-Statement with a SUM() - is that
or such like possible?
Any hints are highly appreciated!
Cheers, JanYou may want to store this separately via an indexed view.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Jan Rsner" <jan.roesner@.web.de> wrote in message
news:etFXka45FHA.4012@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> I have a table "AggregatedSales" which has a column called
> "AggregatedSales.YearSum".
> This column should contain a sum which can be derived directly from
> another table called "Sales" by summing up all values of "Sales.Price"
> WHERE "Sales.Year" = 2005.
> Is it possible to have MS SQL Server automatically calculate the value for
> "AggregatedSales.YearSum" when a new data record is inserted? I would like
> to add a formula to the column "AggregatedSales.YearSum" that calculates
> the value for this field depending on a SELECT-Statement with a SUM() - is
> that or such like possible?
> Any hints are highly appreciated!
> Cheers, Jan
>

automatical calculation of a field value

Hi All,
I have a table "AggregatedSales" which has a column called
"AggregatedSales.YearSum".
This column should contain a sum which can be derived directly from another
table called "Sales" by summing up all values of "Sales.Price" WHERE
"Sales.Year" = 2005.
Is it possible to have MS SQL Server automatically calculate the value for
"AggregatedSales.YearSum" when a new data record is inserted? I would like
to add a formula to the column "AggregatedSales.YearSum" that calculates the
value for this field depending on a SELECT-Statement with a SUM() - is that
or such like possible?
Any hints are highly appreciated!
Cheers, Jan
You may want to store this separately via an indexed view.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Jan Rsner" <jan.roesner@.web.de> wrote in message
news:etFXka45FHA.4012@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> I have a table "AggregatedSales" which has a column called
> "AggregatedSales.YearSum".
> This column should contain a sum which can be derived directly from
> another table called "Sales" by summing up all values of "Sales.Price"
> WHERE "Sales.Year" = 2005.
> Is it possible to have MS SQL Server automatically calculate the value for
> "AggregatedSales.YearSum" when a new data record is inserted? I would like
> to add a formula to the column "AggregatedSales.YearSum" that calculates
> the value for this field depending on a SELECT-Statement with a SUM() - is
> that or such like possible?
> Any hints are highly appreciated!
> Cheers, Jan
>
sql

automatical calculation of a field value

Hi All,
I have a table "AggregatedSales" which has a column called
"AggregatedSales.YearSum".
This column should contain a sum which can be derived directly from another
table called "Sales" by summing up all values of "Sales.Price" WHERE
"Sales.Year" = 2005.
Is it possible to have MS SQL Server automatically calculate the value for
"AggregatedSales.YearSum" when a new data record is inserted? I would like
to add a formula to the column "AggregatedSales.YearSum" that calculates the
value for this field depending on a SELECT-Statement with a SUM() - is that
or such like possible?
Any hints are highly appreciated!
Cheers, JanYou may want to store this separately via an indexed view.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Jan Rösner" <jan.roesner@.web.de> wrote in message
news:etFXka45FHA.4012@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> I have a table "AggregatedSales" which has a column called
> "AggregatedSales.YearSum".
> This column should contain a sum which can be derived directly from
> another table called "Sales" by summing up all values of "Sales.Price"
> WHERE "Sales.Year" = 2005.
> Is it possible to have MS SQL Server automatically calculate the value for
> "AggregatedSales.YearSum" when a new data record is inserted? I would like
> to add a formula to the column "AggregatedSales.YearSum" that calculates
> the value for this field depending on a SELECT-Statement with a SUM() - is
> that or such like possible?
> Any hints are highly appreciated!
> Cheers, Jan
>