Archive for the ‘Scripts to use’ Category

select r.session_id,
r.start_time,
db = db_name(database_id),
r.command,
st.text,
substring(st.text,
r.statement_start_offset/2+1,
(case when r.statement_end_offset = -1 then len(convert(nvarchar(max), st.text)) * 2 else r.statement_end_offset end – r.statement_start_offset)/2+1),
r.blocking_session_id,
r.wait_type,
r.last_wait_type,
r.cpu_time,
r.total_elapsed_time,
r.reads,
r.writes,
s.memory_usage,
s.host_name,
s.program_name,
s.login_name
from sys.dm_exec_requests r
join sys.dm_exec_connections c on r.session_id = c.session_id
join sys.dm_exec_sessions s on s.session_id = r.session_id
cross apply sys.dm_exec_sql_text(r.sql_handle) st
where r.session_id > 50 and r.blocking_session_id>1
order by r.session_id

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

EXEC(@SQL)

sp_configure ‘show advanced options’, 1
GO
RECONFIGURE
GO
sp_configure ‘xp_cmdshell’, 1
GO
RECONFIGURE
GO

Monitor Database Growth For SQLSERVER2005

__________________________________________________________________________________________________________________________________________________

–PART 1

If exists (Select name from sys.objects where name = ‘DBGrowthRate’ and Type = ‘U’)
Drop Table dbo.DBGrowthRate

Create Table dbo.DBGrowthRate (DBGrowthID int identity(1,1), DBName varchar(100), DBID int,
NumPages int, OrigSize_In_MB decimal(10,2), CurSize_In_MB decimal(10,2), GrowthAmt varchar(100),
MetricDate datetime)

Drop table #TempDBSize

Select sd.name as DBName, mf.name as FileName, mf.database_id, file_id, size
into #TempDBSize
from sys.databases sd
join sys.master_files mf
on sd.database_ID = mf.database_ID
Order by mf.database_id, sd.name

Insert into dbo.DBGrowthRate (DBName, DBID, NumPages, OrigSize_In_MB, CurSize_In_MB, GrowthAmt, MetricDate)
(Select tds.DBName, tds.database_ID, Sum(tds.Size) as NumPages,
Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024)) as OrigSize,
Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024)) as CurSize,
‘0.00 MB’ as GrowthAmt, GetDate() as MetricDate
from #TempDBSize tds
where tds.database_ID not in (Select Distinct DBID from DBGrowthRate where DBName = tds.database_ID)
Group by tds.database_ID, tds.DBName)

–Drop table #TempDBSize

Select *
from DBGrowthRate
–Above creates initial table and checks initial data

–PART 2
–Below is the code run weekly to check the growth.
Select sd.name as DBName, mf.name as FileName, mf.database_id, file_id, size
into #TempDBSize2
from sys.databases sd
join sys.master_files mf
on sd.database_ID = mf.database_ID
Order by mf.database_id, sd.name

If Exists (Select Distinct DBName from #TempDBSize2
where DBName in (Select Distinct DBName from DBGrowthRate))
and Convert(varchar(10),GetDate(),101) > (Select Distinct Convert(varchar(10),Max(MetricDate),101) as MetricDate
from DBGrowthRate)
Begin
Insert into dbo.DBGrowthRate (DBName, DBID, NumPages, OrigSize, CurSize, GrowthAmt, MetricDate)
(Select tds.DBName, tds.database_ID, Sum(tds.Size) as NumPages,
dgr.CurSize as OrigSize,
Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024)) as CurSize,
Convert(varchar(100),(Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024))
– dgr.CurSize)) + ‘ MB’ as GrowthAmt, GetDate() as MetricDate
from #TempDBSize2 tds
join DBGrowthRate dgr
on tds.database_ID = dgr.DBID
Where DBGrowthID = (Select Distinct Max(DBGrowthID) from DBGrowthRate
where DBID = dgr.DBID)
Group by tds.database_ID, tds.DBName, dgr.CurSize)
End
Else
IF Not Exists (Select Distinct DBName from #TempDBSize2
where DBName in (Select Distinct DBName from DBGrowthRate))
Begin
Insert into dbo.DBGrowthRate (DBName, DBID, NumPages, OrigSize, CurSize, GrowthAmt, MetricDate)
(Select tds.DBName, tds.database_ID, Sum(tds.Size) as NumPages,
Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024)) as OrigSize,
Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024)) as CurSize,
‘0.00 MB’ as GrowthAmt, GetDate() as MetricDate
from #TempDBSize2 tds
where tds.database_ID not in (Select Distinct DBID from DBGrowthRate
where DBName = tds.database_ID)
Group by tds.database_ID, tds.DBName)
End

–Select *
–from DBGrowthRate
—-Verifies values were entered

Drop table #TempDBSize2

_____________________________________________________________________________________________________________

Monitor Database Growth FOR SQLSERVER2000

_____________________________________________________________________________________________________________

–PART 1

If exists (Select name from sys.objects where name = ‘DBGrowthRate’ and Type = ‘U’)
Drop Table dbo.DBGrowthRate

Create Table dbo.DBGrowthRate (DBGrowthID int identity(1,1), DBName varchar(100), DBID int,
NumPages int, OrigSize_In_MB decimal(10,2), CurSize_In_MB decimal(10,2), GrowthAmt varchar(100),
MetricDate datetime)

Drop table #TempDBSize

Select sd.name as DBName, mf.name as FileName, mf.database_id, file_id, size
into #TempDBSize
from sysdatabase sd
join sysaltfiles mf
on sd.database_ID = mf.database_ID
Order by mf.database_id, sd.name

Insert into dbo.DBGrowthRate (DBName, DBID, NumPages, OrigSize_In_MB, CurSize_In_MB, GrowthAmt, MetricDate)
(Select tds.DBName, tds.database_ID, Sum(tds.Size) as NumPages,
Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024)) as OrigSize,
Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024)) as CurSize,
‘0.00 MB’ as GrowthAmt, GetDate() as MetricDate
from #TempDBSize tds
where tds.database_ID not in (Select Distinct DBID from DBGrowthRate where DBName = tds.database_ID)
Group by tds.database_ID, tds.DBName)

–Drop table #TempDBSize

Select *
from DBGrowthRate
–Above creates initial table and checks initial data

–PART 2
–Below is the code run weekly to check the growth.
Select sd.name as DBName, mf.name as FileName, mf.database_id, file_id, size
into #TempDBSize2
from sys.databases sd
join sys.master_files mf
on sd.database_ID = mf.database_ID
Order by mf.database_id, sd.name

If Exists (Select Distinct DBName from #TempDBSize2
where DBName in (Select Distinct DBName from DBGrowthRate))
and Convert(varchar(10),GetDate(),101) > (Select Distinct Convert(varchar(10),Max(MetricDate),101) as MetricDate
from DBGrowthRate)
Begin
Insert into dbo.DBGrowthRate (DBName, DBID, NumPages, OrigSize, CurSize, GrowthAmt, MetricDate)
(Select tds.DBName, tds.database_ID, Sum(tds.Size) as NumPages,
dgr.CurSize as OrigSize,
Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024)) as CurSize,
Convert(varchar(100),(Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024))
– dgr.CurSize)) + ‘ MB’ as GrowthAmt, GetDate() as MetricDate
from #TempDBSize2 tds
join DBGrowthRate dgr
on tds.database_ID = dgr.DBID
Where DBGrowthID = (Select Distinct Max(DBGrowthID) from DBGrowthRate
where DBID = dgr.DBID)
Group by tds.database_ID, tds.DBName, dgr.CurSize)
End
Else
IF Not Exists (Select Distinct DBName from #TempDBSize2
where DBName in (Select Distinct DBName from DBGrowthRate))
Begin
Insert into dbo.DBGrowthRate (DBName, DBID, NumPages, OrigSize, CurSize, GrowthAmt, MetricDate)
(Select tds.DBName, tds.database_ID, Sum(tds.Size) as NumPages,
Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024)) as OrigSize,
Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024)) as CurSize,
‘0.00 MB’ as GrowthAmt, GetDate() as MetricDate
from #TempDBSize2 tds
where tds.database_ID not in (Select Distinct DBID from DBGrowthRate
where DBName = tds.database_ID)
Group by tds.database_ID, tds.DBName)
End

–Select *
–from DBGrowthRate
—-Verifies values were entered

Drop table #TempDBSize2

bry4+u

/*****************************************************************************************************
First Create Procedure and execute on Sqlserver2000 server
*****************************************************************************************************/

Use Northwind
go
create procedure SpaceInfo @dbname varchar(100)
as
DECLARE @SQLCode varchar(5000)
SET @SQLCode =
‘USE [‘ + @DBName +’]
DECLARE @SpaceSeeker TABLE ([Group_Name] varchar(50), [File_Name] varchar(100), Logical_Disk char(3),[File_Location] varchar(100),
[Allocated_Space] decimal(15,4), [Actual_Size] decimal(15,4), [Space_Available] decimal(15,4),
[Total_FREE_Drive_Space] decimal(15,2), [Max_Growth] char(16),[File_Growth] varchar(15))
— insert calculations into table

INSERT INTO @SpaceSeeker ([Group_Name] , [File_Name] ,Logical_Disk,[File_Location], [Allocated_Space] , [Actual_Size], [Total_FREE_Drive_Space] , [Max_Growth] ,[File_Growth])
SELECT
Group_Name = sfg.groupname,
[File_Name] = sf.[name],
Logical_Disk=LEFT([filename],3),
File_Location = [filename],
Allocated_space = sf.[size] * 8 /1024,
Actual_Size  = FILEPROPERTY(sf.[name], ”SPACEUSED”) * 8 /1024,
Total_Disk_Space = 0.0,
MAX_GROWTH = CASE
WHEN sf.[maxsize] = 0 THEN ”NO GROWTH SET”
WHEN sf.[maxsize] = -1 THEN ”NO MAX LIMIT SET”
ELSE STR((sf.[maxsize] * 8) /1024)+ ” MB”
END,
File_Growth=CASE
WHEN (sf.status&0x100000) > 0 THEN STR(sf.growth)+” %”
ELSE STR((sf.growth * 8) /1024)+” MB”
END
— join sysfilegroup just to get filegroup name
FROM sysfiles sf (NOLOCK) LEFT OUTER JOIN sysfilegroups sfg (NOLOCK)
ON sf.groupid = sfg.groupid
ORDER BY 1
— make a second pass to update table
— this will store the actual space available in the file
UPDATE @SpaceSeeker
SET Space_Available = ((Allocated_Space) – (Actual_Size ))
–*****************************************************
— this is to update total drive space. it uses xp_fixeddrives and temp table
— reason for temp table is that exec is not support when doing an insert into a table variable
— this statement is a little different in the job
Create TABLE  #DRIVESPACE  ([Logical_Disk] char(1), [MB_Free] varchar(15))
INSERT #DriveSpace
exec (”master..xp_fixeddrives”)
update @SpaceSeeker
SET Total_FREE_Drive_Space = (MB_FREE)/1024
from #DriveSpace a JOIN @SpaceSeeker b ON a.Logical_Disk =LEFT(b.Logical_Disk,1)
drop table #DriveSpace
–*******************************************************
IF NOT EXISTS (SELECT 1 FROM Northwind.DBO.sysobjects WHERE NAME = ”DBA_DBSize_Growth” AND type = ”U”)
BEGIN
CREATE TABLE Northwind.DBO.DBA_DBSize_Growth
(
[Database_Name] varchar(50), [Group_Name] varchar(50), [File_Name] varchar(50), Logical_Disk char(3),[File_Location] varchar(100),
[Allocated_Space] decimal(15,4), [Actual_Size] decimal(15,4), [Space_Available] decimal(15,4),
[Total_FREE_Drive_Space_GB] decimal(15,4), [Max_Growth] char(16),[File_Growth] varchar(15),
[Size_Date] smalldatetime DEFAULT (GETDATE())
)
END
–INSERT INTO Northwind
INSERT INTO Northwind.DBO.DBA_DBSize_Growth
SELECT ”’ + @DBName + ”’,
Group_Name ,
File_Name  ,
Logical_Disk,
File_Location,
Total_Allocated_Space_MB = Allocated_Space ,
Actual_File_Size_MB = Actual_Size,
Space_Available_MB = Space_Available ,
Total_FREE_Drive_Space,
MAX_GROWTH_MB = MAX_GROWTH ,
File_Growth,
Size_Date= GETDATE()
FROM @SpaceSeeker
GO’

–print @SQLCode
EXEC (@SQLCode)
–***************************************************************************************
GO

/********************************************************************************************

Place in job to do a nightly dump into table for monitoring purposes.

*********************************************************************************************/

USE Northwind
GO

DECLARE @DBName varchar(50), @SQL nvarchar(250)

DECLARE  tmpDBRetrieval CURSOR FOR
SELECT [name] from master.dbo.sysdatabases
WHERE DBID NOT IN (1,2,3,4)–5,7,8,9,10,11,12)  –AND status NOT IN (16,32,64,128,256,512,520)
ORDER BY [name]
OPEN tmpDBRetrieval
FETCH NEXT FROM tmpDBRetrieval
INTO @DBName

WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = N’exec Northwind.dbo.SpaceInfo ”’ + @DBName + ””
EXEC (@SQL)
–print @sql
IF @@ERROR <> 0
BEGIN
GOTO IFERROR
END
IFERROR:
FETCH NEXT FROM tmpDBRetrieval
INTO @DBName

END
CLOSE tmpDBRetrieval
DEALLOCATE tmpDBRetrieval

/***************************************************************************************************

To get result

***************************************************************************************************/

SELECT     * FROM         DBA_DBSize_Growth

for %1 in (d:\TestPackages\t1*.dtsx) do dtexec /F “%1”

Change the ‘SA’ password without having old password

Use OSQL with the –E option (for authenticated user). If you are an administrator of the OS, you will get in without being prompted for the sa password. Once you are at the OSQL interactive prompt, issue the following command:

—————————————————————————————————
EXEC sp_password NULL, ‘newpassword’, ‘username’ GO

SELECT     name, DATABASEPROPERTYEX(name, ‘Recovery’) AS Expr1, DATABASEPROPERTYEX(name, ‘Status’) AS Expr2
FROM         sysdatabases
WHERE     (DATABASEPROPERTYEX(name, ‘Recovery’) = ‘simple’)
ORDER BY name

/*——–Stored Procedure to find huge tables with Row count in given database————————*/

USE master
GO

IF EXISTS (SELECT 1 FROM sysobjects WHERE name = ‘sp_show_huge_tables’ AND type = ‘P’)
DROP PROC sp_show_huge_tables
GO

CREATE PROC sp_show_huge_tables
(
@top            int    = NULL,
@include_system_tables     bit     = 0
)
AS

/*************************************************************************************************

Purpose:    To list the size of all tables in the database in descending order (that is biggere tables first).
Output can be restricted to top n tables. That is you can pass the value 3 to @top parameter to
see the top 3 biggest tables in your database. Optionally, you can use @include_system_tables
parameter, to include systemt tables in the output.
NOTE: Always create this procedure in the master database and call it from the required databases,
as if the stored procedure is created in that database (That is, don’t prefix the stored procedure
with the database name).

Examples:

To list all the user tables in the database along with their sizes:
EXEC sp_show_huge_tables

To see the top three biggest tables in your database:
EXEC sp_show_huge_tables 3

To list all the user AND system tables in the database along with their sizes:
EXEC sp_show_huge_tables @include_system_tables = 1

To see the top three biggest user or system tables in your database:
EXEC sp_show_huge_tables 3, 1
*************************************************************************************************/

BEGIN
IF @top > 0
SET ROWCOUNT @top

SELECT [Table Name], (SELECT rows FROM sysindexes s WHERE s.indid < 2 AND s.id = OBJECT_ID(a.[Table Name])) AS [Row count], [Total space used (MB)] FROM
(
SELECT    QUOTENAME(USER_NAME(o.uid)) + ‘.’ + QUOTENAME(OBJECT_NAME(i.id)) AS [Table Name],
CONVERT(numeric(15,2),(((CONVERT(numeric(15,2),SUM(i.reserved)) * (SELECT low FROM master.dbo.spt_values (NOLOCK) WHERE number = 1 AND type = ‘E’)) / 1024.)/1024.)) AS [Total space used (MB)]
FROM    sysindexes i (NOLOCK)
INNER JOIN
sysobjects o (NOLOCK)
ON
i.id = o.id AND
((@include_system_tables = 1 AND o.type IN (‘U’, ‘S’)) OR o.type = ‘U’) AND
((@include_system_tables = 1)OR (OBJECTPROPERTY(i.id, ‘IsMSShipped’) = 0))
WHERE    indid IN (0, 1, 255)
GROUP BY    QUOTENAME(USER_NAME(o.uid)) + ‘.’ + QUOTENAME(OBJECT_NAME(i.id))

) as a
ORDER BY    [Total space used (MB)] DESC

SET ROWCOUNT 0
END

GO

GRANT EXEC ON sp_show_huge_tables TO Public