Re: Database/Logs don't shrink with Maintenance Task

I'm probably misusing terms.

IF I have a huge, FULL, transaction log, doing a full backup of both db and
log SHOULD put a lot of free space in both, so that they COULD be
shrunk--main point is that if done frequently enough, as you stated, I
wouldn't have to shrink, as, it would never get that big...

"Andrew J. Kelly" wrote:

No that is not a correct assumption. Backing up the database does not shrink
the file. Only a shrink file can do that but it may require a backup to get
it started. Backing up the log file will however allow the space that
committed transactions take up to be reused so the file does not continue to
grow. Other wise it has to keep growing to make room for more transactions.

--
Andrew J. Kelly SQL MVP

"Bill Bradley" <BillBradley@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:ACD7F00F-4C13-4195-AAFD-543B6C3C0E44@xxxxxxxxxxxxxxxx
Thanks, Andrew,

Am I right in assuming that a full backup of db and log files SHOULD make
the log file smaller--whether you shrink or not?

I'll look into your links. Thanks!

"Andrew J. Kelly" wrote:

My question would be why is your logs getting so large to begin with? It
sounds like the DB is in FULL recovery mode yet you are not issuing
regular
or frequent enough log backups. If not then you should consider changing
to
Simple recovery or run a log backup at frequent intervals. That should
keep
the log to a reasonable size and there should be no need to shrink.
Shrinking in general is not a good practice and with proper db
maintenance
it can be avoided altogether in most cases. Even when it is necessary to
shrink a file I always recommend using DBCC SHRINKFILE so you have more
control over the process. You might want to have a look at these.


http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
considerations
http://www.nigelrivett.net/TransactionLogFileGrows_1.html Log File
issues
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up

--
Andrew J. Kelly SQL MVP

"Bill Bradley" <BillBradley@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:86B17CA1-88A9-4FA7-AACC-B5DC5167F0D3@xxxxxxxxxxxxxxxx
I'm having problems getting databases and logs to shrink, even though
they
are 90% empty and free. Systems are SQL 2005 Ent w/SP1 and SP1
patches.

DB/Log is 375/2575 MB, with both reporting 90% free. I've setup a
maintenance plan that does the shrink AFTER a Full DB and Log backups
occur
(previously, I had it in the default creation order, which is before
the
backup). Shrinking never occurs. However, if I right-click on the
actual
DB, and do a shrink as a task (repeating a few times, usually), the
files
finally shrink.

This is happening on several different servers, on different databases
(not
the system ones, but, user ones).

What am I doing wrong, as...I thought this was easy...<G>

Thanks!






.

Relevant Pages

  • Re: Merge Replication of large database
    ... Backup the transaction log and then issue a shrink. ... Also issue a dbcc opentran to see if there are any open transactions. ... Looking for a SQL Server replication book? ...
    (microsoft.public.sqlserver.replication)
  • Re: Can anyone crash course me in SQL maintenance?
    ... There is nothing you could do to make SQL Server dump logged transactions before they get committed to the data. ... If you take a nightly full db backup, that would leave you with 24 hours of vulnerability to data loss. ... The log file can be much smaller than the data since it only records the transactions that occur. ... So if someone accidentally deletes important data, you can restore a copy of the database exactly as it was 1 second before that mistake. ...
    (microsoft.public.windows.server.sbs)
  • Re: Can anyone crash course me in SQL maintenance?
    ... Let's see if we can get an SQL expert to guide you ... ... I use redgate's SQL Backup to do a nightly backup, ... Log file size: 18g ... log as I'm not quite sure how transactions get committed to the SQL ...
    (microsoft.public.windows.server.sbs)
  • Re: does this backup sequence commit all data to the database
    ... The database is in FULL recovery mode. ... During the day you BACKUP the LOG to files. ... At night you TRUNCATE the log "to flush the log file of committed ...  Then you shrink the log file and backup the database. ...
    (comp.databases.ms-sqlserver)
  • Re: Recovery model and log file shrinking
    ... Any chance you are using SQL 7.0? ... The shrink operation is not always ... http://www.support.microsoft.com/?id=317375 Log File Grows too big ... > Actually I'm trying backup and shrink database for better understanding of> their affect on a database. ...
    (microsoft.public.sqlserver.programming)
Quantcast