Trouble shrinking DB: all logical files are in use

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

High Availability (2005)

Trouble shrinking DB: all logical files are in use

Author

Topic

im1dermike
Posting Yak Master

222 Posts

Posted - 2008-10-15 : 10:24:08

Hi. I've created a SQL Server Agent job to backup a database. It runs at 12:00 am every day. I have another job that backs up transaction logs every hour on the hour from 7:00 am to 7:00 pm. The database backup job is not working. It seems to fail when I try to shrink the database which I do before I make the backup. I do it using the following code:

--Shrink Database
USE [AIMS_CAPS_FINANCE_MGS_PAYMENTS_LATEST]
GO
DBCC SHRINKDATABASE(N'AIMS_CAPS_FINANCE_MGS_PAYMENTS_LATEST', 10, TRUNCATEONLY)

I get the following error when I run it:
Cannot shrink log file 2 (AIMS_CAPS_FINANCE_MGS_PAYMENTS_log) because all logical log files are in use.

No one should be connected to the database and when I run DBCC OPENTRAN it says there are "No active open transactions."

Any ideas?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-10-15 : 10:38:14

Why are you shrinking your DB? Your are causing unnecessary performance issues as it needs to grow again.

im1dermike
Posting Yak Master

222 Posts

Posted - 2008-10-15 : 10:49:21

I'm backing up the DB for emergency purposes. I'm shrinking it so it to reduce the amount of HD space it takes up.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-10-15 : 12:23:54

There is no problem with backing up your database. But read this why you shouldn't shrink db

http://www.karaszi.com/SQLServer/info_dont_shrink.asp

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-10-18 : 14:40:20

If you want to decrease the size prior to a backup , why not look at something like a BACKUP LOG solution

Jack Vamvas
--------------------
http://www.ITjobfeed.com

saurabhsrivastava
Posting Yak Master

216 Posts

Posted - 2008-11-05 : 19:18:00

Just shrink data/log files using shrinkfile instead of using shrinkdatabase.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources