DBCC Shrinkfile

Question

Post reply

DBCC Shrinkfile

SQLSACT

SSC-Insane

Points: 21773
More actions
November 16, 2011 at 7:36 am

#390871

Hi Guys
Is there a way that I can use DBCC shrinkfile to shrink the transaction log to a certain Percentage and not a MB amount.
If this cannot be done with DBCC shrinkfile
Is there a way I can shrink the transaction log to a certain percentage.
Thanks

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

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

Devendrakumar Shirbad SSC-Forever Points: 42493 More actions · Answer 1

It's possible with DBCC SHRINKDATABASE.

DBCC SHRINKDATABASE

( database_name | database_id | 0

[ , target_percent ]

[ , { NOTRUNCATE | TRUNCATEONLY } ]

)

[ WITH NO_INFOMSGS ]

SQLSACT SSC-Insane Points: 21773 More actions · Answer 2

Dev (11/16/2011)
It's possible with DBCC SHRINKDATABASE.
DBCC SHRINKDATABASE
( database_name | database_id | 0
[ , target_percent ]
[ , { NOTRUNCATE | TRUNCATEONLY } ]
)
[ WITH NO_INFOMSGS ]

Thanks

I'm looking for something that will only shrink the log file and not the datafiles as well.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Get the log size, do the math, then run the command =>

DECLARE @a INT

SET @a =10000000

DBCC SHRINKFILE(2, @a)

SQLSACT SSC-Insane Points: 21773 More actions · Answer 4

Ninja's_RGR'us (11/16/2011)
Get the log size, do the math, then run the command =>
DECLARE @a INT
SET @a =10000000
DBCC SHRINKFILE(2, @a)

How can I get this done dynamically?

For instance: I need it to check the size, check the free space, if the free space > a certain % - it needs to shrink the file and release it to the OS.

Is that possible?

Thanks

Devendrakumar Shirbad SSC-Forever Points: 42493 More actions · Answer 5

I guess you are trying to generalize the shrink operation for all database log file for some configurable number say 15%.

It's not available as option by DBCC SHRINKFILE. But can you calculate it with T-SQL & store it in temp table for all DBs. Then loop through it and generate the DBCC command for all, like Dynamic Query.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Yes but that's a little pointless.

If you have runaway jobs or queries bloating the logs you need to know about them. Then fix the query, then resize the log.

It's not something you should do without knowing what's going on.

DBCC SQLPERF(LOGSPACE). Insert that into a table and play with it.

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

derekr 43208 (11/16/2011)
For instance: I need it to check the size, check the free space, if the free space > a certain % - it needs to shrink the file and release it to the OS.

One question: Why?

Free space in the log does not cause problems. Growing the log however is an IO intensive operation that you don't want happening too often. Then there's the VLF problem if the autogrow settings are too low.

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

SQLSACT SSC-Insane Points: 21773 More actions · Answer 8

Ninja's_RGR'us (11/16/2011)
Yes but that's a little pointless.
If you have runaway jobs or queries bloating the logs you need to know about them. Then fix the query, then resize the log.
It's not something you should do without knowing what's going on.
DBCC SQLPERF(LOGSPACE). Insert that into a table and play with it.

I've done this

CREATE TABLE #test

(

DBNAME sysname,

[logSize MB] decimal(18,2),

[logUsed %] decimal(18,2),

status INT

)

INSERT INTO #test

EXEC('dbcc sqlperf(logspace)')

select DBNAME, [logSize MB], [logUsed %]

,100 - [logUsed %] as "Log Free %"

from #test

order by DBNAME

drop table #test

How would I make this dynamically shrink the log if the space free is more than 80% for example.

Thanks

SQLSACT SSC-Insane Points: 21773 More actions · Answer 9

GilaMonster (11/16/2011)
derekr 43208 (11/16/2011)
For instance: I need it to check the size, check the free space, if the free space > a certain % - it needs to shrink the file and release it to the OS.
One question: Why?
Free space in the log does not cause problems. Growing the log however is an IO intensive operation that you don't want happening too often. Then there's the VLF problem if the autogrow settings are too low.

Thanks

I don't want too much space in the log if it can be releases to the OS.

Devendrakumar Shirbad SSC-Forever Points: 42493 More actions · Answer 10

I don't want too much space in the log if it can be releases to the OS.

Are you planning to share disk (that contain Log Files) with some other applications? It’s not a good idea.

SQLSACT SSC-Insane Points: 21773 More actions · Answer 11

Dev (11/16/2011)
I don't want too much space in the log if it can be releases to the OS.
Are you planning to share disk (that contain Log Files) with some other applications? It’s not a good idea.

No, drives allocated to SQL Server are not shared with anything else

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

derekr 43208 (11/16/2011)
GilaMonster (11/16/2011)
derekr 43208 (11/16/2011)
For instance: I need it to check the size, check the free space, if the free space > a certain % - it needs to shrink the file and release it to the OS.
One question: Why?
Free space in the log does not cause problems. Growing the log however is an IO intensive operation that you don't want happening too often. Then there's the VLF problem if the autogrow settings are too low.
Thanks
I don't want too much space in the log if it can be releases to the OS.

But why? Free space in the log does not cause performance problems. Growing the log if it's too small can. What's the point of seeing free space in the file system instead of free space in the log file?

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

SQLSACT SSC-Insane Points: 21773 More actions · Answer 13

GilaMonster (11/17/2011)
derekr 43208 (11/16/2011)
GilaMonster (11/16/2011)
derekr 43208 (11/16/2011)
For instance: I need it to check the size, check the free space, if the free space > a certain % - it needs to shrink the file and release it to the OS.
One question: Why?
Free space in the log does not cause problems. Growing the log however is an IO intensive operation that you don't want happening too often. Then there's the VLF problem if the autogrow settings are too low.
Thanks
I don't want too much space in the log if it can be releases to the OS.
But why? Free space in the log does not cause performance problems. Growing the log if it's too small can. What's the point of seeing free space in the file system instead of free space in the log file?

Ok, I though that there was a performance hit with too much extra space in the log.....

Thanks

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 14

derekr 43208 (11/17/2011)
Ok, I though that there was a performance hit with too much extra space in the log.....
Thanks

No there are issues when you have too many (or too few) VLFs

There are 2-3 posts linked in that one => http://sqlskills.com/blogs/kimberly/post/8-Steps-to-better-Transaction-Log-throughput.aspx