Sql-server – Add column taking too long

sql serversql-server-2008

I'm trying to add a column to a database. The query has been running for 25mins and it's locking web access to the table and breaking our website:

alter table MyTable
add MyNewColumn varchar(max) not null
default ('')

The table contains binary data in a different column and is quite large.

Will cancelling by using the red "Cancel Executing Query" button cause additional problems? I'm just trying to figure out if I should attempt to cancel the query at this point, and what will happen since it's been running for so long.

Best Answer

The amount of time it takes to add a new column depends on the amount of data in your table. If you run sp_who2 active to see what the process id is you can kill the job. It will go into rollback, but will put the table back to the way that it was.

You should never try altering a table of a production system without knowing the how long the process will take.

Related Solutions

Sql-server – How long can the log reader agent run for before a restart will do it some good

I think it depends on your environment and how busy it is but I support quite a few environments that are very stingy with their patches because they have incredibly low surface areas and end up with reboots/SQL restarts pretty infrequently (a few times a year) and the log reader agent isn't restarted outside of this schedule and there are no issues.

If your environment is high transaction and you are experiencing issues with the cleanup jobs on distribution and are running a local distributor you may see a benefit from restarting but, in general, I have found no need to worry about restarting the log reader agent.

I don't believe Microsoft has any particular guidance so this is a YMMV type of question but I think you'll be fine to not restart it outside of your normal patching and reboot/restart policy. As long as you monitor performance and look for issues and symptoms with your replication environment you'll be fine IMHO.

Sql-server – Pivot column query

The following will work with some assumptions:

statusreciept will be 0 or 1- if other values are possible and you want EACH prior location concatenated in the "Prior location" field, that is also doable, but requires a more robust solution.

that createddatetime is unique for all sets you want to track

     ;WITH    ITR
                  AS ( SELECT   CREATEDDATETIME ,
                                CREATEDBY ,
                                INVENTID ,
                                ITO.REFERENCECATEGORY ,
                                IT.STATUSRECEIPT
                       FROM     dbo.TRANS IT
                                INNER JOIN TRANSORIGIN ITO ON IT.TRANSORIGIN = ITO.RECID
                                                                    AND ITO.REFCATEGORY IN (
                                                                    2, 6 )
                       WHERE    CREATEDDATETIME > DATEADD(MONTH, -1, GETDATE())
                     ),
              DS AS
    (
            SELECT  ITR.CREATEDDATETIME ,
                    ITR.CREATEDBY AS [User] ,
                    YPT.PALLETNUMBER AS PalletNumber ,
                    ID.LOCATIONID AS [Current Location] ,
                    ID.PALLETID AS Pallet ,
                    WOT.DEPOTNAME AS Depot ,
                    ITR.REFERENCECATEGORY ,
                    ITR.STATUSRECEIPT

            FROM    dbo.PALLETTABLE AS YPT
                    INNER JOIN dbo.PALLET AS WP ON YPT.PALLETID = WP.PALLETID
                    LEFT OUTER JOIN dbo.DIM AS ID ON WP.PALLETID = ID.PALLETID
                                                           AND ID.BATCHID <> ''
                    LEFT OUTER JOIN dbo.ORDER AS WOT ON ID.DIMID = WOT.DIMID
                    LEFT OUTER JOIN ITR ON ID.DIMID = ITR.DIMID
                    ORDER BY WP.PALLETNUMBER),
   msr AS
(   SELECT createddatetime, max(statusreciept) statusreciept
FROM ds
GROUP BY createddatetime)



SELECT ds.createddatetime,
       ds.user,
       ds.palletnumber,
       ds.[current location],
       ds2.[current location] as [Previous Location],
       ds.pallet,
       ds.depot,
       ds.reference_category,
       ds.statusreciept AS statusreciept
FROM ds
LEFT JOIN ds ds2 
   ON ds2.createddatetime = ds.createddatetime
   AND ds2.statusreciept = ds.statusreciept - 1
INNER JOIN msr 
   ON msr.createddatetime = ds.createddatetime
   AND msr.statusreciept = ds.statusreciept
GROUP BY ds.createddatetime,
       ds.user,
       ds.palletnumber,
       ds.[current location],
       ds2.[current location],
       ds.pallet,
       ds.depot,
       ds.reference_category

Please let me know if either of the assumptions are incorrect or you need a different implementation.

Best Answer

Related Solutions

Sql-server – How long can the log reader agent run for before a restart will do it some good

Sql-server – Pivot column query

Related Question