Solved

How to Shrink Windows Sharepoint 3 Services Log Files

Posted on 2009-07-02
18
3,105 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
Comment Utility
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
Comment Utility
0
 
LVL 2

Author Comment

by:tonydav67
Comment Utility
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
 
LVL 19

Expert Comment

by:MsShadow
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
You can set a job or maintennce task to truncate the log regularly.
0
 
LVL 42

Assisted Solution

by:zephyr_hex
zephyr_hex earned 150 total points
Comment Utility
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 42

Expert Comment

by:zephyr_hex
Comment Utility
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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 2

Author Comment

by:tonydav67
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Windows Internal Database is the WID.  Unlimited size.
0
 
LVL 2

Author Closing Comment

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

Expert Comment

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

Author Comment

by:tonydav67
Comment Utility
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
Comment Utility
Ha ha, no worries dude.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video discusses moving either the default database or any database to a new volume.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

744 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now