Wednesday, March 21, 2012

DB Shrink - Could not adjust the space allocation for file Error

About once a month I receive the follow error from my weekly DB Shrink job.
I have always run a DBCheck afterwards and the database is clean. I have
searched the web and MS but have been unable to find any detailed information
on this error message. Any help / comments would be greatly appreciated.
Message:
Executed as user: xDOMAIN\xxxx Could not adjust the space allocation for
file 'x_Data'. [SQLSTATE 42000] (Error 3140) DBCC execution completed. If
DBCC printed error messages, contact your system administrator. [SQLSTATE
01000] (Error 2528). The step failed.
Server:
SQL 2000 SP3a
JKLI can't help with the error, but I can still give you a bit of good
advice. Stop shrinking every week, or at any other regular interval.
Set the database size to what it needs to be so that there is no need
for automatic growth. Monitor the space and add more before it runs
out. Add enough that you will not have to add more any time soon.
Constant shrinking and growth is bad for performance. Automatic space
allocation always causes a delay. The physical files get fragmented
on the hard drive.
Roy Harvey
Beacon Falls, CT
On Thu, 1 Nov 2007 08:20:03 -0700, JKL <JKL@.discussions.microsoft.com>
wrote:
>About once a month I receive the follow error from my weekly DB Shrink job.
>I have always run a DBCheck afterwards and the database is clean. I have
>searched the web and MS but have been unable to find any detailed information
>on this error message. Any help / comments would be greatly appreciated.
>Message:
>Executed as user: xDOMAIN\xxxx Could not adjust the space allocation for
>file 'x_Data'. [SQLSTATE 42000] (Error 3140) DBCC execution completed. If
>DBCC printed error messages, contact your system administrator. [SQLSTATE
>01000] (Error 2528). The step failed.
>
>Server:
>SQL 2000 SP3a
>JKL|||DO NOT SHRINK SQL SERVER DATABASES OR LOGS!! :-)
See http://www.karaszi.com/SQLServer/info_dont_shrink.asp and search web for
other reasons not to do this.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"JKL" <JKL@.discussions.microsoft.com> wrote in message
news:3450C0F0-3622-4512-BE2D-AC338AD935FF@.microsoft.com...
> About once a month I receive the follow error from my weekly DB Shrink
> job.
> I have always run a DBCheck afterwards and the database is clean. I have
> searched the web and MS but have been unable to find any detailed
> information
> on this error message. Any help / comments would be greatly appreciated.
> Message:
> Executed as user: xDOMAIN\xxxx Could not adjust the space allocation for
> file 'x_Data'. [SQLSTATE 42000] (Error 3140) DBCC execution completed. If
> DBCC printed error messages, contact your system administrator. [SQLSTATE
> 01000] (Error 2528). The step failed.
>
> Server:
> SQL 2000 SP3a
> JKL|||Why are you running a weekly shrink operation in the first place? If it
needed to be that big last week and the week before and the week before
don't you think it will need to be that big next week and the week after?
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Not sure what this error was but have you run a disk diagnostic lately?
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"JKL" <JKL@.discussions.microsoft.com> wrote in message
news:3450C0F0-3622-4512-BE2D-AC338AD935FF@.microsoft.com...
> About once a month I receive the follow error from my weekly DB Shrink
> job.
> I have always run a DBCheck afterwards and the database is clean. I have
> searched the web and MS but have been unable to find any detailed
> information
> on this error message. Any help / comments would be greatly appreciated.
> Message:
> Executed as user: xDOMAIN\xxxx Could not adjust the space allocation for
> file 'x_Data'. [SQLSTATE 42000] (Error 3140) DBCC execution completed. If
> DBCC printed error messages, contact your system administrator. [SQLSTATE
> 01000] (Error 2528). The step failed.
>
> Server:
> SQL 2000 SP3a
> JKL

No comments:

Post a Comment