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»»

95 GB Transaction Log but Log Backups are much smaller Expand / Collapse
Author
Message
Posted Wednesday, April 9, 2014 7:49 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 12:40 PM
Points: 15, Visits: 125
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
Post #1559952
Posted Wednesday, April 9, 2014 7:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:51 AM
Points: 5,425, Visits: 10,085
Karen

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

John
Post #1559956
Posted Wednesday, April 9, 2014 8:01 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 12:40 PM
Points: 15, Visits: 125
John -

ACTIVE_TRANSACTION

Thanks,

Karen
Post #1559961
Posted Wednesday, April 9, 2014 8:05 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:51 AM
Points: 5,425, Visits: 10,085
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
Post #1559964
Posted Wednesday, April 9, 2014 8:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 12:40 PM
Points: 15, Visits: 125
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.
Post #1559972
Posted Wednesday, April 9, 2014 8:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:51 AM
Points: 5,425, Visits: 10,085
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
Post #1559975
Posted Wednesday, April 9, 2014 8:22 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 12:40 PM
Points: 15, Visits: 125
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



Post #1559977
Posted Wednesday, April 9, 2014 8:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:51 AM
Points: 5,425, Visits: 10,085
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
Post #1559983
Posted Wednesday, April 9, 2014 8:34 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 12:40 PM
Points: 15, Visits: 125
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
Post #1559987
Posted Wednesday, April 9, 2014 8:39 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:51 AM
Points: 5,425, Visits: 10,085
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
Post #1559992
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse