SharePoint 错误0x80040E14解决（续）

cyrus

今天SharePoint再次遇到0x80040E14的错误，登陆到服务器上发现又是数据库膨胀导致系统盘空间满了，这回与上次情况（可以参看这里）不同，原因引起是由于SharePoint_Config数据库的日志文件膨胀到很大，17G，导致磁盘没有空间，打开后剩余空间为0字节……居然还能远程登录操作数据库，幸运啊。
解决方案：收缩内容数据库日志。
方法一：
USE DatabaseName
GO
DBCC SHRINKFILE(, 1)
BACKUP LOG  WITH TRUNCATE_ONLY
DBCC SHRINKFILE(, 1)  
方法二：
BACKUP LOG "SharePoint_Config" WITH NO_LOG
选中某个数据库点右键后，在弹出菜单中选择任务-收缩-文件 在弹出的窗口 文件类型 中选择日志，设置收缩的大小即可。

收缩数据库日志文件后，问题解决。

参考资料：
http://social.technet.microsoft.com/forums/en-US/sharepointadmin/thread/daf73588-3031-4e03-9afa-500005e3f47d/
http://www.cnblogs.com/perky_zhou/archive/2004/11/23/67317.html
http://mindsharpblogs.com/bill/archive/2005/01/14/181.aspx
http://www.itpub.net/viewthread.php?tid=1048025

内容：
http://social.technet.microsoft.com/forums/en-US/sharepointadmin/thread/daf73588-3031-4e03-9afa-500005e3f47d/
Sharepoint Config DB size grown to 25 GB

• 
  
  
  
  • Alert Me
    
  Pramod Chavan Wednesday, March 26, 2008 4:34 AM 0 votesVote As Helpful My sharepoint farms' config DB size has grown to 25 GB leaving no sapce on disc. That to alone Config_log.ldf file is of 24.5 GB.
  how can i truncate size of this log DB?
  Is there any way to limit the size of Sharepoint Config DB?
  Thanks in advance. Report As Abuse
  
  
  
  • Reply
    
  • Quote
    
  
  

Answers

• Moonis Tahir Monday, April 21, 2008 7:41 PM 0 votesVote As Helpful Truncate Config DB as a regular SQL transaction log file truncation. it has nothing to do with sharepoint programming forum. however here is how i truncate the log, run these in Query window in SQL USE DatabaseName
  GO
  DBCC SHRINKFILE(, 1)
  BACKUP LOG  WITH TRUNCATE_ONLY
  DBCC SHRINKFILE(, 1) transaction log name is your config database ldf file name without .ldf extension. database name is your wss config database name. Report As Abuse
  
  
  
  • Reply
    
  • Quote
    
  
  

All Replies

• Pramod Chavan Monday, April 21, 2008 6:24 AM 0 votesVote As Helpful anyone please... Report As Abuse
  
  
  
  • Reply
    
  • Quote
    
  • Propose As Answer
    
  
  
• Moonis Tahir Monday, April 21, 2008 7:41 PM 0 votesVote As Helpful Truncate Config DB as a regular SQL transaction log file truncation. it has nothing to do with sharepoint programming forum. however here is how i truncate the log, run these in Query window in SQL USE DatabaseName
  GO
  DBCC SHRINKFILE(, 1)
  BACKUP LOG  WITH TRUNCATE_ONLY
  DBCC SHRINKFILE(, 1) transaction log name is your config database ldf file name without .ldf extension. database name is your wss config database name. Report As Abuse
  
  
  
  • Reply
    
  • Quote
    
  
  
• Pramod Chavan Tuesday, April 22, 2008 5:02 AM 0 votesVote As Helpful Thank you for the response. My apology for putting misleading question. Actually truncating is not an issue but as my sharepoint config DB is growing to 25 GB in 2-3 days after each time I truncate the DB.And I am not able to figure out how this is happening as I am not doing any major updation on my sharepoint portal. So my problem is how I can set restrictions on Config DB so that it will not grow beyond a limit. Thanks a lot. Report As Abuse
  
  
  
  • Reply
    
  • Quote
    
  • Propose As Answer
    
  
  
• Michael Washam - MSFTMSFT, ModeratorTuesday, April 22, 2008 4:35 PM 0 votesVote As Helpful Moving to the admin forum Report As Abuse
  
  
  
  • Reply
    
  • Quote
    
  • Propose As Answer
    
  
  
• Dave Wollerman Thursday, April 24, 2008 12:53 AM 0 votesVote As Helpful This is happening because all the databases (Except for the search and ssp databases) are set to full recovery mode by default. Full recovery mode does not auto shrink / truncate the log files on a full backup. You can change this to simple recovery mode and it will auto truncate on full backups. Microsoft does not recommend changing to simple recovery mode in a production environment. Full recovery mode allows the SQL admins to backup the transcation logs incrementally, simple recovery mode does not allow this and only allows full or differential backups, no transaction log backups. I assume you need full recovery mode to do log shipping and mirroring as well.
  

http://www.cnblogs.com/perky_zhou/archive/2004/11/23/67317.html
部署Sharepoint要注意SQL Server的事务日志文件
公司的Sharepoint系统用到的数据库和其他的应用共享同一个数据库。在规划期，由于缺乏经验，只考虑到了Sharepoint中存储的内容的大小，根本没有去考虑Sharepoint数据库事务的日志的大小。熟悉Sharepoint的人都知道，windows Sharepoint service v2和Sharepoint portal Server系统中几乎所有的内容都存放在数据库中，也就是说，每访问一次Sharepoint站点，就会发生若干次访问数据库的操作，因此，Shaerpoint 数据库的事务日志文件长得非常之快。我们系统中的数据文件不过10个G左右，但是，日志文件却达到了近30G。当数据库的事务日志满了以后，许多的应用程序便无法访问。
那么怎么解决这个问题呢？
首先，我们在规划的时候就要考虑到这个问题，所以，建议大家将Sharepoint 数据库和其他应用的数据库放在不同的分区内，并且考虑保留足够的空间来存放日志。
其次，建议单独辟出一块空间来存放日志。
第三，就是要定期的收缩数据库。您可以使用自动维护任务，但是，有时候自动收缩的效果并不是十分的好。那么，我建议你定期的手工进行收缩。步骤如下：
首先修改数据库的故障恢复模式为“简单”模式，然后，再收缩数据库，最后，该回标准模式。
http://mindsharpblogs.com/bill/archive/2005/01/14/181.aspx
SQL Databases, the LDF File and SharePoint Portal Server backups
What follows is a long email thread about SQL databases, the size of the LDF File and Backups in portal server.  Since this is an email thread, you'll need to start reading from the bottom and work your way to the top.  This issue is about the size of the LDF file relative to SharePoint Portal Server and the backup process.  And many thanks to Sara for allowing me to post this here.  ---------------------------------------------------------------------- Hi Bill – Feel free to do whatever you’d like with the info. I’m hoping it will eventually work its way into something “official” as it’s certainly an issue – especially as the size of the SharePoint databases begins to grow.  Anyways, the end result was that: (1) I was able to use the assorted tools mentioned to manually shrink the unwieldy sizes of my _SITES logfiles – these were the ones that were WAY out of hand (i.e. database size of 30-40 GB; logfile size of 50+ GB!) – so this does work, and potentially needs to become a part of one’s maintenance plan. (2) Per Microsoft – there really is no “rule of thumb” to guess at what a good logfile size SHOULD be; however, it’s really a moot point now due to the final recommendation for the RECOVERY METHOD. (3) Per Microsoft – the only supported restore method for SPS is via SPS’s backup/restore tool. It is NOT supported to restore SPS via its restore tool, and then restore some (or all) of the databases to a more current point in time via SQL using backed-up transaction logs. Hence – with SPS there’s no point to having the databases set to FULL RECOVERY at all; SIMPLE RECOVERY provides better performance. (4) If you have a standalone WSS installation (no SPS), then there are some options for using SQL tools to restore to a more current point in time – depending on your disaster recovery plan, you may or may not want to change the RECOVERY MODE of the WSS DBs. Some of the actual “conversation” with Microsoft: What size SHOULD the LDF files be for optimal SPS performance? (came from Microsoft SQL support team) When you back up an LDF file, you are given the opportunity to specify the new size for this file.  If you don't specify a size, SQL will try to compact this file down as small as possible which might be 1 or 2 MB in size.  Although this is ideal as far as drive space is concerned, it is NOT ideal for performance with the SQL databases for SharePoint.  There is no set formula or rule on how large the LDF file should be, but it should be of an adequate size that will prevent it from having to grow automatically every single day when it reaches its current size limit.  For instance, if you state that the new size of the LDF file is going to be 2 GB in size, the initial size of the LDF file will be 2 GB, and it will stay this size until 2GB worth of new transactions have taken place.  When this point is reached, the LDF file will grow automatically, and for that short time during its automatic growth, you might see a small performance hit on the SQL Server.  This is what needs to be avoided if possible.  The SQL Engineer I spoke with stated that if your MDF file was around 30 to 50 GB in size, a 2 to 3 GB LDF file should be more than adequate to start out with, but again there is no hard formula for what the size of the LDF file should be. Why are some DBs set to FULL RECOVERY and some are not? Is there even any supported SharePoint restore method that uses SharePoint’s RESTORE to restore the full databases; and then a more current Transaction Log restore within SQL to move databases set to FULL RECOVERY to a more current point in time? (came from Microsoft SPS escalation team) The reason that the _SITES database is the only database that has Full Recovery Mode enabled is because it is created by Windows SharePoint Services and the other databases are created by SharePoint Portal Server. [Actually – the Config DB is also set to FULL RECOVERY by default – but it stays small.]  If you have a standalone deployment of Windows SharePoint services, it is possible to back up the database via SQL and add the data from the transaction logs after the fact.  With a SharePoint Portal Server deployment all of the databases must be backed up and restored from the same point in time, so this option is not possible.  I believe this is why the Solution Object from our Development Team stated that it would be all right in a Portal deployment to go ahead and change the Recovery method to simple as the only supported restore method is from the Full SQL Backups taken by the SharePoint Portal Data Backup and Restore utility. Anyway – since I’m a full SPS deployment – my next (and hopefully final!) reconfig task is to set all my databases to SIMPLE RECOVERY for better performance – and this will make the whole logfile size thing a moot point! Sara From: Bill English [mailto:bill@mindsharp.com]
Sent: Sunday, January 09, 2005 7:25 PM
To: Sara
Subject: RE: question from an old student At a minimum, may I turn this into a blog entry?  I’ll post it at mindsharpblogs.com, if that would be acceptable to you. Bill English, MCSE, MCT, MVP From: Sara
Sent: Thursday, January 06, 2005 11:16 AM
To: Bill English
Subject: RE: question from an old student I’m hoping that info about the SQL DBs & logfile sizes, maintenance plans, etc. will eventually be documented via KB or whatever for SPS admins (many of whom, like me, are probably NOT SQL DBAs so are somewhat clueless about SQL maintenance… but nevertheless have to deal with it as a part of SPS!). I’m still muddling things through with PSS – it actually appears that, by default, the PROF & SERV DBs are set to SIMPLE RECOVERY in SQL, while the Config & SITE DBs are set to FULL RECOVERY… which means very different backup/recovery scenarios… which gets very confusing as you then end up (assuming you follow SQL guidelines for frequent & separate backup of the logfiles for the FULL RECOVERY DBs) with the SPS DBs backed up to different points in time: all of them backed up to one point via SPS’s tools, and then the FULL RECOVERY DB logfiles backed up to a more current point in time. The whole “logfile backup” thing is also somewhat separate from the “shrink the logfiles to manageable sizes” thing, which was my original question – this task took a series of logfile backups + DBCC SHRINKFILE commands (multiple times) to finally make it work. I’ll be interested to see what the final recommendations end up being (assuming that there ARE some final recommendations!). Take care, Sara From: Bill English [mailto:bill@mindsharp.com]
Sent: Tuesday, January 04, 2005 7:15 PM
To: Sara
Subject: RE: question from an old student Thanks for the update, Sara.  I’ll be sure to include this in my revised courseware. Bill English, MCSE, MCT, MVP From: Sara
Sent: Tuesday, January 04, 2005 4:07 PM
To: Bill English
Subject: RE: question from an old student Hi again, I actually ended up with a free case from Microsoft Premier Support, as this is apparently a current issue being tossed around by the SPS & SQL folks. It appears that:

• Although SPS can back up the SQL databases through the builtin backup/restore utility – it can’t really (supportably) affect the behavior of the SQL databases & transaction logs themselves… this has to be configured within SQL.
  
• If SQL 2000 is installed as recommended for SharePoint (which is essentially with default configuration), then the SQL recovery mode is set to FULL RECOVERY, which logs all transactions so the installation can be recovered to the point of failure (and not just to the last backup)… which is a good thing.
  
• Additionally, with SQL 2000 installed as recommended, transaction logs filesize is set to be unlimited… so it just continues to grow.
  
• By default, auto shrink is NOT enabled… which they say is a good thing as auto shrink can’t be scheduled so can kick on at very inopportune times.
  
• When a full backup is successfully completed, the committed transactions are purged from the logfile; however – the filesize itself remains the same as it doesn’t go through a shrink process; hence – the LDF filesize remains at the largest size it’s ever reached.
  

Current recommendations (which I’m going to try tonight):

• Do NOT turn on Auto Shrink for the databases; instead create a scheduled job using the DBCC SHRINKFILE utility (KB272318) to shrink the logfile sizes (can be scheduled within Enterprise Manager using the Mgmt tools).
  
• Leave the SQL RECOVERY MODE at “Full Recovery” for all SPS SQL databases; this allows for a restore up to the point of failure.
  
