Not Compelete (To DO -- Next Week):
===================================
http://lynchtek.com/sp_ListNTUserNameDatabaseAccess.aspx
----------------------------------------------------------
ALTER Proc usp_ListAllNTUsersAndDBs (@NtGroup varchar(300))
as
Create Table #DBUsers(DB varChar(3000),ssid varBinary(85))
Create Table #NTUsers(Accountname varChar(300),type varChar(300),Privilege varChar(300), mappedlogin varChar(300),permission varChar(300))
Declare @dbname varChar(3000)
Select @dbname =''
While Not @dbname is Null
begin
Select @dbname = min(name) From master..sysdatabases Where name > @dbname
if @dbname is Null
begin
break
end
Insert Into #DbUsers (db,ssid)
Select @dbname, sid From master..sysusers Where isntgroup=1
end
Select @ntGroup=''
While Not @NtGroup is Null
begin
Select min(sl.name)
From master..sysusers sl join #DBusers db on sl.sid = db.ssid
Where sl.name > @NtGroup
if @NtGroup is Null
begin
break
end
Insert Into #NTUsers (AccountName,Type,Privilege,Mappedlogin,permission)
EXEC xp_logininfo @NtGroup,'members'
end
Select distinct accountname,name,db From #NTUSers N
join (Select sl.name,db.db From master..sysusers sl join #DBusers db on sl.sid = db.ssid) X
on X.Name = N.Permission
drop Table #NTUsers
drop Table #DBUsers
exec usp_ListAllNTUsersAndDBs 'BUILTIN\Administrators'
vrijdag 28 november 2008
donderdag 27 november 2008
Auditing Windows Groups from SQL Server
http://www.mssqltips.com/tip.asp?tip=1252
-------------------------------------------
Problem
Managing database security should be part of every DBAs job. With SQL Server you have the ability to use either SQL Server and Windows or Windows only security. The advantage of using Windows security for your SQL Servers is that you can take advantage of the Windows security model and security policies that have been setup on your domain. Another advantage is that you can manage security at the domain level instead of at the SQL Server instance level.
In addition you can use use Windows groups to manage the security buckets. Based on the groups that are setup you can put specific Windows users in these groups and then give SQL Server access to this Windows group instead of having to create logins for every single windows user.
The disadvantage to this is that the specific people within these groups is masked at the SQL Server level. In your logins you can see the groups that have access, but you have no idea what users are in what groups. So how can you get this information from within SQL Server?
Solution
SQL Server offers some insight into this issue with the xp_logininfo extended stored procedure. This stored procedure is part of both SQL Server 2000 and SQL Server 2005. This xp takes the following parameters:
@acctname - the windows account name or group
@option - information to display
'all' - display information for all permission paths
'members' - display list of members in a group
@privelege - this is an output variable from this command and returns 'admin', 'user' or 'null
Following is T-SQL code that loops through your logins and wherever there is a Windows Group the xp_logininfo XP is called to return information about the Windows group.
SQL Server 2000
For this example we are querying from the syslogins table where the isntgroup = 1 and status = 10.
DECLARE @LoginName sysname
DECLARE @sql NVARCHAR (2000)
BEGIN
DECLARE cur_Loginfetch CURSOR FOR
SELECT [name] FROM master.dbo.syslogins WHERE isntgroup = 1 AND status = 10
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
----------------
SQL Server 2005
For this example we are querying from the sys.server_principals catalog view where type = 'G'.
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
-------------------------------------------
Problem
Managing database security should be part of every DBAs job. With SQL Server you have the ability to use either SQL Server and Windows or Windows only security. The advantage of using Windows security for your SQL Servers is that you can take advantage of the Windows security model and security policies that have been setup on your domain. Another advantage is that you can manage security at the domain level instead of at the SQL Server instance level.
In addition you can use use Windows groups to manage the security buckets. Based on the groups that are setup you can put specific Windows users in these groups and then give SQL Server access to this Windows group instead of having to create logins for every single windows user.
The disadvantage to this is that the specific people within these groups is masked at the SQL Server level. In your logins you can see the groups that have access, but you have no idea what users are in what groups. So how can you get this information from within SQL Server?
Solution
SQL Server offers some insight into this issue with the xp_logininfo extended stored procedure. This stored procedure is part of both SQL Server 2000 and SQL Server 2005. This xp takes the following parameters:
@acctname - the windows account name or group
@option - information to display
'all' - display information for all permission paths
'members' - display list of members in a group
@privelege - this is an output variable from this command and returns 'admin', 'user' or 'null
Following is T-SQL code that loops through your logins and wherever there is a Windows Group the xp_logininfo XP is called to return information about the Windows group.
SQL Server 2000
For this example we are querying from the syslogins table where the isntgroup = 1 and status = 10.
DECLARE @LoginName sysname
DECLARE @sql NVARCHAR (2000)
BEGIN
DECLARE cur_Loginfetch CURSOR FOR
SELECT [name] FROM master.dbo.syslogins WHERE isntgroup = 1 AND status = 10
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
----------------
SQL Server 2005
For this example we are querying from the sys.server_principals catalog view where type = 'G'.
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
Check Again --SQL Server 2000
USE master
GO
IF OBJECT_ID('sp_ListPermissions') IS NOT NULL
AND OBJECTPROPERTY(OBJECT_ID
('sp_ListPermissions'),'IsProcedure')=1
DROP PROCEDURE sp_ListPermissions
GO
CREATE PROCEDURE sp_ListPermissions
@object nvarchar(515) = NULL,
@column sysname = NULL
AS
/*
Procedure to list a uses effective permissions
Author : Itzik Ben−Gan
*/
SET NOCOUNT ON
DECLARE @permissions int
DECLARE @objectid int
SET @objectid = object_id(@object)
--Check input
IF @object IS NOT NULL AND @objectid IS NULL
BEGIN
RAISERROR('Invalid object %s supplied.', 16, 1, @object)
RETURN 1
END
IF @objectid IS NOT NULL
BEGIN
PRINT 'Object permissions on: ' + @object + ' for
login: ' + SUSER_SNAME()
+ ', user: ' + USER_NAME() + '...'
IF @column IS NOT NULL
BEGIN
SET @permissions = permissions(@objectid, @column)
PRINT 'On column: ' + @column + '...'
IF @permissions & 1 = 1 PRINT 'SELECT'
IF @permissions & 2 = 2 PRINT 'UPDATE'
IF @permissions & 4 = 4 PRINT 'REFERENCES'
END -- @column IS NOT NULL
ELSE -- @column IS NULL
BEGIN
SET @permissions = permissions(@objectid)
IF @permissions & 1 = 1 PRINT 'SELECT ALL'
IF @permissions & 2 = 2 PRINT 'UPDATE ALL'
IF @permissions & 4 = 4 PRINT 'REFERENCES ALL'
IF @permissions & 8 = 8 PRINT 'INSERT'
IF @permissions & 16 = 16 PRINT 'DELETE'
IF @permissions & 32 = 32 PRINT 'EXECUTE'
IF @permissions & 4096 = 4096 PRINT 'SELECT ANY (at least one column)'
IF @permissions & 8192 = 8192 PRINT 'UPDATE ANY (at least one column)'
IF @permissions & 16384 = 16384 PRINT 'REFERENCES ANY(at least one column)'
END -- @column IS NULL
END -- @objectid IS NOT NULL
ELSE -- @objectid IS NULL
BEGIN
SET @permissions = permissions()
PRINT 'Statement permissions for login: ' + SUSER_SNAME() + ', user: ' + USER_NAME() + '...'
IF @permissions & 1 = 1 PRINT 'CREATE DATABASE'
IF @permissions & 2 = 2 PRINT 'CREATE TABLE'
IF @permissions & 4 = 4 PRINT 'CREATE PROCEDURE'
IF @permissions & 8 = 8 PRINT 'CREATE VIEW'
IF @permissions & 16 = 16 PRINT 'CREATE RULE'
IF @permissions & 32 = 32 PRINT 'CREATE DEFAULT'
IF @permissions & 64 = 64 PRINT 'BACKUP DATABASE'
IF @permissions & 128 = 128 PRINT 'BACKUP LOG'
--IF @permissions & 256 = 256 PRINT 'Reserved'
END -- @objectid IS NULL
GO
grant exec on sp_ListPermissions to public
GO
--================================================
declare @chvDBName varchar(50)
set @chvDBName = db_name()
SELECT
'Database' = @chvDBName,
'UserOrRoleName' = sysusers.name,
'GrantType' = case protecttype
when 204 then 'RANT_W_GRANT'
when 205 then 'GRANT'
when 206 then 'REVOKE' end,
Permission = CASE action
WHEN 193 THEN 'SELECT'
WHEN 195 THEN 'INSERT'
WHEN 196 THEN 'DELETE'
WHEN 197 THEN 'UPDATE'
WHEN 26 THEN 'REFERENCE'
WHEN 224 THEN 'EXECUTE'
ELSE 'Unknown'
END
, 'ObjectName' = sysobjects.name
, 'ObjectType' = case when sysobjects.xtype = 'U' then 'Table'
when sysobjects.xtype = 'V' then 'View'
when sysobjects.xtype = 'P' then 'Procedure'
else 'Other'
end,
'Is_Role' = case when issqlrole = 1 then 'Yes' else 'No' end
FROM sysprotects, sysobjects, sysusers
WHERE sysobjects.id = sysprotects.id
AND sysprotects.action IN (193, 195, 196, 197, 224, 26)
AND sysprotects.uid = sysusers.uid
AND sysobjects.name not like 'dt[_]%'
AND sysobjects.name not like 'dt%'
AND sysobjects.name not like 'sel[_]%'
AND sysobjects.name not like 'sp_ins[_]%'
AND sysobjects.name not like 'sp_upd[_]%'
AND sysobjects.name not like 'sp_sel[_]%'
AND sysobjects.name not like 'sp_cft[_]%'
AND sysobjects.name not like 'ctsv[_]%'
AND sysobjects.name not like 'tsvw[_]%'
AND sysusers.name not like 'MSmerge[_]%'
AND sysobjects.xtype <> 'S'
AND sysobjects.name not in ('MSsubscription_agents', 'sysmergearticles', 'sysconstraints', 'syssegments')
ORDER BY sysusers.name, sysobjects.xtype, sysobjects.name
GO
--**************************************
--
-- Name: SQL Server Login Info
-- Description:Retrieve the SQL Server l
-- ogin configuration, trusted logins etc.
-- By: Umachandar
--
--This code is copyrighted and has-- limited warranties.Please see http://
-- www.Planet-Source-Code.com/vb/scripts/Sh
-- owCode.asp?txtCodeId=236&lngWId=5--for details.--**************************************
--
USE master
go
IF object_id('sp_Mylogininfo') IS NOT NULL
DROP PROCEDURE sp_Mylogininfo
go
CREATE PROCEDURE sp_Mylogininfo
(
@loginmode varchar(30) = NULL OUTPUT,
@integrated varchar(30) = NULL OUTPUT,
@showdetails bit = 0
)
as
/********************************************************************************/
/* Created BY : Umachandar Jayachandran (UC) */
/* Created ON : 20 October 1996 */
/* Description: This stored PROCEDURE can be used to obtain information */
/* about a SQL Server login. IF executed WITH no parameters*/
/* the stored PROCEDURE will report IF the CURRENT USER IS */
/* connected through standard OR integrated/mixed type */
/* login. The output can also be obtained through variables*/
/* TO make it easy to USE FROM other SPs. The showdetails */
/* flag will display the CURRENT NT logins AND groups which*/
/* have been granted access TO SQL Server. */
/********************************************************************************/
/* Resources : http://www.umachandar.com/resources.htm */
/********************************************************************************/
SET nocount ON
SET ansi_defaults OFF
DECLARE @account varchar(30), @nt_domain varchar(30), @nt_username varchar(30),
@privilege varchar(30), @showresults bit
IF @loginmode IS NULL or @integrated IS NULL
SELECT @showresults = 1
CREATE TABLE #loginconfig (name varchar(30), config_value varchar(30) null)
INSERT #loginconfig EXEC master..xp_loginconfig
SELECT @loginmode = config_value FROM #loginconfig WHERE name = 'login mode'
IF @loginmode = 'standard'
begin
SELECT @integrated = 'No'
GOTO SHOW_RESULTS_LABEL
end
CREATE TABLE #logininfo (account varchar(60), type varchar(30),
privilege varchar(30) null, mapped_login varchar(60) null,
permission_path varchar(255) null)
INSERT #logininfo EXEC master..xp_logininfo
DECLARE accounts insensitive CURSOR FOR
SELECT account FROM #logininfo WHERE charindex('SYSTEM', account) = 0
OPEN accounts
while('FETCH IS OK' = 'FETCH IS OK')
begin
FETCH next FROM accounts INTO @account
IF @@fetch_status < 0 BREAK
INSERT #logininfo EXEC master..xp_logininfo @account, 'members'
end
DEALLOCATE accounts
SELECT @nt_domain = nt_domain, @nt_username = nt_username FROM sysprocesses
WHERE spid = @@spid
-- First check for sa
IF suser_id() = 1
begin
-- Next CHECK FOR NT USER privilege
IF exists( SELECT privilege FROM #logininfo
WHERE account = @nt_domain + '\' + @nt_username
AND privilege = 'admin')
SELECT @integrated = 'Yes'
ELSE
SELECT @integrated = 'No'
GOTO SHOW_RESULTS_LABEL
end
-- Second check for users
SELECT @integrated = CASE WHEN @nt_username = suser_name() THEN 'Yes' ELSE 'No' END
SHOW_RESULTS_LABEL:
IF @showresults = 1
begin
SELECT @loginmode AS "Server Login Mode",
@integrated AS "Integrated"
end
IF @showdetails = 1
begin
PRINT ''
PRINT 'Server Security Configuration'
SELECT * FROM #loginconfig
PRINT ''
PRINT 'Server Security Details'
SELECT * FROM #logininfo
end
go
GRANT EXECUTE on sp_Mylogininfo TO PUBLIC
go
-- Usage Examples:
PRINT 'With no parameters...'
EXEC sp_Mylogininfo
PRINT ''
go
PRINT 'With show details option...'
EXEC sp_Mylogininfo @showdetails = 1
PRINT ''
go
PRINT 'With output parameters...'
DECLARE @loginmode varchar(30), @integrated varchar(30)
SELECT @loginmode = '', @integrated = ''
EXEC sp_Mylogininfo @loginmode out, @integrated out
SELECT @loginmode AS LoginMode, @integrated as Integrated
GO
IF OBJECT_ID('sp_ListPermissions') IS NOT NULL
AND OBJECTPROPERTY(OBJECT_ID
('sp_ListPermissions'),'IsProcedure')=1
DROP PROCEDURE sp_ListPermissions
GO
CREATE PROCEDURE sp_ListPermissions
@object nvarchar(515) = NULL,
@column sysname = NULL
AS
/*
Procedure to list a uses effective permissions
Author : Itzik Ben−Gan
*/
SET NOCOUNT ON
DECLARE @permissions int
DECLARE @objectid int
SET @objectid = object_id(@object)
--Check input
IF @object IS NOT NULL AND @objectid IS NULL
BEGIN
RAISERROR('Invalid object %s supplied.', 16, 1, @object)
RETURN 1
END
IF @objectid IS NOT NULL
BEGIN
PRINT 'Object permissions on: ' + @object + ' for
login: ' + SUSER_SNAME()
+ ', user: ' + USER_NAME() + '...'
IF @column IS NOT NULL
BEGIN
SET @permissions = permissions(@objectid, @column)
PRINT 'On column: ' + @column + '...'
IF @permissions & 1 = 1 PRINT 'SELECT'
IF @permissions & 2 = 2 PRINT 'UPDATE'
IF @permissions & 4 = 4 PRINT 'REFERENCES'
END -- @column IS NOT NULL
ELSE -- @column IS NULL
BEGIN
SET @permissions = permissions(@objectid)
IF @permissions & 1 = 1 PRINT 'SELECT ALL'
IF @permissions & 2 = 2 PRINT 'UPDATE ALL'
IF @permissions & 4 = 4 PRINT 'REFERENCES ALL'
IF @permissions & 8 = 8 PRINT 'INSERT'
IF @permissions & 16 = 16 PRINT 'DELETE'
IF @permissions & 32 = 32 PRINT 'EXECUTE'
IF @permissions & 4096 = 4096 PRINT 'SELECT ANY (at least one column)'
IF @permissions & 8192 = 8192 PRINT 'UPDATE ANY (at least one column)'
IF @permissions & 16384 = 16384 PRINT 'REFERENCES ANY(at least one column)'
END -- @column IS NULL
END -- @objectid IS NOT NULL
ELSE -- @objectid IS NULL
BEGIN
SET @permissions = permissions()
PRINT 'Statement permissions for login: ' + SUSER_SNAME() + ', user: ' + USER_NAME() + '...'
IF @permissions & 1 = 1 PRINT 'CREATE DATABASE'
IF @permissions & 2 = 2 PRINT 'CREATE TABLE'
IF @permissions & 4 = 4 PRINT 'CREATE PROCEDURE'
IF @permissions & 8 = 8 PRINT 'CREATE VIEW'
IF @permissions & 16 = 16 PRINT 'CREATE RULE'
IF @permissions & 32 = 32 PRINT 'CREATE DEFAULT'
IF @permissions & 64 = 64 PRINT 'BACKUP DATABASE'
IF @permissions & 128 = 128 PRINT 'BACKUP LOG'
--IF @permissions & 256 = 256 PRINT 'Reserved'
END -- @objectid IS NULL
GO
grant exec on sp_ListPermissions to public
GO
--================================================
declare @chvDBName varchar(50)
set @chvDBName = db_name()
SELECT
'Database' = @chvDBName,
'UserOrRoleName' = sysusers.name,
'GrantType' = case protecttype
when 204 then 'RANT_W_GRANT'
when 205 then 'GRANT'
when 206 then 'REVOKE' end,
Permission = CASE action
WHEN 193 THEN 'SELECT'
WHEN 195 THEN 'INSERT'
WHEN 196 THEN 'DELETE'
WHEN 197 THEN 'UPDATE'
WHEN 26 THEN 'REFERENCE'
WHEN 224 THEN 'EXECUTE'
ELSE 'Unknown'
END
, 'ObjectName' = sysobjects.name
, 'ObjectType' = case when sysobjects.xtype = 'U' then 'Table'
when sysobjects.xtype = 'V' then 'View'
when sysobjects.xtype = 'P' then 'Procedure'
else 'Other'
end,
'Is_Role' = case when issqlrole = 1 then 'Yes' else 'No' end
FROM sysprotects, sysobjects, sysusers
WHERE sysobjects.id = sysprotects.id
AND sysprotects.action IN (193, 195, 196, 197, 224, 26)
AND sysprotects.uid = sysusers.uid
AND sysobjects.name not like 'dt[_]%'
AND sysobjects.name not like 'dt%'
AND sysobjects.name not like 'sel[_]%'
AND sysobjects.name not like 'sp_ins[_]%'
AND sysobjects.name not like 'sp_upd[_]%'
AND sysobjects.name not like 'sp_sel[_]%'
AND sysobjects.name not like 'sp_cft[_]%'
AND sysobjects.name not like 'ctsv[_]%'
AND sysobjects.name not like 'tsvw[_]%'
AND sysusers.name not like 'MSmerge[_]%'
AND sysobjects.xtype <> 'S'
AND sysobjects.name not in ('MSsubscription_agents', 'sysmergearticles', 'sysconstraints', 'syssegments')
ORDER BY sysusers.name, sysobjects.xtype, sysobjects.name
GO
--**************************************
--
-- Name: SQL Server Login Info
-- Description:Retrieve the SQL Server l
-- ogin configuration, trusted logins etc.
-- By: Umachandar
--
--This code is copyrighted and has-- limited warranties.Please see http://
-- www.Planet-Source-Code.com/vb/scripts/Sh
-- owCode.asp?txtCodeId=236&lngWId=5--for details.--**************************************
--
USE master
go
IF object_id('sp_Mylogininfo') IS NOT NULL
DROP PROCEDURE sp_Mylogininfo
go
CREATE PROCEDURE sp_Mylogininfo
(
@loginmode varchar(30) = NULL OUTPUT,
@integrated varchar(30) = NULL OUTPUT,
@showdetails bit = 0
)
as
/********************************************************************************/
/* Created BY : Umachandar Jayachandran (UC) */
/* Created ON : 20 October 1996 */
/* Description: This stored PROCEDURE can be used to obtain information */
/* about a SQL Server login. IF executed WITH no parameters*/
/* the stored PROCEDURE will report IF the CURRENT USER IS */
/* connected through standard OR integrated/mixed type */
/* login. The output can also be obtained through variables*/
/* TO make it easy to USE FROM other SPs. The showdetails */
/* flag will display the CURRENT NT logins AND groups which*/
/* have been granted access TO SQL Server. */
/********************************************************************************/
/* Resources : http://www.umachandar.com/resources.htm */
/********************************************************************************/
SET nocount ON
SET ansi_defaults OFF
DECLARE @account varchar(30), @nt_domain varchar(30), @nt_username varchar(30),
@privilege varchar(30), @showresults bit
IF @loginmode IS NULL or @integrated IS NULL
SELECT @showresults = 1
CREATE TABLE #loginconfig (name varchar(30), config_value varchar(30) null)
INSERT #loginconfig EXEC master..xp_loginconfig
SELECT @loginmode = config_value FROM #loginconfig WHERE name = 'login mode'
IF @loginmode = 'standard'
begin
SELECT @integrated = 'No'
GOTO SHOW_RESULTS_LABEL
end
CREATE TABLE #logininfo (account varchar(60), type varchar(30),
privilege varchar(30) null, mapped_login varchar(60) null,
permission_path varchar(255) null)
INSERT #logininfo EXEC master..xp_logininfo
DECLARE accounts insensitive CURSOR FOR
SELECT account FROM #logininfo WHERE charindex('SYSTEM', account) = 0
OPEN accounts
while('FETCH IS OK' = 'FETCH IS OK')
begin
FETCH next FROM accounts INTO @account
IF @@fetch_status < 0 BREAK
INSERT #logininfo EXEC master..xp_logininfo @account, 'members'
end
DEALLOCATE accounts
SELECT @nt_domain = nt_domain, @nt_username = nt_username FROM sysprocesses
WHERE spid = @@spid
-- First check for sa
IF suser_id() = 1
begin
-- Next CHECK FOR NT USER privilege
IF exists( SELECT privilege FROM #logininfo
WHERE account = @nt_domain + '\' + @nt_username
AND privilege = 'admin')
SELECT @integrated = 'Yes'
ELSE
SELECT @integrated = 'No'
GOTO SHOW_RESULTS_LABEL
end
-- Second check for users
SELECT @integrated = CASE WHEN @nt_username = suser_name() THEN 'Yes' ELSE 'No' END
SHOW_RESULTS_LABEL:
IF @showresults = 1
begin
SELECT @loginmode AS "Server Login Mode",
@integrated AS "Integrated"
end
IF @showdetails = 1
begin
PRINT ''
PRINT 'Server Security Configuration'
SELECT * FROM #loginconfig
PRINT ''
PRINT 'Server Security Details'
SELECT * FROM #logininfo
end
go
GRANT EXECUTE on sp_Mylogininfo TO PUBLIC
go
-- Usage Examples:
PRINT 'With no parameters...'
EXEC sp_Mylogininfo
PRINT ''
go
PRINT 'With show details option...'
EXEC sp_Mylogininfo @showdetails = 1
PRINT ''
go
PRINT 'With output parameters...'
DECLARE @loginmode varchar(30), @integrated varchar(30)
SELECT @loginmode = '', @integrated = ''
EXEC sp_Mylogininfo @loginmode out, @integrated out
SELECT @loginmode AS LoginMode, @integrated as Integrated
sp_Mylogininfo --Return Error 8198 when Execute
USE master
go
IF object_id('sp_Mylogininfo') IS NOT NULL
DROP PROCEDURE sp_Mylogininfo
go
CREATE PROCEDURE sp_Mylogininfo
(
@loginmode varchar(30) = NULL OUTPUT,
@integrated varchar(30) = NULL OUTPUT,
@showdetails bit = 0
)
as
SET nocount ON
SET ansi_defaults OFF
DECLARE @account varchar(30), @nt_domain varchar(30), @nt_username varchar(30),
@privilege varchar(30), @showresults bit
IF @loginmode IS NULL or @integrated IS NULL
SELECT @showresults = 1
CREATE TABLE #loginconfig (name varchar(30), config_value varchar(30) null)
INSERT #loginconfig EXEC master..xp_loginconfig
SELECT @loginmode = config_value FROM #loginconfig WHERE name = 'login mode'
IF @loginmode = 'standard'
begin
SELECT @integrated = 'No'
GOTO SHOW_RESULTS_LABEL
end
CREATE TABLE #logininfo (account varchar(60), type varchar(30),
privilege varchar(30) null, mapped_login varchar(60) null,
permission_path varchar(255) null)
INSERT #logininfo EXEC master..xp_logininfo
DECLARE accounts insensitive CURSOR FOR
SELECT account FROM #logininfo WHERE charindex('SYSTEM', account) = 0
OPEN accounts
while('FETCH IS OK' = 'FETCH IS OK')
begin
FETCH next FROM accounts INTO @account
IF @@fetch_status < 0 BREAK
INSERT #logininfo EXEC master..xp_logininfo @account, 'members'
end
DEALLOCATE accounts
SELECT @nt_domain = nt_domain, @nt_username = nt_username FROM sysprocesses
WHERE spid = @@spid
-- First check for sa
IF suser_id() = 1
begin
-- Next CHECK FOR NT USER privilege
IF exists( SELECT privilege FROM #logininfo
WHERE account = @nt_domain + '\' + @nt_username
AND privilege = 'admin')
SELECT @integrated = 'Yes'
ELSE
SELECT @integrated = 'No'
GOTO SHOW_RESULTS_LABEL
end
-- Second check for users
SELECT @integrated = CASE WHEN @nt_username = suser_name() THEN 'Yes' ELSE 'No' END
SHOW_RESULTS_LABEL:
IF @showresults = 1
begin
SELECT @loginmode AS "Server Login Mode",
@integrated AS "Integrated"
end
IF @showdetails = 1
begin
PRINT ''
PRINT 'Server Security Configuration'
SELECT * FROM #loginconfig
PRINT ''
PRINT 'Server Security Details'
SELECT * FROM #logininfo
end
go
GRANT EXECUTE on sp_Mylogininfo TO PUBLIC
go
-- Usage Examples:
PRINT 'With no parameters...'
EXEC sp_Mylogininfo
PRINT ''
go
PRINT 'With show details option...'
EXEC sp_Mylogininfo @showdetails = 1
PRINT ''
go
PRINT 'With output parameters...'
DECLARE @loginmode varchar(30), @integrated varchar(30)
SELECT @loginmode = '', @integrated = ''
EXEC sp_Mylogininfo @loginmode out, @integrated out
SELECT @loginmode AS LoginMode, @integrated as Integrated
go
IF object_id('sp_Mylogininfo') IS NOT NULL
DROP PROCEDURE sp_Mylogininfo
go
CREATE PROCEDURE sp_Mylogininfo
(
@loginmode varchar(30) = NULL OUTPUT,
@integrated varchar(30) = NULL OUTPUT,
@showdetails bit = 0
)
as
SET nocount ON
SET ansi_defaults OFF
DECLARE @account varchar(30), @nt_domain varchar(30), @nt_username varchar(30),
@privilege varchar(30), @showresults bit
IF @loginmode IS NULL or @integrated IS NULL
SELECT @showresults = 1
CREATE TABLE #loginconfig (name varchar(30), config_value varchar(30) null)
INSERT #loginconfig EXEC master..xp_loginconfig
SELECT @loginmode = config_value FROM #loginconfig WHERE name = 'login mode'
IF @loginmode = 'standard'
begin
SELECT @integrated = 'No'
GOTO SHOW_RESULTS_LABEL
end
CREATE TABLE #logininfo (account varchar(60), type varchar(30),
privilege varchar(30) null, mapped_login varchar(60) null,
permission_path varchar(255) null)
INSERT #logininfo EXEC master..xp_logininfo
DECLARE accounts insensitive CURSOR FOR
SELECT account FROM #logininfo WHERE charindex('SYSTEM', account) = 0
OPEN accounts
while('FETCH IS OK' = 'FETCH IS OK')
begin
FETCH next FROM accounts INTO @account
IF @@fetch_status < 0 BREAK
INSERT #logininfo EXEC master..xp_logininfo @account, 'members'
end
DEALLOCATE accounts
SELECT @nt_domain = nt_domain, @nt_username = nt_username FROM sysprocesses
WHERE spid = @@spid
-- First check for sa
IF suser_id() = 1
begin
-- Next CHECK FOR NT USER privilege
IF exists( SELECT privilege FROM #logininfo
WHERE account = @nt_domain + '\' + @nt_username
AND privilege = 'admin')
SELECT @integrated = 'Yes'
ELSE
SELECT @integrated = 'No'
GOTO SHOW_RESULTS_LABEL
end
-- Second check for users
SELECT @integrated = CASE WHEN @nt_username = suser_name() THEN 'Yes' ELSE 'No' END
SHOW_RESULTS_LABEL:
IF @showresults = 1
begin
SELECT @loginmode AS "Server Login Mode",
@integrated AS "Integrated"
end
IF @showdetails = 1
begin
PRINT ''
PRINT 'Server Security Configuration'
SELECT * FROM #loginconfig
PRINT ''
PRINT 'Server Security Details'
SELECT * FROM #logininfo
end
go
GRANT EXECUTE on sp_Mylogininfo TO PUBLIC
go
-- Usage Examples:
PRINT 'With no parameters...'
EXEC sp_Mylogininfo
PRINT ''
go
PRINT 'With show details option...'
EXEC sp_Mylogininfo @showdetails = 1
PRINT ''
go
PRINT 'With output parameters...'
DECLARE @loginmode varchar(30), @integrated varchar(30)
SELECT @loginmode = '', @integrated = ''
EXEC sp_Mylogininfo @loginmode out, @integrated out
SELECT @loginmode AS LoginMode, @integrated as Integrated
Discover the name of the Windows group that allowed you to connect
If you need name of the Windows group that allowed you to connect:
==================================================================
If you need name of the Windows group that allowed the user to connect, take
a look at xp_logininfo and the 'mapped user name' column in the result.
You'll need to insert the results into a table like the example below if you
need a scalar result. See xp_logininfo in the Books Online for permission
requirements.
-------------------------------------------:
http://www.megasolutions.net/Sqlserver/Using-suser_id()-and-sys_server_principals-when-connecting-through-a-windows-group-7608.aspx
SET NOCOUNT ON
DECLARE
@me sysname,
@permission_path sysname
SET @me = SUSER_SNAME()
CREATE TABLE #LoginInfo
(
account_name sysname,
logintype char(8),
privilege char(9),
mapped_login_name sysname,
permission_path sysname
)
INSERT INTO #LoginInfo
EXEC xp_logininfo @me
SELECT @permission_path = permission_path
FROM #LoginInfo
DROP TABLE #LoginInfo
SELECT @permission_path
GO
==================================================================
If you need name of the Windows group that allowed the user to connect, take
a look at xp_logininfo and the 'mapped user name' column in the result.
You'll need to insert the results into a table like the example below if you
need a scalar result. See xp_logininfo in the Books Online for permission
requirements.
-------------------------------------------:
http://www.megasolutions.net/Sqlserver/Using-suser_id()-and-sys_server_principals-when-connecting-through-a-windows-group-7608.aspx
SET NOCOUNT ON
DECLARE
@me sysname,
@permission_path sysname
SET @me = SUSER_SNAME()
CREATE TABLE #LoginInfo
(
account_name sysname,
logintype char(8),
privilege char(9),
mapped_login_name sysname,
permission_path sysname
)
INSERT INTO #LoginInfo
EXEC xp_logininfo @me
SELECT @permission_path = permission_path
FROM #LoginInfo
DROP TABLE #LoginInfo
SELECT @permission_path
GO
Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
--------------------------------------------------------------------
http://support.microsoft.com/kb/272318
When DBCC SHRINKFILE is run, SQL Server 2000 shrinks the log file by removing as many virtual log files as it can to attempt to reach the target size. If the target file size is not reached, SQL Server places dummy log entries in the last virtual log file until the virtual log is filled and moves the head of the log to the beginning of the file. The following actions are then required to complete the shrinking of the transaction log:
You must run a BACKUP LOG statement to free up space by removing the inactive portion of the log.
You must run DBCC SHRINKFILE again with the desired target size until the log file shrinks to the target size.
The following example demonstrates this with the pubs database and attempts to shrink the pubs_log file to 2 MB:
Run this code:
DBCC SHRINKFILE(pubs_log, 2)
NOTE: If the target size is not reached, proceed to the next step.
Run this code if you want to truncate the transaction log and not keep a backup of the transaction log. Truncate_only invalidates your transaction log backup sequence. Take a full backup of your database after you perform backup log with truncate_only:
BACKUP LOG pubs WITH TRUNCATE_ONLY
-or-
Run this code if you want to keep a backup of your transaction log and keep your transaction log backup sequence intact. See SQL Server Books Online topic "BACKUP" for more information:
BACKUP LOG pubs TO pubslogbackup
Run this code:
DBCC SHRINKFILE(pubs_log,2)
--------------------------------------------------------------------
http://support.microsoft.com/kb/272318
When DBCC SHRINKFILE is run, SQL Server 2000 shrinks the log file by removing as many virtual log files as it can to attempt to reach the target size. If the target file size is not reached, SQL Server places dummy log entries in the last virtual log file until the virtual log is filled and moves the head of the log to the beginning of the file. The following actions are then required to complete the shrinking of the transaction log:
You must run a BACKUP LOG statement to free up space by removing the inactive portion of the log.
You must run DBCC SHRINKFILE again with the desired target size until the log file shrinks to the target size.
The following example demonstrates this with the pubs database and attempts to shrink the pubs_log file to 2 MB:
Run this code:
DBCC SHRINKFILE(pubs_log, 2)
NOTE: If the target size is not reached, proceed to the next step.
Run this code if you want to truncate the transaction log and not keep a backup of the transaction log. Truncate_only invalidates your transaction log backup sequence. Take a full backup of your database after you perform backup log with truncate_only:
BACKUP LOG pubs WITH TRUNCATE_ONLY
-or-
Run this code if you want to keep a backup of your transaction log and keep your transaction log backup sequence intact. See SQL Server Books Online topic "BACKUP" for more information:
BACKUP LOG pubs TO pubslogbackup
Run this code:
DBCC SHRINKFILE(pubs_log,2)
woensdag 26 november 2008
Script permissions on all databases
use master
go
begin
declare @databasename varchar(30)
declare cur cursor for
select name from sysdatabases
create table #result
(dbname varchar(30),result varchar(300))
open cur
fetch next from cur into @databasename
while(@@fetch_status=0)
begin
create table #t
(a1 varchar(50)
,a2 varchar(50)
,a3 varchar(50)
,a4 varchar(50)
,a5 varchar(50)
,a6 varchar(50)
,a7 varchar(50))
insert into #t exec sp_helprotect @username = null
insert into #result select @databasename,a5+' '+a6+' on ['+a1+'].['+a2+']'+
CASE
WHEN (PATINDEX('%All%', a7)=0) and (a7 <> '.')
THEN ' ('+a7+')'
ELSE ''
END+' to ['+a3+']' from #t
drop table #t
fetch next from cur into @databasename
end
select * from #result
close cur
deallocate cur
drop table #result
end
go
------------------------------------
http://www.sqlservercentral.com/scripts/Security/62477/
go
begin
declare @databasename varchar(30)
declare cur cursor for
select name from sysdatabases
create table #result
(dbname varchar(30),result varchar(300))
open cur
fetch next from cur into @databasename
while(@@fetch_status=0)
begin
create table #t
(a1 varchar(50)
,a2 varchar(50)
,a3 varchar(50)
,a4 varchar(50)
,a5 varchar(50)
,a6 varchar(50)
,a7 varchar(50))
insert into #t exec sp_helprotect @username = null
insert into #result select @databasename,a5+' '+a6+' on ['+a1+'].['+a2+']'+
CASE
WHEN (PATINDEX('%All%', a7)=0) and (a7 <> '.')
THEN ' ('+a7+')'
ELSE ''
END+' to ['+a3+']' from #t
drop table #t
fetch next from cur into @databasename
end
select * from #result
close cur
deallocate cur
drop table #result
end
go
------------------------------------
http://www.sqlservercentral.com/scripts/Security/62477/
ACCESSING REMOTE DATA SOURCE (Linked Servers and Ad Hoc Queries)
http://dbdigger.blogspot.com/search/label/Linked%20Servers%20for%20SQL%20Server:
===============================================================================
In some situations a SQL Server DBA is required to fetch data from another data source. Other data source may be another instance of SQL Server or any other RDBMS. Also it may be a file. SQL Server 2005 provides two major ways to access data from other data sources. These are
Linked Servers (may use distributed queries)
Ad hoc queries with openrowset
In following elaboration of using these two ways I will discuss SQL Server as remote data source.
CONFIGURATION AND USAGE OF LINKED SERVERS:
------------------------------------------
Linked servers provide SQL Server with access to remote data sources that may be SQL Server also or any other relational DBMS or text file. Remote data sources are connected through OLE DB provider. OLE DB selection determines that what kind of distributed query operations may be implemented.
Create Linked Server
--------------------:
To create the linked server, use the system stored procedure sp_addlinkedserver. To create a linked server for a SQL Server instance named test on host DBA, we will have following format
EXEC sp_addlinkedserver @server= 'dba\test',
@srvproduct= 'SQL Server'
GO
Or if it is default instance then you may just enter host name instead of host\instance format.
Change Any Property Of Linked Server
------------------------------------:
After a linked server has been created then you may change its several properties like collation compatibility, collation name, connection timeout, data access and query time out etc. For example to change query time out property to 60 seconds for the created linked server, I will use following system stored procedure
EXEC sp_serveroption
@server = 'dba\test' ,
@optname = 'query timeout',
@optvalue = 60
View Meta Data Of Linked Server:
---------------------------------
Now we have a linked server with query time out property changed to 60 seconds.
In order to view the meta data of this linked servers use following script.
SELECT [name], data_source,query_timeout, modify_date
FROM sys.servers
WHERE is_linked = 1
GO
Configure Logins Of Linked Server:
----------------------------------
Now we have a linked server attached. But to access the data on it we have to map proper logins as required. In following script I have mapped the user dba (my local user) to user sa of linked server (remote data source). If I set the values of parameter @locallogin = null then all my local users will be mapped against the user sa of remote data source. If yoy set the parameter @useself = true then windows authentication will be used for connection to linked server. Also we have to provide password for remote user that is sa in this case.
EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'dba\test',
@useself = false ,
@locallogin = 'dba', -- if null Applies to all local logins
@rmtuser = 'sa',
@rmtpassword = 'test'
GO
View Login Information Of Linked Server:
-----------------------------------------
To view the login mapping for remote server use the following script.
SELECT s.name LinkedServerName, ll.remote_name, p.name LocalLoginName
FROM sys.linked_logins ll
INNER JOIN sys.servers s
ON s.server_id = ll.server_id
LEFT OUTER JOIN sys.server_principals p
ON p.principal_id = ll.local_principal_id
WHERE s.is_linked = 1
Go
Executing the Queries
----------------------:
Now linked server is configured and logins are also mapped properly. It is now time to execute remote queries as it is actual purpose of all these configurations. Distributed queries reference one or more linked servers. And perform read or update operations against remote tables, views, or stored procedures. The types of query operations that are supported against linked servers depend on the level of support for transactions present in the OLE DB providers used. The basic syntax for referencing a linked server is using a four-part name. To fetch data from pubs.authors of dba\test server
SELECT * FROM [dba\test].pubs.dbo.authors
GO
To execute a system-stored procedure on the linked server
--------------------------------------------------------:
EXEC [dba\test].master.dbo.sp_monitor
GO
Tired Of Using Four Part Name:
------------------------------
If it is not convenient to use four parts lengthy name then you may create a synonym for whole name.
To create a synonym mySyn for whole four parts name
CREATE SYNONYM testSynonym FOR [dba\test].pubs.dbo.authors
GO
Using OPENQUERY :
-----------------
It is relevant to mention here that SQL Server provides another way to execute distributed queries other than using the four parts naming method. OPENQUERY
is a function that issues a pass-through query against an existing linked server and is referenced in the FROM clause of a query just like a table. The syntax is as follows:
OPENQUERY ( linked_server ,'query' )
GO
WIPE OUT :
---------
Now its time to see that how to drop the craeted objects like linked servers, login mappings and synonyms.
--To drop the mapped login that is in our case dba sp_droplinkedsrvlogin 'dba\test' , 'dba'
GO --To drop the linked server dba\test sp_dropserver 'dba\test'
GO --To directly drop linked server along with all mapped logins
sp_dropserver 'dba\test', 'droplogins'
GO --To drop the synonym mySyn for linked server four parts name
drop synonym testSynonym
GO
Ad Hoc QUERIES USING OPENROWSET :
---------------------------------
In some cases it is not required to retain the connection from linked server for a long time. So to execute such Ad hoc queries OPENROWSET command is used. It is referenced in the FROM clause and acts like a table in a SELECT statement. OPENROWSET creates an ad hoc connection to the data and does not use an existing linked server connection to query the remote data source.
This property to use OPENROWSET to query a remote data source is off by default in SQL Server 2005. You may turn it on through surface area configuration.
USAGE
SELECT *
FROM OPENROWSET('SQLNCLI','dba\test';'sa';'test','SELECT * from pubs..authors')
Go
===============================================================================
In some situations a SQL Server DBA is required to fetch data from another data source. Other data source may be another instance of SQL Server or any other RDBMS. Also it may be a file. SQL Server 2005 provides two major ways to access data from other data sources. These are
Linked Servers (may use distributed queries)
Ad hoc queries with openrowset
In following elaboration of using these two ways I will discuss SQL Server as remote data source.
CONFIGURATION AND USAGE OF LINKED SERVERS:
------------------------------------------
Linked servers provide SQL Server with access to remote data sources that may be SQL Server also or any other relational DBMS or text file. Remote data sources are connected through OLE DB provider. OLE DB selection determines that what kind of distributed query operations may be implemented.
Create Linked Server
--------------------:
To create the linked server, use the system stored procedure sp_addlinkedserver. To create a linked server for a SQL Server instance named test on host DBA, we will have following format
EXEC sp_addlinkedserver @server= 'dba\test',
@srvproduct= 'SQL Server'
GO
Or if it is default instance then you may just enter host name instead of host\instance format.
Change Any Property Of Linked Server
------------------------------------:
After a linked server has been created then you may change its several properties like collation compatibility, collation name, connection timeout, data access and query time out etc. For example to change query time out property to 60 seconds for the created linked server, I will use following system stored procedure
EXEC sp_serveroption
@server = 'dba\test' ,
@optname = 'query timeout',
@optvalue = 60
View Meta Data Of Linked Server:
---------------------------------
Now we have a linked server with query time out property changed to 60 seconds.
In order to view the meta data of this linked servers use following script.
SELECT [name], data_source,query_timeout, modify_date
FROM sys.servers
WHERE is_linked = 1
GO
Configure Logins Of Linked Server:
----------------------------------
Now we have a linked server attached. But to access the data on it we have to map proper logins as required. In following script I have mapped the user dba (my local user) to user sa of linked server (remote data source). If I set the values of parameter @locallogin = null then all my local users will be mapped against the user sa of remote data source. If yoy set the parameter @useself = true then windows authentication will be used for connection to linked server. Also we have to provide password for remote user that is sa in this case.
EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'dba\test',
@useself = false ,
@locallogin = 'dba', -- if null Applies to all local logins
@rmtuser = 'sa',
@rmtpassword = 'test'
GO
View Login Information Of Linked Server:
-----------------------------------------
To view the login mapping for remote server use the following script.
SELECT s.name LinkedServerName, ll.remote_name, p.name LocalLoginName
FROM sys.linked_logins ll
INNER JOIN sys.servers s
ON s.server_id = ll.server_id
LEFT OUTER JOIN sys.server_principals p
ON p.principal_id = ll.local_principal_id
WHERE s.is_linked = 1
Go
Executing the Queries
----------------------:
Now linked server is configured and logins are also mapped properly. It is now time to execute remote queries as it is actual purpose of all these configurations. Distributed queries reference one or more linked servers. And perform read or update operations against remote tables, views, or stored procedures. The types of query operations that are supported against linked servers depend on the level of support for transactions present in the OLE DB providers used. The basic syntax for referencing a linked server is using a four-part name. To fetch data from pubs.authors of dba\test server
SELECT * FROM [dba\test].pubs.dbo.authors
GO
To execute a system-stored procedure on the linked server
--------------------------------------------------------:
EXEC [dba\test].master.dbo.sp_monitor
GO
Tired Of Using Four Part Name:
------------------------------
If it is not convenient to use four parts lengthy name then you may create a synonym for whole name.
To create a synonym mySyn for whole four parts name
CREATE SYNONYM testSynonym FOR [dba\test].pubs.dbo.authors
GO
Using OPENQUERY :
-----------------
It is relevant to mention here that SQL Server provides another way to execute distributed queries other than using the four parts naming method. OPENQUERY
is a function that issues a pass-through query against an existing linked server and is referenced in the FROM clause of a query just like a table. The syntax is as follows:
OPENQUERY ( linked_server ,'query' )
GO
WIPE OUT :
---------
Now its time to see that how to drop the craeted objects like linked servers, login mappings and synonyms.
--To drop the mapped login that is in our case dba sp_droplinkedsrvlogin 'dba\test' , 'dba'
GO --To drop the linked server dba\test sp_dropserver 'dba\test'
GO --To directly drop linked server along with all mapped logins
sp_dropserver 'dba\test', 'droplogins'
GO --To drop the synonym mySyn for linked server four parts name
drop synonym testSynonym
GO
Ad Hoc QUERIES USING OPENROWSET :
---------------------------------
In some cases it is not required to retain the connection from linked server for a long time. So to execute such Ad hoc queries OPENROWSET command is used. It is referenced in the FROM clause and acts like a table in a SELECT statement. OPENROWSET creates an ad hoc connection to the data and does not use an existing linked server connection to query the remote data source.
This property to use OPENROWSET to query a remote data source is off by default in SQL Server 2005. You may turn it on through surface area configuration.
USAGE
SELECT *
FROM OPENROWSET('SQLNCLI','dba\test';'sa';'test','SELECT * from pubs..authors')
Go
dinsdag 25 november 2008
My Script -- DeltaProject
ReadMe:
======
Voor het creëren en registreren van de uitgevoerde scripts in de databases Volg de onderste stappen:
creeër eerst de benodigde tabel en stored procedures:
----------------------------------------------------
1): Executeur eerst de 'S:\DataManagement\MS-SQL Beheer\Changes\DeltaProject\dam_delta_log_01.sql' Script.
2): Run daarna de 'S:\DataManagement\MS-SQL Beheer\Changes\DeltaProject\sp_StartScript_02.sql' Script
en de 'S:\DataManagement\MS-SQL Beheer\Changes\DeltaProject\sp_EndScript_03.sql' Script.
Voor de registratie van de Script in de 'dam_delta_log' tabel ,voer de volgende uit:
-----------------------------------------------------------------------------------
#): Run de 'Uw_Script.sql' Script en vul de benodigde parameters in:
Script_Nummer:
------------- De Nummer van de door u gemaakte script (of door RedGate).
Database_Naam:
------------- De naam van de database waarin de script uitgevoerd moet worden.
Script_Omschrijving:
------------------- De Script omschrijving.
Test:
=====
/*** --------------Test --------------------------------------------------------------
USE [Price]
GO
SET NOCOUNT ON
DECLARE @Script_Nummer varchar(50)
DECLARE @Script_Omschrijving varchar(50)
DECLARE @Database_Naam varchar(50)
set @Script_Nummer = 'Script_Nummer';
set @Script_Omschrijving = 'Omschrijving van de script';
set @Database_Naam = db_name();
exec BeheerDB..sp_StartScript @Script_Nummer,@Database_Naam,@Script_Omschrijving
select * from BeheerDB..dam_delta_log
GO
--Execute uw sscript------------------
--------------------------------------
exec BeheerDB..sp_EndScript @Script_Nummer
select * from BeheerDB..dam_delta_log
GO
-----------------------------------------------------------------------------------
USE [BeheerDB]
GO
/****** Object: Table [dbo].[dam_delta_log] Script Date: 11/21/2008 14:15:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[dam_delta_log](
[id] [int] IDENTITY(1,1) NOT NULL,
[delta] [varchar](50) NOT NULL,
[Omschrijving] [varchar](2000) NULL,
[Server] [varchar](100) NOT NULL CONSTRAINT [DF_ServerNaam] DEFAULT (@@servername),
[databasenaam] [varchar](100) NOT NULL CONSTRAINT [DF_DatabaseNaam] DEFAULT (db_name()),
[Gebruikernaam] [varchar](100) NOT NULL CONSTRAINT [DF_Gebruikernaam] DEFAULT (SYSTEM_USER),
[osuser] [varchar](100) NOT NULL CONSTRAINT [DF_Osuser] DEFAULT (suser_sname()),
[Hostnaam] [varchar](100) NOT NULL CONSTRAINT [DF_HostNaam] DEFAULT (host_name()),
[starttijd] [varchar](100) NOT NULL CONSTRAINT [DF_GetDate] DEFAULT (getdate()),
[eindtijd] [varchar](100) NULL,
CONSTRAINT [PK_dam_delta_log.dam_delta_log] PRIMARY KEY CLUSTERED
(
[id] ASC
)
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
--drop table dbo.dam_delta_log
------------------------------------------------------------------------------------
/*****************************************************************/
/* Script naam : sp_StartScript */
/* */
/* Server naam : <'Server_Naam'> */
/* */
/* Beschrijving :Dit Script wordt gemaakt voor het creëren van */
/* sp_StartScript' Stored Procedure in de 'BeheerDB'*/
/* database In Elke Server die een script moet */
/* uitgevoerd worden. */
/* */
/* */
/* File : S:\DataManagement\MS-SQL Beheer\Changes\ */
/* DeltaProject\sp_StartScript.sql */
/* */
/* Datum : 21-11-2008 */
/* Autore : Bahaa fadam */
/* Versie : */
/* Geupdated : */
/*****************************************************************/
--===============================================================
--# Script om Stored Procedure sp_StartScript te creëren =
--===============================================================
USE [BeheerDB]
GO
/****** Object: StoredProcedure [dbo].[sp_StartScript] Script Date: 11/24/2008 11:06:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_StartScript] (@Delta varchar(10),@databasenaam varchar(100),@Omschrijven varchar(2000))
AS
Declare @SQL as VARCHAR(2000)
Declare @RunDate as varchar(100)
Declare @i int
BEGIN TRANSACTION
SET NOCOUNT ON
SELECT @i=1
FROM master.dbo.sysdatabases
WHERE UPPER(Name) = UPPER(CONVERT(varchar(100),@databasenaam))
--zet tijd in variable om ook seconden te zien
SELECT @RunDate=convert(varchar, getdate(), 9)
If @i=1
INSERT INTO dbo.dam_delta_log (delta,Omschrijving,databasenaam,Gebruikernaam,Starttijd)
VALUES(CONVERT(varchar(100), @delta), CONVERT(varchar(100), @Omschrijven),@databasenaam,USER_NAME(),@RunDate )
print(@SQL)
EXEC(@SQL)
COMMIT TRANSACTION
------------------------------------------------------------------------------------
/*****************************************************************/
/* Script naam : sp_EndScript.sql */
/* */
/* Server naam : <'Server_Naam'> */
/* */
/* Beschrijving :Dit Script wordt gemaakt voor het creëren van */
/* 'sp_EndScript' Stored Procedure in de 'BeheerDB'*/
/* database In Elke Server die een script moet */
/* uitgevoerd worden. */
/* */
/* */
/* File : S:\DataManagement\MS-SQL Beheer\Changes\ */
/* DeltaProject\sp_EndScript.sql */
/* */
/* Datum : 21-11-2008 */
/* Autore : Bahaa fadam */
/* Versie : */
/* Geupdated : */
/*****************************************************************/
--===============================================================
--# Script om Stored Procedure sp_EndScript te creëren =
--===============================================================
USE [BeheerDB]
GO
/****** Object: StoredProcedure [dbo].[sp_EndScript] Script Date: 11/24/2008 11:38:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_EndScript]
@delta varchar(10)
AS
update BeheerDB..dam_delta_log
set eindtijd = convert(varchar, getdate(), 9)
where delta = @delta
and eindtijd is null
and starttijd = (select MAX(starttijd)
from BeheerDB..dam_delta_log
where delta = @delta
and eindtijd is null
)
GO
-----------------------------------------------------------------------------
DECLARE @Script_Nummer varchar(50)
DECLARE @Script_Omschrijving varchar(50)
DECLARE @Database_Naam varchar(50)
set @Script_Nummer = 'Script_Nummer';
set @Script_Omschrijving = 'Omschrijving van de script';
set @Database_Naam = db_name();
exec BeheerDB..sp_StartScript @Script_Nummer,@Database_Naam,@Script_Omschrijving
select * from BeheerDB..dam_delta_log
GO
--Execute uw script------------------
--
--------------------------------------
exec BeheerDB..sp_EndScript @Script_Nummer
select * from BeheerDB..dam_delta_log
GO
======
Voor het creëren en registreren van de uitgevoerde scripts in de databases Volg de onderste stappen:
creeër eerst de benodigde tabel en stored procedures:
----------------------------------------------------
1): Executeur eerst de 'S:\DataManagement\MS-SQL Beheer\Changes\DeltaProject\dam_delta_log_01.sql' Script.
2): Run daarna de 'S:\DataManagement\MS-SQL Beheer\Changes\DeltaProject\sp_StartScript_02.sql' Script
en de 'S:\DataManagement\MS-SQL Beheer\Changes\DeltaProject\sp_EndScript_03.sql' Script.
Voor de registratie van de Script in de 'dam_delta_log' tabel ,voer de volgende uit:
-----------------------------------------------------------------------------------
#): Run de 'Uw_Script.sql' Script en vul de benodigde parameters in:
Script_Nummer:
------------- De Nummer van de door u gemaakte script (of door RedGate).
Database_Naam:
------------- De naam van de database waarin de script uitgevoerd moet worden.
Script_Omschrijving:
------------------- De Script omschrijving.
Test:
=====
/*** --------------Test --------------------------------------------------------------
USE [Price]
GO
SET NOCOUNT ON
DECLARE @Script_Nummer varchar(50)
DECLARE @Script_Omschrijving varchar(50)
DECLARE @Database_Naam varchar(50)
set @Script_Nummer = 'Script_Nummer';
set @Script_Omschrijving = 'Omschrijving van de script';
set @Database_Naam = db_name();
exec BeheerDB..sp_StartScript @Script_Nummer,@Database_Naam,@Script_Omschrijving
select * from BeheerDB..dam_delta_log
GO
--Execute uw sscript------------------
--------------------------------------
exec BeheerDB..sp_EndScript @Script_Nummer
select * from BeheerDB..dam_delta_log
GO
-----------------------------------------------------------------------------------
USE [BeheerDB]
GO
/****** Object: Table [dbo].[dam_delta_log] Script Date: 11/21/2008 14:15:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[dam_delta_log](
[id] [int] IDENTITY(1,1) NOT NULL,
[delta] [varchar](50) NOT NULL,
[Omschrijving] [varchar](2000) NULL,
[Server] [varchar](100) NOT NULL CONSTRAINT [DF_ServerNaam] DEFAULT (@@servername),
[databasenaam] [varchar](100) NOT NULL CONSTRAINT [DF_DatabaseNaam] DEFAULT (db_name()),
[Gebruikernaam] [varchar](100) NOT NULL CONSTRAINT [DF_Gebruikernaam] DEFAULT (SYSTEM_USER),
[osuser] [varchar](100) NOT NULL CONSTRAINT [DF_Osuser] DEFAULT (suser_sname()),
[Hostnaam] [varchar](100) NOT NULL CONSTRAINT [DF_HostNaam] DEFAULT (host_name()),
[starttijd] [varchar](100) NOT NULL CONSTRAINT [DF_GetDate] DEFAULT (getdate()),
[eindtijd] [varchar](100) NULL,
CONSTRAINT [PK_dam_delta_log.dam_delta_log] PRIMARY KEY CLUSTERED
(
[id] ASC
)
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
--drop table dbo.dam_delta_log
------------------------------------------------------------------------------------
/*****************************************************************/
/* Script naam : sp_StartScript */
/* */
/* Server naam : <'Server_Naam'> */
/* */
/* Beschrijving :Dit Script wordt gemaakt voor het creëren van */
/* sp_StartScript' Stored Procedure in de 'BeheerDB'*/
/* database In Elke Server die een script moet */
/* uitgevoerd worden. */
/* */
/* */
/* File : S:\DataManagement\MS-SQL Beheer\Changes\ */
/* DeltaProject\sp_StartScript.sql */
/* */
/* Datum : 21-11-2008 */
/* Autore : Bahaa fadam */
/* Versie : */
/* Geupdated : */
/*****************************************************************/
--===============================================================
--# Script om Stored Procedure sp_StartScript te creëren =
--===============================================================
USE [BeheerDB]
GO
/****** Object: StoredProcedure [dbo].[sp_StartScript] Script Date: 11/24/2008 11:06:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_StartScript] (@Delta varchar(10),@databasenaam varchar(100),@Omschrijven varchar(2000))
AS
Declare @SQL as VARCHAR(2000)
Declare @RunDate as varchar(100)
Declare @i int
BEGIN TRANSACTION
SET NOCOUNT ON
SELECT @i=1
FROM master.dbo.sysdatabases
WHERE UPPER(Name) = UPPER(CONVERT(varchar(100),@databasenaam))
--zet tijd in variable om ook seconden te zien
SELECT @RunDate=convert(varchar, getdate(), 9)
If @i=1
INSERT INTO dbo.dam_delta_log (delta,Omschrijving,databasenaam,Gebruikernaam,Starttijd)
VALUES(CONVERT(varchar(100), @delta), CONVERT(varchar(100), @Omschrijven),@databasenaam,USER_NAME(),@RunDate )
print(@SQL)
EXEC(@SQL)
COMMIT TRANSACTION
------------------------------------------------------------------------------------
/*****************************************************************/
/* Script naam : sp_EndScript.sql */
/* */
/* Server naam : <'Server_Naam'> */
/* */
/* Beschrijving :Dit Script wordt gemaakt voor het creëren van */
/* 'sp_EndScript' Stored Procedure in de 'BeheerDB'*/
/* database In Elke Server die een script moet */
/* uitgevoerd worden. */
/* */
/* */
/* File : S:\DataManagement\MS-SQL Beheer\Changes\ */
/* DeltaProject\sp_EndScript.sql */
/* */
/* Datum : 21-11-2008 */
/* Autore : Bahaa fadam */
/* Versie : */
/* Geupdated : */
/*****************************************************************/
--===============================================================
--# Script om Stored Procedure sp_EndScript te creëren =
--===============================================================
USE [BeheerDB]
GO
/****** Object: StoredProcedure [dbo].[sp_EndScript] Script Date: 11/24/2008 11:38:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_EndScript]
@delta varchar(10)
AS
update BeheerDB..dam_delta_log
set eindtijd = convert(varchar, getdate(), 9)
where delta = @delta
and eindtijd is null
and starttijd = (select MAX(starttijd)
from BeheerDB..dam_delta_log
where delta = @delta
and eindtijd is null
)
GO
-----------------------------------------------------------------------------
DECLARE @Script_Nummer varchar(50)
DECLARE @Script_Omschrijving varchar(50)
DECLARE @Database_Naam varchar(50)
set @Script_Nummer = 'Script_Nummer';
set @Script_Omschrijving = 'Omschrijving van de script';
set @Database_Naam = db_name();
exec BeheerDB..sp_StartScript @Script_Nummer,@Database_Naam,@Script_Omschrijving
select * from BeheerDB..dam_delta_log
GO
--Execute uw script------------------
--
--------------------------------------
exec BeheerDB..sp_EndScript @Script_Nummer
select * from BeheerDB..dam_delta_log
GO
usp_FindTableUsage
USE [DBeheer]
GO
/****** Object: StoredProcedure [dbo].[usp_FindTableUsage] Script Date: 11/25/2008 13:58:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_FindTableUsage]
AS
SET NOCOUNT ON
DECLARE @vcTableList VARCHAR(8000)
SET @vcTableList = ''
SELECT @vcTableList = COALESCE(@vcTableList+ ', ', '') + name
from sysobjects where type='U'
--Create table to hold table names
DECLARE @tblTableArray TABLE
(
TableName varchar(40)
)
-- load table names into array table
INSERT INTO @tblTableArray
SELECT Element FROM
dbo.split(@vcTableList, ',')
PRINT ''
PRINT 'REPORT FOR TABLE DEPENDENCIES for TABLES:'
PRINT '-----------------------------------------'
PRINT CHAR(9)+CHAR(9)+ REPLACE(@vcTableList,',',CHAR(13)+CHAR(10)+CHAR(9)+CHAR(9))
PRINT ''
PRINT ''
PRINT 'STORED PROCEDURES:'
PRINT ''
SELECT DISTINCT t.TableName , SUBSTRING(o.NAME,1,60) AS [Procedure Name]
FROM sysobjects o
INNER JOIN syscomments c
ON o.ID = c.ID
INNER JOIN @tblTableArray t
ON c.Text LIKE '%[ ,=]' + t.TableName + '[ .,]%'
WHERE o.XTYPE = 'P'
AND o.NAME <> 'usp_FindTableUsage'
ORDER BY t.TableName, [Procedure Name]
PRINT CAST(@@ROWCOUNT as Varchar(5)) + ' dependent stored procedures'
PRINT''
PRINT''
PRINT 'VIEWS:'
PRINT''
SELECT DISTINCT t.TableName , SUBSTRING(o.NAME,1,60) AS [View Name]
FROM sysobjects o
INNER JOIN syscomments c
ON o.ID = c.ID
INNER JOIN @tblTableArray t
ON c.Text LIKE '%[ ,=]' + t.TableName + '[ .,]%'
WHERE o.XTYPE = 'V'
ORDER BY t.TableName, [View Name]
PRINT CAST(@@ROWCOUNT as Varchar(5)) + ' dependent views'
PRINT''
PRINT''
PRINT 'FUNCTIONS:'
PRINT''
SELECT DISTINCT t.TableName , SUBSTRING(o.NAME,1,60) AS [Function Name]
FROM sysobjects o
INNER JOIN syscomments c
ON o.ID = c.ID
INNER JOIN @tblTableArray t
ON c.Text LIKE '%[ ,=]' + t.TableName + '[ .,]%'
WHERE o.XTYPE IN ('FN','IF','TF')
ORDER BY t.TableName, [Function Name]
PRINT CAST(@@ROWCOUNT as Varchar(5)) + ' dependent functions'
PRINT''
PRINT''
PRINT 'TRIGGERS:'
PRINT''
SELECT DISTINCT t.TableName , SUBSTRING(o.NAME,1,60) AS [Trigger Name]
FROM sysobjects o
INNER JOIN syscomments c
ON o.ID = c.ID
INNER JOIN @tblTableArray t
ON c.Text LIKE '%[ ,=]' + t.TableName + '[ .,]%'
WHERE o.XTYPE = 'TR'
ORDER BY t.TableName, [Trigger Name]
PRINT CAST(@@ROWCOUNT as Varchar(5)) + ' dependent triggers'
PRINT''
PRINT''
PRINT 'JOBS:'
PRINT''
DECLARE @table_name SYSNAME;
SELECT @table_name=Element FROM
dbo.split(@vcTableList, ',');
SELECT
j.name,
s.step_name,
s.command
FROM
msdb.dbo.sysjobs j
INNER JOIN
msdb.dbo.sysjobsteps s
ON
j.job_id = s.job_id
WHERE
s.command LIKE '%' + @table_name + '%';
PRINT CAST(@@ROWCOUNT as Varchar(5)) + ' dependent jobs'
RETURN (0)
Error_Handler:
RETURN(-1)
-------------------------------------------------------------
USE [DBeheer]
GO
/****** Object: UserDefinedFunction [dbo].[Split] Script Date: 11/25/2008 14:03:17 ******/
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[Split] ( @vcDelimitedString varchar(8000),
@vcDelimiter varchar
(100) )
RETURNS @tblArray
TABLE
(
ElementID
smallint IDENTITY(1,1), --Array index
Element varchar
(1000) --Array element contents
)
AS
BEGIN
DECLARE
@siIndex smallint,
@siStart smallint,
@siDelSize smallint
SET @siDelSize = LEN(@vcDelimiter)
--loop through source string and add elements to destination table array
WHILE LEN(@vcDelimitedString) > 0
BEGIN
SET @siIndex = CHARINDEX(@vcDelimiter, @vcDelimitedString)
IF @siIndex = 0
BEGIN
INSERT INTO @tblArray VALUES(@vcDelimitedString)
BREAK
END
ELSE
BEGIN
INSERT INTO @tblArray VALUES(SUBSTRING(@vcDelimitedString, 1,@siIndex - 1))
SET @siStart = @siIndex + @siDelSize
SET @vcDelimitedString = SUBSTRING(@vcDelimitedString, @siStart , LEN(@vcDelimitedString) - @siStart + 1)
END
END
RETURN
END
-------------------------------------------------------------
exec usp_FindTableUsage
GO
/****** Object: StoredProcedure [dbo].[usp_FindTableUsage] Script Date: 11/25/2008 13:58:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_FindTableUsage]
AS
SET NOCOUNT ON
DECLARE @vcTableList VARCHAR(8000)
SET @vcTableList = ''
SELECT @vcTableList = COALESCE(@vcTableList+ ', ', '') + name
from sysobjects where type='U'
--Create table to hold table names
DECLARE @tblTableArray TABLE
(
TableName varchar(40)
)
-- load table names into array table
INSERT INTO @tblTableArray
SELECT Element FROM
dbo.split(@vcTableList, ',')
PRINT ''
PRINT 'REPORT FOR TABLE DEPENDENCIES for TABLES:'
PRINT '-----------------------------------------'
PRINT CHAR(9)+CHAR(9)+ REPLACE(@vcTableList,',',CHAR(13)+CHAR(10)+CHAR(9)+CHAR(9))
PRINT ''
PRINT ''
PRINT 'STORED PROCEDURES:'
PRINT ''
SELECT DISTINCT t.TableName , SUBSTRING(o.NAME,1,60) AS [Procedure Name]
FROM sysobjects o
INNER JOIN syscomments c
ON o.ID = c.ID
INNER JOIN @tblTableArray t
ON c.Text LIKE '%[ ,=]' + t.TableName + '[ .,]%'
WHERE o.XTYPE = 'P'
AND o.NAME <> 'usp_FindTableUsage'
ORDER BY t.TableName, [Procedure Name]
PRINT CAST(@@ROWCOUNT as Varchar(5)) + ' dependent stored procedures'
PRINT''
PRINT''
PRINT 'VIEWS:'
PRINT''
SELECT DISTINCT t.TableName , SUBSTRING(o.NAME,1,60) AS [View Name]
FROM sysobjects o
INNER JOIN syscomments c
ON o.ID = c.ID
INNER JOIN @tblTableArray t
ON c.Text LIKE '%[ ,=]' + t.TableName + '[ .,]%'
WHERE o.XTYPE = 'V'
ORDER BY t.TableName, [View Name]
PRINT CAST(@@ROWCOUNT as Varchar(5)) + ' dependent views'
PRINT''
PRINT''
PRINT 'FUNCTIONS:'
PRINT''
SELECT DISTINCT t.TableName , SUBSTRING(o.NAME,1,60) AS [Function Name]
FROM sysobjects o
INNER JOIN syscomments c
ON o.ID = c.ID
INNER JOIN @tblTableArray t
ON c.Text LIKE '%[ ,=]' + t.TableName + '[ .,]%'
WHERE o.XTYPE IN ('FN','IF','TF')
ORDER BY t.TableName, [Function Name]
PRINT CAST(@@ROWCOUNT as Varchar(5)) + ' dependent functions'
PRINT''
PRINT''
PRINT 'TRIGGERS:'
PRINT''
SELECT DISTINCT t.TableName , SUBSTRING(o.NAME,1,60) AS [Trigger Name]
FROM sysobjects o
INNER JOIN syscomments c
ON o.ID = c.ID
INNER JOIN @tblTableArray t
ON c.Text LIKE '%[ ,=]' + t.TableName + '[ .,]%'
WHERE o.XTYPE = 'TR'
ORDER BY t.TableName, [Trigger Name]
PRINT CAST(@@ROWCOUNT as Varchar(5)) + ' dependent triggers'
PRINT''
PRINT''
PRINT 'JOBS:'
PRINT''
DECLARE @table_name SYSNAME;
SELECT @table_name=Element FROM
dbo.split(@vcTableList, ',');
SELECT
j.name,
s.step_name,
s.command
FROM
msdb.dbo.sysjobs j
INNER JOIN
msdb.dbo.sysjobsteps s
ON
j.job_id = s.job_id
WHERE
s.command LIKE '%' + @table_name + '%';
PRINT CAST(@@ROWCOUNT as Varchar(5)) + ' dependent jobs'
RETURN (0)
Error_Handler:
RETURN(-1)
-------------------------------------------------------------
USE [DBeheer]
GO
/****** Object: UserDefinedFunction [dbo].[Split] Script Date: 11/25/2008 14:03:17 ******/
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[Split] ( @vcDelimitedString varchar(8000),
@vcDelimiter varchar
(100) )
RETURNS @tblArray
TABLE
(
ElementID
smallint IDENTITY(1,1), --Array index
Element varchar
(1000) --Array element contents
)
AS
BEGIN
DECLARE
@siIndex smallint,
@siStart smallint,
@siDelSize smallint
SET @siDelSize = LEN(@vcDelimiter)
--loop through source string and add elements to destination table array
WHILE LEN(@vcDelimitedString) > 0
BEGIN
SET @siIndex = CHARINDEX(@vcDelimiter, @vcDelimitedString)
IF @siIndex = 0
BEGIN
INSERT INTO @tblArray VALUES(@vcDelimitedString)
BREAK
END
ELSE
BEGIN
INSERT INTO @tblArray VALUES(SUBSTRING(@vcDelimitedString, 1,@siIndex - 1))
SET @siStart = @siIndex + @siDelSize
SET @vcDelimitedString = SUBSTRING(@vcDelimitedString, @siStart , LEN(@vcDelimitedString) - @siStart + 1)
END
END
RETURN
END
-------------------------------------------------------------
exec usp_FindTableUsage
how to check if linked server exists
how to check if linked server exists in SQL Server 2000:
--------------------------------------------------------
Select 1 Where Exists (Select [SRVID] From master..sysservers Where [srvName]='PSQLWEB1')
how to check if linked server exists in SQL Server 2005:
--------------------------------------------------------
Select 1 Where Exists (Select [SERVER_ID] From sys.servers Where [Name]='PSQLWEB1')
--------------------------------------------------------
Select 1 Where Exists (Select [SRVID] From master..sysservers Where [srvName]='PSQLWEB1')
how to check if linked server exists in SQL Server 2005:
--------------------------------------------------------
Select 1 Where Exists (Select [SERVER_ID] From sys.servers Where [Name]='PSQLWEB1')
Shrink TempDB database
http://dimantdatabasesolutions.blogspot.com/search?updated-min=2007-01-01T00%3A00%3A00-08%3A00&updated-max=2008-01-01T00%3A00%3A00-08%3A00&max-results=44
------------------------------------------------------------------------
Shrink TempDB database
I have been recently worked for some client who has huge tempdb size (SQL Server 2000). As you probably know, you cannot reduce physical size of tempdb without restart MSSQLServices. Using the command DBCC SHRINKFILE to shrink the individual tempdb files you must run DBCC SHRINKFILE command while no other activity occurs in the tempdb database. To make sure that other processes cannot use tempdb while DBCC SHRINKFILE executes, you must restart SQL Server in the single user
mode.
So I would like to show some stepS we ended up. The client completely understands the problem cause tempdb database to be growing and I hope it is not short time solution.
1) Stop SQL Server. Open a command prompt, and then start SQL Server by
typing the following command:
"sqlservr -c -f" (remove the quotation)
The -c and -f parameters force SQL Server to start in a minimum
configuration mode with a tempdb size of 1 MB for the data file and 0.5 MB
for the log file.
Connect to SQL Server with Query Analyzer, and then issue the following
TSQL commands for the tempdb database files that you need to shrink
use tempdb
go
dbcc shrinkfile (tempdev, 'target size in MB') --for the primary data file
or
dbcc shrinkfile (templog, 'target size in MB')--for the log file
As MS says
"An advantage of DBCC SHRINKFILE is that it can reduce the size of a file to
a size smaller than its original size. A limitation of DBCC SHRINKFILE is that you cannot make the database smaller than the size of the model database."
------------------------------------------------------------------------
Shrink TempDB database
I have been recently worked for some client who has huge tempdb size (SQL Server 2000). As you probably know, you cannot reduce physical size of tempdb without restart MSSQLServices. Using the command DBCC SHRINKFILE to shrink the individual tempdb files you must run DBCC SHRINKFILE command while no other activity occurs in the tempdb database. To make sure that other processes cannot use tempdb while DBCC SHRINKFILE executes, you must restart SQL Server in the single user
mode.
So I would like to show some stepS we ended up. The client completely understands the problem cause tempdb database to be growing and I hope it is not short time solution.
1) Stop SQL Server. Open a command prompt, and then start SQL Server by
typing the following command:
"sqlservr -c -f" (remove the quotation)
The -c and -f parameters force SQL Server to start in a minimum
configuration mode with a tempdb size of 1 MB for the data file and 0.5 MB
for the log file.
Connect to SQL Server with Query Analyzer, and then issue the following
TSQL commands for the tempdb database files that you need to shrink
use tempdb
go
dbcc shrinkfile (tempdev, 'target size in MB') --for the primary data file
or
dbcc shrinkfile (templog, 'target size in MB')--for the log file
As MS says
"An advantage of DBCC SHRINKFILE is that it can reduce the size of a file to
a size smaller than its original size. A limitation of DBCC SHRINKFILE is that you cannot make the database smaller than the size of the model database."
A useful undocumented function: fn_dblog
A useful undocumented function: fn_dblog:
========================================
A useful undocumented function: fn_dblog
Posted by decipherinfosys on November 12, 2008
A friend of mine recently told me about this undocumented function: ::fn_dblog when I was talking to him about Lumigent’s Log Explorer product. He pointed me to this post by Andrew Novick which mentions about this function which has existed since quite some time. I have always been using DBCC SQLPERF(LogSpace) when I wanted to capture used and free space and at times DBCC LOGINFO with help from different sites out there to understand the output. I am a fan of Lumigent’s product and have used it quite a bit in our client engagements but the fact that something like this existed all along, was a welcome surprise to me so I thought that we would share this on the blog with our readers as well.
As the post from Andrew Novick points out, the syntax is :
::fn_dblog(@StartingLSN, @EndingLSN)And if you provide Null as the value for both the parameters, then you will get a running record of everything from the start to the end (not a good idea in a production environment but something to play around with in your development/test environment to get an understanding of the output). The post from Andrew goes into some of the details of what the different columns are for.
========================================
A useful undocumented function: fn_dblog
Posted by decipherinfosys on November 12, 2008
A friend of mine recently told me about this undocumented function: ::fn_dblog when I was talking to him about Lumigent’s Log Explorer product. He pointed me to this post by Andrew Novick which mentions about this function which has existed since quite some time. I have always been using DBCC SQLPERF(LogSpace) when I wanted to capture used and free space and at times DBCC LOGINFO with help from different sites out there to understand the output. I am a fan of Lumigent’s product and have used it quite a bit in our client engagements but the fact that something like this existed all along, was a welcome surprise to me so I thought that we would share this on the blog with our readers as well.
As the post from Andrew Novick points out, the syntax is :
::fn_dblog(@StartingLSN, @EndingLSN)And if you provide Null as the value for both the parameters, then you will get a running record of everything from the start to the end (not a good idea in a production environment but something to play around with in your development/test environment to get an understanding of the output). The post from Andrew goes into some of the details of what the different columns are for.
SQL server services didn't started after min & max server memory configuration
http://www.sqlservercentral.com/articles/Administering/2963/
The SQL Server tools are top notch & one of the tools is "sqlservr.exe" which starts, stops, pauses, and continues Database Engine from the command prompt. This article describes how to start an instance of the Database Engine.
Over-Committing Memory
I had an issue recently where SQL server services didn't started after min & max server memory configuration of SQL server was changed. The Server got configured for AWE along with min & max server memory configuration. The SQL Server didn't start after stopping the services. What I intend to show you here is how problem came to happen & how it got resolved. In this article I'd like to talk about how this over committed memory issue is been addressed when value defined for "max server memory" (RAM) is not physically present on the server. Below topic will take you from Server configuration to Boot.INI file & to enable AWE & configuring server memory using system store procedure sp_configure & finally sqlservr.exe coming in rescue to start SQL server.
My Server Configuration:
1> Windows 2000 Datacenter
2> Physical memory 36GB
3> SQL Server 2000 Enterprise Edition
Configuring AWE for SQL Server 2000
Use of the /PAE switch in the Windows Boot.INI configuration file provides access to physical memory above the 4 GB limit. This is a requirement for AWE memory access above 4 GB.
Any change to Boot.INI file requires server restart\reboot. Now is time to enable AWE on SQL Server 2000 by running "sp_configure" from query analyzer.
The LOCK PAGE IN MEMORY permission must be granted to the SQL Server account before enabling AWE (SQL SERVER 2005); this may be enabled if Windows 2003 is on SP1
(USE MASTER)
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
-- Note: - max server memory is in MB
sp_configure 'min server memory', 1024
RECONFIGURE
GO
-- 30GB is 30720MB but accidentally I typed 307200, which is 300GB
sp_configure 'max server memory', 307200
RECONFIGURE
GO
I stopped SQL Server & when I started SQL Server 2000, it didn't start, I tried other possibilities but that didn't help.
Solution
To work around the problem, start SQL Server 2000 in minimal configuration mode by using Sqlservr.exe with the -c -f option and reconfigure "max server memory"
For a SQL Server 2000 Default Instance: Navigate to the Binn folder where the SQL Server 2000 default instance is installed and run the following command:
sqlservr.exe -c -f
For a SQL Server 2000 Named Instance: Navigate to the Binn folder where the SQL Server named instance is installed and run the following command:
sqlservr.exe -c -f -s Instance_Name
Connect to SQL Server through Query Analyzer, and then run this code:
(USE MASTER)
sp_configure 'max server memory', 30720 --- (Which is now 30GB)
RECONFIGURE
GO
Navigate to the command prompt and then press CTRL+C. To shut down the SQL Server 2000 instance, type:
Y
After that when you start again SQL Server it will come up fine & your"max server memory" issue is been resolved.
The SQL Server tools are top notch & one of the tools is "sqlservr.exe" which starts, stops, pauses, and continues Database Engine from the command prompt. This article describes how to start an instance of the Database Engine.
Over-Committing Memory
I had an issue recently where SQL server services didn't started after min & max server memory configuration of SQL server was changed. The Server got configured for AWE along with min & max server memory configuration. The SQL Server didn't start after stopping the services. What I intend to show you here is how problem came to happen & how it got resolved. In this article I'd like to talk about how this over committed memory issue is been addressed when value defined for "max server memory" (RAM) is not physically present on the server. Below topic will take you from Server configuration to Boot.INI file & to enable AWE & configuring server memory using system store procedure sp_configure & finally sqlservr.exe coming in rescue to start SQL server.
My Server Configuration:
1> Windows 2000 Datacenter
2> Physical memory 36GB
3> SQL Server 2000 Enterprise Edition
Configuring AWE for SQL Server 2000
Use of the /PAE switch in the Windows Boot.INI configuration file provides access to physical memory above the 4 GB limit. This is a requirement for AWE memory access above 4 GB.
Any change to Boot.INI file requires server restart\reboot. Now is time to enable AWE on SQL Server 2000 by running "sp_configure" from query analyzer.
The LOCK PAGE IN MEMORY permission must be granted to the SQL Server account before enabling AWE (SQL SERVER 2005); this may be enabled if Windows 2003 is on SP1
(USE MASTER)
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
-- Note: - max server memory is in MB
sp_configure 'min server memory', 1024
RECONFIGURE
GO
-- 30GB is 30720MB but accidentally I typed 307200, which is 300GB
sp_configure 'max server memory', 307200
RECONFIGURE
GO
I stopped SQL Server & when I started SQL Server 2000, it didn't start, I tried other possibilities but that didn't help.
Solution
To work around the problem, start SQL Server 2000 in minimal configuration mode by using Sqlservr.exe with the -c -f option and reconfigure "max server memory"
For a SQL Server 2000 Default Instance: Navigate to the Binn folder where the SQL Server 2000 default instance is installed and run the following command:
sqlservr.exe -c -f
For a SQL Server 2000 Named Instance: Navigate to the Binn folder where the SQL Server named instance is installed and run the following command:
sqlservr.exe -c -f -s Instance_Name
Connect to SQL Server through Query Analyzer, and then run this code:
(USE MASTER)
sp_configure 'max server memory', 30720 --- (Which is now 30GB)
RECONFIGURE
GO
Navigate to the command prompt and then press CTRL+C. To shut down the SQL Server 2000 instance, type:
Y
After that when you start again SQL Server it will come up fine & your"max server memory" issue is been resolved.
maandag 24 november 2008
SQL Server 2005: Get full information about transaction locks
SQL Server 2005: Get full information about transaction locks :
================================================================
http://weblogs.sqlteam.com/mladenp/archive/2008/04/29/SQL-Server-2005-Get-full-information-about-transaction-locks.aspx
================================================================
Sometimes we wish to know what locks are being held by the transaction. Also it would be great to know what SPID owns the transaction, on which objects the locks are being held, what SQL statement caused the locks, etc...
With the introduction of DMV's in SQL Server 2005 getting this information is quite easy with this query:
SELECT L.request_session_id AS SPID,
DB_NAME(L.resource_database_id) AS DatabaseName,
O.Name AS LockedObjectName,
P.object_id AS LockedObjectId,
L.resource_type AS LockedResource,
L.request_mode AS LockType,
ST.text AS SqlStatementText,
ES.login_name AS LoginName,
ES.host_name AS HostName,
TST.is_user_transaction as IsUserTransaction,
AT.name as TransactionName,
CN.auth_scheme as AuthenticationMethod
FROM sys.dm_tran_locks L
JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
JOIN sys.objects O ON O.object_id = P.object_id
JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
WHERE resource_database_id = db_id()
ORDER BY L.request_session_id
Let's look at it one DMV at a time from top to bottom:
sys.dm_tran_locks:
Shows us all locks held on all resources for all transaction. We start from this view since we want to get information about locked resources.
sys.partitions:
Contains information about partitions in the database. We can join it to the sys.dm_tran_locks on the hobt_id only for resource_type values 'HOBT', 'Page', 'RID' and 'Key'. With this join we get the object_id of our locked table.
sys.objects:
Contains data for all schema scoped database objects. We join it to sys.partitions to get the Name of the locked object which is usually a table name.
sys.dm_exec_sessions:
Shows data about all active sessions on SQL server. We join it to sys.dm_tran_locks to get user login information for each held lock.
sys.dm_tran_session_transactions:
Shows data for all transactions for a session. By joining in to sys.dm_exec_sessions we get a link to all active transactions in the session.
sys.dm_tran_active_transactions:
Shows data for all active transactions in SQL Server. By joining it to sys.dm_tran_session_transactions we only get information for the session we want.
sys.dm_exec_connections:
Shows data for all connections in SQL Server. By joining it to sys.dm_exec_sessions we get connection info for our session.
sys.dm_exec_sql_text:
Returns SQL statement that is associated with the SQL handle input parameter. By cross applying it to the sys.dm_exec_connections we get the last executed statement for the connection, which in our case is the statement that is holding locks.
By applying the filter in the where clause you get the answers to questions like:
- What SQL Statement is causing the lock?
- Which user has executed the SQL statement that's holding the locks?
- What objects/tables are being locked?
- What kinds of locks are being held and on which pages, keys, RID's?
- etc...
================================================================
http://weblogs.sqlteam.com/mladenp/archive/2008/04/29/SQL-Server-2005-Get-full-information-about-transaction-locks.aspx
================================================================
Sometimes we wish to know what locks are being held by the transaction. Also it would be great to know what SPID owns the transaction, on which objects the locks are being held, what SQL statement caused the locks, etc...
With the introduction of DMV's in SQL Server 2005 getting this information is quite easy with this query:
SELECT L.request_session_id AS SPID,
DB_NAME(L.resource_database_id) AS DatabaseName,
O.Name AS LockedObjectName,
P.object_id AS LockedObjectId,
L.resource_type AS LockedResource,
L.request_mode AS LockType,
ST.text AS SqlStatementText,
ES.login_name AS LoginName,
ES.host_name AS HostName,
TST.is_user_transaction as IsUserTransaction,
AT.name as TransactionName,
CN.auth_scheme as AuthenticationMethod
FROM sys.dm_tran_locks L
JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
JOIN sys.objects O ON O.object_id = P.object_id
JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
WHERE resource_database_id = db_id()
ORDER BY L.request_session_id
Let's look at it one DMV at a time from top to bottom:
sys.dm_tran_locks:
Shows us all locks held on all resources for all transaction. We start from this view since we want to get information about locked resources.
sys.partitions:
Contains information about partitions in the database. We can join it to the sys.dm_tran_locks on the hobt_id only for resource_type values 'HOBT', 'Page', 'RID' and 'Key'. With this join we get the object_id of our locked table.
sys.objects:
Contains data for all schema scoped database objects. We join it to sys.partitions to get the Name of the locked object which is usually a table name.
sys.dm_exec_sessions:
Shows data about all active sessions on SQL server. We join it to sys.dm_tran_locks to get user login information for each held lock.
sys.dm_tran_session_transactions:
Shows data for all transactions for a session. By joining in to sys.dm_exec_sessions we get a link to all active transactions in the session.
sys.dm_tran_active_transactions:
Shows data for all active transactions in SQL Server. By joining it to sys.dm_tran_session_transactions we only get information for the session we want.
sys.dm_exec_connections:
Shows data for all connections in SQL Server. By joining it to sys.dm_exec_sessions we get connection info for our session.
sys.dm_exec_sql_text:
Returns SQL statement that is associated with the SQL handle input parameter. By cross applying it to the sys.dm_exec_connections we get the last executed statement for the connection, which in our case is the statement that is holding locks.
By applying the filter in the where clause you get the answers to questions like:
- What SQL Statement is causing the lock?
- Which user has executed the SQL statement that's holding the locks?
- What objects/tables are being locked?
- What kinds of locks are being held and on which pages, keys, RID's?
- etc...
usp_FindObject
Create PROC usp_FindObject (
@objname varchar(200) = Null
, @objtype varchar(20) = Null
)
As
Declare @sqlstr nvarchar(200)
-- Insert wildcard, if exact search is not required.
-- Set @objname = '%' + @objname + '%'
-- Its better to supply custom wild card in the input parameter @objname
/* drop the temporary table if already exists */
If Object_Id('tempdb..#tblDBObjects') is Not Null
Drop table #tblDBObjects
/* create temporary table */
Create TABLE #tblDBObjects (
dbName sysname,
objName varchar(200),
objtype char(2)
)
Begin
If @objtype = 'CHECK'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''C'''''''
If @objtype = 'Default'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''D'''''''
If @objtype = 'FOREIGN KEY'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''F'''''''
If @objtype = 'Log'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''L'''''''
If @objtype = 'Scalar function'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''FN'''''''
If @objtype = 'Inlined table-function'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''IF'''''''
If @objtype = 'Stored procedure'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''P'''''''
If @objtype = 'PRIMARY KEY'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''PK'''''''
If @objtype = 'Replication filter stored procedure'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''RF'''''''
If @objtype = 'System table'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''S'''''''
If @objtype = 'Table function'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''TF'''''''
If @objtype = 'Trigger'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''TR'''''''
If @objtype = 'User table'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''U'''''''
If @objtype = 'UNIQUE constraint'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''UQ'''''''
If @objtype = 'View'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''V'''''''
If @objtype = 'Extended stored procedure'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''X'''''''
If (@objtype = '') Or (@objtype is Null)
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects'''
End
/* execute SQL string */
If (@sqlstr <> '') Or (@sqlstr is Not Null)
Exec sp_executesql @sqlstr
/* If @objname is not supplied it should still return result */
If (@objname = '') Or (@objname is Null)
Select * From #tblDBObjects
Else
Select * From #tblDBObjects Where objName like @objname
RETURN
@objname varchar(200) = Null
, @objtype varchar(20) = Null
)
As
Declare @sqlstr nvarchar(200)
-- Insert wildcard, if exact search is not required.
-- Set @objname = '%' + @objname + '%'
-- Its better to supply custom wild card in the input parameter @objname
/* drop the temporary table if already exists */
If Object_Id('tempdb..#tblDBObjects') is Not Null
Drop table #tblDBObjects
/* create temporary table */
Create TABLE #tblDBObjects (
dbName sysname,
objName varchar(200),
objtype char(2)
)
Begin
If @objtype = 'CHECK'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''C'''''''
If @objtype = 'Default'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''D'''''''
If @objtype = 'FOREIGN KEY'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''F'''''''
If @objtype = 'Log'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''L'''''''
If @objtype = 'Scalar function'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''FN'''''''
If @objtype = 'Inlined table-function'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''IF'''''''
If @objtype = 'Stored procedure'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''P'''''''
If @objtype = 'PRIMARY KEY'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''PK'''''''
If @objtype = 'Replication filter stored procedure'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''RF'''''''
If @objtype = 'System table'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''S'''''''
If @objtype = 'Table function'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''TF'''''''
If @objtype = 'Trigger'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''TR'''''''
If @objtype = 'User table'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''U'''''''
If @objtype = 'UNIQUE constraint'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''UQ'''''''
If @objtype = 'View'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''V'''''''
If @objtype = 'Extended stored procedure'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''X'''''''
If (@objtype = '') Or (@objtype is Null)
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects'''
End
/* execute SQL string */
If (@sqlstr <> '') Or (@sqlstr is Not Null)
Exec sp_executesql @sqlstr
/* If @objname is not supplied it should still return result */
If (@objname = '') Or (@objname is Null)
Select * From #tblDBObjects
Else
Select * From #tblDBObjects Where objName like @objname
RETURN
vrijdag 21 november 2008
Get sqlcmd output into a table?
CREATE TABLE ServerList_SSIS([Server] NVARCHAR(512),
[Connect] bit,
[DMZ] bit);
INSERT INTO ServerList_SSIS([Server])
EXEC master..xp_cmdshell 'sqlcmd /Lc';
Delete from ServerList_SSIS where [Server] IS NULL;
Select * from ServerList_SSIS
[Connect] bit,
[DMZ] bit);
INSERT INTO ServerList_SSIS([Server])
EXEC master..xp_cmdshell 'sqlcmd /Lc';
Delete from ServerList_SSIS where [Server] IS NULL;
Select * from ServerList_SSIS
donderdag 20 november 2008
spProcessTrace-fnExtractSPNameFromTextData-SpActivityGraph-spShowActivityGraphByChunks
--Listing 4. spProcessTrace
USE Traces
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'spProcessTraceFile' and type = 'P')
DROP PROC spProcessTraceFile
GO
-------------------------------------------------------------------------------------------------------
-- Name spProcessTraceFile
-- Description Aggregates data from a SQL Server trace file and sends reports with top consumers and
-- long-runners by email
-- Input @ServerName - server name a trace file was created for
-- @ReportDate (optional) - date of the trace file (default - current)
-- @TraceFilePath - path to the trace file
-- @recipients - list of email recipient divided by [;]
-- Created By Viktor Gorodnichenko
-------------------------------------------------------------------------------------------------------
CREATE PROC spProcessTraceFile
@ServerName sysname,
@ReportDate varchar(20) = null,
@TraceFilePath varchar(1000) = '\\Process02\D$\Program Files\Microsoft SQL Server\MSSQL\LOG\',
@recipients varchar(4000) = 'Manager1@company.com;Manager2@company.com'
AS
SET NOCOUNT ON
DECLARE @TabName sysname
DECLARE @Sqlstr varchar(8000)
declare @m varchar(8000)
declare @s varchar(8000)
declare @q varchar(8000)
declare @ReportDateShort varchar(20)
SET @ReportDate = CASE WHEN @ReportDate is null
THEN CONVERT(varchar(20),getdate(),107)
ELSE CONVERT(varchar(20),CONVERT(datetime,@ReportDate),107)
END
SET @ReportDateShort = REPLACE(LEFT(@ReportDate,6),' ','')
SET @TabName = 'Trace_' + @ServerName + @ReportDateShort
SET @Sqlstr = '
SELECT EventClass, TextData, DatabaseId, SPID, Duration, StartTime, Reads, Writes, CPU
INTO '+@TabName+' FROM ::fn_trace_gettable('''+@TraceFilePath+@TabName+'.trc'', default)'
EXEC (@Sqlstr)
IF @@ERROR = 0
PRINT 'Loading the trace file into a table succeeded'
-- CPU consumers
SET @Sqlstr = '
SELECT *
into '+@TabName+'_CPU
FROM (
select dbo.fnExtractSPNameFromTextData(TextData) ''SP'', COUNT(*) ''TimesExecuted'',
SUM(CPU) ''TotalCPU'', MIN(CPU) ''MinCPU'', MAX(CPU) ''MaxCPU''
from '+@TabName+'
WHERE DATALENGTH(TextData) > 0
AND EventClass in (10,12)
GROUP BY dbo.fnExtractSPNameFromTextData(TextData)) t
WHERE SP not in (''--'',''COMMIT'',''select'',''insert'',''delete'',''update'',''trace'',''set'',
''use'',''if'',''@retcode'')
ORDER BY 3 DESC'
EXEC (@Sqlstr)
IF @@ERROR = 0
PRINT 'Aggregation for CPU has been succeeded'
-- Sending top CPU consumers by e-mail
set @m = 'Top Overlord.dev CPU consumers for '+@ReportDate+':'+CHAR(13)+CHAR(10)
set @s = 'Top Overlord.dev CPU consumers for '+@ReportDate
set @q = 'select LEFT(SP,40) ''SP'',
TimesExecuted,
TotalCPU,
TotalCPU/TimesExecuted ''AverageExecTime'',
MinCPU,
MaxCPU
from dbo.'+@TabName+'_CPU
WHERE LEFT(SP,1) like ''[a-z]''
AND TimesExecuted > 1
ORDER BY TotalCPU DESC'
exec master.dbo.xp_sendmail @recipients =@recipients,
@message=@m,
@query =@q,
@subject = @s,
@width = 200,
@dbuse='Traces'
-- Long-runners
SET @Sqlstr = '
SELECT *
into '+@TabName+'_Duration
FROM (
select dbo.fnExtractSPNameFromTextData(TextData) ''SP'', COUNT(*) ''TimesExecuted'',
SUM(Duration) ''TotalDuration'', MIN(Duration) ''MinDuration'', MAX(Duration) ''MaxDuration''
from '+@TabName+'
WHERE DATALENGTH(TextData) > 0
AND EventClass in (10,12)
GROUP BY dbo.fnExtractSPNameFromTextData(TextData)) t
WHERE SP not in (''--'',''COMMIT'',''select'',''insert'',''delete'',''update'',''trace'',''set'',
''use'',''if'',''@retcode'')
ORDER BY 3 DESC'
EXEC (@Sqlstr)
-- Sending top Long-Runners result by e-mail
set @m = 'Top Overlord.dev Long-Runners for '+@ReportDate+':'+CHAR(13)+CHAR(10)
set @s = 'Top Overlord.dev Long-Runners for '+@ReportDate
set @q = 'select LEFT(SP,40) ''SP'',
TimesExecuted,
TotalDuration,
TotalDuration/TimesExecuted ''AverageExecTime'',
MinDuration,
MaxDuration
from dbo.'+@TabName+'_Duration
WHERE LEFT(SP,1) like ''[a-z]''
AND TimesExecuted > 1
ORDER BY TotalDuration DESC'
exec master.dbo.xp_sendmail @recipients =@recipients,
@message=@m,
@query =@q,
@subject = @s,
@width = 200,
@dbuse='Traces'
GO
--Listing 5. fnExtractSPNameFromTextData
USE Traces
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'fnExtractSPNameFromTextData' and type = 'FN')
DROP FUNCTION fnExtractSPNameFromTextData
GO
---------------------------------------------------------------------------------
-- Name fnExtractSPNameFromTextData
-- Description Extracts SP name from profiler's textdata
-- Input @TextData
-- Output SP name
-- Created By Viktor Gorodnichenko
---------------------------------------------------------------------------------
CREATE FUNCTION fnExtractSPNameFromTextData
(@TextData nvarchar(4000))
RETURNS varchar(128)
AS
BEGIN
DECLARE @Name varchar(128)
IF CHARINDEX('sp_execute',@TextData) > 0
BEGIN
SET @Name = 'sp_execute'
GOTO exit_fn
END
IF CHARINDEX('exec',@TextData) > 0
SET @TextData = RIGHT(@TextData,LEN(@TextData)-CHARINDEX('exec',@TextData)+1)
SET @TextData = LTRIM(REPLACE(@TextData,'execute ',''))
SET @TextData = LTRIM(REPLACE(@TextData,'exec ',''))
IF CHARINDEX(' ',@TextData) > 0
BEGIN
IF LEFT(@TextData,1) = '@'
BEGIN
SET @TextData = RIGHT(@TextData,LEN(@TextData)-CHARINDEX('=',@TextData)-1)
END
SET @Name = CASE WHEN CHARINDEX(' ',@TextData) > 0
THEN LEFT(@TextData,CHARINDEX(' ',@TextData)-1)
ELSE @TextData
END
END
ELSE
SET @Name = @TextData
SET @Name = CASE WHEN CHARINDEX(CHAR(9),@Name) > 0
THEN LEFT(@Name,CHARINDEX(CHAR(9),@Name)-1)
ELSE @Name
END
IF CHARINDEX('.',@Name)>0
SET @Name = REVERSE(LEFT(REVERSE(@Name),CHARINDEX('.',REVERSE(@Name))-1))
exit_fn:
RETURN @Name
END
--Listing 6. SpActivityGraph
USE Traces
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'spActivityGraph' and type = 'P')
DROP PROC spActivityGraph
GO
-------------------------------------------------------------------------------------------------------
-- Name spActivityGraph
-- Description Builds a graph of code activities. Uses data from a trace table
-- Input @TraceTable - name of the trace table
-- @TraceStart/@TraceEnd - trace period to cover in the graph
-- @SPNameLayoutLen = 20 - size of the column "name" in the report
-- @DurationLayoutLen = 6 - size of the column "duration" in the report
-- @LayoutWidth int = 115 - width of the report
-- Created By Viktor Gorodnichenko
-------------------------------------------------------------------------------------------------------
CREATE PROC spActivityGraph
@TraceTable varchar(128),
@TraceStart varchar(40),
@TraceEnd varchar(40),
@SPNameLayoutLen int = 20,
@DurationLayoutLen int = 6,
@LayoutWidth int = 115
AS
DECLARE @GraphStart datetime
DECLARE @GraphEnd datetime
DECLARE @TimeToMark datetime
DECLARE @Coeff numeric(7,3)
DECLARE @strCoeff varchar(20)
DECLARE @StartTime datetime
DECLARE @Duration int
DECLARE @TextData varchar(8000)
DECLARE @sqlstr nvarchar(4000)
SET @sqlstr = N'SELECT @GraphStart = MIN(StartTime), @GraphEnd = MAX(StartTime) from '+@TraceTable+'
WHERE StartTime > '''+@TraceStart+''' and StartTime < '''+@TraceEnd+'''
and TextData not like ''%sp_getactiveprocesses%''
and EventClass in (10,12)
and Duration > 5000'
EXEC sp_executesql @sqlstr, N'@GraphStart datetime OUTPUT, @GraphEnd datetime OUTPUT',
@GraphStart = @GraphStart OUTPUT, @GraphEnd = @GraphEnd OUTPUT
IF @GraphStart is null
BEGIN
PRINT 'No data for the period'
RETURN
END
SET @Coeff = CONVERT(numeric(10,2),@LayoutWidth) / CASE WHEN DATEDIFF(ss, @GraphStart, @GraphEnd)>0
THEN CONVERT(numeric(10,2),DATEDIFF(ss, @GraphStart, @GraphEnd)) ELSE 1800 END -- 1800 sec in 30 min
-- The smaller @Coeff the less number of dashes will be used to represent a long-runner.
Fo ex, 0.2 means that a 5 sec process will get 1 dash.
SET @strCoeff = STR(@Coeff,7,3)
EXEC ('DECLARE SPIDs CURSOR FOR
select StartTime, Duration, CONVERT(varchar(8000),TextData) from '+@TraceTable+'
WHERE StartTime > '''+@TraceStart+''' and StartTime < '''+@TraceEnd+'''
and TextData not like ''%sp_getactiveprocesses%''
and EventClass in (10,12)
and (Duration/1000)*'+@strCoeff+' > 1
order by StartTime')
-- Printing time scale
PRINT 'StartTime Duration Text'+
SPACE(12)+LEFT(CONVERT(varchar(10),@GraphStart,108),5)+
SPACE(31)+LEFT(CONVERT(varchar(10),DATEADD(ss,DATEDIFF(ss,@GraphStart,@GraphEnd)/3,@GraphStart),108),5)+
SPACE(31)+LEFT(CONVERT(varchar(10),DATEADD(ss,DATEDIFF(ss,@GraphStart,@GraphEnd)*2/3,@GraphStart),108),5)+
SPACE(31)+LEFT(CONVERT(varchar(10),@GraphEnd,108),5)
IF @TimeToMark is not null
PRINT REPLICATE(' ',ROUND(DATEDIFF(ss,@GraphStart,@TimeToMark)*@Coeff,0))+'*'
OPEN SPIDs
FETCH NEXT FROM SPIDs INTO @StartTime, @Duration, @TextData
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT RIGHT(CONVERT(varchar(10),@StartTime,108),8) + ' ' + STR(@Duration,@DurationLayoutLen) + ' ' +
LEFT(dbo.fnExtractSPNameFromTextData(@TextData),@SPNameLayoutLen)+
REPLICATE(' ',@SPNameLayoutLen-LEN(LEFT(dbo.fnExtractSPNameFromTextData(@TextData),@SPNameLayoutLen)))+
REPLICATE(' ',ROUND(DATEDIFF(ss,@GraphStart,@StartTime)*@Coeff,0))+
REPLICATE('-',ROUND((@Duration/1000)*@Coeff,0))
FETCH NEXT FROM SPIDs INTO @StartTime, @Duration, @TextData
END
DEALLOCATE SPIDs
GO
--Listing 7. spShowActivityGraphByChunks
USE Traces
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'spShowActivityGraphByChunks' and type = 'P')
DROP PROC spShowActivityGraphByChunks
GO
-------------------------------------------------------------------------------------------------------
-- Name spShowActivityGraphByChunks
-- Description Shows activity graphs by small portions
-- Input @ServerName - name of the production server the trace was collected for
-- @ReportDate - date of the trace
-- @StartTime/@EndTime - trace period to cover
-- @ChunkSize - size in minutes of a single graph
-- Created By Viktor Gorodnichenko
-------------------------------------------------------------------------------------------------------
CREATE PROC spShowActivityGraphByChunks
@ServerName sysname,
@ReportDate datetime,
@StartTime varchar(7) = '8:30AM',
@EndTime varchar(7) = '5:30PM',
@ChunkSize int = 30
AS
DECLARE @TraceStart datetime
DECLARE @TraceEnd datetime
DECLARE @ReportDateShort varchar(20)
DECLARE @SqlStr varchar(2000)
SET @ReportDateShort = REPLACE(LEFT(CONVERT(varchar(20),@ReportDate,107),6),' ','')
SET @TraceStart = CONVERT(varchar(20),@ReportDate,107)+' '+@StartTime
WHILE @TraceStart <= CONVERT(varchar(20),@ReportDate,107)+' '+@EndTime
BEGIN
SET @TraceEnd = DATEADD(mi,@ChunkSize,@TraceStart)
SET @SqlStr = 'spActivityGraph Trace_'+@ServerName+@ReportDateShort+ ', '''+
CONVERT(varchar(40),@TraceStart)+''', '''+CONVERT(varchar(30),@TraceEnd)+''''
EXEC (@SqlStr)
SET @TraceStart = DATEADD(mi,60,@TraceStart)
END
--------------------
http://www.sqlservercentral.com/columnists/vgorod/monitoringperformance_scripts.txt
USE Traces
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'spProcessTraceFile' and type = 'P')
DROP PROC spProcessTraceFile
GO
-------------------------------------------------------------------------------------------------------
-- Name spProcessTraceFile
-- Description Aggregates data from a SQL Server trace file and sends reports with top consumers and
-- long-runners by email
-- Input @ServerName - server name a trace file was created for
-- @ReportDate (optional) - date of the trace file (default - current)
-- @TraceFilePath - path to the trace file
-- @recipients - list of email recipient divided by [;]
-- Created By Viktor Gorodnichenko
-------------------------------------------------------------------------------------------------------
CREATE PROC spProcessTraceFile
@ServerName sysname,
@ReportDate varchar(20) = null,
@TraceFilePath varchar(1000) = '\\Process02\D$\Program Files\Microsoft SQL Server\MSSQL\LOG\',
@recipients varchar(4000) = 'Manager1@company.com;Manager2@company.com'
AS
SET NOCOUNT ON
DECLARE @TabName sysname
DECLARE @Sqlstr varchar(8000)
declare @m varchar(8000)
declare @s varchar(8000)
declare @q varchar(8000)
declare @ReportDateShort varchar(20)
SET @ReportDate = CASE WHEN @ReportDate is null
THEN CONVERT(varchar(20),getdate(),107)
ELSE CONVERT(varchar(20),CONVERT(datetime,@ReportDate),107)
END
SET @ReportDateShort = REPLACE(LEFT(@ReportDate,6),' ','')
SET @TabName = 'Trace_' + @ServerName + @ReportDateShort
SET @Sqlstr = '
SELECT EventClass, TextData, DatabaseId, SPID, Duration, StartTime, Reads, Writes, CPU
INTO '+@TabName+' FROM ::fn_trace_gettable('''+@TraceFilePath+@TabName+'.trc'', default)'
EXEC (@Sqlstr)
IF @@ERROR = 0
PRINT 'Loading the trace file into a table succeeded'
-- CPU consumers
SET @Sqlstr = '
SELECT *
into '+@TabName+'_CPU
FROM (
select dbo.fnExtractSPNameFromTextData(TextData) ''SP'', COUNT(*) ''TimesExecuted'',
SUM(CPU) ''TotalCPU'', MIN(CPU) ''MinCPU'', MAX(CPU) ''MaxCPU''
from '+@TabName+'
WHERE DATALENGTH(TextData) > 0
AND EventClass in (10,12)
GROUP BY dbo.fnExtractSPNameFromTextData(TextData)) t
WHERE SP not in (''--'',''COMMIT'',''select'',''insert'',''delete'',''update'',''trace'',''set'',
''use'',''if'',''@retcode'')
ORDER BY 3 DESC'
EXEC (@Sqlstr)
IF @@ERROR = 0
PRINT 'Aggregation for CPU has been succeeded'
-- Sending top CPU consumers by e-mail
set @m = 'Top Overlord.dev CPU consumers for '+@ReportDate+':'+CHAR(13)+CHAR(10)
set @s = 'Top Overlord.dev CPU consumers for '+@ReportDate
set @q = 'select LEFT(SP,40) ''SP'',
TimesExecuted,
TotalCPU,
TotalCPU/TimesExecuted ''AverageExecTime'',
MinCPU,
MaxCPU
from dbo.'+@TabName+'_CPU
WHERE LEFT(SP,1) like ''[a-z]''
AND TimesExecuted > 1
ORDER BY TotalCPU DESC'
exec master.dbo.xp_sendmail @recipients =@recipients,
@message=@m,
@query =@q,
@subject = @s,
@width = 200,
@dbuse='Traces'
-- Long-runners
SET @Sqlstr = '
SELECT *
into '+@TabName+'_Duration
FROM (
select dbo.fnExtractSPNameFromTextData(TextData) ''SP'', COUNT(*) ''TimesExecuted'',
SUM(Duration) ''TotalDuration'', MIN(Duration) ''MinDuration'', MAX(Duration) ''MaxDuration''
from '+@TabName+'
WHERE DATALENGTH(TextData) > 0
AND EventClass in (10,12)
GROUP BY dbo.fnExtractSPNameFromTextData(TextData)) t
WHERE SP not in (''--'',''COMMIT'',''select'',''insert'',''delete'',''update'',''trace'',''set'',
''use'',''if'',''@retcode'')
ORDER BY 3 DESC'
EXEC (@Sqlstr)
-- Sending top Long-Runners result by e-mail
set @m = 'Top Overlord.dev Long-Runners for '+@ReportDate+':'+CHAR(13)+CHAR(10)
set @s = 'Top Overlord.dev Long-Runners for '+@ReportDate
set @q = 'select LEFT(SP,40) ''SP'',
TimesExecuted,
TotalDuration,
TotalDuration/TimesExecuted ''AverageExecTime'',
MinDuration,
MaxDuration
from dbo.'+@TabName+'_Duration
WHERE LEFT(SP,1) like ''[a-z]''
AND TimesExecuted > 1
ORDER BY TotalDuration DESC'
exec master.dbo.xp_sendmail @recipients =@recipients,
@message=@m,
@query =@q,
@subject = @s,
@width = 200,
@dbuse='Traces'
GO
--Listing 5. fnExtractSPNameFromTextData
USE Traces
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'fnExtractSPNameFromTextData' and type = 'FN')
DROP FUNCTION fnExtractSPNameFromTextData
GO
---------------------------------------------------------------------------------
-- Name fnExtractSPNameFromTextData
-- Description Extracts SP name from profiler's textdata
-- Input @TextData
-- Output SP name
-- Created By Viktor Gorodnichenko
---------------------------------------------------------------------------------
CREATE FUNCTION fnExtractSPNameFromTextData
(@TextData nvarchar(4000))
RETURNS varchar(128)
AS
BEGIN
DECLARE @Name varchar(128)
IF CHARINDEX('sp_execute',@TextData) > 0
BEGIN
SET @Name = 'sp_execute'
GOTO exit_fn
END
IF CHARINDEX('exec',@TextData) > 0
SET @TextData = RIGHT(@TextData,LEN(@TextData)-CHARINDEX('exec',@TextData)+1)
SET @TextData = LTRIM(REPLACE(@TextData,'execute ',''))
SET @TextData = LTRIM(REPLACE(@TextData,'exec ',''))
IF CHARINDEX(' ',@TextData) > 0
BEGIN
IF LEFT(@TextData,1) = '@'
BEGIN
SET @TextData = RIGHT(@TextData,LEN(@TextData)-CHARINDEX('=',@TextData)-1)
END
SET @Name = CASE WHEN CHARINDEX(' ',@TextData) > 0
THEN LEFT(@TextData,CHARINDEX(' ',@TextData)-1)
ELSE @TextData
END
END
ELSE
SET @Name = @TextData
SET @Name = CASE WHEN CHARINDEX(CHAR(9),@Name) > 0
THEN LEFT(@Name,CHARINDEX(CHAR(9),@Name)-1)
ELSE @Name
END
IF CHARINDEX('.',@Name)>0
SET @Name = REVERSE(LEFT(REVERSE(@Name),CHARINDEX('.',REVERSE(@Name))-1))
exit_fn:
RETURN @Name
END
--Listing 6. SpActivityGraph
USE Traces
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'spActivityGraph' and type = 'P')
DROP PROC spActivityGraph
GO
-------------------------------------------------------------------------------------------------------
-- Name spActivityGraph
-- Description Builds a graph of code activities. Uses data from a trace table
-- Input @TraceTable - name of the trace table
-- @TraceStart/@TraceEnd - trace period to cover in the graph
-- @SPNameLayoutLen = 20 - size of the column "name" in the report
-- @DurationLayoutLen = 6 - size of the column "duration" in the report
-- @LayoutWidth int = 115 - width of the report
-- Created By Viktor Gorodnichenko
-------------------------------------------------------------------------------------------------------
CREATE PROC spActivityGraph
@TraceTable varchar(128),
@TraceStart varchar(40),
@TraceEnd varchar(40),
@SPNameLayoutLen int = 20,
@DurationLayoutLen int = 6,
@LayoutWidth int = 115
AS
DECLARE @GraphStart datetime
DECLARE @GraphEnd datetime
DECLARE @TimeToMark datetime
DECLARE @Coeff numeric(7,3)
DECLARE @strCoeff varchar(20)
DECLARE @StartTime datetime
DECLARE @Duration int
DECLARE @TextData varchar(8000)
DECLARE @sqlstr nvarchar(4000)
SET @sqlstr = N'SELECT @GraphStart = MIN(StartTime), @GraphEnd = MAX(StartTime) from '+@TraceTable+'
WHERE StartTime > '''+@TraceStart+''' and StartTime < '''+@TraceEnd+'''
and TextData not like ''%sp_getactiveprocesses%''
and EventClass in (10,12)
and Duration > 5000'
EXEC sp_executesql @sqlstr, N'@GraphStart datetime OUTPUT, @GraphEnd datetime OUTPUT',
@GraphStart = @GraphStart OUTPUT, @GraphEnd = @GraphEnd OUTPUT
IF @GraphStart is null
BEGIN
PRINT 'No data for the period'
RETURN
END
SET @Coeff = CONVERT(numeric(10,2),@LayoutWidth) / CASE WHEN DATEDIFF(ss, @GraphStart, @GraphEnd)>0
THEN CONVERT(numeric(10,2),DATEDIFF(ss, @GraphStart, @GraphEnd)) ELSE 1800 END -- 1800 sec in 30 min
-- The smaller @Coeff the less number of dashes will be used to represent a long-runner.
Fo ex, 0.2 means that a 5 sec process will get 1 dash.
SET @strCoeff = STR(@Coeff,7,3)
EXEC ('DECLARE SPIDs CURSOR FOR
select StartTime, Duration, CONVERT(varchar(8000),TextData) from '+@TraceTable+'
WHERE StartTime > '''+@TraceStart+''' and StartTime < '''+@TraceEnd+'''
and TextData not like ''%sp_getactiveprocesses%''
and EventClass in (10,12)
and (Duration/1000)*'+@strCoeff+' > 1
order by StartTime')
-- Printing time scale
PRINT 'StartTime Duration Text'+
SPACE(12)+LEFT(CONVERT(varchar(10),@GraphStart,108),5)+
SPACE(31)+LEFT(CONVERT(varchar(10),DATEADD(ss,DATEDIFF(ss,@GraphStart,@GraphEnd)/3,@GraphStart),108),5)+
SPACE(31)+LEFT(CONVERT(varchar(10),DATEADD(ss,DATEDIFF(ss,@GraphStart,@GraphEnd)*2/3,@GraphStart),108),5)+
SPACE(31)+LEFT(CONVERT(varchar(10),@GraphEnd,108),5)
IF @TimeToMark is not null
PRINT REPLICATE(' ',ROUND(DATEDIFF(ss,@GraphStart,@TimeToMark)*@Coeff,0))+'*'
OPEN SPIDs
FETCH NEXT FROM SPIDs INTO @StartTime, @Duration, @TextData
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT RIGHT(CONVERT(varchar(10),@StartTime,108),8) + ' ' + STR(@Duration,@DurationLayoutLen) + ' ' +
LEFT(dbo.fnExtractSPNameFromTextData(@TextData),@SPNameLayoutLen)+
REPLICATE(' ',@SPNameLayoutLen-LEN(LEFT(dbo.fnExtractSPNameFromTextData(@TextData),@SPNameLayoutLen)))+
REPLICATE(' ',ROUND(DATEDIFF(ss,@GraphStart,@StartTime)*@Coeff,0))+
REPLICATE('-',ROUND((@Duration/1000)*@Coeff,0))
FETCH NEXT FROM SPIDs INTO @StartTime, @Duration, @TextData
END
DEALLOCATE SPIDs
GO
--Listing 7. spShowActivityGraphByChunks
USE Traces
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'spShowActivityGraphByChunks' and type = 'P')
DROP PROC spShowActivityGraphByChunks
GO
-------------------------------------------------------------------------------------------------------
-- Name spShowActivityGraphByChunks
-- Description Shows activity graphs by small portions
-- Input @ServerName - name of the production server the trace was collected for
-- @ReportDate - date of the trace
-- @StartTime/@EndTime - trace period to cover
-- @ChunkSize - size in minutes of a single graph
-- Created By Viktor Gorodnichenko
-------------------------------------------------------------------------------------------------------
CREATE PROC spShowActivityGraphByChunks
@ServerName sysname,
@ReportDate datetime,
@StartTime varchar(7) = '8:30AM',
@EndTime varchar(7) = '5:30PM',
@ChunkSize int = 30
AS
DECLARE @TraceStart datetime
DECLARE @TraceEnd datetime
DECLARE @ReportDateShort varchar(20)
DECLARE @SqlStr varchar(2000)
SET @ReportDateShort = REPLACE(LEFT(CONVERT(varchar(20),@ReportDate,107),6),' ','')
SET @TraceStart = CONVERT(varchar(20),@ReportDate,107)+' '+@StartTime
WHILE @TraceStart <= CONVERT(varchar(20),@ReportDate,107)+' '+@EndTime
BEGIN
SET @TraceEnd = DATEADD(mi,@ChunkSize,@TraceStart)
SET @SqlStr = 'spActivityGraph Trace_'+@ServerName+@ReportDateShort+ ', '''+
CONVERT(varchar(40),@TraceStart)+''', '''+CONVERT(varchar(30),@TraceEnd)+''''
EXEC (@SqlStr)
SET @TraceStart = DATEADD(mi,60,@TraceStart)
END
--------------------
http://www.sqlservercentral.com/columnists/vgorod/monitoringperformance_scripts.txt
spTraceBuild
--Listing 3. spTraceBuild
USE msdb
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'spTraceBuild' and type = 'P')
DROP PROC spTraceBuild
GO
-------------------------------------------------------------------------------------------------------
-- Name spTraceBuild
-- Description Starts a SQL Server trace. The code based on sp build_trace from the Microsoft
-- Knowledge Base Article Q283790.
-- Input From 'C:\Program Files\Microsoft SQL Server\MSSQL\Binn\ActivityTrace.ini'
-- Modified By Viktor Gorodnichenko
-------------------------------------------------------------------------------------------------------
CREATE PROC spTraceBuild
@traceini nvarchar (245) = N'C:\Program Files\Microsoft SQL Server\MSSQL\Binn\ActivityTrace.ini'
as
SET NOCOUNT ON
declare @traceid int, @options int, @tracefile nvarchar (245), @maxfilesize bigint
, @stoptime datetime, @minMBfree bigint, @rc int, @on bit, @cmd1 nvarchar(512)
, @events varchar(512), @columns varchar(512), @event int, @column int, @estart int, @enext int
, @cstart int, @cnext int, @le int, @lc int, @filter nvarchar(245), @filter_num int
create table #t1 ([c1] nvarchar(512))
set @cmd1 = 'bulk insert #t1 FROM '''
--select @cmd1 + @traceini
exec (@cmd1 + @traceini + '''')
select @tracefile = cast(rtrim(ltrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as nvarchar (245))
from #t1 where left(c1,3) = '@tr'
select @maxfilesize = cast(rtrim(ltrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as bigint)
from #t1 where left(c1,3) = '@ma'
select @stoptime = cast(rtrim(ltrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as datetime)
from #t1 where left(c1,3) = '@st'
SET @stoptime = CONVERT(datetime, CONVERT(varchar(20),getdate(),107) + ' ' + @stoptime)
select @options = cast(rtrim(ltrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as int)
from #t1 where left(c1,3) = '@op'
select @events=cast(ltrim(rtrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as nvarchar (512))
from #t1 where left(c1,3) = N'@ev'
select @columns=cast(ltrim(rtrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as nvarchar (512))
from #t1 where left(c1,3) = N'@co'
set @on = 1
set @traceid = 0
select @tracefile = @tracefile + '_' + @@SERVERNAME + REPLACE(LEFT(CONVERT(varchar(20),getdate(),107),6),' ','')
--select @tracefile
SELECT @traceid = traceid FROM :: fn_trace_getinfo(0) where property = 2 and value = @tracefile
if @traceid != 0 goto finish
set @cmd1 = 'if exist "' + @tracefile + '.trc" ' + 'del "' + @tracefile + '*.trc"'
exec @rc = master.dbo.xp_cmdshell @cmd1, no_output
exec @rc = sp_trace_create @traceid output, @options, @tracefile, @maxfilesize, @stoptime
IF @rc <> 0
BEGIN
PRINT
CASE
WHEN @rc = 1 THEN 'sp_trace_create failed. Unknown error.'
WHEN @rc = 10 THEN 'sp_trace_create failed. Invalid options. Options specified are incompatible.'
WHEN @rc = 12 THEN 'sp_trace_create failed. File not created.'
WHEN @rc = 13 THEN 'sp_trace_create failed. Out of memory. There is not enough memory to perform the specified action.'
WHEN @rc = 14 THEN 'sp_trace_create failed. Invalid stop time. The stop time specified has already happened.'
WHEN @rc = 15 THEN 'sp_trace_create failed. Invalid parameters. Supplied incompatible parameters.'
END
RETURN
END
select @estart = 1
select @enext = charindex(',',@events,@estart)
select @cstart = 1
select @cnext = charindex(',',@columns,@cstart)
set @le = len(@events)
set @lc = len(@columns)
while @enext > 0
begin
select @event = cast(substring(@events,@estart,@enext-@estart) as int)
while @cnext > 0
begin
select @column = cast(substring(@columns,@cstart,@cnext-@cstart) as int)
exec @rc = sp_trace_setevent @traceid, @event, @column, @on
IF @rc <> 0
BEGIN
PRINT
CASE
WHEN @rc = 1 THEN 'sp_trace_setevent failed. Unknown error.'
WHEN @rc = 2 THEN 'sp_trace_setevent failed. The trace is currently running.'
WHEN @rc = 3 THEN 'sp_trace_setevent failed. The specified Event is not valid. The Event may not exist or it
is not an appropriate one for the store procedure.'
WHEN @rc = 4 THEN 'sp_trace_setevent failed. The specified Column is not valid.'
WHEN @rc = 9 THEN 'sp_trace_setevent failed. The specified Trace Handle is not valid.'
WHEN @rc = 11 THEN 'sp_trace_setevent failed. The specified Column is used internally and cannot be removed. '
WHEN @rc = 13 THEN 'sp_trace_setevent failed. Out of memory. There is not enough memory to perform the specified action.'
WHEN @rc = 14 THEN 'sp_trace_setevent failed. The function is not valid for this trace.'
END
RETURN
END
select @cstart = @cnext + 1
select @cnext = charindex(',',@columns,@cstart)
if @cnext = 0 set @cnext = @lc + 1
if @cstart >@lc set @cnext = 0
end
select @cstart = 1
select @cnext = charindex(',',@columns,@cstart)
select @estart = @enext + 1
select @enext = charindex(',',@events,@estart)
if @enext = 0 set @enext = @le + 1
if @estart > @le set @enext = 0
end
set @cmd1 = 'exec sp_trace_setfilter '
set @filter = N'none'
select @filter = cast(ltrim(rtrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as nvarchar (245))
from #t1
where cast(ltrim(rtrim(substring(c1,1,charindex('=',c1,1)-1))) as nvarchar (245))= N'@filter1'
set @filter_num = 1
while @filter != N'none'
begin
exec (@cmd1 + @traceid + ','+@filter)
set @filter_num = @filter_num + 1
set @filter = N'none'
select @filter = cast(ltrim(rtrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as nvarchar (245)) from #t1
where cast(ltrim(rtrim(substring(c1,1,charindex('=',c1,1)-1))) as nvarchar (245))= N'@filter'
+ cast(@filter_num as nvarchar(3))
--select @filter
end
finish:
drop table #t1
exec @rc = sp_trace_setstatus @traceid, 1
IF @rc <> 0
BEGIN
PRINT
CASE
WHEN @rc = 1 THEN 'sp_trace_setstatus. Unknown error.'
WHEN @rc = 8 THEN 'sp_trace_setstatus. The specified Status is not valid.'
WHEN @rc = 9 THEN 'sp_trace_setstatus. The specified Trace Handle is not valid.'
WHEN @rc = 13 THEN 'sp_trace_setstatus. Out of memory. Returned when there is not enough memory to perform the specified action.'
END
RETURN
END
SELECT 'Trace '+LTRIM(STR(@traceid)) + ' has been lanched. The trace details:'
SELECT * FROM ::fn_trace_getinfo (@traceid)
GO
exec spTraceBuild
USE msdb
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'spTraceBuild' and type = 'P')
DROP PROC spTraceBuild
GO
-------------------------------------------------------------------------------------------------------
-- Name spTraceBuild
-- Description Starts a SQL Server trace. The code based on sp build_trace from the Microsoft
-- Knowledge Base Article Q283790.
-- Input From 'C:\Program Files\Microsoft SQL Server\MSSQL\Binn\ActivityTrace.ini'
-- Modified By Viktor Gorodnichenko
-------------------------------------------------------------------------------------------------------
CREATE PROC spTraceBuild
@traceini nvarchar (245) = N'C:\Program Files\Microsoft SQL Server\MSSQL\Binn\ActivityTrace.ini'
as
SET NOCOUNT ON
declare @traceid int, @options int, @tracefile nvarchar (245), @maxfilesize bigint
, @stoptime datetime, @minMBfree bigint, @rc int, @on bit, @cmd1 nvarchar(512)
, @events varchar(512), @columns varchar(512), @event int, @column int, @estart int, @enext int
, @cstart int, @cnext int, @le int, @lc int, @filter nvarchar(245), @filter_num int
create table #t1 ([c1] nvarchar(512))
set @cmd1 = 'bulk insert #t1 FROM '''
--select @cmd1 + @traceini
exec (@cmd1 + @traceini + '''')
select @tracefile = cast(rtrim(ltrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as nvarchar (245))
from #t1 where left(c1,3) = '@tr'
select @maxfilesize = cast(rtrim(ltrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as bigint)
from #t1 where left(c1,3) = '@ma'
select @stoptime = cast(rtrim(ltrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as datetime)
from #t1 where left(c1,3) = '@st'
SET @stoptime = CONVERT(datetime, CONVERT(varchar(20),getdate(),107) + ' ' + @stoptime)
select @options = cast(rtrim(ltrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as int)
from #t1 where left(c1,3) = '@op'
select @events=cast(ltrim(rtrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as nvarchar (512))
from #t1 where left(c1,3) = N'@ev'
select @columns=cast(ltrim(rtrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as nvarchar (512))
from #t1 where left(c1,3) = N'@co'
set @on = 1
set @traceid = 0
select @tracefile = @tracefile + '_' + @@SERVERNAME + REPLACE(LEFT(CONVERT(varchar(20),getdate(),107),6),' ','')
--select @tracefile
SELECT @traceid = traceid FROM :: fn_trace_getinfo(0) where property = 2 and value = @tracefile
if @traceid != 0 goto finish
set @cmd1 = 'if exist "' + @tracefile + '.trc" ' + 'del "' + @tracefile + '*.trc"'
exec @rc = master.dbo.xp_cmdshell @cmd1, no_output
exec @rc = sp_trace_create @traceid output, @options, @tracefile, @maxfilesize, @stoptime
IF @rc <> 0
BEGIN
CASE
WHEN @rc = 1 THEN 'sp_trace_create failed. Unknown error.'
WHEN @rc = 10 THEN 'sp_trace_create failed. Invalid options. Options specified are incompatible.'
WHEN @rc = 12 THEN 'sp_trace_create failed. File not created.'
WHEN @rc = 13 THEN 'sp_trace_create failed. Out of memory. There is not enough memory to perform the specified action.'
WHEN @rc = 14 THEN 'sp_trace_create failed. Invalid stop time. The stop time specified has already happened.'
WHEN @rc = 15 THEN 'sp_trace_create failed. Invalid parameters. Supplied incompatible parameters.'
END
RETURN
END
select @estart = 1
select @enext = charindex(',',@events,@estart)
select @cstart = 1
select @cnext = charindex(',',@columns,@cstart)
set @le = len(@events)
set @lc = len(@columns)
while @enext > 0
begin
select @event = cast(substring(@events,@estart,@enext-@estart) as int)
while @cnext > 0
begin
select @column = cast(substring(@columns,@cstart,@cnext-@cstart) as int)
exec @rc = sp_trace_setevent @traceid, @event, @column, @on
IF @rc <> 0
BEGIN
CASE
WHEN @rc = 1 THEN 'sp_trace_setevent failed. Unknown error.'
WHEN @rc = 2 THEN 'sp_trace_setevent failed. The trace is currently running.'
WHEN @rc = 3 THEN 'sp_trace_setevent failed. The specified Event is not valid. The Event may not exist or it
is not an appropriate one for the store procedure.'
WHEN @rc = 4 THEN 'sp_trace_setevent failed. The specified Column is not valid.'
WHEN @rc = 9 THEN 'sp_trace_setevent failed. The specified Trace Handle is not valid.'
WHEN @rc = 11 THEN 'sp_trace_setevent failed. The specified Column is used internally and cannot be removed. '
WHEN @rc = 13 THEN 'sp_trace_setevent failed. Out of memory. There is not enough memory to perform the specified action.'
WHEN @rc = 14 THEN 'sp_trace_setevent failed. The function is not valid for this trace.'
END
RETURN
END
select @cstart = @cnext + 1
select @cnext = charindex(',',@columns,@cstart)
if @cnext = 0 set @cnext = @lc + 1
if @cstart >@lc set @cnext = 0
end
select @cstart = 1
select @cnext = charindex(',',@columns,@cstart)
select @estart = @enext + 1
select @enext = charindex(',',@events,@estart)
if @enext = 0 set @enext = @le + 1
if @estart > @le set @enext = 0
end
set @cmd1 = 'exec sp_trace_setfilter '
set @filter = N'none'
select @filter = cast(ltrim(rtrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as nvarchar (245))
from #t1
where cast(ltrim(rtrim(substring(c1,1,charindex('=',c1,1)-1))) as nvarchar (245))= N'@filter1'
set @filter_num = 1
while @filter != N'none'
begin
exec (@cmd1 + @traceid + ','+@filter)
set @filter_num = @filter_num + 1
set @filter = N'none'
select @filter = cast(ltrim(rtrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as nvarchar (245)) from #t1
where cast(ltrim(rtrim(substring(c1,1,charindex('=',c1,1)-1))) as nvarchar (245))= N'@filter'
+ cast(@filter_num as nvarchar(3))
--select @filter
end
finish:
drop table #t1
exec @rc = sp_trace_setstatus @traceid, 1
IF @rc <> 0
BEGIN
CASE
WHEN @rc = 1 THEN 'sp_trace_setstatus. Unknown error.'
WHEN @rc = 8 THEN 'sp_trace_setstatus. The specified Status is not valid.'
WHEN @rc = 9 THEN 'sp_trace_setstatus. The specified Trace Handle is not valid.'
WHEN @rc = 13 THEN 'sp_trace_setstatus. Out of memory. Returned when there is not enough memory to perform the specified action.'
END
RETURN
END
SELECT 'Trace '+LTRIM(STR(@traceid)) + ' has been lanched. The trace details:'
SELECT * FROM ::fn_trace_getinfo (@traceid)
GO
exec spTraceBuild
sp_BlockedProcesses
--Listing 2. sp_BlockedProcesses
USE [master]
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'sp_BlockedProcesses' and type = 'P')
DROP PROC sp_BlockedProcesses
GO
-------------------------------------------------------------------------------------------------------
-- Name sp_BlockedProcesses
-- Description Returns list of blocked processes and buffers for blocked and blocking processes
-- Input None
-- Created By Viktor Gorodnichenko
-------------------------------------------------------------------------------------------------------
CREATE PROC sp_BlockedProcesses
AS
SET NOCOUNT ON
DECLARE @Blocked int
DECLARE @BlockedBy int
DECLARE @SqlStr varchar(1000)
CREATE TABLE #TmpSysprocesses
(BlockedSPID smallint,
BlockedBuffer nvarchar(255) null,
BlockingSPID smallint,
BlockingBuffer nvarchar(255) null,
waitresource nchar(256),
dbid smallint,
BlockedHostname nchar(128),
BlockedProgram_name nchar(128),
BlockedCmd nchar(16),
BlockedLoginame nchar(128),
BlockingHostname nchar(128),
BlockingProgram_name nchar(128),
BlockingCmd nchar(16),
BlockingLoginame nchar(128))
INSERT INTO #TmpSysprocesses
SELECT blocked.spid 'BlockedSPID', null 'BlockedBuffer', blocked.blocked 'BlockingSPID',
null 'BlockingBuffer', blocked.waitresource, blocked.dbid,
blocked.hostname 'BlockedHostname', blocked.program_name 'BlockedProgram_name',
blocked.cmd 'BlockedCmd', blocked.loginame 'BlockedLoginame',
Blocking.hostname 'BlockingHostname', Blocking.program_name 'BlockingProgram_name',
Blocking.cmd 'BlockingCmd', Blocking.loginame 'BlockingLoginame'
FROM master..sysprocesses blocked
JOIN master..sysprocesses blocking ON blocking.SPID = blocked.blocked
WHERE blocked.Blocked > 0
CREATE TABLE #tmpBuffer (EventType nvarchar(30), Parameters int, EventInfo nvarchar(255))
DECLARE Processes CURSOR FOR
SELECT BlockedSPID, BlockingSPID FROM #TmpSysprocesses
OPEN Processes
FETCH NEXT FROM Processes INTO @Blocked, @BlockedBy
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SqlStr = 'DBCC INPUTBUFFER ('+LTRIM(STR(@Blocked))+')'
INSERT INTO #tmpBuffer EXEC (@SqlStr)
UPDATE #TmpSysprocesses SET BlockedBuffer = EventInfo
FROM #tmpBuffer
WHERE BlockedSPID = @Blocked and BlockingSPID = @BlockedBy
TRUNCATE TABLE #tmpBuffer
SET @SqlStr = 'DBCC INPUTBUFFER ('+LTRIM(STR(@BlockedBy))+')'
INSERT INTO #tmpBuffer EXEC (@SqlStr)
UPDATE #TmpSysprocesses SET BlockingBuffer = EventInfo
FROM #tmpBuffer
WHERE BlockedSPID = @Blocked and BlockingSPID = @BlockedBy
TRUNCATE TABLE #tmpBuffer
FETCH NEXT FROM Processes INTO @Blocked, @BlockedBy
END
SELECT * FROM #TmpSysprocesses
exec sp_BlockedProcesses
USE [master]
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'sp_BlockedProcesses' and type = 'P')
DROP PROC sp_BlockedProcesses
GO
-------------------------------------------------------------------------------------------------------
-- Name sp_BlockedProcesses
-- Description Returns list of blocked processes and buffers for blocked and blocking processes
-- Input None
-- Created By Viktor Gorodnichenko
-------------------------------------------------------------------------------------------------------
CREATE PROC sp_BlockedProcesses
AS
SET NOCOUNT ON
DECLARE @Blocked int
DECLARE @BlockedBy int
DECLARE @SqlStr varchar(1000)
CREATE TABLE #TmpSysprocesses
(BlockedSPID smallint,
BlockedBuffer nvarchar(255) null,
BlockingSPID smallint,
BlockingBuffer nvarchar(255) null,
waitresource nchar(256),
dbid smallint,
BlockedHostname nchar(128),
BlockedProgram_name nchar(128),
BlockedCmd nchar(16),
BlockedLoginame nchar(128),
BlockingHostname nchar(128),
BlockingProgram_name nchar(128),
BlockingCmd nchar(16),
BlockingLoginame nchar(128))
INSERT INTO #TmpSysprocesses
SELECT blocked.spid 'BlockedSPID', null 'BlockedBuffer', blocked.blocked 'BlockingSPID',
null 'BlockingBuffer', blocked.waitresource, blocked.dbid,
blocked.hostname 'BlockedHostname', blocked.program_name 'BlockedProgram_name',
blocked.cmd 'BlockedCmd', blocked.loginame 'BlockedLoginame',
Blocking.hostname 'BlockingHostname', Blocking.program_name 'BlockingProgram_name',
Blocking.cmd 'BlockingCmd', Blocking.loginame 'BlockingLoginame'
FROM master..sysprocesses blocked
JOIN master..sysprocesses blocking ON blocking.SPID = blocked.blocked
WHERE blocked.Blocked > 0
CREATE TABLE #tmpBuffer (EventType nvarchar(30), Parameters int, EventInfo nvarchar(255))
DECLARE Processes CURSOR FOR
SELECT BlockedSPID, BlockingSPID FROM #TmpSysprocesses
OPEN Processes
FETCH NEXT FROM Processes INTO @Blocked, @BlockedBy
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SqlStr = 'DBCC INPUTBUFFER ('+LTRIM(STR(@Blocked))+')'
INSERT INTO #tmpBuffer EXEC (@SqlStr)
UPDATE #TmpSysprocesses SET BlockedBuffer = EventInfo
FROM #tmpBuffer
WHERE BlockedSPID = @Blocked and BlockingSPID = @BlockedBy
TRUNCATE TABLE #tmpBuffer
SET @SqlStr = 'DBCC INPUTBUFFER ('+LTRIM(STR(@BlockedBy))+')'
INSERT INTO #tmpBuffer EXEC (@SqlStr)
UPDATE #TmpSysprocesses SET BlockingBuffer = EventInfo
FROM #tmpBuffer
WHERE BlockedSPID = @Blocked and BlockingSPID = @BlockedBy
TRUNCATE TABLE #tmpBuffer
FETCH NEXT FROM Processes INTO @Blocked, @BlockedBy
END
SELECT * FROM #TmpSysprocesses
exec sp_BlockedProcesses
Sp_ActiveProcesses
--Listing 1. Sp_ActiveProcesses.
USE master
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'sp_ActiveProcesses' and type = 'P')
DROP PROC sp_ActiveProcesses
GO
-------------------------------------------------------------------------------------------------------
-- Name sp_ActiveProcesses
-- Description Returns list of active processes and their buffer contents (what they execute)
-- A process is considered as active if it has some changes of cpu time consumed or number
-- of io operation in specified period.
-- Input (optional) @Delay - Time interval to catch activity
-- Output Result set with active processes
-- Created By Viktor Gorodnichenko
-------------------------------------------------------------------------------------------------------
CREATE PROC sp_ActiveProcesses
@Delay smallint = 5
AS
SET NOCOUNT ON
IF @Delay > 59
SET @Delay = 59
IF @Delay < 1
SET @Delay = 1
print @Delay
DECLARE @SPID int
DECLARE @DelayClock char(8)
DECLARE @SqlStr varchar(1000)
DECLARE @Internal_Value int
SET @DelayClock = '00:00:'+LTRIM(STR(@Delay))
CREATE TABLE #TmpSysprocesses
(EventTime datetime,
FragmentDuration int,
spid smallint,
cpu int,
physical_io int,
cpuUpdate int null,
physical_ioUpdate int null,
hostname sysname,
program_name sysname,
loginame sysname,
dbid smallint,
dbname sysname null,
IsActive bit null,
SPIDBuffer nvarchar(255) null)
INSERT INTO #TmpSysprocesses
SELECT getdate(), null 'FragmentDuration', spid, SUM(cpu), SUM(physical_io), null, null, MAX(hostname), MAX(program_name),
MAX(loginame), MAX(dbid), null, null, null
FROM sysprocesses
GROUP BY spid
HAVING SUM(dbid) > 0
WAITFOR DELAY @DelayClock
CREATE TABLE #TmpSysprocesses2
(EventTime datetime,
spid smallint,
cpu int,
physical_io int,
hostname sysname,
program_name sysname,
loginame sysname,
dbid smallint)
INSERT INTO #TmpSysprocesses2
SELECT getdate(), spid, SUM(cpu), SUM(physical_io), MAX(hostname), MAX(program_name), MAX(loginame), MAX(dbid)
FROM sysprocesses
GROUP BY spid
HAVING SUM(dbid) > 0
UPDATE #TmpSysprocesses SET FragmentDuration = DATEDIFF(ms,t.EventTime,t2.EventTime), IsActive = 1,
cpuUpdate = t2.cpu - t.cpu,
physical_ioUpdate = t2.physical_io - t.physical_io, dbname = d.name
FROM #TmpSysprocesses t
JOIN #TmpSysprocesses2 t2 ON t2.spid = t.spid
and t2.hostname = t.hostname
and t2.loginame = t.loginame
and (t2.cpu <> t.cpu or t2.physical_io <> t.physical_io)
JOIN sysdatabases d ON d.dbid = t.dbid
CREATE TABLE #tmpBuffer (EventType nvarchar(30), Parameters int, EventInfo nvarchar(255))
DECLARE ActiveProcesses CURSOR FOR
SELECT spid FROM #TmpSysprocesses WHERE IsActive = 1
OPEN ActiveProcesses
FETCH NEXT FROM ActiveProcesses INTO @SPID
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SqlStr = 'DBCC INPUTBUFFER ('+LTRIM(STR(@SPID))+')'
INSERT INTO #tmpBuffer EXEC (@SqlStr)
UPDATE #TmpSysprocesses SET SPIDBuffer = EventInfo
FROM #tmpBuffer
WHERE spid = @SPID
TRUNCATE TABLE #tmpBuffer
FETCH NEXT FROM ActiveProcesses INTO @SPID
END
DEALLOCATE ActiveProcesses
CREATE TABLE #xp_msver (
[Index] int,
[Name] varchar(1000) null,
Internal_Value int null,
Character_value varchar(1000) null)
INSERT INTO #xp_msver EXEC master..xp_msver 'ProcessorCount'
SELECT @Internal_Value = Internal_Value FROM #xp_msver
WHERE [Name] = 'ProcessorCount'
SELECT spid 'ProcessId',
cpu 'TotalCPU',
cpuUpdate 'CPU_ConsumedInTheTimeFragment',
physical_io 'TotalPhysical_IO',
physical_ioUpdate 'Physical_IO_InTheTimeFragment',
LEFT(hostname,12) 'Hostname',
LEFT(program_name,30) 'ApplicationName', LEFT(loginame,30) 'NT_LoginName', dbname 'DatabaseName', SPIDBuffer
FROM #TmpSysprocesses s WHERE IsActive = 1
SELECT MAX(FragmentDuration) 'TheFragmentDuration', @Internal_Value 'NumberOfCPUs',
SUM(cpuUpdate) 'SUM CPU_ConsumedInTheTimeFragment',
SUM(physical_ioUpdate) 'SUM Physical_IO_InTheTimeFragment'
FROM #TmpSysprocesses s WHERE IsActive = 1
DROP TABLE #xp_msver
USE master
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'sp_ActiveProcesses' and type = 'P')
DROP PROC sp_ActiveProcesses
GO
-------------------------------------------------------------------------------------------------------
-- Name sp_ActiveProcesses
-- Description Returns list of active processes and their buffer contents (what they execute)
-- A process is considered as active if it has some changes of cpu time consumed or number
-- of io operation in specified period.
-- Input (optional) @Delay - Time interval to catch activity
-- Output Result set with active processes
-- Created By Viktor Gorodnichenko
-------------------------------------------------------------------------------------------------------
CREATE PROC sp_ActiveProcesses
@Delay smallint = 5
AS
SET NOCOUNT ON
IF @Delay > 59
SET @Delay = 59
IF @Delay < 1
SET @Delay = 1
print @Delay
DECLARE @SPID int
DECLARE @DelayClock char(8)
DECLARE @SqlStr varchar(1000)
DECLARE @Internal_Value int
SET @DelayClock = '00:00:'+LTRIM(STR(@Delay))
CREATE TABLE #TmpSysprocesses
(EventTime datetime,
FragmentDuration int,
spid smallint,
cpu int,
physical_io int,
cpuUpdate int null,
physical_ioUpdate int null,
hostname sysname,
program_name sysname,
loginame sysname,
dbid smallint,
dbname sysname null,
IsActive bit null,
SPIDBuffer nvarchar(255) null)
INSERT INTO #TmpSysprocesses
SELECT getdate(), null 'FragmentDuration', spid, SUM(cpu), SUM(physical_io), null, null, MAX(hostname), MAX(program_name),
MAX(loginame), MAX(dbid), null, null, null
FROM sysprocesses
GROUP BY spid
HAVING SUM(dbid) > 0
WAITFOR DELAY @DelayClock
CREATE TABLE #TmpSysprocesses2
(EventTime datetime,
spid smallint,
cpu int,
physical_io int,
hostname sysname,
program_name sysname,
loginame sysname,
dbid smallint)
INSERT INTO #TmpSysprocesses2
SELECT getdate(), spid, SUM(cpu), SUM(physical_io), MAX(hostname), MAX(program_name), MAX(loginame), MAX(dbid)
FROM sysprocesses
GROUP BY spid
HAVING SUM(dbid) > 0
UPDATE #TmpSysprocesses SET FragmentDuration = DATEDIFF(ms,t.EventTime,t2.EventTime), IsActive = 1,
cpuUpdate = t2.cpu - t.cpu,
physical_ioUpdate = t2.physical_io - t.physical_io, dbname = d.name
FROM #TmpSysprocesses t
JOIN #TmpSysprocesses2 t2 ON t2.spid = t.spid
and t2.hostname = t.hostname
and t2.loginame = t.loginame
and (t2.cpu <> t.cpu or t2.physical_io <> t.physical_io)
JOIN sysdatabases d ON d.dbid = t.dbid
CREATE TABLE #tmpBuffer (EventType nvarchar(30), Parameters int, EventInfo nvarchar(255))
DECLARE ActiveProcesses CURSOR FOR
SELECT spid FROM #TmpSysprocesses WHERE IsActive = 1
OPEN ActiveProcesses
FETCH NEXT FROM ActiveProcesses INTO @SPID
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SqlStr = 'DBCC INPUTBUFFER ('+LTRIM(STR(@SPID))+')'
INSERT INTO #tmpBuffer EXEC (@SqlStr)
UPDATE #TmpSysprocesses SET SPIDBuffer = EventInfo
FROM #tmpBuffer
WHERE spid = @SPID
TRUNCATE TABLE #tmpBuffer
FETCH NEXT FROM ActiveProcesses INTO @SPID
END
DEALLOCATE ActiveProcesses
CREATE TABLE #xp_msver (
[Index] int,
[Name] varchar(1000) null,
Internal_Value int null,
Character_value varchar(1000) null)
INSERT INTO #xp_msver EXEC master..xp_msver 'ProcessorCount'
SELECT @Internal_Value = Internal_Value FROM #xp_msver
WHERE [Name] = 'ProcessorCount'
SELECT spid 'ProcessId',
cpu 'TotalCPU',
cpuUpdate 'CPU_ConsumedInTheTimeFragment',
physical_io 'TotalPhysical_IO',
physical_ioUpdate 'Physical_IO_InTheTimeFragment',
LEFT(hostname,12) 'Hostname',
LEFT(program_name,30) 'ApplicationName', LEFT(loginame,30) 'NT_LoginName', dbname 'DatabaseName', SPIDBuffer
FROM #TmpSysprocesses s WHERE IsActive = 1
SELECT MAX(FragmentDuration) 'TheFragmentDuration', @Internal_Value 'NumberOfCPUs',
SUM(cpuUpdate) 'SUM CPU_ConsumedInTheTimeFragment',
SUM(physical_ioUpdate) 'SUM Physical_IO_InTheTimeFragment'
FROM #TmpSysprocesses s WHERE IsActive = 1
DROP TABLE #xp_msver
Using SSIS to monitor SQL Server Databases
Analyzing SQL Server Connections Over Time Using SSIS :
=======================================================
http://www.simple-talk.com/sql/database-administration/using-ssis-to-monitor-sql-server-databases-/
I wanted to know if there were any long-running jobs or processes in any of the eighty-odd SQL Servers that I manage. These are a sign of a problem that has to be investigated and put right. I’d developed a simple query to get the process Information from each database, but I wasn’t sure if this would interfere with its performance if it ran once every hour in a SQL Agent job: if so, was it worth it?
I soon realized that this could do much more than to isolate long running processes: I found that I could now deep dive into the data for trend analysis, capacity planning, concurrency issues, and even potential security breaches. In this article I will demonstrate the simple SSIS package that I use to collect and gather all the process information for these servers. I’ll also show you the queries that I use to analyze the hundreds of thousands of records I collected in just the first week .
Connection Collection
I’ve just said that the query to return the results that I needed was simple. Listing 1 should back me up on that. Notice, though, that even though it is simple, almost everything that you could want is returned. Sure, there are other fields that I could have used, like a “blocked_by” column, the same as the sp_who2 “blkby” column would provide. Also, it would have been useful to know the actual query that was being executed at the time, but this is not a server trace after all. To use a drummer’s cliché, “Less is more.” What I needed was a way to identify potential problems and then use other tools to get the details.
Listing 1: Simple Connections Query
SELECT GetDate() as Run_Date,
rtrim(Cast(@@ServerName as varchar(100))) as Server,
spid,
blocked,
waittime,
sb.name,
lastwaittype,
sp.cpu,
sp.login_time,
sp.last_batch,
sp.status,
sp.hostname,
sp.program_name,
sp.cmd,
sp.loginame,
getdate() - last_batch as duration
FROM master..sysprocesses sp
INNER JOIN master..sysdatabases sb ON sp.dbid = sb.dbid
More Information
http://msdn.microsoft.com/en-us/library/ms139805.aspx
=======================================================
http://www.simple-talk.com/sql/database-administration/using-ssis-to-monitor-sql-server-databases-/
I wanted to know if there were any long-running jobs or processes in any of the eighty-odd SQL Servers that I manage. These are a sign of a problem that has to be investigated and put right. I’d developed a simple query to get the process Information from each database, but I wasn’t sure if this would interfere with its performance if it ran once every hour in a SQL Agent job: if so, was it worth it?
I soon realized that this could do much more than to isolate long running processes: I found that I could now deep dive into the data for trend analysis, capacity planning, concurrency issues, and even potential security breaches. In this article I will demonstrate the simple SSIS package that I use to collect and gather all the process information for these servers. I’ll also show you the queries that I use to analyze the hundreds of thousands of records I collected in just the first week .
Connection Collection
I’ve just said that the query to return the results that I needed was simple. Listing 1 should back me up on that. Notice, though, that even though it is simple, almost everything that you could want is returned. Sure, there are other fields that I could have used, like a “blocked_by” column, the same as the sp_who2 “blkby” column would provide. Also, it would have been useful to know the actual query that was being executed at the time, but this is not a server trace after all. To use a drummer’s cliché, “Less is more.” What I needed was a way to identify potential problems and then use other tools to get the details.
Listing 1: Simple Connections Query
SELECT GetDate() as Run_Date,
rtrim(Cast(@@ServerName as varchar(100))) as Server,
spid,
blocked,
waittime,
sb.name,
lastwaittype,
sp.cpu,
sp.login_time,
sp.last_batch,
sp.status,
sp.hostname,
sp.program_name,
sp.cmd,
sp.loginame,
getdate() - last_batch as duration
FROM master..sysprocesses sp
INNER JOIN master..sysdatabases sb ON sp.dbid = sb.dbid
More Information
http://msdn.microsoft.com/en-us/library/ms139805.aspx
view database connections in sql server
view database connections in sql server:
=======================================
To view database connections in SQL Server you can query the sysprocesses table in master database. This is a SQL Server 2000 table which is included as view in 2005 for backward compatibility. The script will show total number of connections to each database including internal connections, if you want to view only external connections change the WHERE clause to add " AND spid > 51 " to view only external connections to each database.
If you are running SQL Server 2000 use this script.
SELECT DB_NAME(dbid) as 'Database Name',
COUNT(dbid) as 'Total Connections'
FROM master.dbo.sysprocesses WITH (nolock)
WHERE dbid > 0
GROUP BY dbid
If you are using SQL Server 2005 edition then use sys.sysprocesses to get the same results.
SELECT DB_NAME(dbid) as 'Database Name',
COUNT(dbid) as 'Total Connections'
FROM sys.sysprocesses WITH (nolock)
WHERE dbid > 0
GROUP BY dbid
=======================================
To view database connections in SQL Server you can query the sysprocesses table in master database. This is a SQL Server 2000 table which is included as view in 2005 for backward compatibility. The script will show total number of connections to each database including internal connections, if you want to view only external connections change the WHERE clause to add " AND spid > 51 " to view only external connections to each database.
If you are running SQL Server 2000 use this script.
SELECT DB_NAME(dbid) as 'Database Name',
COUNT(dbid) as 'Total Connections'
FROM master.dbo.sysprocesses WITH (nolock)
WHERE dbid > 0
GROUP BY dbid
If you are using SQL Server 2005 edition then use sys.sysprocesses to get the same results.
SELECT DB_NAME(dbid) as 'Database Name',
COUNT(dbid) as 'Total Connections'
FROM sys.sysprocesses WITH (nolock)
WHERE dbid > 0
GROUP BY dbid
Find Largest size Tables in a Database
IF EXISTS
(
SELECT 1 FROM master.dbo.sysobjects
WHERE name = 'sp_LargestTables' AND type = 'P'
)
DROP PROC sp_LargestTables
GO
CREATE PROC sp_LargestTables(@n int = NULL,@IsSystemAllowed bit = 0)
AS
/*=========================================================================
CREATE DATE : Hari N Sharma
CREATION DATE : 10-09-2007
LAST MODIFICATION DATE : 11-10-2007
PURPOSE : To get a list of User/System tables according to their size.
=========================================================================*/
BEGIN
SET NOCOUNT ON
DECLARE @LOW int
SELECT @LOW = LOW FROM master.dbo.spt_values (NOLOCK) WHERE number = 1 AND type = 'E'
IF @n > 0 SET ROWCOUNT @n
SELECT TableName,[Row Count],[Size (KB)] FROM
(
SELECT QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id)) AS TableName,SUM(i.rowcnt) [Row Count],
CONVERT(numeric(15,2),(((CONVERT(numeric(15,2),SUM(i.reserved)) * @LOW) / 1024.0))) AS [Size (KB)]
FROM sysindexes i INNER JOIN sysobjects o (NOLOCK) ON i.id = o.id AND
((@IsSystemAllowed = 1 AND o.type IN ('U', 'S')) OR o.type = 'U')
WHERE indid IN (0, 1, 255)
GROUP BY QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))
) AS Z
ORDER BY [Size (KB)] DESC
SET ROWCOUNT 0
END
GO
----------------------------------------------
http://www.sqlservercentral.com/scripts/Administration/63646/
(
SELECT 1 FROM master.dbo.sysobjects
WHERE name = 'sp_LargestTables' AND type = 'P'
)
DROP PROC sp_LargestTables
GO
CREATE PROC sp_LargestTables(@n int = NULL,@IsSystemAllowed bit = 0)
AS
/*=========================================================================
CREATE DATE : Hari N Sharma
CREATION DATE : 10-09-2007
LAST MODIFICATION DATE : 11-10-2007
PURPOSE : To get a list of User/System tables according to their size.
=========================================================================*/
BEGIN
SET NOCOUNT ON
DECLARE @LOW int
SELECT @LOW = LOW FROM master.dbo.spt_values (NOLOCK) WHERE number = 1 AND type = 'E'
IF @n > 0 SET ROWCOUNT @n
SELECT TableName,[Row Count],[Size (KB)] FROM
(
SELECT QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id)) AS TableName,SUM(i.rowcnt) [Row Count],
CONVERT(numeric(15,2),(((CONVERT(numeric(15,2),SUM(i.reserved)) * @LOW) / 1024.0))) AS [Size (KB)]
FROM sysindexes i INNER JOIN sysobjects o (NOLOCK) ON i.id = o.id AND
((@IsSystemAllowed = 1 AND o.type IN ('U', 'S')) OR o.type = 'U')
WHERE indid IN (0, 1, 255)
GROUP BY QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))
) AS Z
ORDER BY [Size (KB)] DESC
SET ROWCOUNT 0
END
GO
----------------------------------------------
http://www.sqlservercentral.com/scripts/Administration/63646/
woensdag 19 november 2008
Email from SQL Server 2000
Add Extend procedure:
====================
xp_smtp_sendmail
---------------------------------
USE [BeheerDB]
GO
/****** Object: Table [dbo].[DBA_sysdatabases] Script Date: 11/19/2008 16:58:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DBA_sysdatabases](
[name] [sysname] NOT NULL,
PRIMARY KEY CLUSTERED
(
[name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
---------------------------------
USE [BeheerDB]
GO
/****** Object: Table [dbo].[DBA_syslogins] Script Date: 11/19/2008 16:58:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DBA_syslogins](
[name] [sysname] NOT NULL,
PRIMARY KEY CLUSTERED
(
[name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
-----------------------------------
if not exists (select 1 from tempdb..sysobjects
where type = 'U' and name = 'DBA_syslogins')
create table tempdb..DBA_syslogins
(name sysname not null primary key)
go
declare @body varchar(8000)
set @body = 'New logins: '
-- If it's not the first time it executes:
if exists (select 1 from tempdb..DBA_syslogins)
begin
-- If there are any new logins:
if exists (select 1 from master..syslogins
where name not in
(select name from tempdb..DBA_syslogins))
begin
select @body = @body + name + ','
from master..syslogins
where name not in (select name from tempdb..DBA_syslogins)
-- Omit the last comma:
select @body = substring(@body,1,LEN(@body)-1)
-- Send the message to the DBA with new logins:
EXEC msdb.dbo.sp_send_dbmail
@recipients=N'michelle.gutzait@Adventure-Works.com',
@body=N'New logins have been created.'
end
end
-- Modify the table’s content anyway
-- (since we’re not checking for deleted logins):
truncate table tempdb..DBA_syslogins
insert into tempdb..DBA_syslogins (name)
select name from master..syslogins
----------------------------------
USE [BeheerDB]
GO
/****** Object: StoredProcedure [dbo].[spDBA_NewDatabaseNotification] Script Date: 11/19/2008 15:21:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[spDBA_NewDatabaseNotification]
As
if not exists (select 1 from BeheerDB..sysobjects where type = 'U' and name = 'DBA_sysdatabases')
create table BeheerDB..DBA_sysdatabases
(name sysname not null primary key)
declare @body varchar(8000)
set @body = ',New databases: '
-- If it's not the first time it executes:
if exists (select 1 from BeheerDB..DBA_sysdatabases)
begin
if exists (select 1 from master..sysdatabases
where name not in
(select name from BeheerDB..DBA_sysdatabases))
-- Modify the table’s content anyway
-- (since we’re not checking for deleted databases):
truncate table BeheerDB..DBA_sysdatabases
insert into BeheerDB..DBA_sysdatabases (name)
select name from master..sysdatabases
begin
select @body= @body + name + ','
from master..sysdatabases
where name not in
(select name from BeheerDB..DBA_sysdatabases)
-- Omit the last comma:
select @body = substring(@body,1,LEN(@body)-1)
-- Send the message to the DBA with new databases:
exec master.dbo.xp_smtp_sendmail
@TO = N'bfadam@binck.nl',
@From = N'O1WSW00NL',
@priority = N'NORMAL',
@subject = @body,
@type = N'text/plain',
@message = N'New databases have been created',
@messagefile= N'',
@attachment = N'',
@attachments= N'',
@codepage = 0,
@timeout = 10000,
@server = N'EXCHCLUSTER.binck.nl'
end
end
====================
xp_smtp_sendmail
---------------------------------
USE [BeheerDB]
GO
/****** Object: Table [dbo].[DBA_sysdatabases] Script Date: 11/19/2008 16:58:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DBA_sysdatabases](
[name] [sysname] NOT NULL,
PRIMARY KEY CLUSTERED
(
[name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
---------------------------------
USE [BeheerDB]
GO
/****** Object: Table [dbo].[DBA_syslogins] Script Date: 11/19/2008 16:58:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DBA_syslogins](
[name] [sysname] NOT NULL,
PRIMARY KEY CLUSTERED
(
[name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
-----------------------------------
if not exists (select 1 from tempdb..sysobjects
where type = 'U' and name = 'DBA_syslogins')
create table tempdb..DBA_syslogins
(name sysname not null primary key)
go
declare @body varchar(8000)
set @body = 'New logins: '
-- If it's not the first time it executes:
if exists (select 1 from tempdb..DBA_syslogins)
begin
-- If there are any new logins:
if exists (select 1 from master..syslogins
where name not in
(select name from tempdb..DBA_syslogins))
begin
select @body = @body + name + ','
from master..syslogins
where name not in (select name from tempdb..DBA_syslogins)
-- Omit the last comma:
select @body = substring(@body,1,LEN(@body)-1)
-- Send the message to the DBA with new logins:
EXEC msdb.dbo.sp_send_dbmail
@recipients=N'michelle.gutzait@Adventure-Works.com',
@body=N'New logins have been created.'
end
end
-- Modify the table’s content anyway
-- (since we’re not checking for deleted logins):
truncate table tempdb..DBA_syslogins
insert into tempdb..DBA_syslogins (name)
select name from master..syslogins
----------------------------------
USE [BeheerDB]
GO
/****** Object: StoredProcedure [dbo].[spDBA_NewDatabaseNotification] Script Date: 11/19/2008 15:21:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[spDBA_NewDatabaseNotification]
As
if not exists (select 1 from BeheerDB..sysobjects where type = 'U' and name = 'DBA_sysdatabases')
create table BeheerDB..DBA_sysdatabases
(name sysname not null primary key)
declare @body varchar(8000)
set @body = ',New databases: '
-- If it's not the first time it executes:
if exists (select 1 from BeheerDB..DBA_sysdatabases)
begin
if exists (select 1 from master..sysdatabases
where name not in
(select name from BeheerDB..DBA_sysdatabases))
-- Modify the table’s content anyway
-- (since we’re not checking for deleted databases):
truncate table BeheerDB..DBA_sysdatabases
insert into BeheerDB..DBA_sysdatabases (name)
select name from master..sysdatabases
begin
select @body= @body + name + ','
from master..sysdatabases
where name not in
(select name from BeheerDB..DBA_sysdatabases)
-- Omit the last comma:
select @body = substring(@body,1,LEN(@body)-1)
-- Send the message to the DBA with new databases:
exec master.dbo.xp_smtp_sendmail
@TO = N'bfadam@binck.nl',
@From = N'O1WSW00NL',
@priority = N'NORMAL',
@subject = @body,
@type = N'text/plain',
@message = N'New databases have been created',
@messagefile= N'',
@attachment = N'',
@attachments= N'',
@codepage = 0,
@timeout = 10000,
@server = N'EXCHCLUSTER.binck.nl'
end
end
Can I use xp_sendmail to send email from SQL Server without installing Microsoft Outlook?
http://sqldev.net/xp/xpsmtp.htm
XPSMTP.DLL - SQL Server SMTP Mail XP
XPSMTP.DLL - SQL Server SMTP Mail XP
List of Database Users with Database Roles
http://www.sqlservercentral.com/articles/Administering/listofdatabaseuserswithdatabaseroles/1545/
Sometime we come across specific request from our managers or users that needs to explore system tables and database. Today I got the same kind of request from my manager. He needs a list of all database users with database roles. I looked here and there but I didn’t find any help in BOL to fulfill this request. Then I decided to create a procedure to generate this information in Pivot table format. This procedure capture the user name with list of all fixed database roles. I further modified this process to store this information in table with current date time stamp, So that in future it will help audit the changes in fixed database roles. Here is the detail.
1. Create the table DBROLES using below script in any database
CREATE TABLE TBL_DBROLES
( DBName sysname not null,
UserName sysname not null,
db_owner varchar(3) not null,
db_accessadmin varchar(3) not null,
db_securityadmin varchar(3) not null,
db_ddladmin varchar(3) not null,
db_datareader varchar(3) not null,
db_datawriter varchar(3) not null,
db_denydatareader varchar(3) not null,
db_denydatawriter varchar(3) not null,
Cur_Date datetime not null default getdate()
)
GO
---------------------------------------------------
Please include all the user defined database roles in above table as Column Name.
Create the Stored Procedure GET_LIST_OF_DBROLES using the below script in the same database where you have created the table DBROLES.
---------------------------------------------------
USE [DBeheer]
GO
/****** Object: StoredProcedure [dbo].[Get_List_of_dbroles] Script Date: 11/19/2008 10:35:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create procedure [dbo].[usp_RechtenLijst]
as
declare @dbname varchar(200)
declare @mSql1 varchar(8000)
DECLARE DBName_Cursor CURSOR FOR
select name
from master.dbo.sysdatabases
where name not in ('mssecurity','tempdb')
Order by name
OPEN DBName_Cursor
FETCH NEXT FROM DBName_Cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
Set @mSQL1 = ' Insert into TBL_DBROLES ( DBName, UserName, db_owner, db_accessadmin,
db_securityadmin, db_ddladmin, db_datareader, db_datawriter,
db_denydatareader, db_denydatawriter )
SELECT '+''''+@dbName +''''+ ' as DBName ,UserName, '+char(13)+ '
Max(CASE RoleName WHEN ''db_owner'' THEN ''Yes'' ELSE ''No'' END) AS db_owner,
Max(CASE RoleName WHEN ''db_accessadmin '' THEN ''Yes'' ELSE ''No'' END) AS db_accessadmin ,
Max(CASE RoleName WHEN ''db_securityadmin'' THEN ''Yes'' ELSE ''No'' END) AS db_securityadmin,
Max(CASE RoleName WHEN ''db_ddladmin'' THEN ''Yes'' ELSE ''No'' END) AS db_ddladmin,
Max(CASE RoleName WHEN ''db_datareader'' THEN ''Yes'' ELSE ''No'' END) AS db_datareader,
Max(CASE RoleName WHEN ''db_datawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_datawriter,
Max(CASE RoleName WHEN ''db_denydatareader'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatareader,
Max(CASE RoleName WHEN ''db_denydatawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatawriter
from (
select b.name as USERName, c.name as RoleName
from ' + @dbName+'.dbo.sysmembers a '+char(13)+
' join '+ @dbName+'.dbo.sysusers b '+char(13)+
' on a.memberuid = b.uid join '+@dbName +'.dbo.sysusers c
on a.groupuid = c.uid )s
Group by USERName
order by UserName'
--Print @mSql1
Execute (@mSql1)
FETCH NEXT FROM DBName_Cursor INTO @dbname
END
CLOSE DBName_Cursor
DEALLOCATE DBName_Cursor
---------------------------------
4. Please include the additional column in the above scripts also.
5. Please Execute the Stored Procedure GET_LIST_OF_DBROLES
Now you can get the list of all user with roles as below
PS: I have included the few column in result due to row size limitation.
6. To get the list of rights for a specific user or database, please use the where clause as :
Select * from DBROLES where DBName = 'Userdb1'
Select * from DBROLES where UserName = 'User1'
You can schedule the above SP through Job to execute every week or month as per the requirement. Doing this we’ll able to find out when we assigned any rights to a user.
Sometime we come across specific request from our managers or users that needs to explore system tables and database. Today I got the same kind of request from my manager. He needs a list of all database users with database roles. I looked here and there but I didn’t find any help in BOL to fulfill this request. Then I decided to create a procedure to generate this information in Pivot table format. This procedure capture the user name with list of all fixed database roles. I further modified this process to store this information in table with current date time stamp, So that in future it will help audit the changes in fixed database roles. Here is the detail.
1. Create the table DBROLES using below script in any database
CREATE TABLE TBL_DBROLES
( DBName sysname not null,
UserName sysname not null,
db_owner varchar(3) not null,
db_accessadmin varchar(3) not null,
db_securityadmin varchar(3) not null,
db_ddladmin varchar(3) not null,
db_datareader varchar(3) not null,
db_datawriter varchar(3) not null,
db_denydatareader varchar(3) not null,
db_denydatawriter varchar(3) not null,
Cur_Date datetime not null default getdate()
)
GO
---------------------------------------------------
Please include all the user defined database roles in above table as Column Name.
Create the Stored Procedure GET_LIST_OF_DBROLES using the below script in the same database where you have created the table DBROLES.
---------------------------------------------------
USE [DBeheer]
GO
/****** Object: StoredProcedure [dbo].[Get_List_of_dbroles] Script Date: 11/19/2008 10:35:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create procedure [dbo].[usp_RechtenLijst]
as
declare @dbname varchar(200)
declare @mSql1 varchar(8000)
DECLARE DBName_Cursor CURSOR FOR
select name
from master.dbo.sysdatabases
where name not in ('mssecurity','tempdb')
Order by name
OPEN DBName_Cursor
FETCH NEXT FROM DBName_Cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
Set @mSQL1 = ' Insert into TBL_DBROLES ( DBName, UserName, db_owner, db_accessadmin,
db_securityadmin, db_ddladmin, db_datareader, db_datawriter,
db_denydatareader, db_denydatawriter )
SELECT '+''''+@dbName +''''+ ' as DBName ,UserName, '+char(13)+ '
Max(CASE RoleName WHEN ''db_owner'' THEN ''Yes'' ELSE ''No'' END) AS db_owner,
Max(CASE RoleName WHEN ''db_accessadmin '' THEN ''Yes'' ELSE ''No'' END) AS db_accessadmin ,
Max(CASE RoleName WHEN ''db_securityadmin'' THEN ''Yes'' ELSE ''No'' END) AS db_securityadmin,
Max(CASE RoleName WHEN ''db_ddladmin'' THEN ''Yes'' ELSE ''No'' END) AS db_ddladmin,
Max(CASE RoleName WHEN ''db_datareader'' THEN ''Yes'' ELSE ''No'' END) AS db_datareader,
Max(CASE RoleName WHEN ''db_datawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_datawriter,
Max(CASE RoleName WHEN ''db_denydatareader'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatareader,
Max(CASE RoleName WHEN ''db_denydatawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatawriter
from (
select b.name as USERName, c.name as RoleName
from ' + @dbName+'.dbo.sysmembers a '+char(13)+
' join '+ @dbName+'.dbo.sysusers b '+char(13)+
' on a.memberuid = b.uid join '+@dbName +'.dbo.sysusers c
on a.groupuid = c.uid )s
Group by USERName
order by UserName'
--Print @mSql1
Execute (@mSql1)
FETCH NEXT FROM DBName_Cursor INTO @dbname
END
CLOSE DBName_Cursor
DEALLOCATE DBName_Cursor
---------------------------------
4. Please include the additional column in the above scripts also.
5. Please Execute the Stored Procedure GET_LIST_OF_DBROLES
Now you can get the list of all user with roles as below
PS: I have included the few column in result due to row size limitation.
6. To get the list of rights for a specific user or database, please use the where clause as :
Select * from DBROLES where DBName = 'Userdb1'
Select * from DBROLES where UserName = 'User1'
You can schedule the above SP through Job to execute every week or month as per the requirement. Doing this we’ll able to find out when we assigned any rights to a user.
checksqljobs
USE [msdb]
GO
/****** Object: StoredProcedure [dbo].[checksqljobs] Script Date: 03/05/2008 07:54:42 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE Procedure [dbo].[checksqljobs]
As
DECLARE @job_id UNIQUEIDENTIFIER
DECLARE @job_type VARCHAR(12)
DECLARE @owner_login_name sysname
DECLARE @subsystem NVARCHAR(40)
DECLARE @category_id INT
DECLARE @enabled TINYINT
DECLARE @execution_status INT
DECLARE @date_comparator CHAR(1)
DECLARE @date_created DATETIME
DECLARE @date_last_modified DATETIME
DECLARE @description NVARCHAR(512)
DECLARE @is_sysadmin INT
DECLARE @job_owner sysname
SET NOCOUNT ON
-- By 'composite' we mean a combination of sysjobs and xp_sqlagent_enum_jobs data.
-- This proc should only ever be called by sp_help_job, so we don't verify the
-- parameters (sp_help_job has already done this).
-- Step 1: Create intermediate work tables
CREATE TABLE #job_execution_state (job_id UNIQUEIDENTIFIER NOT NULL,
date_started INT NOT NULL,
time_started INT NOT NULL,
execution_job_status INT NOT NULL,
execution_step_id INT NULL,
execution_step_name sysname COLLATE database_default NULL,
execution_retry_attempt INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL)
CREATE TABLE #filtered_jobs (job_id UNIQUEIDENTIFIER NOT NULL,
date_created DATETIME NOT NULL,
date_last_modified DATETIME NOT NULL,
current_execution_status INT NULL,
current_execution_step sysname COLLATE database_default NULL,
current_retry_attempt INT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
last_run_outcome INT NOT NULL,
next_run_date INT NULL,
next_run_time INT NULL,
next_run_schedule_id INT NULL,
type INT NOT NULL)
CREATE TABLE #xp_results (job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL, -- BOOL
request_source INT NOT NULL,
request_source_id sysname COLLATE database_default NULL,
running INT NOT NULL, -- BOOL
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)
-- Step 2: Capture job execution information (for local jobs only since that's all SQLServerAgent caches)
SELECT @is_sysadmin = 1
SELECT @job_owner = SUSER_SNAME()
IF ((@@microsoftversion / 0x01000000) >= 8) -- SQL Server 8.0 or greater
INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner, @job_id
ELSE
INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner
INSERT INTO #job_execution_state
SELECT xpr.job_id,
xpr.last_run_date,
xpr.last_run_time,
xpr.job_state,
sjs.step_id,
sjs.step_name,
xpr.current_retry_attempt,
xpr.next_run_date,
xpr.next_run_time,
xpr.next_run_schedule_id
FROM #xp_results xpr
LEFT OUTER JOIN msdb.dbo.sysjobsteps sjs ON ((xpr.job_id = sjs.job_id) AND (xpr.current_step = sjs.step_id)),
msdb.dbo.sysjobs sjv
WHERE (sjv.job_id = xpr.job_id)
-- Step 3: Filter on everything but dates and job_type
IF ((@subsystem IS NULL) AND
(@owner_login_name IS NULL) AND
(@enabled IS NULL) AND
(@category_id IS NULL) AND
(@execution_status IS NULL) AND
(@description IS NULL) AND
(@job_id IS NULL))
BEGIN
-- Optimize for the frequently used case...
INSERT INTO #filtered_jobs
SELECT sjv.job_id,
sjv.date_created,
sjv.date_modified,
ISNULL(jes.execution_job_status, 4), -- Will be NULL if the job is non-local or is not in #job_execution_state (NOTE: 4 = STATE_IDLE)
CASE ISNULL(jes.execution_step_id, 0)
WHEN 0 THEN NULL -- Will be NULL if the job is non-local or is not in #job_execution_state
ELSE CONVERT(NVARCHAR, jes.execution_step_id) + N' (' + jes.execution_step_name + N')'
END,
jes.execution_retry_attempt, -- Will be NULL if the job is non-local or is not in #job_execution_state
0, -- last_run_date placeholder (we'll fix it up in step 3.3)
0, -- last_run_time placeholder (we'll fix it up in step 3.3)
5, -- last_run_outcome placeholder (we'll fix it up in step 3.3 - NOTE: We use 5 just in case there are no jobservers for the job)
jes.next_run_date, -- Will be NULL if the job is non-local or is not in #job_execution_state
jes.next_run_time, -- Will be NULL if the job is non-local or is not in #job_execution_state
jes.next_run_schedule_id, -- Will be NULL if the job is non-local or is not in #job_execution_state
0 -- type placeholder (we'll fix it up in step 3.4)
FROM msdb.dbo.sysjobs sjv
LEFT OUTER JOIN #job_execution_state jes ON (sjv.job_id = jes.job_id)
END
ELSE
BEGIN
INSERT INTO #filtered_jobs
SELECT DISTINCT
sjv.job_id,
sjv.date_created,
sjv.date_modified,
ISNULL(jes.execution_job_status, 4), -- Will be NULL if the job is non-local or is not in #job_execution_state (NOTE: 4 = STATE_IDLE)
CASE ISNULL(jes.execution_step_id, 0)
WHEN 0 THEN NULL -- Will be NULL if the job is non-local or is not in #job_execution_state
ELSE CONVERT(NVARCHAR, jes.execution_step_id) + N' (' + jes.execution_step_name + N')'
END,
jes.execution_retry_attempt, -- Will be NULL if the job is non-local or is not in #job_execution_state
0, -- last_run_date placeholder (we'll fix it up in step 3.3)
0, -- last_run_time placeholder (we'll fix it up in step 3.3)
5, -- last_run_outcome placeholder (we'll fix it up in step 3.3 - NOTE: We use 5 just in case there are no jobservers for the job)
jes.next_run_date, -- Will be NULL if the job is non-local or is not in #job_execution_state
jes.next_run_time, -- Will be NULL if the job is non-local or is not in #job_execution_state
jes.next_run_schedule_id, -- Will be NULL if the job is non-local or is not in #job_execution_state
0 -- type placeholder (we'll fix it up in step 3.4)
FROM msdb.dbo.sysjobs sjv
LEFT OUTER JOIN #job_execution_state jes ON (sjv.job_id = jes.job_id)
LEFT OUTER JOIN msdb.dbo.sysjobsteps sjs ON (sjv.job_id = sjs.job_id)
WHERE ((@subsystem IS NULL) OR (sjs.subsystem = @subsystem))
AND ((@owner_login_name IS NULL) OR (sjv.owner_sid = SUSER_SID(@owner_login_name)))
AND ((@enabled IS NULL) OR (sjv.enabled = @enabled))
AND ((@category_id IS NULL) OR (sjv.category_id = @category_id))
AND ((@execution_status IS NULL) OR ((@execution_status > 0) AND (jes.execution_job_status = @execution_status))
OR ((@execution_status = 0) AND (jes.execution_job_status <> 4) AND (jes.execution_job_status <> 5)))
AND ((@description IS NULL) OR (sjv.description LIKE @description))
AND ((@job_id IS NULL) OR (sjv.job_id = @job_id))
END
-- Step 3.1: Change the execution status of non-local jobs from 'Idle' to 'Unknown'
UPDATE #filtered_jobs
SET current_execution_status = NULL
WHERE (current_execution_status = 4)
AND (job_id IN (SELECT job_id
FROM msdb.dbo.sysjobservers
WHERE (server_id <> 0)))
-- Step 3.2: Check that if the user asked to see idle jobs that we still have some.
-- If we don't have any then the query should return no rows.
IF (@execution_status = 4) AND
(NOT EXISTS (SELECT *
FROM #filtered_jobs
WHERE (current_execution_status = 4)))
BEGIN
TRUNCATE TABLE #filtered_jobs
END
-- Step 3.3: Populate the last run date/time/outcome [this is a little tricky since for
-- multi-server jobs there are multiple last run details in sysjobservers, so
-- we simply choose the most recent].
IF (EXISTS (SELECT *
FROM msdb.dbo.systargetservers))
BEGIN
UPDATE #filtered_jobs
SET last_run_date = sjs.last_run_date,
last_run_time = sjs.last_run_time,
last_run_outcome = sjs.last_run_outcome
FROM #filtered_jobs fj,
msdb.dbo.sysjobservers sjs
WHERE (CONVERT(FLOAT, sjs.last_run_date) * 1000000) + sjs.last_run_time =
(SELECT MAX((CONVERT(FLOAT, last_run_date) * 1000000) + last_run_time)
FROM msdb.dbo.sysjobservers
WHERE (job_id = sjs.job_id))
AND (fj.job_id = sjs.job_id)
END
ELSE
BEGIN
UPDATE #filtered_jobs
SET last_run_date = sjs.last_run_date,
last_run_time = sjs.last_run_time,
last_run_outcome = sjs.last_run_outcome
FROM #filtered_jobs fj,
msdb.dbo.sysjobservers sjs
WHERE (fj.job_id = sjs.job_id)
END
-- Step 3.4 : Set the type of the job to local (1) or multi-server (2)
-- NOTE: If the job has no jobservers then it wil have a type of 0 meaning
-- unknown. This is marginally inconsistent with the behaviour of
-- defaulting the category of a new job to [Uncategorized (Local)], but
-- prevents incompletely defined jobs from erroneously showing up as valid
-- local jobs.
UPDATE #filtered_jobs
SET type = 1 -- LOCAL
FROM #filtered_jobs fj,
msdb.dbo.sysjobservers sjs
WHERE (fj.job_id = sjs.job_id)
AND (server_id = 0)
UPDATE #filtered_jobs
SET type = 2 -- MULTI-SERVER
FROM #filtered_jobs fj,
msdb.dbo.sysjobservers sjs
WHERE (fj.job_id = sjs.job_id)
AND (server_id <> 0)
-- Step 4: Filter on job_type
IF (@job_type IS NOT NULL)
BEGIN
IF (UPPER(@job_type) = 'LOCAL')
DELETE FROM #filtered_jobs
WHERE (type <> 1) -- IE. Delete all the non-local jobs
IF (UPPER(@job_type) = 'MULTI-SERVER')
DELETE FROM #filtered_jobs
WHERE (type <> 2) -- IE. Delete all the non-multi-server jobs
END
-- Step 5: Filter on dates
IF (@date_comparator IS NOT NULL)
BEGIN
IF (@date_created IS NOT NULL)
BEGIN
IF (@date_comparator = '=')
DELETE FROM #filtered_jobs WHERE (date_created <> @date_created)
IF (@date_comparator = '>')
DELETE FROM #filtered_jobs WHERE (date_created <= @date_created)
IF (@date_comparator = '<')
DELETE FROM #filtered_jobs WHERE (date_created >= @date_created)
END
IF (@date_last_modified IS NOT NULL)
BEGIN
IF (@date_comparator = '=')
DELETE FROM #filtered_jobs WHERE (date_last_modified <> @date_last_modified)
IF (@date_comparator = '>')
DELETE FROM #filtered_jobs WHERE (date_last_modified <= @date_last_modified)
IF (@date_comparator = '<')
DELETE FROM #filtered_jobs WHERE (date_last_modified >= @date_last_modified)
END
END
-- Return the result set (NOTE: No filtering occurs here)
Declare @aantaltotaal int
Set @aantaltotaal=0
SELECT
@aantaltotaal=isnull(count(*),0)
FROM #filtered_jobs fj
LEFT OUTER JOIN msdb.dbo.sysjobs sjv ON (fj.job_id = sjv.job_id)
LEFT OUTER JOIN msdb.dbo.sysoperators so1 ON (sjv.notify_email_operator_id = so1.id)
LEFT OUTER JOIN msdb.dbo.sysoperators so2 ON (sjv.notify_netsend_operator_id = so2.id)
LEFT OUTER JOIN msdb.dbo.sysoperators so3 ON (sjv.notify_page_operator_id = so3.id)
LEFT OUTER JOIN msdb.dbo.syscategories sc ON (sjv.category_id = sc.category_id)
-- Return the result set (NOTE: No filtering occurs here)
Declare @aantalfout int
Set @aantalfout=0
SELECT
@aantalfout=isnull(count(*),0)
FROM #filtered_jobs fj
LEFT OUTER JOIN msdb.dbo.sysjobs sjv ON (fj.job_id = sjv.job_id)
LEFT OUTER JOIN msdb.dbo.sysoperators so1 ON (sjv.notify_email_operator_id = so1.id)
LEFT OUTER JOIN msdb.dbo.sysoperators so2 ON (sjv.notify_netsend_operator_id = so2.id)
LEFT OUTER JOIN msdb.dbo.sysoperators so3 ON (sjv.notify_page_operator_id = so3.id)
LEFT OUTER JOIN msdb.dbo.syscategories sc ON (sjv.category_id = sc.category_id)
where
(fj.last_run_time>0 and fj.last_run_outcome<>1)
--print @aantalfout
Declare @message varchar(200)
Declare @ret int
set @ret=0
set @message='No failed SQL Jobs'
If @aantalfout=1
Begin
SELECT
@ret=1, @message='Op ' + sjv.originating_server + ' is *'+ sjv.name + '* gefailed op ' + cast(fj.last_run_date as varchar(8)) + ''
FROM #filtered_jobs fj
LEFT OUTER JOIN msdb.dbo.sysjobs sjv ON (fj.job_id = sjv.job_id)
LEFT OUTER JOIN msdb.dbo.sysoperators so1 ON (sjv.notify_email_operator_id = so1.id)
LEFT OUTER JOIN msdb.dbo.sysoperators so2 ON (sjv.notify_netsend_operator_id = so2.id)
LEFT OUTER JOIN msdb.dbo.sysoperators so3 ON (sjv.notify_page_operator_id = so3.id)
LEFT OUTER JOIN msdb.dbo.syscategories sc ON (sjv.category_id = sc.category_id)
where
fj.last_run_time>0 and fj.last_run_outcome<>1
ORDER BY sjv.job_id
End
If @aantalfout>1
begin
set @ret=1
set @message='Multible Failed SQL jobs, check SQL Agent'
end
If @aantaltotaal=0
begin
set @ret=1
set @message='Not a single SQL job detected, check this check'
end
-- Clean up
DROP TABLE #job_execution_state
DROP TABLE #filtered_jobs
DROP TABLE #xp_results
--SET NOCOUNT OFF
select ret=@ret, message=@message
go
GO
/****** Object: StoredProcedure [dbo].[checksqljobs] Script Date: 03/05/2008 07:54:42 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE Procedure [dbo].[checksqljobs]
As
DECLARE @job_id UNIQUEIDENTIFIER
DECLARE @job_type VARCHAR(12)
DECLARE @owner_login_name sysname
DECLARE @subsystem NVARCHAR(40)
DECLARE @category_id INT
DECLARE @enabled TINYINT
DECLARE @execution_status INT
DECLARE @date_comparator CHAR(1)
DECLARE @date_created DATETIME
DECLARE @date_last_modified DATETIME
DECLARE @description NVARCHAR(512)
DECLARE @is_sysadmin INT
DECLARE @job_owner sysname
SET NOCOUNT ON
-- By 'composite' we mean a combination of sysjobs and xp_sqlagent_enum_jobs data.
-- This proc should only ever be called by sp_help_job, so we don't verify the
-- parameters (sp_help_job has already done this).
-- Step 1: Create intermediate work tables
CREATE TABLE #job_execution_state (job_id UNIQUEIDENTIFIER NOT NULL,
date_started INT NOT NULL,
time_started INT NOT NULL,
execution_job_status INT NOT NULL,
execution_step_id INT NULL,
execution_step_name sysname COLLATE database_default NULL,
execution_retry_attempt INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL)
CREATE TABLE #filtered_jobs (job_id UNIQUEIDENTIFIER NOT NULL,
date_created DATETIME NOT NULL,
date_last_modified DATETIME NOT NULL,
current_execution_status INT NULL,
current_execution_step sysname COLLATE database_default NULL,
current_retry_attempt INT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
last_run_outcome INT NOT NULL,
next_run_date INT NULL,
next_run_time INT NULL,
next_run_schedule_id INT NULL,
type INT NOT NULL)
CREATE TABLE #xp_results (job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL, -- BOOL
request_source INT NOT NULL,
request_source_id sysname COLLATE database_default NULL,
running INT NOT NULL, -- BOOL
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)
-- Step 2: Capture job execution information (for local jobs only since that's all SQLServerAgent caches)
SELECT @is_sysadmin = 1
SELECT @job_owner = SUSER_SNAME()
IF ((@@microsoftversion / 0x01000000) >= 8) -- SQL Server 8.0 or greater
INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner, @job_id
ELSE
INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner
INSERT INTO #job_execution_state
SELECT xpr.job_id,
xpr.last_run_date,
xpr.last_run_time,
xpr.job_state,
sjs.step_id,
sjs.step_name,
xpr.current_retry_attempt,
xpr.next_run_date,
xpr.next_run_time,
xpr.next_run_schedule_id
FROM #xp_results xpr
LEFT OUTER JOIN msdb.dbo.sysjobsteps sjs ON ((xpr.job_id = sjs.job_id) AND (xpr.current_step = sjs.step_id)),
msdb.dbo.sysjobs sjv
WHERE (sjv.job_id = xpr.job_id)
-- Step 3: Filter on everything but dates and job_type
IF ((@subsystem IS NULL) AND
(@owner_login_name IS NULL) AND
(@enabled IS NULL) AND
(@category_id IS NULL) AND
(@execution_status IS NULL) AND
(@description IS NULL) AND
(@job_id IS NULL))
BEGIN
-- Optimize for the frequently used case...
INSERT INTO #filtered_jobs
SELECT sjv.job_id,
sjv.date_created,
sjv.date_modified,
ISNULL(jes.execution_job_status, 4), -- Will be NULL if the job is non-local or is not in #job_execution_state (NOTE: 4 = STATE_IDLE)
CASE ISNULL(jes.execution_step_id, 0)
WHEN 0 THEN NULL -- Will be NULL if the job is non-local or is not in #job_execution_state
ELSE CONVERT(NVARCHAR, jes.execution_step_id) + N' (' + jes.execution_step_name + N')'
END,
jes.execution_retry_attempt, -- Will be NULL if the job is non-local or is not in #job_execution_state
0, -- last_run_date placeholder (we'll fix it up in step 3.3)
0, -- last_run_time placeholder (we'll fix it up in step 3.3)
5, -- last_run_outcome placeholder (we'll fix it up in step 3.3 - NOTE: We use 5 just in case there are no jobservers for the job)
jes.next_run_date, -- Will be NULL if the job is non-local or is not in #job_execution_state
jes.next_run_time, -- Will be NULL if the job is non-local or is not in #job_execution_state
jes.next_run_schedule_id, -- Will be NULL if the job is non-local or is not in #job_execution_state
0 -- type placeholder (we'll fix it up in step 3.4)
FROM msdb.dbo.sysjobs sjv
LEFT OUTER JOIN #job_execution_state jes ON (sjv.job_id = jes.job_id)
END
ELSE
BEGIN
INSERT INTO #filtered_jobs
SELECT DISTINCT
sjv.job_id,
sjv.date_created,
sjv.date_modified,
ISNULL(jes.execution_job_status, 4), -- Will be NULL if the job is non-local or is not in #job_execution_state (NOTE: 4 = STATE_IDLE)
CASE ISNULL(jes.execution_step_id, 0)
WHEN 0 THEN NULL -- Will be NULL if the job is non-local or is not in #job_execution_state
ELSE CONVERT(NVARCHAR, jes.execution_step_id) + N' (' + jes.execution_step_name + N')'
END,
jes.execution_retry_attempt, -- Will be NULL if the job is non-local or is not in #job_execution_state
0, -- last_run_date placeholder (we'll fix it up in step 3.3)
0, -- last_run_time placeholder (we'll fix it up in step 3.3)
5, -- last_run_outcome placeholder (we'll fix it up in step 3.3 - NOTE: We use 5 just in case there are no jobservers for the job)
jes.next_run_date, -- Will be NULL if the job is non-local or is not in #job_execution_state
jes.next_run_time, -- Will be NULL if the job is non-local or is not in #job_execution_state
jes.next_run_schedule_id, -- Will be NULL if the job is non-local or is not in #job_execution_state
0 -- type placeholder (we'll fix it up in step 3.4)
FROM msdb.dbo.sysjobs sjv
LEFT OUTER JOIN #job_execution_state jes ON (sjv.job_id = jes.job_id)
LEFT OUTER JOIN msdb.dbo.sysjobsteps sjs ON (sjv.job_id = sjs.job_id)
WHERE ((@subsystem IS NULL) OR (sjs.subsystem = @subsystem))
AND ((@owner_login_name IS NULL) OR (sjv.owner_sid = SUSER_SID(@owner_login_name)))
AND ((@enabled IS NULL) OR (sjv.enabled = @enabled))
AND ((@category_id IS NULL) OR (sjv.category_id = @category_id))
AND ((@execution_status IS NULL) OR ((@execution_status > 0) AND (jes.execution_job_status = @execution_status))
OR ((@execution_status = 0) AND (jes.execution_job_status <> 4) AND (jes.execution_job_status <> 5)))
AND ((@description IS NULL) OR (sjv.description LIKE @description))
AND ((@job_id IS NULL) OR (sjv.job_id = @job_id))
END
-- Step 3.1: Change the execution status of non-local jobs from 'Idle' to 'Unknown'
UPDATE #filtered_jobs
SET current_execution_status = NULL
WHERE (current_execution_status = 4)
AND (job_id IN (SELECT job_id
FROM msdb.dbo.sysjobservers
WHERE (server_id <> 0)))
-- Step 3.2: Check that if the user asked to see idle jobs that we still have some.
-- If we don't have any then the query should return no rows.
IF (@execution_status = 4) AND
(NOT EXISTS (SELECT *
FROM #filtered_jobs
WHERE (current_execution_status = 4)))
BEGIN
TRUNCATE TABLE #filtered_jobs
END
-- Step 3.3: Populate the last run date/time/outcome [this is a little tricky since for
-- multi-server jobs there are multiple last run details in sysjobservers, so
-- we simply choose the most recent].
IF (EXISTS (SELECT *
FROM msdb.dbo.systargetservers))
BEGIN
UPDATE #filtered_jobs
SET last_run_date = sjs.last_run_date,
last_run_time = sjs.last_run_time,
last_run_outcome = sjs.last_run_outcome
FROM #filtered_jobs fj,
msdb.dbo.sysjobservers sjs
WHERE (CONVERT(FLOAT, sjs.last_run_date) * 1000000) + sjs.last_run_time =
(SELECT MAX((CONVERT(FLOAT, last_run_date) * 1000000) + last_run_time)
FROM msdb.dbo.sysjobservers
WHERE (job_id = sjs.job_id))
AND (fj.job_id = sjs.job_id)
END
ELSE
BEGIN
UPDATE #filtered_jobs
SET last_run_date = sjs.last_run_date,
last_run_time = sjs.last_run_time,
last_run_outcome = sjs.last_run_outcome
FROM #filtered_jobs fj,
msdb.dbo.sysjobservers sjs
WHERE (fj.job_id = sjs.job_id)
END
-- Step 3.4 : Set the type of the job to local (1) or multi-server (2)
-- NOTE: If the job has no jobservers then it wil have a type of 0 meaning
-- unknown. This is marginally inconsistent with the behaviour of
-- defaulting the category of a new job to [Uncategorized (Local)], but
-- prevents incompletely defined jobs from erroneously showing up as valid
-- local jobs.
UPDATE #filtered_jobs
SET type = 1 -- LOCAL
FROM #filtered_jobs fj,
msdb.dbo.sysjobservers sjs
WHERE (fj.job_id = sjs.job_id)
AND (server_id = 0)
UPDATE #filtered_jobs
SET type = 2 -- MULTI-SERVER
FROM #filtered_jobs fj,
msdb.dbo.sysjobservers sjs
WHERE (fj.job_id = sjs.job_id)
AND (server_id <> 0)
-- Step 4: Filter on job_type
IF (@job_type IS NOT NULL)
BEGIN
IF (UPPER(@job_type) = 'LOCAL')
DELETE FROM #filtered_jobs
WHERE (type <> 1) -- IE. Delete all the non-local jobs
IF (UPPER(@job_type) = 'MULTI-SERVER')
DELETE FROM #filtered_jobs
WHERE (type <> 2) -- IE. Delete all the non-multi-server jobs
END
-- Step 5: Filter on dates
IF (@date_comparator IS NOT NULL)
BEGIN
IF (@date_created IS NOT NULL)
BEGIN
IF (@date_comparator = '=')
DELETE FROM #filtered_jobs WHERE (date_created <> @date_created)
IF (@date_comparator = '>')
DELETE FROM #filtered_jobs WHERE (date_created <= @date_created)
IF (@date_comparator = '<')
DELETE FROM #filtered_jobs WHERE (date_created >= @date_created)
END
IF (@date_last_modified IS NOT NULL)
BEGIN
IF (@date_comparator = '=')
DELETE FROM #filtered_jobs WHERE (date_last_modified <> @date_last_modified)
IF (@date_comparator = '>')
DELETE FROM #filtered_jobs WHERE (date_last_modified <= @date_last_modified)
IF (@date_comparator = '<')
DELETE FROM #filtered_jobs WHERE (date_last_modified >= @date_last_modified)
END
END
-- Return the result set (NOTE: No filtering occurs here)
Declare @aantaltotaal int
Set @aantaltotaal=0
SELECT
@aantaltotaal=isnull(count(*),0)
FROM #filtered_jobs fj
LEFT OUTER JOIN msdb.dbo.sysjobs sjv ON (fj.job_id = sjv.job_id)
LEFT OUTER JOIN msdb.dbo.sysoperators so1 ON (sjv.notify_email_operator_id = so1.id)
LEFT OUTER JOIN msdb.dbo.sysoperators so2 ON (sjv.notify_netsend_operator_id = so2.id)
LEFT OUTER JOIN msdb.dbo.sysoperators so3 ON (sjv.notify_page_operator_id = so3.id)
LEFT OUTER JOIN msdb.dbo.syscategories sc ON (sjv.category_id = sc.category_id)
-- Return the result set (NOTE: No filtering occurs here)
Declare @aantalfout int
Set @aantalfout=0
SELECT
@aantalfout=isnull(count(*),0)
FROM #filtered_jobs fj
LEFT OUTER JOIN msdb.dbo.sysjobs sjv ON (fj.job_id = sjv.job_id)
LEFT OUTER JOIN msdb.dbo.sysoperators so1 ON (sjv.notify_email_operator_id = so1.id)
LEFT OUTER JOIN msdb.dbo.sysoperators so2 ON (sjv.notify_netsend_operator_id = so2.id)
LEFT OUTER JOIN msdb.dbo.sysoperators so3 ON (sjv.notify_page_operator_id = so3.id)
LEFT OUTER JOIN msdb.dbo.syscategories sc ON (sjv.category_id = sc.category_id)
where
(fj.last_run_time>0 and fj.last_run_outcome<>1)
--print @aantalfout
Declare @message varchar(200)
Declare @ret int
set @ret=0
set @message='No failed SQL Jobs'
If @aantalfout=1
Begin
SELECT
@ret=1, @message='Op ' + sjv.originating_server + ' is *'+ sjv.name + '* gefailed op ' + cast(fj.last_run_date as varchar(8)) + ''
FROM #filtered_jobs fj
LEFT OUTER JOIN msdb.dbo.sysjobs sjv ON (fj.job_id = sjv.job_id)
LEFT OUTER JOIN msdb.dbo.sysoperators so1 ON (sjv.notify_email_operator_id = so1.id)
LEFT OUTER JOIN msdb.dbo.sysoperators so2 ON (sjv.notify_netsend_operator_id = so2.id)
LEFT OUTER JOIN msdb.dbo.sysoperators so3 ON (sjv.notify_page_operator_id = so3.id)
LEFT OUTER JOIN msdb.dbo.syscategories sc ON (sjv.category_id = sc.category_id)
where
fj.last_run_time>0 and fj.last_run_outcome<>1
ORDER BY sjv.job_id
End
If @aantalfout>1
begin
set @ret=1
set @message='Multible Failed SQL jobs, check SQL Agent'
end
If @aantaltotaal=0
begin
set @ret=1
set @message='Not a single SQL job detected, check this check'
end
-- Clean up
DROP TABLE #job_execution_state
DROP TABLE #filtered_jobs
DROP TABLE #xp_results
--SET NOCOUNT OFF
select ret=@ret, message=@message
go
Abonneren op:
Posts (Atom)