Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2015
    Posts
    1

    Unanswered: Creating a Template DB for Test

    Hi, I am creating a template DB with Master Data to be reused for testing. I have deleted data from the transaction tables. I would also like to clear the transaction log. I used the command

    dump transaction databasename with truncate_only

    However, the current transaction log utilization (using the script below) remains the same. It was 96% before and after I ran the dump transaction as above and sp_spaceused shows the same values too (no change in running the dump transaction command).

    select db_name(d.dbid) as db_name,
    ceiling(sum(case when u.segmap != 4 then u.size/1048576.*@@maxpagesize end )) as data_size,
    ceiling(sum(case when u.segmap != 4 then size - curunreservedpgs(u.dbid, u.lstart, u.unreservedpgs) end)/1048576.*@@maxpagesize) as data_used,
    ceiling(100 * (1 - 1.0 * sum(case when u.segmap != 4 then curunreservedpgs(u.dbid, u.lstart, u.unreservedpgs) end) / sum(case when u.segmap != 4 then u.size end))) as data_used_pct,
    ceiling(sum(case when u.segmap = 4 then u.size/1048576.*@@maxpagesize end)) as log_size,
    ceiling(sum(case when u.segmap = 4 then u.size/1048576.*@@maxpagesize end) - lct_admin("logsegment_freepages",d.dbid)/1048576.*@@maxpagesize) as log_used,
    ceiling(100 * (1 - 1.0 * lct_admin("logsegment_freepages",d.dbid) / sum(case when u.segmap in (4, 7) then u.size end))) as log_used_pct
    from master..sysdatabases d, master..sysusages u
    where u.dbid = d.dbid and d.status != 256
    group by d.dbid
    order by db_name(d.dbid)


    How do I clear the transaction log and verify that there has been space freed up?

  2. #2
    Join Date
    Feb 2012
    Posts
    133
    for testing environment only
    try dump transaction dbname with no_log.
    see if if clears the log.

    sp_helpdb dbname --log only free kybtes
    go

    or

    use dbname
    go
    sp_helpsegment logsegment --will show log free pages and log used pages.
    go

Posting Permissions

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