Solved

DBCC Shrinkfile

Posted on 2010-09-22
3
781 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 33738374
backup log with truncate_only
0
 
LVL 15

Accepted Solution

by:
mcmonap earned 500 total points
ID: 33738433
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
ID: 33738943
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Let’s list some of the technologies that enable smooth teleworking. 
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…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

623 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