Useful Undocumented Extended Sps

First post after launching my blog..

Now its lot more exciting ( and a bit scary as well :) ) to post..

Thanks to all the people for reading my blog and please keep reading :)

There are quite a few undocumented extended sps in SQL 2k/2k5. Just thought of discussing couple of useful ones..

EXEC MASTER..Xp_fixeddrives

Returns the free space on all hard disks on the Server running SQL Server.

If you are always running short of space then this one can be handy.

EXEC MASTER..Xp_servicecontrol 'Querystate' , 'SQLAgent'

Returns the Current state of the SQLAgent ie One can check whether the SQL Agent is running or stopped.XP_Servicecontrol requires Sysadmin rights as one can start or stop a sqlagent using it.

EXEC MASTER..Xp_servicecontrol 'Start' , 'SQLAgent' -- to start

EXEC MASTER..Xp_servicecontrol 'Stop' , 'SQLAgent' -- to stop

The second parameter in the above command is the SQL Agent's service name.

For a default instance it is 'SQLAgent'. For a named instance it would differ.

SQLAgent service name can be checked by going to Start->;Run->;Services.msc and identifying Sql agent service. Normally for a named instanced it is named in the following way.

The name would be SQLAgent+$+instancename.

For ex: If the instance name is Raj then its 'SQLAgent$Raj'.

But always you are better off checking it on the actual server.

Also note that Servicecontrol can check the state of other services running on the server.For ex:

EXEC MASTER..Xp_servicecontrol 'Querystate' , 'IISAdmin'

So just be careful while using it or while assigning permissions to it.

Also, this command igoners spaces in between. ie.. If service name is

IIS Admin this one takes it as IISAdmin.

Its undocumented..So no guarentees that it will work the same way after a patch installation etc..So these commands can be used for occasional checking but not recommended to be a part of your application code.So just bear in mind..

These two commands work on both SQL 2k and 2k5 :)