We run via the Enterprise Manager (sql server 2000) a Shrinkdatabase job via the SQL Server Agent with the following statements:

DBCC Shrinkdatabase  (xxx, 20)
DBCC Shrinkdatabase  (tempdb, 10)

The last couple of days I frequently got the following failed-message:

Executed as user: xxx. Could not adjust the space allocation for file 'xxx'.
[SQLSTATE 42000] (Error 3140)  DBCC execution completed. If DBCC printed error messages, contact your system administrator.
[SQLSTATE 01000] (Error 2528)  Associated statement is not prepared [SQLSTATE HY007]
(Error 0)  Cannot shrink log file 2 (templog) because requested size (7256KB)
is larger than the start of the last logical log file. [SQLSTATE 01000]
(Message 9007)  DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(Message 0).  The step failed.

Can someone explain to me what's the problem we currently have?

Go to BOL, Shrinking the Transaction Log:
mk:@MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\architec.chm::/8_ar_da2_1uzr.htm

Change your DBCC statement to:

DBCC SHRINKDATABASE (tempdb, 10) WITH NO_INFOMSGS

Andy

Hi Andy,

What's the intention of the command "WITH NO_INFOMSGS", because I tried to find out via the SQL Server Books Online what this command means but with NO result.

Please provide me with some explanations.

Kind regards,

Irma

We walk in the dark places no others will enter
We stand on the bridge and no one may pass