DBCC SHRINKFILE

SQL Server 2000
I have a large .ldf file that I want to shrink, however, DBCC SHRINKFILE
will only shrink it so much. The file went from 74 MB to 50 MB, but won't go
smaller. I have been routinely shrinking low use DB log files to 20 MB, so I
know that's not it.
Original: DB_Log 74 MB
Ran:
use DB
exec sp_helpfile -- to get logical name
BACKUP LOG DB WITH TRUNCATE_ONLY
DBCC SHRINKFILE (DB_Log, 20, TRUNCATEONLY)
I get the message: "Cannot shrink log file 2 (DB_Log) because total number
of logical log files cannot be fewer than 2."
The output of 'sp_helpfile' is:
DB_Data 1 e:\data\DB.mdf PRIMARY 149504 KB Unlimited 5%
data only
DB_Log 2 e:\data\DB_Log.ldf NULL 51080 KB Unlimited
5% log only
The output of: DBCC sqlperf(logspace)
DB 49.875 0.40922618 0
Says there are little, to no transactions in it.
Now the Log file is 50 MB and won't shrink any further.
Whats up with that?Did you run DBCC OPENTRAN to check out for possible open\uncommited
transactions?
If there are active virtual logs in it, it's not gonna shrink. Besides, you
could use DBCC LOGINFO('myDatabase') as Tibor mentioned in his great
article. I suggest you to check it out from the following link:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Ekrem Önsoy
"Jay" <nospan@.nospam.org> wrote in message
news:emoz%23e1DIHA.1164@.TK2MSFTNGP02.phx.gbl...
> SQL Server 2000
> I have a large .ldf file that I want to shrink, however, DBCC SHRINKFILE
> will only shrink it so much. The file went from 74 MB to 50 MB, but won't
> go smaller. I have been routinely shrinking low use DB log files to 20 MB,
> so I know that's not it.
> Original: DB_Log 74 MB
> Ran:
> use DB
> exec sp_helpfile -- to get logical name
> BACKUP LOG DB WITH TRUNCATE_ONLY
> DBCC SHRINKFILE (DB_Log, 20, TRUNCATEONLY)
> I get the message: "Cannot shrink log file 2 (DB_Log) because total number
> of logical log files cannot be fewer than 2."
> The output of 'sp_helpfile' is:
> DB_Data 1 e:\data\DB.mdf PRIMARY 149504 KB Unlimited 5%
> data only
> DB_Log 2 e:\data\DB_Log.ldf NULL 51080 KB Unlimited
> 5% log only
> The output of: DBCC sqlperf(logspace)
> DB 49.875 0.40922618 0
> Says there are little, to no transactions in it.
> Now the Log file is 50 MB and won't shrink any further.
>
> Whats up with that?
>|||Well, that info allowed me to shrink the 2.5 GB logfile. However, I have two
left that that won't shrink below 50 MB (both DB's are about 150 MB and they
don't get used much), which I would like to get to 20 MB.
The message: "Cannot shrink log file 2 (DB_Log) because total number of
logical log files cannot be fewer than 2."
Seems to be refering to the VLF's as both DB's only get two rows returned
from DBCC LOGINFO
2 26,148,864 8192 189239 0 128 0
2 26,148,864 26157056 189240 2 64 0
and
2 26,148,864 8192 189110 2 128 0
2 26,148,864 26157056 189109 0 128 0
I'm guessing that this is because they were created with a 50 MB log file
and (most important) that I would have to do a "dump and reload" to get the
physical file smaller.
"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:79B1E902-AD6B-4DD7-BB4D-9896B31613D8@.microsoft.com...
> Did you run DBCC OPENTRAN to check out for possible open\uncommited
> transactions?
> If there are active virtual logs in it, it's not gonna shrink. Besides,
> you could use DBCC LOGINFO('myDatabase') as Tibor mentioned in his great
> article. I suggest you to check it out from the following link:
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>
> --
> Ekrem Önsoy
>
> "Jay" <nospan@.nospam.org> wrote in message
> news:emoz%23e1DIHA.1164@.TK2MSFTNGP02.phx.gbl...
>> SQL Server 2000
>> I have a large .ldf file that I want to shrink, however, DBCC SHRINKFILE
>> will only shrink it so much. The file went from 74 MB to 50 MB, but won't
>> go smaller. I have been routinely shrinking low use DB log files to 20
>> MB, so I know that's not it.
>> Original: DB_Log 74 MB
>> Ran:
>> use DB
>> exec sp_helpfile -- to get logical name
>> BACKUP LOG DB WITH TRUNCATE_ONLY
>> DBCC SHRINKFILE (DB_Log, 20, TRUNCATEONLY)
>> I get the message: "Cannot shrink log file 2 (DB_Log) because total
>> number of logical log files cannot be fewer than 2."
>> The output of 'sp_helpfile' is:
>> DB_Data 1 e:\data\DB.mdf PRIMARY 149504 KB Unlimited
>> 5% data only
>> DB_Log 2 e:\data\DB_Log.ldf NULL 51080 KB
>> Unlimited 5% log only
>> The output of: DBCC sqlperf(logspace)
>> DB 49.875 0.40922618 0
>> Says there are little, to no transactions in it.
>> Now the Log file is 50 MB and won't shrink any further.
>>
>> Whats up with that?
>|||If they were originally created at 50MB they may not be able to shrink any
further. If you run DBCC LOGINFO do you only have 1 VLF? If so you can't
shrink anymore. Is 30MB really an issue?
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Jay" <nospan@.nospam.org> wrote in message
news:uO0NgV2DIHA.5328@.TK2MSFTNGP05.phx.gbl...
> Well, that info allowed me to shrink the 2.5 GB logfile. However, I have
> two left that that won't shrink below 50 MB (both DB's are about 150 MB
> and they don't get used much), which I would like to get to 20 MB.
> The message: "Cannot shrink log file 2 (DB_Log) because total number of
> logical log files cannot be fewer than 2."
> Seems to be refering to the VLF's as both DB's only get two rows returned
> from DBCC LOGINFO
> 2 26,148,864 8192 189239 0 128 0
> 2 26,148,864 26157056 189240 2 64 0
> and
> 2 26,148,864 8192 189110 2 128 0
> 2 26,148,864 26157056 189109 0 128 0
> I'm guessing that this is because they were created with a 50 MB log file
> and (most important) that I would have to do a "dump and reload" to get
> the physical file smaller.
>
> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
> news:79B1E902-AD6B-4DD7-BB4D-9896B31613D8@.microsoft.com...
>> Did you run DBCC OPENTRAN to check out for possible open\uncommited
>> transactions?
>> If there are active virtual logs in it, it's not gonna shrink. Besides,
>> you could use DBCC LOGINFO('myDatabase') as Tibor mentioned in his great
>> article. I suggest you to check it out from the following link:
>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>>
>> --
>> Ekrem Önsoy
>>
>> "Jay" <nospan@.nospam.org> wrote in message
>> news:emoz%23e1DIHA.1164@.TK2MSFTNGP02.phx.gbl...
>> SQL Server 2000
>> I have a large .ldf file that I want to shrink, however, DBCC SHRINKFILE
>> will only shrink it so much. The file went from 74 MB to 50 MB, but
>> won't go smaller. I have been routinely shrinking low use DB log files
>> to 20 MB, so I know that's not it.
>> Original: DB_Log 74 MB
>> Ran:
>> use DB
>> exec sp_helpfile -- to get logical name
>> BACKUP LOG DB WITH TRUNCATE_ONLY
>> DBCC SHRINKFILE (DB_Log, 20, TRUNCATEONLY)
>> I get the message: "Cannot shrink log file 2 (DB_Log) because total
>> number of logical log files cannot be fewer than 2."
>> The output of 'sp_helpfile' is:
>> DB_Data 1 e:\data\DB.mdf PRIMARY 149504 KB Unlimited
>> 5% data only
>> DB_Log 2 e:\data\DB_Log.ldf NULL 51080 KB
>> Unlimited 5% log only
>> The output of: DBCC sqlperf(logspace)
>> DB 49.875 0.40922618 0
>> Says there are little, to no transactions in it.
>> Now the Log file is 50 MB and won't shrink any further.
>>
>> Whats up with that?
>>
>|||As other has noted, you seem to be down to only two VLFs, which is the minimum number for a log
file. SQL Server need to be able to "circle through" the VLFs, which is difficult to do with only
one VLF. I believe that you get a minimum of 4 VLFs when you create a log file (although haven't
seen the exact algorithm), so probably the log file was created with a larger size, perhaps 100MB.
I'm with Andrew on this, leave them at 50MB.
You *might* be able to get the size down by adding another file, then shrinking the 50MB file using
the EMPTYFILE option, verifying that the active log is on the new file and then ALTER DATABASE
REMOVE FILE. The trick here is that you can't remove the file until LOGINFO show you that non of the
VLFs are used.
I say "might", because I'm not sure whether SQL Server has the concept of "a primary" log file such
as it has for data files. I don't think so, at least I don't see any architectural reasons.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <nospan@.nospam.org> wrote in message news:uO0NgV2DIHA.5328@.TK2MSFTNGP05.phx.gbl...
> Well, that info allowed me to shrink the 2.5 GB logfile. However, I have two left that that won't
> shrink below 50 MB (both DB's are about 150 MB and they don't get used much), which I would like
> to get to 20 MB.
> The message: "Cannot shrink log file 2 (DB_Log) because total number of logical log files cannot
> be fewer than 2."
> Seems to be refering to the VLF's as both DB's only get two rows returned from DBCC LOGINFO
> 2 26,148,864 8192 189239 0 128 0
> 2 26,148,864 26157056 189240 2 64 0
> and
> 2 26,148,864 8192 189110 2 128 0
> 2 26,148,864 26157056 189109 0 128 0
> I'm guessing that this is because they were created with a 50 MB log file and (most important)
> that I would have to do a "dump and reload" to get the physical file smaller.
>
> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
> news:79B1E902-AD6B-4DD7-BB4D-9896B31613D8@.microsoft.com...
>> Did you run DBCC OPENTRAN to check out for possible open\uncommited transactions?
>> If there are active virtual logs in it, it's not gonna shrink. Besides, you could use DBCC
>> LOGINFO('myDatabase') as Tibor mentioned in his great article. I suggest you to check it out from
>> the following link:
>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>>
>> --
>> Ekrem Önsoy
>>
>> "Jay" <nospan@.nospam.org> wrote in message news:emoz%23e1DIHA.1164@.TK2MSFTNGP02.phx.gbl...
>> SQL Server 2000
>> I have a large .ldf file that I want to shrink, however, DBCC SHRINKFILE will only shrink it so
>> much. The file went from 74 MB to 50 MB, but won't go smaller. I have been routinely shrinking
>> low use DB log files to 20 MB, so I know that's not it.
>> Original: DB_Log 74 MB
>> Ran:
>> use DB
>> exec sp_helpfile -- to get logical name
>> BACKUP LOG DB WITH TRUNCATE_ONLY
>> DBCC SHRINKFILE (DB_Log, 20, TRUNCATEONLY)
>> I get the message: "Cannot shrink log file 2 (DB_Log) because total number of logical log files
>> cannot be fewer than 2."
>> The output of 'sp_helpfile' is:
>> DB_Data 1 e:\data\DB.mdf PRIMARY 149504 KB Unlimited 5% data only
>> DB_Log 2 e:\data\DB_Log.ldf NULL 51080 KB Unlimited 5% log only
>> The output of: DBCC sqlperf(logspace)
>> DB 49.875 0.40922618 0
>> Says there are little, to no transactions in it.
>> Now the Log file is 50 MB and won't shrink any further.
>>
>> Whats up with that?
>>
>|||There is a "primary log file" thing and it can be emptied but can not be
removed.
For information only.
--
Ekrem Önsoy
MCBDA, MCTS: SQL Server 2005, MCITP:DBA, MCSD.Net, MCSE, MCT
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:BF611763-4529-40F1-81EB-22476C01611F@.microsoft.com...
> As other has noted, you seem to be down to only two VLFs, which is the
> minimum number for a log file. SQL Server need to be able to "circle
> through" the VLFs, which is difficult to do with only one VLF. I believe
> that you get a minimum of 4 VLFs when you create a log file (although
> haven't seen the exact algorithm), so probably the log file was created
> with a larger size, perhaps 100MB.
> I'm with Andrew on this, leave them at 50MB.
> You *might* be able to get the size down by adding another file, then
> shrinking the 50MB file using the EMPTYFILE option, verifying that the
> active log is on the new file and then ALTER DATABASE REMOVE FILE. The
> trick here is that you can't remove the file until LOGINFO show you that
> non of the VLFs are used.
> I say "might", because I'm not sure whether SQL Server has the concept of
> "a primary" log file such as it has for data files. I don't think so, at
> least I don't see any architectural reasons.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jay" <nospan@.nospam.org> wrote in message
> news:uO0NgV2DIHA.5328@.TK2MSFTNGP05.phx.gbl...
>> Well, that info allowed me to shrink the 2.5 GB logfile. However, I have
>> two left that that won't shrink below 50 MB (both DB's are about 150 MB
>> and they don't get used much), which I would like to get to 20 MB.
>> The message: "Cannot shrink log file 2 (DB_Log) because total number of
>> logical log files cannot be fewer than 2."
>> Seems to be refering to the VLF's as both DB's only get two rows returned
>> from DBCC LOGINFO
>> 2 26,148,864 8192 189239 0 128 0
>> 2 26,148,864 26157056 189240 2 64 0
>> and
>> 2 26,148,864 8192 189110 2 128 0
>> 2 26,148,864 26157056 189109 0 128 0
>> I'm guessing that this is because they were created with a 50 MB log file
>> and (most important) that I would have to do a "dump and reload" to get
>> the physical file smaller.
>>
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:79B1E902-AD6B-4DD7-BB4D-9896B31613D8@.microsoft.com...
>> Did you run DBCC OPENTRAN to check out for possible open\uncommited
>> transactions?
>> If there are active virtual logs in it, it's not gonna shrink. Besides,
>> you could use DBCC LOGINFO('myDatabase') as Tibor mentioned in his great
>> article. I suggest you to check it out from the following link:
>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>>
>> --
>> Ekrem Önsoy
>>
>> "Jay" <nospan@.nospam.org> wrote in message
>> news:emoz%23e1DIHA.1164@.TK2MSFTNGP02.phx.gbl...
>> SQL Server 2000
>> I have a large .ldf file that I want to shrink, however, DBCC
>> SHRINKFILE will only shrink it so much. The file went from 74 MB to 50
>> MB, but won't go smaller. I have been routinely shrinking low use DB
>> log files to 20 MB, so I know that's not it.
>> Original: DB_Log 74 MB
>> Ran:
>> use DB
>> exec sp_helpfile -- to get logical name
>> BACKUP LOG DB WITH TRUNCATE_ONLY
>> DBCC SHRINKFILE (DB_Log, 20, TRUNCATEONLY)
>> I get the message: "Cannot shrink log file 2 (DB_Log) because total
>> number of logical log files cannot be fewer than 2."
>> The output of 'sp_helpfile' is:
>> DB_Data 1 e:\data\DB.mdf PRIMARY 149504 KB Unlimited
>> 5% data only
>> DB_Log 2 e:\data\DB_Log.ldf NULL 51080 KB
>> Unlimited 5% log only
>> The output of: DBCC sqlperf(logspace)
>> DB 49.875 0.40922618 0
>> Says there are little, to no transactions in it.
>> Now the Log file is 50 MB and won't shrink any further.
>>
>> Whats up with that?
>>
>>
>|||> There is a "primary log file" thing and it can be emptied but can not be removed.
Yes, I see that now. The error message is pretty clear on that:
"Msg 5020, Level 16, State 1, Line 1
The primary data or log file cannot be removed from a database."
Thanks Ekrem.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:86D5E5D2-A204-4554-A271-F6F73AE4D296@.microsoft.com...
> There is a "primary log file" thing and it can be emptied but can not be removed.
> For information only.
> --
> Ekrem Önsoy
> MCBDA, MCTS: SQL Server 2005, MCITP:DBA, MCSD.Net, MCSE, MCT
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:BF611763-4529-40F1-81EB-22476C01611F@.microsoft.com...
>> As other has noted, you seem to be down to only two VLFs, which is the minimum number for a log
>> file. SQL Server need to be able to "circle through" the VLFs, which is difficult to do with only
>> one VLF. I believe that you get a minimum of 4 VLFs when you create a log file (although haven't
>> seen the exact algorithm), so probably the log file was created with a larger size, perhaps
>> 100MB.
>> I'm with Andrew on this, leave them at 50MB.
>> You *might* be able to get the size down by adding another file, then shrinking the 50MB file
>> using the EMPTYFILE option, verifying that the active log is on the new file and then ALTER
>> DATABASE REMOVE FILE. The trick here is that you can't remove the file until LOGINFO show you
>> that non of the VLFs are used.
>> I say "might", because I'm not sure whether SQL Server has the concept of "a primary" log file
>> such as it has for data files. I don't think so, at least I don't see any architectural reasons.
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Jay" <nospan@.nospam.org> wrote in message news:uO0NgV2DIHA.5328@.TK2MSFTNGP05.phx.gbl...
>> Well, that info allowed me to shrink the 2.5 GB logfile. However, I have two left that that
>> won't shrink below 50 MB (both DB's are about 150 MB and they don't get used much), which I
>> would like to get to 20 MB.
>> The message: "Cannot shrink log file 2 (DB_Log) because total number of logical log files cannot
>> be fewer than 2."
>> Seems to be refering to the VLF's as both DB's only get two rows returned from DBCC LOGINFO
>> 2 26,148,864 8192 189239 0 128 0
>> 2 26,148,864 26157056 189240 2 64 0
>> and
>> 2 26,148,864 8192 189110 2 128 0
>> 2 26,148,864 26157056 189109 0 128 0
>> I'm guessing that this is because they were created with a 50 MB log file and (most important)
>> that I would have to do a "dump and reload" to get the physical file smaller.
>>
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:79B1E902-AD6B-4DD7-BB4D-9896B31613D8@.microsoft.com...
>> Did you run DBCC OPENTRAN to check out for possible open\uncommited transactions?
>> If there are active virtual logs in it, it's not gonna shrink. Besides, you could use DBCC
>> LOGINFO('myDatabase') as Tibor mentioned in his great article. I suggest you to check it out
>> from the following link:
>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>>
>> --
>> Ekrem Önsoy
>>
>> "Jay" <nospan@.nospam.org> wrote in message news:emoz%23e1DIHA.1164@.TK2MSFTNGP02.phx.gbl...
>> SQL Server 2000
>> I have a large .ldf file that I want to shrink, however, DBCC SHRINKFILE will only shrink it
>> so much. The file went from 74 MB to 50 MB, but won't go smaller. I have been routinely
>> shrinking low use DB log files to 20 MB, so I know that's not it.
>> Original: DB_Log 74 MB
>> Ran:
>> use DB
>> exec sp_helpfile -- to get logical name
>> BACKUP LOG DB WITH TRUNCATE_ONLY
>> DBCC SHRINKFILE (DB_Log, 20, TRUNCATEONLY)
>> I get the message: "Cannot shrink log file 2 (DB_Log) because total number of logical log
>> files cannot be fewer than 2."
>> The output of 'sp_helpfile' is:
>> DB_Data 1 e:\data\DB.mdf PRIMARY 149504 KB Unlimited 5% data only
>> DB_Log 2 e:\data\DB_Log.ldf NULL 51080 KB Unlimited 5% log only
>> The output of: DBCC sqlperf(logspace)
>> DB 49.875 0.40922618 0
>> Says there are little, to no transactions in it.
>> Now the Log file is 50 MB and won't shrink any further.
>>
>> Whats up with that?
>>
>>
>|||Thanks guys.
You're right, 30 MB is no big deal. Still, if I could have whacked the
excess, I would have prefered to.
The issue is actually one of my own creation. I have a report that tells me
when a log file is over 25% of the data file and also over 20 MB. So, a 50
MB log on a 140 MB DB fires the alarm ( I'm also looking for file
growth/shrinkage and tracking overall drive usage across the farm). I just
increased the data file to 201 MB and will deal with it later.
I suppose a dump and reload is my only option ... hmm, I think I'll try a
backup/restore from EM, see what that does.
Thanks,
Jay
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:BF611763-4529-40F1-81EB-22476C01611F@.microsoft.com...
> As other has noted, you seem to be down to only two VLFs, which is the
> minimum number for a log file. SQL Server need to be able to "circle
> through" the VLFs, which is difficult to do with only one VLF. I believe
> that you get a minimum of 4 VLFs when you create a log file (although
> haven't seen the exact algorithm), so probably the log file was created
> with a larger size, perhaps 100MB.
> I'm with Andrew on this, leave them at 50MB.
> You *might* be able to get the size down by adding another file, then
> shrinking the 50MB file using the EMPTYFILE option, verifying that the
> active log is on the new file and then ALTER DATABASE REMOVE FILE. The
> trick here is that you can't remove the file until LOGINFO show you that
> non of the VLFs are used.
> I say "might", because I'm not sure whether SQL Server has the concept of
> "a primary" log file such as it has for data files. I don't think so, at
> least I don't see any architectural reasons.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jay" <nospan@.nospam.org> wrote in message
> news:uO0NgV2DIHA.5328@.TK2MSFTNGP05.phx.gbl...
>> Well, that info allowed me to shrink the 2.5 GB logfile. However, I have
>> two left that that won't shrink below 50 MB (both DB's are about 150 MB
>> and they don't get used much), which I would like to get to 20 MB.
>> The message: "Cannot shrink log file 2 (DB_Log) because total number of
>> logical log files cannot be fewer than 2."
>> Seems to be refering to the VLF's as both DB's only get two rows returned
>> from DBCC LOGINFO
>> 2 26,148,864 8192 189239 0 128 0
>> 2 26,148,864 26157056 189240 2 64 0
>> and
>> 2 26,148,864 8192 189110 2 128 0
>> 2 26,148,864 26157056 189109 0 128 0
>> I'm guessing that this is because they were created with a 50 MB log file
>> and (most important) that I would have to do a "dump and reload" to get
>> the physical file smaller.
>>
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:79B1E902-AD6B-4DD7-BB4D-9896B31613D8@.microsoft.com...
>> Did you run DBCC OPENTRAN to check out for possible open\uncommited
>> transactions?
>> If there are active virtual logs in it, it's not gonna shrink. Besides,
>> you could use DBCC LOGINFO('myDatabase') as Tibor mentioned in his great
>> article. I suggest you to check it out from the following link:
>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>>
>> --
>> Ekrem Önsoy
>>
>> "Jay" <nospan@.nospam.org> wrote in message
>> news:emoz%23e1DIHA.1164@.TK2MSFTNGP02.phx.gbl...
>> SQL Server 2000
>> I have a large .ldf file that I want to shrink, however, DBCC
>> SHRINKFILE will only shrink it so much. The file went from 74 MB to 50
>> MB, but won't go smaller. I have been routinely shrinking low use DB
>> log files to 20 MB, so I know that's not it.
>> Original: DB_Log 74 MB
>> Ran:
>> use DB
>> exec sp_helpfile -- to get logical name
>> BACKUP LOG DB WITH TRUNCATE_ONLY
>> DBCC SHRINKFILE (DB_Log, 20, TRUNCATEONLY)
>> I get the message: "Cannot shrink log file 2 (DB_Log) because total
>> number of logical log files cannot be fewer than 2."
>> The output of 'sp_helpfile' is:
>> DB_Data 1 e:\data\DB.mdf PRIMARY 149504 KB Unlimited
>> 5% data only
>> DB_Log 2 e:\data\DB_Log.ldf NULL 51080 KB
>> Unlimited 5% log only
>> The output of: DBCC sqlperf(logspace)
>> DB 49.875 0.40922618 0
>> Says there are little, to no transactions in it.
>> Now the Log file is 50 MB and won't shrink any further.
>>
>> Whats up with that?
>>
>>
>|||A backup & restore will put it back exactly like it was when the backup was
taken. You can try detaching the db and then attaching only the mdf using
sp_attach_single_file_db. Just make sure you have good backups.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Jay" <spam@.nospam.org> wrote in message
news:OyfMll$DIHA.3548@.TK2MSFTNGP06.phx.gbl...
> Thanks guys.
> You're right, 30 MB is no big deal. Still, if I could have whacked the
> excess, I would have prefered to.
> The issue is actually one of my own creation. I have a report that tells
> me when a log file is over 25% of the data file and also over 20 MB. So, a
> 50 MB log on a 140 MB DB fires the alarm ( I'm also looking for file
> growth/shrinkage and tracking overall drive usage across the farm). I just
> increased the data file to 201 MB and will deal with it later.
> I suppose a dump and reload is my only option ... hmm, I think I'll try a
> backup/restore from EM, see what that does.
> Thanks,
> Jay
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:BF611763-4529-40F1-81EB-22476C01611F@.microsoft.com...
>> As other has noted, you seem to be down to only two VLFs, which is the
>> minimum number for a log file. SQL Server need to be able to "circle
>> through" the VLFs, which is difficult to do with only one VLF. I believe
>> that you get a minimum of 4 VLFs when you create a log file (although
>> haven't seen the exact algorithm), so probably the log file was created
>> with a larger size, perhaps 100MB.
>> I'm with Andrew on this, leave them at 50MB.
>> You *might* be able to get the size down by adding another file, then
>> shrinking the 50MB file using the EMPTYFILE option, verifying that the
>> active log is on the new file and then ALTER DATABASE REMOVE FILE. The
>> trick here is that you can't remove the file until LOGINFO show you that
>> non of the VLFs are used.
>> I say "might", because I'm not sure whether SQL Server has the concept of
>> "a primary" log file such as it has for data files. I don't think so, at
>> least I don't see any architectural reasons.
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Jay" <nospan@.nospam.org> wrote in message
>> news:uO0NgV2DIHA.5328@.TK2MSFTNGP05.phx.gbl...
>> Well, that info allowed me to shrink the 2.5 GB logfile. However, I have
>> two left that that won't shrink below 50 MB (both DB's are about 150 MB
>> and they don't get used much), which I would like to get to 20 MB.
>> The message: "Cannot shrink log file 2 (DB_Log) because total number of
>> logical log files cannot be fewer than 2."
>> Seems to be refering to the VLF's as both DB's only get two rows
>> returned from DBCC LOGINFO
>> 2 26,148,864 8192 189239 0 128 0
>> 2 26,148,864 26157056 189240 2 64 0
>> and
>> 2 26,148,864 8192 189110 2 128 0
>> 2 26,148,864 26157056 189109 0 128 0
>> I'm guessing that this is because they were created with a 50 MB log
>> file and (most important) that I would have to do a "dump and reload" to
>> get the physical file smaller.
>>
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:79B1E902-AD6B-4DD7-BB4D-9896B31613D8@.microsoft.com...
>> Did you run DBCC OPENTRAN to check out for possible open\uncommited
>> transactions?
>> If there are active virtual logs in it, it's not gonna shrink. Besides,
>> you could use DBCC LOGINFO('myDatabase') as Tibor mentioned in his
>> great article. I suggest you to check it out from the following link:
>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>>
>> --
>> Ekrem Önsoy
>>
>> "Jay" <nospan@.nospam.org> wrote in message
>> news:emoz%23e1DIHA.1164@.TK2MSFTNGP02.phx.gbl...
>> SQL Server 2000
>> I have a large .ldf file that I want to shrink, however, DBCC
>> SHRINKFILE will only shrink it so much. The file went from 74 MB to 50
>> MB, but won't go smaller. I have been routinely shrinking low use DB
>> log files to 20 MB, so I know that's not it.
>> Original: DB_Log 74 MB
>> Ran:
>> use DB
>> exec sp_helpfile -- to get logical name
>> BACKUP LOG DB WITH TRUNCATE_ONLY
>> DBCC SHRINKFILE (DB_Log, 20, TRUNCATEONLY)
>> I get the message: "Cannot shrink log file 2 (DB_Log) because total
>> number of logical log files cannot be fewer than 2."
>> The output of 'sp_helpfile' is:
>> DB_Data 1 e:\data\DB.mdf PRIMARY 149504 KB
>> Unlimited 5% data only
>> DB_Log 2 e:\data\DB_Log.ldf NULL 51080 KB
>> Unlimited 5% log only
>> The output of: DBCC sqlperf(logspace)
>> DB 49.875 0.40922618 0
>> Says there are little, to no transactions in it.
>> Now the Log file is 50 MB and won't shrink any further.
>>
>> Whats up with that?
>>
>>
>|||> The issue is actually one of my own creation. I have a report that tells me when a log file is
> over 25% of the data file and also over 20 MB.
Sorry if I state the obvious, but how about changing your report to warn only of > 50 or 100MB or
something like that? :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <spam@.nospam.org> wrote in message news:OyfMll$DIHA.3548@.TK2MSFTNGP06.phx.gbl...
> Thanks guys.
> You're right, 30 MB is no big deal. Still, if I could have whacked the excess, I would have
> prefered to.
> The issue is actually one of my own creation. I have a report that tells me when a log file is
> over 25% of the data file and also over 20 MB. So, a 50 MB log on a 140 MB DB fires the alarm (
> I'm also looking for file growth/shrinkage and tracking overall drive usage across the farm). I
> just increased the data file to 201 MB and will deal with it later.
> I suppose a dump and reload is my only option ... hmm, I think I'll try a backup/restore from EM,
> see what that does.
> Thanks,
> Jay
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:BF611763-4529-40F1-81EB-22476C01611F@.microsoft.com...
>> As other has noted, you seem to be down to only two VLFs, which is the minimum number for a log
>> file. SQL Server need to be able to "circle through" the VLFs, which is difficult to do with only
>> one VLF. I believe that you get a minimum of 4 VLFs when you create a log file (although haven't
>> seen the exact algorithm), so probably the log file was created with a larger size, perhaps
>> 100MB.
>> I'm with Andrew on this, leave them at 50MB.
>> You *might* be able to get the size down by adding another file, then shrinking the 50MB file
>> using the EMPTYFILE option, verifying that the active log is on the new file and then ALTER
>> DATABASE REMOVE FILE. The trick here is that you can't remove the file until LOGINFO show you
>> that non of the VLFs are used.
>> I say "might", because I'm not sure whether SQL Server has the concept of "a primary" log file
>> such as it has for data files. I don't think so, at least I don't see any architectural reasons.
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Jay" <nospan@.nospam.org> wrote in message news:uO0NgV2DIHA.5328@.TK2MSFTNGP05.phx.gbl...
>> Well, that info allowed me to shrink the 2.5 GB logfile. However, I have two left that that
>> won't shrink below 50 MB (both DB's are about 150 MB and they don't get used much), which I
>> would like to get to 20 MB.
>> The message: "Cannot shrink log file 2 (DB_Log) because total number of logical log files cannot
>> be fewer than 2."
>> Seems to be refering to the VLF's as both DB's only get two rows returned from DBCC LOGINFO
>> 2 26,148,864 8192 189239 0 128 0
>> 2 26,148,864 26157056 189240 2 64 0
>> and
>> 2 26,148,864 8192 189110 2 128 0
>> 2 26,148,864 26157056 189109 0 128 0
>> I'm guessing that this is because they were created with a 50 MB log file and (most important)
>> that I would have to do a "dump and reload" to get the physical file smaller.
>>
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:79B1E902-AD6B-4DD7-BB4D-9896B31613D8@.microsoft.com...
>> Did you run DBCC OPENTRAN to check out for possible open\uncommited transactions?
>> If there are active virtual logs in it, it's not gonna shrink. Besides, you could use DBCC
>> LOGINFO('myDatabase') as Tibor mentioned in his great article. I suggest you to check it out
>> from the following link:
>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>>
>> --
>> Ekrem Önsoy
>>
>> "Jay" <nospan@.nospam.org> wrote in message news:emoz%23e1DIHA.1164@.TK2MSFTNGP02.phx.gbl...
>> SQL Server 2000
>> I have a large .ldf file that I want to shrink, however, DBCC SHRINKFILE will only shrink it
>> so much. The file went from 74 MB to 50 MB, but won't go smaller. I have been routinely
>> shrinking low use DB log files to 20 MB, so I know that's not it.
>> Original: DB_Log 74 MB
>> Ran:
>> use DB
>> exec sp_helpfile -- to get logical name
>> BACKUP LOG DB WITH TRUNCATE_ONLY
>> DBCC SHRINKFILE (DB_Log, 20, TRUNCATEONLY)
>> I get the message: "Cannot shrink log file 2 (DB_Log) because total number of logical log
>> files cannot be fewer than 2."
>> The output of 'sp_helpfile' is:
>> DB_Data 1 e:\data\DB.mdf PRIMARY 149504 KB Unlimited 5% data only
>> DB_Log 2 e:\data\DB_Log.ldf NULL 51080 KB Unlimited 5% log only
>> The output of: DBCC sqlperf(logspace)
>> DB 49.875 0.40922618 0
>> Says there are little, to no transactions in it.
>> Now the Log file is 50 MB and won't shrink any further.
>>
>> Whats up with that?
>>
>>
>|||It's a reasonable suggestion, however, I'm still absorbing what is
reasonable and with my backup program running once a minute and backing up
the logs is they exceed 75% (or n minutes have passed) I think the logs
should be smaller.
I'm in a situation where I need to both understand what is happening and
maintain real world constraints. By assuming a smaller number, I get to see
things. For example, if I set a transaction log to 20 MB and it grows anyway
(but the database is still only 80 MB), I've learned something about the
nature of that DB and can administrate it better.
Remember that the main force driving this are things like that 11 GB log
file (which, thankfully, was the extreme).
Jay
PS. FWIW, the thresholds are command line args with defaults of 20 MB & 25%,
I'm just calling it using the defaults.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:E9B771CE-8D75-4624-84A9-7AF75E73F24B@.microsoft.com...
>> The issue is actually one of my own creation. I have a report that tells
>> me when a log file is over 25% of the data file and also over 20 MB.
> Sorry if I state the obvious, but how about changing your report to warn
> only of > 50 or 100MB or something like that? :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jay" <spam@.nospam.org> wrote in message
> news:OyfMll$DIHA.3548@.TK2MSFTNGP06.phx.gbl...
>> Thanks guys.
>> You're right, 30 MB is no big deal. Still, if I could have whacked the
>> excess, I would have prefered to.
>> The issue is actually one of my own creation. I have a report that tells
>> me when a log file is over 25% of the data file and also over 20 MB. So,
>> a 50 MB log on a 140 MB DB fires the alarm ( I'm also looking for file
>> growth/shrinkage and tracking overall drive usage across the farm). I
>> just increased the data file to 201 MB and will deal with it later.
>> I suppose a dump and reload is my only option ... hmm, I think I'll try a
>> backup/restore from EM, see what that does.
>> Thanks,
>> Jay
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:BF611763-4529-40F1-81EB-22476C01611F@.microsoft.com...
>> As other has noted, you seem to be down to only two VLFs, which is the
>> minimum number for a log file. SQL Server need to be able to "circle
>> through" the VLFs, which is difficult to do with only one VLF. I believe
>> that you get a minimum of 4 VLFs when you create a log file (although
>> haven't seen the exact algorithm), so probably the log file was created
>> with a larger size, perhaps 100MB.
>> I'm with Andrew on this, leave them at 50MB.
>> You *might* be able to get the size down by adding another file, then
>> shrinking the 50MB file using the EMPTYFILE option, verifying that the
>> active log is on the new file and then ALTER DATABASE REMOVE FILE. The
>> trick here is that you can't remove the file until LOGINFO show you that
>> non of the VLFs are used.
>> I say "might", because I'm not sure whether SQL Server has the concept
>> of "a primary" log file such as it has for data files. I don't think so,
>> at least I don't see any architectural reasons.
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Jay" <nospan@.nospam.org> wrote in message
>> news:uO0NgV2DIHA.5328@.TK2MSFTNGP05.phx.gbl...
>> Well, that info allowed me to shrink the 2.5 GB logfile. However, I
>> have two left that that won't shrink below 50 MB (both DB's are about
>> 150 MB and they don't get used much), which I would like to get to 20
>> MB.
>> The message: "Cannot shrink log file 2 (DB_Log) because total number of
>> logical log files cannot be fewer than 2."
>> Seems to be refering to the VLF's as both DB's only get two rows
>> returned from DBCC LOGINFO
>> 2 26,148,864 8192 189239 0 128 0
>> 2 26,148,864 26157056 189240 2 64 0
>> and
>> 2 26,148,864 8192 189110 2 128 0
>> 2 26,148,864 26157056 189109 0 128 0
>> I'm guessing that this is because they were created with a 50 MB log
>> file and (most important) that I would have to do a "dump and reload"
>> to get the physical file smaller.
>>
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:79B1E902-AD6B-4DD7-BB4D-9896B31613D8@.microsoft.com...
>> Did you run DBCC OPENTRAN to check out for possible open\uncommited
>> transactions?
>> If there are active virtual logs in it, it's not gonna shrink.
>> Besides, you could use DBCC LOGINFO('myDatabase') as Tibor mentioned
>> in his great article. I suggest you to check it out from the following
>> link:
>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>>
>> --
>> Ekrem Önsoy
>>
>> "Jay" <nospan@.nospam.org> wrote in message
>> news:emoz%23e1DIHA.1164@.TK2MSFTNGP02.phx.gbl...
>> SQL Server 2000
>> I have a large .ldf file that I want to shrink, however, DBCC
>> SHRINKFILE will only shrink it so much. The file went from 74 MB to
>> 50 MB, but won't go smaller. I have been routinely shrinking low use
>> DB log files to 20 MB, so I know that's not it.
>> Original: DB_Log 74 MB
>> Ran:
>> use DB
>> exec sp_helpfile -- to get logical name
>> BACKUP LOG DB WITH TRUNCATE_ONLY
>> DBCC SHRINKFILE (DB_Log, 20, TRUNCATEONLY)
>> I get the message: "Cannot shrink log file 2 (DB_Log) because total
>> number of logical log files cannot be fewer than 2."
>> The output of 'sp_helpfile' is:
>> DB_Data 1 e:\data\DB.mdf PRIMARY 149504 KB
>> Unlimited 5% data only
>> DB_Log 2 e:\data\DB_Log.ldf NULL 51080 KB
>> Unlimited 5% log only
>> The output of: DBCC sqlperf(logspace)
>> DB 49.875 0.40922618 0
>> Says there are little, to no transactions in it.
>> Now the Log file is 50 MB and won't shrink any further.
>>
>> Whats up with that?
>>
>>
>>
>