Newbie-shrink transaction log files

26 Nov 2007 3:41 AM

Coop

Hi. I know this is an often-discussed topic, but I hope you can clear up
some confusion for me. I set up a SQL Server 2005 maintenance plan to back
up my databases to disk (we also backup to tape using Backup Exec). My goals
in backing up to disk are 1) to have a couple days of backups on disk in case
I need to do quick restores, and 2) to reduce the size of transaction log
files. In the maint plan, I selected the options to shrink the databases,
backup databases (full), and backup transaction logs. The problem is that
when I executed the maint plan, it didn't reduce the size of the transaction
log physical files which are growing.

I grabbed this SQL code from the Internet, and it works to shrink the tran
log file size:

USE mydb
BACKUP LOG mydb WITH TRUNCATEONLY
DBCC SHRINKFILE [mydb_log, TRUNCATEONLY]
GO

I want to automate this task. I don't want to have to edit the above script
every time I create or drop a database. I know I can just have a SQL Agent
job run the script, BUT isn't the maintenance job supposed to shrink tran log
files as part of the "Backup Database (Full)" and Backup Database
"(Transaction Log)" options?

By the way, is the BACKUP LOG statement required to precede the DBCC
SHRINKFILE statement?

Thanks for your help.

26 Nov 2007 6:18 AM

Ben Nevarez

Hi Coop,

I see a couple of problems with your request. First, do not shrink your
database and transaction log files periodically. When you shrink a file, the
next thing SQL Server will do is to expand these files to do its work, and
shrinking and expanding these files periodically could be a performance
problem in addition to creating more disk fragmentation that probably will
also affect the performance of your database. My recommendation here is just
schedule full database and transaction log backups. You can still shrink the
files manually once in a while only if you need it.

Second, you want to use TRUNCATE_ONLY which truncates the log without really
doing a backup. The problem here is that you are not doing a transaction log
backup and you are breaking the log chain. I recommend here that you see the
SQL Server documentation (BOL) and understand the SQL Server recovery models
simple, bulk-logged and full. If you really need to use TRUNCATE_ONLY better
change the recovery model to simple.

Hope this helps,

Ben Nevarez
Senior Database Administrator
AIG SunAmerica

Show quote

"Coop" wrote:

> Hi. I know this is an often-discussed topic, but I hope you can clear up
> some confusion for me. I set up a SQL Server 2005 maintenance plan to back
> up my databases to disk (we also backup to tape using Backup Exec). My goals
> in backing up to disk are 1) to have a couple days of backups on disk in case
> I need to do quick restores, and 2) to reduce the size of transaction log
> files. In the maint plan, I selected the options to shrink the databases,
> backup databases (full), and backup transaction logs. The problem is that
> when I executed the maint plan, it didn't reduce the size of the transaction
> log physical files which are growing.
>
> I grabbed this SQL code from the Internet, and it works to shrink the tran
> log file size:
>
> USE mydb
> BACKUP LOG mydb WITH TRUNCATEONLY
> DBCC SHRINKFILE [mydb_log, TRUNCATEONLY]
> GO
>
> I want to automate this task. I don't want to have to edit the above script
> every time I create or drop a database. I know I can just have a SQL Agent
> job run the script, BUT isn't the maintenance job supposed to shrink tran log
> files as part of the "Backup Database (Full)" and Backup Database
> "(Transaction Log)" options?
>
> By the way, is the BACKUP LOG statement required to precede the DBCC
> SHRINKFILE statement?
>
> Thanks for your help.

26 Nov 2007 6:40 AM

Uri Dimant

Coop
http://www.karaszi.com/SQLServer/info_dont_shrink.asp

Show quote

"Coop" <C***@discussions.microsoft.com> wrote in message
news:91C8FA70-23CF-4D9A-8879-93EC8EDAEF71@microsoft.com...
> Hi. I know this is an often-discussed topic, but I hope you can clear up
> some confusion for me. I set up a SQL Server 2005 maintenance plan to
> back
> up my databases to disk (we also backup to tape using Backup Exec). My
> goals
> in backing up to disk are 1) to have a couple days of backups on disk in
> case
> I need to do quick restores, and 2) to reduce the size of transaction log
> files. In the maint plan, I selected the options to shrink the databases,
> backup databases (full), and backup transaction logs. The problem is that
> when I executed the maint plan, it didn't reduce the size of the
> transaction
> log physical files which are growing.
>
> I grabbed this SQL code from the Internet, and it works to shrink the tran
> log file size:
>
> USE mydb
> BACKUP LOG mydb WITH TRUNCATEONLY
> DBCC SHRINKFILE [mydb_log, TRUNCATEONLY]
> GO
>
> I want to automate this task. I don't want to have to edit the above
> script
> every time I create or drop a database. I know I can just have a SQL
> Agent
> job run the script, BUT isn't the maintenance job supposed to shrink tran
> log
> files as part of the "Backup Database (Full)" and Backup Database
> "(Transaction Log)" options?
>
> By the way, is the BACKUP LOG statement required to precede the DBCC
> SHRINKFILE statement?
>
> Thanks for your help.