Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 how to shrink db extra size and truncare log file

Author  Topic 

maifs
Yak Posting Veteran

57 Posts

Posted - 2010-07-21 : 06:32:51
Hi
i want to truncate log file and remove extra space from my database.
My database version is :

Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) Jul 9 2008 14:43:34 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition on Windows NT 6.0 <X86> (Build 6001: Service Pack 1)

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-07-21 : 08:20:17
Why?

Truncating the log breaks your log chain and prevents point-in-time recovery until another full/diff backup is taken. Is that acceptable?
Shrinking the DB causes massive index fragmentation and will just result in the DB growing again next time someone adds data.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

Llewellyn
Starting Member

13 Posts

Posted - 2010-07-21 : 09:24:09
Alter database <Database Name> SET Recovery simple

use <Database Name>

DBCC SHRINKFILE('<log file name>', 1) -- use logical file name

dbcc shrinkdatabase(<Database Name>)

Alter database <Database Name> SET Recovery full
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2010-07-21 : 09:34:31
I think that is poor advice until the O/P answers Gail's "Why" question, otherwise the O/P may well be at high risk from having no restore-able backup chain and disastrous performance - and, unless its a one-off accidental delete or somesuch that has caused the problem, the size will be back up to the same level in a few days anyway

At the very least a FULL backup should be made immediately after doing the above steps.
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-07-21 : 09:48:44
quote:
Originally posted by Kristen

At the very least a FULL backup should be made immediately after doing the above steps.


And a rebuild of all indexes in the database to remove the fragmentation that the shrink introduced.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2010-07-21 : 12:58:43
hehehe ... and a SHRINK to get rid of the extra working space that Reindex created?
Go to Top of Page

maifs
Yak Posting Veteran

57 Posts

Posted - 2010-07-22 : 02:34:25
Hi ..
Because my client wants performance , he doesn't need extra space and log file.
I am using a main procedure from which i am calling the shrink_db_size procedure and then calling main procedure from front-end (PowerBuilder11.5). It doesn't take even a single minute.
I have already tried below script but all in vain.

Alter database <Database Name> SET Recovery simple

use <Database Name>

DBCC SHRINKFILE('<log file name>', 1) -- use logical file name

dbcc shrinkdatabase(<Database Name>)

Alter database <Database Name> SET Recovery full
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-07-22 : 03:06:20
Shrink does not improve performance. Quite the reverse. The log file is not an optional file. you can't say that you don't need it.

Please read through both of these: [url]http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/[/url] [url]http://www.sqlservercentral.com/articles/64582/[/url]

--
Gail Shaw
SQL Server MVP
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2010-07-22 : 03:09:08
If your client needs performance then you need to provide:

A. Carefully optimised queries using parametrised, cache-able, queries and the indexes appropriate for those queries

B. Housekeeping that rebuilds fragmented indexes and updates statistics regularly (we do that daily but only on tables that are fragmented, many people do it weekly although my view is that if there is a quiet slot overnight why wait until the weekend? - database will be slower on Friday than it was on Monday ...)

C. Decent hardware

Shrinking the database will not help at all, almost certainly it will make performance worse by fragmenting the files / indexes

With regard to log file size:

If the database is in FULL recovery model then schedule the transaction log backups to be every 15 minutes (not hourly or once a day. FWIW we increase TLog backups frequency to every 2 minutes during index rebuilds)

Check for any large transaction log backup files - do they occur at the same time each day? / day of the week? Review what transactions are happening at that time - Scheduled Index rebuild is a common cause, and if that's the case implement a "more intelligent" process that doesn't just rebuild all the indexes on all tables regardless of whether they need doing, or not.

But don't shrink the database or log file - except as a one off operation for a file that has accidentally grown larger than required for normal operating conditions.
Go to Top of Page

maifs
Yak Posting Veteran

57 Posts

Posted - 2010-07-22 : 08:34:44
THanks.
but i think db size would be a major cause of performance in case if db log file size would be more than 39GB and other data files creating 8 to 10 GB extra space which is not required and required db size would be 12GB.So whole size becomes 58GB from which we just need 12GB.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2010-07-22 : 09:04:36
I would be comfortable with Log file for OLTP application which is 130% of Data file size.

