December 15, 2008 at 10:47 am
hi,
We have a maintenance job which runs weekly, in that Database maintenance plan in Optimizations tab 2nd option 'change free space for page to percentage to' is selected as 10%.This job has failed and the error message is
Database ABC: Removing unused space from the database files (if database size is more than 50 MB). Reducing free space to 10 percent of data...[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3140: [Microsoft][ODBC SQL Server Driver][SQL Server]Could not adjust the space allocation for file 'ABC_DAT'.
[Microsoft][ODBC SQL Server Driver][SQL Server]DBCC execution completed. If DBCC printed error messages, contact your system administrator.
As soon as this job failed, we got a dead lock as below:
Deadlock encountered .... Printing deadlock information
2008-12-14 01:06:11.43 spid4
2008-12-14 01:06:11.43 spid4 Wait-for graph
2008-12-14 01:06:11.43 spid4
2008-12-14 01:06:11.43 spid4 Node:1
2008-12-14 01:06:11.46 spid4 KEY: 5:2:1 (c6007c8f1797) CleanCnt:2 Mode: U Flags: 0x0
2008-12-14 01:06:11.46 spid4 Grant List 0::
2008-12-14 01:06:11.46 spid4 Owner:0x787506c0 Mode: S Flg:0x0 Ref:1 Life:00000000 SPID:8 ECID:0
2008-12-14 01:06:11.46 spid4 SPID: 8 ECID: 0 Statement Type: UNKNOWN TOKEN Line #: 0
2008-12-14 01:06:11.46 spid4 Input Buf: No Event:
2008-12-14 01:06:11.46 spid4 Grant List 2::
2008-12-14 01:06:11.46 spid4 Requested By:
2008-12-14 01:06:11.46 spid4 ResType:LockOwner Stype:'OR' Mode: X SPID:56 ECID:0 Ec:(0x2D807520) Value:0x6e2a62a0 Cost:(0/128C)
2008-12-14 01:06:11.46 spid4
2008-12-14 01:06:11.46 spid4 Node:2
2008-12-14 01:06:11.46 spid4 TAB: 5:962102468 [] CleanCnt:2 Mode: Schema-Mod Flags: 0x0
2008-12-14 01:06:11.46 spid4 Grant List 2::
2008-12-14 01:06:11.46 spid4 Owner:0x6e2a6600 Mode: Schema-Mod Flg:0x0 Ref:1 Life:00000000 SPID:56 ECID:0
2008-12-14 01:06:11.46 spid4 SPID: 56 ECID: 0 Statement Type: DBCC Line #: 1
2008-12-14 01:06:11.46 spid4 Input Buf: Language Event: dbcc dbreindex(N'[ABC].[AUTPOL]', N'', 90)
2008-12-14 01:06:11.46 spid4 Requested By:
2008-12-14 01:06:11.46 spid4 ResType:LockOwner Stype:'OR' Mode: IX SPID:8 ECID:0 Ec:(0x42E363F0) Value:0x78751f80 Cost:(1/0)
2008-12-14 01:06:11.46 spid4 Victim Resource Owner:
2008-12-14 01:06:11.46 spid4 ResType:LockOwner Stype:'OR' Mode: IX SPID:8 ECID:0 Ec:(0x42E363F0) Value:0x78751f80 Cost:(1/0)
2008-12-14 01:06:11.54 spid8 Transaction (Process ID 8) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
from the experts from this forum ,I removed the autogrowth option couple of days ago and increased available the datafile sizes to 15%.
Plz advice me
thank you
December 15, 2008 at 11:42 am
Why would you want to free up space by getting rid of data? As there are processes running on the database that need space to run
Thanks!!
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
December 15, 2008 at 11:59 am
If I change the option to reorganize pages with original amount of free space in optimization tab in maintenance plan will solve my problem?
Thanks in advance
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply