Steps to Save/Store file into Database

February 16, 2012 Leave a Comment

You can use the below scripts to store/save the file into SQL Server database table. Please note it is not recommended to store file into database. You can store the file on file system and path in the database.

use sqlDBPool
--documents table will store files into varbinary field
--drop table documents
create table documents
(
	documentID int identity(1,1),
	doctype char(5),
	document varbinary(max)
)


--script to store/save document into table
insert into documents 
 Select 'xls', (SELECT * FROM OPENROWSET(BULK N'C:\JSpace\book1.xls', SINGLE_BLOB) AS document) document
go

select * from documents

Filed under SQL Scripts, SQL Server, SQL Server 2008, SQL Server 2008 R2, SQL Server 2011 (Denali) Tagged with , , , , , ,

@@MICROSOFTVERSION Function

February 12, 2012 Leave a Comment

Problem: Take an example you are writing a script which is going to be deployed on the all the versions of SQL Servers and you want to check the SQL Server version details using T-SQL code. Below solution will guide you how to check the SQL Server version in stored procedure/t-sql batch.

Solution: You can use the @@MICROSOFTVERSION to get the SQL Server version information. If the output of the below script is 9 than its SQL 2005, if 10 than SQL Server 2008 and if 11 than SQL Server 2011

select @@VERSION

--method - 1
select @@MICROSOFTVERSION as MSVersion, CAST (@@MICROSOFTVERSION as BINARY(5)) as MsVersionInBinary
-- Remove the first non-zero character after 0x0 from binary output here it is A and divide the @@MicrosoftVersion outout 
select substring(cast(@@MICROSOFTVERSION/0x000000640 as varchar(10)),1,2) as MsSQLVersion

--Method 2
select @@MICROSOFTVERSION / POWER(2,24) as usingPowerFunctionMSSQLVersion

Filed under Database, SQL Scripts, SQL Server, SQL Server 2008, SQL Server 2008 R2, SQL Server 2011 (Denali) Tagged with , , , , ,

Steps to create the deadlock scenario

February 12, 2012 1 Comment

A deadlock occurs when two or more processes permanently block each other by each process having a lock on a resource which the other process are trying to lock.

Please execute the below queries as per the mentioned comments to produce a deadlock.

--turning on the traceflag to record deadlock info into error log
dbcc traceon(1204,-1)
dbcc tracestatus(1204)

--creating test database
create database sqlDBPool
--Connecting to SQLDBPool database
use sqldbpool
--table creation
create table tb1 (col1 int)
create table tb2 (col1 int)
--inserting dummy records
insert into tb1 values(1),(2),(3)
insert into tb2 values(1),(2),(3)

--Open first connection to update table explicit transaction
begin transaction
  update tb1 set col1 = 5
  
--Open second connection to update table explicit transaction
use sqlDBPool
begin transaction
  update tb2 set col1 = 6
  update tb1 set col1 = 6

--Open first connection to update table explicit transaction
  update tb2 set col1 = 5

You can see the one of the transaction will fail with the below error message.

Msg 1205, Level 13, State 45, Line 3
Transaction (Process ID 55) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

As we have turned on the deadlock trace flag, you can see the below information in the SQL Server error log.

Starting up database 'sqlDBPool'.
Deadlock encountered .... Printing deadlock information
Wait-for graph
NULL
Node:1  
RID: 9:1:153:0                 CleanCnt:2 Mode:X Flags: 0x3
 Grant List 1:
   Owner:0x05684480 Mode: X        Flg:0x40 Ref:0 Life:02000000 SPID:52 ECID:0 XactLockInfo: 0x065F82A8
   SPID: 52 ECID: 0 Statement Type: UPDATE Line #: 1
   Input Buf: Language Event: update tb2 set col1 = 5
Requested by: 
  ResType:LockOwner Stype:'OR'Xdes:0x05A8CC10 Mode: U SPID:55 BatchID:0 ECID:0 TaskProxy:(0x05A70354) Value:0x6767b20 Cost:(0/432)
NULL
Node:2  
RID: 9:1:155:0                 CleanCnt:2 Mode:X Flags: 0x3
 Grant List 2:
   Owner:0x067679A0 Mode: X        Flg:0x40 Ref:0 Life:02000000 SPID:55 ECID:0 XactLockInfo: 0x05A8CC38
   SPID: 55 ECID: 0 Statement Type: UPDATE Line #: 3
   Input Buf: Language Event: begin transaction    update tb2 set col1 = 6    update tb1 set col1 = 6
Requested by: 
  ResType:LockOwner Stype:'OR'Xdes:0x065F8280 Mode: U SPID:52 BatchID:0 ECID:0 TaskProxy:(0x0941A354) Value:0x6a943a0 Cost:(0/432)
