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

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

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

Managing a Large Log File

Good Afternoon,
We have a particular database whose log file has grown particularly large on
one of our SQL servers (SQL 2000). The database is around 30 GB and the log
file is over 50 GB. I would like to accomplish 2 things and am looking for
any recommendations. First, I would like to get the log file down to a
reasonable size and secondly I would like to keep the log file from growing
this enormous again in the future. A co-worker dealt with a situation like
this in the past by taking the following steps:
-Detach the SQL database
-Rename, move or delete the existing log file
-Re-attach the database. At this point you will be notified that the log
file cannot be found and will be prompted to generate a new one.
-Set the recovery mode to Simple (as opposed to full) to prevent the log
file from growing this large again
Is this a reliable way of correcting this issue or is there a "best
practice" for performing something like this? Could any potential problems
arise from following the steps above? Is there a danger in setting the
recovery mode to Simple as opposed to Full? Any information anyone has
regarding how I can shrink the log file and prevent it from growing in the
future (but still be able to restore in the event of an emergency) would be
greatly appreciated.
Thanks!
-Shane Diaz
> -Detach the SQL database
> -Rename, move or delete the existing log file
> -Re-attach the database. At this point you will be notified that the log
> file cannot be found and will be prompted to generate a new one.
> Is this a reliable way of correcting this issue
I would never do that. I don't trust SQL Server creating a log file. I recommend that you:
Backup the log to the filename 'nul' (I assume you don't want the log backups).
DBCC SHRINKFILE the log file
Investigate the virtual log file layout (DBCC LOGINFO)
Repeat above steps two or three times until the log is at desired size.
Now set the recovery model to simple. Again, assuming you don't want to do log backups.
See http://www.karaszi.com/SQLServer/info_dont_shrink.asp for some details about log shrinking.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"shanediaz" <shanediaz@.discussions.microsoft.com> wrote in message
news:83B39C51-D0E5-48CF-B9A6-DD4E9256A877@.microsoft.com...
> Good Afternoon,
> We have a particular database whose log file has grown particularly large on
> one of our SQL servers (SQL 2000). The database is around 30 GB and the log
> file is over 50 GB. I would like to accomplish 2 things and am looking for
> any recommendations. First, I would like to get the log file down to a
> reasonable size and secondly I would like to keep the log file from growing
> this enormous again in the future. A co-worker dealt with a situation like
> this in the past by taking the following steps:
> -Detach the SQL database
> -Rename, move or delete the existing log file
> -Re-attach the database. At this point you will be notified that the log
> file cannot be found and will be prompted to generate a new one.
> -Set the recovery mode to Simple (as opposed to full) to prevent the log
> file from growing this large again
> Is this a reliable way of correcting this issue or is there a "best
> practice" for performing something like this? Could any potential problems
> arise from following the steps above? Is there a danger in setting the
> recovery mode to Simple as opposed to Full? Any information anyone has
> regarding how I can shrink the log file and prevent it from growing in the
> future (but still be able to restore in the event of an emergency) would be
> greatly appreciated.
> Thanks!
> -Shane Diaz

Managing a Large Log File

Good Afternoon,
We have a particular database whose log file has grown particularly large on
one of our SQL servers (SQL 2000). The database is around 30 GB and the log
file is over 50 GB. I would like to accomplish 2 things and am looking for
any recommendations. First, I would like to get the log file down to a
reasonable size and secondly I would like to keep the log file from growing
this enormous again in the future. A co-worker dealt with a situation like
this in the past by taking the following steps:
-Detach the SQL database
-Rename, move or delete the existing log file
-Re-attach the database. At this point you will be notified that the log
file cannot be found and will be prompted to generate a new one.
-Set the recovery mode to Simple (as opposed to full) to prevent the log
file from growing this large again
Is this a reliable way of correcting this issue or is there a "best
practice" for performing something like this? Could any potential problems
arise from following the steps above? Is there a danger in setting the
recovery mode to Simple as opposed to Full? Any information anyone has
regarding how I can shrink the log file and prevent it from growing in the
future (but still be able to restore in the event of an emergency) would be
greatly appreciated.
Thanks!
-Shane Diaz> -Detach the SQL database
> -Rename, move or delete the existing log file
> -Re-attach the database. At this point you will be notified that the log
> file cannot be found and will be prompted to generate a new one.
> Is this a reliable way of correcting this issue
I would never do that. I don't trust SQL Server creating a log file. I recommend that you:
Backup the log to the filename 'nul' (I assume you don't want the log backups).
DBCC SHRINKFILE the log file
Investigate the virtual log file layout (DBCC LOGINFO)
Repeat above steps two or three times until the log is at desired size.
Now set the recovery model to simple. Again, assuming you don't want to do log backups.
See http://www.karaszi.com/SQLServer/info_dont_shrink.asp for some details about log shrinking.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"shanediaz" <shanediaz@.discussions.microsoft.com> wrote in message
news:83B39C51-D0E5-48CF-B9A6-DD4E9256A877@.microsoft.com...
> Good Afternoon,
> We have a particular database whose log file has grown particularly large on
> one of our SQL servers (SQL 2000). The database is around 30 GB and the log
> file is over 50 GB. I would like to accomplish 2 things and am looking for
> any recommendations. First, I would like to get the log file down to a
> reasonable size and secondly I would like to keep the log file from growing
> this enormous again in the future. A co-worker dealt with a situation like
> this in the past by taking the following steps:
> -Detach the SQL database
> -Rename, move or delete the existing log file
> -Re-attach the database. At this point you will be notified that the log
> file cannot be found and will be prompted to generate a new one.
> -Set the recovery mode to Simple (as opposed to full) to prevent the log
> file from growing this large again
> Is this a reliable way of correcting this issue or is there a "best
> practice" for performing something like this? Could any potential problems
> arise from following the steps above? Is there a danger in setting the
> recovery mode to Simple as opposed to Full? Any information anyone has
> regarding how I can shrink the log file and prevent it from growing in the
> future (but still be able to restore in the event of an emergency) would be
> greatly appreciated.
> Thanks!
> -Shane Diaz

Managing a Large Log File

Good Afternoon,
We have a particular database whose log file has grown particularly large on
one of our SQL servers (SQL 2000). The database is around 30 GB and the log
file is over 50 GB. I would like to accomplish 2 things and am looking for
any recommendations. First, I would like to get the log file down to a
reasonable size and secondly I would like to keep the log file from growing
this enormous again in the future. A co-worker dealt with a situation like
this in the past by taking the following steps:
-Detach the SQL database
-Rename, move or delete the existing log file
-Re-attach the database. At this point you will be notified that the log
file cannot be found and will be prompted to generate a new one.
-Set the recovery mode to Simple (as opposed to full) to prevent the log
file from growing this large again
Is this a reliable way of correcting this issue or is there a "best
practice" for performing something like this? Could any potential problems
arise from following the steps above? Is there a danger in setting the
recovery mode to Simple as opposed to Full? Any information anyone has
regarding how I can shrink the log file and prevent it from growing in the
future (but still be able to restore in the event of an emergency) would be
greatly appreciated.
Thanks!
-Shane Diaz> -Detach the SQL database
> -Rename, move or delete the existing log file
> -Re-attach the database. At this point you will be notified that the log
> file cannot be found and will be prompted to generate a new one.
> Is this a reliable way of correcting this issue
I would never do that. I don't trust SQL Server creating a log file. I recom
mend that you:
Backup the log to the filename 'nul' (I assume you don't want the log backup
s).
DBCC SHRINKFILE the log file
Investigate the virtual log file layout (DBCC LOGINFO)
Repeat above steps two or three times until the log is at desired size.
Now set the recovery model to simple. Again, assuming you don't want to do l
og backups.
See http://www.karaszi.com/SQLServer/info_dont_shrink.asp for some details a
bout log shrinking.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"shanediaz" <shanediaz@.discussions.microsoft.com> wrote in message
news:83B39C51-D0E5-48CF-B9A6-DD4E9256A877@.microsoft.com...
> Good Afternoon,
> We have a particular database whose log file has grown particularly large
on
> one of our SQL servers (SQL 2000). The database is around 30 GB and the l
og
> file is over 50 GB. I would like to accomplish 2 things and am looking fo
r
> any recommendations. First, I would like to get the log file down to a
> reasonable size and secondly I would like to keep the log file from growin
g
> this enormous again in the future. A co-worker dealt with a situation lik
e
> this in the past by taking the following steps:
> -Detach the SQL database
> -Rename, move or delete the existing log file
> -Re-attach the database. At this point you will be notified that the log
> file cannot be found and will be prompted to generate a new one.
> -Set the recovery mode to Simple (as opposed to full) to prevent the log
> file from growing this large again
> Is this a reliable way of correcting this issue or is there a "best
> practice" for performing something like this? Could any potential problem
s
> arise from following the steps above? Is there a danger in setting the
> recovery mode to Simple as opposed to Full? Any information anyone has
> regarding how I can shrink the log file and prevent it from growing in the
> future (but still be able to restore in the event of an emergency) would b
e
> greatly appreciated.
> Thanks!
> -Shane Diaz