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=.