Log is growing crazy when run DBCC INDEXDEFRAG or DBREINDEX

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

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.

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

Log file won't truncate on backup (no open transaction)

Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Running dbcc opentran in the problem database yields no open transactions
running
dbcc sqlperf(logspace)
shows log file over 95% full (1.5 gig )
Then doing a FULL database backup doesn't truncate the log (still 95%
full) - running dbcc sqlperf(logspace) again doesn't change the usage %.
Problem just started in last couple of weeks. Running ok for over a year in
SQL 2000, and several years in SQL 7. Only change is the use of Tivoli
backup.
I am fairly sure that dropping the database and recreating it from a backup
will probably fix this. I have done a restore to a new database and the log
is empty.
Any thoughts on how to fix without dropping and restoring the database?
Any thoughts on what may have caused this?
TIA,
MikeAre you doing transaction log backups? These should empty the log.
Christian Smith
"M Searer" <nospam@.nospam.com> wrote in message
news:%23XlsKHY9DHA.2924@.tk2msftngp13.phx.gbl...
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Running dbcc opentran in the problem database yields no open transactions
> running
> dbcc sqlperf(logspace)
> shows log file over 95% full (1.5 gig )
> Then doing a FULL database backup doesn't truncate the log (still 95%
> full) - running dbcc sqlperf(logspace) again doesn't change the usage %.
> Problem just started in last couple of weeks. Running ok for over a year
in
> SQL 2000, and several years in SQL 7. Only change is the use of Tivoli
> backup.
> I am fairly sure that dropping the database and recreating it from a
backup
> will probably fix this. I have done a restore to a new database and the
log
> is empty.
> Any thoughts on how to fix without dropping and restoring the database?
> Any thoughts on what may have caused this?
> TIA,
> Mike
>
>|||As Christian indicate. Full (DB) backup does not empty the transaction log.
Only transaction log backup does. Perhaps the db was in simple recovery mode
and Tivoli (or someone else) did set it to full recovery mode). Also, before
you did your first db backup, the log is in "auto-truncate mode".
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"M Searer" <nospam@.nospam.com> wrote in message
news:%23XlsKHY9DHA.2924@.tk2msftngp13.phx.gbl...
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Running dbcc opentran in the problem database yields no open transactions
> running
> dbcc sqlperf(logspace)
> shows log file over 95% full (1.5 gig )
> Then doing a FULL database backup doesn't truncate the log (still 95%
> full) - running dbcc sqlperf(logspace) again doesn't change the usage %.
> Problem just started in last couple of weeks. Running ok for over a year
in
> SQL 2000, and several years in SQL 7. Only change is the use of Tivoli
> backup.
> I am fairly sure that dropping the database and recreating it from a
backup
> will probably fix this. I have done a restore to a new database and the
log
> is empty.
> Any thoughts on how to fix without dropping and restoring the database?
> Any thoughts on what may have caused this?
> TIA,
> Mike
>
>sql

Log file won't truncate on backup (no open transaction)

Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Running dbcc opentran in the problem database yields no open transactions
running
dbcc sqlperf(logspace)
shows log file over 95% full (1.5 gig )
Then doing a FULL database backup doesn't truncate the log (still 95%
full) - running dbcc sqlperf(logspace) again doesn't change the usage %.
Problem just started in last couple of weeks. Running ok for over a year in
SQL 2000, and several years in SQL 7. Only change is the use of Tivoli
backup.
I am fairly sure that dropping the database and recreating it from a backup
will probably fix this. I have done a restore to a new database and the log
is empty.
Any thoughts on how to fix without dropping and restoring the database?
Any thoughts on what may have caused this?
TIA,
MikeAre you doing transaction log backups? These should empty the log.
Christian Smith
"M Searer" <nospam@.nospam.com> wrote in message
news:%23XlsKHY9DHA.2924@.tk2msftngp13.phx.gbl...
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Running dbcc opentran in the problem database yields no open transactions
> running
> dbcc sqlperf(logspace)
> shows log file over 95% full (1.5 gig )
> Then doing a FULL database backup doesn't truncate the log (still 95%
> full) - running dbcc sqlperf(logspace) again doesn't change the usage %.
> Problem just started in last couple of weeks. Running ok for over a year
in
> SQL 2000, and several years in SQL 7. Only change is the use of Tivoli
> backup.
> I am fairly sure that dropping the database and recreating it from a
backup
> will probably fix this. I have done a restore to a new database and the
log
> is empty.
> Any thoughts on how to fix without dropping and restoring the database?
> Any thoughts on what may have caused this?
> TIA,
> Mike
>
>|||As Christian indicate. Full (DB) backup does not empty the transaction log.
Only transaction log backup does. Perhaps the db was in simple recovery mode
and Tivoli (or someone else) did set it to full recovery mode). Also, before
you did your first db backup, the log is in "auto-truncate mode".
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"M Searer" <nospam@.nospam.com> wrote in message
news:%23XlsKHY9DHA.2924@.tk2msftngp13.phx.gbl...
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Running dbcc opentran in the problem database yields no open transactions
> running
> dbcc sqlperf(logspace)
> shows log file over 95% full (1.5 gig )
> Then doing a FULL database backup doesn't truncate the log (still 95%
> full) - running dbcc sqlperf(logspace) again doesn't change the usage %.
> Problem just started in last couple of weeks. Running ok for over a year
in
> SQL 2000, and several years in SQL 7. Only change is the use of Tivoli
> backup.
> I am fairly sure that dropping the database and recreating it from a
backup
> will probably fix this. I have done a restore to a new database and the
log
> is empty.
> Any thoughts on how to fix without dropping and restoring the database?
> Any thoughts on what may have caused this?
> TIA,
> Mike
>
>

Log file size

I find that the following commands successfully decrease the size of the log
file:
- Backup Log MyDB With Truncate_Only
- DBCC SHRINKDATABASE (MyDB)
However the following commands (exec one after one) can't decrease the size
of the log file:
- DBCC SHRINKDATABASE (MyDb, TruncateOnly)
- DBCC SHRINKDATABASE (MyDB)
Can anybody explain why?
Thanks,
KMYou can use DBCC SHRINKFILE to shrink a log file. See the Books Online for
usage information.
Hope this helps.
Dan Guzman
SQL Server MVP
"krygim" <krygim@.hotmail.com> wrote in message
news:edS35FvIGHA.3056@.TK2MSFTNGP09.phx.gbl...
>I find that the following commands successfully decrease the size of the
>log
> file:
> - Backup Log MyDB With Truncate_Only
> - DBCC SHRINKDATABASE (MyDB)
> However the following commands (exec one after one) can't decrease the
> size
> of the log file:
> - DBCC SHRINKDATABASE (MyDb, TruncateOnly)
> - DBCC SHRINKDATABASE (MyDB)
> Can anybody explain why?
> Thanks,
> KM
>|||the TLog is made up internally of virtual log files. truncating the
log frees up the virtual files so shrinkdatabase/shrinkfile can take
back the space. if you run shrinkdatabase/shrinkfile without first
freeing up the virtual files, the space those virtual files take up
cannot be reallocated to the OS.
BOL has a GREAT explanation at
http://msdn.microsoft.com/library/d...r />
_1uzr.aspsql

Log file for database is full

