Shrink the Content DB Log files

Don't know how many people faced these type of issue - Not able to proceed any thing in the share Point site - Reason "DB space is Full" - How to create the space. what we can compress or not  - so that at least work can continue unless until - administrator doesn't   add new disk or add more space to existing space.

Locate the database that has the log file and use either of the following sets of steps to shrink the transaction log file down to a reasonable size.

Option 1:- Manual Steps :-
Open the SQL Server Management Studio.
  1. Expand the Databases node and expand User Databases
  2. Right-click the database, and click Properties, which opens the Database Properties dialog box.
  3. In the Select a page pane, click Options.
  4. View the current recovery model in the Recovery model list box, which should be set to Full
  5. Click the dropdown arrow in the recovery model section and select the Simple recovery model
  6. Click OK.
  7. Right-click on the same database name and click Task-> Shrink-> Files 
  8. Use the File type drop-down menu and choose Log
  9. You can use the default setting of Release Unused Space or select Reorganize pages before releasing unused space, and you can specify the file size by supplying a value in the Shrink file to option.
           Note: the shrink may take some time depending on how large the file is and how much it has to shrink.
  1. After the shrink completes, change the recovery model back to Full by clicking the recovery model dropdown arrow and selecting the Full recovery model

Option 2:- Using SQL Command:-

use [databasename];
alter database [databasename] set recovery simple;
dbcc shrinkfile (2,0);
alter database [databasename] set recovery full;
for example:

use WSS_Content;
alter database WSS_Content set recovery simple;
dbcc shrinkfile (2,0);
alter database WSS_Content set recovery full;


Happy Coding !!

No comments:

Post a Comment