Solved

How to Shrink Windows Sharepoint 3 Services Log Files

Posted on 2009-07-02
18
3,143 Views
Last Modified: 2012-06-27
I have referred to this previous answer here: http://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

0
Comment
Question by:tonydav67
  • 7
  • 4
  • 3
  • +2
18 Comments
 
LVL 19

Accepted Solution

by:
MsShadow earned 200 total points
ID: 24771012
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.
0
 
LVL 15

Assisted Solution

by:dp_expert
dp_expert earned 50 total points
ID: 24771694
0
 
LVL 2

Author Comment

by:tonydav67
ID: 24771830
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?
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 19

Expert Comment

by:MsShadow
ID: 24771881
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.
0
 
LVL 2

Author Comment

by:tonydav67
ID: 24772040
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?
0
 
LVL 19

Expert Comment

by:MsShadow
ID: 24772058
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.
0
 
LVL 15

Expert Comment

by:dp_expert
ID: 24772549
You can set a job or maintennce task to truncate the log regularly.
0
 
LVL 43

Assisted Solution

by:zephyr_hex (Megan)
zephyr_hex (Megan) earned 150 total points
ID: 24773295
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).

http://grounding.co.za/blogs/brett/archive/2007/12/08/sharepoint-and-sql-express-sql-standard-and-sql-embedded.aspx

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:
http://msdn.microsoft.com/en-us/library/aa173531%28SQL.80%29.aspx

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:
http://serverfault.com/questions/22138/whats-your-best-practice-recovery-model-for-sharepoint-databases
0
 
LVL 43

Expert Comment

by:zephyr_hex (Megan)
ID: 24773318
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.
0
 
LVL 2

Author Comment

by:tonydav67
ID: 24774789
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.
0
 
LVL 13

Assisted Solution

by:itgroove
itgroove earned 100 total points
ID: 24775065
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: http://support.microsoft.com/kb/321185
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:
\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query
Reference: http://blog.brainlitter.com/archive/2008/10/21/how-to-connect-to-a-sharepoint-windows-internal-database-wib-with-sql-management-studio-express-2005.aspx  
0
 
LVL 2

Author Comment

by:tonydav67
ID: 24778174
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?
0
 
LVL 2

Author Comment

by:tonydav67
ID: 24778213
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: http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_24544254.html
0
 
LVL 13

Expert Comment

by:itgroove
ID: 24778721
Windows Internal Database is the WID.  Unlimited size.
0
 
LVL 2

Author Closing Comment

by:tonydav67
ID: 31599872
Thanks guys. I asked a lot of questions and got a lot of helpful responses.
0
 
LVL 13

Expert Comment

by:itgroove
ID: 24778927
Yep, that was a lot of work to earn 100 points. :)
0
 
LVL 2

Author Comment

by:tonydav67
ID: 24779342
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.
0
 
LVL 13

Expert Comment

by:itgroove
ID: 24780700
Ha ha, no worries dude.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Summary In SharePoint 2010 it is easy to create custom color themes to jazz up a site. Theme colors can also be created in PowerPoint 2010 with a few clicks. But how do the chosen colors actually look in the SharePoint site? The attached PowerPoint…
SharePoint Designer 2010 has tools and commands to do everything that can be done with web parts in the browser, and then some – except uploading a web part straight into a page that is edited in SPD. So, can it be done? Scenario For a recent pr…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question