Post reply

sql server 2005 blocking locks

  • June 3, 2009 at 9:28 am

    #130925

    Dear Sql Experts,

    I am new to sql server. we are using sql server 2005 .How can i identify which sessions are having blocking locks and how to kill the blocking session.Is there any script like oracle to find blocking locks in the database and to kill it. I have many the shared locks in my database(production).please anybody know how to kill the blocking sessions.Also I have saw the disk is currupt or unusable plesae run chkdsk utility in the event log.

    Also in my database maintence plan is running daily.In that the following task are running daily as a job

    1.check db

    2.shrink db if it exceeds 50mb

    3.reoganise all tables/indexes

    4rebuild all tables/indexes

    5.clear old history

    the database is in simple recovery model.Also autoshrink option is disabled.

    please let me how to release all shared locks and Also is it advisable to run the job daily or weekly?

    How to confirm there is no lock in the database?

    please share your ideas

    Thanks in Advance

    Vijay.S

  • June 3, 2009 at 9:51 am

    #1004147

    I would personally start with why you are getting blocking...?

    1. What hardware does the SQL server run on and is it enough?

    2. Check for "missing" indexes. The script attached uses dmv's that are collect since the last reboot. Not all indexes need to be created, so just use it as a guide.

    3. Do you update statistics?

    4. Why do you shrink the data files?

    JL

  • June 4, 2009 at 4:52 am

    #1004513

    Hi

    1. What hardware does the SQL server run on and is it enough?

    intel(R) Xeon(R) cpu 2.33ghz 32gb ram windows server 2003 sp2

    2. Check for "missing" indexes. The script attached uses dmv's that are collect since the last reboot. Not all indexes need to be created, so just use it as a guide.

    Actually I have a doubt, whenever server or sqlserver is restarted,is it necesserary to recreate the indexes?what happen to the index when we restart sql server at that stage?

    3. Do you update statistics?

    As i daily rotuine we are updating statistics after rebuilding the indexes/tables.

    4. Why do you shrink the data files?

    I can avoid this as per your advice.

    thanks

    vijay.s

Viewing 3 posts - 1 through 2 (of 2 total)

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