Re: SQL 2005 Shrink issue

Tech-Archive recommends: Fix windows errors by optimizing your registry

Check out this scenario to see what happens in case you perform only full backup:
1- You take your database's full backup on 12:00 am
2- You take your database' s log backup every hour

Of course this scenario subject to change according to the workload and needs of different environments.

However, in this scenario if your database corrupts (because of a power cut or a hardware problem) you'd lose only 1 hour of data at most. To restore your database to its most recent point, you'd first restore the full backup and then the log backups one after another.

So let's say your database becomes unusable at 12:15 pm. Then you'd be able to restore it till 12:00 pm and you'd be lost only 15 minutes data.

If you take only one full backup once in a day and let's say you take it at 12:00 am then according to the above scenario you'd lose all data that's inserted, modified, deleted from 12:01 am to 12:15 pm.

This is why it's important to take log backups for production databases. It's safer, it's more practical and it's faster to restore in case of a point of failure.

Regarding to your problem with shrinking in FULL Database Recovery Model. What makes you think that you can't shrink a database when it's in Full Recovery Model? Of course you can shrink your database and log file when your database in FULL Recovery Model. If you can't shrink your log file, then most probably you didn't empty it.

To empty a transaction log, you can change your Recovery Model to SIMPLE. Or better, take your transaction log file's backup. And this is what should be done in most of production environments. To empty your transaction log file, you should take it's backup. A Full database backup wouldn't empty it. You should explicitly take your log file's backup.

--
Ekrem Önsoy




"Joe C" <JoeC@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:360DA762-7A0F-4F16-A3B0-FD24055853FF@xxxxxxxxxxxxxxxx
I believe that is my problem that I might not be backing up the logs.
currently I use Backup Exec 11D to back up my SQL DB's and I use the Full
back up method. The Full Method is suppose to backup the entire database,
including all tables and file groups. I'm wondering if I need a 2nd job to
back up the logs, I thought creating a full back up of the DB's would also
backup log files and truncate them.


And yes currently I have been having to perform shrinking of DB's on a
regular basis. However that still doesn't explain why I would have to switch
to simple mode in order to shrink the DB, what would prevent me running the
shrink on the .ldf DB's in full mode?

"Erland Sommarskog" wrote:

Joe C (Joe C@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
> I'm having a similar issue with both SQL 2000 and 2005, where if the
> DB's are left in a full recovery mode I can't shrink the DB's. I need > to
> switch the DB's to simple restore mode in order to shrink them. Any
> thoughts?

But why would you shrink the log in the first place?

Keep in mind that shrinking the transaction log is something you only
would do in exceptional cases. You might have performed some unusual
operation that you are not to perform for the next six months. Or the
job for backing up the log has inadvertedly been disabled.

If you are shrinking your logs on any kind of regular basis, you are
doing something wrong.

When you say that you need to switch to simple mode to shrink the log, I
suspect that you are not backing up the transaction log, which you of
course should do, if you want to be able to restore to a point in time.

If you are content with restoring the latest backup in case of failure,
switch to simple recovery for good.


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx


.

Relevant Pages

  • Backup and Restore Files
    ... What is the best way to backup and restore a runtime database file? ... pszDisplayName As String ...
    (microsoft.public.access.gettingstarted)
  • RE: How do I restore from mdf and ldf files?
    ... Tasks -> Backup Database ... Tasks -> Restore Database ... Windows 2003 Server with Latest Service Pack ... Pre-requisites for Sharepoint Backup and Restore: ...
    (microsoft.public.sharepoint.portalserver)
  • Re: Restoring SQL Server Backups
    ... ORIGINAL BACKUP & RESTORE (MSDE Database) ... occurrenceof cachestore flush for the 'Object Plans' cachestore (part ...
    (microsoft.public.sqlserver.server)
  • Re: Restoring SQL Server Backups
    ... ORIGINAL BACKUP & RESTORE (MSDE Database) ... occurrenceof cachestore flush for the 'Object Plans' cachestore (part ...
    (microsoft.public.sqlserver.server)
  • Re: POINT IN TIME RESTORE
    ... This is what you should have done in order to do the restore as you wish: ... > 3- erase data ... > 5- restore database with norecovery ... The BACKUP DATABAE can of course be at an earlier point in time, ...
    (microsoft.public.sqlserver.server)