Search string or object used in all databases

USE [master]

GO



CREATE Procedure [dbo].[sp_Find2]

@SearchText1 varchar(100) = ''

, @SearchText2 varchar(100) = ''

, @SearchText3 varchar(100) = ''

, @SearchText4 varchar(100) = ''

, @DBName sysname = Null

-- , @PreviewTextSize int = 200

, @SearchDBsFlag char(1) = 'Y'

, @SearchJobsFlag char(1) = 'Y'

, @SearchSSISFlag char(1) = 'Y'

As

/*

* Test: sp_Find4 'KEYWORD 1' -- Search for 1 keyword across all objects/DBs/SSIS/Jobs

* sp_Find4 'KEYWORD 1', 'KEYWORD2' -- Search for 2 keywords across all objects/DBs/SSIS/Jobs

* sp_Find4 'track', NULL, NULL, NULL, 'Common'

* sp_Find4 'track', NULL, NULL, NULL, 'Common', 'Y', 'N', 'N' --DB Only

* sp_Find4 'track', NULL, NULL, NULL, 'Common', 'N', 'N', 'Y' --SSIS Only

*/

Set Transaction Isolation Level Read Uncommitted;

Set Nocount On;



DECLARE @PreviewTextSize int

SET @PreviewTextSize = 200



Create Table #FoundObject (

DatabaseName sysname

, ObjectName sysname

, ObjectTypeDesc nvarchar(60)

, PreviewText varchar(max))--To show a little bit of the code and other info



Declare @SQL as nvarchar(max);



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

* Database Search

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

If @SearchDBsFlag = 'Y'

Begin

If @DBName Is Null --Loop through all normal user databases

Begin

Declare ObjCursor Cursor Local Fast_Forward For

Select [Name]

From Master.sys.Databases

Where [Name] Not In ('AdventureWorks', 'AdventureWorksDW', 'Distribution', 'Model', 'TempDB')

and state_desc = 'ONLINE'

-- ('AdventureWorks', 'AdventureWorksDW', 'Distribution', 'Master', 'MSDB', 'Model', 'TempDB');



Open ObjCursor;



Fetch Next From ObjCursor Into @DBName;

While @@Fetch_Status = 0

Begin

Select @SQL = '

Use [' + @DBName + ']



Insert Into #FoundObject (DatabaseName, ObjectName, ObjectTypeDesc, PreviewText)

Select Distinct

''' + @DBName + '''

, sch.[Name] + ''.'' + obj.[Name] as ObjectName

, obj.Type_Desc

, Replace(Replace(SubString(mod.Definition, CharIndex(''' + @SearchText1 + ''', mod.Definition) - ' + Cast(@PreviewTextSize / 2 As varchar) + ', ' +

Cast(@PreviewTextSize As varchar) + '), char(13) + char(10), ''''), ''' + @SearchText1 + ''', ''***' + @SearchText1 + '***'')

From sys.objects obj

Inner Join sys.SQL_Modules mod On obj.Object_Id = mod.Object_Id

Inner Join sys.Schemas sch On obj.Schema_Id = sch.Schema_Id

Where mod.Definition Like ''%' + @SearchText1 + '%''

AND mod.Definition Like ''%' + COALESCE(@SearchText2, '') + '%''

AND mod.Definition Like ''%' + COALESCE(@SearchText3, '') + '%''

AND mod.Definition Like ''%' + COALESCE(@SearchText4, '') + '%''';

-- Order By ObjectName';



Exec dbo.sp_executesql @SQL;



Select @SQL = '

Use [' + @DBName + ']



Insert Into #FoundObject ( DatabaseName, ObjectName, ObjectTypeDesc, PreviewText)

Select TABLE_CATALOG, TABLE_SCHEMA+''.''+TABLE_NAME, TABLE_TYPE, ''Table definitions are not set up yet''

From information_schema.tables

Where TABLE_NAME like ''%' + @SearchText1 + '%''

AND TABLE_NAME LIKE ''%' + COALESCE(@SearchText2, '') + '%''

AND TABLE_NAME LIKE ''%' + COALESCE(@SearchText3, '') + '%''

AND TABLE_NAME LIKE ''%' + COALESCE(@SearchText4, '') + '%''';



Exec dbo.sp_executesql @SQL;





Select @SQL = '

Use [' + @DBName + ']



Insert Into #FoundObject ( DatabaseName , ObjectName, ObjectTypeDesc, PreviewText)

Select TABLE_CATALOG, TABLE_SCHEMA+''.''+TABLE_NAME+''.''+COLUMN_NAME, ''COLUMN'', DATA_TYPE + ''('' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(100)) + '')''

From information_schema.columns

Where COLUMN_NAME like ''%' + @SearchText1 + '%''

AND COLUMN_NAME LIKE ''%' + COALESCE(@SearchText2, '') + '%''

AND COLUMN_NAME LIKE ''%' + COALESCE(@SearchText3, '') + '%''

AND COLUMN_NAME LIKE ''%' + COALESCE(@SearchText4, '') + '%''';



Exec dbo.sp_executesql @SQL;





Fetch Next From ObjCursor Into @DBName;

End;



Close ObjCursor;



Deallocate ObjCursor;

End

Else --Only look through given database

Begin

Select @SQL = '

Use [' + @DBName + ']



Insert Into #FoundObject (DatabaseName, ObjectName, ObjectTypeDesc, PreviewText)

Select Distinct

''' + @DBName + '''

, sch.[Name] + ''.'' + obj.[Name] as ObjectName

, obj.Type_Desc

, Replace(Replace(SubString(mod.Definition, CharIndex(''' + @SearchText1 + ''', mod.Definition) - ' + Cast(@PreviewTextSize / 2 As varchar) + ', ' +

Cast(@PreviewTextSize As varchar) + '), char(13) + char(10), ''''), ''' + @SearchText1 + ''', ''***' + @SearchText1 + '***'')

From sys.objects obj

Inner Join sys.SQL_Modules mod On obj.Object_Id = mod.Object_Id

Inner Join sys.Schemas sch On obj.Schema_Id = sch.Schema_Id

Where mod.Definition Like ''%' + @SearchText1 + '%''

AND mod.Definition Like ''%' + COALESCE(@SearchText2, '') + '%''

AND mod.Definition Like ''%' + COALESCE(@SearchText3, '') + '%''

AND mod.Definition Like ''%' + COALESCE(@SearchText4, '') + '%''';



Exec dbo.sp_ExecuteSQL @SQL;

End;



Select 'Database Objects' As SearchType;



Select

DatabaseName

, ObjectName

, ObjectTypeDesc As ObjectType

, PreviewText

From #FoundObject

Order By DatabaseName, ObjectName;

End



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

* Job Search

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

If @SearchJobsFlag = 'Y'

Begin

Select 'Job Steps' As SearchType;





Select j.[Name] As [Job Name], s.Step_Id As [Step #]

, Replace(Replace(SubString(s.Command, CharIndex(@SearchText1, s.Command) - @PreviewTextSize / 2, @PreviewTextSize), char(13) + char(10), ''), @SearchText1, '***' + @SearchText1 + '***') As Command

From MSDB.dbo.sysJobs j

Inner Join MSDB.dbo.sysJobSteps s On j.Job_Id = s.Job_Id

Where s.Command Like '%' + @SearchText1 + '%'

AND s.Command Like '%' + COALESCE(@SearchText2, '') + '%'

AND s.Command Like '%' + COALESCE(@SearchText3, '') + '%'

AND s.Command Like '%' + COALESCE(@SearchText4, '') + '%';



End



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

* SSIS Search
***************************/

If @SearchSSISFlag = 'Y'

Begin

Select 'SSIS Packages' As SearchType;



Select [Name] As [SSIS Name]

, Replace(Replace(SubString(Cast(Cast(PackageData As varbinary(Max)) As varchar(Max)), CharIndex(@SearchText1, Cast(Cast(PackageData As varbinary(Max)) As varchar(Max))) -

@PreviewTextSize / 2, @PreviewTextSize), char(13) + char(10), ''), @SearchText1, '***' + @SearchText1 + '***') As [SSIS XML]

From MSDB.dbo.sysDTSPackages90

Where Cast(Cast(PackageData As varbinary(Max)) As varchar(Max)) Like '%' + @SearchText1 + '%'

AND Cast(Cast(PackageData As varbinary(Max)) As varchar(Max)) Like '%' + COALESCE(@SearchText2, '') + '%'

AND Cast(Cast(PackageData As varbinary(Max)) As varchar(Max)) Like '%' + COALESCE(@SearchText3, '') + '%'

AND Cast(Cast(PackageData As varbinary(Max)) As varchar(Max)) Like '%' + COALESCE(@SearchText4, '') + '%';

End
GO

Trigger to block host ips

-- This will create a trigger to stop host ips to connect sql server




CREATE TRIGGER tr_logon_hostname_blacklist

ON

ALL SERVER WITH EXECUTE AS 'sa'

FOR LOGON

AS

BEGIN

DECLARE @ClientHost nvarchar(max);

SELECT @ClientHost = EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]','nvarchar(max)');

-- ClientHost gives IP except if the connecting to SQL Server from instance machine.

-- Do NOT put '' in this otherwise you will block client access from instance machine.

IF @ClientHost IN ('10.168.178.71','10.168.178.7','10.168.178.8','10.168.178.4','10.168.178.70')

ROLLBACK;

END;





-- To drop the trigger.



DROP TRIGGER tr_logon_hostname_blacklist ON ALL SERVER;

DBCC LOGINFO

Follow the link for better knowledge on log file information
http://www.simple-talk.com/sql/database-administration/sql-server-transaction-log-fragmentation-a-primer/

Deeper Look at DBCC LOGINFO

Run the DBCC LOGINFO and each one representing a VLF

FileId   FileSize      StartOffset      FSeqNo    Status      Parity CreateLSN
-------- ------------- ---------------- ---------- ---------- ------ ---------
2        253952         8192            30        2           64     0
2        253952         262144          0         0           0      0
2        253952         516096          0         0           0      0
2        278528         770048          0         0           0      0

These columns have the following meaning:

• FileID – the FileID number as found in sysfiles
• FileSize – the size of the VLF in bytes
• StartOffset – the start of the VLF in bytes, from the front of the transaction log
• FSeqNo – indicates the order in which transactions have been written to the different VLF files. The VLF with the highest number is the VLF to which log records are currently being written.
• Status – identifies whether or not a VLF contains part of the active log. A value of 2 indicates an active VLF that can't be overwritten.
• Parity – the Parity Value, which can be 0, 64 or 128 (see the Additional Resources section at the end of this article for more information)
• CreateLSN – Identifies the LSN when the VLF was created. A value of zero indicates that the VLF was created when the database was created. If two VLFs have the same number then they were created at the same time, via an auto-grow event.

Find the list of server on Network

Method 1 :

Set SQLCMD mode => Use below query

!!SQLCMD -L

Method 2 :

use below query:

EXEC master..XP_CMDShell 'OSQL -L'

Moving the system Database in SQL Server

Moving the system Database in SQL Server

Below are the simple tips for moving system databases to differnet location.


Master Database =>

right-click on SQL Server in Enterprise Manager (EM) and choose Properties.
click the Startup Parameters

Replace the path for -d and -e parameter with new one

-d New patha\msater.mdf.
-l New patha\msatlog.ldf

Stop SQL Server and move the master data and log files to its new location.
start SQL Server and you have successfully moved your Master



Tempdb Database =>
open query analyzer and run the following query:

use master go
Alter database tempdb modify file (name = tempdev, filename = ‘E:Sqldata empdb.mdf’)
go
Alter database tempdb modify file (name = templog, filename = ‘E:Sqldata emplog.ldf’)
Go

Stop sql server move tempdb files to new location and delete the older one.
Restart sql server


MSDB Database =>

Right-click the SQL-Server name and click properties.
choose your startup parameters.
enter the parameter -T3608.
Restart SQL Server.
detach the database and move them to their appropriate place.
and reattach it

Checkpoint Vs LazyWriter

Difference between Checkpoint and LazyWriter

C: Flush dirty pages to Disk
L: Flush dirty pages to disk.

C: Flush only Data pages to disk
L: Check for available memory and removed Buffer pool (execution plan/compile plan/ Data pages /Memory objects)

C: Default, Occurs approximately every 1 minute
L: Occurs depending upon memory pressure and resource availability

C: Can be managed with sp_confige -recovery interval option
L:It is lazy, Sql server manages by its own.

C: Does not check the memory pressure
L:Monitor the memory pressure and try maintain the available free memory.

C: crash recovery process will be fast to read log as data file is updated.
L: No role in recovery

C: Occurs for any DDL statement
L: Occurs per requirement

C: Occurs before Backup/Detach command
L: Occurs per requirement

C: Depends upon the configuration setting, we can control.
L: Works on Least recent used pages and removed unused plans first, no user control.

C: for simple recovery it flush the tlog file after 70% full.
L: No effect on recovery model.

C: can manually /Forcefully run command “Checkpoint”
L: No command for Lazy Writer

C: Very Less performance impact
L: No performance impact

Checkpoint can be run manullay using command "Checkpoint":

Find log space and disk utilization

DECLARE @dbname VARCHAR(200),@sql VARCHAR(8000)
SET @sql = '' SET @dbname = ''

IF OBJECT_ID('tempdb..#T_Drive') IS NOT NULL DROP TABLE #T_Drive
IF OBJECT_ID('tempdb..#T_LogSpace') IS NOT NULL DROP TABLE #T_LogSpace
IF OBJECT_ID('tempdb..#T_DBInfo') IS NOT NULL DROP TABLE #T_DBInfo
IF OBJECT_ID('tempdb..#T_FileSpace') IS NOT NULL DROP TABLE #T_FileSpace
IF OBJECT_ID('tempdb..#T_Database') IS NOT NULL DROP TABLE #T_Database

CREATE TABLE #T_Drive([DriveName] VARCHAR(5) PRIMARY KEY,[FreeDriveSpace] BIGINT)

INSERT INTO #T_Drive EXEC master..xp_fixeddrives

CREATE TABLE #T_LogSpace ([DBName] VARCHAR(200) NOT NULL PRIMARY KEY,
[LogSize] MONEY NOT NULL,[LogPercentUsed] MONEY NOT NULL,[LogStatus] INT NOT NULL)

SELECT @sql = 'DBCC SQLPERF (LOGSPACE) WITH NO_INFOMSGS'

INSERT INTO #T_LogSpace EXEC(@sql)

CREATE TABLE #T_DBInfo (
[DBName] VARCHAR(200),[FileLogicalName] VARCHAR(200),[FileID] INT NOT NULL,[Filename] VARCHAR(250) NOT NULL,
[Filegroup] VARCHAR(100) NOT NULL,[FileCurrentSize] BIGINT NOT NULL,[FileMaxSize] VARCHAR(50) NOT NULL,
[FileGrowth] VARCHAR(50) NOT NULL,[FileUsage] VARCHAR(50) NOT NULL,[FileGrowthSize] BIGINT NOT NULL)

CREATE TABLE #T_Database ( [DBName] VARCHAR(200) PRIMARY KEY)

INSERT INTO #T_Database
SELECT DBName = LTRIM(RTRIM(name)) FROM master.dbo.sysdatabases
WHERE category IN ('0', '1','16')AND DATABASEPROPERTYEX(name, 'status') = 'ONLINE' ORDER BY name

CREATE TABLE #T_FileSpace ( [DBName] VARCHAR(200) NULL,[Fileid] INT NOT NULL,
[FileGroup] INT NOT NULL,[TotalExtents] MONEY NOT NULL,[UsedExtents] MONEY NOT NULL,
[FileLogicalName] sysname NOT NULL,[Filename] VARCHAR(1000) NOT NULL)

SELECT @dbname = MIN(dbname) FROM #T_Database

WHILE @dbname IS NOT NULL
BEGIN
SET @sql = 'USE ' + @dbname + '
INSERT INTO #T_DBInfo (
[DBName],[FileLogicalName],[FileID],[Filename],[Filegroup],[FileCurrentSize],
[FileMaxSize],[FileGrowth],[FileUsage],[FileGrowthSize])
SELECT DBName = ''' + @dbname + ''',FileLogicalName = SF.name,
FileID = SF.fileid, Filename = SF.filename, Filegroup = ISNULL(filegroup_name(SF.groupid),''''),
FileCurrentSize = (SF.size * 8)/1024, FileMaxSize =CASE SF.maxsize WHEN -1 THEN N''Unlimited''
ELSE CONVERT(VARCHAR(15), (CAST(SF.maxsize AS BIGINT) * 8)/1024) + N'' MB'' END,
FileGrowth = (case SF.status & 0x100000 when 0x100000 then
convert(varchar(3), SF.growth) + N'' %''
else
convert(varchar(15), ((CAST(SF.growth AS BIGINT) * 8)/1024)) + N'' MB'' end),
FileUsage = (case WHEN SF.status & 0x40 = 0x40 then ''Log'' else ''Data'' end),
FileGrowthSize = CASE SF.status & 0x100000 WHEN 0x100000 THEN
((((CAST(SF.size AS BIGINT) * 8)/1024)* SF.growth)/100) + ((CAST(SF.size AS BIGINT) * 8)/1024)
ELSE
((CAST(SF.size AS BIGINT) * 8)/1024) + ((CAST(SF.growth AS BIGINT) * 8)/1024)
END
FROM sysfiles SF
WHERE filename like ''%.ldf''
ORDER BY SF.fileid'

EXEC(@sql)

SET @sql = 'USE ' + @dbname + ' DBCC SHOWFILESTATS WITH NO_INFOMSGS'

INSERT INTO #T_FileSpace ([Fileid],[FileGroup],[TotalExtents],[UsedExtents],[FileLogicalName],[Filename])
EXEC (@sql)

UPDATE #T_FileSpace SET [DBName] = @dbname WHERE ISNULL([DBName],'') = ''

SELECT @dbname = MIN(dbname) FROM #T_Database WHERE dbname > @dbname
END

SELECT 'DBName' = DFI.DBName,
'FileLogicalName' = DFI.FileLogicalName,
'Filename' = DFI.[Filename],
'FileMBSize' = DFI.FileCurrentSize,
'FileGrowth' = DFI.FileGrowth,
'FileMBGrowth' = DFI.FileGrowthSize,
'DriveName' = TD.DriveName,
'DriveMBEmpty' = TD.FreeDriveSpace,
'FileMBUsed' = CAST(ISNULL(((FS.UsedExtents * 64.00) / 1024), LSP.LogSize *(LSP.LogPercentUsed/100)) AS BIGINT),
'FileMBEmpty' = DFI.FileCurrentSize - CAST(ISNULL(((FS.UsedExtents * 64.00) / 1024), LSP.LogSize *(LSP.LogPercentUsed/100)) AS BIGINT),
'LogSpaceUsed' = LSP.LogPercentUsed
FROM #T_DBInfo DFI
LEFT OUTER JOIN #T_Drive TD
ON LEFT(LTRIM(RTRIM(DFI.[FileName])),1) = LTRIM(RTRIM(TD.DriveName))
LEFT OUTER JOIN #T_FileSpace FS
ON LTRIM(RTRIM(FS.[Filename])) = LTRIM(RTRIM(DFI.[Filename]))
LEFT OUTER JOIN #T_LogSpace LSP
ON LtRIM(RTRIM(LSP.DBName)) = LTRIM(RTRIM(DFI.DBName))
ORDER BY 1