I created a file (size = 1 GB, filegrowth=1 GB) but I forgot to add it to the
correct filegroup (it defaulted to the primary).
I am trying to use DBCC shrinkfile with the emptyfile option so that I can
then go and do a 'alter database remove file'. I start the process and with
sp_who2 active it shows the command DbccFilesCompact, but it just sits there
and spins forever.
I see cpu and diskio cycles escalating but nothing ever happens.
is there a step before this that i am forgetting?
setup is: x64 sql 2005, sp1 (windwos 2003 ee x64), db in simple recovery
model.
gracias,
Robert
i should state a few more facts.
It is a data file (not log), and the whole DB 1.8 TB. The file is only 100GB.
I have tried to backup just that filegroup (only 1 file in it) and do a
emptyfile right afterwards but no luck...
"sql411@.nospam.com" wrote:
> I created a file (size = 1 GB, filegrowth=1 GB) but I forgot to add it to the
> correct filegroup (it defaulted to the primary).
> I am trying to use DBCC shrinkfile with the emptyfile option so that I can
> then go and do a 'alter database remove file'. I start the process and with
> sp_who2 active it shows the command DbccFilesCompact, but it just sits there
> and spins forever.
> I see cpu and diskio cycles escalating but nothing ever happens.
> is there a step before this that i am forgetting?
> setup is: x64 sql 2005, sp1 (windwos 2003 ee x64), db in simple recovery
> model.
> gracias,
> Robert
sql
Thursday, March 29, 2012
DBCC SHRINKFILE ('filename' , EMPTYFILE) not working
I created a file (size = 1 GB, filegrowth=1 GB) but I forgot to add it to th
e
correct filegroup (it defaulted to the primary).
I am trying to use DBCC shrinkfile with the emptyfile option so that I can
then go and do a 'alter database remove file'. I start the process and with
sp_who2 active it shows the command DbccFilesCompact, but it just sits there
and spins forever.
I see cpu and diskio cycles escalating but nothing ever happens.
is there a step before this that i am forgetting?
setup is: x64 sql 2005, sp1 (windwos 2003 ee x64), db in simple recovery
model.
gracias,
Roberti should state a few more facts.
It is a data file (not log), and the whole DB 1.8 TB. The file is only 100G
B.
I have tried to backup just that filegroup (only 1 file in it) and do a
emptyfile right afterwards but no luck...
"sql411@.nospam.com" wrote:
> I created a file (size = 1 GB, filegrowth=1 GB) but I forgot to add it to
the
> correct filegroup (it defaulted to the primary).
> I am trying to use DBCC shrinkfile with the emptyfile option so that I can
> then go and do a 'alter database remove file'. I start the process and wi
th
> sp_who2 active it shows the command DbccFilesCompact, but it just sits the
re
> and spins forever.
> I see cpu and diskio cycles escalating but nothing ever happens.
> is there a step before this that i am forgetting?
> setup is: x64 sql 2005, sp1 (windwos 2003 ee x64), db in simple recovery
> model.
> gracias,
> Robert
e
correct filegroup (it defaulted to the primary).
I am trying to use DBCC shrinkfile with the emptyfile option so that I can
then go and do a 'alter database remove file'. I start the process and with
sp_who2 active it shows the command DbccFilesCompact, but it just sits there
and spins forever.
I see cpu and diskio cycles escalating but nothing ever happens.
is there a step before this that i am forgetting?
setup is: x64 sql 2005, sp1 (windwos 2003 ee x64), db in simple recovery
model.
gracias,
Roberti should state a few more facts.
It is a data file (not log), and the whole DB 1.8 TB. The file is only 100G
B.
I have tried to backup just that filegroup (only 1 file in it) and do a
emptyfile right afterwards but no luck...
"sql411@.nospam.com" wrote:
> I created a file (size = 1 GB, filegrowth=1 GB) but I forgot to add it to
the
> correct filegroup (it defaulted to the primary).
> I am trying to use DBCC shrinkfile with the emptyfile option so that I can
> then go and do a 'alter database remove file'. I start the process and wi
th
> sp_who2 active it shows the command DbccFilesCompact, but it just sits the
re
> and spins forever.
> I see cpu and diskio cycles escalating but nothing ever happens.
> is there a step before this that i am forgetting?
> setup is: x64 sql 2005, sp1 (windwos 2003 ee x64), db in simple recovery
> model.
> gracias,
> Robert
dbcc shrinkfile & recovery mode
Occasionally, we have gotten messages that the logfile is too big on one
of our servers. At that point, we will run a backup of the database and
then dbcc shrinkfile on it.
From what I've read, I thought that some of the properties we have set
would make it unnecessary to have to do that. On the Options tab on the
database properties, we have the Recovery model set to Simple, and have
the following settings checked: Auto Update Statistics, Auto Shrink,
and Auto Create Statistics.
Is there another setting I'm missing? Or, should I just set up a job to
back up the database and run the dbcc shrinkfile on it at set intervals?
BC
AutoShrink is taking your log file down to a very minimal size when it runs,
and then your Autogrow has to kick in when the db has a lot of
activity...if it is very busy, it could grow quite large. I've never seen
a SQL Server message "too big"...what are you really getting, and what is
the size at that point.?
In theory, if you set the db to Simple and DBCC Shrinkfile it down to a size
that is big enough to handle your busiest time frames you should be good
from there.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Blasting Cap" <goober@.christian.net> wrote in message
news:%23xn12hncFHA.1384@.TK2MSFTNGP09.phx.gbl...
> Occasionally, we have gotten messages that the logfile is too big on one
> of our servers. At that point, we will run a backup of the database and
> then dbcc shrinkfile on it.
> From what I've read, I thought that some of the properties we have set
> would make it unnecessary to have to do that. On the Options tab on the
> database properties, we have the Recovery model set to Simple, and have
> the following settings checked: Auto Update Statistics, Auto Shrink, and
> Auto Create Statistics.
> Is there another setting I'm missing? Or, should I just set up a job to
> back up the database and run the dbcc shrinkfile on it at set intervals?
> BC
of our servers. At that point, we will run a backup of the database and
then dbcc shrinkfile on it.
From what I've read, I thought that some of the properties we have set
would make it unnecessary to have to do that. On the Options tab on the
database properties, we have the Recovery model set to Simple, and have
the following settings checked: Auto Update Statistics, Auto Shrink,
and Auto Create Statistics.
Is there another setting I'm missing? Or, should I just set up a job to
back up the database and run the dbcc shrinkfile on it at set intervals?
BC
AutoShrink is taking your log file down to a very minimal size when it runs,
and then your Autogrow has to kick in when the db has a lot of
activity...if it is very busy, it could grow quite large. I've never seen
a SQL Server message "too big"...what are you really getting, and what is
the size at that point.?
In theory, if you set the db to Simple and DBCC Shrinkfile it down to a size
that is big enough to handle your busiest time frames you should be good
from there.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Blasting Cap" <goober@.christian.net> wrote in message
news:%23xn12hncFHA.1384@.TK2MSFTNGP09.phx.gbl...
> Occasionally, we have gotten messages that the logfile is too big on one
> of our servers. At that point, we will run a backup of the database and
> then dbcc shrinkfile on it.
> From what I've read, I thought that some of the properties we have set
> would make it unnecessary to have to do that. On the Options tab on the
> database properties, we have the Recovery model set to Simple, and have
> the following settings checked: Auto Update Statistics, Auto Shrink, and
> Auto Create Statistics.
> Is there another setting I'm missing? Or, should I just set up a job to
> back up the database and run the dbcc shrinkfile on it at set intervals?
> BC
dbcc shrinkfile & recovery mode
Occasionally, we have gotten messages that the logfile is too big on one
of our servers. At that point, we will run a backup of the database and
then dbcc shrinkfile on it.
From what I've read, I thought that some of the properties we have set
would make it unnecessary to have to do that. On the Options tab on the
database properties, we have the Recovery model set to Simple, and have
the following settings checked: Auto Update Statistics, Auto Shrink,
and Auto Create Statistics.
Is there another setting I'm missing? Or, should I just set up a job to
back up the database and run the dbcc shrinkfile on it at set intervals?
BCAutoShrink is taking your log file down to a very minimal size when it runs,
and then your Autogrow has to kick in when the db has a lot of
activity...if it is very busy, it could grow quite large. I've never seen
a SQL Server message "too big"...what are you really getting, and what is
the size at that point.?
In theory, if you set the db to Simple and DBCC Shrinkfile it down to a size
that is big enough to handle your busiest time frames you should be good
from there.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Blasting Cap" <goober@.christian.net> wrote in message
news:%23xn12hncFHA.1384@.TK2MSFTNGP09.phx.gbl...
> Occasionally, we have gotten messages that the logfile is too big on one
> of our servers. At that point, we will run a backup of the database and
> then dbcc shrinkfile on it.
> From what I've read, I thought that some of the properties we have set
> would make it unnecessary to have to do that. On the Options tab on the
> database properties, we have the Recovery model set to Simple, and have
> the following settings checked: Auto Update Statistics, Auto Shrink, and
> Auto Create Statistics.
> Is there another setting I'm missing? Or, should I just set up a job to
> back up the database and run the dbcc shrinkfile on it at set intervals?
> BC
of our servers. At that point, we will run a backup of the database and
then dbcc shrinkfile on it.
From what I've read, I thought that some of the properties we have set
would make it unnecessary to have to do that. On the Options tab on the
database properties, we have the Recovery model set to Simple, and have
the following settings checked: Auto Update Statistics, Auto Shrink,
and Auto Create Statistics.
Is there another setting I'm missing? Or, should I just set up a job to
back up the database and run the dbcc shrinkfile on it at set intervals?
BCAutoShrink is taking your log file down to a very minimal size when it runs,
and then your Autogrow has to kick in when the db has a lot of
activity...if it is very busy, it could grow quite large. I've never seen
a SQL Server message "too big"...what are you really getting, and what is
the size at that point.?
In theory, if you set the db to Simple and DBCC Shrinkfile it down to a size
that is big enough to handle your busiest time frames you should be good
from there.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Blasting Cap" <goober@.christian.net> wrote in message
news:%23xn12hncFHA.1384@.TK2MSFTNGP09.phx.gbl...
> Occasionally, we have gotten messages that the logfile is too big on one
> of our servers. At that point, we will run a backup of the database and
> then dbcc shrinkfile on it.
> From what I've read, I thought that some of the properties we have set
> would make it unnecessary to have to do that. On the Options tab on the
> database properties, we have the Recovery model set to Simple, and have
> the following settings checked: Auto Update Statistics, Auto Shrink, and
> Auto Create Statistics.
> Is there another setting I'm missing? Or, should I just set up a job to
> back up the database and run the dbcc shrinkfile on it at set intervals?
> BC
dbcc shrinkfile & recovery mode
Occasionally, we have gotten messages that the logfile is too big on one
of our servers. At that point, we will run a backup of the database and
then dbcc shrinkfile on it.
From what I've read, I thought that some of the properties we have set
would make it unnecessary to have to do that. On the Options tab on the
database properties, we have the Recovery model set to Simple, and have
the following settings checked: Auto Update Statistics, Auto Shrink,
and Auto Create Statistics.
Is there another setting I'm missing? Or, should I just set up a job to
back up the database and run the dbcc shrinkfile on it at set intervals?
BCAutoShrink is taking your log file down to a very minimal size when it runs,
and then your Autogrow has to kick in when the db has a lot of
activity...if it is very busy, it could grow quite large. I've never seen
a SQL Server message "too big"...what are you really getting, and what is
the size at that point.?
In theory, if you set the db to Simple and DBCC Shrinkfile it down to a size
that is big enough to handle your busiest time frames you should be good
from there.
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Blasting Cap" <goober@.christian.net> wrote in message
news:%23xn12hncFHA.1384@.TK2MSFTNGP09.phx.gbl...
> Occasionally, we have gotten messages that the logfile is too big on one
> of our servers. At that point, we will run a backup of the database and
> then dbcc shrinkfile on it.
> From what I've read, I thought that some of the properties we have set
> would make it unnecessary to have to do that. On the Options tab on the
> database properties, we have the Recovery model set to Simple, and have
> the following settings checked: Auto Update Statistics, Auto Shrink, and
> Auto Create Statistics.
> Is there another setting I'm missing? Or, should I just set up a job to
> back up the database and run the dbcc shrinkfile on it at set intervals?
> BC
of our servers. At that point, we will run a backup of the database and
then dbcc shrinkfile on it.
From what I've read, I thought that some of the properties we have set
would make it unnecessary to have to do that. On the Options tab on the
database properties, we have the Recovery model set to Simple, and have
the following settings checked: Auto Update Statistics, Auto Shrink,
and Auto Create Statistics.
Is there another setting I'm missing? Or, should I just set up a job to
back up the database and run the dbcc shrinkfile on it at set intervals?
BCAutoShrink is taking your log file down to a very minimal size when it runs,
and then your Autogrow has to kick in when the db has a lot of
activity...if it is very busy, it could grow quite large. I've never seen
a SQL Server message "too big"...what are you really getting, and what is
the size at that point.?
In theory, if you set the db to Simple and DBCC Shrinkfile it down to a size
that is big enough to handle your busiest time frames you should be good
from there.
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Blasting Cap" <goober@.christian.net> wrote in message
news:%23xn12hncFHA.1384@.TK2MSFTNGP09.phx.gbl...
> Occasionally, we have gotten messages that the logfile is too big on one
> of our servers. At that point, we will run a backup of the database and
> then dbcc shrinkfile on it.
> From what I've read, I thought that some of the properties we have set
> would make it unnecessary to have to do that. On the Options tab on the
> database properties, we have the Recovery model set to Simple, and have
> the following settings checked: Auto Update Statistics, Auto Shrink, and
> Auto Create Statistics.
> Is there another setting I'm missing? Or, should I just set up a job to
> back up the database and run the dbcc shrinkfile on it at set intervals?
> BC
dbcc shrinkfile - SQL Server 2000 - invalidate log?
Hi,
I need to shrink a database file and was wondering whether it is
required to run a full backup after the shrink operation.
In SQL Server 7.0 shrinkfile was a non-logged operation so would
invalidate your transaction logs. Is the same true for 2000?
Obviously as a matter of course I would backup before and after the
operation but going forward I may want to implement this on a regular
basis.
Cheers
Deedeebeeay@.gmail.com wrote:
> Hi,
> I need to shrink a database file and was wondering whether it is
> required to run a full backup after the shrink operation.
> In SQL Server 7.0 shrinkfile was a non-logged operation so would
> invalidate your transaction logs. Is the same true for 2000?
> Obviously as a matter of course I would backup before and after the
> operation but going forward I may want to implement this on a regular
> basis.
> Cheers
> Dee
Shrinking log files does not invalidate the log. It is however a very
bad idea to shrink on a regular basis. If you are running full recovery
and doing regular transaction log backups then why would you want to
keep shrinking the log?
For more information see:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||Hi David,
Its not the log I want to shrink but the database.|||deebeeay@.gmail.com wrote:
> Hi David,
> Its not the log I want to shrink but the database.
Skrinking the database regularly is also unlikely to be a good idea in
most environments. Do you enable autogrow? Auto-growing a database is
potentially a very expensive operation. Much better to preset the
database size, turn auto-grow OFF and then don't shrink it at all.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||Yes I agree, auto-growth isn't good and by that same token a regular
shrink is probably not a good idea.
However I'd still like to know if a dbcc shrinkfile would then require
a post full database backup to ensure recoverability.|||deebeeay@.gmail.com wrote:
> Yes I agree, auto-growth isn't good and by that same token a regular
> shrink is probably not a good idea.
> However I'd still like to know if a dbcc shrinkfile would then require
> a post full database backup to ensure recoverability.
Shrinking a data file does not invalidate the log. However, it will
cause an exceptional amount of logging. Shrinking may require at least
as much log as you have data in the file(s) being shrunk. So your next
log backup could be vastly inflated. Assuming your database is offline
you may want to backup the log with the TRUNCATE ONLY option
immediately after shrinking and then perform a database backup. That's
not essential but it does mean you can return the log to its more
typical size. On the other hand if you think you need to do this
regularly then you'll have to ensure sufficient log space to support it
- so shrinking the data file is usually a false economy because it
moves data around without reducing the storage requirement.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||Many thanks for the reply David.
If I do a dbcc shrinkfile using the TRUNCATEONLY option - would this
reduce the amount of logging required?|||deebeeay@.gmail.com (deebeeay@.gmail.com) writes:
> Many thanks for the reply David.
> If I do a dbcc shrinkfile using the TRUNCATEONLY option - would this
> reduce the amount of logging required?
As I understand Books Online, this should not be an expensive operation
with regards to the tranaction log. On the other hand, it may not have
much effect, since it removes only extents at the end of the data file.
If you mistakenly created a file 10 times too large, then I would
expect TRUNCATEONLY to be useful. But if you want to shrink the database,
because you just deleted 5 years worth of data, TRUNCATEONLY is not likely
to have any effect at all.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql
dbcc shrinkfile - how long will it take
Hi,
We are shrinking a large databse using dbcc shrinkfile and it has been
running for hours. Is there any way of determining how long the job still has
left to run (the equivalent of the Oracle dynamic view v$session_longops)?.
Thanks,
Andy
Not that I am aware of. I avoid this by shrinking in increments of 100MB.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Squirrel" <Squirrel@.discussions.microsoft.com> wrote in message
news:956637B6-5C96-4F71-9630-E13702C94D78@.microsoft.com...
> Hi,
> We are shrinking a large databse using dbcc shrinkfile and it has been
> running for hours. Is there any way of determining how long the job still
> has
> left to run (the equivalent of the Oracle dynamic view
> v$session_longops)?.
> Thanks,
> Andy
|||Hi,
In SQL Server we can not exactly say how the process is going to run. But as
Kevin mentioned you could try
shrinking the files by providing a lower value (500 MB or soo...). Ensure
that you do a Backup LOG command
before shrinking the tranasction log.
Thanks
Hari
Sql server MVP
"Squirrel" <Squirrel@.discussions.microsoft.com> wrote in message
news:956637B6-5C96-4F71-9630-E13702C94D78@.microsoft.com...
> Hi,
> We are shrinking a large databse using dbcc shrinkfile and it has been
> running for hours. Is there any way of determining how long the job still
> has
> left to run (the equivalent of the Oracle dynamic view
> v$session_longops)?.
> Thanks,
> Andy
|||Not in SQL Server 2000. We've put progress reporting in for SQL Server 2005,
but even that is just a percentage complete with an elapsed-time based
extrapolation of the completion time. Basically, there are far too many
variables to consider to have a hope of being able to predict the run-time -
the worst ones being blocking, the starting state of the file/database, and
how much work shrink needs to do. For instance, if another process takes a
lock that shrink needs, shrink will wait forever for that lock.
Have you checked to make sure it is actually progressing and isn't blocked?
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Squirrel" <Squirrel@.discussions.microsoft.com> wrote in message
news:956637B6-5C96-4F71-9630-E13702C94D78@.microsoft.com...
> Hi,
> We are shrinking a large databse using dbcc shrinkfile and it has been
> running for hours. Is there any way of determining how long the job still
has
> left to run (the equivalent of the Oracle dynamic view
v$session_longops)?.
> Thanks,
> Andy
|||Not sure if this is related to the original post but I have SQL2000 SP4
install and have tried running DBCC SHRINKDATABASE (MyDB). This results is a
lock that is not released. Ent. Mgr shows "spid 58 (blocked by 58)" the Wait
Type is "PAGEIOLATCH_SH".
I think this maybe a bug in the SP4? I haven't had this problem with a DB
shrink before.
Regards,
John
"Paul S Randal [MS]" wrote:
> Not in SQL Server 2000. We've put progress reporting in for SQL Server 2005,
> but even that is just a percentage complete with an elapsed-time based
> extrapolation of the completion time. Basically, there are far too many
> variables to consider to have a hope of being able to predict the run-time -
> the worst ones being blocking, the starting state of the file/database, and
> how much work shrink needs to do. For instance, if another process takes a
> lock that shrink needs, shrink will wait forever for that lock.
> Have you checked to make sure it is actually progressing and isn't blocked?
> Regards
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Squirrel" <Squirrel@.discussions.microsoft.com> wrote in message
> news:956637B6-5C96-4F71-9630-E13702C94D78@.microsoft.com...
> has
> v$session_longops)?.
>
>
We are shrinking a large databse using dbcc shrinkfile and it has been
running for hours. Is there any way of determining how long the job still has
left to run (the equivalent of the Oracle dynamic view v$session_longops)?.
Thanks,
Andy
Not that I am aware of. I avoid this by shrinking in increments of 100MB.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Squirrel" <Squirrel@.discussions.microsoft.com> wrote in message
news:956637B6-5C96-4F71-9630-E13702C94D78@.microsoft.com...
> Hi,
> We are shrinking a large databse using dbcc shrinkfile and it has been
> running for hours. Is there any way of determining how long the job still
> has
> left to run (the equivalent of the Oracle dynamic view
> v$session_longops)?.
> Thanks,
> Andy
|||Hi,
In SQL Server we can not exactly say how the process is going to run. But as
Kevin mentioned you could try
shrinking the files by providing a lower value (500 MB or soo...). Ensure
that you do a Backup LOG command
before shrinking the tranasction log.
Thanks
Hari
Sql server MVP
"Squirrel" <Squirrel@.discussions.microsoft.com> wrote in message
news:956637B6-5C96-4F71-9630-E13702C94D78@.microsoft.com...
> Hi,
> We are shrinking a large databse using dbcc shrinkfile and it has been
> running for hours. Is there any way of determining how long the job still
> has
> left to run (the equivalent of the Oracle dynamic view
> v$session_longops)?.
> Thanks,
> Andy
|||Not in SQL Server 2000. We've put progress reporting in for SQL Server 2005,
but even that is just a percentage complete with an elapsed-time based
extrapolation of the completion time. Basically, there are far too many
variables to consider to have a hope of being able to predict the run-time -
the worst ones being blocking, the starting state of the file/database, and
how much work shrink needs to do. For instance, if another process takes a
lock that shrink needs, shrink will wait forever for that lock.
Have you checked to make sure it is actually progressing and isn't blocked?
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Squirrel" <Squirrel@.discussions.microsoft.com> wrote in message
news:956637B6-5C96-4F71-9630-E13702C94D78@.microsoft.com...
> Hi,
> We are shrinking a large databse using dbcc shrinkfile and it has been
> running for hours. Is there any way of determining how long the job still
has
> left to run (the equivalent of the Oracle dynamic view
v$session_longops)?.
> Thanks,
> Andy
|||Not sure if this is related to the original post but I have SQL2000 SP4
install and have tried running DBCC SHRINKDATABASE (MyDB). This results is a
lock that is not released. Ent. Mgr shows "spid 58 (blocked by 58)" the Wait
Type is "PAGEIOLATCH_SH".
I think this maybe a bug in the SP4? I haven't had this problem with a DB
shrink before.
Regards,
John
"Paul S Randal [MS]" wrote:
> Not in SQL Server 2000. We've put progress reporting in for SQL Server 2005,
> but even that is just a percentage complete with an elapsed-time based
> extrapolation of the completion time. Basically, there are far too many
> variables to consider to have a hope of being able to predict the run-time -
> the worst ones being blocking, the starting state of the file/database, and
> how much work shrink needs to do. For instance, if another process takes a
> lock that shrink needs, shrink will wait forever for that lock.
> Have you checked to make sure it is actually progressing and isn't blocked?
> Regards
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Squirrel" <Squirrel@.discussions.microsoft.com> wrote in message
> news:956637B6-5C96-4F71-9630-E13702C94D78@.microsoft.com...
> has
> v$session_longops)?.
>
>
dbcc shrinkfile - how long will it take
Hi,
We are shrinking a large databse using dbcc shrinkfile and it has been
running for hours. Is there any way of determining how long the job still ha
s
left to run (the equivalent of the Oracle dynamic view v$session_longops)?.
Thanks,
AndyNot that I am aware of. I avoid this by shrinking in increments of 100MB.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Squirrel" <Squirrel@.discussions.microsoft.com> wrote in message
news:956637B6-5C96-4F71-9630-E13702C94D78@.microsoft.com...
> Hi,
> We are shrinking a large databse using dbcc shrinkfile and it has been
> running for hours. Is there any way of determining how long the job still
> has
> left to run (the equivalent of the Oracle dynamic view
> v$session_longops)?.
> Thanks,
> Andy|||Hi,
In SQL Server we can not exactly say how the process is going to run. But as
Kevin mentioned you could try
shrinking the files by providing a lower value (500 MB or soo...). Ensure
that you do a Backup LOG command
before shrinking the tranasction log.
Thanks
Hari
Sql server MVP
"Squirrel" <Squirrel@.discussions.microsoft.com> wrote in message
news:956637B6-5C96-4F71-9630-E13702C94D78@.microsoft.com...
> Hi,
> We are shrinking a large databse using dbcc shrinkfile and it has been
> running for hours. Is there any way of determining how long the job still
> has
> left to run (the equivalent of the Oracle dynamic view
> v$session_longops)?.
> Thanks,
> Andy|||Not in SQL Server 2000. We've put progress reporting in for SQL Server 2005,
but even that is just a percentage complete with an elapsed-time based
extrapolation of the completion time. Basically, there are far too many
variables to consider to have a hope of being able to predict the run-time -
the worst ones being blocking, the starting state of the file/database, and
how much work shrink needs to do. For instance, if another process takes a
lock that shrink needs, shrink will wait forever for that lock.
Have you checked to make sure it is actually progressing and isn't blocked?
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Squirrel" <Squirrel@.discussions.microsoft.com> wrote in message
news:956637B6-5C96-4F71-9630-E13702C94D78@.microsoft.com...
> Hi,
> We are shrinking a large databse using dbcc shrinkfile and it has been
> running for hours. Is there any way of determining how long the job still
has
> left to run (the equivalent of the Oracle dynamic view
v$session_longops)?.
> Thanks,
> Andy|||Not sure if this is related to the original post but I have SQL2000 SP4
install and have tried running DBCC SHRINKDATABASE (MyDB). This results is a
lock that is not released. Ent. Mgr shows "spid 58 (blocked by 58)" the Wait
Type is "PAGEIOLATCH_SH".
I think this maybe a bug in the SP4? I haven't had this problem with a DB
shrink before.
Regards,
John
"Paul S Randal [MS]" wrote:
> Not in SQL Server 2000. We've put progress reporting in for SQL Server 200
5,
> but even that is just a percentage complete with an elapsed-time based
> extrapolation of the completion time. Basically, there are far too many
> variables to consider to have a hope of being able to predict the run-time
-
> the worst ones being blocking, the starting state of the file/database, an
d
> how much work shrink needs to do. For instance, if another process takes a
> lock that shrink needs, shrink will wait forever for that lock.
> Have you checked to make sure it is actually progressing and isn't blocked
?
> Regards
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Squirrel" <Squirrel@.discussions.microsoft.com> wrote in message
> news:956637B6-5C96-4F71-9630-E13702C94D78@.microsoft.com...
> has
> v$session_longops)?.
>
>
We are shrinking a large databse using dbcc shrinkfile and it has been
running for hours. Is there any way of determining how long the job still ha
s
left to run (the equivalent of the Oracle dynamic view v$session_longops)?.
Thanks,
AndyNot that I am aware of. I avoid this by shrinking in increments of 100MB.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Squirrel" <Squirrel@.discussions.microsoft.com> wrote in message
news:956637B6-5C96-4F71-9630-E13702C94D78@.microsoft.com...
> Hi,
> We are shrinking a large databse using dbcc shrinkfile and it has been
> running for hours. Is there any way of determining how long the job still
> has
> left to run (the equivalent of the Oracle dynamic view
> v$session_longops)?.
> Thanks,
> Andy|||Hi,
In SQL Server we can not exactly say how the process is going to run. But as
Kevin mentioned you could try
shrinking the files by providing a lower value (500 MB or soo...). Ensure
that you do a Backup LOG command
before shrinking the tranasction log.
Thanks
Hari
Sql server MVP
"Squirrel" <Squirrel@.discussions.microsoft.com> wrote in message
news:956637B6-5C96-4F71-9630-E13702C94D78@.microsoft.com...
> Hi,
> We are shrinking a large databse using dbcc shrinkfile and it has been
> running for hours. Is there any way of determining how long the job still
> has
> left to run (the equivalent of the Oracle dynamic view
> v$session_longops)?.
> Thanks,
> Andy|||Not in SQL Server 2000. We've put progress reporting in for SQL Server 2005,
but even that is just a percentage complete with an elapsed-time based
extrapolation of the completion time. Basically, there are far too many
variables to consider to have a hope of being able to predict the run-time -
the worst ones being blocking, the starting state of the file/database, and
how much work shrink needs to do. For instance, if another process takes a
lock that shrink needs, shrink will wait forever for that lock.
Have you checked to make sure it is actually progressing and isn't blocked?
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Squirrel" <Squirrel@.discussions.microsoft.com> wrote in message
news:956637B6-5C96-4F71-9630-E13702C94D78@.microsoft.com...
> Hi,
> We are shrinking a large databse using dbcc shrinkfile and it has been
> running for hours. Is there any way of determining how long the job still
has
> left to run (the equivalent of the Oracle dynamic view
v$session_longops)?.
> Thanks,
> Andy|||Not sure if this is related to the original post but I have SQL2000 SP4
install and have tried running DBCC SHRINKDATABASE (MyDB). This results is a
lock that is not released. Ent. Mgr shows "spid 58 (blocked by 58)" the Wait
Type is "PAGEIOLATCH_SH".
I think this maybe a bug in the SP4? I haven't had this problem with a DB
shrink before.
Regards,
John
"Paul S Randal [MS]" wrote:
> Not in SQL Server 2000. We've put progress reporting in for SQL Server 200
5,
> but even that is just a percentage complete with an elapsed-time based
> extrapolation of the completion time. Basically, there are far too many
> variables to consider to have a hope of being able to predict the run-time
-
> the worst ones being blocking, the starting state of the file/database, an
d
> how much work shrink needs to do. For instance, if another process takes a
> lock that shrink needs, shrink will wait forever for that lock.
> Have you checked to make sure it is actually progressing and isn't blocked
?
> Regards
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Squirrel" <Squirrel@.discussions.microsoft.com> wrote in message
> news:956637B6-5C96-4F71-9630-E13702C94D78@.microsoft.com...
> has
> v$session_longops)?.
>
>
dbcc shrinkfile - how long will it take
Hi,
We are shrinking a large databse using dbcc shrinkfile and it has been
running for hours. Is there any way of determining how long the job still has
left to run (the equivalent of the Oracle dynamic view v$session_longops)?.
Thanks,
AndyNot that I am aware of. I avoid this by shrinking in increments of 100MB.
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Squirrel" <Squirrel@.discussions.microsoft.com> wrote in message
news:956637B6-5C96-4F71-9630-E13702C94D78@.microsoft.com...
> Hi,
> We are shrinking a large databse using dbcc shrinkfile and it has been
> running for hours. Is there any way of determining how long the job still
> has
> left to run (the equivalent of the Oracle dynamic view
> v$session_longops)?.
> Thanks,
> Andy|||Hi,
In SQL Server we can not exactly say how the process is going to run. But as
Kevin mentioned you could try
shrinking the files by providing a lower value (500 MB or soo...). Ensure
that you do a Backup LOG command
before shrinking the tranasction log.
Thanks
Hari
Sql server MVP
"Squirrel" <Squirrel@.discussions.microsoft.com> wrote in message
news:956637B6-5C96-4F71-9630-E13702C94D78@.microsoft.com...
> Hi,
> We are shrinking a large databse using dbcc shrinkfile and it has been
> running for hours. Is there any way of determining how long the job still
> has
> left to run (the equivalent of the Oracle dynamic view
> v$session_longops)?.
> Thanks,
> Andy|||Not in SQL Server 2000. We've put progress reporting in for SQL Server 2005,
but even that is just a percentage complete with an elapsed-time based
extrapolation of the completion time. Basically, there are far too many
variables to consider to have a hope of being able to predict the run-time -
the worst ones being blocking, the starting state of the file/database, and
how much work shrink needs to do. For instance, if another process takes a
lock that shrink needs, shrink will wait forever for that lock.
Have you checked to make sure it is actually progressing and isn't blocked?
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Squirrel" <Squirrel@.discussions.microsoft.com> wrote in message
news:956637B6-5C96-4F71-9630-E13702C94D78@.microsoft.com...
> Hi,
> We are shrinking a large databse using dbcc shrinkfile and it has been
> running for hours. Is there any way of determining how long the job still
has
> left to run (the equivalent of the Oracle dynamic view
v$session_longops)?.
> Thanks,
> Andy|||Not sure if this is related to the original post but I have SQL2000 SP4
install and have tried running DBCC SHRINKDATABASE (MyDB). This results is a
lock that is not released. Ent. Mgr shows "spid 58 (blocked by 58)" the Wait
Type is "PAGEIOLATCH_SH".
I think this maybe a bug in the SP4? I haven't had this problem with a DB
shrink before.
Regards,
John
"Paul S Randal [MS]" wrote:
> Not in SQL Server 2000. We've put progress reporting in for SQL Server 2005,
> but even that is just a percentage complete with an elapsed-time based
> extrapolation of the completion time. Basically, there are far too many
> variables to consider to have a hope of being able to predict the run-time -
> the worst ones being blocking, the starting state of the file/database, and
> how much work shrink needs to do. For instance, if another process takes a
> lock that shrink needs, shrink will wait forever for that lock.
> Have you checked to make sure it is actually progressing and isn't blocked?
> Regards
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Squirrel" <Squirrel@.discussions.microsoft.com> wrote in message
> news:956637B6-5C96-4F71-9630-E13702C94D78@.microsoft.com...
> > Hi,
> > We are shrinking a large databse using dbcc shrinkfile and it has been
> > running for hours. Is there any way of determining how long the job still
> has
> > left to run (the equivalent of the Oracle dynamic view
> v$session_longops)?.
> >
> > Thanks,
> > Andy
>
>
We are shrinking a large databse using dbcc shrinkfile and it has been
running for hours. Is there any way of determining how long the job still has
left to run (the equivalent of the Oracle dynamic view v$session_longops)?.
Thanks,
AndyNot that I am aware of. I avoid this by shrinking in increments of 100MB.
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Squirrel" <Squirrel@.discussions.microsoft.com> wrote in message
news:956637B6-5C96-4F71-9630-E13702C94D78@.microsoft.com...
> Hi,
> We are shrinking a large databse using dbcc shrinkfile and it has been
> running for hours. Is there any way of determining how long the job still
> has
> left to run (the equivalent of the Oracle dynamic view
> v$session_longops)?.
> Thanks,
> Andy|||Hi,
In SQL Server we can not exactly say how the process is going to run. But as
Kevin mentioned you could try
shrinking the files by providing a lower value (500 MB or soo...). Ensure
that you do a Backup LOG command
before shrinking the tranasction log.
Thanks
Hari
Sql server MVP
"Squirrel" <Squirrel@.discussions.microsoft.com> wrote in message
news:956637B6-5C96-4F71-9630-E13702C94D78@.microsoft.com...
> Hi,
> We are shrinking a large databse using dbcc shrinkfile and it has been
> running for hours. Is there any way of determining how long the job still
> has
> left to run (the equivalent of the Oracle dynamic view
> v$session_longops)?.
> Thanks,
> Andy|||Not in SQL Server 2000. We've put progress reporting in for SQL Server 2005,
but even that is just a percentage complete with an elapsed-time based
extrapolation of the completion time. Basically, there are far too many
variables to consider to have a hope of being able to predict the run-time -
the worst ones being blocking, the starting state of the file/database, and
how much work shrink needs to do. For instance, if another process takes a
lock that shrink needs, shrink will wait forever for that lock.
Have you checked to make sure it is actually progressing and isn't blocked?
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Squirrel" <Squirrel@.discussions.microsoft.com> wrote in message
news:956637B6-5C96-4F71-9630-E13702C94D78@.microsoft.com...
> Hi,
> We are shrinking a large databse using dbcc shrinkfile and it has been
> running for hours. Is there any way of determining how long the job still
has
> left to run (the equivalent of the Oracle dynamic view
v$session_longops)?.
> Thanks,
> Andy|||Not sure if this is related to the original post but I have SQL2000 SP4
install and have tried running DBCC SHRINKDATABASE (MyDB). This results is a
lock that is not released. Ent. Mgr shows "spid 58 (blocked by 58)" the Wait
Type is "PAGEIOLATCH_SH".
I think this maybe a bug in the SP4? I haven't had this problem with a DB
shrink before.
Regards,
John
"Paul S Randal [MS]" wrote:
> Not in SQL Server 2000. We've put progress reporting in for SQL Server 2005,
> but even that is just a percentage complete with an elapsed-time based
> extrapolation of the completion time. Basically, there are far too many
> variables to consider to have a hope of being able to predict the run-time -
> the worst ones being blocking, the starting state of the file/database, and
> how much work shrink needs to do. For instance, if another process takes a
> lock that shrink needs, shrink will wait forever for that lock.
> Have you checked to make sure it is actually progressing and isn't blocked?
> Regards
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Squirrel" <Squirrel@.discussions.microsoft.com> wrote in message
> news:956637B6-5C96-4F71-9630-E13702C94D78@.microsoft.com...
> > Hi,
> > We are shrinking a large databse using dbcc shrinkfile and it has been
> > running for hours. Is there any way of determining how long the job still
> has
> > left to run (the equivalent of the Oracle dynamic view
> v$session_longops)?.
> >
> > Thanks,
> > Andy
>
>
DBCC ShrinkFile
How long does it take to execute DBCC Shrinkfile(DB_FILE, emptyfile) on a 10GB datafiles? If you put your datafiles together with the tempdb datafiles on the same logical drive do we have a performance issue?
Thanks
The time taken for this activity is depends.
THe other part of ur question, Temp db should always be in seperate physical drive if possible for better performance. Datafile is Random read/write but temp db will have moreor less sequential read./write. So if u keep both in same drive it will have performance hit
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/tempdb_capacity_planning_index.mspx
http://msdn2.microsoft.com/en-us/library/ms345368.aspx
Madhu
dbcc shrinkfile
hi, can I shrink the database file, not only the trans log file?You can shrink individual files using DBCC SHRINKFILE. See the Books Online
for details.
Hope this helps.
Dan Guzman
SQL Server MVP
"js" <js@.someone.com> wrote in message
news:udr2WIcWFHA.1044@.TK2MSFTNGP10.phx.gbl...
> hi, can I shrink the database file, not only the trans log file?
>|||Hi JS
Check for DBCC SHRINKDATABASE if you want to shrink the size of the data
files in the specified database
http://msdn.microsoft.com/library/d...
pd1.asp
If you are trying to reduce the size of a single file, then u need to check
DBCC SHRINKFILE
http://msdn.microsoft.com/library/e...asp?frame=true
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.examnotes.net/gurus/default.asp?p=4223
---
"js" wrote:
> hi, can I shrink the database file, not only the trans log file?
>
>|||Thanks,
When to shrink database and when to shrink file usually?
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:950B4DD6-B2B1-4AC8-991A-A8C9F4FB6778@.microsoft.com...
> Hi JS
> Check for DBCC SHRINKDATABASE if you want to shrink the size of the data
> files in the specified database
> http://msdn.microsoft.com/library/d...r />
_3pd1.asp
> If you are trying to reduce the size of a single file, then u need to
> check
> DBCC SHRINKFILE
> http://msdn.microsoft.com/library/e...s.blogspot.com/
> http://www.examnotes.net/gurus/default.asp?p=4223
> ---
>
> "js" wrote:
>|||Another question:
how to show the database files in size use query? Thanks.
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:950B4DD6-B2B1-4AC8-991A-A8C9F4FB6778@.microsoft.com...
> Hi JS
> Check for DBCC SHRINKDATABASE if you want to shrink the size of the data
> files in the specified database
> http://msdn.microsoft.com/library/d...r />
_3pd1.asp
> If you are trying to reduce the size of a single file, then u need to
> check
> DBCC SHRINKFILE
> http://msdn.microsoft.com/library/e...s.blogspot.com/
> http://www.examnotes.net/gurus/default.asp?p=4223
> ---
>
> "js" wrote:
>|||js
exec sp_helpdb 'databasename'
"js" <js@.someone@.hotmail.com> wrote in message
news:ednshxhWFHA.228@.TK2MSFTNGP12.phx.gbl...
> Another question:
> how to show the database files in size use query? Thanks.
> "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> news:950B4DD6-B2B1-4AC8-991A-A8C9F4FB6778@.microsoft.com...
http://msdn.microsoft.com/library/d...s_dbcc_3pd1.asp[c
olor=darkred]
http://msdn.microsoft.com/library/e...asp?frame=true
>|||Usually never. You really only want to shrink the files when they have
grown due to a one time data load or similar type operation. Since
operations such as reindexing require lots of free space int he files you
should always have plenty of free space available.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Andrew J. Kelly SQL MVP
"js" <js@.someone@.hotmail.com> wrote in message
news:Oizi9mhWFHA.2448@.TK2MSFTNGP12.phx.gbl...
> Thanks,
> When to shrink database and when to shrink file usually?
> "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> news:950B4DD6-B2B1-4AC8-991A-A8C9F4FB6778@.microsoft.com...
>|||In addition to sbcc shrinkfile, you can use dbcc showcontig to determine the
level of data and index fragmentation. Much space within a table can be
wasted if you perform frequent updates and deletes on a table with a
clustered index or insert rows in an order other than the clustered order.
Also, you can use dbcc indexdefrag to defragment indexes prior to shrinking
the file or database.
"js" <js@.someone.com> wrote in message
news:udr2WIcWFHA.1044@.TK2MSFTNGP10.phx.gbl...
> hi, can I shrink the database file, not only the trans log file?
>|||BTW - Do you know that you're a month ahead of the rest of the world?
Joe Webb
SQL Server MVP
~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/t...il/-/0972688811
I support PASS, the Professional Association for SQL Server.
(www.sqlpass.org)
On Tue, 14 Jun 2005 22:50:50 -0400, "js" <js@.someone.com> wrote:
>hi, can I shrink the database file, not only the trans log file?
>sql
for details.
Hope this helps.
Dan Guzman
SQL Server MVP
"js" <js@.someone.com> wrote in message
news:udr2WIcWFHA.1044@.TK2MSFTNGP10.phx.gbl...
> hi, can I shrink the database file, not only the trans log file?
>|||Hi JS
Check for DBCC SHRINKDATABASE if you want to shrink the size of the data
files in the specified database
http://msdn.microsoft.com/library/d...
pd1.asp
If you are trying to reduce the size of a single file, then u need to check
DBCC SHRINKFILE
http://msdn.microsoft.com/library/e...asp?frame=true
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.examnotes.net/gurus/default.asp?p=4223
---
"js" wrote:
> hi, can I shrink the database file, not only the trans log file?
>
>|||Thanks,
When to shrink database and when to shrink file usually?
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:950B4DD6-B2B1-4AC8-991A-A8C9F4FB6778@.microsoft.com...
> Hi JS
> Check for DBCC SHRINKDATABASE if you want to shrink the size of the data
> files in the specified database
> http://msdn.microsoft.com/library/d...r />
_3pd1.asp
> If you are trying to reduce the size of a single file, then u need to
> check
> DBCC SHRINKFILE
> http://msdn.microsoft.com/library/e...s.blogspot.com/
> http://www.examnotes.net/gurus/default.asp?p=4223
> ---
>
> "js" wrote:
>|||Another question:
how to show the database files in size use query? Thanks.
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:950B4DD6-B2B1-4AC8-991A-A8C9F4FB6778@.microsoft.com...
> Hi JS
> Check for DBCC SHRINKDATABASE if you want to shrink the size of the data
> files in the specified database
> http://msdn.microsoft.com/library/d...r />
_3pd1.asp
> If you are trying to reduce the size of a single file, then u need to
> check
> DBCC SHRINKFILE
> http://msdn.microsoft.com/library/e...s.blogspot.com/
> http://www.examnotes.net/gurus/default.asp?p=4223
> ---
>
> "js" wrote:
>|||js
exec sp_helpdb 'databasename'
"js" <js@.someone@.hotmail.com> wrote in message
news:ednshxhWFHA.228@.TK2MSFTNGP12.phx.gbl...
> Another question:
> how to show the database files in size use query? Thanks.
> "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> news:950B4DD6-B2B1-4AC8-991A-A8C9F4FB6778@.microsoft.com...
http://msdn.microsoft.com/library/d...s_dbcc_3pd1.asp[c
olor=darkred]
http://msdn.microsoft.com/library/e...asp?frame=true
>|||Usually never. You really only want to shrink the files when they have
grown due to a one time data load or similar type operation. Since
operations such as reindexing require lots of free space int he files you
should always have plenty of free space available.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Andrew J. Kelly SQL MVP
"js" <js@.someone@.hotmail.com> wrote in message
news:Oizi9mhWFHA.2448@.TK2MSFTNGP12.phx.gbl...
> Thanks,
> When to shrink database and when to shrink file usually?
> "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> news:950B4DD6-B2B1-4AC8-991A-A8C9F4FB6778@.microsoft.com...
>|||In addition to sbcc shrinkfile, you can use dbcc showcontig to determine the
level of data and index fragmentation. Much space within a table can be
wasted if you perform frequent updates and deletes on a table with a
clustered index or insert rows in an order other than the clustered order.
Also, you can use dbcc indexdefrag to defragment indexes prior to shrinking
the file or database.
"js" <js@.someone.com> wrote in message
news:udr2WIcWFHA.1044@.TK2MSFTNGP10.phx.gbl...
> hi, can I shrink the database file, not only the trans log file?
>|||BTW - Do you know that you're a month ahead of the rest of the world?
Joe Webb
SQL Server MVP
~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/t...il/-/0972688811
I support PASS, the Professional Association for SQL Server.
(www.sqlpass.org)
On Tue, 14 Jun 2005 22:50:50 -0400, "js" <js@.someone.com> wrote:
>hi, can I shrink the database file, not only the trans log file?
>sql
DBCC Shrinkfile
I ran a dbcc shrinkfile after deleting data in order to take thte db down in size and it has been running for 2 days. Can I canel the command? Will the file be partially shrunk? Last year when we did this I ran a re-index command. Will I have to run a bakup or truncate log command? I have been trying to get the client to upgrade from SQL 7 but they just have not done it yet.follow up question. If I cancel how long will it take to finish the cancel operation?
DBCC Shrinkfile
Hi,
Can someone tell me of some the reasons why DBCC Shrinkfile does not shrink
the size of the log file in my SQL Server 2000 server? After backing up the
log file, I tried to shrink the physical size of the log file to 2000 MB but
it gave me an error (which I can't remember) even though the actual used size
(not phyical) was like 1500 MB. I tried again with a larger number, 5000 MB
and this time it worked.
TIA.When you have problems shrinking, you should check for open
transactions using dbcc opentran
You also need to keep in mind that an active portion of the
log can't be impacted. So if you have an active portion of
the log at the end, you won't be able to shrink. You can
check this using dbcc loginfo(YourDatabase). A status of 2
indicates it's active.
Make sure you aren't needlessly shrinking. It should be more
of an ad hoc process - after some out of control process
blows out the size of the log type of thing.
You can find more information in the following article and a
more thorough explanation of what you see with dbcc loginfo:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
-Sue
On Wed, 11 Oct 2006 14:19:02 -0700, Rob
<Rob@.discussions.microsoft.com> wrote:
>Hi,
>Can someone tell me of some the reasons why DBCC Shrinkfile does not shrink
>the size of the log file in my SQL Server 2000 server? After backing up the
>log file, I tried to shrink the physical size of the log file to 2000 MB but
>it gave me an error (which I can't remember) even though the actual used size
>(not phyical) was like 1500 MB. I tried again with a larger number, 5000 MB
>and this time it worked.
>TIA.
Can someone tell me of some the reasons why DBCC Shrinkfile does not shrink
the size of the log file in my SQL Server 2000 server? After backing up the
log file, I tried to shrink the physical size of the log file to 2000 MB but
it gave me an error (which I can't remember) even though the actual used size
(not phyical) was like 1500 MB. I tried again with a larger number, 5000 MB
and this time it worked.
TIA.When you have problems shrinking, you should check for open
transactions using dbcc opentran
You also need to keep in mind that an active portion of the
log can't be impacted. So if you have an active portion of
the log at the end, you won't be able to shrink. You can
check this using dbcc loginfo(YourDatabase). A status of 2
indicates it's active.
Make sure you aren't needlessly shrinking. It should be more
of an ad hoc process - after some out of control process
blows out the size of the log type of thing.
You can find more information in the following article and a
more thorough explanation of what you see with dbcc loginfo:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
-Sue
On Wed, 11 Oct 2006 14:19:02 -0700, Rob
<Rob@.discussions.microsoft.com> wrote:
>Hi,
>Can someone tell me of some the reasons why DBCC Shrinkfile does not shrink
>the size of the log file in my SQL Server 2000 server? After backing up the
>log file, I tried to shrink the physical size of the log file to 2000 MB but
>it gave me an error (which I can't remember) even though the actual used size
>(not phyical) was like 1500 MB. I tried again with a larger number, 5000 MB
>and this time it worked.
>TIA.
DBCC SHRINKFILE
I have recently changed the indexes ina large table in our database. I have
a lot of excess unused space ,approx 64GB, in the .MDF and want to give it
back to Windows 200 Server.
Our .MDF is not in the SQL DATA directory it is on another volume.
Is there a way to specify a path to another volume when using DBCC SHRINKFILE?
Right now I am forced to use DBCC SHRINKDATABASE and it is taking a long time.
TIA,
-BenDBCC SHRINKFILE doesn't care where the file is as it uses the logical
filename. There is no reason you need to use SHRINKDATABASE.
--
Andrew J. Kelly SQL MVP
"Ben" <Ben@.discussions.microsoft.com> wrote in message
news:33AB1ADE-1D27-45F0-A054-B6F4B5F88727@.microsoft.com...
>I have recently changed the indexes ina large table in our database. I
>have
> a lot of excess unused space ,approx 64GB, in the .MDF and want to give
> it
> back to Windows 200 Server.
> Our .MDF is not in the SQL DATA directory it is on another volume.
> Is there a way to specify a path to another volume when using DBCC
> SHRINKFILE?
> Right now I am forced to use DBCC SHRINKDATABASE and it is taking a long
> time.
> TIA,
> -Ben
a lot of excess unused space ,approx 64GB, in the .MDF and want to give it
back to Windows 200 Server.
Our .MDF is not in the SQL DATA directory it is on another volume.
Is there a way to specify a path to another volume when using DBCC SHRINKFILE?
Right now I am forced to use DBCC SHRINKDATABASE and it is taking a long time.
TIA,
-BenDBCC SHRINKFILE doesn't care where the file is as it uses the logical
filename. There is no reason you need to use SHRINKDATABASE.
--
Andrew J. Kelly SQL MVP
"Ben" <Ben@.discussions.microsoft.com> wrote in message
news:33AB1ADE-1D27-45F0-A054-B6F4B5F88727@.microsoft.com...
>I have recently changed the indexes ina large table in our database. I
>have
> a lot of excess unused space ,approx 64GB, in the .MDF and want to give
> it
> back to Windows 200 Server.
> Our .MDF is not in the SQL DATA directory it is on another volume.
> Is there a way to specify a path to another volume when using DBCC
> SHRINKFILE?
> Right now I am forced to use DBCC SHRINKDATABASE and it is taking a long
> time.
> TIA,
> -Ben
DBCC Shrinkfile
What happens underlying the DBCC Shrinkfile statement ? What moves around
,etc.. ? Trying to figure out why it takes so long for this command to run
,, what does it do ? ThanksHassan, you really should pick up a copy of Kalen Delaney's Inside SQL
Server 2000. It answers all of the questions you've had recently, and then
some. It really is the best money you'll spend if you want to learn the
guts of SQL Server...
On 3/19/05 1:14 AM, in article OllldrELFHA.3988@.tk2msftngp13.phx.gbl,
"Hassan" <fatima_ja@.hotmail.com> wrote:
> What happens underlying the DBCC Shrinkfile statement ? What moves around
> ,etc.. ? Trying to figure out why it takes so long for this command to run
> ,, what does it do ? Thanks
>|||It moves data pages from the end of the file towards then beginning of the file.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Hassan" <fatima_ja@.hotmail.com> wrote in message news:OllldrELFHA.3988@.tk2msftngp13.phx.gbl...
> What happens underlying the DBCC Shrinkfile statement ? What moves around
> ,etc.. ? Trying to figure out why it takes so long for this command to run
> ,, what does it do ? Thanks
>|||I forgot to mention, that all of this data movement and space
allocation/deallocation is logged, so your transaction log can grow very
large as a result as well.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OllldrELFHA.3988@.tk2msftngp13.phx.gbl...
> What happens underlying the DBCC Shrinkfile statement ? What moves around
> ,etc.. ? Trying to figure out why it takes so long for this command to run
> ,, what does it do ? Thanks
>|||Kalen also has a nice article in this month's SQL mag... But in addition to
Itzik's comment it is really a 2 part operation, first it move either rows,
or whole pages from the end of the file to the front of the file, then it
( by default) releases the space from the end of the file to the OS... One
warning however, is that this can cause fragmentation of your data, which
may ( or may not ) affect your performance.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OllldrELFHA.3988@.tk2msftngp13.phx.gbl...
> What happens underlying the DBCC Shrinkfile statement ? What moves around
> ,etc.. ? Trying to figure out why it takes so long for this command to run
> ,, what does it do ? Thanks
>
,etc.. ? Trying to figure out why it takes so long for this command to run
,, what does it do ? ThanksHassan, you really should pick up a copy of Kalen Delaney's Inside SQL
Server 2000. It answers all of the questions you've had recently, and then
some. It really is the best money you'll spend if you want to learn the
guts of SQL Server...
On 3/19/05 1:14 AM, in article OllldrELFHA.3988@.tk2msftngp13.phx.gbl,
"Hassan" <fatima_ja@.hotmail.com> wrote:
> What happens underlying the DBCC Shrinkfile statement ? What moves around
> ,etc.. ? Trying to figure out why it takes so long for this command to run
> ,, what does it do ? Thanks
>|||It moves data pages from the end of the file towards then beginning of the file.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Hassan" <fatima_ja@.hotmail.com> wrote in message news:OllldrELFHA.3988@.tk2msftngp13.phx.gbl...
> What happens underlying the DBCC Shrinkfile statement ? What moves around
> ,etc.. ? Trying to figure out why it takes so long for this command to run
> ,, what does it do ? Thanks
>|||I forgot to mention, that all of this data movement and space
allocation/deallocation is logged, so your transaction log can grow very
large as a result as well.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OllldrELFHA.3988@.tk2msftngp13.phx.gbl...
> What happens underlying the DBCC Shrinkfile statement ? What moves around
> ,etc.. ? Trying to figure out why it takes so long for this command to run
> ,, what does it do ? Thanks
>|||Kalen also has a nice article in this month's SQL mag... But in addition to
Itzik's comment it is really a 2 part operation, first it move either rows,
or whole pages from the end of the file to the front of the file, then it
( by default) releases the space from the end of the file to the OS... One
warning however, is that this can cause fragmentation of your data, which
may ( or may not ) affect your performance.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OllldrELFHA.3988@.tk2msftngp13.phx.gbl...
> What happens underlying the DBCC Shrinkfile statement ? What moves around
> ,etc.. ? Trying to figure out why it takes so long for this command to run
> ,, what does it do ? Thanks
>
DBCC Shrinkfile
I am looking to shrink a database file using DBCC Shrinkfile to try to reclaim some disk space. For some unexplained reason I have some unsettled feelings. I need to confirm:
1. I was considering running DBCC Shrinkfile with the TruncateOnly argument, believing this is how I might free up some disk space?
2. If I do not have a clue as to what target size I might go for, what is safe?
3. Are there any unknown dangers lurking out their when running DBCC Shrinkfile?
--
Message posted via http://www.sqlmonster.com"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:30b3c02dee4a4a4988cebcf126e266f3@.SQLMonster.com...
> I am looking to shrink a database file using DBCC Shrinkfile to try to
reclaim some disk space. For some unexplained reason I have some unsettled
feelings. I need to confirm:
> 1. I was considering running DBCC Shrinkfile with the TruncateOnly
argument, believing this is how I might free up some disk space?
> 2. If I do not have a clue as to what target size I might go for, what is
safe?
> 3. Are there any unknown dangers lurking out their when running DBCC
Shrinkfile?
>
1. That's the option you should use -- you might have to defragment some
indexes first, though. If you have heavy fragmentation you probably won't
reclaim much space (if any is available).
2. I usually use 1 if I want to reclaim ALL possible space. What that
option is actually doing is specifying a new minimum size for the database
(overriding whatever was set when it was created). So there's no real
danger in using whatever value you want...
3. None that I'm aware of -- probably a small performance hit if you run it
on a very active system, though...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||What version of sql are you using?
"Robert Richards via SQLMonster.com" wrote:
> I am looking to shrink a database file using DBCC Shrinkfile to try to reclaim some disk space. For some unexplained reason I have some unsettled feelings. I need to confirm:
> 1. I was considering running DBCC Shrinkfile with the TruncateOnly argument, believing this is how I might free up some disk space?
> 2. If I do not have a clue as to what target size I might go for, what is safe?
> 3. Are there any unknown dangers lurking out their when running DBCC Shrinkfile?
> --
> Message posted via http://www.sqlmonster.com
>|||I am running SQL 2K.
--
Message posted via http://www.sqlmonster.com|||I am running SQL 2K.
--
Message posted via http://www.sqlmonster.com|||1. Sure it may free up some space but what happens when you need more room
in the db? You also need plenty of free space to do things like creating
and reindexing.
2. Reindexing an index will require at least 1.2 times the size of the
index or in the case of a clustered index the table itself.
3. Shrinking a file simply to gain more disk space is a bad idea in most
cases. There was some reason why the file got that large in the first place
and there is a good chance it will need that much space again. If you
shrink the file and use that space for something else what is going to
happen when the DB grows again? Chances are you will stop any modifications
on your sql server for that db. If you are that low on disk space I suggest
you get another disk before you really run into trouble.
--
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:30b3c02dee4a4a4988cebcf126e266f3@.SQLMonster.com...
>I am looking to shrink a database file using DBCC Shrinkfile to try to
>reclaim some disk space. For some unexplained reason I have some unsettled
>feelings. I need to confirm:
> 1. I was considering running DBCC Shrinkfile with the TruncateOnly
> argument, believing this is how I might free up some disk space?
> 2. If I do not have a clue as to what target size I might go for, what is
> safe?
> 3. Are there any unknown dangers lurking out their when running DBCC
> Shrinkfile?
> --
> Message posted via http://www.sqlmonster.comsql
1. I was considering running DBCC Shrinkfile with the TruncateOnly argument, believing this is how I might free up some disk space?
2. If I do not have a clue as to what target size I might go for, what is safe?
3. Are there any unknown dangers lurking out their when running DBCC Shrinkfile?
--
Message posted via http://www.sqlmonster.com"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:30b3c02dee4a4a4988cebcf126e266f3@.SQLMonster.com...
> I am looking to shrink a database file using DBCC Shrinkfile to try to
reclaim some disk space. For some unexplained reason I have some unsettled
feelings. I need to confirm:
> 1. I was considering running DBCC Shrinkfile with the TruncateOnly
argument, believing this is how I might free up some disk space?
> 2. If I do not have a clue as to what target size I might go for, what is
safe?
> 3. Are there any unknown dangers lurking out their when running DBCC
Shrinkfile?
>
1. That's the option you should use -- you might have to defragment some
indexes first, though. If you have heavy fragmentation you probably won't
reclaim much space (if any is available).
2. I usually use 1 if I want to reclaim ALL possible space. What that
option is actually doing is specifying a new minimum size for the database
(overriding whatever was set when it was created). So there's no real
danger in using whatever value you want...
3. None that I'm aware of -- probably a small performance hit if you run it
on a very active system, though...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||What version of sql are you using?
"Robert Richards via SQLMonster.com" wrote:
> I am looking to shrink a database file using DBCC Shrinkfile to try to reclaim some disk space. For some unexplained reason I have some unsettled feelings. I need to confirm:
> 1. I was considering running DBCC Shrinkfile with the TruncateOnly argument, believing this is how I might free up some disk space?
> 2. If I do not have a clue as to what target size I might go for, what is safe?
> 3. Are there any unknown dangers lurking out their when running DBCC Shrinkfile?
> --
> Message posted via http://www.sqlmonster.com
>|||I am running SQL 2K.
--
Message posted via http://www.sqlmonster.com|||I am running SQL 2K.
--
Message posted via http://www.sqlmonster.com|||1. Sure it may free up some space but what happens when you need more room
in the db? You also need plenty of free space to do things like creating
and reindexing.
2. Reindexing an index will require at least 1.2 times the size of the
index or in the case of a clustered index the table itself.
3. Shrinking a file simply to gain more disk space is a bad idea in most
cases. There was some reason why the file got that large in the first place
and there is a good chance it will need that much space again. If you
shrink the file and use that space for something else what is going to
happen when the DB grows again? Chances are you will stop any modifications
on your sql server for that db. If you are that low on disk space I suggest
you get another disk before you really run into trouble.
--
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:30b3c02dee4a4a4988cebcf126e266f3@.SQLMonster.com...
>I am looking to shrink a database file using DBCC Shrinkfile to try to
>reclaim some disk space. For some unexplained reason I have some unsettled
>feelings. I need to confirm:
> 1. I was considering running DBCC Shrinkfile with the TruncateOnly
> argument, believing this is how I might free up some disk space?
> 2. If I do not have a clue as to what target size I might go for, what is
> safe?
> 3. Are there any unknown dangers lurking out their when running DBCC
> Shrinkfile?
> --
> Message posted via http://www.sqlmonster.comsql
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
As I have discovered over several communications with you
(have been very helpful), its seems best that I have our
production database in a "Full" model recovery, with the
database file and transaction file in autogrowth mode.
Because my transaction file was extremely large I also
found out that I needed to do a "backup log" command in
order to shrink the transaction file. Another question:
Our database after a backup is 735mg and the database file
before the backup shows a size of 7Gigs. Is it necessary
to also shrink the database file. Does this mean that
there is a lot of space within the database that needs to
be cleaned out?
Thank you again,
RandyRand,
If your in Full recovery mode you need to issue a log backup on a regular
basis, not just to shrink it. And by the way backing it up does not shrink
it, that is a result of running DBCC SHRINKFILE. The backup log just
truncates the committed trans so it can be shrunk. When you say your
database is 735mg after a backup and 7Gigs before, where are you getting
these numbers?
--
Andrew J. Kelly
SQL Server MVP
"Rand Monroe" <randmo@.ix.netcom.com> wrote in message
news:042501c357c1$daf6ae10$a601280a@.phx.gbl...
> As I have discovered over several communications with you
> (have been very helpful), its seems best that I have our
> production database in a "Full" model recovery, with the
> database file and transaction file in autogrowth mode.
> Because my transaction file was extremely large I also
> found out that I needed to do a "backup log" command in
> order to shrink the transaction file. Another question:
> Our database after a backup is 735mg and the database file
> before the backup shows a size of 7Gigs. Is it necessary
> to also shrink the database file. Does this mean that
> there is a lot of space within the database that needs to
> be cleaned out?
> Thank you again,
> Randy
(have been very helpful), its seems best that I have our
production database in a "Full" model recovery, with the
database file and transaction file in autogrowth mode.
Because my transaction file was extremely large I also
found out that I needed to do a "backup log" command in
order to shrink the transaction file. Another question:
Our database after a backup is 735mg and the database file
before the backup shows a size of 7Gigs. Is it necessary
to also shrink the database file. Does this mean that
there is a lot of space within the database that needs to
be cleaned out?
Thank you again,
RandyRand,
If your in Full recovery mode you need to issue a log backup on a regular
basis, not just to shrink it. And by the way backing it up does not shrink
it, that is a result of running DBCC SHRINKFILE. The backup log just
truncates the committed trans so it can be shrunk. When you say your
database is 735mg after a backup and 7Gigs before, where are you getting
these numbers?
--
Andrew J. Kelly
SQL Server MVP
"Rand Monroe" <randmo@.ix.netcom.com> wrote in message
news:042501c357c1$daf6ae10$a601280a@.phx.gbl...
> As I have discovered over several communications with you
> (have been very helpful), its seems best that I have our
> production database in a "Full" model recovery, with the
> database file and transaction file in autogrowth mode.
> Because my transaction file was extremely large I also
> found out that I needed to do a "backup log" command in
> order to shrink the transaction file. Another question:
> Our database after a backup is 735mg and the database file
> before the backup shows a size of 7Gigs. Is it necessary
> to also shrink the database file. Does this mean that
> there is a lot of space within the database that needs to
> be cleaned out?
> Thank you again,
> Randy
DBCC Shrinkfile
Hi,
I am plng to shrink a production database logfile from
14 GB to 3 GB. Does this slow down the application or does
it block users from accessing the database while i am
shrinking it ? It is an OLTP system. Also any idea how
much time shrinking a 14GB file would take ? Just
ballpark ..
TIA
MOHi MO,
Its best to update your stats on the DB and run your DBCCs first then do a
backup. The shrik should not take that long I would expect 15 min at the
outside. However, it will slow the system down and the more load from users
that is on the box the longer it will take. I would recommend shceduling
this for a slow time of the day I usually do this after 3am.
Hope that helps
John ...
"Mo" <anonymous@.discussions.microsoft.com> wrote in message
news:068901c3fd47$74bb8ea0$a601280a@.phx.gbl...
> Hi,
> I am plng to shrink a production database logfile from
> 14 GB to 3 GB. Does this slow down the application or does
> it block users from accessing the database while i am
> shrinking it ? It is an OLTP system. Also any idea how
> much time shrinking a 14GB file would take ? Just
> ballpark ..
> TIA
> MO|||Mo - there's no way to predict how long a shrink will take on a live system
as it could block waiting for a lock or spend time searching for free space
if the space usage within the database is fragmented. It also depends on how
much heap and text usage you have, the speed of your IO subsystem, the
concurrent workload (as John says below) and so on. I'd be very surprised to
see it take only 15 minutes. The best you can do is time it on a completely
quiescent system and then extrapolate.
Shrink is setup to be the deadlock victim in all cases so should not cause
deadlocks. It does not hold long-term locks so should not block although
because it does hold page locks while moving pages, and does a bunch of IO
you should expect some slowdown. On a test system setup with a TPCC
benchmark workload running at close to 100% cpu, I've seen a roughly 20%
drop in transaction throughput while shrink is running - YMMV.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"John VanderVliet" <john.vandervliet@.sjrb.ca.NOSPAM> wrote in message
news:e6MPetW$DHA.2808@.TK2MSFTNGP10.phx.gbl...
> Hi MO,
> Its best to update your stats on the DB and run your DBCCs first then do a
> backup. The shrik should not take that long I would expect 15 min at the
> outside. However, it will slow the system down and the more load from
users
> that is on the box the longer it will take. I would recommend shceduling
> this for a slow time of the day I usually do this after 3am.
> Hope that helps
> John ...
> "Mo" <anonymous@.discussions.microsoft.com> wrote in message
> news:068901c3fd47$74bb8ea0$a601280a@.phx.gbl...
> > Hi,
> > I am plng to shrink a production database logfile from
> > 14 GB to 3 GB. Does this slow down the application or does
> > it block users from accessing the database while i am
> > shrinking it ? It is an OLTP system. Also any idea how
> > much time shrinking a 14GB file would take ? Just
> > ballpark ..
> > TIA
> > MO
>
I am plng to shrink a production database logfile from
14 GB to 3 GB. Does this slow down the application or does
it block users from accessing the database while i am
shrinking it ? It is an OLTP system. Also any idea how
much time shrinking a 14GB file would take ? Just
ballpark ..
TIA
MOHi MO,
Its best to update your stats on the DB and run your DBCCs first then do a
backup. The shrik should not take that long I would expect 15 min at the
outside. However, it will slow the system down and the more load from users
that is on the box the longer it will take. I would recommend shceduling
this for a slow time of the day I usually do this after 3am.
Hope that helps
John ...
"Mo" <anonymous@.discussions.microsoft.com> wrote in message
news:068901c3fd47$74bb8ea0$a601280a@.phx.gbl...
> Hi,
> I am plng to shrink a production database logfile from
> 14 GB to 3 GB. Does this slow down the application or does
> it block users from accessing the database while i am
> shrinking it ? It is an OLTP system. Also any idea how
> much time shrinking a 14GB file would take ? Just
> ballpark ..
> TIA
> MO|||Mo - there's no way to predict how long a shrink will take on a live system
as it could block waiting for a lock or spend time searching for free space
if the space usage within the database is fragmented. It also depends on how
much heap and text usage you have, the speed of your IO subsystem, the
concurrent workload (as John says below) and so on. I'd be very surprised to
see it take only 15 minutes. The best you can do is time it on a completely
quiescent system and then extrapolate.
Shrink is setup to be the deadlock victim in all cases so should not cause
deadlocks. It does not hold long-term locks so should not block although
because it does hold page locks while moving pages, and does a bunch of IO
you should expect some slowdown. On a test system setup with a TPCC
benchmark workload running at close to 100% cpu, I've seen a roughly 20%
drop in transaction throughput while shrink is running - YMMV.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"John VanderVliet" <john.vandervliet@.sjrb.ca.NOSPAM> wrote in message
news:e6MPetW$DHA.2808@.TK2MSFTNGP10.phx.gbl...
> Hi MO,
> Its best to update your stats on the DB and run your DBCCs first then do a
> backup. The shrik should not take that long I would expect 15 min at the
> outside. However, it will slow the system down and the more load from
users
> that is on the box the longer it will take. I would recommend shceduling
> this for a slow time of the day I usually do this after 3am.
> Hope that helps
> John ...
> "Mo" <anonymous@.discussions.microsoft.com> wrote in message
> news:068901c3fd47$74bb8ea0$a601280a@.phx.gbl...
> > Hi,
> > I am plng to shrink a production database logfile from
> > 14 GB to 3 GB. Does this slow down the application or does
> > it block users from accessing the database while i am
> > shrinking it ? It is an OLTP system. Also any idea how
> > much time shrinking a 14GB file would take ? Just
> > ballpark ..
> > TIA
> > MO
>
DBCC SHRINKFILE
A few days ago, I made some structure changes to 5 tables and added
some indexes to them.
This caused the mdf file size to increase from 82GB to 109GB. Because
our development and training boxes do not have enough disk space, I
tried to shrink the file so that the database can fit into the
development box when I do a restore.
I created a job to run the following at 3AM:
DBCC SHRINKFILE (N'Nu_Data', 85000)
At 6AM, the job crashed with the following error.
Transaction (Process ID 66) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction.
Our database is running transactional replication. Do I need to pause
the replication and set the database in single user mode before
executing the DBCC SHRINKFILE (N'N_Data', 85000)?
alter database Nu
set single_user with ROLLBACK IMMEDIATE
go
DBCC SHRINKFILE (N'Nu_Data', 85000)
go
alter database Nu
set multi_user
goHi
Execute DBCC SHRINKFILE when there is no active transacton in progress for that database. Check this by executing
DBCC OPENTRAN(DBNAME). It can be a replication process or it can can be a normal user connecting to the server and using some DML.
So execute the SHRINKFILE command when there is no activity in the database. Preferably do not schedule this as a job, rather do it manually if it is a production serve
Thank
Har
MCDB
-- KC wrote: --
A few days ago, I made some structure changes to 5 tables and adde
some indexes to them
This caused the mdf file size to increase from 82GB to 109GB. Becaus
our development and training boxes do not have enough disk space,
tried to shrink the file so that the database can fit into th
development box when I do a restore
I created a job to run the following at 3AM
DBCC SHRINKFILE (N'Nu_Data', 85000
At 6AM, the job crashed with the following error
Transaction (Process ID 66) was deadlocked on lock resources wit
another process and has been chosen as the deadlock victim. Rerun th
transaction
Our database is running transactional replication. Do I need to paus
the replication and set the database in single user mode befor
executing the DBCC SHRINKFILE (N'N_Data', 85000)
alter database N
set single_user with ROLLBACK IMMEDIATE
g
DBCC SHRINKFILE (N'Nu_Data', 85000
g
alter database N
set multi_use
g
some indexes to them.
This caused the mdf file size to increase from 82GB to 109GB. Because
our development and training boxes do not have enough disk space, I
tried to shrink the file so that the database can fit into the
development box when I do a restore.
I created a job to run the following at 3AM:
DBCC SHRINKFILE (N'Nu_Data', 85000)
At 6AM, the job crashed with the following error.
Transaction (Process ID 66) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction.
Our database is running transactional replication. Do I need to pause
the replication and set the database in single user mode before
executing the DBCC SHRINKFILE (N'N_Data', 85000)?
alter database Nu
set single_user with ROLLBACK IMMEDIATE
go
DBCC SHRINKFILE (N'Nu_Data', 85000)
go
alter database Nu
set multi_user
goHi
Execute DBCC SHRINKFILE when there is no active transacton in progress for that database. Check this by executing
DBCC OPENTRAN(DBNAME). It can be a replication process or it can can be a normal user connecting to the server and using some DML.
So execute the SHRINKFILE command when there is no activity in the database. Preferably do not schedule this as a job, rather do it manually if it is a production serve
Thank
Har
MCDB
-- KC wrote: --
A few days ago, I made some structure changes to 5 tables and adde
some indexes to them
This caused the mdf file size to increase from 82GB to 109GB. Becaus
our development and training boxes do not have enough disk space,
tried to shrink the file so that the database can fit into th
development box when I do a restore
I created a job to run the following at 3AM
DBCC SHRINKFILE (N'Nu_Data', 85000
At 6AM, the job crashed with the following error
Transaction (Process ID 66) was deadlocked on lock resources wit
another process and has been chosen as the deadlock victim. Rerun th
transaction
Our database is running transactional replication. Do I need to paus
the replication and set the database in single user mode befor
executing the DBCC SHRINKFILE (N'N_Data', 85000)
alter database N
set single_user with ROLLBACK IMMEDIATE
g
DBCC SHRINKFILE (N'Nu_Data', 85000
g
alter database N
set multi_use
g
dbcc shrinkfile
I have a DB with two transaction log files. I'd like have
one of them removed.
After running the following set of commands successfully...
backup log db_tdadatamart with truncate_only
dbcc shrinkfile ('datamartLog', EMPTYFILE)
...when I try to remove the second file using this
command...
alter database db_datamart
remove file datamartLog
...I get this error:
The file 'datamartLog' cannot be removed because it is not
empty.
Thanks in advance for your help.Run DBCC OPENTRAN to ensure there are no open transactions. And you might
want to make sure you have adone a log backup after that command as well.
--
Andrew J. Kelly SQL MVP
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:1df5a01c4546d$9c7f3f90$a001280a@.phx.gbl...
> I have a DB with two transaction log files. I'd like have
> one of them removed.
> After running the following set of commands successfully...
> backup log db_tdadatamart with truncate_only
> dbcc shrinkfile ('datamartLog', EMPTYFILE)
> ...when I try to remove the second file using this
> command...
> alter database db_datamart
> remove file datamartLog
> ...I get this error:
> The file 'datamartLog' cannot be removed because it is not
> empty.
> Thanks in advance for your help.|||'No active open transactions' were reported... still
encoutner the same problem.
Thanks.
>--Original Message--
>Run DBCC OPENTRAN to ensure there are no open
transactions. And you might
>want to make sure you have adone a log backup after that
command as well.
>--
>Andrew J. Kelly SQL MVP
>
>"Rob" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1df5a01c4546d$9c7f3f90$a001280a@.phx.gbl...
>> I have a DB with two transaction log files. I'd like
have
>> one of them removed.
>> After running the following set of commands
successfully...
>> backup log db_tdadatamart with truncate_only
>> dbcc shrinkfile ('datamartLog', EMPTYFILE)
>> ...when I try to remove the second file using this
>> command...
>> alter database db_datamart
>> remove file datamartLog
>> ...I get this error:
>> The file 'datamartLog' cannot be removed because it is
not
>> empty.
>> Thanks in advance for your help.
>
>.
>|||Did you do a Log backup? Any chance this is related?
http://support.microsoft.com/default.aspx?scid=kb;en-us;324432&Product=sql2k
--
Andrew J. Kelly SQL MVP
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:1d78101c45470$65a7c2a0$a601280a@.phx.gbl...
> 'No active open transactions' were reported... still
> encoutner the same problem.
> Thanks.
> >--Original Message--
> >Run DBCC OPENTRAN to ensure there are no open
> transactions. And you might
> >want to make sure you have adone a log backup after that
> command as well.
> >
> >--
> >Andrew J. Kelly SQL MVP
> >
> >
> >"Rob" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:1df5a01c4546d$9c7f3f90$a001280a@.phx.gbl...
> >> I have a DB with two transaction log files. I'd like
> have
> >> one of them removed.
> >>
> >> After running the following set of commands
> successfully...
> >>
> >> backup log db_tdadatamart with truncate_only
> >> dbcc shrinkfile ('datamartLog', EMPTYFILE)
> >>
> >> ...when I try to remove the second file using this
> >> command...
> >>
> >> alter database db_datamart
> >> remove file datamartLog
> >>
> >> ...I get this error:
> >>
> >> The file 'datamartLog' cannot be removed because it is
> not
> >> empty.
> >>
> >> Thanks in advance for your help.
> >
> >
> >.
> >|||I have some information about DBCC LOGINFO etc on my article regarding shrinking of database files:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:1d78101c45470$65a7c2a0$a601280a@.phx.gbl...
> 'No active open transactions' were reported... still
> encoutner the same problem.
> Thanks.
> >--Original Message--
> >Run DBCC OPENTRAN to ensure there are no open
> transactions. And you might
> >want to make sure you have adone a log backup after that
> command as well.
> >
> >--
> >Andrew J. Kelly SQL MVP
> >
> >
> >"Rob" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:1df5a01c4546d$9c7f3f90$a001280a@.phx.gbl...
> >> I have a DB with two transaction log files. I'd like
> have
> >> one of them removed.
> >>
> >> After running the following set of commands
> successfully...
> >>
> >> backup log db_tdadatamart with truncate_only
> >> dbcc shrinkfile ('datamartLog', EMPTYFILE)
> >>
> >> ...when I try to remove the second file using this
> >> command...
> >>
> >> alter database db_datamart
> >> remove file datamartLog
> >>
> >> ...I get this error:
> >>
> >> The file 'datamartLog' cannot be removed because it is
> not
> >> empty.
> >>
> >> Thanks in advance for your help.
> >
> >
> >.
> >sql
one of them removed.
After running the following set of commands successfully...
backup log db_tdadatamart with truncate_only
dbcc shrinkfile ('datamartLog', EMPTYFILE)
...when I try to remove the second file using this
command...
alter database db_datamart
remove file datamartLog
...I get this error:
The file 'datamartLog' cannot be removed because it is not
empty.
Thanks in advance for your help.Run DBCC OPENTRAN to ensure there are no open transactions. And you might
want to make sure you have adone a log backup after that command as well.
--
Andrew J. Kelly SQL MVP
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:1df5a01c4546d$9c7f3f90$a001280a@.phx.gbl...
> I have a DB with two transaction log files. I'd like have
> one of them removed.
> After running the following set of commands successfully...
> backup log db_tdadatamart with truncate_only
> dbcc shrinkfile ('datamartLog', EMPTYFILE)
> ...when I try to remove the second file using this
> command...
> alter database db_datamart
> remove file datamartLog
> ...I get this error:
> The file 'datamartLog' cannot be removed because it is not
> empty.
> Thanks in advance for your help.|||'No active open transactions' were reported... still
encoutner the same problem.
Thanks.
>--Original Message--
>Run DBCC OPENTRAN to ensure there are no open
transactions. And you might
>want to make sure you have adone a log backup after that
command as well.
>--
>Andrew J. Kelly SQL MVP
>
>"Rob" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1df5a01c4546d$9c7f3f90$a001280a@.phx.gbl...
>> I have a DB with two transaction log files. I'd like
have
>> one of them removed.
>> After running the following set of commands
successfully...
>> backup log db_tdadatamart with truncate_only
>> dbcc shrinkfile ('datamartLog', EMPTYFILE)
>> ...when I try to remove the second file using this
>> command...
>> alter database db_datamart
>> remove file datamartLog
>> ...I get this error:
>> The file 'datamartLog' cannot be removed because it is
not
>> empty.
>> Thanks in advance for your help.
>
>.
>|||Did you do a Log backup? Any chance this is related?
http://support.microsoft.com/default.aspx?scid=kb;en-us;324432&Product=sql2k
--
Andrew J. Kelly SQL MVP
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:1d78101c45470$65a7c2a0$a601280a@.phx.gbl...
> 'No active open transactions' were reported... still
> encoutner the same problem.
> Thanks.
> >--Original Message--
> >Run DBCC OPENTRAN to ensure there are no open
> transactions. And you might
> >want to make sure you have adone a log backup after that
> command as well.
> >
> >--
> >Andrew J. Kelly SQL MVP
> >
> >
> >"Rob" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:1df5a01c4546d$9c7f3f90$a001280a@.phx.gbl...
> >> I have a DB with two transaction log files. I'd like
> have
> >> one of them removed.
> >>
> >> After running the following set of commands
> successfully...
> >>
> >> backup log db_tdadatamart with truncate_only
> >> dbcc shrinkfile ('datamartLog', EMPTYFILE)
> >>
> >> ...when I try to remove the second file using this
> >> command...
> >>
> >> alter database db_datamart
> >> remove file datamartLog
> >>
> >> ...I get this error:
> >>
> >> The file 'datamartLog' cannot be removed because it is
> not
> >> empty.
> >>
> >> Thanks in advance for your help.
> >
> >
> >.
> >|||I have some information about DBCC LOGINFO etc on my article regarding shrinking of database files:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:1d78101c45470$65a7c2a0$a601280a@.phx.gbl...
> 'No active open transactions' were reported... still
> encoutner the same problem.
> Thanks.
> >--Original Message--
> >Run DBCC OPENTRAN to ensure there are no open
> transactions. And you might
> >want to make sure you have adone a log backup after that
> command as well.
> >
> >--
> >Andrew J. Kelly SQL MVP
> >
> >
> >"Rob" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:1df5a01c4546d$9c7f3f90$a001280a@.phx.gbl...
> >> I have a DB with two transaction log files. I'd like
> have
> >> one of them removed.
> >>
> >> After running the following set of commands
> successfully...
> >>
> >> backup log db_tdadatamart with truncate_only
> >> dbcc shrinkfile ('datamartLog', EMPTYFILE)
> >>
> >> ...when I try to remove the second file using this
> >> command...
> >>
> >> alter database db_datamart
> >> remove file datamartLog
> >>
> >> ...I get this error:
> >>
> >> The file 'datamartLog' cannot be removed because it is
> not
> >> empty.
> >>
> >> Thanks in advance for your help.
> >
> >
> >.
> >sql
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?
>>
>>
>>
>
Subscribe to:
Posts (Atom)