Greetings,
I am running into a problem where some of our tables are
creating the log files to grow even with simple
recovery. Everything I read says that simple recovery
will prevent this. We especially notice this when we
delete the rows in a large table from the query analyzer
however if we issue the same command within a DTS package
the logs doesn't grow.
Any suggestions? Thanks!!hi terry,
using simple recovery model does not mean, DML operation is not logged. when
you issue a delete statement againtst the table it is logged into
transaction log, All that simple recovery mode does is, it truncates the log
each time SQL Server performs a checkpoint.
if you want to perform a large delete do it in chunks of batches, following
example will delete the rows from table in batch of 1000 per batch. Im not
sure what does DTS do behind the scene. probably, it is doing the same
thing. you can run a profiler trace and check whats happening.
ex:
set rowcount 1000
while 1=1
begin
delete from customers
if @.@.rowcount=0
break
end
set rowcount 0
Refer to following urls for more information.
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Transaction Log
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com|||Thank you for this information. It is very helpful!
>--Original Message--
>hi terry,
>using simple recovery model does not mean, DML operation
is not logged. when
>you issue a delete statement againtst the table it is
logged into
>transaction log, All that simple recovery mode does is,
it truncates the log
>each time SQL Server performs a checkpoint.
>if you want to perform a large delete do it in chunks of
batches, following
>example will delete the rows from table in batch of 1000
per batch. Im not
>sure what does DTS do behind the scene. probably, it is
doing the same
>thing. you can run a profiler trace and check whats
happening.
>ex:
>set rowcount 1000
>while 1=1
>begin
>delete from customers
>if @.@.rowcount=0
>break
>end
>set rowcount 0
>Refer to following urls for more information.
>http://www.support.microsoft.com/?id=256650 INF: How
to Shrink the SQL
>Server 7.0 Transaction Log
>http://www.support.microsoft.com/?id=317375 Log File
Grows too big
>http://www.support.microsoft.com/?id=110139 Log file
filling up
>http://www.mssqlserver.com/faq/logs-shrinklog.asp
Shrink File
>http://www.support.microsoft.com/?id=315512
Considerations for Autogrow
>and AutoShrink
>http://www.support.microsoft.com/?id=272318 INF:
Shrinking Log in SQL
>Server 2000 with DBCC SHRINKFILE
>
>--
>Vishal Parkar
>vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
>
>
>.
>
Showing posts with label recovery. Show all posts
Showing posts with label recovery. Show all posts
Wednesday, March 7, 2012
Large Transacation Log even with Simple Recovery
Greetings,
I am running into a problem where some of our tables are
creating the log files to grow even with simple
recovery. Everything I read says that simple recovery
will prevent this. We especially notice this when we
delete the rows in a large table from the query analyzer
however if we issue the same command within a DTS package
the logs doesn't grow.
Any suggestions? Thanks!!
hi terry,
using simple recovery model does not mean, DML operation is not logged. when
you issue a delete statement againtst the table it is logged into
transaction log, All that simple recovery mode does is, it truncates the log
each time SQL Server performs a checkpoint.
if you want to perform a large delete do it in chunks of batches, following
example will delete the rows from table in batch of 1000 per batch. Im not
sure what does DTS do behind the scene. probably, it is doing the same
thing. you can run a profiler trace and check whats happening.
ex:
set rowcount 1000
while 1=1
begin
delete from customers
if @.@.rowcount=0
break
end
set rowcount 0
Refer to following urls for more information.
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Transaction Log
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
|||Thank you for this information. It is very helpful!
>--Original Message--
>hi terry,
>using simple recovery model does not mean, DML operation
is not logged. when
>you issue a delete statement againtst the table it is
logged into
>transaction log, All that simple recovery mode does is,
it truncates the log
>each time SQL Server performs a checkpoint.
>if you want to perform a large delete do it in chunks of
batches, following
>example will delete the rows from table in batch of 1000
per batch. Im not
>sure what does DTS do behind the scene. probably, it is
doing the same
>thing. you can run a profiler trace and check whats
happening.
>ex:
>set rowcount 1000
>while 1=1
>begin
>delete from customers
>if @.@.rowcount=0
>break
>end
>set rowcount 0
>Refer to following urls for more information.
>http://www.support.microsoft.com/?id=256650 INF: How
to Shrink the SQL
>Server 7.0 Transaction Log
>http://www.support.microsoft.com/?id=317375 Log File
Grows too big
>http://www.support.microsoft.com/?id=110139 Log file
filling up
>http://www.mssqlserver.com/faq/logs-shrinklog.asp
Shrink File
>http://www.support.microsoft.com/?id=315512
Considerations for Autogrow
>and AutoShrink
>http://www.support.microsoft.com/?id=272318 INF:
Shrinking Log in SQL
>Server 2000 with DBCC SHRINKFILE
>
>--
>Vishal Parkar
>vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
>
>
>.
>
I am running into a problem where some of our tables are
creating the log files to grow even with simple
recovery. Everything I read says that simple recovery
will prevent this. We especially notice this when we
delete the rows in a large table from the query analyzer
however if we issue the same command within a DTS package
the logs doesn't grow.
Any suggestions? Thanks!!
hi terry,
using simple recovery model does not mean, DML operation is not logged. when
you issue a delete statement againtst the table it is logged into
transaction log, All that simple recovery mode does is, it truncates the log
each time SQL Server performs a checkpoint.
if you want to perform a large delete do it in chunks of batches, following
example will delete the rows from table in batch of 1000 per batch. Im not
sure what does DTS do behind the scene. probably, it is doing the same
thing. you can run a profiler trace and check whats happening.
ex:
set rowcount 1000
while 1=1
begin
delete from customers
if @.@.rowcount=0
break
end
set rowcount 0
Refer to following urls for more information.
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Transaction Log
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
|||Thank you for this information. It is very helpful!
>--Original Message--
>hi terry,
>using simple recovery model does not mean, DML operation
is not logged. when
>you issue a delete statement againtst the table it is
logged into
>transaction log, All that simple recovery mode does is,
it truncates the log
>each time SQL Server performs a checkpoint.
>if you want to perform a large delete do it in chunks of
batches, following
>example will delete the rows from table in batch of 1000
per batch. Im not
>sure what does DTS do behind the scene. probably, it is
doing the same
>thing. you can run a profiler trace and check whats
happening.
>ex:
>set rowcount 1000
>while 1=1
>begin
>delete from customers
>if @.@.rowcount=0
>break
>end
>set rowcount 0
>Refer to following urls for more information.
>http://www.support.microsoft.com/?id=256650 INF: How
to Shrink the SQL
>Server 7.0 Transaction Log
>http://www.support.microsoft.com/?id=317375 Log File
Grows too big
>http://www.support.microsoft.com/?id=110139 Log file
filling up
>http://www.mssqlserver.com/faq/logs-shrinklog.asp
Shrink File
>http://www.support.microsoft.com/?id=315512
Considerations for Autogrow
>and AutoShrink
>http://www.support.microsoft.com/?id=272318 INF:
Shrinking Log in SQL
>Server 2000 with DBCC SHRINKFILE
>
>--
>Vishal Parkar
>vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
>
>
>.
>
Monday, February 20, 2012
Large LDF File
My LDF file is 1.8GB big and my data file is only about 100mb big. I have had
the recovery model set to full until recently which is probably why it has
ended up so big. I have now changed that to simple. Now no matter what I do
the LDF file stays at exactly 1,785,856 KB in size. I have tried backing up
the database and backing up the transaction log but neither have made any
difference to the size of the file. Even using the database doesn't appear to
make any difference to the file size. Can you please advise me as to what I
can do to shrink this file and get it working again.
Many Thanks.
Look up DBCC SHRINKFILE in BOL.
"Boxwell" <Boxwell@.discussions.microsoft.com> wrote in message
news:25DB49E4-46C3-48E2-9B2F-1984B11E0216@.microsoft.com...
> My LDF file is 1.8GB big and my data file is only about 100mb big. I have
> had
> the recovery model set to full until recently which is probably why it has
> ended up so big. I have now changed that to simple. Now no matter what I
> do
> the LDF file stays at exactly 1,785,856 KB in size. I have tried backing
> up
> the database and backing up the transaction log but neither have made any
> difference to the size of the file. Even using the database doesn't appear
> to
> make any difference to the file size. Can you please advise me as to what
> I
> can do to shrink this file and get it working again.
> Many Thanks.
|||Run => DBCC sqlperf(logspace), list the % of logspace used
Run => backup transaction DBNAME with no_log
BOL =>NO_LOG | TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it and
truncates the log. This option frees space. Specifying a backup device is
unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY
are synonyms.
Run => DBCC sqlperf(logspace)
To Add on to this, to save disk space you need to run,
sp_helpdb 'dbname' => get logical log name of file.
DBCC SHRINKFILE ('DBNAME_Log',0) => this will release dsik space...
Thanks,
Sree
"Boxwell" wrote:
> My LDF file is 1.8GB big and my data file is only about 100mb big. I have had
> the recovery model set to full until recently which is probably why it has
> ended up so big. I have now changed that to simple. Now no matter what I do
> the LDF file stays at exactly 1,785,856 KB in size. I have tried backing up
> the database and backing up the transaction log but neither have made any
> difference to the size of the file. Even using the database doesn't appear to
> make any difference to the file size. Can you please advise me as to what I
> can do to shrink this file and get it working again.
> Many Thanks.
the recovery model set to full until recently which is probably why it has
ended up so big. I have now changed that to simple. Now no matter what I do
the LDF file stays at exactly 1,785,856 KB in size. I have tried backing up
the database and backing up the transaction log but neither have made any
difference to the size of the file. Even using the database doesn't appear to
make any difference to the file size. Can you please advise me as to what I
can do to shrink this file and get it working again.
Many Thanks.
Look up DBCC SHRINKFILE in BOL.
"Boxwell" <Boxwell@.discussions.microsoft.com> wrote in message
news:25DB49E4-46C3-48E2-9B2F-1984B11E0216@.microsoft.com...
> My LDF file is 1.8GB big and my data file is only about 100mb big. I have
> had
> the recovery model set to full until recently which is probably why it has
> ended up so big. I have now changed that to simple. Now no matter what I
> do
> the LDF file stays at exactly 1,785,856 KB in size. I have tried backing
> up
> the database and backing up the transaction log but neither have made any
> difference to the size of the file. Even using the database doesn't appear
> to
> make any difference to the file size. Can you please advise me as to what
> I
> can do to shrink this file and get it working again.
> Many Thanks.
|||Run => DBCC sqlperf(logspace), list the % of logspace used
Run => backup transaction DBNAME with no_log
BOL =>NO_LOG | TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it and
truncates the log. This option frees space. Specifying a backup device is
unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY
are synonyms.
Run => DBCC sqlperf(logspace)
To Add on to this, to save disk space you need to run,
sp_helpdb 'dbname' => get logical log name of file.
DBCC SHRINKFILE ('DBNAME_Log',0) => this will release dsik space...
Thanks,
Sree
"Boxwell" wrote:
> My LDF file is 1.8GB big and my data file is only about 100mb big. I have had
> the recovery model set to full until recently which is probably why it has
> ended up so big. I have now changed that to simple. Now no matter what I do
> the LDF file stays at exactly 1,785,856 KB in size. I have tried backing up
> the database and backing up the transaction log but neither have made any
> difference to the size of the file. Even using the database doesn't appear to
> make any difference to the file size. Can you please advise me as to what I
> can do to shrink this file and get it working again.
> Many Thanks.
Large LDF File
My LDF file is 1.8GB big and my data file is only about 100mb big. I have ha
d
the recovery model set to full until recently which is probably why it has
ended up so big. I have now changed that to simple. Now no matter what I do
the LDF file stays at exactly 1,785,856 KB in size. I have tried backing up
the database and backing up the transaction log but neither have made any
difference to the size of the file. Even using the database doesn't appear t
o
make any difference to the file size. Can you please advise me as to what I
can do to shrink this file and get it working again.
Many Thanks.Look up DBCC SHRINKFILE in BOL.
"Boxwell" <Boxwell@.discussions.microsoft.com> wrote in message
news:25DB49E4-46C3-48E2-9B2F-1984B11E0216@.microsoft.com...
> My LDF file is 1.8GB big and my data file is only about 100mb big. I have
> had
> the recovery model set to full until recently which is probably why it has
> ended up so big. I have now changed that to simple. Now no matter what I
> do
> the LDF file stays at exactly 1,785,856 KB in size. I have tried backing
> up
> the database and backing up the transaction log but neither have made any
> difference to the size of the file. Even using the database doesn't appear
> to
> make any difference to the file size. Can you please advise me as to what
> I
> can do to shrink this file and get it working again.
> Many Thanks.|||Run => DBCC sqlperf(logspace), list the % of logspace used
Run => backup transaction DBNAME with no_log
BOL =>NO_LOG | TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it and
truncates the log. This option frees space. Specifying a backup device is
unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY
are synonyms.
Run => DBCC sqlperf(logspace)
To Add on to this, to save disk space you need to run,
sp_helpdb 'dbname' => get logical log name of file.
DBCC SHRINKFILE ('DBNAME_Log',0) => this will release dsik space...
Thanks,
Sree
"Boxwell" wrote:
> My LDF file is 1.8GB big and my data file is only about 100mb big. I have
had
> the recovery model set to full until recently which is probably why it has
> ended up so big. I have now changed that to simple. Now no matter what I d
o
> the LDF file stays at exactly 1,785,856 KB in size. I have tried backing u
p
> the database and backing up the transaction log but neither have made any
> difference to the size of the file. Even using the database doesn't appear
to
> make any difference to the file size. Can you please advise me as to what
I
> can do to shrink this file and get it working again.
> Many Thanks.
d
the recovery model set to full until recently which is probably why it has
ended up so big. I have now changed that to simple. Now no matter what I do
the LDF file stays at exactly 1,785,856 KB in size. I have tried backing up
the database and backing up the transaction log but neither have made any
difference to the size of the file. Even using the database doesn't appear t
o
make any difference to the file size. Can you please advise me as to what I
can do to shrink this file and get it working again.
Many Thanks.Look up DBCC SHRINKFILE in BOL.
"Boxwell" <Boxwell@.discussions.microsoft.com> wrote in message
news:25DB49E4-46C3-48E2-9B2F-1984B11E0216@.microsoft.com...
> My LDF file is 1.8GB big and my data file is only about 100mb big. I have
> had
> the recovery model set to full until recently which is probably why it has
> ended up so big. I have now changed that to simple. Now no matter what I
> do
> the LDF file stays at exactly 1,785,856 KB in size. I have tried backing
> up
> the database and backing up the transaction log but neither have made any
> difference to the size of the file. Even using the database doesn't appear
> to
> make any difference to the file size. Can you please advise me as to what
> I
> can do to shrink this file and get it working again.
> Many Thanks.|||Run => DBCC sqlperf(logspace), list the % of logspace used
Run => backup transaction DBNAME with no_log
BOL =>NO_LOG | TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it and
truncates the log. This option frees space. Specifying a backup device is
unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY
are synonyms.
Run => DBCC sqlperf(logspace)
To Add on to this, to save disk space you need to run,
sp_helpdb 'dbname' => get logical log name of file.
DBCC SHRINKFILE ('DBNAME_Log',0) => this will release dsik space...
Thanks,
Sree
"Boxwell" wrote:
> My LDF file is 1.8GB big and my data file is only about 100mb big. I have
had
> the recovery model set to full until recently which is probably why it has
> ended up so big. I have now changed that to simple. Now no matter what I d
o
> the LDF file stays at exactly 1,785,856 KB in size. I have tried backing u
p
> the database and backing up the transaction log but neither have made any
> difference to the size of the file. Even using the database doesn't appear
to
> make any difference to the file size. Can you please advise me as to what
I
> can do to shrink this file and get it working again.
> Many Thanks.
Large LDF File
My LDF file is 1.8GB big and my data file is only about 100mb big. I have had
the recovery model set to full until recently which is probably why it has
ended up so big. I have now changed that to simple. Now no matter what I do
the LDF file stays at exactly 1,785,856 KB in size. I have tried backing up
the database and backing up the transaction log but neither have made any
difference to the size of the file. Even using the database doesn't appear to
make any difference to the file size. Can you please advise me as to what I
can do to shrink this file and get it working again.
Many Thanks.Look up DBCC SHRINKFILE in BOL.
"Boxwell" <Boxwell@.discussions.microsoft.com> wrote in message
news:25DB49E4-46C3-48E2-9B2F-1984B11E0216@.microsoft.com...
> My LDF file is 1.8GB big and my data file is only about 100mb big. I have
> had
> the recovery model set to full until recently which is probably why it has
> ended up so big. I have now changed that to simple. Now no matter what I
> do
> the LDF file stays at exactly 1,785,856 KB in size. I have tried backing
> up
> the database and backing up the transaction log but neither have made any
> difference to the size of the file. Even using the database doesn't appear
> to
> make any difference to the file size. Can you please advise me as to what
> I
> can do to shrink this file and get it working again.
> Many Thanks.|||Run => DBCC sqlperf(logspace), list the % of logspace used
Run => backup transaction DBNAME with no_log
BOL =>NO_LOG | TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it and
truncates the log. This option frees space. Specifying a backup device is
unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY
are synonyms.
Run => DBCC sqlperf(logspace)
To Add on to this, to save disk space you need to run,
sp_helpdb 'dbname' => get logical log name of file.
DBCC SHRINKFILE ('DBNAME_Log',0) => this will release dsik space...
Thanks,
Sree
"Boxwell" wrote:
> My LDF file is 1.8GB big and my data file is only about 100mb big. I have had
> the recovery model set to full until recently which is probably why it has
> ended up so big. I have now changed that to simple. Now no matter what I do
> the LDF file stays at exactly 1,785,856 KB in size. I have tried backing up
> the database and backing up the transaction log but neither have made any
> difference to the size of the file. Even using the database doesn't appear to
> make any difference to the file size. Can you please advise me as to what I
> can do to shrink this file and get it working again.
> Many Thanks.
the recovery model set to full until recently which is probably why it has
ended up so big. I have now changed that to simple. Now no matter what I do
the LDF file stays at exactly 1,785,856 KB in size. I have tried backing up
the database and backing up the transaction log but neither have made any
difference to the size of the file. Even using the database doesn't appear to
make any difference to the file size. Can you please advise me as to what I
can do to shrink this file and get it working again.
Many Thanks.Look up DBCC SHRINKFILE in BOL.
"Boxwell" <Boxwell@.discussions.microsoft.com> wrote in message
news:25DB49E4-46C3-48E2-9B2F-1984B11E0216@.microsoft.com...
> My LDF file is 1.8GB big and my data file is only about 100mb big. I have
> had
> the recovery model set to full until recently which is probably why it has
> ended up so big. I have now changed that to simple. Now no matter what I
> do
> the LDF file stays at exactly 1,785,856 KB in size. I have tried backing
> up
> the database and backing up the transaction log but neither have made any
> difference to the size of the file. Even using the database doesn't appear
> to
> make any difference to the file size. Can you please advise me as to what
> I
> can do to shrink this file and get it working again.
> Many Thanks.|||Run => DBCC sqlperf(logspace), list the % of logspace used
Run => backup transaction DBNAME with no_log
BOL =>NO_LOG | TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it and
truncates the log. This option frees space. Specifying a backup device is
unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY
are synonyms.
Run => DBCC sqlperf(logspace)
To Add on to this, to save disk space you need to run,
sp_helpdb 'dbname' => get logical log name of file.
DBCC SHRINKFILE ('DBNAME_Log',0) => this will release dsik space...
Thanks,
Sree
"Boxwell" wrote:
> My LDF file is 1.8GB big and my data file is only about 100mb big. I have had
> the recovery model set to full until recently which is probably why it has
> ended up so big. I have now changed that to simple. Now no matter what I do
> the LDF file stays at exactly 1,785,856 KB in size. I have tried backing up
> the database and backing up the transaction log but neither have made any
> difference to the size of the file. Even using the database doesn't appear to
> make any difference to the file size. Can you please advise me as to what I
> can do to shrink this file and get it working again.
> Many Thanks.
Subscribe to:
Posts (Atom)