String list to table row

String list to table row

Today I have small question about PL/SQL string list to table row.following simple example will help for solution. 

WITH DATABASEUSERGROUP AS
     ( SELECT 'DATABASEUSERGROUP01,DATABASEUSERGROUP002,
DATABASEUSERGROUP0003,,N/A,
DATABASEUSERGROUP00004,DATABASEUSERGROUP000005' strList FROM dual
      )
    SELECT trim(REGEXP_SUBSTR(strList, '[^,]+', 1, LEVEL)) strList
    FROM DATABASEUSERGROUP
    CONNECT BY INSTR(strList, ',', 1, LEVEL - 1) > 0;

CLR function and procedures in SQL Server(GZipStream)

CLR function and procedures in SQL Server(GZipStream)

Today I have work with some CLR function and procedures in SQL Server. And plan to publish some CLR code example in my weblog. 


following code will explain,

• How to Write CLR function using C# and T-SQL.
• How to add “GZipStream” in to CLR function.
• How to compress and decompress given string.
• How to Create “ASSEMBLY” using T-SQL.

First will create C# code for CLR Assembly mapping DLL,

using System;using System.Collections.Generic;

using System.Text;using System.IO;

using System.IO.Compression;using System.IO.IsolatedStorage;

using System.Data.SqlTypes;

using System.Data.SqlClient;

using Microsoft.SqlServer.Server;

    public class GZipDatabaseUserGroup
    {
        public static byte[] GZip(string value)
        {
            byte[] byteArray = Encoding.UTF8.GetBytes(value);
            MemoryStream ms = new MemoryStream();
            GZipStream zip = new GZipStream(ms, CompressionMode.Compress);
            zip.Write(byteArray, 0, byteArray.Length);
            zip.Close();
            byteArray = ms.ToArray();
            ms.Close();
            zip.Dispose();
            ms.Dispose();
            return byteArray;
        }

        public static string unGZip(byte[] data)
        {
            MemoryStream output = new MemoryStream();
            MemoryStream ms = new MemoryStream(data);
            GZipStream zip = new GZipStream(ms, CompressionMode.Decompress);
            zip.CopyTo(output);          
            string strOutPut = Encoding.UTF8.GetString(output.ToArray());
            return strOutPut.ToString();
        }

        public static string fn_CLR_DecompressGZIPBytesAsString(byte[] ZipInput)
        {
            try
            {
                string returnVal = unGZip(ZipInput);
                return returnVal;
            }
            catch
            {
                return "Error";
            }
        }

        public static byte[] fn_CLR_CompressGZIPStringToBytes(string ZipInput)
        {
            try
            {
                byte[] returnVal = GZip(ZipInput);
                return returnVal;
            }
            catch
            {
                return null;
            }
        }
    }

using above C# code you can build DLL. And next with using above DLL you can create  assembly as follows,

CREATE ASSEMBLY <ASSEMBLY NAME> from '<DLL Path>\GZipDatabaseUserGroup.dll' WITH PERMISSION_SET = SAFE;

Using above Assembly name, you can create CLR function as follows,

CREATE FUNCTION fn_CompressGZIPStringToBytes(@InPut nvarchar(max)) RETURNS varbinary(max)ASEXTERNAL NAME <ASSEMBLY NAME>.<C# Name>.<C# function name>;GO

Hope now you have some idea about how to create CLR function.And this will need bit C# knowledge, if you are not friendly with C#, then you can get some help from Microsoft MVA.

Well! now i'll show you full T-SQL code example for above task,

use master
GO
EXEC sp_configure 'allow updates', 0
RECONFIGURE
GO
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