NULL
Victim Resource Owner:
 ResType:LockOwner Stype:'OR'Xdes:0x05A8CC10 Mode: U SPID:55 BatchID:0 ECID:0 TaskProxy:(0x05A70354) Value:0x6767b20 Cost:(0/432)

Filed under Database, DB Articles, SQL Scripts, SQL Server, SQL Server 2008, SQL Server 2008 R2, SQL Server 2011 (Denali) Tagged with , , , , , ,

Different ways to check the SQL Server Instance Port number

January 18, 2012 1 Comment

Problem: If there are multiple SQL instances running on the same computer, it is difficult to identify the instance port number. You can use the below solution to find the instance specific port numbers.

Solution: You can check the list of port number used by the SQL Server instances using one of the below way.

Soln 1# Using SQL Server Configuration Manager

  • Go to SQL Server Configuration Manager
  • Select Protocols for SQL2005/2008 under SQL server Network Configuration
  • Right click on TCP/IP and select Properties
  • Select the IP Addresses-tab
  • In the section IP ALL, you can see the ports

Soln 2#From Registry Values
SQL Server 2005
Type the regedit command in Run window and check the below registry values.HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.#

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\ MSSQL.#\ MSSQLServer\ SuperSocketNetLib\TCP\IPAll

SQL Server 2008
Default instance
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\TCP\IPAll

Named instance
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.(InstanceName)\MSSQLServer\SuperSocketNetLib\TCP\IPAll

Soln 3# Error Log
Query the error log as below to get the port number.

EXEC xp_readerrorlog 0,1,”Server is listening on”,Null

Soln 4# Command Prompts
Execute the below command from the command prompt.

Netstat -abn

Filed under SQL Scripts, SQL Server, SQL Server 2008, SQL Server 2008 R2, SQL Server 2011 (Denali) Tagged with , , ,

Steps to Attach a SQL Server database without transaction log file

December 20, 2011 Leave a Comment

Problem: There could be situation where you missed the database transaction log file(.LDF) and you have only data file (.MDF). You can attach the database using below solution.

Solution: In the below script I have created the database,dropped its log file and created the database with the .mdf file.

--created database with .mdf and .ldf file
CREATE DATABASE [singleFileDemo] ON  PRIMARY 
( NAME = N'singleFileDemo', FILENAME = N'L:\singleFileDemo.mdf' , SIZE = 2048KB , FILEGROWTH = 10240KB )
 LOG ON 
( NAME = N'singleFileDemo_log', FILENAME = N'F:\singleFileDemo_log.ldf' , SIZE = 1024KB , FILEGROWTH = 5120KB )
GO

--inserting data into database
use singleFileDemo
create table tb1 (name varchar(10))

--inserting records
insert into tb1 values('Jugal')
go 10;

--deleting the log file
--detaching the database file
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'singleFileDemo'
GO

-- now next step is delete the file manually or you can do it from command prompt
EXEC xp_cmdshell 'del F:\singleFileDemo_log.ldf'

-- script to attach the database 
USE [master]
GO
CREATE DATABASE [singleFileDemo] ON 
( FILENAME = N'L:\singleFileDemo.mdf' )
FOR ATTACH
GO

When you will execute the CREATE DATABASE FOR Attach script you will get the below warning message.

File activation failure. The physical file name "F:\singleFileDemo_log.ldf" may be incorrect.
New log file 'F:\singleFileDemo_log.LDF' was created.

Once the database is ready execute the DBCC CHECKDB for any error.

Filed under Database, SQL Server 2008, SQL Server 2008 R2, SQL Server 2011 (Denali) Tagged with , , , , , , , ,

Extended Stored Procedure xp_msver

December 8, 2011 1 Comment

xp_msver returns information about the SQL Server version, actual build number of the server and information about the server environment.

You can also pass the parameter to get the specific information.

Filed under SQL Server, SQL Server 2008, SQL Server 2008 R2, SQL Server 2011 (Denali) Tagged with , , ,

Script to Enable/Disable Database for Replication

December 2, 2011 Leave a Comment

You can enable the database for replication using below script.

use master
exec sp_replicationdboption @dbname = 'sqldbpool',
@optname = 'publish',
@value = 'true'
go

If you have restore the database on test environment and you are getting the error that “Database is part of Replication”, you can clear/disable it by executing below query.

use master
exec sp_replicationdboption @dbname = 'sqldbpool',
@optname = 'publish',
@value = 'false'
go

Filed under Database, SQL Server, SQL Server 2008, SQL Server 2008 R2, SQL Server 2011 (Denali) Tagged with , , , , ,

Steps to change the server name for a SQL Server machine

November 25, 2011 Leave a Comment

ProblemIn this tip we look at the steps within SQL Server you need to follow if you change the physical server name for a standalone SQL Server.

