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 auto shrink question

Hi, I am trying to automate shrinking the transaction log file on SQL server.
Every so often we get errors with our application using SQL server, in which I resolve by running the backup log and shrink log commands. However, recently I got the error: Could not allocate space for object 'table_name' in database 'database_name' because the 'Primary' file group is full. To resolve this I had to create another transaction log and then run the backup log and shrink log commands.

I know need to automate the process of shrinking the log file. I have checked and the Auto Shrink checkbox is ticked but these errors still occur.
How can I delete the additional log file I created and automate this task of shrinking the log file within SQL server? Any help would be appreciated...ThanksThe error mentioned does not refer to transaction log, but rather to data device of your database. Usually it's caused by either BULK INSERT/BCP...IN or an INSERT/UPDATE where the amount of resulting data exceeds the growth capacity of the database. These operations also affect the transaction log but the error would be different if that was the case. There are multiple sources on the net with similar approach. You can check here (http://www.codeproject.com/database/ShrinkingSQLServerTransLo.asp) for a fancy SQL-DMO version of it. For a known technique to handle shrinking of transaction log files using T-SQL go to this (http://dbforums.com/t515230.html) post.

Log

Please correct my concept, as I am thinking when log backup it auto shrink
the file to its mininum point like when I take the log backup and see the
size it's still on the same size but yes when I shrink the log then it's
goes into minimum size so now the
point is like I do take only the log backup every day after every 15 mins
but the log is not shrinking... can any one update me like what I have to do
for this ?
You should turn off autoshrink. Let the log file (and data files) stay at
the size it needs to do it's work plus some free space. Shrinking is
generally a bad thing not a good one.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Andrew J. Kelly SQL MVP
"Joh" <joh@.mailcity.com> wrote in message
news:er5TNnXVFHA.3152@.TK2MSFTNGP12.phx.gbl...
> Please correct my concept, as I am thinking when log backup it auto shrink
> the file to its mininum point like when I take the log backup and see the
> size it's still on the same size but yes when I shrink the log then it's
> goes into minimum size so now the
> point is like I do take only the log backup every day after every 15 mins
> but the log is not shrinking... can any one update me like what I have to
> do
> for this ?
>
|||I have a backup strategy to take the full backup daily and log every after
15 mins. that's why I am asking like if log backup then the size should also
decrease.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eqfxpIYVFHA.3424@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> You should turn off autoshrink. Let the log file (and data files) stay at
> the size it needs to do it's work plus some free space. Shrinking is
> generally a bad thing not a good one.
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> --
> Andrew J. Kelly SQL MVP
>
> "Joh" <joh@.mailcity.com> wrote in message
> news:er5TNnXVFHA.3152@.TK2MSFTNGP12.phx.gbl...
shrink[vbcol=seagreen]
the[vbcol=seagreen]
mins[vbcol=seagreen]
to
>
|||Andrew, should I disable to autoshrink so when the log will shrink? never ?
"Joh" <joh@.mailcity.com> wrote in message
news:erYxm0YVFHA.2700@.TK2MSFTNGP12.phx.gbl...
> I have a backup strategy to take the full backup daily and log every after
> 15 mins. that's why I am asking like if log backup then the size should
also[vbcol=seagreen]
> decrease.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eqfxpIYVFHA.3424@.TK2MSFTNGP09.phx.gbl...
at[vbcol=seagreen]
> shrink
> the
it's
> mins
> to
>
|||If you turn it off it will never shrink. That is the desired effect. As
long as you issue regular log backups it should not continue to grow past a
size that it needs to operate on a regular basis. That is as long as you
don't have any very long running open transactions that will prevent the log
from being truncated during the backup.
Andrew J. Kelly SQL MVP
"Joh" <joh@.mailcity.com> wrote in message
news:ujF9p6YVFHA.2540@.tk2msftngp13.phx.gbl...
> Andrew, should I disable to autoshrink so when the log will shrink? never
> ?
> "Joh" <joh@.mailcity.com> wrote in message
> news:erYxm0YVFHA.2700@.TK2MSFTNGP12.phx.gbl...
> also
> at
> it's
>
|||If you had a long running open transaction it may not be able to shrink
until the transaction is committed.
Andrew J. Kelly SQL MVP
"Joh" <joh@.mailcity.com> wrote in message
news:erYxm0YVFHA.2700@.TK2MSFTNGP12.phx.gbl...
>I have a backup strategy to take the full backup daily and log every after
> 15 mins. that's why I am asking like if log backup then the size should
> also
> decrease.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eqfxpIYVFHA.3424@.TK2MSFTNGP09.phx.gbl...
> shrink
> the
> mins
> to
>
|||Joh wrote:
> Andrew, should I disable to autoshrink so when the log will shrink?
> never ?
>
When you backup the log, it is truncated. The physical size does not
change, but the backed-up data in the log is removed. You should already
have the size set to avoid an auto-grow operations during normal
operation. There's no reason to shrink the physical log files unless
you're dealing with a runaway transaction that caused the log to grow to
an enormous size.
David Gugick
Imceda Software
www.imceda.com
|||Hi,
To add on to Andrew, just execute DBCC OPENTRAN command to check if there
is any open active transactions. If there is not then try to
shrink the LDF file manually and see if you could shrink. See DBCC
SHRINKFILE in books online. Perform a transaction log backup before the
shrink file command.
Thanks
Hari
SQL Server MVP
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23%232divZVFHA.3152@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> If you had a long running open transaction it may not be able to shrink
> until the transaction is committed.
> --
> Andrew J. Kelly SQL MVP
>
> "Joh" <joh@.mailcity.com> wrote in message
> news:erYxm0YVFHA.2700@.TK2MSFTNGP12.phx.gbl...
after[vbcol=seagreen]
have
>
|||Thanks Hari, like in a day there are 24 Logfiles exists in one folder so in
the job I can mention after the log backup to shrink the log file but the
problem is there is there any query like
If Exists(
DBCC OpenTran
)
Begin
DBCC ShrinkFile(logFileName,EMPTYFILE)
End
what you prefer should I use EMPTYFILE or TRUNCATEONLY or NOTRUNCATE
Thanks and waiting for your reply
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:#EESi7cVFHA.2328@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Hi,
> To add on to Andrew, just execute DBCC OPENTRAN command to check if there
> is any open active transactions. If there is not then try to
> shrink the LDF file manually and see if you could shrink. See DBCC
> SHRINKFILE in books online. Perform a transaction log backup before the
> shrink file command.
> Thanks
> Hari
> SQL Server MVP
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23%232divZVFHA.3152@.TK2MSFTNGP12.phx.gbl...
> after
should[vbcol=seagreen]
stay[vbcol=seagreen]
see[vbcol=seagreen]
15
> have
>