Solved

DBCC Shrinkfile

Posted on 2010-09-22
3
771 Views
Last Modified: 2013-11-05
Obviously I am not a SQL person...

I have an application that is locking up because the log file (carpedata_log) is full.  I opened SQL and ran the following query:

  dbcc shrinkfile (carpedata_log,10)
 
backup log carpedata_log with truncate_only

The response I get is:

Cannot shrink log file 2 (carpedata_log) because all logical log files are in use.

(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 911, Level 16, State 10, Line 3
Could not locate entry in sysdatabases for database 'carpedata_log'. No entry found with that name. Make sure that the name is entered correctly.
Msg 3013, Level 16, State 1, Line 3
BACKUP LOG is terminating abnormally.

1 row:
Dbld - 7
Field - 2
CurrentSize - 27392760
MinimumSize - 25600
UsedPages - 27392760
EstimatedPages - 25600
0
Comment
Question by:tmartin40
3 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
Comment Utility
backup log with truncate_only
0
 
LVL 15

Accepted Solution

by:
mcmonap earned 500 total points
Comment Utility
Hi,

Your actions here will make your database more difficult to recover if you have problems so you must proceed with caution!  I assume your application is not working so your main goal is to get it working, here is how I would proceed.
1. try a sql backup (this will probably fail if you have no disk space left)
2. truncate the transaction log (you were trying to do this)
3. shrink the log file (you were trying to do this too
4. setup a regular planned backup schedule for the database(s)
 
So first off, where you are probably going wrong above:
1. When you use backup you should specify the database name (probably carpedata?) not the file name
2. the shrink command will only shrink the file to the last used portion of the file so it is best to run this after the backup not before.

what to do now:
1. look at the code below; try running the commands in order, one at a time.
2. Post back and we can talk about backups
--try backing up the database (to a disk location with space
BACKUP DATABASE CARPEDATA TO DISK='c:\carpe.bak'

--truncate the log (YOU LOSE POINT IN TIME RECOVERY OF YOUR DATABASE BY DOING THIS)
BACKUP LOG CARPEDATA WITH TRUNCATE_ONLY

--shrink the log file
dbcc shrinkfile (carpedata_log,10)

Open in new window

0
 

Author Closing Comment

by:tmartin40
Comment Utility
Like you thought, the backup failed (not enough disk space) but I ran line 5 and 8 one at a time and it cleaned it right up.  I guess I need to employ a maintenance plan...shrinkfile restored over 200 GB of diskspace!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
If you use NetMotion Mobility on your PC and plan to upgrade to Windows 10, it may not work unless you take these steps.
After creating this article (http://www.experts-exchange.com/articles/23699/Setup-Mikrotik-routers-with-OSPF.html), I decided to make a video (no audio) to show you how to configure the routers and run some trace routes and pings between the 7 sites…
After creating this article (http://www.experts-exchange.com/articles/23699/Setup-Mikrotik-routers-with-OSPF.html), I decided to make a video (no audio) to show you how to configure the routers and run some trace routes and pings between the 7 sites…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now