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