Hi
We usae Sql2000.
I'm setting up backups jobs and need advise.
When I run a backup of a database, does it automatically clean out the
transaction log ? For a complete backup ? for a differential backup ?
When would it be best to run a shrinkdatabase command ? Why would I want
to run the shrinkdatabase ?
Thnaks
David Greenberg
David Greenberg (davidgr@iba.org.il) writes:
> We usae Sql2000.
> I'm setting up backups jobs and need advise.
> When I run a backup of a database, does it automatically clean out the
> transaction log ? For a complete backup ? for a differential backup ?
No. Backing up the database and backing up the transaction log are two
separate operations.
Before you go ahead, determine what level of recovery you need. If the
database goes belly-up, do you need restore to a point in time? Or would
you be content with restoring the most recent backup?
In the former case, you need to use full recovery and you need to back up
your transaction log regularly. In the latter case, you can go with simple
recovery and forget all about the transaction log. SQL Server will
automatically truncate the transaction log regularly so that only active
transctions remain.
> When would it be best to run a shrinkdatabase command ? Why would I want
> to run the shrinkdatabase ?
Most of the time you don't want to. Shrinking the database is a very
exceptional operation. This article gives some insight about this:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx
Erland Sommarskog wrote:
> David Greenberg (davidgr@iba.org.il) writes:
>
>>We usae Sql2000.
>>I'm setting up backups jobs and need advise.
>>When I run a backup of a database, does it automatically clean out the
>>transaction log ? For a complete backup ? for a differential backup ?
>
>
> No. Backing up the database and backing up the transaction log are two
> separate operations.
>
> Before you go ahead, determine what level of recovery you need. If the
> database goes belly-up, do you need restore to a point in time? Or would
> you be content with restoring the most recent backup?
>
> In the former case, you need to use full recovery and you need to back up
> your transaction log regularly. In the latter case, you can go with simple
> recovery and forget all about the transaction log. SQL Server will
> automatically truncate the transaction log regularly so that only active
> transctions remain.
>
>
>>When would it be best to run a shrinkdatabase command ? Why would I want
>>to run the shrinkdatabase ?
>
>
> Most of the time you don't want to. Shrinking the database is a very
> exceptional operation. This article gives some insight about this:
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>
>
So when does SqlServer decide to shrink the database automaticaly ? When
a backup is performed ?
David Greenberg (davidgr@iba.org.il) writes:
> So when does SqlServer decide to shrink the database automaticaly ? When
> a backup is performed ?
To have SQL Server automatically shrink a database, you have configure
the database to be in auto-shrink mode, which I strongly recommend against.
As I said, shrinking is something you should do only exceptionally, and
when you do, you want to have control over it.
If you are in AUTO_SHRINK mode, Books Online tells us this:
The database files are candidates for periodic shrinking.
Both data file and log files can be automatically shrunk. AUTO_SHRINK
reduces the size of the transaction log only if the database is set to
SIMPLE recovery model or if the log is backed up. When set to OFF, the
database files are not automatically shrunk during periodic checks for
unused space.
The AUTO_SHRINK option causes files to be shrunk when more than 25
percent of the file contains unused space. The file is shrunk to a size
where 25 percent of the file is unused space, or to the size of the
file when it was created, whichever is larger.
You cannot shrink a read-only database
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx