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
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:
> 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:
|||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 run.
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 virtual 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/subscriptions/managednewsgroups/default.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/subscriptions/support/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 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/subscriptions/managednewsgroups/default.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/subscriptions/support/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 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,
DBCC SHRINKFILE is used for shrinking the transaction file (.ldf) not the
backup file (.bak or .trn). Logical file name should be used in DBCC
SHRINKFILE.
Your saying "Backup danv2_log to danV2_log" make me think that danV2_log is
a backup device name which cannot be used in DBCC SHRINKFILE. Since you
have pointed that dan2_log is the logical file name, you can use it in DBCC
SHRINKFILE.
Tibor's suggestion is helpful. Please first ensure that only you log on
your SQL Server and then try backing up your transaction log with truncate
only and shrink the file several times.
If this issue persists, please feel free to post back. We would like to
work with you for further research.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.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/subscriptions/support/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 and Tibor,
You'll both be glad to know... I've sussed it. Although to be fair, I'm not
entirely sure what I was doing wrong.
danV2_log is the name of the log as provided by sp_helpfile, and I'd also
named my dump device as that too, so that probably confused matters.
To clarify for future, here's what I did..
USE master
EXEC sp_addumpdevice 'disk', 'danV2_db', '<filepath>'
EXEC sp_addumpdevice 'disk', 'danV2_log', '<filepath>'
BACKUP DATABASE danV2 TO danV2_db
BACKUP LOG danV2 WITH TRUNCATE_ONLY
USE danV2
DBCC SHRINKFILE(danV2_log, 20)
go
I'd like to thank you both for your conitued help with this one. Hopefully
it won't be so painful next time!
And I have just one final question for you... if I keep doing a full backup
everyday, can I use the full backup to do a restore if anything goes wrong?
(I know it's not the best solution but it'll do until we our new SQL Server
2005 system setup).
Thanks again,
Dan
"Charles Wang[MSFT]" wrote:
> Hi, Dan,
> DBCC SHRINKFILE is used for shrinking the transaction file (.ldf) not the
> backup file (.bak or .trn). Logical file name should be used in DBCC
> SHRINKFILE.
> Your saying "Backup danv2_log to danV2_log" make me think that danV2_log is
> a backup device name which cannot be used in DBCC SHRINKFILE. Since you
> have pointed that dan2_log is the logical file name, you can use it in DBCC
> SHRINKFILE.
> Tibor's suggestion is helpful. Please first ensure that only you log on
> your SQL Server and then try backing up your transaction log with truncate
> only and shrink the file several times.
> If this issue persists, please feel free to post back. We would like to
> work with you for further research.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ================================================== ===
> Get notification to my posts through email? Please refer to:
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.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/subscriptions/support/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.
> ================================================== ====
>
>
>
|||Tibor's in an airliner on his way to Seattle.
If all you are going to do with the log is back it up with truncate only,
then you might as well put the DB into Simple Recovery Model and just do
full backups only.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
"musosdev" <musoswire@.community.nospam> wrote in message
news:746B5D8E-C290-473C-8563-9E7FEA7CA5F2@.microsoft.com...
Hi Charles and Tibor,
You'll both be glad to know... I've sussed it. Although to be fair, I'm not
entirely sure what I was doing wrong.
danV2_log is the name of the log as provided by sp_helpfile, and I'd also
named my dump device as that too, so that probably confused matters.
To clarify for future, here's what I did..
USE master
EXEC sp_addumpdevice 'disk', 'danV2_db', '<filepath>'
EXEC sp_addumpdevice 'disk', 'danV2_log', '<filepath>'
BACKUP DATABASE danV2 TO danV2_db
BACKUP LOG danV2 WITH TRUNCATE_ONLY
USE danV2
DBCC SHRINKFILE(danV2_log, 20)
go
I'd like to thank you both for your conitued help with this one. Hopefully
it won't be so painful next time!
And I have just one final question for you... if I keep doing a full backup
everyday, can I use the full backup to do a restore if anything goes wrong?
(I know it's not the best solution but it'll do until we our new SQL Server
2005 system setup).
Thanks again,
Dan
"Charles Wang[MSFT]" wrote:
> Hi, Dan,
> DBCC SHRINKFILE is used for shrinking the transaction file (.ldf) not the
> backup file (.bak or .trn). Logical file name should be used in DBCC
> SHRINKFILE.
> Your saying "Backup danv2_log to danV2_log" make me think that danV2_log
> is
> a backup device name which cannot be used in DBCC SHRINKFILE. Since you
> have pointed that dan2_log is the logical file name, you can use it in
> DBCC
> SHRINKFILE.
> Tibor's suggestion is helpful. Please first ensure that only you log on
> your SQL Server and then try backing up your transaction log with truncate
> only and shrink the file several times.
> If this issue persists, please feel free to post back. We would like to
> work with you for further research.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ================================================== ===
> Get notification to my posts through email? Please refer to:
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.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/subscriptions/support/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.
> ================================================== ====
>
>
>
No comments:
Post a Comment