I have seen lot of sql developers facing a generic problem in their database.
Problem description: Log file size increases a lot and its occupying the enitire database and i need to truncate my log file. Is it possible?
Yes, Its possible. Its one of an important DBA role. Lets see some of the cause for this issue and afterwards, we will discuss how to sort down this problem?
Recovery model of the database.
There are various recovery model like,
Full - It will take the entire log related information and place it in the log file and it can be used to recover the entire database irrespective of the backups strategy followed.
Bulk logged : This will take only bulk operations happened in the database and its following the concept of minimal logging and it's quite similar to the full recovery model.
Simple :This will fetch normal operations like DML operations and DDL operations and the log file will be flushed after the backup have been taken on the database and it will start logging the informations from the last backup.
Log files available in sql server
Each database will hold three files named
1. Master file (MDF) which will hold the master data and general data
2. Log file (LDF) to hold the log related information.
3. Secondary file (NDF) which hold the data, if mdf file is filled out.
In these files, we are having the autogrowth option, which is very important for a DBA. Based on our business requirements and strategies. We need to se this option. The option may be of two types,
1. Growth by percentage
2. Growth by value.
Now, Lets come to our problem. I need to reduce the size of my log file.
1. Shrinking the file with out backup.
Backup log database with truncate_only
and execute the command,
DBCC shrinkfile (Logfilename,size)
2. Shrinking the file with backup.
Backup log database to backupname
and execute the command,
DBCC shrinkfile (Logfilename,size)
Happy Learning!!!
Thanks and Regards,
Venkatesan Prabu .J