Solved

DBCC Shrinkfile

Posted on 2010-09-22
3
778 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
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql query help 2 57
Getting max record but maybe not use Group BY 2 32
Addition to SQL for dynamic fields 6 47
What is this datetime? 1 18
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Juniper VPN devices are a popular alternative to using Cisco products. Last year I needed to set up an international site-to-site VPN over the Internet, but the client had high security requirements -- FIPS 140. What and Why of FIPS 140 Federa…
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…

765 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