Link to home
Start Free TrialLog in
Avatar of TRocex
TRocex

asked on

sql error log file is very big in SQL 2008

SQL error log file is rapidly growing and it has reached in size 43 GB so how can I delete it safely? I guess a transaction log file of a database has caused so when I look at the sql log file thera are lots of messages " Transaction log for the database XXX is full" so I guess this is the reason why error log file was huge..

the question is that how can I delete old error log file safely or another way?please attached file
other question. the transaction log of this database is alwasy rapidly growing and I am shrinking this log file everyday so How can I bring under control this transaction log of this database?
SQLErrorLog.JPG
Avatar of x-men
x-men
Flag of Portugal image

delete (or move) the old files at :...\MSSQL\Log\ERRORLOG##
do you ever recycle the error log file, at least regularly (i have a job scheduled monthly to do this):
Exec Sp_Cycle_Errorlog


in regards to the "errors" about transaction log file file: you need to implement & maintain regular transaction logbackups for all your databases that are in full recovery mode.
failing to do so will make the files will just grow (filling your disk), and if disk is full OR the log files are not set to auto-grow, filling the error log with the message you get.
hi

refer to this in order to monitor and change time retention of log.

http://archive.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=ErrorLogMon&referringTitle=AutomatedDBA

Aaron.
Avatar of TRocex
TRocex

ASKER

thanks I have deleted the SQL Error log file that is very large in size as I said the reason that sql error log file is very big in size is to be the transaction log file of the database XXX is always growing so the backups and the related this transaction log backups are being taken regulary but although this, I dont know why the transaction log file is growing in this way
check the output of this:
DBCC OPENTRAN

do you have replication in place for this db?
Avatar of TRocex

ASKER

I have run this command and it says no active open transaction..when i check the transaction log file every morning, it reaches 13GB in size..when I shrink this log file but other day is the same situation..there is no replication or other thing like mirrror
>.when i check the transaction log file every morning, it reaches 13GB in size..when I shrink this log file but other day is the same situation..

then, first, stop shrinking the transaction log file every morning, you will just waste IO.

instead, try to increase the frequency of the transaction log backups. what is the current t-log backups ...
Avatar of TRocex

ASKER

the latest backup to be taken is at 8:07 am and the size of the current t-log backup is 8 gb and I think in the morning it will be 13 gb in size again then the result of this will be the errors of disk full
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial