SQL Server 2008 Cluster configuration on Virtual Server 2005

Today I finished SQL Server 2008 Cluster installation on Virtual Server 2005.
It was not difficult in the end but, I encountered with lots of changes in the configuration as compared to SQL Server 2005. Couple of new things have been added in SQL Server 2008 clustering configuration including clustering setup, Shared drive configuration etc...

The environment I used is as follows.
1. Virtual Server 2005 SP1
2. 3 Virtual machines of Windows 2008 running on it.
3. SQL Server 2008
4. ISCSI Drives using StarWind.

Steps I followed.
1. Create a Parent Virtual Machine.
2. Make that machine to work as a Domain controller
3. Create 2 Clone machine of DC to work as Cluster Nodes.
4. Create a Private/Public network between all 3 machines.
5. Add other 2 machine in the Domain.
6. Create ISCSI Drives which can be used as shared drive in the configuration.
7. Configure Machine1 as a Failover cluster Node1 with ISCSI Drive.
8. Configure Machine2 as a Failover Cluster Node2 With ISCSI Drive.
9. Configure MSDTC Service in the cluster setup.
10. Install SQL Server 2008 as Cluster on Node1.
11. Add Node2 in the cluster configuration.

In the end it was good learning for me, will be sharing the details as how to configure soon!!

ERROR:Property IsLocked is not available

Yesterday one of my friend was facing following issue.
When he was trying to edit one of the SQL Server Login property manually using GUI, as soon as he was clicking on that login SQL Server was giving following error.
Error:-
"Property IsLocked is not available for Login '[sa]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)"

Root Cause:-
"This issue arise when you enforce check_policy for our SQL Auth accounts. So if your check_policy is on and your password has been expired or your password has been locked due to number of wrong attempts. it may not allow you to open the login window and it will through the error."

Resolution:-

To resolve this issue you will have to alter the login in either one of the 2 was following way.
1. if you are fine with changing the password.
alter login (Login name) with password = 'yourpwd' unlock,check_policy = off,check_expiration = off

Or

2.if you dont want to change the password.
alter login (Login name) with check_policy = off,check_expiration = off

Happy Learning.

Collecting SQL Server database information, All at one place.

AS a DBA Job time to time we have to do trend analysis or we have to find database related information to perform our day to day tasks. The information we generally try to capture or need is mentioned below.

1.Server/Instance Name
2.Database Name
3.Database File Names (both logical and full physical path)
4.File Size (In Megabytes)
5.Database Status
6.Recovery Mode
7.Free Space (In Megabytes and Percent)

There are lots of ways to find this information, but we have run couple of queries also we need to do couple of calculations to find the free space. What if we get everything at single place???
Here is the small T SQL which will give you almost everything which you are looking at for your monitoring. So you can use this query to do your Trend analysis or you can use it to monitor all your database/instances from a centralized location.

################################################################
DECLARE @DBInfo TABLE
( ServerName VARCHAR(100),
DatabaseName VARCHAR(100),
FileSizeMB INT,
LogicalFileName sysname,
PhysicalFileName NVARCHAR(520),
Status sysname,
Updateability sysname,
RecoveryMode sysname,
FreeSpaceMB INT,
FreeSpacePct VARCHAR(7),
FreeSpacePages INT,
PollDate datetime)

DECLARE @command VARCHAR(5000)

SELECT @command = 'Use [' + '?' + '] SELECT
@@servername as ServerName,
' + '''' + '?' + '''' + ' AS DatabaseName,
CAST(sysfiles.size/128.0 AS int) AS FileSize,
sysfiles.name AS LogicalFileName, sysfiles.filename AS PhysicalFileName,
CONVERT(sysname,DatabasePropertyEx(''?'',''Status'')) AS Status,
CONVERT(sysname,DatabasePropertyEx(''?'',''Updateability'')) AS Updateability,
CONVERT(sysname,DatabasePropertyEx(''?'',''Recovery'')) AS RecoveryMode,
CAST(sysfiles.size/128.0 - CAST(FILEPROPERTY(sysfiles.name, ' + '''' +
'SpaceUsed' + '''' + ' ) AS int)/128.0 AS int) AS FreeSpaceMB,
CAST(100 * (CAST (((sysfiles.size/128.0 -CAST(FILEPROPERTY(sysfiles.name,
' + '''' + 'SpaceUsed' + '''' + ' ) AS int)/128.0)/(sysfiles.size/128.0))
AS decimal(4,2))) AS varchar(8)) + ' + '''' + '%' + '''' + ' AS FreeSpacePct,
GETDATE() as PollDate FROM dbo.sysfiles'
INSERT INTO @DBInfo
(ServerName,
DatabaseName,
FileSizeMB,
LogicalFileName,
PhysicalFileName,
Status,
Updateability,
RecoveryMode,
FreeSpaceMB,
FreeSpacePct,
PollDate)
EXEC sp_MSForEachDB @command

SELECT
ServerName,
DatabaseName,
FileSizeMB,
LogicalFileName,
PhysicalFileName,
Status,
Updateability,
RecoveryMode,
FreeSpaceMB,
FreeSpacePct,
PollDate
FROM @DBInfo
ORDER BY
ServerName,
DatabaseName
################################################################

