Alert on Job Failure (email alert)

I have had such luck getting a single failure alert to happen.  Here’s what I did.
I presume you have an email account set up in SQL Server.

Right-click SQL Server Agent –> Properties

Select Alert System.
Turn on Enable mail Profile and click OK. 
Then repeat by turning it off! Then turn it back on.

Then, I created a job called fail that would force to fail.
Right-click the ill-fated job –> Properties
 
select Notifications
don’t mess with Alerts (they annoy me at times; my ignorance shows).

Make the ill-fated-job  run so it can fail; check your mail.  If you have enabled mail properly, (turn off + turn on SQL Alert system) you typically won’t have any problems

Remote servers

How can you talk to another server on your network or across the internet?

There a lot of ways to skin a cat, and there are many ways of getting your through to the database you need to talk to.

Have on hand:

• A personal computer you can play with (SQL Server Express?)
  
• A windows login that is godlike (that can map to another computer). Password, etc.
  

Permissions

Practice on a personal computer you can play with -- SQL Server Express installed.
Then try it on a server on a network. If you gradually get the mapping of permissions down, you will understand how it works.

Think of a linked server as a rope bridge. The same permissions must be duplicated to the other server.
Windows permissions is a bit more tricky than that of a SQL login. You will find lots of discussions and many people relenting to SQL login permissions (I own my issue on that account!).

Try a script

What you need to do to make this work:

'[domain\[username]' : replace the text in the below script '[domain\[username]' with your own type of windows login.
this windows user name must match permissions exactly on the target server

'mylinkedserver' - you probably don't have a server called 'mylinkedserver', so find the name of the server/instance of your database you want to link to)..

EXEC master.dbo.sp_addlinkedserver @server = N'mylinkedserver', @srvproduct=N'SQL Server' EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'mylinkedserver',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'mylinkedserver',@useself=N'True',@locallogin=N'[domain]\[username]',@rmtuser=NULL,@rmtpassword=NULL GO EXEC master.dbo.sp_serveroption @server=N'mylinkedserver', @optname=N'collation compatible', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'mylinkedserver', @optname=N'data access', @optvalue=N'true' GO EXEC master.dbo.sp_serveroption @server=N'mylinkedserver', @optname=N'dist', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'mylinkedserver', @optname=N'pub', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'mylinkedserver', @optname=N'rpc', @optvalue=N'true' GO EXEC master.dbo.sp_serveroption @server=N'mylinkedserver', @optname=N'rpc out', @optvalue=N'true' GO EXEC master.dbo.sp_serveroption @server=N'mylinkedserver', @optname=N'sub', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'mylinkedserver', @optname=N'connect timeout', @optvalue=N'0' GO EXEC master.dbo.sp_serveroption @server=N'mylinkedserver', @optname=N'collation name', @optvalue=null GO EXEC master.dbo.sp_serveroption @server=N'mylinkedserver', @optname=N'lazy schema validation', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'mylinkedserver', @optname=N'query timeout', @optvalue=N'0' GO EXEC master.dbo.sp_serveroption @server=N'mylinkedserver', @optname=N'use remote collation', @optvalue=N'true'

 
Troubleshooting: test the connection:



If you fall into any kinds of errors (I made a fake server to emulate an error):



Then, click the advanced technical error



Select the Message areas to get an error number and google the error number or the phrase; you will find some good some bad advice; stick to Microsoft or reputable websites.




 
If you have no errors, then you will call a table from the other server:
Select * from mylinkedserver.ReportServer.dbo.Catalog


 
Related articles (linked servers between 2005 and 2008)

Calling a linked server (microsoft's site)

Use this helpful documentation when scripting out linked servers!

Seriously! (Severity in Error Messages) Take time to be careful

—- use the master database

SELECT error, severity, dlevel,
[description]
FROM sysmessagesWHERE
(msglangid = 1033)
-- hint-hint: the msglangid =1033 is for English..
You can use this list of errors and severities to make sure your scripts are neat and clean.

Function	Description
ERROR_NUMBER()	Returns the number of the error
ERROR_SEVERITY()	Returns the severity
ERROR_STATE()	Returns the error state number
ERROR_PROCEDURE()	Returns the name of the stored procedure or trigger where the error occurred
ERROR_LINE()	Returns the line number inside the routine that caused the error
ERROR_MESSAGE()	Returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times

whaa? Differential won't restore? (what bloke backed up last)

/*
We had to restore a full backup and a differential backup a while ago
To a local db to get some old data put back into the live database.
my manager & I had a heck of a time trying to find out where someone put the last back up.


This query helped us know a utility was still backing up to another location AFTER the job ran...
hence all the extents the DIFF were looking for were bye-bye into the rogue, full-backup.
*/

-------------copy below this line-----------------------

declare
@startDate DATETIME,
@endDate DATETIME,
@database sysname


-- enter your database and date range here:
select
@database = 'SSRC_V5SP1-2',
@startDate = '09/28/09',
@endDate = '09/30/09'


SELECT b.database_name,
b.backup_start_date,
b.backup_finish_date,
b.user_name,
f.logical_name,
f.physical_name,
mf.physical_device_name,
f.file_type,
f.file_size,
b.backup_size
FROM msdb.dbo.backupfile f,
msdb.dbo.backupset b,
msdb.dbo.backupmediafamily mf
WHERE f.backup_set_id = b.backup_set_id
AND b.media_set_id = mf.media_set_id
AND b.backup_start_date BETWEEN @startDate
AND @endDate
AND b.database_name = COALESCE
(@database,database_name)
ORDER BY b.database_name,
b.backup_start_date

Eloquent way of logging errors in SQL Server2

I enjoy this template of error trapping.
It uses the TRY CATCH way of doing things, which is awesome.

To take this out for a spin:
Create the error table (just like inside Adventureworks):

CREATE TABLE [dbo].[ErrorLog]( [ErrorLogID] [int] IDENTITY(1,1) NOT NULL,[ErrorTime] [datetime] NOT NULL,[UserName] [sysname] NOT NULL,[ErrorNumber] [int] NOT NULL, [ErrorSeverity] [int] NULL, [ErrorState] [int] NULL,[ErrorProcedure] [nvarchar](126) NULL, [ErrorLine] [int] NULL,[ErrorMessage] [nvarchar](4000) NOT NULL,CONSTRAINT [PK_ErrorLog_ErrorLogID] PRIMARY KEY CLUSTERED ( [ErrorLogID] ASC)WITH (PAD_INDEX= OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] GO

This table will be filled when there is an error.
The below procedure will be filled with SQL Server's famous @error_number

<><>

CREATE PROCEDURE [dbo].[uspLogError] @ErrorLogID [int] = 0 OUTPUT -- Contains the ErrorLogID of the row inserted -- by uspLogError in the ErrorLog table. AS BEGIN SET NOCOUNT ON; -- ditch the extra messages SET @ErrorLogID = 0; -- all is ok with the world BEGIN TRY IF ERROR_NUMBER() IS null -- no error? RETURN; IF XACT_STATE() = -1 -- uncommitable transaction? don't do any damage! BEGIN PRINT 'Cannot log error since the current transaction is in an uncommittable state. ' + char(10) + 'Rollback the transaction before executing uspLogError in order to successfully log error information.'; RETURN; END; INSERT [dbo].[ErrorLog] ( [UserName], [ErrorNumber], [ErrorSeverity], [ErrorState], [ErrorProcedure], [ErrorLine],[ErrorMessage] ) VALUES( --CONVERT(sysname, CURRENT_USER), -- current_user is the owner (ie: dbo.) CONVERT(sysname,system_user), ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), coalesce(ERROR_PROCEDURE(),'n/a'), ERROR_LINE(),ERROR_MESSAGE() ); -- Pass back the ErrorLogID of the row inserted SELECT @ErrorLogID =convert(nvarchar(16),@@IDENTITY); END TRY BEGIN CATCH PRINT 'An error occurred in stored procedure uspLogError: '; EXECUTE [dbo].[uspPrintError]; RETURN -1; END CATCH END;

Now, you can create any procedure and use the TRY CATCH method!

<><>

begin try begin transaction -- --insert a bad data type into a table insert into departments(deptnament) values ('a string that is way too long to append, way too long for sure absolutely no doubt to ') commit transaction End try Begin catch rollback transaction -----begin print error print'oops!' + char(10) + error_message() +char(10)+ 'error number: ' + convert(nvarchar(16),error_number()) ; ------end print error declare @err int set @err =error_number() ; execute uspLogError @err -- log the error here, passing the error # End catch -- test: select * from errorlog

And there you have your scripting for error logs. If you use a version of the above script, you will be able to track the errors way better
.

RESTORE HEADERONLY

If you don't know which backup files hold the right database to restore?
If you need to locate the correct backup file to restore, use RESTORE BACKUP WITH HEADERONLY option.

See msdn definition file:
http://msdn.microsoft.com/en-us/library/aa238455(SQL.80).aspx

How Heavy is my SQL Server database is being used?

I wrote this script after studying about I/O statistics.
This view is based on sys.dm_io_virtual file status
This looks at the physical file reads and writes, and how many times it stalled.
If it is stalling, you can dig deeper to see what is causing the stall… then you can partition the offensive table/object etc.

Cool system view!

-----------@Mydatabase-----------------------

-- type in name of database between the ''
-- or type in 'all' between the ''
--------------------------------------------

declare @Mydatabase nvarchar(255)
set @Mydatabase = 'all'
--------------------------------------------

if @Mydatabase = 'all'
begin

select

db_name(database_id)
as database_name,
file_name(file_id)
as file_name,
sample_ms,
num_of_reads,
num_of_bytes_read,
io_stall_read_ms,
num_of_writes,
num_of_bytes_written,
io_stall_write_ms,
io_stall,
size_on_disk_bytes

from
sys.dm_io_virtual_file_stats(null, null) ;
end
else
begin
declare @database int
set @database = db_id(@Mydatabase) ;

select

db_name(database_id)
as database_name,
file_name(file_id) as file_name,
sample_ms,
num_of_reads,
num_of_bytes_read,
io_stall_read_ms,
num_of_writes,
num_of_bytes_written,
io_stall_write_ms,
io_stall,
size_on_disk_bytes
from

sys.dm_io_virtual_file_stats(@database, null) ;
end

I keep losing this helpfile.. when I need it the most, it is located in the BOL labeled "sp_addlinkedserver (Transact-SQL)", if you scroll to the bottom of the article.

The frustrating thing about learning about linked servers is the coordination between the different types of connections! Viva la difference?

Remote OLE DB data source	OLE DB provider (@srvproduct)	product_name (whatever you want to name it "blah blah"…)	provider_name (@provider)	data_source (@datasrc)	location (anyone?)	provider_string (@providerstr?)	catalog (@catalog)
SQL Server	Microsoft SQL Server Native Client OLE DB Provider	SQL Server 1 (default)
SQL Server	Microsoft SQL Server Native Client OLE DB Provider		SQLNCLI	Network name of SQL Server (for default instance)			Database name (optional)
SQL Server	Microsoft SQL Server Native Client OLE DB Provider		SQLNCLI	servername\instancename (for specific instance)			Database name (optional)
Oracle	Microsoft OLE DB Provider for Oracle	Any2	MSDAORA	SQL*Net alias for Oracle database
Oracle, version 8 and later	Oracle Provider for OLE DB	Any	OraOLEDB. Oracle	Alias for the Oracle database
Access/Jet	Microsoft OLE DB Provider for Jet	Any	Microsoft.Jet. OLEDB.4.0	Full path of Jet database file
ODBC data source	Microsoft OLE DB Provider for ODBC	Any	MSDASQL	System DSN of ODBC data source
ODBC data source	Microsoft OLE DB Provider for ODBC	Any	MSDASQL			ODBC connection string
File system	Microsoft OLE DB Provider for Indexing Service	Any	MSIDXS	Indexing Service catalog name
Microsoft Excel Spreadsheet	Microsoft OLE DB Provider for Jet	Any	Microsoft.Jet. OLEDB.4.0	Full path of Excel file		Excel 5.0
IBM DB2 Database	Microsoft OLE DB Provider for DB2	Any	DB2OLEDB			See Microsoft OLE DB Provider for DB2 documentation.	Catalog name of DB2 database

Link SQL Server 2008 to 2005

Have on hand:

1. A name for your linked server you want to create; can be anything, just keep it clean.
   I will call mine MyLinkedServer
   
   
2. If default instance, name of the server only (as opposed to server/instance)
   Mine is MyComputerName\SQLEXPRESS2
   
   
3. If second instance, server/instance_name
   
   
4. It is presumed you have same login name as the server you are reaching over and linking to.
   I tested these two servers on the same computer (SQL Server 2005 & 2008)
   If they are not, impersonate yourself to the server you are trying to talk to:
   
   1. After scripting this out, go to SMSS and right-click your near MyLinkedServer à Properties
      
   2. Select the security option
      
   3. Add the login name the other server uses and click impersonate.
      

   EXEC
   master.dbo.sp_addlinkedserver
   

   @server =
   N'MyLinkedServer',
   

   @srvproduct=N'',
   

   @provider=N'SQLNCLI',
   

   @datasrc=N'
   MyComputerName\SQLEXPRESS2'
   

If you use the graphical interface, go for it. This way is easier for me.

Now you can call whatever database you have there. Here's what I did:

select
*
from [MyLinkedServer].[MyDatabase].dbo.[MyTable]

Last time statistics were done?

-- view the date the statistics were last updated:

select 'index Name' = i.[name],
'Statistics Date' = stats_date(i.[object_id], i.index_id)
from sys.objects o
inner join sys.indexes i
on o.name = 'Employee'
and o.[object_id] = i.[object_id]

-- if you need to update all the indexes:

update statistics HumanResources.Employee
with fullscan

dm_db_index_operational_stats

here's a method I found:

select object_schema_name(ddios.object_id) + '.' + object_name(ddios.object_id) as objectName,
indexes.name, case when is_unique = 1 then 'UNIQUE ' else '' end + indexes.type_desc as index_type,
page_latch_wait_count , page_io_latch_wait_count
from sys.dm_db_index_operational_stats(db_id(),null,null,null) as ddios
join sys.indexes
on indexes.object_id = ddios.object_id
and indexes.index_id = ddios.index_id
order by page_latch_wait_count + page_io_latch_wait_count desc

Find Fragmentation on a specific table

-- How to find fragmentation (yet another way)
declare @MyDatabase sysname,
@MyTable sysname

set @MyDatabase = 'Adventureworks'
set @MyTable = 'HumanREsources.Employee'
select
index_id,
avg_fragmentation_in_percent,
avg_page_space_used_in_percent
from
sys.dm_db_index_physical_stats(db_id(@MyDatabase),
object_id(@MyTable),
null,
null, 'detailed')
where
index_id <> 0


also, find row-level i/o, locking and latching issues and access method activity:
by the way, this is an excerpt from SQL Server Books online

DECLARE @db_id smallint;
DECLARE @object_id int;
SET @db_id = DB_ID(N'AdventureWorks');
SET @object_id = OBJECT_ID(N'AdventureWorks.Person.Address');
IF @db_id IS NULL
BEGIN;
PRINT N'Invalid database';
END;
ELSE IF @object_id IS NULL
BEGIN;
PRINT N'Invalid object';
END;
ELSE
BEGIN;
SELECT * FROM sys.dm_db_index_operational_stats(@db_id, @object_id, NULL, NULL);
END;
GO

Index Fragmentation for SQL Server

RE: Microsoft's MCTS 70-431 certification for SQL SErver 2005 : Fragementation Section

This section was horribly boring until I figured out a way to create fragementation on a table in a database.
Here's how I did it:

-- create database called repository
create database repository
go
-- create table:
USE [repository]
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tmp]') AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[tmp]
(
[id]
[int] IDENTITY(1,1) NOT NULL,
[field] [varchar](64) NULL,
PRIMARY KEY CLUSTERED
(
[id] ASC
)
WITH
(
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [repository]
)
ON [repository]
END

THEN:

1. I inserted 10,000 rows of random string data in the [field] column (I used Redgate's Data generator.. fantastic time-cutting tool)
2. I truncated some data to be smaller (random rows)
3. I added string data to be larger (random rows)
4. I deleted random rows.

What I got:

• I had fragmentation to play with (yay!).

THEN:

1. I did an alter on the ALTER index REORGANIZE. (reduced fragmentation by 1/2)
2. Then I used the alter index... rebuild. (yes, you guessed it: ALTER INDEX...REBUILD thwacks all of the fragmentation & performance is improved).

WHEN SHOULD I DEFRAG MY INDEXES?

here is a system tool to peek into what kind of fragmentation you have:

select * from sys.dm_db_index_physical_stats
-- (this system view is a "dynamic management function" = DMF. yet another acronymn to learn. yippee)

Do a INDEX....REORGANIZE if:

avg_page_space_used_in_percent is between 60 and 75
or
avg_avg_fragmentation_in_percent between 10 and 15

Do a INDEX....REBUILD if:

avg_page_space_used_in_percent is greater than 60
or
avg_fragmentation_in_percent is greater than 15

This is also a procedure I use to analyze the fragmentation (by database). I threw this together from a few sources & use it regularly:

EXEC dbo.sp_executesql @statement = N'
ALTER procedure [dbo].[database_fragmentation_check]
@database sysname
as
select object_name(dt.object_id),
si.name,
dt.avg_fragmentation_in_percent,
dt.avg_page_space_used_in_percent
from ( select object_id,
index_id, avg_fragmentation_in_percent,
avg_page_space_used_in_percent
from sys.dm_db_index_physical_stats(db_id(@database),
null,
null,
null,
''detailed'')
where index_id <> 0
) as dt -- does not return information about heaps
inner join sys.indexes si on si.object_id = dt.[object_id]
and si.index_id = dt.index_id
'

70-431 Certification Brain Dump (Configuration Section)

Configuring Server Security principals

(Lesson 4 in Microsoft cert book)

You can use the below as flash cards: print them out and fold over the answers

What are the two authentication modes. And Which one is reccomended	Windows and Mixed Mode. Windows Mode is recommended because you can completely rely on Active Directory's integrated security model.
What two ways can you re-configure the modes of authentication	Through SMSS graphically, right-click the server properties à Security Or Type script create login ….. domain\username from WINDOWS Or Create login …. With PASSWORD = '….'
What are the three other options the cert book offers with the create login script?	Must_change – login & pw must change at login Check_expiration – SQL Server will check the expiration of the login when the user logs in Check_policy – windows will apply the local windows password policy on the SQL Server logins
Why would you use SQL Server login (server thus set to Mixed mode)	If you have a contractor working on an external project and cannot log into the network, or if they can, the bandwidth is too large. If a legacy application requires a login as such. Then you can apply the windows rules to that user login by using the check_policy method
If you have mixed mode and create a user, what is the best practice to handle that user	Add an expiration date when you create a user (create user MyUser with PASSWORD = '…' check_expiration)
What are 8 SQL Server's fixed server roles	Sysadmin Serveradmin Setupadmin Securityadmin Processadmin Dbcreator Diskadmin Bulkadmin
Describe sysadmin fixed server role	Performs any activity in SQL Server. The permissions on this role all fixed server roles
Describe serveradmin fixed server role	Configure server-wide settings
Describe setupadmin fixed server role	Adds and removes linked servers and execute some system stored procedures. (ie: sp_serveroption)
Describe the securityadmin fixed server role	Manage server logins
Describe processadmin fixed server role	Manage processes running in an instance of SQL Server
Describe dbcreator fixed server role	Creates and alters databases
Describe diskadmin fixed server role	Manages disk files
Describe bulkadmin fixed server role	Execute the BULK INSERT statement

Create a user with an expiration date, enabling the password policy

CREATE LOGIN [login name] WITH PASSWORD='password', CHECK _EXPIRATION=ON, CHECK_POLICY=ON

Modify existing login

ALTER LOGIN [login name] WITH PASSWORD = 'password'

Disable login

ALTER LOGIN [login name] DIABLE

Drop a Windows login – or user:

DROP LOGIN [domain\user] or DROP LOGIN [username]

Get login information

Select * from Sys.sql_logins