SQL 7.0
How do I manually truncate a LOG file?
Thanks,
Don
BACKUP LOG database_name
WITH TRUNCATE_ONLY
BOL describes it nicely.
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:64e601c4c903$3e4969e0$a301280a@.phx.gbl...
> SQL 7.0
> How do I manually truncate a LOG file?
> Thanks,
> Don
>
|||That's the command I used on my 30GB LOG file and after
it ran without errors, all the pink turned to blue in EM
and the space remained the same.
How can I get rid of all of this space in the LOG file?
Thanks,
Don
>--Original Message--
>BACKUP LOG database_name
> WITH TRUNCATE_ONLY
>BOL describes it nicely.
>--
>Mike Epprecht, Microsoft SQL Server MVP
>Zurich, Switzerland
>
>IM: mike@.epprecht.net
>MVP Program: http://www.microsoft.com/mvp
>Blog: http://www.msmvps.com/epprecht/
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message
>news:64e601c4c903$3e4969e0$a301280a@.phx.gbl...
>
>.
>
|||I do that command and then go into EM and right-click on DB.
Choose SHRINK files.
Click on the FILES button - brings you to another pop-up window - choose the
LOG file from the DROPDOWN. Then click OK (the correct check box should be
already the default). This window disappears - then I cancel off the main
window, so I don't shrink the DB itself.
I wish I knew the SQL command string to do this action - EM does it in a
very cumbersome fashion.
But the LOG is 1024 K after this operation - so I know it works!!
"Don" wrote:
> That's the command I used on my 30GB LOG file and after
> it ran without errors, all the pink turned to blue in EM
> and the space remained the same.
> How can I get rid of all of this space in the LOG file?
> Thanks,
> Don
> message
>
|||Take a look at the DBCC SHRINKFILE option in BooksOnLine. If you are only
going to truncate the log why not set the recovery mode to SIMPLE?
Andrew J. Kelly SQL MVP
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:546301c4c908$6a3ab450$a401280a@.phx.gbl...[vbcol=seagreen]
> That's the command I used on my 30GB LOG file and after
> it ran without errors, all the pink turned to blue in EM
> and the space remained the same.
> How can I get rid of all of this space in the LOG file?
> Thanks,
> Don
> message
|||Andrew,
Don is using SQL 7. So best option is that he can enable the TRUNCATE LOG
ON CHECKPOINT option using sp_dboption.
Don,
Enable the database option TRUNCATE LOG ON CHECKPOINT and execute below:-
backup log dbname with truncate_only
go
DBCC SHRINKFILE (see books online)
Thanks
Hari
SQL Server MVP
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O$gLdzRyEHA.3844@.TK2MSFTNGP09.phx.gbl...
> Take a look at the DBCC SHRINKFILE option in BooksOnLine. If you are only
> going to truncate the log why not set the recovery mode to SIMPLE?
> --
> Andrew J. Kelly SQL MVP
>
> "Don" <anonymous@.discussions.microsoft.com> wrote in message
> news:546301c4c908$6a3ab450$a401280a@.phx.gbl...
>
sql
Showing posts with label truncate. Show all posts
Showing posts with label truncate. Show all posts
Wednesday, March 21, 2012
Manually truncate a LOG file
SQL 7.0
How do I manually truncate a LOG file?
Thanks,
DonBACKUP LOG database_name
WITH TRUNCATE_ONLY
BOL describes it nicely.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:64e601c4c903$3e4969e0$a301280a@.phx.gbl...
> SQL 7.0
> How do I manually truncate a LOG file?
> Thanks,
> Don
>|||That's the command I used on my 30GB LOG file and after
it ran without errors, all the pink turned to blue in EM
and the space remained the same.
How can I get rid of all of this space in the LOG file?
Thanks,
Don
>--Original Message--
>BACKUP LOG database_name
> WITH TRUNCATE_ONLY
>BOL describes it nicely.
>--
>Mike Epprecht, Microsoft SQL Server MVP
>Zurich, Switzerland
>
>IM: mike@.epprecht.net
>MVP Program: http://www.microsoft.com/mvp
>Blog: http://www.msmvps.com/epprecht/
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message
>news:64e601c4c903$3e4969e0$a301280a@.phx.gbl...
>> SQL 7.0
>> How do I manually truncate a LOG file?
>> Thanks,
>> Don
>
>.
>|||I do that command and then go into EM and right-click on DB.
Choose SHRINK files.
Click on the FILES button - brings you to another pop-up window - choose the
LOG file from the DROPDOWN. Then click OK (the correct check box should be
already the default). This window disappears - then I cancel off the main
window, so I don't shrink the DB itself.
I wish I knew the SQL command string to do this action - EM does it in a
very cumbersome fashion.
But the LOG is 1024 K after this operation - so I know it works!!
"Don" wrote:
> That's the command I used on my 30GB LOG file and after
> it ran without errors, all the pink turned to blue in EM
> and the space remained the same.
> How can I get rid of all of this space in the LOG file?
> Thanks,
> Don
> >--Original Message--
> >BACKUP LOG database_name
> > WITH TRUNCATE_ONLY
> >
> >BOL describes it nicely.
> >
> >--
> >Mike Epprecht, Microsoft SQL Server MVP
> >Zurich, Switzerland
> >
> >
> >IM: mike@.epprecht.net
> >
> >MVP Program: http://www.microsoft.com/mvp
> >
> >Blog: http://www.msmvps.com/epprecht/
> >
> >"Don" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:64e601c4c903$3e4969e0$a301280a@.phx.gbl...
> >> SQL 7.0
> >>
> >> How do I manually truncate a LOG file?
> >>
> >> Thanks,
> >> Don
> >>
> >
> >
> >.
> >
>|||Take a look at the DBCC SHRINKFILE option in BooksOnLine. If you are only
going to truncate the log why not set the recovery mode to SIMPLE?
--
Andrew J. Kelly SQL MVP
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:546301c4c908$6a3ab450$a401280a@.phx.gbl...
> That's the command I used on my 30GB LOG file and after
> it ran without errors, all the pink turned to blue in EM
> and the space remained the same.
> How can I get rid of all of this space in the LOG file?
> Thanks,
> Don
>>--Original Message--
>>BACKUP LOG database_name
>> WITH TRUNCATE_ONLY
>>BOL describes it nicely.
>>--
>>Mike Epprecht, Microsoft SQL Server MVP
>>Zurich, Switzerland
>>
>>IM: mike@.epprecht.net
>>MVP Program: http://www.microsoft.com/mvp
>>Blog: http://www.msmvps.com/epprecht/
>>"Don" <anonymous@.discussions.microsoft.com> wrote in
> message
>>news:64e601c4c903$3e4969e0$a301280a@.phx.gbl...
>> SQL 7.0
>> How do I manually truncate a LOG file?
>> Thanks,
>> Don
>>
>>.|||Andrew,
Don is using SQL 7. So best option is that he can enable the TRUNCATE LOG
ON CHECKPOINT option using sp_dboption.
Don,
Enable the database option TRUNCATE LOG ON CHECKPOINT and execute below:-
backup log dbname with truncate_only
go
DBCC SHRINKFILE (see books online)
Thanks
Hari
SQL Server MVP
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O$gLdzRyEHA.3844@.TK2MSFTNGP09.phx.gbl...
> Take a look at the DBCC SHRINKFILE option in BooksOnLine. If you are only
> going to truncate the log why not set the recovery mode to SIMPLE?
> --
> Andrew J. Kelly SQL MVP
>
> "Don" <anonymous@.discussions.microsoft.com> wrote in message
> news:546301c4c908$6a3ab450$a401280a@.phx.gbl...
>> That's the command I used on my 30GB LOG file and after
>> it ran without errors, all the pink turned to blue in EM
>> and the space remained the same.
>> How can I get rid of all of this space in the LOG file?
>> Thanks,
>> Don
>>--Original Message--
>>BACKUP LOG database_name
>> WITH TRUNCATE_ONLY
>>BOL describes it nicely.
>>--
>>Mike Epprecht, Microsoft SQL Server MVP
>>Zurich, Switzerland
>>
>>IM: mike@.epprecht.net
>>MVP Program: http://www.microsoft.com/mvp
>>Blog: http://www.msmvps.com/epprecht/
>>"Don" <anonymous@.discussions.microsoft.com> wrote in
>> message
>>news:64e601c4c903$3e4969e0$a301280a@.phx.gbl...
>> SQL 7.0
>> How do I manually truncate a LOG file?
>> Thanks,
>> Don
>>
>>.
>
How do I manually truncate a LOG file?
Thanks,
DonBACKUP LOG database_name
WITH TRUNCATE_ONLY
BOL describes it nicely.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:64e601c4c903$3e4969e0$a301280a@.phx.gbl...
> SQL 7.0
> How do I manually truncate a LOG file?
> Thanks,
> Don
>|||That's the command I used on my 30GB LOG file and after
it ran without errors, all the pink turned to blue in EM
and the space remained the same.
How can I get rid of all of this space in the LOG file?
Thanks,
Don
>--Original Message--
>BACKUP LOG database_name
> WITH TRUNCATE_ONLY
>BOL describes it nicely.
>--
>Mike Epprecht, Microsoft SQL Server MVP
>Zurich, Switzerland
>
>IM: mike@.epprecht.net
>MVP Program: http://www.microsoft.com/mvp
>Blog: http://www.msmvps.com/epprecht/
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message
>news:64e601c4c903$3e4969e0$a301280a@.phx.gbl...
>> SQL 7.0
>> How do I manually truncate a LOG file?
>> Thanks,
>> Don
>
>.
>|||I do that command and then go into EM and right-click on DB.
Choose SHRINK files.
Click on the FILES button - brings you to another pop-up window - choose the
LOG file from the DROPDOWN. Then click OK (the correct check box should be
already the default). This window disappears - then I cancel off the main
window, so I don't shrink the DB itself.
I wish I knew the SQL command string to do this action - EM does it in a
very cumbersome fashion.
But the LOG is 1024 K after this operation - so I know it works!!
"Don" wrote:
> That's the command I used on my 30GB LOG file and after
> it ran without errors, all the pink turned to blue in EM
> and the space remained the same.
> How can I get rid of all of this space in the LOG file?
> Thanks,
> Don
> >--Original Message--
> >BACKUP LOG database_name
> > WITH TRUNCATE_ONLY
> >
> >BOL describes it nicely.
> >
> >--
> >Mike Epprecht, Microsoft SQL Server MVP
> >Zurich, Switzerland
> >
> >
> >IM: mike@.epprecht.net
> >
> >MVP Program: http://www.microsoft.com/mvp
> >
> >Blog: http://www.msmvps.com/epprecht/
> >
> >"Don" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:64e601c4c903$3e4969e0$a301280a@.phx.gbl...
> >> SQL 7.0
> >>
> >> How do I manually truncate a LOG file?
> >>
> >> Thanks,
> >> Don
> >>
> >
> >
> >.
> >
>|||Take a look at the DBCC SHRINKFILE option in BooksOnLine. If you are only
going to truncate the log why not set the recovery mode to SIMPLE?
--
Andrew J. Kelly SQL MVP
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:546301c4c908$6a3ab450$a401280a@.phx.gbl...
> That's the command I used on my 30GB LOG file and after
> it ran without errors, all the pink turned to blue in EM
> and the space remained the same.
> How can I get rid of all of this space in the LOG file?
> Thanks,
> Don
>>--Original Message--
>>BACKUP LOG database_name
>> WITH TRUNCATE_ONLY
>>BOL describes it nicely.
>>--
>>Mike Epprecht, Microsoft SQL Server MVP
>>Zurich, Switzerland
>>
>>IM: mike@.epprecht.net
>>MVP Program: http://www.microsoft.com/mvp
>>Blog: http://www.msmvps.com/epprecht/
>>"Don" <anonymous@.discussions.microsoft.com> wrote in
> message
>>news:64e601c4c903$3e4969e0$a301280a@.phx.gbl...
>> SQL 7.0
>> How do I manually truncate a LOG file?
>> Thanks,
>> Don
>>
>>.|||Andrew,
Don is using SQL 7. So best option is that he can enable the TRUNCATE LOG
ON CHECKPOINT option using sp_dboption.
Don,
Enable the database option TRUNCATE LOG ON CHECKPOINT and execute below:-
backup log dbname with truncate_only
go
DBCC SHRINKFILE (see books online)
Thanks
Hari
SQL Server MVP
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O$gLdzRyEHA.3844@.TK2MSFTNGP09.phx.gbl...
> Take a look at the DBCC SHRINKFILE option in BooksOnLine. If you are only
> going to truncate the log why not set the recovery mode to SIMPLE?
> --
> Andrew J. Kelly SQL MVP
>
> "Don" <anonymous@.discussions.microsoft.com> wrote in message
> news:546301c4c908$6a3ab450$a401280a@.phx.gbl...
>> That's the command I used on my 30GB LOG file and after
>> it ran without errors, all the pink turned to blue in EM
>> and the space remained the same.
>> How can I get rid of all of this space in the LOG file?
>> Thanks,
>> Don
>>--Original Message--
>>BACKUP LOG database_name
>> WITH TRUNCATE_ONLY
>>BOL describes it nicely.
>>--
>>Mike Epprecht, Microsoft SQL Server MVP
>>Zurich, Switzerland
>>
>>IM: mike@.epprecht.net
>>MVP Program: http://www.microsoft.com/mvp
>>Blog: http://www.msmvps.com/epprecht/
>>"Don" <anonymous@.discussions.microsoft.com> wrote in
>> message
>>news:64e601c4c903$3e4969e0$a301280a@.phx.gbl...
>> SQL 7.0
>> How do I manually truncate a LOG file?
>> Thanks,
>> Don
>>
>>.
>
Manually truncate a LOG file
SQL 7.0
How do I manually truncate a LOG file?
Thanks,
DonBACKUP LOG database_name
WITH TRUNCATE_ONLY
BOL describes it nicely.
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:64e601c4c903$3e4969e0$a301280a@.phx.gbl...
> SQL 7.0
> How do I manually truncate a LOG file?
> Thanks,
> Don
>|||That's the command I used on my 30GB LOG file and after
it ran without errors, all the pink turned to blue in EM
and the space remained the same.
How can I get rid of all of this space in the LOG file?
Thanks,
Don
>--Original Message--
>BACKUP LOG database_name
> WITH TRUNCATE_ONLY
>BOL describes it nicely.
>--
>Mike Epprecht, Microsoft SQL Server MVP
>Zurich, Switzerland
>
>IM: mike@.epprecht.net
>MVP Program: http://www.microsoft.com/mvp
>Blog: http://www.msmvps.com/epprecht/
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message
>news:64e601c4c903$3e4969e0$a301280a@.phx.gbl...
>
>.
>|||I do that command and then go into EM and right-click on DB.
Choose SHRINK files.
Click on the FILES button - brings you to another pop-up window - choose the
LOG file from the DROPDOWN. Then click OK (the correct check box should be
already the default). This window disappears - then I cancel off the main
window, so I don't shrink the DB itself.
I wish I knew the SQL command string to do this action - EM does it in a
very cumbersome fashion.
But the LOG is 1024 K after this operation - so I know it works!!
"Don" wrote:
> That's the command I used on my 30GB LOG file and after
> it ran without errors, all the pink turned to blue in EM
> and the space remained the same.
> How can I get rid of all of this space in the LOG file?
> Thanks,
> Don
>
> message
>|||Take a look at the DBCC SHRINKFILE option in BooksOnLine. If you are only
going to truncate the log why not set the recovery mode to SIMPLE?
Andrew J. Kelly SQL MVP
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:546301c4c908$6a3ab450$a401280a@.phx.gbl...[vbcol=seagreen]
> That's the command I used on my 30GB LOG file and after
> it ran without errors, all the pink turned to blue in EM
> and the space remained the same.
> How can I get rid of all of this space in the LOG file?
> Thanks,
> Don
>
> message|||Andrew,
Don is using SQL 7. So best option is that he can enable the TRUNCATE LOG
ON CHECKPOINT option using sp_dboption.
Don,
Enable the database option TRUNCATE LOG ON CHECKPOINT and execute below:-
backup log dbname with truncate_only
go
DBCC SHRINKFILE (see books online)
Thanks
Hari
SQL Server MVP
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O$gLdzRyEHA.3844@.TK2MSFTNGP09.phx.gbl...
> Take a look at the DBCC SHRINKFILE option in BooksOnLine. If you are only
> going to truncate the log why not set the recovery mode to SIMPLE?
> --
> Andrew J. Kelly SQL MVP
>
> "Don" <anonymous@.discussions.microsoft.com> wrote in message
> news:546301c4c908$6a3ab450$a401280a@.phx.gbl...
>
How do I manually truncate a LOG file?
Thanks,
DonBACKUP LOG database_name
WITH TRUNCATE_ONLY
BOL describes it nicely.
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:64e601c4c903$3e4969e0$a301280a@.phx.gbl...
> SQL 7.0
> How do I manually truncate a LOG file?
> Thanks,
> Don
>|||That's the command I used on my 30GB LOG file and after
it ran without errors, all the pink turned to blue in EM
and the space remained the same.
How can I get rid of all of this space in the LOG file?
Thanks,
Don
>--Original Message--
>BACKUP LOG database_name
> WITH TRUNCATE_ONLY
>BOL describes it nicely.
>--
>Mike Epprecht, Microsoft SQL Server MVP
>Zurich, Switzerland
>
>IM: mike@.epprecht.net
>MVP Program: http://www.microsoft.com/mvp
>Blog: http://www.msmvps.com/epprecht/
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message
>news:64e601c4c903$3e4969e0$a301280a@.phx.gbl...
>
>.
>|||I do that command and then go into EM and right-click on DB.
Choose SHRINK files.
Click on the FILES button - brings you to another pop-up window - choose the
LOG file from the DROPDOWN. Then click OK (the correct check box should be
already the default). This window disappears - then I cancel off the main
window, so I don't shrink the DB itself.
I wish I knew the SQL command string to do this action - EM does it in a
very cumbersome fashion.
But the LOG is 1024 K after this operation - so I know it works!!
"Don" wrote:
> That's the command I used on my 30GB LOG file and after
> it ran without errors, all the pink turned to blue in EM
> and the space remained the same.
> How can I get rid of all of this space in the LOG file?
> Thanks,
> Don
>
> message
>|||Take a look at the DBCC SHRINKFILE option in BooksOnLine. If you are only
going to truncate the log why not set the recovery mode to SIMPLE?
Andrew J. Kelly SQL MVP
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:546301c4c908$6a3ab450$a401280a@.phx.gbl...[vbcol=seagreen]
> That's the command I used on my 30GB LOG file and after
> it ran without errors, all the pink turned to blue in EM
> and the space remained the same.
> How can I get rid of all of this space in the LOG file?
> Thanks,
> Don
>
> message|||Andrew,
Don is using SQL 7. So best option is that he can enable the TRUNCATE LOG
ON CHECKPOINT option using sp_dboption.
Don,
Enable the database option TRUNCATE LOG ON CHECKPOINT and execute below:-
backup log dbname with truncate_only
go
DBCC SHRINKFILE (see books online)
Thanks
Hari
SQL Server MVP
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O$gLdzRyEHA.3844@.TK2MSFTNGP09.phx.gbl...
> Take a look at the DBCC SHRINKFILE option in BooksOnLine. If you are only
> going to truncate the log why not set the recovery mode to SIMPLE?
> --
> Andrew J. Kelly SQL MVP
>
> "Don" <anonymous@.discussions.microsoft.com> wrote in message
> news:546301c4c908$6a3ab450$a401280a@.phx.gbl...
>
manually truncate a log
I have a log.ldf file that has grown to around 150 GB ... I can't really do a
BACKUP of the database to truncate it.... it seems to struggle with that...
is there another way that I can MANUALLY truncate this log file?
help..
How full is the log? Do you do any t-log backups at all? Its
possible you can just shrink it if the log is somewhat empty.
Otherwise, take a full backup, switch to simple recovery, shrink the
log, set back to Full and schedule your t-logs to be backed up on a
regular basis (we do our critical systems every 10 minutes, and our
important systems every two hours)
On Mar 16, 11:57 am, MSUTech <MSUT...@.discussions.microsoft.com>
wrote:
> I have a log.ldf file that has grown to around 150 GB ... I can't really do a
> BACKUP of the database to truncate it.... it seems to struggle with that...
> is there another way that I can MANUALLY truncate this log file?
> help..
|||The 'actual' log file shows its size as 154,081,280 KB
when I run DBCC Shrinkfile it says:
DbID: 12
FileId: 2
CurrentSize: 19260160
MinimumSize: 640
UsedPages: 19260160
EstimatedPages: 640
when I run this... the actual file size does not change.. and if I try to do
a backup of the transaction log... the system runs and then fails...
"PSPDBA" wrote:
> How full is the log? Do you do any t-log backups at all? Its
> possible you can just shrink it if the log is somewhat empty.
> Otherwise, take a full backup, switch to simple recovery, shrink the
> log, set back to Full and schedule your t-logs to be backed up on a
> regular basis (we do our critical systems every 10 minutes, and our
> important systems every two hours)
> On Mar 16, 11:57 am, MSUTech <MSUT...@.discussions.microsoft.com>
> wrote:
>
>
|||"MSUTech" <MSUTech@.discussions.microsoft.com> wrote in message
news:4AE50DC8-86DF-40D6-8610-B3CD572D070B@.microsoft.com...
> The 'actual' log file shows its size as 154,081,280 KB
>
BACKUP LOG <dbname> WITH TRUNCATEONLY
But not it'll invalidate your backup chain (which I guess doesn't exist.)
Once you do this, do a FULL database backup and then setup transaction
backups to run more often.
[vbcol=seagreen]
> when I run DBCC Shrinkfile it says:
> DbID: 12
> FileId: 2
> CurrentSize: 19260160
> MinimumSize: 640
> UsedPages: 19260160
> EstimatedPages: 640
> when I run this... the actual file size does not change.. and if I try to
> do
> a backup of the transaction log... the system runs and then fails...
> "PSPDBA" wrote:
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com
BACKUP of the database to truncate it.... it seems to struggle with that...
is there another way that I can MANUALLY truncate this log file?
help..
How full is the log? Do you do any t-log backups at all? Its
possible you can just shrink it if the log is somewhat empty.
Otherwise, take a full backup, switch to simple recovery, shrink the
log, set back to Full and schedule your t-logs to be backed up on a
regular basis (we do our critical systems every 10 minutes, and our
important systems every two hours)
On Mar 16, 11:57 am, MSUTech <MSUT...@.discussions.microsoft.com>
wrote:
> I have a log.ldf file that has grown to around 150 GB ... I can't really do a
> BACKUP of the database to truncate it.... it seems to struggle with that...
> is there another way that I can MANUALLY truncate this log file?
> help..
|||The 'actual' log file shows its size as 154,081,280 KB
when I run DBCC Shrinkfile it says:
DbID: 12
FileId: 2
CurrentSize: 19260160
MinimumSize: 640
UsedPages: 19260160
EstimatedPages: 640
when I run this... the actual file size does not change.. and if I try to do
a backup of the transaction log... the system runs and then fails...
"PSPDBA" wrote:
> How full is the log? Do you do any t-log backups at all? Its
> possible you can just shrink it if the log is somewhat empty.
> Otherwise, take a full backup, switch to simple recovery, shrink the
> log, set back to Full and schedule your t-logs to be backed up on a
> regular basis (we do our critical systems every 10 minutes, and our
> important systems every two hours)
> On Mar 16, 11:57 am, MSUTech <MSUT...@.discussions.microsoft.com>
> wrote:
>
>
|||"MSUTech" <MSUTech@.discussions.microsoft.com> wrote in message
news:4AE50DC8-86DF-40D6-8610-B3CD572D070B@.microsoft.com...
> The 'actual' log file shows its size as 154,081,280 KB
>
BACKUP LOG <dbname> WITH TRUNCATEONLY
But not it'll invalidate your backup chain (which I guess doesn't exist.)
Once you do this, do a FULL database backup and then setup transaction
backups to run more often.
[vbcol=seagreen]
> when I run DBCC Shrinkfile it says:
> DbID: 12
> FileId: 2
> CurrentSize: 19260160
> MinimumSize: 640
> UsedPages: 19260160
> EstimatedPages: 640
> when I run this... the actual file size does not change.. and if I try to
> do
> a backup of the transaction log... the system runs and then fails...
> "PSPDBA" wrote:
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com
Monday, March 19, 2012
manually truncate a log
I have a log.ldf file that has grown to around 150 GB ... I can't really do
a
BACKUP of the database to truncate it.... it seems to struggle with that...
.
is there another way that I can MANUALLY truncate this log file?
help..How full is the log? Do you do any t-log backups at all? Its
possible you can just shrink it if the log is somewhat empty.
Otherwise, take a full backup, switch to simple recovery, shrink the
log, set back to Full and schedule your t-logs to be backed up on a
regular basis (we do our critical systems every 10 minutes, and our
important systems every two hours)
On Mar 16, 11:57 am, MSUTech <MSUT...@.discussions.microsoft.com>
wrote:
> I have a log.ldf file that has grown to around 150 GB ... I can't really d
o a
> BACKUP of the database to truncate it.... it seems to struggle with that.
..
> is there another way that I can MANUALLY truncate this log file?
> help..|||Also: http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
news:1174062754.069322.283110@.y80g2000hsf.googlegroups.com...
> How full is the log? Do you do any t-log backups at all? Its
> possible you can just shrink it if the log is somewhat empty.
> Otherwise, take a full backup, switch to simple recovery, shrink the
> log, set back to Full and schedule your t-logs to be backed up on a
> regular basis (we do our critical systems every 10 minutes, and our
> important systems every two hours)
> On Mar 16, 11:57 am, MSUTech <MSUT...@.discussions.microsoft.com>
> wrote:
>|||The 'actual' log file shows its size as 154,081,280 KB
when I run DBCC Shrinkfile it says:
DbID: 12
FileId: 2
CurrentSize: 19260160
MinimumSize: 640
UsedPages: 19260160
EstimatedPages: 640
when I run this... the actual file size does not change.. and if I try to do
a backup of the transaction log... the system runs and then fails...
"PSPDBA" wrote:
> How full is the log? Do you do any t-log backups at all? Its
> possible you can just shrink it if the log is somewhat empty.
> Otherwise, take a full backup, switch to simple recovery, shrink the
> log, set back to Full and schedule your t-logs to be backed up on a
> regular basis (we do our critical systems every 10 minutes, and our
> important systems every two hours)
> On Mar 16, 11:57 am, MSUTech <MSUT...@.discussions.microsoft.com>
> wrote:
>
>|||"MSUTech" <MSUTech@.discussions.microsoft.com> wrote in message
news:4AE50DC8-86DF-40D6-8610-B3CD572D070B@.microsoft.com...
> The 'actual' log file shows its size as 154,081,280 KB
>
BACKUP LOG <dbname> WITH TRUNCATEONLY
But not it'll invalidate your backup chain (which I guess doesn't exist.)
Once you do this, do a FULL database backup and then setup transaction
backups to run more often.
[vbcol=seagreen]
> when I run DBCC Shrinkfile it says:
> DbID: 12
> FileId: 2
> CurrentSize: 19260160
> MinimumSize: 640
> UsedPages: 19260160
> EstimatedPages: 640
> when I run this... the actual file size does not change.. and if I try to
> do
> a backup of the transaction log... the system runs and then fails...
> "PSPDBA" wrote:
>
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com
a
BACKUP of the database to truncate it.... it seems to struggle with that...
.
is there another way that I can MANUALLY truncate this log file?
help..How full is the log? Do you do any t-log backups at all? Its
possible you can just shrink it if the log is somewhat empty.
Otherwise, take a full backup, switch to simple recovery, shrink the
log, set back to Full and schedule your t-logs to be backed up on a
regular basis (we do our critical systems every 10 minutes, and our
important systems every two hours)
On Mar 16, 11:57 am, MSUTech <MSUT...@.discussions.microsoft.com>
wrote:
> I have a log.ldf file that has grown to around 150 GB ... I can't really d
o a
> BACKUP of the database to truncate it.... it seems to struggle with that.
..
> is there another way that I can MANUALLY truncate this log file?
> help..|||Also: http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
news:1174062754.069322.283110@.y80g2000hsf.googlegroups.com...
> How full is the log? Do you do any t-log backups at all? Its
> possible you can just shrink it if the log is somewhat empty.
> Otherwise, take a full backup, switch to simple recovery, shrink the
> log, set back to Full and schedule your t-logs to be backed up on a
> regular basis (we do our critical systems every 10 minutes, and our
> important systems every two hours)
> On Mar 16, 11:57 am, MSUTech <MSUT...@.discussions.microsoft.com>
> wrote:
>|||The 'actual' log file shows its size as 154,081,280 KB
when I run DBCC Shrinkfile it says:
DbID: 12
FileId: 2
CurrentSize: 19260160
MinimumSize: 640
UsedPages: 19260160
EstimatedPages: 640
when I run this... the actual file size does not change.. and if I try to do
a backup of the transaction log... the system runs and then fails...
"PSPDBA" wrote:
> How full is the log? Do you do any t-log backups at all? Its
> possible you can just shrink it if the log is somewhat empty.
> Otherwise, take a full backup, switch to simple recovery, shrink the
> log, set back to Full and schedule your t-logs to be backed up on a
> regular basis (we do our critical systems every 10 minutes, and our
> important systems every two hours)
> On Mar 16, 11:57 am, MSUTech <MSUT...@.discussions.microsoft.com>
> wrote:
>
>|||"MSUTech" <MSUTech@.discussions.microsoft.com> wrote in message
news:4AE50DC8-86DF-40D6-8610-B3CD572D070B@.microsoft.com...
> The 'actual' log file shows its size as 154,081,280 KB
>
BACKUP LOG <dbname> WITH TRUNCATEONLY
But not it'll invalidate your backup chain (which I guess doesn't exist.)
Once you do this, do a FULL database backup and then setup transaction
backups to run more often.
[vbcol=seagreen]
> when I run DBCC Shrinkfile it says:
> DbID: 12
> FileId: 2
> CurrentSize: 19260160
> MinimumSize: 640
> UsedPages: 19260160
> EstimatedPages: 640
> when I run this... the actual file size does not change.. and if I try to
> do
> a backup of the transaction log... the system runs and then fails...
> "PSPDBA" wrote:
>
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com
manually truncate a log
I have a log.ldf file that has grown to around 150 GB ... I can't really do a
BACKUP of the database to truncate it.... it seems to struggle with that...
is there another way that I can MANUALLY truncate this log file?
help..How full is the log? Do you do any t-log backups at all? Its
possible you can just shrink it if the log is somewhat empty.
Otherwise, take a full backup, switch to simple recovery, shrink the
log, set back to Full and schedule your t-logs to be backed up on a
regular basis (we do our critical systems every 10 minutes, and our
important systems every two hours)
On Mar 16, 11:57 am, MSUTech <MSUT...@.discussions.microsoft.com>
wrote:
> I have a log.ldf file that has grown to around 150 GB ... I can't really do a
> BACKUP of the database to truncate it.... it seems to struggle with that...
> is there another way that I can MANUALLY truncate this log file?
> help..|||Also: http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
news:1174062754.069322.283110@.y80g2000hsf.googlegroups.com...
> How full is the log? Do you do any t-log backups at all? Its
> possible you can just shrink it if the log is somewhat empty.
> Otherwise, take a full backup, switch to simple recovery, shrink the
> log, set back to Full and schedule your t-logs to be backed up on a
> regular basis (we do our critical systems every 10 minutes, and our
> important systems every two hours)
> On Mar 16, 11:57 am, MSUTech <MSUT...@.discussions.microsoft.com>
> wrote:
>> I have a log.ldf file that has grown to around 150 GB ... I can't really do a
>> BACKUP of the database to truncate it.... it seems to struggle with that...
>> is there another way that I can MANUALLY truncate this log file?
>> help..
>|||The 'actual' log file shows its size as 154,081,280 KB
when I run DBCC Shrinkfile it says:
DbID: 12
FileId: 2
CurrentSize: 19260160
MinimumSize: 640
UsedPages: 19260160
EstimatedPages: 640
when I run this... the actual file size does not change.. and if I try to do
a backup of the transaction log... the system runs and then fails...
"PSPDBA" wrote:
> How full is the log? Do you do any t-log backups at all? Its
> possible you can just shrink it if the log is somewhat empty.
> Otherwise, take a full backup, switch to simple recovery, shrink the
> log, set back to Full and schedule your t-logs to be backed up on a
> regular basis (we do our critical systems every 10 minutes, and our
> important systems every two hours)
> On Mar 16, 11:57 am, MSUTech <MSUT...@.discussions.microsoft.com>
> wrote:
> > I have a log.ldf file that has grown to around 150 GB ... I can't really do a
> > BACKUP of the database to truncate it.... it seems to struggle with that...
> >
> > is there another way that I can MANUALLY truncate this log file?
> >
> > help..
>
>|||"MSUTech" <MSUTech@.discussions.microsoft.com> wrote in message
news:4AE50DC8-86DF-40D6-8610-B3CD572D070B@.microsoft.com...
> The 'actual' log file shows its size as 154,081,280 KB
>
BACKUP LOG <dbname> WITH TRUNCATEONLY
But not it'll invalidate your backup chain (which I guess doesn't exist.)
Once you do this, do a FULL database backup and then setup transaction
backups to run more often.
> when I run DBCC Shrinkfile it says:
> DbID: 12
> FileId: 2
> CurrentSize: 19260160
> MinimumSize: 640
> UsedPages: 19260160
> EstimatedPages: 640
> when I run this... the actual file size does not change.. and if I try to
> do
> a backup of the transaction log... the system runs and then fails...
> "PSPDBA" wrote:
>> How full is the log? Do you do any t-log backups at all? Its
>> possible you can just shrink it if the log is somewhat empty.
>> Otherwise, take a full backup, switch to simple recovery, shrink the
>> log, set back to Full and schedule your t-logs to be backed up on a
>> regular basis (we do our critical systems every 10 minutes, and our
>> important systems every two hours)
>> On Mar 16, 11:57 am, MSUTech <MSUT...@.discussions.microsoft.com>
>> wrote:
>> > I have a log.ldf file that has grown to around 150 GB ... I can't
>> > really do a
>> > BACKUP of the database to truncate it.... it seems to struggle with
>> > that...
>> >
>> > is there another way that I can MANUALLY truncate this log file?
>> >
>> > help..
>>
--
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com
BACKUP of the database to truncate it.... it seems to struggle with that...
is there another way that I can MANUALLY truncate this log file?
help..How full is the log? Do you do any t-log backups at all? Its
possible you can just shrink it if the log is somewhat empty.
Otherwise, take a full backup, switch to simple recovery, shrink the
log, set back to Full and schedule your t-logs to be backed up on a
regular basis (we do our critical systems every 10 minutes, and our
important systems every two hours)
On Mar 16, 11:57 am, MSUTech <MSUT...@.discussions.microsoft.com>
wrote:
> I have a log.ldf file that has grown to around 150 GB ... I can't really do a
> BACKUP of the database to truncate it.... it seems to struggle with that...
> is there another way that I can MANUALLY truncate this log file?
> help..|||Also: http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
news:1174062754.069322.283110@.y80g2000hsf.googlegroups.com...
> How full is the log? Do you do any t-log backups at all? Its
> possible you can just shrink it if the log is somewhat empty.
> Otherwise, take a full backup, switch to simple recovery, shrink the
> log, set back to Full and schedule your t-logs to be backed up on a
> regular basis (we do our critical systems every 10 minutes, and our
> important systems every two hours)
> On Mar 16, 11:57 am, MSUTech <MSUT...@.discussions.microsoft.com>
> wrote:
>> I have a log.ldf file that has grown to around 150 GB ... I can't really do a
>> BACKUP of the database to truncate it.... it seems to struggle with that...
>> is there another way that I can MANUALLY truncate this log file?
>> help..
>|||The 'actual' log file shows its size as 154,081,280 KB
when I run DBCC Shrinkfile it says:
DbID: 12
FileId: 2
CurrentSize: 19260160
MinimumSize: 640
UsedPages: 19260160
EstimatedPages: 640
when I run this... the actual file size does not change.. and if I try to do
a backup of the transaction log... the system runs and then fails...
"PSPDBA" wrote:
> How full is the log? Do you do any t-log backups at all? Its
> possible you can just shrink it if the log is somewhat empty.
> Otherwise, take a full backup, switch to simple recovery, shrink the
> log, set back to Full and schedule your t-logs to be backed up on a
> regular basis (we do our critical systems every 10 minutes, and our
> important systems every two hours)
> On Mar 16, 11:57 am, MSUTech <MSUT...@.discussions.microsoft.com>
> wrote:
> > I have a log.ldf file that has grown to around 150 GB ... I can't really do a
> > BACKUP of the database to truncate it.... it seems to struggle with that...
> >
> > is there another way that I can MANUALLY truncate this log file?
> >
> > help..
>
>|||"MSUTech" <MSUTech@.discussions.microsoft.com> wrote in message
news:4AE50DC8-86DF-40D6-8610-B3CD572D070B@.microsoft.com...
> The 'actual' log file shows its size as 154,081,280 KB
>
BACKUP LOG <dbname> WITH TRUNCATEONLY
But not it'll invalidate your backup chain (which I guess doesn't exist.)
Once you do this, do a FULL database backup and then setup transaction
backups to run more often.
> when I run DBCC Shrinkfile it says:
> DbID: 12
> FileId: 2
> CurrentSize: 19260160
> MinimumSize: 640
> UsedPages: 19260160
> EstimatedPages: 640
> when I run this... the actual file size does not change.. and if I try to
> do
> a backup of the transaction log... the system runs and then fails...
> "PSPDBA" wrote:
>> How full is the log? Do you do any t-log backups at all? Its
>> possible you can just shrink it if the log is somewhat empty.
>> Otherwise, take a full backup, switch to simple recovery, shrink the
>> log, set back to Full and schedule your t-logs to be backed up on a
>> regular basis (we do our critical systems every 10 minutes, and our
>> important systems every two hours)
>> On Mar 16, 11:57 am, MSUTech <MSUT...@.discussions.microsoft.com>
>> wrote:
>> > I have a log.ldf file that has grown to around 150 GB ... I can't
>> > really do a
>> > BACKUP of the database to truncate it.... it seems to struggle with
>> > that...
>> >
>> > is there another way that I can MANUALLY truncate this log file?
>> >
>> > help..
>>
--
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com
Subscribe to:
Posts (Atom)