May 19, 2011 at 12:22 pm
Is it possible to find the pending ALTER TABLE commands issued? For the options that require a reboot like changing file names and locations.
I think I've got a couple queued up and waiting for a restart but I've not been able to squeeze this in for several months. (Hey, my uptimes are looking good at least).
I think (hope) that I have the actual code stored so I can find exactly what the new file names and locations but just in case. Since this is waiting for a restart, it seems it must be stored somewhere.
Any pointers to where to look?
Thanks!
Norman
May 19, 2011 at 12:45 pm
n.heyen (5/19/2011)
Is it possible to find the pending ALTER TABLE commands issued? For the options that require a reboot like changing file names and locations.
I'm not sure what you mean by queued and/or pending?
Do you mean an Alter that is in progress or or one that is waiting for a job to execute, etc?
How are the DDL commands being executed?
Do yo have other people making schema changes, file name, etc? If so I would closely coordinate with them.
I would execute the commands and watch very closely.
If an ALTER TABLE is in progress you can check for locks. You can also check the processes and EXEC DBCC INPUT_BUFFER to find the current SQL Command being executed by the SPID or take advantage of the newer DMV's.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 19, 2011 at 12:53 pm
Alter table statements are never pending, only running or complete.
The only alter database that needs a restart of SQL (not reboot of the server) is an alter database ... modify file .. move. With TempDB SQL recreates the files in the new location on restart, with user databases a restart isn't even necessary, just offline/online and the DB will look for its files in the new place.
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
May 19, 2011 at 2:37 pm
Thank you for the answer, you are correct. I meant to ask about ALTER DATABASE. It has been one of those weeks...
I didn't know that if I take the DB offline and back again the modify will complete. That is certainly good news, I can do that during off hours without having to negotiate with all the other database users.
Can I go home now? I learned something new! 😀
Norman
May 19, 2011 at 11:34 pm
n.heyen (5/19/2011)
I didn't know that if I take the DB offline and back again the modify will complete. That is certainly good news, I can do that during off hours without having to negotiate with all the other database users.
Just note that for TempDB SQL will move the files (actually it will just recreate them in the new location). With user databases, you have to move the files while the DB is offline. Otherwise it won't come online afterwards (catalog says new location, but files not there)
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply