Issue with maintenance

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

bidzis - 27 Jul 2008 23:32 GMT

Hi,

I have SQL Server 2000 installation used by ProjectServer 2003. I used to
truncate logs and shrink database every month, but last two months I didn't
and the log grow to almost 3 Gigs. The problem is the commands:

BACKUP LOG @database WITH TRUNCATE_ONLY
DBCC SHRINKDATABASE (@database)

this time doesn't work. I don't know, some not closed transaction ? When I
run the script it works endlessly. The same when try to reindex database (it
was always successfull so far...).

I have tried to backup database and it still has over 3 Gigs (previously
used to have about 800MB after shrinking)

There is some problem with updates in Project Server caused by this, I
think. Does anybody know how to fix this ?

thx,
bidzis

Reply to this Message

Andrew J. Kelly - 28 Jul 2008 00:05 GMT

First off you should not be shrinking the files as that will pretty much
undo any work you did with the reindex in the first place. And backing up
the log with Truncate_Only makes the log useless so why not put the
database in Simple recovery mode and you won't have to worry about backing
up the log. You most likely have a long running open transaction of which
you can see if you run DBCC OPENTRAN() in that db context. But have a look
at these for more details:

http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
considerations
http://www.nigelrivett.net/TransactionLogFileGrows_1.html Log File issues
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up

Signature

Andrew J. Kelly SQL MVP
Solid Quality Mentors

> Hi,
>
[quoted text clipped - 17 lines]
> thx,
> bidzis

Reply to this Message