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
Showing posts with label file. Show all posts
Showing posts with label file. Show all posts
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 - 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
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
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 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
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
SQL Server 2000
I have a large .ldf file that I want to shrink, however, DBCC SHRINKFILE
will only shrink it so much. The file went from 74 MB to 50 MB, but won't go
smaller. I have been routinely shrinking low use DB log files to 20 MB, so I
know that's not it.
Original: DB_Log 74 MB
Ran:
use DB
exec sp_helpfile -- to get logical name
BACKUP LOG DB WITH TRUNCATE_ONLY
DBCC SHRINKFILE (DB_Log, 20, TRUNCATEONLY)
I get the message: "Cannot shrink log file 2 (DB_Log) because total number
of logical log files cannot be fewer than 2."
The output of 'sp_helpfile' is:
DB_Data 1 e:\data\DB.mdf PRIMARY 149504 KB Unlimited 5%
data only
DB_Log 2 e:\data\DB_Log.ldf NULL 51080 KB Unlimited
5% log only
The output of: DBCC sqlperf(logspace)
DB 49.875 0.40922618 0
Says there are little, to no transactions in it.
Now the Log file is 50 MB and won't shrink any further.
Whats up with that?Did you run DBCC OPENTRAN to check out for possible open\uncommited
transactions?
If there are active virtual logs in it, it's not gonna shrink. Besides, you
could use DBCC LOGINFO('myDatabase') as Tibor mentioned in his great
article. I suggest you to check it out from the following link:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Ekrem Önsoy
"Jay" <nospan@.nospam.org> wrote in message
news:emoz%23e1DIHA.1164@.TK2MSFTNGP02.phx.gbl...
> SQL Server 2000
> I have a large .ldf file that I want to shrink, however, DBCC SHRINKFILE
> will only shrink it so much. The file went from 74 MB to 50 MB, but won't
> go smaller. I have been routinely shrinking low use DB log files to 20 MB,
> so I know that's not it.
> Original: DB_Log 74 MB
> Ran:
> use DB
> exec sp_helpfile -- to get logical name
> BACKUP LOG DB WITH TRUNCATE_ONLY
> DBCC SHRINKFILE (DB_Log, 20, TRUNCATEONLY)
> I get the message: "Cannot shrink log file 2 (DB_Log) because total number
> of logical log files cannot be fewer than 2."
> The output of 'sp_helpfile' is:
> DB_Data 1 e:\data\DB.mdf PRIMARY 149504 KB Unlimited 5%
> data only
> DB_Log 2 e:\data\DB_Log.ldf NULL 51080 KB Unlimited
> 5% log only
> The output of: DBCC sqlperf(logspace)
> DB 49.875 0.40922618 0
> Says there are little, to no transactions in it.
> Now the Log file is 50 MB and won't shrink any further.
>
> Whats up with that?
>|||Well, that info allowed me to shrink the 2.5 GB logfile. However, I have two
left that that won't shrink below 50 MB (both DB's are about 150 MB and they
don't get used much), which I would like to get to 20 MB.
The message: "Cannot shrink log file 2 (DB_Log) because total number of
logical log files cannot be fewer than 2."
Seems to be refering to the VLF's as both DB's only get two rows returned
from DBCC LOGINFO
2 26,148,864 8192 189239 0 128 0
2 26,148,864 26157056 189240 2 64 0
and
2 26,148,864 8192 189110 2 128 0
2 26,148,864 26157056 189109 0 128 0
I'm guessing that this is because they were created with a 50 MB log file
and (most important) that I would have to do a "dump and reload" to get the
physical file smaller.
"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:79B1E902-AD6B-4DD7-BB4D-9896B31613D8@.microsoft.com...
> Did you run DBCC OPENTRAN to check out for possible open\uncommited
> transactions?
> If there are active virtual logs in it, it's not gonna shrink. Besides,
> you could use DBCC LOGINFO('myDatabase') as Tibor mentioned in his great
> article. I suggest you to check it out from the following link:
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>
> --
> Ekrem Önsoy
>
> "Jay" <nospan@.nospam.org> wrote in message
> news:emoz%23e1DIHA.1164@.TK2MSFTNGP02.phx.gbl...
>> SQL Server 2000
>> I have a large .ldf file that I want to shrink, however, DBCC SHRINKFILE
>> will only shrink it so much. The file went from 74 MB to 50 MB, but won't
>> go smaller. I have been routinely shrinking low use DB log files to 20
>> MB, so I know that's not it.
>> Original: DB_Log 74 MB
>> Ran:
>> use DB
>> exec sp_helpfile -- to get logical name
>> BACKUP LOG DB WITH TRUNCATE_ONLY
>> DBCC SHRINKFILE (DB_Log, 20, TRUNCATEONLY)
>> I get the message: "Cannot shrink log file 2 (DB_Log) because total
>> number of logical log files cannot be fewer than 2."
>> The output of 'sp_helpfile' is:
>> DB_Data 1 e:\data\DB.mdf PRIMARY 149504 KB Unlimited
>> 5% data only
>> DB_Log 2 e:\data\DB_Log.ldf NULL 51080 KB
>> Unlimited 5% log only
>> The output of: DBCC sqlperf(logspace)
>> DB 49.875 0.40922618 0
>> Says there are little, to no transactions in it.
>> Now the Log file is 50 MB and won't shrink any further.
>>
>> Whats up with that?
>|||If they were originally created at 50MB they may not be able to shrink any
further. If you run DBCC LOGINFO do you only have 1 VLF? If so you can't
shrink anymore. Is 30MB really an issue?
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Jay" <nospan@.nospam.org> wrote in message
news:uO0NgV2DIHA.5328@.TK2MSFTNGP05.phx.gbl...
> Well, that info allowed me to shrink the 2.5 GB logfile. However, I have
> two left that that won't shrink below 50 MB (both DB's are about 150 MB
> and they don't get used much), which I would like to get to 20 MB.
> The message: "Cannot shrink log file 2 (DB_Log) because total number of
> logical log files cannot be fewer than 2."
> Seems to be refering to the VLF's as both DB's only get two rows returned
> from DBCC LOGINFO
> 2 26,148,864 8192 189239 0 128 0
> 2 26,148,864 26157056 189240 2 64 0
> and
> 2 26,148,864 8192 189110 2 128 0
> 2 26,148,864 26157056 189109 0 128 0
> I'm guessing that this is because they were created with a 50 MB log file
> and (most important) that I would have to do a "dump and reload" to get
> the physical file smaller.
>
> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
> news:79B1E902-AD6B-4DD7-BB4D-9896B31613D8@.microsoft.com...
>> Did you run DBCC OPENTRAN to check out for possible open\uncommited
>> transactions?
>> If there are active virtual logs in it, it's not gonna shrink. Besides,
>> you could use DBCC LOGINFO('myDatabase') as Tibor mentioned in his great
>> article. I suggest you to check it out from the following link:
>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>>
>> --
>> Ekrem Önsoy
>>
>> "Jay" <nospan@.nospam.org> wrote in message
>> news:emoz%23e1DIHA.1164@.TK2MSFTNGP02.phx.gbl...
>> SQL Server 2000
>> I have a large .ldf file that I want to shrink, however, DBCC SHRINKFILE
>> will only shrink it so much. The file went from 74 MB to 50 MB, but
>> won't go smaller. I have been routinely shrinking low use DB log files
>> to 20 MB, so I know that's not it.
>> Original: DB_Log 74 MB
>> Ran:
>> use DB
>> exec sp_helpfile -- to get logical name
>> BACKUP LOG DB WITH TRUNCATE_ONLY
>> DBCC SHRINKFILE (DB_Log, 20, TRUNCATEONLY)
>> I get the message: "Cannot shrink log file 2 (DB_Log) because total
>> number of logical log files cannot be fewer than 2."
>> The output of 'sp_helpfile' is:
>> DB_Data 1 e:\data\DB.mdf PRIMARY 149504 KB Unlimited
>> 5% data only
>> DB_Log 2 e:\data\DB_Log.ldf NULL 51080 KB
>> Unlimited 5% log only
>> The output of: DBCC sqlperf(logspace)
>> DB 49.875 0.40922618 0
>> Says there are little, to no transactions in it.
>> Now the Log file is 50 MB and won't shrink any further.
>>
>> Whats up with that?
>>
>|||As other has noted, you seem to be down to only two VLFs, which is the minimum number for a log
file. SQL Server need to be able to "circle through" the VLFs, which is difficult to do with only
one VLF. I believe that you get a minimum of 4 VLFs when you create a log file (although haven't
seen the exact algorithm), so probably the log file was created with a larger size, perhaps 100MB.
I'm with Andrew on this, leave them at 50MB.
You *might* be able to get the size down by adding another file, then shrinking the 50MB file using
the EMPTYFILE option, verifying that the active log is on the new file and then ALTER DATABASE
REMOVE FILE. The trick here is that you can't remove the file until LOGINFO show you that non of the
VLFs are used.
I say "might", because I'm not sure whether SQL Server has the concept of "a primary" log file such
as it has for data files. I don't think so, at least I don't see any architectural reasons.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <nospan@.nospam.org> wrote in message news:uO0NgV2DIHA.5328@.TK2MSFTNGP05.phx.gbl...
> Well, that info allowed me to shrink the 2.5 GB logfile. However, I have two left that that won't
> shrink below 50 MB (both DB's are about 150 MB and they don't get used much), which I would like
> to get to 20 MB.
> The message: "Cannot shrink log file 2 (DB_Log) because total number of logical log files cannot
> be fewer than 2."
> Seems to be refering to the VLF's as both DB's only get two rows returned from DBCC LOGINFO
> 2 26,148,864 8192 189239 0 128 0
> 2 26,148,864 26157056 189240 2 64 0
> and
> 2 26,148,864 8192 189110 2 128 0
> 2 26,148,864 26157056 189109 0 128 0
> I'm guessing that this is because they were created with a 50 MB log file and (most important)
> that I would have to do a "dump and reload" to get the physical file smaller.
>
> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
> news:79B1E902-AD6B-4DD7-BB4D-9896B31613D8@.microsoft.com...
>> Did you run DBCC OPENTRAN to check out for possible open\uncommited transactions?
>> If there are active virtual logs in it, it's not gonna shrink. Besides, you could use DBCC
>> LOGINFO('myDatabase') as Tibor mentioned in his great article. I suggest you to check it out from
>> the following link:
>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>>
>> --
>> Ekrem Önsoy
>>
>> "Jay" <nospan@.nospam.org> wrote in message news:emoz%23e1DIHA.1164@.TK2MSFTNGP02.phx.gbl...
>> SQL Server 2000
>> I have a large .ldf file that I want to shrink, however, DBCC SHRINKFILE will only shrink it so
>> much. The file went from 74 MB to 50 MB, but won't go smaller. I have been routinely shrinking
>> low use DB log files to 20 MB, so I know that's not it.
>> Original: DB_Log 74 MB
>> Ran:
>> use DB
>> exec sp_helpfile -- to get logical name
>> BACKUP LOG DB WITH TRUNCATE_ONLY
>> DBCC SHRINKFILE (DB_Log, 20, TRUNCATEONLY)
>> I get the message: "Cannot shrink log file 2 (DB_Log) because total number of logical log files
>> cannot be fewer than 2."
>> The output of 'sp_helpfile' is:
>> DB_Data 1 e:\data\DB.mdf PRIMARY 149504 KB Unlimited 5% data only
>> DB_Log 2 e:\data\DB_Log.ldf NULL 51080 KB Unlimited 5% log only
>> The output of: DBCC sqlperf(logspace)
>> DB 49.875 0.40922618 0
>> Says there are little, to no transactions in it.
>> Now the Log file is 50 MB and won't shrink any further.
>>
>> Whats up with that?
>>
>|||There is a "primary log file" thing and it can be emptied but can not be
removed.
For information only.
--
Ekrem Önsoy
MCBDA, MCTS: SQL Server 2005, MCITP:DBA, MCSD.Net, MCSE, MCT
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:BF611763-4529-40F1-81EB-22476C01611F@.microsoft.com...
> As other has noted, you seem to be down to only two VLFs, which is the
> minimum number for a log file. SQL Server need to be able to "circle
> through" the VLFs, which is difficult to do with only one VLF. I believe
> that you get a minimum of 4 VLFs when you create a log file (although
> haven't seen the exact algorithm), so probably the log file was created
> with a larger size, perhaps 100MB.
> I'm with Andrew on this, leave them at 50MB.
> You *might* be able to get the size down by adding another file, then
> shrinking the 50MB file using the EMPTYFILE option, verifying that the
> active log is on the new file and then ALTER DATABASE REMOVE FILE. The
> trick here is that you can't remove the file until LOGINFO show you that
> non of the VLFs are used.
> I say "might", because I'm not sure whether SQL Server has the concept of
> "a primary" log file such as it has for data files. I don't think so, at
> least I don't see any architectural reasons.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jay" <nospan@.nospam.org> wrote in message
> news:uO0NgV2DIHA.5328@.TK2MSFTNGP05.phx.gbl...
>> Well, that info allowed me to shrink the 2.5 GB logfile. However, I have
>> two left that that won't shrink below 50 MB (both DB's are about 150 MB
>> and they don't get used much), which I would like to get to 20 MB.
>> The message: "Cannot shrink log file 2 (DB_Log) because total number of
>> logical log files cannot be fewer than 2."
>> Seems to be refering to the VLF's as both DB's only get two rows returned
>> from DBCC LOGINFO
>> 2 26,148,864 8192 189239 0 128 0
>> 2 26,148,864 26157056 189240 2 64 0
>> and
>> 2 26,148,864 8192 189110 2 128 0
>> 2 26,148,864 26157056 189109 0 128 0
>> I'm guessing that this is because they were created with a 50 MB log file
>> and (most important) that I would have to do a "dump and reload" to get
>> the physical file smaller.
>>
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:79B1E902-AD6B-4DD7-BB4D-9896B31613D8@.microsoft.com...
>> Did you run DBCC OPENTRAN to check out for possible open\uncommited
>> transactions?
>> If there are active virtual logs in it, it's not gonna shrink. Besides,
>> you could use DBCC LOGINFO('myDatabase') as Tibor mentioned in his great
>> article. I suggest you to check it out from the following link:
>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>>
>> --
>> Ekrem Önsoy
>>
>> "Jay" <nospan@.nospam.org> wrote in message
>> news:emoz%23e1DIHA.1164@.TK2MSFTNGP02.phx.gbl...
>> SQL Server 2000
>> I have a large .ldf file that I want to shrink, however, DBCC
>> SHRINKFILE will only shrink it so much. The file went from 74 MB to 50
>> MB, but won't go smaller. I have been routinely shrinking low use DB
>> log files to 20 MB, so I know that's not it.
>> Original: DB_Log 74 MB
>> Ran:
>> use DB
>> exec sp_helpfile -- to get logical name
>> BACKUP LOG DB WITH TRUNCATE_ONLY
>> DBCC SHRINKFILE (DB_Log, 20, TRUNCATEONLY)
>> I get the message: "Cannot shrink log file 2 (DB_Log) because total
>> number of logical log files cannot be fewer than 2."
>> The output of 'sp_helpfile' is:
>> DB_Data 1 e:\data\DB.mdf PRIMARY 149504 KB Unlimited
>> 5% data only
>> DB_Log 2 e:\data\DB_Log.ldf NULL 51080 KB
>> Unlimited 5% log only
>> The output of: DBCC sqlperf(logspace)
>> DB 49.875 0.40922618 0
>> Says there are little, to no transactions in it.
>> Now the Log file is 50 MB and won't shrink any further.
>>
>> Whats up with that?
>>
>>
>|||> There is a "primary log file" thing and it can be emptied but can not be removed.
Yes, I see that now. The error message is pretty clear on that:
"Msg 5020, Level 16, State 1, Line 1
The primary data or log file cannot be removed from a database."
Thanks Ekrem.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:86D5E5D2-A204-4554-A271-F6F73AE4D296@.microsoft.com...
> There is a "primary log file" thing and it can be emptied but can not be removed.
> For information only.
> --
> Ekrem Önsoy
> MCBDA, MCTS: SQL Server 2005, MCITP:DBA, MCSD.Net, MCSE, MCT
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:BF611763-4529-40F1-81EB-22476C01611F@.microsoft.com...
>> As other has noted, you seem to be down to only two VLFs, which is the minimum number for a log
>> file. SQL Server need to be able to "circle through" the VLFs, which is difficult to do with only
>> one VLF. I believe that you get a minimum of 4 VLFs when you create a log file (although haven't
>> seen the exact algorithm), so probably the log file was created with a larger size, perhaps
>> 100MB.
>> I'm with Andrew on this, leave them at 50MB.
>> You *might* be able to get the size down by adding another file, then shrinking the 50MB file
>> using the EMPTYFILE option, verifying that the active log is on the new file and then ALTER
>> DATABASE REMOVE FILE. The trick here is that you can't remove the file until LOGINFO show you
>> that non of the VLFs are used.
>> I say "might", because I'm not sure whether SQL Server has the concept of "a primary" log file
>> such as it has for data files. I don't think so, at least I don't see any architectural reasons.
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Jay" <nospan@.nospam.org> wrote in message news:uO0NgV2DIHA.5328@.TK2MSFTNGP05.phx.gbl...
>> Well, that info allowed me to shrink the 2.5 GB logfile. However, I have two left that that
>> won't shrink below 50 MB (both DB's are about 150 MB and they don't get used much), which I
>> would like to get to 20 MB.
>> The message: "Cannot shrink log file 2 (DB_Log) because total number of logical log files cannot
>> be fewer than 2."
>> Seems to be refering to the VLF's as both DB's only get two rows returned from DBCC LOGINFO
>> 2 26,148,864 8192 189239 0 128 0
>> 2 26,148,864 26157056 189240 2 64 0
>> and
>> 2 26,148,864 8192 189110 2 128 0
>> 2 26,148,864 26157056 189109 0 128 0
>> I'm guessing that this is because they were created with a 50 MB log file and (most important)
>> that I would have to do a "dump and reload" to get the physical file smaller.
>>
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:79B1E902-AD6B-4DD7-BB4D-9896B31613D8@.microsoft.com...
>> Did you run DBCC OPENTRAN to check out for possible open\uncommited transactions?
>> If there are active virtual logs in it, it's not gonna shrink. Besides, you could use DBCC
>> LOGINFO('myDatabase') as Tibor mentioned in his great article. I suggest you to check it out
>> from the following link:
>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>>
>> --
>> Ekrem Önsoy
>>
>> "Jay" <nospan@.nospam.org> wrote in message news:emoz%23e1DIHA.1164@.TK2MSFTNGP02.phx.gbl...
>> SQL Server 2000
>> I have a large .ldf file that I want to shrink, however, DBCC SHRINKFILE will only shrink it
>> so much. The file went from 74 MB to 50 MB, but won't go smaller. I have been routinely
>> shrinking low use DB log files to 20 MB, so I know that's not it.
>> Original: DB_Log 74 MB
>> Ran:
>> use DB
>> exec sp_helpfile -- to get logical name
>> BACKUP LOG DB WITH TRUNCATE_ONLY
>> DBCC SHRINKFILE (DB_Log, 20, TRUNCATEONLY)
>> I get the message: "Cannot shrink log file 2 (DB_Log) because total number of logical log
>> files cannot be fewer than 2."
>> The output of 'sp_helpfile' is:
>> DB_Data 1 e:\data\DB.mdf PRIMARY 149504 KB Unlimited 5% data only
>> DB_Log 2 e:\data\DB_Log.ldf NULL 51080 KB Unlimited 5% log only
>> The output of: DBCC sqlperf(logspace)
>> DB 49.875 0.40922618 0
>> Says there are little, to no transactions in it.
>> Now the Log file is 50 MB and won't shrink any further.
>>
>> Whats up with that?
>>
>>
>|||Thanks guys.
You're right, 30 MB is no big deal. Still, if I could have whacked the
excess, I would have prefered to.
The issue is actually one of my own creation. I have a report that tells me
when a log file is over 25% of the data file and also over 20 MB. So, a 50
MB log on a 140 MB DB fires the alarm ( I'm also looking for file
growth/shrinkage and tracking overall drive usage across the farm). I just
increased the data file to 201 MB and will deal with it later.
I suppose a dump and reload is my only option ... hmm, I think I'll try a
backup/restore from EM, see what that does.
Thanks,
Jay
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:BF611763-4529-40F1-81EB-22476C01611F@.microsoft.com...
> As other has noted, you seem to be down to only two VLFs, which is the
> minimum number for a log file. SQL Server need to be able to "circle
> through" the VLFs, which is difficult to do with only one VLF. I believe
> that you get a minimum of 4 VLFs when you create a log file (although
> haven't seen the exact algorithm), so probably the log file was created
> with a larger size, perhaps 100MB.
> I'm with Andrew on this, leave them at 50MB.
> You *might* be able to get the size down by adding another file, then
> shrinking the 50MB file using the EMPTYFILE option, verifying that the
> active log is on the new file and then ALTER DATABASE REMOVE FILE. The
> trick here is that you can't remove the file until LOGINFO show you that
> non of the VLFs are used.
> I say "might", because I'm not sure whether SQL Server has the concept of
> "a primary" log file such as it has for data files. I don't think so, at
> least I don't see any architectural reasons.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jay" <nospan@.nospam.org> wrote in message
> news:uO0NgV2DIHA.5328@.TK2MSFTNGP05.phx.gbl...
>> Well, that info allowed me to shrink the 2.5 GB logfile. However, I have
>> two left that that won't shrink below 50 MB (both DB's are about 150 MB
>> and they don't get used much), which I would like to get to 20 MB.
>> The message: "Cannot shrink log file 2 (DB_Log) because total number of
>> logical log files cannot be fewer than 2."
>> Seems to be refering to the VLF's as both DB's only get two rows returned
>> from DBCC LOGINFO
>> 2 26,148,864 8192 189239 0 128 0
>> 2 26,148,864 26157056 189240 2 64 0
>> and
>> 2 26,148,864 8192 189110 2 128 0
>> 2 26,148,864 26157056 189109 0 128 0
>> I'm guessing that this is because they were created with a 50 MB log file
>> and (most important) that I would have to do a "dump and reload" to get
>> the physical file smaller.
>>
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:79B1E902-AD6B-4DD7-BB4D-9896B31613D8@.microsoft.com...
>> Did you run DBCC OPENTRAN to check out for possible open\uncommited
>> transactions?
>> If there are active virtual logs in it, it's not gonna shrink. Besides,
>> you could use DBCC LOGINFO('myDatabase') as Tibor mentioned in his great
>> article. I suggest you to check it out from the following link:
>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>>
>> --
>> Ekrem Önsoy
>>
>> "Jay" <nospan@.nospam.org> wrote in message
>> news:emoz%23e1DIHA.1164@.TK2MSFTNGP02.phx.gbl...
>> SQL Server 2000
>> I have a large .ldf file that I want to shrink, however, DBCC
>> SHRINKFILE will only shrink it so much. The file went from 74 MB to 50
>> MB, but won't go smaller. I have been routinely shrinking low use DB
>> log files to 20 MB, so I know that's not it.
>> Original: DB_Log 74 MB
>> Ran:
>> use DB
>> exec sp_helpfile -- to get logical name
>> BACKUP LOG DB WITH TRUNCATE_ONLY
>> DBCC SHRINKFILE (DB_Log, 20, TRUNCATEONLY)
>> I get the message: "Cannot shrink log file 2 (DB_Log) because total
>> number of logical log files cannot be fewer than 2."
>> The output of 'sp_helpfile' is:
>> DB_Data 1 e:\data\DB.mdf PRIMARY 149504 KB Unlimited
>> 5% data only
>> DB_Log 2 e:\data\DB_Log.ldf NULL 51080 KB
>> Unlimited 5% log only
>> The output of: DBCC sqlperf(logspace)
>> DB 49.875 0.40922618 0
>> Says there are little, to no transactions in it.
>> Now the Log file is 50 MB and won't shrink any further.
>>
>> Whats up with that?
>>
>>
>|||A backup & restore will put it back exactly like it was when the backup was
taken. You can try detaching the db and then attaching only the mdf using
sp_attach_single_file_db. Just make sure you have good backups.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Jay" <spam@.nospam.org> wrote in message
news:OyfMll$DIHA.3548@.TK2MSFTNGP06.phx.gbl...
> Thanks guys.
> You're right, 30 MB is no big deal. Still, if I could have whacked the
> excess, I would have prefered to.
> The issue is actually one of my own creation. I have a report that tells
> me when a log file is over 25% of the data file and also over 20 MB. So, a
> 50 MB log on a 140 MB DB fires the alarm ( I'm also looking for file
> growth/shrinkage and tracking overall drive usage across the farm). I just
> increased the data file to 201 MB and will deal with it later.
> I suppose a dump and reload is my only option ... hmm, I think I'll try a
> backup/restore from EM, see what that does.
> Thanks,
> Jay
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:BF611763-4529-40F1-81EB-22476C01611F@.microsoft.com...
>> As other has noted, you seem to be down to only two VLFs, which is the
>> minimum number for a log file. SQL Server need to be able to "circle
>> through" the VLFs, which is difficult to do with only one VLF. I believe
>> that you get a minimum of 4 VLFs when you create a log file (although
>> haven't seen the exact algorithm), so probably the log file was created
>> with a larger size, perhaps 100MB.
>> I'm with Andrew on this, leave them at 50MB.
>> You *might* be able to get the size down by adding another file, then
>> shrinking the 50MB file using the EMPTYFILE option, verifying that the
>> active log is on the new file and then ALTER DATABASE REMOVE FILE. The
>> trick here is that you can't remove the file until LOGINFO show you that
>> non of the VLFs are used.
>> I say "might", because I'm not sure whether SQL Server has the concept of
>> "a primary" log file such as it has for data files. I don't think so, at
>> least I don't see any architectural reasons.
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Jay" <nospan@.nospam.org> wrote in message
>> news:uO0NgV2DIHA.5328@.TK2MSFTNGP05.phx.gbl...
>> Well, that info allowed me to shrink the 2.5 GB logfile. However, I have
>> two left that that won't shrink below 50 MB (both DB's are about 150 MB
>> and they don't get used much), which I would like to get to 20 MB.
>> The message: "Cannot shrink log file 2 (DB_Log) because total number of
>> logical log files cannot be fewer than 2."
>> Seems to be refering to the VLF's as both DB's only get two rows
>> returned from DBCC LOGINFO
>> 2 26,148,864 8192 189239 0 128 0
>> 2 26,148,864 26157056 189240 2 64 0
>> and
>> 2 26,148,864 8192 189110 2 128 0
>> 2 26,148,864 26157056 189109 0 128 0
>> I'm guessing that this is because they were created with a 50 MB log
>> file and (most important) that I would have to do a "dump and reload" to
>> get the physical file smaller.
>>
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:79B1E902-AD6B-4DD7-BB4D-9896B31613D8@.microsoft.com...
>> Did you run DBCC OPENTRAN to check out for possible open\uncommited
>> transactions?
>> If there are active virtual logs in it, it's not gonna shrink. Besides,
>> you could use DBCC LOGINFO('myDatabase') as Tibor mentioned in his
>> great article. I suggest you to check it out from the following link:
>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>>
>> --
>> Ekrem Önsoy
>>
>> "Jay" <nospan@.nospam.org> wrote in message
>> news:emoz%23e1DIHA.1164@.TK2MSFTNGP02.phx.gbl...
>> SQL Server 2000
>> I have a large .ldf file that I want to shrink, however, DBCC
>> SHRINKFILE will only shrink it so much. The file went from 74 MB to 50
>> MB, but won't go smaller. I have been routinely shrinking low use DB
>> log files to 20 MB, so I know that's not it.
>> Original: DB_Log 74 MB
>> Ran:
>> use DB
>> exec sp_helpfile -- to get logical name
>> BACKUP LOG DB WITH TRUNCATE_ONLY
>> DBCC SHRINKFILE (DB_Log, 20, TRUNCATEONLY)
>> I get the message: "Cannot shrink log file 2 (DB_Log) because total
>> number of logical log files cannot be fewer than 2."
>> The output of 'sp_helpfile' is:
>> DB_Data 1 e:\data\DB.mdf PRIMARY 149504 KB
>> Unlimited 5% data only
>> DB_Log 2 e:\data\DB_Log.ldf NULL 51080 KB
>> Unlimited 5% log only
>> The output of: DBCC sqlperf(logspace)
>> DB 49.875 0.40922618 0
>> Says there are little, to no transactions in it.
>> Now the Log file is 50 MB and won't shrink any further.
>>
>> Whats up with that?
>>
>>
>|||> The issue is actually one of my own creation. I have a report that tells me when a log file is
> over 25% of the data file and also over 20 MB.
Sorry if I state the obvious, but how about changing your report to warn only of > 50 or 100MB or
something like that? :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <spam@.nospam.org> wrote in message news:OyfMll$DIHA.3548@.TK2MSFTNGP06.phx.gbl...
> Thanks guys.
> You're right, 30 MB is no big deal. Still, if I could have whacked the excess, I would have
> prefered to.
> The issue is actually one of my own creation. I have a report that tells me when a log file is
> over 25% of the data file and also over 20 MB. So, a 50 MB log on a 140 MB DB fires the alarm (
> I'm also looking for file growth/shrinkage and tracking overall drive usage across the farm). I
> just increased the data file to 201 MB and will deal with it later.
> I suppose a dump and reload is my only option ... hmm, I think I'll try a backup/restore from EM,
> see what that does.
> Thanks,
> Jay
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:BF611763-4529-40F1-81EB-22476C01611F@.microsoft.com...
>> As other has noted, you seem to be down to only two VLFs, which is the minimum number for a log
>> file. SQL Server need to be able to "circle through" the VLFs, which is difficult to do with only
>> one VLF. I believe that you get a minimum of 4 VLFs when you create a log file (although haven't
>> seen the exact algorithm), so probably the log file was created with a larger size, perhaps
>> 100MB.
>> I'm with Andrew on this, leave them at 50MB.
>> You *might* be able to get the size down by adding another file, then shrinking the 50MB file
>> using the EMPTYFILE option, verifying that the active log is on the new file and then ALTER
>> DATABASE REMOVE FILE. The trick here is that you can't remove the file until LOGINFO show you
>> that non of the VLFs are used.
>> I say "might", because I'm not sure whether SQL Server has the concept of "a primary" log file
>> such as it has for data files. I don't think so, at least I don't see any architectural reasons.
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Jay" <nospan@.nospam.org> wrote in message news:uO0NgV2DIHA.5328@.TK2MSFTNGP05.phx.gbl...
>> Well, that info allowed me to shrink the 2.5 GB logfile. However, I have two left that that
>> won't shrink below 50 MB (both DB's are about 150 MB and they don't get used much), which I
>> would like to get to 20 MB.
>> The message: "Cannot shrink log file 2 (DB_Log) because total number of logical log files cannot
>> be fewer than 2."
>> Seems to be refering to the VLF's as both DB's only get two rows returned from DBCC LOGINFO
>> 2 26,148,864 8192 189239 0 128 0
>> 2 26,148,864 26157056 189240 2 64 0
>> and
>> 2 26,148,864 8192 189110 2 128 0
>> 2 26,148,864 26157056 189109 0 128 0
>> I'm guessing that this is because they were created with a 50 MB log file and (most important)
>> that I would have to do a "dump and reload" to get the physical file smaller.
>>
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:79B1E902-AD6B-4DD7-BB4D-9896B31613D8@.microsoft.com...
>> Did you run DBCC OPENTRAN to check out for possible open\uncommited transactions?
>> If there are active virtual logs in it, it's not gonna shrink. Besides, you could use DBCC
>> LOGINFO('myDatabase') as Tibor mentioned in his great article. I suggest you to check it out
>> from the following link:
>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>>
>> --
>> Ekrem Önsoy
>>
>> "Jay" <nospan@.nospam.org> wrote in message news:emoz%23e1DIHA.1164@.TK2MSFTNGP02.phx.gbl...
>> SQL Server 2000
>> I have a large .ldf file that I want to shrink, however, DBCC SHRINKFILE will only shrink it
>> so much. The file went from 74 MB to 50 MB, but won't go smaller. I have been routinely
>> shrinking low use DB log files to 20 MB, so I know that's not it.
>> Original: DB_Log 74 MB
>> Ran:
>> use DB
>> exec sp_helpfile -- to get logical name
>> BACKUP LOG DB WITH TRUNCATE_ONLY
>> DBCC SHRINKFILE (DB_Log, 20, TRUNCATEONLY)
>> I get the message: "Cannot shrink log file 2 (DB_Log) because total number of logical log
>> files cannot be fewer than 2."
>> The output of 'sp_helpfile' is:
>> DB_Data 1 e:\data\DB.mdf PRIMARY 149504 KB Unlimited 5% data only
>> DB_Log 2 e:\data\DB_Log.ldf NULL 51080 KB Unlimited 5% log only
>> The output of: DBCC sqlperf(logspace)
>> DB 49.875 0.40922618 0
>> Says there are little, to no transactions in it.
>> Now the Log file is 50 MB and won't shrink any further.
>>
>> Whats up with that?
>>
>>
>|||It's a reasonable suggestion, however, I'm still absorbing what is
reasonable and with my backup program running once a minute and backing up
the logs is they exceed 75% (or n minutes have passed) I think the logs
should be smaller.
I'm in a situation where I need to both understand what is happening and
maintain real world constraints. By assuming a smaller number, I get to see
things. For example, if I set a transaction log to 20 MB and it grows anyway
(but the database is still only 80 MB), I've learned something about the
nature of that DB and can administrate it better.
Remember that the main force driving this are things like that 11 GB log
file (which, thankfully, was the extreme).
Jay
PS. FWIW, the thresholds are command line args with defaults of 20 MB & 25%,
I'm just calling it using the defaults.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:E9B771CE-8D75-4624-84A9-7AF75E73F24B@.microsoft.com...
>> The issue is actually one of my own creation. I have a report that tells
>> me when a log file is over 25% of the data file and also over 20 MB.
> Sorry if I state the obvious, but how about changing your report to warn
> only of > 50 or 100MB or something like that? :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jay" <spam@.nospam.org> wrote in message
> news:OyfMll$DIHA.3548@.TK2MSFTNGP06.phx.gbl...
>> Thanks guys.
>> You're right, 30 MB is no big deal. Still, if I could have whacked the
>> excess, I would have prefered to.
>> The issue is actually one of my own creation. I have a report that tells
>> me when a log file is over 25% of the data file and also over 20 MB. So,
>> a 50 MB log on a 140 MB DB fires the alarm ( I'm also looking for file
>> growth/shrinkage and tracking overall drive usage across the farm). I
>> just increased the data file to 201 MB and will deal with it later.
>> I suppose a dump and reload is my only option ... hmm, I think I'll try a
>> backup/restore from EM, see what that does.
>> Thanks,
>> Jay
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:BF611763-4529-40F1-81EB-22476C01611F@.microsoft.com...
>> As other has noted, you seem to be down to only two VLFs, which is the
>> minimum number for a log file. SQL Server need to be able to "circle
>> through" the VLFs, which is difficult to do with only one VLF. I believe
>> that you get a minimum of 4 VLFs when you create a log file (although
>> haven't seen the exact algorithm), so probably the log file was created
>> with a larger size, perhaps 100MB.
>> I'm with Andrew on this, leave them at 50MB.
>> You *might* be able to get the size down by adding another file, then
>> shrinking the 50MB file using the EMPTYFILE option, verifying that the
>> active log is on the new file and then ALTER DATABASE REMOVE FILE. The
>> trick here is that you can't remove the file until LOGINFO show you that
>> non of the VLFs are used.
>> I say "might", because I'm not sure whether SQL Server has the concept
>> of "a primary" log file such as it has for data files. I don't think so,
>> at least I don't see any architectural reasons.
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Jay" <nospan@.nospam.org> wrote in message
>> news:uO0NgV2DIHA.5328@.TK2MSFTNGP05.phx.gbl...
>> Well, that info allowed me to shrink the 2.5 GB logfile. However, I
>> have two left that that won't shrink below 50 MB (both DB's are about
>> 150 MB and they don't get used much), which I would like to get to 20
>> MB.
>> The message: "Cannot shrink log file 2 (DB_Log) because total number of
>> logical log files cannot be fewer than 2."
>> Seems to be refering to the VLF's as both DB's only get two rows
>> returned from DBCC LOGINFO
>> 2 26,148,864 8192 189239 0 128 0
>> 2 26,148,864 26157056 189240 2 64 0
>> and
>> 2 26,148,864 8192 189110 2 128 0
>> 2 26,148,864 26157056 189109 0 128 0
>> I'm guessing that this is because they were created with a 50 MB log
>> file and (most important) that I would have to do a "dump and reload"
>> to get the physical file smaller.
>>
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:79B1E902-AD6B-4DD7-BB4D-9896B31613D8@.microsoft.com...
>> Did you run DBCC OPENTRAN to check out for possible open\uncommited
>> transactions?
>> If there are active virtual logs in it, it's not gonna shrink.
>> Besides, you could use DBCC LOGINFO('myDatabase') as Tibor mentioned
>> in his great article. I suggest you to check it out from the following
>> link:
>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>>
>> --
>> Ekrem Önsoy
>>
>> "Jay" <nospan@.nospam.org> wrote in message
>> news:emoz%23e1DIHA.1164@.TK2MSFTNGP02.phx.gbl...
>> SQL Server 2000
>> I have a large .ldf file that I want to shrink, however, DBCC
>> SHRINKFILE will only shrink it so much. The file went from 74 MB to
>> 50 MB, but won't go smaller. I have been routinely shrinking low use
>> DB log files to 20 MB, so I know that's not it.
>> Original: DB_Log 74 MB
>> Ran:
>> use DB
>> exec sp_helpfile -- to get logical name
>> BACKUP LOG DB WITH TRUNCATE_ONLY
>> DBCC SHRINKFILE (DB_Log, 20, TRUNCATEONLY)
>> I get the message: "Cannot shrink log file 2 (DB_Log) because total
>> number of logical log files cannot be fewer than 2."
>> The output of 'sp_helpfile' is:
>> DB_Data 1 e:\data\DB.mdf PRIMARY 149504 KB
>> Unlimited 5% data only
>> DB_Log 2 e:\data\DB_Log.ldf NULL 51080 KB
>> Unlimited 5% log only
>> The output of: DBCC sqlperf(logspace)
>> DB 49.875 0.40922618 0
>> Says there are little, to no transactions in it.
>> Now the Log file is 50 MB and won't shrink any further.
>>
>> Whats up with that?
>>
>>
>>
>
I have a large .ldf file that I want to shrink, however, DBCC SHRINKFILE
will only shrink it so much. The file went from 74 MB to 50 MB, but won't go
smaller. I have been routinely shrinking low use DB log files to 20 MB, so I
know that's not it.
Original: DB_Log 74 MB
Ran:
use DB
exec sp_helpfile -- to get logical name
BACKUP LOG DB WITH TRUNCATE_ONLY
DBCC SHRINKFILE (DB_Log, 20, TRUNCATEONLY)
I get the message: "Cannot shrink log file 2 (DB_Log) because total number
of logical log files cannot be fewer than 2."
The output of 'sp_helpfile' is:
DB_Data 1 e:\data\DB.mdf PRIMARY 149504 KB Unlimited 5%
data only
DB_Log 2 e:\data\DB_Log.ldf NULL 51080 KB Unlimited
5% log only
The output of: DBCC sqlperf(logspace)
DB 49.875 0.40922618 0
Says there are little, to no transactions in it.
Now the Log file is 50 MB and won't shrink any further.
Whats up with that?Did you run DBCC OPENTRAN to check out for possible open\uncommited
transactions?
If there are active virtual logs in it, it's not gonna shrink. Besides, you
could use DBCC LOGINFO('myDatabase') as Tibor mentioned in his great
article. I suggest you to check it out from the following link:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Ekrem Önsoy
"Jay" <nospan@.nospam.org> wrote in message
news:emoz%23e1DIHA.1164@.TK2MSFTNGP02.phx.gbl...
> SQL Server 2000
> I have a large .ldf file that I want to shrink, however, DBCC SHRINKFILE
> will only shrink it so much. The file went from 74 MB to 50 MB, but won't
> go smaller. I have been routinely shrinking low use DB log files to 20 MB,
> so I know that's not it.
> Original: DB_Log 74 MB
> Ran:
> use DB
> exec sp_helpfile -- to get logical name
> BACKUP LOG DB WITH TRUNCATE_ONLY
> DBCC SHRINKFILE (DB_Log, 20, TRUNCATEONLY)
> I get the message: "Cannot shrink log file 2 (DB_Log) because total number
> of logical log files cannot be fewer than 2."
> The output of 'sp_helpfile' is:
> DB_Data 1 e:\data\DB.mdf PRIMARY 149504 KB Unlimited 5%
> data only
> DB_Log 2 e:\data\DB_Log.ldf NULL 51080 KB Unlimited
> 5% log only
> The output of: DBCC sqlperf(logspace)
> DB 49.875 0.40922618 0
> Says there are little, to no transactions in it.
> Now the Log file is 50 MB and won't shrink any further.
>
> Whats up with that?
>|||Well, that info allowed me to shrink the 2.5 GB logfile. However, I have two
left that that won't shrink below 50 MB (both DB's are about 150 MB and they
don't get used much), which I would like to get to 20 MB.
The message: "Cannot shrink log file 2 (DB_Log) because total number of
logical log files cannot be fewer than 2."
Seems to be refering to the VLF's as both DB's only get two rows returned
from DBCC LOGINFO
2 26,148,864 8192 189239 0 128 0
2 26,148,864 26157056 189240 2 64 0
and
2 26,148,864 8192 189110 2 128 0
2 26,148,864 26157056 189109 0 128 0
I'm guessing that this is because they were created with a 50 MB log file
and (most important) that I would have to do a "dump and reload" to get the
physical file smaller.
"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:79B1E902-AD6B-4DD7-BB4D-9896B31613D8@.microsoft.com...
> Did you run DBCC OPENTRAN to check out for possible open\uncommited
> transactions?
> If there are active virtual logs in it, it's not gonna shrink. Besides,
> you could use DBCC LOGINFO('myDatabase') as Tibor mentioned in his great
> article. I suggest you to check it out from the following link:
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>
> --
> Ekrem Önsoy
>
> "Jay" <nospan@.nospam.org> wrote in message
> news:emoz%23e1DIHA.1164@.TK2MSFTNGP02.phx.gbl...
>> SQL Server 2000
>> I have a large .ldf file that I want to shrink, however, DBCC SHRINKFILE
>> will only shrink it so much. The file went from 74 MB to 50 MB, but won't
>> go smaller. I have been routinely shrinking low use DB log files to 20
>> MB, so I know that's not it.
>> Original: DB_Log 74 MB
>> Ran:
>> use DB
>> exec sp_helpfile -- to get logical name
>> BACKUP LOG DB WITH TRUNCATE_ONLY
>> DBCC SHRINKFILE (DB_Log, 20, TRUNCATEONLY)
>> I get the message: "Cannot shrink log file 2 (DB_Log) because total
>> number of logical log files cannot be fewer than 2."
>> The output of 'sp_helpfile' is:
>> DB_Data 1 e:\data\DB.mdf PRIMARY 149504 KB Unlimited
>> 5% data only
>> DB_Log 2 e:\data\DB_Log.ldf NULL 51080 KB
>> Unlimited 5% log only
>> The output of: DBCC sqlperf(logspace)
>> DB 49.875 0.40922618 0
>> Says there are little, to no transactions in it.
>> Now the Log file is 50 MB and won't shrink any further.
>>
>> Whats up with that?
>|||If they were originally created at 50MB they may not be able to shrink any
further. If you run DBCC LOGINFO do you only have 1 VLF? If so you can't
shrink anymore. Is 30MB really an issue?
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Jay" <nospan@.nospam.org> wrote in message
news:uO0NgV2DIHA.5328@.TK2MSFTNGP05.phx.gbl...
> Well, that info allowed me to shrink the 2.5 GB logfile. However, I have
> two left that that won't shrink below 50 MB (both DB's are about 150 MB
> and they don't get used much), which I would like to get to 20 MB.
> The message: "Cannot shrink log file 2 (DB_Log) because total number of
> logical log files cannot be fewer than 2."
> Seems to be refering to the VLF's as both DB's only get two rows returned
> from DBCC LOGINFO
> 2 26,148,864 8192 189239 0 128 0
> 2 26,148,864 26157056 189240 2 64 0
> and
> 2 26,148,864 8192 189110 2 128 0
> 2 26,148,864 26157056 189109 0 128 0
> I'm guessing that this is because they were created with a 50 MB log file
> and (most important) that I would have to do a "dump and reload" to get
> the physical file smaller.
>
> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
> news:79B1E902-AD6B-4DD7-BB4D-9896B31613D8@.microsoft.com...
>> Did you run DBCC OPENTRAN to check out for possible open\uncommited
>> transactions?
>> If there are active virtual logs in it, it's not gonna shrink. Besides,
>> you could use DBCC LOGINFO('myDatabase') as Tibor mentioned in his great
>> article. I suggest you to check it out from the following link:
>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>>
>> --
>> Ekrem Önsoy
>>
>> "Jay" <nospan@.nospam.org> wrote in message
>> news:emoz%23e1DIHA.1164@.TK2MSFTNGP02.phx.gbl...
>> SQL Server 2000
>> I have a large .ldf file that I want to shrink, however, DBCC SHRINKFILE
>> will only shrink it so much. The file went from 74 MB to 50 MB, but
>> won't go smaller. I have been routinely shrinking low use DB log files
>> to 20 MB, so I know that's not it.
>> Original: DB_Log 74 MB
>> Ran:
>> use DB
>> exec sp_helpfile -- to get logical name
>> BACKUP LOG DB WITH TRUNCATE_ONLY
>> DBCC SHRINKFILE (DB_Log, 20, TRUNCATEONLY)
>> I get the message: "Cannot shrink log file 2 (DB_Log) because total
>> number of logical log files cannot be fewer than 2."
>> The output of 'sp_helpfile' is:
>> DB_Data 1 e:\data\DB.mdf PRIMARY 149504 KB Unlimited
>> 5% data only
>> DB_Log 2 e:\data\DB_Log.ldf NULL 51080 KB
>> Unlimited 5% log only
>> The output of: DBCC sqlperf(logspace)
>> DB 49.875 0.40922618 0
>> Says there are little, to no transactions in it.
>> Now the Log file is 50 MB and won't shrink any further.
>>
>> Whats up with that?
>>
>|||As other has noted, you seem to be down to only two VLFs, which is the minimum number for a log
file. SQL Server need to be able to "circle through" the VLFs, which is difficult to do with only
one VLF. I believe that you get a minimum of 4 VLFs when you create a log file (although haven't
seen the exact algorithm), so probably the log file was created with a larger size, perhaps 100MB.
I'm with Andrew on this, leave them at 50MB.
You *might* be able to get the size down by adding another file, then shrinking the 50MB file using
the EMPTYFILE option, verifying that the active log is on the new file and then ALTER DATABASE
REMOVE FILE. The trick here is that you can't remove the file until LOGINFO show you that non of the
VLFs are used.
I say "might", because I'm not sure whether SQL Server has the concept of "a primary" log file such
as it has for data files. I don't think so, at least I don't see any architectural reasons.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <nospan@.nospam.org> wrote in message news:uO0NgV2DIHA.5328@.TK2MSFTNGP05.phx.gbl...
> Well, that info allowed me to shrink the 2.5 GB logfile. However, I have two left that that won't
> shrink below 50 MB (both DB's are about 150 MB and they don't get used much), which I would like
> to get to 20 MB.
> The message: "Cannot shrink log file 2 (DB_Log) because total number of logical log files cannot
> be fewer than 2."
> Seems to be refering to the VLF's as both DB's only get two rows returned from DBCC LOGINFO
> 2 26,148,864 8192 189239 0 128 0
> 2 26,148,864 26157056 189240 2 64 0
> and
> 2 26,148,864 8192 189110 2 128 0
> 2 26,148,864 26157056 189109 0 128 0
> I'm guessing that this is because they were created with a 50 MB log file and (most important)
> that I would have to do a "dump and reload" to get the physical file smaller.
>
> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
> news:79B1E902-AD6B-4DD7-BB4D-9896B31613D8@.microsoft.com...
>> Did you run DBCC OPENTRAN to check out for possible open\uncommited transactions?
>> If there are active virtual logs in it, it's not gonna shrink. Besides, you could use DBCC
>> LOGINFO('myDatabase') as Tibor mentioned in his great article. I suggest you to check it out from
>> the following link:
>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>>
>> --
>> Ekrem Önsoy
>>
>> "Jay" <nospan@.nospam.org> wrote in message news:emoz%23e1DIHA.1164@.TK2MSFTNGP02.phx.gbl...
>> SQL Server 2000
>> I have a large .ldf file that I want to shrink, however, DBCC SHRINKFILE will only shrink it so
>> much. The file went from 74 MB to 50 MB, but won't go smaller. I have been routinely shrinking
>> low use DB log files to 20 MB, so I know that's not it.
>> Original: DB_Log 74 MB
>> Ran:
>> use DB
>> exec sp_helpfile -- to get logical name
>> BACKUP LOG DB WITH TRUNCATE_ONLY
>> DBCC SHRINKFILE (DB_Log, 20, TRUNCATEONLY)
>> I get the message: "Cannot shrink log file 2 (DB_Log) because total number of logical log files
>> cannot be fewer than 2."
>> The output of 'sp_helpfile' is:
>> DB_Data 1 e:\data\DB.mdf PRIMARY 149504 KB Unlimited 5% data only
>> DB_Log 2 e:\data\DB_Log.ldf NULL 51080 KB Unlimited 5% log only
>> The output of: DBCC sqlperf(logspace)
>> DB 49.875 0.40922618 0
>> Says there are little, to no transactions in it.
>> Now the Log file is 50 MB and won't shrink any further.
>>
>> Whats up with that?
>>
>|||There is a "primary log file" thing and it can be emptied but can not be
removed.
For information only.
--
Ekrem Önsoy
MCBDA, MCTS: SQL Server 2005, MCITP:DBA, MCSD.Net, MCSE, MCT
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:BF611763-4529-40F1-81EB-22476C01611F@.microsoft.com...
> As other has noted, you seem to be down to only two VLFs, which is the
> minimum number for a log file. SQL Server need to be able to "circle
> through" the VLFs, which is difficult to do with only one VLF. I believe
> that you get a minimum of 4 VLFs when you create a log file (although
> haven't seen the exact algorithm), so probably the log file was created
> with a larger size, perhaps 100MB.
> I'm with Andrew on this, leave them at 50MB.
> You *might* be able to get the size down by adding another file, then
> shrinking the 50MB file using the EMPTYFILE option, verifying that the
> active log is on the new file and then ALTER DATABASE REMOVE FILE. The
> trick here is that you can't remove the file until LOGINFO show you that
> non of the VLFs are used.
> I say "might", because I'm not sure whether SQL Server has the concept of
> "a primary" log file such as it has for data files. I don't think so, at
> least I don't see any architectural reasons.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jay" <nospan@.nospam.org> wrote in message
> news:uO0NgV2DIHA.5328@.TK2MSFTNGP05.phx.gbl...
>> Well, that info allowed me to shrink the 2.5 GB logfile. However, I have
>> two left that that won't shrink below 50 MB (both DB's are about 150 MB
>> and they don't get used much), which I would like to get to 20 MB.
>> The message: "Cannot shrink log file 2 (DB_Log) because total number of
>> logical log files cannot be fewer than 2."
>> Seems to be refering to the VLF's as both DB's only get two rows returned
>> from DBCC LOGINFO
>> 2 26,148,864 8192 189239 0 128 0
>> 2 26,148,864 26157056 189240 2 64 0
>> and
>> 2 26,148,864 8192 189110 2 128 0
>> 2 26,148,864 26157056 189109 0 128 0
>> I'm guessing that this is because they were created with a 50 MB log file
>> and (most important) that I would have to do a "dump and reload" to get
>> the physical file smaller.
>>
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:79B1E902-AD6B-4DD7-BB4D-9896B31613D8@.microsoft.com...
>> Did you run DBCC OPENTRAN to check out for possible open\uncommited
>> transactions?
>> If there are active virtual logs in it, it's not gonna shrink. Besides,
>> you could use DBCC LOGINFO('myDatabase') as Tibor mentioned in his great
>> article. I suggest you to check it out from the following link:
>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>>
>> --
>> Ekrem Önsoy
>>
>> "Jay" <nospan@.nospam.org> wrote in message
>> news:emoz%23e1DIHA.1164@.TK2MSFTNGP02.phx.gbl...
>> SQL Server 2000
>> I have a large .ldf file that I want to shrink, however, DBCC
>> SHRINKFILE will only shrink it so much. The file went from 74 MB to 50
>> MB, but won't go smaller. I have been routinely shrinking low use DB
>> log files to 20 MB, so I know that's not it.
>> Original: DB_Log 74 MB
>> Ran:
>> use DB
>> exec sp_helpfile -- to get logical name
>> BACKUP LOG DB WITH TRUNCATE_ONLY
>> DBCC SHRINKFILE (DB_Log, 20, TRUNCATEONLY)
>> I get the message: "Cannot shrink log file 2 (DB_Log) because total
>> number of logical log files cannot be fewer than 2."
>> The output of 'sp_helpfile' is:
>> DB_Data 1 e:\data\DB.mdf PRIMARY 149504 KB Unlimited
>> 5% data only
>> DB_Log 2 e:\data\DB_Log.ldf NULL 51080 KB
>> Unlimited 5% log only
>> The output of: DBCC sqlperf(logspace)
>> DB 49.875 0.40922618 0
>> Says there are little, to no transactions in it.
>> Now the Log file is 50 MB and won't shrink any further.
>>
>> Whats up with that?
>>
>>
>|||> There is a "primary log file" thing and it can be emptied but can not be removed.
Yes, I see that now. The error message is pretty clear on that:
"Msg 5020, Level 16, State 1, Line 1
The primary data or log file cannot be removed from a database."
Thanks Ekrem.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:86D5E5D2-A204-4554-A271-F6F73AE4D296@.microsoft.com...
> There is a "primary log file" thing and it can be emptied but can not be removed.
> For information only.
> --
> Ekrem Önsoy
> MCBDA, MCTS: SQL Server 2005, MCITP:DBA, MCSD.Net, MCSE, MCT
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:BF611763-4529-40F1-81EB-22476C01611F@.microsoft.com...
>> As other has noted, you seem to be down to only two VLFs, which is the minimum number for a log
>> file. SQL Server need to be able to "circle through" the VLFs, which is difficult to do with only
>> one VLF. I believe that you get a minimum of 4 VLFs when you create a log file (although haven't
>> seen the exact algorithm), so probably the log file was created with a larger size, perhaps
>> 100MB.
>> I'm with Andrew on this, leave them at 50MB.
>> You *might* be able to get the size down by adding another file, then shrinking the 50MB file
>> using the EMPTYFILE option, verifying that the active log is on the new file and then ALTER
>> DATABASE REMOVE FILE. The trick here is that you can't remove the file until LOGINFO show you
>> that non of the VLFs are used.
>> I say "might", because I'm not sure whether SQL Server has the concept of "a primary" log file
>> such as it has for data files. I don't think so, at least I don't see any architectural reasons.
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Jay" <nospan@.nospam.org> wrote in message news:uO0NgV2DIHA.5328@.TK2MSFTNGP05.phx.gbl...
>> Well, that info allowed me to shrink the 2.5 GB logfile. However, I have two left that that
>> won't shrink below 50 MB (both DB's are about 150 MB and they don't get used much), which I
>> would like to get to 20 MB.
>> The message: "Cannot shrink log file 2 (DB_Log) because total number of logical log files cannot
>> be fewer than 2."
>> Seems to be refering to the VLF's as both DB's only get two rows returned from DBCC LOGINFO
>> 2 26,148,864 8192 189239 0 128 0
>> 2 26,148,864 26157056 189240 2 64 0
>> and
>> 2 26,148,864 8192 189110 2 128 0
>> 2 26,148,864 26157056 189109 0 128 0
>> I'm guessing that this is because they were created with a 50 MB log file and (most important)
>> that I would have to do a "dump and reload" to get the physical file smaller.
>>
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:79B1E902-AD6B-4DD7-BB4D-9896B31613D8@.microsoft.com...
>> Did you run DBCC OPENTRAN to check out for possible open\uncommited transactions?
>> If there are active virtual logs in it, it's not gonna shrink. Besides, you could use DBCC
>> LOGINFO('myDatabase') as Tibor mentioned in his great article. I suggest you to check it out
>> from the following link:
>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>>
>> --
>> Ekrem Önsoy
>>
>> "Jay" <nospan@.nospam.org> wrote in message news:emoz%23e1DIHA.1164@.TK2MSFTNGP02.phx.gbl...
>> SQL Server 2000
>> I have a large .ldf file that I want to shrink, however, DBCC SHRINKFILE will only shrink it
>> so much. The file went from 74 MB to 50 MB, but won't go smaller. I have been routinely
>> shrinking low use DB log files to 20 MB, so I know that's not it.
>> Original: DB_Log 74 MB
>> Ran:
>> use DB
>> exec sp_helpfile -- to get logical name
>> BACKUP LOG DB WITH TRUNCATE_ONLY
>> DBCC SHRINKFILE (DB_Log, 20, TRUNCATEONLY)
>> I get the message: "Cannot shrink log file 2 (DB_Log) because total number of logical log
>> files cannot be fewer than 2."
>> The output of 'sp_helpfile' is:
>> DB_Data 1 e:\data\DB.mdf PRIMARY 149504 KB Unlimited 5% data only
>> DB_Log 2 e:\data\DB_Log.ldf NULL 51080 KB Unlimited 5% log only
>> The output of: DBCC sqlperf(logspace)
>> DB 49.875 0.40922618 0
>> Says there are little, to no transactions in it.
>> Now the Log file is 50 MB and won't shrink any further.
>>
>> Whats up with that?
>>
>>
>|||Thanks guys.
You're right, 30 MB is no big deal. Still, if I could have whacked the
excess, I would have prefered to.
The issue is actually one of my own creation. I have a report that tells me
when a log file is over 25% of the data file and also over 20 MB. So, a 50
MB log on a 140 MB DB fires the alarm ( I'm also looking for file
growth/shrinkage and tracking overall drive usage across the farm). I just
increased the data file to 201 MB and will deal with it later.
I suppose a dump and reload is my only option ... hmm, I think I'll try a
backup/restore from EM, see what that does.
Thanks,
Jay
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:BF611763-4529-40F1-81EB-22476C01611F@.microsoft.com...
> As other has noted, you seem to be down to only two VLFs, which is the
> minimum number for a log file. SQL Server need to be able to "circle
> through" the VLFs, which is difficult to do with only one VLF. I believe
> that you get a minimum of 4 VLFs when you create a log file (although
> haven't seen the exact algorithm), so probably the log file was created
> with a larger size, perhaps 100MB.
> I'm with Andrew on this, leave them at 50MB.
> You *might* be able to get the size down by adding another file, then
> shrinking the 50MB file using the EMPTYFILE option, verifying that the
> active log is on the new file and then ALTER DATABASE REMOVE FILE. The
> trick here is that you can't remove the file until LOGINFO show you that
> non of the VLFs are used.
> I say "might", because I'm not sure whether SQL Server has the concept of
> "a primary" log file such as it has for data files. I don't think so, at
> least I don't see any architectural reasons.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jay" <nospan@.nospam.org> wrote in message
> news:uO0NgV2DIHA.5328@.TK2MSFTNGP05.phx.gbl...
>> Well, that info allowed me to shrink the 2.5 GB logfile. However, I have
>> two left that that won't shrink below 50 MB (both DB's are about 150 MB
>> and they don't get used much), which I would like to get to 20 MB.
>> The message: "Cannot shrink log file 2 (DB_Log) because total number of
>> logical log files cannot be fewer than 2."
>> Seems to be refering to the VLF's as both DB's only get two rows returned
>> from DBCC LOGINFO
>> 2 26,148,864 8192 189239 0 128 0
>> 2 26,148,864 26157056 189240 2 64 0
>> and
>> 2 26,148,864 8192 189110 2 128 0
>> 2 26,148,864 26157056 189109 0 128 0
>> I'm guessing that this is because they were created with a 50 MB log file
>> and (most important) that I would have to do a "dump and reload" to get
>> the physical file smaller.
>>
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:79B1E902-AD6B-4DD7-BB4D-9896B31613D8@.microsoft.com...
>> Did you run DBCC OPENTRAN to check out for possible open\uncommited
>> transactions?
>> If there are active virtual logs in it, it's not gonna shrink. Besides,
>> you could use DBCC LOGINFO('myDatabase') as Tibor mentioned in his great
>> article. I suggest you to check it out from the following link:
>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>>
>> --
>> Ekrem Önsoy
>>
>> "Jay" <nospan@.nospam.org> wrote in message
>> news:emoz%23e1DIHA.1164@.TK2MSFTNGP02.phx.gbl...
>> SQL Server 2000
>> I have a large .ldf file that I want to shrink, however, DBCC
>> SHRINKFILE will only shrink it so much. The file went from 74 MB to 50
>> MB, but won't go smaller. I have been routinely shrinking low use DB
>> log files to 20 MB, so I know that's not it.
>> Original: DB_Log 74 MB
>> Ran:
>> use DB
>> exec sp_helpfile -- to get logical name
>> BACKUP LOG DB WITH TRUNCATE_ONLY
>> DBCC SHRINKFILE (DB_Log, 20, TRUNCATEONLY)
>> I get the message: "Cannot shrink log file 2 (DB_Log) because total
>> number of logical log files cannot be fewer than 2."
>> The output of 'sp_helpfile' is:
>> DB_Data 1 e:\data\DB.mdf PRIMARY 149504 KB Unlimited
>> 5% data only
>> DB_Log 2 e:\data\DB_Log.ldf NULL 51080 KB
>> Unlimited 5% log only
>> The output of: DBCC sqlperf(logspace)
>> DB 49.875 0.40922618 0
>> Says there are little, to no transactions in it.
>> Now the Log file is 50 MB and won't shrink any further.
>>
>> Whats up with that?
>>
>>
>|||A backup & restore will put it back exactly like it was when the backup was
taken. You can try detaching the db and then attaching only the mdf using
sp_attach_single_file_db. Just make sure you have good backups.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Jay" <spam@.nospam.org> wrote in message
news:OyfMll$DIHA.3548@.TK2MSFTNGP06.phx.gbl...
> Thanks guys.
> You're right, 30 MB is no big deal. Still, if I could have whacked the
> excess, I would have prefered to.
> The issue is actually one of my own creation. I have a report that tells
> me when a log file is over 25% of the data file and also over 20 MB. So, a
> 50 MB log on a 140 MB DB fires the alarm ( I'm also looking for file
> growth/shrinkage and tracking overall drive usage across the farm). I just
> increased the data file to 201 MB and will deal with it later.
> I suppose a dump and reload is my only option ... hmm, I think I'll try a
> backup/restore from EM, see what that does.
> Thanks,
> Jay
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:BF611763-4529-40F1-81EB-22476C01611F@.microsoft.com...
>> As other has noted, you seem to be down to only two VLFs, which is the
>> minimum number for a log file. SQL Server need to be able to "circle
>> through" the VLFs, which is difficult to do with only one VLF. I believe
>> that you get a minimum of 4 VLFs when you create a log file (although
>> haven't seen the exact algorithm), so probably the log file was created
>> with a larger size, perhaps 100MB.
>> I'm with Andrew on this, leave them at 50MB.
>> You *might* be able to get the size down by adding another file, then
>> shrinking the 50MB file using the EMPTYFILE option, verifying that the
>> active log is on the new file and then ALTER DATABASE REMOVE FILE. The
>> trick here is that you can't remove the file until LOGINFO show you that
>> non of the VLFs are used.
>> I say "might", because I'm not sure whether SQL Server has the concept of
>> "a primary" log file such as it has for data files. I don't think so, at
>> least I don't see any architectural reasons.
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Jay" <nospan@.nospam.org> wrote in message
>> news:uO0NgV2DIHA.5328@.TK2MSFTNGP05.phx.gbl...
>> Well, that info allowed me to shrink the 2.5 GB logfile. However, I have
>> two left that that won't shrink below 50 MB (both DB's are about 150 MB
>> and they don't get used much), which I would like to get to 20 MB.
>> The message: "Cannot shrink log file 2 (DB_Log) because total number of
>> logical log files cannot be fewer than 2."
>> Seems to be refering to the VLF's as both DB's only get two rows
>> returned from DBCC LOGINFO
>> 2 26,148,864 8192 189239 0 128 0
>> 2 26,148,864 26157056 189240 2 64 0
>> and
>> 2 26,148,864 8192 189110 2 128 0
>> 2 26,148,864 26157056 189109 0 128 0
>> I'm guessing that this is because they were created with a 50 MB log
>> file and (most important) that I would have to do a "dump and reload" to
>> get the physical file smaller.
>>
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:79B1E902-AD6B-4DD7-BB4D-9896B31613D8@.microsoft.com...
>> Did you run DBCC OPENTRAN to check out for possible open\uncommited
>> transactions?
>> If there are active virtual logs in it, it's not gonna shrink. Besides,
>> you could use DBCC LOGINFO('myDatabase') as Tibor mentioned in his
>> great article. I suggest you to check it out from the following link:
>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>>
>> --
>> Ekrem Önsoy
>>
>> "Jay" <nospan@.nospam.org> wrote in message
>> news:emoz%23e1DIHA.1164@.TK2MSFTNGP02.phx.gbl...
>> SQL Server 2000
>> I have a large .ldf file that I want to shrink, however, DBCC
>> SHRINKFILE will only shrink it so much. The file went from 74 MB to 50
>> MB, but won't go smaller. I have been routinely shrinking low use DB
>> log files to 20 MB, so I know that's not it.
>> Original: DB_Log 74 MB
>> Ran:
>> use DB
>> exec sp_helpfile -- to get logical name
>> BACKUP LOG DB WITH TRUNCATE_ONLY
>> DBCC SHRINKFILE (DB_Log, 20, TRUNCATEONLY)
>> I get the message: "Cannot shrink log file 2 (DB_Log) because total
>> number of logical log files cannot be fewer than 2."
>> The output of 'sp_helpfile' is:
>> DB_Data 1 e:\data\DB.mdf PRIMARY 149504 KB
>> Unlimited 5% data only
>> DB_Log 2 e:\data\DB_Log.ldf NULL 51080 KB
>> Unlimited 5% log only
>> The output of: DBCC sqlperf(logspace)
>> DB 49.875 0.40922618 0
>> Says there are little, to no transactions in it.
>> Now the Log file is 50 MB and won't shrink any further.
>>
>> Whats up with that?
>>
>>
>|||> The issue is actually one of my own creation. I have a report that tells me when a log file is
> over 25% of the data file and also over 20 MB.
Sorry if I state the obvious, but how about changing your report to warn only of > 50 or 100MB or
something like that? :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <spam@.nospam.org> wrote in message news:OyfMll$DIHA.3548@.TK2MSFTNGP06.phx.gbl...
> Thanks guys.
> You're right, 30 MB is no big deal. Still, if I could have whacked the excess, I would have
> prefered to.
> The issue is actually one of my own creation. I have a report that tells me when a log file is
> over 25% of the data file and also over 20 MB. So, a 50 MB log on a 140 MB DB fires the alarm (
> I'm also looking for file growth/shrinkage and tracking overall drive usage across the farm). I
> just increased the data file to 201 MB and will deal with it later.
> I suppose a dump and reload is my only option ... hmm, I think I'll try a backup/restore from EM,
> see what that does.
> Thanks,
> Jay
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:BF611763-4529-40F1-81EB-22476C01611F@.microsoft.com...
>> As other has noted, you seem to be down to only two VLFs, which is the minimum number for a log
>> file. SQL Server need to be able to "circle through" the VLFs, which is difficult to do with only
>> one VLF. I believe that you get a minimum of 4 VLFs when you create a log file (although haven't
>> seen the exact algorithm), so probably the log file was created with a larger size, perhaps
>> 100MB.
>> I'm with Andrew on this, leave them at 50MB.
>> You *might* be able to get the size down by adding another file, then shrinking the 50MB file
>> using the EMPTYFILE option, verifying that the active log is on the new file and then ALTER
>> DATABASE REMOVE FILE. The trick here is that you can't remove the file until LOGINFO show you
>> that non of the VLFs are used.
>> I say "might", because I'm not sure whether SQL Server has the concept of "a primary" log file
>> such as it has for data files. I don't think so, at least I don't see any architectural reasons.
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Jay" <nospan@.nospam.org> wrote in message news:uO0NgV2DIHA.5328@.TK2MSFTNGP05.phx.gbl...
>> Well, that info allowed me to shrink the 2.5 GB logfile. However, I have two left that that
>> won't shrink below 50 MB (both DB's are about 150 MB and they don't get used much), which I
>> would like to get to 20 MB.
>> The message: "Cannot shrink log file 2 (DB_Log) because total number of logical log files cannot
>> be fewer than 2."
>> Seems to be refering to the VLF's as both DB's only get two rows returned from DBCC LOGINFO
>> 2 26,148,864 8192 189239 0 128 0
>> 2 26,148,864 26157056 189240 2 64 0
>> and
>> 2 26,148,864 8192 189110 2 128 0
>> 2 26,148,864 26157056 189109 0 128 0
>> I'm guessing that this is because they were created with a 50 MB log file and (most important)
>> that I would have to do a "dump and reload" to get the physical file smaller.
>>
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:79B1E902-AD6B-4DD7-BB4D-9896B31613D8@.microsoft.com...
>> Did you run DBCC OPENTRAN to check out for possible open\uncommited transactions?
>> If there are active virtual logs in it, it's not gonna shrink. Besides, you could use DBCC
>> LOGINFO('myDatabase') as Tibor mentioned in his great article. I suggest you to check it out
>> from the following link:
>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>>
>> --
>> Ekrem Önsoy
>>
>> "Jay" <nospan@.nospam.org> wrote in message news:emoz%23e1DIHA.1164@.TK2MSFTNGP02.phx.gbl...
>> SQL Server 2000
>> I have a large .ldf file that I want to shrink, however, DBCC SHRINKFILE will only shrink it
>> so much. The file went from 74 MB to 50 MB, but won't go smaller. I have been routinely
>> shrinking low use DB log files to 20 MB, so I know that's not it.
>> Original: DB_Log 74 MB
>> Ran:
>> use DB
>> exec sp_helpfile -- to get logical name
>> BACKUP LOG DB WITH TRUNCATE_ONLY
>> DBCC SHRINKFILE (DB_Log, 20, TRUNCATEONLY)
>> I get the message: "Cannot shrink log file 2 (DB_Log) because total number of logical log
>> files cannot be fewer than 2."
>> The output of 'sp_helpfile' is:
>> DB_Data 1 e:\data\DB.mdf PRIMARY 149504 KB Unlimited 5% data only
>> DB_Log 2 e:\data\DB_Log.ldf NULL 51080 KB Unlimited 5% log only
>> The output of: DBCC sqlperf(logspace)
>> DB 49.875 0.40922618 0
>> Says there are little, to no transactions in it.
>> Now the Log file is 50 MB and won't shrink any further.
>>
>> Whats up with that?
>>
>>
>|||It's a reasonable suggestion, however, I'm still absorbing what is
reasonable and with my backup program running once a minute and backing up
the logs is they exceed 75% (or n minutes have passed) I think the logs
should be smaller.
I'm in a situation where I need to both understand what is happening and
maintain real world constraints. By assuming a smaller number, I get to see
things. For example, if I set a transaction log to 20 MB and it grows anyway
(but the database is still only 80 MB), I've learned something about the
nature of that DB and can administrate it better.
Remember that the main force driving this are things like that 11 GB log
file (which, thankfully, was the extreme).
Jay
PS. FWIW, the thresholds are command line args with defaults of 20 MB & 25%,
I'm just calling it using the defaults.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:E9B771CE-8D75-4624-84A9-7AF75E73F24B@.microsoft.com...
>> The issue is actually one of my own creation. I have a report that tells
>> me when a log file is over 25% of the data file and also over 20 MB.
> Sorry if I state the obvious, but how about changing your report to warn
> only of > 50 or 100MB or something like that? :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jay" <spam@.nospam.org> wrote in message
> news:OyfMll$DIHA.3548@.TK2MSFTNGP06.phx.gbl...
>> Thanks guys.
>> You're right, 30 MB is no big deal. Still, if I could have whacked the
>> excess, I would have prefered to.
>> The issue is actually one of my own creation. I have a report that tells
>> me when a log file is over 25% of the data file and also over 20 MB. So,
>> a 50 MB log on a 140 MB DB fires the alarm ( I'm also looking for file
>> growth/shrinkage and tracking overall drive usage across the farm). I
>> just increased the data file to 201 MB and will deal with it later.
>> I suppose a dump and reload is my only option ... hmm, I think I'll try a
>> backup/restore from EM, see what that does.
>> Thanks,
>> Jay
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:BF611763-4529-40F1-81EB-22476C01611F@.microsoft.com...
>> As other has noted, you seem to be down to only two VLFs, which is the
>> minimum number for a log file. SQL Server need to be able to "circle
>> through" the VLFs, which is difficult to do with only one VLF. I believe
>> that you get a minimum of 4 VLFs when you create a log file (although
>> haven't seen the exact algorithm), so probably the log file was created
>> with a larger size, perhaps 100MB.
>> I'm with Andrew on this, leave them at 50MB.
>> You *might* be able to get the size down by adding another file, then
>> shrinking the 50MB file using the EMPTYFILE option, verifying that the
>> active log is on the new file and then ALTER DATABASE REMOVE FILE. The
>> trick here is that you can't remove the file until LOGINFO show you that
>> non of the VLFs are used.
>> I say "might", because I'm not sure whether SQL Server has the concept
>> of "a primary" log file such as it has for data files. I don't think so,
>> at least I don't see any architectural reasons.
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Jay" <nospan@.nospam.org> wrote in message
>> news:uO0NgV2DIHA.5328@.TK2MSFTNGP05.phx.gbl...
>> Well, that info allowed me to shrink the 2.5 GB logfile. However, I
>> have two left that that won't shrink below 50 MB (both DB's are about
>> 150 MB and they don't get used much), which I would like to get to 20
>> MB.
>> The message: "Cannot shrink log file 2 (DB_Log) because total number of
>> logical log files cannot be fewer than 2."
>> Seems to be refering to the VLF's as both DB's only get two rows
>> returned from DBCC LOGINFO
>> 2 26,148,864 8192 189239 0 128 0
>> 2 26,148,864 26157056 189240 2 64 0
>> and
>> 2 26,148,864 8192 189110 2 128 0
>> 2 26,148,864 26157056 189109 0 128 0
>> I'm guessing that this is because they were created with a 50 MB log
>> file and (most important) that I would have to do a "dump and reload"
>> to get the physical file smaller.
>>
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:79B1E902-AD6B-4DD7-BB4D-9896B31613D8@.microsoft.com...
>> Did you run DBCC OPENTRAN to check out for possible open\uncommited
>> transactions?
>> If there are active virtual logs in it, it's not gonna shrink.
>> Besides, you could use DBCC LOGINFO('myDatabase') as Tibor mentioned
>> in his great article. I suggest you to check it out from the following
>> link:
>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>>
>> --
>> Ekrem Önsoy
>>
>> "Jay" <nospan@.nospam.org> wrote in message
>> news:emoz%23e1DIHA.1164@.TK2MSFTNGP02.phx.gbl...
>> SQL Server 2000
>> I have a large .ldf file that I want to shrink, however, DBCC
>> SHRINKFILE will only shrink it so much. The file went from 74 MB to
>> 50 MB, but won't go smaller. I have been routinely shrinking low use
>> DB log files to 20 MB, so I know that's not it.
>> Original: DB_Log 74 MB
>> Ran:
>> use DB
>> exec sp_helpfile -- to get logical name
>> BACKUP LOG DB WITH TRUNCATE_ONLY
>> DBCC SHRINKFILE (DB_Log, 20, TRUNCATEONLY)
>> I get the message: "Cannot shrink log file 2 (DB_Log) because total
>> number of logical log files cannot be fewer than 2."
>> The output of 'sp_helpfile' is:
>> DB_Data 1 e:\data\DB.mdf PRIMARY 149504 KB
>> Unlimited 5% data only
>> DB_Log 2 e:\data\DB_Log.ldf NULL 51080 KB
>> Unlimited 5% log only
>> The output of: DBCC sqlperf(logspace)
>> DB 49.875 0.40922618 0
>> Says there are little, to no transactions in it.
>> Now the Log file is 50 MB and won't shrink any further.
>>
>> Whats up with that?
>>
>>
>>
>
DBCC Shrinkfile
Dear All,
I have just tried to shrink a file, both in Enterprise
Manager and in Query Analyser.
Within EM it hung.
Within QA it said it shrinked it but when I looked at the
size of the file it was still the same size.
The code I used in QA was
dbcc shrinkfile (NAMEOFFILE, TRUNCATEONLY).
Can anyone gice me pointers ?
Thanks
JBy any chance, are you trying to shrink the tempdb data file? Sometimes,
that fails because there are temporary worktables in there that are still
being used, though QA would report as if the file had been shrunk.
--
Regards
Ray Mond|||No, I am shrinking a non system, non tempdb file.
Thanks
J
>--Original Message--
>By any chance, are you trying to shrink the tempdb data
file? Sometimes,
>that fails because there are temporary worktables in
there that are still
>being used, though QA would report as if the file had
been shrunk.
>--
>Regards
>Ray Mond
>
>.
>|||Hi,
Please look into the below article from MS. This will help you out in
shrinking the TX Log.
http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
Thanks
Hari
MCDBA
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:0b8601c3d9bb$08533b50$a101280a@.phx.gbl...
> Dear All,
> I have just tried to shrink a file, both in Enterprise
> Manager and in Query Analyser.
> Within EM it hung.
> Within QA it said it shrinked it but when I looked at the
> size of the file it was still the same size.
> The code I used in QA was
> dbcc shrinkfile (NAMEOFFILE, TRUNCATEONLY).
> Can anyone gice me pointers ?
> Thanks
> J|||Why don't you try DBCC SHRINKFILE(NAMEOFFILE, somesize) ? Your data might
be residing on the end of the file, which prevents any significant
truncation.
--
Regards
Ray Mond
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:07ae01c3d9c0$9415a2e0$a501280a@.phx.gbl...
> No, I am shrinking a non system, non tempdb file.
> Thanks
> J
>
> >--Original Message--
> >By any chance, are you trying to shrink the tempdb data
> file? Sometimes,
> >that fails because there are temporary worktables in
> there that are still
> >being used, though QA would report as if the file had
> been shrunk.
> >
> >--
> >Regards
> >Ray Mond
> >
> >
> >.
> >|||THe command you are using with the Truncateonly parameter does NOT move any
of the data from the end of the file to the front of the file, it only
returns any unused end portion of the file... instead try something like
dbcc shrinkfile(nameoffile, targetfilesizeinMB)
this command will move data from the end to the front , then truncate all of
the unused portion ...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:0b8601c3d9bb$08533b50$a101280a@.phx.gbl...
> Dear All,
> I have just tried to shrink a file, both in Enterprise
> Manager and in Query Analyser.
> Within EM it hung.
> Within QA it said it shrinked it but when I looked at the
> size of the file it was still the same size.
> The code I used in QA was
> dbcc shrinkfile (NAMEOFFILE, TRUNCATEONLY).
> Can anyone gice me pointers ?
> Thanks
> J|||Thanks Guys,
It worked using the dbcc shrinkfile(FileName, 4670) option
a couple of you guys sugested.
The main problem here was time. In this case it took over
20 minutes to shrink, and I was about to cancel it when it
worked ;)
Anyway thanks for that.
Question though, is there an automated way without using
DBCC Shrinkfile of moving all the data to the beginning of
the datafile before a DBCC Shrinkfile ?
Again thanks for your help
J
>--Original Message--
>THe command you are using with the Truncateonly parameter
does NOT move any
>of the data from the end of the file to the front of the
file, it only
>returns any unused end portion of the file... instead
try something like
>dbcc shrinkfile(nameoffile, targetfilesizeinMB)
>this command will move data from the end to the front ,
then truncate all of
>the unused portion ...
>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Computer Education Services Corporation (CESC),
Charlotte, NC
>www.computeredservices.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
>
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0b8601c3d9bb$08533b50$a101280a@.phx.gbl...
>> Dear All,
>> I have just tried to shrink a file, both in Enterprise
>> Manager and in Query Analyser.
>> Within EM it hung.
>> Within QA it said it shrinked it but when I looked at
the
>> size of the file it was still the same size.
>> The code I used in QA was
>> dbcc shrinkfile (NAMEOFFILE, TRUNCATEONLY).
>> Can anyone gice me pointers ?
>> Thanks
>> J
>
>.
>|||> Question though, is there an automated way without using
> DBCC Shrinkfile of moving all the data to the beginning of
> the datafile before a DBCC Shrinkfile ?
No - that's what shrink does.
Also, regarding your 20 minutes comment, the runtime of shrink is dependent
on a bunch of things, including how much you ask the file to shrink, the
distribution of free space at the start of the file, the speed of your IO
system, and most importantly, the amount of concurrent activity on the
system that may block shrink. Remember that shrink is an online operation
and so does not block concurrent activity - this means you can let it run
longer than your maintenance window as long as you can cope with the small
drop in workload throughput from having shrink working in the database.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:0a2501c3d9e1$0740c4a0$a601280a@.phx.gbl...
> Thanks Guys,
> It worked using the dbcc shrinkfile(FileName, 4670) option
> a couple of you guys sugested.
> The main problem here was time. In this case it took over
> 20 minutes to shrink, and I was about to cancel it when it
> worked ;)
> Anyway thanks for that.
> Question though, is there an automated way without using
> DBCC Shrinkfile of moving all the data to the beginning of
> the datafile before a DBCC Shrinkfile ?
> Again thanks for your help
> J
>
>
> >--Original Message--
> >THe command you are using with the Truncateonly parameter
> does NOT move any
> >of the data from the end of the file to the front of the
> file, it only
> >returns any unused end portion of the file... instead
> try something like
> >
> >dbcc shrinkfile(nameoffile, targetfilesizeinMB)
> >
> >this command will move data from the end to the front ,
> then truncate all of
> >the unused portion ...
> >
> >--
> >Wayne Snyder, MCDBA, SQL Server MVP
> >Computer Education Services Corporation (CESC),
> Charlotte, NC
> >www.computeredservices.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
> >
> >
> >"Julie" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:0b8601c3d9bb$08533b50$a101280a@.phx.gbl...
> >> Dear All,
> >>
> >> I have just tried to shrink a file, both in Enterprise
> >> Manager and in Query Analyser.
> >>
> >> Within EM it hung.
> >>
> >> Within QA it said it shrinked it but when I looked at
> the
> >> size of the file it was still the same size.
> >>
> >> The code I used in QA was
> >>
> >> dbcc shrinkfile (NAMEOFFILE, TRUNCATEONLY).
> >>
> >> Can anyone gice me pointers ?
> >>
> >> Thanks
> >> J
> >
> >
> >.
> >|||Thanks Paul
>--Original Message--
>> Question though, is there an automated way without using
>> DBCC Shrinkfile of moving all the data to the beginning
of
>> the datafile before a DBCC Shrinkfile ?
>No - that's what shrink does.
>Also, regarding your 20 minutes comment, the runtime of
shrink is dependent
>on a bunch of things, including how much you ask the file
to shrink, the
>distribution of free space at the start of the file, the
speed of your IO
>system, and most importantly, the amount of concurrent
activity on the
>system that may block shrink. Remember that shrink is an
online operation
>and so does not block concurrent activity - this means
you can let it run
>longer than your maintenance window as long as you can
cope with the small
>drop in workload throughput from having shrink working in
the database.
>Regards.
>--
>Paul Randal
>Dev Lead, Microsoft SQL Server Storage Engine
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0a2501c3d9e1$0740c4a0$a601280a@.phx.gbl...
>> Thanks Guys,
>> It worked using the dbcc shrinkfile(FileName, 4670)
option
>> a couple of you guys sugested.
>> The main problem here was time. In this case it took
over
>> 20 minutes to shrink, and I was about to cancel it when
it
>> worked ;)
>> Anyway thanks for that.
>> Question though, is there an automated way without using
>> DBCC Shrinkfile of moving all the data to the beginning
of
>> the datafile before a DBCC Shrinkfile ?
>> Again thanks for your help
>> J
>>
>>
>> >--Original Message--
>> >THe command you are using with the Truncateonly
parameter
>> does NOT move any
>> >of the data from the end of the file to the front of
the
>> file, it only
>> >returns any unused end portion of the file... instead
>> try something like
>> >
>> >dbcc shrinkfile(nameoffile, targetfilesizeinMB)
>> >
>> >this command will move data from the end to the front ,
>> then truncate all of
>> >the unused portion ...
>> >
>> >--
>> >Wayne Snyder, MCDBA, SQL Server MVP
>> >Computer Education Services Corporation (CESC),
>> Charlotte, NC
>> >www.computeredservices.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
>> >
>> >
>> >"Julie" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:0b8601c3d9bb$08533b50$a101280a@.phx.gbl...
>> >> Dear All,
>> >>
>> >> I have just tried to shrink a file, both in
Enterprise
>> >> Manager and in Query Analyser.
>> >>
>> >> Within EM it hung.
>> >>
>> >> Within QA it said it shrinked it but when I looked at
>> the
>> >> size of the file it was still the same size.
>> >>
>> >> The code I used in QA was
>> >>
>> >> dbcc shrinkfile (NAMEOFFILE, TRUNCATEONLY).
>> >>
>> >> Can anyone gice me pointers ?
>> >>
>> >> Thanks
>> >> J
>> >
>> >
>> >.
>> >
>
>.
>
I have just tried to shrink a file, both in Enterprise
Manager and in Query Analyser.
Within EM it hung.
Within QA it said it shrinked it but when I looked at the
size of the file it was still the same size.
The code I used in QA was
dbcc shrinkfile (NAMEOFFILE, TRUNCATEONLY).
Can anyone gice me pointers ?
Thanks
JBy any chance, are you trying to shrink the tempdb data file? Sometimes,
that fails because there are temporary worktables in there that are still
being used, though QA would report as if the file had been shrunk.
--
Regards
Ray Mond|||No, I am shrinking a non system, non tempdb file.
Thanks
J
>--Original Message--
>By any chance, are you trying to shrink the tempdb data
file? Sometimes,
>that fails because there are temporary worktables in
there that are still
>being used, though QA would report as if the file had
been shrunk.
>--
>Regards
>Ray Mond
>
>.
>|||Hi,
Please look into the below article from MS. This will help you out in
shrinking the TX Log.
http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
Thanks
Hari
MCDBA
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:0b8601c3d9bb$08533b50$a101280a@.phx.gbl...
> Dear All,
> I have just tried to shrink a file, both in Enterprise
> Manager and in Query Analyser.
> Within EM it hung.
> Within QA it said it shrinked it but when I looked at the
> size of the file it was still the same size.
> The code I used in QA was
> dbcc shrinkfile (NAMEOFFILE, TRUNCATEONLY).
> Can anyone gice me pointers ?
> Thanks
> J|||Why don't you try DBCC SHRINKFILE(NAMEOFFILE, somesize) ? Your data might
be residing on the end of the file, which prevents any significant
truncation.
--
Regards
Ray Mond
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:07ae01c3d9c0$9415a2e0$a501280a@.phx.gbl...
> No, I am shrinking a non system, non tempdb file.
> Thanks
> J
>
> >--Original Message--
> >By any chance, are you trying to shrink the tempdb data
> file? Sometimes,
> >that fails because there are temporary worktables in
> there that are still
> >being used, though QA would report as if the file had
> been shrunk.
> >
> >--
> >Regards
> >Ray Mond
> >
> >
> >.
> >|||THe command you are using with the Truncateonly parameter does NOT move any
of the data from the end of the file to the front of the file, it only
returns any unused end portion of the file... instead try something like
dbcc shrinkfile(nameoffile, targetfilesizeinMB)
this command will move data from the end to the front , then truncate all of
the unused portion ...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:0b8601c3d9bb$08533b50$a101280a@.phx.gbl...
> Dear All,
> I have just tried to shrink a file, both in Enterprise
> Manager and in Query Analyser.
> Within EM it hung.
> Within QA it said it shrinked it but when I looked at the
> size of the file it was still the same size.
> The code I used in QA was
> dbcc shrinkfile (NAMEOFFILE, TRUNCATEONLY).
> Can anyone gice me pointers ?
> Thanks
> J|||Thanks Guys,
It worked using the dbcc shrinkfile(FileName, 4670) option
a couple of you guys sugested.
The main problem here was time. In this case it took over
20 minutes to shrink, and I was about to cancel it when it
worked ;)
Anyway thanks for that.
Question though, is there an automated way without using
DBCC Shrinkfile of moving all the data to the beginning of
the datafile before a DBCC Shrinkfile ?
Again thanks for your help
J
>--Original Message--
>THe command you are using with the Truncateonly parameter
does NOT move any
>of the data from the end of the file to the front of the
file, it only
>returns any unused end portion of the file... instead
try something like
>dbcc shrinkfile(nameoffile, targetfilesizeinMB)
>this command will move data from the end to the front ,
then truncate all of
>the unused portion ...
>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Computer Education Services Corporation (CESC),
Charlotte, NC
>www.computeredservices.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
>
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0b8601c3d9bb$08533b50$a101280a@.phx.gbl...
>> Dear All,
>> I have just tried to shrink a file, both in Enterprise
>> Manager and in Query Analyser.
>> Within EM it hung.
>> Within QA it said it shrinked it but when I looked at
the
>> size of the file it was still the same size.
>> The code I used in QA was
>> dbcc shrinkfile (NAMEOFFILE, TRUNCATEONLY).
>> Can anyone gice me pointers ?
>> Thanks
>> J
>
>.
>|||> Question though, is there an automated way without using
> DBCC Shrinkfile of moving all the data to the beginning of
> the datafile before a DBCC Shrinkfile ?
No - that's what shrink does.
Also, regarding your 20 minutes comment, the runtime of shrink is dependent
on a bunch of things, including how much you ask the file to shrink, the
distribution of free space at the start of the file, the speed of your IO
system, and most importantly, the amount of concurrent activity on the
system that may block shrink. Remember that shrink is an online operation
and so does not block concurrent activity - this means you can let it run
longer than your maintenance window as long as you can cope with the small
drop in workload throughput from having shrink working in the database.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:0a2501c3d9e1$0740c4a0$a601280a@.phx.gbl...
> Thanks Guys,
> It worked using the dbcc shrinkfile(FileName, 4670) option
> a couple of you guys sugested.
> The main problem here was time. In this case it took over
> 20 minutes to shrink, and I was about to cancel it when it
> worked ;)
> Anyway thanks for that.
> Question though, is there an automated way without using
> DBCC Shrinkfile of moving all the data to the beginning of
> the datafile before a DBCC Shrinkfile ?
> Again thanks for your help
> J
>
>
> >--Original Message--
> >THe command you are using with the Truncateonly parameter
> does NOT move any
> >of the data from the end of the file to the front of the
> file, it only
> >returns any unused end portion of the file... instead
> try something like
> >
> >dbcc shrinkfile(nameoffile, targetfilesizeinMB)
> >
> >this command will move data from the end to the front ,
> then truncate all of
> >the unused portion ...
> >
> >--
> >Wayne Snyder, MCDBA, SQL Server MVP
> >Computer Education Services Corporation (CESC),
> Charlotte, NC
> >www.computeredservices.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
> >
> >
> >"Julie" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:0b8601c3d9bb$08533b50$a101280a@.phx.gbl...
> >> Dear All,
> >>
> >> I have just tried to shrink a file, both in Enterprise
> >> Manager and in Query Analyser.
> >>
> >> Within EM it hung.
> >>
> >> Within QA it said it shrinked it but when I looked at
> the
> >> size of the file it was still the same size.
> >>
> >> The code I used in QA was
> >>
> >> dbcc shrinkfile (NAMEOFFILE, TRUNCATEONLY).
> >>
> >> Can anyone gice me pointers ?
> >>
> >> Thanks
> >> J
> >
> >
> >.
> >|||Thanks Paul
>--Original Message--
>> Question though, is there an automated way without using
>> DBCC Shrinkfile of moving all the data to the beginning
of
>> the datafile before a DBCC Shrinkfile ?
>No - that's what shrink does.
>Also, regarding your 20 minutes comment, the runtime of
shrink is dependent
>on a bunch of things, including how much you ask the file
to shrink, the
>distribution of free space at the start of the file, the
speed of your IO
>system, and most importantly, the amount of concurrent
activity on the
>system that may block shrink. Remember that shrink is an
online operation
>and so does not block concurrent activity - this means
you can let it run
>longer than your maintenance window as long as you can
cope with the small
>drop in workload throughput from having shrink working in
the database.
>Regards.
>--
>Paul Randal
>Dev Lead, Microsoft SQL Server Storage Engine
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0a2501c3d9e1$0740c4a0$a601280a@.phx.gbl...
>> Thanks Guys,
>> It worked using the dbcc shrinkfile(FileName, 4670)
option
>> a couple of you guys sugested.
>> The main problem here was time. In this case it took
over
>> 20 minutes to shrink, and I was about to cancel it when
it
>> worked ;)
>> Anyway thanks for that.
>> Question though, is there an automated way without using
>> DBCC Shrinkfile of moving all the data to the beginning
of
>> the datafile before a DBCC Shrinkfile ?
>> Again thanks for your help
>> J
>>
>>
>> >--Original Message--
>> >THe command you are using with the Truncateonly
parameter
>> does NOT move any
>> >of the data from the end of the file to the front of
the
>> file, it only
>> >returns any unused end portion of the file... instead
>> try something like
>> >
>> >dbcc shrinkfile(nameoffile, targetfilesizeinMB)
>> >
>> >this command will move data from the end to the front ,
>> then truncate all of
>> >the unused portion ...
>> >
>> >--
>> >Wayne Snyder, MCDBA, SQL Server MVP
>> >Computer Education Services Corporation (CESC),
>> Charlotte, NC
>> >www.computeredservices.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
>> >
>> >
>> >"Julie" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:0b8601c3d9bb$08533b50$a101280a@.phx.gbl...
>> >> Dear All,
>> >>
>> >> I have just tried to shrink a file, both in
Enterprise
>> >> Manager and in Query Analyser.
>> >>
>> >> Within EM it hung.
>> >>
>> >> Within QA it said it shrinked it but when I looked at
>> the
>> >> size of the file it was still the same size.
>> >>
>> >> The code I used in QA was
>> >>
>> >> dbcc shrinkfile (NAMEOFFILE, TRUNCATEONLY).
>> >>
>> >> Can anyone gice me pointers ?
>> >>
>> >> Thanks
>> >> J
>> >
>> >
>> >.
>> >
>
>.
>
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 t
hat database. Check this by executing
DBCC OPENTRAN(DBNAME). It can be a replication process or it can can be a no
rmal 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 server
Thanks
Hari
MCDBA
-- KC wrote: --
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
go
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 t
hat database. Check this by executing
DBCC OPENTRAN(DBNAME). It can be a replication process or it can can be a no
rmal 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 server
Thanks
Hari
MCDBA
-- KC wrote: --
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
go
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 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.c om...
> 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.c om...
>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
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.c om...
> 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.c om...
>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
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
go
Hi,
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 server
Thanks
Hari
MCDBA
-- KC wrote: --
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
go
sql
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
go
Hi,
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 server
Thanks
Hari
MCDBA
-- KC wrote: --
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
go
sql
DBCC Shrinkfile
I am looking to shrink a database file using DBCC Shrinkfile to try to recla
im some disk space. For some unexplained reason I have some unsettled feelin
gs. 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 sa
fe?
3. Are there any unknown dangers lurking out their when running DBCC Shrinkf
ile?
Message posted via http://www.droptable.com"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:30b3c02dee4a4a4988cebcf126e266f3@.SQ
droptable.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 droptable.com" wrote:
> I am looking to shrink a database file using DBCC Shrinkfile to try to rec
laim some disk space. For some unexplained reason I have some unsettled feel
ings. I need to confirm:
> 1. I was considering running DBCC Shrinkfile with the TruncateOnly argumen
t, 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 Shrin
kfile?
> --
> Message posted via http://www.droptable.com
>|||I am running SQL 2K.
Message posted via http://www.droptable.com|||I am running SQL 2K.
Message posted via http://www.droptable.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 droptable.com" <forum@.droptable.com> wrote in message
news:30b3c02dee4a4a4988cebcf126e266f3@.SQ
droptable.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.droptable.com
im some disk space. For some unexplained reason I have some unsettled feelin
gs. 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 sa
fe?
3. Are there any unknown dangers lurking out their when running DBCC Shrinkf
ile?
Message posted via http://www.droptable.com"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:30b3c02dee4a4a4988cebcf126e266f3@.SQ
droptable.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 droptable.com" wrote:
> I am looking to shrink a database file using DBCC Shrinkfile to try to rec
laim some disk space. For some unexplained reason I have some unsettled feel
ings. I need to confirm:
> 1. I was considering running DBCC Shrinkfile with the TruncateOnly argumen
t, 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 Shrin
kfile?
> --
> Message posted via http://www.droptable.com
>|||I am running SQL 2K.
Message posted via http://www.droptable.com|||I am running SQL 2K.
Message posted via http://www.droptable.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 droptable.com" <forum@.droptable.com> wrote in message
news:30b3c02dee4a4a4988cebcf126e266f3@.SQ
droptable.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.droptable.com
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 siz
e
(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 bu
t
>it gave me an error (which I can't remember) even though the actual used si
ze
>(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 siz
e
(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 bu
t
>it gave me an error (which I can't remember) even though the actual used si
ze
>(not phyical) was like 1500 MB. I tried again with a larger number, 5000 MB
>and this time it worked.
>TIA.
DBCC Shrinkfile
Dear All,
I have just tried to shrink a file, both in Enterprise
Manager and in Query Analyser.
Within EM it hung.
Within QA it said it shrinked it but when I looked at the
size of the file it was still the same size.
The code I used in QA was
dbcc shrinkfile (NAMEOFFILE, TRUNCATEONLY).
Can anyone gice me pointers ?
Thanks
JBy any chance, are you trying to shrink the tempdb data file? Sometimes,
that fails because there are temporary worktables in there that are still
being used, though QA would report as if the file had been shrunk.
Regards
Ray Mond|||No, I am shrinking a non system, non tempdb file.
Thanks
J
file? Sometimes,
there that are still
been shrunk.
Please look into the below article from MS. This will help you out in
shrinking the TX Log.
http://support.microsoft.com/defaul...kb;en-us;272318
Thanks
Hari
MCDBA
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:0b8601c3d9bb$08533b50$a101280a@.phx.gbl...
be residing on the end of the file, which prevents any significant
truncation.
Regards
Ray Mond
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:07ae01c3d9c0$9415a2e0$a501280a@.phx.gbl...[QUOTE]
> No, I am shrinking a non system, non tempdb file.
> Thanks
> J
>
> file? Sometimes,
> there that are still
> been shrunk.|||THe command you are using with the Truncateonly parameter does NOT move any
of the data from the end of the file to the front of the file, it only
returns any unused end portion of the file... instead try something like
dbcc shrinkfile(nameoffile, targetfilesizeinMB)
this command will move data from the end to the front , then truncate all of
the unused portion ...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:0b8601c3d9bb$08533b50$a101280a@.phx.gbl...
It worked using the dbcc shrinkfile(FileName, 4670) option
a couple of you guys sugested.
The main problem here was time. In this case it took over
20 minutes to shrink, and I was about to cancel it when it
worked ;)
Anyway thanks for that.
Question though, is there an automated way without using
DBCC Shrinkfile of moving all the data to the beginning of
the datafile before a DBCC Shrinkfile ?
Again thanks for your help
J
does NOT move any
file, it only
try something like
then truncate all of
Charlotte, NC
(PASS) and it's
message
No - that's what shrink does.
Also, regarding your 20 minutes comment, the runtime of shrink is dependent
on a bunch of things, including how much you ask the file to shrink, the
distribution of free space at the start of the file, the speed of your IO
system, and most importantly, the amount of concurrent activity on the
system that may block shrink. Remember that shrink is an online operation
and so does not block concurrent activity - this means you can let it run
longer than your maintenance window as long as you can cope with the small
drop in workload throughput from having shrink working in the database.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:0a2501c3d9e1$0740c4a0$a601280a@.phx.gbl...[QUOTE]
> Thanks Guys,
> It worked using the dbcc shrinkfile(FileName, 4670) option
> a couple of you guys sugested.
> The main problem here was time. In this case it took over
> 20 minutes to shrink, and I was about to cancel it when it
> worked ;)
> Anyway thanks for that.
> Question though, is there an automated way without using
> DBCC Shrinkfile of moving all the data to the beginning of
> the datafile before a DBCC Shrinkfile ?
> Again thanks for your help
> J
>
>
> does NOT move any
> file, it only
> try something like
> then truncate all of
> Charlotte, NC
> (PASS) and it's
> message
> the|||Thanks Paul
shrink is dependent
to shrink, the
speed of your IO
activity on the
online operation
you can let it run
cope with the small
the database.
confers no rights.
message
I have just tried to shrink a file, both in Enterprise
Manager and in Query Analyser.
Within EM it hung.
Within QA it said it shrinked it but when I looked at the
size of the file it was still the same size.
The code I used in QA was
dbcc shrinkfile (NAMEOFFILE, TRUNCATEONLY).
Can anyone gice me pointers ?
Thanks
JBy any chance, are you trying to shrink the tempdb data file? Sometimes,
that fails because there are temporary worktables in there that are still
being used, though QA would report as if the file had been shrunk.
Regards
Ray Mond|||No, I am shrinking a non system, non tempdb file.
Thanks
J
quote:
>--Original Message--
>By any chance, are you trying to shrink the tempdb data
file? Sometimes,
quote:
>that fails because there are temporary worktables in
there that are still
quote:
>being used, though QA would report as if the file had
been shrunk.
quote:|||Hi,
>--
>Regards
>Ray Mond
>
>.
>
Please look into the below article from MS. This will help you out in
shrinking the TX Log.
http://support.microsoft.com/defaul...kb;en-us;272318
Thanks
Hari
MCDBA
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:0b8601c3d9bb$08533b50$a101280a@.phx.gbl...
quote:|||Why don't you try DBCC SHRINKFILE(NAMEOFFILE, somesize) ? Your data might
> Dear All,
> I have just tried to shrink a file, both in Enterprise
> Manager and in Query Analyser.
> Within EM it hung.
> Within QA it said it shrinked it but when I looked at the
> size of the file it was still the same size.
> The code I used in QA was
> dbcc shrinkfile (NAMEOFFILE, TRUNCATEONLY).
> Can anyone gice me pointers ?
> Thanks
> J
be residing on the end of the file, which prevents any significant
truncation.
Regards
Ray Mond
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:07ae01c3d9c0$9415a2e0$a501280a@.phx.gbl...[QUOTE]
> No, I am shrinking a non system, non tempdb file.
> Thanks
> J
>
> file? Sometimes,
> there that are still
> been shrunk.|||THe command you are using with the Truncateonly parameter does NOT move any
of the data from the end of the file to the front of the file, it only
returns any unused end portion of the file... instead try something like
dbcc shrinkfile(nameoffile, targetfilesizeinMB)
this command will move data from the end to the front , then truncate all of
the unused portion ...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:0b8601c3d9bb$08533b50$a101280a@.phx.gbl...
quote:|||Thanks Guys,
> Dear All,
> I have just tried to shrink a file, both in Enterprise
> Manager and in Query Analyser.
> Within EM it hung.
> Within QA it said it shrinked it but when I looked at the
> size of the file it was still the same size.
> The code I used in QA was
> dbcc shrinkfile (NAMEOFFILE, TRUNCATEONLY).
> Can anyone gice me pointers ?
> Thanks
> J
It worked using the dbcc shrinkfile(FileName, 4670) option
a couple of you guys sugested.
The main problem here was time. In this case it took over
20 minutes to shrink, and I was about to cancel it when it
worked ;)
Anyway thanks for that.
Question though, is there an automated way without using
DBCC Shrinkfile of moving all the data to the beginning of
the datafile before a DBCC Shrinkfile ?
Again thanks for your help
J
quote:
>--Original Message--
>THe command you are using with the Truncateonly parameter
does NOT move any
quote:
>of the data from the end of the file to the front of the
file, it only
quote:
>returns any unused end portion of the file... instead
try something like
quote:
>dbcc shrinkfile(nameoffile, targetfilesizeinMB)
>this command will move data from the end to the front ,
then truncate all of
quote:
>the unused portion ...
>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Computer Education Services Corporation (CESC),
Charlotte, NC
quote:
>www.computeredservices.com
>(Please respond only to the newsgroups.)
>I support the Professional Association of SQL Server
(PASS) and it's
quote:
>community of SQL Server professionals.
>www.sqlpass.org
>
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message
quote:|||> Question though, is there an automated way without using
>news:0b8601c3d9bb$08533b50$a101280a@.phx.gbl...
the[QUOTE]
>
>.
>
quote:
> DBCC Shrinkfile of moving all the data to the beginning of
> the datafile before a DBCC Shrinkfile ?
No - that's what shrink does.
Also, regarding your 20 minutes comment, the runtime of shrink is dependent
on a bunch of things, including how much you ask the file to shrink, the
distribution of free space at the start of the file, the speed of your IO
system, and most importantly, the amount of concurrent activity on the
system that may block shrink. Remember that shrink is an online operation
and so does not block concurrent activity - this means you can let it run
longer than your maintenance window as long as you can cope with the small
drop in workload throughput from having shrink working in the database.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:0a2501c3d9e1$0740c4a0$a601280a@.phx.gbl...[QUOTE]
> Thanks Guys,
> It worked using the dbcc shrinkfile(FileName, 4670) option
> a couple of you guys sugested.
> The main problem here was time. In this case it took over
> 20 minutes to shrink, and I was about to cancel it when it
> worked ;)
> Anyway thanks for that.
> Question though, is there an automated way without using
> DBCC Shrinkfile of moving all the data to the beginning of
> the datafile before a DBCC Shrinkfile ?
> Again thanks for your help
> J
>
>
> does NOT move any
> file, it only
> try something like
> then truncate all of
> Charlotte, NC
> (PASS) and it's
> message
> the|||Thanks Paul
quote:
>--Original Message--
of[QUOTE]
>No - that's what shrink does.
>Also, regarding your 20 minutes comment, the runtime of
shrink is dependent
quote:
>on a bunch of things, including how much you ask the file
to shrink, the
quote:
>distribution of free space at the start of the file, the
speed of your IO
quote:
>system, and most importantly, the amount of concurrent
activity on the
quote:
>system that may block shrink. Remember that shrink is an
online operation
quote:
>and so does not block concurrent activity - this means
you can let it run
quote:
>longer than your maintenance window as long as you can
cope with the small
quote:
>drop in workload throughput from having shrink working in
the database.
quote:
>Regards.
>--
>Paul Randal
>Dev Lead, Microsoft SQL Server Storage Engine
>This posting is provided "AS IS" with no warranties, and
confers no rights.
quote:
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message
quote:
>news:0a2501c3d9e1$0740c4a0$a601280a@.phx.gbl...
option[QUOTE]
over[QUOTE]
it[QUOTE]
of[QUOTE]
parameter[QUOTE]
the[QUOTE]
Enterprise[QUOTE]
>
>.
>
Subscribe to:
Posts (Atom)