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.
Showing posts with label shrinkfile. Show all posts
Showing posts with label shrinkfile. Show all posts
Wednesday, March 28, 2012
log grows after shrinking
I have truncated the log file and then used the DBCC Shrinkfile command to
reduce to physical size of the file. This seemed to work perfectly.
However, after a few days the transaction log file returns to its huge size
(8Gb). Has anyone else experienced this problem and if so can you suggest a
solution?
Thanks in advance, Simon.Simon
It sounds like you have your database in full recovery mode, but you are not
performing transaction log backups.
If you want to use your transaction logs as part of your backup and recovery
strategy, (recommended for production systems) you need to back them up
regularly. Something like every 15 or 30 minutes (or whatever is suitable for
your requirements) during your business day.
If you really do not want your transaction logs, set your recovery mode to
simple.
Hope this helps
John
"Simon" wrote:
> I have truncated the log file and then used the DBCC Shrinkfile command to
> reduce to physical size of the file. This seemed to work perfectly.
> However, after a few days the transaction log file returns to its huge size
> (8Gb). Has anyone else experienced this problem and if so can you suggest a
> solution?
> Thanks in advance, Simon.|||Even in SIMPLE RECOVERY mode, the transaction log file(s) will grow to about
the size of the data portion of your data files if you are doing regular
maintenance with the DB maintenance wizard. The reason is that the
Organization task will serially reorganize your data tables. The largest
table will dictate the amount of free space in your data file and the size
of transaction log for normal operations.
If you do not want your tranaction log to be bigger than this, you need to
be in SIMPLE RECOVERY or backup the tlogs frequently if in FULL RECOVERY or
BULK INSERT RECOVERY modes.
How big is your data files?
Sincerely,
Anthony Thomas
"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
message news:9B630219-A733-4032-87DF-45B858628C31@.microsoft.com...
Simon
It sounds like you have your database in full recovery mode, but you are not
performing transaction log backups.
If you want to use your transaction logs as part of your backup and recovery
strategy, (recommended for production systems) you need to back them up
regularly. Something like every 15 or 30 minutes (or whatever is suitable
for
your requirements) during your business day.
If you really do not want your transaction logs, set your recovery mode to
simple.
Hope this helps
John
"Simon" wrote:
> I have truncated the log file and then used the DBCC Shrinkfile command to
> reduce to physical size of the file. This seemed to work perfectly.
> However, after a few days the transaction log file returns to its huge
size
> (8Gb). Has anyone else experienced this problem and if so can you suggest
a
> solution?
> Thanks in advance, Simon.sql
reduce to physical size of the file. This seemed to work perfectly.
However, after a few days the transaction log file returns to its huge size
(8Gb). Has anyone else experienced this problem and if so can you suggest a
solution?
Thanks in advance, Simon.Simon
It sounds like you have your database in full recovery mode, but you are not
performing transaction log backups.
If you want to use your transaction logs as part of your backup and recovery
strategy, (recommended for production systems) you need to back them up
regularly. Something like every 15 or 30 minutes (or whatever is suitable for
your requirements) during your business day.
If you really do not want your transaction logs, set your recovery mode to
simple.
Hope this helps
John
"Simon" wrote:
> I have truncated the log file and then used the DBCC Shrinkfile command to
> reduce to physical size of the file. This seemed to work perfectly.
> However, after a few days the transaction log file returns to its huge size
> (8Gb). Has anyone else experienced this problem and if so can you suggest a
> solution?
> Thanks in advance, Simon.|||Even in SIMPLE RECOVERY mode, the transaction log file(s) will grow to about
the size of the data portion of your data files if you are doing regular
maintenance with the DB maintenance wizard. The reason is that the
Organization task will serially reorganize your data tables. The largest
table will dictate the amount of free space in your data file and the size
of transaction log for normal operations.
If you do not want your tranaction log to be bigger than this, you need to
be in SIMPLE RECOVERY or backup the tlogs frequently if in FULL RECOVERY or
BULK INSERT RECOVERY modes.
How big is your data files?
Sincerely,
Anthony Thomas
"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
message news:9B630219-A733-4032-87DF-45B858628C31@.microsoft.com...
Simon
It sounds like you have your database in full recovery mode, but you are not
performing transaction log backups.
If you want to use your transaction logs as part of your backup and recovery
strategy, (recommended for production systems) you need to back them up
regularly. Something like every 15 or 30 minutes (or whatever is suitable
for
your requirements) during your business day.
If you really do not want your transaction logs, set your recovery mode to
simple.
Hope this helps
John
"Simon" wrote:
> I have truncated the log file and then used the DBCC Shrinkfile command to
> reduce to physical size of the file. This seemed to work perfectly.
> However, after a few days the transaction log file returns to its huge
size
> (8Gb). Has anyone else experienced this problem and if so can you suggest
a
> solution?
> Thanks in advance, Simon.sql
Friday, March 23, 2012
log file size and mirroring
How do I shrink a log file in a database that's been mirrored?
DBCC ShrinkFile(id) reports the size can drop considerably, but even after a CHECKPOINT, it doesn't shrink.
DBCC ShrinkFile('name') says it doesn't find the file name. The properties dialog for the database in SSMS doesn't list any files, even though sp_helpdb says they're there.
Must I disable mirroring, fix DBCC ShrinkSomething, then re-enable mirroring? Can I just pause mirroring?
There are 2 options available for you,
1.
USE XXX
GO
CHECKPOINT
GO
BACKUP LOG [XXX] TO [XXX_TLOG] WITH NOFORMAT, INIT, NAME = N'XXX-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
DBCC SHRINKFILE(XXX_Log, 1)
GO
2.
BACKUP LOG [dbname] TO DISK='Path'
Then do the shrink
dbcc shrinkfile ('TLOGFILENAME', yoursize) -- where yoursize is a number in MB
Since the CHECKPOINT option didnt work for you, try the 2nd option and revert if you have any concern
|||Thanks for responding, Deepak.I'm sorry if my question was not clear. I've tried both of the techniques you suggest and the database log file doesn't shrink.
The log_reuse_wait_desc column in sys.databases says "NOTHING" for the invovled database.
Why doesn't SQL server release the log file space to the OS?
|||
May I ask a question related issue? Is the participal database should be pause before shrinking?
Regards,
Melih
|||Deepak,
I tried to run query and I get message following:
Cannot shrink log file 2 (XXX_log) because all logical log files are in use.
Deepak Rangarajan wrote:
There are 2 options available for you,
1.
USE XXX
GO
CHECKPOINT
GO
BACKUP LOG [XXX] TO [XXX_TLOG] WITH NOFORMAT, INIT, NAME = N'XXX-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
DBCC SHRINKFILE(XXX_Log, 1)
GO2.
BACKUP LOG [dbname] TO DISK='Path'
Then do the shrink
dbcc shrinkfile ('TLOGFILENAME', yoursize) -- where yoursize is a number in MB
Since the CHECKPOINT option didnt work for you, try the 2nd option and revert if you have any concern
log file size and mirroring
How do I shrink a log file in a database that's been mirrored?
DBCC ShrinkFile(id) reports the size can drop considerably, but even after a CHECKPOINT, it doesn't shrink.
DBCC ShrinkFile('name') says it doesn't find the file name. The properties dialog for the database in SSMS doesn't list any files, even though sp_helpdb says they're there.
Must I disable mirroring, fix DBCC ShrinkSomething, then re-enable mirroring? Can I just pause mirroring?
There are 2 options available for you,
1.
USE XXX
GO
CHECKPOINT
GO
BACKUP LOG [XXX] TO [XXX_TLOG] WITH NOFORMAT, INIT, NAME = N'XXX-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
DBCC SHRINKFILE(XXX_Log, 1)
GO
2.
BACKUP LOG [dbname] TO DISK='Path'
Then do the shrink
dbcc shrinkfile ('TLOGFILENAME', yoursize) -- where yoursize is a number in MB
Since the CHECKPOINT option didnt work for you, try the 2nd option and revert if you have any concern
|||Thanks for responding, Deepak.I'm sorry if my question was not clear. I've tried both of the techniques you suggest and the database log file doesn't shrink.
The log_reuse_wait_desc column in sys.databases says "NOTHING" for the invovled database.
Why doesn't SQL server release the log file space to the OS?
|||
May I ask a question related issue? Is the participal database should be pause before shrinking?
Regards,
Melih
|||Deepak,
I tried to run query and I get message following:
Cannot shrink log file 2 (XXX_log) because all logical log files are in use.
sql
There are 2 options available for you,
1.
USE XXX
GO
CHECKPOINT
GO
BACKUP LOG [XXX] TO [XXX_TLOG] WITH NOFORMAT, INIT, NAME = N'XXX-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
DBCC SHRINKFILE(XXX_Log, 1)
GO2.
BACKUP LOG [dbname] TO DISK='Path'
Then do the shrink
dbcc shrinkfile ('TLOGFILENAME', yoursize) -- where yoursize is a number in MB
Since the CHECKPOINT option didnt work for you, try the 2nd option and revert if you have any concern
Monday, March 12, 2012
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...
>
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...
>
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?
> >
> >
>
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?
> >
> >
>
Subscribe to:
Posts (Atom)