Sharing the Scripts which I have collected--SQL server Scripts..

Hi All,

As a DBA we should have all the scripts in handy for an an emergency/non-emergency this is our basic task and not only should have the scrips we should understand what that scripts for?here few scripts for you which is useful for the daily basis...............

1.Get the detail of the members of NT Group Account using MSSQL SERVER 2005 xp_logininfo.
->xp_logininfo 'domain\<your groupname>', members

2.Use the following script to know about the Windows Group in SQL Server
->DECLARE @LoginName sysname
DECLARE @sql NVARCHAR (2000)

BEGIN
DECLARE cur_Loginfetch CURSOR FOR

SELECT [name] FROM master.sys.server_principals WHERE TYPE = 'G'

OPEN cur_Loginfetch

FETCH NEXT FROM cur_Loginfetch INTO @LoginName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC xp_logininfo @LoginName , 'members'
FETCH NEXT FROM cur_Loginfetch INTO @LoginName
END
CLOSE cur_Loginfetch
DEALLOCATE cur_Loginfetch
RETURN
END







3.To know thefile group size  (you need to execute on an each database if use the below query how ever I have provided another one which gives all the databases filegroup information)

SELECT
    b.groupname AS 'File Group',
    Name,
    [Filename],
    CONVERT (Decimal(15,2),ROUND(a.Size/128.000,2))
        [Currently Allocated Space (MB)],
    CONVERT (Decimal(15,2),
    ROUND(FILEPROPERTY(a.Name,'SpaceUsed')/128.000,2))
        AS [Space Used (MB)],
    CONVERT (Decimal(15,2),
    ROUND((a.Size-FILEPROPERTY(a.Name,'SpaceUsed'))/128.000,2))
        AS [Available Space (MB)]
FROM dbo.sysfiles a (NOLOCK)
JOIN sysfilegroups b (NOLOCK) ON a.groupid = b.groupid
ORDER BY b.groupname

or use the below query to know the filegroup usuage on all the databases

declare @dbname varchar(256)
declare @sql varchar(4000)

IF (OBJECT_ID('tempdb..#space') IS NOT NULL)
drop table #space

IF (OBJECT_ID('tempdb..#fs') IS NOT NULL)
drop table #fs

IF (OBJECT_ID('tempdb..#fg') IS NOT NULL)
drop table #fg

create table #space
(dbname varchar(256),
fileid int,
filegroup int,
totalextents int,
usedextents int,
name varchar(255),
filename varchar(1000))

create table #fs
(fileid int,
filegroup int,
totalextents int,
usedextents int,
name varchar(255),
filename varchar(1000))

create table #fg
(dbname varchar(256),
groupid int,
groupname varchar(256))

declare csdb cursor for
select name
from master.dbo.sysdatabases
order by name

open csdb
fetch csdb into @dbname

while @@fetch_status = 0
begin

set @sql = '
use ' + @dbname + '
insert into #fs
exec (''DBCC showfilestats'')'

exec (@sql)

insert into #space
select @dbname, *
from #fs

set @sql = '
use ' + @dbname + '
insert into #fg
select ''' + @dbname + ''', groupid, groupname
from sysfilegroups'

exec (@sql)

truncate table #fs

fetch csdb into @dbname

end

close csdb
deallocate csdb

select g.dbname, g.groupname, sum((TotalExtents - UsedExtents) * 64.0 / 1024.0) AvailSpaceMB
from #space f
join #fg g on f.dbname = g.dbname
and f.filegroup = g.groupid
group by g.dbname, g.groupname
order by g.dbname, g.groupname

drop table #space

drop table #fs

drop table #fg 

4.To know what is the available space & used space used by the physical files like mdf,ndf,ldf.


SELECT name AS 'FileName' , physical_name AS 'PhysicalName', size/128 AS 'TotalSizeinMB',
    size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'AvailableSpaceInMB',
    CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'ActualSpaceUsedInMB',
    (CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0)/(size/128)*100. as '%SpaceUsed'
    FROM sys.database_files;

or use the below auery as well

SELECT Name, Filename,

