Contents tagged with SQL Server 2008

  • Find stored procedures that reference a table column

    1 Comment

    We recently moved away from SQL Server replication and the database still has all of the rowguid columns and their associated indexes and constraints in it. We wanted to gain all of that disk space back so we went ahead with scripting out the delete of the indexes, constraints, and columns.

    One thing though is that there are many stored procedures in use and we needed to make sure none of these referenced the rowguid column.  The stored procedures really should not have used the rowguid column at all since it is solely created and used for SQL Replication, but you never know so we needed to confirm.

    The below script is what I created to search all of the stored procedures in the database for the rowguid column.  It could easily be modified to find any information within the stored procedures of a database.  (Note: One thing to watch out for is encrypted stored procedures.  This wouldn’t find anything in those so you would need to handle it through another method.)

    SELECT p.name, c.text FROM syscomments c
    JOIN sys.procedures p ON p.object_id=c.id
    WHERE c.text LIKE '%rowguid%'
    ORDER BY p.name

  • Lock request time out period exceeded

    6 Comments

    I was trying to drop a foreign key for a table I was working on and I ran into a time out exception from SQL Server Management Studio:

    image

    TITLE: Microsoft SQL Server Management Studio
    ------------------------------
    Drop failed for ForeignKey 'fk_MyForeignKey'.  (Microsoft.SqlServer.Smo)
    ------------------------------
    ADDITIONAL INFORMATION:
    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
    ------------------------------
    Lock request time out period exceeded. (Microsoft SQL Server, Error: 1222)
    ------------------------------
    BUTTONS: OK
    ------------------------------

    I also tried dropping the foreign key manually using:

    ALTER TABLE MyTable DROP CONSTRAINT fk_MyForeignKey

    But this time the query was just sitting there running and running.  I let it run for some time and then when I checked the currently executing requests I found it was sitting in a suspended state.  What was interesting about the request was that the wait_time equaled the total_elapsed_time, so it was just waiting there for something else before proceeding.

    This is the sql query I used to see the currently executing requests (one of which was mine):

    SELECT r.session_id, r.status, r.start_time, r.command, s.text,
    r.wait_time, r.cpu_time, r.total_elapsed_time, r.reads, r.writes, r.logical_reads, r.transaction_isolation_level
    ,r.*
    FROM sys.dm_exec_requests r
    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s

    And this in the particular row in question that made me realize my query was waiting on something else:

    image

    Now I needed to find out what is blocking my Alter Table command from running.  For that I used a query I found on this blog post Error 1222 Lock Request Time Out Period Exceeded When Set up Replication by Andrew Chen:

    select distinct object_name(a.rsc_objid), a.req_spid, b.loginame
    from master.dbo.syslockinfo a (nolock) join
    master.dbo.sysprocesses b (nolock) on a.req_spid=b.spid
    where object_name(a.rsc_objid) is not null

    I found that another SPID from SQL Server Management Studio was holding onto the table I was trying to alter.  Using sp_who2 with the SPID showed me the owner and where it was coming from, and also that it had been holding onto the table for 2 hours... and guess what!?!  It was me!  Smile

    I had been looking at the execution plan and client statistics of a query that I was performance tuning and that SQL Server Management window had a hold of the table I was trying to Alter.  As soon as I closed that window (and canceled that transaction) then I could drop the foreign key without a problem.

    Hopefully this will help someone else in the future!

  • SharePoint 2010 – SQL Server has an unsupported version 10.0.2531.0

    9 Comments

    I am trying to perform a database attach upgrade to SharePoint Foundation 2010.

    At this point I am trying to attach the content database to a Web application by using Windows Powershell:

    Mount-SPContentDatabase -Name <DatabaseName> -DatabaseServer <ServerName> -WebApplication <URL> [-Updateuserexperience]

    I am following the directions from this TechNet article: Attach databases and upgrade to SharePoint Foundation 2010.  When I go to mount the content database I am receiving this error:

    Mount-SPContentDatabase : Could not connect to [DATABASE_SERVER] using integrated security: SQL server at [DATABASE_SERVER] has an unsupported version 10.0.2531.0. Please refer to “http://go.microsoft.com/fwlink/?LinkId=165761” for information on the minimum required SQL Server versions and how to download them.

    At first this did not make sense because the default SharePoint Foundation 2010 website was running just fine.  But then I realized that the default SharePoint Foundation site runs off of SQL Server Express and that I had just installed SQL Server Web Edition (since the database is greater than 4GB) and restored the database to this version of SQL Server.

    Checking the documentation link above I see that SharePoint Server 2010 requires a 64-bit edition of SQL Server with the minimum required SQL Server versions as follows:

  • SQL Server 2008 Express Edition Service Pack 1, version number 10.0.2531
  • SQL Server 2005 Service Pack 3 cumulative update package 3, version number 9.00.4220.00
  • SQL Server 2008 Service Pack 1 cumulative update package 2, version number 10.00.2714.00

    The version of SQL Server 2008 Web Edition with Service Pack 1 (the version I installed on this machine) is 10.0.2531.0.

    SELECT @@VERSION:
    Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)   Mar 29 2009 10:11:52   Copyright (c) 1988-2008 Microsoft Corporation  Web Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) (VM)

    But I had to read the article several times since the minimum version number for SQL Server Express is 10.0.2531.0.  At first I thought I was good with the version of SQL Server 2008 Web that I had installed, also 10.0.2531.0.  But then I read further to see that there is a cumulative update (hotfix) for SQL Server 2008 SP1 (NOT the Express edition) that is required for SharePoint 2010 and will bump the version number to 10.0.2714.00.

    So the solution was to install the Cumulative update package 2 for SQL Server 2008 Service Pack 1 on my SQL Server 2008 Web Edition to allow SharePoint 2010 to work with SQL Server 2008 (other than the SQL Server 2008 Express version).

    SELECT @@VERSION (After installing Cumulative update package 2):
    Microsoft SQL Server 2008 (SP1) - 10.0.2714.0 (X64)   May 14 2009 16:08:52   Copyright (c) 1988-2008 Microsoft Corporation  Web Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) (VM)