Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Shrinkdatabase job running via the SQL Server Agent Expand / Collapse
Author
Message
Posted Wednesday, June 22, 2005 7:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 02, 2005 1:31 AM
Points: 2, Visits: 1

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?

Post #192940
Posted Thursday, June 23, 2005 1:46 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, August 27, 2013 8:59 PM
Points: 631, Visits: 233

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




Post #193339
Posted Thursday, June 23, 2005 3:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 02, 2005 1:31 AM
Points: 2, Visits: 1

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

Post #193377
Posted Thursday, June 23, 2005 11:47 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, November 22, 2013 11:05 AM
Points: 3,108, Visits: 2,116
Search this website for 'shrink transaction log' ... there are some interesting threads. The 'WITH' thing does not have to do with your problem. It determinese whether ot not messages are displayed). Transaction logs are 'circular' files so to speak and you cannot shrink them beyond their initial allocation or as far down the chain as have been allocated. Sometimes shrinking a transactino log is a repetitive process (hence the suggestion to search this site, there are a few solutions available).



Regards
Rudy Komacsar
Senior Database Administrator

"Ave Caesar! - Morituri te salutamus."
Post #193624
Posted Monday, September 19, 2011 2:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 11, 2013 11:22 PM
Points: 2, Visits: 56
Irma Martis (6/22/2005)


We run via the Enterprise Manager (sql server 2000) a Shrinkdatabase job via the SQL Server Agentwith 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?
Post #1177119
Posted Monday, September 19, 2011 3:56 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 12:20 PM
Points: 41,529, Visits: 34,445
Please note: 6 year old thread


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

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

Post #1177148
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse