• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 178
  • Last Modified:

Need to shrink log files in Sharepoint

Now my MOSS_SHAREPOINT_CONFIG_LOG file is at 173 GB, please see picture. I have shrunk these types of files before in SQL but using increments of like 5-10 GB using the SQL command shell. And I have only shrunk the SEARCH DB LOG files. Can I shrink the CONFIG LOG Files without problems?

Please see attachment of my files. I have seen some articles saying you can just shrink using tasks__>shrink files, but I don want to mess anything up. Can I do it this way or is it not recommended because my file is so big?Sharepoint.jpg
0
JessicaWatters
Asked:
JessicaWatters
  • 12
  • 5
4 Solutions
 
JessicaWattersAuthor Commented:
Can I shrink using this method? Or should it be done in increments using the command shell?SHarepoint2.jpg
0
 
JessicaWattersAuthor Commented:
Also when I go to put MOSS_SHREPOINT_CONFIG file into simple mode it get the below message. See attached.
0
 
JessicaWattersAuthor Commented:
sharepoint-3.jpg
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
Randy Knight, MCMCommented:
You can shrink any size file using the DBCC SHRINKFILE command (which is what Management Studio uses if you to the Tasks-->Shrink).

To me the bigger question would be why is your file getting so big?  It sounds like you are in FULL recovery model.  Are you doing regular transaction log backups?  

Post the results of the following commands and we can probably give you some more help.
select name, log_reuse_wait_desc from sys.databases

DBCC LOGINFO

Open in new window

0
 
JessicaWattersAuthor Commented:
Here is the info requested. Hope this helps.SP3.jpg
0
 
JessicaWattersAuthor Commented:
As a side note, I was told the best way to shrink DB files is by using the command window if you have really big file sizes, rather than the GUI, is that correct?

Secondly is there any reason to NOT shrink "MOSS_SHAREPOINT_CONFIG_LOG file? I tried to run the following command and it didnt seem to do anything:

USE MOSS_SHAREPOINT_CONFIG

 EXEC sp_helpfile
 
 DBCC SHRINKFILE ( moss_Sharepoint_Config_log, 170000 )

Please advise as I have not done this before. Thank you!
0
 
Anthony PerkinsCommented:
I was told the best way to shrink DB files is by using the command window
If you mean from the SSMS Query window (as opposed to the SSMS GUI), then yes that is true.

Secondly is there any reason to NOT shrink "MOSS_SHAREPOINT_CONFIG_LOG file?
You should only do this in an emergency (as in this case) and it should never be scheduled.

DBCC SHRINKFILE ( moss_Sharepoint_Config_log, 170000 )
This would "shrink" it to 170000MB or 166GB, I suspect since you said it was initially 173GB you want a smaller value

However, before you do that you have to:
1. Backup your Transaction Log
2. If you are unable/unwilling to do 1 you will have to change the Recovery Model to Simple and lose the option of point-in-time restores.  However, since you were not doing Transaction Log backups, that may be a moot point.
0
 
Anthony PerkinsCommented:
Two additional points:
1. I suspect all the databases flagged with LOG_BACKUP in the log_reuse_wait_desc are in Full Recovery Model and do not have frequent Transaction Log backups.  Again you have a choice:  Start doing frequent Transaction Log backups or change to Simple Recovery Model and lose the option of point-in-time restores.

2. If you do select to do a Transaction Log backup of your database that has a Transaction Log file that is 173 GB, it will take a long time and you will need lots of disk space.
0
 
JessicaWattersAuthor Commented:
We have a backup system which backs up my servers and takes a mirror image of everything every two hours, I am thinking that will suffice. Do I need to take a SQL backup of these files?

If I change the recovery model to simple for the shrink process only I can change it back to full afterwards correct and all will be back same as before? I would like to keep the optoin to have point in time restores.

This has happened before to the DB_SEARCH_LOG db and we did this process and it seems after it stopped growing so largely. SO same for this file I want it to smaller size. How small can I go? Will size effect anything?
0
 
Anthony PerkinsCommented:
We have a backup system which backs up my servers and takes a mirror image of everything every two hours, I am thinking that will suffice
Not if you want to run in Full Recovery Model and get point-in-time restores.  Again here are your options:
1.  Keep it in Full Recovery Model and do frequent Transaction Log backups (this is different from a Full backup).
2.  Change to Simple Recovery Model.

If I change the recovery model to simple for the shrink process only I can change it back to full afterwards correct and all will be back same as before?
Yes.  Just make sure you do a Full Backup when it is complete.

SO same for this file I want it to smaller size. How small can I go?
That is very subjective and depends largely on the queries that you are doing.  Typically if you are using Full Recovery Model and doing frequent Transaction Log backups then you usually can determine the optimum size.  That is defined by the smallest size necessary for which the log file does not have to auto grow.

Having said all of that, try shrinking it down (after changing to Simple Recovery Model or doing a Transaction Log backup) to 4GB, as in:
DBCC SHRINKFILE(<YourTransactionLogNameGoesHere>, 4096)

That is assuming you just have the one Transaction Log file.

Will size effect anything?
And yes size is important.  It usually is.  :)
0
 
JessicaWattersAuthor Commented:
Okay sorry if I am sounding dumb but we almost never have to go into SQL to do anything so I need you to be very specific if possible. How do I run a simple backup, and before I run it how can I determine the size of the backup beforehand? I have 50GB left on my server and dont want to get near that number if I run a backup. Is there a way to query what the size of the backup will be before I do my shrink, and can you tell me exactly what commands to run?
0
 
JessicaWattersAuthor Commented:
Here is my current plan , please see attached.SP5.jpgSP6.jpg
0
 
JessicaWattersAuthor Commented:
Also I think my SQL logs are supposed to be getting cleaned up, why is the file growing so large?
0
 
JessicaWattersAuthor Commented:
I am trying to schedule a plan through the wizard, should I do a full backup and a transaction log backup, or only transaction log backup?
0
 
JessicaWattersAuthor Commented:
If I backup the MOSS_SHAREPOINT_CONFIG DB does that automatically backup MOSS_SHAREPOINT_CONFIG_LOG?
0
 
JessicaWattersAuthor Commented:
DOing a full backup for all DB and trans logs to be safe on offsite device. Please advise on next step or if there is anything else I need to check before running my shrink.
0
 
Anthony PerkinsCommented:
I have 50GB left on my server and dont want to get near that number if I run a backup.
Assuming that your Transaction log file is totally used (see below), if you only have 50GB for backups, then it will not be enough and this is what I would do:

1.  Change the Recovery Model to Simple.  You can do this from the GUI, but I find this a lot simpler:
ALTER DATABASE YourDatabaseName SET RECOVERY SIMPLE
2.  Run the following several times
Checkpoint
3.  Check the space used in the Transaction Log (see below)
4.  It may take a few minutes before most of your 170GB Transaction Log file is flagged unused.
5.  Shrink the Transaction Log (remember this should only be done in an emergency) as follows:
     DBCC SHRINKFILE(<YourTransactionLogNameGoesHere>, 4096)
if you only have 2 files: Data and Transaction file then you can do this:
     DBCC SHRINKFILE(2, 4096)
This will shrink the file down to 4GB.  I am not sure if that is the appropriate amount, as I have no idea what is your situation there.
6.  Do a Full backup
7.  If you have setup a Transaction Log backup plan, then you can reset back to Full Recovery Model as in:
ALTER DATABASE YourDatabaseName SET RECOVERY FULL
     if you have not, skip this step and leave it in Simple Recovery Model.

Space Used
Seeing as you are not doing any Transaction Log backups, more than likely the file is totally used.  There are a number of ways to find out the space used in the data and Transaction Log files, however it sounds like you are a visual person and prefers graphics.  So I would suggest you do the following from SSMS:
1. In the Object Explorer expand the Databases node.
2. Right click on your database and select Reports.
3. Select Standard Reports and then Disk Usage
4. Wait a few seconds.
5.  Compare how much is used vs how much is allocated.
0
 
Anthony PerkinsCommented:
Also I think my SQL logs are supposed to be getting cleaned up, why is the file growing so large?
There are two ways this can happen:
1.  You are doing frequent Transaction Log backups.
2.  The database is set to Simple Recovery Model.

Which one is it? If you are not sure, then I would suggest you find out soon.  This is important.  You do not want to run out of disk space.  This is how you can find out:
1. Checkout the Standard Reports I mentioned previously and look this time for the Backup and Restore Events report.
2. Use the GUI or simply from the Query window use:
SELECT DATABASEPROPERTYEX(N'YourDatabaseNameGoesHere', N'Recovery')

But rather than guessing how much space is allocated and how much is used, read up on the steps I outlined under Space Used.  This will save you having to update your resume, when you find out that the Transaction Logs were not "getting cleaned up" as you thought.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 12
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now