Archive for June, 2010

Below script will help you get the configuration details of sqlserver. This configuration can be captured and use it anytime to perform audit and to do inventory.

—-Script to get configuration details—-
SERVERPROPERTY(‘BuildClrVersion’)as dotNetBuildClrVersion,
SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS’)as ComputerNamePhysicalNetBIOS,
SERVERPROPERTY(‘EngineEdition’)as EngineEdition,
SERVERPROPERTY(‘InstanceName’)as InstanceName,
SERVERPROPERTY(‘IsClustered’)as IsClustered,
SERVERPROPERTY(‘IsFullTextInstalled’)as IsFullTextInstalled,
SERVERPROPERTY(‘IsIntegratedSecurityOnly’)as IsIntegratedSecurityOnly,
SERVERPROPERTY(‘IsSingleUser’)as IsSingleUser,
SERVERPROPERTY(‘MachineName’)as MachineName,
SERVERPROPERTY(‘LicenseType’)as LicenseType,
SERVERPROPERTY(‘ProcessID’)as ProcessID,
SERVERPROPERTY(‘ProductVersion’)as ProductVersion,
SERVERPROPERTY(‘ProductLevel’)as ProductLevel,
SERVERPROPERTY(‘ResourceLastUpdateDateTime’)as ResourceLastUpdateDateTime,
SERVERPROPERTY(‘ResourceVersion’)as SqlserverResourceVersion,
SERVERPROPERTY(‘ServerName’)as ServerName

Below script will help you to kill all the existing connection coming to the user databases.
Copy and paste the script in Query window and change the DBName to the one that you want to troubleshoot.

DECLARE @DatabaseName nvarchar(50)
SET @DatabaseName = N”

DECLARE @SQL varchar(max)
SET @SQL = ”

SELECT @SQL = @SQL + ‘Kill ‘ + Convert(varchar, SPId) + ‘;’
FROM master..sysprocesses
WHERE DBId = DB_ID(@DatabaseName) AND SPId @@SPId