GeekZilla
Truncate all Logs on a SQL Server with TSQL
Hers's some T-SQL for truncating all logs on a Microsoft SQL Server:
DECLARE @DBName varchar(255) DECLARE @LogName varchar(255) DECLARE @DATABASES_Fetch int DECLARE DATABASES_CURSOR CURSOR FOR select distinct name, db_name(s_mf.database_id) dbName from sys.master_files s_mf where s_mf.state = 0 and -- ONLINE has_dbaccess(db_name(s_mf.database_id)) = 1 -- Only look at databases to which we have access and db_name(s_mf.database_id) not in ('Master','tempdb','model') and db_name(s_mf.database_id) not like 'MSDB%' and db_name(s_mf.database_id) not like 'Report%' and type=1 order by db_name(s_mf.database_id) OPEN DATABASES_CURSOR FETCH NEXT FROM DATABASES_CURSOR INTO @LogName, @DBName WHILE @@FETCH_STATUS = 0 BEGIN exec ('USE [' + @DBName + '] ; DBCC SHRINKFILE (N''' + @LogName + ''' , 0, TRUNCATEONLY)') FETCH NEXT FROM DATABASES_CURSOR INTO @LogName, @DBName END CLOSE DATABASES_CURSOR DEALLOCATE DATABASES_CURSOR
Author Paul Hayman
Paul is the COO of kwiboo ltd and has more than 20 years IT consultancy experience. He has consulted for a number of blue chip companies and has been exposed to the folowing sectors: Utilities, Telecommunications, Insurance, Media, Investment Banking, Leisure, Legal, CRM, Pharmaceuticals, Interactive Gaming, Mobile Communications, Online Services.
Paul is the COO and co-founder of kwiboo (http://www.kwiboo.com/) and is also the creator of GeekZilla.
Comments
Sadegh said:
Excellent.. helped me very much