Log free space question

I have tried all through enterprise manager and dbcc
shrinkfile but does not help it is not the datalog of the
log file getting big but the unused space in the log
files which needs to be truncated to a smaller size any
ideads

>--Original Message--
>Hi
>I am using sql server 7.0 and one of DB is relatively
>small. The size is about 2 MB however the log size is
>about 30GB the problems is the log used space is 38 MB
>but the log free space is about 37 GB so the whole
backup
>and restore takes some time as the toal og size is the
>sum of these two.
>I tried to shrink the file with truncate_only option but
>cant get it to a small size
>Can anyone help
>.
>Did you read the links that were posted? Have you checked to see if you
have any open transactions with DBCC OPENTRAN()? Do you have Truncate Log
on Checkpoint turned on?
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:021b01c4f787$1d1fb780$a601280a@.phx.gbl...[vbcol=seagreen]
>I have tried all through enterprise manager and dbcc
> shrinkfile but does not help it is not the datalog of the
> log file getting big but the unused space in the log
> files which needs to be truncated to a smaller size any
> ideads
>
> backup|||Yes i checked both and read all the articles but
does not solve it
basically it is something like this
transaction log space 2553.55
used: 43.25
unused:2510.3
so I am worried as how to get that unused spcae from log
file back and none of the soln seem to work.

>--Original Message--
>Did you read the links that were posted? Have you
checked to see if you
>have any open transactions with DBCC OPENTRAN()? Do you
have Truncate Log
>on Checkpoint turned on?
>--
>Andrew J. Kelly SQL MVP
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:021b01c4f787$1d1fb780$a601280a@.phx.gbl...
the[vbcol=seagreen]
but[vbcol=seagreen]
>
>.
>|||Did you have a look with DBCC LOGINFO to see where the active VLF is? If it
is at the end you can not shrink it until you get it wrapped towards the
beginning. One of those links has a script for 7.0 to do this.
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:161601c4f79e$6f5a9f80$a501280a@.phx.gbl...[vbcol=seagreen]
> Yes i checked both and read all the articles but
> does not solve it
> basically it is something like this
> transaction log space 2553.55
> used: 43.25
> unused:2510.3
> so I am worried as how to get that unused spcae from log
> file back and none of the soln seem to work.
>
>
> checked to see if you
> have Truncate Log
> the
> but

Log free space question

I have tried all through enterprise manager and dbcc
shrinkfile but does not help it is not the datalog of the
log file getting big but the unused space in the log
files which needs to be truncated to a smaller size any
ideads

>--Original Message--
>Hi
>I am using sql server 7.0 and one of DB is relatively
>small. The size is about 2 MB however the log size is
>about 30GB the problems is the log used space is 38 MB
>but the log free space is about 37 GB so the whole
backup
>and restore takes some time as the toal og size is the
>sum of these two.
>I tried to shrink the file with truncate_only option but
>cant get it to a small size
>Can anyone help
>.
>
Did you read the links that were posted? Have you checked to see if you
have any open transactions with DBCC OPENTRAN()? Do you have Truncate Log
on Checkpoint turned on?
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:021b01c4f787$1d1fb780$a601280a@.phx.gbl...[vbcol=seagreen]
>I have tried all through enterprise manager and dbcc
> shrinkfile but does not help it is not the datalog of the
> log file getting big but the unused space in the log
> files which needs to be truncated to a smaller size any
> ideads
>
> backup
|||Yes i checked both and read all the articles but
does not solve it
basically it is something like this
transaction log space 2553.55
used: 43.25
unused:2510.3
so I am worried as how to get that unused spcae from log
file back and none of the soln seem to work.

>--Original Message--
>Did you read the links that were posted? Have you
checked to see if you
>have any open transactions with DBCC OPENTRAN()? Do you
have Truncate Log[vbcol=seagreen]
>on Checkpoint turned on?
>--
>Andrew J. Kelly SQL MVP
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:021b01c4f787$1d1fb780$a601280a@.phx.gbl...
the[vbcol=seagreen]
but
>
>.
>
|||Did you have a look with DBCC LOGINFO to see where the active VLF is? If it
is at the end you can not shrink it until you get it wrapped towards the
beginning. One of those links has a script for 7.0 to do this.
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:161601c4f79e$6f5a9f80$a501280a@.phx.gbl...[vbcol=seagreen]
> Yes i checked both and read all the articles but
> does not solve it
> basically it is something like this
> transaction log space 2553.55
> used: 43.25
> unused:2510.3
> so I am worried as how to get that unused spcae from log
> file back and none of the soln seem to work.
>
> checked to see if you
> have Truncate Log
> the
> but
sql

Log

I backup the sql data file(log size is 30GB(30GB is used, 0 is unused).) I
think i just the data only, not include log.
Then i get that backup data file to the other sql server machine to restone
once i finish to restore, the log size is also restored to 30GB as well. but
(1G used, 29 unused)
why will have this result?Restore of a full db backup restores all files as they were present at the
time of the backup. If you want to shring log, use DBCC SHRINKFILE command.
Do please llok for the syntax in Books OnLine.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Inamori" <test@.test.com> wrote in message
news:ue9BS5OsEHA.2612@.TK2MSFTNGP10.phx.gbl...
> I backup the sql data file(log size is 30GB(30GB is used, 0 is unused).) I
> think i just the data only, not include log.
> Then i get that backup data file to the other sql server machine to
restone
> once i finish to restore, the log size is also restored to 30GB as well.
but
> (1G used, 29 unused)
> why will have this result?
>
>|||the point is orginial is 30GB(30GB is used, 0 is unused
new restore one is (1G used, 29 unused)
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:#2tasAPsEHA.3564@.tk2msftngp13.phx.gbl...
> Restore of a full db backup restores all files as they were present at the
> time of the backup. If you want to shring log, use DBCC SHRINKFILE
command.
> Do please llok for the syntax in Books OnLine.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
> "Inamori" <test@.test.com> wrote in message
> news:ue9BS5OsEHA.2612@.TK2MSFTNGP10.phx.gbl...
I[vbcol=seagreen]
> restone
> but
>|||> the point is orginial is 30GB(30GB is used, 0 is unused
> new restore one is (1G used, 29 unused)
Did you use Enterprise Manager t do the full db backup? If you llok at the
Options tab when using EM backup, you will see by default a checked checkbox
near the "Remove inactive entries from transaction log" option.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com|||thanks thanks
I got it...
yes it is checked
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:eyNTNLPsEHA.2664@.TK2MSFTNGP12.phx.gbl...
> Did you use Enterprise Manager t do the full db backup? If you llok at the
> Options tab when using EM backup, you will see by default a checked
checkbox
> near the "Remove inactive entries from transaction log" option.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
>

Log

I backup the sql data file(log size is 30GB(30GB is used, 0 is unused).) I
think i just the data only, not include log.
Then i get that backup data file to the other sql server machine to restone
once i finish to restore, the log size is also restored to 30GB as well. but
(1G used, 29 unused)
why will have this result?Restore of a full db backup restores all files as they were present at the
time of the backup. If you want to shring log, use DBCC SHRINKFILE command.
Do please llok for the syntax in Books OnLine.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Inamori" <test@.test.com> wrote in message
news:ue9BS5OsEHA.2612@.TK2MSFTNGP10.phx.gbl...
> I backup the sql data file(log size is 30GB(30GB is used, 0 is unused).) I
> think i just the data only, not include log.
> Then i get that backup data file to the other sql server machine to
restone
> once i finish to restore, the log size is also restored to 30GB as well.
but
> (1G used, 29 unused)
> why will have this result?
>
>|||the point is orginial is 30GB(30GB is used, 0 is unused
new restore one is (1G used, 29 unused)
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:#2tasAPsEHA.3564@.tk2msftngp13.phx.gbl...
> Restore of a full db backup restores all files as they were present at the
> time of the backup. If you want to shring log, use DBCC SHRINKFILE
command.
> Do please llok for the syntax in Books OnLine.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
> "Inamori" <test@.test.com> wrote in message
> news:ue9BS5OsEHA.2612@.TK2MSFTNGP10.phx.gbl...
> > I backup the sql data file(log size is 30GB(30GB is used, 0 is unused).)
I
> > think i just the data only, not include log.
> >
> > Then i get that backup data file to the other sql server machine to
> restone
> >
> > once i finish to restore, the log size is also restored to 30GB as well.
> but
> > (1G used, 29 unused)
> >
> > why will have this result?
> >
> >
> >
> >
>|||> the point is orginial is 30GB(30GB is used, 0 is unused
> new restore one is (1G used, 29 unused)
Did you use Enterprise Manager t do the full db backup? If you llok at the
Options tab when using EM backup, you will see by default a checked checkbox
near the "Remove inactive entries from transaction log" option.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com|||thanks thanks
I got it...
yes it is checked
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:eyNTNLPsEHA.2664@.TK2MSFTNGP12.phx.gbl...
> > the point is orginial is 30GB(30GB is used, 0 is unused
> >
> > new restore one is (1G used, 29 unused)
> Did you use Enterprise Manager t do the full db backup? If you llok at the
> Options tab when using EM backup, you will see by default a checked
checkbox
> near the "Remove inactive entries from transaction log" option.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
>

Log

I backup the sql data file(log size is 30GB(30GB is used, 0 is unused).) I
think i just the data only, not include log.
Then i get that backup data file to the other sql server machine to restone
once i finish to restore, the log size is also restored to 30GB as well. but
(1G used, 29 unused)
why will have this result?
Restore of a full db backup restores all files as they were present at the
time of the backup. If you want to shring log, use DBCC SHRINKFILE command.
Do please llok for the syntax in Books OnLine.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Inamori" <test@.test.com> wrote in message
news:ue9BS5OsEHA.2612@.TK2MSFTNGP10.phx.gbl...
> I backup the sql data file(log size is 30GB(30GB is used, 0 is unused).) I
> think i just the data only, not include log.
> Then i get that backup data file to the other sql server machine to
restone
> once i finish to restore, the log size is also restored to 30GB as well.
but
> (1G used, 29 unused)
> why will have this result?
>
>
|||the point is orginial is 30GB(30GB is used, 0 is unused
new restore one is (1G used, 29 unused)
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in
message news:#2tasAPsEHA.3564@.tk2msftngp13.phx.gbl...
> Restore of a full db backup restores all files as they were present at the
> time of the backup. If you want to shring log, use DBCC SHRINKFILE
command.[vbcol=seagreen]
> Do please llok for the syntax in Books OnLine.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
> "Inamori" <test@.test.com> wrote in message
> news:ue9BS5OsEHA.2612@.TK2MSFTNGP10.phx.gbl...
I
> restone
> but
>
|||> the point is orginial is 30GB(30GB is used, 0 is unused
> new restore one is (1G used, 29 unused)
Did you use Enterprise Manager t do the full db backup? If you llok at the
Options tab when using EM backup, you will see by default a checked checkbox
near the "Remove inactive entries from transaction log" option.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
|||thanks thanks
I got it...
yes it is checked
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in
message news:eyNTNLPsEHA.2664@.TK2MSFTNGP12.phx.gbl...
> Did you use Enterprise Manager t do the full db backup? If you llok at the
> Options tab when using EM backup, you will see by default a checked
checkbox
> near the "Remove inactive entries from transaction log" option.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
>