I need to shrink a database file and was wondering whether it is
required to run a full backup after the shrink operation.
In SQL Server 7.0 shrinkfile was a non-logged operation so would
invalidate your transaction logs. Is the same true for 2000?
Obviously as a matter of course I would backup before and after the
operation but going forward I may want to implement this on a regular
basis.
Cheers
Deedeebeeay@.gmail.com wrote:
> Hi,
> I need to shrink a database file and was wondering whether it is
> required to run a full backup after the shrink operation.
> In SQL Server 7.0 shrinkfile was a non-logged operation so would
> invalidate your transaction logs. Is the same true for 2000?
> Obviously as a matter of course I would backup before and after the
> operation but going forward I may want to implement this on a regular
> basis.
> Cheers
> Dee
Shrinking log files does not invalidate the log. It is however a very
bad idea to shrink on a regular basis. If you are running full recovery
and doing regular transaction log backups then why would you want to
keep shrinking the log?
For more information see:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||Hi David,
Its not the log I want to shrink but the database.|||deebeeay@.gmail.com wrote:
> Hi David,
> Its not the log I want to shrink but the database.
Skrinking the database regularly is also unlikely to be a good idea in
most environments. Do you enable autogrow? Auto-growing a database is
potentially a very expensive operation. Much better to preset the
database size, turn auto-grow OFF and then don't shrink it at all.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||Yes I agree, auto-growth isn't good and by that same token a regular
shrink is probably not a good idea.
However I'd still like to know if a dbcc shrinkfile would then require
a post full database backup to ensure recoverability.|||deebeeay@.gmail.com wrote:
> Yes I agree, auto-growth isn't good and by that same token a regular
> shrink is probably not a good idea.
> However I'd still like to know if a dbcc shrinkfile would then require
> a post full database backup to ensure recoverability.
Shrinking a data file does not invalidate the log. However, it will
cause an exceptional amount of logging. Shrinking may require at least
as much log as you have data in the file(s) being shrunk. So your next
log backup could be vastly inflated. Assuming your database is offline
you may want to backup the log with the TRUNCATE ONLY option
immediately after shrinking and then perform a database backup. That's
not essential but it does mean you can return the log to its more
typical size. On the other hand if you think you need to do this
regularly then you'll have to ensure sufficient log space to support it
- so shrinking the data file is usually a false economy because it
moves data around without reducing the storage requirement.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||Many thanks for the reply David.
If I do a dbcc shrinkfile using the TRUNCATEONLY option - would this
reduce the amount of logging required?|||deebeeay@.gmail.com (deebeeay@.gmail.com) writes:
> Many thanks for the reply David.
> If I do a dbcc shrinkfile using the TRUNCATEONLY option - would this
> reduce the amount of logging required?
As I understand Books Online, this should not be an expensive operation
with regards to the tranaction log. On the other hand, it may not have
much effect, since it removes only extents at the end of the data file.
If you mistakenly created a file 10 times too large, then I would
expect TRUNCATEONLY to be useful. But if you want to shrink the database,
because you just deleted 5 years worth of data, TRUNCATEONLY is not likely
to have any effect at all.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql
No comments:
Post a Comment