Updated: 2016-11-07 Part 2: settings inside SQL Server When the server is all good and installed, it’s time to go through and set up a bunch of server settings. First of all – dedicated admin connections. This is used as a “get out of jail for free”-card for a DBA if all the schedulers are […]
Very useful to transfer logins with their passwords intact. USE master GO IF OBJECT_ID (‘sp_hexadecimal’) IS NOT NULL DROP PROCEDURE sp_hexadecimal GO CREATE PROCEDURE sp_hexadecimal @binvalue varbinary(256), @hexvalue varchar (514) OUTPUT AS DECLARE @charvalue varchar (514) DECLARE @i int DECLARE @length int DECLARE @hexstring char(16) SELECT @charvalue = ‘0x’ SELECT @i = 1 SELECT @length […]
SELECT ‘Starting’ –sets @@ROWCOUNT WHILE @@ROWCOUNT ; 0 DELETE TOP (xxx) MyTable
SELECT t.NAME AS TableName, i.name as indexName, sum(p.rows) as RowCounts, sum(a.total_pages) as TotalPages, sum(a.used_pages) as UsedPages, sum(a.data_pages) as DataPages, (sum(a.total_pages) * 8) / 1024 as TotalSpaceMB, (sum(a.used_pages) * 8) / 1024 as UsedSpaceMB, (sum(a.data_pages) * 8) / 1024 as DataSpaceMB FROM sys.tables t INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id INNER JOIN sys.partitions p […]
CREATE PROCEDURE wait_stats_monitor AS SET NOCOUNT ON DECLARE @latest datetime, @now datetime SELECT @now = getdate() IF object_id(‘waitstats’) IS NULL BEGIN CREATE TABLE waitstats ( sample_time datetime NOT NULL, wait_type nvarchar(60) NOT NULL, sample_length_sec int NULL, delta_tasks_count bigint NULL, delta_wait_time bigint […]
if exists (select * from sys.objects where object_id = object_id(N'[dbo].[get_statements_from_waiter_list]’) and OBJECTPROPERTY(object_id, N’IsProcedure’) = 1) drop procedure [dbo].[get_statements_from_waiter_list] go create proc get_statements_from_waiter_list (@wait_type nvarchar(60)=NULL) as select r.wait_type ,r.wait_time ,SUBSTRING(qt.text,r.statement_start_offset/2, (case when r.statement_end_offset = -1 then len(convert(nvarchar(max), qt.text)) * 2 else r.statement_end_offset end -r.statement_start_offset)/2) as query_text ,qt.dbid, dbname=db_name(qt.dbid) ,qt.objectid ,r.sql_handle ,(SELECT query_plan FROM sys.dm_exec_query_plan (r.plan_handle)) as […]
Today I took a look at a clients 10g Oracle RAC environment where one node apparently had started misbehaving quite some time ago. The scenario was that both nodes were up, cluster ready services was up, nodeapps were up, ASM was up but the instance was down. Starting from the top with the database alert […]
Whenever I set up an SQL Server I usually do the same things. I’ve collected a list of best practice settings over the years (and most often put them in a script, as most of my friends are scripts). Throughout a few blog posts I’ll go over the settings I do use and discuss a […]
We have all heard (and gnashed our teeth over) that in order to get a proper automatic failover environment for Oracle (i.e Data Guard) we need Enterprise Edition. Oracle Enterprise Edition (or as Brent Ozar so succinctly put it: Expensive Edition) is hideously expensive, especially if you’re only ever going to use the Data Guard […]
So here we are. Again. The blog is live. Hopefully better, stronger and tastier. You be the judge 🙂