Free ebook - Troubleshooting SQL Server: A Guide for the Accidental DBA

Wait for this ebook ( "Troubleshooting SQL Server: A Guide for the Accidental DBA") from Jonathan Kehayias for few weeks and I receive newsletter from Simple-Talk today!
Quote from Simple Talk

"
•Understand how to approach troubleshooting and the essential tools you need.
•Explore the areas where problems regularly arise and the tools you can use to diagnose them.
•Get practical solutions to remove the root causes of issues, rather than "papering over the cracks".
"

You can download the ebook from here
You should subscribe newsletter from Simple-Talk

SQL Denali Resource

Free Ebook
Introducing Microsoft SQL Server Code Name “Denali” (DRAFT Preview)


Free Resource download from Microsoft.com
Microsoft® SQL Server® code name 'Denali', Community Technology Preview 3 (CTP 3) Product Guide
Download the datasheets, white papers, technical presentations, demonstrations, and links to videos that will help you evaluate Microsoft® SQL Server® code name 'Denali.'

*Added 7/Feb/2012
SQL Server Virtual Lab
http://www.microsoft.com/sqlserver/en/us/learning-center/virtual-labs.aspx?WT.mc_id=soc-n-[TR]-loc-[Services]-[farukc]

replication issue 'sp_repldone/sp_replcounters'

Summary:- SQL server out of diskspace.
- This cause by replication issue. Replication fail and space in transaction log not release.
-Fix the replication issue with change log reader agent property and restart replication log reader .
value change for log reader agent :
Query Timeout (3600) and ReadBatchSize (100).
-Replication resumed and Transaction log size clear.

Detail:
Checking
1. SQL error log
- Replication-Replication Transaction-Log Reader Subsystem: agent servername-dbname-4 scheduled for retry. The process could not execute 'sp_repldone/sp_replcounters' on 'servername'.

2. Replication monitor


3. Transaction grow very huge, even with backup transaction, the content can't clear

How to solve:
1. From replication monitor->My Publishers->Servername->Right Click ->Agent Profiles->Select log reader agent






2. Select new agent profile and press OK

3. GO to Job Activity Monitor, restart this job servername-database-4 under Repl-log_reader category

4. GO back to Replication monitor, and you will see



5. 5Before we choose the new agent profiler, “The process is running and waiting for a response from the server” run 6 time and will show error
Agent servername-database-4' is retrying after an error. 185 retries attempted. See agent job history in the Jobs folder for more details.

6. For new log, we see this records more than 6 time ““The process is running and waiting for a response from the server””



7. After log reader finish scan transaction log , replicate start.
8.After replication successful and transaction log start backup, free space of transaction log is resumed
9. Transaction log can be shrink now and free space of the server resumed.


Reference:Reference

SQL Fill Factor

SQL Fill Factor
Learn about SQL Fill Factor, and come out the summary.
Correct me if I wrong on SQL Fill Factor

- Fill factor setting define how full the page should be fill when index is created.

- Use fill factor to prevent frequent page split for high update/insert operation.

- Fill factor setting Will not take affect if table no has data when we define fill factor on table.

- If table has data when index created/rebuilt, Create/rebuilt index operation will redistribute data in leaf page based on the fill factor define.

- Low fill factor will slower data selection operation as data span across more page compare to high fill factor.

- Low fill factor will use more disk space as well.

- For table with high insert/update operation, high fill factor will cause page split and operation will be slow.

- For data will be added to the end of the table, fill factor should be default 0. Example index with identity column.

Reference
Fill Factor, Microsoft, http://msdn.microsoft.com/en-us/library/ms177459.aspx

Fill Factor, Microsoft, http://msdn.microsoft.com/en-us/library/aa933139%28v=sql.80%29.aspx

SQL Table no have index key

I use the below script to search
1. SQL table without clustered index
2. SQL table without any index

-- this only show table without clustered index
SELECT
sys.tables.name,
sys.indexes.name
FROM sys.tables
left join sys.indexes
ON sys.tables.object_id = sys.indexes.object_id
WHERE
isnull(sys.indexes.name,'')=''
and sys.tables.name = 'tablename'
ORDER BY
sys.tables.name

--this will show table without any index
SELECT
a.name,
FROM sys.tables a
left join sys.indexes b
ON a.object_id = b.object_id
WHERE
isnull(b.name,'')=''
--and sys.tables.name = 'PackageServerPathLink'
and not exists (select 1 from sys.indexes c where isnull(name,'')<>''
and c.object_id=b.object_id)
ORDER BY
a.name

Troubleshoot on timeout query

We have one request to trace on timeout issue on applicaition.
I setup profiler to trace which query has timeout issue


Error and Warnings
- Attention


Stored Procedures
- RPC: Starting
- SP: Starting
- SP: StmtStarting

TSQL
- SQL: BatchStarting
- SQL: StmtStarting


Setting sample table and data
Create table member3
(member_no int,
lastName varchar(20),
firstName varchar(20));
insert into member3 values (1,'tan','tanlast'),(100,'lee','leelast');

Setting timeout environment
begin tran
update member3
set lastname='me'
where member_no=1

Open a second Microsoft SQL Server Management Studio
In connect to server box, Click on Option and change Execution time-out from 0 (unlimited) to 2 seconds
Press connect



In Query Window, type this,

select * from member3
where member_no=100

After run for 2 second, SQL will give the below message

Msg -2, Level 11, State 0, Line 0
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

From SQL Profiler, look for EventClass=Attention which highlight with red color
You can do this to find “Attention”

CTRL+F, In “Find what” column, keying “Attention”



After get the EventClass with “Attention”, look for the SPID. In this example, SPID for “Attention” = 58
Look backward for SPID=58, you will know the above query is face timeout



In a real production environment, lot of the query is running and we difficult to find the backward query. We can do in this way.

Save the trace log in trace file
Open the trace file that you save just now

Filter the SPID from File-> Properties -> Events Selection -> Column Filter -> SPID -> Equals -> Keying in 58 and press ok, ok




The SQL profiler will only show trace with SPID 58, which easy us to search which query cause timeout.

SQL Free Learning Resource

SQL Server 2008 Microsoft Certified Master (MCM) Readiness Videos
Total 70 Video post by Microsoft.
Topic include : Database Structures, Database Structures - Demo Video, New Database Structures in SQL Server 2008, New Database Structures in SQL Server 2008 - Demo Video, Data File Internals and Maintenance. .and more. Please visit Microsoft Site to look for more

You can download in the below format:
Video: WMV | 3GP | iPod | Zune | MP4 | WMV (ZIP) | PSP
Audio: AAC | WMA | MP3 | MP4



SQL Server 2008 R2 Update for Developers Training Kit (January 2011 Update)
This update include how to build Microsoft Business Intelligence Solution with SQL 2008 R2"

Summary of what include in download package:
31 presentations (PowerPoint slides, videos and transcripts included)
27 demos (installer scripts, videos and transcripts included)
12 hands on labs (installer scripts included)


SQL Stairway From SQLServercentral.com
Just wonder how to start the below topic ?
Database Design
Stairway to Integration Services
Stairway to MDX
Stairway to Server-side Tracing
Stairway to SQL Server Agent
Stairway to SQL Server Indexes
Stairway to SQL Server Replication
Stairway to SQL Server Reporting Services
Stairway to StreamInsight

Visit this SQLServercentral.com

SQLBits.com share video session for last year conference (2010), and it free

SQL Server 2008 Jump Start
There are 5 tracks in this event: Overview Sessions, Database Infrastructure and Scalability, Business Intelligence Part I & II, Developer & Software + Services, and Application Compatibility & Upgrade.

Alternative method of insert data if not found, update record if data already exists

Recover Merge SQL Statement in Microsoft SQL Server 2008 Product Overview

Previously, SQL Statement for "insert data if not found, update record if data already exists" :
If (select 1 from table where id=@id)
Update
Else
Insert

With merge function, we can do this way
MERGE Table1 cs
USING Table2 cd
on Table1.id=cd.table1_id
WHEN MATCHED THEN
UPDATE
SET cs.Cust_Name=cd.Cust_name,
cs.Cust_Surname=cd.Cust_surname,
cs.Cust_Email=cd.Cust_email
WHEN NOT MATCHED BY TARGET THEN
INSERT (Cust_ID,cust_name,cust_surname,cust_email)
VALUES(cd.Cust_ID, cd.Cust_Name,cd.Cust_Surname,cd.Cust_Email);

Merge function can do more
If not match by target
If not match by source
Output

Reference
SQL Server 2008: Locking with MERGE statement compared to Update/Insert in SQL Server 2005
SQL Server: Best way to Update row if exists, Insert if not
The MERGE Statement in SQL Server 2008
SQL SERVER – 2008 – Introduction to Merge Statement – One Statement for INSERT, UPDATE, DELETE
Using SQL Server 2008's MERGE statement

---------------------------------------------------------------------------------
Example

Create database temptest1456

use temptest1456

CREATE TABLE [dbo].[Customers](
[Cust_ID] [int] NOT NULL,
[Cust_Name] [varchar](50) NOT NULL,
[Cust_Surname] [varchar](50) NOT NULL,
[Cust_Email] [varchar](50) NOT NULL
) ON [PRIMARY]

CREATE TYPE [dbo].[customertype] AS TABLE(
[Cust_ID] [int] NULL,
[Cust_Name] [varchar](50) NULL,
[Cust_Surname] [varchar](50) NULL,
[Cust_Email] [varchar](50) NULL
)
GO


