November 2, 2012 at 6:16 pm
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!
November 3, 2012 at 10:16 am
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.
---------------------------------------------------------------------
November 4, 2012 at 3:37 pm
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.
November 5, 2012 at 12:54 am
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
-----------------------------------------------------------------------------
संकेत कोकणे
November 5, 2012 at 4:52 am
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.
---------------------------------------------------------------------
November 5, 2012 at 4:01 pm
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
November 5, 2012 at 8:32 pm
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
November 6, 2012 at 12:14 am
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 unusedNo, 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/
-----------------------------------------------------------------------------
संकेत कोकणे
November 6, 2012 at 2:25 am
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.
---------------------------------------------------------------------
November 7, 2012 at 4:02 pm
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!
November 8, 2012 at 12:37 am
For shrinking,fragmentation and forecast the future database space growth u should use a dedicated tool that will fulfill your requirements regarding SQL SERVER STORAGE .....there are many tool fall in this category like Lepide SQL Storage Manager ,idera ,quest software sql tool ..trial version of these tool are available free for 30 days
November 8, 2012 at 12:59 am
sqladmin 45377 (11/7/2012)
So I shouldn't see any more of these very large translog files unless/until I run another reindex job?
Reindexing should be one of your regular maintenance tasks
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?
No.
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
November 8, 2012 at 4:08 am
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?
reindex is often the cause of the largest tran log backup, check the time of this backup relative to you reindex job to confirm this. If its not giving you space issues don't worry about it, its normal behaviour.
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?
as Gail said, no, but it is an intrusive process that will block user queries so be careful when you run it, best to leave it to your out of hours maintenance plan.
....and you dont need to buy any tools for any of this stuff
---------------------------------------------------------------------
November 9, 2012 at 5:55 pm
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?
as Gail said, no, but it is an intrusive process that will block user queries so be careful when you run it, best to leave it to your out of hours maintenance plan.
....and you dont need to buy any tools for any of this stuff
Thanks so much to you and to Gail for all the helpful info. It really helps.
I've been trying to locate a performance hit that's been slowing down my users during the translog backups and I think I've found the cause. On the Integrity tab of the plan, "Check database integrity" and "Include indexes" are checked *along with* "Perform tests before backing up the db or translog". Looking at the translog backup log I can see that it takes about 4 min for this DB check to run. So is there a way to have the integrity check run only for the DB and not the translog backups? Or should I create separate plans for each so I can avoid the 4-minute drag on resources?
Maybe the simplest thing to do is just to Uncheck "Perform these tests..." and have the checks run only with the weekly reindex job?
Sorry for the long post. Hope it makes sense.
Thanks.
November 9, 2012 at 6:03 pm
You should have two separate plans, one that does full backups and runs however often is required for full backups and a second plan that does log backups and runs however often is required for log backups, usually a lot more frequently than full backups.
The integrity check (DBCC CheckDB) should be run before the full backup preferably.
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
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply