| Author |
Shrinking Transaction Log
|
|
|
| I have a 300mb database, the transaction log is currently 1200mb!
I have tried to use DBCC SHRINKFILE and DBCC SHRINKDATABASE but neither are
having an impact on the size of this file.
The database shrinks, the system reports back the following:
Current Size Minimun Size Used Pages Estimated Pages
168808 1280 168808 1280
How can I get the physical file size down?
| |
| David Gugick 2005-09-21, 8:24 pm |
| AndyB wrote:
> I have a 300mb database, the transaction log is currently 1200mb!
>
> I have tried to use DBCC SHRINKFILE and DBCC SHRINKDATABASE but
> neither are having an impact on the size of this file.
>
> The database shrinks, the system reports back the following:
> Current Size Minimun Size Used Pages Estimated Pages
> 168808 1280 168808 1280
> How can I get the physical file size down?
What recovery model are you using?
What backup strategy do you employ? Do you even backup the transaction
log? The database?
The simple answer is to backup the log using the truncate_only option
and then use DBCC SHRINKFILE to get the file size down. But that won't
keep this from happening all over again if the cause was using a
recovery model that keeps transactions around and a recovery strategy
that does nothing with the log. Once we know more about what your
environment, we can provide additional details on keeping this problem
from happening again.
Do a full backup after you truncate and shrink the log.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com
| |
|
|
"David Gugick" wrote:
> AndyB wrote:
>
> What recovery model are you using?
> What backup strategy do you employ? Do you even backup the transaction
> log? The database?
>
> The simple answer is to backup the log using the truncate_only option
> and then use DBCC SHRINKFILE to get the file size down. But that won't
> keep this from happening all over again if the cause was using a
> recovery model that keeps transactions around and a recovery strategy
> that does nothing with the log. Once we know more about what your
> environment, we can provide additional details on keeping this problem
> from happening again.
>
> Do a full backup after you truncate and shrink the log.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
>
Thanks for a very prompt response.
I'm using SQL 2000 Standard
Full Recovery Model
Full DB BackUp daily (11:00pm)
Transaction Log BackUp Daily (12:00pm)
Using the SQL GUI to do a transaction log backup is it the 'Remove inactive
entries..' option that will cause a truncate_only backup or do I need to use
the command line?
| |
| robert 2005-09-21, 8:24 pm |
| Try this
use db;
BACKUP LOG db_dbname TO DISK = 'E:\dbname';
DBCC SHRINKFILE (db_dbname, <size you want it to be> );
"AndyB" <AndyB@discussions.microsoft.com> wrote in message
news:D6EAFB5B-4D97-4EA5-9B2A- 28E5FD9CAE13@microso
ft.com...
>
>
> "David Gugick" wrote:
>
> Thanks for a very prompt response.
> I'm using SQL 2000 Standard
> Full Recovery Model
> Full DB BackUp daily (11:00pm)
> Transaction Log BackUp Daily (12:00pm)
>
> Using the SQL GUI to do a transaction log backup is it the 'Remove
> inactive
> entries..' option that will cause a truncate_only backup or do I need to
> use
> the command line?
>
| |
| David Gugick 2005-09-21, 8:24 pm |
| AndyB wrote:
> "David Gugick" wrote:
>
> Thanks for a very prompt response.
> I'm using SQL 2000 Standard
> Full Recovery Model
> Full DB BackUp daily (11:00pm)
> Transaction Log BackUp Daily (12:00pm)
>
> Using the SQL GUI to do a transaction log backup is it the 'Remove
> inactive entries..' option that will cause a truncate_only backup or
> do I need to use the command line?
If you are backing up the transaction log daily, what caused the t-log
to grow so much? In your case backup the t-log normally and then shrink
the file. The truncate only option is not needed if you backup the
t-log.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com
|
|
|
|