• Be sure to create a scheduled job that backs up the Transaction Logs separate from the SharePoint backups; without the Transaction Logs SQL can’t be restored to the point of failure (can be done within Enterprise Manager using the Mgmt tools).
  

I’m going to try the steps tonight & see how it goes… it sounds like some recommendations/solutions will eventually work their way into SPS documentation since there are definitely related performance issues as well as disaster recovery issues. Hope all is well! Thanks, Sara From: Sara
Sent: Monday, January 03, 2005 11:52 AM
To: Bill English (bill@mindsharp.com)
Subject: question from an old student Hi Bill, In all your spare time – if you have a few moments for another question from an old student I’d sure appreciate your input! In looking at the SQL databases housing my SPS2003 data, I see that for the larger databases (typically _SITE) the LDF file (which I believe is the SQL transaction logfile) is quite a bit larger than the MDF file (which I believe is the primary SQL database file). For example, one of my _SITE database files is about 33GB in size; its companion LDF file is about 55GB in size. I’m successfully backing up my SPS sites with the builtin backup/restore tool [still working out issues with the CommVault Qinetix SPS backup piece] – successful backups are logged both by SharePoint as well as on the SQL box itself… and I know the backups are working as I’ve had to move my SPS sites around WAY more often than I’d like via the restore tool. I’ve only recently begun to really take a look at the actual SQL files (had to move the databases to larger partitions) – and I guess I expected the logfiles to be purged once successful backups are done – but it appears that they just keep growing. I got a newsgroup response saying that the behavior of the SQL LDF files actually depends on settings within SQL itself – and that I probably don’t need the logfiles if I’m using SPS backups or some other tool (which I am) – but I’m wondering if there are any SPS recommendations about the SQL logfile settings? I pretty much installed SQL with the default settings other than file locations (but in an active/passive 2-node SQL cluster). I can’t seem to find anything on recommended SQL settings for SharePoint – is there any info you can pass along? (please let me know if this doesn’t make any sense) Thanks, Sara  
http://www.itpub.net/viewthread.php?tid=1048025

如何压缩WSS3.0的ldf日志文件（利用SQL2005 express管理工具压缩WSS3.0日志文件） 因为最近也遇到这个问题，而且是在生产环境中，WSS3.0所在分区只剩不到10%空间，情况十分严重。经过多方查找（本人在SQL方面连个菜鸟都算不上）终于找到了解决的方法。因为是学习多位大侠的方法，所以写在这儿，以供遇到同类问题的朋友参考。
环境：WSS3.0 安装的数据库是SQL简化版
问题：WSS的WSS_Content_log.LDF/ SharePoint_Config_cab00c0b-ee12-434b-aed3-482add86567f_log.LDF两个日志文件分别达到了50G和17G左右，而且还在不断增大。
解决方法：先安装SQL2005 express版的管理工具（在迅雷里找的）安装后将服务器名称设为： \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query
进行连接。
打开数据库目录后，可以发现WSS_Content 和SharePoint_Config_cab00c0b-ee12-434b-aed3-482add86567f两个数据库（我的机器是这样的） 选中某个数据库点右键后，在弹出菜单中选择任务-收缩-文件 在弹出的窗口 文件类型 中选择日志 （下面一步很关键）这个时候选择 “确实”来收缩日志文件实际是收缩不了的。必须采用 一个命令来中断日志，然后才可以收缩成功，我的作法是：在右键菜单里选择 新建查询 输入命令：BACKUP LOG "SharePoint_Config_cab00c0b-ee12-434b-aed3-482add86567f" WITH NO_LOG （这里的数据库名称中有不规则字符，所以要""起来），点执行 完成后，再回到刚才的收缩界面中，即可以收缩成功（注意这里的收缩操作有3种，我自己总结应该用第二种，不过第二种需要指定容量）。
再说一个我自己遇到的问题，因为很菜，所以遇到。
在我收缩WSS3.0 config日志后，在WSS管理中心中，进行备份时无法进行。
也无法删除备份任务，删除时即提示未知错误。管理中心的很多功能也无法执行。用WSS的修复功能也无法解决。重压之下，偶然用到了 收缩窗口中的 收缩操作第二项“在释放未使用的空间前重新组织页”然后重新进行了一次收缩（在未中断日志的情况下），结果全部恢复正常。估计是收缩日志时选了第一项收缩，引起日志错误了。 写这么多，是因为被日志问题困扰了一个多月。希望其它的朋友不必象我这样麻烦