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