Steps to insert error log records into temporary table

You can follow the below steps to enter error log records into temporary table and query it.

-- Command will create the temporary table in tempdb CREATE TABLE [dbo].[#TmpErrorLog] ([LogDate] DATETIME NULL, [ProcessInfo] VARCHAR(20) NULL, [Text] VARCHAR(MAX) NULL ) ; -- Command will insert the errorlog data into temporary table INSERT INTO #TmpErrorLog ([LogDate], [ProcessInfo], [Text]) EXEC [master].[dbo].[xp_readerrorlog] 0 ; -- retrieves the data from temporary table SELECT * FROM #TmpErrorLog

Stop successfull backup loging messages in SQL Server Error Log?

Whenever backup peformed on SQL Server, it records the backup entry in the SQL Server. Because of that error log file grows and sometimes we are missing important information from there.

For example,

use master backup database jshah to disk = 'c:\jshah.bak'

Above command will log the below message in the SQL Server error log.
Backup Message:
Database backed up. Database: jshah, creation date(time): 2010/12/31(09:55:22), pages dumped: 178, first LSN: 28:60:170, last LSN: 28:130:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'c:\jshah.bak'}). This is an informational message only. No user action is required.

Solution
As a solution we can turn on the trace flag 3226 to stop loging of sucessfull backup message.

You can turn it on either using SQL Server Service Starup Parameter (-T 3226) or using DBCC TRACEON command.

-- To turn on the trace flag at global level DBCC TRACEON (3226,-1) -- To turn off the trace flag at global level DBCC TRACEOFF (3226,-1)

Wish You All Very Happy and Prosperous New Year 2011

[caption id="attachment_880" align="aligncenter" width="525" caption="Wish You all Very Happy and Prosperous New Year"][/caption]

Download SQL Server Interview Question in PDF

[caption id="attachment_873" align="aligncenter" width="614" caption="SQL Server Interview Questions for DBAs and Devlopers"][/caption]

Transaction Log Backup

Transaction Log Backup
In Full or Bulk Logged recovery models, it is very important that we have scheduled periodic Transaction Log backups so it will help us to maintain the the size of the transaction log within reasonable limits and will allow for the recovery of data with the least amount of data loss in case of any failure.

Transaction Log backups come in three forms:

Pure Log Backup: —A Pure Log backup contains only transactions and is completed when the database is in Full recovery model or Bulk Logged recovery model, but no bulk operations have been executed. In case of Bulk Logged recovery Bulk Operations are minimally logged.

Bulk Log Backup: —Bulk Log backups contain both transactional data and any physical extents modified by bulk operations while the database was in Bulk Logged recovery.

Tail Log Backup: —Tail Log backups are completed when the database is in Full or Bulk Logged recovery prior to a database restoration to capture all transaction log records that have not yet been backed up. It is possible in some instances to execute a Tail Log backup even if the database is damaged.

Pure or Bulk Log Backup Example
BACKUP LOG SQLDBPool
TO DISK = 'D:\SQLBackups\SQLDBPool.TRN'

Tail Log Backup Example
BACKUP LOG SQLDBPool
TO DISK = ‘D:\SQLBackups\SQLDBPoolTailLog.TRN’
WITH NO_TRUNCATE

How the SQLBrowser Service Works Internally?

The SQLBrowser Service is used by SQL Server for named instance name resolution and server name enumeration over TCP/IP and VIA networks.

The default instance of SQL Server is assigned the TCP Port 1433 by default to support client incoming requests. However, because more than one application/SQL Server Instances cannot share a port assignment, any named instances are given a random port number when the service is started. This random port assignment makes it difficult for clients to connect to it, because the client applications don't know what port the server is listening on. To meet this need, the SQLBrowser Service was created.

On start‐up, the SQLBrowser Service queries the registry to discover all the names and port numbers of installed servers and reserves UDP Port 1434. It then listens on UDP Port 1434 for SQL Server Resolution Protocol (SSRP) requests and responds to the requests with the list of instances and their respective port assignments so that clients can connect without knowing the port number assignment.

It is very important that no unauthenticated traffic on UDP Port 1434 be allowed on the network, because the service will respond to any request on that port.

If the SQLBrowser Service is disabled, it will be necessary to specify a static port number for all named instances of the SQL Server Service and to configure all client applications that connect to those instances with the appropriate connection information.

There will be only one SQL Browser Service for all the instances on same machine.

Row Versioning in SQL Server 2005

SQL Server 2005 has introduced two new Isolation Levels. We can use these Isolation Levels for row versioning.

1. READ_COMMITTED_SNAPSHOT (statement level)
2. ALLOW_SNAPSHOT_ISOLATION (transaction level)

These Isolation level turned on database level. You can turn on the Isolation Level using below command.

ALTER DATABASE sqldbpool
SET READ_COMMITTED_SNAPSHOT ON

ALTER DATABASE sqldbpool
SET ALLOW_SNAPSHOT_ISOLATION ON


When above Isolation level is turned on it will enable the row versioning at database level. Transaction or statement views the data as it existed at the start of the statement or transaction, instead of protecting all reads with locks. Row versioning will reduce the blocking/deadlock issues and boost the database performance. Row versioning also prevents users from reading uncommitted data and prevents multiple users from attempting to change the same data at the same time.

You can query sys.databases to check the above isolation level status.

SELECT name, snapshot_isolation_state_desc, is_read_committed_snapshot_on FROM sys.databases


SQL Server 2005 Output Clause

The OUTPUT clause helps in returning the actual data from a table soon after an insert, update or delete. Output clause uses two virtual tables inserted and deleted. Output clause can be useful to detect actual rows affected by DML statements.


CREATE TABLE Emp(
EmpName VARCHAR(9),Age INT,MaritalStatus char(1))

INSERT INTO Emp VALUES ('Dhvani',20,'M')
INSERT INTO Emp VALUES ('Nehal',20,'M')
INSERT INTO Emp VALUES ('Sunil',95,'M')
INSERT INTO Emp VALUES ('Suvrendu',40,'M')
INSERT INTO Emp VALUES ('Bill',11,'S')
INSERT INTO Emp VALUES ('Ram',100,'S')
INSERT INTO Emp VALUES ('Nirmal',50,'S')
INSERT INTO Emp VALUES ('R',30,'S')

Declare @dummyEMp as table (EmpName VARCHAR(9),Age INT,MaritalStatus char(1))

INSERT INTO Emp (EmpName,Age,MaritalStatus)
OUTPUT inserted.EmpName,inserted.Age,inserted.MaritalStatus INTO @dummyEMP
VALUES ('Dhvani',20,'M')

select * from @dummyEMp

Declare @dummyEMp as table (EmpName VARCHAR(9),Age INT,MaritalStatus char(1))
INSERT INTO Emp (EmpName,Age,MaritalStatus)
OUTPUT inserted.* INTO @dummyEMP
VALUES ('Deepali',27,'M')
select * from @dummyEMp


Declare @dummyEMp as table (EmpName VARCHAR(9),Age INT,MaritalStatus char(1))
update Emp
set MaritalStatus = 'M'
output deleted.* into @dummyEMP
where EmpName = 'Dhvani'
select * from @dummyEMp

Declare @dummyEMp as table (EmpName VARCHAR(9),Age INT,MaritalStatus char(1))
delete from Emp
output deleted.* into @dummyEMP
where Age > 50
select * from @dummyEMp


Ranking Function - NTITLE()

NTITLE() function is used to break up a record set into a specific number of groups.

SELECT NTILE(3) OVER (ORDER BY Age) AS [Group by Age],
EmpName,
Age
FROM Emp

Group by Age EmpName Age
-------------------- --------- -----------
1 Bill 11
1 Dhvani 20
1 Nehal 20
2 R 30
2 Abhinav 40
2 Suvrendu 40
3 Nirmal 50
3 Sunil 95
3 Ram 100

(9 row(s) affected)


Ranking Function - Dense_Rank()

Dense_Rank() :- The DENSE_RANK function is similar to the RANK function, although this function doesn’t produce gaps in the ranking numbers. Instead this function sequentially ranks each unique ORDER BY value. With the DENSE_RANK function each row either has the same ranking as the preceeding row, or has a ranking 1 greater then the prior row.

CREATE TABLE Emp(
EmpName VARCHAR(9),
Age INT,
MaritalStatus char(1))

INSERT INTO Emp VALUES ('Abhinav',40,'S')
INSERT INTO Emp VALUES ('Dhvani',20,'M')
INSERT INTO Emp VALUES ('Nehal',20,'M')
INSERT INTO Emp VALUES ('Sunil',95,'M')
INSERT INTO Emp VALUES ('Suvrendu',40,'M')
INSERT INTO Emp VALUES ('Bill',11,'S')
INSERT INTO Emp VALUES ('Ram',100,'S')
INSERT INTO Emp VALUES ('Nirmal',50,'S')
INSERT INTO Emp VALUES ('R',30,'S')

SELECT Dense_RANK() OVER (ORDER BY Age) AS [Rank by Age],
EmpName,
Age
FROM Emp

Rank by Age EmpName Age
-------------------- --------- -----------
1 Bill 11
2 Dhvani 20
2 Nehal 20
3 R 30
4 Abhinav 40
4 Suvrendu 40
5 Nirmal 50
6 Sunil 95
7 Ram 100

(9 row(s) affected)

SELECT Dense_RANK() OVER (PARTITION BY MaritalStatus ORDER BY Age) AS [Partition by MaritalStatus],
EmpName,
Age,
MaritalStatus
FROM emp

Partition by MaritalStatus EmpName Age MaritalStatus
-------------------------- --------- ----------- -------------
1 Dhvani 20 M
1 Nehal 20 M
2 Suvrendu 40 M
3 Sunil 95 M
1 Bill 11 S
2 R 30 S
3 Abhinav 40 S
4 Nirmal 50 S
5 Ram 100 S

(9 row(s) affected)



Ranking Function - Rank()

Rank()
The RANK function sequentially numbers a record set, but when two rows have the same order by value then they get the same ranking. Ranking value will be incremented by 1 for next un-matched row.

Syntax
RANK ( ) OVER ( [ ] )

Examples
CREATE TABLE Emp(
EmpName VARCHAR(9),
Age INT,
MaritalStatus char(1))

INSERT INTO Emp VALUES ('Abhinav',40,'S')
INSERT INTO Emp VALUES ('Dhvani',20,'M')
INSERT INTO Emp VALUES ('Nehal',20,'M')
INSERT INTO Emp VALUES ('Sunil',95,'M')
INSERT INTO Emp VALUES ('Suvrendu',40,'M')
INSERT INTO Emp VALUES ('Bill',11,'S')
INSERT INTO Emp VALUES ('Ram',100,'S')
INSERT INTO Emp VALUES ('Nirmal',50,'S')
INSERT INTO Emp VALUES ('R',30,'S')

Query - I

SELECT RANK() OVER (ORDER BY Age) AS [Rank by Age],
EmpName,
Age
FROM Emp

Rank by Age EmpName Age
-------------------- --------- -----------
1 Bill 11
2 Dhvani 20
2 Nehal 20
4 R 30
5 Abhinav 40
5 Suvrendu 40
7 Nirmal 50
8 Sunil 95
9 Ram 100

(9 row(s) affected)



SELECT RANK() OVER (PARTITION BY MaritalStatus ORDER BY Age) AS [Partition by MaritalStatus],
EmpName,
Age,
MaritalStatus
FROM emp

Partition by MaritalStatus EmpName Age MaritalStatus
-------------------------- --------- ----------- -------------
1 Dhvani 20 M
1 Nehal 20 M
3 Suvrendu 40 M
4 Sunil 95 M
1 Bill 11 S
2 R 30 S
3 Abhinav 40 S
4 Nirmal 50 S
5 Ram 100 S


Ranking Function - Row_Number

Ranking functions are functions that allow you to sequentially number your result set.

Syntax

ROW_NUMBER ( ) OVER ([ ] )

partition_by_clause is a column or set of columns used to determine the grouping in which the ROW_NUMBER function applies sequential numbering.

order_by_clause is a column or set of columns used to order the result set within the grouping.

Examples

CREATE TABLE Emp(
EmpName VARCHAR(9),
Age INT,
MaritalStatus char(1))

INSERT INTO Emp VALUES ('Abhinav',40,'S')
INSERT INTO Emp VALUES ('Dhvani',20,'M')
INSERT INTO Emp VALUES ('Nehal',20,'M')
INSERT INTO Emp VALUES ('Sunil',95,'M')
INSERT INTO Emp VALUES ('Bill',11,'S')
INSERT INTO Emp VALUES ('Ram',100,'S')
INSERT INTO Emp VALUES ('Nirmal',50,'S')


Sample - 1
SELECT ROW_NUMBER() OVER (ORDER BY Age) AS [Row Number by Age],
EmpName,
Age
FROM Emp
Sample - 2
SELECT ROW_NUMBER() OVER (ORDER BY Age desc) AS [Row Number by Age],
EmpName,
Age
FROM Emp


Row Number by Age EmpName Age
-------------------- --------- -----------
1 Bill 11
2 Dhvani 20
3 Nehal 20
4 Abhinav 40
5 Nirmal 50
6 Sunil 95
7 Ram 100

(7 row(s) affected)

Sample - 3
SELECT ROW_NUMBER() OVER (PARTITION BY MaritalStatus ORDER BY Age) AS [Partition by MaritalStatus],
EmpName,
Age,
MaritalStatus
FROM Emp

Partition by MaritalStatus EmpName Age MaritalStatus
-------------------------- --------- ----------- -------------
1 Dhvani 20 M
2 Nehal 20 M
3 Sunil 95 M
1 Bill 11 S
2 Abhinav 40 S
3 Nirmal 50 S
4 Ram 100 S

(7 row(s) affected)


xp_enumgroups Extended stored procedure

xp_enumgroups Extended stored procedure returns the list of Windows NT groups and their description. To get the list of the Windows NT groups, run:

Sample
EXEC master..xp_enumgroups

How to read error log using T-SQL?

xp_enumerrorlogs
This extended stored procedure returns the list of all error logs with their last change date and size. To get the list of error logs, run:



XP_ReadErrorLog: You can execute below query to read the error log. You can read the archive file by giving the number as per the output of xp_enumerrorlogs command.  If you will not specify any file number it will open the current error log file.

EXEC sys.xp_readerrorlog @p1

@p1 = file number you want to read.

How to check SQL Server Authentication Mode using T-SQL and SSMS?

Dear Readers,

Check Out my new article on MSSQLTips.com
Visit MSQLTips
Thanks,
Jugal Shah

Database Level Fixed Roles

DB Levek Fixed Roles	Rights and Description
db_accessadmin	Members can manage Windows groups and SQL Server logins.
db_backupoperator	Issue DBCC, CHECKPOINT, and BACKUP statements.
db_datareader	Select all data from any user table in the database
db_datawriter	Modify any data in any user table in the database
db_ddladmin	Issue all Data Definition Language (DDL) statements
db_denydatareader	Cannot select any data from any user table in the database
db_denydatawriter	Cannot modify any data in any user table in the database
db_owner	Has full permissions to the database
db_securityadmin	Members can modify role membership and manage permissions.

SQL Server Level Roles and Description

Server Level Roles	Rights
Bulkadmin	Can execute BULK INSERT statements
Dbcreator	Create, alter, and drop databases
Diskadmin	Manage disk files
Processadmin	Manage processes running in SQL Server
Securityadmin	Manage logins and CREATE DATABASE permissions, also read error logs and change passwords.
Serveradmin	Set server-wide configuration options, also shut down the server; alter endpoints
Setupadmin	Manage linked servers and startup procedures
Sysadmin	Can perform any activity in SQL Server

How can we remove Orphan User Instead of fixing it?

You can list of the list of orphan user by executing below query.

EXEC sp_change_users_login 'Report'

To remove/Delete Orphan User you can use below query.

exec sp_revokedbaccess 'DJ'

How to fix Orphan User in absense of login

Error
Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 131
Terminating this procedure. The Login name 'DJ' is absent or invalid

What is Orphaned User?
An Orphaned User in SQL Server is a database user for which a valid SQL Server Login is not available or it is wrongly defined with the different SID in the SQL Server instance, thereby not allowing the user to get connect to the database to perform activities.

Below scenarios are mostly responsible for Orphan Users
1. A SQL Server Login was accidentally dropped
2. A database is restored with a copy of database from another SQL Server Instance
3. SID of the login is different in sys.server_principals and sys.sysusers

Steps to re-produce issue
Step 1: Creating database SQLDBPool

USE [master]
GO

