MSSQL Server Database’s Transaction Log Shrink
MSSQL Server Database’s Transaction Log Shrink
For MSSQL DBA .Its very commo issue that transaction log locaation is about to full or completly fill.If the log fills while the database is online, the database remains online but can only be read, not updated.
Which is for an OLTP DB is kind DB or application hung issue.
c. Enter Backup file Folder and File name with extension “.bak”
d. Select “Task” ==> “Backup” ==> Choose “Backup type” as “Transaction Log”
e. Enter Backup file Folder and File name with extension “.trn”
Which is for an OLTP DB is kind DB or application hung issue.
In this blog ,We will discuss and show How you can resolve this issue.
1) Login to the system
2) Access “Microsoft SQL Server Management Studio”
3) Click on “Connect” Button.
Now, you are now connected to the Database.
4) Expand “Databases” tree
5) Database’s Full & Transaction Logs backup before Shrink.
a. Right Click on desired database
b. Select “Task” ==> “Backup” ==>Choose “Backup type” as “Full”
a. Right Click on desired database
b. Select “Task” ==> “Backup” ==>Choose “Backup type” as “Full”
c. Enter Backup file Folder and File name with extension “.bak”
d. Select “Task” ==> “Backup” ==> Choose “Backup type” as “Transaction Log”
e. Enter Backup file Folder and File name with extension “.trn”
6) Shrink Database.
a. Right Click on desired database
b. Select “Task” ==> “Shrink”==> “Files”
a. Right Click on desired database
b. Select “Task” ==> “Shrink”==> “Files”
c. Choose File type as “Log”
d. Click on “Reorganize pages before releasing unused space” under “Shrink action”
e. Enter “10” in “Shrink Fileto” box
f. Click “Ok”
e. Enter “10” in “Shrink Fileto” box
f. Click “Ok”
g. Check at O/S level whether File shrunk to the 10MB.
Important Note : When above given procedure is not SHRINKing the Log File, then perform the additional steps as below :
Important Note : When above given procedure is not SHRINKing the Log File, then perform the additional steps as below :
==>Right Click on the Database Properties
==>Choose Options
==>Change “Recovery Model” from ‘Full’ to ‘Simple’
==>Choose Options
==>Change “Recovery Model” from ‘Full’ to ‘Simple’
==>Click OK
==>Perform ‘Step 6’
==>Change “Recovery Model” from ‘Simple’ to ‘Full’ again after shrunk.
==>Perform ‘Step 6’
==>Change “Recovery Model” from ‘Simple’ to ‘Full’ again after shrunk.
==> Perform Full database backup after converting database recovery model to Full.
You can learn more about SQL Server database from below books:
==============================================================
You can also , Visit our Blogs for related topic on MSSQL:
Comments
Post a Comment
Please do not enter any spam link in comment Section suggestions are Always Appreciated. Thanks.. !