Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Modifying a SQL 2000 maintenance plan -- and shrinking


Modifying a SQL 2000 maintenance plan -- and shrinking

Author
Message
sqladmin 45377
sqladmin 45377
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 61
I've got a SQL 2000 DB with a plan that was created long ago. On the Optimizations tab it's got "Remove unused space from database files" checked. Also "Shrink database when it grown beyond:" is set to 500MB. ("Amount of free space to remain" is set to 10%.) This DB has been larger than 2GB for years now, so is this shrink attempted every time the plan is executed, or does the plan not even bother?

I know that a Shrink operation is considered a bad thing. If it's been happening to this DB for years, is there something I can do to safely clean up fragmentation that has been created? And I assume I can just UNcheck this "Shrink DB" box now w/o any downside?

Thanks for any info!
Attachments
db optimization settings.png (1 view, 7.00 KB)
george sibbald
george sibbald
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6348 Visits: 13687
the shrink will be running but is probably unable to acheive much on the data file as your database is unlikely to have much free space, the log file could be frequently shrinking and growing though. It is safe to uncheck those boxes and stop running those shrinks, I would just make sure the database file growth factors are set sensibly and keep an eye on any database growth for a while.

run a reindex (optimisation) to clean up any fragmentation.

---------------------------------------------------------------------
sqladmin 45377
sqladmin 45377
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 61
Thanks so much for the info, George. I'll uncheck that shrink option and keep an eye on the size.

I'll have to brush up on how to best adjust the growth settings for this DB. It's about 2.5GB and has grown very slowly over the last few years. Any recommendations?

I'll run a Reindex manually from Enterprise Manager, though if that operation is already running weekly (Sunday early morn, as part of the current Plan), isn't that accomplishing the same thing?

Thanks again.
sanket kokane
sanket kokane
Old Hand
Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)

Group: General Forum Members
Points: 331 Visits: 1019
make sure that you have clustered index on almost each tables .

Rebuilding clustered indexes can free up unused space from your db ..
that still depends upon DB size,as you can not find its much effective on Smaller Db size.

also as per the above person said never shrink your data file,it makes your indexes completely unused

-----------------------------------------------------------------------------
संकेत कोकणे
george sibbald
george sibbald
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6348 Visits: 13687
sqladmin 45377 (11/4/2012)
Thanks so much for the info, George. I'll uncheck that shrink option and keep an eye on the size.

I'll have to brush up on how to best adjust the growth settings for this DB. It's about 2.5GB and has grown very slowly over the last few years. Any recommendations?


For the data file I would say about 100MB, certainly avoid growth factors stored as a percentage. For the log file a growth factor of 64MB. As for what size it should be that depends on a number of factors, recovery mode mainly. what is your log backup strategy? if you take log backups whats the frequency and size of the largest log backup?read this

I'll run a Reindex manually from Enterprise Manager, though if that operation is already running weekly (Sunday early morn, as part of the current Plan), isn't that accomplishing the same thing?



it will be, its just that the work done by the reindex would have been ruined by the shrink, which fragments indexes as it just attempts to move data to the front of the file without taking into account what object that data relates to. Your largest amount of log activity will be when the reindex is run.

---------------------------------------------------------------------
sqladmin 45377
sqladmin 45377
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 61
george sibbald (11/5/2012)

For the data file I would say about 100MB, certainly avoid growth factors stored as a percentage. For the log file a growth factor of 64MB. As for what size it should be that depends on a number of factors, recovery mode mainly. what is your log backup strategy? if you take log backups whats the frequency and size of the largest log backup?read this


The data file and the translog file are set to Auto grow, by 10%; I'll change them and keep an eye on the growth. They're also both set to Unlimited file growth. (This DB was originally set up by the vendor and I figure they just went with the defaults.)

The current Plan backs up this DB every evening at 10:15pm; I've set the translog to get backed up once each hour during the workday (between 9am-6pm). The largest translog backup is just over 2GB, the others of course much smaller, between 2 and 35mb. The DB recovery model is Full.

it will be, its just that the work done by the reindex would have been ruined by the shrink, which fragments indexes as it just attempts to move data to the front of the file without taking into account what object that data relates to. Your largest amount of log activity will be when the reindex is run.


Is the "Reorganize Data..." task on the Optimizations tab the same as reindexing? (It's checked right now and set to "Change free space per page..." to 15%.) I don't see a specific option for reindexing?

Thanks again for your help and suggestions!

Tom
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47343 Visits: 44392
sanket kokane (11/5/2012)
also as per the above person said never shrink your data file,it makes your indexes completely unused


No, it does not. It fragments indexes, doesn't make them unusable, doesn't make SQL scan more or seek less or anything like that.


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


sanket kokane
sanket kokane
Old Hand
Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)

Group: General Forum Members
Points: 331 Visits: 1019
GilaMonster (11/5/2012)
sanket kokane (11/5/2012)
also as per the above person said never shrink your data file,it makes your indexes completely unused


No, it does not. It fragments indexes, doesn't make them unusable, doesn't make SQL scan more or seek less or anything like that.



Point Noted :-)


also couple of good links ,not to shrink your data files

http://www.sqlskills.com/blogs/paul/post/why-you-should-not-shrink-your-data-files.aspx
http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/
http://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/

-----------------------------------------------------------------------------
संकेत कोकणे
george sibbald
george sibbald
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6348 Visits: 13687
unlimited growth is ok when your database growth is fairly stable, again when you are keeping an eye on the database itself just check the free space on the drive.

size your log to handle the largest log backup, I would guess it is already around 2GB? I would presume this log backup is after your reindex job. If this is giving you any problems there are things that can be done, otherwise if all is running ok no harm is being done.

The reorganize data on the optimisations tab is the same as a reindex. It actually runs a DBCC DBREINDEX job.

---------------------------------------------------------------------
sqladmin 45377
sqladmin 45377
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 61
george sibbald (11/6/2012)
size your log to handle the largest log backup, I would guess it is already around 2GB? I would presume this log backup is after your reindex job. If this is giving you any problems there are things that can be done, otherwise if all is running ok no harm is being done.

Yes, there is a very large translog file (2.1GB) in the TRN backup folder, followed by others no larger than 35MB. So I shouldn't see any more of these very large translog files unless/until I run another reindex job?

The reorganize data on the optimisations tab is the same as a reindex. It actually runs a DBCC DBREINDEX job.

If I decide to run a DBCC DBREINDEX job in the future (from inside Query Analyzer I assume?) do I have to put the DB in Single User mode before I do so?

Thanks for your continual assistance, George. And to everyone else, too!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search