cannt remove data file from user database

We are running SQL server 2003 with SP3. I'm trying to
shrink a data files with the emptyfile option so I can
eventually remove the file using the alter database
command. However, I get the following error message when I
run the alter database command:

Error: the file PRADATA4 cannot be removed because it is
not empty.

The file that I'm trying to remove still has 62 extents on it.
I looked MS Knowledge base 254253 and 279511 on this problem but they say it is corrected by SQL server 7.0 with service pack 3.
commands that I'm running are as follows:

1) USE PRA
DBCC TRACEON(8901)
DBCC SHRINKFILE ('PRADATA4', EMPTYFILE)
DBCC TRACEOFF(8901)

2) USE PRA
GO
ALTERDATABASE PRA
REMOVE FILE PRADATA4
GO

Can anyone help?

ThanksYou are shrinking one file, but removing another. This sounds like a sub-optimal approach to me.

-PatP|||Originally posted by Pat Phelan
You are shrinking one file, but removing another. This sounds like a sub-optimal approach to me.

-PatP
sorry,I write error,the commands are:
1) USE PRA
DBCC TRACEON(8901)
DBCC SHRINKFILE ('PRADATA4', EMPTYFILE)
DBCC TRACEOFF(8901)

2) USE PRA
GO
ALTERDATABASE PRA
REMOVE FILE PRADATA4
GO|||First make sure there are no open transactions that are stopping this piece of data from being moved and ensure to try with trace flags as specified in one of the KBAs.|||Originally posted by Satya
First make sure there are no open transactions that are stopping this piece of data from being moved and ensure to try with trace flags as specified in one of the KBAs.

How to check open transactions?Use sp_who2?|||DBCC OPENTRAN and refer to books online for more information.|||Originally posted by Satya
DBCC OPENTRAN and refer to books online for more information.
Hi Satya:
Yesterday , I restarted our database service,and used DBCC OPENTRAN to check there are no open transactions,but we still cann't remove the PRADATA4 from database.

Thanks|||Books Online (BOL) documents the REMOVE FILE option of ALTER DATABASE, but BOL doesn't tell you that you first need to run SHRINKFILE('file_name', EMPTYFILE). This command shrinks the file to 0MB of space used and ensures that you've removed all the objects from the file.|||Originally posted by Satya
Books Online (BOL) documents the REMOVE FILE option of ALTER DATABASE, but BOL doesn't tell you that you first need to run SHRINKFILE('file_name', EMPTYFILE). This command shrinks the file to 0MB of space used and ensures that you've removed all the objects from the file.
Hi Satya:
Thanks for your help,I have used KB 324432 to resolve the problem.

Cannot truncate transaction log.

DB Newbie question ahead.

I have a mere 100MB db with a 4GB transaction log. I want to truncate the log as I understand that truncating it will shrink the log by removing the transactions that have already taken place. However, the option to do a transaction backup is greyed out. I suspect this is from the db being in transactional replication with another server; however, I don't know for sure.

Are there any other ways that I can shrink the transaction log? I would like to do shrink it without taking the db offline either.I think the recovery model was set to simple, that's why you can't take log backup. You can use DBCC Shrinkfile to shrink your log file to the size you want.|||Are you using SQL 2000?
If so you can do that by the following

- Go to DB properties and change the backup type to FULL
- Take a backup of the database and keep it safe.
- Go to DB properties again change the backup type to SIMPLE
- Go to all tasks and Shrink the file
- Go to DB properties again and change the backup type to FULL again so that it logs everything.|||Yes, I am using SQL2000. That's important information ;)
SQL2000 on win2003.

smasanam: I tried that and it didn't shrink the log file. Still 4.5GB.

Is the log file important for transactional replication? This db is replicated nightly to another server. If everything is updated and fully bkup'd would the log file be necessary? Or could I just delete it?

Thanks for the help.

Cannot shrink transaction log of a SQL Server 2005 db

I have a SQL Server 2005 db for a Navision installation.
I have tried to execute DBCC Shrinkfile on the related transaction log after
the running of backup log. I have had this error:
"Cannot shrink log file 2 (...) because all logical log files are in use".
The transaction log is of 15 GB!
I know that it is possible to reduce the space of the log while the db is in
use.
How can I solve this issue?
Many thanksOpen transaction? Check using DBCC OPENTRAN.
Also, details on shrinking found at 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
"Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
news:BF7E5E86-D439-47DA-B469-68526A22A19F@.microsoft.com...
>I have a SQL Server 2005 db for a Navision installation.
> I have tried to execute DBCC Shrinkfile on the related transaction log after
> the running of backup log. I have had this error:
> "Cannot shrink log file 2 (...) because all logical log files are in use".
> The transaction log is of 15 GB!
> I know that it is possible to reduce the space of the log while the db is in
> use.
> How can I solve this issue?
> Many thanks
>|||I have executed DBCC OPENTRAN(MyDB) and
there aren't any active transactions.
The DBCC SQLPERF(LOGSPACE) return 4% used for the log.
"Tibor Karaszi" wrote:
> Open transaction? Check using DBCC OPENTRAN.
> Also, details on shrinking found at 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
>
> "Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
> news:BF7E5E86-D439-47DA-B469-68526A22A19F@.microsoft.com...
> >I have a SQL Server 2005 db for a Navision installation.
> > I have tried to execute DBCC Shrinkfile on the related transaction log after
> > the running of backup log. I have had this error:
> > "Cannot shrink log file 2 (...) because all logical log files are in use".
> > The transaction log is of 15 GB!
> > I know that it is possible to reduce the space of the log while the db is in
> > use.
> >
> > How can I solve this issue?
> >
> > Many thanks
> >
>|||I have executed a complete backup of the db after logging out of the users
from Navision. So I have executed first the backup log (copy and truncate)
and then the dbcc shrinkfile for three times.
I have obtained a log of 320 MB from 15 GB, but I have always the same error:
"Cannot shrink log file 2 (..._Log) because all logical log files are in use".
Do you have any suggests to understand this error?
Thanks
"Tibor Karaszi" wrote:
> Open transaction? Check using DBCC OPENTRAN.
> Also, details on shrinking found at 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
>
> "Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
> news:BF7E5E86-D439-47DA-B469-68526A22A19F@.microsoft.com...
> >I have a SQL Server 2005 db for a Navision installation.
> > I have tried to execute DBCC Shrinkfile on the related transaction log after
> > the running of backup log. I have had this error:
> > "Cannot shrink log file 2 (...) because all logical log files are in use".
> > The transaction log is of 15 GB!
> > I know that it is possible to reduce the space of the log while the db is in
> > use.
> >
> > How can I solve this issue?
> >
> > Many thanks
> >
>|||Did you investigate the virtual log file layout as suggested i my article?
Also, are you saying that you got it down to 320 MB, and want to shrink it further? If so, why? See
http://sqlblog.com/blogs/tibor_karaszi/archive/2007/02/25/leaking-roof-and-file-shrinking.aspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
news:A3FEF114-AB29-41E5-A260-6B451C39706D@.microsoft.com...
>I have executed a complete backup of the db after logging out of the users
> from Navision. So I have executed first the backup log (copy and truncate)
> and then the dbcc shrinkfile for three times.
> I have obtained a log of 320 MB from 15 GB, but I have always the same error:
> "Cannot shrink log file 2 (..._Log) because all logical log files are in use".
> Do you have any suggests to understand this error?
> Thanks
>
> "Tibor Karaszi" wrote:
>> Open transaction? Check using DBCC OPENTRAN.
>> Also, details on shrinking found at 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
>>
>> "Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
>> news:BF7E5E86-D439-47DA-B469-68526A22A19F@.microsoft.com...
>> >I have a SQL Server 2005 db for a Navision installation.
>> > I have tried to execute DBCC Shrinkfile on the related transaction log after
>> > the running of backup log. I have had this error:
>> > "Cannot shrink log file 2 (...) because all logical log files are in use".
>> > The transaction log is of 15 GB!
>> > I know that it is possible to reduce the space of the log while the db is in
>> > use.
>> >
>> > How can I solve this issue?
>> >
>> > Many thanks
>> >
>>|||320 MB for the log is enough for me, absolutely!
I want to underline that I have still the error every time that I shrink the
log.
"Tibor Karaszi" wrote:
> Did you investigate the virtual log file layout as suggested i my article?
> Also, are you saying that you got it down to 320 MB, and want to shrink it further? If so, why? See
> http://sqlblog.com/blogs/tibor_karaszi/archive/2007/02/25/leaking-roof-and-file-shrinking.aspx
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
> news:A3FEF114-AB29-41E5-A260-6B451C39706D@.microsoft.com...
> >I have executed a complete backup of the db after logging out of the users
> > from Navision. So I have executed first the backup log (copy and truncate)
> > and then the dbcc shrinkfile for three times.
> > I have obtained a log of 320 MB from 15 GB, but I have always the same error:
> > "Cannot shrink log file 2 (..._Log) because all logical log files are in use".
> >
> > Do you have any suggests to understand this error?
> >
> > Thanks
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> Open transaction? Check using DBCC OPENTRAN.
> >>
> >> Also, details on shrinking found at 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
> >>
> >>
> >> "Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
> >> news:BF7E5E86-D439-47DA-B469-68526A22A19F@.microsoft.com...
> >> >I have a SQL Server 2005 db for a Navision installation.
> >> > I have tried to execute DBCC Shrinkfile on the related transaction log after
> >> > the running of backup log. I have had this error:
> >> > "Cannot shrink log file 2 (...) because all logical log files are in use".
> >> > The transaction log is of 15 GB!
> >> > I know that it is possible to reduce the space of the log while the db is in
> >> > use.
> >> >
> >> > How can I solve this issue?
> >> >
> >> > Many thanks
> >> >
> >>
> >>
>|||As I stated twice before, the answer is in the virtual log file layout. Also, minimum number of
virtual log files is 2 or 4 (I don't remember).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
news:37F476C6-F5C1-4C15-8AD1-39ED6C5275D9@.microsoft.com...
> 320 MB for the log is enough for me, absolutely!
> I want to underline that I have still the error every time that I shrink the
> log.
>
> "Tibor Karaszi" wrote:
>> Did you investigate the virtual log file layout as suggested i my article?
>> Also, are you saying that you got it down to 320 MB, and want to shrink it further? If so, why?
>> See
>> http://sqlblog.com/blogs/tibor_karaszi/archive/2007/02/25/leaking-roof-and-file-shrinking.aspx
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
>> news:A3FEF114-AB29-41E5-A260-6B451C39706D@.microsoft.com...
>> >I have executed a complete backup of the db after logging out of the users
>> > from Navision. So I have executed first the backup log (copy and truncate)
>> > and then the dbcc shrinkfile for three times.
>> > I have obtained a log of 320 MB from 15 GB, but I have always the same error:
>> > "Cannot shrink log file 2 (..._Log) because all logical log files are in use".
>> >
>> > Do you have any suggests to understand this error?
>> >
>> > Thanks
>> >
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> Open transaction? Check using DBCC OPENTRAN.
>> >>
>> >> Also, details on shrinking found at 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
>> >>
>> >>
>> >> "Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
>> >> news:BF7E5E86-D439-47DA-B469-68526A22A19F@.microsoft.com...
>> >> >I have a SQL Server 2005 db for a Navision installation.
>> >> > I have tried to execute DBCC Shrinkfile on the related transaction log after
>> >> > the running of backup log. I have had this error:
>> >> > "Cannot shrink log file 2 (...) because all logical log files are in use".
>> >> > The transaction log is of 15 GB!
>> >> > I know that it is possible to reduce the space of the log while the db is in
>> >> > use.
>> >> >
>> >> > How can I solve this issue?
>> >> >
>> >> > Many thanks
>> >> >
>> >>
>> >>
>>|||I'm not sure if this helps but, below is what I do to shrink and clean the
tlogs.
USE [mydb];
GO
BACKUP LOG [mydb] WITH TRUNCATE_ONLY;
GO
DBCC SHRINKFILE (mydb_Log, EMPTYFILE);
GO
DBCC SHRINKDATABASE('mydb')
GO
"Pasquale" wrote:
> I have a SQL Server 2005 db for a Navision installation.
> I have tried to execute DBCC Shrinkfile on the related transaction log after
> the running of backup log. I have had this error:
> "Cannot shrink log file 2 (...) because all logical log files are in use".
> The transaction log is of 15 GB!
> I know that it is possible to reduce the space of the log while the db is in
> use.
> How can I solve this issue?
> Many thanks
>

Cannot shrink transaction log of a SQL Server 2005 db

I have a SQL Server 2005 db for a Navision installation.
I have tried to execute DBCC Shrinkfile on the related transaction log after
the running of backup log. I have had this error:
"Cannot shrink log file 2 (...) because all logical log files are in use".
The transaction log is of 15 GB!
I know that it is possible to reduce the space of the log while the db is in
use.
How can I solve this issue?
Many thanks
I have executed DBCC OPENTRAN(MyDB) and
there aren't any active transactions.
The DBCC SQLPERF(LOGSPACE) return 4% used for the log.
"Tibor Karaszi" wrote:

> Open transaction? Check using DBCC OPENTRAN.
> Also, details on shrinking found at 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
>
> "Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
> news:BF7E5E86-D439-47DA-B469-68526A22A19F@.microsoft.com...
>
|||I have executed a complete backup of the db after logging out of the users
from Navision. So I have executed first the backup log (copy and truncate)
and then the dbcc shrinkfile for three times.
I have obtained a log of 320 MB from 15 GB, but I have always the same error:
"Cannot shrink log file 2 (..._Log) because all logical log files are in use".
Do you have any suggests to understand this error?
Thanks
"Tibor Karaszi" wrote:

> Open transaction? Check using DBCC OPENTRAN.
> Also, details on shrinking found at 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
>
> "Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
> news:BF7E5E86-D439-47DA-B469-68526A22A19F@.microsoft.com...
>
|||320 MB for the log is enough for me, absolutely!
I want to underline that I have still the error every time that I shrink the
log.
"Tibor Karaszi" wrote:

> Did you investigate the virtual log file layout as suggested i my article?
> Also, are you saying that you got it down to 320 MB, and want to shrink it further? If so, why? See
> http://sqlblog.com/blogs/tibor_karaszi/archive/2007/02/25/leaking-roof-and-file-shrinking.aspx
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
> news:A3FEF114-AB29-41E5-A260-6B451C39706D@.microsoft.com...
>
|||I'm not sure if this helps but, below is what I do to shrink and clean the
tlogs.
USE [mydb];
GO
BACKUP LOG [mydb] WITH TRUNCATE_ONLY;
GO
DBCC SHRINKFILE (mydb_Log, EMPTYFILE);
GO
DBCC SHRINKDATABASE('mydb')
GO
"Pasquale" wrote:

> I have a SQL Server 2005 db for a Navision installation.
> I have tried to execute DBCC Shrinkfile on the related transaction log after
> the running of backup log. I have had this error:
> "Cannot shrink log file 2 (...) because all logical log files are in use".
> The transaction log is of 15 GB!
> I know that it is possible to reduce the space of the log while the db is in
> use.
> How can I solve this issue?
> Many thanks
>

Cannot shrink transaction log of a SQL Server 2005 db

I have a SQL Server 2005 db for a Navision installation.
I have tried to execute DBCC Shrinkfile on the related transaction log after
the running of backup log. I have had this error:
"Cannot shrink log file 2 (...) because all logical log files are in use".
The transaction log is of 15 GB!
I know that it is possible to reduce the space of the log while the db is in
use.
How can I solve this issue?
Many thanksOpen transaction? Check using DBCC OPENTRAN.
Also, details on shrinking found at http://www.karaszi.com/SQLServer/in...
ink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
news:BF7E5E86-D439-47DA-B469-68526A22A19F@.microsoft.com...
>I have a SQL Server 2005 db for a Navision installation.
> I have tried to execute DBCC Shrinkfile on the related transaction log aft
er
> the running of backup log. I have had this error:
> "Cannot shrink log file 2 (...) because all logical log files are in use".
> The transaction log is of 15 GB!
> I know that it is possible to reduce the space of the log while the db is
in
> use.
> How can I solve this issue?
> Many thanks
>|||I have executed DBCC OPENTRAN(MyDB) and
there aren't any active transactions.
The DBCC SQLPERF(LOGSPACE) return 4% used for the log.
"Tibor Karaszi" wrote:

> Open transaction? Check using DBCC OPENTRAN.
> Also, details on shrinking found at http://www.karaszi.com/SQLServer/in...r />
hrink.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
> news:BF7E5E86-D439-47DA-B469-68526A22A19F@.microsoft.com...
>|||I'm not sure if this helps but, below is what I do to shrink and clean the
tlogs.
USE [mydb];
GO
BACKUP LOG [mydb] WITH TRUNCATE_ONLY;
GO
DBCC SHRINKFILE (mydb_Log, EMPTYFILE);
GO
DBCC SHRINKDATABASE('mydb')
GO
"Pasquale" wrote:

> I have a SQL Server 2005 db for a Navision installation.
> I have tried to execute DBCC Shrinkfile on the related transaction log aft
er
> the running of backup log. I have had this error:
> "Cannot shrink log file 2 (...) because all logical log files are in use".
> The transaction log is of 15 GB!
> I know that it is possible to reduce the space of the log while the db is
in
> use.
> How can I solve this issue?
> Many thanks
>

Cannot shrink transaction log - windows 2003/sp1 and SQL 2000/SP4

I cannot shrink a transaction log to a database through the enterprise mmc.
This particular transaction log is about 135 GB and is filling up the hard
drive. Is there a way to force this?http://support.microsoft.com/kb/q272318/
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Sabo, Eric" wrote:
> I cannot shrink a transaction log to a database through the enterprise mmc.
> This particular transaction log is about 135 GB and is filling up the hard
> drive. Is there a way to force this?|||Mike,
Thanks for the reply but where do you run this command from. Also, what
if I have no space to backup this transaction log.
"Mike Epprecht (SQL MVP)" wrote:
> http://support.microsoft.com/kb/q272318/
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Sabo, Eric" wrote:
> > I cannot shrink a transaction log to a database through the enterprise mmc.
> > This particular transaction log is about 135 GB and is filling up the hard
> > drive. Is there a way to force this?|||It is saying the following when I tried to shrink the database:
Cannot shrink log file 2 (XXXXXXXX_log) because all logical log files are in
use.
"Sabo, Eric" wrote:
> I cannot shrink a transaction log to a database through the enterprise mmc.
> This particular transaction log is about 135 GB and is filling up the hard
> drive. Is there a way to force this?|||Hi
OK, so I assume that you have not backuped up your transaction log, nor
require it, so put the DB in simple Recovery Mode using Enterprise Manager
(Property of the DB)
This will clear all inactive entries in the log and then you can shrink the
log,
You need to run those commands in the KB from Query Analyser.
Look up "recovery plan" in Books Online to understand how the log is used
for recovery in DR scenarios.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Sabo, Eric" wrote:
> Mike,
> Thanks for the reply but where do you run this command from. Also, what
> if I have no space to backup this transaction log.
> "Mike Epprecht (SQL MVP)" wrote:
> > http://support.microsoft.com/kb/q272318/
> > --
> > --
> > Mike Epprecht, Microsoft SQL Server MVP
> > Zurich, Switzerland
> >
> > MVP Program: http://www.microsoft.com/mvp
> >
> > Blog: http://www.msmvps.com/epprecht/
> >
> >
> >
> > "Sabo, Eric" wrote:
> >
> > > I cannot shrink a transaction log to a database through the enterprise mmc.
> > > This particular transaction log is about 135 GB and is filling up the hard
> > > drive. Is there a way to force this?|||Mike,
Thanks alot.
That worked. I restricted the growth this time so it will not fill up my
hard drive again. Thanks again!
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> OK, so I assume that you have not backuped up your transaction log, nor
> require it, so put the DB in simple Recovery Mode using Enterprise Manager
> (Property of the DB)
> This will clear all inactive entries in the log and then you can shrink the
> log,
> You need to run those commands in the KB from Query Analyser.
> Look up "recovery plan" in Books Online to understand how the log is used
> for recovery in DR scenarios.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Sabo, Eric" wrote:
> > Mike,
> >
> > Thanks for the reply but where do you run this command from. Also, what
> > if I have no space to backup this transaction log.
> >
> > "Mike Epprecht (SQL MVP)" wrote:
> >
> > > http://support.microsoft.com/kb/q272318/
> > > --
> > > --
> > > Mike Epprecht, Microsoft SQL Server MVP
> > > Zurich, Switzerland
> > >
> > > MVP Program: http://www.microsoft.com/mvp
> > >
> > > Blog: http://www.msmvps.com/epprecht/
> > >
> > >
> > >
> > > "Sabo, Eric" wrote:
> > >
> > > > I cannot shrink a transaction log to a database through the enterprise mmc.
> > > > This particular transaction log is about 135 GB and is filling up the hard
> > > > drive. Is there a way to force this?

Cannot shrink transaction log - windows 2003/sp1 and SQL 2000/SP4

I cannot shrink a transaction log to a database through the enterprise mmc.
This particular transaction log is about 135 GB and is filling up the hard
drive. Is there a way to force this?
http://support.microsoft.com/kb/q272318/
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Sabo, Eric" wrote:

> I cannot shrink a transaction log to a database through the enterprise mmc.
> This particular transaction log is about 135 GB and is filling up the hard
> drive. Is there a way to force this?
|||It is saying the following when I tried to shrink the database:
Cannot shrink log file 2 (XXXXXXXX_log) because all logical log files are in
use.
"Sabo, Eric" wrote:

> I cannot shrink a transaction log to a database through the enterprise mmc.
> This particular transaction log is about 135 GB and is filling up the hard
> drive. Is there a way to force this?

Cannot shrink transaction log - windows 2003/sp1 and SQL 2000/SP4

I cannot shrink a transaction log to a database through the enterprise mmc.
This particular transaction log is about 135 GB and is filling up the hard
drive. Is there a way to force this?http://support.microsoft.com/kb/q272318/
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Sabo, Eric" wrote:

> I cannot shrink a transaction log to a database through the enterprise mmc
.
> This particular transaction log is about 135 GB and is filling up the hard
> drive. Is there a way to force this?|||It is saying the following when I tried to shrink the database:
Cannot shrink log file 2 (XXXXXXXX_log) because all logical log files are in
use.
"Sabo, Eric" wrote:

> I cannot shrink a transaction log to a database through the enterprise mmc
.
> This particular transaction log is about 135 GB and is filling up the hard
> drive. Is there a way to force this?

Cannot shrink transaction log

Hi,

I have a t-log on one database which is 400mb when the database size is only 30mb. dbcc shrinkfile does not work and dbcc opentran shows no open transactions. When I tried to do a Backup log with truncate_only the following message displays:

The log was not truncated because records at the beginning of the log are pending replication. Ensure the Log Reader Agent is running or use sp_repldone to mark transactions as distributed.

The database in question is not a publisher and only receives 5 subscribed articles, none of which are changed very often. The database operates 24 x 7.

Does anyone have a solution to this dilemma?

Thank YouHave you enabled the transaction checkpoint ? so that it will reduce automatically ?

dave|||Run this script (courtesy of SQL Server Professional magazine)

Sometimes a piece of data blocks the rest of the transaction log from being truncated. This script will solve your problem by created and removing garbage data until the active portion of the log loops to the beginning of the t'log.

Cannot shrink Transaction Log

Hello. I've got a situation where a live database has stopped working because
the transaction logs have filled all available disk space. I would really
appreciate any advice on how to get it running again. My knowledge of SQL
Server 2000 is very limited so I would need so quite detailed instructions! I
am happy with entering statements into Query Analyzer.
I've tried to shrink the transaction log by running a backup on it in
Enterprise Manager. However, the largest .LDF file is 33Gb and there is not
enough disk space to back this up.
So somehow I need to shrink these .LDF transaction log files. Also, if
anyone could advise on how to stop this situation happening again, that'd be
fantastic. If anyone can give advice on how to do this, you really would be
saving my life!
1. Consider the recovery model for the database. If not "simple" then SQL Server will not empty the
log files automatically, and the log will keep growing. When not "simple", only backup of the
transaction log will empty the log files.
2. After either setting to simple or scheduling regular transaction log backups, verify that the log
files are more or less empty. Use:
DBCC SQLPERF(LOGSPACE)
3. Now time for the shrink. Use DBCC SHRINKFILE, (not SHRINKDATABASE), as documented in Books
Online.
Also, there are some links and general info about shrink on
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Fluffy_Ninja" <FluffyNinja@.discussions.microsoft.com> wrote in message
news:C338EACF-9E37-47B3-B307-D9A15A87539C@.microsoft.com...
> Hello. I've got a situation where a live database has stopped working because
> the transaction logs have filled all available disk space. I would really
> appreciate any advice on how to get it running again. My knowledge of SQL
> Server 2000 is very limited so I would need so quite detailed instructions! I
> am happy with entering statements into Query Analyzer.
> I've tried to shrink the transaction log by running a backup on it in
> Enterprise Manager. However, the largest .LDF file is 33Gb and there is not
> enough disk space to back this up.
> So somehow I need to shrink these .LDF transaction log files. Also, if
> anyone could advise on how to stop this situation happening again, that'd be
> fantastic. If anyone can give advice on how to do this, you really would be
> saving my life!
|||Hi,
What is your recovery model? Full, Simple ?
If you are using Full recovery model then you need to backup the transaction
log regularly.
If you don't need to set recovery model to Full then use Simple Recovery
Model you can find detailed information about it from Books Online.
Check out BACKUP LOG WITH TRUNCATE_ONLY (if it's acceptable for you..)
FROM BOL:
NO_LOG | TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it and
truncates the log. This option frees space. Specifying a backup device is
unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY are
synonyms.
After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes
recorded in the log are not recoverable. For recovery purposes, immediately
execute BACKUP DATABASE.
"Fluffy_Ninja" wrote:

> Hello. I've got a situation where a live database has stopped working because
> the transaction logs have filled all available disk space. I would really
> appreciate any advice on how to get it running again. My knowledge of SQL
> Server 2000 is very limited so I would need so quite detailed instructions! I
> am happy with entering statements into Query Analyzer.
> I've tried to shrink the transaction log by running a backup on it in
> Enterprise Manager. However, the largest .LDF file is 33Gb and there is not
> enough disk space to back this up.
> So somehow I need to shrink these .LDF transaction log files. Also, if
> anyone could advise on how to stop this situation happening again, that'd be
> fantastic. If anyone can give advice on how to do this, you really would be
> saving my life!

Cannot shrink Transaction Log

Hello. I've got a situation where a live database has stopped working becaus
e
the transaction logs have filled all available disk space. I would really
appreciate any advice on how to get it running again. My knowledge of SQL
Server 2000 is very limited so I would need so quite detailed instructions!
I
am happy with entering statements into Query Analyzer.
I've tried to shrink the transaction log by running a backup on it in
Enterprise Manager. However, the largest .LDF file is 33Gb and there is not
enough disk space to back this up.
So somehow I need to shrink these .LDF transaction log files. Also, if
anyone could advise on how to stop this situation happening again, that'd be
fantastic. If anyone can give advice on how to do this, you really would be
saving my life!1. Consider the recovery model for the database. If not "simple" then SQL Se
rver will not empty the
log files automatically, and the log will keep growing. When not "simple", o
nly backup of the
transaction log will empty the log files.
2. After either setting to simple or scheduling regular transaction log back
ups, verify that the log
files are more or less empty. Use:
DBCC SQLPERF(LOGSPACE)
3. Now time for the shrink. Use DBCC SHRINKFILE, (not SHRINKDATABASE), as do
cumented in Books
Online.
Also, there are some links and general info about shrink on
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Fluffy_Ninja" <FluffyNinja@.discussions.microsoft.com> wrote in message
news:C338EACF-9E37-47B3-B307-D9A15A87539C@.microsoft.com...
> Hello. I've got a situation where a live database has stopped working beca
use
> the transaction logs have filled all available disk space. I would really
> appreciate any advice on how to get it running again. My knowledge of SQL
> Server 2000 is very limited so I would need so quite detailed instructions
! I
> am happy with entering statements into Query Analyzer.
> I've tried to shrink the transaction log by running a backup on it in
> Enterprise Manager. However, the largest .LDF file is 33Gb and there is no
t
> enough disk space to back this up.
> So somehow I need to shrink these .LDF transaction log files. Also, if
> anyone could advise on how to stop this situation happening again, that'd
be
> fantastic. If anyone can give advice on how to do this, you really would b
e
> saving my life!|||Hi,
What is your recovery model? Full, Simple ?
If you are using Full recovery model then you need to backup the transaction
log regularly.
If you don't need to set recovery model to Full then use Simple Recovery
Model you can find detailed information about it from Books Online.
Check out BACKUP LOG WITH TRUNCATE_ONLY (if it's acceptable for you..)
FROM BOL:
NO_LOG | TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it and
truncates the log. This option frees space. Specifying a backup device is
unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY ar
e
synonyms.
After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes
recorded in the log are not recoverable. For recovery purposes, immediately
execute BACKUP DATABASE.
"Fluffy_Ninja" wrote:

> Hello. I've got a situation where a live database has stopped working beca
use
> the transaction logs have filled all available disk space. I would really
> appreciate any advice on how to get it running again. My knowledge of SQL
> Server 2000 is very limited so I would need so quite detailed instructions
! I
> am happy with entering statements into Query Analyzer.
> I've tried to shrink the transaction log by running a backup on it in
> Enterprise Manager. However, the largest .LDF file is 33Gb and there is no
t
> enough disk space to back this up.
> So somehow I need to shrink these .LDF transaction log files. Also, if
> anyone could advise on how to stop this situation happening again, that'd
be
> fantastic. If anyone can give advice on how to do this, you really would b
e
> saving my life!

cannot shrink transaction log

I have problems with shrinking the transaction log. I have first backup the
transactionlog and then I try to shrink de transaction log with the shrink
file command but the log wil not shrink. When I use the DBCC loginfo I see
that the status is 2 for the VLF's and they never change to 0.
How can I shrink my transaction log file.A quick way to achieve this is by:
1)Backup your db
2)Detach db
3)rename the relevant log file
4)Attach db - the reattach will automatically create a new log file
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"cas" <cas@.discussions.microsoft.com> wrote in message
news:62EB1A28-DC4C-4A6D-A5DB-D293B8B00E0C@.microsoft.com...

> I have problems with shrinking the transaction log. I have first backup
the
> transactionlog and then I try to shrink de transaction log with the shrink
> file command but the log wil not shrink. When I use the DBCC loginfo I see
> that the status is 2 for the VLF's and they never change to 0.
> How can I shrink my transaction log file.|||After taking the backup make sure there is no open transaction run DBCC
OpenTran to get the open transaction.
"Jack Vamvas" wrote:

> A quick way to achieve this is by:
> 1)Backup your db
> 2)Detach db
> 3)rename the relevant log file
> 4)Attach db - the reattach will automatically create a new log file
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
>
> "cas" <cas@.discussions.microsoft.com> wrote in message
> news:62EB1A28-DC4C-4A6D-A5DB-D293B8B00E0C@.microsoft.com...
>
> the
>
>

cannot shrink transaction log

I have not been able to successfully shrink my transaction log. I'm
receiving the infamous "the log file for database is full".
I have tried:
backup log database with truncate_only
go
dbcc shrinkfile (database _log,0)
go
I receive this message when I am importing a large excel spreadsheet.
The error occurs on line 7956. Again, this is large, so what can I do to
make sure this works. This import is daily.
Thanks!!
*** Sent via Developersdex http://www.examnotes.net ***Joey,
Sounds like you have two issues.
1. >I have not been able to successfully shrink my transaction log.
Are there any open transactions (can use DBCC OPENTRAN to determine and KILL
to end).
Also see:
http://groups.google.com/group/micr...9c56843a0f1d1db
2. Import issue - ensure adequate log space is available by increasing the
size of the log file as well as ensuring auto-growth is enabled. You can
use DBCC SQLPERF(LOGSPACE) to track the log usage. If the table has a high
number of indexes you may consider dropping the indexes prior to the import
then recreating them following the import. Minimally logged op with BCP or
BULK COPY is another option - but probably not waranted here.
HTH
Jerry
"Joey Martin" <joey@.infosmiths.net> wrote in message
news:OtHjosX2FHA.3592@.TK2MSFTNGP12.phx.gbl...
>I have not been able to successfully shrink my transaction log. I'm
> receiving the infamous "the log file for database is full".
> I have tried:
> backup log database with truncate_only
> go
> dbcc shrinkfile (database _log,0)
> go
> I receive this message when I am importing a large excel spreadsheet.
> The error occurs on line 7956. Again, this is large, so what can I do to
> make sure this works. This import is daily.
> Thanks!!
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||What version of SQL Server are you using? What service pack? I've seen
this problem before, but I think it went away when I installed SP3.
Have you tried shrinking the file using Enterprise Manager?
You might try adding a second log file on another disk, truncate and shrink
the first file, and then remove the second log file. I'm not sure if that
will work, but it's worth a try.
Another option would be to replace the log file: detach the database using
sp_detach_db, delete the log file and then reattach the database using
sp_attach_single_file_db. If the log file wasn't created on the correct
physical disk drive, detach the database using sp_detach_db, move the log
file onto the log disk drive, and then reattach the database using
sp_attach_db.
"Joey Martin" <joey@.infosmiths.net> wrote in message
news:OtHjosX2FHA.3592@.TK2MSFTNGP12.phx.gbl...
>I have not been able to successfully shrink my transaction log. I'm
> receiving the infamous "the log file for database is full".
> I have tried:
> backup log database with truncate_only
> go
> dbcc shrinkfile (database _log,0)
> go
> I receive this message when I am importing a large excel spreadsheet.
> The error occurs on line 7956. Again, this is large, so what can I do to
> make sure this works. This import is daily.
> Thanks!!
>
>
> *** Sent via Developersdex http://www.examnotes.net ***

cannot shrink transaction log

I have problems with shrinking the transaction log. I have first backup the
transactionlog and then I try to shrink de transaction log with the shrink
file command but the log wil not shrink. When I use the DBCC loginfo I see
that the status is 2 for the VLF's and they never change to 0.
How can I shrink my transaction log file.A quick way to achieve this is by:
1)Backup your db
2)Detach db
3)rename the relevant log file
4)Attach db - the reattach will automatically create a new log file
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"cas" <cas@.discussions.microsoft.com> wrote in message
news:62EB1A28-DC4C-4A6D-A5DB-D293B8B00E0C@.microsoft.com...
> I have problems with shrinking the transaction log. I have first backup
the
> transactionlog and then I try to shrink de transaction log with the shrink
> file command but the log wil not shrink. When I use the DBCC loginfo I see
> that the status is 2 for the VLF's and they never change to 0.
> How can I shrink my transaction log file.|||After taking the backup make sure there is no open transaction run DBCC
OpenTran to get the open transaction.
"Jack Vamvas" wrote:
> A quick way to achieve this is by:
> 1)Backup your db
> 2)Detach db
> 3)rename the relevant log file
> 4)Attach db - the reattach will automatically create a new log file
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
>
> "cas" <cas@.discussions.microsoft.com> wrote in message
> news:62EB1A28-DC4C-4A6D-A5DB-D293B8B00E0C@.microsoft.com...
> > I have problems with shrinking the transaction log. I have first backup
> the
> > transactionlog and then I try to shrink de transaction log with the shrink
> > file command but the log wil not shrink. When I use the DBCC loginfo I see
> > that the status is 2 for the VLF's and they never change to 0.
> > How can I shrink my transaction log file.
>
>

