Occasionally, there comes a requirement to search all databases on a particular server for either columns with a specific name, or columns relating to a specific subject. In the most recent case, I had to find all similar columns in all databases because the company plans to change the datatype of these columns. My task began with having to locate each of these columns in order to determine the impact of the change, and what actually needed to change. For this, I used the undocumented sp_MSForEachDB procedure. It is a great procedure that actually takes a SQL string as an input parameter. The SQL used for the input parameter must then contain a question mark (?) in which each database replaces the question mark and the SQL is executed. Here is an example: [cc lang=”sql”] sp_MSForEachDB @command1=’USE ?; SELECT Table_Catalog ,Table_Schema ,Table_Name ,Column_Name ,Data_Type ,Character_Maximum_Length FROM INFORMATION_SCHEMA.COLUMNS WHERE Data_Type = ”int” AND COLUMN_NAME like ”%ColumnNameHere%”’ [/cc] To utilize correctly, replace the ColumnNameHere with the name of the column you want to find (or what the column name should be like). It will then return you all the columns on the server that are named like the column you specified.
Continue reading ...
When scripting out jobs using ssms (sql server management studio) the default script for a drop statement is to drop the job according to it’s job_id. This is not beneficial however when pushing code to different environments. Job_id’s are specific to the windows environment in which they are created. To get around this is easy however, you simply need to pass the @job_name parameter to the sp_delete_job stored procedure. [cc lang=”sql”] IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N’jobnamegoeshere’) EXEC sp_delete_job @job_name = N’jobnamegoeshere’ , @delete_unused_schedule=1 [/cc]
Continue reading ...
The following SQL Server script works to determine if a particular job is currently running. This feature is nice to have if you do not want jobs to step over one another. I frequently will use this script as the first step of the job. If it is currently running, then the step will raise an error and not proceed to the next step. [cc lang=”sql”] sp_configure ‘Ad Hoc Distributed Queries’,1 RECONFIGURE WITH OVERRIDE GO DECLARE @ExecutionStatus INT DECLARE @JobName varchar(100) = ‘%yourjobnamehere%’ SELECT name, current_execution_status, job_id INTO #Jobs FROM OPENROWSET(‘SQLNCLI’, ‘server=(local);trusted_connection=yes’, ‘set fmtonly off exec msdb.dbo.sp_help_job’) SELECT @ExecutionStatus = current_execution_status FROM #Jobs WHERE job_id = (SELECT sj.job_id FROM msdb.dbo.sysjobs sj WHERE name like @JobName) IF @ExecutionStatus != 1 BEGIN RAISERROR (‘Job Is Not Running’, 16, 1 ) END [/cc] The only caveat to this method, which is the only one I know, is that you need to enable ‘Ad Hoc Distributed Queries’. While I don’t consider this a harmful change, it may not be allowed unless you have dbo permissions on the database.
Continue reading ...
The kill command is used against a SPID. (Server Process ID). This command is typically used because something is misbehaving. In order to use the Kill command, simply type the word “kill” followed by a space and then the number of the Server Process ID to kill. [cc lang=”sql”] — Kill Server process ID 98 kill 98 [/cc] The kill command has three parameters. Two of which I have no idea what they mean: KILL { session ID | UOW } [ WITH STATUSONLY ]
Continue reading ...
This article shows the usage of sp_who2. To diagnose system slowdowns, see (Troubleshooting SQL Slowness). One of the first lines of defense in determining the causes of database slowdowns is to use sp_who2. sp_who2 shows all the sessions that are currently established in the database. These are denoted as SPID‘s, or Server process Id’s. Running sp_who2 is easy, all that is required is to type sp_who2 and execute it, however it’s a good idea to call it with EXEC. [cc lang=”sql”] EXEC sp_who2 [/cc] The first 50 results are system SPIDs. Generally these do not effect slowdowns of the system. These system events include the Checkpoint writer, Log writers and task schedulers. User processes are SPID numbers 50 and over. In diagnosing slowdowns it is these SPIDs that are the potential resource hogs. sp_who2 also takes a optional parameter of a SPID. If a spid is passed, then the results of sp_who2 only show the row or rows of the executing SPID. [cc lang=”sql”] EXEC sp_who2 243 [/cc] There are four main things to look for when when diagnosing slowdowns. Blocking High CPU usage High IO usage Multiple entries for the same SPID representing parallelism. When a number is shown in the column named BlkBy, this represents the SPID that is currently stopping the SPID in the row shown. Sometimes many rows will show SPID numbers in the BlkBy column. This is because there is a chain of blockers. The way this occurs usually starts with one “lead” blocker blocking […]
Continue reading ...
