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

Question

Post reply

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

Rambler

Ten Centuries

Points: 1301
More actions
October 21, 2010 at 9:18 am

#388066

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

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

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

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 1

Use the DBCC SHRINKFILE command.

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

Rambler Ten Centuries Points: 1301 More actions · Answer 2

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.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

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

ss-457805 SSCertifiable Points: 5872 More actions · Answer 4

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

blog: http://sarveshsingh.com Twitter: @sarveshsing

Rambler Ten Centuries Points: 1301 More actions · Answer 5

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...).

Rambler Ten Centuries Points: 1301 More actions · Answer 6

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?

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 7

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 8

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)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 9

[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)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 10

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)
Intro to Tally Tables and Functions

Rambler Ten Centuries Points: 1301 More actions · Answer 11

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.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 12

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

Rambler Ten Centuries Points: 1301 More actions · Answer 13

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?

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 14

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