How to Shrink Windows Sharepoint 3 Services Log Files

I have referred to this previous answer here: 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
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

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Put the backup settings of your database to simple and run shrink again. You should see a lot more space being cleared.
Also, for the 4gb database limit, it means 4gb of database size, and not 4gb of file size. This means that your database can be 5gig but have 1gig of free space in it. Users will experience documents no longer being saved when you reach the 4gb limit.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tonydav67Author 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?
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

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.
tonydav67Author 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?
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.
zephyr_hex (Megan)DeveloperCommented:
just want to clarify here about the 4GB database limit.  if you did the standalone installation of wss 3.0, there is no limit on the database size.  that is sql server embedded edition, which is not the same animal as sql server express or wmsde (which is wss 2.0 standalone database).

as for keeping your SQL log file under control... by default, the Recovery Model is set to Full.  that makes for a large transaction log.  you can change the Recovery Model to Simple and that will greatly reduce the size of the transaction log.

"shrinking" a database is usually only a temporary solution, as SQL will soon "grow" again.

here is more info about Recovery Models:

since you are talking about "shrinking" the databases, i'm assuming you've already figured out how to connect to the sql instance.  perhaps using SQL Management Studio?  i'll assume that's the case.  so, right click on the database and select Properties.  On the Options page, change Recovery Model to Simple.  then do a Shrink ;)

one thing you want to be aware of :  with Simple Recovery, you are basically relying on your SQL database backups for recovery, so you'll want to keep a full backup.  i do mine daily.  i also run sharepoint backup daily.  that gives me 2 options for recovery.  and if something bad happens, i can restore from the previous night's backup.
the "answer" selected in this post gives more detail:
zephyr_hex (Megan)DeveloperCommented:
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.
tonydav67Author 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.
If you leave them set to simple, the transaction logs will not grow exponentially as once the transactions are committed to the database, the log lets it go.  That said, FULL recovery is always *recommended* as it gives you the opportunity to restore 'to the moment of failure' by restoring the most recent backup and then 'replaying' the logs.
So, go Simple if you don't have separate disk volumes (as separate disks help in a recovery scenario, where you would put the DB's on one, and the logs on another) OR if you can't afford the space AND/OR you only ever need to revert back to your last full backup in a recovery (i.e. you can only do full backups, you can't do differentials, with SIMPLE as there are no logs to differentiate).
You may be able to optimize your database a little bit (MDF) but usually negligible.  Never hurts to occasionally optimize, defragment, etc. though.
Re: determining your SQL Version:
Note though, that if you don't have a SQL Server Enterprise manager tool or programs group for SQL, you will have either SQL Server Express (2.0) or the WID (3.0) or some other concoction.
If that is the case, download and install SQL Server Management Studio Express (its free) and connect to your SharePoint SQL instance. If you have the WID (unlimited size, WSS 3.0 database by default), you'll need to connect to it by using this path:
tonydav67Author 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?
tonydav67Author 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:
Windows Internal Database is the WID.  Unlimited size.
tonydav67Author Commented:
Thanks guys. I asked a lot of questions and got a lot of helpful responses.
Yep, that was a lot of work to earn 100 points. :)
tonydav67Author 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.
Ha ha, no worries dude.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.