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

Add Comment

Latest Articles