May 23, 2011 at 9:24 pm
Hi all,
I would like to ask a question. In sql server 2000, we can right click the database and choose shrink database, and we aslo can use command dbcc shrinkdatabase to run in query analyze to shrink database. May i know this two function what is different??
May 23, 2011 at 11:24 pm
If you right click -> shrink database then Enterprise Manager runs the DBCC ShrinkDatabase command.
Be aware, shrink causes massive index fragmentation. Not something that should be scheduled or run regularly.
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
May 26, 2011 at 6:35 pm
I am looking after some inherited databases, and have noticed that there are automated shrinks in the overnight scripts. Now I know they are bad, and I want to remove them, but what's the best thing to do?
I know that my largest database has log files that keep filling up, and currently I have put it into simple recovery to stop the logs growing. Though it's horrible shrink does give me some disk space back.
I know I shouldn't use shrink but what's best practice - starting from a thoroughly fragmented database I'd imagine!
cheers
Matt
May 26, 2011 at 7:11 pm
Matthew Spinks (5/26/2011)
I am looking after some inherited databases, and have noticed that there are automated shrinks in the overnight scripts. Now I know they are bad, and I want to remove them, but what's the best thing to do?I know that my largest database has log files that keep filling up, and currently I have put it into simple recovery to stop the logs growing. Though it's horrible shrink does give me some disk space back.
I know I shouldn't use shrink but what's best practice - starting from a thoroughly fragmented database I'd imagine!
cheers
Matt
If you log files are growing like that, then you probably are not backing up the transaction log. If you need to be able to restore to a point in time after a disaster, then you need the database in full recovery model and frequent (every 15 minutes would be ideal) transaction log backups.
If you can afford up to 36 hours of data loss - then keep the database in simple recovery model and perform daily full backups.
Note: the amount of data loss is dependent on how often you copy the backup files to tape, when that copy happens, and when that tape is shipped offsite. If that isn't done daily, then you could lose even more data.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 26, 2011 at 11:22 pm
Jeffrey
The database is normally in full recovery mode and the transaction logs are backed up every 2 hours. A daily full backup happens each evening too.
What I need to know is how to keep my disk space and have defragmented indexes. Should I do a log back up every 15 mins, and rebuild indexes? how do I regain freagmented table spaces/files. I want to get to the right place of well indexed, unfragmented tables and indexes, but am unsure how to keep disk space when the machine is under load.
When I run a maintenance job to rebuild indexes, verify database integrity, etc. that's when my logs fill!
May 27, 2011 at 2:07 am
Matthew Spinks (5/26/2011)
When I run a maintenance job to rebuild indexes, verify database integrity, etc. that's when my logs fill!
Yeah, they will. Rebuild all indexes, in full recovery, needs something like 1.2-1.5x the size of the data for the log.
Two things.
Get a custom index rebuild that only rebuilds the ones that need it. (there's one at http://www.sqlfool.com/)
Switch to bulk-logged for the duration of the index rebuild. You log backups will be the same size, or even larger, the log file won't grow as much.
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
May 27, 2011 at 10:06 am
In addition to Gail's recommendations - another option is just to leave the size alone. If you are going to rebuild all indexes - then you need the space. Let it grow and don't shrink it again.
The question you need to ask is what else is going to use that space. Why worry about using disk space when that is what you have created it for. If you are using that same disk space for something else - move that something else off that drive and don't risk not having the space available to grow the logs when they actually do need to grow.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply