Problem running DBCC Shrinkfile

Hi guys
I was trying to backup db and truncate the log, here's what I did..
1) Used sp_addumpdevice danV2_db
2) Backup danV2 to danV2_db
3) Used sp_addumpdevice danV2_log
4) Backup danV2_log to danV2_log
Then I tried..
DBCC SHRINKFILE(danV2_log, 5)
...but I got the following error...
"Could not shrink danV2_log. All logical drives are in use" or something.
What does this mean, why can't I shrink the log? Or what did I miss?
Thanks,
DanEasy way...
Right click on database in Enterprise Manager.
Select shrink database , select the logfile you wan't to shrink.
I drank alot of beer and ended up in the police department database.
Drank more beer and learned SQL in the dark hours.
DELETE FROM offenders WHERE Title=''MrAA'' AND Year=2006;
I love SQL
"musosdev" wrote:

> Hi guys
> I was trying to backup db and truncate the log, here's what I did..
> 1) Used sp_addumpdevice danV2_db
> 2) Backup danV2 to danV2_db
> 3) Used sp_addumpdevice danV2_log
> 4) Backup danV2_log to danV2_log
> Then I tried..
> DBCC SHRINKFILE(danV2_log, 5)
> ...but I got the following error...
> "Could not shrink danV2_log. All logical drives are in use" or something.
> What does this mean, why can't I shrink the log? Or what did I miss?
> Thanks,
>
> Dan
>|||To understand the error, read about the virtual log file concept (see
http://www.karaszi.com/SQLServer/info_dont_shrink.asp).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"musosdev" <musoswire@.community.nospam> wrote in message
news:8E9617EF-92CB-44DF-9617-C197F4C59420@.microsoft.com...
> Hi guys
> I was trying to backup db and truncate the log, here's what I did..
> 1) Used sp_addumpdevice danV2_db
> 2) Backup danV2 to danV2_db
> 3) Used sp_addumpdevice danV2_log
> 4) Backup danV2_log to danV2_log
> Then I tried..
> DBCC SHRINKFILE(danV2_log, 5)
> ...but I got the following error...
> "Could not shrink danV2_log. All logical drives are in use" or something.
> What does this mean, why can't I shrink the log? Or what did I miss?
> Thanks,
>
> Dan
>|||Thanks, but it's MSDE, so there's no EM. Should be able to do it with osql
though right?!
"Hate_orphaned_users" wrote:
[vbcol=seagreen]
> Easy way...
> Right click on database in Enterprise Manager.
> Select shrink database , select the logfile you wan't to shrink.
>
> --
> I drank alot of beer and ended up in the police department database.
> Drank more beer and learned SQL in the dark hours.
> DELETE FROM offenders WHERE Title=''MrAA'' AND Year=2006;
> I love SQL
>
>
> "musosdev" wrote:
>|||DBCC SHRINKFILE is indeed the command to use. Again, investigate the virtual
log file layout, see
how many virtual log files you have, where the "latest" in use VLF is, and y
ou might have to do
several BACKUP LOG and SHRINK. See the article I referred to.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"musosdev" <musoswire@.community.nospam> wrote in message
news:21888405-903D-4BE9-AB87-E477142A0304@.microsoft.com...[vbcol=seagreen]
> Thanks, but it's MSDE, so there's no EM. Should be able to do it with osql
> though right?!
> "Hate_orphaned_users" wrote:
>|||OK, well according to DBCC loginfo, I have 325 virtual log files. Which is
enough that I can't actually see the header info in my cmd info once it's ru
n.
Is there a way to "page" the dbcc loginfo command (like more in msdos) ?
Once I do find the last vlf - how do I then start to shrink the database?!
Thanks,
Dan
"Tibor Karaszi" wrote:

> DBCC SHRINKFILE is indeed the command to use. Again, investigate the virtu
al log file layout, see
> how many virtual log files you have, where the "latest" in use VLF is, and
you might have to do
> several BACKUP LOG and SHRINK. See the article I referred to.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "musosdev" <musoswire@.community.nospam> wrote in message
> news:21888405-903D-4BE9-AB87-E477142A0304@.microsoft.com...
>
>|||Hi, Dan,
First, I notice that you said:
"
4) Backup danV2_log to danV2_log
Then I tried..
DBCC SHRINKFILE(danV2_log, 5)
"
This seems not right. danv2_log should be a logical device name for storing
your database transaction log backup file; however it (logical device name)
was used as a parameter (logical file name) in DBCC SHRINKFILE. Is it a
writing mistake?
From the error message, I think that the cause is most likely that the
database was being used by other people when you perform BACKUP LOG and
DBCC SHRINKFILE operations.
Please run sp_who2 to check if some other users are using your database. If
so, please ask them to log off. Make sure that only you use the database.
After that, please run BACKUP LOG ... WITH TRUNCATE_ONLY again and then
run DBCC SHRINKFILE(file_name, target_size).
If this issue persists, could you please post the full error message here
for further research?
Please feel free to let us know if you have any other questions or concerns.
Have a good day!
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Hi Charles,
Thanks for the information. Can I get some clarification on using DBCC
shrinkfile? I thought you gave it the name of the log as referenced in osql?
Are you saying I need to give it the actual log file location, ie..
DBCC SHRINKFILE('c:\danSQL\dabV2_log.bak', 5)
If not, where do I find out what the "logical device name" for the log file
is, so I can shrink it?!
Thanks,
Dan
"Charles Wang[MSFT]" wrote:

> Hi, Dan,
> First, I notice that you said:
> "
> 4) Backup danV2_log to danV2_log
> Then I tried..
> DBCC SHRINKFILE(danV2_log, 5)
> "
> This seems not right. danv2_log should be a logical device name for storin
g
> your database transaction log backup file; however it (logical device name
)
> was used as a parameter (logical file name) in DBCC SHRINKFILE. Is it a
> writing mistake?
> From the error message, I think that the cause is most likely that the
> database was being used by other people when you perform BACKUP LOG and
> DBCC SHRINKFILE operations.
> Please run sp_who2 to check if some other users are using your database. I
f
> so, please ask them to log off. Make sure that only you use the database.
> After that, please run BACKUP LOG ... WITH TRUNCATE_ONLY again and then
> run DBCC SHRINKFILE(file_name, target_size).
> If this issue persists, could you please post the full error message here
> for further research?
> Please feel free to let us know if you have any other questions or concern
s.
> Have a good day!
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ========================================
=============
> Get notification to my posts through email? Please refer to:
> [url]http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif[/ur
l]
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply promptly
.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscript...t/default.aspx.
> ========================================
==============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> ========================================
==============
>
>
>
>|||Hi again Charles,
Just a bit more info on my database log file. I just ran a sp_helpfile to
determine the names of the log files..
danV2_log
c:\dansql\data\danV2_log.ldf
NULL
2653056kb
Umlimited
10%
log only
So, should I use the name danV2_log in my shrinkfile command, or the full
file path? Or am I missing a step?
Thanks,
Dan
"musosdev" wrote:
[vbcol=seagreen]
> Hi Charles,
> Thanks for the information. Can I get some clarification on using DBCC
> shrinkfile? I thought you gave it the name of the log as referenced in osq
l?
> Are you saying I need to give it the actual log file location, ie..
> DBCC SHRINKFILE('c:\danSQL\dabV2_log.bak', 5)
> If not, where do I find out what the "logical device name" for the log fil
e
> is, so I can shrink it?!
> Thanks,
>
> Dan
> "Charles Wang[MSFT]" wrote:
>|||> So, should I use the name danV2_log in my shrinkfile command, or the full
> file path? Or am I missing a step?
Specify the logical name, not the physical filename.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"musosdev" <musoswire@.community.nospam> wrote in message
news:32E789EF-007D-40F4-92C8-BC05ACFA8CBD@.microsoft.com...[vbcol=seagreen]
> Hi again Charles,
> Just a bit more info on my database log file. I just ran a sp_helpfile to
> determine the names of the log files..
> danV2_log
> c:\dansql\data\danV2_log.ldf
> NULL
> 2653056kb
> Umlimited
> 10%
> log only
> So, should I use the name danV2_log in my shrinkfile command, or the full
> file path? Or am I missing a step?
> Thanks,
>
> Dan
>
> "musosdev" wrote:
>