Backup or dump transaction log

How do I backup and dump the transaction log? If I look at the log for each
container the Metabase log is 21.6 Gig and the Distribution log is 11.2 Gig.
I would like to dump the transaction log and start off clean.Hi
Please read How to create a transaction log backup in the BOL
"KMD" <KMD@.discussions.microsoft.com> wrote in message
news:6FBBCDE3-FC43-4BF8-82EE-40559E35786A@.microsoft.com...
> How do I backup and dump the transaction log? If I look at the log for
each
> container the Metabase log is 21.6 Gig and the Distribution log is 11.2
Gig.
> I would like to dump the transaction log and start off clean.|||KMD
Sounds like your logs have got out of hand. Have a look at these articles
they should help.
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/defaul...kb;en-us;256650
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/defaul...kb;en-us;272318
http://www.mssqlserver.com/faq/logs-shrinklog.asp
Hope this helps
John
"Uri Dimant" wrote:

> Hi
> Please read How to create a transaction log backup in the BOL
>
> "KMD" <KMD@.discussions.microsoft.com> wrote in message
> news:6FBBCDE3-FC43-4BF8-82EE-40559E35786A@.microsoft.com...
> each
> Gig.
>
>|||Number 1, back up your database (Just in case)!!
2) Detach the database via Enterprise Manager (Right CLick the DB to which
the log relates 'All Tasks)
3) Via explorer go to where the .MDF and .LDF fiels are stored for the DB
(Usually program files/Micrososft SQL Server/MSSQL/Data
4) delete the .LDF file
5) Via Enterprise Manager Right Click on Databases icon and select Attach
Database. Reattach the database by pointing it to the .MDF file, you will
receive a message saying the .LDF is missing, or something similar, just
accept this and it will create a new .LDF file of abut a MB.
6) Unless you have any other reason not to set your DB to SIMPLE, then go to
the Properties option and select the Options tab and set the Recovery model
to Simple. This will prevent your log growing out of all proportion again.
Cheers
Coburndavis
"KMD" wrote:

> How do I backup and dump the transaction log? If I look at the log for ea
ch
> container the Metabase log is 21.6 Gig and the Distribution log is 11.2 Gi
g.
> I would like to dump the transaction log and start off clean.|||Number 1, back up your database (Just in case)!!
2) Detach the database via Enterprise Manager (Right CLick the DB to which
the log relates 'All Tasks)
3) Via explorer go to where the .MDF and .LDF fiels are stored for the DB
(Usually program files/Micrososft SQL Server/MSSQL/Data
4) delete the .LDF file
5) Via Enterprise Manager Right Click on Databases icon and select Attach
Database. Reattach the database by pointing it to the .MDF file, you will
receive a message saying the .LDF is missing, or something similar, just
accept this and it will create a new .LDF file of abut a MB.
6) Unless you have any other reason not to set your DB to SIMPLE, then go to
the Properties option and select the Options tab and set the Recovery model
to Simple. This will prevent your log growing out of all proportion again.
Cheers
Coburndavis
--
Cheers
Coburndavis
"KMD" wrote:

> How do I backup and dump the transaction log? If I look at the log for ea
ch
> container the Metabase log is 21.6 Gig and the Distribution log is 11.2 Gi
g.
> I would like to dump the transaction log and start off clean.|||Number 1, back up your database (Just in case)!!
2) Detach the database via Enterprise Manager (Right CLick the DB to which
the log relates 'All Tasks)
3) Via explorer go to where the .MDF and .LDF fiels are stored for the DB
(Usually program files/Micrososft SQL Server/MSSQL/Data
4) delete the .LDF file
5) Via Enterprise Manager Right Click on Databases icon and select Attach
Database. Reattach the database by pointing it to the .MDF file, you will
receive a message saying the .LDF is missing, or something similar, just
accept this and it will create a new .LDF file of abut a MB.
6) Unless you have any other reason not to set your DB to SIMPLE, then go to
the Properties option and select the Options tab and set the Recovery model
to Simple. This will prevent your log growing out of all proportion again.
Cheers
Coburndavis
"KMD" wrote:

> How do I backup and dump the transaction log? If I look at the log for ea
ch
> container the Metabase log is 21.6 Gig and the Distribution log is 11.2 Gi
g.
> I would like to dump the transaction log and start off clean.|||KMD
I am sorry this is a very bad way to do it. Look at the articles I sent you
and shrink the log properly. Deleting the log file and trying to attach a
single file database is not a good idea unless you have no other options, yo
u
only need to look through historical messages on here to find lots of people
who have had problems trying to attach a single file database when they have
lost or damaged the transaction log.
If these are production databases you should not be setting recovery mode to
simple, unless you have a very quick and easy method to re-create the
database, Instead try to understand what transaction log backups are used fo
r
and how you can best use them in your environment. In general the only
databases that usually are set to simple recovery mode are development ones
and ones used for data warehousing were the data is static.
Even if you don't have a DR server, being able to restore to a point in time
on your existing server will be someting you may be very happy to have in
case of a disaster.
Here is a link to a good article on DR. This has links to several other
articles which should help you see the bigger picture.
"COBURNDAVIS" wrote:
[vbcol=seagreen]
> Number 1, back up your database (Just in case)!!
> 2) Detach the database via Enterprise Manager (Right CLick the DB to which
> the log relates 'All Tasks)
> 3) Via explorer go to where the .MDF and .LDF fiels are stored for the DB
> (Usually program files/Micrososft SQL Server/MSSQL/Data
> 4) delete the .LDF file
> 5) Via Enterprise Manager Right Click on Databases icon and select Attach
> Database. Reattach the database by pointing it to the .MDF file, you will
> receive a message saying the .LDF is missing, or something similar, just
> accept this and it will create a new .LDF file of abut a MB.
> 6) Unless you have any other reason not to set your DB to SIMPLE, then go
to
> the Properties option and select the Options tab and set the Recovery mode
l
> to Simple. This will prevent your log growing out of all proportion again
.
> Cheers
> Coburndavis
> --
> Cheers
> Coburndavis
>
> "KMD" wrote:
>|||I have a full backup of my databases, would it not be feasible to dump the
transaction log all together. The server recovery model is setup for Simple
.
Once I dump the transaction log, everything is back to normal, then I could
change the model to Full but then start to perform Transaction Log backups.
How does one perform a transaction log backup? Is there only one transactio
n
log back to perform or do you need to perform a log backup of all databases
such as Metabase and Distribution?
"John Bandettini" wrote:
[vbcol=seagreen]
> KMD
> I am sorry this is a very bad way to do it. Look at the articles I sent yo
u
> and shrink the log properly. Deleting the log file and trying to attach a
> single file database is not a good idea unless you have no other options,
you
> only need to look through historical messages on here to find lots of peop
le
> who have had problems trying to attach a single file database when they ha
ve
> lost or damaged the transaction log.
> If these are production databases you should not be setting recovery mode
to
> simple, unless you have a very quick and easy method to re-create the
> database, Instead try to understand what transaction log backups are used
for
> and how you can best use them in your environment. In general the only
> databases that usually are set to simple recovery mode are development one
s
> and ones used for data warehousing were the data is static.
> Even if you don't have a DR server, being able to restore to a point in ti
me
> on your existing server will be someting you may be very happy to have in
> case of a disaster.
> Here is a link to a good article on DR. This has links to several other
> articles which should help you see the bigger picture.
> "COBURNDAVIS" wrote:
>|||KMD
If your database recovery model is simple you can not backup the transaction
log. You can only backup transaction logs in full or bulk logged recovery
mode. In simple mode you still write to the transaction log, but when a
checkpoint occurs all completed transactions are removed from the log. In
simple mode you should not need to perform transaction log backups.
What size where your transaction logs originally? How much of their current
size is being used? You might just need to shrink them. Do you perform some
very large updates? If you do and you perform them as one transaction that
may be why your logs are as large as they are.
Do you have any open transactions that may have caused the logs to grow? Use
dbcc opentran to check for open transactions.
As for what databases you should perform transaction log backups on, all
that are in full recovery mode. Generally all production databases should be
in full recovery mode. Main exception to that would be static historical
databases or databases that are only updated once in a time period (daily,
weekly, monthly or when ever) and have a full backup taken following the
update.
Hope this helps
John
"KMD" wrote:
[vbcol=seagreen]
> I have a full backup of my databases, would it not be feasible to dump the
> transaction log all together. The server recovery model is setup for Simp
le.
> Once I dump the transaction log, everything is back to normal, then I cou
ld
> change the model to Full but then start to perform Transaction Log backups
.
> How does one perform a transaction log backup? Is there only one transact
ion
> log back to perform or do you need to perform a log backup of all database
s
> such as Metabase and Distribution?
> "John Bandettini" wrote:
>