We carefully set the size of the files initially to optimise and reduce the chance of excessive numbers of virtual logical units, and to provide sufficient space for database to grow (without automatic file extension which is likely cause fragmentation of the files). Typically our log file will be 200% to 400% of maximum predicted size to ensure it will never need to extend. Database file will be set to have enough spare capacity for at least a year.

That way the DBA can ensure that database filespace is contiguous and not extended like topsy and heavily fragmented (at the filesystem level)

What does it matter if only 50% of the database file is used? (assuming the database is likely to grow over the coming year)

and the Log file needs to be big enough for the largest transaction volume it will ever hold in normal operation.

Sure, if disk space is tight you can cut the filesize to the bone, and that is fine if you want a copy of the database for occasional reference / development; but it is the direct opposite of having a database that performs well (which is your stated aim)

Backup files only contain "used pages", so are going to be the same size, regardless of the physical file size.

I'll say it one more time: if you want a high performing database do NOT use Shrink. Ever. Except after an accidental, one-off, catastrophic growth of the files or to reduce the database size if it has become smaller (e.g. a very significant proportion of the database data has been deleted)
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2010-07-22 : 09:10:20
P.S. Obviously, if your files are large because of some previous accident that allowed them to grow unstopped, or there was some massive deletion/transaction(s) previously, then you may want to tame them, but make sure you leave enough space for reasonable growth and ensure that your LOG file si large enough for the normal worst-case scenario.

If you check the sizes again in a day / week / month and they have grown then you reduced the size too much!!, and you should a) consider if they need expanding further to accommodate short-term future growth and b) defragement the files so that they are contiguous (in the filesystem).

make sure you do a full Reindex after shrinking (as that will probably wreck the index structure and ahve an adverse effect on performance) - and check to make sure the files haven't grown again just from that activity!
Go to Top of Page

maifs
Yak Posting Veteran

57 Posts

Posted - 2010-07-22 : 10:05:08
dear i built the indexes but i can't able to understand that why we can't shrink log file or database?
why?
its a overburden on db. why shouldn't we remove the garbage? i want to do it programmatically.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2010-07-22 : 11:32:51
I can't explain it any better than I have tried to do. If you don't understand then I'm sorry. You can google for other explanations of why Shrink is a bad idea and maybe you will understand those better.

Here is a somewhat over-simplified example:

"i built the indexes"

So ... you reindex them daily, or once a week, so that they are optimum shape for fastest performance.

SQL will make a fresh copy of the index in the spare space at the end of the MDF file (and it will extend the MDF file if it has to). So SQL will make a complete copy of your largest index into space that SQL has carefully organised so that the index pages are all physically next to each other on the disk, and will read fastest possible from disk.

You still have the same amount of data in your database, but now all the space that used to be taken up by the index (lets assume it was scattered around the first half of the file) is now free. That space will be reused when needed, so the physical file will not grow any bigger.

Now you shrink the file, so SQL copies all the index at the end of the file (which is neatly stored on contiguous disk pages, and fully optimised) back to the free pages earlier in the file - so once again the index will be scattered all over the file. You will reduce your database filesize by 50% (say).

Tomorrow you will rebuild the index again, SQL will extend the file (which takes time and interferes with performance) and copy all the index block into a neat, contiguous, block at the end of the file.

You notice that there is spare space in the file, so you shrink it again. Again, SQL copies all the neat index blocks back into whatever gaps it can find.

Mean time the repeated Grow/Shrink has caused the file to be fragmented at the O/S level, so now reading the file is slower than it was before ...
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-07-22 : 13:04:14
Please read this THOROUGHLY and understand it THOROUGHLY before you ask us again about shrinking:

http://sqlskills.com/BLOGS/PAUL/post/Why-you-should-not-shrink-your-data-files.aspx

For a little background, Paul Randal was the manager of the storage engine group at Microsoft. The same team that wrote DBCC SHRINKFILE. AND EVEN HE SAYS DON'T SHRINK. Please keep that in mind.
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-07-22 : 18:19:30
quote:
Originally posted by maifs

but i think db size would be a major cause of performance in case if db log file size would be more than 39GB and other data files creating 8 to 10 GB extra space which is not required and required db size would be 12GB.


You may think so, but you are incorrect. A file with free space does not degrade performance in any way. A well-configured SQL database will always have some free space inside it. It is not a problem. It is not a cause for concern. It is not something that needs to be rectified.

--
Gail Shaw
SQL Server MVP
Go to Top of Page
   

- Advertisement -