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. — Kill Server process ID 98 kill 98 [...]
Archive of entries posted on December 2009
Best SQL Server Pagination Method
The best pagination method is actually mind-bogglingly simple. Aren’t all things like this? It involves using (the mighty powerful) ROWCOUNT statement — twice. Anyways, the code: CREATE PROCEDURE [dbo].[spUsersPaged] ( @RowBegin int, @MaxRows int ) AS BEGIN DECLARE @StartID int SET ROWCOUNT @RowBegin SELECT @StartID = UserID FROM dbo.Users u ORDER BY UserID — Set [...]
Using sp_who2
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 [...]
Cannot Log in Remotely to SQL Server Express
SQL Server Express 2008 does not allow remote connections by default. This is done by design as SQL Express is often used either as a development tool, or as a local installation for resident applications. Another thing to note, is that SQL Express uses dynamic ports by default so in order to connect, The SQL [...]
SQL Management Tools
Working in Administration or Development, we find ourselves performing the same tasks over and over. Often we do not recognize the time each of these menial tasks take and how much time out of our day they waste. Luckily, there are external tools we can rely on to make our lives easier. SSMS Tools Pack [...]
Compare Stored Procedures
Comparing the results of two different stored procedures output can be difficult task. The first difficulty is to return the result sets of the stored procedures into temporary tables. There are two different methods of doing this outlined in the link above. For this example we will use the local linked server method. Your method [...]
Configure Change Data Capture Settings
After setting up change data capture (CDC), there are a few configuration options that allow for optimal performance based upon applications load. Using the default values, the most number of transactions that can be grabbed every 5 seconds is 5000. Remember, a transaction is not the same as a database row. One transaction can update [...]
Get End of Month
The following short code will return the date and time for the very end of the month passed in. It works by first getting the beginning of the month for the date passed in, adding one month to that date, then subtracting 3 milliseconds. This gives the latest possible date for the end of the [...]
Beginning of Month
Possibly the easiest way to calculate the beginning of the month, is to use the DateAdd function and add a date difference of nothing to the month: DECLARE @Date datetime SET @Date = GETDATE() SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)
