@@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 , , , , , ,

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 , , , , , , , ,

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 , , , , ,

Estimating the size of the Database backup file

November 21, 2011 Leave a Comment

Problem: Often we would like to check how much size backup file will take on the disk? You can follow below simple steps to estimate the backup file size?

Solution:
Execute the SP_SPACEUSED procedure to get the database size statistics.
USE [sqldbpool]
GO
EXEC sp_spaceused @updateusage = ‘true’

Now you have to subtract the ununsed pages from reserved pages and divided by 1024 to get the output in MB.

In our scenario we have 2592 (KB) – 96 (KB) = 2496/1024 = 2.4374(MB) will be the backup size.

Filed under Database, SQL Server 2008, SQL Server 2008 R2 Tagged with , , , , ,

How to Move Resource Database?

June 14, 2011 Leave a Comment

Resource Database: Resource database is available from the SQL Server 2005 and higher level versions. Resource database is read only and hidden database. Resource database contains all the system objects that shipped with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database.

Resource database will be very useful in doing upgrades or in un-installing up-grades. In the previous versions of SQL Server up-grade needs creating/dropping of the system objects. From the SQL Server 2005 version upgrade is just procedure to copying resource database file to local server.

Name of Resource database data and log file.
mssqlsystemresource.mdf
mssqlsystemresource.ldf

Resource database data and log file location is same as the Master database location. In case if you are moving Master database you have to move the Resource database as well to the same location.

You can check the Resource database version and last up-grade time using the SERVERPROPERTY function.

SELECT SERVERPROPERTY(‘RESOURCEVERSION’);
GO
SELECT SERVERPROPERTY(‘RESOURCELASTUPDATEDATETIME’);
GO

To move the resource database, you have to start the SQL Server service using either -m (single user mode) or -f (minimal configuration) and using -T3608 trace flag which will skip the recovery of all the databases other than the master database.

You can do it either from the Configuration manager or from the command prompt using below command.
Default Instance
NET START MSSQLSERVER /f /T3608
Named Instance
NET START MSSQL$instancename /f /T3608

Execute the below ALTER command once you have started the SQL Service by specifying the new location, location should be same as Master database location.

ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=data, FILENAME= '\mssqlsystemresource.mdf')
ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=log, FILENAME= '\mssqlsystemresource.ldf')

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

How to kill all sessions that have open connection in a SQL Server Database?

June 8, 2011 Leave a Comment

As SQL Server DBAs, many times we need to KILL all Open Sessions against the SQL Server Database to proceed with Maintenance Task, Restore and more…

You can use below different techniques to KILL all open sessions against the database.

Technique – I
Here we will query the SysProcesses table to get the session running against the user database and prepare the dynamic SQL statement to KILL all the connection.

DECLARE @DbName nvarchar(50)
SET @DbName = N'Write a DB Name here'

DECLARE @EXECSQL varchar(max)
SET @EXECSQL = ''

SELECT @EXECSQL = @EXECSQL + 'Kill ' + Convert(varchar, SPId) + ';'
FROM MASTER..SysProcesses
WHERE DBId = DB_ID(@DbName) AND SPId  @@SPId

EXEC(@EXECSQL)

Technique – II
Take the database into Single User Mode and execute all the task needs to perform against the databse.

ALTER DATABASE [Database Name] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Once you are finish with all the required task make the database accessible to everyone.

ALTER DATABASE [Database Name] SET MULTI_USER

Technique – III
In case of restore the database by replacing existing database, you can take the database OFFLINE and restore it. Restore will bring the database online.

ALTER DATABASE [Database Name] SET OFFLINE WITH ROLLBACK IMMEDIATE 

ALTER DATABASE [Database Name] SET ONLINE

Technique – IV
Go to Activity Monitor, Select the desired database and right click on the database to KILL the process.

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

How to Turn Off SSMS Auto Recovery Feature

April 23, 2011 Leave a Comment

Problem
By default the Auto Recovery feature is enabled for SSMS and because of this when opening SSMS it may hang or become unresponsive for some time if the previous session was closed unexpectedly. There is not a way to turn this feature off from within SSMS, but we will look at how this can be done by modifying some registry entries.

Solution
http://www.mssqltips.com/tip.asp?tip=2352

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

Script to Monitor the progress of ALTER, Backup, Restore, DBCC, Rollback and TDE commands

March 29, 2011 Leave a Comment

We often like to check the progess or completed percentage of time consuming command. You can query the sys.dm_exec_requests DMV to check the status of the command.

You must have atleast view state permission to execute the below query.
ALTER INDEX REORGANIZE
AUTO_SHRINK option with ALTER DATABASE
BACKUP DATABASE
DBCC CHECKDB
DBCC CHECKFILEGROUP
DBCC CHECKTABLE
DBCC INDEXDEFRAG
DBCC SHRINKDATABASE
DBCC SHRINKFILE
RECOVERY
RESTORE DATABASE
ROLLBACK
TDE ENCRYPTION

SELECT dmr.session_id,
       dmr.status,
       dmr.start_time,
       dmr.command,
       dmt.TEXT,
       dmr.percent_complete
FROM sys.dm_exec_requests dmr  CROSS APPLY sys.Dm_exec_sql_text(dmr.sql_handle) dmt WHERE dmr.command IN (‘ALTER’, ‘Backup’, ‘Restore’, ‘DBCC’, ‘Rollback’,  ‘TDE’)

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

Script to Update Statistics by passing database name

March 26, 2011 Leave a Comment

You can use below script to update the statistics with the FULL Scan. You can pass the database name in below script, I have given JShah as database name.

EXEC Sp_msforeachdb
@command1=‘IF ”#” IN (”JShah”) BEGIN PRINT ”#”;
EXEC #.dbo.sp_msForEachTable ”UPDATE STATISTICS ? WITH FULLSCAN”, @command2=”PRINT CONVERT(VARCHAR, GETDATE(), 9) + ”” - ? Stats Updated””” END’
,@replaceChar = ‘#’

You can use below script to update the statistics with the SAMPLE Percent agrument. You can pass the database name in below script, I have given JShah as database name.

EXEC Sp_msforeachdb
@command1=‘IF ”#” IN (”JShah”) BEGIN PRINT ”#”;
EXEC #.dbo.sp_msForEachTable ”UPDATE STATISTICS ? WITH SAMPLE 50 PERCENT”, @command2=”PRINT CONVERT(VARCHAR, GETDATE(), 9) + ”” - ? Stats Updated””” END’
,@replaceChar = ‘#’

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

Resolving could not open a connection to SQL Server errors

March 21, 2011 Leave a Comment

Problem

Sometimes you may have issues connecting to SQL Server and you may get messages such as the following:

ERROR: (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error:) An error has occurred while establishing a connection to the server. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 5)

Or

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 1326)

Or

A network-related error or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible.  Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 – No such host is known.) (Microsoft SQL Server, Error: 11001)

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

Click Me for Solution

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

Doing a lot of SQL coding – Try SQL Complete

March 14, 2011 Leave a Comment

Are you doing a lot of SQL coding every day and looking devouringly at every tool offering replacement of the native SSMS IntelliSense available only for SQL Server 2008? Then don’t miss Devart dbForge SQL Complete while you are seeking an assistant to do the boring part of your work that should have been automated and simplified long ago.

General Information
The tool is available in two editions:
Express – a free edition providing basic functionality to complete and format SQL code.
Standard – fully-featured edition providing all necessary functionality for completing and formatting T-SQL code.

30-day trial period available for Standard Edition should be enough for anyone to test all product features and check if it meets one’s requirements.

During evaluation and usage, you can submit requests and suggestions to the tool’s support team. Besides, you can take part in creating its development roadmap at UserVoice.

Features

  • Context-based prompt of different object types
  • Context-based prompt of keywords
  • Context-based prompt of function parameters
  • Word autocompletion
  • Automatic filtering of object in the suggestion list
  • Context-based sorting of suggestions in the list
  • Determining a current database or schema
  • Supporting queries to various databases
  • Automatic displaying suggestions while typing
  • Two ways to insert a suggested word into a query
  • Usage of syntax highlight settings for the suggestions list
  • Query formatting
  • Support of various query types
  • Semi-transparent view of the suggestion box
  • Inserting columns list on pressing Tab
  • Suggesting methods for columns and variables
  • Suggesting conditions for JOIN statement
  • Automatic alias generation in SELECT statements
  • Sorting keywords by relevance
  • Quick object info
  • Expanding INSERT statements
  • Export/Import settings wizard

    • 

Review

I chose to install trial of SQL Complete Standard Edition. The installation took several seconds, and the tool and all its options are ready to use and easy to access from the SQL Server Management Studio main menu:

OK, but we will peep into the options later, when we see how the tool actually works and be sure that it’s worth spending time on learning different advanced settings. Now we want to type any query we think of first. This can be as simple as SELECT * FROM:

What can we see? The tool filtered available suggestions depending on the probability of their usage. This saved me from tedious selecting of the needed word in a list sorted alphabetically (imagine it was the last one in it!) or typing almost the whole word

Now I’d like to exclude some columns from the table I am working with in this statement. Unfortunately, the “*” symbol is rarely replaced with the columns list by code author. But I found a way to do this quickly and effortlessly using the tool – just pressed Tab, as was written in the hint, and it was all:

Surely, you often have to join tables in queries just as I do. Quite simply, these statements allow combining data in multiple tables to quickly and efficiently process large quantities of data, but often they take too much time to write. There is a feature declaring SQL Complete capability to do such a trick painlessly for the one who is writing code:

I can say these were positive impressions, and the tool is really worth spending more time for testing. I decided on using some more complicated unformatted query, as the vendor puts emphasis on the tool’s advanced formatting capabilities. Here’s what we’ve got:

Before:

After:

My query was successfully formatted and is readable now.

Being able to access essential information on a database object is pretty useful and saves some efforts on looking it up:

The tool offers quite a lot of formatting options and a wizard for importing and exporting settings – this should be useful for large companies where some standardized T-SQL code formatting is necessary:

Summary

SQL Complete can be used by professionals, amateurs, and everybody who has something to do with writing SQL code. Besides, the price of the fully-featured edition (less than $50.00), availability of the free edition, effective product support provided by its development team make it worth testing seriously when choosing a tool from a number of alternatives offering similar functionality.

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

← Older posts