Results 1 to 2 of 2
  1. #1
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Transaction Log File Size (SQL 2000)

    I am currently having a problem with a specific SQL Database Log file which is growing at a ridiculous rate. Unbeknown to me the files caused the application it runs to lock today and I was able to remedy it quite quickly by shrinking the files and restarting the services on the server. I have introduced a maintenance routine now and I want to keep an eye on it for a few days.

    I want to be able to see the growth of the Transaction logs over a 24 hour period and am desperately scrabbling around to find a TSQL command that will do this for me. I have been using DBCC SQLPERF(LOGSPACE) for part of the evening which is fine for an overview of the whole server and the databases on it but I specifically want to look at one particular database and its associated files.

    Can anybody think of the correct command if it is around please?
    Jerry

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Transaction Log File Size (SQL 2000)

    OK, sorry. Moment of inspiration, adapting some code I got this:
    <pre>CREATE PROC sp_LogSpace (@dbName sysname = NULL)

    AS
    BEGIN
    SET NOCOUNT ON
    -- get current database name.
    IF (@dbName is null) SET @dbName = DB_NAME()
    -- Create a temporary table to store the DBCC SQLPERF results.
    CREATE TABLE tempLogSpace (dbName sysname,
    LogSize real,
    LogSpacePctUsed real,
    stat int)
    -- Execute the DBCC SQLPERF statement and insert the results into the temporary table.
    INSERT INTO tempLogSpace EXEC ('DBCC SQLPERF (LOGSPACE)')
    -- Output the log size and used log space (in MB).
    SELECT CAST (CONVERT (decimal (8,3), ROUND(LogSize,3)) AS varchar(20)) +
    ' MB' AS LogSize ,
    CAST (CONVERT (decimal (8,3), ROUND (LogSize * LogSpacePctUsed / 100.0,3)) AS varchar(20)) + ' MB' AS LogSpaceUsed

    FROM tempLogSpace
    WHERE LOWER (RTRIM(LTRIM(dbName))) = LOWER (RTRIM(LTRIM(@dbName)))

    -- Get rid of the temporary table.
    DROP TABLE #tempLogSpace
    SET NOCOUNT OFF


    END

    GO
    </pre>


    After that you can use this code in the SQL QA

    use [mydatabasename] exec sp_LogSpace

    With a little adaptation tomorrow I can get this appending to a custom table for monitoring
    Jerry

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •