Log File Size (SQL 2000)

Dear All,
How to maintain the transaction log file size ?
When I should use Restrict filegrowth or Unrestricted filegrowth ?
How many MB shoud I setup for the log file size ?
Thanks.
Vensia
Hi
http://www.sql-server-performance.co...e_settings.asp
"Vensia" <vensia2000_nospam@.yahoo.com> wrote in message
news:eUNfuu1FGHA.752@.TK2MSFTNGP12.phx.gbl...
> Dear All,
> How to maintain the transaction log file size ?
> When I should use Restrict filegrowth or Unrestricted filegrowth ?
> How many MB shoud I setup for the log file size ?
> Thanks.
> Vensia
>

Log File Size (SQL 2000)

Dear All,
How to maintain the transaction log file size ?
When I should use Restrict filegrowth or Unrestricted filegrowth ?
How many MB shoud I setup for the log file size ?
Thanks.
VensiaHi
http://www.sql-server-performance.com/database_settings.asp
"Vensia" <vensia2000_nospam@.yahoo.com> wrote in message
news:eUNfuu1FGHA.752@.TK2MSFTNGP12.phx.gbl...
> Dear All,
> How to maintain the transaction log file size ?
> When I should use Restrict filegrowth or Unrestricted filegrowth ?
> How many MB shoud I setup for the log file size ?
> Thanks.
> Vensia
>

Log File Size (SQL 2000)

Dear All,
How to maintain the transaction log file size ?
When I should use Restrict filegrowth or Unrestricted filegrowth ?
How many MB shoud I setup for the log file size ?
Thanks.
VensiaHi
http://www.sql-server-performance.c...se_settings.asp
"Vensia" <vensia2000_nospam@.yahoo.com> wrote in message
news:eUNfuu1FGHA.752@.TK2MSFTNGP12.phx.gbl...
> Dear All,
> How to maintain the transaction log file size ?
> When I should use Restrict filegrowth or Unrestricted filegrowth ?
> How many MB shoud I setup for the log file size ?
> Thanks.
> Vensia
>

Log file is full

I am using Simple Recover model for my database

Initial size of log file is 1 MB and auto growth is set to by 10%, unrestricted growth.

auto shrink property is set TRUE

i m using SSIS package to copy some data from one table to another ( in same database)

my package fails saying "The log file for database 'WebInterfaceData' is full. Back up the transaction log for the database to free up some log space."

i dont see ne reason y it is fail as i have set up autogrowth to incease by 10%

FYI. there nothing to deal with SSIS as when I try to run simple SQL statement in Management studio it also fails complaining about same error after package execution

can ne one help me in this regard?

thanx

Anas

How large is the transaction log? Run the query below to find out:

SELECT name AS 'File Name' , physical_name AS 'Physical Name', size/128 AS 'Total Size in MB',
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB', *
FROM sys.database_files;

It sounds like you might have some long running transaction that is not letting the log get checkpointed, so that it can be truncated.

Does the problem go away (for a while at least) if you stop and start SQL Server?

|||

have u configured maxsize ?

select *from sysfiles

sp_helpfile

post the result

Madhu

|||

Obviously check the space on the disk that the logfile is on (I would assume that it is not full).

The other thing to watch out for is a disk quota of some description on the service account being used for SQL Server. This looks to SQL Server the same as a disk full error.

You might like to look in the logs for a System Error 112 (ERROR_DISK_FULL) which will be a symptom of either of these problems.

|||

Madhu K Nair wrote:

have u configured maxsize ?

select *from sysfiles

sp_helpfile

post the result

Madhu

the result of query is that and max size is set to -1 (means no max size)

1 1 47312 -1 10 1081346 0 WebInterfaceData_Data D:\SQL\MSSQL\data\WebInterfaceData_Data.MDF

2 0 2560 -1 10 1081410 0 WebInterfaceData_Log D:\SQL\MSSQL\data\WebInterfaceData_Log.LDF

Plus there is no issue like disk space

thanx,

Anas

Log File

If your database is set to unrestricted log file growth can anyone tell me why the log file becomes full? I get this error. "Back up the transaction log for the database to free up some log space. [SQLSTATE 42000] (Error 9002)" After about three or four days now on a consistent bases. I don't believe it is because I am running out of disk space. ???

TomWhat is your current filesystem ... FAT or NTFS ?|||It has to be the case, you can't get this error for any other reason other than when running out of disk...It is NTFS, isn't it?