Log File Filling up - Fast!

Hi All,
We have a problem with our SQL server log file filling up. This problem has
got progressivley worse over a period of weeks. Probably easiest if I bullet
point the situation:
- Application is an ASP based website
- Site perviously ran fine for at least 18 months
- No recent code changes
- Started off happening once a week, got worse and worse, now the log file
fills in one hour
- Hosting company suggests this is due to incomplete transactions
- We've recently seen error messages reporting that the SQL server is
unreachable
- Even after doubling the log file size, the error occurs just as often
- Site stats show that the site has *not* become much more busy lateley
- Log file fills even when we avoid using anything that does alot of
inserts/updates
Any ideas what could be causing this? Is there any way I can test the SQL
server connectivity? I'm thinking it may be a bad network between the IIS
server and the SQL box, what elese should I be looking at?
Any help/advice will be much appreciated!!
Many Thanks,
Simon.
PS: Apologies if this is a re-post, my first attempt at sending this didn't
work for some reason!Simon
What is recovery mode of the database? Is that SIMPLE?
"Simon Harris" <too-much-spam@.makes-you-fat.com> wrote in message
news:uxUvJCbnGHA.3436@.TK2MSFTNGP02.phx.gbl...
> Hi All,
> We have a problem with our SQL server log file filling up. This problem
> has
> got progressivley worse over a period of weeks. Probably easiest if I
> bullet
> point the situation:
> - Application is an ASP based website
> - Site perviously ran fine for at least 18 months
> - No recent code changes
> - Started off happening once a week, got worse and worse, now the log file
> fills in one hour
> - Hosting company suggests this is due to incomplete transactions
> - We've recently seen error messages reporting that the SQL server is
> unreachable
> - Even after doubling the log file size, the error occurs just as often
> - Site stats show that the site has *not* become much more busy lateley
> - Log file fills even when we avoid using anything that does alot of
> inserts/updates
> Any ideas what could be causing this? Is there any way I can test the SQL
> server connectivity? I'm thinking it may be a bad network between the IIS
> server and the SQL box, what elese should I be looking at?
> Any help/advice will be much appreciated!!
> Many Thanks,
> Simon.
> PS: Apologies if this is a re-post, my first attempt at sending this
> didn't work for some reason!
>|||I believe so, what significance would this have?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eGxzRIcnGHA.1808@.TK2MSFTNGP02.phx.gbl...
> Simon
> What is recovery mode of the database? Is that SIMPLE?
>
> "Simon Harris" <too-much-spam@.makes-you-fat.com> wrote in message
> news:uxUvJCbnGHA.3436@.TK2MSFTNGP02.phx.gbl...
>> Hi All,
>> We have a problem with our SQL server log file filling up. This problem
>> has
>> got progressivley worse over a period of weeks. Probably easiest if I
>> bullet
>> point the situation:
>> - Application is an ASP based website
>> - Site perviously ran fine for at least 18 months
>> - No recent code changes
>> - Started off happening once a week, got worse and worse, now the log
>> file
>> fills in one hour
>> - Hosting company suggests this is due to incomplete transactions
>> - We've recently seen error messages reporting that the SQL server is
>> unreachable
>> - Even after doubling the log file size, the error occurs just as often
>> - Site stats show that the site has *not* become much more busy lateley
>> - Log file fills even when we avoid using anything that does alot of
>> inserts/updates
>> Any ideas what could be causing this? Is there any way I can test the SQL
>> server connectivity? I'm thinking it may be a bad network between the IIS
>> server and the SQL box, what elese should I be looking at?
>> Any help/advice will be much appreciated!!
>> Many Thanks,
>> Simon.
>> PS: Apologies if this is a re-post, my first attempt at sending this
>> didn't work for some reason!
>|||Simon
If you have FULL recovery mode , so just BACKUP LOG File and then (If you
want to reduce physical size of the file) rin DBCC SHRINKFILE command
However, if you have SIMPLE recovery mode then check out if some
trunsactions are still opened. Don't allow the use to interaction inside
tranactions
More info you will found here
http://support.microsoft.com/kb/317375/
"Simon Harris" <too-much-spam@.makes-you-fat.com> wrote in message
news:e%23I9uKcnGHA.5096@.TK2MSFTNGP03.phx.gbl...
>I believe so, what significance would this have?
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eGxzRIcnGHA.1808@.TK2MSFTNGP02.phx.gbl...
>> Simon
>> What is recovery mode of the database? Is that SIMPLE?
>>
>> "Simon Harris" <too-much-spam@.makes-you-fat.com> wrote in message
>> news:uxUvJCbnGHA.3436@.TK2MSFTNGP02.phx.gbl...
>> Hi All,
>> We have a problem with our SQL server log file filling up. This problem
>> has
>> got progressivley worse over a period of weeks. Probably easiest if I
>> bullet
>> point the situation:
>> - Application is an ASP based website
>> - Site perviously ran fine for at least 18 months
>> - No recent code changes
>> - Started off happening once a week, got worse and worse, now the log
>> file
>> fills in one hour
>> - Hosting company suggests this is due to incomplete transactions
>> - We've recently seen error messages reporting that the SQL server is
>> unreachable
>> - Even after doubling the log file size, the error occurs just as often
>> - Site stats show that the site has *not* become much more busy lateley
>> - Log file fills even when we avoid using anything that does alot of
>> inserts/updates
>> Any ideas what could be causing this? Is there any way I can test the
>> SQL
>> server connectivity? I'm thinking it may be a bad network between the
>> IIS
>> server and the SQL box, what elese should I be looking at?
>> Any help/advice will be much appreciated!!
>> Many Thanks,
>> Simon.
>> PS: Apologies if this is a re-post, my first attempt at sending this
>> didn't work for some reason!
>>
>|||Simon Harris wrote:
> I believe so, what significance would this have?
>
>From this reply it's apparent that you don't understand or haven't
thought enough about your backup / recovery strategy. Do read the
backup and recovery topics in Books Online to understand the relevance
of transaction log backups. I'm not sure why you'd run a website using
simple recovery unless it was a read-only database.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--