Solution
http://www.mssqltips.com/sqlservertip/2525/steps-to-change-the-server-name-for-a-sql-server-machine/

Filed under SQL Server 2008, SQL Server 2008 R2, SQL Server 2011 (Denali) Tagged with , , ,

Common cause of the performance issues

November 17, 2011 2 Comments

Below are the most common reasons for the performance issues.


  • SQL Server Configuration Issues

  • Database/table/schema Design and Configuration Issues

  • CPU/IO/Memory Bottleneck

  • Blocking

  • Network Bottleneck

  • Poor Indexing Strategy (Missing Index, Fragmented Index, Un-Used Index)

  • Out-of-date/missing statistics

  • T-SQL Code

  • Application Code

Filed under SQL Server, SQL Server 2008, SQL Server 2008 R2, SQL Server 2011 (Denali) Tagged with , ,

Script to find out Heap Table/Table without clustered Index

November 17, 2011 Leave a Comment

Heap Table: Table without a clustered index is called Heap Table. You can find out the Heap Table by querying the sys.indexes or sys.partitions against the index_id column.

select OBJECT_NAME(object_id),* 
from sys.indexes where index_id = 0

Select OBJECT_NAME(object_id),*  
from sys.partitions where index_id = 0

Filed under SQL Scripts, SQL Server, SQL Server 2008, SQL Server 2008 R2, SQL Server 2011 (Denali) Tagged with , , , , ,

Steps to restore Database using Database Snapshot

November 16, 2011 1 Comment

Problem: Take a scenario where vendor wants you to up-grade the vendor databae using the SQL scripts. In case if something goes wrong you can either recover database by restoring all backup or by creating database snapshot.

Solution:
Database snapshots feature is available from the SQL Server 2005. Snapshot will be used to create a read-only copy of a database at a given point in time. Any transactions which are uncommitted at the time you create a snapshot will not be included in the database snapshot. You can create multiple snapshots of a source database, but those snapshots must reside on the same instance as the source database.

You must have SQL Server enterprise edition to create the snapshot. A database snapshot only contains the data pages that have changed in the source database since the snapshot was created. It contains the original copies of those pages in order to give the effect of a read-only view. The file that is created to hold the changed data pages when the snapshot is created is known as a sparse file.

A source database that contains a snapshot cannot be dropped, detached, or restored until all of the snapshots have been dropped.

Check below queries to restore the database using snapshot
Before you start restoring using database snapshot
– Make sure the source database does not contain read-only or compressed filegroups.
–Make sure all the files are online that were online when the snapshot was created.
–Delete all snapshots of the source database, except the one you are reverting to.

create database db_pool
use db_pool

create table tb1
(
	id int,
	name varchar(10)
)

insert into tb1 values(10,'Jugal')
go 50;

--select the database and execute the below query to get logical name
SELECT name FROM sys.database_files WHERE type <> 1

--execute below command to create the database snapshot
CREATE DATABASE db_pool_Snapshot
 ON
(Name = db_pool,
 FileName = 'C:\db_pool_Data.ss')
 AS SNAPSHOT OF db_pool
 
--select the original database and drop the rows
use db_pool
delete from tb1

--use the snapshot and query tb1, we can see the data evenif it is deleted 
 use db_pool_Snapshot
 select * from tb1
 
 --now restore database using below script from snapshot
USE master
GO
RESTORE DATABASE db_pool FROM DATABASE_SNAPSHOT = 'db_pool_Snapshot'
 
 --query the database to check changes
 use db_pool
 select * from tb1

Filed under SQL Server 2008, SQL Server 2008 R2, SQL Server 2011 (Denali) Tagged with , , , , , ,

Steps to restore the resource database

November 16, 2011 2 Comments

Few days back, I had discussion with my team member regarding the resource database and we all are curious to see the resource database.

As you all know resource database is hidden database and we can’t see it SQL Server. We follow below steps to restore the resource database and it worked. We can see the resource database.

Resource database file location
By default, these files are located in :\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\. Each instance of SQL Server has one and only one associated mssqlsystemresource.mdf file, and instances do not share this file. In a cluster, the Resource database exists in the \Data folder on a shared disk.

Follow below steps if you want to see the resource database.
Step 1: Copy the resource database MDF/LDF file to different location and rename it. We have name both data and log file to resourcetest.

Step 2: Execute the below command to attach the resource database file and you can see the resource database.

USE [master]
GO
CREATE DATABASE [resourcetest] ON 
( FILENAME = N'J:\resourcetest.mdf' ),
( FILENAME = N'J:\resourcetest.ldf' )
 FOR ATTACH
GO

Resource Database Image

Filed under SQL Server 2008, SQL Server 2008 R2, SQL Server 2011 (Denali) Tagged with , , ,

← Older posts