how to shrink log_file database in sql server


Here I want to share how to shrink log_file in sql server :

1. First we have to check whether the database recovery mode is simple or still full. If still full, change it to simple.

     query :
Alter database DATABASE_name
Set recovery SIMPLE
eg :
Alter database DEMO
Set recovery SIMPLE

2. After that, check logic name from .ldf file database.

     query :
Sp_helpdb database_name
eg :
sp_helpdb DEMO

     of the query result will appear as follows :

query result 1

     then we select the name, the usage log_only = PMS_Log

3. Run the following query :

     Dbcc shrinkfile (logic_name_file_log_DB,the_desired_size_in_MB)

     eg :
Dbcc shrinkfile (PMS_Log,200)

query result 2

4. Once the shrink process is complete, the usage of the DB must be updated so that the allocation size to DB can be updated.

     query :
DBCC Updateusage(nama_database)
eg :
DBCC Updateusage(DEMO)

     up pops a message like the following :

query result 3

5. (optional)
If the first step above, the recovery model of the DB = full then the recovery model must be in return it to the full.

    query :
Alter database DATABASE_name
Set recovery FULL
eg :
Alter database DEMO
Set recovery FULL

6 thoughts on “how to shrink log_file database in sql server

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s