USE [DATABASEUSERGROUP]
GO
IF  EXISTS (SELECT 1 FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[GZipDatabaseUserGroup_Table]') AND [type] in (N'U')) BEGIN
DROP TABLE [dbo].[GZipDatabaseUserGroup_Table];
END
GO
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[GZipDatabaseUserGroup_Table]') AND [type] in (N'U')) BEGIN
CREATE TABLE [dbo].[GZipDatabaseUserGroup_Table](
[id] [int] NULL,
[strData] [nvarchar](4000) NULL,
[unGzipBinary] [varbinary](max) NULL,
[GzipBinary] [varbinary](max) NULL
); 
END
GO

IF  EXISTS (SELECT 1 FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[fn_DecompressGZIPBytesAsString]') AND [type]in (N'FN', N'IF', N'TF', N'FS', N'FT')) BEGIN
DROP FUNCTION [dbo].[fn_DecompressGZIPBytesAsString];
END
GO

IF  EXISTS (SELECT 1 FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[fn_CompressGZIPStringToBytes]') AND [type]in (N'FN', N'IF', N'TF', N'FS', N'FT')) BEGIN
DROP FUNCTION [dbo].[fn_CompressGZIPStringToBytes];
END
GO

IF EXISTS (SELECT 1 FROM sys.assemblies WHERE [name]='GZipDatabaseUserGroup') BEGIN
DROP ASSEMBLY GZipDatabaseUserGroup;
END
GO

IF NOT EXISTS (SELECT 1 FROM sys.assemblies WHERE [name]='GZipDatabaseUserGroup') BEGIN
CREATE ASSEMBLY GZipDatabaseUserGroup from 'E:\databaseusergroup\bin\Debug\GZipDatabaseUserGroup.dll' WITH PERMISSION_SET = SAFE;
END
GO

CREATE FUNCTION fn_CompressGZIPStringToBytes
(@InPut nvarchar(max)) RETURNS varbinary(max)
AS
EXTERNAL NAME GZipDatabaseUserGroup.GZipDatabaseUserGroup.fn_CLR_CompressGZIPStringToBytes
GO

CREATE FUNCTION fn_DecompressGZIPBytesAsString
(@InPut varbinary(max)) RETURNS nvarchar(max)
AS
EXTERNAL NAME GZipDatabaseUserGroup.GZipDatabaseUserGroup.fn_CLR_DecompressGZIPBytesAsString;
GO

--insert data
INSERT INTO [dbo].[GZipDatabaseUserGroup_Table]([id],[strData],[unGzipBinary],[GzipBinary]) 
VALUES(1, 'Database User Group [http://www.databaseusergroup.com/] Validate varbinary Insert Data to Microsoft SQL Server',
CAST(N'Database User Group [http://www.databaseusergroup.com/] Validate varbinary Insert Data to Microsoft SQL Server' AS varbinary(max)),
dbo.fn_CompressGZIPStringToBytes('Database User Group [http://www.databaseusergroup.com/] Validate varbinary Insert Data to Microsoft SQL Server')) ;
GO
SELECT DATALENGTH([unGzipBinary])unGzipBinary,DATALENGTH([GzipBinary])GzipBinary,* FROM [dbo].[GZipDatabaseUserGroup_Table];
GO

SELECT dbo.fn_DecompressGZIPBytesAsString([GzipBinary]) GzipBinary,* FROM [DATABASEUSERGROUP].[dbo].[GZipDatabaseUserGroup_Table];
GO


use master
GO
EXEC sp_configure 'allow updates', 0
RECONFIGURE
GO
sp_configure 'clr enabled', 0;
GO
RECONFIGURE;
GO
sp_configure 'show advanced options', 0;
GO
RECONFIGURE;
GO

Cheers...

and Most welcome your comments and ideas...

      

SQL Server - Linked Servers

SQL Server - Linked Servers

Hope most of SQL Server DBA or Developer knows how to create link server in SQL Server.according to Microsoft  "the OLE DB provider (DLL) for that data source must be present on the same server as the instance of SQL Server. " then you are eligibale to create link server between two data sources. This following picture will explain great view of link servers,(this picture used from MSDN)

• How to create link server between two SQL Servers,

USE master
GO
--MSSQL
DECLARE @LINKED_REMOTE_SERVER_NAME nvarchar(128) = 'PC_DATABASEUSERGROUP'; -- provide the linking server name
IF NOT EXISTS (SELECT 1 FROM [master].[dbo].sysservers WHERE srvname = @LINKED_REMOTE_SERVER_NAME) BEGIN
EXEC master.dbo.sp_addlinkedserver
@server=@LINKED_REMOTE_SERVER_NAME,
@srvproduct='SQL Server';
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=@LINKED_REMOTE_SERVER_NAME,
@useself='False',
@locallogin=NULL,
@rmtuser='sa',
@rmtpassword='<sa password>';--you can used any user name and pwd with sysadmin role
EXEC master.dbo.sp_serveroption
@server=@LINKED_REMOTE_SERVER_NAME,
@optname=N'query timeout',
@optvalue=N'36000';

 EXEC master.dbo.sp_serveroption
  @server=@LINKED_REMOTE_SERVER_NAME,
  @optname=N'remote proc transaction promotion',
  @optvalue=N'true'; 

END ELSE
BEGIN
PRINT  'This Linked server '+ @LINKED_REMOTE_SERVER_NAME +' already exists!';
END
BEGIN TRY
EXEC sp_testlinkedserver @LINKED_REMOTE_SERVER_NAME;
PRINT 'This Linked server [' + @LINKED_REMOTE_SERVER_NAME + '] successfully created!';
END TRY
BEGIN CATCH PRINT 'This Linked server [' + @LINKED_REMOTE_SERVER_NAME + '] create fail! - ' + ERROR_MESSAGE();
END CATCH

• How to create link server between SQL Server & Oracle,

USE master

GO

--ORACLE

DECLARE @LINKED_REMOTE_SERVER_NAME nvarchar(128) = 'ORA_DATABASEUSERGROUP'; -- provide the linking server name

IF NOT EXISTS (SELECT 1 FROM [master].[dbo].sysservers WHERE srvname = @LINKED_REMOTE_SERVER_NAME) BEGIN

EXEC master.dbo.sp_addlinkedserver 

@server = @LINKED_REMOTE_SERVER_NAME, 

@srvproduct=N'Oracle', 

@provider=N'OraOLEDB.Oracle', 

@datasrc=@LINKED_REMOTE_SERVER_NAME;

EXEC master.dbo.sp_addlinkedsrvlogin 

@rmtsrvname=@LINKED_REMOTE_SERVER_NAME,

@useself=N'False',

@locallogin=NULL,

@rmtuser='ORA_USER1',

@rmtpassword='<password>';

EXEC master.dbo.sp_serveroption 

@server=@LINKED_REMOTE_SERVER_NAME,

@optname=N'query timeout',

@optvalue=N'36000';

EXEC master.dbo.sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'AllowInProcess', 1;

END ELSE

BEGIN

PRINT  'This Linked server '+ @LINKED_REMOTE_SERVER_NAME +' already exists!';

END

BEGIN TRY

EXEC sp_testlinkedserver @LINKED_REMOTE_SERVER_NAME;

PRINT 'This Linked server [' + @LINKED_REMOTE_SERVER_NAME + '] successfully created!';

END TRY

BEGIN CATCH

PRINT 'This Linked server [' + @LINKED_REMOTE_SERVER_NAME + '] create fail! - ' + ERROR_MESSAGE();

END CATCH

• Drop Link Server

USE master
GO
--DROP LINK SERVER
DECLARE @LINKED_REMOTE_SERVER_NAME nvarchar(128) = 'PC_DATABASEUSERGROUP' -- provide the linking server name
IF EXISTS (SELECT 1 FROM [master].[dbo].sysservers WHERE srvname = @LINKED_REMOTE_SERVER_NAME) BEGIN EXEC master.dbo.sp_dropserver
@server=@LINKED_REMOTE_SERVER_NAME,
@droplogins='droplogins';
END

Well! hope above script will help you to manage SQL Server link servers.

Cheers...

and Most welcome your comments and ideas...

Delete SQL Server ErrorLog

Delete SQL Server ErrorLog

After long time i have come up issue "SQL Server ErrorLog" grown to a very large size. I have done this SQL Server 2005 and after that, I have never come up this issue. 

~~~
This Monday, I have small issue (most common one :) ) SQL Server Database server have very less disk space.   
As usual, I have verify transaction log grown in "tempdb" database because our most of temp databases(all local test database environments) located in C drive. But unexpectedly it's normal, and I was bit surprised! 
finally I have just check SQL Server error log(As a best practices any DBA this should check first unfortunately I haven't follows that :( )               
well! well! I was bit happy because after long time I have face this issue. :D :D 
~~~

use master 

GO
EXEC sp_cycle_errorlog;
GO

And I have check MSDN, Did SQL Server come up with any new method to face above issue. but according to Microsoft still we have to follows old way. 

with above command you can shift SQL Server error log one by one and last log will be deleted.And with restating SQL Server instance also will be apply same method in background.  

Cheers...
and Most welcome your comments and ideas...

CREATE DATABASE SNAPSHOT GIVEN DATABASE

CREATE DATABASE SNAPSHOT GIVEN DATABASE

                            Sometimes you have noticed, when you need to create database snapshot of given database, then SQL Server management studio will not support that facility for your self.The only way is, you have to execute "create database snapshot" T-SQL script.
And at the same time, if your database contains many data files in different disks, then you have to take some time to create script for given database. 
And same as if you need to change snapshot location to different disk then, again you need to change it manually.

Hope this T-SQL script will help you above issues,

SET NOCOUNT ON

DECLARE @database_name nvarchar(128)='<Your Database Name>';

DECLARE @database_snapshot_location nvarchar(128)='';--if this is blank then snapshot create same location

DECLARE @ssql nvarchar(max)='';

SET @ssql='CREATE DATABASE ' +@database_name +'_SNAPSHOT  ON '

SELECT @ssql=@ssql+ssql_data FROM

(

SELECT  TOP (100) PERCENT N'( NAME = N'''+name+''' , FILENAME = '+CASE WHEN @database_snapshot_location='' THEN ' N'''+REPLACE(REPLACE(physical_name,'.mdf','_SNAPSHOT_DATA_FILE.ss'),'.ndf','_SNAPSHOT_DATA_FILE.ss') ELSE ''''+ @database_snapshot_location+name+'.ss' END+''' )'+CASE WHEN (MAX([file_id]) OVER(PARTITION BY database_id))<>[file_id] THEN ',' ELSE '' END+char(10)+char(13) ssql_data

FROM sys.master_files WHERE (database_id =DB_ID(@database_name)) AND ([type]=0) 

ORDER BY [file_id] 

) T;

SET @ssql=@ssql+' AS SNAPSHOT OF ' +@database_name +' ;'

PRINT @ssql

EXEC(@ssql);

    

Cheers...
and Most welcome your comments and ideas...

MEMORY OPTIMIZED TABLE

MEMORY OPTIMIZED TABLE

Today I was work with “MEMORY OPTIMIZED TABLE” SQL Server 2014 as R&D.This is very interesting feature in SQL Server 2014. According to Microsoft,
 

• MEMORY OPTIMIZED TABLES located in Memory above table contains data written to memory and read from memory. But additional copy(second copy) of the table data is maintained on disk(special file group call “MEMORY_OPTIMIZED_DATA”), but only for durability purposes.

• Data Written and Read much more faster.(First time data load from disk bit slow when you have normal table)

• This table types more likely normal table apart from some limitation

• This will support limited data types

• You can not use Truncate command
• Msg 10794, Level 16, State 92, Line 155
  The statement 'TRUNCATE TABLE' is not supported with memory optimized tables.

• If “DURABILITY=SCHEMA_AND_DATA” then maintained additional copy or If “DURABILITY=SCHEMA_ONLY” then it wont keep data in disk.

• Some table functionality wont supports this, like FK(more info you can find above Microsoft link)

First need to create database with file group(MEMORY_OPTIMIZED_DATA)

IF NOT EXISTS(SELECT 1 FROM sys.sysdatabases WHERE (name='DATABASEUSERGROUP')) BEGIN
    CREATE DATABASE [DATABASEUSERGROUP]
     CONTAINMENT = NONE
     ON  PRIMARY
    ( NAME = N'DATABASEUSERGROUP', FILENAME = N'E:\MSSQL\2014\MSSQL12.MSSQL2014\MSSQL\DATA\DATABASEUSERGROUP.mdf' , SIZE = 5120KB , FILEGROWTH = 1024KB ),
     FILEGROUP [MEM_OPZ_FG] CONTAINS MEMORY_OPTIMIZED_DATA
    ( NAME = N'DATABASEUSERGROUP_MOF', FILENAME = N'E:\MSSQL\2014\MSSQL12.MSSQL2014\MSSQL\DATA\DATABASEUSERGROUP_MOF.ndf' )
     LOG ON
    ( NAME = N'DATABASEUSERGROUP_log', FILENAME = N'E:\MSSQL\2014\MSSQL12.MSSQL2014\MSSQL\DATA\DATABASEUSERGROUP_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%);
END
GO

After create database, next we will create OM tables with normal table.

USE DATABASEUSERGROUP
GO
CREATE TABLE MO_SD_tbl(col1 int not null PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=2000000),
                    col2 varchar(25),
                    col3 datetime,
                    col4 bit)
WITH (MEMORY_OPTIMIZED=ON,DURABILITY=SCHEMA_AND_DATA);
GO
CREATE TABLE MO_S_tbl(col1 int not null PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=2000000),
                    col2 varchar(25),
                    col3 datetime,
                    col4 bit)
WITH (MEMORY_OPTIMIZED=ON,DURABILITY=SCHEMA_ONLY);
GO
CREATE TABLE Default_tbl(col1 int not null PRIMARY KEY NONCLUSTERED,
                    col2 varchar(25),
                    col3 datetime,
                    col4 bit);
GO
SELECT name,type_desc,is_memory_optimized,[durability],[durability_desc] FROM sys.tables WHERE name IN('MO_SD_tbl','MO_S_tbl','Default_tbl');
GO

OK! Lets start, first i have inserted above three tables 1M records with loop. lets check execution statistics of following table inserts,
 

--Insert data MO_SD_tbl
SET NOCOUNT ON
DECLARE @i int = 1000000

BEGIN TRAN
WHILE @i > 0  BEGIN
    INSERT INTO MO_SD_tbl VALUES (@i, 'varchar('+CAST(@i AS varchar(25))+')' ,SYSDATETIME() , 1);
    SET @i -= 1
END
COMMIT TRAN
SET NOCOUNT OFF
--Time 7 Seconds
GO


--Insert data MO_S_tbl
SET NOCOUNT ON
DECLARE @i int = 1000000

BEGIN TRAN
WHILE @i > 0  BEGIN
    INSERT INTO MO_S_tbl VALUES (@i, 'varchar('+CAST(@i AS varchar(25))+')' ,SYSDATETIME() , 1);
    SET @i -= 1
END
COMMIT TRAN

SET NOCOUNT OFF
--Time 5 Seconds
GO


--Insert data MO_S_tbl
SET NOCOUNT ON
DECLARE @i int = 1000000

BEGIN TRAN
WHILE @i > 0  BEGIN
    INSERT INTO Default_tbl VALUES (@i, 'varchar('+CAST(@i AS varchar(25))+')' ,SYSDATETIME() , 1);
    SET @i -= 1
END
COMMIT TRAN

SET NOCOUNT OFF
--Time 17 Seconds
GO

well... well... now you can get some idea "MEMORY OPTIMIZED TABLES" much more fast than normal tables.

SELECT COUNT(*) FROM Default_tbl;
SELECT COUNT(*) FROM MO_SD_tbl;
SELECT COUNT(*) FROM MO_S_tbl;
GO 

SELECT  * FROM Default_tbl;
SELECT  * FROM MO_SD_tbl;
SELECT  * FROM MO_S_tbl;
GO
DELETE FROM Default_tbl;--11 Seconds
GO 
DELETE FROM MO_SD_tbl;--less than 1 Seconds
GO
DELETE FROM MO_S_tbl;--less than 1 Seconds
GO

For "MEMORY OPTIMIZED TABLES" SQL Server 2014 newly introduce natively compiled stored procedure.which is significant fast method to insert,update or Delete.

-- natively compiled stored procedure for MO_SD_tbl Table
 CREATE PROCEDURE dbo.proc_MO_SD_tbl
 WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
 AS
 BEGIN ATOMIC
    WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
   
    DECLARE @i int = 1000000
   
    WHILE @i > 0  BEGIN
        INSERT INTO dbo.MO_SD_tbl VALUES (@i, 'varchar('+CAST(@i AS varchar(25))+')' ,SYSDATETIME() , 1);
        SET @i -= 1
    END    
 END
 GO

 -- natively compiled stored procedure for MO_S_tbl Table
 CREATE PROCEDURE dbo.proc_MO_S_tbl  WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER  AS
 BEGIN ATOMIC
    WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
   
    DECLARE @i int = 1000000
   
    WHILE @i > 0  BEGIN
        INSERT INTO dbo.MO_S_tbl VALUES (@i, 'varchar('+CAST(@i AS varchar(25))+')' ,SYSDATETIME() , 1);
        SET @i -= 1
    END    
 END
 GO

  -- compiled stored procedure for Default_tbl Table
 CREATE PROCEDURE dbo.proc_Default_tbl
 AS
 BEGIN    
    SET NOCOUNT ON
    DECLARE @i int = 1000000
    BEGIN TRAN
    WHILE @i > 0  BEGIN
        INSERT INTO dbo.Default_tbl VALUES (@i, 'varchar('+CAST(@i AS varchar(25))+')' ,SYSDATETIME() , 1);
        SET @i -= 1
    END    
    COMMIT TRAN
 END
 GO

 EXEC proc_MO_S_tbl --1 Seconds
 GO
 EXEC proc_MO_SD_tbl--1 Seconds
 GO
 EXEC proc_Default_tbl--15 Seconds
 GO

Hope you have get some idea for natively compiled stored procedure(according to Microsoft this is compile as C++)

• After CHECKPOINT above memory optimized tables wont be any issues(Data wont removed) 
• If you restart your SQL Server service then memory optimized tables which is categorizes under "SCHEMA_ONLY" will remove data others remain as same.
• These tables not like "#" or "Declare @T Table" you can access any sessions(two diffrnt SPIDs).
• I haven't tested this, but backups also work as "DURABILITY" option. 

Cheers...
and Most welcome your comments and ideas...

Database Space Usage

Database Space Usage

                                                    Today I have a request for T-SQL script needed for Database Space Usage and Estimated Space Need.I have decided to publish my database space usage script(this is only modified script not full script, full script contains table and index wise space usage in later article I have plan to publish it). following script,

• Calculated three decimal places (current situation we no need to but script will supports :) )
• You can create SQL Agent Job using following script and schedule it.
•  All data contains "tblDatabaseSpaceUsed" table and you can reuse it.

USE [master]
GO
DECLARE @ssql                nvarchar(MAX);
DECLARE @DatabaseName        sysname;
  
IF NOT EXISTS (SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[tblDatabaseSpaceUsed]') AND [type] in (N'U'))
BEGIN
    CREATE TABLE [dbo].[tblDatabaseSpaceUsed](
        ROW_ID                [bigint] IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
        CUR_DATE            [date] NOT NULL,
        DATABASE_NAME        [sysname] NOT NULL,
        LOGICAL_NAME        [sysname] NOT NULL,
        FILE_SIZE_MB        [decimal](18, 3) NULL,
        SPACE_USED_MB        [decimal](18, 3) NULL,
        FREE_SPACE_MB        [decimal](18, 3) NULL,
        DB_FILE_NAME        [sysname] NOT NULL
    );

    CREATE CLUSTERED INDEX [CIX_tblDatabaseSpaceUsed_DB_NAME] ON [dbo].[tblDatabaseSpaceUsed]([DATABASE_NAME] ASC);

END


DECLARE CurDBSpace CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
SELECT name FROM sys.databases WHERE [state]=0 ORDER BY database_id;
  
OPEN CurDBSpace;
WHILE 1 = 1
BEGIN
    FETCH NEXT FROM CurDBSpace INTO @DatabaseName;
    IF @@FETCH_STATUS = -1 BREAK;
    SET @ssql = N'USE '
        + QUOTENAME(@DatabaseName)
        + CHAR(13)+ CHAR(10)
        + N'INSERT INTO [master].[dbo].[tblDatabaseSpaceUsed](CUR_DATE,DATABASE_NAME,LOGICAL_NAME,FILE_SIZE_MB,SPACE_USED_MB,FREE_SPACE_MB,DB_FILE_NAME)'+ CHAR(13)+ CHAR(10)+
    'SELECT
         GETDATE()
        ,DB_NAME()
        ,f.name
        ,CONVERT(decimal(12,2),round(f.size/128.000,3))
        ,CONVERT(decimal(12,2),round(fileproperty(f.name,''SpaceUsed'')/128.000,3))
        ,CONVERT(decimal(12,2),round((f.size-fileproperty(f.name,''SpaceUsed''))/128.000,3))
        ,f.name
    FROM sys.database_files f;';

    EXEC sp_executesql @ssql;
  
END
CLOSE CurDBSpace;
DEALLOCATE CurDBSpace;

with using above script data. I have written for another small T-SQL script for get  daily Space usage and "Estimated Space Need". you can modify this for your own requirement with using "tblDatabaseSpaceUsed" this table, 

DECLARE @NextNoOfDays    smallint=30;

SELECT Prv.Prv_RowID RowID,Prv.CUR_DATE,ISNULL(Crnt.CUR_DATE,Prv.CUR_DATE) CUR_DATE,Prv.Prv_SPACE_USED_MB,ISNULL(Crnt.Crnt_SPACE_USED_MB,0) Crnt_SPACE_USED_MB,ISNULL(Crnt.Crnt_SPACE_USED_MB,Prv.Prv_SPACE_USED_MB)-Prv.Prv_SPACE_USED_MB DIFF_SPACE_USED_MB,
ISNULL(SUM(ISNULL(Crnt.Crnt_SPACE_USED_MB,Prv.Prv_SPACE_USED_MB)-Prv.Prv_SPACE_USED_MB) OVER(PARTITION BY NULL),1) /ISNULL(COUNT(*) OVER(PARTITION BY NULL),1)*@NextNoOfDays NextNoOfDaysEstimatedSpaceNeedMB
FROM
    (
        SELECT ROW_NUMBER() OVER(ORDER BY CUR_DATE) AS Prv_RowID,CUR_DATE,SUM(FILE_SIZE_MB) Prv_FILE_SIZE_MB,SUM(SPACE_USED_MB) Prv_SPACE_USED_MB,SUM(FREE_SPACE_MB) Prv_FREE_SPACE_MB
        FROM tblDatabaseSpaceUsed WHERE DATABASE_NAME='AdventureWorks'
        GROUP BY CUR_DATE
    ) Prv LEFT JOIN
    (
        SELECT (ROW_NUMBER() OVER(ORDER BY CUR_DATE)-1) AS Crnt_RowID,CUR_DATE,SUM(FILE_SIZE_MB) Crnt_FILE_SIZE_MB,SUM(SPACE_USED_MB) Crnt_SPACE_USED_MB,SUM(FREE_SPACE_MB) Crnt_FREE_SPACE_MB
        FROM tblDatabaseSpaceUsed WHERE DATABASE_NAME='AdventureWorks'
        GROUP BY CUR_DATE
    ) Crnt ON Prv.Prv_RowID=Crnt.Crnt_RowID

If you have many databases in your SQL Server instance then you have to done small amendments for above given T-SQL script as follows, 

DECLARE @NextNoOfDays    smallint=30;

SELECT Prv.Prv_DATABASE_NAME,Prv.CUR_DATE,ISNULL(Crnt.CUR_DATE,Prv.CUR_DATE) CUR_DATE,Prv.Prv_SPACE_USED_MB,ISNULL(Crnt.Crnt_SPACE_USED_MB,0) Crnt_SPACE_USED_MB,ISNULL(Crnt.Crnt_SPACE_USED_MB,Prv.Prv_SPACE_USED_MB)-Prv.Prv_SPACE_USED_MB DIFF_SPACE_USED_MB,
ISNULL(SUM(ISNULL(Crnt.Crnt_SPACE_USED_MB,Prv.Prv_SPACE_USED_MB)-Prv.Prv_SPACE_USED_MB) OVER(PARTITION BY Prv.Prv_DATABASE_NAME),1) /ISNULL(COUNT(*) OVER(PARTITION BY Prv.Prv_DATABASE_NAME),1)*@NextNoOfDays NextNoOfDaysEstimatedSpaceNeedMB
FROM
    (
        SELECT ROW_NUMBER() OVER(PARTITION BY DATABASE_NAME ORDER BY CUR_DATE) AS Prv_RowID,DATABASE_NAME Prv_DATABASE_NAME,CUR_DATE,SUM(FILE_SIZE_MB) Prv_FILE_SIZE_MB,SUM(SPACE_USED_MB) Prv_SPACE_USED_MB,SUM(FREE_SPACE_MB) Prv_FREE_SPACE_MB
        FROM tblDatabaseSpaceUsed WHERE DATABASE_NAME NOT IN('master','model','msdb')
        GROUP BY DATABASE_NAME,CUR_DATE
    ) Prv LEFT JOIN
    (
        SELECT (ROW_NUMBER() OVER(PARTITION BY DATABASE_NAME ORDER BY CUR_DATE)-1) AS Crnt_RowID,DATABASE_NAME Crnt_DATABASE_NAME,CUR_DATE,SUM(FILE_SIZE_MB) Crnt_FILE_SIZE_MB,SUM(SPACE_USED_MB) Crnt_SPACE_USED_MB,SUM(FREE_SPACE_MB) Crnt_FREE_SPACE_MB
        FROM tblDatabaseSpaceUsed WHERE DATABASE_NAME NOT IN('master','model','msdb')
        GROUP BY DATABASE_NAME,CUR_DATE
    ) Crnt ON Prv.Prv_RowID=Crnt.Crnt_RowID AND Prv.Prv_DATABASE_NAME=Crnt.Crnt_DATABASE_NAME
ORDER BY Prv.Prv_DATABASE_NAME

Cheers...
and Most welcome your comments and ideas...

Unexpected lock in SQL Server

Unexpected lock in SQL Server

                                Yesterday I have an issue with unexpected lock in SQL Server. I have open MSDN forum post regarding this.  

My SQL Server Info,

 

First create table and insert some data as follows,

--Script 01

USE [DATABASEUSERGROUP]
GO
DROP TABLE [dbo].[tblChild];
GO
CREATE TABLE [dbo].[tblChild]([id] [int] IDENTITY(1,1) NOT NULL,[Parentid] [int] NULL,[category] [int] NULL,[Childname] [varchar](100) NULL,[cdate] [datetime] NULL,[statusof] [bit] NULL);
GO
INSERT INTO [dbo].[tblChild]([Parentid],[category],[Childname],[cdate],[statusof])
SELECT id,xtype,name,GETDATE(),0 FROM sys.syscolumns;
GO 2000
GO
CREATE CLUSTERED INDEX [CIX_tblChild_Parentid] ON [dbo].[tblChild]([Parentid] ASC);
--CREATE CLUSTERED INDEX [CIX_tblChild_category] ON [dbo].[tblChild]([category] ASC);
--CREATE CLUSTERED INDEX [CIX_tblChild_id] ON [dbo].[tblChild]([id] ASC);
GO

Next execute following Update command in one SPID(Ex: SPID=51),

--Script 02

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

SELECT session_id,Transaction_Isolation_Level FROM sys.dm_exec_sessions WHERE session_id=51;

BEGIN TRAN
SELECT  [id]
      ,[Parentid]
      ,[category]
      ,[Childname]
      ,[cdate]
      ,[statusof]
  FROM [DATABASEUSERGROUP].[dbo].[tblChild] WHERE id=1;

UPDATE [DATABASEUSERGROUP].[dbo].[tblChild] SET [Childname]='test' WHERE id=1;
--ROLLBACK TRAN
--COMMIT TRAN

Note : do not commit or rollback above Update and open another window and hit update again as follows,(Ex: SPID=54),

--Script 03

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

SELECT session_id,Transaction_Isolation_Level FROM sys.dm_exec_sessions WHERE session_id=54

BEGIN TRAN
SELECT  [id]
      ,[Parentid]
      ,[category]
      ,[Childname]
      ,[cdate]
      ,[statusof]
  FROM [DATABASEUSERGROUP].[dbo].[tblChild] WHERE id=2;

UPDATE [DATABASEUSERGROUP].[dbo].[tblChild] SET [Childname]='test1' WHERE id=2;

--ROLLBACK TRAN
--COMMIT TRAN

Hope now you will notice your second window is lock by first update.But after change Clustered Index to ID lock behavior totally different.  (Like First SQL script 01) still wondering why above lock behavior...