We have SQL 2K Enterprise Edition with SP3. Server is dedicated database
server used for JDE application. Database size is 100 Gig. This DB is
configured for replication (only 25 tables). This database is also
configured for Log ship to a stand by server where we run our reports.
When we run DBCC DBREINDEX or DBCC INDEXDEFRAG on this database, the log
file start growing crazy, which makes replication and log ship to break.
Our concern is how can we avoid growing log file while DBREINDEX or
INDEXDEFRAG running?
Your response is appreciated.
Thanks,
AbbasBackup or truncate the transaction log frequently during
the processes is the only thing I can think of. Or set
the Database Recovery model to Simple. Any other ideas?
>--Original Message--
>We have SQL 2K Enterprise Edition with SP3. Server is
dedicated database
>server used for JDE application. Database size is 100
Gig. This DB is
>configured for replication (only 25 tables). This
database is also
>configured for Log ship to a stand by server where we run
our reports.
>When we run DBCC DBREINDEX or DBCC INDEXDEFRAG on this
database, the log
>file start growing crazy, which makes replication and log
ship to break.
>Our concern is how can we avoid growing log file while
DBREINDEX or
>INDEXDEFRAG running?
>Your response is appreciated.
>Thanks,
>Abbas
>
>.
>|||*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||You can't. These actions, like all in the server are logged. These type of
actions will send a lot of data to the log files. I suggest doing them in
small batches so the logs can recover in between the reindexing. If done
often or there is little fragmentation INDEXDEFRAGmay produce less log
entries.
--
Andrew J. Kelly
SQL Server MVP
"Moh Abb" <mabbas@.aligntech.com> wrote in message
news:%23kkvKL%23cDHA.1828@.TK2MSFTNGP10.phx.gbl...
> We have SQL 2K Enterprise Edition with SP3. Server is dedicated database
> server used for JDE application. Database size is 100 Gig. This DB is
> configured for replication (only 25 tables). This database is also
> configured for Log ship to a stand by server where we run our reports.
> When we run DBCC DBREINDEX or DBCC INDEXDEFRAG on this database, the log
> file start growing crazy, which makes replication and log ship to break.
> Our concern is how can we avoid growing log file while DBREINDEX or
> INDEXDEFRAG running?
> Your response is appreciated.
> Thanks,
> Abbas
>
>|||Hello
You can't avoid this, but you can accommodate yourself to this :-)
I'm using the system of two connected jobs. One of them runs
DBCC INDEXDEFRAG and second periodically (every minute)
checks log state and stops first job when log have more than 70%
of space filled. And the system waits for the next log backup and
starts again. In general controlling job can start log backup instead
of stopping defragmentation.
> When we run DBCC DBREINDEX or DBCC INDEXDEFRAG on this database, the log
> file start growing crazy, which makes replication and log ship to break.
> Our concern is how can we avoid growing log file while DBREINDEX or
> INDEXDEFRAG running?
Serge Shakhovsql
Showing posts with label size. Show all posts
Showing posts with label size. Show all posts
Friday, March 30, 2012
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.
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.
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
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
Log free space question
Hi
I am using sql server 7.0 and one of DB is relatively
small. The size is about 2 MB however the log size is
about 30GB the problems is the log used space is 38 MB
but the log free space is about 37 GB so the whole backup
and restore takes some time as the toal og size is the
sum of these two.
I tried to shrink the file with truncate_only option but
cant get it to a small size
Can anyone helphttp://www.nigelrivett.net/Transact...ileGrows_1.html Log File issues
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
http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
considerations
Andrew J. Kelly SQL MVP
"Ap" <anonymous@.discussions.microsoft.com> wrote in message
news:154201c4f773$43971110$a501280a@.phx.gbl...
> Hi
> I am using sql server 7.0 and one of DB is relatively
> small. The size is about 2 MB however the log size is
> about 30GB the problems is the log used space is 38 MB
> but the log free space is about 37 GB so the whole backup
> and restore takes some time as the toal og size is the
> sum of these two.
> I tried to shrink the file with truncate_only option but
> cant get it to a small size
> Can anyone help|||Ap wrote:
> Hi
> I am using sql server 7.0 and one of DB is relatively
> small. The size is about 2 MB however the log size is
> about 30GB the problems is the log used space is 38 MB
> but the log free space is about 37 GB so the whole backup
> and restore takes some time as the toal og size is the
> sum of these two.
> I tried to shrink the file with truncate_only option but
> cant get it to a small size
> Can anyone help
Try DBCC SHRINKFILE (off hours, of course).
David Gugick
Imceda Software
www.imceda.com|||I see the same thing all the time and the solutions provided by David
and Andrew work beautifully (I've had logs up to 6GB and this process
has worked as advertised). Order is everything, are you sure you're
running the them in the appropriate order? (Try sticking with isql or
isqlw. Enterprise Manager has it's place, however, for general
maintenance, it's often easier to issue a statement directly.)
Step One:
This will give you an idea of your usage and should provide the
information you'd posted earlier
DBCC SQLPERF(LOGSPACE)
Step Two:
If your logs are huge, check to see where the current active vlf is
located. Active vlfs have a status of two while inactives have a status
of zero. Chances are, if you're running this off hours you'll see a
single active vlf towards the very bottom of the result set.
DBCC LOGINFO
Step Three:
If this is the case, you need to BACKUP LOG WITH NO_LOG or
TRUNCATE_ONLY (synonymous) to move the vlf closer to the beginning of
the log file. This essentially trashes the transactions that are in the
log (they are no longer recoverable), so you'll want perform a BACKUP
DATABASE soon after.
BACKUP LOG DB_NAME WITH NO_LOG
BACKUP DATABASE DB_NAME TO DISK = '\\anywhere\db.bak'
Step Four:
As soon as you BACKUP NO_LOG (and the *highly* recommended BACKUP
DATABASE) you're free to shrink the log file.
DBCC SHRINKFILE(DB_LOG, 2)
Step Five:
Double check that everything went well (hasn't failed for me yet!):
DBCC SQLPERF(LOGSPACE)
I am using sql server 7.0 and one of DB is relatively
small. The size is about 2 MB however the log size is
about 30GB the problems is the log used space is 38 MB
but the log free space is about 37 GB so the whole backup
and restore takes some time as the toal og size is the
sum of these two.
I tried to shrink the file with truncate_only option but
cant get it to a small size
Can anyone helphttp://www.nigelrivett.net/Transact...ileGrows_1.html Log File issues
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
http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
considerations
Andrew J. Kelly SQL MVP
"Ap" <anonymous@.discussions.microsoft.com> wrote in message
news:154201c4f773$43971110$a501280a@.phx.gbl...
> Hi
> I am using sql server 7.0 and one of DB is relatively
> small. The size is about 2 MB however the log size is
> about 30GB the problems is the log used space is 38 MB
> but the log free space is about 37 GB so the whole backup
> and restore takes some time as the toal og size is the
> sum of these two.
> I tried to shrink the file with truncate_only option but
> cant get it to a small size
> Can anyone help|||Ap wrote:
> Hi
> I am using sql server 7.0 and one of DB is relatively
> small. The size is about 2 MB however the log size is
> about 30GB the problems is the log used space is 38 MB
> but the log free space is about 37 GB so the whole backup
> and restore takes some time as the toal og size is the
> sum of these two.
> I tried to shrink the file with truncate_only option but
> cant get it to a small size
> Can anyone help
Try DBCC SHRINKFILE (off hours, of course).
David Gugick
Imceda Software
www.imceda.com|||I see the same thing all the time and the solutions provided by David
and Andrew work beautifully (I've had logs up to 6GB and this process
has worked as advertised). Order is everything, are you sure you're
running the them in the appropriate order? (Try sticking with isql or
isqlw. Enterprise Manager has it's place, however, for general
maintenance, it's often easier to issue a statement directly.)
Step One:
This will give you an idea of your usage and should provide the
information you'd posted earlier
DBCC SQLPERF(LOGSPACE)
Step Two:
If your logs are huge, check to see where the current active vlf is
located. Active vlfs have a status of two while inactives have a status
of zero. Chances are, if you're running this off hours you'll see a
single active vlf towards the very bottom of the result set.
DBCC LOGINFO
Step Three:
If this is the case, you need to BACKUP LOG WITH NO_LOG or
TRUNCATE_ONLY (synonymous) to move the vlf closer to the beginning of
the log file. This essentially trashes the transactions that are in the
log (they are no longer recoverable), so you'll want perform a BACKUP
DATABASE soon after.
BACKUP LOG DB_NAME WITH NO_LOG
BACKUP DATABASE DB_NAME TO DISK = '\\anywhere\db.bak'
Step Four:
As soon as you BACKUP NO_LOG (and the *highly* recommended BACKUP
DATABASE) you're free to shrink the log file.
DBCC SHRINKFILE(DB_LOG, 2)
Step Five:
Double check that everything went well (hasn't failed for me yet!):
DBCC SQLPERF(LOGSPACE)
Log free space question
Hi
I am using sql server 7.0 and one of DB is relatively
small. The size is about 2 MB however the log size is
about 30GB the problems is the log used space is 38 MB
but the log free space is about 37 GB so the whole backup
and restore takes some time as the toal og size is the
sum of these two.
I tried to shrink the file with truncate_only option but
cant get it to a small size
Can anyone helphttp://www.nigelrivett.net/TransactionLogFileGrows_1.html Log File issues
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
http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
considerations
Andrew J. Kelly SQL MVP
"Ap" <anonymous@.discussions.microsoft.com> wrote in message
news:154201c4f773$43971110$a501280a@.phx.gbl...
> Hi
> I am using sql server 7.0 and one of DB is relatively
> small. The size is about 2 MB however the log size is
> about 30GB the problems is the log used space is 38 MB
> but the log free space is about 37 GB so the whole backup
> and restore takes some time as the toal og size is the
> sum of these two.
> I tried to shrink the file with truncate_only option but
> cant get it to a small size
> Can anyone help|||Ap wrote:
> Hi
> I am using sql server 7.0 and one of DB is relatively
> small. The size is about 2 MB however the log size is
> about 30GB the problems is the log used space is 38 MB
> but the log free space is about 37 GB so the whole backup
> and restore takes some time as the toal og size is the
> sum of these two.
> I tried to shrink the file with truncate_only option but
> cant get it to a small size
> Can anyone help
Try DBCC SHRINKFILE (off hours, of course).
--
David Gugick
Imceda Software
www.imceda.com|||I have tried all through enterprise manager and dbcc
shrinkfile but does not help it is not the datalog of the
log file getting big but the unused space in the log
files which needs to be truncated to a smaller size any
ideads
>--Original Message--
>Hi
>I am using sql server 7.0 and one of DB is relatively
>small. The size is about 2 MB however the log size is
>about 30GB the problems is the log used space is 38 MB
>but the log free space is about 37 GB so the whole
backup
>and restore takes some time as the toal og size is the
>sum of these two.
>I tried to shrink the file with truncate_only option but
>cant get it to a small size
>Can anyone help
>.
>|||Did you read the links that were posted? Have you checked to see if you
have any open transactions with DBCC OPENTRAN()? Do you have Truncate Log
on Checkpoint turned on?
--
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:021b01c4f787$1d1fb780$a601280a@.phx.gbl...
>I have tried all through enterprise manager and dbcc
> shrinkfile but does not help it is not the datalog of the
> log file getting big but the unused space in the log
> files which needs to be truncated to a smaller size any
> ideads
>
>>--Original Message--
>>Hi
>>I am using sql server 7.0 and one of DB is relatively
>>small. The size is about 2 MB however the log size is
>>about 30GB the problems is the log used space is 38 MB
>>but the log free space is about 37 GB so the whole
> backup
>>and restore takes some time as the toal og size is the
>>sum of these two.
>>I tried to shrink the file with truncate_only option but
>>cant get it to a small size
>>Can anyone help
>>.|||Yes i checked both and read all the articles but
does not solve it
basically it is something like this
transaction log space 2553.55
used: 43.25
unused:2510.3
so I am worried as how to get that unused spcae from log
file back and none of the soln seem to work.
>--Original Message--
>Did you read the links that were posted? Have you
checked to see if you
>have any open transactions with DBCC OPENTRAN()? Do you
have Truncate Log
>on Checkpoint turned on?
>--
>Andrew J. Kelly SQL MVP
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:021b01c4f787$1d1fb780$a601280a@.phx.gbl...
>>I have tried all through enterprise manager and dbcc
>> shrinkfile but does not help it is not the datalog of
the
>> log file getting big but the unused space in the log
>> files which needs to be truncated to a smaller size any
>> ideads
>>
>>--Original Message--
>>Hi
>>I am using sql server 7.0 and one of DB is relatively
>>small. The size is about 2 MB however the log size is
>>about 30GB the problems is the log used space is 38 MB
>>but the log free space is about 37 GB so the whole
>> backup
>>and restore takes some time as the toal og size is the
>>sum of these two.
>>I tried to shrink the file with truncate_only option
but
>>cant get it to a small size
>>Can anyone help
>>.
>
>.
>|||Did you have a look with DBCC LOGINFO to see where the active VLF is? If it
is at the end you can not shrink it until you get it wrapped towards the
beginning. One of those links has a script for 7.0 to do this.
--
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:161601c4f79e$6f5a9f80$a501280a@.phx.gbl...
> Yes i checked both and read all the articles but
> does not solve it
> basically it is something like this
> transaction log space 2553.55
> used: 43.25
> unused:2510.3
> so I am worried as how to get that unused spcae from log
> file back and none of the soln seem to work.
>
>>--Original Message--
>>Did you read the links that were posted? Have you
> checked to see if you
>>have any open transactions with DBCC OPENTRAN()? Do you
> have Truncate Log
>>on Checkpoint turned on?
>>--
>>Andrew J. Kelly SQL MVP
>>
>><anonymous@.discussions.microsoft.com> wrote in message
>>news:021b01c4f787$1d1fb780$a601280a@.phx.gbl...
>>I have tried all through enterprise manager and dbcc
>> shrinkfile but does not help it is not the datalog of
> the
>> log file getting big but the unused space in the log
>> files which needs to be truncated to a smaller size any
>> ideads
>>
>>--Original Message--
>>Hi
>>I am using sql server 7.0 and one of DB is relatively
>>small. The size is about 2 MB however the log size is
>>about 30GB the problems is the log used space is 38 MB
>>but the log free space is about 37 GB so the whole
>> backup
>>and restore takes some time as the toal og size is the
>>sum of these two.
>>I tried to shrink the file with truncate_only option
> but
>>cant get it to a small size
>>Can anyone help
>>.
>>
>>.|||I see the same thing all the time and the solutions provided by David
and Andrew work beautifully (I've had logs up to 6GB and this process
has worked as advertised). Order is everything, are you sure you're
running the them in the appropriate order? (Try sticking with isql or
isqlw. Enterprise Manager has it's place, however, for general
maintenance, it's often easier to issue a statement directly.)
Step One:
This will give you an idea of your usage and should provide the
information you'd posted earlier
DBCC SQLPERF(LOGSPACE)
Step Two:
If your logs are huge, check to see where the current active vlf is
located. Active vlfs have a status of two while inactives have a status
of zero. Chances are, if you're running this off hours you'll see a
single active vlf towards the very bottom of the result set.
DBCC LOGINFO
Step Three:
If this is the case, you need to BACKUP LOG WITH NO_LOG or
TRUNCATE_ONLY (synonymous) to move the vlf closer to the beginning of
the log file. This essentially trashes the transactions that are in the
log (they are no longer recoverable), so you'll want perform a BACKUP
DATABASE soon after.
BACKUP LOG DB_NAME WITH NO_LOG
BACKUP DATABASE DB_NAME TO DISK = '\\anywhere\db.bak'
Step Four:
As soon as you BACKUP NO_LOG (and the *highly* recommended BACKUP
DATABASE) you're free to shrink the log file.
DBCC SHRINKFILE(DB_LOG, 2)
Step Five:
Double check that everything went well (hasn't failed for me yet!):
DBCC SQLPERF(LOGSPACE)
I am using sql server 7.0 and one of DB is relatively
small. The size is about 2 MB however the log size is
about 30GB the problems is the log used space is 38 MB
but the log free space is about 37 GB so the whole backup
and restore takes some time as the toal og size is the
sum of these two.
I tried to shrink the file with truncate_only option but
cant get it to a small size
Can anyone helphttp://www.nigelrivett.net/TransactionLogFileGrows_1.html Log File issues
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
http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
considerations
Andrew J. Kelly SQL MVP
"Ap" <anonymous@.discussions.microsoft.com> wrote in message
news:154201c4f773$43971110$a501280a@.phx.gbl...
> Hi
> I am using sql server 7.0 and one of DB is relatively
> small. The size is about 2 MB however the log size is
> about 30GB the problems is the log used space is 38 MB
> but the log free space is about 37 GB so the whole backup
> and restore takes some time as the toal og size is the
> sum of these two.
> I tried to shrink the file with truncate_only option but
> cant get it to a small size
> Can anyone help|||Ap wrote:
> Hi
> I am using sql server 7.0 and one of DB is relatively
> small. The size is about 2 MB however the log size is
> about 30GB the problems is the log used space is 38 MB
> but the log free space is about 37 GB so the whole backup
> and restore takes some time as the toal og size is the
> sum of these two.
> I tried to shrink the file with truncate_only option but
> cant get it to a small size
> Can anyone help
Try DBCC SHRINKFILE (off hours, of course).
--
David Gugick
Imceda Software
www.imceda.com|||I have tried all through enterprise manager and dbcc
shrinkfile but does not help it is not the datalog of the
log file getting big but the unused space in the log
files which needs to be truncated to a smaller size any
ideads
>--Original Message--
>Hi
>I am using sql server 7.0 and one of DB is relatively
>small. The size is about 2 MB however the log size is
>about 30GB the problems is the log used space is 38 MB
>but the log free space is about 37 GB so the whole
backup
>and restore takes some time as the toal og size is the
>sum of these two.
>I tried to shrink the file with truncate_only option but
>cant get it to a small size
>Can anyone help
>.
>|||Did you read the links that were posted? Have you checked to see if you
have any open transactions with DBCC OPENTRAN()? Do you have Truncate Log
on Checkpoint turned on?
--
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:021b01c4f787$1d1fb780$a601280a@.phx.gbl...
>I have tried all through enterprise manager and dbcc
> shrinkfile but does not help it is not the datalog of the
> log file getting big but the unused space in the log
> files which needs to be truncated to a smaller size any
> ideads
>
>>--Original Message--
>>Hi
>>I am using sql server 7.0 and one of DB is relatively
>>small. The size is about 2 MB however the log size is
>>about 30GB the problems is the log used space is 38 MB
>>but the log free space is about 37 GB so the whole
> backup
>>and restore takes some time as the toal og size is the
>>sum of these two.
>>I tried to shrink the file with truncate_only option but
>>cant get it to a small size
>>Can anyone help
>>.|||Yes i checked both and read all the articles but
does not solve it
basically it is something like this
transaction log space 2553.55
used: 43.25
unused:2510.3
so I am worried as how to get that unused spcae from log
file back and none of the soln seem to work.
>--Original Message--
>Did you read the links that were posted? Have you
checked to see if you
>have any open transactions with DBCC OPENTRAN()? Do you
have Truncate Log
>on Checkpoint turned on?
>--
>Andrew J. Kelly SQL MVP
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:021b01c4f787$1d1fb780$a601280a@.phx.gbl...
>>I have tried all through enterprise manager and dbcc
>> shrinkfile but does not help it is not the datalog of
the
>> log file getting big but the unused space in the log
>> files which needs to be truncated to a smaller size any
>> ideads
>>
>>--Original Message--
>>Hi
>>I am using sql server 7.0 and one of DB is relatively
>>small. The size is about 2 MB however the log size is
>>about 30GB the problems is the log used space is 38 MB
>>but the log free space is about 37 GB so the whole
>> backup
>>and restore takes some time as the toal og size is the
>>sum of these two.
>>I tried to shrink the file with truncate_only option
but
>>cant get it to a small size
>>Can anyone help
>>.
>
>.
>|||Did you have a look with DBCC LOGINFO to see where the active VLF is? If it
is at the end you can not shrink it until you get it wrapped towards the
beginning. One of those links has a script for 7.0 to do this.
--
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:161601c4f79e$6f5a9f80$a501280a@.phx.gbl...
> Yes i checked both and read all the articles but
> does not solve it
> basically it is something like this
> transaction log space 2553.55
> used: 43.25
> unused:2510.3
> so I am worried as how to get that unused spcae from log
> file back and none of the soln seem to work.
>
>>--Original Message--
>>Did you read the links that were posted? Have you
> checked to see if you
>>have any open transactions with DBCC OPENTRAN()? Do you
> have Truncate Log
>>on Checkpoint turned on?
>>--
>>Andrew J. Kelly SQL MVP
>>
>><anonymous@.discussions.microsoft.com> wrote in message
>>news:021b01c4f787$1d1fb780$a601280a@.phx.gbl...
>>I have tried all through enterprise manager and dbcc
>> shrinkfile but does not help it is not the datalog of
> the
>> log file getting big but the unused space in the log
>> files which needs to be truncated to a smaller size any
>> ideads
>>
>>--Original Message--
>>Hi
>>I am using sql server 7.0 and one of DB is relatively
>>small. The size is about 2 MB however the log size is
>>about 30GB the problems is the log used space is 38 MB
>>but the log free space is about 37 GB so the whole
>> backup
>>and restore takes some time as the toal og size is the
>>sum of these two.
>>I tried to shrink the file with truncate_only option
> but
>>cant get it to a small size
>>Can anyone help
>>.
>>
>>.|||I see the same thing all the time and the solutions provided by David
and Andrew work beautifully (I've had logs up to 6GB and this process
has worked as advertised). Order is everything, are you sure you're
running the them in the appropriate order? (Try sticking with isql or
isqlw. Enterprise Manager has it's place, however, for general
maintenance, it's often easier to issue a statement directly.)
Step One:
This will give you an idea of your usage and should provide the
information you'd posted earlier
DBCC SQLPERF(LOGSPACE)
Step Two:
If your logs are huge, check to see where the current active vlf is
located. Active vlfs have a status of two while inactives have a status
of zero. Chances are, if you're running this off hours you'll see a
single active vlf towards the very bottom of the result set.
DBCC LOGINFO
Step Three:
If this is the case, you need to BACKUP LOG WITH NO_LOG or
TRUNCATE_ONLY (synonymous) to move the vlf closer to the beginning of
the log file. This essentially trashes the transactions that are in the
log (they are no longer recoverable), so you'll want perform a BACKUP
DATABASE soon after.
BACKUP LOG DB_NAME WITH NO_LOG
BACKUP DATABASE DB_NAME TO DISK = '\\anywhere\db.bak'
Step Four:
As soon as you BACKUP NO_LOG (and the *highly* recommended BACKUP
DATABASE) you're free to shrink the log file.
DBCC SHRINKFILE(DB_LOG, 2)
Step Five:
Double check that everything went well (hasn't failed for me yet!):
DBCC SQLPERF(LOGSPACE)
Log free space question
Hi
I am using sql server 7.0 and one of DB is relatively
small. The size is about 2 MB however the log size is
about 30GB the problems is the log used space is 38 MB
but the log free space is about 37 GB so the whole backup
and restore takes some time as the toal og size is the
sum of these two.
I tried to shrink the file with truncate_only option but
cant get it to a small size
Can anyone help
http://www.nigelrivett.net/Transacti...leGrows_1.html Log File issues
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
http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
considerations
Andrew J. Kelly SQL MVP
"Ap" <anonymous@.discussions.microsoft.com> wrote in message
news:154201c4f773$43971110$a501280a@.phx.gbl...
> Hi
> I am using sql server 7.0 and one of DB is relatively
> small. The size is about 2 MB however the log size is
> about 30GB the problems is the log used space is 38 MB
> but the log free space is about 37 GB so the whole backup
> and restore takes some time as the toal og size is the
> sum of these two.
> I tried to shrink the file with truncate_only option but
> cant get it to a small size
> Can anyone help
|||Ap wrote:
> Hi
> I am using sql server 7.0 and one of DB is relatively
> small. The size is about 2 MB however the log size is
> about 30GB the problems is the log used space is 38 MB
> but the log free space is about 37 GB so the whole backup
> and restore takes some time as the toal og size is the
> sum of these two.
> I tried to shrink the file with truncate_only option but
> cant get it to a small size
> Can anyone help
Try DBCC SHRINKFILE (off hours, of course).
David Gugick
Imceda Software
www.imceda.com
|||I see the same thing all the time and the solutions provided by David
and Andrew work beautifully (I've had logs up to 6GB and this process
has worked as advertised). Order is everything, are you sure you're
running the them in the appropriate order? (Try sticking with isql or
isqlw. Enterprise Manager has it's place, however, for general
maintenance, it's often easier to issue a statement directly.)
Step One:
This will give you an idea of your usage and should provide the
information you'd posted earlier
DBCC SQLPERF(LOGSPACE)
Step Two:
If your logs are huge, check to see where the current active vlf is
located. Active vlfs have a status of two while inactives have a status
of zero. Chances are, if you're running this off hours you'll see a
single active vlf towards the very bottom of the result set.
DBCC LOGINFO
Step Three:
If this is the case, you need to BACKUP LOG WITH NO_LOG or
TRUNCATE_ONLY (synonymous) to move the vlf closer to the beginning of
the log file. This essentially trashes the transactions that are in the
log (they are no longer recoverable), so you'll want perform a BACKUP
DATABASE soon after.
BACKUP LOG DB_NAME WITH NO_LOG
BACKUP DATABASE DB_NAME TO DISK = '\\anywhere\db.bak'
Step Four:
As soon as you BACKUP NO_LOG (and the *highly* recommended BACKUP
DATABASE) you're free to shrink the log file.
DBCC SHRINKFILE(DB_LOG, 2)
Step Five:
Double check that everything went well (hasn't failed for me yet!):
DBCC SQLPERF(LOGSPACE)
I am using sql server 7.0 and one of DB is relatively
small. The size is about 2 MB however the log size is
about 30GB the problems is the log used space is 38 MB
but the log free space is about 37 GB so the whole backup
and restore takes some time as the toal og size is the
sum of these two.
I tried to shrink the file with truncate_only option but
cant get it to a small size
Can anyone help
http://www.nigelrivett.net/Transacti...leGrows_1.html Log File issues
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
http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
considerations
Andrew J. Kelly SQL MVP
"Ap" <anonymous@.discussions.microsoft.com> wrote in message
news:154201c4f773$43971110$a501280a@.phx.gbl...
> Hi
> I am using sql server 7.0 and one of DB is relatively
> small. The size is about 2 MB however the log size is
> about 30GB the problems is the log used space is 38 MB
> but the log free space is about 37 GB so the whole backup
> and restore takes some time as the toal og size is the
> sum of these two.
> I tried to shrink the file with truncate_only option but
> cant get it to a small size
> Can anyone help
|||Ap wrote:
> Hi
> I am using sql server 7.0 and one of DB is relatively
> small. The size is about 2 MB however the log size is
> about 30GB the problems is the log used space is 38 MB
> but the log free space is about 37 GB so the whole backup
> and restore takes some time as the toal og size is the
> sum of these two.
> I tried to shrink the file with truncate_only option but
> cant get it to a small size
> Can anyone help
Try DBCC SHRINKFILE (off hours, of course).
David Gugick
Imceda Software
www.imceda.com
|||I see the same thing all the time and the solutions provided by David
and Andrew work beautifully (I've had logs up to 6GB and this process
has worked as advertised). Order is everything, are you sure you're
running the them in the appropriate order? (Try sticking with isql or
isqlw. Enterprise Manager has it's place, however, for general
maintenance, it's often easier to issue a statement directly.)
Step One:
This will give you an idea of your usage and should provide the
information you'd posted earlier
DBCC SQLPERF(LOGSPACE)
Step Two:
If your logs are huge, check to see where the current active vlf is
located. Active vlfs have a status of two while inactives have a status
of zero. Chances are, if you're running this off hours you'll see a
single active vlf towards the very bottom of the result set.
DBCC LOGINFO
Step Three:
If this is the case, you need to BACKUP LOG WITH NO_LOG or
TRUNCATE_ONLY (synonymous) to move the vlf closer to the beginning of
the log file. This essentially trashes the transactions that are in the
log (they are no longer recoverable), so you'll want perform a BACKUP
DATABASE soon after.
BACKUP LOG DB_NAME WITH NO_LOG
BACKUP DATABASE DB_NAME TO DISK = '\\anywhere\db.bak'
Step Four:
As soon as you BACKUP NO_LOG (and the *highly* recommended BACKUP
DATABASE) you're free to shrink the log file.
DBCC SHRINKFILE(DB_LOG, 2)
Step Five:
Double check that everything went well (hasn't failed for me yet!):
DBCC SQLPERF(LOGSPACE)
Monday, March 26, 2012
log files
hi
when size of my lof file become full than it shows the error....so for this i manully delete the content of my log file...than it does not show the error....can anybody tell me any better way to avoid manual deletion...
thanx a lot
Right click the database go to Tasks->Shrink -> Files, select the log option and give a very small size.
after that run this command,
BACKUP LOG <db name> with TRUNCATE_ONLY
your transactional log size will be reduced in this manner.
|||hi thanx for reply....u mean i have to do what u said each time ...here also we are doing manual deletion....any automatic way to do this ?|||You can by developing a SSIS package with a Shrink Database task, call it using a SQL Server agent job and then schedule it as your need be.|||after right click it is showing shrink option disabled.why?|||The options I had said is for SSMS 2005. What are you using ?|||sqlserver 2000|||
Check if this helps....
http://support.microsoft.com/default.aspx/kb/272318
|||Frequently backup you database and log.This is the way you can keep the log from growing.
LOG FILES
I have a problem with one of the SQL Logs where the LOG
file has reached 5GB in size the DB is only around 400MB,
I have also tried running the command:
dbcc shrinkfile (database, 256)
I have tried altering the number to reflect the size but
alway get the following:
Cannot shrink log file 2 (System21MD_Log) because all
logical log files are in use.
(1 row(s) affected)
also under grids I get:
DbId Filed Current Min Used estimated
size Size Size pages.
10 2 639912 128 639912 128
could anyone advise howto shrink this logfile or truncate
it, please. Any herlp is much appriciated.Adam
Did you try to backup the log file ?
For more details please refer to BOL
"Adam" <anonymous@.discussions.microsoft.com> wrote in message
news:0b2b01c3d52a$07340be0$a301280a@.phx.gbl...
> I have a problem with one of the SQL Logs where the LOG
> file has reached 5GB in size the DB is only around 400MB,
> I have also tried running the command:
> dbcc shrinkfile (database, 256)
> I have tried altering the number to reflect the size but
> alway get the following:
> Cannot shrink log file 2 (System21MD_Log) because all
> logical log files are in use.
> (1 row(s) affected)
>
> also under grids I get:
> DbId Filed Current Min Used estimated
> size Size Size pages.
> 10 2 639912 128 639912 128
>
> could anyone advise howto shrink this logfile or truncate
> it, please. Any herlp is much appriciated.|||the SQL is backed up every night using the veritas addon
for SQL and has previously kept the size down.
>--Original Message--
>Adam
>Did you try to backup the log file ?
>For more details please refer to BOL
>
>"Adam" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0b2b01c3d52a$07340be0$a301280a@.phx.gbl...
>> I have a problem with one of the SQL Logs where the LOG
>> file has reached 5GB in size the DB is only around
400MB,
>> I have also tried running the command:
>> dbcc shrinkfile (database, 256)
>> I have tried altering the number to reflect the size but
>> alway get the following:
>> Cannot shrink log file 2 (System21MD_Log) because all
>> logical log files are in use.
>> (1 row(s) affected)
>>
>> also under grids I get:
>> DbId Filed Current Min Used estimated
>> size Size Size pages.
>> 10 2 639912 128 639912 128
>>
>> could anyone advise howto shrink this logfile or
truncate
>> it, please. Any herlp is much appriciated.
>
>.
>|||make usre you have backed up the log.
then retry the dbcc shrinkfile, until you get some love... It will not work
until all active transactions have moved off of the logical file... Maybe
try this every 15 minutes or so...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"Adam" <anonymous@.discussions.microsoft.com> wrote in message
news:0b2b01c3d52a$07340be0$a301280a@.phx.gbl...
> I have a problem with one of the SQL Logs where the LOG
> file has reached 5GB in size the DB is only around 400MB,
> I have also tried running the command:
> dbcc shrinkfile (database, 256)
> I have tried altering the number to reflect the size but
> alway get the following:
> Cannot shrink log file 2 (System21MD_Log) because all
> logical log files are in use.
> (1 row(s) affected)
>
> also under grids I get:
> DbId Filed Current Min Used estimated
> size Size Size pages.
> 10 2 639912 128 639912 128
>
> could anyone advise howto shrink this logfile or truncate
> it, please. Any herlp is much appriciated.|||This is a multi-part message in MIME format.
--=_NextPart_000_0048_01C3D534.884AB940
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Have you tried backing up the transaction log? This should truncate it.
Also, do you have any long running transactions? These inflate the transaction log, & if they are running at the time of =backup, they will not be truncated.
-- Cheers,
James Goodman MCSE, MCDBA
http://www.angelfire.com/sports/f1pictures
--=_NextPart_000_0048_01C3D534.884AB940
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Have you tried backing up the =transaction log? This should truncate it.
Also, do you have any long running =transactions? These inflate the transaction log, =& if they are running at the time of backup, they will not be =truncated.
-- Cheers,
James Goodman MCSE, MCDBAhttp://www.angelfire.com/sports/f1pictures">http://www.angelfire.=com/sports/f1pictures
--=_NextPart_000_0048_01C3D534.884AB940--
file has reached 5GB in size the DB is only around 400MB,
I have also tried running the command:
dbcc shrinkfile (database, 256)
I have tried altering the number to reflect the size but
alway get the following:
Cannot shrink log file 2 (System21MD_Log) because all
logical log files are in use.
(1 row(s) affected)
also under grids I get:
DbId Filed Current Min Used estimated
size Size Size pages.
10 2 639912 128 639912 128
could anyone advise howto shrink this logfile or truncate
it, please. Any herlp is much appriciated.Adam
Did you try to backup the log file ?
For more details please refer to BOL
"Adam" <anonymous@.discussions.microsoft.com> wrote in message
news:0b2b01c3d52a$07340be0$a301280a@.phx.gbl...
> I have a problem with one of the SQL Logs where the LOG
> file has reached 5GB in size the DB is only around 400MB,
> I have also tried running the command:
> dbcc shrinkfile (database, 256)
> I have tried altering the number to reflect the size but
> alway get the following:
> Cannot shrink log file 2 (System21MD_Log) because all
> logical log files are in use.
> (1 row(s) affected)
>
> also under grids I get:
> DbId Filed Current Min Used estimated
> size Size Size pages.
> 10 2 639912 128 639912 128
>
> could anyone advise howto shrink this logfile or truncate
> it, please. Any herlp is much appriciated.|||the SQL is backed up every night using the veritas addon
for SQL and has previously kept the size down.
>--Original Message--
>Adam
>Did you try to backup the log file ?
>For more details please refer to BOL
>
>"Adam" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0b2b01c3d52a$07340be0$a301280a@.phx.gbl...
>> I have a problem with one of the SQL Logs where the LOG
>> file has reached 5GB in size the DB is only around
400MB,
>> I have also tried running the command:
>> dbcc shrinkfile (database, 256)
>> I have tried altering the number to reflect the size but
>> alway get the following:
>> Cannot shrink log file 2 (System21MD_Log) because all
>> logical log files are in use.
>> (1 row(s) affected)
>>
>> also under grids I get:
>> DbId Filed Current Min Used estimated
>> size Size Size pages.
>> 10 2 639912 128 639912 128
>>
>> could anyone advise howto shrink this logfile or
truncate
>> it, please. Any herlp is much appriciated.
>
>.
>|||make usre you have backed up the log.
then retry the dbcc shrinkfile, until you get some love... It will not work
until all active transactions have moved off of the logical file... Maybe
try this every 15 minutes or so...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"Adam" <anonymous@.discussions.microsoft.com> wrote in message
news:0b2b01c3d52a$07340be0$a301280a@.phx.gbl...
> I have a problem with one of the SQL Logs where the LOG
> file has reached 5GB in size the DB is only around 400MB,
> I have also tried running the command:
> dbcc shrinkfile (database, 256)
> I have tried altering the number to reflect the size but
> alway get the following:
> Cannot shrink log file 2 (System21MD_Log) because all
> logical log files are in use.
> (1 row(s) affected)
>
> also under grids I get:
> DbId Filed Current Min Used estimated
> size Size Size pages.
> 10 2 639912 128 639912 128
>
> could anyone advise howto shrink this logfile or truncate
> it, please. Any herlp is much appriciated.|||This is a multi-part message in MIME format.
--=_NextPart_000_0048_01C3D534.884AB940
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Have you tried backing up the transaction log? This should truncate it.
Also, do you have any long running transactions? These inflate the transaction log, & if they are running at the time of =backup, they will not be truncated.
-- Cheers,
James Goodman MCSE, MCDBA
http://www.angelfire.com/sports/f1pictures
--=_NextPart_000_0048_01C3D534.884AB940
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Have you tried backing up the =transaction log? This should truncate it.
Also, do you have any long running =transactions? These inflate the transaction log, =& if they are running at the time of backup, they will not be =truncated.
-- Cheers,
James Goodman MCSE, MCDBAhttp://www.angelfire.com/sports/f1pictures">http://www.angelfire.=com/sports/f1pictures
--=_NextPart_000_0048_01C3D534.884AB940--
Log file too big
I have a transaction log file too big (about 13GB). I think the one who make
it first time has declared a fixed size. Haw can I make it little ?
First of all fix up the backup of the log so that it doesn't keep growing -
either has to be backed up regularly or the database should be in Simple
Recovery Mode (mainly for development/test databases).
From Enterprise manager you can then right click on the database name,
select All Tasks-Shrink Database. You can then click the Files button and
change the file to the log file and shrink it as much as possible (leave
some room for growth). The shrink may not happen immediately.
Mary
"Sorin Sandu" <ssandu@.yahoo.com.del> wrote in message
news:%23njEtDrqEHA.2900@.TK2MSFTNGP12.phx.gbl...
>I have a transaction log file too big (about 13GB). I think the one who
>make it first time has declared a fixed size. Haw can I make it little ?
>
|||I've tried to shrink from enterprise manager but the minimum size is the
same like log files 13GB and I can't modify it.
Whith query analizer DBCC SHRINKFILE (LOGFILE, 10) i get
"9 2 1693472 128 1693472 128 "
I backup up full this database every night.
What should I do ?
"Mary Bray" <no@.spam.com> a scris n mesajul de
tiri:epc6%23ZrqEHA.3748@.TK2MSFTNGP09.phx.gbl...
> First of all fix up the backup of the log so that it doesn't keep
> growing - either has to be backed up regularly or the database should be
> in Simple Recovery Mode (mainly for development/test databases).
> From Enterprise manager you can then right click on the database name,
> select All Tasks-Shrink Database. You can then click the Files button and
> change the file to the log file and shrink it as much as possible (leave
> some room for growth). The shrink may not happen immediately.
> Mary
> "Sorin Sandu" <ssandu@.yahoo.com.del> wrote in message
> news:%23njEtDrqEHA.2900@.TK2MSFTNGP12.phx.gbl...
>
|||The log file is not emptied when you perform a database backup, only for a transaction log backup.
If you don't perform transaction log backups, set the database in simple recovery mode.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sorin Sandu" <ssandu@.yahoo.com.del> wrote in message news:eZdwGItqEHA.2696@.TK2MSFTNGP15.phx.gbl...
> I've tried to shrink from enterprise manager but the minimum size is the same like log files 13GB
> and I can't modify it.
> Whith query analizer DBCC SHRINKFILE (LOGFILE, 10) i get
> "9 2 1693472 128 1693472 128 "
> I backup up full this database every night.
> What should I do ?
> "Mary Bray" <no@.spam.com> a scris n mesajul de tiri:epc6%23ZrqEHA.3748@.TK2MSFTNGP09.phx.gbl...
>
it first time has declared a fixed size. Haw can I make it little ?
First of all fix up the backup of the log so that it doesn't keep growing -
either has to be backed up regularly or the database should be in Simple
Recovery Mode (mainly for development/test databases).
From Enterprise manager you can then right click on the database name,
select All Tasks-Shrink Database. You can then click the Files button and
change the file to the log file and shrink it as much as possible (leave
some room for growth). The shrink may not happen immediately.
Mary
"Sorin Sandu" <ssandu@.yahoo.com.del> wrote in message
news:%23njEtDrqEHA.2900@.TK2MSFTNGP12.phx.gbl...
>I have a transaction log file too big (about 13GB). I think the one who
>make it first time has declared a fixed size. Haw can I make it little ?
>
|||I've tried to shrink from enterprise manager but the minimum size is the
same like log files 13GB and I can't modify it.
Whith query analizer DBCC SHRINKFILE (LOGFILE, 10) i get
"9 2 1693472 128 1693472 128 "
I backup up full this database every night.
What should I do ?
"Mary Bray" <no@.spam.com> a scris n mesajul de
tiri:epc6%23ZrqEHA.3748@.TK2MSFTNGP09.phx.gbl...
> First of all fix up the backup of the log so that it doesn't keep
> growing - either has to be backed up regularly or the database should be
> in Simple Recovery Mode (mainly for development/test databases).
> From Enterprise manager you can then right click on the database name,
> select All Tasks-Shrink Database. You can then click the Files button and
> change the file to the log file and shrink it as much as possible (leave
> some room for growth). The shrink may not happen immediately.
> Mary
> "Sorin Sandu" <ssandu@.yahoo.com.del> wrote in message
> news:%23njEtDrqEHA.2900@.TK2MSFTNGP12.phx.gbl...
>
|||The log file is not emptied when you perform a database backup, only for a transaction log backup.
If you don't perform transaction log backups, set the database in simple recovery mode.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sorin Sandu" <ssandu@.yahoo.com.del> wrote in message news:eZdwGItqEHA.2696@.TK2MSFTNGP15.phx.gbl...
> I've tried to shrink from enterprise manager but the minimum size is the same like log files 13GB
> and I can't modify it.
> Whith query analizer DBCC SHRINKFILE (LOGFILE, 10) i get
> "9 2 1693472 128 1693472 128 "
> I backup up full this database every night.
> What should I do ?
> "Mary Bray" <no@.spam.com> a scris n mesajul de tiri:epc6%23ZrqEHA.3748@.TK2MSFTNGP09.phx.gbl...
>
Friday, March 23, 2012
Log File too big
The size of the .MDF is 175,000 Kb while that of the .LDF is 2,377,000 Kb. What should I do to truncate the Log File? Pls advise.before executing this code read BOL, dbcc shrinkfile
backup DATABASE yourdatabasename to disk='c:\yourdatabasename .bak'
go
dbcc shrinkfile (yourdatabase_log,size,truncateonly)
go
--to get log file name--
select * from sysfiles|||1.Please change the db options to Simple recovery Model.
2.Use the DBCC Shrinkfile ('FileNAme','SizeinMB) e.g DBCC Shrinkfile ('Myfile',100)
Thankssql
backup DATABASE yourdatabasename to disk='c:\yourdatabasename .bak'
go
dbcc shrinkfile (yourdatabase_log,size,truncateonly)
go
--to get log file name--
select * from sysfiles|||1.Please change the db options to Simple recovery Model.
2.Use the DBCC Shrinkfile ('FileNAme','SizeinMB) e.g DBCC Shrinkfile ('Myfile',100)
Thankssql
Log file too big
I have a transaction log file too big (about 13GB). I think the one who make
it first time has declared a fixed size. Haw can I make it little ?First of all fix up the backup of the log so that it doesn't keep growing -
either has to be backed up regularly or the database should be in Simple
Recovery Mode (mainly for development/test databases).
From Enterprise manager you can then right click on the database name,
select All Tasks-Shrink Database. You can then click the Files button and
change the file to the log file and shrink it as much as possible (leave
some room for growth). The shrink may not happen immediately.
Mary
"Sorin Sandu" <ssandu@.yahoo.com.del> wrote in message
news:%23njEtDrqEHA.2900@.TK2MSFTNGP12.phx.gbl...
>I have a transaction log file too big (about 13GB). I think the one who
>make it first time has declared a fixed size. Haw can I make it little ?
>|||I've tried to shrink from enterprise manager but the minimum size is the
same like log files 13GB and I can't modify it.
Whith query analizer DBCC SHRINKFILE (LOGFILE, 10) i get
"9 2 1693472 128 1693472 128 "
I backup up full this database every night.
What should I do ?
"Mary Bray" <no@.spam.com> a scris în mesajul de
ºtiri:epc6%23ZrqEHA.3748@.TK2MSFTNGP09.phx.gbl...
> First of all fix up the backup of the log so that it doesn't keep
> growing - either has to be backed up regularly or the database should be
> in Simple Recovery Mode (mainly for development/test databases).
> From Enterprise manager you can then right click on the database name,
> select All Tasks-Shrink Database. You can then click the Files button and
> change the file to the log file and shrink it as much as possible (leave
> some room for growth). The shrink may not happen immediately.
> Mary
> "Sorin Sandu" <ssandu@.yahoo.com.del> wrote in message
> news:%23njEtDrqEHA.2900@.TK2MSFTNGP12.phx.gbl...
>>I have a transaction log file too big (about 13GB). I think the one who
>>make it first time has declared a fixed size. Haw can I make it little ?
>|||The log file is not emptied when you perform a database backup, only for a transaction log backup.
If you don't perform transaction log backups, set the database in simple recovery mode.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sorin Sandu" <ssandu@.yahoo.com.del> wrote in message news:eZdwGItqEHA.2696@.TK2MSFTNGP15.phx.gbl...
> I've tried to shrink from enterprise manager but the minimum size is the same like log files 13GB
> and I can't modify it.
> Whith query analizer DBCC SHRINKFILE (LOGFILE, 10) i get
> "9 2 1693472 128 1693472 128 "
> I backup up full this database every night.
> What should I do ?
> "Mary Bray" <no@.spam.com> a scris în mesajul de ºtiri:epc6%23ZrqEHA.3748@.TK2MSFTNGP09.phx.gbl...
>> First of all fix up the backup of the log so that it doesn't keep growing - either has to be
>> backed up regularly or the database should be in Simple Recovery Mode (mainly for
>> development/test databases).
>> From Enterprise manager you can then right click on the database name, select All Tasks-Shrink
>> Database. You can then click the Files button and change the file to the log file and shrink it
>> as much as possible (leave some room for growth). The shrink may not happen immediately.
>> Mary
>> "Sorin Sandu" <ssandu@.yahoo.com.del> wrote in message
>> news:%23njEtDrqEHA.2900@.TK2MSFTNGP12.phx.gbl...
>>I have a transaction log file too big (about 13GB). I think the one who make it first time has
>>declared a fixed size. Haw can I make it little ?
>>
>
it first time has declared a fixed size. Haw can I make it little ?First of all fix up the backup of the log so that it doesn't keep growing -
either has to be backed up regularly or the database should be in Simple
Recovery Mode (mainly for development/test databases).
From Enterprise manager you can then right click on the database name,
select All Tasks-Shrink Database. You can then click the Files button and
change the file to the log file and shrink it as much as possible (leave
some room for growth). The shrink may not happen immediately.
Mary
"Sorin Sandu" <ssandu@.yahoo.com.del> wrote in message
news:%23njEtDrqEHA.2900@.TK2MSFTNGP12.phx.gbl...
>I have a transaction log file too big (about 13GB). I think the one who
>make it first time has declared a fixed size. Haw can I make it little ?
>|||I've tried to shrink from enterprise manager but the minimum size is the
same like log files 13GB and I can't modify it.
Whith query analizer DBCC SHRINKFILE (LOGFILE, 10) i get
"9 2 1693472 128 1693472 128 "
I backup up full this database every night.
What should I do ?
"Mary Bray" <no@.spam.com> a scris în mesajul de
ºtiri:epc6%23ZrqEHA.3748@.TK2MSFTNGP09.phx.gbl...
> First of all fix up the backup of the log so that it doesn't keep
> growing - either has to be backed up regularly or the database should be
> in Simple Recovery Mode (mainly for development/test databases).
> From Enterprise manager you can then right click on the database name,
> select All Tasks-Shrink Database. You can then click the Files button and
> change the file to the log file and shrink it as much as possible (leave
> some room for growth). The shrink may not happen immediately.
> Mary
> "Sorin Sandu" <ssandu@.yahoo.com.del> wrote in message
> news:%23njEtDrqEHA.2900@.TK2MSFTNGP12.phx.gbl...
>>I have a transaction log file too big (about 13GB). I think the one who
>>make it first time has declared a fixed size. Haw can I make it little ?
>|||The log file is not emptied when you perform a database backup, only for a transaction log backup.
If you don't perform transaction log backups, set the database in simple recovery mode.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sorin Sandu" <ssandu@.yahoo.com.del> wrote in message news:eZdwGItqEHA.2696@.TK2MSFTNGP15.phx.gbl...
> I've tried to shrink from enterprise manager but the minimum size is the same like log files 13GB
> and I can't modify it.
> Whith query analizer DBCC SHRINKFILE (LOGFILE, 10) i get
> "9 2 1693472 128 1693472 128 "
> I backup up full this database every night.
> What should I do ?
> "Mary Bray" <no@.spam.com> a scris în mesajul de ºtiri:epc6%23ZrqEHA.3748@.TK2MSFTNGP09.phx.gbl...
>> First of all fix up the backup of the log so that it doesn't keep growing - either has to be
>> backed up regularly or the database should be in Simple Recovery Mode (mainly for
>> development/test databases).
>> From Enterprise manager you can then right click on the database name, select All Tasks-Shrink
>> Database. You can then click the Files button and change the file to the log file and shrink it
>> as much as possible (leave some room for growth). The shrink may not happen immediately.
>> Mary
>> "Sorin Sandu" <ssandu@.yahoo.com.del> wrote in message
>> news:%23njEtDrqEHA.2900@.TK2MSFTNGP12.phx.gbl...
>>I have a transaction log file too big (about 13GB). I think the one who make it first time has
>>declared a fixed size. Haw can I make it little ?
>>
>
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
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
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 vs. Data Size ?
I am creating maintenance plans in SQL Server 2005 to manage the backups of
a
database. The Databasename_Data.MDF file is approx. 234 MB in size. The
Databasename_Log.LDF is approx. 3.5 GB. Does this ratio of log size to data
size
sound "normal" ? If not, any thoughts or advice ?
If I want to try shrinking the log file, do I need to use a combination of
'BACKUP LOG' and 'DBCC SHRINKFILE' commands ? Or is there a more
automated way using maintenance plans?
Thanks in advance!
TomTom Glasser wrote:
> I am creating maintenance plans in SQL Server 2005 to manage the backups o
f a
> database. The Databasename_Data.MDF file is approx. 234 MB in size. The
> Databasename_Log.LDF is approx. 3.5 GB. Does this ratio of log size to da
ta
> size
> sound "normal" ? If not, any thoughts or advice ?
> If I want to try shrinking the log file, do I need to use a combination of
> 'BACKUP LOG' and 'DBCC SHRINKFILE' commands ? Or is there a more
> automated way using maintenance plans?
> Thanks in advance!
> Tom
Hi Tom
It sounds like your logfile is much bigger than it needs to be, but it
could also be right. If you are running in FULL recovery mode and you're
not backing up your logfile, it will just grow and grow and grow. When
you backup the logfile, the backup commands will truncate you log which
means that old transactions can be overwritten. In this way the space
will be re-used so the file doesn't need to grow.
If you find out that you have to shrink the logfile, you'll have to run
DBCC SHRINKFILE.
Be carefull about shrinking the log though. A logfile should only be
shrunk in cases where there has been extraordinary activity in the
database which has made the logfile grow. This could e.g. be if you have
imported or deleted a lot of data as a "one time" operation. You
shouldn't shrink a logfile on a regular basis - that will only lead to
poor performance because it will have to grow again, and the file will
most likely be fragmented as well.
Try to have a look at -
http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
Regards
Steen Schlüter Persson
Database Administrator / System Administratorsql
a
database. The Databasename_Data.MDF file is approx. 234 MB in size. The
Databasename_Log.LDF is approx. 3.5 GB. Does this ratio of log size to data
size
sound "normal" ? If not, any thoughts or advice ?
If I want to try shrinking the log file, do I need to use a combination of
'BACKUP LOG' and 'DBCC SHRINKFILE' commands ? Or is there a more
automated way using maintenance plans?
Thanks in advance!
TomTom Glasser wrote:
> I am creating maintenance plans in SQL Server 2005 to manage the backups o
f a
> database. The Databasename_Data.MDF file is approx. 234 MB in size. The
> Databasename_Log.LDF is approx. 3.5 GB. Does this ratio of log size to da
ta
> size
> sound "normal" ? If not, any thoughts or advice ?
> If I want to try shrinking the log file, do I need to use a combination of
> 'BACKUP LOG' and 'DBCC SHRINKFILE' commands ? Or is there a more
> automated way using maintenance plans?
> Thanks in advance!
> Tom
Hi Tom
It sounds like your logfile is much bigger than it needs to be, but it
could also be right. If you are running in FULL recovery mode and you're
not backing up your logfile, it will just grow and grow and grow. When
you backup the logfile, the backup commands will truncate you log which
means that old transactions can be overwritten. In this way the space
will be re-used so the file doesn't need to grow.
If you find out that you have to shrink the logfile, you'll have to run
DBCC SHRINKFILE.
Be carefull about shrinking the log though. A logfile should only be
shrunk in cases where there has been extraordinary activity in the
database which has made the logfile grow. This could e.g. be if you have
imported or deleted a lot of data as a "one time" operation. You
shouldn't shrink a logfile on a regular basis - that will only lead to
poor performance because it will have to grow again, and the file will
most likely be fragmented as well.
Try to have a look at -
http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
Regards
Steen Schlüter Persson
Database Administrator / System Administratorsql
Log File Size vs. Data Size ?
I am creating maintenance plans in SQL Server 2005 to manage the backups of a
database. The Databasename_Data.MDF file is approx. 234 MB in size. The
Databasename_Log.LDF is approx. 3.5 GB. Does this ratio of log size to data
size
sound "normal" ? If not, any thoughts or advice ?
If I want to try shrinking the log file, do I need to use a combination of
'BACKUP LOG' and 'DBCC SHRINKFILE' commands ? Or is there a more
automated way using maintenance plans?
Thanks in advance!
TomTom Glasser wrote:
> I am creating maintenance plans in SQL Server 2005 to manage the backups of a
> database. The Databasename_Data.MDF file is approx. 234 MB in size. The
> Databasename_Log.LDF is approx. 3.5 GB. Does this ratio of log size to data
> size
> sound "normal" ? If not, any thoughts or advice ?
> If I want to try shrinking the log file, do I need to use a combination of
> 'BACKUP LOG' and 'DBCC SHRINKFILE' commands ? Or is there a more
> automated way using maintenance plans?
> Thanks in advance!
> Tom
Hi Tom
It sounds like your logfile is much bigger than it needs to be, but it
could also be right. If you are running in FULL recovery mode and you're
not backing up your logfile, it will just grow and grow and grow. When
you backup the logfile, the backup commands will truncate you log which
means that old transactions can be overwritten. In this way the space
will be re-used so the file doesn't need to grow.
If you find out that you have to shrink the logfile, you'll have to run
DBCC SHRINKFILE.
Be carefull about shrinking the log though. A logfile should only be
shrunk in cases where there has been extraordinary activity in the
database which has made the logfile grow. This could e.g. be if you have
imported or deleted a lot of data as a "one time" operation. You
shouldn't shrink a logfile on a regular basis - that will only lead to
poor performance because it will have to grow again, and the file will
most likely be fragmented as well.
Try to have a look at -
http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator
database. The Databasename_Data.MDF file is approx. 234 MB in size. The
Databasename_Log.LDF is approx. 3.5 GB. Does this ratio of log size to data
size
sound "normal" ? If not, any thoughts or advice ?
If I want to try shrinking the log file, do I need to use a combination of
'BACKUP LOG' and 'DBCC SHRINKFILE' commands ? Or is there a more
automated way using maintenance plans?
Thanks in advance!
TomTom Glasser wrote:
> I am creating maintenance plans in SQL Server 2005 to manage the backups of a
> database. The Databasename_Data.MDF file is approx. 234 MB in size. The
> Databasename_Log.LDF is approx. 3.5 GB. Does this ratio of log size to data
> size
> sound "normal" ? If not, any thoughts or advice ?
> If I want to try shrinking the log file, do I need to use a combination of
> 'BACKUP LOG' and 'DBCC SHRINKFILE' commands ? Or is there a more
> automated way using maintenance plans?
> Thanks in advance!
> Tom
Hi Tom
It sounds like your logfile is much bigger than it needs to be, but it
could also be right. If you are running in FULL recovery mode and you're
not backing up your logfile, it will just grow and grow and grow. When
you backup the logfile, the backup commands will truncate you log which
means that old transactions can be overwritten. In this way the space
will be re-used so the file doesn't need to grow.
If you find out that you have to shrink the logfile, you'll have to run
DBCC SHRINKFILE.
Be carefull about shrinking the log though. A logfile should only be
shrunk in cases where there has been extraordinary activity in the
database which has made the logfile grow. This could e.g. be if you have
imported or deleted a lot of data as a "one time" operation. You
shouldn't shrink a logfile on a regular basis - that will only lead to
poor performance because it will have to grow again, and the file will
most likely be fragmented as well.
Try to have a look at -
http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator
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
>
>
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
>
>
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 reduction
Hi
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.
[vbcol=seagreen]
> Tomasz B.
> "Sathian" wrote:
>
and[vbcol=seagreen]|||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|||Hi
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
>
>
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.
[vbcol=seagreen]
> Tomasz B.
> "Sathian" wrote:
>
and[vbcol=seagreen]|||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|||Hi
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
>
>
Log File Size Reducing
Hi Friends,
I am using SQL Server version 7 with Service Pack 4. We have a full database
backup job which is executed everyday at 12:00 noon. Now everyday manually
we goto to the Property Of that particular database to turn the Truncate Log
On Checkpoint Option TRUE.
Then, from the query analyzer we put CHECKPOINT. And then again we turn the
option FALSE. All this things are done before 12:00 noon, say around 11:55 a
m. Now suddenly we are noticing that the physical size of the Log File is ge
tting reduced. Say first d
ay it was 1024 MB, next day after the same operation it has become 512 mb...
The Auto Shrink option is not set, it is FALSE. Can anyone tell me why is th
is happening ? Actually I don't want the physical size of the log file to re
duce.
Any help in this regard is highly appreciated.
Dipankar.Dipankar Ganguly wrote:
> Hi Friends,
> I am using SQL Server version 7 with Service Pack 4. We have a full
> database backup job which is executed everyday at 12:00 noon. Now
> everyday manually we goto to the Property Of that particular database
> to turn the Truncate Log On Checkpoint Option TRUE. Then, from the
> query analyzer we put CHECKPOINT. And then again we turn the option
> FALSE. (...)
There are better ways to do this
Check out below KB 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
Log File Grows too big
http://www.support.microsoft.com/?id=317375
Log file filling up
http://www.support.microsoft.com/?id=110139
Considerations for Autogrow and AutoShrink
http://www.support.microsoft.com/?id=315512
http://www.mssqlserver.com/faq/logs-shrinklog.asp
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks for the links. But please tell me why is this size reducing incident
happening ? Is there any other setting which is changed by someone and hence
it is behaving in that manner?|||I have had a lot of fun with this exact same problem. This happens when
someone does a DBCC SHRINKFILE(2, 512) on the log file. Once this has been
issued, it will always try to shrink to that size. There is no way of
turning this value off or back to the default of not shrinking. Even if you
manually grow the log file it will still shrink. A pain I know.
So the next question is how to get around it. If you want the log file to
be 1Gb, grow the log file to say 1.5GB, then shrink it to 1GB. Now it will
always shrink to 1Gb.
HTH
Barry McAuslin
Look inside your SQL Server files with SQL File Explorer.
Go to http://www.sqlfe.com for more information.
"Dipankar Ganguly" <dipankarganguly@.hotmail.com> wrote in message
news:005FFB0F-7DA2-4C62-8D47-302601D7207A@.microsoft.com...
> Thanks for the links. But please tell me why is this size reducing
incident happening ? Is there any other setting which is changed by someone
and hence it is behaving in that manner?sql
I am using SQL Server version 7 with Service Pack 4. We have a full database
backup job which is executed everyday at 12:00 noon. Now everyday manually
we goto to the Property Of that particular database to turn the Truncate Log
On Checkpoint Option TRUE.
Then, from the query analyzer we put CHECKPOINT. And then again we turn the
option FALSE. All this things are done before 12:00 noon, say around 11:55 a
m. Now suddenly we are noticing that the physical size of the Log File is ge
tting reduced. Say first d
ay it was 1024 MB, next day after the same operation it has become 512 mb...
The Auto Shrink option is not set, it is FALSE. Can anyone tell me why is th
is happening ? Actually I don't want the physical size of the log file to re
duce.
Any help in this regard is highly appreciated.
Dipankar.Dipankar Ganguly wrote:
> Hi Friends,
> I am using SQL Server version 7 with Service Pack 4. We have a full
> database backup job which is executed everyday at 12:00 noon. Now
> everyday manually we goto to the Property Of that particular database
> to turn the Truncate Log On Checkpoint Option TRUE. Then, from the
> query analyzer we put CHECKPOINT. And then again we turn the option
> FALSE. (...)
There are better ways to do this
Check out below KB 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
Log File Grows too big
http://www.support.microsoft.com/?id=317375
Log file filling up
http://www.support.microsoft.com/?id=110139
Considerations for Autogrow and AutoShrink
http://www.support.microsoft.com/?id=315512
http://www.mssqlserver.com/faq/logs-shrinklog.asp
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks for the links. But please tell me why is this size reducing incident
happening ? Is there any other setting which is changed by someone and hence
it is behaving in that manner?|||I have had a lot of fun with this exact same problem. This happens when
someone does a DBCC SHRINKFILE(2, 512) on the log file. Once this has been
issued, it will always try to shrink to that size. There is no way of
turning this value off or back to the default of not shrinking. Even if you
manually grow the log file it will still shrink. A pain I know.
So the next question is how to get around it. If you want the log file to
be 1Gb, grow the log file to say 1.5GB, then shrink it to 1GB. Now it will
always shrink to 1Gb.
HTH
Barry McAuslin
Look inside your SQL Server files with SQL File Explorer.
Go to http://www.sqlfe.com for more information.
"Dipankar Ganguly" <dipankarganguly@.hotmail.com> wrote in message
news:005FFB0F-7DA2-4C62-8D47-302601D7207A@.microsoft.com...
> Thanks for the links. But please tell me why is this size reducing
incident happening ? Is there any other setting which is changed by someone
and hence it is behaving in that manner?sql
Log file size not reduce
Database log of my DB is around 2GB.
The database is using FULL recovery option.
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
Subscribe to:
Posts (Atom)