July 19, 2012 at 7:35 pm
BTW Gail, I did what you suggested. And still ShrinkFile not working....Before running the transactions and after running the transactions its same
CurrentSize MinimumSize UsedPages EstimatedPages
2564960 80 2551968 2551968
July 19, 2012 at 7:56 pm
Another issue noted. Even though I increased the size of the log, when a user tries to log in to the system through an app that is connected to the database, % log space grows and it fills up. When a user tries to sign in, it writes an entries to a table (which has about 3688321) rows and good number of indexes.
Any suggestions?
July 20, 2012 at 1:08 am
No, the initial size has no effect, that's literally just the size the log started.
Log reuse wait desc when the user gets that error?
How big is the log file?
Is autogrow enabled on it
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 20, 2012 at 4:51 am
Yes. autogrow is enabled. I have increased the size o 5000 MB as DB size is 20 GB.
July 20, 2012 at 12:05 pm
Gali, when user gets the error, log_reuse_wiat desc is changed to ACTIVE_TRANSACTION.
July 20, 2012 at 1:25 pm
Sounds like the log's just not large enough for the activity on the DB. Do some monitoring, see how much log space the various transactions use
sys.dm_tran_database_transactions
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 20, 2012 at 1:45 pm
Attached is the result of DMV..See what do u suggest?
July 20, 2012 at 1:55 pm
That you do some investigation into whatever that was to find out why it is using a few GB of transaction log space.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 20, 2012 at 2:49 pm
Thanks Gail from your help. Given the transaciton id, what other DMV can I use what query/process is it executing?
July 20, 2012 at 4:20 pm
Check Books Online, it gives you the related DMVs and all details on what the column values show and mean.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 20, 2012 at 7:12 pm
Does OS server level configuration (for e.g Lock pages in memory or any other configuration settings on the sql server will have issue on the transaction log becoming full immediately upon the start of the application? If yes, can anybody advise which settings to check for ?
July 21, 2012 at 12:38 am
reserved data index_size unused
20416424 KB 10244200 KB 9928824 KB 243400 KB
This is what i found when I ran sp_spaceused. Seeems like index size is causing the log to grow full? If so, should I rebuild the indexes?
Do I increase the log file size before rebuilding the index? Currently log file size is set to 1GB.
Currently recovery model is simple.
July 21, 2012 at 4:28 am
SQL_Surfer (7/20/2012)
Does OS server level configuration (for e.g Lock pages in memory or any other configuration settings on the sql server will have issue on the transaction log becoming full immediately upon the start of the application?
No.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 21, 2012 at 4:32 am
SQL_Surfer (7/21/2012)
reserved data index_size unused20416424 KB 10244200 KB 9928824 KB 243400 KB
This is what i found when I ran sp_spaceused. Seeems like index size is causing the log to grow full?
The index size alone won't cause a log to fill.
Log records are written to the log when data is changed, So if something is writing several GB of log records to the log, it means it is changing several GB of data. Maybe it's updating an entire table, or rebuilding an index. Either way, it's not the index at fault, it's the operation that's changing the data
Start with the DMV I gave you and the 2 DMVs that it links to (as shown in Books Online). From there you can get the session_id which will let you track where the sessions writing all the log records are coming from (host, application, login name, etc) and what they are doing (sql statement text)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 21, 2012 at 10:11 am
did you already run DBCC LOGINFO?
it is a great option to peek in and find out as to what is actually happening inside the transaction log.
Viewing 15 posts - 16 through 30 (of 48 total)
You must be logged in to reply to this topic. Login to reply