Log mining SQL server Transaction Log

use AdventureWorks2012
go
dbcc sqlperf(logspace)
/*-----------------------------
Database Name    Log Size (MB)    Log Space Used (%)    Status
master    2.242188    26.02352    0
tempdb    0.7421875    59.01316    0
model    0.4921875    90.77381    0
msdb    0.7421875    49.21053    0
AdventureWorks2012    0.9921875    55.56102    0
ReportServer    1.054688    44.02778    0
ReportServerTempDB    1.054688    43.84259    0
---------------------------*/s
go
select  * from sys.dm_db_log_space_usage
/*-----------------------------
database_id    total_log_size_in_bytes    used_log_space_in_bytes    used_log_space_in_percent    log_space_in_bytes_since_last_backup
5    1040384    578048    55.56102    101888
---------------------------*/
go

select * into dbo.emplyee_new from HumanResources.Employee

go
select o.name, o.type,o.create_date,o.modify_date,m.definition from sys.all_objects o inner join sys.all_sql_modules m
on o.object_id=m.object_id
where name like '%dblog%'
/*---------------------------
name    type    create_date    modify_date
fn_dblog    IF    2011-11-04 21:26:15.210    2011-11-04 21:27:08.297
fn_dblog_xtp    IF    2013-10-03 18:03:58.533    2013-10-03 18:03:58.567
fn_dump_dblog    IF    2011-11-04 21:26:16.583    2011-11-04 21:27:08.300
fn_dump_dblog_xtp    IF    2013-10-03 18:03:58.580    2013-10-03 18:03:58.597
---------------------------*/
GO

select [Current LSN], Context, [Operation],AllocUnitName from sys.fn_dblog(null,null)
-- where [Current LSN]> '0000002d:0000017b:0002'
go

select [Current LSN],[Begin Time],[Transaction Name]  from sys.fn_dblog(null,null) where [Transaction Name] is not null
go

select convert(char(30),AllocUnitName) as AllocUnitName,
sum(convert(numeric, [Log Record Length])) as log_record_bytes,
sum(convert(numeric, [Log Reserve])) as log_reserve_bytes  from sys.fn_dblog('45:379:1',null)
group by AllocUnitName order by log_reserve_bytes desc
/*---------------------------
AllocUnitName                  log_record_bytes                        log_reserve_bytes
------------------------------ --------------------------------------- ---------------------------------------
dbo.emplyee_new                75956                                   136732
NULL                           1360                                    36966
sys.sysobjvalues.clst          1540                                    297
sys.sysprufiles.clst           244                                     209
sys.sysiscols.clst             128                                     74
sys.sysiscols.nc1              116                                     74
sys.sysidxstats.nc             164                                     74
sys.sysidxstats.clst           196                                     74
Unknown Alloc Unit             4216                                    0
sys.sysschobjs.clst            420                                     0
---------------------------*/
go

select * from fn_dblog_xtp(null,null)
go
select * from sys.database_files where type_desc='LOG'
GO

delete from dbo.emplyee_new where SickLeaveHours>20
go

BACKUP LOG [AdventureWorks2012] TO  DISK = N'C:\SampleDB\AdventureWorks2012_log.bak'
WITH NOFORMAT, NOINIT,
NAME = N'AdventureWorks2012-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

select * from fn_dump_dblog(null,null,'DISK',1,'C:\SampleDB\AdventureWorks2012_log.ldf',
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL)

select * from fn_dump_dblog(null,null,'DISK',1,'C:\SampleDB\AdventureWorks2012_log.bak',
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL)
go

select * from fn_dump_dblog_xtp(null,null,'DISK',1,'C:\SampleDB\AdventureWorks2012_log.bak',
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL)
go