CODE: VLF counts

— High VLF counts can affect write performance — and they can make full database restores and crash recovery take much longer — Try to keep your VLF counts under 200 in most cases (depending on log file size) CREATE … Continue reading

CODE: VLF SQL 2012+

DECLARE @query VARCHAR(1000), @dbname VARCHAR(1000), @count INT; SET NOCOUNT ON; DECLARE csr CURSOR FAST_FORWARD READ_ONLY FOR SELECT name FROM sys.databases; CREATE TABLE ##loginfo ( dbname VARCHAR(100), num_of_rows INT ); OPEN csr; FETCH NEXT FROM csr INTO @dbname; WHILE(@@fetch_status -1) BEGIN … Continue reading

Fill Factor

Static Tables – Set Fill Factor at 100 (or default server fill factor), As these tables are never changing, keeping the Fill Factor at 100 is the best option. They conserve the space, and also there is no fragmentation. Tables … Continue reading

CODE: find missing indexes

— 25 missing indexes that will improve performance the most SELECT TOP 25 dm_mid.database_id AS DatabaseID, DB_NAME(dm_mid.database_id) AS [Database], dm_migs.avg_user_impact * ( dm_migs.user_seeks + dm_migs.user_scans ) AS [Avg Estimated Impact], dm_migs.last_user_seek AS [Last Usage Time], OBJECT_NAME(dm_mid.OBJECT_ID, dm_mid.database_id) AS [Table], ‘CREATE … Continue reading

CODE: number of cores on a SQL server

SELECT( cpu_count / hyperthread_ratio ) AS NumberOfPhysicalCPUs, CASE WHEN hyperthread_ratio = cpu_count THEN cpu_count ELSE(( cpu_count – hyperthread_ratio ) / ( cpu_count / hyperthread_ratio )) END AS NumberOfCoresInEachCPU, CASE WHEN hyperthread_ratio = cpu_count THEN cpu_count ELSE( cpu_count / hyperthread_ratio ) … Continue reading

CODE: SQL error 1222

In the SQL Server Management Studio, to find out details of the active transaction, execute following command DBCC opentran() You will get the detail of the active transaction, then from the SPID of the active transaction. For example, if SPID … Continue reading

CODE: Agent job details

SELECT SJ.name as job_name , SJ.enabled as is_job_enabled , SS.enabled as is_schedule_enabled /*, ISNULL(SJ.description, ”) as job_desc*/ , SS.name as schedule_name , CASE freq_type WHEN 1 THEN ‘Occurs on ‘ + STUFF(RIGHT(active_start_date, 4), 3,0, ‘/’) + ‘/’ + LEFT(active_start_date, 4) … Continue reading

CODE:Unused Index Script

– Unused Index Script SELECT TOP 25 o.name AS ObjectName , i.name AS IndexName , i.index_id AS IndexID , dm_ius.user_seeks AS UserSeek , dm_ius.user_scans AS UserScans , dm_ius.user_lookups AS UserLookups , dm_ius.user_updates AS UserUpdates , p.TableRows , ‘DROP INDEX ‘ … Continue reading

CODE:waiting_tasks

SELECT DISTINCT wt.wait_type FROM sys.dm_os_waiting_tasks AS wt JOIN sys.dm_exec_sessions AS s ON wt.session_id = s.session_id WHERE s.is_user_process = 0

CODE: counter

DECLARE @CounterPrefix NVARCHAR(30) SET @CounterPrefix = CASE WHEN @@SERVICENAME = ‘MSSQLSERVER’ THEN ‘SQLServer:’
ELSE ‘MSSQL$’ + @@SERVICENAME + ‘:’ END ;
– Capture the first counter set SELECT CAST(1 AS INT) AS collection_instance , [OBJECT_NAME] , counter_name ,
instance_name ,
cntr_value ,
cntr_type ,
CURRENT_TIMESTAMP AS … Continue reading

CODE: execution count

SELECT TOP 10 execution_count , statement_start_offset AS stmt_start_offset ,
sql_handle ,
plan_handle ,
total_logical_reads / execution_count AS avg_logical_reads , total_logical_writes / execution_count AS avg_logical_writes , total_physical_reads / execution_count AS avg_physical_reads , t.text FROM sys.dm_exec_query_stats AS s
CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS t ORDER BY avg_physical_reads … Continue reading

CODE: wait times

SELECT TOP 10 wait_type , max_wait_time_ms wait_time_ms , signal_wait_time_ms , wait_time_ms – signal_wait_time_ms AS resource_wait_time_ms , 100.0 * wait_time_ms / SUM(wait_time_ms) OVER ( ) AS percent_total_waits , 100.0 * signal_wait_time_ms / SUM(signal_wait_time_ms) OVER ( ) AS percent_total_signal_waits , 100.0 * … Continue reading

Types of Replication

Types of Replication Microsoft SQL Server provides the following types of replication for use in distributed applications: Transactional replication. For more information, see Transactional Replication. Merge replication. For more information, see Merge Replication. Snapshot replication. For more information, see Snapshot Replication.

Index type

Index type Description Clustered A clustered index sorts and stores the data rows of the table or view in order based on the clustered index key. The clustered index is implemented as a B-tree index structure that supports fast retrieval … Continue reading

Database Engine Stored Procedures (Transact-SQL)

Database Engine Stored Procedures (Transact-SQL) SQL Server 2014 QL Server supports the following system stored procedures that are used for general maintenance of an instance of SQL Server. sp_add_data_file_recover_suspect_db sp_executesql sp_add_log_file_recover_suspect_db sp_getapplock sp_addextendedproc sp_getbindtoken sp_addextendedproperty sp_help sp_addmessage sp_helpconstraint sp_addtype sp_helpdb … Continue reading

CODE:CONCAT Function

CONCAT Function Prior to SQL Server 2012, we used to use the “+” operator to combine or concatenate two or more string values but starting with SQL Server 2012, we can use CONCAT function to perform this type of operation … Continue reading

CODE: STUFF Function

STUFF Function We looked at using the CONCAT function to concatenate or combine two or more string values. With this function, string values are appended at the end of the prior string but there are times when you want to … Continue reading

CODE: SET XACT_ABORT

SET XACT_ABORT  is helpful in many ways. example: suppose you are working on a stored procedure which is doing a complex data manipulation. You though this query will run with in 1 minute when you run it from .NET or … Continue reading

Locking hint

Locking hint Description HOLDLOCK Hold a shared lock until completion of the transaction instead of releasing the lock as soon as the required table, row, or data page is no longer required. HOLDLOCK is equivalent to SERIALIZABLE. NOLOCK Do not … Continue reading

CODE: Errors 823, 824, and 825

Errors 823,824, and 825 To do this, run the following command: USE msdb GO EXEC msdb.dbo.sp_add_alert @name = N’823 – Read/Write Failure’,     @message_id = 823, @severity = 0, @enabled = 1, @delay_between_responses = 0, @include_event_description_in = 1 GO EXEC … Continue reading

DBCC commands

DBCC DBREINDEX This statement is used to recreate the indexes for a particular table. This statement rebuilds indexes in a single step. It also assigns fresh pages to reduce internal and external fragmentation. DBCC DBREPAIR This statement is used to … Continue reading

CODE: databse,details

MS SQL 2005+ SELECT @@servername AS instance_name ,a.name AS database_name, c.data_Size_MBs AS data_size , b.log_Size_MBs AS log_size ,a.total_Size_MBs AS total_size FROM ( SELECT d.name, ROUND(SUM(mf.size) * 8 / 1024, 0) total_Size_MBs FROM sys.master_files mf INNER JOIN sys.databases d ON d.database_id … Continue reading

CODE: replication

SELECT P.[publication] AS [Publication Name] ,A.[publisher_db] AS [Database Name] ,A.[article] AS [Article Name] ,A.[source_owner] AS [Schema] ,A.[source_object] AS [Table] FROM [distribution].[dbo].[MSarticles] AS A INNER JOIN [distribution].[dbo].[MSpublications] AS P ON (A.[publication_id] = P.[publication_id]) ORDER BY P.[publication], A.[article];  

CODE: find jobs on a server

SELECT        @@SERVERNAME AS server_name, sjs.last_run_outcome,sj.enabled, sj.name, sjs.step_id, sjs.step_name, sjs.database_name, CAST(CAST(sjs.last_run_date AS varchar(10)) AS datetime) AS failed_date FROM            msdb.dbo.sysjobsteps AS sjs INNER JOIN msdb.dbo.sysjobs AS sj ON sjs.job_id = sj.job_id WHERE       (sjs.last_run_date <> 0) AND (CAST(CAST(sjs.last_run_date AS varchar(10)) AS datetime) > … Continue reading

CODE:Check Backup or Restore progress

SELECT r.session_id, r.command, CONVERT(NUMERIC(6,2),r.percent_complete)AS [Percent Complete], CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time], CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min], CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min], CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours], CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2, CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END) FROM sys.dm_exec_sql_text(sql_handle))) AS [SQL Command] … Continue reading

