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
It saved my time.
ReplyDeleteI was looking for command without taking the backup of log file...
Thanks...good work...keep it up...
Know good tool which works with sql files-repair mdf,as far as I know it is free,utiltiy repair data from corrupted databases in the MS SQL Server format (files with the *.mdf extension),supports data extraction via the local area network,can save recovered data as SQL scripts, it is also possible to split data into files of any size,compatible with all supported versions of Microsoft Windows, such as Windows 98, Windows Me, Windows NT 4.0, Windows 2000, Windows XP, Windows XP SP2, Windows 2003 Server, Windows Vista,tool supports the following database formats: Microsoft SQL Server 7.0, 2000, 2005,also can repair .mdf files of Microsoft SQL Server 2005, repair mdf file of Microsoft SQL Server 2005 (64-bit).
ReplyDeleteYesterday I lost all my sql files...But fortunately in net I found-how to recover sql database,and tool helped me in a minute and free of cost.Moreover tool showed repair data from corrupted databases in the MS SQL Server format (files with the *.mdf extension).
ReplyDeleteI spent more time in reading your blog. Excellent & wonderful blog
ReplyDeleteKeep up the good work !!!
For me sql server is a quite important tool,because I often work with it. But yesterday I had a big unplesant problem. For luck I fast found a next software - recovery mdf file. And it determined my issue quite fast and easy as far as I remember. Moreover it learnt me how repair data from corrupted databases in the MS SQL Server format (files with the *.mdf extension).
ReplyDelete