backup log databasename with truncate_only doesn't work

[cathyg18]

I have a database that is about twice as big as it should be. Trying to shrink it results in error message tx log is full, backup tx log and try again. The backup log command takes maybe a second, returns a message that the task is complete, and I have the same size everything, data files and log files.

I'm positive that the database is not actually as big as it says it is.

Any ideas for me?

Thanks in advance,

Cathy

 

[mrdenny]

In Enterprise Manager select the database, right click on it and select view then taskpad. Give it a minute to create the screen. At the bottom with be a slide bar for each database file and for the transaction log file. It will tell you how much data is in each file, and how large each file is.

Is the drive that these files are on full?

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[SQLBill]

1. The error tells you the log file is full. This means that if 10 GB is assigned, then 10 GB is used (or so much is used that it can't put anything more into it).

2. Truncate removes data from the log file. In other words, it frees up space...but it does NOT remove space. If your log file is 10GB before the truncate, it's 10GB after the truncate. The difference is that before truncating, the 10GB is full. After truncating, the 10GB might be 4 GB full and 6 GB empty.

-SQLBill

Posting advice: FAQ481-4875

 

[SQLBill]

Another way to look at it....

consider a 12 ounce glass. You fill it with water until it has 12 ounces of water. Now you get a message that the glass is full and you need to drink some to free up space. You drink 6 ounces. Is the GLASS smaller? No, there's just more room and you can now put more water into the glass.

-SQLBill

Posting advice: FAQ481-4875

 

[cathyg18]

Thanks for your comments.

MrDenny, I checked as you suggested, and the slide bar for the database shows approx 28 gig used with approx 29 gig free. For the tx log it show approx 137 meg used with approx 8 gig free. The drive itself has only 2 gig free. So I seem to be in a pickle. Any ideas how to free up that space?

SQLBill,

How can I pour those remaining 6 ounces of water into a six or eight ounce glass? I seem to have the right size glass in my cupboard, if only I could get that pesky water into one.

Thanks for your help.

 

[SQLBill]

You can run DBCC SHRINKFILE on both the database and log files. It might not immediately free up the log files, though.

Are you doing FULL backups and Transaction log backups? Reason I ask is that transaction log backups will help keep the TLog at a managable size.

-SQLBill

Posting advice: FAQ481-4875