We help IT Professionals succeed at work.

How to Shrink Windows Sharepoint 3 Services Log Files

3,433 Views
Last Modified: 2012-06-27
I have referred to this previous answer here: https://www.experts-exchange.com/OS/Microsoft_Operating_Systems/Server/MS-SharePoint/Q_23802162.html which provided a lot of information.

In this instance, we have files in the Data directory (as per the code section).

The log files in some cases are somewhat extreme and looking at the shrink function of Studio Express it looks like it will only recover a few hundred MBs using the release free space option, although I notice there is a shrink to xMB option.

I don't want to screw things up here, but this looks pretty extreme.

Also, I don't understand if SQL Express has a 4GB limit (excluding log files) how some of the other data files can be over 5GB.

Where is the actual sharepoint data stored?

Is it possible to use a SQL backup routine to truncate these log files, and run this regularly?
Volume in drive C is System
 Volume Serial Number is 888C-A916
 
 Directory of C:\WINDOWS\SYSMSI\SSEE\MSSQL.2005\MSSQL\Data
 
03/07/2009  11:38 AM    <DIR>          .
03/07/2009  11:38 AM    <DIR>          ..
22/01/2007  10:16 AM               517 78700386-9CAA-4EB7-9DF1-955B08FF32A3.cer
07/11/2008  05:32 AM               517 D04FA4D9-2D3D-4EC2-9D26-B3423952AB08.cer
03/07/2009  11:38 AM                 0 dircontents.txt
26/06/2009  07:21 AM         4,194,304 master.mdf
26/06/2009  07:21 AM           786,432 mastlog.ldf
26/06/2009  07:21 AM         1,245,184 model.mdf
26/06/2009  07:21 AM        30,212,096 modellog.ldf
26/06/2009  07:21 AM        22,282,240 msdbdata.mdf
26/06/2009  07:21 AM         3,538,944 msdblog.ldf
26/02/2008  07:19 PM           524,288 mssqlsystemresource.ldf
26/02/2008  07:19 PM        40,239,104 mssqlsystemresource.mdf
20/05/2009  07:13 AM        31,653,888 SharePoint_AdminContent_09a1b766-2cd6-4c68-bafe-e898663fe1d9.mdf
20/05/2009  07:13 AM       168,624,128 SharePoint_AdminContent_09a1b766-2cd6-4c68-bafe-e898663fe1d9_log.LDF
26/06/2009  07:21 AM         6,488,064 SharePoint_Config_d2892589-659e-454c-b214-5942c4a4b222.mdf
26/06/2009  07:21 AM    14,888,534,016 SharePoint_Config_d2892589-659e-454c-b214-5942c4a4b222_log.LDF
26/06/2009  07:24 AM         8,388,608 tempdb.mdf
26/06/2009  09:18 AM           786,432 templog.ldf
26/06/2009  07:21 AM        15,925,248 WSS_Content.mdf
26/06/2009  07:21 AM         9,633,792 WSS_Content_20bdf2f30fc4434cb741856093bc4a47.mdf
26/06/2009  07:21 AM         9,437,184 WSS_Content_20bdf2f30fc4434cb741856093bc4a47_log.LDF
26/06/2009  07:21 AM        13,828,096 WSS_Content_234448f4c0404f9c920a23cba18ec3cb.mdf
26/06/2009  07:21 AM       185,532,416 WSS_Content_234448f4c0404f9c920a23cba18ec3cb_log.LDF
26/06/2009  07:21 AM        13,828,096 WSS_Content_ceadb08e98604bc6a64ffb87fbd7440e.mdf
26/06/2009  07:21 AM       185,532,416 WSS_Content_ceadb08e98604bc6a64ffb87fbd7440e_log.LDF
03/07/2009  09:35 AM     5,764,218,880 WSS_Content_d43be3aa57054a1681cc5e1d5d8cf9d7.mdf
26/06/2009  07:21 AM    46,726,971,392 WSS_Content_d43be3aa57054a1681cc5e1d5d8cf9d7_log.LDF
26/06/2009  07:21 AM       204,079,104 WSS_Content_log.LDF
03/07/2009  11:24 AM       135,462,912 WSS_Search_UNI-SERVER2.mdf
03/07/2009  11:24 AM        36,569,088 WSS_Search_UNI-SERVER2_log.LDF
              29 File(s) 68,508,517,386 bytes
               2 Dir(s)  10,965,622,784 bytes free

Open in new window

Comment
Watch Question

Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Tony DavidsonCyber Security Specialist

Author

Commented:
That post has really good info in it. Easy to follow for an SQL dummy like me.

So what are the files listed?  Which files have my Sharepoint data in them?

Commented:
Your administration site
20/05/2009  07:13 AM        31,653,888 SharePoint_AdminContent_09a1b766-2cd6-4c68-bafe-e898663fe1d9.mdf
26/06/2009  07:21 AM         6,488,064 SharePoint_Config_d2892589-659e-454c-b214-5942c4a4b222.mdf

Your firstly created content database, probably contains your portal
26/06/2009  07:21 AM        15,925,248 WSS_Content.mdf


Other Web Applications, one should be hosting your mysites as well.
26/06/2009  07:21 AM         9,633,792 WSS_Content_20bdf2f30fc4434cb741856093bc4a47.mdf
26/06/2009  07:21 AM        13,828,096 WSS_Content_234448f4c0404f9c920a23cba18ec3cb.mdf
26/06/2009  07:21 AM        13,828,096 WSS_Content_ceadb08e98604bc6a64ffb87fbd7440e.mdf
03/07/2009  09:35 AM     5,764,218,880 WSS_Content_d43be3aa57054a1681cc5e1d5d8cf9d7.mdf

Your Search Index Database
03/07/2009  11:24 AM       135,462,912 WSS_Search_UNI-SERVER2.mdf

In the future, when creating a web application, when specifying the content database add something to the WSS_Content part, as if you don't SharePoint will add a guid to it (as shown on your files). If you make it something like WSS_Content_HRMPortal it will be a lot easier for the SQL Administrator to see what the database is used for.
Tony DavidsonCyber Security Specialist

Author

Commented:
This was all created pre my time looking after the site, but I'll certainly take your comments on board for new applications.

I've managed to reduce the sharepoint_config log file from 40GB to 50MB.  Can I do the same with the WSS_Content databases?

Also, is there some regular routine that I can run/script to stop these growing to silly sizes?

Commented:
Keeping the backup on simple will keep the log files a lot smaller.
Shrinking only gives a real boost on the log files (also on the WSS_Content_.... log files), on the normal data files there usually isn't a big difference.
You can set a job or maintennce task to truncate the log regularly.
CERTIFIED EXPERT
Top Expert 2010
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Top Expert 2010

Commented:
i wanted to add:
changing the Recovery Model and then Shrinking will not be a temporary reduction in the size of the log file.  it will significantly reduce the size of the log on a permanent basis.  just Shrinking or Truncating without changing the Recovery Model is a temporary reduction in size... it will re-grow to a large size again and you will need to keep Shrinking or Truncating.
Tony DavidsonCyber Security Specialist

Author

Commented:
Very good assistance. I've now set the log files to simple and shrunk them, for a major saving in space. (i.e. 60GB+ saved out of 66GB).

With these set to simple am I correct in assuming they won't grow large again?

Will I still need to shrink on a regular basis? If so, can I setup a task to do this?

Finally, with the 5GB data file, I'm assuming there is unusued space. Is it possible to free this up?

How do I determine what version of SQL I'm running. This install was all done previous to me working at the company, but from discussions with the CFO it was all included with Windows Server 2003 and nothing extra was purchased.
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Tony DavidsonCyber Security Specialist

Author

Commented:
I've run the script that the MS page you reference advises, and that returned the following:

9.00.3068.00    SP2    Windows Internal Database

Does that mean we're using SQL Express and the 4GB limit applies?

If so, why do we have a file greater than 5GB?
Tony DavidsonCyber Security Specialist

Author

Commented:
I've also asked another related question about backup and restore as I don't want to drag this question off-topic. If anyone is interested in looking at it the question is here: https://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_24544254.html

Commented:
Windows Internal Database is the WID.  Unlimited size.
Tony DavidsonCyber Security Specialist

Author

Commented:
Thanks guys. I asked a lot of questions and got a lot of helpful responses.

Commented:
Yep, that was a lot of work to earn 100 points. :)
Tony DavidsonCyber Security Specialist

Author

Commented:
But I did make it an A grade response so you get the bonus times....  I'd like to give more points but 500 is the limit unfortunately.

Commented:
Ha ha, no worries dude.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.