need T-SQL code/script for shrinking all user db t-log files

  • Hi. Can anyone provide me a T-SQL code that will do the following? Please ignore the reasons that lead me to shrinking the log files, I'm aware of all the ramifications.

    The code should:

    - determine t-log usage of all user DBs

    - loop through all user databases

    - if t-log usage for given DB is below 20%, then shrink the log file to current size increased by 20% of the original log size

    Example: DB with t-log file size of 100MB. Current t-log usage is 10%, which translates to 10MB. 10% usage is below 20% threshold, hence calculate new size to which the t-log should be shrunk by:

    (t-log_usage/100 * t-log_file_size) + (threshold/100 * t-log_file_size)

    Example: (10/100 * 100) + (20/100 * 100) = 30MB

    Note: This T-SQL code would be executed in a maintenance plan if it makes any difference

    Thank you in advance

  • Use the DBCC SHRINKFILE command.

    You can look up the syntax in SQL Server Books Online.

  • Thanks, but I know it's done via DBCC SHRINKFILE, but I have very little knowledge of T-SQL, so that's why I was asking for the code. For someone knowledgeable, this would be (?) a 5min job, where it would take me hours if not days.

  • Shrinking logs once-off (after bad maintenance) is one thing. Shrinking in a maint plan (scheduled) is a seriously bad idea. The logs are just going to grow again unless you've changed recovery models, log backup frequencies, transaction size or activity, and when the log is out of space and grows, transactions will have to wait for that, and logs must be zero-initialised so that grow can take time.

    It's not the current log usage that you need to take into account if you are insistent on shrinking, it's the max log usage in normal activity. If you check the usage straight after a log backup it will be very low, but if you have data imports or index rebuilds they can take a lot of log space.

    Last big DB I worked on, the log file was 250GB. During the day, usage didn't exceed 5% however at certain points during the overnight data loads we could get that log up past 80% usage.

    Maybe you can share the reasons for this scheduled shrink?

    As for the script:

    Shrinkfile takes two parameters, the name or of the file and the target size. It must be executed from within the context of the DB whose file you're trying to shrink.

    Probably the easiest way is a cursor over sys.master_files, inside that some dynamic SQL that contains a use database and then shrinkfile with a fileID of 2, as the first log file is always file 2.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks, but I know it's done via DBCC SHRINKFILE, but I have very little knowledge of T-SQL, so that's why I was asking for the code. For someone knowledgeable, this would be (?) a 5min job, where it would take me hours if not days.

    Michael has already given u the answer above. check Books online. It will not take u more than 5 minutes.

    Here's the link. You can get the code from here

    http://msdn.microsoft.com/en-us/library/ms189493.aspx

  • It seems my initial post was bit unclear judging by the responses.

    I'm not a DBA, I'm a sysadmin. I have very limited knowledge of T-SQL (can write simple SELECT statements or understand some simple code from other people (something like short and easy cursor)).

    I know DBCC SHRINKFILE and its usage, that's not the problem. What I'm having troubles with is the additional logic I wanted from the script as described in my original post.

    As for the reasons - I'm merely maintaining the server and I've already explained good practices that concern transaction logs to the client (repeatedly), but they still insist (and are dead set on) on performing them regularly. So there's little reason to discuss here something that's beyond my control.

    I'm sorry if I'm asking too much or something that doesn't belong here, but it seemed easier to me to ask the experts than spend hours on the code myself (learning more T-SQL, learning how to implement the logic I described, debug errors etc...).

  • Ok, 2 days and 10 lines of code later I've moved somewhere....

    I have created a table which is a result of DBCC SQLPERF(logspace), so it contains DB names, log usage, log size etc. I've added one more (computed) column, which has either value of 0 or 1, indicating if the t-log for given DB should be shrunk or not.

    Now, can anyone help me - how do I go through all databases (cursor or sp_MSForEachDb I guess) and perform log shrinking using DBCC SHRINKFILE, but only on those t-logs of those databases that have the value of 1 in my custom table?

  • Declare the cursor over your table (which contains just the DBs that need shrinking). Then, for each database in there, use dynamic SQL that does a USE on the database name and a shrinkfile(2,<targetsize>)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • ss-457805 (10/24/2010)


    Thanks, but I know it's done via DBCC SHRINKFILE, but I have very little knowledge of T-SQL, so that's why I was asking for the code. For someone knowledgeable, this would be (?) a 5min job, where it would take me hours if not days.

    Michael has already given u the answer above. check Books online. It will not take u more than 5 minutes.

    Here's the link. You can get the code from here

    http://msdn.microsoft.com/en-us/library/ms189493.aspx

    The DBCC SHRINKFILE part wasn't the problem here. Determining the amount of usage of the log file for each DB and then running DBCC SHRINKFILE on each was the problem.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • [edit] Bad post.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Rambler (10/27/2010)


    Ok, 2 days and 10 lines of code later I've moved somewhere....

    I have created a table which is a result of DBCC SQLPERF(logspace), so it contains DB names, log usage, log size etc. I've added one more (computed) column, which has either value of 0 or 1, indicating if the t-log for given DB should be shrunk or not.

    Now, can anyone help me - how do I go through all databases (cursor or sp_MSForEachDb I guess) and perform log shrinking using DBCC SHRINKFILE, but only on those t-logs of those databases that have the value of 1 in my custom table?

    Piece of cake. Show me your code for that and the table definition and I'll use that to quickly show you how to do this. I can't post code from work so if you catch me during the day, you'll have to wait until I get home. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff, thank you for responding. Here's the code - please bear with me, as I've already stated I'm no T-SQL expert 😉

    use Maint_db

    drop table dba_logusage_temp

    create table dba_logusage_temp

    ( database_name nvarchar(255),

    log_size_MB decimal,

    log_space_used_percentage decimal,

    statuscol char(10),

    log_space_target_size AS convert(integer, (log_space_used_percentage / 100 * log_size_MB) + (0.2 * log_size_MB)),

    shrink AS CAST (case when (log_space_used_percentage < 20 and log_size_MB > 50) then '1' else '0' end as smallint))

    insert into dba_logusage_temp

    execute sp_executesql N'dbcc sqlperf(logspace)'

    DECLARE @db nvarchar(255)

    DECLARE @targetsize integer

    DECLARE @logname nvarchar(255)

    DECLARE c1 CURSOR

    FOR

    SELECT database_name FROM dba_logusage_temp where shrink='1'

    OPEN c1

    FETCH NEXT FROM c1

    INTO @db

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SELECT @targetsize = (SELECT log_space_target_size FROM dba_logusage_temp WHERE database_name = @db)

    print @db

    print @targetsize

    declare @sql nvarchar(255)

    select @sql = 'USE '

    select @sql = @sql + @db

    print @sql

    exec @sql

    select @logname = (select name from sys.database_files where type='1')

    print @logname

    --DBCC SHRINKFILE(@logname,@targetsize)

    FETCH NEXT FROM c1

    INTO @db

    END

    The exec @sql line resulting in USE <db_name> fails with message 'Could not find stored procedure 'USE <db_name>', hence @logname will always return the log name of the Maint_db database.

  • EXEC (@sql)

    That variable need to hold the use <dbName> AND the Shrinkfile, otherwise you'll just be shrinking master's log repeatedly. The USE within the dynamic SQL only has effect within that piece of dynamic SQL, not outside.

    As I said before, you don't need to look up the log file name. Use the file id instead. It's always 2 for the primary log file. Shrinkfile can take the fileid as the first parameter instead of the file name

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks GilaMonster,

    but when I put the Shrinkfile inside the dynamic SQL statement, it complains that I have to define the scalar variable @targetsize. When I put the declaration of that variable inside the dynamic SQL statement, it complains about @db not being declared....

    How to go around this?

  • Concatenate them into the string instead of putting the names. Remember what the scope of the variables is - inside or outside the dynamic SQL, not both. Please go and read up on variable scope in T-SQL.

    Other option sp_executesql with parameters.

    Let me put it this way - if you don't put the shrinkfile inside the dynamic SQL, this will never work. Anything that's inside the dynamic SQL must work as if it were in a separate query window.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 15 posts - 1 through 15 (of 16 total)

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