Log File Filling Up

Hi All,
Recently we have experienced a problem with our transaction log file filling
up. The application is a moderatley busy e-commerce site. The database is
set to simple recovery mode. The transaction log space is 100mb. This is
filling up on a daily basis.
I have two questions:
- What sort of things should I be looking for to solve this? (My hosting
company have suggested transactions are not completing) ?
- Is there a command (Like sp_helpdb) that returns the trans log size?
Many Thanks,
Simon.
-
* Please reply to group for the benefit of all
* Found the answer to your own question? Post it!
* Get a useful reply to one of your posts?...post an answer to another one
* Search first, post later : http://www.google.co.uk/groups
* Want my email address? Ask me in a post...Cos2MuchSpamMakesUFat!Your hosting company may be right. Run DBCC OPENTRAN on your DB. If the
same SPID comes up with the same date/time, then you have an open
transaction. Only once that tran is committed or rolled back will you be
able to truncate the log.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Simon Harris" <too-much-spam@.makes-you-fat.com> wrote in message
news:gg_fg.1444$lQ.247@.newsfe3-gui.ntli.net...
Hi All,
Recently we have experienced a problem with our transaction log file filling
up. The application is a moderatley busy e-commerce site. The database is
set to simple recovery mode. The transaction log space is 100mb. This is
filling up on a daily basis.
I have two questions:
- What sort of things should I be looking for to solve this? (My hosting
company have suggested transactions are not completing) ?
- Is there a command (Like sp_helpdb) that returns the trans log size?
Many Thanks,
Simon.
-
* Please reply to group for the benefit of all
* Found the answer to your own question? Post it!
* Get a useful reply to one of your posts?...post an answer to another one
* Search first, post later : http://www.google.co.uk/groups
* Want my email address? Ask me in a post...Cos2MuchSpamMakesUFat!|||Xref: TK2MSFTNGP01.phx.gbl microsoft.public.sqlserver.server:436392
Thank you for your reply Tom.
When you say "If the same SPID comes up with the same date/time" what
SPID/Date Time should I be comparing to?
Am I correct in thinking that the transaction log is automatically truncated
following completion of a successful transaction? (When in Simple recovery
mode)
Can you define an 'open transaction'? What circumstances may cause this?
Do you have any hints/tips as to how I may diagnose what code/queries are
causing this?
Sorry for all the questions, thanks again.
Simon.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OJH2iqmhGHA.4044@.TK2MSFTNGP03.phx.gbl...
> Your hosting company may be right. Run DBCC OPENTRAN on your DB. If the
> same SPID comes up with the same date/time, then you have an open
> transaction. Only once that tran is committed or rolled back will you be
> able to truncate the log.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Simon Harris" <too-much-spam@.makes-you-fat.com> wrote in message
> news:gg_fg.1444$lQ.247@.newsfe3-gui.ntli.net...
> Hi All,
> Recently we have experienced a problem with our transaction log file
> filling
> up. The application is a moderatley busy e-commerce site. The database is
> set to simple recovery mode. The transaction log space is 100mb. This is
> filling up on a daily basis.
> I have two questions:
> - What sort of things should I be looking for to solve this? (My hosting
> company have suggested transactions are not completing) ?
> - Is there a command (Like sp_helpdb) that returns the trans log size?
> Many Thanks,
> Simon.
> --
> -
> * Please reply to group for the benefit of all
> * Found the answer to your own question? Post it!
> * Get a useful reply to one of your posts?...post an answer to another one
> * Search first, post later : http://www.google.co.uk/groups
> * Want my email address? Ask me in a post...Cos2MuchSpamMakesUFat!
>|||Thank you for your reply Tom.
When you say "If the same SPID comes up with the same date/time" what
SPID/Date Time should I be comparing to?
Am I correct in thinking that the transaction log is automatically truncated
following completion of a successful transaction? (When in Simple recovery
mode)
Can you define an 'open transaction'? What circumstances may cause this?
Do you have any hints/tips as to how I may diagnose what code/queries are
causing this?
Sorry for all the questions, thanks again.
Simon.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OJH2iqmhGHA.4044@.TK2MSFTNGP03.phx.gbl...
> Your hosting company may be right. Run DBCC OPENTRAN on your DB. If the
> same SPID comes up with the same date/time, then you have an open
> transaction. Only once that tran is committed or rolled back will you be
> able to truncate the log.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Simon Harris" <too-much-spam@.makes-you-fat.com> wrote in message
> news:gg_fg.1444$lQ.247@.newsfe3-gui.ntli.net...
> Hi All,
> Recently we have experienced a problem with our transaction log file
> filling
> up. The application is a moderatley busy e-commerce site. The database is
> set to simple recovery mode. The transaction log space is 100mb. This is
> filling up on a daily basis.
> I have two questions:
> - What sort of things should I be looking for to solve this? (My hosting
> company have suggested transactions are not completing) ?
> - Is there a command (Like sp_helpdb) that returns the trans log size?
> Many Thanks,
> Simon.
> --
> -
> * Please reply to group for the benefit of all
> * Found the answer to your own question? Post it!
> * Get a useful reply to one of your posts?...post an answer to another one
> * Search first, post later : http://www.google.co.uk/groups
> * Want my email address? Ask me in a post...Cos2MuchSpamMakesUFat!
>
---
I am using the free version of SPAMfighter for private users.
It has removed 5334 spam emails to date.
Paying users do not have this message in their emails.
Get the free SPAMfighter here: http://www.spamfighter.com/len|||Check out "DBCC OPENTRAN" in the BOL. The output it gives you the date/time
of the longest-running transaction for a given DB. The SPID (system process
ID). If the date/time never changes as you repeatedly run DBCC OPENTRAN,
then you have a long-running transaction. You could then run DBCC
INPUTBUFFER on that SPID to see what the last command was.
An explicit transaction begins with a BEGIN TRAN. None of the work that you
do from that point forward is made permanent until you issue a COMMIT TRAN
on that same connection. Once that occurs, the log can be truncated up to
that point. An implicit transaction is an INSERT, UPDATE or DELETE. Check
out the BOL for Transactions for more details.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Simon Harris" <too-much-spam@.makes-you-fat.com> wrote in message
news:r9%fg.1734$lQ.1657@.newsfe3-gui.ntli.net...
Thank you for your reply Tom.
When you say "If the same SPID comes up with the same date/time" what
SPID/Date Time should I be comparing to?
Am I correct in thinking that the transaction log is automatically truncated
following completion of a successful transaction? (When in Simple recovery
mode)
Can you define an 'open transaction'? What circumstances may cause this?
Do you have any hints/tips as to how I may diagnose what code/queries are
causing this?
Sorry for all the questions, thanks again.
Simon.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OJH2iqmhGHA.4044@.TK2MSFTNGP03.phx.gbl...
> Your hosting company may be right. Run DBCC OPENTRAN on your DB. If the
> same SPID comes up with the same date/time, then you have an open
> transaction. Only once that tran is committed or rolled back will you be
> able to truncate the log.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Simon Harris" <too-much-spam@.makes-you-fat.com> wrote in message
> news:gg_fg.1444$lQ.247@.newsfe3-gui.ntli.net...
> Hi All,
> Recently we have experienced a problem with our transaction log file
> filling
> up. The application is a moderatley busy e-commerce site. The database is
> set to simple recovery mode. The transaction log space is 100mb. This is
> filling up on a daily basis.
> I have two questions:
> - What sort of things should I be looking for to solve this? (My hosting
> company have suggested transactions are not completing) ?
> - Is there a command (Like sp_helpdb) that returns the trans log size?
> Many Thanks,
> Simon.
> --
> -
> * Please reply to group for the benefit of all
> * Found the answer to your own question? Post it!
> * Get a useful reply to one of your posts?...post an answer to another one
> * Search first, post later : http://www.google.co.uk/groups
> * Want my email address? Ask me in a post...Cos2MuchSpamMakesUFat!
>|||Seems my hosting company do not give me the relevent permissions to run DBCC
OPENTRAN.
---
I am using the free version of SPAMfighter for private users.
It has removed 5334 spam emails to date.
Paying users do not have this message in their emails.
Get the free SPAMfighter here: http://www.spamfighter.com/len|||Perhaps you can have their DBA run it.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Simon Harris" <too-much-spam@.makes-you-fat.com> wrote in message
news:yL%fg.1320$W93.238@.newsfe6-win.ntli.net...
Seems my hosting company do not give me the relevent permissions to run DBCC
OPENTRAN.
---
I am using the free version of SPAMfighter for private users.
It has removed 5334 spam emails to date.
Paying users do not have this message in their emails.
Get the free SPAMfighter here: http://www.spamfighter.com/len