MS SQL pivit table

select pvt.abc,pvt.def,pvt.ghi from ( SELECT ConnectionName, Connection.ConnectionString FROM dbo.Global_Applications_ConnectionString AS Connection ) AS SourceTable pivot ( min(ConnectionString) for ConnectionName in (abc,def,ghi) ) as pvt

MS SQL : MS replication_objects

  select article, * from dbo.MSreplication_objects where article like ‘Table_name’   select sp.name, ss.srvname, sa.name from sysarticles sa join syssubscriptions ss on ss.artid = sa.artid join syspublications sp on sa.pubid = sp.pubid where sa.name like ‘table_name’

CODE:List tables with no primary key – SQL Server 2000

SELECT DB_NAME() AS [Database], T.TABLE_NAME AS [Tables without Primary Keys] FROM INFORMATION_SCHEMA.TABLES AS T WHERE NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC WHERE CONSTRAINT_TYPE = ‘PRIMARY KEY’ AND T.TABLE_NAME = TC.TABLE_NAME) AND T.TABLE_TYPE = ‘BASE TABLE’  

CODE: Audit who Dropped the Database

USE master; GO — Create the Event Session IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name=’DBDeletedCreated’) DROP EVENT SESSION DBDeletedCreated ON SERVER; GO CREATE EVENT SESSION DBDeletedCreated ON SERVER ADD EVENT sqlserver.object_created ( SET collect_database_name = (1) ACTION(sqlserver.nt_username,sqlserver.session_id,sqlserver.client_hostname,sqlserver.client_app_name,sqlserver.sql_text) WHERE object_type = … Continue reading

CODE: last_value

SELECT CustomerID , SalesOrderID , CAST(OrderDate AS DATE) AS OrderDate , TotalDue , LAST_VALUE(TotalDue) OVER ( PARTITION BY CustomerID ORDER BY SalesOrderID ) AS TD FROM Sales.SalesOrderHeader ORDER BY CustomerID

CODE: Parse XML to table

CREATE PROCEDURE dbo.ParseXMLtoTable @strXML AS XML ,@rootnode NVARCHAR(255) AS BEGIN SET NOCOUNT ON DECLARE @strText AS NVARCHAR(MAX) ,@idoc INT ,@id INT ,@parentid INT IF OBJECT_ID(‘tempdb..#ChildList’) IS NOT NULL DROP TABLE #ChildList CREATE TABLE #ChildList ( [RowNum] INT IDENTITY(1,1) NOT NULL, … Continue reading

CODE: Ping update

USE [Leads] GO SET ANSI_NULLSON GO SET QUOTED_IDENTIFIERON GO CREATE PROCEDURE [dbo].[sp_PingBLSWebsites] AS DECLARE @counter int DECLARE @result VARCHAR(15) DECLARE @ip varchar(15) DECLARE @web varchar(100) DECLARE @webs varchar(100) set @counter = 0 — ping all the IP’s WHILE @counter <(SELECT … Continue reading

CODE: Delete Temporary Internet files

Option Explicit ‘ Variables Dim objWSH, sProfile, objFolder Dim objFSO, sProfileRoot, objProfileFolder Dim sTemp, sWindows ‘ Object Initialisation   set objFSO=CreateObject(“Scripting.FileSystemObject”)   ‘ Get user profile root folder set objWSH    = CreateObject(“WScript.Shell”) sTemp = objWSH.ExpandEnvironmentStrings(“%TEMP%”) sWindows = objWSH.ExpandEnvironmentStrings(“%WINDIR%”) sProfile = … Continue reading

CODE: ipconfig /all

ipconfig /all   This option displays the same IP addressing information for each adapter as the default option. Additionally, it displays DNS and WINS settings for each adapter.  ipconfig /release This option terminates any active TCP/IP connections on all network adapters … Continue reading

CODE: WScript loop

Dim oShell Set oShell = WScript.CreateObject (“WScript.Shell” Dim counter counter = 0 Do counter = counter + 1 oShell.Run “C:\php\php.exe d:\data\webapps\php\crawler_all.php”, 9 WScript.Sleep 10000 Loop Until counter = 18

CODE: get ip address

USE  [leads] GO /****** Object:  StoredProcedure [dbo].[sp_get_ip_address]    Script Date: 02/22/2013 17:08:45 ******/ SET ANSI_NULLSON GO SET QUOTED_IDENTIFIERON GO CREATE Procedure [dbo].[sp_get_ip_address](@web varchar(100), @ip varchar(15) out) as begin Declare @ipLine varchar(200) Declare @pos int DECLARE @strCmd VARCHAR(60) DECLARE @strServerIP VARCHAR(100) DECLARE @result … Continue reading

Code:sends to Notification to send email

USE [PingService] GO /****** Object:  StoredProcedure [dbo].[sp_PingServer_failed_sendemail_now]    Script Date: 02/26/2010 16:33:00 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO — ============================================= — Author:  Clive Roberts — Create date: 30 november 2007 — Description: sends infomation to notification services to send email … Continue reading

CODE: MS SQL Express backup

backup.sql …. BACKUP DATABASE [AdaptiveCR] TO DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\AdaptiveCR.bak’ WITH NOFORMAT, NOINIT, NAME = N’AdaptiveCR-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO MF20bak.cmd…. sqlcmd -S ICOWK077\AMCR -i C:\backup.sql Scheduled Tasks to call MF20bak.cmd

CODE:nearest match

— Declare variables to use DECLARE @x int DECLARE @Price money DECLARE @LENGTH int DECLARE @prefix1 varchar(100) DECLARE @Match varchar (100) — Test for supplied value SET @Price = (select price from table1 where breakout_no LIKE @prefix ) — Was … Continue reading

CODE: clean urls

{ fn LCASE((CASE WHEN CHARINDEX(‘/’, url) = 0 THEN ‘http://www.’ + REPLACE(REPLACE(url, ‘www3.’, ”), ‘www.’, ”)
ELSE CASE WHEN (url) LIKE ‘www’ THEN ” ELSE ‘http://www.’ + REPLACE(REPLACE(REPLACE(LEFT(url, CHARINDEX(‘/’, url)), ‘www3.’, ”), ‘www.’, ”), ‘/’,
”) END END)) } AS websiteurl Continue reading

CODE: to kill a task

strCommand = “taskkill /F /IM php.exe” WshShell.Run strCommand, 0, TRUE WScript.Sleep 100 ‘bit of an over kill but it makes sure its dead WshShell.Run strCommand, 0, TRUE WScript.Sleep 100 WshShell.Run strCommand, 0, TRUE WScript.Sleep 100 Software engineers do the job … Continue reading

MS SQL SP Variables

DECLARE @enteredOn  datetime; — Sun IF datepart(dw, getdate()) = 1 Set @enteredOn =getdate()-7 (CONVERT(datetime, CONVERT(varchar, SOEB.ASK_salesproccomp, 103), 103) >= CONVERT(datetime, CONVERT(varchar, @enteredOn, 103), 103)) Software engineers do the job better!

CODE: XML

DECLARE @Datafull XML DECLARE @Layoutfull XML SET @Datafull = dbo.ufn_Common_Layout_DataCells_AsXML(@Layoutfull, 24125, 1, 0, 0)   SET @Layoutfull = dbo.ufn_Common_Layout_ByDealerAndItemFromCache_AsXML_ex(5,5030, 658)   select @Datafull AS [values]   select @Layoutfull AS [layout] DECLARE @ret XML;     EXEC @ret = [ufn_Common_Layout_InsertValues] @pLayout=@Layoutfull,@pValues=@Datafull   select @ret  AS [result]

MS SQL CASE statement

CASE WHEN DATEDIFF(mm, NPB.CreatedOn, soeb.ASK_ContractEndDate) > 1 THEN DATEDIFF(mm,NPB.CreatedOn, soeb.ASK_ContractEndDate) ELSE 0 END Software engineers do the job better!