insert into Customers values (1,'1steven','1gerrard','1sg@liverpool.com')
insert into Customers values (2,'1jamie','1gerrard','1sg@liverpool.com')
insert into Customers values (3,'1Kamie','1Kerrard','1sg@liverpool.com')


CREATE procedure newcustomer(@cust_id varchar(50),@cust_name varchar(50),@cust_surname varchar(50),@cust_email varchar(50))

as

begin

IF exists (select 1 FROM Customers where Cust_ID=@cust_id)
update Customers
set Cust_Name=@cust_name,
Cust_Surname=@cust_surname,
Cust_Email=@cust_email
where Customers.Cust_ID= @cust_id
ELSE
insert into customers
(Cust_ID,Cust_Name,Cust_Surname,Cust_Email)
values (@cust_id,@cust_name,@cust_surname,@cust_email)
END

Exec newcustomer 3, '2jamie','2caragher','2jc@liverpool.com'
Exec newcustomer 4, '2kamie','2daragher','2kc@liverpool.com'
Exec newcustomer 6, '2lamie','2earagher','2lc@liverpool.com'
Exec newcustomer 7, '2lamie','2earagher','2lc@liverpool.com'
Exec newcustomer 8, '2lamie','2earagher','2lc@liverpool.com'
Exec newcustomer 9, '2lamie','2earagher','2lc@liverpool.com'
Exec newcustomer 10, '2lamie','2earagher','2lc@liverpool.com'
Exec newcustomer 11, '2lamie','2earagher','2lc@liverpool.com'
Exec newcustomer 12, '2lamie','2earagher','2lc@liverpool.com'
Exec newcustomer 13, '2lamie','2earagher','2lc@liverpool.com'
Exec newcustomer 14, '2kamie','2daragher','2kc@liverpool.com'
Exec newcustomer 16, '2lamie','2earagher','2lc@liverpool.com'
Exec newcustomer 17, '2lamie','2earagher','2lc@liverpool.com'
Exec newcustomer 18, '2lamie','2earagher','2lc@liverpool.com'
Exec newcustomer 19, '2lamie','2earagher','2lc@liverpool.com'








Alter procedure newcustomer(@Customer_details customertype READONLY)

as

begin

update Customers
set Cust_Name=b.Cust_name,
Cust_Surname=b.Cust_surname,
Cust_Email=b.Cust_email
from @Customer_details b
where Customers.Cust_ID=b.Cust_Id

insert into customers
select * from @Customer_details a
where not exists (Select 1 from Customers b where a.cust_id=b.Cust_ID)

end

