SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 how to shrink db extra size and truncare log file
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

maifs
Yak Posting Veteran

Pakistan
57 Posts

Posted - 07/21/2010 :  06:32:51  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 07/21/2010 :  08:20:17  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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

South Africa
13 Posts

Posted - 07/21/2010 :  09:24:09  Show Profile  Reply with Quote
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

United Kingdom
22403 Posts

Posted - 07/21/2010 :  09:34:31  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 07/21/2010 :  09:48:44  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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

United Kingdom
22403 Posts

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

maifs
Yak Posting Veteran

Pakistan
57 Posts

Posted - 07/22/2010 :  02:34:25  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 07/22/2010 :  03:06:20  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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: http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/ http://www.sqlservercentral.com/articles/64582/

--
Gail Shaw
SQL Server MVP

Edited by - GilaMonster on 07/22/2010 08:10:11
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 07/22/2010 :  03:09:08  Show Profile  Reply with Quote
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

Pakistan
57 Posts

Posted - 07/22/2010 :  08:34:44  Show Profile  Reply with Quote
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

United Kingdom
22403 Posts

Posted - 07/22/2010 :  09:04:36  Show Profile  Reply with Quote
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

United Kingdom
22403 Posts

Posted - 07/22/2010 :  09:10:20  Show Profile  Reply with Quote
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

Pakistan
57 Posts

Posted - 07/22/2010 :  10:05:08  Show Profile  Reply with Quote
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

United Kingdom
22403 Posts

Posted - 07/22/2010 :  11:32:51  Show Profile  Reply with Quote
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

USA
15635 Posts

Posted - 07/22/2010 :  13:04:14  Show Profile  Visit robvolk's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 07/22/2010 :  18:19:30  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000