Is this use of shrinkfile safe?

Hi all,
I've been hunting around, but haven't found a satisfactory answer
yet, so am hoping one of you can help me.
On a production database on a SQL 2K server with Full recovery mode,
we do a daily differential backup on the database. Then, weekly, we do
a full backup on both the database and log (without truncateonly).
I understand that these themselves don't shrink the physical file, so
am wondering if it is safe to (occasionally) do a dbcc shrinkfile on
the log after the weekly full backup, or if the potential exists to
lose data if we need to do a restore.
Thanks,
-Phil M
You can do a shrinkfile if you wish, but you have to ask yourself if its
worth the performance impact of doing so, bearing in mind that your files
have grown to a certain size, and probably will continue to do so each week.
When sql server performs an automatic growth, it is inherently slow and much
slower than performing a manual grow of a file. Even better for performance
is just to leave the files as they are, knowing the space will get used up by
the end of the week anyway.
To answer your question: yes it is safe. you wont lose data unless something
catastrophic occurs.
Andy Price,
Sr. Database Administrator,
MCDBA 2003
"fuiru2000@.yahoo.com" wrote:

> Hi all,
> I've been hunting around, but haven't found a satisfactory answer
> yet, so am hoping one of you can help me.
> On a production database on a SQL 2K server with Full recovery mode,
> we do a daily differential backup on the database. Then, weekly, we do
> a full backup on both the database and log (without truncateonly).
> I understand that these themselves don't shrink the physical file, so
> am wondering if it is safe to (occasionally) do a dbcc shrinkfile on
> the log after the weekly full backup, or if the potential exists to
> lose data if we need to do a restore.
> Thanks,
> -Phil M
>
|||fuiru2000@.yahoo.com wrote:
> Hi all,
> I've been hunting around, but haven't found a satisfactory answer
> yet, so am hoping one of you can help me.
> On a production database on a SQL 2K server with Full recovery mode,
> we do a daily differential backup on the database. Then, weekly, we do
> a full backup on both the database and log (without truncateonly).
> I understand that these themselves don't shrink the physical file, so
> am wondering if it is safe to (occasionally) do a dbcc shrinkfile on
> the log after the weekly full backup, or if the potential exists to
> lose data if we need to do a restore.
> Thanks,
> -Phil M
Why do you do both a database backup and log backup weekly? That sounds
pointless to me. If you don't know the difference between log and full
backups then read about them in Books Online. If you don't require log
backups (it sounds like you don't) then select the Simple Recovery
model. In any case shrinking regularly in a production system is a very
bad idea and it isn't necessary.
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/ms130214(en-US,SQL.90).aspx
|||I'm not a DBA, and didn't design the backup software, so can't speak as
to why it's done that way; I'm simply looking at a complaint someone
has about disk usage.
I'm not sure how I gave the impression that we don't need log backups.
The database is part of an archive of rather important information, so
I'm assuming that, if there's any redundancy in doing the incremental
and full backups this way, there's a good reason. Or, perhaps there was
in some previous incarnation of SQL or another DBMS. I'll ask the guy
responsible, though.
The thing I think the person who is reporting the disk usage problem is
worried about is the fact that, after doing a shrinkfile yesterday, the
log was 1.8 GB (at ~12% use, if I remember what SQLPERF reported) to 8
GB today after an incremental, with about 96% usage. I think it's
gotten up as high as 30 GB.
I may end up telling them to live with it, if I can determine that that
log file won't grow beyond a certain size. That, or he'll need to move
the log file. If he presses the issue, though, I just want to know if
throwing in the shrinkfile every week or two is safe, which it sounds
like it is.
In any case, it appears I have a few options at this point, so I'll see
where it goes from here. Thank you both for your help.
-Phil
|||No. I don't think you get the difference between a DB backup, a
differential backup and a transaction log backup (it may take a bit of
reading, but Books Online has this all pretty clearly documented). In
your scenario the tlog backups are essentially useless since you're
doing differential backups daily, so there's no point (as David said) in
doing the tlog backups weekly (when restoring, tlog backups get restored
AFTER the latest differential). Differential backups do not truncate
the transaction log. This is why it continues to grow until the tlog
backup that occurs once a week.
The answer is, rather than doing differentials daily, do transaction log
backups daily and forget about the differentials (this will keep the
tlog much smaller since you're truncating it every day rather than once
a week). Alternately, if you're doing weekly DB backups and daily
differentials, then do tlog backups more frequently than daily (ie. the
differential backup frequency), for example hourly or 4-hourly or
6-hourly (depending on how small you wish to keep your tlog).
With the way it currently is, if you ever have to restore, you'll
restore your latest DB backup, then the most recent differential backup
(that was taken after the DB backup), then every tlog backup that has
occurred since that differential backup, up to the point in time you
wish to restore to (presumedly as most recent as possible to minimise
data loss). If your tlog backups are only done once a week (right after
the DB backup) then they'll essentially be useless as they won't contain
any transactions that the DB backup doesn't contain and they're not even
as current as your subsequent differentials so you won't be able to use
them at all when you restore.
Basically, the tlog backups are what keep you transaction log trim (they
automatically truncate the transaction log so the space can be reused by
future transactions). If it's growing too fat then do tlog backups more
often or don't keep the transaction log at all (ie. simple recovery
mode). My recommendation would be weekly DB backups, daily diff backups
and hourly tlog backups, OR weekly DB backups and daily tlog backups.
(The production DBs where I worked are on a daily DB backup/15 min tlog
backup schedule to minimise data loss to, at most, 15 minutes and keep
the tlog files nice and small.)
Hope this helps.
*mike hodgson*
http://sqlnerd.blogspot.com
fuiru2000@.yahoo.com wrote:

>I'm not a DBA, and didn't design the backup software, so can't speak as
>to why it's done that way; I'm simply looking at a complaint someone
>has about disk usage.
>I'm not sure how I gave the impression that we don't need log backups.
>The database is part of an archive of rather important information, so
>I'm assuming that, if there's any redundancy in doing the incremental
>and full backups this way, there's a good reason. Or, perhaps there was
>in some previous incarnation of SQL or another DBMS. I'll ask the guy
>responsible, though.
>The thing I think the person who is reporting the disk usage problem is
>worried about is the fact that, after doing a shrinkfile yesterday, the
>log was 1.8 GB (at ~12% use, if I remember what SQLPERF reported) to 8
>GB today after an incremental, with about 96% usage. I think it's
>gotten up as high as 30 GB.
>I may end up telling them to live with it, if I can determine that that
>log file won't grow beyond a certain size. That, or he'll need to move
>the log file. If he presses the issue, though, I just want to know if
>throwing in the shrinkfile every week or two is safe, which it sounds
>like it is.
>In any case, it appears I have a few options at this point, so I'll see
>where it goes from here. Thank you both for your help.
>-Phil
>
>