Forum Replies Created

Viewing 15 posts - 45,601 through 45,615 (of 49,552 total)

  • RE: sysprocesses and blocking

    The query being run by the processes is in the st.text column, and it's almost the same as what fn_getSQLHandle would return.

    I've used this query for years to track blocking...

    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

    July 18, 2008 at 1:04 am

    #844821

  • RE: sysprocesses and blocking

    roadtrain64 (7/18/2008)

    WEll thanks Gail

    But

    I want to capture blocking_session_id's commands and from where these commands come from?

    Did you run my code? The query been run is...

    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

    July 18, 2008 at 12:54 am

    #844817

  • RE: query taking too much time

    Index definitions and execution plan please?

    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

    July 18, 2008 at 12:50 am

    #844815

  • RE: query taking too much time

    Can you post the query please, the table definition, the index definitions and the execution plan?

    You get the exec plan by running the query in management studio with the Include...

    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

    July 18, 2008 at 12:36 am

    #844810

  • RE: Transaction looking Table

    The following form of code will usually deadlock if run enough times.

    Connection 1:

    WHILE (1=1)

    BEGIN

    BEGIN TRANSACTION

    update Table1 SET SomeNumberField = SomeNumberField +1

    WAITFOR DELAY '00:00:30

    UPDATE...

    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

    July 18, 2008 at 12:25 am

    #844806

  • RE: SQL Server does not allow remote connections

    LISETH_T (7/15/2008)

    'Call a function to create the sqlDatareader

    objDataReader = getReader("Select *...

    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

    July 18, 2008 at 12:20 am

    #844803

  • RE: sysprocesses and blocking

    For SQL 2005, try this one. Create a table somewhere then have a SQL agent job doing the insert at whatever interval you like

    SELECT er.session_id, blocking_session_id, wait_type, wait_time, wait_resource, database_id,...

    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

    July 18, 2008 at 12:15 am

    #844800

  • RE: SQL Server does not allow remote connections

    That error's the generic 'I can't connect' error. It could be because the server doesn't allow remote connections. It could be that the service is stopped, or there could be...

    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

    July 18, 2008 at 12:13 am

    #844797

  • RE: Transaction looking Table

    Catcha (7/17/2008)

    Will above while loop causes deadlock?

    Not on its own. Deadlocks require two connections wanting locks that the other connection holds.

    It is a really, really inefficient way to do...

    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

    July 18, 2008 at 12:10 am

    #844796

  • RE: Which plan is the most efficient one?

    Unsure (7/17/2008)

    Thank you.

    I will not use 'forceplan'. Will work with just the covering index.

    Thanks again.

    If you want any suggestions on improving the query more, please post the query, along with...

    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

    July 18, 2008 at 12:02 am

    #844794

  • RE: Which plan is the most efficient one?

    Shamshad Ali (7/17/2008)

    And what about the style for selecting data between start and end?

    ----------------------------------------------------

    AND 7/14/2008 BETWEEN s.start_date AND s.end_date

    Please start a separate thread for your questions.

    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

    July 17, 2008 at 11:58 pm

    #844793

  • RE: Restoring differential backup on other server

    All of the backups, other than the last transaction log backup must be restored WITH NORECOVERY. Once any backup has been restored WITH RECOVERY, no more backups can be applied...

    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

    July 17, 2008 at 11:57 pm

    #844792

  • RE: Question specific to Table/Lazy Spool

    It might be a concern, it might not. Without the query, that's about all that can be said.

    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

    July 17, 2008 at 11:54 pm

    #844790

  • RE: Restore package from msdb back-up copy

    Provided its from the same version of SQL, it can be restored. See the above link for details on how.

    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

    July 17, 2008 at 11:53 pm

    #844789

  • RE: Log File Full

    angie stein (7/17/2008)

    Actually, CAN I do a shrinkfile in a mirrored environment? I was told by our contractor that you should never truncate the logs since you don't know...

    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

    July 17, 2008 at 11:50 pm

    #844787

Viewing 15 posts - 45,601 through 45,615 (of 49,552 total)