Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

transaction log eats up all the space and growing until sql is stopped Expand / Collapse
Author
Message
Posted Friday, November 22, 2013 12:13 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 27, 2014 9:04 PM
Points: 46, Visits: 113
Hi ,

I've got a peculiar problem with my VCENTER's SQL 2008 database.

My Database is running on SIMPLE recovery model. The problem is my transaction log keeps on increasing all the time until we stop the database and truncate/shrink it. That way i can run it for another one more day.

I cannot shrink/truncate it when it is running because it shows ACTIVE TRANSACTION.

I cannot restart it everyday just to stop the transaction log from growing. What do i do?

Please advice.

Thanks,
Jai
Post #1516660
Posted Friday, November 22, 2013 12:41 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 42,812, Visits: 35,931
Something's leaving a transaction open. Identify who that is (from DBCC OpenTran and sys.dm_exec_sessions) and either commit the transaction or kill the session and let it rollback. Then do some investigations as to why transactions are getting left open every day.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1516663
Posted Friday, November 22, 2013 12:59 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 27, 2014 9:04 PM
Points: 46, Visits: 113
dbcc opentrans tells me No active open transactions.

whereas the following query returns two rows

login name session_count
Adminstrator 3
sa 16

SELECT login_name ,COUNT(session_id) AS session_count
FROM sys.dm_exec_sessions
GROUP BY login_name;


oops and btw...sorry this is SQL 2005 not 2008
Post #1516665
Posted Friday, November 22, 2013 1:09 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 42,812, Visits: 35,931
Opentran is database-specific, run it in the database that has the growing log. If your log reuse wait is active transaction, then you have an open transaction.

The number of sessions per login is pretty irrelevant, it's not who's using the system you're interested in, it's who has an open transaction they have not committed. Check DBCC OpenTran, find the spid that it returns, check that spid in sys.dm_exec_sessions and sys.dm_exec_connections to identify who is running it and from where, then decide whether you can get the person to commit the tran (if it's ad-hoc queries) or roll it back after checking what it's doing.

Once you've identified which session has the open transaction, you need to figure out why there is an active transaction every day. Is someone running this manually? Is there a bug in the app? You're going to need to do some investigations.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1516666
Posted Friday, November 22, 2013 1:12 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 27, 2014 9:04 PM
Points: 46, Visits: 113
I notice one idle session that have open tranasaction.

The program name is Service Runner and i suspect its probably a VMWare system process with status as sleeping and lock_timeout = -1

Not sure what that means, though. Can i just go ahead and kill it. How do i do that?

Post #1516667
Posted Friday, November 22, 2013 1:14 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 42,812, Visits: 35,931
I suggest you go and make sure you know exactly what it is before you kill it, what it was running and from where. You shouldn't be guessing as to what's happening, check and be sure.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1516669
Posted Friday, November 22, 2013 1:35 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 27, 2014 9:04 PM
Points: 46, Visits: 113
yup, a lock has been placed on the database by a process with the following atrributes:


owner id = 0
owner type = shared_transaction_workspace
objectid=0
request mode = S
Post #1516671
Posted Friday, November 22, 2013 1:46 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 42,812, Visits: 35,931
That's normal, every single session will have a shared lock on the database it uses and it will hold that lock until the session ends. This is to ensure that a database cannot be dropped while a session is connected to it.

That lock is not the cause of your problems. Your problem is an open transaction that is preventing the log from being reused. Don't look at the locks tables, they're not going to tell you what's happening. Use DBCC OPENTRAN to identify which session(s) have an open (active) transaction and investigate those sessions, see where they are coming from, which machine, which user, which application



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1516673
Posted Friday, November 22, 2013 1:53 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 27, 2014 9:04 PM
Points: 46, Visits: 113
unfortunately all i get to see from DBCC OPENTRAN is:

No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Post #1516674
Posted Friday, November 22, 2013 1:55 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 42,812, Visits: 35,931
What database context are you running it in?
What database shows Active Transaction as the log_reuse_wait in sys.databases?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1516675
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse