Hi there,
I use SQL 2000 and the log of a database is very big(I have full recovery).
I tried backing it up with Veritas BackupExec in order to truncate it, did
not work.
What should I do?
Thanks a lot, CalinRun DBCC OPENTRAN ('yourDB') to see whether you have an
active transaction that prevents the log from being
truncated.
Linchi
>--Original Message--
>Hi there,
>I use SQL 2000 and the log of a database is very big(I
have full recovery).
>I tried backing it up with Veritas BackupExec in order to
truncate it, did
>not work.
>What should I do?
>Thanks a lot, Calin
>.
>|||There is a difference between truncating the log and shrinking the file
size. A normal log backup will truncate the log, freeing internal space,
but the file size will remain the same. You'll need to use DBCC SHRINKFILE
to reduce the transaction log file size.
Jim
"Tester" <CalinGuga@.netscape.net> wrote in message
news:%23E8xJUkzDHA.2412@.TK2MSFTNGP10.phx.gbl...
> Hi there,
> I use SQL 2000 and the log of a database is very big(I have full
recovery).
> I tried backing it up with Veritas BackupExec in order to truncate it, did
> not work.
> What should I do?
> Thanks a lot, Calin
>|||I don't know about Veritas BackupExec but I do have some
knowledge of SQLServer backup. When you do a full DB
backup using SQLServer backup, the transactions which are
in the log file and which have been marked as commited
are deleted from the log file. Then you can run DBCC
SHRINKFILE and it will work.
If you have Auto Shrink checked on the database
properties, then the SQLServer will shrink the size of
the log file over time after a full DB backup. This is
the advantage of using a maintenance plan backup in
SQLServer.
If you are in a super hurry to shrink the log file, do
the following:
1. Make sure you are not publishing using Transactional
replication.
2. Make sure there are no open transactions by running0
DBCC OPENTRAN.
3. Make sure there are no remote server links to this
database.
4. Make sure you know what maintenance plans are using
this database because you will probably have to rebuild
them.
5. Detach the database.
6. Manually delete the log file in Windows Explorer.
7. Attach the database which will build a new, 504 KB,
log file.
8. Rebuild your mainenance plans.
>--Original Message--
>Hi there,
>I use SQL 2000 and the log of a database is very big(I
have full recovery).
>I tried backing it up with Veritas BackupExec in order
to truncate it, did
>not work.
>What should I do?
>Thanks a lot, Calin
>.
>
Showing posts with label recovery. Show all posts
Showing posts with label recovery. Show all posts
Friday, March 30, 2012
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
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
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
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
Sunday, February 19, 2012
How to stop automatic recovery ?
I have a database that is doing a automatic recovery. The
event log indicates this will take about 15 hours. I would
like to stop the recovery and reload from backup. I cannot
connect with Enterprise Manager because it time's out
connecting to the server. Anybody have an idea what I can
do?OK, this is a drastic action.
Stop the SQL Server. Rename the underlying data files (.MDF, .NDF, and .LDF
are the normal file extensions). If you don't have the space to hold the
replacement files, delete them. Restart SQL Server. You can then restore
from backup.
Make sure you want to do this and that your backup is good. This is NOT an
exercise you can undo. If you have the space, I would do a test restore
using a different DB name and file names. Look at the 'WITH MOVE' option
in BOL for RESTORE DATABASE.
--
Geoff N. Hiten
SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Pat" <mcilweep@.ctbsonline.com> wrote in message
news:013a01c35d01$3c70ed10$a301280a@.phx.gbl...
> I have a database that is doing a automatic recovery. The
> event log indicates this will take about 15 hours. I would
> like to stop the recovery and reload from backup. I cannot
> connect with Enterprise Manager because it time's out
> connecting to the server. Anybody have an idea what I can
> do?
event log indicates this will take about 15 hours. I would
like to stop the recovery and reload from backup. I cannot
connect with Enterprise Manager because it time's out
connecting to the server. Anybody have an idea what I can
do?OK, this is a drastic action.
Stop the SQL Server. Rename the underlying data files (.MDF, .NDF, and .LDF
are the normal file extensions). If you don't have the space to hold the
replacement files, delete them. Restart SQL Server. You can then restore
from backup.
Make sure you want to do this and that your backup is good. This is NOT an
exercise you can undo. If you have the space, I would do a test restore
using a different DB name and file names. Look at the 'WITH MOVE' option
in BOL for RESTORE DATABASE.
--
Geoff N. Hiten
SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Pat" <mcilweep@.ctbsonline.com> wrote in message
news:013a01c35d01$3c70ed10$a301280a@.phx.gbl...
> I have a database that is doing a automatic recovery. The
> event log indicates this will take about 15 hours. I would
> like to stop the recovery and reload from backup. I cannot
> connect with Enterprise Manager because it time's out
> connecting to the server. Anybody have an idea what I can
> do?
Subscribe to:
Posts (Atom)