Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Shrinkdatabase job running via the SQL Server Agent


Shrinkdatabase job running via the SQL Server Agent

Author
Message
Irma Martis
Irma Martis
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 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?


David A. Long
David A. Long
SSChasing Mays
SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)

Group: General Forum Members
Points: 643 Visits: 237

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





Irma Martis
Irma Martis
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 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


Rudyx - the Doctor
Rudyx - the Doctor
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3290 Visits: 2476
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."
shashikantrana
shashikantrana
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 58
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?

GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47241 Visits: 44377
Please note: 6 year old thread


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search