Hello,
I'm getting the following error on 1 of our DB's;
The log file for database 'database' is full. Back up the transaction
log for the database to free up some log space.
How can I dump the transaction log?
Thanks,
C
BACKUP LOG { database_name | @.database_name_var }
{
[ WITH
{ NO_LOG | TRUNCATE_ONLY } ]
}
example:
BACKUP LOG Northwind WITH TRUNCATE_ONLY
if you dont need your transaction logs backed up regularly (not worried
about data loss, etc) then I recommend placing the database in SIMPLE
Recovery Mode.
Greg Jackson
PDX, Oregon
"Craig Alexander" <craig@.itas.net> wrote in message
news:2582929c.0407121143.16fa26b9@.posting.google.c om...
> Hello,
> I'm getting the following error on 1 of our DB's;
> The log file for database 'database' is full. Back up the transaction
> log for the database to free up some log space.
>
> How can I dump the transaction log?
> Thanks,
> C
|||backup log <databasename> to disk = 'filename'
Also check you recovery mode
select databasepropertyex('databasename','Recovery')
If you don't need point in time recovery, set the database to simple
recovery mode
alter database <databasename> set recovery simple
and just use full backups. If you require more upto date backups set up a
regular job to backup your transaction log (the easisest way is to use a
maintenance plan). You can also set the log file to autogrow to prevent this
issue assuming you have done one of the steps above. You should set your log
to a reasonable size so its not constally growing. Have a look at
INF: Shrinking the Transaction Log in
SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default...;en-us;Q272318
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/default...b;EN-US;256650
INF: Causes of SQL Transaction Log Filling Up
http://support.microsoft.com/default...b;EN-US;110139
INFO: Reasons Why SQL Transaction Log Is Not Being Truncated
http://support.microsoft.com/default...kb;EN-US;62866
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Craig Alexander" <craig@.itas.net> wrote in message
news:2582929c.0407121143.16fa26b9@.posting.google.c om...
> Hello,
> I'm getting the following error on 1 of our DB's;
> The log file for database 'database' is full. Back up the transaction
> log for the database to free up some log space.
>
> How can I dump the transaction log?
> Thanks,
> C
Showing posts with label transactionlog. Show all posts
Showing posts with label transactionlog. Show all posts
Wednesday, March 21, 2012
Dump Transaction Log
Hello,
I'm getting the following error on 1 of our DB's;
The log file for database 'database' is full. Back up the transaction
log for the database to free up some log space.
How can I dump the transaction log?
Thanks,
CBACKUP LOG { database_name | @.database_name_var }
{
[ WITH
{ NO_LOG | TRUNCATE_ONLY } ]
}
example:
BACKUP LOG Northwind WITH TRUNCATE_ONLY
if you dont need your transaction logs backed up regularly (not worried
about data loss, etc) then I recommend placing the database in SIMPLE
Recovery Mode.
Greg Jackson
PDX, Oregon
"Craig Alexander" <craig@.itas.net> wrote in message
news:2582929c.0407121143.16fa26b9@.posting.google.com...
> Hello,
> I'm getting the following error on 1 of our DB's;
> The log file for database 'database' is full. Back up the transaction
> log for the database to free up some log space.
>
> How can I dump the transaction log?
> Thanks,
> C|||backup log <databasename> to disk = 'filename'
Also check you recovery mode
select databasepropertyex('databasename','Recov
ery')
If you don't need point in time recovery, set the database to simple
recovery mode
alter database <databasename> set recovery simple
and just use full backups. If you require more upto date backups set up a
regular job to backup your transaction log (the easisest way is to use a
maintenance plan). You can also set the log file to autogrow to prevent this
issue assuming you have done one of the steps above. You should set your log
to a reasonable size so its not constally growing. Have a look at
INF: Shrinking the Transaction Log in
SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/defaul...b;en-us;Q272318
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/defaul...kb;EN-US;256650
INF: Causes of SQL Transaction Log Filling Up
http://support.microsoft.com/defaul...kb;EN-US;110139
INFO: Reasons Why SQL Transaction Log Is Not Being Truncated
http://support.microsoft.com/defaul...=kb;EN-US;62866
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Craig Alexander" <craig@.itas.net> wrote in message
news:2582929c.0407121143.16fa26b9@.posting.google.com...
> Hello,
> I'm getting the following error on 1 of our DB's;
> The log file for database 'database' is full. Back up the transaction
> log for the database to free up some log space.
>
> How can I dump the transaction log?
> Thanks,
> C
I'm getting the following error on 1 of our DB's;
The log file for database 'database' is full. Back up the transaction
log for the database to free up some log space.
How can I dump the transaction log?
Thanks,
CBACKUP LOG { database_name | @.database_name_var }
{
[ WITH
{ NO_LOG | TRUNCATE_ONLY } ]
}
example:
BACKUP LOG Northwind WITH TRUNCATE_ONLY
if you dont need your transaction logs backed up regularly (not worried
about data loss, etc) then I recommend placing the database in SIMPLE
Recovery Mode.
Greg Jackson
PDX, Oregon
"Craig Alexander" <craig@.itas.net> wrote in message
news:2582929c.0407121143.16fa26b9@.posting.google.com...
> Hello,
> I'm getting the following error on 1 of our DB's;
> The log file for database 'database' is full. Back up the transaction
> log for the database to free up some log space.
>
> How can I dump the transaction log?
> Thanks,
> C|||backup log <databasename> to disk = 'filename'
Also check you recovery mode
select databasepropertyex('databasename','Recov
ery')
If you don't need point in time recovery, set the database to simple
recovery mode
alter database <databasename> set recovery simple
and just use full backups. If you require more upto date backups set up a
regular job to backup your transaction log (the easisest way is to use a
maintenance plan). You can also set the log file to autogrow to prevent this
issue assuming you have done one of the steps above. You should set your log
to a reasonable size so its not constally growing. Have a look at
INF: Shrinking the Transaction Log in
SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/defaul...b;en-us;Q272318
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/defaul...kb;EN-US;256650
INF: Causes of SQL Transaction Log Filling Up
http://support.microsoft.com/defaul...kb;EN-US;110139
INFO: Reasons Why SQL Transaction Log Is Not Being Truncated
http://support.microsoft.com/defaul...=kb;EN-US;62866
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Craig Alexander" <craig@.itas.net> wrote in message
news:2582929c.0407121143.16fa26b9@.posting.google.com...
> Hello,
> I'm getting the following error on 1 of our DB's;
> The log file for database 'database' is full. Back up the transaction
> log for the database to free up some log space.
>
> How can I dump the transaction log?
> Thanks,
> C
Sunday, March 11, 2012
Dual Transaction Log
I have just taken over an SQL Server 7.0 database which has two transaction
log files. One is on the system drive, and the other is on the same drive as
the database..
The transaction log on the system drive is huge, leaving very little free
space, whilst the database drive has tonnes of free space..
How do I remove the log file on the system drive, and just use the second
one?
Thnak you
Nick
Hi,
Best way is :-
1. Detach the database using sp_detach_db
2. copy the LDF file in system drive to new location
3. Attach the database using SP_ATTACH_DB, specify the new LDF location.
One more method to remove ldf , is backup the database and TX log , shrink
the LDF file using DBCC SHRINKFILE and REMOVE the FILE using Alter database
see the below link for details
http://groups.google.com/groups?q=re...t .com&rnum=5
Thanks
Hari
MCDBA
"nh" <anonymous@.discussions.microsoft.com> wrote in message
news:egnAeCymEHA.2372@.TK2MSFTNGP10.phx.gbl...
>I have just taken over an SQL Server 7.0 database which has two transaction
> log files. One is on the system drive, and the other is on the same drive
> as
> the database..
> The transaction log on the system drive is huge, leaving very little free
> space, whilst the database drive has tonnes of free space..
> How do I remove the log file on the system drive, and just use the second
> one?
> Thnak you
> Nick
>
log files. One is on the system drive, and the other is on the same drive as
the database..
The transaction log on the system drive is huge, leaving very little free
space, whilst the database drive has tonnes of free space..
How do I remove the log file on the system drive, and just use the second
one?
Thnak you
Nick
Hi,
Best way is :-
1. Detach the database using sp_detach_db
2. copy the LDF file in system drive to new location
3. Attach the database using SP_ATTACH_DB, specify the new LDF location.
One more method to remove ldf , is backup the database and TX log , shrink
the LDF file using DBCC SHRINKFILE and REMOVE the FILE using Alter database
see the below link for details
http://groups.google.com/groups?q=re...t .com&rnum=5
Thanks
Hari
MCDBA
"nh" <anonymous@.discussions.microsoft.com> wrote in message
news:egnAeCymEHA.2372@.TK2MSFTNGP10.phx.gbl...
>I have just taken over an SQL Server 7.0 database which has two transaction
> log files. One is on the system drive, and the other is on the same drive
> as
> the database..
> The transaction log on the system drive is huge, leaving very little free
> space, whilst the database drive has tonnes of free space..
> How do I remove the log file on the system drive, and just use the second
> one?
> Thnak you
> Nick
>
Subscribe to:
Posts (Atom)