Skip to content

Clive Roberts

SQL DBA

Menu
  • Server Setup Scritps
  • mssql
  • Scripts
  • MS Notification Services

Our Blog

Access a SQL server without a password.

November 9, 2017
| No Comments
| mssql

Access a SQL server without a password.   After installing SQL server on a machine, it happens that you connect or disconnect that machine to domain. When you do this, the administrator account can no longer access the database engine. The below steps allow you to regain access to the SQL server   Stop SQL…

Read More »

Posted in mssql

CODE: kill SQL processes

March 30, 2017
| No Comments
| mssql

–For MS SQL Server 2012 and above USE [master]; DECLARE @kill varchar(8000) = ”; SELECT @kill = @kill + ‘kill ‘ + CONVERT(varchar(5), session_id) + ‘;’ FROM sys.dm_exec_sessions WHERE database_id = db_id(‘MyDB’) EXEC(@kill); –For MS SQL Server 2000, 2005, 2008 USE master; DECLARE @kill varchar(8000); SET @kill = ”; SELECT @kill = @kill + ‘kill…

Read More »

Posted in mssql

Sp_who3

October 31, 2016
| No Comments
| Server Setup Scritps

USE [master] GO /****** Object: StoredProcedure [dbo].[sp_who3] Script Date: 24/06/2016 15:29:28 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[sp_who3] AS ——————————————————————————————————– — mimics sp_who2 with query text — SPID , Status , Login , HostName , BlkBy , DBName , Command , CPUTime , DiskIO , LastBatch , ProgramName , SPID…

Read More »

Posted in Server Setup Scritps

Trace_Status

October 31, 2016
| No Comments
| Server Setup Scritps

DBCC TRACESTATUS (-1 ); ————————————– —–TRACEON ————————————– DBCC TRACEON (1117, -1 ); DBCC TRACEON (1118, -1 ); DBCC TRACEON (1222, -1 ); DBCC TRACEON (3226, -1 ); DBCC TRACEON (4199, -1 ); ————————————– —–TRACEOFF ————————————– –DBCC TRACEOFF — (1117, -1 — ); –DBCC TRACEOFF — (1118, -1 — ); –DBCC TRACEOFF — (1222, -1 —…

Read More »

Posted in Server Setup Scritps

Job_Integrity_Check

October 31, 2016
| No Comments
| Server Setup Scritps

USE [msdb]; GO /****** Object: Job [USER Integrity Check main] Script Date: 19/04/2016 14:06:30 ******/ BEGIN TRANSACTION; DECLARE @ReturnCode INT; SELECT @ReturnCode = 0; /****** Object: JobCategory [Database Maintenance] Script Date: 19/04/2016 14:06:30 ******/ IF NOT EXISTS ( SELECT name FROM msdb.dbo.syscategories WHERE name = N’Database Maintenance’ AND category_class = 1 ) BEGIN EXEC @ReturnCode…

Read More »

Posted in Server Setup Scritps

Database_Integrity_Check

October 31, 2016
| No Comments
| Server Setup Scritps

USE [master]; GO /****** Object: StoredProcedure [dbo].[DatabaseIntegrityCheck] Script Date: 19/02/2016 09:58:06 ******/ –DROP PROCEDURE [dbo].[DatabaseIntegrityCheck]; –GO /****** Object: StoredProcedure [dbo].[DatabaseIntegrityCheck] Script Date: 19/02/2016 09:58:06 ******/ SET ANSI_NULLS ON; GO SET QUOTED_IDENTIFIER ON; GO CREATE PROCEDURE [dbo].[DatabaseIntegrityCheck] @Databases NVARCHAR(MAX), @CheckCommands NVARCHAR(MAX)= ‘CHECKDB’, @PhysicalOnly NVARCHAR(MAX)= ‘N’, @NoIndex NVARCHAR(MAX)= ‘N’, @ExtendedLogicalChecks NVARCHAR(MAX)= ‘N’, @TabLock NVARCHAR(MAX)= ‘N’, @FileGroups NVARCHAR(MAX)=…

Read More »

Posted in Server Setup Scritps

Index_job

October 31, 2016
| No Comments
| Server Setup Scritps

USE [msdb]; GO /****** Object: Job [USER INDEXES] Script Date: 18/02/2016 16:54:31 ******/ BEGIN TRANSACTION; DECLARE @ReturnCode INT; SELECT @ReturnCode = 0; /****** Object: JobCategory [[Uncategorized (Local)]] Script Date: 18/02/2016 16:54:31 ******/ IF NOT EXISTS ( SELECT name FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]’ AND category_class = 1 ) BEGIN EXEC @ReturnCode = msdb.dbo.sp_add_category…

Read More »

Posted in Server Setup Scritps

Create_Backup_Objects_and_Jobs

October 31, 2016
| No Comments
| Server Setup Scritps

/* Back up the master, msdb, and model databases to ensure that you can roll back to a previous configuration if problems occur. master: This database hosts system-level information. Back up this database regularly. model: You need to back up the template database only after you have made modifications. Because changes to the template database…

Read More »

Posted in Server Setup Scritps

Index_Optimize

October 31, 2016
| No Comments
| Server Setup Scritps

USE [MASTER]; GO SET ANSI_NULLS ON; GO SET QUOTED_IDENTIFIER ON; GO CREATE PROCEDURE dbo.CommandExecute @Command NVARCHAR(MAX), @CommandType NVARCHAR(MAX), @Mode INT, @Comment NVARCHAR(MAX)= NULL, @DatabaseName NVARCHAR(MAX)= NULL, @SchemaName NVARCHAR(MAX)= NULL, @ObjectName NVARCHAR(MAX)= NULL, @ObjectType NVARCHAR(MAX)= NULL, @IndexName NVARCHAR(MAX)= NULL, @IndexType INT= NULL, @StatisticsName NVARCHAR(MAX)= NULL, @PartitionNumber INT= NULL, @ExtendedInfo XML= NULL, @LogToTable NVARCHAR(MAX), @Execute NVARCHAR(MAX) AS…

Read More »

Posted in Server Setup Scritps

Create_Operator_and_Standard_Alerts

October 31, 2016
| No Comments
| Server Setup Scritps

USE [msdb]; GO IF ( SELECT 1 FROM [dbo].[sysoperators] WHERE name = ‘DBA_Alerts’ ) IS NULL BEGIN EXEC msdb.dbo.sp_add_operator @name = N’DBA_Alerts’, @enabled = 1, @pager_days = 0, @email_address = N’DBA_Alerts@dwf.co.uk’; END; ELSE BEGIN PRINT ‘Operator Already Exists’; END; GO USE [msdb]; GO IF ( SELECT 1 FROM [dbo].[sysalerts] WHERE name = ‘Corruption Alert 823…

Read More »

Posted in Server Setup Scritps

Setup_SQL_Server_Agent_Alerting_System

October 31, 2016
| No Comments
| Server Setup Scritps

USE [msdb]; GO EXEC master.dbo.xp_instance_regwrite N’HKEY_LOCAL_MACHINE’, N’SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent’, N’UseDatabaseMail’, N’REG_DWORD’, 1; GO EXEC master.dbo.xp_instance_regwrite N’HKEY_LOCAL_MACHINE’, N’SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent’, N’DatabaseMailProfile’, N’REG_SZ’, N’SQL_Alerting’; GO EXEC msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder = 1; GO –RESTART SQL SERVER AGENT

Read More »

Posted in Server Setup Scritps

Setup_TempDB_Files

October 31, 2016
| No Comments
| Server Setup Scritps

–Add TempDB Files to number of prcessors (up to 8) DECLARE @dbname VARCHAR(200); DECLARE @datafilename VARCHAR(200); DECLARE @logfilename VARCHAR(200); DECLARE @cpucount SMALLINT; DECLARE @datafilepath VARCHAR(200); DECLARE @logilepath VARCHAR(200); DECLARE @datasizeMB INT; DECLARE @logsizeMB INT; DECLARE @datafilegrowthMB INT; DECLARE @logfilegrowthMB INT; DECLARE @sqlstring VARCHAR(8000); DECLARE @loop SMALLINT; SELECT @dbname = ‘tempdb’; SELECT @datasizeMB = 1024; SELECT…

Read More »

Posted in Server Setup Scritps

Config_Options

October 31, 2016
| No Comments
| Server Setup Scritps

— Focus on these settings: — backup compression default (should be 1 in most cases) — clr enabled (only enable if it is needed) — cost threshold for parallelism (depends on your workload) 50 is good — lightweight pooling (should be zero) — max degree of parallelism (depends on your workload) 4-8 — max server…

Read More »

Posted in Server Setup Scritps

Cycle_Error_Logs

October 31, 2016
| No Comments
| Server Setup Scritps

USE [msdb]; GO /****** Object: Job [(00:00) Maintenance – CycleErrorLogs] Script Date: 06/10/2014 11:19:43 ******/ BEGIN TRANSACTION; DECLARE @ReturnCode INT; SELECT @ReturnCode = 0; /****** Object: JobCategory [DBA_Maintenance] Script Date: 06/10/2014 11:19:43 ******/ IF NOT EXISTS ( SELECT name FROM msdb.dbo.syscategories WHERE name = N’DBA_Maintenance’ AND category_class = 1 ) BEGIN EXEC @ReturnCode = msdb.dbo.sp_add_category…

Read More »

Posted in Server Setup Scritps

Configure_MSDB

October 31, 2016
| No Comments
| Server Setup Scritps

USE [master]; GO — setup ALTER DATABASE [msdb] MODIFY FILE (NAME = N’MSDBData’, SIZE = 51200 KB, FILEGROWTH = 204800 KB ); GO ALTER DATABASE [msdb] MODIFY FILE (NAME = N’MSDBLog’, SIZE = 51200 KB, MAXSIZE = UNLIMITED, FILEGROWTH = 204800 KB ); GO

Read More »

Posted in Server Setup Scritps

CODE:using TempDB currently

July 29, 2016
| No Comments
| mssql

SELECT st.dbid AS QueryExecutionContextDBID, DB_NAME(st.dbid) AS QueryExecContextDBNAME, st.objectid AS ModuleObjectId, SUBSTRING(st.TEXT, dmv_er.statement_start_offset/2 + 1, (CASE WHEN dmv_er.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX),st.TEXT)) * 2 ELSE dmv_er.statement_end_offset END – dmv_er.statement_start_offset)/2) AS Query_Text, dmv_tsu.session_id , dmv_tsu.request_id, dmv_tsu.exec_context_id, (dmv_tsu.user_objects_alloc_page_count – dmv_tsu.user_objects_dealloc_page_count) AS OutStanding_user_objects_page_counts, (dmv_tsu.internal_objects_alloc_page_count – dmv_tsu.internal_objects_dealloc_page_count) AS OutStanding_internal_objects_page_counts, dmv_er.start_time, dmv_er.command, dmv_er.open_transaction_count, dmv_er.percent_complete, dmv_er.estimated_completion_time, dmv_er.cpu_time, dmv_er.total_elapsed_time, dmv_er.reads,dmv_er.writes, dmv_er.logical_reads, dmv_er.granted_query_memory, dmv_es.HOST_NAME,…

Read More »

Posted in mssql

CODE: connected to SQL server

May 1, 2016
| No Comments
| mssql

SELECT DB_NAME(dbid) AS [Database Name], COUNT(dbid) AS [No Of Connections], loginame AS [Login Name] FROM sys.sysprocesses WHERE dbid > 0 GROUP BY dbid, loginame;

Read More »

Posted in mssql

CODE: VLF counts

April 19, 2016
| No Comments
| mssql

— 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 TABLE #VLFInfo ( FileID INT, FileSize BIGINT, StartOffset BIGINT, FSeqNo BIGINT, [Status] BIGINT, Parity BIGINT,…

Read More »

Posted in mssql

CODE: move backups

April 11, 2016
| No Comments
| mssql

declare @cmd nvarchar(200) set @cmd = ‘move \\server\UserDB\*.bak \\server\UserDB\Archive’ exec xp_cmdshell @cmd

Read More »

Posted in mssql

CODE: VLF SQL 2012+

March 19, 2016
| No Comments
| mssql

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 CREATE TABLE #log_info ( RecoveryUnitId TINYINT, fileid TINYINT, file_size BIGINT, start_offset BIGINT, FSeqNo INT, [status]…

Read More »

Posted in mssql

Error message when you open SQL Server Configuration Manager in SQL Server

February 19, 2016
| No Comments
| mssql

Error message when you open SQL Server Configuration Manager in SQL Server: “Cannot connect to WMI provider. You do not have permission or the server is unreachable” https://support.microsoft.com/en-us/kb/956013  

Read More »

Posted in mssql

The Disk Cleanup executable file cleanmgr.exe

November 18, 2015
| No Comments
| Scripts

Summary The Disk Cleanup executable file cleanmgr.exe and the associated Disk Cleanup button are not present in Windows Server® 2008 or in Windows Server® 2008 R2 by default. Cause This is by design, as the Disk Cleanup button is part of the Desktop Experience feature. In order to have Disk Cleanup button appear on a disk’s Properties dialog,…

Read More »

Posted in Scripts

Fill Factor

November 11, 2015
| No Comments
| mssql

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 Updated Less Often – Set Fill Factor at 95. These tables are more or less…

Read More »

Posted in mssql

CODE: find missing indexes

October 26, 2015
| No Comments
| mssql

— 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 INDEX [IDX_’ + OBJECT_NAME(dm_mid.OBJECT_ID, dm_mid.database_id) + ‘_’ + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns, ”), ‘, ‘, ‘_’), ‘[‘, ”),…

Read More »

Posted in mssql

CODE: number of cores on a SQL server

September 18, 2015
| No Comments
| mssql

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 ) * (( cpu_count – hyperthread_ratio ) / ( cpu_count / hyperthread_ratio )) END AS TotalNumberOfCores,…

Read More »

Posted in mssql

CODE: DBCC CHECKDB

August 6, 2015
| No Comments
| mssql

Classic way to check the database is alright. DBCC CHECKDB(‘DatabaseName’) WITH NO_INFOMSGS, ALL_ERRORMSGS

Read More »

Posted in mssql

CODE: SQL error 1222

July 15, 2015
| No Comments
| mssql

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 is 69 then execute the command as sp_who2 69 sp_lock 69 Now , you can…

Read More »

Posted in mssql

CODE: Agent job details

July 1, 2015
| No Comments
| mssql

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) + ‘ at ‘ + REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT(‘000000’ + CAST(active_start_time as varchar(10)), 6), 3,…

Read More »

Posted in mssql

CODE:Unused Index Script

July 1, 2015
| No Comments
| mssql

– 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 ‘ + QUOTENAME(i.name) + ‘ ON ‘ + QUOTENAME(s.name) + ‘.’ + QUOTENAME(OBJECT_NAME(dm_ius.OBJECT_ID)) AS ‘drop statement’…

Read More »

Posted in mssql

CODE:waiting_tasks

July 1, 2015
| No Comments
| mssql

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

Read More »

Posted in mssql

CODE: counter

