Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Modifying a SQL 2000 maintenance plan -- and shrinking Expand / Collapse
Author
Message
Posted Friday, November 2, 2012 6:16 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, June 30, 2013 9:59 PM
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!


  Post Attachments 
db optimization settings.png (1 view, 7.61 KB)
Post #1380648
Posted Saturday, November 3, 2012 10:16 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:19 PM
Points: 5,974, Visits: 12,874
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.


---------------------------------------------------------------------

Post #1380722
Posted Sunday, November 4, 2012 3:37 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, June 30, 2013 9:59 PM
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.
Post #1380841
Posted Monday, November 5, 2012 12:54 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 1, 2013 10:17 AM
Points: 323, Visits: 984
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


-----------------------------------------------------------------------------
संकेत कोकणे
Post #1380902
Posted Monday, November 5, 2012 4:52 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:19 PM
Points: 5,974, Visits: 12,874
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.


---------------------------------------------------------------------

Post #1380988
Posted Monday, November 5, 2012 4:01 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, June 30, 2013 9:59 PM
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
Post #1381298
Posted Monday, November 5, 2012 8:32 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:54 PM
Points: 42,434, Visits: 35,488
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 2008, MVP
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

Post #1381352
Posted Tuesday, November 6, 2012 12:14 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 1, 2013 10:17 AM
Points: 323, Visits: 984
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/


-----------------------------------------------------------------------------
संकेत कोकणे
Post #1381409
Posted Tuesday, November 6, 2012 2:25 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:19 PM
Points: 5,974, Visits: 12,874
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.


---------------------------------------------------------------------

Post #1381456
Posted Wednesday, November 7, 2012 4:02 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, June 30, 2013 9:59 PM
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!

Post #1382212
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse