Locks and Blocks: reduce

Showing posts with label reduce. Show all posts

Wednesday, March 28, 2012

log grows after shrinking

I have truncated the log file and then used the DBCC Shrinkfile command to
reduce to physical size of the file. This seemed to work perfectly.
However, after a few days the transaction log file returns to its huge size
(8Gb). Has anyone else experienced this problem and if so can you suggest a
solution?
Thanks in advance, Simon.Simon
It sounds like you have your database in full recovery mode, but you are not
performing transaction log backups.
If you want to use your transaction logs as part of your backup and recovery
strategy, (recommended for production systems) you need to back them up
regularly. Something like every 15 or 30 minutes (or whatever is suitable for
your requirements) during your business day.
If you really do not want your transaction logs, set your recovery mode to
simple.
Hope this helps
John
"Simon" wrote:
> I have truncated the log file and then used the DBCC Shrinkfile command to
> reduce to physical size of the file. This seemed to work perfectly.
> However, after a few days the transaction log file returns to its huge size
> (8Gb). Has anyone else experienced this problem and if so can you suggest a
> solution?
> Thanks in advance, Simon.|||Even in SIMPLE RECOVERY mode, the transaction log file(s) will grow to about
the size of the data portion of your data files if you are doing regular
maintenance with the DB maintenance wizard. The reason is that the
Organization task will serially reorganize your data tables. The largest
table will dictate the amount of free space in your data file and the size
of transaction log for normal operations.
If you do not want your tranaction log to be bigger than this, you need to
be in SIMPLE RECOVERY or backup the tlogs frequently if in FULL RECOVERY or
BULK INSERT RECOVERY modes.
How big is your data files?
Sincerely,
Anthony Thomas
"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
message news:9B630219-A733-4032-87DF-45B858628C31@.microsoft.com...
Simon
It sounds like you have your database in full recovery mode, but you are not
performing transaction log backups.
If you want to use your transaction logs as part of your backup and recovery
strategy, (recommended for production systems) you need to back them up
regularly. Something like every 15 or 30 minutes (or whatever is suitable
for
your requirements) during your business day.
If you really do not want your transaction logs, set your recovery mode to
simple.
Hope this helps
John
"Simon" wrote:
> I have truncated the log file and then used the DBCC Shrinkfile command to
> reduce to physical size of the file. This seemed to work perfectly.
> However, after a few days the transaction log file returns to its huge
size
> (8Gb). Has anyone else experienced this problem and if so can you suggest
a
> solution?
> Thanks in advance, Simon.sql

Friday, March 23, 2012

log file to be reduced

Hi All
I have a mdf file of around 4gb but log file increased to
20gb. how to reduce size of log file.
Any help would be highly appreciated.
Rgds
TriveniHave a look at these articles
INF: How to Shrink the SQL Server 7.0 Transaction Lo
http://support.microsoft.com/default.aspx?scid=kb;en-us;25665
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFIL
http://support.microsoft.com/default.aspx?scid=kb;en-us;27231
http://www.mssqlserver.com/faq/logs-shrinklog.as
Hope this help
John

log file to be reduced

Hi All
I have a mdf file of around 4gb but log file increased to
20gb. how to reduce size of log file.
Any help would be highly appreciated.
Rgds
TriveniHave a look at these articles.
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/defaul...kb;en-us;256650
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/defaul...kb;en-us;272318
http://www.mssqlserver.com/faq/logs-shrinklog.asp
Hope this helps
John

Log File Size reduction

Dear All,
Our database log file in SQL 2000 is growing and filling the disk.
What are the option to reduce the log file size which is existing?
We tried the options :
DBCC Shrinkfile
SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
However the file size remained same.
Are we making any mistake? Which is the best way to reduce the file size and
keep the size to a preset limit (without affecting the trasaction after
reaching the limit)
Regards
SathianHi
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"Sathian" <sathian.t@.in.bosch.com> wrote in message
news:e75ujo$sfo$1@.ns2.fe.internet.bosch.com...
> Dear All,
> Our database log file in SQL 2000 is growing and filling the disk.
> What are the option to reduce the log file size which is existing?
> We tried the options :
> DBCC Shrinkfile
> SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
> However the file size remained same.
> Are we making any mistake? Which is the best way to reduce the file size
> and
> keep the size to a preset limit (without affecting the trasaction after
> reaching the limit)
> Regards
> Sathian
>|||take a backup and restore upto that point, truncate the log.
do this when users are offline if you can.
delete redundant objects and temp tables and shrink the log files. if that
doesn't work get more disk.
"Sathian" wrote:

> Dear All,
> Our database log file in SQL 2000 is growing and filling the disk.
> What are the option to reduce the log file size which is existing?
> We tried the options :
> DBCC Shrinkfile
> SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
> However the file size remained same.
> Are we making any mistake? Which is the best way to reduce the file size a
nd
> keep the size to a preset limit (without affecting the trasaction after
> reaching the limit)
> Regards
> Sathian
>
>|||marcmc wrote:
> take a backup and restore upto that point, truncate the log.
> do this when users are offline if you can.
> delete redundant objects and temp tables and shrink the log files. if that
> doesn't work get more disk.
>
I don't really see any point in restoring anything. The problem seems
like you are running in FULL recovery mode and you've never backed up
your logfile.
If you are running on FULL recovery, you should make sure that you have
a recent backup of your database. Then you run a backup of your logfile.
Now you can run a DBCC SHRINKFILE(YourLogicalFileName, TargetSize) which
will try to shrink your logfile to the size you've specified.
Just keep in mind that if you have any open transactions, then you can't
shrink the file past these transactions.
If you don't care about your logfile backup, then you can also run the
backup log command with the NO_LOG option. This will only truncate the
log file and not create a log backup file. You can then afterwards run
the DBCC SHRINKFILE command.
Another good idea is also to look up "Truncating the transcation log" in
Books On Line.
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator|||Hi,
Here is every time working solution
1. Change recovery model into SIMPLE.
2. Truncate and shrink transaction log.
3. Resize log into required size.
Tomasz B.
"Sathian" wrote:

> Dear All,
> Our database log file in SQL 2000 is growing and filling the disk.
> What are the option to reduce the log file size which is existing?
> We tried the options :
> DBCC Shrinkfile
> SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
> However the file size remained same.
> Are we making any mistake? Which is the best way to reduce the file size a
nd
> keep the size to a preset limit (without affecting the trasaction after
> reaching the limit)
> Regards
> Sathian
>
>|||"Tomasz Borawski" <TomaszBorawski@.discussions.microsoft.com> wrote in
message news:EFCEBE5D-1ADA-49BB-AD52-93ECD498C7B7@.microsoft.com...
> Hi,
> Here is every time working solution
> 1. Change recovery model into SIMPLE.
> 2. Truncate and shrink transaction log.
> 3. Resize log into required size.
You forgot the critical (assuming this is a production database or one they
care about otherwise) of "backup"
Once you've changed the recovery model to simple, you've negated your backup
string.
> Tomasz B.
> "Sathian" wrote:
>
and|||If this is production server, and recovery mode=full, I will
1. backup transaction_log dbname to disk='filename'
2. dbcc shrinkfile logical_db name
3. schedule to backup transaction_log depand on needed + recovery purpose
--soonyu
"Sathian" wrote:

> Dear All,
> Our database log file in SQL 2000 is growing and filling the disk.
> What are the option to reduce the log file size which is existing?
> We tried the options :
> DBCC Shrinkfile
> SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
> However the file size remained same.
> Are we making any mistake? Which is the best way to reduce the file size a
nd
> keep the size to a preset limit (without affecting the trasaction after
> reaching the limit)
> Regards
> Sathian
>
>

Log File Size reduction

Dear All,
Our database log file in SQL 2000 is growing and filling the disk.
What are the option to reduce the log file size which is existing?
We tried the options :
DBCC Shrinkfile
SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
However the file size remained same.
Are we making any mistake? Which is the best way to reduce the file size and
keep the size to a preset limit (without affecting the trasaction after
reaching the limit)
Regards
SathianHi
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"Sathian" <sathian.t@.in.bosch.com> wrote in message
news:e75ujo$sfo$1@.ns2.fe.internet.bosch.com...
> Dear All,
> Our database log file in SQL 2000 is growing and filling the disk.
> What are the option to reduce the log file size which is existing?
> We tried the options :
> DBCC Shrinkfile
> SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
> However the file size remained same.
> Are we making any mistake? Which is the best way to reduce the file size
> and
> keep the size to a preset limit (without affecting the trasaction after
> reaching the limit)
> Regards
> Sathian
>|||take a backup and restore upto that point, truncate the log.
do this when users are offline if you can.
delete redundant objects and temp tables and shrink the log files. if that
doesn't work get more disk.
"Sathian" wrote:
> Dear All,
> Our database log file in SQL 2000 is growing and filling the disk.
> What are the option to reduce the log file size which is existing?
> We tried the options :
> DBCC Shrinkfile
> SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
> However the file size remained same.
> Are we making any mistake? Which is the best way to reduce the file size and
> keep the size to a preset limit (without affecting the trasaction after
> reaching the limit)
> Regards
> Sathian
>
>|||marcmc wrote:
> take a backup and restore upto that point, truncate the log.
> do this when users are offline if you can.
> delete redundant objects and temp tables and shrink the log files. if that
> doesn't work get more disk.
>
I don't really see any point in restoring anything. The problem seems
like you are running in FULL recovery mode and you've never backed up
your logfile.
If you are running on FULL recovery, you should make sure that you have
a recent backup of your database. Then you run a backup of your logfile.
Now you can run a DBCC SHRINKFILE(YourLogicalFileName, TargetSize) which
will try to shrink your logfile to the size you've specified.
Just keep in mind that if you have any open transactions, then you can't
shrink the file past these transactions.
If you don't care about your logfile backup, then you can also run the
backup log command with the NO_LOG option. This will only truncate the
log file and not create a log backup file. You can then afterwards run
the DBCC SHRINKFILE command.
Another good idea is also to look up "Truncating the transcation log" in
Books On Line.
Regards
Steen SchlÃ¼ter Persson
Databaseadministrator / Systemadministrator|||Hi,
Here is every time working solution
1. Change recovery model into SIMPLE.
2. Truncate and shrink transaction log.
3. Resize log into required size.
Tomasz B.
"Sathian" wrote:
> Dear All,
> Our database log file in SQL 2000 is growing and filling the disk.
> What are the option to reduce the log file size which is existing?
> We tried the options :
> DBCC Shrinkfile
> SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
> However the file size remained same.
> Are we making any mistake? Which is the best way to reduce the file size and
> keep the size to a preset limit (without affecting the trasaction after
> reaching the limit)
> Regards
> Sathian
>
>|||"Tomasz Borawski" <TomaszBorawski@.discussions.microsoft.com> wrote in
message news:EFCEBE5D-1ADA-49BB-AD52-93ECD498C7B7@.microsoft.com...
> Hi,
> Here is every time working solution
> 1. Change recovery model into SIMPLE.
> 2. Truncate and shrink transaction log.
> 3. Resize log into required size.
You forgot the critical (assuming this is a production database or one they
care about otherwise) of "backup"
Once you've changed the recovery model to simple, you've negated your backup
string.
> Tomasz B.
> "Sathian" wrote:
> > Dear All,
> >
> > Our database log file in SQL 2000 is growing and filling the disk.
> >
> > What are the option to reduce the log file size which is existing?
> >
> > We tried the options :
> >
> > DBCC Shrinkfile
> > SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
> >
> > However the file size remained same.
> >
> > Are we making any mistake? Which is the best way to reduce the file size
and
> > keep the size to a preset limit (without affecting the trasaction after
> > reaching the limit)
> >
> > Regards
> >
> > Sathian
> >
> >
> >|||If this is production server, and recovery mode=full, I will
1. backup transaction_log dbname to disk='filename'
2. dbcc shrinkfile logical_db name
3. schedule to backup transaction_log depand on needed + recovery purpose
--soonyu
"Sathian" wrote:
> Dear All,
> Our database log file in SQL 2000 is growing and filling the disk.
> What are the option to reduce the log file size which is existing?
> We tried the options :
> DBCC Shrinkfile
> SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
> However the file size remained same.
> Are we making any mistake? Which is the best way to reduce the file size and
> keep the size to a preset limit (without affecting the trasaction after
> reaching the limit)
> Regards
> Sathian
>
>

Log file size not reduce

Database log of my DB is around 2GB.
The database is using FULL recovery option.

I want to reduce the file size of the log cause it takes up a lot of
space.

I'd do a full database backup, then backup the transaction log as well
... both backup performed with a check on the option "clear inactive
entries from transaction log".

But after I backup, the database log is still 2GB.

What should I do to reduce the database log file size?
Should I use?:
==============================
Dump Tran databaseName with no_log
DBCC shrinkdatabase(databaseName, 30)
==============================

Is that safe to be used in production server?

Peter CCH[posted and mailed, please reply in news]

Peter CCH (petercch.wodoy@.gmail.com) writes:
> Database log of my DB is around 2GB.
> The database is using FULL recovery option.
> I want to reduce the file size of the log cause it takes up a lot of
> space.
> I'd do a full database backup, then backup the transaction log as well
> ... both backup performed with a check on the option "clear inactive
> entries from transaction log".
> But after I backup, the database log is still 2GB.
> What should I do to reduce the database log file size?
> Should I use?:
>==============================
> Dump Tran databaseName with no_log
> DBCC shrinkdatabase(databaseName, 30)
>==============================
> Is that safe to be used in production server?

First of alll, think twice before you start to shrink files at all.
If you know that your database have experienced a heavy once-in-a-blue-
moon update, then it could make sense to shrink the log. But if the
log is 2GB because of daily operations, the log will grow again. And
while the log is growing you lose performance on you server. And the
log file may be fragmented on file-system level.)

If you use NO_LOG, you must take a full backup, or else you will no
long have any up-to-the-point recovery option from that point and on.

DBCC SHRINKFILE is better to shrink the log file than DBCC SHRINKDATABASE
that will also operate on the data file.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||hi

you can use DBCC SHRINKFILE for this purpose

best Regards,
Chandra
http://groups.msn.com/SQLResource/
http://chanduas.blogspot.com/
------------

*** Sent via Developersdex http://www.developersdex.com ***|||I use the below statement. The concept of "it is just going to grow
again" is bogus. If you don't shrink it, it will eventually take up the
entire hard drive it is on (at least in our experience) and we have
experienced no performance problems with shrinking it every day to one
megabyte.

use DBTest
go

DBCC SHRINKFILE(DBTest_log, 1)
BACKUP LOG DBTest WITH TRUNCATE_ONLY
DBCC SHRINKFILE(DBTest_log, 1)
go|||> I use the below statement. The concept of "it is just going to grow
> again" is bogus. If you don't shrink it, it will eventually take up the
> entire hard drive it is on (at least in our experience) and we have
> experienced no performance problems with shrinking it every day to one
> megabyte.

This is very wrong and dangerous advice. Peter stated he is using FULL
RECOVERY so you just invalidated his log backups. Shrinking and growing
the log in this way can also put your system completely out of action.

If you need to shrink the log every day then someone isn't doing their
job properly.

Read:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp

--
David Portas
SQL Server MVP
--|||pb648174 (google@.webpaul.net) writes:
> I use the below statement. The concept of "it is just going to grow
> again" is bogus. If you don't shrink it, it will eventually take up the
> entire hard drive it is on (at least in our experience) and we have
> experienced no performance problems with shrinking it every day to one
> megabyte.

As David said, if you need to shrink it daily, you have a problem that
you should fix. If the log keeps growing, the most likely reason can be
because you are running with full or bulk-logged recovery, but are not
taking regular log backups. (Backing up the database does not truncate the
log.)

> use DBTest
> go
> DBCC SHRINKFILE(DBTest_log, 1)
> BACKUP LOG DBTest WITH TRUNCATE_ONLY
> DBCC SHRINKFILE(DBTest_log, 1)

Well, I know a better way: if you don't care about up-to-the-point recovery,
and are content with restoring the latest backup in case of a crash, set
the recovery mode to simple, and SQL Server will regularly truncate the
transaction log.

If your business requirements do call for up-to-the-point recovery,
you should never use WITH TRUNCATE_ONLY unless there is real emergency.
And in such case, you should take a full backup directly.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I haven't ever manually set any recovery mode setting, so is there a
default setting somewhere(I'm wondering why this is the default
behavior)? I have seen this same issue on many other databases and this
is the fix I have always used - We just use database backups for
diaster recovery and make them every two hours.|||pb648174 (google@.webpaul.net) writes:
> I haven't ever manually set any recovery mode setting, so is there a
> default setting somewhere

The default setting is inherited from the model database. And model is
shipped with the default setting of full recovery. (A vague recollections
tells me that MSDE may have it the other way round.)

>(I'm wondering why this is the default behavior)?

Because it assumed that you will take database and log backups to be
able to recover the database up to the last minute in case of a crash
or a severe manual mistake.

> I have seen this same issue on many other databases and this
> is the fix I have always used - We just use database backups for
> diaster recovery and make them every two hours.

If that is OK, set recovery mode to simple and for get about the
transaction log. Or start to take regular log backups.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||What about Database maintenance plan? is it helpful?

i mean if i configured it to tun everey night, and perform backup is T log
will shrinked ?

TNX

--
Message posted via http://www.sqlmonster.com|||Just out of curiosity, what point would there be in up to the minute
recovery? You will still need to make a point in time backup of the mdf
and ldf files, no? So why not just make a backup? Under what scenario
would you use the point in time restore?|||pb648174 (google@.webpaul.net) writes:
> Just out of curiosity, what point would there be in up to the minute
> recovery? You will still need to make a point in time backup of the mdf
> and ldf files, no? So why not just make a backup? Under what scenario
> would you use the point in time restore?

Say that you run a web shop. If you have a serious error, how much data
do you want to lose?

Say that you take full backsups every second hour. Last backup was 13:00.
At 14:53 you make intend a manual update from Query Analyzer to an order
which had some minor problem. But when you select the statement in the
query window you glitch, and the query is sent to SQL Server without the
WHERE clause. If you have simple recovery, you will have to restore that
backup from 13:00 and lose two hours of business. (And probably more,
because the customers whose orders disappeared this way may never come
back.)

With full recovery, you can in this situation take a backup of the
transaction log, and you can restore the database to the state it was
in at 14:52, and minimize your losses.

Another option for this fatal scenario is to have a log-reader tool like
Lumigent Log Explorer or LOG PI, which permits you rollback statements
selectively.

But for a scenario where the database becomes corrupt for an unknown
reason, they are less useful, and of course they can't help in case of
disk crash. In case of a disk crash you may not be able to backup the
last part of the log. However, some shops run transaction-log dumps
as often as every five minutes, or even by the minute.

And for many shops a full backup evervy second hour is not an option.
If your database is 100 GB, the backup takes time to run. And takes up
disk space.

But far from all business need all this. I recall a client whose database
proved to be corrupt. And what's worse, the most recent backup was also
corrupt. We had to go back over a week to find a good version. However,
this was not a major issue for them. They had their transaction log on
paper, so to speak, and could re-register all data that was lost.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Every minute of data lost costs money. That's why we have up to the
minute recovery from log backups.

How big are your databases? For a terabyte-scale database it isn't
practical to take backups every few minutes or even every hour.
Apparently your databases are small enough that it's feasible to take
2-hourly full backups. In that case use simple recovery.

--
David Portas
SQL Server MVP
--|||So if you are keeping a running transaction log and you get a command
that wipes out some data at 2:30, you can go back to how it was at
2:29? Can you post a command or a link on how to do that? I would like
to experiment with that.|||http://msdn.microsoft.com/library/d...backpc_5a61.asp
http://msdn.microsoft.com/library/d...backpc_6pv6.asp

--
David Portas
SQL Server MVP
--|||akej (forum@.SQLMonster.com) writes:
> What about Database maintenance plan? is it helpful?
> i mean if i configured it to tun everey night, and perform backup is T log
> will shrinked ?

If your database plan includes a backup of the transaction log, it
will trunctate the log. There should not really be any shrink, though.
Shrinking is only something you should do in special cases.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

LOG File size is 18GB

Hi everyone,
In one of my databases, the size of the LOG has gone upto 18GB. "Truncate
Log" doesn't help. How do I reduce the physical size of the file?
Abhay
Abhay
Perfom BACKUP LOG ..... and then DBCC SHRINKFILE commad (for more details
please refer to BOL)
"Abhay Sobti" <abhay@.stansoftware.com> wrote in message
news:%23OS6JH9nEHA.3668@.TK2MSFTNGP15.phx.gbl...
> Hi everyone,
> In one of my databases, the size of the LOG has gone upto 18GB. "Truncate
> Log" doesn't help. How do I reduce the physical size of the file?
> Abhay
>
|||Enabling the "Truncate log" is just part of the job. You also need to
shrink the file. Check BOL for dbcc shrinkfile.
Adi
|||Fix your clock.
"Abhay Sobti" <abhay@.stansoftware.com> wrote in message
news:%23OS6JH9nEHA.3668@.TK2MSFTNGP15.phx.gbl...
> Hi everyone,
> In one of my databases, the size of the LOG has gone upto 18GB. "Truncate
> Log" doesn't help. How do I reduce the physical size of the file?
> Abhay
>
|||Hi
Do the following tasks:
BACKUP LOG <database name> WITH TRUNCATE_ONLY
DBCC SHRINKDATABASE(<database name>)
Regards
Eli
Abhay Sobti wrote:
> Hi everyone,
> In one of my databases, the size of the LOG has gone upto 18GB. "Truncate
> Log" doesn't help. How do I reduce the physical size of the file?
> Abhay
>

LOG File size is 18GB

Hi everyone,
In one of my databases, the size of the LOG has gone upto 18GB. "Truncate
Log" doesn't help. How do I reduce the physical size of the file?
AbhayAbhay
Perfom BACKUP LOG ..... and then DBCC SHRINKFILE commad (for more details
please refer to BOL)
"Abhay Sobti" <abhay@.stansoftware.com> wrote in message
news:%23OS6JH9nEHA.3668@.TK2MSFTNGP15.phx.gbl...
> Hi everyone,
> In one of my databases, the size of the LOG has gone upto 18GB. "Truncate
> Log" doesn't help. How do I reduce the physical size of the file?
> Abhay
>|||Enabling the "Truncate log" is just part of the job. You also need to
shrink the file. Check BOL for dbcc shrinkfile.
Adi|||Fix your clock.
"Abhay Sobti" <abhay@.stansoftware.com> wrote in message
news:%23OS6JH9nEHA.3668@.TK2MSFTNGP15.phx.gbl...
> Hi everyone,
> In one of my databases, the size of the LOG has gone upto 18GB. "Truncate
> Log" doesn't help. How do I reduce the physical size of the file?
> Abhay
>|||Hi
Do the following tasks:
BACKUP LOG <database name> WITH TRUNCATE_ONLY
DBCC SHRINKDATABASE(<database name>)
Regards
Eli
Abhay Sobti wrote:
> Hi everyone,
> In one of my databases, the size of the LOG has gone upto 18GB. "Truncate
> Log" doesn't help. How do I reduce the physical size of the file?
> Abhay
>

Log file size

Hi,
Can anyone help on this, how to reduce the size of the log file ?
I have a database (SQL7.0) the mdf file is 1.6 Gbytes, but the ldf file is
17Gbytes. The database has been in operation for more than 2 years and why
has the log file grown to such a size ? It was set to auto growth at 10%. Can
I reduce it size and how? I need to claim back some disk space.
Thank you for your time
KC ThongKCThong wrote:
> Hi,
> Can anyone help on this, how to reduce the size of the log file ?
> I have a database (SQL7.0) the mdf file is 1.6 Gbytes, but the ldf
> file is 17Gbytes. The database has been in operation for more than 2
> years and why has the log file grown to such a size ? It was set to
> auto growth at 10%. Can I reduce it size and how? I need to claim
> back some disk space.
> Thank you for your time
> KC Thong
See DBCC SHRINKFILE in BOL.
--
David Gugick
Imceda Software
www.imceda.com|||Here are a few things to keep in mind:
â?¢ Always perform system database and user database backups before and after
you make changes that affect the system. DBCC SHRINKFILE and DBCC
SHRINKDATABASE are not logged operations, and running them invalidates
further transaction log backups. You must make a full database backup after
you run either the DBCC SHRINKFILE or the DBCC SHRINKDATABASE commands.
â?¢ Make sure that there are no backups scheduled to occur during the time the
shrink is supposed to occur.
â?¢ Make sure that there are no old, long-running, or unreplicated
transactions. To do so, use code similar to:DBCC OPENTRAN (database_name)
â?¢ Run the DBCC SHRINKFILE or DBCC SHRINKDATABASE command to mark a
shrinkpoint. DBCC SHRINKFILE and DBCC SHRINKDATABASE permissions default to
members of the sysadmin fixed server role or the db_owner fixed database
role, and are not transferable. For information about the differences between
these commands, refer to the following topics in SQL Books Online (note the
different parameters):
DBCC SHRINKFILE (file_name, target_size)
DBCC SHRINKDATABASE (database_name, target_percent)
â?¢ Create some dummy transactions to make the log wrap around and then issue
a BACKUP command to truncate the log. The BACKUP statement is what actually
attempts to shrink the log to the marked target size.
Here is a sample of how to create a dummy transactions that wraps the log
for a single logical log file and causes it to truncate, allowing for
shrinkage. Modify the sample as needed for your environment. SET NOCOUNT ON
DECLARE @.LogicalFileName sysname,
@.MaxMinutes INT,
@.NewSize INT
-- *** MAKE SURE TO CHANGE THE NEXT 4 LINES WITH YOUR CRITERIA. ***
USE [Test DB] -- This is the name of the database
-- for which the log will be shrunk.
SELECT @.LogicalFileName = 'Test DB Log', -- Use sp_helpfile to
-- identify the logical file
-- name that you want to shrink.
@.MaxMinutes = 10, -- Limit on time allowed to wrap log.
@.NewSize = 10 -- in MB
-- Setup / initialize
DECLARE @.OriginalSize int
SELECT @.OriginalSize = size -- in 8K pages
FROM sysfiles
WHERE name = @.LogicalFileName
SELECT 'Original Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),@.OriginalSize) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(@.OriginalSize*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @.LogicalFileName
CREATE TABLE DummyTrans
(DummyColumn char (8000) not null)
-- Wrap log and truncate it.
DECLARE @.Counter INT,
@.StartTime DATETIME,
@.TruncLog VARCHAR(255)
SELECT @.StartTime = GETDATE(),
@.TruncLog = 'BACKUP LOG ['+ db_name() + '] WITH TRUNCATE_ONLY'
-- Try an initial shrink.
DBCC SHRINKFILE (@.LogicalFileName, @.NewSize)
EXEC (@.TruncLog)
-- Wrap the log if necessary.
WHILE @.MaxMinutes > DATEDIFF (mi, @.StartTime, GETDATE()) -- time has
not expired
AND @.OriginalSize = (SELECT size FROM sysfiles WHERE name =@.LogicalFileName) -- the log has not shrunk
AND (@.OriginalSize * 8 /1024) > @.NewSize -- The value passed in
for new size is smaller than the current size.
BEGIN -- Outer loop.
SELECT @.Counter = 0
WHILE ((@.Counter < @.OriginalSize / 16) AND (@.Counter < 50000))
BEGIN -- update
INSERT DummyTrans VALUES ('Fill Log') -- Because it is a char
field it inserts 8000 bytes.
DELETE DummyTrans
SELECT @.Counter = @.Counter + 1
END -- update
EXEC (@.TruncLog) -- See if a trunc of the log shrinks it.
END -- outer loop
SELECT 'Final Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @.LogicalFileName
DROP TABLE DummyTrans
PRINT '*** Perform a full database backup ***'
SET NOCOUNT OFF
Check to see if the log has shrunk from its original size.Repeat the
preceding steps if necessary. If the log is not shrinking, re-check the
summary at the top of the article to see if you are encountering any of the
common issues with shrinking the log.
After the log shrinks:
1. Perform a full database backup of the master database.
2. Perform a full database backup of the user database. This is necessary
because the SHRINK command is not logged and invalidates future transaction
log backups unless a full database backup is completed.
To determine why the log is growing so big in the first place, you can check
for open transactions, long running transactions, unreplicated transactions,
or transactions that touch a lot of data.
"KCThong" wrote:
> Hi,
> Can anyone help on this, how to reduce the size of the log file ?
> I have a database (SQL7.0) the mdf file is 1.6 Gbytes, but the ldf file is
> 17Gbytes. The database has been in operation for more than 2 years and why
> has the log file grown to such a size ? It was set to auto growth at 10%. Can
> I reduce it size and how? I need to claim back some disk space.
> Thank you for your time
> KC Thong
>
>|||At this stage, if I set " trunc. log on chkpt" in the database option to
TRUE, does it help to clear the log or shrink ?
Pls advise.
"surajits" wrote:
> Here are a few things to keep in mind:
> â?¢ Always perform system database and user database backups before and after
> you make changes that affect the system. DBCC SHRINKFILE and DBCC
> SHRINKDATABASE are not logged operations, and running them invalidates
> further transaction log backups. You must make a full database backup after
> you run either the DBCC SHRINKFILE or the DBCC SHRINKDATABASE commands.
> â?¢ Make sure that there are no backups scheduled to occur during the time the
> shrink is supposed to occur.
> â?¢ Make sure that there are no old, long-running, or unreplicated
> transactions. To do so, use code similar to:DBCC OPENTRAN (database_name)
> â?¢ Run the DBCC SHRINKFILE or DBCC SHRINKDATABASE command to mark a
> shrinkpoint. DBCC SHRINKFILE and DBCC SHRINKDATABASE permissions default to
> members of the sysadmin fixed server role or the db_owner fixed database
> role, and are not transferable. For information about the differences between
> these commands, refer to the following topics in SQL Books Online (note the
> different parameters):
> DBCC SHRINKFILE (file_name, target_size)
> DBCC SHRINKDATABASE (database_name, target_percent)
> â?¢ Create some dummy transactions to make the log wrap around and then issue
> a BACKUP command to truncate the log. The BACKUP statement is what actually
> attempts to shrink the log to the marked target size.
> Here is a sample of how to create a dummy transactions that wraps the log
> for a single logical log file and causes it to truncate, allowing for
> shrinkage. Modify the sample as needed for your environment. SET NOCOUNT ON
> DECLARE @.LogicalFileName sysname,
> @.MaxMinutes INT,
> @.NewSize INT
> -- *** MAKE SURE TO CHANGE THE NEXT 4 LINES WITH YOUR CRITERIA. ***
> USE [Test DB] -- This is the name of the database
> -- for which the log will be shrunk.
> SELECT @.LogicalFileName = 'Test DB Log', -- Use sp_helpfile to
> -- identify the logical file
> -- name that you want to shrink.
> @.MaxMinutes = 10, -- Limit on time allowed to wrap log.
> @.NewSize = 10 -- in MB
> -- Setup / initialize
> DECLARE @.OriginalSize int
> SELECT @.OriginalSize = size -- in 8K pages
> FROM sysfiles
> WHERE name = @.LogicalFileName
> SELECT 'Original Size of ' + db_name() + ' LOG is ' +
> CONVERT(VARCHAR(30),@.OriginalSize) + ' 8K pages or ' +
> CONVERT(VARCHAR(30),(@.OriginalSize*8/1024)) + 'MB'
> FROM sysfiles
> WHERE name = @.LogicalFileName
> CREATE TABLE DummyTrans
> (DummyColumn char (8000) not null)
> -- Wrap log and truncate it.
> DECLARE @.Counter INT,
> @.StartTime DATETIME,
> @.TruncLog VARCHAR(255)
> SELECT @.StartTime = GETDATE(),
> @.TruncLog = 'BACKUP LOG ['+ db_name() + '] WITH TRUNCATE_ONLY'
> -- Try an initial shrink.
> DBCC SHRINKFILE (@.LogicalFileName, @.NewSize)
> EXEC (@.TruncLog)
> -- Wrap the log if necessary.
> WHILE @.MaxMinutes > DATEDIFF (mi, @.StartTime, GETDATE()) -- time has
> not expired
> AND @.OriginalSize = (SELECT size FROM sysfiles WHERE name => @.LogicalFileName) -- the log has not shrunk
> AND (@.OriginalSize * 8 /1024) > @.NewSize -- The value passed in
> for new size is smaller than the current size.
> BEGIN -- Outer loop.
> SELECT @.Counter = 0
> WHILE ((@.Counter < @.OriginalSize / 16) AND (@.Counter < 50000))
> BEGIN -- update
> INSERT DummyTrans VALUES ('Fill Log') -- Because it is a char
> field it inserts 8000 bytes.
> DELETE DummyTrans
> SELECT @.Counter = @.Counter + 1
> END -- update
> EXEC (@.TruncLog) -- See if a trunc of the log shrinks it.
> END -- outer loop
> SELECT 'Final Size of ' + db_name() + ' LOG is ' +
> CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
> CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
> FROM sysfiles
> WHERE name = @.LogicalFileName
> DROP TABLE DummyTrans
> PRINT '*** Perform a full database backup ***'
> SET NOCOUNT OFF
> Check to see if the log has shrunk from its original size.Repeat the
> preceding steps if necessary. If the log is not shrinking, re-check the
> summary at the top of the article to see if you are encountering any of the
> common issues with shrinking the log.
> After the log shrinks:
> 1. Perform a full database backup of the master database.
> 2. Perform a full database backup of the user database. This is necessary
> because the SHRINK command is not logged and invalidates future transaction
> log backups unless a full database backup is completed.
> To determine why the log is growing so big in the first place, you can check
> for open transactions, long running transactions, unreplicated transactions,
> or transactions that touch a lot of data.
> "KCThong" wrote:
> > Hi,
> > Can anyone help on this, how to reduce the size of the log file ?
> > I have a database (SQL7.0) the mdf file is 1.6 Gbytes, but the ldf file is
> > 17Gbytes. The database has been in operation for more than 2 years and why
> > has the log file grown to such a size ? It was set to auto growth at 10%. Can
> > I reduce it size and how? I need to claim back some disk space.
> >
> > Thank you for your time
> > KC Thong
> >
> >
> >
> >sql

Log file size

Hi,
Can anyone help on this, how to reduce the size of the log file ?
I have a database (SQL7.0) the mdf file is 1.6 Gbytes, but the ldf file is
17Gbytes. The database has been in operation for more than 2 years and why
has the log file grown to such a size ? It was set to auto growth at 10%. Can
I reduce it size and how? I need to claim back some disk space.
Thank you for your time
KC Thong
KCThong wrote:
> Hi,
> Can anyone help on this, how to reduce the size of the log file ?
> I have a database (SQL7.0) the mdf file is 1.6 Gbytes, but the ldf
> file is 17Gbytes. The database has been in operation for more than 2
> years and why has the log file grown to such a size ? It was set to
> auto growth at 10%. Can I reduce it size and how? I need to claim
> back some disk space.
> Thank you for your time
> KC Thong
See DBCC SHRINKFILE in BOL.
David Gugick
Imceda Software
www.imceda.com
|||Here are a few things to keep in mind:
? Always perform system database and user database backups before and after
you make changes that affect the system. DBCC SHRINKFILE and DBCC
SHRINKDATABASE are not logged operations, and running them invalidates
further transaction log backups. You must make a full database backup after
you run either the DBCC SHRINKFILE or the DBCC SHRINKDATABASE commands.
? Make sure that there are no backups scheduled to occur during the time the
shrink is supposed to occur.
? Make sure that there are no old, long-running, or unreplicated
transactions. To do so, use code similar to:DBCC OPENTRAN (database_name)
? Run the DBCC SHRINKFILE or DBCC SHRINKDATABASE command to mark a
shrinkpoint. DBCC SHRINKFILE and DBCC SHRINKDATABASE permissions default to
members of the sysadmin fixed server role or the db_owner fixed database
role, and are not transferable. For information about the differences between
these commands, refer to the following topics in SQL Books Online (note the
different parameters):
DBCC SHRINKFILE (file_name, target_size)
DBCC SHRINKDATABASE (database_name, target_percent)
? Create some dummy transactions to make the log wrap around and then issue
a BACKUP command to truncate the log. The BACKUP statement is what actually
attempts to shrink the log to the marked target size.
Here is a sample of how to create a dummy transactions that wraps the log
for a single logical log file and causes it to truncate, allowing for
shrinkage. Modify the sample as needed for your environment. SET NOCOUNT ON
DECLARE @.LogicalFileName sysname,
@.MaxMinutes INT,
@.NewSize INT
-- *** MAKE SURE TO CHANGE THE NEXT 4 LINES WITH YOUR CRITERIA. ***
USE [Test DB] -- This is the name of the database
-- for which the log will be shrunk.
SELECT @.LogicalFileName = 'Test DB Log', -- Use sp_helpfile to
-- identify the logical file
-- name that you want to shrink.
@.MaxMinutes = 10, -- Limit on time allowed to wrap log.
@.NewSize = 10 -- in MB
-- Setup / initialize
DECLARE @.OriginalSize int
SELECT @.OriginalSize = size -- in 8K pages
FROM sysfiles
WHERE name = @.LogicalFileName
SELECT 'Original Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),@.OriginalSize) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(@.OriginalSize*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @.LogicalFileName
CREATE TABLE DummyTrans
(DummyColumn char (8000) not null)
-- Wrap log and truncate it.
DECLARE @.Counter INT,
@.StartTime DATETIME,
@.TruncLog VARCHAR(255)
SELECT @.StartTime = GETDATE(),
@.TruncLog = 'BACKUP LOG ['+ db_name() + '] WITH TRUNCATE_ONLY'
-- Try an initial shrink.
DBCC SHRINKFILE (@.LogicalFileName, @.NewSize)
EXEC (@.TruncLog)
-- Wrap the log if necessary.
WHILE @.MaxMinutes > DATEDIFF (mi, @.StartTime, GETDATE()) -- time has
not expired
AND @.OriginalSize = (SELECT size FROM sysfiles WHERE name =
@.LogicalFileName) -- the log has not shrunk
AND (@.OriginalSize * 8 /1024) > @.NewSize -- The value passed in
for new size is smaller than the current size.
BEGIN -- Outer loop.
SELECT @.Counter = 0
WHILE ((@.Counter < @.OriginalSize / 16) AND (@.Counter < 50000))
BEGIN -- update
INSERT DummyTrans VALUES ('Fill Log') -- Because it is a char
field it inserts 8000 bytes.
DELETE DummyTrans
SELECT @.Counter = @.Counter + 1
END -- update
EXEC (@.TruncLog) -- See if a trunc of the log shrinks it.
END -- outer loop
SELECT 'Final Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @.LogicalFileName
DROP TABLE DummyTrans
PRINT '*** Perform a full database backup ***'
SET NOCOUNT OFF
Check to see if the log has shrunk from its original size.Repeat the
preceding steps if necessary. If the log is not shrinking, re-check the
summary at the top of the article to see if you are encountering any of the
common issues with shrinking the log.
After the log shrinks:
1. Perform a full database backup of the master database.
2. Perform a full database backup of the user database. This is necessary
because the SHRINK command is not logged and invalidates future transaction
log backups unless a full database backup is completed.
To determine why the log is growing so big in the first place, you can check
for open transactions, long running transactions, unreplicated transactions,
or transactions that touch a lot of data.
"KCThong" wrote:

> Hi,
> Can anyone help on this, how to reduce the size of the log file ?
> I have a database (SQL7.0) the mdf file is 1.6 Gbytes, but the ldf file is
> 17Gbytes. The database has been in operation for more than 2 years and why
> has the log file grown to such a size ? It was set to auto growth at 10%. Can
> I reduce it size and how? I need to claim back some disk space.
> Thank you for your time
> KC Thong
>
>
|||At this stage, if I set " trunc. log on chkpt" in the database option to
TRUE, does it help to clear the log or shrink ?
Pls advise.
"surajits" wrote:
[vbcol=seagreen]
> Here are a few things to keep in mind:
> ? Always perform system database and user database backups before and after
> you make changes that affect the system. DBCC SHRINKFILE and DBCC
> SHRINKDATABASE are not logged operations, and running them invalidates
> further transaction log backups. You must make a full database backup after
> you run either the DBCC SHRINKFILE or the DBCC SHRINKDATABASE commands.
> ? Make sure that there are no backups scheduled to occur during the time the
> shrink is supposed to occur.
> ? Make sure that there are no old, long-running, or unreplicated
> transactions. To do so, use code similar to:DBCC OPENTRAN (database_name)
> ? Run the DBCC SHRINKFILE or DBCC SHRINKDATABASE command to mark a
> shrinkpoint. DBCC SHRINKFILE and DBCC SHRINKDATABASE permissions default to
> members of the sysadmin fixed server role or the db_owner fixed database
> role, and are not transferable. For information about the differences between
> these commands, refer to the following topics in SQL Books Online (note the
> different parameters):
> DBCC SHRINKFILE (file_name, target_size)
> DBCC SHRINKDATABASE (database_name, target_percent)
> ? Create some dummy transactions to make the log wrap around and then issue
> a BACKUP command to truncate the log. The BACKUP statement is what actually
> attempts to shrink the log to the marked target size.
> Here is a sample of how to create a dummy transactions that wraps the log
> for a single logical log file and causes it to truncate, allowing for
> shrinkage. Modify the sample as needed for your environment. SET NOCOUNT ON
> DECLARE @.LogicalFileName sysname,
> @.MaxMinutes INT,
> @.NewSize INT
> -- *** MAKE SURE TO CHANGE THE NEXT 4 LINES WITH YOUR CRITERIA. ***
> USE [Test DB] -- This is the name of the database
> -- for which the log will be shrunk.
> SELECT @.LogicalFileName = 'Test DB Log', -- Use sp_helpfile to
> -- identify the logical file
> -- name that you want to shrink.
> @.MaxMinutes = 10, -- Limit on time allowed to wrap log.
> @.NewSize = 10 -- in MB
> -- Setup / initialize
> DECLARE @.OriginalSize int
> SELECT @.OriginalSize = size -- in 8K pages
> FROM sysfiles
> WHERE name = @.LogicalFileName
> SELECT 'Original Size of ' + db_name() + ' LOG is ' +
> CONVERT(VARCHAR(30),@.OriginalSize) + ' 8K pages or ' +
> CONVERT(VARCHAR(30),(@.OriginalSize*8/1024)) + 'MB'
> FROM sysfiles
> WHERE name = @.LogicalFileName
> CREATE TABLE DummyTrans
> (DummyColumn char (8000) not null)
> -- Wrap log and truncate it.
> DECLARE @.Counter INT,
> @.StartTime DATETIME,
> @.TruncLog VARCHAR(255)
> SELECT @.StartTime = GETDATE(),
> @.TruncLog = 'BACKUP LOG ['+ db_name() + '] WITH TRUNCATE_ONLY'
> -- Try an initial shrink.
> DBCC SHRINKFILE (@.LogicalFileName, @.NewSize)
> EXEC (@.TruncLog)
> -- Wrap the log if necessary.
> WHILE @.MaxMinutes > DATEDIFF (mi, @.StartTime, GETDATE()) -- time has
> not expired
> AND @.OriginalSize = (SELECT size FROM sysfiles WHERE name =
> @.LogicalFileName) -- the log has not shrunk
> AND (@.OriginalSize * 8 /1024) > @.NewSize -- The value passed in
> for new size is smaller than the current size.
> BEGIN -- Outer loop.
> SELECT @.Counter = 0
> WHILE ((@.Counter < @.OriginalSize / 16) AND (@.Counter < 50000))
> BEGIN -- update
> INSERT DummyTrans VALUES ('Fill Log') -- Because it is a char
> field it inserts 8000 bytes.
> DELETE DummyTrans
> SELECT @.Counter = @.Counter + 1
> END -- update
> EXEC (@.TruncLog) -- See if a trunc of the log shrinks it.
> END -- outer loop
> SELECT 'Final Size of ' + db_name() + ' LOG is ' +
> CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
> CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
> FROM sysfiles
> WHERE name = @.LogicalFileName
> DROP TABLE DummyTrans
> PRINT '*** Perform a full database backup ***'
> SET NOCOUNT OFF
> Check to see if the log has shrunk from its original size.Repeat the
> preceding steps if necessary. If the log is not shrinking, re-check the
> summary at the top of the article to see if you are encountering any of the
> common issues with shrinking the log.
> After the log shrinks:
> 1. Perform a full database backup of the master database.
> 2. Perform a full database backup of the user database. This is necessary
> because the SHRINK command is not logged and invalidates future transaction
> log backups unless a full database backup is completed.
> To determine why the log is growing so big in the first place, you can check
> for open transactions, long running transactions, unreplicated transactions,
> or transactions that touch a lot of data.
> "KCThong" wrote:

Wednesday, March 21, 2012

Log file is increeasing too much

Hi
i have a database and a proccess which delete everything from database and
then import some stuff from other databases.
how can i reduce to increase log file because sometimes import stops because
of it.
I use the following procedures :
backup log mydatabase with truncate_only
dbcc shrinkdatabase (mydatabase)
i was wondering if it's any possible way to put a maximum size for log file
and when it get to the maximum file size to override the existent logs and
not raise me the error that "The log file for database 'mydatabase' is full.
Back up the transaction log for the database to free up some log space."
thanks in advanceIf you are using the FULL recovery model, you would need to backup the log
before it can be reused. Otherwise, you might consider using the simple
recovery model.
Another possibility is that your import process is done within one
transaction, hence all the data is either committed or rolled back. If this
was the case (and you are using the simple recovery model), you might
consider breaking the import into smaller transactions.
If you are deleting everything initially, you might also consider using the
TRUNCATE TABLE option as the row deletion is not individually logged.
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Psycho" <Psycho@.discussions.microsoft.com> wrote in message
news:26E3911D-CEA7-4389-8E26-1E76B3EEDF11@.microsoft.com...
> Hi
> i have a database and a proccess which delete everything from database and
> then import some stuff from other databases.
> how can i reduce to increase log file because sometimes import stops
because
> of it.
> I use the following procedures :
> backup log mydatabase with truncate_only
> dbcc shrinkdatabase (mydatabase)
> i was wondering if it's any possible way to put a maximum size for log
file
> and when it get to the maximum file size to override the existent logs and
> not raise me the error that "The log file for database 'mydatabase' is
full.
> Back up the transaction log for the database to free up some log space."
> thanks in advance|||Thanks
i think this was the problem. I use delete command instead of truncate. I
turned the recovery mode to simple and I put the option auto shrink on.
Thanks again.
"Peter Yeoh" wrote:
> If you are using the FULL recovery model, you would need to backup the log
> before it can be reused. Otherwise, you might consider using the simple
> recovery model.
> Another possibility is that your import process is done within one
> transaction, hence all the data is either committed or rolled back. If this
> was the case (and you are using the simple recovery model), you might
> consider breaking the import into smaller transactions.
> If you are deleting everything initially, you might also consider using the
> TRUNCATE TABLE option as the row deletion is not individually logged.
> --
> Peter Yeoh
> http://www.yohz.com
> Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
>
> "Psycho" <Psycho@.discussions.microsoft.com> wrote in message
> news:26E3911D-CEA7-4389-8E26-1E76B3EEDF11@.microsoft.com...
> > Hi
> > i have a database and a proccess which delete everything from database and
> > then import some stuff from other databases.
> > how can i reduce to increase log file because sometimes import stops
> because
> > of it.
> > I use the following procedures :
> >
> > backup log mydatabase with truncate_only
> > dbcc shrinkdatabase (mydatabase)
> >
> > i was wondering if it's any possible way to put a maximum size for log
> file
> > and when it get to the maximum file size to override the existent logs and
> > not raise me the error that "The log file for database 'mydatabase' is
> full.
> > Back up the transaction log for the database to free up some log space."
> >
> > thanks in advance
>
>|||You might want to check out my article regarding shrink:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Psycho" <Psycho@.discussions.microsoft.com> wrote in message
news:AC3F1D5E-CA73-47D8-8EA0-D3FBF3315DAA@.microsoft.com...
> Thanks
> i think this was the problem. I use delete command instead of truncate. I
> turned the recovery mode to simple and I put the option auto shrink on.
> Thanks again.
> "Peter Yeoh" wrote:
>> If you are using the FULL recovery model, you would need to backup the log
>> before it can be reused. Otherwise, you might consider using the simple
>> recovery model.
>> Another possibility is that your import process is done within one
>> transaction, hence all the data is either committed or rolled back. If this
>> was the case (and you are using the simple recovery model), you might
>> consider breaking the import into smaller transactions.
>> If you are deleting everything initially, you might also consider using the
>> TRUNCATE TABLE option as the row deletion is not individually logged.
>> --
>> Peter Yeoh
>> http://www.yohz.com
>> Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
>>
>> "Psycho" <Psycho@.discussions.microsoft.com> wrote in message
>> news:26E3911D-CEA7-4389-8E26-1E76B3EEDF11@.microsoft.com...
>> > Hi
>> > i have a database and a proccess which delete everything from database and
>> > then import some stuff from other databases.
>> > how can i reduce to increase log file because sometimes import stops
>> because
>> > of it.
>> > I use the following procedures :
>> >
>> > backup log mydatabase with truncate_only
>> > dbcc shrinkdatabase (mydatabase)
>> >
>> > i was wondering if it's any possible way to put a maximum size for log
>> file
>> > and when it get to the maximum file size to override the existent logs and
>> > not raise me the error that "The log file for database 'mydatabase' is
>> full.
>> > Back up the transaction log for the database to free up some log space."
>> >
>> > thanks in advance
>>|||Also Truncate table does NOT work on tables with FKs, so you'll have to
remove those first..
Another alternative is to delete a range of rows ie
set rowcount 10000
delete from mytable
while @.@.rowcount !=0
delete from mytable
set rowcount 0
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Psycho" <Psycho@.discussions.microsoft.com> wrote in message
news:26E3911D-CEA7-4389-8E26-1E76B3EEDF11@.microsoft.com...
> Hi
> i have a database and a proccess which delete everything from database and
> then import some stuff from other databases.
> how can i reduce to increase log file because sometimes import stops
because
> of it.
> I use the following procedures :
> backup log mydatabase with truncate_only
> dbcc shrinkdatabase (mydatabase)
> i was wondering if it's any possible way to put a maximum size for log
file
> and when it get to the maximum file size to override the existent logs and
> not raise me the error that "The log file for database 'mydatabase' is
full.
> Back up the transaction log for the database to free up some log space."
> thanks in advance

Monday, March 19, 2012

Log file is increeasing too much

Monday, March 12, 2012

Log File (LDF) ?? (HELP)

Is there any way to clear out the LDF file or reduce in size?
thank you in advance fro any help,
Michael KintnerHi
DBCC SHRINKFILE. For more details please see BOL
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
"Michael Kintner" <nospam@.nospam.com> wrote in message
news:13dvsktmk6afhb3@.corp.supernews.com...
> Is there any way to clear out the LDF file or reduce in size?
> thank you in advance fro any help,
> Michael Kintner
>|||On Thu, 6 Sep 2007 08:29:16 -0400, "Michael Kintner"
<nospam@.nospam.com> wrote:
>Is there any way to clear out the LDF file or reduce in size?
>thank you in advance fro any help,
>Michael Kintner
First, what recovery model (Simple, Full, Bulk-Logged) is the database
using? If Simple, it will clear out by itself. For the others it
will only be cleared with the log is backed up. If no log backups are
running then it simply grows, and grows, and grows...
As for reducing the size, DBCC SHRINKFILE will do the job. However
shrinking the log file should be a VERY rare event. Shrinking the
file, only to have it grow again, is bad for performance. Allocating
more space is a significant delay for the transaction that causes it,
and physical file fragmentation is a common side effect. To over
simplify things a bit, set the log size large enough and backup the
log regularly to keep it from getting full.
Roy Harvey
Beacon Falls, CT|||In article <hn50e39j2sjnksnka0m5jem0qq12483ep0@.4ax.com>,
roy_harvey@.snet.net says...
> On Thu, 6 Sep 2007 08:29:16 -0400, "Michael Kintner"
> <nospam@.nospam.com> wrote:
> >Is there any way to clear out the LDF file or reduce in size?
> >
> >thank you in advance fro any help,
> >Michael Kintner
> First, what recovery model (Simple, Full, Bulk-Logged) is the database
> using? If Simple, it will clear out by itself. For the others it
> will only be cleared with the log is backed up. If no log backups are
> running then it simply grows, and grows, and grows...
> As for reducing the size, DBCC SHRINKFILE will do the job. However
> shrinking the log file should be a VERY rare event. Shrinking the
> file, only to have it grow again, is bad for performance. Allocating
> more space is a significant delay for the transaction that causes it,
> and physical file fragmentation is a common side effect. To over
> simplify things a bit, set the log size large enough and backup the
> log regularly to keep it from getting full.
> Roy Harvey
> Beacon Falls, CT
>
Just to add a bit -- SHRINKFILE can also be thwarted by open long-
running transactions since the shrink is done in units of VLF's and
won't occur if there is an open active transaction within the virtual
log. In order to get back space recently -- a 5G database had an 8G
logfile -- I had to backup the log, truncate the logfile and then shrink
to a more reasonable target. I figured 25% was a good guesstimate and of
that only 14M was actually used. Why so out of whack? -- try running an
app w/ no BACKUP LOG for over 2 years. The vendor never bothered to tell
my predecessor the basics of configuring sql and it just grew unbounded.
--
Graham (Pete) Berry
PeteBerry@.Caltech.edu
--
Graham (Pete) Berry
PeteBerry@.Caltech.edu

Log File (LDF) ?? (HELP)

Is there any way to clear out the LDF file or reduce in size?

thank you in advance fro any help,
Michael KintnerHi
DBCC SHRINKFILE. For more details please see BOL
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
"Michael Kintner" <nospam@.nospam.comwrote in message
news:13dvsktmk6afhb3@.corp.supernews.com...

Quote:

Originally Posted by

Is there any way to clear out the LDF file or reduce in size?
>
thank you in advance fro any help,
Michael Kintner
>

|||On Thu, 6 Sep 2007 08:29:16 -0400, "Michael Kintner"
<nospam@.nospam.comwrote:

Quote:

Originally Posted by

>Is there any way to clear out the LDF file or reduce in size?
>
>thank you in advance fro any help,
>Michael Kintner

First, what recovery model (Simple, Full, Bulk-Logged) is the database
using? If Simple, it will clear out by itself. For the others it
will only be cleared with the log is backed up. If no log backups are
running then it simply grows, and grows, and grows...

As for reducing the size, DBCC SHRINKFILE will do the job. However
shrinking the log file should be a VERY rare event. Shrinking the
file, only to have it grow again, is bad for performance. Allocating
more space is a significant delay for the transaction that causes it,
and physical file fragmentation is a common side effect. To over
simplify things a bit, set the log size large enough and backup the
log regularly to keep it from getting full.

Roy Harvey
Beacon Falls, CT

Friday, February 24, 2012

log

the database file is 112m
the log file is 2614m
but it only uses 50m of 2614m of the log file.
how can i shrink / reduce the size of the log file.
it is getting bigger. it makes me not comfortable by its size.
thanks.Make sure you either change to SIMPLE recovery mode or do regular log
backups. These should explain everything:
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=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
Andrew J. Kelly
SQL Server MVP
"tony wong" <x34@.hknet.com> wrote in message
news:OJR8$l88DHA.1548@.tk2msftngp13.phx.gbl...
> the database file is 112m
> the log file is 2614m
> but it only uses 50m of 2614m of the log file.
> how can i shrink / reduce the size of the log file.
> it is getting bigger. it makes me not comfortable by its size.
> thanks.
>
>|||Andrew, thanks a lot.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> glsD
:Op0gQ988DHA.2760@.TK2MSFTNGP09.phx.gbl...
> Make sure you either change to SIMPLE recovery mode or do regular log
> backups. These should explain everything:
> 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=256650 INF: How to Shrink the SQL
> Server 7.0 Tran Log
> http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
> Server 2000 with DBCC SHRINKFILE
>
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "tony wong" <x34@.hknet.com> wrote in message
> news:OJR8$l88DHA.1548@.tk2msftngp13.phx.gbl...
>

Monday, February 20, 2012

log

the database file is 112m
the log file is 2614m
but it only uses 50m of 2614m of the log file.
how can i shrink / reduce the size of the log file.
it is getting bigger. it makes me not comfortable by its size.
thanks.Make sure you either change to SIMPLE recovery mode or do regular log
backups. These should explain everything:
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=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
Andrew J. Kelly
SQL Server MVP
"tony wong" <x34@.hknet.com> wrote in message
news:OJR8$l88DHA.1548@.tk2msftngp13.phx.gbl...
> the database file is 112m
> the log file is 2614m
> but it only uses 50m of 2614m of the log file.
> how can i shrink / reduce the size of the log file.
> it is getting bigger. it makes me not comfortable by its size.
> thanks.
>
>|||Andrew, thanks a lot.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> ¼¶¼g©ó¶l¥ó·s»D
:Op0gQ988DHA.2760@.TK2MSFTNGP09.phx.gbl...
> Make sure you either change to SIMPLE recovery mode or do regular log
> backups. These should explain everything:
> 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=256650 INF: How to Shrink the SQL
> Server 7.0 Tran Log
> http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
> Server 2000 with DBCC SHRINKFILE
>
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "tony wong" <x34@.hknet.com> wrote in message
> news:OJR8$l88DHA.1548@.tk2msftngp13.phx.gbl...
> > the database file is 112m
> > the log file is 2614m
> >
> > but it only uses 50m of 2614m of the log file.
> >
> > how can i shrink / reduce the size of the log file.
> >
> > it is getting bigger. it makes me not comfortable by its size.
> >
> > thanks.
> >
> >
> >
>

Wednesday, March 28, 2012

log grows after shrinking

Friday, March 23, 2012

log file to be reduced

log file to be reduced

Log File Size reduction

Log File Size reduction

Log file size not reduce

LOG File size is 18GB

LOG File size is 18GB

Log file size

Log file size

Wednesday, March 21, 2012

Log file is increeasing too much

Monday, March 19, 2012

Log file is increeasing too much

Log file is increeasing too much

Monday, March 12, 2012

Log File (LDF) ?? (HELP)

Log File (LDF) ?? (HELP)

Friday, February 24, 2012

log

Monday, February 20, 2012

log

Locks and Blocks

Blog Archive

About Me