How to receive Deadlock information automatically via email.

http://www.sqlservercentral.com/articles/deadlock/71374/


Requirement number one:
The first requirement is to turn on the appropriate Trace Flags. We do that by running the following command.

DBCC TRACEON (3605,1204,1222,-1)

A brief overview to what those flags do is listed below.
3605 = write what we want to the error log.
1204 = Capture Deadlock Events.
1222 = Capture Deadlock Events with more info (SQL 2005 and higher)

It is important to note that setting trace flags this way will only work until the next time SQL Server is restarted. If you want to make sure your trace flags are always set this way, you can edit the startup options of SQL Server by using the -T switch or creating a SQL Agent job to run that code when the Agent starts.

Using SQL Profiler also can find the dead lock.

Grant all permissions on all tables,views

Grant select,insert,update,delete on all tables,views to Dev_Role on database

select 'grant select,insert,update,delete on '+ name + ' to Dev_Role' from sysobjects where type = 'U' or Type='V' GO

or
SELECT 'GRANT SELECT,insert,update,delete ON ' + TABLE_NAME + ' TO BATCH_ROLE' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
Go

--Grant exec permissions for SP's
Grant execute privileges to all Stored procedures to Dev_Role role
select 'grant exec on '+ name + ' to Dev_Role' from sysobjects where type = 'P' AND category =0
Go

DB_OWNER Role remediation

DDL Admin role Can issue ALL DDL, but cannot issue GRANT, REVOKE, orDENY statements.When you dont want to give you developer DB Owner rightfor security concerns , You can assign this role to your developer withDB Data Reader and Data Writer permission.
The members of db_ddladmin database role can make any data definition language commands in the database.


1.db_datareader – select ? Developer role

   The members of db_datareader database role can see any data from all user tables in the database.

2.db_datawriter – insert, update, delete ?
The members of db_datawriter database role can add, change, or delete data from all user tables in the database.

3. db_ddladmin – Create,alter,drop,truncate -?
The members of db_ddladmin database role can make any data definition language commands in the database.

Connection Pooling

Setting the maximum size of the connection pool is usefull if you suspect that you have blocking problems due to the large number of concurrent queries. In this case you may be able to reduce blocking by limiting the maximum size of the connection pool. Of course, the pool already limits the number of concurrent connections, so you may never encounter this problem.

Master database Transaction log backup can not take.

Only full database backups of master can be performed. Transactional log, differential or filegroup backups of master are not allowed. Thus if you create a Database Maintenance Plan for all the system databases or if you select the master database and you select the Back up the transaction log as part of the maintenance plan option, the backup transaction log step for the master database will fail with this error message:

SQL Server Instance connection using Client tool

If SQL Server Instance is not named Instance then you can connect using the below any option for connection.

1.ServerName.domainName.com,PortNumber
or
2.IPNUmber,PortNumber
10.20.30.40,1590

SQL Server Interview questions

1. http://blog.sqlauthority.com/2007/04/21/sql-server-interview-questions-and-answers-complete-list-download/
2.http://www.techinterviews.com/ms-sql-server-interview-questions
3.http://www.mssqltips.com/category.asp?catid=46
4.http://vyaskn.tripod.com/iq.htm
5. http://dayananthan.wordpress.com/2008/01/20/sql-server-interview-questions/
6.http://www.codeproject.com/KB/database/SQLInterviewQuestions.aspx

Database Mirroring Setup Steps

You need to perform the following prior to start database mirroing:
1. Take a full backup in principal database and copy it to mirror server
2. Restore the principal database using With NORECOVERY option
3. Take a single transaction log backup in principal servers database and copy it to mirror server
4. Restore that single t-log backup using With NORECOVERY option and start mirroring,
it should work..

SQL Server Agent jobs setup minimum permissions

SQL Server 2005 introduced the following msdb database fixed database roles, which give administrators finer control over access to SQL Server Agent. The roles listed from least to most privileged access are:
SQLAgentUserRole
SQLAgentReaderRole
SQLAgentOperatorRole


http://msdn.microsoft.com/en-us/library/ms188283.aspx

To find out users and roles on SQL Server Instance

--users and roles on SQL Server Instance
sp_helplogins

Snapshot Database Creation

CREATE DATABASE Test_Snapshot ON
(
NAME = N'Test_Datafile',
FILENAME = N'D:\DB_Snapshots\test_snapshot.ss'
)
AS SNAPSHOT OF Test;

1.Test_Datafile' is the logical name for Test(primary DB) database, which is same logical name for snapshot database.
2. Only physical location is different
3.Snashot Database

• is a read-only static view of a database.
• This option is available only Enterprise Edition.
• Can create in same Instance only.It is not possible with different Instanaces
• Can not take backup of the Snapshot Database.

4.To drop the Snapshot Database
DROP Database [Test_Snapshot ]

5.Hourly based Snapshot Database:

• Create Sql Server Agent job
• Configure the Agent job as first drop snapshot database
• Re-create Snapshot database
• Schedule the agent job every hour

Tran Log/Differential/File group Backups can not take for Master Database

Only full database backups of master database can be performed. Transactional log, differential or filegroup backups of master are not allowed. Thus if you create a Database Maintenance Plan for all the system databases or if you select the master database and you select the Back up the transaction log as part of the maintenance plan option, the backup transaction log step for the master database will fail with the error message.

SSAS Videos

Part-1
http://www.youtube.com/watch?v=yawq4SGoRoY

Part-2
http://www.youtube.com/watch?v=VR22wNN40g0

Part-3
http://www.youtube.com/watch?v=i_rcXVYSr3w


Creating a Basic SSAS Cube
http://www.sqlshare.com/creating-a-basic-ssas-cube_22.aspx

Using Excel 2007 with Analysis Serviceshttp://www.sqlshare.com/using-excel-2007-with-analysis-services_54.aspx

SQL 2005 Database Snapshots

Database Snapshot is new concept in SQL Server 2005 and available in Enterprise Edition only. It provides a read-only, "virtual" copy of a database, at a given point in time.

Database Snapshot can work in

1. Single Server
2. Database Mirroring
3. Failover Cluster

Note: Source database and snapshot database must be on the same instance.

More details Database Snapshot:
1. http://www.simple-talk.com/sql/database-administration/sql-server-2005-snapshots/

2.[PPT] Implementing Database Snapshot & Database Mirroring in SQL - Html ...
or www.sqlmvp.com/ppt/p3.ppt

3. DB Mirroring
http://www.emc.com/collateral/hardware/white-papers/h2649-microsoft-sql-srvr-2005-db-mirror-wp-ldv.pdf

Database Owner issue

EXEC MyDatabase..sp_changedbowner ‘sa’;

Shrinking Truncate Log File – Log Full

Tran log full and disk space also full.
Can not take the tran log on same disk.So take tran log to diffrent disk.then follow the 3 steps 2 or 3 times and log space will come to normal.

USE DatabaseName
GO
1. DBCC SHRINKFILE(, 1)
2. BACKUP LOG WITH TRUNCATE_ONLY
3. DBCC SHRINKFILE(, 1)
GO

--Balki