SQL Server Performance

which db holds all the log files

Discussion in 'General DBA Questions' started by aaronsandy, Apr 7, 2005.

  1. aaronsandy New Member

    Hi!
    I am modifing a script which will truncate the log files of all the databases on the server.This script will first open a cursor consisting of dbname,backup the log.and shrink the log file.But I am unable to write the correct shrinkfile command ,because i am not getting all the logfile name/path in any db.So pls tell me how to get the path of all log files and use it in this script
    Here is the script
    USE ADMIN
    GO
    CREATE PROCEDURE usp_truncatelog
    AS
    Set quoted_identifier off
    DECLARE @dbname varchar(30)
    DECLARE @dbname_header varchar(60)
    DECLARE dbnames_cursor CURSOR FOR SELECT name FROM master..sysdatabases
    WHERE name not in ('master', 'msdb','pubs', 'tempdb', 'model', 'northwind')

    OPEN dbnames_cursor

    FETCH NEXT FROM dbnames_cursor INTO @dbname

    WHILE (@@fetch_status <> -1)
    BEGIN
    IF (@@fetch_status = -2)
    BEGIN
    FETCH NEXT FROM dbnames_cursor INTO @dbname
    CONTINUE
    END
    SELECT @dbname_header = "Database " + RTRIM(UPPER(@dbname))
    PRINT @dbname_header
    EXEC("BACKUP LOG " + @dbname + " WITH TRUNCATE_ONLY")
    --EXEC("DBCC SHRINKFILE ("logfilename",1,TRUNCATEONLY)")
    ----The above statement need to be modified for logfile name corresp to the currently -------pointed db by the cursor
    FETCH NEXT FROM dbnames_cursor INTO @dbname
    END

    DEALLOCATE dbnames_cursor

  2. satya Moderator

    A database has at least one data file and one transaction log file. Data and transaction log information is never mixed on the same file, and individual files are used by only one database.

    To get the log file name you execution SP_HELPFILE.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. ranjitjain New Member

    select filename from sysfiles where fileid=2
  4. ranjitjain New Member

    declare @dbname as varchar(25)
    declare @sqlstr as nvarchar(4000)
    declare cur_cursor cursor for
    select name from master..sysdatabases where dbid>4
    open cur_cursor
    fetch next from cur_cursor into @dbname
    while @@fetch_status=0
    begin
    select @sqlstr='SELECT filename from '+@dbname+'..sysfiles where fileid=2'
    execute sp_executesql @sqlstr
    print @sqlstr
    fetch next from cur_cursor into @dbname
    end
    close cur_cursor
    deallocate cur_cursor
    go

Share This Page