How to truncate DB logs in SQL 2000.

Can someone tell me how to truncate the DB logs in SQL 2000. I have a server with 35 GB of transaction log
Thanks.Check out below KB articles:
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
Log File Grows too big
http://www.support.microsoft.com/?id=317375
Log file filling up
http://www.support.microsoft.com/?id=110139
Considerations for Autogrow and AutoShrink
http://www.support.microsoft.com/?id=315512
http://www.mssqlserver.com/faq/logs-shrinklog.asp
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"HUBCO" <anonymous@.discussions.microsoft.com> wrote in message
news:07E35360-B62D-43FC-8619-97CBCF4546EC@.microsoft.com...
> Can someone tell me how to truncate the DB logs in SQL 2000. I have a server with 35 GB of
transaction log.
> Thanks.|||http://www.aspfaq.com/2471
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"HUBCO" <anonymous@.discussions.microsoft.com> wrote in message
news:07E35360-B62D-43FC-8619-97CBCF4546EC@.microsoft.com...
> Can someone tell me how to truncate the DB logs in SQL 2000. I have a
server with 35 GB of transaction log.
> Thanks.|||The simplest and easiest way is to go into your databases
properties and set the recovery to 'simple' then apply
the changes. This will allow sql to automatically
truncate the tran log at the next checkpoint run.
Once that is complete(you will need to monitor the db to
find out when then occurs but it should happen fast) you
can go back in and change the recovery mode back to full
or whatever you had it set at before you started.
>--Original Message--
>Can someone tell me how to truncate the DB logs in SQL
2000. I have a server with 35 GB of transaction log.
>Thanks.
>.
>

How to truncate a transaction log using Simple Recovery Model?

I have a SQL Server database on a server with limited disk space and I do no
t
need the transaction log. Is there a TSQL command to truncate the log? I a
m
using a Simple Recovery Model, but it is still keeping a transaction log.
Do I have to force a check point programatically?
Any suggestions would be appreciated.
Thanks in advance,
WayneMHi
Simple Recovery Mode forces the log to be truncated at every checkpoint.
Checkpoint runs by default every minute. Only the inactive transactions can
be truncated.
But, If you have a transaction that affects 100'000 records, until the
transaction is complete (committed or rolled back), those records can not be
truncated. The log has to store them all. So, keep your transactions short.
How big is your DB, your transaction log?
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"WayneM" <WayneM@.discussions.microsoft.com> wrote in message
news:31259267-F903-4773-8842-5BF2EFA896A4@.microsoft.com...
> I have a SQL Server database on a server with limited disk space and I do
not
> need the transaction log. Is there a TSQL command to truncate the log? I
am
> using a Simple Recovery Model, but it is still keeping a transaction log.
> Do I have to force a check point programatically?
> Any suggestions would be appreciated.
> Thanks in advance,
> WayneM|||Just because you don't need the tran log doesn't mean SQL Server doesn't<g>.
The tran log is crucial to proper operation. I am not sure what you are
asking for here but I think you are misusing the term truncation. I suspect
what you really what is to shrink the log. A truncation does not do that and
in Simple mode it will truncate all on it's own when it gets about 70% full.
If you want to shrink the size of the log then use DBCC SHRINKFILE. Just
make sure it is large enough to hold the largest transaction you will do or
it will just grow again.
Andrew J. Kelly SQL MVP
"WayneM" <WayneM@.discussions.microsoft.com> wrote in message
news:31259267-F903-4773-8842-5BF2EFA896A4@.microsoft.com...
>I have a SQL Server database on a server with limited disk space and I do
>not
> need the transaction log. Is there a TSQL command to truncate the log? I
> am
> using a Simple Recovery Model, but it is still keeping a transaction log.
> Do I have to force a check point programatically?
> Any suggestions would be appreciated.
> Thanks in advance,
> WayneM|||Mike,
My db is 1.4GB and the log is 2.0 GB. This in itself is not big, but there
are several databases in the server. Each DB has files that are parsed and
loaded into the database, so the concept of transactions does not apply here
.
We just keep the files and re-parse and load them if necessary.
Thanks,
Wayne
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> Simple Recovery Mode forces the log to be truncated at every checkpoint.
> Checkpoint runs by default every minute. Only the inactive transactions ca
n
> be truncated.
> But, If you have a transaction that affects 100'000 records, until the
> transaction is complete (committed or rolled back), those records can not
be
> truncated. The log has to store them all. So, keep your transactions short
.
> How big is your DB, your transaction log?
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "WayneM" <WayneM@.discussions.microsoft.com> wrote in message
> news:31259267-F903-4773-8842-5BF2EFA896A4@.microsoft.com...
> not
> am
>
>|||> so the concept of transactions does not apply here.
You don't seem to understand how Sql Server works as compare to some smaller
database engines. Everything is always in a transaction in Sql Server. It
may be explicit in which you manually issue a Begin Tran and a commit Tran
to wrap two or more statements to keep them Atomic. But even if you don't
each statement is Atomic by itself and is wrapped in a transaction behind
the scenes. If you bulk load 100K rows they will all be in a single
transaction whether you want them to be or not. This activity is always
logged in one form or another. If you do a minimally logged load the
extents that are affected are logged. Otherwise it is fully logged. You
need to meet the requirements listed in BooksOnLine under "minimally logged
bulk copy" to do this though. If you really do just load the files and the
db is replaceable then you should ensure your loads meet these conditions.
That will speed up the load and and allow you to keep the log file smaller.
Andrew J. Kelly SQL MVP
"WayneM" <WayneM@.discussions.microsoft.com> wrote in message
news:AAF9E36F-4784-43B1-B662-E9332884BB41@.microsoft.com...[vbcol=seagreen]
> Mike,
> My db is 1.4GB and the log is 2.0 GB. This in itself is not big, but
> there
> are several databases in the server. Each DB has files that are parsed
> and
> loaded into the database, so the concept of transactions does not apply
> here.
> We just keep the files and re-parse and load them if necessary.
> Thanks,
> Wayne
> "Mike Epprecht (SQL MVP)" wrote:
>|||If you just want to get rid of the log. Then (after you have backed up the
DB) 'Detach Database' and then go the .LDF file, via explorer, usually in th
e
Program File\MSSQLServer|Data path, and delete it.
Then go back to Enterprise Manger and right clidk on Databases and attach
the DB, it will tell you it can't find the .LDF and will creae one of 1MB.
I am assuming that as you are using Simple Recovery, it either is not a
Production DB, or it only contains static data.
--
Cheers
Coburndavis
"WayneM" wrote:

> I have a SQL Server database on a server with limited disk space and I do
not
> need the transaction log. Is there a TSQL command to truncate the log? I
am
> using a Simple Recovery Model, but it is still keeping a transaction log.
> Do I have to force a check point programatically?
> Any suggestions would be appreciated.
> Thanks in advance,
> WayneM|||Try backing up the DB, then use Enterprise Manager (EM) to 'Detach Dabtabase
'
go to the .LDF (ie the log file) via explorer and delete it.
Go back to EM right click on Databases and 'Attach DB (point it to the .MDF
for the DB) it will tell you there is no .LDF, accept this and it will creat
e
one of 1MB.
I am assuming as your recovery mode is Simple it is not a production DB, or
that the data is static
--
Cheers
Coburndavis
"WayneM" wrote:

> I have a SQL Server database on a server with limited disk space and I do
not
> need the transaction log. Is there a TSQL command to truncate the log? I
am
> using a Simple Recovery Model, but it is still keeping a transaction log.
> Do I have to force a check point programatically?
> Any suggestions would be appreciated.
> Thanks in advance,
> WayneM|||Try backing up the DB, then use Enterprise Manager (EM) to 'Detach Dabtabase
'
go to the .LDF (ie the log file) via explorer and delete it.
Go back to EM right click on Databases and 'Attach DB (point it to the .MDF
for the DB) it will tell you there is no .LDF, accept this and it will creat
e
one of 1MB.
I am assuming, as your recovery mode is Simple, it is not a production DB,
or that the data is static
Cheers
Coburndavis
"WayneM" wrote:

> I have a SQL Server database on a server with limited disk space and I do
not
> need the transaction log. Is there a TSQL command to truncate the log? I
am
> using a Simple Recovery Model, but it is still keeping a transaction log.
> Do I have to force a check point programatically?
> Any suggestions would be appreciated.
> Thanks in advance,
> WayneM|||Try backing up the DB, then use Enterprise Manager (EM) to 'Detach Dabtabase
'
go to the .LDF (ie the log file) via explorer and delete it.
Go back to EM right click on Databases and 'Attach DB (point it to the .MDF
for the DB) it will tell you there is no .LDF, accept this and it will creat
e
one of 1MB.
I am assuming, as your recovery mode is Simple, it is not a production DB,
or that the data is static
Cheers
Coburndavis
"WayneM" wrote:

> I have a SQL Server database on a server with limited disk space and I do
not
> need the transaction log. Is there a TSQL command to truncate the log? I
am
> using a Simple Recovery Model, but it is still keeping a transaction log.
> Do I have to force a check point programatically?
> Any suggestions would be appreciated.
> Thanks in advance,
> WayneM|||Try backing up the DB, then use Enterprise Manager (EM) to 'Detach Dabtabase
'
go to the .LDF (ie the log file) via explorer and delete it.
Go back to EM right click on Databases and 'Attach DB (point it to the .MDF
for the DB) it will tell you there is no .LDF, accept this and it will creat
e
one of 1MB.
I am assuming, as your recovery mode is Simple, it is not a production DB,
or that the data is static
Cheers
Coburndavis
"WayneM" wrote:

> I have a SQL Server database on a server with limited disk space and I do
not
> need the transaction log. Is there a TSQL command to truncate the log? I
am
> using a Simple Recovery Model, but it is still keeping a transaction log.
> Do I have to force a check point programatically?
> Any suggestions would be appreciated.
> Thanks in advance,
> WayneMsql

How to truncate a transaction log using Simple Recovery Model?

I have a SQL Server database on a server with limited disk space and I do not
need the transaction log. Is there a TSQL command to truncate the log? I am
using a Simple Recovery Model, but it is still keeping a transaction log.
Do I have to force a check point programatically?
Any suggestions would be appreciated.
Thanks in advance,
WayneM
Hi
Simple Recovery Mode forces the log to be truncated at every checkpoint.
Checkpoint runs by default every minute. Only the inactive transactions can
be truncated.
But, If you have a transaction that affects 100'000 records, until the
transaction is complete (committed or rolled back), those records can not be
truncated. The log has to store them all. So, keep your transactions short.
How big is your DB, your transaction log?
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"WayneM" <WayneM@.discussions.microsoft.com> wrote in message
news:31259267-F903-4773-8842-5BF2EFA896A4@.microsoft.com...
> I have a SQL Server database on a server with limited disk space and I do
not
> need the transaction log. Is there a TSQL command to truncate the log? I
am
> using a Simple Recovery Model, but it is still keeping a transaction log.
> Do I have to force a check point programatically?
> Any suggestions would be appreciated.
> Thanks in advance,
> WayneM
|||Just because you don't need the tran log doesn't mean SQL Server doesn't<g>.
The tran log is crucial to proper operation. I am not sure what you are
asking for here but I think you are misusing the term truncation. I suspect
what you really what is to shrink the log. A truncation does not do that and
in Simple mode it will truncate all on it's own when it gets about 70% full.
If you want to shrink the size of the log then use DBCC SHRINKFILE. Just
make sure it is large enough to hold the largest transaction you will do or
it will just grow again.
Andrew J. Kelly SQL MVP
"WayneM" <WayneM@.discussions.microsoft.com> wrote in message
news:31259267-F903-4773-8842-5BF2EFA896A4@.microsoft.com...
>I have a SQL Server database on a server with limited disk space and I do
>not
> need the transaction log. Is there a TSQL command to truncate the log? I
> am
> using a Simple Recovery Model, but it is still keeping a transaction log.
> Do I have to force a check point programatically?
> Any suggestions would be appreciated.
> Thanks in advance,
> WayneM
|||Mike,
My db is 1.4GB and the log is 2.0 GB. This in itself is not big, but there
are several databases in the server. Each DB has files that are parsed and
loaded into the database, so the concept of transactions does not apply here.
We just keep the files and re-parse and load them if necessary.
Thanks,
Wayne
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> Simple Recovery Mode forces the log to be truncated at every checkpoint.
> Checkpoint runs by default every minute. Only the inactive transactions can
> be truncated.
> But, If you have a transaction that affects 100'000 records, until the
> transaction is complete (committed or rolled back), those records can not be
> truncated. The log has to store them all. So, keep your transactions short.
> How big is your DB, your transaction log?
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "WayneM" <WayneM@.discussions.microsoft.com> wrote in message
> news:31259267-F903-4773-8842-5BF2EFA896A4@.microsoft.com...
> not
> am
>
>
|||> so the concept of transactions does not apply here.
You don't seem to understand how Sql Server works as compare to some smaller
database engines. Everything is always in a transaction in Sql Server. It
may be explicit in which you manually issue a Begin Tran and a commit Tran
to wrap two or more statements to keep them Atomic. But even if you don't
each statement is Atomic by itself and is wrapped in a transaction behind
the scenes. If you bulk load 100K rows they will all be in a single
transaction whether you want them to be or not. This activity is always
logged in one form or another. If you do a minimally logged load the
extents that are affected are logged. Otherwise it is fully logged. You
need to meet the requirements listed in BooksOnLine under "minimally logged
bulk copy" to do this though. If you really do just load the files and the
db is replaceable then you should ensure your loads meet these conditions.
That will speed up the load and and allow you to keep the log file smaller.
Andrew J. Kelly SQL MVP
"WayneM" <WayneM@.discussions.microsoft.com> wrote in message
news:AAF9E36F-4784-43B1-B662-E9332884BB41@.microsoft.com...[vbcol=seagreen]
> Mike,
> My db is 1.4GB and the log is 2.0 GB. This in itself is not big, but
> there
> are several databases in the server. Each DB has files that are parsed
> and
> loaded into the database, so the concept of transactions does not apply
> here.
> We just keep the files and re-parse and load them if necessary.
> Thanks,
> Wayne
> "Mike Epprecht (SQL MVP)" wrote:
|||If you just want to get rid of the log. Then (after you have backed up the
DB) 'Detach Database' and then go the .LDF file, via explorer, usually in the
Program File\MSSQLServer|Data path, and delete it.
Then go back to Enterprise Manger and right clidk on Databases and attach
the DB, it will tell you it can't find the .LDF and will creae one of 1MB.
I am assuming that as you are using Simple Recovery, it either is not a
Production DB, or it only contains static data.
Cheers
Coburndavis
"WayneM" wrote:

> I have a SQL Server database on a server with limited disk space and I do not
> need the transaction log. Is there a TSQL command to truncate the log? I am
> using a Simple Recovery Model, but it is still keeping a transaction log.
> Do I have to force a check point programatically?
> Any suggestions would be appreciated.
> Thanks in advance,
> WayneM
|||Try backing up the DB, then use Enterprise Manager (EM) to 'Detach Dabtabase'
go to the .LDF (ie the log file) via explorer and delete it.
Go back to EM right click on Databases and 'Attach DB (point it to the .MDF
for the DB) it will tell you there is no .LDF, accept this and it will create
one of 1MB.
I am assuming as your recovery mode is Simple it is not a production DB, or
that the data is static
Cheers
Coburndavis
"WayneM" wrote:

> I have a SQL Server database on a server with limited disk space and I do not
> need the transaction log. Is there a TSQL command to truncate the log? I am
> using a Simple Recovery Model, but it is still keeping a transaction log.
> Do I have to force a check point programatically?
> Any suggestions would be appreciated.
> Thanks in advance,
> WayneM
|||Try backing up the DB, then use Enterprise Manager (EM) to 'Detach Dabtabase'
go to the .LDF (ie the log file) via explorer and delete it.
Go back to EM right click on Databases and 'Attach DB (point it to the .MDF
for the DB) it will tell you there is no .LDF, accept this and it will create
one of 1MB.
I am assuming, as your recovery mode is Simple, it is not a production DB,
or that the data is static
Cheers
Coburndavis
"WayneM" wrote:

> I have a SQL Server database on a server with limited disk space and I do not
> need the transaction log. Is there a TSQL command to truncate the log? I am
> using a Simple Recovery Model, but it is still keeping a transaction log.
> Do I have to force a check point programatically?
> Any suggestions would be appreciated.
> Thanks in advance,
> WayneM
|||Try backing up the DB, then use Enterprise Manager (EM) to 'Detach Dabtabase'
go to the .LDF (ie the log file) via explorer and delete it.
Go back to EM right click on Databases and 'Attach DB (point it to the .MDF
for the DB) it will tell you there is no .LDF, accept this and it will create
one of 1MB.
I am assuming, as your recovery mode is Simple, it is not a production DB,
or that the data is static
Cheers
Coburndavis
"WayneM" wrote:

> I have a SQL Server database on a server with limited disk space and I do not
> need the transaction log. Is there a TSQL command to truncate the log? I am
> using a Simple Recovery Model, but it is still keeping a transaction log.
> Do I have to force a check point programatically?
> Any suggestions would be appreciated.
> Thanks in advance,
> WayneM
|||Try backing up the DB, then use Enterprise Manager (EM) to 'Detach Dabtabase'
go to the .LDF (ie the log file) via explorer and delete it.
Go back to EM right click on Databases and 'Attach DB (point it to the .MDF
for the DB) it will tell you there is no .LDF, accept this and it will create
one of 1MB.
I am assuming, as your recovery mode is Simple, it is not a production DB,
or that the data is static
Cheers
Coburndavis
"WayneM" wrote:

> I have a SQL Server database on a server with limited disk space and I do not
> need the transaction log. Is there a TSQL command to truncate the log? I am
> using a Simple Recovery Model, but it is still keeping a transaction log.
> Do I have to force a check point programatically?
> Any suggestions would be appreciated.
> Thanks in advance,
> WayneM

How to truncate a transaction log using Simple Recovery Model?

I have a SQL Server database on a server with limited disk space and I do not
need the transaction log. Is there a TSQL command to truncate the log? I am
using a Simple Recovery Model, but it is still keeping a transaction log.
Do I have to force a check point programatically?
Any suggestions would be appreciated.
Thanks in advance,
WayneMHi
Simple Recovery Mode forces the log to be truncated at every checkpoint.
Checkpoint runs by default every minute. Only the inactive transactions can
be truncated.
But, If you have a transaction that affects 100'000 records, until the
transaction is complete (committed or rolled back), those records can not be
truncated. The log has to store them all. So, keep your transactions short.
How big is your DB, your transaction log?
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"WayneM" <WayneM@.discussions.microsoft.com> wrote in message
news:31259267-F903-4773-8842-5BF2EFA896A4@.microsoft.com...
> I have a SQL Server database on a server with limited disk space and I do
not
> need the transaction log. Is there a TSQL command to truncate the log? I
am
> using a Simple Recovery Model, but it is still keeping a transaction log.
> Do I have to force a check point programatically?
> Any suggestions would be appreciated.
> Thanks in advance,
> WayneM|||Just because you don't need the tran log doesn't mean SQL Server doesn't<g>.
The tran log is crucial to proper operation. I am not sure what you are
asking for here but I think you are misusing the term truncation. I suspect
what you really what is to shrink the log. A truncation does not do that and
in Simple mode it will truncate all on it's own when it gets about 70% full.
If you want to shrink the size of the log then use DBCC SHRINKFILE. Just
make sure it is large enough to hold the largest transaction you will do or
it will just grow again.
--
Andrew J. Kelly SQL MVP
"WayneM" <WayneM@.discussions.microsoft.com> wrote in message
news:31259267-F903-4773-8842-5BF2EFA896A4@.microsoft.com...
>I have a SQL Server database on a server with limited disk space and I do
>not
> need the transaction log. Is there a TSQL command to truncate the log? I
> am
> using a Simple Recovery Model, but it is still keeping a transaction log.
> Do I have to force a check point programatically?
> Any suggestions would be appreciated.
> Thanks in advance,
> WayneM|||Mike,
My db is 1.4GB and the log is 2.0 GB. This in itself is not big, but there
are several databases in the server. Each DB has files that are parsed and
loaded into the database, so the concept of transactions does not apply here.
We just keep the files and re-parse and load them if necessary.
Thanks,
Wayne
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Simple Recovery Mode forces the log to be truncated at every checkpoint.
> Checkpoint runs by default every minute. Only the inactive transactions can
> be truncated.
> But, If you have a transaction that affects 100'000 records, until the
> transaction is complete (committed or rolled back), those records can not be
> truncated. The log has to store them all. So, keep your transactions short.
> How big is your DB, your transaction log?
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "WayneM" <WayneM@.discussions.microsoft.com> wrote in message
> news:31259267-F903-4773-8842-5BF2EFA896A4@.microsoft.com...
> > I have a SQL Server database on a server with limited disk space and I do
> not
> > need the transaction log. Is there a TSQL command to truncate the log? I
> am
> > using a Simple Recovery Model, but it is still keeping a transaction log.
> >
> > Do I have to force a check point programatically?
> >
> > Any suggestions would be appreciated.
> >
> > Thanks in advance,
> >
> > WayneM
>
>|||> so the concept of transactions does not apply here.
You don't seem to understand how Sql Server works as compare to some smaller
database engines. Everything is always in a transaction in Sql Server. It
may be explicit in which you manually issue a Begin Tran and a commit Tran
to wrap two or more statements to keep them Atomic. But even if you don't
each statement is Atomic by itself and is wrapped in a transaction behind
the scenes. If you bulk load 100K rows they will all be in a single
transaction whether you want them to be or not. This activity is always
logged in one form or another. If you do a minimally logged load the
extents that are affected are logged. Otherwise it is fully logged. You
need to meet the requirements listed in BooksOnLine under "minimally logged
bulk copy" to do this though. If you really do just load the files and the
db is replaceable then you should ensure your loads meet these conditions.
That will speed up the load and and allow you to keep the log file smaller.
--
Andrew J. Kelly SQL MVP
"WayneM" <WayneM@.discussions.microsoft.com> wrote in message
news:AAF9E36F-4784-43B1-B662-E9332884BB41@.microsoft.com...
> Mike,
> My db is 1.4GB and the log is 2.0 GB. This in itself is not big, but
> there
> are several databases in the server. Each DB has files that are parsed
> and
> loaded into the database, so the concept of transactions does not apply
> here.
> We just keep the files and re-parse and load them if necessary.
> Thanks,
> Wayne
> "Mike Epprecht (SQL MVP)" wrote:
>> Hi
>> Simple Recovery Mode forces the log to be truncated at every checkpoint.
>> Checkpoint runs by default every minute. Only the inactive transactions
>> can
>> be truncated.
>> But, If you have a transaction that affects 100'000 records, until the
>> transaction is complete (committed or rolled back), those records can not
>> be
>> truncated. The log has to store them all. So, keep your transactions
>> short.
>> How big is your DB, your transaction log?
>> Regards
>> --
>> Mike Epprecht, Microsoft SQL Server MVP
>> Zurich, Switzerland
>> IM: mike@.epprecht.net
>> MVP Program: http://www.microsoft.com/mvp
>> Blog: http://www.msmvps.com/epprecht/
>> "WayneM" <WayneM@.discussions.microsoft.com> wrote in message
>> news:31259267-F903-4773-8842-5BF2EFA896A4@.microsoft.com...
>> > I have a SQL Server database on a server with limited disk space and I
>> > do
>> not
>> > need the transaction log. Is there a TSQL command to truncate the log?
>> > I
>> am
>> > using a Simple Recovery Model, but it is still keeping a transaction
>> > log.
>> >
>> > Do I have to force a check point programatically?
>> >
>> > Any suggestions would be appreciated.
>> >
>> > Thanks in advance,
>> >
>> > WayneM
>>|||If you just want to get rid of the log. Then (after you have backed up the
DB) 'Detach Database' and then go the .LDF file, via explorer, usually in the
Program File\MSSQLServer|Data path, and delete it.
Then go back to Enterprise Manger and right clidk on Databases and attach
the DB, it will tell you it can't find the .LDF and will creae one of 1MB.
I am assuming that as you are using Simple Recovery, it either is not a
Production DB, or it only contains static data.
--
Cheers
Coburndavis
"WayneM" wrote:
> I have a SQL Server database on a server with limited disk space and I do not
> need the transaction log. Is there a TSQL command to truncate the log? I am
> using a Simple Recovery Model, but it is still keeping a transaction log.
> Do I have to force a check point programatically?
> Any suggestions would be appreciated.
> Thanks in advance,
> WayneM|||Try backing up the DB, then use Enterprise Manager (EM) to 'Detach Dabtabase'
go to the .LDF (ie the log file) via explorer and delete it.
Go back to EM right click on Databases and 'Attach DB (point it to the .MDF
for the DB) it will tell you there is no .LDF, accept this and it will create
one of 1MB.
I am assuming as your recovery mode is Simple it is not a production DB, or
that the data is static
--
Cheers
Coburndavis
"WayneM" wrote:
> I have a SQL Server database on a server with limited disk space and I do not
> need the transaction log. Is there a TSQL command to truncate the log? I am
> using a Simple Recovery Model, but it is still keeping a transaction log.
> Do I have to force a check point programatically?
> Any suggestions would be appreciated.
> Thanks in advance,
> WayneM|||Try backing up the DB, then use Enterprise Manager (EM) to 'Detach Dabtabase'
go to the .LDF (ie the log file) via explorer and delete it.
Go back to EM right click on Databases and 'Attach DB (point it to the .MDF
for the DB) it will tell you there is no .LDF, accept this and it will create
one of 1MB.
I am assuming, as your recovery mode is Simple, it is not a production DB,
or that the data is static
--
Cheers
Coburndavis
"WayneM" wrote:
> I have a SQL Server database on a server with limited disk space and I do not
> need the transaction log. Is there a TSQL command to truncate the log? I am
> using a Simple Recovery Model, but it is still keeping a transaction log.
> Do I have to force a check point programatically?
> Any suggestions would be appreciated.
> Thanks in advance,
> WayneM|||Try backing up the DB, then use Enterprise Manager (EM) to 'Detach Dabtabase'
go to the .LDF (ie the log file) via explorer and delete it.
Go back to EM right click on Databases and 'Attach DB (point it to the .MDF
for the DB) it will tell you there is no .LDF, accept this and it will create
one of 1MB.
I am assuming, as your recovery mode is Simple, it is not a production DB,
or that the data is static
--
Cheers
Coburndavis
"WayneM" wrote:
> I have a SQL Server database on a server with limited disk space and I do not
> need the transaction log. Is there a TSQL command to truncate the log? I am
> using a Simple Recovery Model, but it is still keeping a transaction log.
> Do I have to force a check point programatically?
> Any suggestions would be appreciated.
> Thanks in advance,
> WayneM|||Try backing up the DB, then use Enterprise Manager (EM) to 'Detach Dabtabase'
go to the .LDF (ie the log file) via explorer and delete it.
Go back to EM right click on Databases and 'Attach DB (point it to the .MDF
for the DB) it will tell you there is no .LDF, accept this and it will create
one of 1MB.
I am assuming, as your recovery mode is Simple, it is not a production DB,
or that the data is static
--
Cheers
Coburndavis
"WayneM" wrote:
> I have a SQL Server database on a server with limited disk space and I do not
> need the transaction log. Is there a TSQL command to truncate the log? I am
> using a Simple Recovery Model, but it is still keeping a transaction log.
> Do I have to force a check point programatically?
> Any suggestions would be appreciated.
> Thanks in advance,
> WayneM|||Try backing up the DB, then use Enterprise Manager (EM) to 'Detach Dabtabase'
go to the .LDF (ie the log file) via explorer and delete it.
Go back to EM right click on Databases and 'Attach DB (point it to the .MDF
for the DB) it will tell you there is no .LDF, accept this and it will create
one of 1MB.
I am assuming, as your recovery mode is Simple, it is not a production DB,
or that the data is static
--
Cheers
Coburndavis
"WayneM" wrote:
> I have a SQL Server database on a server with limited disk space and I do not
> need the transaction log. Is there a TSQL command to truncate the log? I am
> using a Simple Recovery Model, but it is still keeping a transaction log.
> Do I have to force a check point programatically?
> Any suggestions would be appreciated.
> Thanks in advance,
> WayneM|||Try backing up the DB, then use Enterprise Manager (EM) to 'Detach Dabtabase'
go to the .LDF (ie the log file) via explorer and delete it.
Go back to EM right click on Databases and 'Attach DB (point it to the .MDF
for the DB) it will tell you there is no .LDF, accept this and it will create
one of 1MB.
I am assuming, as your recovery mode is Simple, it is not a production DB,
or that the data is static
--
Cheers
Coburndavis
"WayneM" wrote:
> I have a SQL Server database on a server with limited disk space and I do not
> need the transaction log. Is there a TSQL command to truncate the log? I am
> using a Simple Recovery Model, but it is still keeping a transaction log.
> Do I have to force a check point programatically?
> Any suggestions would be appreciated.
> Thanks in advance,
> WayneM|||Try backing up the DB, then use Enterprise Manager (EM) to 'Detach Dabtabase'
go to the .LDF (ie the log file) via explorer and delete it.
Go back to EM right click on Databases and 'Attach DB (point it to the .MDF
for the DB) it will tell you there is no .LDF, accept this and it will create
one of 1MB.
I am assuming, as your recovery mode is Simple, it is not a production DB,
or that the data is static
--
Cheers
Coburndavis
"WayneM" wrote:
> I have a SQL Server database on a server with limited disk space and I do not
> need the transaction log. Is there a TSQL command to truncate the log? I am
> using a Simple Recovery Model, but it is still keeping a transaction log.
> Do I have to force a check point programatically?
> Any suggestions would be appreciated.
> Thanks in advance,
> WayneM|||Try backing up the DB, then use Enterprise Manager (EM) to 'Detach Dabtabase'
go to the .LDF (ie the log file) via explorer and delete it.
Go back to EM right click on Databases and 'Attach DB (point it to the .MDF
for the DB) it will tell you there is no .LDF, accept this and it will create
one of 1MB.
I am assuming, as your recovery mode is Simple, it is not a production DB,
or that the data is static
--
Cheers
Coburndavis
"WayneM" wrote:
> I have a SQL Server database on a server with limited disk space and I do not
> need the transaction log. Is there a TSQL command to truncate the log? I am
> using a Simple Recovery Model, but it is still keeping a transaction log.
> Do I have to force a check point programatically?
> Any suggestions would be appreciated.
> Thanks in advance,
> WayneM

How to truncate a transaction log manually?

please help me
thank you very muchBackup Log with truncate_only
"Alan" <alanchang@.jetmaster.com.tw> wrote in message
news:05a301c35af5$38f259c0$a601280a@.phx.gbl...
> please help me
> thank you very much|||Cheapest is this one:
http://www.msde.biz/msdebackup/msdebackup.htm
only 14$ !!!
Here you can find some severe problems with SQL Server, incompatible
modes and problems with transactions and backups:
http://www.itworld.com/nl/db_mgr/09032001/
"Lumigent Technologies has released a new version of Log Explorer, one
of the best-selling third-party products in the SQL Server space. Log
Explorer reads a SQL Server 2000 or 7.0 transaction log and decodes the
information into a readable format. Log Explorer lets you analyze
transaction histories, but most important, it lets you selectively
recover data. The newest release, Log Explorer 3.03, adds multiple
enhancements to this valuable capability."
regards, Guido Stepken
Alan wrote:
> please help me
> thank you very much|||http://support.microsoft.com/default.aspx?scid=kb;EN-US;272318
--
BR,
Mark Broadbent mcdba,mcse+i
_________________________
"Alan" <alanchang@.jetmaster.com.tw> wrote in message
news:05a301c35af5$38f259c0$a601280a@.phx.gbl...
> please help me
> thank you very much

How to trancate transaction log?

I use the backup wizard to run a transaction log backup with option to remov
e
inactive transaction logs. After completing the backup, the size of
transaction log file does not shrink. Why? Thanks.Are you using the full or bulk-logged recovery mode? I guess you are if the
backup completes successfully. You probably have an uncommitted transaction
that's preventing the log being truncated. Look at the output from DBCC
OPENTRAN to determine the age of the oldest uncommitted transaction (on SQL
2000)
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"joeau" <joeau@.discussions.microsoft.com> wrote in message
news:01EAAADA-8F83-4CF4-A0A3-AE776092F7EB@.microsoft.com...
> I use the backup wizard to run a transaction log backup with option to
remove
> inactive transaction logs. After completing the backup, the size of
> transaction log file does not shrink. Why? Thanks.|||Hi
The physical size of the log file will not change when you do a backup -
that will only truncate the log. To reduce the filesize, you'll have to
shrink the file.
You can try to look up DBCC SHRINKFILE in BOL - and also check out
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
Regards
Steen
joeau wrote:
> I use the backup wizard to run a transaction log backup with option
> to remove inactive transaction logs. After completing the backup, the
> size of transaction log file does not shrink. Why? Thanks.|||Removing unused transactions means incorporating those committed
transactions in the full backup as well.
Truncation does not imply file shrinkage, just the removal, actually just a
mark on the virtual logs, that those transactions have completed and backed
up.
Although the transaction log file may not have been shrunk like you'd like,
because all of the old transactions have committed and backed up, the
transaction log does not have any reason to grow either because it can now
use those freed up virtaul logs.
Sincerely,
Anthony Thomas
"joeau" <joeau@.discussions.microsoft.com> wrote in message
news:01EAAADA-8F83-4CF4-A0A3-AE776092F7EB@.microsoft.com...
I use the backup wizard to run a transaction log backup with option to
remove
inactive transaction logs. After completing the backup, the size of
transaction log file does not shrink. Why? Thanks.

How to trancate transaction log?

I use the backup wizard to run a transaction log backup with option to remove
inactive transaction logs. After completing the backup, the size of
transaction log file does not shrink. Why? Thanks.
Are you using the full or bulk-logged recovery mode? I guess you are if the
backup completes successfully. You probably have an uncommitted transaction
that's preventing the log being truncated. Look at the output from DBCC
OPENTRAN to determine the age of the oldest uncommitted transaction (on SQL
2000)
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"joeau" <joeau@.discussions.microsoft.com> wrote in message
news:01EAAADA-8F83-4CF4-A0A3-AE776092F7EB@.microsoft.com...
> I use the backup wizard to run a transaction log backup with option to
remove
> inactive transaction logs. After completing the backup, the size of
> transaction log file does not shrink. Why? Thanks.
|||Hi
The physical size of the log file will not change when you do a backup -
that will only truncate the log. To reduce the filesize, you'll have to
shrink the file.
You can try to look up DBCC SHRINKFILE in BOL - and also check out
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
Regards
Steen
joeau wrote:
> I use the backup wizard to run a transaction log backup with option
> to remove inactive transaction logs. After completing the backup, the
> size of transaction log file does not shrink. Why? Thanks.
|||Removing unused transactions means incorporating those committed
transactions in the full backup as well.
Truncation does not imply file shrinkage, just the removal, actually just a
mark on the virtual logs, that those transactions have completed and backed
up.
Although the transaction log file may not have been shrunk like you'd like,
because all of the old transactions have committed and backed up, the
transaction log does not have any reason to grow either because it can now
use those freed up virtaul logs.
Sincerely,
Anthony Thomas
"joeau" <joeau@.discussions.microsoft.com> wrote in message
news:01EAAADA-8F83-4CF4-A0A3-AE776092F7EB@.microsoft.com...
I use the backup wizard to run a transaction log backup with option to
remove
inactive transaction logs. After completing the backup, the size of
transaction log file does not shrink. Why? Thanks.
sql

How to trancate transaction log?

I use the backup wizard to run a transaction log backup with option to remove
inactive transaction logs. After completing the backup, the size of
transaction log file does not shrink. Why? Thanks.Are you using the full or bulk-logged recovery mode? I guess you are if the
backup completes successfully. You probably have an uncommitted transaction
that's preventing the log being truncated. Look at the output from DBCC
OPENTRAN to determine the age of the oldest uncommitted transaction (on SQL
2000)
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"joeau" <joeau@.discussions.microsoft.com> wrote in message
news:01EAAADA-8F83-4CF4-A0A3-AE776092F7EB@.microsoft.com...
> I use the backup wizard to run a transaction log backup with option to
remove
> inactive transaction logs. After completing the backup, the size of
> transaction log file does not shrink. Why? Thanks.|||Hi
The physical size of the log file will not change when you do a backup -
that will only truncate the log. To reduce the filesize, you'll have to
shrink the file.
You can try to look up DBCC SHRINKFILE in BOL - and also check out
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
Regards
Steen
joeau wrote:
> I use the backup wizard to run a transaction log backup with option
> to remove inactive transaction logs. After completing the backup, the
> size of transaction log file does not shrink. Why? Thanks.|||Removing unused transactions means incorporating those committed
transactions in the full backup as well.
Truncation does not imply file shrinkage, just the removal, actually just a
mark on the virtual logs, that those transactions have completed and backed
up.
Although the transaction log file may not have been shrunk like you'd like,
because all of the old transactions have committed and backed up, the
transaction log does not have any reason to grow either because it can now
use those freed up virtaul logs.
Sincerely,
Anthony Thomas
--
"joeau" <joeau@.discussions.microsoft.com> wrote in message
news:01EAAADA-8F83-4CF4-A0A3-AE776092F7EB@.microsoft.com...
I use the backup wizard to run a transaction log backup with option to
remove
inactive transaction logs. After completing the backup, the size of
transaction log file does not shrink. Why? Thanks.

How to stop transaction logging in SQL Server 7

I have a lengthy atomic action in a SQL script that
generates a huge transaction log volume. How can I
essentially stop the transaction logging for SQL Server
7? I know I can use ALTER DATABASE for SQL Server 2000
to change the recovery model to Simple, but SQL Server 7
doesn't have these recovery models. The script action
happens in one call to ALTER TABLE which operates on a
huge table, so I can't break it apart and truncate on
checkpoints inside the action. Any suggestions?
Thanks!The SIMPLE recovery model in SQL 2000 is essentially the same as 'select
into/bulk copy' and 'trunc. log on chkpt.' in SQL 7. In both cases,
committed data are removed from the log when a checkpoint occurs.
Transaction logging is required in order to ensure logical and physical
integrity. You may find it significantly faster to recreate the table with
a minimally logged SELECT ... INTO instead of a fully-logged ALTER TABLE.
Hope this helps.
Dan Guzman
SQL Server MVP
"Paul Clapis" <pclapis@.earthlinkdotnet> wrote in message
news:2bf101c3e13f$ac14f630$a501280a@.phx.gbl...

quote:

---

> I have a lengthy atomic action in a SQL script that
> generates a huge transaction log volume. How can I
> essentially stop the transaction logging for SQL Server
> 7? I know I can use ALTER DATABASE for SQL Server 2000
> to change the recovery model to Simple, but SQL Server 7
> doesn't have these recovery models. The script action
> happens in one call to ALTER TABLE which operates on a
> huge table, so I can't break it apart and truncate on
> checkpoints inside the action. Any suggestions?
> Thanks!

---

How to stop transaction logging in SQL Server 7

I have a lengthy atomic action in a SQL script that
generates a huge transaction log volume. How can I
essentially stop the transaction logging for SQL Server
7? I know I can use ALTER DATABASE for SQL Server 2000
to change the recovery model to Simple, but SQL Server 7
doesn't have these recovery models. The script action
happens in one call to ALTER TABLE which operates on a
huge table, so I can't break it apart and truncate on
checkpoints inside the action. Any suggestions?
Thanks!The SIMPLE recovery model in SQL 2000 is essentially the same as 'select
into/bulk copy' and 'trunc. log on chkpt.' in SQL 7. In both cases,
committed data are removed from the log when a checkpoint occurs.
Transaction logging is required in order to ensure logical and physical
integrity. You may find it significantly faster to recreate the table with
a minimally logged SELECT ... INTO instead of a fully-logged ALTER TABLE.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Paul Clapis" <pclapis@.earthlinkdotnet> wrote in message
news:2bf101c3e13f$ac14f630$a501280a@.phx.gbl...
> I have a lengthy atomic action in a SQL script that
> generates a huge transaction log volume. How can I
> essentially stop the transaction logging for SQL Server
> 7? I know I can use ALTER DATABASE for SQL Server 2000
> to change the recovery model to Simple, but SQL Server 7
> doesn't have these recovery models. The script action
> happens in one call to ALTER TABLE which operates on a
> huge table, so I can't break it apart and truncate on
> checkpoints inside the action. Any suggestions?
> Thanks!