SQL Training Q&A - 4
Q) How do you know if an instance of SQL Server 2005 has been upgraded to SP1 and what do you do to assure all SQL Server Services are shut down prior to installing the service pack? I attempted to install SP1 and got some lock violations and kept shutting down all services I could think of under Computer Management? It looks like I should have run sqlcmd and did a shutdown with nowait command. Is that how they do it in the field? I am not sure SP1 actually installed even though it said it finished okay. Nowhere do I see SP1 on anything.
When installing a Service Pack or a patch, if you need to update or a component that's in memory, you might be required to either stop that component before the update or to restart the computer after the apdate is completed.
Please be aware that if you update a component that is running and the update software informs you that you need to restart, you won't be running the new component until you do restart. If you updated that component to fix a security issue, you will be vulnerable until you restart and load the updated component.
To check what version of SQL you're running and to check what Service Pack is applied, you should query the version number using SELECT @@VERSION. For SQL Server 2005 SP1, you should see version 2005.90.2047. You can also query specific server properties by using SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition').
Check this article for details: http://support.microsoft.com/?id=321185