CONVERT(Decimal(15,2),ROUND(a.Size/128.000,2)) [Currently Allocated Space (MB)],

CONVERT(Decimal(15,2),ROUND(FILEPROPERTY(a.Name,'SpaceUsed')/128.000,2)) AS [Space Used (MB)],

CONVERT(Decimal(15,2),ROUND((a.Size-FILEPROPERTY(a.Name,'SpaceUsed'))/128.000,2)) AS [Available Space (MB)]

FROM dbo.sysfiles a (NOLOCK)


5. In SQL Server 2005 for below operations you are able to see percent of completion or estimation time using the DMV



SELECT TOP 2 start_time,
percent_complete ,estimated_completion_time
FROM sys.dm_exec_requests
ORDER by start_time desc  

or

use select * from sys.dm_exec_requests

Identify the SPID and go for the validation of time consuming how ever in SQL server 2000 it is not possible
to estimate the Time completion...

6. Whats causing my tempdb to grow – SQL Server 2005 ?

Being a DBA, and when your tempdb size is growing extra ordinary then a DBA has to check, which Transact-SQL statements are top consumers of tempdb.
Here is script, which will help you to figure out, which adhoc TSQL statements are consuming tempdb.

SELECT

    t1.session_id,

    t1.request_id,

    t1.task_alloc,

    t1.task_dealloc,

    t2.sql_handle,

    t2.statement_start_offset,

    t2.statement_end_offset,

    t2.plan_handle

FROM (Select session_id, request_id,

        SUM(internal_objects_alloc_page_count) AS task_alloc,

        SUM (internal_objects_dealloc_page_count) AS task_dealloc

  FROM sys.dm_db_task_space_usage

  GROUP BY session_id, request_id) AS t1,

  sys.dm_exec_requests AS t2

WHERE t1.session_id = t2.session_id  AND

        (t1.request_id = t2.request_id) and

        (t1.task_alloc + t1.task_dealloc > 0)

ORDER BY t1.task_alloc DESC

7.Get SQL server information in OS registry levels.

Irrespective of the version of SQL Server, information is saved in the registry. Only the location changes for various versions. The path in the registry is as follows.

SQL Server Default Instance(2000)

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Setup

SQL Server Default Instance(2005)

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup

SQL Server Named Instance (2008)

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.ONE\Setup

8.Virtual File stats

Virtual File Stats

2005/2008

select
    read_stall_ms = case when num_of_reads = 0 then 0 else (io_stall_read_ms/num_of_reads) end,
    write_stall_ms = case when io_stall_write_ms = 0 then 0 else (io_stall_write_ms/num_of_writes) end,
    total_stall_ms = case when (num_of_reads = 0 and num_of_writes = 0) then 0 else (io_stall/(num_of_reads + num_of_writes)) end,
    db = db_name(vfs.database_id),
    mf.physical_name,
    vfs.*
from sys.dm_io_virtual_file_stats(null, null) as vfs
join sys.master_files as mf on vfs.database_id = mf.database_id and vfs.file_id = mf.file_id
order by total_stall_ms desc


2000
select db = db_name(dbid),
    total_stall_ms = (IoStallMS/(NumberReads + NumberWrites)), *
from ::fn_virtualfilestats(-1,-1)
order by 2 desc

9.A script to list all blocking processes, and other processes blocked by them, all the way down the chain.

2005
select r.session_id, r.blocking_session_id, r.database_id, r.command, s.last_request_start_time, s.login_name, r.last_wait_type, r.status
from sys.dm_exec_requests r
join sys.dm_exec_sessions s on r.session_id = s.session_id
where (r.blocking_session_id > 0 and r.blocking_session_id <> r.session_id)
or r.session_id in (select blocking_session_id from sys.dm_exec_requests where blocking_session_id > 0 and blocking_session_id <> session_id)


2000
select spid, blocked, dbid, cmd, last_batch, loginame, lastwaittype, status
from master..sysprocesses
where (blocked > 0 and blocked <> spid)
or spid in (select blocked from master..sysprocesses where blocked > 0 and blocked <> spid)