Tag Archives: MS SQL Server 2005

Get latest running job from sysJobHistory in SQL Server 2005/2008

We might have several job running in our servers and we can find its stat from its log file but it would be interesting to query job history table. Basically all details about JOBS reside in MSDB database in SQL Server and you can query to SysJobHistory to get information about jobs. You can have so many details about JOB in SysJobHistory table like Job run date, time, job name, message about whether it was successful or not etc. I often need to know what JOBs has been ran in last 24 or 48 hours, whether it was successful or not etc. Let us see that small yet useful T-SQL.
select * from msdb..sysjobhistory


If you want to look at jobs which ran in last one or two days, you can query date like this:
select *
from msdb..sysjobhistory
where (  run_date = convert(varchar(8), getdate(), 112)
     and run_time < replace(convert(varchar(10), getdate(), 8), ‘:’, )
      )
   or(  run_date = convert(varchar(8), getdate() 1, 112)
     and run_time >= replace(convert(varchar(10), getdate(), 8), ‘:’, )
      )
order by run_date, run_time
Actually date and time both are stored in separate INT column so that we have to convert it in proper format and have to compare it by using string functions.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Deal with Indian regional language in SQL Server 2005/2008

Yesterday, I have received one email for help about querying and filtering records contain Telugu Indian regional language. Have a look:
The problem was when person filtering records of telugu language, was not able to get any single records. You have to deal with few things while dealing with regional languages.
1.)    If whole database should be in regional language, set proper collation for that.
2.)    Set proper collation for column
3.)    While querying or inserting data, MUST USE “N” as a prefix as regional data should be in Unicode and “N” prefix is must to differentiate whether data is Unicode.
When I asked that person, what collation are you using for database and column? Below is the answer:
=========================================================================
Hi Ritish,
                Yes.I have set Indic_General_90_BIN as Proper collation to Telugu Column.
                 But I am not able to get the records.
                 Is there any way to get the records please ? 
=========================================================================
What is the reason if proper collation is set even though data doesn’t get filtered by following query?
SELECT * FROM TeluguDictionary where (Telugu like ‘%అక్క%’)


Or doesn’t insert proper data with following INSERT statement?
INSERT INTO TeluguDictionary VALUES (‘akkadi’,అక్కడి)


“N” prefix was missing in both query. If s/he would have used same query with “N” prefix like below, it would have worked.
SELECT * FROM TeluguDictionary where (Telugu like N‘%అక్క%’)
INSERT INTO TeluguDictionary VALUES (‘akkadi’,Nఅక్కడి)

Reference: Ritesh Shah

Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

DateTime observation in SQL Server 2008, Rounding or Bug?

Recently I come across with one very interesting observation. When you assign any date in datetime variable with milliseconds and print it, you will get difference of few milliseconds, sometime one or two or three. I don’t know whether SQL Server is rounding the datetime or it is a bug or SQL Server is not so much accurate up to milliseconds. Have a look at below examples:

declare @datetest datetime
set @datetest = ‘2009-01-01 23:59:59.998’
select @datetest –answer is 2009-01-01 23:59:59.997
GO

declare @datetest datetime
set @datetest = ‘2009-01-01 23:59:59.992’
select @datetest –answer is 2009-01-01 23:59:59.993
GO

declare @datetest datetime
set @datetest = ‘2009-01-01 23:59:59.982’
select @datetest –answer is 2009-01-01 23:59:59.983
GO

 –in this case you can see even day is changed because of one milliseconds :)

declare @datetest datetime
set @datetest = ‘2009-01-01 23:59:59.999’
select @datetest –answer is 2009-01-02 00:00:00.000
GO

I tried to find exact reason for this but wasn’t able to do so.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder ofhttp://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Find table being locked in SQL Server 2008/2005

As a DBA one need to keep watch on database for lock. This is really very interesting subject and often useful in many different troubleshooting situation. There are many types of LOCK in database, I will write article on different types of lock very soon, right now my intention is to show which table is being locked at the moment. Sometime you index the table or change the schema of table, table got completely locked at that time. I have one very small yet useful and handy query which can show you which table is locked right now. I have executed the query on AdventureWorks database under SQL Server 2005 environment but the same query is compatible under SQL Server 2005 and SQL Server 2008 both.
use Adventureworks
go


–to list of tables which are being locked at the moment
select
  object_name(resource_associated_entity_id) as ‘TableName’ ,*
from
  sys.dm_tran_locks
where resource_type = ‘OBJECT’
  and resource_database_id = DB_ID()
GO


–find complete table lock with  ‘SCH-M’ request mode
–generally when you rebuld index, table is being locked
–and unaccesible, it has schema mode change lock (‘SCH-M’) mode
–so, find out those table which is unavailable
select
  object_name(resource_associated_entity_id) as ‘TableName’ ,*
from
  sys.dm_tran_locks
where resource_type = ‘OBJECT’
  and resource_database_id = DB_ID()
   and request_mode = ‘Sch-M’
GO

–one more beautiful query which can show you which command is causing problem.
–if rebuild index causing complete table lock than you will have DBCC in command column
select
  object_name(sl.resource_associated_entity_id) as ‘TableName’ ,dr.command,sl.*
from
  sys.dm_tran_locks as sl left join sys.dm_exec_requests dr on sl.request_session_id=dr.session_id
where sl.resource_type = ‘OBJECT’
  and sl.resource_database_id = DB_ID()
   and sl.request_mode = ‘Sch-M’
GO



Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Find stored procedure in your database with T-SQL in SQL Server 2005/2008

I have observed quite a few times in different forum, people keep asking question that I have created stored procedure in my database but I can’t find it. Well there are quite a few reason for that, either you have created your stored procedure in different database and you are trying to run in different database or you have not specified schema name for your stored procedure so default schema of your login will be automatically applied to it, while running SP, you don’t specify that schema name and you will face an error like:
“Could not find stored procedure ‘Your SP name’”
Well, to ensure that you stored procedure is created and it is exists in particular database, I used to run following very simple query to confirm.

select ss.name + ‘.’ + sp.name as ‘Available Stored Procedure’ from sys.procedures as sp join sys.schemas ss on sp.schema_id=ss.schema_id



Above query will show you all the available stored procedure along with its schema name available in your database.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Keep watch on your database file size in SQL Server 2008/2005

Keep watch on your database file size in SQL Server 2008/2005

Well, in my previous article, I saw the method of how to keep watch on your disk drive (Click here to view), how to get information about your data and log file size as those are the very important task of DBA. Today, I am going to show one more important job which DBA simply can’t ignore. DBA needs to keep watch on every data and log file of databases server wide.  If it reaches at certain limits, DBA should be notified by email immediately so that s/he can take any action regarding this. Let me show you one very simple script which can make this task very easy for DBA.

Here is the script with Cursor:

 

DECLARE @DiskSpace bigint
DECLARE @DataBaseName VARCHAR(50)
DECLARE @Name VARCHAR(50)
DECLARE @DriveLetter CHAR(1)
DECLARE @DiskSize bigint
Declare @fileName varchar(max)
SET   @DiskSpace = 1
DECLARE DriveSpace CURSOR FAST_FORWARD FOR

SELECT
databasename=DB_NAME(dbid),
name,
filename,
drive=LEFT(filename,1),
[size]=convert(bigint,[size])*8/1024
FROM
sysaltfiles
ORDER BY
dbid

OPEN
DriveSpace
FETCH NEXT FROM
DriveSpace
INTO
@DataBaseName, @Name,@fileName, @DriveLetter, @DiskSize

WHILE
(@@FETCH_STATUS = 0)
BEGIN
IF
@DiskSize > @DiskSpace
BEGIN
DECLARE @mes VARCHAR(MAX)
SET
@mes='Hi Ritesh, ' + @Name + ' file of ' + @DataBaseName + ' database exceeds 1000 MB limit, current size is ' + cast(@DiskSize as varchar(10)) + ' and file name is ' + @fileName
EXEC
msdb..sp_send_dbmail
@recipients = 'Rits4Friends@gmail.com',
@subject= 'Drive is about to full',
@body= @mes
End

FETCH NEXT FROM
DriveSpace
INTO
@DataBaseName, @Name,@fileName, @DriveLetter, @DiskSize
End

CLOSE DriveSpace
DEALLOCATE DriveSpace
GO
All you need to do is, keep this script in JOBS and run that job once or twice everyday. If you don’t have database email configured than you could check this by remove comment before “Print” statement in script, don’t forget to comment email sending code, if you don’t have email configured in database.
 

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Quick Learn

Note: Microsoft Books online is a default reference of all articles .

Keep watch on your disk space in SQL Server 2008/2005

Keep watch on your disk space in SQL Server 2008/2005
Disk always plays an important role in life of DBA. We have o keep watch on disk size, free space, read & write capacity and much more. One of the important task for DBA is to keep watch on the free space of hard drive so that no databases face any space related issue. There are so many ways to check it, you can either manually check free space from your explorer, you can use sp_fixedDrives extended stored procedure in SQL Server. What I am going to show you is how you can atomize this process, which keeps an eye on the disk space and can send an email to administrator when it reaches below our warning level.
Let us see one simple stored procedure for this task:
USE
[master]
GO
CREATE PROCEDURE [dbo].[SpDiskSpaceAlert]
AS
IF OBJECT_ID(N'[tempdb]..[#Temptbl]') is not null
DROP TABLE #Temptbl
CREATE TABLE #Temptbl
(
Drive CHAR(1) NOT NULL,
MB INTEGER NOT NULL
)

DECLARE @dsc INT
DECLARE @FreeSpace INT
DECLARE @Drive CHAR(1)
SET @dsc = 20000

INSERT
INTO #Temptbl
EXEC master..xp_fixeddrives
DECLARE DriveSpace CURSOR FAST_FORWARD FOR select Drive, MB from #Temptbl

OPEN DriveSpace
FETCH
NEXT FROM DriveSpace INTO @Drive, @FreeSpace
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF
@FreeSpace < @dsc
BEGIN
DECLARE @mes VARCHAR(MAX)
SET @mes='Hi Ritesh'
+ @Drive + ': drive reached warning limit, current status is ' + cast(@FreeSpace as varchar) + ' MB only'
EXEC
msdb..xp_send_dbmail @recipients = 'Rits4Friends@gmail.com',
@subject= 'Drive is about to full',
@body= @mes
End

FETCH NEXT FROM DriveSpace INTO @Drive, @FreeSpace
End
CLOSE DriveSpace
DEALLOCATE DriveSpace
DROP TABLE #Temptbl
GO
--check whether this works or not
EXEC [Master].[dbo].[SpDiskSpaceAlert]


After creating this stored procedure, you can call it in SQL Server jobs, one or may be twice a day, which sends you an email, if warning level reaches.  Right now I have set it to 20000 MB so if any of the drive in server reached to this capacity, it will shoot you an email. This is just a basic script, need modification as per the need of individual.
Note: You should have Database mail enable. If you don’t want to send an email, you can either create log or store information in any of the table.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Quick Learn

Note: Microsoft Books online is a default reference of all articles .

Convert local DateTime to UTC DateTime in SQL Server 200/2005

I have seen one question in many forums about asking how to convert local DateTime to UTC DateTime? This is really very simple task to done.  All you need to do is, get difference between your local datetime and UTC datetime. GetDate() function will you, your local DateTime and for UTC DateTime, you can use getUTCdate() function. Simply find the difference between these two DateTime in Minutes or in Hours and Add/Deduct that Minutes/Hours from your local datetime and you are done. Let us see how easily we can do it

 

select DATEADD(hh,datediff(hh,GETDATE(),getUTCdate()),GETDATE()) as ‘UTC DateTime’, GETDATE() as ‘Local DateTime’

–or you can pass your own dateTime field or variable like this.

declare @dt datetime

set @dt=‘9/22/2009 1:25:00 PM’

select DATEADD(hh,datediff(hh,GETDATE(),getUTCdate()),@dt) as ‘UTC DateTime’

 

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Watch your file size of databases in SQL Server 2008/2005

Keeping watch on file size of databases is one of the important task for DBA. I am using one small yet very useful script since very long time for this task which I wanted to share with all of my blog reader. I don’t exactly remember whether I have developed it or found it from internet somewhere. I tried to find this script by GOOGLE but didn’t find it anywhere so I forced to believe that I might have developed it. If anybody knows where does it located than do let me know so that I can give due credit.

 

select
      databasename=DB_NAME(dbid),
      name,
      fileid,
      drive=LEFT(filename,1),
      filename,
      filegroup=FILEGROUP_NAME(groupid),
      [size]=CONVERT(nvarchar(15),convert(bigint,[size])*8)+N’KB’,
      [maxsize]=(case maxsize when 1 then N’Unlimited’ else CONVERT(nvarchar(15),convert(bigint,maxsize)*8)+N’ KB’ END),
      [growth]=(case status & 0x100000 when 0x100000 then convert(nvarchar(15),growth)+N’%’ else convert(nvarchar(15),convert(bigint,growth)*8)+ N’ KB’ end),
      [usage]=(case status & 0x40 when 0x40 then ‘log only’ else ‘data only’ end)
 from  sysaltfiles
order by dbid


Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Single User , Restricted User and Multi_user database mode in SQL Server 2008/2005

We may need to put our database in single_user mode and restricted_user mode sometime. I have observed that many of the developers are not aware with the differences between these two modes. It becomes very sensitive to choose right mode in certain situation. Let us see what it is.
Single_User Mode:
— does not keep database open only for DBO, first come and first served basis.
— Database can only be accessed by one user and that user can be any one.
Restricted_User Mode:
— restrict database for DBO user, any user can login to database as long as they have permission of db_owner, db_creator and sysadmin
Multi_User Mode:
— Default mode of database, this is normal behavior and you should put your database to Multi_User mode as long as you finish your task with Single_User/Restricted_User.
Let us see small code snippet about how we can set database to different modes.
use master
go
ALTER DATABASE adventureworks
SET single_user WITH ROLLBACK IMMEDIATE;
go
ALTER DATABASE adventureworks
SET restricted_user WITH ROLLBACK IMMEDIATE;
go
ALTER DATABASE adventureworks
SET multi_user WITH ROLLBACK IMMEDIATE;
go



Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah