Transaction log in sql server

could be a few things affecting... are the transaction logs under a maintenance plan of some description where full backups and frequent ttransaction logs backups for a FULL recovery mode database, or, maybe you are using SIMPLE recovery mode.

do you run a shrink or some other "clean up" process ? has the database files been analysed and optimised for a size and growth ? Is there sufficient disk space ? has the lof been allowed to grow (obviously not), what is the current size ?

all these types of things might come into play as well... not necessarily just a "rogue/unruley/hungry" process sending it into a spin...

one thing you can do if you notice and increase in size:

DBCC OPENTRAN

it will show you the open transactions and the SPID's associated. You can then track back to the SPID's and see the SQL that they are using at the time.

you could automate the whole lot in a stored procedure and schedule it to run every so often... have a look at a couple of code snippets below...



--capture logspace into a table (and a general approach for collecting dbcc outputs)
create table tmp_sqlperf(databaseName varchar(100),LogSize decimal(18,5),LogSpace decimal(18,5),Status int)

insert tmp_sqlperf
exec ( 'dbcc sqlperf (logspace)')
select * from tmp_sqlperf


--check current log activity
select * from fn_dblog(NULL,NULL)
--or
dbcc log('MY_DB',-1)


--check current SPID's with opentrans
dbcc opentran

--capture the SQL /processes belonging to a specific spid
DECLARE @Handle binary(20)
SELECT @Handle = sql_handle FROM master.dbo.sysprocesses WHERE spid = 57
SELECT * FROM ::fn_get_sql(@Handle)
--or
select * from sys.dm_exec_sql_text(@handle)
Open in New Window Select All Not what you're looking for? Ask an Expert.

Hiç yorum yok:

Visual Studio 2017'de Devexpress 17.2.5 Toolbox görünmüyor

Visual Studio 2017 Toolbox'ı üzerinde Developer Express componentlerini göremiyorsanız aşağıdaki komutu çalıştırmak işini görecektir. (...