Cannot shrink Transaction Log

Hello. I've got a situation where a live database has stopped working because
the transaction logs have filled all available disk space. I would really
appreciate any advice on how to get it running again. My knowledge of SQL
Server 2000 is very limited so I would need so quite detailed instructions! I
am happy with entering statements into Query Analyzer.
I've tried to shrink the transaction log by running a backup on it in
Enterprise Manager. However, the largest .LDF file is 33Gb and there is not
enough disk space to back this up.
So somehow I need to shrink these .LDF transaction log files. Also, if
anyone could advise on how to stop this situation happening again, that'd be
fantastic. If anyone can give advice on how to do this, you really would be
saving my life!1. Consider the recovery model for the database. If not "simple" then SQL Server will not empty the
log files automatically, and the log will keep growing. When not "simple", only backup of the
transaction log will empty the log files.
2. After either setting to simple or scheduling regular transaction log backups, verify that the log
files are more or less empty. Use:
DBCC SQLPERF(LOGSPACE)
3. Now time for the shrink. Use DBCC SHRINKFILE, (not SHRINKDATABASE), as documented in Books
Online.
Also, there are some links and general info about shrink on
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Fluffy_Ninja" <FluffyNinja@.discussions.microsoft.com> wrote in message
news:C338EACF-9E37-47B3-B307-D9A15A87539C@.microsoft.com...
> Hello. I've got a situation where a live database has stopped working because
> the transaction logs have filled all available disk space. I would really
> appreciate any advice on how to get it running again. My knowledge of SQL
> Server 2000 is very limited so I would need so quite detailed instructions! I
> am happy with entering statements into Query Analyzer.
> I've tried to shrink the transaction log by running a backup on it in
> Enterprise Manager. However, the largest .LDF file is 33Gb and there is not
> enough disk space to back this up.
> So somehow I need to shrink these .LDF transaction log files. Also, if
> anyone could advise on how to stop this situation happening again, that'd be
> fantastic. If anyone can give advice on how to do this, you really would be
> saving my life!|||Hi,
What is your recovery model? Full, Simple ?
If you are using Full recovery model then you need to backup the transaction
log regularly.
If you don't need to set recovery model to Full then use Simple Recovery
Model you can find detailed information about it from Books Online.
Check out BACKUP LOG WITH TRUNCATE_ONLY (if it's acceptable for you..)
FROM BOL:
NO_LOG | TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it and
truncates the log. This option frees space. Specifying a backup device is
unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY are
synonyms.
After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes
recorded in the log are not recoverable. For recovery purposes, immediately
execute BACKUP DATABASE.
"Fluffy_Ninja" wrote:
> Hello. I've got a situation where a live database has stopped working because
> the transaction logs have filled all available disk space. I would really
> appreciate any advice on how to get it running again. My knowledge of SQL
> Server 2000 is very limited so I would need so quite detailed instructions! I
> am happy with entering statements into Query Analyzer.
> I've tried to shrink the transaction log by running a backup on it in
> Enterprise Manager. However, the largest .LDF file is 33Gb and there is not
> enough disk space to back this up.
> So somehow I need to shrink these .LDF transaction log files. Also, if
> anyone could advise on how to stop this situation happening again, that'd be
> fantastic. If anyone can give advice on how to do this, you really would be
> saving my life!

Cannot shrink the transaction logs.

I have a SQL Server 2000 SP3 database that has a large transaction
file (Size 9384.99 space used 87MB). I would like to shrink it, but
it's not working. I don't get any errors, the file just doesn't get any
smaller.
I'm using the Enterprise Manager. Datavbases.. Tasks.. Shrink
Databases.. Shrink File.. selecting the log file and trying both
"Compress" and "Truncate: options.
I can see a progress bar, so i know its doing something However the
file does not get smaller. And I have no idea why.
Any suggestions (including "go find someone who knows what he's
doing") are welcome
Jim Helfer
WTW Architects
Pittsburgh PACheck out http://www.karaszi.com/SQLServer/info_dont_shrink.asp, and pay special attention to the
section about DBCC LOGINFO.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jim Helfer" <JimHelfer@.newsgroup.nospam> wrote in message
news:eyczNyvSHHA.1200@.TK2MSFTNGP04.phx.gbl...
> I have a SQL Server 2000 SP3 database that has a large transaction file (Size 9384.99 space
> used 87MB). I would like to shrink it, but it's not working. I don't get any errors, the file
> just doesn't get any smaller.
> I'm using the Enterprise Manager. Datavbases.. Tasks.. Shrink Databases.. Shrink File..
> selecting the log file and trying both "Compress" and "Truncate: options.
> I can see a progress bar, so i know its doing something However the file does not get smaller.
> And I have no idea why.
> Any suggestions (including "go find someone who knows what he's doing") are welcome
> Jim Helfer
> WTW Architects
> Pittsburgh PA
>
>

Cannot shrink the transaction logs.

I have a SQL Server 2000 SP3 database that has a large transaction
file (Size 9384.99 space used 87MB). I would like to shrink it, but
it's not working. I don't get any errors, the file just doesn't get any
smaller.
I'm using the Enterprise Manager. Datavbases.. Tasks.. Shrink
Databases.. Shrink File.. selecting the log file and trying both
"Compress" and "Truncate: options.
I can see a progress bar, so i know its doing something However the
file does not get smaller. And I have no idea why.
Any suggestions (including "go find someone who knows what he's
doing") are welcome
Jim Helfer
WTW Architects
Pittsburgh PACheck out http://www.karaszi.com/SQLServer/info_dont_shrink.asp, and pay spe
cial attention to the
section about DBCC LOGINFO.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jim Helfer" <JimHelfer@.newsgroup.nospam> wrote in message
news:eyczNyvSHHA.1200@.TK2MSFTNGP04.phx.gbl...
> I have a SQL Server 2000 SP3 database that has a large transaction file
(Size 9384.99 space
> used 87MB). I would like to shrink it, but it's not working. I don't get
any errors, the file
> just doesn't get any smaller.
> I'm using the Enterprise Manager. Datavbases.. Tasks.. Shrink Databases
. Shrink File..
> selecting the log file and trying both "Compress" and "Truncate: options.
> I can see a progress bar, so i know its doing something However the file
does not get smaller.
> And I have no idea why.
> Any suggestions (including "go find someone who knows what he's doing") a
re welcome
> Jim Helfer
> WTW Architects
> Pittsburgh PA
>
>

Cannot shrink the transaction log

Hi Friends,
I have tried almost everything but I cant seem to shrink the
transaction log.
Executing DBCC SQLPERF(LOGSPACE)
gives me this info:
Database Log Size (MB) Log Space Used (%) Status
MY_eems 368.49219 16.034182 0
I made a complete backup of the database and transaction log and then
executed this statement:
DBCC SHRINKFILE (MYeems_log, 1)
and this is the message I got
Cannot shrink log file 2 (ghgeems_Log) because all logical log files
are in use.
DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
-- -- ---- ---- ---- -----
17 2 42880 34561 42880 34560

(1 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your
system administrator.

How can I get the transaction log to its minimum size?
Any help will be appreciated,
Thanks
A.BLooks like you need to truncate the log first before shrinking it. To
truncate the log, make sure there is no inflight transaction in the
database, then either perform a log backup or issue a checkpoint command
with the database recovery mode set to SIMPLE. DBCC ShrinkFile afterwards
should reduce the log file size.

--
Gang He
Software Design Engineer
Microsoft SQL Server Storage Engine

This posting is provided "AS IS" with no warranties, and confers no rights.
"BashiraInTrouble" <abashir_ii@.hotmail.com> wrote in message
news:aa9a64b8.0411091315.2b0761f4@.posting.google.c om...
> Hi Friends,
> I have tried almost everything but I cant seem to shrink the
> transaction log.
> Executing DBCC SQLPERF(LOGSPACE)
> gives me this info:
> Database Log Size (MB) Log Space Used (%) Status
> MY_eems 368.49219 16.034182 0
> I made a complete backup of the database and transaction log and then
> executed this statement:
> DBCC SHRINKFILE (MYeems_log, 1)
> and this is the message I got
> Cannot shrink log file 2 (ghgeems_Log) because all logical log files
> are in use.
> DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
> -- -- ---- ---- ---- -----
> 17 2 42880 34561 42880 34560
> (1 row(s) affected)
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> How can I get the transaction log to its minimum size?
> Any help will be appreciated,
> Thanks
> A.B|||Hi faculties
Should one mantain a single-user connection to the server to
truncate the log file?

Thanks in advance
Debashish|||debashish (debashish_majumdar@.rediffmail.com) writes:
> Should one mantain a single-user connection to the server to
> truncate the log file?

There is no reason to set the database in single-user mode to truncate
the log file.

What you may want to check, though, is for open transactions with
DBCC OPENTRAN. If there is a stray transaction which has been left
open, you cannot truncate the log file past the beginning of that
transaction.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||abashir_ii@.hotmail.com (BashiraInTrouble) wrote in message news:<aa9a64b8.0411091315.2b0761f4@.posting.google.com>...
> Hi Friends,
> I have tried almost everything but I cant seem to shrink the
> transaction log.
> Executing DBCC SQLPERF(LOGSPACE)
> gives me this info:
> Database Log Size (MB) Log Space Used (%) Status
> MY_eems 368.49219 16.034182 0
> I made a complete backup of the database and transaction log and then
> executed this statement:
> DBCC SHRINKFILE (MYeems_log, 1)
> and this is the message I got
> Cannot shrink log file 2 (ghgeems_Log) because all logical log files
> are in use.
> DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
> -- -- ---- ---- ---- -----
> 17 2 42880 34561 42880 34560
> (1 row(s) affected)
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> How can I get the transaction log to its minimum size?
> Any help will be appreciated,
> Thanks
> A.B

The error messasge show you the answer already. There are still some
other active transactions and the log that SQL server have to keep is
bigger than 1M.
Hope that help|||Use this script. Just change the value from "10" to "1". It will shrink
the file as much as possible. Run it a second time for better results.

Always remember to perform a full database backup after truncating the
transaction log.

Tho Nguyen wrote:
> abashir_ii@.hotmail.com (BashiraInTrouble) wrote in message news:<aa9a64b8.0411091315.2b0761f4@.posting.google.com>...
>>Hi Friends,
>>I have tried almost everything but I cant seem to shrink the
>>transaction log.
>>Executing DBCC SQLPERF(LOGSPACE)
>>gives me this info:
>>Database Log Size (MB) Log Space Used (%) Status
>>MY_eems 368.49219 16.034182 0
>>I made a complete backup of the database and transaction log and then
>>executed this statement:
>>DBCC SHRINKFILE (MYeems_log, 1)
>>and this is the message I got
>>Cannot shrink log file 2 (ghgeems_Log) because all logical log files
>>are in use.
>>DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
>>-- -- ---- ---- ---- -----
>>17 2 42880 34561 42880 34560
>>
>>(1 row(s) affected)
>>
>>DBCC execution completed. If DBCC printed error messages, contact your
>>system administrator.
>>
>>How can I get the transaction log to its minimum size?
>>Any help will be appreciated,
>>Thanks
>>A.B
>
> The error messasge show you the answer already. There are still some
> other active transactions and the log that SQL server have to keep is
> bigger than 1M.
> Hope that help

/*
Shrink a named transaction log file belonging to a database
*/
SET NOCOUNT ON;

DECLARE @.MaxMinutes INT, @.NewSize INT, @.Factor FLOAT;
/*
The process has several control parameters, most of the time you only need to
worry about the first few as these are the big controls whereas the fifth is
simply a fine tuning control.

Switch to the database we are attempting to shrink the logs for. Uncomment this to
automatically switch to a database, otherwise it uses the current db. */ --USE [MyDatabase];

-- Define the ideal size of logfile in MB and also how much time may be used to shrink the log
SET @.NewSize = 10; SET @.MaxMinutes = 1;

/*
Factor determining maximum number of pages to pad out based on the original
number of pages in use (single page = 8K). Values in the range 1.0 - 0.8 seems to work
well for many databases.

Increasing the number will increase the maximum number of pages allowed to be padded,
which should force larger amounts of data to be dropped before the process finishes.
Often speeds up shrinking very large databases which are going through the process
before the timer runs out.

Decreasing the number will decrease the maximum number of pages allowed to be padded,
which should force less work to be done. Often aids with forcing smaller databases
to shrink to minimum size when larger values were actually expanding them.

*/
SET @.Factor = 0.95;

/*
Automatically retrieve the logical filename of the largest logfile currently
assigned to the database. If you want to manually select the file to shrink
then you can retrieve a list of the files using sp_helpfile and then setting
@.LogicalFileName manually.
*/
DECLARE @.LogicalFileName SYSNAME,
@.MaxSize INT,
@.OriginalSize INT,
@.StringData VARCHAR(500);

SELECT TOP 1
@.LogicalFileName = RTRIM( [name] ),
@.MaxSize = CASE [maxsize]
WHEN -1 THEN -1
ELSE ([maxsize] * 8)/1024
END
FROM sysfiles
WHERE [groupid] = 0
ORDER BY [size] DESC;

/*
All code after this point is driven by these parameters and will not require
editing unless you need to fix a bug in the padding/shrinking process itself. */

--Check user size is not larger than maximum file size
IF @.MaxSize > 0 AND @.NewSize > @.MaxSize
BEGIN
PRINT 'Note: '+CAST( @.NewSize AS VARCHAR )+'MB exceeds the limit of '+CAST( @.MaxSize AS VARCHAR )+'MB on this file, correcting...'
PRINT ''
SET @.NewSize = @.MaxSize;
END

SELECT @.OriginalSize = size -- in 8K pages
FROM sysfiles
WHERE name = @.LogicalFileName;

SELECT @.StringData = 'Original Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),@.OriginalSize) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(@.OriginalSize*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @.LogicalFileName;

PRINT @.StringData;
PRINT '' --Drop the temporary table if it already exists
IF ( OBJECT_ID('[dbo].[DummyTrans]') IS NOT NULL )
DROP TABLE [DummyTrans]

CREATE TABLE [DummyTrans]( [DummyColumn] CHAR(8000) NOT NULL );

-- Wrap log and truncate it.
DECLARE @.Counter INT,
@.MaxCount INT,
@.StartTime DATETIME,
@.TruncLog VARCHAR(500)

-- Try an initial shrink. (this is what causes data to be returned)
DBCC SHRINKFILE (@.LogicalFileName, @.NewSize)

SET @.TruncLog = 'BACKUP LOG [' + db_name() + '] WITH TRUNCATE_ONLY'; EXEC (@.TruncLog)

-- Configure limiter
IF @.OriginalSize / @.Factor > 50000
SET @.MaxCount = 50000;
ELSE
SET @.MaxCount = @.OriginalSize * @.Factor;

-- Attempt to shrink down the log file
PRINT 'Minimum Quantity : '+CAST( @.MaxCount AS VARCHAR(10) ); IF @.MaxMinutes = 1
PRINT 'Maximum Time : '+CAST( @.MaxMinutes AS VARCHAR(10) )+' minute ('+CAST( @.MaxMinutes*60 AS VARCHAR(10) )+' seconds)'; ELSE
PRINT 'Maximum Time : '+CAST( @.MaxMinutes AS VARCHAR(10) )+' minutes ('+CAST( @.MaxMinutes*60 AS VARCHAR(10) )+' seconds)'; PRINT '';

SET @.Counter = 0;
SET @.StartTime = GETDATE();

--loop the padding code to reduce the log while
-- within time limit and
-- log has not been shrunk enough
WHILE (
(@.MaxMinutes*60 > DATEDIFF(ss, @.StartTime, GETDATE())) AND
(@.OriginalSize = (SELECT size FROM sysfiles WHERE name =
@.LogicalFileName)) AND
((@.OriginalSize * 8 / 1024) > @.NewSize)
)
BEGIN --Outer loop.

--pad out the logfile a page at a time while
-- number of pages padded does not exceed our maximum page padding limit
-- within time limit and
-- log has not been shrunk enough
WHILE (
(@.Counter < @.MaxCount) AND
(@.MaxMinutes*60 > DATEDIFF(ss, @.StartTime, GETDATE())) AND
(@.OriginalSize = (SELECT size FROM sysfiles WHERE name =
@.LogicalFileName)) AND
((@.OriginalSize * 8 / 1024) > @.NewSize)
)
BEGIN --Inner loop

INSERT INTO DummyTrans VALUES ('Fill Log') -- Because it is a char field it inserts 8000 bytes.
DELETE FROM DummyTrans

SET @.Counter = @.Counter + 1

--Every 1,000 cycles tell the user what is going on
IF ROUND( @.Counter , -3 ) = @.Counter
BEGIN
PRINT 'Padded '+LTRIM( CAST( @.Counter*8 AS VARCHAR(10) ) )+'K @. '+LTRIM( CAST( DATEDIFF( ss, @.StartTime, GETDATE() ) AS VARCHAR(10) ) )+' seconds';
END
END

--See if a trunc of the log shrinks it.
EXEC( @.TruncLog )

END
PRINT ''

SELECT @.StringData = 'Final Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @.LogicalFileName;

PRINT @.StringData
PRINT ''

DROP TABLE DummyTrans;
PRINT '*** Perform a full database backup ***'

SET NOCOUNT OFF

/*
Based on:

http://support.microsoft.com/suppor...s/q256/6/50.asp

Changes:
28.08.2001
Modified the inner loop so it tested the dx time so long overruns did not happen Modified the inner loop so it had a fixed minimum quantity so there was no skip in skip out

29.08.2001
Modified the inner loop so it had a dynamic minimum quantity to allow faster shrinkage

24.01.2002
Modified the USE statement so it uses brackets around the dbname Modified the @.TruncLog variable so it uses brackets around the dbname

31.05.2002
Modified the code to use PRINT instead of SELECT in several cases Modified the code to use @.MaxCount instead of two unclear rules
Modified the code to use @.Factor instead of several hard-coded values
Commented the use of @.Factor
Moved the configuration and @.Counter init code to before the start of the first loop to avoid repetition Modified the code to display the process runtime in seconds rather than minutes

17.01.2003
Modified timing display code to handle 1 minute correctly

08.05.2003
Modified the instructions to make them easier to read & understand

*/

Cannot shrink tempdb

HI There

I have a strange problem.

This is on Sql Server 2005 Standard Edition X64.

Tempdb ran out of space, ok cool no problem i know why we fixed it.

However temodb grew to the size of the disk when this happened. So now i just need to shrink the tempdb file on disk.

But no matter what i try it wont shrink.

The temodb data file is 29Gigs on disk. When i right click on th DB and you to tasks and go to shrink file, the GUI says that the file is 15 Gigs with 15 Gig free space available, i select the option to shrink to 15Gig. I click ok. Afterwards the file is still 29Gig on disk.

SO i ran sp_spaceused , it reports that tempdb has 29Gig free space, so i ran DBCC SHRINKFILE for the tempdb data file to 15 Gig, the command completely successfully , however afterwards the file is still 29Gig on disk.

My first problem is that management studio, thinks the file 15 gig not 29 gig, and i cannot shrink the file with management studio or tsql, even though they report that there is plenty of free space.

PLEASE HELP?

Thanx

You can start here.
http://support.microsoft.com/kb/307487|||...and don't forget to read up on this excellent whitepaper on tempdb.
http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx

Cannot shrink log file more than 2GB in small database

Hi2all,
I have some trouble with shrinking log file. No more user is connected
on this database. According to dbcc loginfo command log file is just
made up with active log. Here is the result
FileId_FileSize_____ StartOffset FSeqNo Status
Parity CreateLSN
2 1186201600 8192 15 2 128 0
2 1186201600 1186209792 16
2 64 0
I don't know why it remains so much space in active log because no
transaction is applied.
Can someone help me ?
Mo
--
Mo
Posted via http://dbforums.comSee
http://support.microsoft.com/default.aspx?scid=kb;en-
us;272318
http://support.microsoft.com/default.aspx?scid=kb;en-
us;256650
(and the references at the botom)
Do you have the database in full recovery mode and are you
taking log backups
If yes and no then set it to simple and read about backup
strategies in bol.