Log File Details

  • I need to figure out a query which will give the used space and freee space on the log files ( dbcc sqlperf(logspace) wont do, since there are more than 1 logfiles, and this statement gives details only as a total). Please suggest on this.

    Thanks in advance!! 🙂

  • try this:

    select * from sysfiles

    Why do you have more than 1 logfile? It won't acclerate your database ....

    Wilfred
    The best things in life are the simple things

  • I do not get the corect nos.. can u pls let me know which are the fields , and do I need to do any conversions, like conversion from extents to kb/ mb or so...

    Thank u.... 🙂

  • Sorry, I posted the wrong answer. Try this:

    print '*** Creating objecten voor DBSize'

    print ''

    if not exists (select 'yes' from sysobjects where name = 'dbsize')

    BEGIN

    CREATE TABLE DBSize(

    [Servername] sysname DEFAULT @@SERVERNAME

    , [Servicename] sysname DEFAULT @@SERVICENAME

    , [databaseid] int NOT NULL

    , [databasesize] bigint null

    , [logsize] bigint null

    , [rows] bigint NULL

    , [reserved] BIGINT NULL

    , [data] bigint NULL

    , [index_size] bigint NULL

    , [unused] bigint NULL

    , [timestamps] SMALLDATETIME NOT NULL DEFAULT GETDATE()

    )

    CREATE UNIQUE INDEX IDX__DBSIZE__01 ON dbsize(servername,servicename, databaseid, timestamps) WITH IGNORE_DUP_KEY

    END

    GO

    if exists (select 'yes' from sysobjects where name = 'vw_dbsize')

    DROP VIEW vw_DBSize

    GO

    CREATE VIEW vw_dbsize as

    SELECT *

    from dbsize

    where timestamps = (select max(timestamps) from DBSize)

    GO

    if exists (select 'yes' from sysobjects where name = 'usp_collectDBsize')

    DROP PROCEDURE usp_collectDBSize

    GO

    CREATE PROCEDURE USP_CollectDBSize @verbose bit = 0, @debug bit = 0 AS

    /*

    Verzamelt statistieken over de database.

    Deze procedure is een afgeleide van de sp_spaceused procedure

    */

    SET NOCOUNT ON

    BEGIN

    DECLARE @SQLCmd nvarchar(2048)

    if @verbose = 1 or @debug = 1

    if LEFT(CAST(SERVERPROPERTY('productversion') AS CHAR),1) = '8'

    print 'Using the MSSQL 2000 method'

    else

    print 'Using the MSSQL 2005 method'

    if LEFT(CAST(SERVERPROPERTY('productversion') AS CHAR),1) = '8'

    set @SQLCMD = N'

    USE [?]

    GO

    DECLARE @rows bigint

    DECLARE @reserved bigint

    DECLARE @data bigint

    DECLARE @index_size bigint

    DECLARE @unused bigint

    SELECT @dbsize = sum(convert(bigint, case when status & 64 = 0 then size else 0 end))

    , @logsize = sum(convert(bigint, case when status & 64 <> 0 then size else 0 end))

    FROM dbo.sysfiles

    SELECT @rows = sum(rows) FROM sysindexes WHERE indid < 2

    SELECT @reserved = sum(reserved) * 8 FROM sysindexes WHERE indid in (0, 1, 255)

    SELECT @data = (SELECT sum(dpages) FROM sysindexes WHERE indid < 2 ) * 8 + (SELECT sum(used)FROM sysindexes WHERE indid = 255) * 8

    SELECT @index_size = (SELECT sum(used) FROM sysindexes WHERE indid in (0, 1, 255)) * 8 - @data

    SELECT @unused= @reserved - (SELECT sum(used) FROM sysindexes WHERE indid in (0, 1, 255)) * 8

    INSERT INTO ' + DB_NAME() + '.dbo.DBSize([databaseid],[databasesize],[logsize],[rows],[reserved],[data],[index_size],[unused])

    VALUES (DB_ID(),@dbsize, @logsize,@rows,@reserved,@data,@index_size,@unused)'

    else

    set @SQLCMD = N'

    USE [?]

    DECLARE @dbsize bigint

    DECLARE @logsize bigint

    DECLARE @rows bigint

    DECLARE @reservedpages bigint

    DECLARE @pages bigint

    DECLARE @Usedpages bigint

    DECLARE @index_size bigint

    DECLARE @unused bigint

    SELECT @dbsize = sum(convert(bigint, case when status & 64 = 0 then size else 0 end))

    , @logsize = sum(convert(bigint, case when status & 64 <> 0 then size else 0 end))

    FROM dbo.sysfiles

    select @reservedpages = sum(a.total_pages) *8,

    @usedpages = sum(a.used_pages) *8,

    @pages = sum(

    CASE

    -- XML-Index and FT-Index-Docid is not considered "data", but is part of "index_size"

    When it.internal_type IN (202,204) Then 0

    When a.type <> 1 Then a.used_pages

    When p.index_id < 2 Then a.data_pages

    Else 0

    END

    ) *8

    from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id

    left join sys.internal_tables it on p.object_id = it.object_id

    SELECT @rows = sum(rows) FROM sysindexes WHERE indid < 2

    INSERT INTO ' + DB_NAME() + '.dbo.DBSize([databaseid],[databasesize],[logsize],[rows],[reserved],[data],[index_size],[unused])

    VALUES (DB_ID(),@dbsize, @logsize,@rows,@reservedpages,@pages,@usedpages-@pages,@reservedpages-@usedpages)'

    if @debug = 0

    EXEC sp_msforeachdb @SQLCmd

    else

    print @SQLCmd

    END

    GO

    print '*** Executing usp_collectDBsize'

    print ''

    exec usp_collectdbsize

    GO

    print 'ok.'

    Wilfred
    The best things in life are the simple things

  • Hey thanks a lot... 🙂 .. that was Gr8

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply