Ideal Shrink Size

Question

Ideal Shrink Size

shohelr2003

Hall of Fame

Points: 3535
More actions
November 11, 2012 at 10:25 pm

#265287

Dear,
I have a database of 400 tables. I set the default size of database file is 20 GB and log file is 2.5 GB. These files are set to auto grow by 10%. So total allocation size of my database is 22.5 GB.
Now I see that only 48% space of 22.5 GB is available from the allocation.
Hence I want to shrink my transaction log file. Can you tell me the ideal size to which log file can be shrinked?
Please suggest me.
Regards,
Akbar

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

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

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 1

shohelr2003 (11/11/2012)
Dear,
I have a database of 400 tables. I set the default size of database file is 20 GB and log file is 2.5 GB. These files are set to auto grow by 10%. So total allocation size of my database is 22.5 GB.
Now I see that only 48% space of 22.5 GB is available from the allocation.
Hence I want to shrink my transaction log file. Can you tell me the ideal size to which log file can be shrinked?
Please suggest me.
Regards,
Akbar

Except for the growth factor, I'd recommmend leaving things the way they are. 48% of the database being full just means that you've already preallocated space for the next year or two and you shouldn't see a growth for a very long time.

The growth factor should never be set to a percentage, IMHO. Set it to 100MB, 250MB, or 500MB and forget about it.

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

shohelr2003 (11/11/2012)
Hence I want to shrink my transaction log file. Can you tell me the ideal size to which log file can be shrinked?

why you want to shrink it ? unless system is crying with space shortage(though it is not recommended)

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

shohelr2003 Hall of Fame Points: 3535 More actions · Answer 3

Thank you Jeff Moden. I will change the growth strategy from auto growth to MB growth.

shohelr2003 Hall of Fame Points: 3535 More actions · Answer 4

Bhuvnesh (11/12/2012)
why you want to shrink it ? unless system is crying with space shortage(though it is not recommended)

Because free disk space is decreasing day by day. Say, I stored data in D drive and its size is 300 GB. Last week I saw the available free space is 257 GB. Now I see it 240 GB. That's why my desktop based application performs slowly.

I think shrink of log file may improve the situation.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 5

shohelr2003 (11/12/2012)
Because free disk space is decreasing day by day. Say, I stored data in D drive and its size is 300 GB. Last week I saw the available free space is 257 GB. Now I see it 240 GB. That's why my desktop based application performs slowly.
I think shrink of log file may improve the situation.

increment is nature of data. 300 gb according to your stats might not be enough storage ... soonner or later you need to have more space

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Ant-Green SSC Guru Points: 113507 More actions · Answer 6

If disk space is going down but the DB total size is staying at 22.5GB then you have something else which is causing the drive to run out of space.

sanket kokane Hall of Fame Points: 3465 More actions · Answer 7

Rebuilding clustered indexes can free up unused space.

But as you provide default value 20 GB to data file , it will not fall below 20 GB.

-----------------------------------------------------------------------------
संकेत कोकणे

Dennis Post SSCarpal Tunnel Points: 4473 More actions · Answer 8

Assuming you are using Full Recovery, backing up you log regulary will keep it from growing out of hand.

For better, quicker answers on T-SQL questions, read Jeff Moden's suggestions.[/url]

"Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort

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

shohelr2003 (11/12/2012)
Bhuvnesh (11/12/2012)
why you want to shrink it ? unless system is crying with space shortage(though it is not recommended)
Because free disk space is decreasing day by day. Say, I stored data in D drive and its size is 300 GB. Last week I saw the available free space is 257 GB. Now I see it 240 GB. That's why my desktop based application performs slowly.
I think shrink of log file may improve the situation.

No. The size of a database has virtually nothing to do with the performance of an application that uses the database. Shrinking a log file that's only 2.5 GB to start with isn't going to help that, either.

You need to find out what ate 43GB in just a week.

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

sql-lover SSCoach Points: 18530 More actions · Answer 10

shohelr2003 (11/11/2012)
Dear,
I have a database of 400 tables. I set the default size of database file is 20 GB and log file is 2.5 GB. These files are set to auto grow by 10%. So total allocation size of my database is 22.5 GB.
Now I see that only 48% space of 22.5 GB is available from the allocation.
Hence I want to shrink my transaction log file. Can you tell me the ideal size to which log file can be shrinked?
Please suggest me.
Regards,
Akbar

I never use percentage in auto-growth, but fix MB values. The reason is, you may end using much more space than what you actually need.

Now, before shrinking the Tlog file, you need to find out how much you need and how much you are actually using. Tlog files are not like data files. It can be that you need additional Tlog space weekends, during the ReIndex job , because you have one, right? 🙂 , and on that case, it will growth again so any action to shrink it will be a futile one, as it will increase in size during the weekend again.

You can create a SQL job and run it every minute or so for all your database and collect that info. Here's what I use at work:

CREATE TABLE #tFileList

(

databaseName sysname,

logSize decimal(18,5),

logUsed decimal(18,5),

status INT

)

INSERT INTO #tFileList

EXEC spSQLPerf

INSERT INTO logSpaceStats (databaseName, logSize, logUsed)

SELECT databasename, logSize, logUsed

FROM #tFileList

DROP TABLE #tFileList

After a few days, you will be able to see how much space you are using and when the spikes occur.

If you are using the right size, you should never shrink the Tlog or Data files.

shohelr2003 Hall of Fame Points: 3535 More actions · Answer 11

Yesterday I shrunk transaction log file. After shrinking, my drive's free space was increased to 270 GB. But before shrinking it was 240 GB.

So what's the matter?

Ant-Green SSC Guru Points: 113507 More actions · Answer 12

Database in full recovery but not doing regular transaction log backups?

shohelr2003 Hall of Fame Points: 3535 More actions · Answer 13

Actually I do not take transaction log backup. But I take full database backup everyday.

Is there any inconsistency in my backup activities? Why is transaction log backup necessary?

Please let me know.

Thank in advance.

Ant-Green SSC Guru Points: 113507 More actions · Answer 14

Transaction log backups are there so that you can revert to a point in time.

If you dont do transaction log backups you need to ask yourself why is your database in full recovery? Wouldn't it be better in the simple recovery model?

Look at the website below and choose which one of the three recovery models best suits your recoverability needs.

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

Are you not bothered about the data that is inserted/updated/deleted during the day or is it so mission ciritical that you cant live without it.

Then go and read up on transaction log management. There is a whole stairways section on it in the stairways part of the site, but also the following will help

Managing Transaction Logs - http://www.sqlservercentral.com/articles/Administration/64582/

Why is my transaction log full - http://www.sqlservercentral.com/articles/Transaction+Logs/72488/

Also get a copy of the Accidental DBA Guide, (link in my signature and can also be found in the books section of the site) it will help you understand large transaction logs (chapter 8)