hello
when i execute this command:
DBCC DBREINDEX ('dbo.OV_PM_LPolGroupAssignment',
PK_OV_PM_LPolGroupAssignment)
GO
give me this error:
Server: Msg 9002, Level 17, State 6, Line 1
The log file for database 'openview' is full. Back up the transaction log
for the database to free up some log space.
The statement has been terminated.
How can fix this? or what can i do ?
Regards
José Júlio DuarteHi
Every operation in the database is logged to t -log file
Set recovery mode of the database to SIMPLE and run this commnad again
"Jos? J?lio Duarte" <JosJlioDuarte@.discussions.microsoft.com> wrote in
message news:73E755C8-ECC5-43C2-A38C-400A68040BBD@.microsoft.com...
> hello
> when i execute this command:
> DBCC DBREINDEX ('dbo.OV_PM_LPolGroupAssignment',
> PK_OV_PM_LPolGroupAssignment)
> GO
> give me this error:
> Server: Msg 9002, Level 17, State 6, Line 1
> The log file for database 'openview' is full. Back up the transaction log
> for the database to free up some log space.
> The statement has been terminated.
> How can fix this? or what can i do ?
> Regards
> Jos? J?lio Duarte|||This is a multi-part message in MIME format.
--090303040507050906090408
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
José Júlio Duarte wrote:
> hello
> when i execute this command:
> DBCC DBREINDEX ('dbo.OV_PM_LPolGroupAssignment',
> PK_OV_PM_LPolGroupAssignment)
> GO
> give me this error:
> Server: Msg 9002, Level 17, State 6, Line 1
> The log file for database 'openview' is full. Back up the transaction log
> for the database to free up some log space.
> The statement has been terminated.
> How can fix this? or what can i do ?
> Regards
> José Júlio Duarte
>
You could either:
1: Backup the logfile to truncate the file so the space can be re-used.
(Look up BACKUP LOG in Books On Line)
2: Set the Autogrow ON for the logfile. (Look up ALTER DATABASE in Books
On Line )
3: Manually increase the logfile size. (Look up ALTER DATABASE in Books
On Line)
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator
--090303040507050906090408
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
José Júlio Duarte wrote:
<blockquote cite="mid73E755C8-ECC5-43C2-A38C-400A68040BBD@.microsoft.com"
type="cite">
<pre wrap="">hello
when i execute this command:
DBCC DBREINDEX ('dbo.OV_PM_LPolGroupAssignment',
PK_OV_PM_LPolGroupAssignment)
GO
give me this error:
Server: Msg 9002, Level 17, State 6, Line 1
The log file for database 'openview' is full. Back up the transaction log
for the database to free up some log space.
The statement has been terminated.
How can fix this? or what can i do ?
Regards
José Júlio Duarte
</pre>
</blockquote>
<font size="-1"><font face="Arial">You could either:<br>
<br>
1: Backup the logfile to truncate the file so the space can be re-used.
(Look up BACKUP LOG in Books On Line)<br>
2: Set the Autogrow ON for the logfile. (Look up ALTER DATABASE in
Books On Line )<br>
3: Manually increase the logfile size. (Look up ALTER DATABASE in Books
On Line)<br>
<br>
<br>
-- <br>
Regards<br>
Steen Schlüter Persson<br>
Databaseadministrator / Systemadministrator<br>
</font></font>
</body>
</html>
--090303040507050906090408--|||You can use DBCC SHRINKFILE (logical filename ... logsize)
to physically shrink the file for want of disk space
M A Srinivas
Jos=E9 J=FAlio Duarte wrote:
> hello
> when i execute this command:
> DBCC DBREINDEX ('dbo.OV_PM_LPolGroupAssignment',
> PK_OV_PM_LPolGroupAssignment)
> GO
> give me this error:
> Server: Msg 9002, Level 17, State 6, Line 1
> The log file for database 'openview' is full. Back up the transaction log
> for the database to free up some log space.
> The statement has been terminated.
> How can fix this? or what can i do ?
> > Regards
> > Jos=E9 J=FAlio Duarte|||masri999@.gmail.com wrote:
> You can use DBCC SHRINKFILE (logical filename ... logsize)
> to physically shrink the file for want of disk space
> M A Srinivas
>
I doubt that will make any difference? If SQL server reports that the
logfile is full, then there're no free space in the file meaning that
you can't shrink it.
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator|||The first obvious thing to try would be to backup the log file as it states
in the error message. If there still isn't enough log space after backing
up the log you will need to expand the size of the log file somehow - either
free up some space or add another file to the log
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"José Júlio Duarte" <JosJlioDuarte@.discussions.microsoft.com> wrote in
message news:73E755C8-ECC5-43C2-A38C-400A68040BBD@.microsoft.com...
> hello
> when i execute this command:
> DBCC DBREINDEX ('dbo.OV_PM_LPolGroupAssignment',
> PK_OV_PM_LPolGroupAssignment)
> GO
> give me this error:
> Server: Msg 9002, Level 17, State 6, Line 1
> The log file for database 'openview' is full. Back up the transaction log
> for the database to free up some log space.
> The statement has been terminated.
> How can fix this? or what can i do ?
> Regards
> José Júlio Duarte

log file 30GB

I would like to shrink the log file dbcc shrinkfile (abc_log,10) to 10MB
Before Shrinking the log file, what actions should be taken?
do i need checkpoint to make sure all transaction are comminted to data
file?Understand that SQL Server will not allow you to shrink the log file if it
still requires any data in there, so it's safe to shrink the file. It's the
truncate process you should be careful about. See
http://www.karaszi.com/sqlserver/info_dont_shrink.asp for some info on
shrinking log files.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Inamori" <test@.test.com> wrote in message
news:%23$jvZczrEHA.644@.tk2msftngp13.phx.gbl...
> I would like to shrink the log file dbcc shrinkfile (abc_log,10) to 10MB
> Before Shrinking the log file, what actions should be taken?
> do i need checkpoint to make sure all transaction are comminted to data
> file?
>|||that mean can i shrinkfile(abc_log, 1M)''
"Peter Yeoh" <nospam@.nospam.com> wrote in message
news:ONfC#hzrEHA.3876@.TK2MSFTNGP15.phx.gbl...
> Understand that SQL Server will not allow you to shrink the log file if it
> still requires any data in there, so it's safe to shrink the file. It's
the
> truncate process you should be careful about. See
> http://www.karaszi.com/sqlserver/info_dont_shrink.asp for some info on
> shrinking log files.
> --
> Peter Yeoh
> http://www.yohz.com
> Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
>
> "Inamori" <test@.test.com> wrote in message
> news:%23$jvZczrEHA.644@.tk2msftngp13.phx.gbl...
>|||Sure, if everything in that log file has been written to disk and there are
no active trxs at the end of the log file. SQL Server will ensure you do
not harm yourself performing a shrinkfile. But note Tibor's article: you
will probably fragment your log file badly if you constantly shrink it.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Inamori" <test@.test.com> wrote in message
news:ObuaSuzrEHA.3748@.TK2MSFTNGP09.phx.gbl...
> that mean can i shrinkfile(abc_log, 1M)''
>
> "Peter Yeoh" <nospam@.nospam.com> wrote in message
> news:ONfC#hzrEHA.3876@.TK2MSFTNGP15.phx.gbl...
it[vbcol=seagreen]
> the
10MB[vbcol=seagreen]
data[vbcol=seagreen]
>|||Are you interested in backups at all? Did you consider backing up the
database and seeing what happens after that?
http://www.aspfaq.com/
(Reverse address to reply.)
"Inamori" <test@.test.com> wrote in message
news:#$jvZczrEHA.644@.tk2msftngp13.phx.gbl...
> I would like to shrink the log file dbcc shrinkfile (abc_log,10) to 10MB
> Before Shrinking the log file, what actions should be taken?
> do i need checkpoint to make sure all transaction are comminted to data
> file?
>|||yes
i understand as follows
1. shrink only shrink something are inactive
2. No harm to data file
3. testing in testing environment
4. I will backup data file and log file first and shrink afterwards
5. even i want to shrink to 1M, i udnerstand actually it cannot if the log
data is active.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ePAtSZ0rEHA.1152@.TK2MSFTNGP11.phx.gbl...
> Are you interested in backups at all? Did you consider backing up the
> database and seeing what happens after that?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Inamori" <test@.test.com> wrote in message
> news:#$jvZczrEHA.644@.tk2msftngp13.phx.gbl...
>