CREATE DATABASE [sqldbpool] ON PRIMARY
( NAME = N'sqldbpool', FILENAME = N'C:\sqldbpool.mdf' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'sqldbpool_log', FILENAME = N'C:\sqldbpool_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO

Step 2:
In this example I am creating user DJ using below script. You can also create user from GUI as well
Using GUI



[caption id="attachment_797" align="aligncenter" width="355" caption="Right Click On Security Node and Select New Login"][/caption]



Using Script

USE [master]
GO
CREATE LOGIN [DJ] WITH PASSWORD=N'pune@123',
DEFAULT_DATABASE=[master],
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF
GO

Step 3: I am assigning Data Reader and writer permission to Login DJ to database SQLDBPool

USE [sqldbpool]
GO
CREATE USER [DJ] FOR LOGIN [DJ]
GO
USE [sqldbpool]
GO
EXEC sp_addrolemember N'db_datawriter', N'DJ'
GO
USE [sqldbpool]
GO
EXEC sp_addrolemember N'db_denydatareader', N'DJ'
GO

Step 4: Dropping the login DJ and it will make the user DJ as orphan in SQLDBPool database

USE [master]
GO
DROP LOGIN [DJ]
GO

Steps to fix the issue
Step 1: You can use SP_CHANGE_USERS_LOGIN stored procedure to fix the Orphan user issue, for that you need the Database Owner permission on that database.

Syntax
sp_change_users_login [ @Action= ] 'action'
[ , [ @UserNamePattern= ] 'user' ]
[ , [ @LoginName= ] 'login' ]
[ , [ @Password= ] 'password' ]
[;]

Examples

EXEC sp_change_users_login 'Report' --Reports orphaned user
EXEC sp_change_users_login 'Auto_Fix', 'user' -- auto fix when SID mis-match issue
EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'

The stored procedure SP_CHANGE_USERS_LOGIN accepts arguments AUTO_FIX, REPORT or UPDATE_ONE as @Action parameter.

AUTO_FIX: If this value is used for @Action parameter, it will create a SQL Server Login if it was not present earlier and will synchronize the SQL Server Login with that of the Database User.We have to also provide the password here.

REPORT: It will display the list of all the Orphaned Users along with the SID (Security Identifiers) value within the current database which are not linked to a SQL Server Login.

UPDATE_ONE:will synchronize the specified database user with an existing SQL Server Login.

Step 2: As first step I am checking orphan user using Report parameter, it will show me the SID and orphan user name.

[caption id="attachment_798" align="aligncenter" width="334" caption="List of Orphaned User"][/caption]

Step 3: In this scenario we have dropped the SQL Login so it is required to create the login. We can create the login using below query specifying password and mapped it to orphan user

EXEC sp_change_users_login 'Auto_Fix', 'DJ', NULL, 'pune@123'

Step 4: Executing again the report query to list out orphan users.
EXEC sp_change_users_login 'Report'

Use below link to list out and fix the Orphan User issue for all the databases.

http://sqldbpool.com/2010/03/27/script-to-fix-and-list-out-orphan-users-on-all-the-databases-on-server/

 

Activity Monitor

In SQL Server 2008, Activity Monitor has below five sections namely Overview, Processes, Resource Waits, Data File I/O and Recent Expensive Queries.



Overview Section
This shows the graphical display of Processor Time (%), Number of Waiting Tasks, Database I/O (MB/Sec) and the Number of Batch Requests/second.

Processes: - section will show you the list of all the active users which are connected to SQL Server Database Engine. You can even KILL the sessions from here.

Resource Waits - Shows wait state information. It will help DBAs to identify potential bottlenecks with respect to Memory, CPU, Network I/O, Cache etc

Data File I/O - Shows I/O information for database data and log files. You can troubleshoot DISK IO related issues from here.

Recent Expensive Queries - Shows information about the most expensive queries. You can check the execution plan as well by just doing right click.

How to Open Activity Monitor from SSMS tool?

To start Activity Monitor in SQL Server 2008 SSMS, right-click on the SQL Server name you want to monitor.





From  SQL Server 2008 Management Studio’s toolbar, click Activity Monitor as shown in the snippet below.

How to check SQL Server Version Or Build No?

Option 1
EXEC master..sp_MSgetversion
Output
Character_Value
-------------------- ----------- -----------
10.0.2789.0 1 3

Option 2
select @@version
Output
Microsoft SQL Server 2008 (SP1) - 10.0.2789.0 (X64)
Jul 12 2010 19:21:29
Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

Option 3
SELECT SERVERPROPERTY('productversion')
Output Build Version
10.0.2789.0

Option 5
SELECT SERVERPROPERTY ('productlevel')
Output-Service Pack
SP1

Option 6
SELECT SERVERPROPERTY ('edition')
Output-Edition
Enterprise Edition (64-bit)

Operating System Error 112

Operating system error 112 describes the insufficient disk space on drive.

OS Error 112 occured while Backup
1. Delete the old backuo files on drive if not required
2. Archieve the old files to tape
3. Compress the old backup files to create space
4. Take the backup on other drive

OS Error 112 occured while Restore
1. Shrink the data and log file using DBCC ShrinkFile to reclaim space
2. Move un-necessary files to other drives (.Bakup or etc)

Step by Step DB Mail Configuration

[gallery orderby="ID"]

How to enable database mail feature in SQL Server 2005/2008?

You can enable database mail feature using Surface area configuration tool or using SP_Configure procedure.

use master
go
sp_configure 'show advanced options',1
go
reconfigure with override
go
sp_configure 'Database Mail XPs',1
--go
--sp_configure 'SQL Mail XPs',0
go
reconfigure
go


Using Surface Area Configuration
1. Select Surface area configuration for features



2. Go to database page and click on check box.

Different types of SQL Server Database Access mode

Different types of SQL Server Database Access mode, you can allow access to different users by setting database into below three modes.

Syntax
ALTER DATABASE DBNAME SET ACCESS MODE WITH ROLLBACK

OPTIONS
DB Access Modes
SINGLE_USER - Single user connection to the database
RESTRICTED_USER - Any number of users with db_owner or db creator or logins with sys admin rights can connect to the database
MULTI_USER - Any number of users with rights to the database can connect to the database

As you are changing the database access, you first need to take the exclusive connection database by dropping / rollback the existing connections.

You can specify below options to rollback the existing connections.

WITH ROLLBACK Options - Determines how the exclusive access to the database will take place
ROLLBACK AFTER integer [SECONDS] - Rollback the SPIDs after a particular number of seconds
ROLLBACK IMMEDIATE - Rollback the SPIDs immediately
NO_WAIT - If all of the SPIDs do not commit or rollback immediately the request to put the database in an exclusive state will fail

Examples

ALTER DATABASE DBName
SET RESTRICTED_USER WITH ROLLBACK AFTER 60 SECONDS

ALTER DATABASE DBName
SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE DBName
SET SINGLE_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE DBName SET MULTI_USER


Format Your Query Online

Dear Reader,

I would like to share good Query formating link. Please check it.

http://www.sqlinform.com/online.phtml

How to get the lists of System Stored Procedures in SQL Server?

Get the list of System Stored Procedure

SELECT * FROM SYS.ALL_OBJECTS WHERE type='P' and object_id < 0


You can get the list of CLR procedures using below query

SELECT NAME FROM SYS.ALL_OBJECTS WHERE type='PC'


Run a Stored Procedure when SQL Server starts

Steps to Run a Stored Procedure when SQL Server starts

As you all know, when SQL Server starts, it will first scan the registry to get the startup parameter values. By scaning registry it will find the master database files first and then make the master database online.

So if you want a procedure to execute when SQL Server starts automatically, you have to create that procedure in Master database only.

Step 1: Create a procedure in master database

Step 2: We have to use sp_procoption to set the stored procedure to execute when SQL Server service starts. Please see the example below.

EXEC sp_procoption 'procedure name', 'startup', 'true'

Step 3: Restart the SQL Service, to check the procedure output.

How turn off startup procedure?
You can turn-off the procedrue execution using below query.
EXEC sp_procoption 'procedure name', 'startup', 'false'

Master..spt_values system table

SPT_Values is un-documented table. It seems it is available from Sybase days and it is used in system stored procedures to translate codes into readable strings.
It stores the specific number of each SQL Server Property. If you execute below query, you can get the diffrent kind of result and you can use it as per the need.

select min(number) FROM master..spt_values
--Output
-32768

select min(number) FROM master..spt_values where number > 0
-- Output
1
select max(number) FROM master..spt_values
-- Output
1469283328


You can use to generate number from 1 to 2048 as well.

SELECT distinct number
FROM master..spt_values
WHERE number BETWEEN 10 AND 15


Unable to change autogrowth properties of SQL Server of log file

Problem
People often complains that they are unable to change the Log File Growth to un-restricted.

Solution
Whenever we are chaging max size as unlimited for log file, SQL Server reset the Max Size avalue to 2TB and this is the max size supported for log file. This is by design only.

Please check the below image for more detail.

Download SQL Server 2008 R2 System Views Map

Download Link

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=65b81dce-db80-43f3-90e8-0753c751eaa7

LOGINPROPERTY Built-In function

SQL Server 2005/2008 built-in function LOGINPROPERTY can help us retrieve the important information of login properties.

We can retrieve the below important details using LOGINPROPERTY function.
1. Date of last password change
2. Login is locked or not
3. Loign password expired
4. Need to change password at next login or not
5. Count of consecutive failed login attempts
6. DateTime when the last login failed
7. DateTime when the login was locked out
8. Password hash
9. Returns the number of days until the password expires. LOGINPROPERTY('sa', 'DaysUntilExpiration') argument is only available with SQL Server 2008

SELECT
name,
CASE LOGINPROPERTY(name, 'IsLocked')
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
ELSE 'Unknown'
END as IsAccountLocked,
LOGINPROPERTY(name, 'PasswordLastSetTime') as PasswordLastSetDate,
LOGINPROPERTY(name, 'BadPasswordCount') as CountOfFailedLoginAttempts,
LOGINPROPERTY(name, 'BadPasswordTime') as LastFailedLoginTime,
LOGINPROPERTY(name, 'LockoutTime') as LoginLockedOutDateTime,
CASE LOGINPROPERTY(name, 'IsExpired')
WHEN 0 THEN 'Password is not expired'
WHEN 1 THEN 'Password is not expired,change it'
ELSE 'Unknown'
END as PasswordExpired,
CASE LOGINPROPERTY(name, 'IsMustChange')
WHEN 0 THEN 'Must not change password at next login'
WHEN 1 THEN 'Must change password at next login'
ELSE 'Unknown'
END as PasswordChangeOnNextLogin,
LOGINPROPERTY(name, 'DaysUntilExpiration') as 'DaysUntilthePasswordExpires', --New Property in SQL Server 2008
LOGINPROPERTY(name, 'PasswordHash') as PasswordHash
From sys.sql_logins
order by name


Output

How to get data of SQL Server PerfMon Counters without running Performance Monitor tool?

Execute the query againts sys.dm_os_performance_counters DMV to get the result.

SELECT * FROM sys.dm_os_performance_counters


[caption id="attachment_720" align="aligncenter" width="560" caption="sys.dm_os_performance_counters output"][/caption]

Make SQL Server DB Read Only/Read-Write Only

Read Only
You can add the database in readonly mode by using below query. You can not add, update or delete any records once database is in read only mode.

ALTER DATABASE SQLDBPOOL READ_ONLY
GO

READ_WRITE
You can set the database again in to Read/Write mode using below query.

ALTER DATABASE SQLDBPOOL READ_WRITE
GO


MSQL_XP Wait type

MSQL_XP
This kind of wait type occurs when a task is waiting for an extended stored procedure to end. SQL Server uses this wait state to detect potential MARS application deadlocks. The wait stops when the extended stored procedure call ends. In simple word SQL Server lost the control of extended stored procedure.

Few days back LiteSpeed backup job are running longer than normal, when I checked the process status using Activity Monitor, it shows processes are running but in MSQL_XIP wait state.

[caption id="attachment_713" align="aligncenter" width="600" caption="Activity Monitor"][/caption]

I tried to KILL the backup sessions and it gone into KILLED\RollBack state forever.

You can either reboot the server to free/terminate all the session or there is another way that you can find the SQL Server Backup Process associated Operating System Id.

You can follow the below steps to find out SPID related KPID

Step 1: Execute SP_WHO2 active command, you will SPID related KPID which is OS Process ID.
OR
Step 1: Check for DMVs to get the OS Process ID

Step 2: To get the more information regarding the OS Process download the Process Explorer from the below link.

http://technet.microsoft.com/en-us/sysinternals/bb896653.aspx

Step 3: Once confirmed you can kill OS process using Task Manager.

Error: The INSERT statement conflicted with the FOREIGN KEY constraint

Error Description
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "foreignKey_child". The conflict occurred in database "jugal_1", table "dbo.parent", column 'parentid'.
The statement has been terminated.

Solution: Error message is pretty clear, it means the value you are inserting into child table is not exist in the parent table. There could be number of reason for i.e value not exist in parnet table, you have added value in the table but yet it is not commited, you are updating the foreign key column.

Script to reproduce issue
create table parent
(
parentid int constraint primaryKey_parent primary key,
name varchar(10)
)

create table child
(
childid int constraint foreignKey_child foreign key references parent(parentid),
name varchar(10)
)

insert into child values(10,'jugal')

Script to list out all DMVs and DMFs of SQL Server


-- To check the diffrent kind of system objects
SELECT Distinct type_desc
FROM sys.system_objects

-- To get the list of DMVs or DMFs
SELECT name, type, type_desc,SCHEMA_NAME(schema_id) as SNAME
FROM sys.system_objects
WHERE name LIKE 'dm[_]%'
ORDER BY name


How to generate script using SSMS GUI?

You can generate script of GUI functionality by clicking below high-lighted option in the image.

It will give you all the below options.

How to rename SA account in SQL Server

You can use below script to rename SA account. Script will work with SQL Server 2005 and higer versions.

 ALTER LOGIN sa Disable;
ALTER LOGIN sa WITH NAME = [Jugal];


New DMF for SQL Server 2008 sys.dm_fts_parser to parse a string

Check my new article on MSSQLTips

http://www.mssqltips.com/tip.asp?tip=2175

SP to recycle SQL Server and Agent Error Log file

To limit the size of the SQL Server error log, the sp_cycle_errorlog system stored procedure can be issued to start a new error log. 

To recycle SQL Server Agent log you can execute sp_cycle_agent_errorlog system stored procedure.

Issues with Using Run As to Start SQL Server Management Studio with a Different Login

http://www.mssqltips.com/tip.asp?tip=2164

Download SQL Server 2011 code name Denali

Click on below link to download Denali

http://www.microsoft.com/sqlserver/en/us/product-info/future-editions.aspx

How to check database ISOLATION Level?

You can use below query to check the database isolation level.

DBCC UserOptions

SQL Server Version and Code Name

SQL Server Version	Code Name
SQL Server 6.0	SQL95
SQL Server 6.5	Hydra
SQL Server 7.0	Sphinx
SQL Server 7.0 OLAP	Plato
SQL Server 2000 32-bit	Shiloh
SQL Server 2000 64-bit	Liberty
SQL Server 2005	Yukon
SQL Server 2008	Katmai
SQL Server 2008 R2	Kilimanjaro
SQL Server 2011	Denali

Find the create and modified date of Database Objects

Query

SELECT name, create_date, modify_date
FROM sys.objects

Peer-to-peer transactional replication and conflict

Peer-to-peer transactional replication
Peer-to-peer transactional replication allows you insert, update, or delete data at any node in a topology and have data changes propagated to the other nodes.

Because we can change data at any node, data changes at different nodes could conflict with each other.

In SQL Server 2008, peer-to-peer replication introduces the option to enable conflict detection across a peer-to-peer topology. This option helps us prevent the issues that are caused by undetected conflicts.

When we enable conflict detection, a conflicting change is considered a critical error that causes the Distribution Agent to fail. In the event of a conflict, the topology remains in an inconsistent state until the conflict is resolved and the data is made consistent across the topology.

To use conflict detection, all nodes must be running SQL Server 2008 or a later version, and detection must be enabled for all nodes. You can enable and disable detection in Management Studio either by using the Subscription Options page of the Publication Properties dialog box or the Configure Topology page of the Configure Peer-to-Peer Topology Wizard.

You can also enable and disable conflict detection by using the sp_addpublication or sp_configure_peerconflictdetection stored procedures.
Syntax
sp_configure_peerconflictdetection [ @publication= ] 'publication'
[ , [ @action= ] 'action']
[ , [ @originator_id= ] originator_id ]
[ , [ @conflict_retention= ] conflict_retention ]
[ , [ @continue_onconflict= ] 'continue_onconflict']
[ , [ @local= ] 'local']
[ , [ @timeout= ] timeout ]

sp_help_peerconflictdetection stored procedure returns information about the conflict detection settings for a publication that is involved in a peer-to-peer transactional replication
topology.

Happy Independence Day (I Love my country India)

[caption id="attachment_676" align="aligncenter" width="600" caption="Happy Independence Day"][/caption]

Unable to start execution of step (reason: The PowerShell subsystem failed to load [see the SQLAGENT.OUT file for details];The job has been suspended). The step failed.

Problem:
Unable to start execution of step 3 (reason: The PowerShell subsystem failed to load [see the SQLAGENT.OUT file for details];The job has been suspended). The step failed.

Cause of the failure: This is due to invalid location of SQLPS.exe file

Solution:

1. We can use the below script check the location of SQLPS.exe file.
[sourcecode language="sql"]
SELECT * FROM msdb.dbo.syssubsystems WHERE start_entry_point ='PowerShellStart'
[/sourcecode]
2. Go to the server and check whether the file ‘SQLPS.exe’ is located in the path as per step1.

3. There may be chance that the path is different on Node1 and Node2.

4. Find out the correct path of the SQLPS.exe and re-configure it using below command
[sourcecode language="sql"]
Use msdb
go
sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
[/sourcecode]
/* Update to correct path
For example correct path is “D:\Server_apps\x86\MSSQL\100\Tools\Binn\SQLPS.exe” Than change it accordingly*/

[sourcecode language="sql"]
UPDATE msdb.dbo.syssubsystems SET agent_exe='E:\Server_apps\x86\MSSQL\100\Tools\Binn\SQLPS.exe' WHERE start_entry_point ='PowerShellStart'

sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE
[/sourcecode]
5. Confirm that SQLPS.exe file path has changed by running the below script once again
[sourcecode language="sql"]
SELECT * FROM msdb.dbo.syssubsystems WHERE start_entry_point ='PowerShellStart'
[/sourcecode]
6. Restart the respective SQL agent service. For the cluster server you have to use cluster administrator to restart the service.

7. Re - run the job.

SQL Server Upgrade Startegies

An upgrade is any kind of transition from SQL Server 2000 or SQL Server 2005 to SQL Server 2008.

Upgradation Startegies

In-place upgrade: Using the SQL Server 2008 Setup program to directly upgrade an instance of SQL Server 2000 or SQL Server 2005 to SQL Server 2008. The older instance of SQL Server is replaced.

• Side-by-side upgrade: Using steps to move all or some data from an instance of SQL Server 2000 or SQL Server 2005 to a separate instance of SQL Server 2008. There are two types of side-by-side upgrade strategy:
  
  
  
  • One server: The new instance exists on the same server as the target instance.
  
  
  • Two servers: The new instance exists on a different server than the target instance.
  
  
  
  

MVP Award Kit

[caption id="attachment_663" align="aligncenter" width="600" caption="Most Valueable Professional Award"][/caption]

Resource System Database

The system objects are physically stored in the resource database, they are logically presented as the sys schema in each database.

The following code returns the build number of the resource database:
SELECT SERVERPROPERTY('ResourceVersion')

To return the date and time the resource database was last updated, the following code can be executed:
SELECT SERVERPROPERTY('ResourceLastUpdateDateTime')

Dedicated Administrator Connection (DAC)

SQLCMD is particularly useful for creating batch scripting jobs for administrative purposes. SQLCMD has replace OSQL. It is using OLEDB to connect the SQL Server. However, as an emergency utility to diagnose and hopefully correct server problems, it has no peer. By Using the –A argument, the SQLCMD utilizes an exclusive connection to SQL Server. If no other connection is possible, the SQLCMD –A command is the last and best hope for diagnosing server problems and preventing data loss. By default, only local DACs are allowed because the DAC components only listen on the loopback connection. However, remote DACs can be enabled using the sp_configure stored procedure by changing the remote admin connections option to true, as the following code illustrates:

sp_configure ‘remote admin connections’, 1
RECONFIGURE

SQL Server Full Backup Internally

Q. What are the steps SQL Server performs internally at the time of FULL backup?
Ans. SQL Server follow the below steps once you execute the BACKUP command
1.Backup Process will lock the database and block all the transaction
2.Place a mark in the transaction log
3.Release the database lock
4.Extract all the pages in the data files and write them to the backup device
5.Lock the database and block all the transactions
6.Place a mark in the transaction log
7.Release the database lock
8.Extract the portion of the log between the marks and append it to backup

Q. Which operations do not allowed during the full backup?
1.Adding and removing database files
2.Shrinking the database

Monitor, Start and Stop services using xp_servicecontrol

Please read the below article for more detail.

http://www.mssqltips.com/tip.asp?tip=2036

Split-Brain/tiebreaker in Cluster

HA clusters usually use a heartbeat private network connection which is used to monitor the health and status of each node in the cluster. One difficult, but serious condition every clustering software must be able to handle is split-brain. Split-brain occurs when all of the private links go down simultaneously, but the cluster nodes are still running. If that happens, each node in the cluster may mistakenly decide that every other node has gone down and attempt to start services that other nodes are still running. Having duplicate instances of services may cause data corruption on the shared storage.

It's also used as a tiebreaker when nodes can no longer communicate (that is, are "split-brain"). When it cannot communicate with the nodes, Cluster Service cannot really detect the problem: It's possible that the nodes are dead, but it may also be possible that just the communication links are. In this situation, to prevent each node from thinking that it is the sole survivor and bringing your database online, they go into arbitration, using the quorum resource.

The node that owns the quorum resource puts a reservation on the device every three seconds; this guarantees that the second node cannot write to the quorum resource. When the second node determines that it cannot communicate with the quorum-owning node and wants to grab the quorum, it first puts a reset on the bus.

The reset breaks the reservation, waits for about 10 seconds to give the first node time to renew its reservation at least twice, and then tries to put a reservation on the quorum for the second node. If the second node's reservation succeeds, it means that the first node failed to renew the reservation. And the only reason for the failure to renew is because the node is dead. At this point, the second node can take over the quorum resource and restart all the resources.

Reference: http://technet.microsoft.com/en-us/library/bb742593.aspx

Sign Up Procedure for Interview Agent

Interview Agent

[caption id="attachment_616" align="aligncenter" width="600" caption="Call on +91-9881710142 for more information"][/caption]

SQLDBPool has now its own domain

Dear Readers,

Now you dont have to write the long http://sqldbpool.wordpress.com/ URL, We have now our own domain. You can now check your favourite articles by just typing http://sqldbpool.com/

Thank You

Best Regards,
Jugal Shah
jugal.shah@sqldbpool.com

Error: 1101, Severity: 17, State: 2.

Error: 1101, Severity: 17, State: 2.
Could not allocate a new page for database 'tempdb' because of insufficient disk space in filegroup 'PRIMARY'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

We have configured the tempdb on J:\ which has only 10GB space. TempDB has occupied the 10GB space. We have tried to shrink the tempdb but no luck, after sometime we are not able to even right click the TempDB and neither execute the some system stored procedures (sp_who2)

Solution 1
If you don’t want to restart the SQL Server you can add the another data file to different drive in tempdb

ALTER DATABASE tempdb
ADD FILE
(
NAME = tempdev1,
FILENAME = 'E:\tempdb1.mdf',
SIZE = 5MB,
MAXSIZE = 5000MB,
FILEGROWTH = 10MB
) TO FILEGROUP [PRIMARY];
GO

Solution 2
If the disk space is available on the drive where tempdb residing you can change file MAX SIZE option to allocate more space.

Solution 3
Restart the SQL Services, it will create the new instance of tempdb

Solution 4
For user databases with restricted growth you can use the AUTO GROWTH option true or increase the file max size

Note: You can't move the tempdb files, if you execute the ALTER command to move the tempdb files, it will just mark the file to diffrent drive and will create the file the target drive on SQL Server restart only

How to move data and log file using Alter statement

You can use Alter command to move data and log file.

Sample Script

-- Moving data file to E:\ drive
ALTER DATABASE tempdb
MODIFY FILE (Name = tempdev,FILENAME = 'E:\tempdb.mdf')
-- Moving log file to E:\ drive
ALTER DATABASE tempdb
MODIFY FILE (Name = templog,FILENAME = 'E:\templog.ldf')


Shrink SQL Server 2000 Database

Query to generate DBCC shrinkfile script for all the user databases in SQL Server 2000


select 'use ' + ltrim(rtrim(db_name(sd.dbid))) + char(13) + 'dbcc shrinkfile (' + quotename(ltrim(rtrim(sf.name)),'''') + ' ,truncateonly)' from sysaltfiles sf
inner join sysdatabases sd on sf.dbid = sd.dbid
where sd.dbid > 4

Remote Desktop Error - The terminal server has exceeded the maximum number of allowed connections

You can visit my article on below link.

http://www.mssqltips.com/tip.asp?tip=1981

How to check authentication scheme in SQL 2005/SQL 2008?

You can use below query to check authentication scheme whether it is Kerberos or NTLM.

select auth_scheme from sys.dm_exec_connections where session_id=@@spid

MVP Award

Dear Readers,

With your support and comments, I am announced as MVP
http://blogs.technet.com/southasiamvp/archive/2010/04/01/new-mvps-announced-april-2010.aspx



Thank You,
Jugal Shah

how to delete temporary files (*.tmp) and bak files (*.bak)

You can use below commands to delete temporary files and .BAK files from all the folders and sub-folders

To delete .tmp files
del *.tmp/f/s

To delete .bak files
del *.bak/f/s

SQL Server and Protocols

TCP/IP and Named Pipes
By default, clients have TCP and Named Pipes as available protocols on most of client computer. You can manipulate the protocol ordering by using the SQL Server Client utility. The client application uses the protocols in the order specified on the client computer. If you are using SQL Server 2005, the protocol order is stored in the ProtocolOrder registry entry under the following registry subkey:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI9.0

IPX/SPX
SQL Server 2005 does not support IPX/SPX. The newer versions of NetWare, such as NetWare 6.5, support TCP/IP which makes it the common protocol for all clients.

VIA
The Virtual Interface Adapter (VIA) can be used only by VIA hardware.

Shared Memory
Shared Memory can only be used on the local computer and cannot be used as a network protocol.

Reference: Microsoft SQL Server 2005 Books Online (2006), Index: client connections [SQL Server], about client network connections, choosing a network protocol

Number of articles and readers

Dear Readers,

With your help and support SQLDBPOOL has completed 150 technical articles and the readers stats reached 2,00,000 +, so please keep reading, post your comments, feedbacks and questions.





Thank you for your support.

Jugal Shah

shutdown -i dos command to reboot/shutdown server remotely

[caption id="attachment_556" align="aligncenter" width="300" caption="shutdown -i command to reboot/shutdown server remotely"][/caption]

Default Startup Parameters - SQL Server

Join SQLDBPool Community on dotNetSpider

http://www.dotnetspider.com/sites/1332/-SQLDBPool.aspx

Trace Flag 3226

I have posted this article on DotNetSpider.com Please visit below link for more information.

http://www.dotnetspider.com/resources/37294-Trace-Flag.aspx

Key Points in Disaster Recovery

I have posted this article on DotNetSpider.com. Please follow link.

http://www.dotnetspider.com/resources/37324-Key-Points-Disaster-Recovery.aspx

Script to fix and list out Orphan Users on all the databases on server


USE MASTER
GO
SET NoCount ON
DECLARE @VarDbId INT,
@SQL nvarchar(4000),
@VDBName nvarchar(260),
@OUCounter INT,
@Max_OUCounter INT
SELECT @VarDbId=4,
@SQL =''
CREATE TABLE #OrphaneUsers
(
ID INT IDENTITY (1,1) NOT NULL,
DBName VARCHAR(125) NULL ,
UserName sysname NULL ,
UserSID VARBINARY(85) NULL ,
LoginExists bit NULL
)
WHILE EXISTS
(SELECT database_id
FROM sys.databases
WHERE database_id>@VarDbId
AND state_desc ='ONLINE'
)
BEGIN
SELECT TOP 1
@SQL ='Create table #OrphaneUser

(UserName sysname null,

UserSID varbinary(85) null )

insert into #OrphaneUser exec ' + name+ '.dbo.sp_change_users_login ''report''

insert into #OrphaneUsers(DBName,UserName,UserSID,LoginExists) select '''+ name+''' as[dbname], UserName, UserSID,0 from #OrphaneUser

drop Table #OrphaneUser',
@VDBName=name
FROM sys.databases
WHERE database_id>@VarDbId
AND state_desc ='ONLINE'
ORDER BY database_id
EXEC SP_Executesql @SQL
SELECT TOP 1
@VarDbId=database_id
FROM sys.databases
WHERE database_id>@VarDbId
AND state_desc ='ONLINE'
END
UPDATE #OrphaneUsers
SET LoginExists=1
FROM #OrphaneUsers
JOIN syslogins
ON #OrphaneUsers.UserName=syslogins.NAME
SELECT @OUCounter =0,
@Max_OUCounter =COUNT(0)
FROM #OrphaneUsers
WHERE LoginExists=1
WHILE EXISTS
(SELECT TOP 1
id
FROM #OrphaneUsers
WHERE LoginExists=1
AND id >@OUCounter
)
BEGIN
SELECT TOP 1
@OUCounter=id
FROM #OrphaneUsers
WHERE LoginExists=1
AND id >@OUCounter
SELECT @SQL ='EXEC '+DBName+'.dbo.sp_change_users_login ''Auto_Fix'', '''+UserName+''', NULL, '''+UserName+''''
FROM #OrphaneUsers
WHERE LoginExists=1
AND id =@OUCounter
EXEC SP_Executesql @SQL
PRINT @SQL
END
SELECT *
FROM #OrphaneUsers
DROP TABLE #OrphaneUsers


RAID Levels

RAID Levels
RAID 5:-  RAID 5 will provide good performance throughput and prevent data loss if there is a disk failure. For the database which has high read/write activity, RAID 5 will be a better choice when compared to RAID 0 and RAID 1 in terms of data availability and data protection. For an ideal SQL Server configuration, you should have a combination of RAID 1 and RAID 5. You should mirror the operating system using RAID 1, and place transaction logs on a RAID 1 that is separate from the RAID 1 that hosts the operating system. SQL Server writes data to the transaction logs and maintains serial information of all modifications that occurred in a SQL database. The transaction log files can be used for rollback and roll forward of information from a SQL Server database. The SQL Server files and filegroups should be placed on a RAID 5, because you get best performance throughput by placing database files on RAID 5 disk array.

RAID 10 RAID 10 is a better choice than RAID 5, but RAID 10 would be more expensive than RAID 5. RAID 10 is a combination of RAID 0+1, which is known as striping with mirroring. You should first mirror disks and then create a stripe set of mirrored disks. This provides high fault tolerance for data and excellent throughput performance.

RAID 1 RAID 1 is known as disk mirroring. You need a minimum of two disks to form a RAID 1 array. One primary disk is used for read/write operations and the data is replicated to the second disk. This RAID level offers better read performance but slower write performance.

RAID 0 RAID 0 is known as disk striping. This RAID level stripes data across disks in the array, offering better throughput on the read/write operations. However, there is no data protection offered in this RAID level. If one disk fails, the data stored on the disk will be lost.

Enabling Filestream to store compress documents

Problem: If you are in the process of enabling FILESTREAM on volume and you also want to ensure that documents will be stored in the compressed form but during that time you notice that volume F:\ drive is not formatted. You need to make sure that the volume will be able to store documents in a compressed form.  You can fix the issue by using below command.

Command to format F:\
format F: /FS:NTFS /V:MYBLOBContainer/A:4096 /C

The above command will format the volume, convert it to NTFS and then label it as MYBLOBContainer. It will also set the NTFS cluster size to 4096. It is important to note that the cluster size must be 4096 or smaller for compression to be enabled for a volume. And at last, the /c parameter will compress the volume. When you are storing the FILESTREAM data, you can store it on a compressed or a non-compressed volume. The data in FILESTREAM is actually stored on the file system. There are a number of benefits to storing the data on the file system. For instance, you will be able to compress data with the NTFS compression capabilities. When the data is stored, it is in compressed form and when the data is retrieved, it is decompressed.

Max Degree of Parallelism and MAXDOP

Max Degree of Parallelism
If you do not want to use all of the installed processors in your SQL Server operations, you can limit the use of processors by configuring the processor value to a lower number than the actual number of processors installed. For instance, if a server has four processors and you want to use only three for parallelism, then you should set the max degree of parallelism option to 3.

The default option, 0, uses the actual number of processors installed in the system for the parallel execution plans of queries and index operations.

sp_configure 'max degree of parallelism', 6;
GO
RECONFIGURE WITH OVERRIDE;
GO

MAXDOP
When you want to use a specific number of processors for an individual query or index operation, you can use the MAXDOP query hint or index option to provide better control. MAXDOP overrides the settings defined in the max degree of parallelism option. The use of MAXDOP is limited to the execution of a query that contains the MAXDOP query hint or index option.

Use below extended procedure to get the number of processors.
EXECUTE xp_msver 'ProcessorCount'

Example
SELECT
database_id AS databaseID
, QUOTENAME(DB_NAME(database_id)) AS 'databaseName'
, [OBJECT_ID] AS objectID
, index_id AS indexID
, partition_number AS partitionNumber
, avg_fragmentation_in_percent AS fragmentation
, page_count
, 0 AS 'defragStatus' /* 0 = unprocessed, 1 = processed */
FROM sys.dm_db_index_physical_stats (@databaseID, OBJECT_ID(@tableName), Null , Null, @scanMode)
WHERE avg_fragmentation_in_percent >= 30
And index_id > 0
And page_count > 8 -- ignore objects with less than 1 extent
And index_level = 0 -- leaf-level nodes only, supports @scanMode
OPTION (MaxDop 2);

Join Me on dotnetSpider.com

Dear Readers

Hope you are doing well and enjoying all these database articles. I am also helping the user community on dotnetSpider.com

I am requesting you to join  http://www.dotnetspider.com, which is a very good site for knowledge sharing. The most attractive feature of the site is, it has a revenue sharing program in association with Google. When you post any content or share your knowledge through various sections like Discussion Forums, 90% of the revenue generated from Google Advertisement in those pages are shared with the authors.

Since the revenue sharing program is approved by Google and the revenue is paid to members directly by Google when it reaches certain minimum limit, this is a very reliable program to earn some pocket money during your free time.

If you plan to join this site, please use my user id (tojugalshah123) as the referrer so I will be added in your buddy list

regards,
Jugal Shah

SQL Server 2008 R2 Editions and Key Features

The release of SQL Server is officially called SQL Server 2008 R2, which replaces the code name SQL Server Kilimanjaro. 

SQL Server 2008 R2 edition will come into below different flavors
 

1. Data Center Edition


2. Parallel Data Warehouse


3. Enterprise Edition


4. Standard Edition

 

List of Key features
 
Data Center Edition

• Application and Multi-Server Management for enrolling, gaining insights and managing over 25 instances, During the airlift event Microsoft Program Manager mentioned that  they have tested this feature by adding more than 200 servers


• Highest virtualization support for maximum ROI on consolidation and virtualization


• High-scale complex event processing with SQL Server StreamInsight


• Supports more than 8 processors and up to 256 logical processors for highest levels of scale


• Supports memory limits up to OS maximum

 
Parallel Data Warehouse

• 10s to 100s TBs enabled by massively parallel processing architecture and compatibility with hardware partners


• Advanced data warehousing capabilities like Star Join Queries and Change Data Capture


• Integration with SSIS, SSRS, and SSAS


• Supports industry standard data warehousing hub and spoke architecture and parallel database copy

 
Enterprise Edition

• PowerPivot for SharePoint to support the hosting and management of PowerPivot applications in SharePoint


• Application and Multi-Server Management for enrolling, gaining insights and managing up to 25 instances (CPU and Storage). Microsoft is planning to cover Memory in the next release


• Master Data Services for data consistency across heterogeneous systems


• Data Compression now enabled with UCS-2 Unicode support

Standard Edition

• Backup Compression to reduce data backups by up to 60% and help reduce time spent on backups


• Managed instance for Application and Multi-Server Management capabilities

Reference: Microsoft Books Online and SQL Server 2008 R2 Airlift Event

Pictures of my Kerala Visit

Download SQL Server 2008 R2 CTP

Dear Readers,

You can download the SQL Server 2008 R2 CTP version from the below link

http://www.microsoft.com/sqlserver/2008/en/us/R2Downloads.aspx

Regards,
Jugal Shah

SQL Server 2008 R2 Airlift and Report Builder 3.0

In SQL Server 2008 Airlift there was small session on Report Builder 3.0 Microsoft Report builder is has below features and it is also integrated with the Share Point

List of features included in Report Builder 3.0

I will walk you through the below features shortly

• Shared Datasets
• SharePoint List Data Extension
• Configuring a Map Layer
• Calculating Aggregates of Aggregates
• Enhanced Support for Expressions
• Overall Page Number and Overall Total Pages
• Page Name
• Render Format
• Rotating Text 270 Degrees (Property)
• Naming Excel Worksheet Tabs while export
• Rendering Reports to Data Feeds
• Writable Variables
• Cached data
• Drill down reports

SQL Server 2008 R2 Airlift Event

Dear Readers,

During my Seattle visit I got a chance to attend the Microsoft SQL Server 2008 R2 Airlift event and it has covers the below topic.

The event was fantastic and very knowledgeable. I would like to thank all the Microsoft Program Managers who has given their best to us.

Event has captured the below topics and I will do my best to share the SQL Server 2008 R2 and New feataures.

1. SQL Server 2008 R2 Application and Multi Server Management
2. Managed Self Service Business Intelligence (Reporting Services)
3. Managed Self Service Business Intelligence (Analysis and PowerPivot)
4. Managed Self Service Business Intelligence (Microsoft Office and SharePoint)
5. SQL Server 2008 R2 Master Data Management

Script to Unlock SQL User account

Easy way without passing Password to unlock SQL user account is just check/unchecked Password policy check box.

USE [master]
GO
ALTER LOGIN [Jugal] WITH CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO
ALTER LOGIN [Jugal] WITH CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO


Insert Extended Characters into a SQL Server Table Using OSQL and T-SQL

Dear Readers,

You can read my artilce on MSQLTips.com. Please follow below link.

http://www.mssqltips.com/tip.asp?tip=1953

Thank You,
Jugal Shah

Database Migration Checklist

Dear Readers,

You can read my artilce on MSQLTips.com. Please follow below link.

http://www.mssqltips.com/tip.asp?tip=1936

Thank You,
Jugal Shah

An Apple in the Bag

Dear Readers,

Good Story, please read it.

A teacher teaching Maths to seven-year-old Arnav asked him, "If I give you one apple and one apple and one apple, how many apples will you have?"Within a few seconds Arnav replied confidently, "Four!"

The dismayed teacher was expecting an effortless correct answer (three).  She was disappointed.  "Maybe the child did not listen properly," she thought.  She repeated, "Arnav, listen carefully.  If I give you one apple and one apple and one apple, how many apples will you have?"

Arnav had seen the disappointment on his teacher's face.  He calculated again on his fingers.  But within him he was also searching for the answer that will make the teacher happy.  His search for the answer was not for the correct one, but the one that will make his teacher happy.  This time hesitatingly he replied, "Four…"

The disappointment stayed on the teacher's face.  She remembered that Arnav liked strawberries.  She thought maybe he doesn't like apples and that is making him loose focus.  This time with an exaggerated excitement and twinkling in her eyes she asked, "If I give you one strawberry and one strawberry and one strawberry, then how many you will have?"

Seeing the teacher happy, young Arnav calculated on his fingers again.  There was no pressure on him, but a little on the teacher.  She wanted her new approach to succeed.  With a hesitating smile young Arnav enquired, "Three?"

The teacher now had a victorious smile.  Her approach had succeeded.  She wanted to congratulate herself.  But one last thing remained.  Once again she asked him, "Now if I give you one apple and one apple and one more apple how many will you have?"

Promptly Arnav answered, "Four!"

The teacher was aghast.  "How Arnav, how?" she demanded in a little stern and irritated voice.

In a voice that was low and hesitating young Arnav replied, "Because I already have one apple in my bag."

"When someone gives you an answer that is different from what you expect don't think they are wrong.  There may be an angle that you have not understood at all. You will have to listen and understand, but never listen with your mind already made up."

Create A Love Account

Mother Teresa once said, "There are no great acts. There are only small acts done with great love." What small acts can you do today to deepen the bonds between you and the people you value the most? What random acts of kindness and senseless acts of beauty can you offer to someone in an effort to make his or her day just a little better? The irony of being more compassionate is that the very act of giving to others makes you feel better as well.

To practice being more loving, create a love account. Each day, make a few deposits in this very special reserve by doing something small to add joy to the life of someone around you. Buying your partner fresh cut flowers for no reason at all, sending your best friend a copy of your favorite book or taking the time to tell your children in no uncertain terms how you feel about them are good places to start.

If there is one thing that I have learned in life, it is that the little things are the big things. Those tiny, daily deposits into the love account will give you far more happiness than any amount of money in your bank account. As Emerson said so eloquently, "Without the rich heart, wealth is an ugly beggar." Or as Tolstoy wrote, "The means to gain happiness is to throw out for oneself, like a spider, in all directions an adhesive web of love, and to catch in all that comes."

From Author's desk

Dear Readers,

First of thank you very much for reading this blog

I hope this blog will help you in clearing your interviews, daily database issues and will enhance your knowledge.

As you aware WORDPRESS is not supporting Google Ads until you purchase its domain rights due to security reasons.

To earn some money by using Google Ads I have started blogging on BLOGSPOT.COM and the URL of my new blog is as below. I believe I will get the same kind of support and traffic on this blog as well.

http://dbsconsultant.blogspot.com/

Thank You,
Jugal Shah

SQL Server 2008 Interview Questions - I

Which types of compression supported by SQL Server 2008?
SQL Server 2008 supports two types of compression namely as below

• Database Backup Compression


• Data Compression

Explain Database Backup Compression
SQL Server 2008 introduces a new feature called Database Backup Compression (DBC). This feature allows DBA’s to compress SQL Server 2008 database backups natively rather than taking uncompressed native SQL Server database backups or using a third-party tool. By default, database backup compression feature is turned off in the SQL Server 2008.
Using the Database Backup Compression feature, we can perform Full, Differential and Transactional log compressed backups.

Which SQL Server editions supports database backup compression feature?
Currently this feature is only available in the Enterprise Edition of SQL Server 2008. However, all editions of SQL Server 2008 allow the restoration of compressed database backup.

What are the pre-requisites for using database backup compression feature?

1. SQL Server 2008 Enterprise edition


2. Enable database compression at server level


3. User WITH COMPRESSION clause while taking FULL, DIFFRENTIAL and LOG backup

How to enable database backup compression feature at server level?
You can use below TSQL code to enable the DBC at server level.
USE MASTER
GO
EXEC sp_configure 'backup compression default', '1'
GO
RECONFIGURE WITH OVERRIDE
GO

SP_Who Process status

Status	Description
dormant	SQL Server is resetting the session.
running	The session is running one or more batches. When Multiple Active Result Sets (MARS) is enabled, a session can run multiple batches.
background	The session is running a background task, such as deadlock detection
rollback	The session has a transaction rollback in process
pending	The session is waiting for a worker thread to become available
runnable	The session's task is in the runnable queue of a scheduler while waiting to get a time quantum.
spinloop	The session's task is waiting for a spinlock to become free
suspended	The session is waiting for an event, such as I/O, to complete

Reference taken from microsoft books online

Database Mirroring Vs Log Shipping

Please read the below table to find out the diffrence between mirroring and log shipping.

Database Mirroring	Log-shipping
Database mirroring is functionality in the SQL Server engine that reads from the transaction log and copies transactions from the principal server instance to the mirror server instance.  Database mirroring can operate synchronously or asynchronously.	Log shipping is based on SQL Server Agent jobs that periodically take log backups of the primary database, copy the backup files to one or more secondary server instances, and restore the backups into the secondary database(s).  Log shipping supports an unlimited number of secondary’s for each primary database.
Database mirroring can operate synchronously or asynchronously. If configured to operate synchronously, the transaction on the principal will not be committed until it is hardened to disk on the mirror.	Log shipping is always asynchrony. Log shipping totally depends on the log backup and restore schedule
Database mirroring supports only one mirror for each principal database. That means DB mirroring is at database level	Log-shipping can work on database and server level. You can configure multiple databases in logshipping
Data Transfer:    Individual T-Log records are transferred using TCP endpoints Transactional Consistency:  Only committed transactions are transferred Server Limitation:   Can be applied to only one mirror server Failover:   Automatic Failover Duration:  Failover is fast, sometimes < 3 seconds but not more than 10 seconds Role Change:   Role change is fully automatic Client Re-direction:  Fully automatic as it uses .NET 2.0/.Net 3.0	With Log Shipping: Data Transfer:    T-Logs are backed up and transferred to secondary server Transactional Consistency:  All committed and un-committed are transferred Server Limitation:   Can be applied to multiple stand-by servers Failover:   Manual Failover Duration:  Can take more than 30 mins Role Change:   Role change is manual Client Re-direction:  Manual changes required
Support only full recovery model	Supports full and bulk-logged recovery model
Mirror database is always in recovery mode. To read it you have use database snapshot.	You can use the stand-by option to read the database on standby server
Auto Page Recovery introduced with SQL SERVER 2008 so it will recover the damaged pages.	Not supported

Script to find out data and log file size

You can use below script to find out data file (*.mdf) and Log file (.ldf) size

exec sp_helpdb [dbName]

or

use [dbName]
select str(sum(convert(dec(17,2),size)) / 128,10,2) + 'MB'
from dbo.sysfiles


Script to Take database offline

Why anyone needs to take the database offline?
1. May be user don't want to use database for time being
2. To restore the database which is used by multiple users. Yes you can restore the database eventhough it is offline


EXEC sp_dboption N'DBName', N'offline', N'true'

OR

ALTER DATABASE [DBName] SET OFFLINE WITH
ROLLBACK IMMEDIATE


How to make database read only?

You can use the below script to make the database read only so that no user can edit the database.


USE [master]
GO
ALTER DATABASE [DBName] SET READ_ONLY WITH NO_WAIT
OR
GO
ALTER DATABASE [DBName] SET READ_ONLY
GO


What is Orphan User and Script to fix the Orphan Users

Orphan User:

An orphan user is a user in a SQL Server database that is not associated with a SQL Server login.


-- Script to check the orphan user
EXEC sp_change_users_login 'Report'
--Use below code to fix the Orphan User issue
DECLARE @username varchar(25)
DECLARE fixOrphanusers CURSOR
FOR
SELECT UserName = name FROM sysusers
WHERE issqluser = 1 and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null
ORDER BY name
OPEN fixOrphanusers
FETCH NEXT FROM fixOrphanusers
INTO @username
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_change_users_login 'update_one', @username, @username
FETCH NEXT FROM fixOrphanusers
INTO @username
END
CLOSE fixOrphanusers
DEALLOCATE fixOrphanusers


MySQL Replication

Problem/Error

Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave

Resolution Steps

You have to follow below steps to troubleshoot the error.

Execute the below command

SHOW MASTER STATUS

SHOW SLAVE STATUS

Check the error log for replication and its position.

Ideally there are three sets of file/position coordinates in SHOW SLAVE STATUS to identify the correct file

1) The position, ON THE MASTER, from which the I/O thread is reading: Master_Log_File/Read_Master_Log_Pos.

2) The position, IN THE RELAY LOGS, at which the SQL thread is executing: Relay_Log_File/Relay_Log_Pos

3) The position, ON THE MASTER, at which the SQL thread is executing: Relay_Master_Log_File/Exec_Master_Log_Pos

Next you have to check the error log for the log position to identify the correct binary log file and set the correct log file using below command.

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000480'

Once the problem is resolved you can use Maatkit tool to sync table to multiple slaves.

mk-table-checksum command is used to check what tables are out of sync and when use mk-table-sync command is used to resync them.

FAQs .Net Assembly

How is the DLL Hell problem solved in .NET?  Assembly versioning allows the application to specify not only the library it needs to run (which was available under Win32), but also the version of the assembly. 

How to deploy .Net assembly? An MSI installer, a CAB archive, and XCOPY command. 

What is a satellite assembly and what is the use of it? When you write a multilingual or multi-cultural application in .NET, and want to distribute the core application separately from the localized modules, the localized assemblies that modify the core application are called satellite assemblies. 

What namespaces are necessary to create a localized application? System.Globalization and System.Resources. 

What is the smallest unit of execution in .NET? An Assembly is the smallest unit of execution in .Net 

When should you call the garbage collector in .NET? It is recommended that, you should not call the garbage collector.  However, you could call the garbage collector when you are done using a large object (or set of objects) to force the garbage collector to dispose of those very large objects from memory. 

How do you convert a value-type to a reference-type? Use Boxing 

What happens in memory when you Box and Unbox a value-type?

Boxing converts a value-type to a reference-type, thus storing the object on the heap.  Unboxing converts a reference-type to a value-type, thus storing the value on the stack