I have read that having databases auto shrink can cause performance
problems, timeouts and fragmentation. I was wondering if using the "Remove
unused space from database files" from the Optimizations tab in Database
Maintenance Plans had the same affect. Should I avaoid using those two
settings?
ThanksYes (although maintenance plans are usually scheduled out of core business
hours). Have a look at http://www.karaszi.com/sqlserver/info_dont_shrink.asp
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Alison Blackburn" <alison.blackburn@.thalerus.com> wrote in message
news:Ohn3boEFFHA.2756@.TK2MSFTNGP15.phx.gbl...
>I have read that having databases auto shrink can cause performance
>problems, timeouts and fragmentation. I was wondering if using the "Remove
>unused space from database files" from the Optimizations tab in Database
>Maintenance Plans had the same affect. Should I avaoid using those two
>settings?
> Thanks
>
Showing posts with label unused. Show all posts
Showing posts with label unused. Show all posts
Sunday, March 11, 2012
Wednesday, March 7, 2012
Database Maintenance
I have a maintenance plan set up to:
Remove unused space from database files
Shrink database when it grows beyond: 50 MB
Amount of free space to remain after shrink = 10%
Does this effect log files or just data files, or both?
Message posted via http://www.sqlmonster.com
both.
Many DBA's would not recommend shrinking the Database unless you
specifically are running into storage space issues.
My personal recommendation is that you disable this feature.
Greg Jackson
PDX, Oregon
|||The maintenance plan will do a dbcc shrinkdatabase, not a
dbcc shrinkfile. As Greg posted, you really don't need this
in a regular maintenance plan. It's better to just size your
files appropriately and let them have the space they may
need for your normal operations. If they grow and you shrink
them and then they grow again right after you shrink them,
you just waste resources. Also, I think the maintenance plan
defaults to where you do a dbreindex and then a
shrinkdatabase. In that scenario, you likely just
reintroduce the fragmentation you cleaned up with the
dbreindex.
The following article on Tibor Karaszi's site had some
additional information on shrinking:
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
-Sue
On Tue, 08 Feb 2005 22:21:42 GMT, "Robert Richards via
SQLMonster.com" <forum@.SQLMonster.com> wrote:
>I have a maintenance plan set up to:
>Remove unused space from database files
> Shrink database when it grows beyond: 50 MB
> Amount of free space to remain after shrink = 10%
>Does this effect log files or just data files, or both?
|||I have applied "autoshrink" to many of my databases specifically=
to try and keep the size of the logs down, and it seems to help.=
I had much trouble with logs growinbg larger than the database,=
but this has improved with autoshrink and nightly log backups.
> I have a maintenance plan set up to:
> Remove unused space from database files
> Shrink database when it grows beyond: 50 MB
> Amount of free space to remain after shrink =3D 10%
> Does this effect log files or just data files, or both?
> --
> Message posted via http://www.sqlmonster.com
User submitted from AEWNET (http://www.aewnet.com/)
|||http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"engel1" <engel1@.aew_nospam.com> wrote in message news:OE6hmHxEFHA.4004@.tk2msftngp13.phx.gbl...
I have applied "autoshrink" to many of my databases specifically to try and keep the size of the
logs down, and it seems to help. I had much trouble with logs growinbg larger than the database, but
this has improved with autoshrink and nightly log backups.
> I have a maintenance plan set up to:
> Remove unused space from database files
> Shrink database when it grows beyond: 50 MB
> Amount of free space to remain after shrink = 10%
> Does this effect log files or just data files, or both?
> --
> Message posted via http://www.sqlmonster.com
User submitted from AEWNET (http://www.aewnet.com/)
Remove unused space from database files
Shrink database when it grows beyond: 50 MB
Amount of free space to remain after shrink = 10%
Does this effect log files or just data files, or both?
Message posted via http://www.sqlmonster.com
both.
Many DBA's would not recommend shrinking the Database unless you
specifically are running into storage space issues.
My personal recommendation is that you disable this feature.
Greg Jackson
PDX, Oregon
|||The maintenance plan will do a dbcc shrinkdatabase, not a
dbcc shrinkfile. As Greg posted, you really don't need this
in a regular maintenance plan. It's better to just size your
files appropriately and let them have the space they may
need for your normal operations. If they grow and you shrink
them and then they grow again right after you shrink them,
you just waste resources. Also, I think the maintenance plan
defaults to where you do a dbreindex and then a
shrinkdatabase. In that scenario, you likely just
reintroduce the fragmentation you cleaned up with the
dbreindex.
The following article on Tibor Karaszi's site had some
additional information on shrinking:
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
-Sue
On Tue, 08 Feb 2005 22:21:42 GMT, "Robert Richards via
SQLMonster.com" <forum@.SQLMonster.com> wrote:
>I have a maintenance plan set up to:
>Remove unused space from database files
> Shrink database when it grows beyond: 50 MB
> Amount of free space to remain after shrink = 10%
>Does this effect log files or just data files, or both?
|||I have applied "autoshrink" to many of my databases specifically=
to try and keep the size of the logs down, and it seems to help.=
I had much trouble with logs growinbg larger than the database,=
but this has improved with autoshrink and nightly log backups.
> I have a maintenance plan set up to:
> Remove unused space from database files
> Shrink database when it grows beyond: 50 MB
> Amount of free space to remain after shrink =3D 10%
> Does this effect log files or just data files, or both?
> --
> Message posted via http://www.sqlmonster.com
User submitted from AEWNET (http://www.aewnet.com/)
|||http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"engel1" <engel1@.aew_nospam.com> wrote in message news:OE6hmHxEFHA.4004@.tk2msftngp13.phx.gbl...
I have applied "autoshrink" to many of my databases specifically to try and keep the size of the
logs down, and it seems to help. I had much trouble with logs growinbg larger than the database, but
this has improved with autoshrink and nightly log backups.
> I have a maintenance plan set up to:
> Remove unused space from database files
> Shrink database when it grows beyond: 50 MB
> Amount of free space to remain after shrink = 10%
> Does this effect log files or just data files, or both?
> --
> Message posted via http://www.sqlmonster.com
User submitted from AEWNET (http://www.aewnet.com/)
Database Maintenance
I have a maintenance plan set up to:
Remove unused space from database files
Shrink database when it grows beyond: 50 MB
Amount of free space to remain after shrink = 10%
Does this effect log files or just data files, or both?
--
Message posted via http://www.sqlmonster.comboth.
Many DBA's would not recommend shrinking the Database unless you
specifically are running into storage space issues.
My personal recommendation is that you disable this feature.
Greg Jackson
PDX, Oregon|||The maintenance plan will do a dbcc shrinkdatabase, not a
dbcc shrinkfile. As Greg posted, you really don't need this
in a regular maintenance plan. It's better to just size your
files appropriately and let them have the space they may
need for your normal operations. If they grow and you shrink
them and then they grow again right after you shrink them,
you just waste resources. Also, I think the maintenance plan
defaults to where you do a dbreindex and then a
shrinkdatabase. In that scenario, you likely just
reintroduce the fragmentation you cleaned up with the
dbreindex.
The following article on Tibor Karaszi's site had some
additional information on shrinking:
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
-Sue
On Tue, 08 Feb 2005 22:21:42 GMT, "Robert Richards via
SQLMonster.com" <forum@.SQLMonster.com> wrote:
>I have a maintenance plan set up to:
>Remove unused space from database files
> Shrink database when it grows beyond: 50 MB
> Amount of free space to remain after shrink = 10%
>Does this effect log files or just data files, or both?|||I have applied "autoshrink" to many of my databases specifically= to try and keep the size of the logs down, and it seems to help.= I had much trouble with logs growinbg larger than the database,= but this has improved with autoshrink and nightly log backups.
> I have a maintenance plan set up to:
> Remove unused space from database files
> Shrink database when it grows beyond: 50 MB
> Amount of free space to remain after shrink =3D 10%
> Does this effect log files or just data files, or both?
> --
> Message posted via http://www.sqlmonster.com
User submitted from AEWNET (http://www.aewnet.com/)|||http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"engel1" <engel1@.aew_nospam.com> wrote in message news:OE6hmHxEFHA.4004@.tk2msftngp13.phx.gbl...
I have applied "autoshrink" to many of my databases specifically to try and keep the size of the
logs down, and it seems to help. I had much trouble with logs growinbg larger than the database, but
this has improved with autoshrink and nightly log backups.
> I have a maintenance plan set up to:
> Remove unused space from database files
> Shrink database when it grows beyond: 50 MB
> Amount of free space to remain after shrink = 10%
> Does this effect log files or just data files, or both?
> --
> Message posted via http://www.sqlmonster.com
User submitted from AEWNET (http://www.aewnet.com/)
Remove unused space from database files
Shrink database when it grows beyond: 50 MB
Amount of free space to remain after shrink = 10%
Does this effect log files or just data files, or both?
--
Message posted via http://www.sqlmonster.comboth.
Many DBA's would not recommend shrinking the Database unless you
specifically are running into storage space issues.
My personal recommendation is that you disable this feature.
Greg Jackson
PDX, Oregon|||The maintenance plan will do a dbcc shrinkdatabase, not a
dbcc shrinkfile. As Greg posted, you really don't need this
in a regular maintenance plan. It's better to just size your
files appropriately and let them have the space they may
need for your normal operations. If they grow and you shrink
them and then they grow again right after you shrink them,
you just waste resources. Also, I think the maintenance plan
defaults to where you do a dbreindex and then a
shrinkdatabase. In that scenario, you likely just
reintroduce the fragmentation you cleaned up with the
dbreindex.
The following article on Tibor Karaszi's site had some
additional information on shrinking:
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
-Sue
On Tue, 08 Feb 2005 22:21:42 GMT, "Robert Richards via
SQLMonster.com" <forum@.SQLMonster.com> wrote:
>I have a maintenance plan set up to:
>Remove unused space from database files
> Shrink database when it grows beyond: 50 MB
> Amount of free space to remain after shrink = 10%
>Does this effect log files or just data files, or both?|||I have applied "autoshrink" to many of my databases specifically= to try and keep the size of the logs down, and it seems to help.= I had much trouble with logs growinbg larger than the database,= but this has improved with autoshrink and nightly log backups.
> I have a maintenance plan set up to:
> Remove unused space from database files
> Shrink database when it grows beyond: 50 MB
> Amount of free space to remain after shrink =3D 10%
> Does this effect log files or just data files, or both?
> --
> Message posted via http://www.sqlmonster.com
User submitted from AEWNET (http://www.aewnet.com/)|||http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"engel1" <engel1@.aew_nospam.com> wrote in message news:OE6hmHxEFHA.4004@.tk2msftngp13.phx.gbl...
I have applied "autoshrink" to many of my databases specifically to try and keep the size of the
logs down, and it seems to help. I had much trouble with logs growinbg larger than the database, but
this has improved with autoshrink and nightly log backups.
> I have a maintenance plan set up to:
> Remove unused space from database files
> Shrink database when it grows beyond: 50 MB
> Amount of free space to remain after shrink = 10%
> Does this effect log files or just data files, or both?
> --
> Message posted via http://www.sqlmonster.com
User submitted from AEWNET (http://www.aewnet.com/)
Database Maintenance
I have a maintenance plan set up to:
Remove unused space from database files
Shrink database when it grows beyond: 50 MB
Amount of free space to remain after shrink = 10%
Does this effect log files or just data files, or both?
Message posted via http://www.droptable.comboth.
Many DBA's would not recommend shrinking the Database unless you
specifically are running into storage space issues.
My personal recommendation is that you disable this feature.
Greg Jackson
PDX, Oregon|||The maintenance plan will do a dbcc shrinkdatabase, not a
dbcc shrinkfile. As Greg posted, you really don't need this
in a regular maintenance plan. It's better to just size your
files appropriately and let them have the space they may
need for your normal operations. If they grow and you shrink
them and then they grow again right after you shrink them,
you just waste resources. Also, I think the maintenance plan
defaults to where you do a dbreindex and then a
shrinkdatabase. In that scenario, you likely just
reintroduce the fragmentation you cleaned up with the
dbreindex.
The following article on Tibor Karaszi's site had some
additional information on shrinking:
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
-Sue
On Tue, 08 Feb 2005 22:21:42 GMT, "Robert Richards via
droptable.com" <forum@.droptable.com> wrote:
>I have a maintenance plan set up to:
>Remove unused space from database files
> Shrink database when it grows beyond: 50 MB
> Amount of free space to remain after shrink = 10%
>Does this effect log files or just data files, or both?|||I have applied "autoshrink" to many of my databases specifically=
to try and keep the size of the logs down, and it seems to help.=
I had much trouble with logs growinbg larger than the database,=
but this has improved with autoshrink and nightly log backups.
> I have a maintenance plan set up to:
> Remove unused space from database files
> Shrink database when it grows beyond: 50 MB
> Amount of free space to remain after shrink =3D 10%
> Does this effect log files or just data files, or both?
> --
> Message posted via http://www.droptable.com
User submitted from AEWNET (http://www.aewnet.com/)|||http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"engel1" <engel1@.aew_nospam.com> wrote in message news:OE6hmHxEFHA.4004@.tk2m
sftngp13.phx.gbl...
I have applied "autoshrink" to many of my databases specifically to try and
keep the size of the
logs down, and it seems to help. I had much trouble with logs growinbg large
r than the database, but
this has improved with autoshrink and nightly log backups.
> I have a maintenance plan set up to:
> Remove unused space from database files
> Shrink database when it grows beyond: 50 MB
> Amount of free space to remain after shrink = 10%
> Does this effect log files or just data files, or both?
> --
> Message posted via http://www.droptable.com
User submitted from AEWNET (http://www.aewnet.com/)
Remove unused space from database files
Shrink database when it grows beyond: 50 MB
Amount of free space to remain after shrink = 10%
Does this effect log files or just data files, or both?
Message posted via http://www.droptable.comboth.
Many DBA's would not recommend shrinking the Database unless you
specifically are running into storage space issues.
My personal recommendation is that you disable this feature.
Greg Jackson
PDX, Oregon|||The maintenance plan will do a dbcc shrinkdatabase, not a
dbcc shrinkfile. As Greg posted, you really don't need this
in a regular maintenance plan. It's better to just size your
files appropriately and let them have the space they may
need for your normal operations. If they grow and you shrink
them and then they grow again right after you shrink them,
you just waste resources. Also, I think the maintenance plan
defaults to where you do a dbreindex and then a
shrinkdatabase. In that scenario, you likely just
reintroduce the fragmentation you cleaned up with the
dbreindex.
The following article on Tibor Karaszi's site had some
additional information on shrinking:
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
-Sue
On Tue, 08 Feb 2005 22:21:42 GMT, "Robert Richards via
droptable.com" <forum@.droptable.com> wrote:
>I have a maintenance plan set up to:
>Remove unused space from database files
> Shrink database when it grows beyond: 50 MB
> Amount of free space to remain after shrink = 10%
>Does this effect log files or just data files, or both?|||I have applied "autoshrink" to many of my databases specifically=
to try and keep the size of the logs down, and it seems to help.=
I had much trouble with logs growinbg larger than the database,=
but this has improved with autoshrink and nightly log backups.
> I have a maintenance plan set up to:
> Remove unused space from database files
> Shrink database when it grows beyond: 50 MB
> Amount of free space to remain after shrink =3D 10%
> Does this effect log files or just data files, or both?
> --
> Message posted via http://www.droptable.com
User submitted from AEWNET (http://www.aewnet.com/)|||http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"engel1" <engel1@.aew_nospam.com> wrote in message news:OE6hmHxEFHA.4004@.tk2m
sftngp13.phx.gbl...
I have applied "autoshrink" to many of my databases specifically to try and
keep the size of the
logs down, and it seems to help. I had much trouble with logs growinbg large
r than the database, but
this has improved with autoshrink and nightly log backups.
> I have a maintenance plan set up to:
> Remove unused space from database files
> Shrink database when it grows beyond: 50 MB
> Amount of free space to remain after shrink = 10%
> Does this effect log files or just data files, or both?
> --
> Message posted via http://www.droptable.com
User submitted from AEWNET (http://www.aewnet.com/)
Subscribe to:
Posts (Atom)