log file 30GB

I would like to shrink the log file dbcc shrinkfile (abc_log,10) to 10MB
Before Shrinking the log file, what actions should be taken?
do i need checkpoint to make sure all transaction are comminted to data
file?
Understand that SQL Server will not allow you to shrink the log file if it
still requires any data in there, so it's safe to shrink the file. It's the
truncate process you should be careful about. See
http://www.karaszi.com/sqlserver/info_dont_shrink.asp for some info on
shrinking log files.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Inamori" <test@.test.com> wrote in message
news:%23$jvZczrEHA.644@.tk2msftngp13.phx.gbl...
> I would like to shrink the log file dbcc shrinkfile (abc_log,10) to 10MB
> Before Shrinking the log file, what actions should be taken?
> do i need checkpoint to make sure all transaction are comminted to data
> file?
>
|||that mean can i shrinkfile(abc_log, 1M)??
"Peter Yeoh" <nospam@.nospam.com> wrote in message
news:ONfC#hzrEHA.3876@.TK2MSFTNGP15.phx.gbl...
> Understand that SQL Server will not allow you to shrink the log file if it
> still requires any data in there, so it's safe to shrink the file. It's
the
> truncate process you should be careful about. See
> http://www.karaszi.com/sqlserver/info_dont_shrink.asp for some info on
> shrinking log files.
> --
> Peter Yeoh
> http://www.yohz.com
> Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
>
> "Inamori" <test@.test.com> wrote in message
> news:%23$jvZczrEHA.644@.tk2msftngp13.phx.gbl...
>
|||Sure, if everything in that log file has been written to disk and there are
no active trxs at the end of the log file. SQL Server will ensure you do
not harm yourself performing a shrinkfile. But note Tibor's article: you
will probably fragment your log file badly if you constantly shrink it.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Inamori" <test@.test.com> wrote in message
news:ObuaSuzrEHA.3748@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> that mean can i shrinkfile(abc_log, 1M)??
>
> "Peter Yeoh" <nospam@.nospam.com> wrote in message
> news:ONfC#hzrEHA.3876@.TK2MSFTNGP15.phx.gbl...
it[vbcol=seagreen]
> the
10MB[vbcol=seagreen]
data
>
|||Are you interested in backups at all? Did you consider backing up the
database and seeing what happens after that?
http://www.aspfaq.com/
(Reverse address to reply.)
"Inamori" <test@.test.com> wrote in message
news:#$jvZczrEHA.644@.tk2msftngp13.phx.gbl...
> I would like to shrink the log file dbcc shrinkfile (abc_log,10) to 10MB
> Before Shrinking the log file, what actions should be taken?
> do i need checkpoint to make sure all transaction are comminted to data
> file?
>
|||yes
i understand as follows
1. shrink only shrink something are inactive
2. No harm to data file
3. testing in testing environment
4. I will backup data file and log file first and shrink afterwards
5. even i want to shrink to 1M, i udnerstand actually it cannot if the log
data is active.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ePAtSZ0rEHA.1152@.TK2MSFTNGP11.phx.gbl...
> Are you interested in backups at all? Did you consider backing up the
> database and seeing what happens after that?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Inamori" <test@.test.com> wrote in message
> news:#$jvZczrEHA.644@.tk2msftngp13.phx.gbl...
>

log file 30GB

