Archive: ‘SQL’ Category

Stop All Processes Accessing a SQL Database.

No comments June 7th, 2010

Ever try to exclusively lock a database but something else is already is still utilizing it, well try this:

USE master

-- Declare working variables
DECLARE @vcdbname1 VARCHAR(50)
DECLARE @vcdbname2 VARCHAR(50)

DECLARE @spid INT,
@str VARCHAR(255)

-- Set the input database names here - if you don't have a db set it to NULL

SET @vcdbname1 = 'MOSS_CONTENT_SSP_ADMIN'
SET @vcdbname2 = 'MOSS_SSP_DB'

SET NOCOUNT ON

-- Create the cursor from the sysprocesses table to get the ids of the databases

DECLARE UsersCur CURSOR FOR
SELECT spid
FROM master..sysprocesses
WHERE (db_name(dbid) = @vcdbname1 AND @vcdbname1 IS NOT NULL)
OR (db_name(dbid) = @vcdbname2 AND @vcdbname2 IS NOT NULL)

-- Open the cursor
OPEN UsersCur

-- Get the first row (database id)
FETCH NEXT FROM UsersCur INTO @spid

WHILE @@fetch_status <> -1
BEGIN

IF @@fetch_status = 0
BEGIN

SET @str = 'kill ' + CONVERT(VARCHAR, @spid)
-- uncomment the row to test (it will show you the commands it will run
PRINT @str

-- comment the above and uncomment this to kill the sessions
EXEC (@str)

END

-- Get the next row
FETCH NEXT FROM UsersCur INTO @spid
-- you are done
END

-- clean up your variables
CLOSE UsersCur
DEALLOCATE UsersCur

SQL Performance Dashboard…

No comments April 17th, 2010

The SQL Server 2005 Performance Dashboard Reports are Reporting Services report files designed to be used with the Custom Reports feature introduced in the SP2 release of SQL Server Management Studio. The reports allow a database administrator to quickly identify whether there is a current bottleneck on their system, and if a bottleneck is present, capture additional diagnostic data that may be necessary to resolve the problem. For example, if the system is experiencing waits for disk IO the dashboard allows the user to quickly see which sessions are performing the most IO, what query is running on each session and the query plan for each statement.

Common performance problems that the dashboard reports may help to resolve include:
– CPU bottlenecks (and what queries are consuming the most CPU)
– IO bottlenecks (and what queries are performing the most IO).
– Index recommendations generated by the query optimizer (missing indexes)
– Blocking
– Latch contention

The information captured in the reports is retrieved from SQL Server’s dynamic management views. There is no additional tracing or data capture required, which means the information is always available and this is a very inexpensive means of monitoring your server.

Reporting Services is not required to be installed to use the Performance Dashboard Reports.

Download it here:

http://www.microsoft.com/downloads/details.aspx?FamilyID=1d3a4a0d-7e0c-4730-8204-e419218c1efc&DisplayLang=en

Installation:

  1. Execute the .msi file on your SQL server, and follow the prompts.
  2. Browse to: C:\Program Files\Microsoft SQL Server\90\Tools\PerformanceDashboard\setup.sql and double click it.
  3. This will automatically open SSMS and display the content of setup.sql. 
  4. Connect to your database instance, and execute the setup.sql query which is already open, simply click execute.  It should complete successfully.
  5. Right click on your database instance, select Reports, then Custom Reports.  Browse to:
  6. C:\Program Files (x86)\Microsoft SQL Server\90\Tools\PerformanceDashboard and open performance_dashboard_main.
  7. This will access the performance dashboard and you can begin to better understand the performance of your SQL instance as well as how it affects your applications, such as SharePoint. =o)