Find Transaction Log information

Transaction Log in SQL server records all operation on database.This recoring depends on Recovery Model selected for database.You can find Recovery Model for each database by following Query

SELECT

    name as DataBaseName,

    log_reuse_wait_desc,

    recovery_model_desc

    FROM sys.databases      

Output:

There are three Recovery Model as

1. Simple: In simple Recovery Model,SQL server truncates the log ate every Transaction Checkpoint.It can not be used for disaster recovery purpose.So you can choose this one on test Server.
2. Full: As  name suggested,In Full recovery model SQL server truncates transaction log every time you backup it under Full recovery Model.With this you can recover database up to a certain point in time.
3. Bulk-Logged: Performs logging of bulk operations.In this, Transaction log can be backed up quicker than with the FULL recovery model.
   
   
   

   How to see Transaction Log Size

   you can use DBCC command for same as
   DBCC SQLPERF(LOGSPACE);

   
   

   Output:

   

   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   You see if you choose recovery model of your database FULL,then transaction size will increase frequently.So you need to truncate transaction log on regular basis to keep it from filling up.You can schedule a job for backup and truncation transaction log or you can create maintenance plan.
   
   You can shrink your Transaction Log File in below simple steps\

USE your_db

GO

ALTER DATABASE your_db_name SET RECOVERY SIMPLE WITH NO_WAIT

DBCC SHRINKFILE(N'your_db_log_name', 1)

ALTER DATABASE your_db_name SET RECOVERY FULL WITH NO_WAIT

GO