SQL recovery model

First we see the recovery model of database .SQL server 2005 have three recovery model

1. Full

2. Bulk-logged

3. Simple

Full—This is what it says; everything is logged. Under this model, you should have no data

loss in the event of system failure assuming you had a backup of the data available and have all

transaction logs since that backup. If you are missing a log or have one that is damaged, then

you’ll be able to recover all data up through the last intact log you have available. Keep in mind,

However, that as keeping everything suggests, this can take up a fair amount of space in a system

that receives a lot of changes or new data.

Bulk-Logged—This is like “full recovery light.” Under this option, regular transactions are

logged just as they are with the full recovery method, but bulk operations are not. The result is

that, in the event of system failure, a restored backup will contain any changes to data pages

that did not participate in bulk operations (bulk import of data or index creation for example),

But any bulk operations must be redone. The good news on this one is that bulk operations perform

much better. This performance comes with risk attached, so your mileage may vary.

Simple—Under this model, the transaction log essentially exists merely to support transactions

as they happen. The transaction log is regularly truncated, with any completed or rolled back

transactions essentially being removed from the log (not quite that simple, but that is the effect).

This gives us a nice tight log that is smaller and often performs a bit better, but the log is of zero

use for recovery from system failure.

Decrease the log file Size:

Here we see how to decrease the log file we have lot of methods

Method 1:

First right click the database go to tasks and navigate to detach

And remove the old ldf file and again attach the same database again now ldf file created with minimum size of ldf file it have more or less 540 kb.

And again detach the database and copy and paste that ldf your normal path .again attach the database with specific ldf

Method 2:

Another one method is shrink the database

Right click the database go to tasks and choose shrink and navigate the database one pop-up window as below

then tick the reorganize files checkbox then click OK button .If you want to give the maximum size of free space the put it that box else leave it.