July 1, 2015
| No Comments
| mssql

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 collection_time INTO #perf_counters_init
FROM sys.dm_os_performance_counters
WHERE ( OBJECT_NAME = @CounterPrefix + ‘Access Methods’ AND counter_name = ‘Full…

Read More »

Posted in mssql

CODE: execution count

July 1, 2015
| No Comments
| mssql

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 DESC

Read More »

Posted in mssql

CODE: wait times

July 1, 2015
| No Comments
| mssql

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 * ( wait_time_ms – signal_wait_time_ms ) / SUM(wait_time_ms) OVER ( ) AS percent_total_resource_waits FROM sys.dm_os_wait_stats WHERE…

Read More »

Posted in mssql

To back up the tail of the transaction log

June 11, 2015
| No Comments
| mssql

To back up the tail of the transaction log 1 After connecting to the appropriate instance of the Microsoft SQL Server Database Engine, in Object Explorer, click the server name to expand the server tree. 2 Expand Databases, and, depending on the database, either select a user database or expand System Databases and select a system database….

Read More »

Posted in mssql

Types of Replication

May 19, 2015
| No Comments
| mssql

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.

Read More »

Posted in mssql

Index type

May 19, 2015
| No Comments
| mssql

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 of the rows, based on their clustered index key values. Non clustered A non clustered…

Read More »

Posted in mssql

Database Engine Stored Procedures (Transact-SQL)

May 8, 2015
| No Comments
| mssql

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 sp_addumpdevice sp_helpdevice sp_altermessage sp_helpextendedproc sp_attach_db sp_helpfile sp_attach_single_file_db sp_helpfilegroup sp_autostats sp_helpindex sp_bindefault sp_helplanguage sp_bindrule sp_helpserver sp_bindsession…

Read More »

Posted in mssql

SET TRANSACTION ISOLATION LEVEL (Transact-SQL)

May 7, 2015
| No Comments
| mssql

READ UNCOMMITTED Specifies that statements can read rows that have been modified by other transactions but not yet committed. Transactions running at the READ UNCOMMITTED level do not issue shared locks to prevent other transactions from modifying data read by the current transaction. READ UNCOMMITTED transactions are also not blocked by exclusive locks that would…

Read More »

Posted in mssql

CODE:CONCAT Function

May 1, 2015
| No Comments
| mssql

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 more neatly. For example, the below script and screenshot shows usage of the “+” operator…

Read More »

Posted in mssql

CODE: STUFF Function

May 1, 2015
| No Comments
| mssql

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 insert a string value into another string value or replace a part of one string…

Read More »

Posted in mssql

CODE: Tables without clustered indexes defined

April 15, 2015
| No Comments
| mssql

To analyze further, the following query can be used to find out which indexes don’t have clustered indexes defined: WITH CTE_1 AS ( SELECT db_name() as dbname, o.name as tablename, (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = i.index_id AND i.object_id = p.object_id) as number_of_rows FROM sys.indexes i INNER JOIN sys.objects o ON i.object_id =…

Read More »

Posted in mssql

Blocking, Timeouts and Deadlocks

April 7, 2015
| No Comments
| mssql

These three issues are all related, yet very different. When we deal with reports of problems, it’s essential to clearly identify what is actually happening. Sometimes, we need clarification by asking about the symptoms, instead of accepting what the user says without delving any deeper. The root cause of a problem may not even be…

Read More »

Posted in mssql

CODE: SET XACT_ABORT

March 30, 2015
| No Comments
| mssql

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 other code , but some how it took quite long time and with in one…

Read More »

Posted in mssql

Locking hint

March 7, 2015
| No Comments
| mssql

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 issue shared locks and do not honor exclusive locks. When this option is in effect,…

Read More »

Posted in mssql

CODE: Errors 823, 824, and 825

February 5, 2015
| No Comments
| mssql

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 msdb.dbo.sp_add_alert @name = N’824 – Page Error’, @message_id = 824, @severity = 0, @enabled =…

Read More »

Posted in mssql

DBCC commands

January 13, 2015
| No Comments
| mssql

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 drop or delete a damaged database. However, this command is no longer available with Microsoft…

Read More »

Posted in mssql

CODE: calculate number of transactions on SQL Server

December 4, 2014
| No Comments
| mssql

As we use PERFMON – Performance Monitor to identify real time happening on SQL Server, Here using a DMV “sys.dm_os_performance_counters” may be used to calculate number of transactions on SQL Server as DECLARE @Counter bigint SELECT @Counter = cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = ‘Transactions/sec’ AND object_name=‘SQLServer:Databases’ AND instance_name =‘Test’   – Your DB Name – Wait for…

Read More »

Posted in mssql

CODE: Call a function

November 29, 2014
| No Comments
| mssql

DECLARE @ret XML; EXEC @ret = [ufn_Common_Layout_function] @pLayout=@Layoutfull,@pValues=@Data SELECT @ret  AS [result]

Read More »

Posted in mssql

CODE: databse,details

October 10, 2014
| No Comments
| mssql

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 = mf.database_id WHERE d.database_id > 4 — Skip system databases GROUP BY d.name ) AS…

Read More »

Posted in mssql

CODE: replication

September 10, 2014
| No Comments
| mssql

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];  

Read More »

Posted in mssql

CODE: find jobs on a server

August 10, 2014
| No Comments
| mssql

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) > GETDATE() – 2) ORDER BY sj.name

Read More »

Posted in mssql

CODE:Check Backup or Restore progress

July 10, 2014
| No Comments
| mssql

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] FROM sys.dm_exec_requests AS r WHERE r.command IN (‘RESTORE DATABASE’,’BACKUP DATABASE’)

Read More »

Posted in mssql

MS SQL pivit table

June 9, 2014
| No Comments
| mssql

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

Read More »

Posted in mssql

Visual studio: How to remove projects from the recent projects list.

May 18, 2014
| No Comments
| mssql

Visual  studio: How to remove projects from the recent projects list. Open the registry by clicking Start (Start > Run) and type RegEdit > (enter) The Registry Editor will open. Drill down the following path… HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\9.0\ProjectMRUList… This is where you recent projects are stored in Visual Studio 2008. (HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\8.0\ProjectMRUList for Visual Studio 2008).

Read More »

Posted in mssql

MS SQL : MS replication_objects

April 19, 2014
| No Comments
| mssql

  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’

Read More »

Posted in mssql

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

March 10, 2014
| No Comments
| mssql

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’  

Read More »

Posted in mssql

CODE: Audit who Dropped the Database

February 15, 2014
| No Comments
| mssql

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 = ‘DATABASE’ ), ADD EVENT sqlserver.object_deleted( 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 = ‘DATABASE’ )…

Read More »

Posted in mssql

CODE: last_value

January 15, 2014
| No Comments
| mssql

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

Read More »

Posted in mssql

CODE: Parse XML to table

December 15, 2013
| No Comments
| mssql

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, [parentid] INT NULL, [id] INT NULL, PRIMARY KEY (RowNum)) IF OBJECT_ID(‘tempdb..#NodeList’) IS NOT NULL DROP…

Read More »

Posted in mssql

CODE: Search a string in a text with number of occurrences

November 15, 2013
| No Comments
| mssql

IF EXISTS (SELECT * FROM sys.objects WHERE type = ‘P’ AND name = ‘Usp_findstring’) DROP PROCEDURE Usp_findstring GO CREATE PROC Usp_findstring (@INPUTSTRING VARCHAR(max), @SEARCHSTRING VARCHAR(500)) AS BEGIN DECLARE @OCCURENCES AS INT, @POSITION AS INT, @RETURNVALUE INT SET @OCCURENCES = 0 SET @POSITION = 0 WHILE @POSITION < Len(@INPUTSTRING) BEGIN IF Charindex(@SEARCHSTRING, @INPUTSTRING, @POSITION) > 0…

Read More »

Posted in mssql

CODE: Ping update

August 9, 2013
| No Comments
| mssql

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 COUNT(*)AS blscount FROM dbo.keywords_domains where URLip isnull) BEGIN SELECT top 1 @web=replace([url],’http://’,”),@webs=replace([url],’http://’,”) FROM keywords_domains WHERE…

Read More »

Posted in mssql

CODE: Delete Temporary Internet files

July 10, 2013
| No Comments
| Scripts

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 = objWSH.ExpandEnvironmentStrings(“%USERPROFILE%”) sProfileRoot=objFSO.GetFolder(sProfile).ParentFolder.Path set objWSH=nothing ‘ Main Code   set objProfileFolder=objFSO.GetFolder(sProfileRoot) for each objFolder in objProfileFolder.SubFolders…

Read More »

Posted in Scripts

CODE: ipconfig /all

June 24, 2013
| No Comments
| Scripts

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 and releases those IP addresses for use by other applications. ‘ipconfig /release” can be used…

Read More »

Posted in Scripts

CODE: WScript loop

April 5, 2013
| No Comments
| Scripts

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

Read More »

Posted in Scripts

CODE: Calculating the Difference (Duration) Between Two DATETIMES

March 15, 2013
| No Comments
| mssql

— “DT” is an abbreviation for “DATETIME”, not “DATE” DECLARE @StartDT DATETIME ,@EndDT DATETIME ; SELECT @StartDT = ‘2000-01-01 10:30:50.780’ ,@EndDT = ‘2000-01-02 12:34:56.789’ ; –===== Display the dates and the duration SELECT StartDT = @StartDT ,EndDT = @EndDT ,Duration = @EndDT-@StartDT ;

Read More »

Posted in mssql

CODE: get ip address

February 22, 2013
| No Comments
| mssql

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 VARCHAR(15) set nocounton        set @ip =null    set @strServerIP =@web SELECT @strCmd =‘ping ‘+ @strServerIP Insert…

Read More »

Posted in mssql

REF.How to Fix SQL Server Database Error 3707?

January 19, 2013
| No Comments
| mssql

How to Fix SQL Server Database Error 3707? 1. ALTER Database leads SET Emergency; GO 2. ALTER Database leads Set Single_User GO 3. DBCC CHECKDB (leads, REPAIR_ALLOW_DATA_LOSS) With ALL_ERRORMSGS;   GO 4. ALTER Database leads set multi_user GO

Read More »

Posted in mssql

Code:sends to Notification to send email

December 26, 2012
| No Comments
| MS Notification Services

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 — ============================================= CREATE PROCEDURE [dbo].[sp_PingServer_failed_sendemail_now] — Add the parameters for the stored procedure here @customers_domain…

Read More »

Posted in MS Notification Services

Code:Ping from a stored procedure

August 18, 2012
| No Comments
| mssql

EXEC @result=Master..xp_cmdShell @strCmd

Read More »

Posted in mssql

CODE: MS SQL TRUNCATE LOGS

June 28, 2012
| No Comments
| mssql

backup log Leads with TRUNCATE_ONLY dbcc shrinkdatabase (Leads , TRUNCATEONLY)

Read More »

Posted in mssql

CODE:MS SQL SHRINKDATABASE

May 28, 2012
| No Comments
| mssql

DBCC SHRINKDATABASE (N’Leads’, 0,TRUNCATEONLY)

Read More »

Posted in mssql

CODE: MS SQL Express backup

April 27, 2012
| No Comments
| mssql

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

Read More »

Posted in mssql

CODE: MSSQL convert an IP into a IP number

March 3, 2012
| No Comments
| mssql

(16777216 * CONVERT(float, PARSENAME(URLip, 4))

Read More »

Posted in mssql

CODE: MSSQL devide up an IP

February 3, 2012
| No Comments
| mssql

PARSENAME(URLip, 4)

Read More »

Posted in mssql

CODE:nearest match

January 27, 2012
| No Comments
| mssql

— 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 it found? If so, then print it and skip the rest of the code IF…

Read More »

Posted in mssql

CODE: Enable the Xp_CmdShell

October 21, 2011
| No Comments
| mssql

EXEC sp_configure ‘xp_cmdshell’, 1

Read More »

Posted in mssql

Code:Output a file from a stored procedures in MS SQL

September 19, 2011
| No Comments
| mssql

How to output a file from a stored procedures in MS SQL, this is how I did it a few years ago exec master..xp_cmdShell ‘bcp “SELECT  url.loc, url.publication_date, url.keywords  FROM ICONews..vw_googlenews url For XML Auto,Elements”  queryout  C:InetpubwwwrootwebsiteSQLgoogle.xml -c -t, -T -S IP address’ Link: http://www.sqlteam.com/article/exporting-data-programatically-with-bcp-and-xp_cmdshell

Read More »

Posted in mssql

CODE: change the IP with script

August 19, 2011
| No Comments
| Scripts

oShell.run “cmd /K CD C:\ & ipconfig/flushdns” oShell.run “cmd /K CD C:\ & Netsh interface ip set address name=””Local Area Connection”” source=static addr=IP mask=255.255.252.0 gateway=IP gwmetric=1″

Read More »

Posted in Scripts

CODE:script to run a php page

July 7, 2011
| No Comments
| Scripts

Set WshShell = WScript.CreateObject(“WScript.Shell”)

Read More »

Posted in Scripts

CODE: find duplicate urls

June 26, 2011
| No Comments
| mssql

HAVING (COUNT(*) > 1)

Read More »

Posted in mssql

CODE: pull out sub domains

May 26, 2011
| No Comments
| mssql

(PATINDEX(‘%.%’, REPLACE(REPLACE(url, ‘www.’, ”), RIGHT(url, 7), ”)) > 1)

Read More »

Posted in mssql

CODE: clean urls

April 26, 2011
| No Comments
| mssql

{ 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

Read More »

Posted in mssql

CODE: to kill a task

March 6, 2011
| No Comments
| Scripts

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 better!

Read More »

Posted in Scripts

CODE .vbs to close a browser window

January 3, 2011
| No Comments
| Scripts

CODE .vbs to close a browser window

Read More »

Posted in Scripts

Config. Notification Service Instance

December 6, 2010
| No Comments
| MS Notification Services

Full document on, how to setup Notification services Opened SQL Server Management Studio and right-click the Notification Services object in the Object Explorer Click on New Notification Service Instance In configuration file click browse. Within that panel, location of the ICF, Select the NSLimeICF.xml file and press open Software engineers do the job better!

Read More »

Posted in MS Notification Services

CODE to: connect to Ms SQL, search google, and scrape sponsored links.

September 16, 2010
| No Comments
| mssql

CODE to: connect to Ms SQL, search google, and scrape sponsored links.

Read More »

Posted in mssql

MS SQL SP Variables

August 4, 2010
| No Comments
| mssql

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!

Read More »

Posted in mssql

CODE: XML

July 7, 2010
| No Comments
| mssql

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]

Read More »

Posted in mssql

MS SQL CASE statement

May 4, 2010
| No Comments
| mssql

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!

Read More »

Posted in mssql

Code: MS SQL To find the start of the month

March 2, 2010
| No Comments
| mssql

MS SQL To find the start of the month This code: DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0) was excuted on 2/3/2010 at 16:11 the result: 01/03/2010 00:00:00 Software engineers do the job better!

Read More »

Posted in mssql

Notification Service setup

February 27, 2010
| No Comments
| MS Notification Services

To start you should have MS SQL 2005 and Notification Services installed and IIS with SMPT Open files and copy and rename sub NSPing-files to c:NSPing open NSLime.ssmssqlproj with MS SQL 2005 Server Management Studio and connect to your SQL server Select and right click on NS, select New Notification Services Instance.. Browse to c:NSPing…

Read More »

Posted in MS Notification Services

CODE:Ping website and send an email if the site is down

January 22, 2010
| No Comments
| MS Notification Services

If your website goes down and you have pay per click, you still pay for every click. If you know your website is down you can disable PPC until your website is back up. But who will watch your site 24/7. I have over 10,000 domains,  so I wrote this program using Microsoft’s Notification Services,…

Read More »

Posted in MS Notification Services

Categories

Sidebar

  • November 2017 (1)
  • March 2017 (1)
  • October 2016 (13)
  • July 2016 (1)
  • May 2016 (1)
  • April 2016 (2)
  • March 2016 (1)
  • February 2016 (1)
  • November 2015 (2)
  • October 2015 (1)
  • September 2015 (1)
  • August 2015 (1)
  • July 2015 (7)
  • June 2015 (1)
  • May 2015 (6)
  • April 2015 (2)
  • March 2015 (2)
  • February 2015 (1)
  • January 2015 (1)
  • December 2014 (1)
  • November 2014 (1)
  • October 2014 (1)
  • September 2014 (1)
  • August 2014 (1)
  • July 2014 (1)
  • June 2014 (1)
  • May 2014 (1)
  • April 2014 (1)
  • March 2014 (1)
  • February 2014 (1)
  • January 2014 (1)
  • December 2013 (1)
  • November 2013 (1)
  • August 2013 (1)
  • July 2013 (1)
  • June 2013 (1)
  • April 2013 (1)
  • March 2013 (1)
  • February 2013 (1)
  • January 2013 (1)
  • December 2012 (1)
  • August 2012 (1)
  • June 2012 (1)
  • May 2012 (1)
  • April 2012 (1)
  • March 2012 (1)
  • February 2012 (1)
  • January 2012 (1)
  • October 2011 (1)
  • September 2011 (1)
  • August 2011 (1)
  • July 2011 (1)
  • June 2011 (1)
  • May 2011 (1)
  • April 2011 (1)
  • March 2011 (1)
  • January 2011 (1)
  • December 2010 (1)
  • September 2010 (1)
  • August 2010 (1)
  • July 2010 (1)
  • May 2010 (1)
  • March 2010 (1)
  • February 2010 (1)
  • January 2010 (1)
  • Server Setup Scritps
  • mssql
  • Scripts
  • MS Notification Services

Precious Lite 2022 | All Rights Reserved. Precious Lite theme by Flythemes