Hi ,
i was trying to shrink a database but i got the following error :
it's something like " logical file is in use" despite me logging in as a
single user
the statement i used : DBCC Shrinkdatabase ('dbname', 1 , truncate_only)
i have also used the EM's shrink database , though it says it completed
successfully but it did not shrink the database
kindly advise
tks & rdgsHi
What is the Error Number and Severity?
You might be getting an informational message to tell you that the DB can't
be shunk any smaller than it already is.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"maxzsim" wrote:
> Hi ,
> i was trying to shrink a database but i got the following error :
> it's something like " logical file is in use" despite me logging in as a
> single user
> the statement i used : DBCC Shrinkdatabase ('dbname', 1 , truncate_only)
> i have also used the EM's shrink database , though it says it completed
> successfully but it did not shrink the database
> kindly advise
> tks & rdgs|||Hi,
Post the exact error.
I feel that Probably there will be some active transactions. Execute DBCC
OPENTRAN('DBNAME') to identify if there is any open active trans.
If yes, wait that to complete. After that you could execute a backup LOG to
clear the transaction log and then execute DBCC SHRINKFILE to
shrink MDF and LDF seperately.
Thanks
Hari
SQL Server MVP
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:BFE5D0BA-D797-40CE-899B-246D6BA039D4@.microsoft.com...
> Hi ,
> i was trying to shrink a database but i got the following error :
> it's something like " logical file is in use" despite me logging in as a
> single user
> the statement i used : DBCC Shrinkdatabase ('dbname', 1 , truncate_only)
> i have also used the EM's shrink database , though it says it completed
> successfully but it did not shrink the database
> kindly advise
> tks & rdgs|||Hi ,
before doing the shrink database , have already did a txn log bakup and it
says 4GB is free, though the log file is still occupying 5 GB
so thought of shrinking the database/log file and return the free space back
to OS
tks & rdgs
"Hari Prasad" wrote:
> Hi,
> Post the exact error.
> I feel that Probably there will be some active transactions. Execute DBCC
> OPENTRAN('DBNAME') to identify if there is any open active trans.
> If yes, wait that to complete. After that you could execute a backup LOG t
o
> clear the transaction log and then execute DBCC SHRINKFILE to
> shrink MDF and LDF seperately.
> Thanks
> Hari
> SQL Server MVP
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:BFE5D0BA-D797-40CE-899B-246D6BA039D4@.microsoft.com...
>
>|||Hi ,
I have done a txn log backup file earlier and it says that 4GB is free
tks & rdgs
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> What is the Error Number and Severity?
> You might be getting an informational message to tell you that the DB can'
t
> be shunk any smaller than it already is.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "maxzsim" wrote:
>|||Hi
It all depends on where the "Virtual Log"/"Active Portion of the Log" is in
relation to the end of the log file.
The Active Portion may be close to the end of the file at the moment, so the
log can not be shrunk. Once the Active Portion is at the beginning of the lo
g
file, then the log can be truncated to where it is.
Look at "virtual log files" in BOL.
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"maxzsim" wrote:
[vbcol=seagreen]
> Hi ,
> before doing the shrink database , have already did a txn log bakup and i
t
> says 4GB is free, though the log file is still occupying 5 GB
> so thought of shrinking the database/log file and return the free space ba
ck
> to OS
> tks & rdgs
> "Hari Prasad" wrote:
>|||Are you trying to shrink the db file or the log file? Sounds like the log
file. You should use DBCC SHRINKFILE not database as it allows you to
choose the specific file you want to shrink and how much.
Andrew J. Kelly SQL MVP
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:BFE5D0BA-D797-40CE-899B-246D6BA039D4@.microsoft.com...
> Hi ,
> i was trying to shrink a database but i got the following error :
> it's something like " logical file is in use" despite me logging in as a
> single user
> the statement i used : DBCC Shrinkdatabase ('dbname', 1 , truncate_only)
> i have also used the EM's shrink database , though it says it completed
> successfully but it did not shrink the database
> kindly advise
> tks & rdgs|||Hi Andrew ,
i was trying to shrink the log file but it couldn't as per the error below
and i have also tried to shrink the database as well , hoping it'll shrink
the log file
tks & rdgs
"Andrew J. Kelly" wrote:
> Are you trying to shrink the db file or the log file? Sounds like the log
> file. You should use DBCC SHRINKFILE not database as it allows you to
> choose the specific file you want to shrink and how much.
> --
> Andrew J. Kelly SQL MVP
>
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:BFE5D0BA-D797-40CE-899B-246D6BA039D4@.microsoft.com...
>
>|||Did you actually try DBCC SHRINKFILE? If not you should. You don't need to
be in single user mode to run this.
Andrew J. Kelly SQL MVP
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:A84212A5-E2B0-4CF4-9F03-D4B84FCC4E59@.microsoft.com...[vbcol=seagreen]
> Hi Andrew ,
> i was trying to shrink the log file but it couldn't as per the error
> below
> and i have also tried to shrink the database as well , hoping it'll shrink
> the log file
> tks & rdgs
> "Andrew J. Kelly" wrote:
>|||Hi Andrew ,
i did tried with the DBCC shrink file statement but got the "logical log in
use" error as per the DBCC Shrinkdatabase. however, when i changed the mode
to "Simple" recovery mode , i could somehow shrink the db
tks & rdgs
"Andrew J. Kelly" wrote:
> Did you actually try DBCC SHRINKFILE? If not you should. You don't need
to
> be in single user mode to run this.
> --
> Andrew J. Kelly SQL MVP
>
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:A84212A5-E2B0-4CF4-9F03-D4B84FCC4E59@.microsoft.com...
>
>
Showing posts with label logging. Show all posts
Showing posts with label logging. Show all posts
Tuesday, March 27, 2012
dbcc shrinkdatabase error
Hi ,
i was trying to shrink a database but i got the following error :
it's something like " logical file is in use" despite me logging in as a
single user
the statement i used : DBCC Shrinkdatabase ('dbname', 1 , truncate_only)
i have also used the EM's shrink database , though it says it completed
successfully but it did not shrink the database
kindly advise
tks & rdgsHi
What is the Error Number and Severity?
You might be getting an informational message to tell you that the DB can't
be shunk any smaller than it already is.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"maxzsim" wrote:
> Hi ,
> i was trying to shrink a database but i got the following error :
> it's something like " logical file is in use" despite me logging in as a
> single user
> the statement i used : DBCC Shrinkdatabase ('dbname', 1 , truncate_only)
> i have also used the EM's shrink database , though it says it completed
> successfully but it did not shrink the database
> kindly advise
> tks & rdgs|||Hi,
Post the exact error.
I feel that Probably there will be some active transactions. Execute DBCC
OPENTRAN('DBNAME') to identify if there is any open active trans.
If yes, wait that to complete. After that you could execute a backup LOG to
clear the transaction log and then execute DBCC SHRINKFILE to
shrink MDF and LDF seperately.
Thanks
Hari
SQL Server MVP
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:BFE5D0BA-D797-40CE-899B-246D6BA039D4@.microsoft.com...
> Hi ,
> i was trying to shrink a database but i got the following error :
> it's something like " logical file is in use" despite me logging in as a
> single user
> the statement i used : DBCC Shrinkdatabase ('dbname', 1 , truncate_only)
> i have also used the EM's shrink database , though it says it completed
> successfully but it did not shrink the database
> kindly advise
> tks & rdgs|||Hi ,
before doing the shrink database , have already did a txn log bakup and it
says 4GB is free, though the log file is still occupying 5 GB
so thought of shrinking the database/log file and return the free space back
to OS
tks & rdgs
"Hari Prasad" wrote:
> Hi,
> Post the exact error.
> I feel that Probably there will be some active transactions. Execute DBCC
> OPENTRAN('DBNAME') to identify if there is any open active trans.
> If yes, wait that to complete. After that you could execute a backup LOG to
> clear the transaction log and then execute DBCC SHRINKFILE to
> shrink MDF and LDF seperately.
> Thanks
> Hari
> SQL Server MVP
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:BFE5D0BA-D797-40CE-899B-246D6BA039D4@.microsoft.com...
> > Hi ,
> >
> > i was trying to shrink a database but i got the following error :
> > it's something like " logical file is in use" despite me logging in as a
> > single user
> >
> > the statement i used : DBCC Shrinkdatabase ('dbname', 1 , truncate_only)
> >
> > i have also used the EM's shrink database , though it says it completed
> > successfully but it did not shrink the database
> >
> > kindly advise
> > tks & rdgs
>
>|||Hi ,
I have done a txn log backup file earlier and it says that 4GB is free
tks & rdgs
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> What is the Error Number and Severity?
> You might be getting an informational message to tell you that the DB can't
> be shunk any smaller than it already is.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "maxzsim" wrote:
> > Hi ,
> >
> > i was trying to shrink a database but i got the following error :
> > it's something like " logical file is in use" despite me logging in as a
> > single user
> >
> > the statement i used : DBCC Shrinkdatabase ('dbname', 1 , truncate_only)
> >
> > i have also used the EM's shrink database , though it says it completed
> > successfully but it did not shrink the database
> >
> > kindly advise
> > tks & rdgs|||Hi
It all depends on where the "Virtual Log"/"Active Portion of the Log" is in
relation to the end of the log file.
The Active Portion may be close to the end of the file at the moment, so the
log can not be shrunk. Once the Active Portion is at the beginning of the log
file, then the log can be truncated to where it is.
Look at "virtual log files" in BOL.
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"maxzsim" wrote:
> Hi ,
> before doing the shrink database , have already did a txn log bakup and it
> says 4GB is free, though the log file is still occupying 5 GB
> so thought of shrinking the database/log file and return the free space back
> to OS
> tks & rdgs
> "Hari Prasad" wrote:
> > Hi,
> >
> > Post the exact error.
> >
> > I feel that Probably there will be some active transactions. Execute DBCC
> > OPENTRAN('DBNAME') to identify if there is any open active trans.
> > If yes, wait that to complete. After that you could execute a backup LOG to
> > clear the transaction log and then execute DBCC SHRINKFILE to
> > shrink MDF and LDF seperately.
> >
> > Thanks
> > Hari
> > SQL Server MVP
> >
> > "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> > news:BFE5D0BA-D797-40CE-899B-246D6BA039D4@.microsoft.com...
> > > Hi ,
> > >
> > > i was trying to shrink a database but i got the following error :
> > > it's something like " logical file is in use" despite me logging in as a
> > > single user
> > >
> > > the statement i used : DBCC Shrinkdatabase ('dbname', 1 , truncate_only)
> > >
> > > i have also used the EM's shrink database , though it says it completed
> > > successfully but it did not shrink the database
> > >
> > > kindly advise
> > > tks & rdgs
> >
> >
> >|||Are you trying to shrink the db file or the log file? Sounds like the log
file. You should use DBCC SHRINKFILE not database as it allows you to
choose the specific file you want to shrink and how much.
--
Andrew J. Kelly SQL MVP
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:BFE5D0BA-D797-40CE-899B-246D6BA039D4@.microsoft.com...
> Hi ,
> i was trying to shrink a database but i got the following error :
> it's something like " logical file is in use" despite me logging in as a
> single user
> the statement i used : DBCC Shrinkdatabase ('dbname', 1 , truncate_only)
> i have also used the EM's shrink database , though it says it completed
> successfully but it did not shrink the database
> kindly advise
> tks & rdgs|||Hi Andrew ,
i was trying to shrink the log file but it couldn't as per the error below
and i have also tried to shrink the database as well , hoping it'll shrink
the log file
tks & rdgs
"Andrew J. Kelly" wrote:
> Are you trying to shrink the db file or the log file? Sounds like the log
> file. You should use DBCC SHRINKFILE not database as it allows you to
> choose the specific file you want to shrink and how much.
> --
> Andrew J. Kelly SQL MVP
>
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:BFE5D0BA-D797-40CE-899B-246D6BA039D4@.microsoft.com...
> > Hi ,
> >
> > i was trying to shrink a database but i got the following error :
> > it's something like " logical file is in use" despite me logging in as a
> > single user
> >
> > the statement i used : DBCC Shrinkdatabase ('dbname', 1 , truncate_only)
> >
> > i have also used the EM's shrink database , though it says it completed
> > successfully but it did not shrink the database
> >
> > kindly advise
> > tks & rdgs
>
>|||Did you actually try DBCC SHRINKFILE? If not you should. You don't need to
be in single user mode to run this.
--
Andrew J. Kelly SQL MVP
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:A84212A5-E2B0-4CF4-9F03-D4B84FCC4E59@.microsoft.com...
> Hi Andrew ,
> i was trying to shrink the log file but it couldn't as per the error
> below
> and i have also tried to shrink the database as well , hoping it'll shrink
> the log file
> tks & rdgs
> "Andrew J. Kelly" wrote:
>> Are you trying to shrink the db file or the log file? Sounds like the
>> log
>> file. You should use DBCC SHRINKFILE not database as it allows you to
>> choose the specific file you want to shrink and how much.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
>> news:BFE5D0BA-D797-40CE-899B-246D6BA039D4@.microsoft.com...
>> > Hi ,
>> >
>> > i was trying to shrink a database but i got the following error :
>> > it's something like " logical file is in use" despite me logging in as
>> > a
>> > single user
>> >
>> > the statement i used : DBCC Shrinkdatabase ('dbname', 1 ,
>> > truncate_only)
>> >
>> > i have also used the EM's shrink database , though it says it completed
>> > successfully but it did not shrink the database
>> >
>> > kindly advise
>> > tks & rdgs
>>|||Hi Andrew ,
i did tried with the DBCC shrink file statement but got the "logical log in
use" error as per the DBCC Shrinkdatabase. however, when i changed the mode
to "Simple" recovery mode , i could somehow shrink the db
tks & rdgs
"Andrew J. Kelly" wrote:
> Did you actually try DBCC SHRINKFILE? If not you should. You don't need to
> be in single user mode to run this.
> --
> Andrew J. Kelly SQL MVP
>
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:A84212A5-E2B0-4CF4-9F03-D4B84FCC4E59@.microsoft.com...
> > Hi Andrew ,
> >
> > i was trying to shrink the log file but it couldn't as per the error
> > below
> > and i have also tried to shrink the database as well , hoping it'll shrink
> > the log file
> >
> > tks & rdgs
> >
> > "Andrew J. Kelly" wrote:
> >
> >> Are you trying to shrink the db file or the log file? Sounds like the
> >> log
> >> file. You should use DBCC SHRINKFILE not database as it allows you to
> >> choose the specific file you want to shrink and how much.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> >> news:BFE5D0BA-D797-40CE-899B-246D6BA039D4@.microsoft.com...
> >> > Hi ,
> >> >
> >> > i was trying to shrink a database but i got the following error :
> >> > it's something like " logical file is in use" despite me logging in as
> >> > a
> >> > single user
> >> >
> >> > the statement i used : DBCC Shrinkdatabase ('dbname', 1 ,
> >> > truncate_only)
> >> >
> >> > i have also used the EM's shrink database , though it says it completed
> >> > successfully but it did not shrink the database
> >> >
> >> > kindly advise
> >> > tks & rdgs
> >>
> >>
> >>
>
>|||This is because the recovery mode you were in was preventing the log being
truncated until you'd backed it up. Switching to simple mode removes that
restriction. See BOL for more info ('Shrinking the Transaction Log' is a
good place to start)
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:65ADA556-3489-4620-844E-CC0DCB0BDF3A@.microsoft.com...
> Hi Andrew ,
> i did tried with the DBCC shrink file statement but got the "logical log
> in
> use" error as per the DBCC Shrinkdatabase. however, when i changed the
> mode
> to "Simple" recovery mode , i could somehow shrink the db
> tks & rdgs
> "Andrew J. Kelly" wrote:
>> Did you actually try DBCC SHRINKFILE? If not you should. You don't need
>> to
>> be in single user mode to run this.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
>> news:A84212A5-E2B0-4CF4-9F03-D4B84FCC4E59@.microsoft.com...
>> > Hi Andrew ,
>> >
>> > i was trying to shrink the log file but it couldn't as per the error
>> > below
>> > and i have also tried to shrink the database as well , hoping it'll
>> > shrink
>> > the log file
>> >
>> > tks & rdgs
>> >
>> > "Andrew J. Kelly" wrote:
>> >
>> >> Are you trying to shrink the db file or the log file? Sounds like the
>> >> log
>> >> file. You should use DBCC SHRINKFILE not database as it allows you to
>> >> choose the specific file you want to shrink and how much.
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >>
>> >>
>> >> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
>> >> news:BFE5D0BA-D797-40CE-899B-246D6BA039D4@.microsoft.com...
>> >> > Hi ,
>> >> >
>> >> > i was trying to shrink a database but i got the following error :
>> >> > it's something like " logical file is in use" despite me logging in
>> >> > as
>> >> > a
>> >> > single user
>> >> >
>> >> > the statement i used : DBCC Shrinkdatabase ('dbname', 1 ,
>> >> > truncate_only)
>> >> >
>> >> > i have also used the EM's shrink database , though it says it
>> >> > completed
>> >> > successfully but it did not shrink the database
>> >> >
>> >> > kindly advise
>> >> > tks & rdgs
>> >>
>> >>
>> >>
>>
i was trying to shrink a database but i got the following error :
it's something like " logical file is in use" despite me logging in as a
single user
the statement i used : DBCC Shrinkdatabase ('dbname', 1 , truncate_only)
i have also used the EM's shrink database , though it says it completed
successfully but it did not shrink the database
kindly advise
tks & rdgsHi
What is the Error Number and Severity?
You might be getting an informational message to tell you that the DB can't
be shunk any smaller than it already is.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"maxzsim" wrote:
> Hi ,
> i was trying to shrink a database but i got the following error :
> it's something like " logical file is in use" despite me logging in as a
> single user
> the statement i used : DBCC Shrinkdatabase ('dbname', 1 , truncate_only)
> i have also used the EM's shrink database , though it says it completed
> successfully but it did not shrink the database
> kindly advise
> tks & rdgs|||Hi,
Post the exact error.
I feel that Probably there will be some active transactions. Execute DBCC
OPENTRAN('DBNAME') to identify if there is any open active trans.
If yes, wait that to complete. After that you could execute a backup LOG to
clear the transaction log and then execute DBCC SHRINKFILE to
shrink MDF and LDF seperately.
Thanks
Hari
SQL Server MVP
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:BFE5D0BA-D797-40CE-899B-246D6BA039D4@.microsoft.com...
> Hi ,
> i was trying to shrink a database but i got the following error :
> it's something like " logical file is in use" despite me logging in as a
> single user
> the statement i used : DBCC Shrinkdatabase ('dbname', 1 , truncate_only)
> i have also used the EM's shrink database , though it says it completed
> successfully but it did not shrink the database
> kindly advise
> tks & rdgs|||Hi ,
before doing the shrink database , have already did a txn log bakup and it
says 4GB is free, though the log file is still occupying 5 GB
so thought of shrinking the database/log file and return the free space back
to OS
tks & rdgs
"Hari Prasad" wrote:
> Hi,
> Post the exact error.
> I feel that Probably there will be some active transactions. Execute DBCC
> OPENTRAN('DBNAME') to identify if there is any open active trans.
> If yes, wait that to complete. After that you could execute a backup LOG to
> clear the transaction log and then execute DBCC SHRINKFILE to
> shrink MDF and LDF seperately.
> Thanks
> Hari
> SQL Server MVP
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:BFE5D0BA-D797-40CE-899B-246D6BA039D4@.microsoft.com...
> > Hi ,
> >
> > i was trying to shrink a database but i got the following error :
> > it's something like " logical file is in use" despite me logging in as a
> > single user
> >
> > the statement i used : DBCC Shrinkdatabase ('dbname', 1 , truncate_only)
> >
> > i have also used the EM's shrink database , though it says it completed
> > successfully but it did not shrink the database
> >
> > kindly advise
> > tks & rdgs
>
>|||Hi ,
I have done a txn log backup file earlier and it says that 4GB is free
tks & rdgs
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> What is the Error Number and Severity?
> You might be getting an informational message to tell you that the DB can't
> be shunk any smaller than it already is.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "maxzsim" wrote:
> > Hi ,
> >
> > i was trying to shrink a database but i got the following error :
> > it's something like " logical file is in use" despite me logging in as a
> > single user
> >
> > the statement i used : DBCC Shrinkdatabase ('dbname', 1 , truncate_only)
> >
> > i have also used the EM's shrink database , though it says it completed
> > successfully but it did not shrink the database
> >
> > kindly advise
> > tks & rdgs|||Hi
It all depends on where the "Virtual Log"/"Active Portion of the Log" is in
relation to the end of the log file.
The Active Portion may be close to the end of the file at the moment, so the
log can not be shrunk. Once the Active Portion is at the beginning of the log
file, then the log can be truncated to where it is.
Look at "virtual log files" in BOL.
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"maxzsim" wrote:
> Hi ,
> before doing the shrink database , have already did a txn log bakup and it
> says 4GB is free, though the log file is still occupying 5 GB
> so thought of shrinking the database/log file and return the free space back
> to OS
> tks & rdgs
> "Hari Prasad" wrote:
> > Hi,
> >
> > Post the exact error.
> >
> > I feel that Probably there will be some active transactions. Execute DBCC
> > OPENTRAN('DBNAME') to identify if there is any open active trans.
> > If yes, wait that to complete. After that you could execute a backup LOG to
> > clear the transaction log and then execute DBCC SHRINKFILE to
> > shrink MDF and LDF seperately.
> >
> > Thanks
> > Hari
> > SQL Server MVP
> >
> > "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> > news:BFE5D0BA-D797-40CE-899B-246D6BA039D4@.microsoft.com...
> > > Hi ,
> > >
> > > i was trying to shrink a database but i got the following error :
> > > it's something like " logical file is in use" despite me logging in as a
> > > single user
> > >
> > > the statement i used : DBCC Shrinkdatabase ('dbname', 1 , truncate_only)
> > >
> > > i have also used the EM's shrink database , though it says it completed
> > > successfully but it did not shrink the database
> > >
> > > kindly advise
> > > tks & rdgs
> >
> >
> >|||Are you trying to shrink the db file or the log file? Sounds like the log
file. You should use DBCC SHRINKFILE not database as it allows you to
choose the specific file you want to shrink and how much.
--
Andrew J. Kelly SQL MVP
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:BFE5D0BA-D797-40CE-899B-246D6BA039D4@.microsoft.com...
> Hi ,
> i was trying to shrink a database but i got the following error :
> it's something like " logical file is in use" despite me logging in as a
> single user
> the statement i used : DBCC Shrinkdatabase ('dbname', 1 , truncate_only)
> i have also used the EM's shrink database , though it says it completed
> successfully but it did not shrink the database
> kindly advise
> tks & rdgs|||Hi Andrew ,
i was trying to shrink the log file but it couldn't as per the error below
and i have also tried to shrink the database as well , hoping it'll shrink
the log file
tks & rdgs
"Andrew J. Kelly" wrote:
> Are you trying to shrink the db file or the log file? Sounds like the log
> file. You should use DBCC SHRINKFILE not database as it allows you to
> choose the specific file you want to shrink and how much.
> --
> Andrew J. Kelly SQL MVP
>
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:BFE5D0BA-D797-40CE-899B-246D6BA039D4@.microsoft.com...
> > Hi ,
> >
> > i was trying to shrink a database but i got the following error :
> > it's something like " logical file is in use" despite me logging in as a
> > single user
> >
> > the statement i used : DBCC Shrinkdatabase ('dbname', 1 , truncate_only)
> >
> > i have also used the EM's shrink database , though it says it completed
> > successfully but it did not shrink the database
> >
> > kindly advise
> > tks & rdgs
>
>|||Did you actually try DBCC SHRINKFILE? If not you should. You don't need to
be in single user mode to run this.
--
Andrew J. Kelly SQL MVP
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:A84212A5-E2B0-4CF4-9F03-D4B84FCC4E59@.microsoft.com...
> Hi Andrew ,
> i was trying to shrink the log file but it couldn't as per the error
> below
> and i have also tried to shrink the database as well , hoping it'll shrink
> the log file
> tks & rdgs
> "Andrew J. Kelly" wrote:
>> Are you trying to shrink the db file or the log file? Sounds like the
>> log
>> file. You should use DBCC SHRINKFILE not database as it allows you to
>> choose the specific file you want to shrink and how much.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
>> news:BFE5D0BA-D797-40CE-899B-246D6BA039D4@.microsoft.com...
>> > Hi ,
>> >
>> > i was trying to shrink a database but i got the following error :
>> > it's something like " logical file is in use" despite me logging in as
>> > a
>> > single user
>> >
>> > the statement i used : DBCC Shrinkdatabase ('dbname', 1 ,
>> > truncate_only)
>> >
>> > i have also used the EM's shrink database , though it says it completed
>> > successfully but it did not shrink the database
>> >
>> > kindly advise
>> > tks & rdgs
>>|||Hi Andrew ,
i did tried with the DBCC shrink file statement but got the "logical log in
use" error as per the DBCC Shrinkdatabase. however, when i changed the mode
to "Simple" recovery mode , i could somehow shrink the db
tks & rdgs
"Andrew J. Kelly" wrote:
> Did you actually try DBCC SHRINKFILE? If not you should. You don't need to
> be in single user mode to run this.
> --
> Andrew J. Kelly SQL MVP
>
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:A84212A5-E2B0-4CF4-9F03-D4B84FCC4E59@.microsoft.com...
> > Hi Andrew ,
> >
> > i was trying to shrink the log file but it couldn't as per the error
> > below
> > and i have also tried to shrink the database as well , hoping it'll shrink
> > the log file
> >
> > tks & rdgs
> >
> > "Andrew J. Kelly" wrote:
> >
> >> Are you trying to shrink the db file or the log file? Sounds like the
> >> log
> >> file. You should use DBCC SHRINKFILE not database as it allows you to
> >> choose the specific file you want to shrink and how much.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> >> news:BFE5D0BA-D797-40CE-899B-246D6BA039D4@.microsoft.com...
> >> > Hi ,
> >> >
> >> > i was trying to shrink a database but i got the following error :
> >> > it's something like " logical file is in use" despite me logging in as
> >> > a
> >> > single user
> >> >
> >> > the statement i used : DBCC Shrinkdatabase ('dbname', 1 ,
> >> > truncate_only)
> >> >
> >> > i have also used the EM's shrink database , though it says it completed
> >> > successfully but it did not shrink the database
> >> >
> >> > kindly advise
> >> > tks & rdgs
> >>
> >>
> >>
>
>|||This is because the recovery mode you were in was preventing the log being
truncated until you'd backed it up. Switching to simple mode removes that
restriction. See BOL for more info ('Shrinking the Transaction Log' is a
good place to start)
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:65ADA556-3489-4620-844E-CC0DCB0BDF3A@.microsoft.com...
> Hi Andrew ,
> i did tried with the DBCC shrink file statement but got the "logical log
> in
> use" error as per the DBCC Shrinkdatabase. however, when i changed the
> mode
> to "Simple" recovery mode , i could somehow shrink the db
> tks & rdgs
> "Andrew J. Kelly" wrote:
>> Did you actually try DBCC SHRINKFILE? If not you should. You don't need
>> to
>> be in single user mode to run this.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
>> news:A84212A5-E2B0-4CF4-9F03-D4B84FCC4E59@.microsoft.com...
>> > Hi Andrew ,
>> >
>> > i was trying to shrink the log file but it couldn't as per the error
>> > below
>> > and i have also tried to shrink the database as well , hoping it'll
>> > shrink
>> > the log file
>> >
>> > tks & rdgs
>> >
>> > "Andrew J. Kelly" wrote:
>> >
>> >> Are you trying to shrink the db file or the log file? Sounds like the
>> >> log
>> >> file. You should use DBCC SHRINKFILE not database as it allows you to
>> >> choose the specific file you want to shrink and how much.
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >>
>> >>
>> >> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
>> >> news:BFE5D0BA-D797-40CE-899B-246D6BA039D4@.microsoft.com...
>> >> > Hi ,
>> >> >
>> >> > i was trying to shrink a database but i got the following error :
>> >> > it's something like " logical file is in use" despite me logging in
>> >> > as
>> >> > a
>> >> > single user
>> >> >
>> >> > the statement i used : DBCC Shrinkdatabase ('dbname', 1 ,
>> >> > truncate_only)
>> >> >
>> >> > i have also used the EM's shrink database , though it says it
>> >> > completed
>> >> > successfully but it did not shrink the database
>> >> >
>> >> > kindly advise
>> >> > tks & rdgs
>> >>
>> >>
>> >>
>>
dbcc shrinkdatabase error
Hi ,
i was trying to shrink a database but i got the following error :
it's something like " logical file is in use" despite me logging in as a
single user
the statement i used : DBCC Shrinkdatabase ('dbname', 1 , truncate_only)
i have also used the EM's shrink database , though it says it completed
successfully but it did not shrink the database
kindly advise
tks & rdgs
Hi
What is the Error Number and Severity?
You might be getting an informational message to tell you that the DB can't
be shunk any smaller than it already is.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"maxzsim" wrote:
> Hi ,
> i was trying to shrink a database but i got the following error :
> it's something like " logical file is in use" despite me logging in as a
> single user
> the statement i used : DBCC Shrinkdatabase ('dbname', 1 , truncate_only)
> i have also used the EM's shrink database , though it says it completed
> successfully but it did not shrink the database
> kindly advise
> tks & rdgs
|||Hi,
Post the exact error.
I feel that Probably there will be some active transactions. Execute DBCC
OPENTRAN('DBNAME') to identify if there is any open active trans.
If yes, wait that to complete. After that you could execute a backup LOG to
clear the transaction log and then execute DBCC SHRINKFILE to
shrink MDF and LDF seperately.
Thanks
Hari
SQL Server MVP
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:BFE5D0BA-D797-40CE-899B-246D6BA039D4@.microsoft.com...
> Hi ,
> i was trying to shrink a database but i got the following error :
> it's something like " logical file is in use" despite me logging in as a
> single user
> the statement i used : DBCC Shrinkdatabase ('dbname', 1 , truncate_only)
> i have also used the EM's shrink database , though it says it completed
> successfully but it did not shrink the database
> kindly advise
> tks & rdgs
|||Hi ,
before doing the shrink database , have already did a txn log bakup and it
says 4GB is free, though the log file is still occupying 5 GB
so thought of shrinking the database/log file and return the free space back
to OS
tks & rdgs
"Hari Prasad" wrote:
> Hi,
> Post the exact error.
> I feel that Probably there will be some active transactions. Execute DBCC
> OPENTRAN('DBNAME') to identify if there is any open active trans.
> If yes, wait that to complete. After that you could execute a backup LOG to
> clear the transaction log and then execute DBCC SHRINKFILE to
> shrink MDF and LDF seperately.
> Thanks
> Hari
> SQL Server MVP
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:BFE5D0BA-D797-40CE-899B-246D6BA039D4@.microsoft.com...
>
>
|||Hi ,
I have done a txn log backup file earlier and it says that 4GB is free
tks & rdgs
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> What is the Error Number and Severity?
> You might be getting an informational message to tell you that the DB can't
> be shunk any smaller than it already is.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "maxzsim" wrote:
|||Hi
It all depends on where the "Virtual Log"/"Active Portion of the Log" is in
relation to the end of the log file.
The Active Portion may be close to the end of the file at the moment, so the
log can not be shrunk. Once the Active Portion is at the beginning of the log
file, then the log can be truncated to where it is.
Look at "virtual log files" in BOL.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"maxzsim" wrote:
[vbcol=seagreen]
> Hi ,
> before doing the shrink database , have already did a txn log bakup and it
> says 4GB is free, though the log file is still occupying 5 GB
> so thought of shrinking the database/log file and return the free space back
> to OS
> tks & rdgs
> "Hari Prasad" wrote:
|||Are you trying to shrink the db file or the log file? Sounds like the log
file. You should use DBCC SHRINKFILE not database as it allows you to
choose the specific file you want to shrink and how much.
Andrew J. Kelly SQL MVP
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:BFE5D0BA-D797-40CE-899B-246D6BA039D4@.microsoft.com...
> Hi ,
> i was trying to shrink a database but i got the following error :
> it's something like " logical file is in use" despite me logging in as a
> single user
> the statement i used : DBCC Shrinkdatabase ('dbname', 1 , truncate_only)
> i have also used the EM's shrink database , though it says it completed
> successfully but it did not shrink the database
> kindly advise
> tks & rdgs
|||Hi Andrew ,
i was trying to shrink the log file but it couldn't as per the error below
and i have also tried to shrink the database as well , hoping it'll shrink
the log file
tks & rdgs
"Andrew J. Kelly" wrote:
> Are you trying to shrink the db file or the log file? Sounds like the log
> file. You should use DBCC SHRINKFILE not database as it allows you to
> choose the specific file you want to shrink and how much.
> --
> Andrew J. Kelly SQL MVP
>
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:BFE5D0BA-D797-40CE-899B-246D6BA039D4@.microsoft.com...
>
>
|||Did you actually try DBCC SHRINKFILE? If not you should. You don't need to
be in single user mode to run this.
Andrew J. Kelly SQL MVP
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:A84212A5-E2B0-4CF4-9F03-D4B84FCC4E59@.microsoft.com...[vbcol=seagreen]
> Hi Andrew ,
> i was trying to shrink the log file but it couldn't as per the error
> below
> and i have also tried to shrink the database as well , hoping it'll shrink
> the log file
> tks & rdgs
> "Andrew J. Kelly" wrote:
|||Hi Andrew ,
i did tried with the DBCC shrink file statement but got the "logical log in
use" error as per the DBCC Shrinkdatabase. however, when i changed the mode
to "Simple" recovery mode , i could somehow shrink the db
tks & rdgs
"Andrew J. Kelly" wrote:
> Did you actually try DBCC SHRINKFILE? If not you should. You don't need to
> be in single user mode to run this.
> --
> Andrew J. Kelly SQL MVP
>
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:A84212A5-E2B0-4CF4-9F03-D4B84FCC4E59@.microsoft.com...
>
>
i was trying to shrink a database but i got the following error :
it's something like " logical file is in use" despite me logging in as a
single user
the statement i used : DBCC Shrinkdatabase ('dbname', 1 , truncate_only)
i have also used the EM's shrink database , though it says it completed
successfully but it did not shrink the database
kindly advise
tks & rdgs
Hi
What is the Error Number and Severity?
You might be getting an informational message to tell you that the DB can't
be shunk any smaller than it already is.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"maxzsim" wrote:
> Hi ,
> i was trying to shrink a database but i got the following error :
> it's something like " logical file is in use" despite me logging in as a
> single user
> the statement i used : DBCC Shrinkdatabase ('dbname', 1 , truncate_only)
> i have also used the EM's shrink database , though it says it completed
> successfully but it did not shrink the database
> kindly advise
> tks & rdgs
|||Hi,
Post the exact error.
I feel that Probably there will be some active transactions. Execute DBCC
OPENTRAN('DBNAME') to identify if there is any open active trans.
If yes, wait that to complete. After that you could execute a backup LOG to
clear the transaction log and then execute DBCC SHRINKFILE to
shrink MDF and LDF seperately.
Thanks
Hari
SQL Server MVP
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:BFE5D0BA-D797-40CE-899B-246D6BA039D4@.microsoft.com...
> Hi ,
> i was trying to shrink a database but i got the following error :
> it's something like " logical file is in use" despite me logging in as a
> single user
> the statement i used : DBCC Shrinkdatabase ('dbname', 1 , truncate_only)
> i have also used the EM's shrink database , though it says it completed
> successfully but it did not shrink the database
> kindly advise
> tks & rdgs
|||Hi ,
before doing the shrink database , have already did a txn log bakup and it
says 4GB is free, though the log file is still occupying 5 GB
so thought of shrinking the database/log file and return the free space back
to OS
tks & rdgs
"Hari Prasad" wrote:
> Hi,
> Post the exact error.
> I feel that Probably there will be some active transactions. Execute DBCC
> OPENTRAN('DBNAME') to identify if there is any open active trans.
> If yes, wait that to complete. After that you could execute a backup LOG to
> clear the transaction log and then execute DBCC SHRINKFILE to
> shrink MDF and LDF seperately.
> Thanks
> Hari
> SQL Server MVP
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:BFE5D0BA-D797-40CE-899B-246D6BA039D4@.microsoft.com...
>
>
|||Hi ,
I have done a txn log backup file earlier and it says that 4GB is free
tks & rdgs
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> What is the Error Number and Severity?
> You might be getting an informational message to tell you that the DB can't
> be shunk any smaller than it already is.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "maxzsim" wrote:
|||Hi
It all depends on where the "Virtual Log"/"Active Portion of the Log" is in
relation to the end of the log file.
The Active Portion may be close to the end of the file at the moment, so the
log can not be shrunk. Once the Active Portion is at the beginning of the log
file, then the log can be truncated to where it is.
Look at "virtual log files" in BOL.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"maxzsim" wrote:
[vbcol=seagreen]
> Hi ,
> before doing the shrink database , have already did a txn log bakup and it
> says 4GB is free, though the log file is still occupying 5 GB
> so thought of shrinking the database/log file and return the free space back
> to OS
> tks & rdgs
> "Hari Prasad" wrote:
|||Are you trying to shrink the db file or the log file? Sounds like the log
file. You should use DBCC SHRINKFILE not database as it allows you to
choose the specific file you want to shrink and how much.
Andrew J. Kelly SQL MVP
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:BFE5D0BA-D797-40CE-899B-246D6BA039D4@.microsoft.com...
> Hi ,
> i was trying to shrink a database but i got the following error :
> it's something like " logical file is in use" despite me logging in as a
> single user
> the statement i used : DBCC Shrinkdatabase ('dbname', 1 , truncate_only)
> i have also used the EM's shrink database , though it says it completed
> successfully but it did not shrink the database
> kindly advise
> tks & rdgs
|||Hi Andrew ,
i was trying to shrink the log file but it couldn't as per the error below
and i have also tried to shrink the database as well , hoping it'll shrink
the log file
tks & rdgs
"Andrew J. Kelly" wrote:
> Are you trying to shrink the db file or the log file? Sounds like the log
> file. You should use DBCC SHRINKFILE not database as it allows you to
> choose the specific file you want to shrink and how much.
> --
> Andrew J. Kelly SQL MVP
>
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:BFE5D0BA-D797-40CE-899B-246D6BA039D4@.microsoft.com...
>
>
|||Did you actually try DBCC SHRINKFILE? If not you should. You don't need to
be in single user mode to run this.
Andrew J. Kelly SQL MVP
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:A84212A5-E2B0-4CF4-9F03-D4B84FCC4E59@.microsoft.com...[vbcol=seagreen]
> Hi Andrew ,
> i was trying to shrink the log file but it couldn't as per the error
> below
> and i have also tried to shrink the database as well , hoping it'll shrink
> the log file
> tks & rdgs
> "Andrew J. Kelly" wrote:
|||Hi Andrew ,
i did tried with the DBCC shrink file statement but got the "logical log in
use" error as per the DBCC Shrinkdatabase. however, when i changed the mode
to "Simple" recovery mode , i could somehow shrink the db
tks & rdgs
"Andrew J. Kelly" wrote:
> Did you actually try DBCC SHRINKFILE? If not you should. You don't need to
> be in single user mode to run this.
> --
> Andrew J. Kelly SQL MVP
>
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:A84212A5-E2B0-4CF4-9F03-D4B84FCC4E59@.microsoft.com...
>
>
Thursday, March 8, 2012
DBCC Error Logging
Hello,
I am running some DBCC checks as a routine maintenance task on my sql server 2000 servers. I am running DBCC CHECKDB, DBCC CHECKCONSTRAINTS and DBCC CHECKCATALOG. I have established a set of procedures and tables to capture the output from the error log after running these DBCC commands. After running all three, it appears the same info captured for CHECKDB is not logged for these two: CHECKCONSTRAINTS and CHECKCATALOG. Does anyone know why these are not logged and if it is even necessary for me to run these 2 extra DBCC checks? If not, can anyone recommend what database consistency checks should be executed on a daily basis for a production database server?
DBCC CHECKDB (DBNAME) executed by lshores found 0 errors and repaired 0 errors. Elapsed time: 0 hours 20 minutes 32 seconds.I found help for this question in this article http://www.sql-server-performance.com/ak_inside_sql_server_maintenance_plans.asp
and further info in BOL (sqlmaint Utility.)
I am running some DBCC checks as a routine maintenance task on my sql server 2000 servers. I am running DBCC CHECKDB, DBCC CHECKCONSTRAINTS and DBCC CHECKCATALOG. I have established a set of procedures and tables to capture the output from the error log after running these DBCC commands. After running all three, it appears the same info captured for CHECKDB is not logged for these two: CHECKCONSTRAINTS and CHECKCATALOG. Does anyone know why these are not logged and if it is even necessary for me to run these 2 extra DBCC checks? If not, can anyone recommend what database consistency checks should be executed on a daily basis for a production database server?
DBCC CHECKDB (DBNAME) executed by lshores found 0 errors and repaired 0 errors. Elapsed time: 0 hours 20 minutes 32 seconds.I found help for this question in this article http://www.sql-server-performance.com/ak_inside_sql_server_maintenance_plans.asp
and further info in BOL (sqlmaint Utility.)
Subscribe to:
Posts (Atom)