Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


95 GB Transaction Log but Log Backups are much smaller


95 GB Transaction Log but Log Backups are much smaller

Author
Message
Karen Yorgy
Karen Yorgy
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 137
Transaction Log Details
The transaction log is around 94 GB (with 7 GB free) and is being backed up every 20 minutes. The backup sizes run the gamut between 6 MB - 300 MB (usually). I don't understand why the log size is so large. The recovery interval is set at 0, automatic configuration, so check point should be being issued regularly.

Here is typical backup script:

BACKUP LOG [PEP] TO DISK = N''\\sapdeploy001\dbBackupPEP$\PEP\TRN\PEP_backup_2014_04_08_150004_6878249.trn'' WITH NOFORMAT, NOINIT, NAME = N''PEP_backup_2014_04_08_150004_6858247'', SKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 10

What am I missing? Thanks in advance, Karen
John Mitchell-245523
John Mitchell-245523
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7388 Visits: 15101
Karen

What does this return?
SELECT log_reuse_wait_desc
FROM sys.databases
WHERE name = 'PEP'



John
Karen Yorgy
Karen Yorgy
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 137
John -

ACTIVE_TRANSACTION

Thanks,

Karen
John Mitchell-245523
John Mitchell-245523
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7388 Visits: 15101
Karen

Your log won't be truncated until that transaction is complete. Do you have a long-running job or some other process that's been going for a while?

John
Karen Yorgy
Karen Yorgy
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 137
This is our SAP ECC production database. No jobs are currently active but we have a very complex installation with links to multiple other systems.

I'm wondering whether increasing the transaction log interval to a larger time period would allow the long running job to complete.

Is there something I can run to see the longest running process?

Thank you for the help.
John Mitchell-245523
John Mitchell-245523
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7388 Visits: 15101
Karen

Don't change your log backup interval. The log backups won't stop your processes from completing, so just let them run as normal.

You can use sp_who2 or the Activity Monitor to see a list of connections to the server, what time they connected, whether they are being blocked, and so on. Use DBCC INPUTBUFFER to see what code an individual SPID is executing. For more detailed information, search the web for sp_whoisactive.

John
Karen Yorgy
Karen Yorgy
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 137
I tried a script from PinalDave - Look at this! Here are the top ten queries:

MaxElapsedTime   AvgElapsedTime   LogCreatedOn
3584972048   2652049   4/8/2014
2855344316   8238   10/18/2013
2853692221   8631   10/18/2013
2346033185   21   10/18/2013
2343770056   26870   10/18/2013
2046910076   149   10/18/2013
1785333115   363808   2/26/2014
1407043478   30259   10/18/2013
888047793   23902   10/18/2013
646335968   18086   3/15/2014


SELECT DISTINCT TOP 10
t.TEXT QueryName,
s.execution_count AS ExecutionCount,
s.max_elapsed_time AS MaxElapsedTime,
ISNULL(s.total_elapsed_time / 1000 / NULLIF(s.execution_count, 0), 0) AS AvgElapsedTime,
s.creation_time AS LogCreatedOn,
ISNULL(s.execution_count / 1000 / NULLIF(DATEDIFF(s, s.creation_time, GETDATE()), 0), 0) AS FrequencyPerSec
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
ORDER BY s.max_elapsed_time DESC, ExecutionCount DESC
GO
John Mitchell-245523
John Mitchell-245523
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7388 Visits: 15101
Karen

While that script is useful for identifying which are your most expensive and/or most used queries for tuning, it doesn't help with identifying what is running at the moment. You could write your own script using sys.dm_exec_requests and other DMVs, or just use sp_whoisactiveor sp_who2 or Activity Monitor, which does that for you.

John
Karen Yorgy
Karen Yorgy
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 137
Hope this won't look too garbled. sp_who2 shows a bunch of processes from 10/18. Checking the sql error log, I see that was after a server restart on the same day. The server has been restarted once since then (2/24), but I do not see any processes with that date.

Would the CHECKPOINT in this list be significant?

DBName   Command   CPUTime   DiskIO   LastBatch
NULL   RESOURCE MONITOR   639401   0   10/18/2014 0:18
NULL   XE DISPATCHER    2730   0   10/18/2014 0:18
NULL   XE TIMER    5865   0   10/18/2014 0:18
NULL   LOG WRITER    6666765   0   10/18/2014 0:18
NULL   LAZY WRITER    4931300   0   10/18/2014 0:18
master   SIGNAL HANDLER    0   0   10/18/2014 0:18
NULL   LOCK MONITOR    842499   0   10/18/2014 0:18
master   TASK MANAGER    0   0   10/18/2014 0:18
master   TRACE QUEUE TASK   101743   0   10/18/2014 0:18
master   BRKR TASK    363123   1   10/18/2014 0:18
master   CHECKPOINT    75585744   38039072   10/18/2014 0:18
master   TASK MANAGER    0   0   10/18/2014 0:18
master   BRKR EVENT HNDLR   0   74   10/18/2014 0:18
master   BRKR TASK    0   0   10/18/2014 0:18
master   BRKR TASK    0   0   10/18/2014 0:18
master   TASK MANAGER    0   33   10/18/2014 0:18
master   TASK MANAGER    0   584   10/18/2014 0:18
master   TASK MANAGER    0   7578   10/18/2014 0:18
master   TASK MANAGER    0   5978   10/18/2014 0:18
master   TASK MANAGER    0   295   10/18/2014 0:18
master   TASK MANAGER    0   345   10/18/2014 0:18
master   TASK MANAGER    0   159   10/18/2014 0:18
master   TASK MANAGER    0   664   10/18/2014 0:18
master   TASK MANAGER    0   3220   10/18/2014 0:18
John Mitchell-245523
John Mitchell-245523
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7388 Visits: 15101
Now i am confused! 18th October 2014? You need to disable DBCC TIMEWARP. Was that the full output of sp_who2 that you posted? You're mostly interested in SPIDs above 50 - the others are system tasks.

John
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search