When I attempt to launch DBCC SHRINKFILE for any filename that contains a
period before the extension , (example : northwnd.ldf), I receive the error
message:
"Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '.'.
What am I doing wrong? Thanks to everyone for being there to help.You need to use the ligical file name and not the physical.
Example:
use northwind
go
exec sp_helpdb northwind
go
dbcc shrinkfile (Northwind_log, 5)
go
AMB
"coenzyme" wrote:
> When I attempt to launch DBCC SHRINKFILE for any filename that contains a
> period before the extension , (example : northwnd.ldf), I receive the error
> message:
> "Server: Msg 170, Level 15, State 1, Line 2
> Line 2: Incorrect syntax near '.'.
> What am I doing wrong? Thanks to everyone for being there to help.
Showing posts with label ldf. Show all posts
Showing posts with label ldf. Show all posts
Thursday, March 29, 2012
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?
>>
>>
>>
>
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?
>>
>>
>>
>
DBCC SHRINKFILE
When I attempt to launch DBCC SHRINKFILE for any filename that contains a
period before the extension , (example : northwnd.ldf), I receive the error
message:
"Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '.'.
What am I doing wrong? Thanks to everyone for being there to help.
You need to use the ligical file name and not the physical.
Example:
use northwind
go
exec sp_helpdb northwind
go
dbcc shrinkfile (Northwind_log, 5)
go
AMB
"coenzyme" wrote:
> When I attempt to launch DBCC SHRINKFILE for any filename that contains a
> period before the extension , (example : northwnd.ldf), I receive the error
> message:
> "Server: Msg 170, Level 15, State 1, Line 2
> Line 2: Incorrect syntax near '.'.
> What am I doing wrong? Thanks to everyone for being there to help.
period before the extension , (example : northwnd.ldf), I receive the error
message:
"Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '.'.
What am I doing wrong? Thanks to everyone for being there to help.
You need to use the ligical file name and not the physical.
Example:
use northwind
go
exec sp_helpdb northwind
go
dbcc shrinkfile (Northwind_log, 5)
go
AMB
"coenzyme" wrote:
> When I attempt to launch DBCC SHRINKFILE for any filename that contains a
> period before the extension , (example : northwnd.ldf), I receive the error
> message:
> "Server: Msg 170, Level 15, State 1, Line 2
> Line 2: Incorrect syntax near '.'.
> What am I doing wrong? Thanks to everyone for being there to help.
DBCC SHRINKFILE
When I attempt to launch DBCC SHRINKFILE for any filename that contains a
period before the extension , (example : northwnd.ldf), I receive the error
message:
"Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '.'.
What am I doing wrong? Thanks to everyone for being there to help.You need to use the ligical file name and not the physical.
Example:
use northwind
go
exec sp_helpdb northwind
go
dbcc shrinkfile (Northwind_log, 5)
go
AMB
"coenzyme" wrote:
> When I attempt to launch DBCC SHRINKFILE for any filename that contains a
> period before the extension , (example : northwnd.ldf), I receive the erro
r
> message:
> "Server: Msg 170, Level 15, State 1, Line 2
> Line 2: Incorrect syntax near '.'.
> What am I doing wrong? Thanks to everyone for being there to help.
period before the extension , (example : northwnd.ldf), I receive the error
message:
"Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '.'.
What am I doing wrong? Thanks to everyone for being there to help.You need to use the ligical file name and not the physical.
Example:
use northwind
go
exec sp_helpdb northwind
go
dbcc shrinkfile (Northwind_log, 5)
go
AMB
"coenzyme" wrote:
> When I attempt to launch DBCC SHRINKFILE for any filename that contains a
> period before the extension , (example : northwnd.ldf), I receive the erro
r
> message:
> "Server: Msg 170, Level 15, State 1, Line 2
> Line 2: Incorrect syntax near '.'.
> What am I doing wrong? Thanks to everyone for being there to help.
Monday, March 19, 2012
DBCC Log does not show all the logs in LDF file
Hi,
I am using DBCC Log command to see all the logs in online log file. After
checkpoint these logs disaapear from the dbcc log result as expected.
But when I open the .LDF file using tools available in market to read the
.LDF file, I found that logs before checkpoint are still there in .LDF file
,
so when these logs will go from .LDF file and what I am gaining by doing
checkpoint?
How does DBCC Log command works? Does it read .LDF and explicitly ignore the
logs before the checkpoint or something else.
Please clear my thoughts on this issue.
Thanks a lot in advance.
Pushkarcould you please put the whole statement that you launch?
--
Current location: Alicante (ES)
"Pushkar" wrote:
> Hi,
> I am using DBCC Log command to see all the logs in online log file. After
> checkpoint these logs disaapear from the dbcc log result as expected.
> But when I open the .LDF file using tools available in market to read the
> ..LDF file, I found that logs before checkpoint are still there in .LDF fi
le,
> so when these logs will go from .LDF file and what I am gaining by doing
> checkpoint?
> How does DBCC Log command works? Does it read .LDF and explicitly ignore t
he
> logs before the checkpoint or something else.
> Please clear my thoughts on this issue.
> Thanks a lot in advance.
> Pushkar
>
>|||I just launch dbcc log(dbname,2)
and to read LDF file I used external tools.
"Enric" <vtam13@.terra.es.(donotspam)> wrote in message
news:5EE4F325-A80E-459C-8A8F-40B99E0C83AB@.microsoft.com...
> could you please put the whole statement that you launch?
> --
> Current location: Alicante (ES)
>
> "Pushkar" wrote:
>|||> I am using DBCC Log command to see all the logs in online log file. After checkpoint thes
e logs
> disaapear from the dbcc log result as expected.
So I assume the database is in simple recovery mode?
> But when I open the .LDF file using tools available in market to read the
.LDF file, I found that
> logs before checkpoint are still there in .LDF file, so when these logs wi
ll go from .LDF file and
> what I am gaining by doing checkpoint?
My guess that the checkpoint (when in simple recovery mode) makes the space
available for re-use,
but doesn't zero out the data. And that the log reader tools does show this
data which isn't part of
the active log, but haven't been zeroed out.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Pushkar" <pushkartiwari@.gmail.com> wrote in message news:eDjPDhMTGHA.1868@.TK2MSFTNGP09.phx
.gbl...
> Hi,
> I am using DBCC Log command to see all the logs in online log file. After
checkpoint these logs
> disaapear from the dbcc log result as expected.
> But when I open the .LDF file using tools available in market to read the
.LDF file, I found that
> logs before checkpoint are still there in .LDF file, so when these logs wi
ll go from .LDF file and
> what I am gaining by doing checkpoint?
> How does DBCC Log command works? Does it read .LDF and explicitly ignore t
he logs before the
> checkpoint or something else.
> Please clear my thoughts on this issue.
> Thanks a lot in advance.
> Pushkar
>|||About simple recovery model we had in our development databases such feature
put on
--
Current location: Alicante (ES)
"Tibor Karaszi" wrote:
> So I assume the database is in simple recovery mode?
>
> My guess that the checkpoint (when in simple recovery mode) makes the spac
e available for re-use,
> but doesn't zero out the data. And that the log reader tools does show thi
s data which isn't part of
> the active log, but haven't been zeroed out.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Pushkar" <pushkartiwari@.gmail.com> wrote in message news:eDjPDhMTGHA.1868
@.TK2MSFTNGP09.phx.gbl...
>|||Thanks!!!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:efZEXMNTGHA.5728@.tk2msftngp13.phx.gbl...
> So I assume the database is in simple recovery mode?
>
> My guess that the checkpoint (when in simple recovery mode) makes the
> space available for re-use, but doesn't zero out the data. And that the
> log reader tools does show this data which isn't part of the active log,
> but haven't been zeroed out.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Pushkar" <pushkartiwari@.gmail.com> wrote in message
> news:eDjPDhMTGHA.1868@.TK2MSFTNGP09.phx.gbl...
>
I am using DBCC Log command to see all the logs in online log file. After
checkpoint these logs disaapear from the dbcc log result as expected.
But when I open the .LDF file using tools available in market to read the
.LDF file, I found that logs before checkpoint are still there in .LDF file
,
so when these logs will go from .LDF file and what I am gaining by doing
checkpoint?
How does DBCC Log command works? Does it read .LDF and explicitly ignore the
logs before the checkpoint or something else.
Please clear my thoughts on this issue.
Thanks a lot in advance.
Pushkarcould you please put the whole statement that you launch?
--
Current location: Alicante (ES)
"Pushkar" wrote:
> Hi,
> I am using DBCC Log command to see all the logs in online log file. After
> checkpoint these logs disaapear from the dbcc log result as expected.
> But when I open the .LDF file using tools available in market to read the
> ..LDF file, I found that logs before checkpoint are still there in .LDF fi
le,
> so when these logs will go from .LDF file and what I am gaining by doing
> checkpoint?
> How does DBCC Log command works? Does it read .LDF and explicitly ignore t
he
> logs before the checkpoint or something else.
> Please clear my thoughts on this issue.
> Thanks a lot in advance.
> Pushkar
>
>|||I just launch dbcc log(dbname,2)
and to read LDF file I used external tools.
"Enric" <vtam13@.terra.es.(donotspam)> wrote in message
news:5EE4F325-A80E-459C-8A8F-40B99E0C83AB@.microsoft.com...
> could you please put the whole statement that you launch?
> --
> Current location: Alicante (ES)
>
> "Pushkar" wrote:
>|||> I am using DBCC Log command to see all the logs in online log file. After checkpoint thes
e logs
> disaapear from the dbcc log result as expected.
So I assume the database is in simple recovery mode?
> But when I open the .LDF file using tools available in market to read the
.LDF file, I found that
> logs before checkpoint are still there in .LDF file, so when these logs wi
ll go from .LDF file and
> what I am gaining by doing checkpoint?
My guess that the checkpoint (when in simple recovery mode) makes the space
available for re-use,
but doesn't zero out the data. And that the log reader tools does show this
data which isn't part of
the active log, but haven't been zeroed out.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Pushkar" <pushkartiwari@.gmail.com> wrote in message news:eDjPDhMTGHA.1868@.TK2MSFTNGP09.phx
.gbl...
> Hi,
> I am using DBCC Log command to see all the logs in online log file. After
checkpoint these logs
> disaapear from the dbcc log result as expected.
> But when I open the .LDF file using tools available in market to read the
.LDF file, I found that
> logs before checkpoint are still there in .LDF file, so when these logs wi
ll go from .LDF file and
> what I am gaining by doing checkpoint?
> How does DBCC Log command works? Does it read .LDF and explicitly ignore t
he logs before the
> checkpoint or something else.
> Please clear my thoughts on this issue.
> Thanks a lot in advance.
> Pushkar
>|||About simple recovery model we had in our development databases such feature
put on
--
Current location: Alicante (ES)
"Tibor Karaszi" wrote:
> So I assume the database is in simple recovery mode?
>
> My guess that the checkpoint (when in simple recovery mode) makes the spac
e available for re-use,
> but doesn't zero out the data. And that the log reader tools does show thi
s data which isn't part of
> the active log, but haven't been zeroed out.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Pushkar" <pushkartiwari@.gmail.com> wrote in message news:eDjPDhMTGHA.1868
@.TK2MSFTNGP09.phx.gbl...
>|||Thanks!!!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:efZEXMNTGHA.5728@.tk2msftngp13.phx.gbl...
> So I assume the database is in simple recovery mode?
>
> My guess that the checkpoint (when in simple recovery mode) makes the
> space available for re-use, but doesn't zero out the data. And that the
> log reader tools does show this data which isn't part of the active log,
> but haven't been zeroed out.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Pushkar" <pushkartiwari@.gmail.com> wrote in message
> news:eDjPDhMTGHA.1868@.TK2MSFTNGP09.phx.gbl...
>
Subscribe to:
Posts (Atom)