DECLARE @Customer_details AS customertype
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (5, '3jamie','3caragher','3jc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (6, '3kamie','3daragher','3kc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (7, '3lamie','3earagher','3lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (8, '3kamie','3daragher','3kc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (9, '3lamie','3earagher','3lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (10, '3lamie','3earagher','3lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (11, '3lamie','3earagher','3lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (12, '3lamie','3earagher','3lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (13, '3lamie','3earagher','3lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (14, '3lamie','3earagher','3lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (15, '3lamie','3earagher','3lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (16, '3lamie','3earagher','3lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (17, '3lamie','3earagher','3lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (18, '3lamie','3earagher','3lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (19, '3lamie','3earagher','3lc@liverpool.com')


exec newcustomer1 @customer_details


ALTER procedure newcustomer (@Customer_details customertype READONLY)
as
BEGIN
MERGE Customers cs
USING @Customer_details cd
on cs.cust_id=cd.cust_id
WHEN MATCHED THEN
UPDATE
SET cs.Cust_Name=cd.Cust_name,
cs.Cust_Surname=cd.Cust_surname,
cs.Cust_Email=cd.Cust_email
WHEN NOT MATCHED BY TARGET THEN
INSERT (Cust_ID,cust_name,cust_surname,cust_email)
VALUES(cd.Cust_ID, cd.Cust_Name,cd.Cust_Surname,cd.Cust_Email);

END


DECLARE @Customer_details AS customertype
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (7, '4jamie','4caragher','4jc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (8, '4kamie','4daragher','4kc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (9, '5lamie','4earagher','4lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (15, '4lamie','4earagher','4lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (16, '4jamie','4caragher','4jc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (17, '4kamie','4daragher','4kc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (22, '5lamie','4earagher','4lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (23, '4lamie','4earagher','4lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (24, '4lamie','4earagher','4lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (25, '4lamie','4earagher','4lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (26, '4lamie','4earagher','4lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (27, '4lamie','4earagher','4lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (28, '4lamie','4earagher','4lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (29, '4lamie','4earagher','4lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (30, '4lamie','4earagher','4lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (31, '4lamie','4earagher','4lc@liverpool.com')

exec newcustomer1 @customer_details

drop database temptest1456

SQL 2008 Logon Trigger

Create Logon Trigger
CREATE TRIGGER logon_trigger_not_from_excel
ON ALL SERVER FOR LOGON
AS
BEGIN
IF APP_NAME() LIKE '%excel%'
ROLLBACK
END

Drop Logon trigger
DROP TRIGGER logon_trigger_not_from_excel ON ALL SERVER


Which trigger appear in SQL Server
SELECT name, s.type_desc SQL_or_CLR,
is_disabled, e.type_desc FiringEvents
FROM sys.server_triggers s
INNER JOIN sys.server_trigger_events e ON
s.object_id = e.object_id

What is the metadata of Trigger?
SELECT t.name, m.definition
FROM sys.server_sql_modules m
INNER JOIN sys.server_triggers t ON
m.object_id = t.object_id

Refenrece:
Logon Triggers

Frederik Vandeputte : SQL Server Logon triggers part 2

Viewing DDL Triger Medatdata by joining sys.server_SQL_modules and sys.server_triggers

SQL 2008 control access from EXCEL, ODBC, ACCESS

We want change Application access from SQL Authentication to SSPI in order to trace who is access SQL table
We worry user may connect to SQL Server from Microsoft Excel, Microsft Access, SQL Server Management Studio
After google, we found out that is a features in SQL Server to control acccess of user: Logon Trigger

Below Code from Frederik Vandeputte-SQL Server Logon triggers part 2

CREATE TRIGGER logon_trigger_not_from_excel
ON ALL SERVER FOR LOGON
AS
BEGIN
IF APP_NAME() LIKE '%excel%'
ROLLBACK
END


After create Logon Trigger in SQL Server, I open Microsoft Excel, try connect to SQL Server, but connection successful!!
The result is not expected! SQL Server should reject the connection from Microsoft Excel
I can see the database and table in the SQL Server and successful retrieve the data and display in Excel File.
why the connection can establish even I already setup to rollback if connection from Excel?


Step to made connection from Microsoft Excel to local server:
Open Excel file->data->From Other Sources
From SQL Server-> keying .
"." for local server
log on credentials = User WIndows Authentication
I can view all database and table from local server

Now let troubleshoot why the trigger logon is not effective or no rollback the connection:

Below code from Vipul Shah's SQL Server Logon triggers part 2

CREATE DATABASE dbadb
go
use dbadb
CREATE TABLE myTest
(id int identity(1,1),
EventTime datetime,
LoginName varchar(50),
HostName varchar(50),
LoginType varchar(50),
ApplicationName varchar(100))

CREATE TRIGGER myTest_LogonTrigger ON ALL SERVER
WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
declare @LogonTriggerData xml,
@EventTime datetime,
@LoginName varchar(50),
@HostName varchar(50),
@LoginType varchar(50)
set @LogonTriggerData = eventdata()
set @EventTime = @LogonTriggerData.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime')
set @LoginName = @LogonTriggerData.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(50)')
set @HostName = @LogonTriggerData.value('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(50)')
set @LoginType = @LogonTriggerData.value('(/EVENT_INSTANCE/LoginType)[1]', 'varchar(50)')
insert into dbadb..myTest values (@EventTime, @LoginName, @HostName, @LoginType,app_name())
END

Connect to SQLServer from Microsft Excel :
Open Excel file->data->From Other Sources
From SQL Server-> keying .
"." for local server
log on credentials = User WIndows Authentication and Press "Next"
Connection succesful and "Select Database and Table" page appear

Go backup to SQLServer and type the below query
select * from dbadb.dbo.mytest

It show the below result :

id	EventTime	LoginName	HostName	LoginType	ApplicationName
1	2011-01-01 20:25:04.467	testuser		Windows (NT) Login	2007 Microsoft Office system

It show that Excel connection appear as Application Name "2007 Microsoft Office system"

Let alter logon trigger not from excel as below
ALTER TRIGGER logon_trigger_not_from_excel
ON ALL SERVER FOR LOGON
AS
BEGIN
IF APP_NAME() LIKE '%2007 Microsoft Office system%' ROLLBACK
END

Make connection to SQL Server from Microsoft Excel again.
Connection Fail!
It show logon failed for login 'testuser' due to trigger execution.
We successful stop user from connection to SQLServer through Microsoft Excel.

I test few application and the associate application name detect from trigger logon listed as below:

Application	App_name()
Excel Office Excel 2007	2007 Micorosoft office System
Microsoft Office Access 2007	2007 Micorosoft office System
Microsoft SQLServer Management Studio	Microsoft SQL Server Management Studio - Transact-SQL IntelliSense
OSQL	OSQL-32

Reference:
Logon Triggers

Prevent Access to SQL Server 2005 databases from Microsoft Excel and Word

Frederik Vandeputte : SQL Server Logon triggers part 2