List orphan IDs

This script displays the list of orphan SQL Server accounts from all the databases

Exec SP_MSForEachDB 'select ''?'' as DBName, name AS UserName, sid AS UserSID
from ?..sysusers
where issqluser = 1 and
(sid is not null and sid <> 0x0)
and suser_sname(sid) is null
order by name'

Estimated time for backup/restore

This script can be used to find the estimated time of backup and restore that is on progress in your SQL server.


SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete)
AS [Percent Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],
CONVERT(NUMERIC(6,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],
CONVERT(NUMERIC(6,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],
CONVERT(NUMERIC(6,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],
CONVERT(VARCHAR(100),(SELECT SUBSTRING(text,r.statement_start_offset/2,
CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)
FROM sys.dm_exec_sql_text(sql_handle)))
FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')

Check if /3GB is configured or not

This script is used to check whether the 3gb switch is configured or not in the boot.ini file.

If(Select Virtual_Memory_In_Bytes/1024/(2048*1024) from Sys.dm_os_Sys_Info) < 1

Begin

PRINT '/3GB Switch Not Configured in Boot.Ini (CHECK)'

End

Else

Begin

PRINT '/3GB Switch Configured in Boot.Ini'

End

Create Windows network shares

This procedure can be used to share any windows folders with default permissions
This procedure accepts 3 parameters
-- @path - The path of the share folder
-- @sharename - The sharename to be given
-- @type - This accepts 2 vaules either to create or revoke if create a new share is created if revoke the share ie removed

Script:

create procedure windowsshare (@path varchar(200), @sharename varchar(50),@type varchar(10))
as
begin
declare @cmd varchar(100)
if lower(@type) = lower('create')
BEGIN
set @cmd = 'net share '+@sharename+'='+@path
print @cmd
exec master..xp_cmdshell @cmd,no_output
PRINT ' SHARE '+@sharename+' HAS BEEN CREATED SUCCESSFULLY'
end
ELSE
BEGIN
set @cmd = 'net share '+@sharename+' '+@path+' /delete'
exec master..xp_cmdshell @cmd,no_output
PRINT ' SHARE '+@sharename+' HAS BEEN REMOVED SUCCESSFULLY'
end
END

Give permission to Windows NTFS objects

This procedure can be used to provide the security options in windows NTFS folders with default permissions.
This procedure accepts 5 parameters
-- @path - The path of the share folder
-- @traverse - To specify if the permission needs to be traversed to sub folders or not
-- Y - to give permissions to subfolders also
-- N - to not give permissions to sub folders
-- @type - This accepts 4 vaules
-- g - to grant access to the specified folder and/or sub-folders
-- p - to modify access to the specified folder and/or sub-folders
-- r - to revoke access to the specified folder and/or sub-folders
-- d - to deny access to the specified folder and/or sub-folders
-- @user - the user to whom access needs to be specified
-- @perm - type of permission to be given and this accepts 4 parameters
-- N - No Permissions
-- R - Read
-- C - Change
-- F - Full Control


Script:
create procedure windowssec (@path varchar(200), @traverse char,@type char,@user varchar(50),@perm char)
as
begin
declare @cmd varchar(100)
if (upper(@traverse) <> 'Y')
begin
set @cmd = 'cacls '+@path
end
else
begin
set @cmd = 'cacls '+@path+' /t '
end
if (lower(@type) = 'r' or lower(@type) = 'd')
begin
set @cmd = @cmd+' /e /'+@type+' '+@user
exec master..xp_cmdshell @cmd
end
else
begin
set @cmd = @cmd+' /e /'+@type+' '+@user+':'+@perm
exec master..xp_cmdshell @cmd
END
end

DMVs in SQL Server 2005 - PART I

Introduction:

Microsoft has brought lot of new changes to the way how SQL Server works and handles things in SQL Server 2005. These changes have been incorporated not only to the developers but also to those who administer SQL Server databases and instances. One of the major changes that have been done is a new feature that comes with SQL Server 2005 that is called as Dynamic views. Let’s get to know what it exactly is and does.

What’s a Dynamic View:

Dynamic Management Views and functions are designed to give detailed information about internal working of SQL server 2005. The information gives a insight of what’s going inside SQL server 2005 like information on health of server and databases which can be used to monitor performance, diagnose problems and tune databases. They are designed to be used instead of system tables and functions in SQL server 2000.

DMV’s are of both views and table-valued functions. There are two types of DMV’s.
1. Server scope DMV
2. Database scope DMV

Server scope DMV’s apply to the entire server and are stored in master database whereas Database scope DMV’s are specific to that database.

Dynamic Management views can be identified with their name starting with dm_. They reside in the master database.

Dynamic Management Views and Functions are categorized into twelve as given below.

1. CLR related DMV’s
2. I/O related DMV’s
3. Database Mirroring related DMV’s
4. Query Notifications related DMV’s
5. Database related DMV’s
6. Replication related DMV’s
7. Execution related DMV’s
8. Service Broker related DMV’s
9. Full-Text Search related DMV’s
10. SQL Server Operating System related DMV’s
11. Index related DMV’s
12. Transaction related DMV’s

DMV’s are owned by the schema sys and is to be referenced in two part naming convention as sys.DMV name.

Permission Required:

1. Select permission required on the Dynamic Management View/Functions
2. View server state permission for server scope DMV’s
3. View database state permission for database scope DMV’s

Important:

Microsoft reserves the right to change/drop any DMV’s and DMF’s in future releases.

What's to come next:
------------------------------
1. Some Useful DMV's for Real Time
2. Transaction, IO and OS Related DMV's
3. Some Useful scripts using DMv's and DMF's

So keep checking and update yourself.

Script to backup table @ database level

This script is used to take a backup of the table @ the database level. This is used in the case where the number of records in the table keeps on growing and the historical records are only used for reporting rather than transactional purpose. This script when executed creates a backup of the table with name TABLENAME_BKP_DATE. If this script is executed more than once on the same day, the already backed up table is renamed as TABLENAME_BKP_DATETIME and new backup table is created.

Script:
******
create procedure TABLEARCHIVE
as
BEGIN
SET NOCOUNT ON
/*******************************************************************************************************/
-- This Procedure was written by Sugesh Kumar Rajendran - Date: 31 July 2006
-- This Procedure is used to back up the table
-- Which will have the name TABLENAME_BKP_getdate() when this procedure is run
-- If this procedure is run more than once in the same day the already existing table will be backed up
-- With TIMESTAMP in it
-- No Input Parameters and No Output Parameters
/*******************************************************************************************************/
declare @sql nvarchar(200)
declare @sql1 nvarchar(200)
declare @ddate int
declare @dmonth varchar(3)
declare @dyear int
declare @dhour int
declare @dmin int
declare @bkptable varchar(100)
declare @bkptable1 varchar(100)
declare @date datetime
declare @bsql nvarchar(200)
declare @count int
declare @count1 int
/* GETTING THE DATE MONTH AND YEAR INTO SEPERATE VARIABLES*/
set @ddate = (select DATEPART(dd,getdate()))
set @dmonth = (select DATENAME(month,getdate()))
set @dyear = (select DATEPART(yyyy,getdate()))
set @dhour = (select DATEPART(hh,getdate()))
set @dmin = (select DATEPART(mi,getdate()))
/* DEFINING THE NAME OF THE BACKUP TABLE*/
select @bkptable = 'TABLENAME_BKP_'+cast(@dyear as varchar)+UPPER(@dmonth)+cast(@ddate as varchar)
/* CHECKING IF THE BACKUP TABLE ALREADY EXISTS IN THE SYSTEM CATALOG*/
/* IF SO THIS TABLE IS BACKED UP TO AN ALTERNATE TABLE TO MAINTAIN HISTORY*/
/* AND THE BACKUP TABLE IS DROPPED SO THAT IT CAN BE CREATED AGAIN WITH SAME NAME*/
set @bsql = 'DROP TABLE '+@bkptable
set @count1 = (select count(*) from sysobjects where name like @bkptable)
if @count1 = 1
BEGIN
select @bkptable1 = 'TABLENAME_BKP_'+cast(@dyear as varchar)+UPPER(@dmonth)+cast(@ddate as varchar)+cast(@dhour as
varchar)+cast(@dmin as varchar)
set @sql1 = 'select * into '+@bkptable1+' from '+@bkptable
exec sp_executesql @sql1
if @@error = 0 PRINT 'RECORDS MOVED TO TABLE '+@bkptable1
exec sp_executesql @bsql
END
/* CREATING THE BACKUP TABLE */
set @sql = 'select * into '+@bkptable+' from TABLENAME
exec sp_executesql @sql
if @@error = 0
PRINT 'TABLE ARCHIVED SUCCESSFULLY'
else PRINT 'TABLE ARCHIVE NOT SUCCESSFUL'
/*DELETING THE RECORDS FROM TABLE*/
delete from TABLENAME
select @count = @@rowcount
if @@error = 0
PRINT cast(@count as varchar) +' ROWS DELETED FROM TABLENAME'
else PRINT 'ERROR PROCESSING DELETION FROM TABLENAME'END

My first post

This is my first post in this blog. I don’t want to bore you with all bla…..bla……bla………….’s. You will find some good posts from me for sql server and windows server system. Those posts will have some good scripts, technical articles and suggestions to keep your server alive and performing good. Check for them to keep you updated.