Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Querying Microsoft SQL Server

I am a technology enthusiast and software developer by profession. I am developing .Net/database based enterprise applications from past 3 years.

My skills includes C# ,ASP.NET,SQL Server 2008 and MVC . My areas of interests are database development and application software development using Microsoft Technologies.

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


Comments

Leave a comment on the original post [queryingsql.blogspot.com, opens in a new window]

Loading comments...