Shrink database transaction logs script:

SharePoint tranaction logs can get out of control so if needed run this to truncate all user database logs or just tweek it to select the @sql and run the sql for a particular database.    Very simple.    This will only truncate the none active portion of the transaction log so if you are running this while there is no activity then I recommend setting the initial size to be bigger than the default 1 MB after running the script or uncomment the alter database script below to change the size of the log file.  

Hope this helps. 

-----------------------------------------------------------------------------------------------------------

SET NOCOUNT ON

CREATE TABLE #TransactionLogFiles (

DBName VARCHAR(150),

LogFileName VARCHAR(150)

)

DECLARE DBList CURSOR FOR

SELECT name FROM master..sysdatabases

WHERE name NOT IN ('master','tempdb','model','msdb','distribution')

AND status & 512 = 0

DECLARE @DB VARCHAR(100)

DECLARE @SQL VARCHAR(8000)

OPEN DBList

FETCH NEXT FROM DBList INTO @DB WHILE @@FETCH_STATUS <> -1

BEGIN

SET @SQL = 'USE [' + @DB + '] INSERT INTO #TransactionLogFiles(DBName, LogFileName) SELECT '''+ @DB + ''', RTRIM(Name) FROM sysfiles WHERE FileID=2'

EXEC(@SQL)

FETCH NEXT FROM DBList INTO @DB

END

DEALLOCATE DBList

DECLARE TranLogList CURSOR FOR

SELECT DBName, LogFileName FROM #TransactionLogFiles

DECLARE @LogFile VARCHAR(100)

OPEN TranLogList

FETCH NEXT FROM TranLogList INTO @DB, @LogFile WHILE @@FETCH_STATUS <> -1

BEGIN

SELECT @SQL = 'EXEC sp_dbOption [' + @DB + '], ''trunc. log on chkpt.'', ''True'''

EXEC (@SQL)

SELECT @SQL = 'USE [' + @DB + '] DBCC SHRINKFILE(''' + @LogFile + ''',''truncateonly'') WITH NO_INFOMSGS'

EXEC (@SQL)

SELECT @SQL = 'EXEC sp_dbOption [' + @DB + '], ''trunc. log on chkpt.'', ''False'''

EXEC(@SQL)

/*

if( exists (select [name] from master..sysdatabases where @DB in ('AnalyticsReporting',  'Farm_Config')))

begin

 SELECT @SQL = 'ALTER DATABASE[' + @DB + '] MODIFY FILE ( NAME = ' + @LogFile + ', SIZE = 8000MB ) '

 EXEC(@SQL)

end

*/

FETCH NEXT FROM TranLogList INTO @DB, @LogFile

END

DEALLOCATE TranLogList

DROP TABLE #TransactionLogFiles

_thank you for the script to =JR=.  

Fix DCOM errors on Sharepoint Servers

The registry entries for them are owned by the trusted installer by default.  In order to change the permissions to give the service accounts local activation rights, you will need to change the owner to the local administrator.