I would like to shrink the log file dbcc shrinkfile (abc_log,10) to 10MB
Before Shrinking the log file, what actions should be taken?
do i need checkpoint to make sure all transaction are comminted to data
file?Understand that SQL Server will not allow you to shrink the log file if it
still requires any data in there, so it's safe to shrink the file. It's the
truncate process you should be careful about. See
http://www.karaszi.com/sqlserver/info_dont_shrink.asp for some info on
shrinking log files.
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Inamori" <test@.test.com> wrote in message
news:%23$jvZczrEHA.644@.tk2msftngp13.phx.gbl...
> I would like to shrink the log file dbcc shrinkfile (abc_log,10) to 10MB
> Before Shrinking the log file, what actions should be taken?
> do i need checkpoint to make sure all transaction are comminted to data
> file?
>|||that mean can i shrinkfile(abc_log, 1M)''
"Peter Yeoh" <nospam@.nospam.com> wrote in message
news:ONfC#hzrEHA.3876@.TK2MSFTNGP15.phx.gbl...
> Understand that SQL Server will not allow you to shrink the log file if it
> still requires any data in there, so it's safe to shrink the file. It's
the
> truncate process you should be careful about. See
> http://www.karaszi.com/sqlserver/info_dont_shrink.asp for some info on
> shrinking log files.
> --
> Peter Yeoh
> http://www.yohz.com
> Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
>
> "Inamori" <test@.test.com> wrote in message
> news:%23$jvZczrEHA.644@.tk2msftngp13.phx.gbl...
> > I would like to shrink the log file dbcc shrinkfile (abc_log,10) to 10MB
> >
> > Before Shrinking the log file, what actions should be taken?
> >
> > do i need checkpoint to make sure all transaction are comminted to data
> > file?
> >
> >
>|||Sure, if everything in that log file has been written to disk and there are
no active trxs at the end of the log file. SQL Server will ensure you do
not harm yourself performing a shrinkfile. But note Tibor's article: you
will probably fragment your log file badly if you constantly shrink it.
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Inamori" <test@.test.com> wrote in message
news:ObuaSuzrEHA.3748@.TK2MSFTNGP09.phx.gbl...
> that mean can i shrinkfile(abc_log, 1M)''
>
> "Peter Yeoh" <nospam@.nospam.com> wrote in message
> news:ONfC#hzrEHA.3876@.TK2MSFTNGP15.phx.gbl...
> > Understand that SQL Server will not allow you to shrink the log file if
it
> > still requires any data in there, so it's safe to shrink the file. It's
> the
> > truncate process you should be careful about. See
> > http://www.karaszi.com/sqlserver/info_dont_shrink.asp for some info on
> > shrinking log files.
> >
> > --
> > Peter Yeoh
> > http://www.yohz.com
> > Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
> >
> >
> > "Inamori" <test@.test.com> wrote in message
> > news:%23$jvZczrEHA.644@.tk2msftngp13.phx.gbl...
> > > I would like to shrink the log file dbcc shrinkfile (abc_log,10) to
10MB
> > >
> > > Before Shrinking the log file, what actions should be taken?
> > >
> > > do i need checkpoint to make sure all transaction are comminted to
data
> > > file?
> > >
> > >
> >
> >
>|||Are you interested in backups at all? Did you consider backing up the
database and seeing what happens after that?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Inamori" <test@.test.com> wrote in message
news:#$jvZczrEHA.644@.tk2msftngp13.phx.gbl...
> I would like to shrink the log file dbcc shrinkfile (abc_log,10) to 10MB
> Before Shrinking the log file, what actions should be taken?
> do i need checkpoint to make sure all transaction are comminted to data
> file?
>|||yes
i understand as follows
1. shrink only shrink something are inactive
2. No harm to data file
3. testing in testing environment
4. I will backup data file and log file first and shrink afterwards
5. even i want to shrink to 1M, i udnerstand actually it cannot if the log
data is active.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ePAtSZ0rEHA.1152@.TK2MSFTNGP11.phx.gbl...
> Are you interested in backups at all? Did you consider backing up the
> database and seeing what happens after that?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Inamori" <test@.test.com> wrote in message
> news:#$jvZczrEHA.644@.tk2msftngp13.phx.gbl...
> > I would like to shrink the log file dbcc shrinkfile (abc_log,10) to 10MB
> >
> > Before Shrinking the log file, what actions should be taken?
> >
> > do i need checkpoint to make sure all transaction are comminted to data
> > file?
> >
> >
>