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/default...b;en-us;256650
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default...b;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 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.
|||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 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.
|||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 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
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, you
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 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 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 model
> 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 transaction
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 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, you
> 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 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 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:
|||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 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 transaction
> log back to perform or do you need to perform a log backup of all databases
> such as Metabase and Distribution?
> "John Bandettini" wrote:

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/default.aspx?scid=kb;en-us;256650
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=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...
> > 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.
>
>|||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 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.|||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 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.|||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 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
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, you
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 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 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:
> 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 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.|||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 transaction
log back to perform or do you need to perform a log backup of all databases
such as Metabase and Distribution?
"John Bandettini" wrote:
> 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, you
> 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 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 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:
> > 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 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
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:
> 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 transaction
> log back to perform or do you need to perform a log backup of all databases
> such as Metabase and Distribution?
> "John Bandettini" wrote:
> > 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, you
> > 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 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 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:
> >
> > > 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 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.

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:
>

Backup intermittently fails. OS Error:32

Hi,
We've been having an usual problem with our SQL server
backup. The database is around 41Gb, which does a full
database dump to a .bak daily, and then transaction log
dumps every 15mins. However, intermittently the full
backup will fail with the following:
nightlyrun.log
Msg 3201, Level 16, State 1, Server EARTH, Line 24
Cannot open backup device 'dratlas_backup'. Device error
or
device off-line. See the SQL Server error log for more
details.
Msg 3013, Level 16, State 1, Server EARTH, Line 24
BACKUP DATABASE is terminating abnormally.
ERRORLOG
2004-04-28 02:27:58.27 spid58 DBCC CHECKDB (hrlink)
executed by sa found 0 errors and repaired 0 errors.
2004-04-28 02:28:00.77 backup Database log truncated:
Database: hrlink.
2004-04-28 02:28:13.16 spid58
BackupDiskFile::CreateMedia: Backup
device '\\jupiter\dump$\dratlas_backup.bak' failed to
create. Operating system error = 32(error not found).
2004-04-28 02:28:13.18 backup BACKUP failed to
complete the command
The network share is always 100% available to the SQL
server on a 1Gb core network. As I said, this is
intermittent, and may fail once a week, or five times a
week, and nothing has changed on the servers or network.
The knowledge base does not include any information about
the OS error or Msg 3201
Any help will be much appreciated.
Thankyou.
Regards,
David
A single network interruption can an error such as this. With a 41GB file
being backed up over the network there is plenty of time for this to occur.
Another common cause is if you are reusing an existing device (either
appending or initing) and you have a tape backup trying to backup that
device at the same time. If these files are backed up to tape by a
different process you may want to check the timing of it compared to the
backup failure times.
Andrew J. Kelly SQL MVP
"David Meldrum" <david.meldrum@.skilled.com.au> wrote in message
news:526d01c42cb4$07a18da0$a301280a@.phx.gbl...
> Hi,
> We've been having an usual problem with our SQL server
> backup. The database is around 41Gb, which does a full
> database dump to a .bak daily, and then transaction log
> dumps every 15mins. However, intermittently the full
> backup will fail with the following:
> nightlyrun.log
> Msg 3201, Level 16, State 1, Server EARTH, Line 24
> Cannot open backup device 'dratlas_backup'. Device error
> or
> device off-line. See the SQL Server error log for more
> details.
> Msg 3013, Level 16, State 1, Server EARTH, Line 24
> BACKUP DATABASE is terminating abnormally.
> ERRORLOG
> 2004-04-28 02:27:58.27 spid58 DBCC CHECKDB (hrlink)
> executed by sa found 0 errors and repaired 0 errors.
> 2004-04-28 02:28:00.77 backup Database log truncated:
> Database: hrlink.
> 2004-04-28 02:28:13.16 spid58
> BackupDiskFile::CreateMedia: Backup
> device '\\jupiter\dump$\dratlas_backup.bak' failed to
> create. Operating system error = 32(error not found).
> 2004-04-28 02:28:13.18 backup BACKUP failed to
> complete the command
> The network share is always 100% available to the SQL
> server on a 1Gb core network. As I said, this is
> intermittent, and may fail once a week, or five times a
> week, and nothing has changed on the servers or network.
> The knowledge base does not include any information about
> the OS error or Msg 3201
> Any help will be much appreciated.
> Thankyou.
> Regards,
> David

Backup intermittently fails. OS Error:32

Hi,
We've been having an usual problem with our SQL server
backup. The database is around 41Gb, which does a full
database dump to a .bak daily, and then transaction log
dumps every 15mins. However, intermittently the full
backup will fail with the following:
nightlyrun.log
Msg 3201, Level 16, State 1, Server EARTH, Line 24
Cannot open backup device 'dratlas_backup'. Device error
or
device off-line. See the SQL Server error log for more
details.
Msg 3013, Level 16, State 1, Server EARTH, Line 24
BACKUP DATABASE is terminating abnormally.
ERRORLOG
2004-04-28 02:27:58.27 spid58 DBCC CHECKDB (hrlink)
executed by sa found 0 errors and repaired 0 errors.
2004-04-28 02:28:00.77 backup Database log truncated:
Database: hrlink.
2004-04-28 02:28:13.16 spid58
BackupDiskFile::CreateMedia: Backup
device '\\jupiter\dump$\dratlas_backup.bak' failed to
create. Operating system error = 32(error not found).
2004-04-28 02:28:13.18 backup BACKUP failed to
complete the command
The network share is always 100% available to the SQL
server on a 1Gb core network. As I said, this is
intermittent, and may fail once a week, or five times a
week, and nothing has changed on the servers or network.
The knowledge base does not include any information about
the OS error or Msg 3201
Any help will be much appreciated.
Thankyou.
Regards,
DavidA single network interruption can an error such as this. With a 41GB file
being backed up over the network there is plenty of time for this to occur.
Another common cause is if you are reusing an existing device (either
appending or initing) and you have a tape backup trying to backup that
device at the same time. If these files are backed up to tape by a
different process you may want to check the timing of it compared to the
backup failure times.
--
Andrew J. Kelly SQL MVP
"David Meldrum" <david.meldrum@.skilled.com.au> wrote in message
news:526d01c42cb4$07a18da0$a301280a@.phx.gbl...
> Hi,
> We've been having an usual problem with our SQL server
> backup. The database is around 41Gb, which does a full
> database dump to a .bak daily, and then transaction log
> dumps every 15mins. However, intermittently the full
> backup will fail with the following:
> nightlyrun.log
> Msg 3201, Level 16, State 1, Server EARTH, Line 24
> Cannot open backup device 'dratlas_backup'. Device error
> or
> device off-line. See the SQL Server error log for more
> details.
> Msg 3013, Level 16, State 1, Server EARTH, Line 24
> BACKUP DATABASE is terminating abnormally.
> ERRORLOG
> 2004-04-28 02:27:58.27 spid58 DBCC CHECKDB (hrlink)
> executed by sa found 0 errors and repaired 0 errors.
> 2004-04-28 02:28:00.77 backup Database log truncated:
> Database: hrlink.
> 2004-04-28 02:28:13.16 spid58
> BackupDiskFile::CreateMedia: Backup
> device '\\jupiter\dump$\dratlas_backup.bak' failed to
> create. Operating system error = 32(error not found).
> 2004-04-28 02:28:13.18 backup BACKUP failed to
> complete the command
> The network share is always 100% available to the SQL
> server on a 1Gb core network. As I said, this is
> intermittent, and may fail once a week, or five times a
> week, and nothing has changed on the servers or network.
> The knowledge base does not include any information about
> the OS error or Msg 3201
> Any help will be much appreciated.
> Thankyou.
> Regards,
> David

Backup intermittently fails. OS Error:32

Hi,
We've been having an usual problem with our SQL server
backup. The database is around 41Gb, which does a full
database dump to a .bak daily, and then transaction log
dumps every 15mins. However, intermittently the full
backup will fail with the following:
nightlyrun.log
Msg 3201, Level 16, State 1, Server EARTH, Line 24
Cannot open backup device 'dratlas_backup'. Device error
or
device off-line. See the SQL Server error log for more
details.
Msg 3013, Level 16, State 1, Server EARTH, Line 24
BACKUP DATABASE is terminating abnormally.
ERRORLOG
2004-04-28 02:27:58.27 spid58 DBCC CHECKDB (hrlink)
executed by sa found 0 errors and repaired 0 errors.
2004-04-28 02:28:00.77 backup Database log truncated:
Database: hrlink.
2004-04-28 02:28:13.16 spid58
BackupDiskFile::CreateMedia: Backup
device '\\jupiter\dump$\dratlas_backup.bak' failed to
create. Operating system error = 32(error not found).
2004-04-28 02:28:13.18 backup BACKUP failed to
complete the command
The network share is always 100% available to the SQL
server on a 1Gb core network. As I said, this is
intermittent, and may fail once a week, or five times a
week, and nothing has changed on the servers or network.
The knowledge base does not include any information about
the OS error or Msg 3201
Any help will be much appreciated.
Thankyou.
Regards,
DavidA single network interruption can an error such as this. With a 41GB file
being backed up over the network there is plenty of time for this to occur.
Another common cause is if you are reusing an existing device (either
appending or initing) and you have a tape backup trying to backup that
device at the same time. If these files are backed up to tape by a
different process you may want to check the timing of it compared to the
backup failure times.
Andrew J. Kelly SQL MVP
"David Meldrum" <david.meldrum@.skilled.com.au> wrote in message
news:526d01c42cb4$07a18da0$a301280a@.phx.gbl...
> Hi,
> We've been having an usual problem with our SQL server
> backup. The database is around 41Gb, which does a full
> database dump to a .bak daily, and then transaction log
> dumps every 15mins. However, intermittently the full
> backup will fail with the following:
> nightlyrun.log
> Msg 3201, Level 16, State 1, Server EARTH, Line 24
> Cannot open backup device 'dratlas_backup'. Device error
> or
> device off-line. See the SQL Server error log for more
> details.
> Msg 3013, Level 16, State 1, Server EARTH, Line 24
> BACKUP DATABASE is terminating abnormally.
> ERRORLOG
> 2004-04-28 02:27:58.27 spid58 DBCC CHECKDB (hrlink)
> executed by sa found 0 errors and repaired 0 errors.
> 2004-04-28 02:28:00.77 backup Database log truncated:
> Database: hrlink.
> 2004-04-28 02:28:13.16 spid58
> BackupDiskFile::CreateMedia: Backup
> device '\\jupiter\dump$\dratlas_backup.bak' failed to
> create. Operating system error = 32(error not found).
> 2004-04-28 02:28:13.18 backup BACKUP failed to
> complete the command
> The network share is always 100% available to the SQL
> server on a 1Gb core network. As I said, this is
> intermittent, and may fail once a week, or five times a
> week, and nothing has changed on the servers or network.
> The knowledge base does not include any information about
> the OS error or Msg 3201
> Any help will be much appreciated.
> Thankyou.
> Regards,
> David