Hope this will reduce our pain.
Cheers,
Manish

SQL Server Job throws error while sending mail alerts.

Some times when you enable Database Mail profile SQL Server agent will fail to send mail through SQL Server agent Jobs.
Test mails from Database mail facility goes fine but if you put any alert through SQL Server agent Job you receive folliwng error in the error log file.
"SQL2005 [264] An attempt was made to send an email when no email session has been established"
The Fix for this issue is:
1. In Management Studio, right-click on SQL Server Agent and select "Properties."
2. Click "Alert System"
3. Unclick "Enable mail profile"
4. Click "OK"
5. right-click SQL Server Agent again and select "Properties."
6. Click "Alert System"
7. Re-click "Enable mail profile"
8. Click "OK"
9. Restart SQL Server Agent.

Enhanced Version of SP_who2 to find little bit more relevent data.

Till now if user comes and ask you, My queries are running slow, what activity is going on in the database?
The steps you usually follow is
1. First thing that DBA should do is check what processes are running in the server
2. To find out you will run either sp_who or sp_who2 active
3. Look at the biggest CPU time or disk IO process
4. Note the SP ID
5. Than run DBCC INPUTBUFFER with parameter SP ID of the process
The issue is sometimes we are not so lucky to find the culprit. We have to run the sp_who2 active and DBCC INPUTBUFFER several times.
So why we don’t enhance sp_who2 so that can display the statement when we run sp_who2.
Here is a small solution for you,This script for display active processes only along with the SQL Statements  and data order by CPU time descending. You can modified this script depend on your necessity

SET NOCOUNT ON
DECLARE
@SPID SMALLINT
,@SQLID SMALLINT
,@SqlStatement VARCHAR(4000)
IF object_id('tempdb..#tbl_sysprocesses') IS NOT NULL
DROP TABLE #tbl_sysprocesses
IF object_id('tempdb..#spid_cmds') IS NOT NULL
DROP TABLE #spid_cmds
IF object_id('tempdb..#spid') IS NOT NULL
DROP TABLE #spid
SELECT
spid
,CAST(NULL AS VARCHAR(8000)) AS commandtext
,status
,sid
,hostname
,program_name
,cmd
,cpu
,physical_io
,blocked
,dbid
,convert(SYSNAME, rtrim(loginame)) AS loginname
,substring(convert(VARCHAR,last_batch,111) ,6 ,5 )
+ ' ' + substring( convert(VARCHAR,last_batch,113) ,13 ,8 )
as 'last_batch_char'
INTO #tbl_sysprocesses
FROM master.dbo.sysprocesses (NOLOCK)
WHERE NOT(lower(status) = 'sleeping'
AND upper(cmd) IN (
'AWAITING COMMAND'
,'MIRROR HANDLER'
,'LAZY WRITER'
,'CHECKPOINT SLEEP'
,'RA MANAGER' )
AND blocked = 0)

--Show only active processes
DELETE #tbl_sysprocesses
WHERE lower(status) = 'sleeping'
AND upper(cmd) IN (
'AWAITING COMMAND'
,'LAZY WRITER'
,'CHECKPOINT SLEEP' ) AND blocked = 0
CREATE TABLE #spid_cmds
(
SQLID INT IDENTITY
,SPID INT
,EventType VARCHAR(30)
,Parameters INT
,Command VARCHAR(8000)
)
SELECT DISTINCT spid INTO #spid FROM #tbl_sysprocesses
WHILE (1 = 1)
BEGIN
SET @SPID = NULL
SELECT TOP 1 @SPID = spid FROM #spid
IF (@SPID IS NULL) GOTO _NEXT
INSERT INTO #spid_cmds (EventType, Parameters, Command)
EXEC('DBCC INPUTBUFFER( ' + @SPID + ')')
SELECT @SQLID = MAX(SQLID) FROM #spid_cmds
UPDATE #spid_cmds SET spid = @SPID WHERE SQLID = @SQLID
DELETE FROM #spid WHERE spid = @SPID
END
_NEXT:
UPDATE p
SET p.commandtext = s.command
FROM #tbl_sysprocesses p
INNER JOIN #spid_cmds s ON p.spid = s.spid
SET @SqlStatement =
'
SELECT SPID = spid
,CommandText
,Status =
rtrim(CASE lower(status)
When ''sleeping'' THEN lower(status)
Else upper(status)
END)
,Login = loginname
,HostName =
CASE hostname
When NULL THEN '' .''
When '' '' THEN '' .''
Else hostname
END
,BlkBy =
CASE ISNULL(blocked,0)
When 0 THEN 0
Else blocked
END
,DBName = case when dbid = 0 THEN NULL
when dbid <> 0 THEN db_name(dbid) END
,Command = cmd
,CPUTime = cpu
,DiskIO = physical_io
,LastBatch = last_batch_char
,ProgramName = program_name
FROM #tbl_sysprocesses
ORDER BY CPUTime DESC
'
EXEC(@SqlStatement)
DROP TABLE #tbl_sysprocesses
DROP TABLE #spid_cmds
DROP TABLE #spid
SET NOCOUNT ON