Category Archives: DB Articles

Table Hints – NoLock vs ReadPast

When any data in a database is read or modified, the database engine uses special type of mechanism, called locks, to maintain integrity in the database. Locks will be used to make sure the transaction consistency.

NoLock Table Hint
– Will allow you to read the uncommited data
– Only used with SELECT statement
– Blocking will not occur
– Will reduce the concurrency and improve the performance at some extent
– Risk of doing Phantom reads

Let’s create table for the NOLOCK and READPAST hint demo

create table tranDemo
(
			id int identity(1,1),
			name varchar(10)
)

insert into tranDemo values ('Jugal')
insert into tranDemo values ('Nehal')

–Now let’s update the values by specifying the explicit transaction and don’t commit/rollback the transaction

begin transaction
   update tranDemo
   set name = 'DJ'
   where name = 'Jugal'

Now open new query window and execute the below query and you will notice query will not return data and will continue running as it is blocked

select * from trandemo

Now open new query window and execute the below query to check the blocking, you can see the blocking SPID in the result set

sp_who2 active

Now open new query window and execute the below query using NOLOCK hint and it will return data, yet transaction is not committed but it will return the updated value.

select * from trandemo(NOLOCK)

READPast Table Hint: Less commonly used table hint than NOLOCK. This hint specifies that the database engine not consider any locked rows or data pages when returning results.
– Will only read the commited rows which are not locked
– Blocking will not occur
– Only used with SELECT statement
– Will reduce the concurrency and improve the performance at some extent
– Result set returned by this hint is not perfect as it will not retun the locked rows or pages, so you can not make any decision based on data

Now open new query window and run the below query it will return only one record (“Nehal”) which is not locked or modified

select * from trandemo(ReadPast)

SQL Server 2008: SQL Mail does not work with the 64-bit version of SQL Server (Microsoft SQL Server, Error: 17938)

Recently I got email from one of the blog reader that he is getting below error while setting up SQL MAIL as legacy feature in SQL Server 2008.

Error:
SQL Mail does not work with the 64-bit version of SQL Server (Microsoft SQL Server, Error: 17938)

Solution:
SQL Mail is not supported on 64-bit versions of SQL Server. SQL Mail stored procedures cannot be installed on 64-bit versions. If you want to still use it you have to go with the 32-bit edition or start using database mail, which is more easy.

Follow below article link to start converting your SQLMAIL to DB Mail.
http://msdn.microsoft.com/en-us/library/ms187891.aspx

T-SQL Script to find out the database file size, space used and available free space

While troubleshooting disk space issue, it is essential to know about the database file size statistics. You can execute below script to get database file size information.

set nocount on

create table #dbfileInfo(
name varchar(300),
location varchar(300),
filesizeMB decimal(9,2),
spaceUsedMB decimal(9,2),
FreespaceMB decimal(9,2))

declare @mySQL nvarchar(2000)
DECLARE @dbName varchar(MAX)
DECLARE @cur_DBName CURSOR

SET @cur_DBName = CURSOR FOR
select name from sys.databases

OPEN @cur_DBName
FETCH NEXT
FROM @cur_DBName INTO @dbName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @dbName
if DATABASEPROPERTYEX(@dbName, 'status') = 'ONLINE'
begin
select @mySQL = 
    '
        use ' + @dbname + '
        INSERT INTO #dbfileInfo
        select
      name
    , filename
    , convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB
    , convert(decimal(12,2),round(fileproperty(a.name,''SpaceUsed'')/128.000,2)) as SpaceUsedMB
    , convert(decimal(12,2),round((a.size-fileproperty(a.name,''SpaceUsed''))/128.000,2)) as FreeSpaceMB
    from dbo.sysfiles a
    '
    exec sp_executesql @mySQL
end
FETCH NEXT
FROM @cur_DBName INTO @dbName

END
CLOSE @cur_DBName
DEALLOCATE @cur_DBName
GO

select * from #dbfileInfo
drop table #dbfileInfo

FileOutput

How to make Lite Speed Central database and Local repository in sync?

If you have configured the LiteSpeed in your environment using the Lite Speed Central repository option, there will be a scenario when Lite Speed Central repository misses some data. To make the data available in central repository you can use the LiteSpeed local database and execute the below procedure to ensure that cenral repository comes in sync with the local repository.

exec dbo.xp_replicate_activity_statistics

Permission required for SP_UpdateStats

To update the statistics on the database you should either be DBO of that database or SysAdmin on SQL instance.
SPUpdateStats

If you want to grant the least permission instead of server level permission you make the user DBO of the database. You can do it from database properties windows as below.

DBO

You can also make the user DBO using below command.

USE [My_Policy]
GO
EXEC dbo.sp_changedbowner @loginame = N’jugal’, @map = false
GO