DBCC INPUTBUFFER being blocked by MSAccess

Question

DBCC INPUTBUFFER being blocked by MSAccess

growl

Hall of Fame

Points: 3067
More actions
August 18, 2004 at 5:50 pm

#86327

Hi I am running the below script on a database every night. Some users are connecting using
MS access 🙁 . For some reason they decide to leave this open and running. Doing this will create a
block and prevent dbcc indexdefrag from running? To my knowledge dbcc indexdefrag is non intrusive and
should not be affected by other users accessing the table while it is doing index defrag?
Has anyone had a similar situation or I am doing something wrong with the script below?
Thanks

Declare @databasename varchar(50)
DECLARE @TableName sysname
DECLARE @indid int
set @databasename = 'my database name'
DECLARE cur_tblfetch CURSOR FOR
SELECT table_name FROM information_schema.tables WHERE table_type = 'base table'
OPEN cur_tblfetch
FETCH NEXT FROM cur_tblfetch INTO @TableName
WHILE @@FETCH_STATUS = 0
--select * from sysindexes where name = 'db_maintenanceHistory'
BEGIN
DECLARE cur_indfetch CURSOR FOR
SELECT indid FROM sysindexes WHERE id = OBJECT_ID (@TableName) and keycnt > 0
OPEN cur_indfetch
FETCH NEXT FROM cur_indfetch INTO @indid
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT 'Derfagmenting index_id = ' + convert(char(3), @indid) + 'of the '
+ rtrim(@TableName) + ' table'
IF @indid <> 255 DBCC INDEXDEFRAG (@databasename, @TableName, @indid)
FETCH NEXT FROM cur_indfetch INTO @indid
END
CLOSE cur_indfetch
DEALLOCATE cur_indfetch
FETCH NEXT FROM cur_tblfetch INTO @TableName
END
CLOSE cur_tblfetch
DEALLOCATE cur_tblfetch

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 1

do you mean dbcc indexdefrag? Access can grab a large number of locks and I suspect, block the index defrag from running. DBCC inputbuffer returns the last sql batch (or a portion of it) that a particular spid was running.

growl Hall of Fame Points: 3067 More actions · Answer 2

Thanks Steve.

I have dbcc inputbuffer and fn_get_sql () on the brain!

I meant dbcc indexdefrag

growl Hall of Fame Points: 3067 More actions · Answer 3

Thanks Steve.

I have dbcc inputbuffer and fn_get_sql () on the brain!

I meant dbcc indexdefrag

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 4

sp_who2 active will tell you whether your dbcc indexdefrag is blocked when user access your database with ACCESS.

And sp_lock will give you which table is being locked and what type of lock has been placed to the table.

Alamir SSCertifiable Points: 6085 More actions · Answer 5

I think you have to make this indexdefrag after working hourse .. and to kill users who are connecting bewfore you do this (there is a script to make this .. search for it)

about the locking Problem between Access and SQL server 2000 or 7, read this carefully :

When you want a report which based on complicated queries joined from several tables, These tables are updated frequently, then you lock these tables (or you will wait for previous transaction lock on these tables to be completed to put your locks on them) .. so all other poeple using these tables are locked too (so hang appened - because they are in the queue of lock of these tables)!!

if you use a query in Access and make a report based on it, and in that query "properties" you choose "No locks" in "Record locks", the query will lock some tables or pages (so although you choose "No locks", it still makes locks!! -the lock type is IS lock- .. because it is a bug in Access with SQL Server)

the best thing is to run the query on the server (by making Pass-Through Query in Access or View on SQl Server ) .. and with each table name in the query, add this statement "with (nolock)" .. so you read data as it is (not recently updated = not commited transaction), and you don't wait for locks to be free and you don't lock these tables, and any other transaction will complete successfully (for you and others) .

you will write something like this in your Pass-Through Query in Access:

select Invoices.*, Situation.*

from Situation with (nolock) INNER JOIN Invoices with (nolock)

ON Situation.SituationID = Invoices.Situation

where Situation.SituationID =1

Disadv:

- when using "with (nolock)", you will not seen comitted transaction (changed one) at this moment ... but you can see it at any other time.

Adv:

- no hang at all

- very fast Response

- Little summary for Locks in SQL Log file.

also : you may need to add more Memory (RAM) when server still hangs a little after transfering your queries to path-through queries.... becuase every transaction or query is done on server first, then processed data is passed to your PC.

ALSO:

try to put the DATA and the LOG file of SQL Database on a partition that is Formatted with 32 K or 64 K Cluster Size (not the defalut = 4 K)

because the default Page size in SQL in memory is 8K , And If u use the defulat Cluster Size while formating partioin, then to get 1 page (8K), you need 2 hits for the HardDisk (4 K each)

But If u use 32 K Cluster Size while formatiing partioin, then on 1 hit for the HardDisk (32 K each) , you get 4 pages into Memory (more faster ... because the slowest thing that slow the whole system is to read/write from Hard Disk)

I use 32 K not 64 K.. because with 64 K you may have a lot of empty pages in memory ( lost in memeory and your DB may be graw more)

this of course will help you to solve the problem.

Alamir Mohamed
Alamir_mohamed@yahoo.com