varchar(2000) column.
The database has a "simple" recovery model.
We insert data in the database every millisecond.
Every day, we delete data from the table that is older than 1 w
old.We then changed the column to be varchar(100), because the data is more
compact now.
Since we do this, I notice the log file grew from less than 100 meg to 2
gig. The data file is about 500 meg.
Why is the log file grew so much once I changed 1 of the column to be
varchar(100) ? Thank you very much.
Here is the stored procedure to delete data every day.
CREATE PROCEDURE DeleteDataInBatch
AS
declare @.LastCount smallint
set ROWCOUNT 5000
set @.LastCount = 1
while (@.LastCount > 0)
begin
begin tran
delete from ...
set @.LastCount = @.@.ROWCOUNT
commit tran
end
set ROWCOUNT 0
GO
Here is the stored procedure to insert data.
CREATE PROCEDURE InsertData
@.sContract varchar(8),
@.sData varchar(2000)
AS
insert into ...
GODid you use Enterprise manager to do the change? If so it usually creates a
new table behind the scenes, copies all the data over to it and then drops
the original table. All of this is fully logged and will require lots of
space.
Andrew J. Kelly SQL MVP
"fniles" <fniles@.pfmail.com> wrote in message
news:uhAArB1RFHA.3944@.TK2MSFTNGP10.phx.gbl...
> We have a table in SQL Server with 3 columns, and 1 of the columns is a
> varchar(2000) column.
> The database has a "simple" recovery model.
> We insert data in the database every millisecond.
> Every day, we delete data from the table that is older than 1 w
old.> We then changed the column to be varchar(100), because the data is more
> compact now.
> Since we do this, I notice the log file grew from less than 100 meg to 2
> gig. The data file is about 500 meg.
> Why is the log file grew so much once I changed 1 of the column to be
> varchar(100) ? Thank you very much.
> Here is the stored procedure to delete data every day.
> CREATE PROCEDURE DeleteDataInBatch
> AS
> declare @.LastCount smallint
> set ROWCOUNT 5000
> set @.LastCount = 1
> while (@.LastCount > 0)
> begin
> begin tran
> delete from ...
> set @.LastCount = @.@.ROWCOUNT
> commit tran
> end
> set ROWCOUNT 0
> GO
> Here is the stored procedure to insert data.
> CREATE PROCEDURE InsertData
> @.sContract varchar(8),
> @.sData varchar(2000)
> AS
> insert into ...
> GO
>|||> Did you use Enterprise manager to do the change?
Yes, I changed the column from varchar(2000) to varchar(100) in EM.
So, you think the log file grew big during the process of changing the
column from varchar(2000) to varchar(100) in EM ?
I though I checked the log file size after I did that, but I might be wrong.
With a log file 2 gig in size, will it slow down any querying, inserting or
deleting in the database ?
Do I need and can I shrink this log file size, or shall I leave it alone ?
Thank you very much.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OPBHJb1RFHA.164@.TK2MSFTNGP12.phx.gbl...
> Did you use Enterprise manager to do the change? If so it usually creates
> a new table behind the scenes, copies all the data over to it and then
> drops the original table. All of this is fully logged and will require
> lots of space.
> --
> Andrew J. Kelly SQL MVP
>
> "fniles" wrote in message news:uhAArB1RFHA.3944@.TK2MSFTNGP10.phx.gbl...
>|||I usually set my database log files to about 20 to 30 % of the total databas
e
space.
Also depends on how transaction intensive your database is.
I always restrict my log file size to a maximum value.
I do not thing you woul gain anything by having that big a log file size. I
woud have shrunk it after backing up the database and doing a dump of the
transaction log.
Nishant
"fniles" wrote:
> Yes, I changed the column from varchar(2000) to varchar(100) in EM.
> So, you think the log file grew big during the process of changing the
> column from varchar(2000) to varchar(100) in EM ?
> I though I checked the log file size after I did that, but I might be wron
g.
> With a log file 2 gig in size, will it slow down any querying, inserting o
r
> deleting in the database ?
> Do I need and can I shrink this log file size, or shall I leave it alone ?
> Thank you very much.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OPBHJb1RFHA.164@.TK2MSFTNGP12.phx.gbl...
>
>|||Thank you for your reply.
I do not want the log file to be that big.
We insert data to the database every miliseconds, currently the database is
about 500 meg in size and has about 15 million records in this table.
Is it too late to restrict the log file size now that it already grew to 2
gig ?
If I restrict the log file size, will it slow down inserting and deleting ?
How do I dump the transaction log ?
If I shrink the transaction log, will it slow down inserting and deleting
later ?
Thank you very much.
"NB" <NB@.discussions.microsoft.com> wrote in message
news:705DD5E1-3F87-4D71-86DD-AAFA78DE424A@.microsoft.com...
>I usually set my database log files to about 20 to 30 % of the total
>database
> space.
> Also depends on how transaction intensive your database is.
> I always restrict my log file size to a maximum value.
> I do not thing you woul gain anything by having that big a log file size.
> I
> woud have shrunk it after backing up the database and doing a dump of the
> transaction log.
>
> Nishant
> "fniles" wrote:
>|||There's no penalty to have a "too big" file. Having a small file does come w
ith a penalty, however:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"NB" <NB@.discussions.microsoft.com> wrote in message
news:705DD5E1-3F87-4D71-86DD-AAFA78DE424A@.microsoft.com...
>I usually set my database log files to about 20 to 30 % of the total databa
se
> space.
> Also depends on how transaction intensive your database is.
> I always restrict my log file size to a maximum value.
> I do not thing you woul gain anything by having that big a log file size.
I
> woud have shrunk it after backing up the database and doing a dump of the
> transaction log.
>
> Nishant
> "fniles" wrote:
>
