How to create table with filestream column and Insert data

How to create table with filestream column and Insert data?

Filestream data type in SQL Server can be used to store images,documents,etc., in database.

In this blog I will explain how to create table with filestream data type.

Before creating table with filestream column,we have to enable filestream feature in SQL Server configuration manager.Follow “How to enable and configure Filestream in SQL SERVER 2008 / 2012”

Create a table and adding filestream data:

1.Create Filestream enabled database.

Syntax:

CREATE DATABASE test
ON
PRIMARY ( NAME = test1,
    FILENAME = ‘c:\data\testdat1.mdf’),
FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = test3,
    FILENAME = ‘c:\data\test1’)
LOG ON ( NAME = testlog1,
    FILENAME = ‘c:\data\test1.ldf’)
GO

2.After you have a filestream enabled database.we now need to create a table and add data to it.

Syntax:

CREATE TABLE [dbo].[test](
   [ID] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
   [Number] VARCHAR(20),
   [Description] VARCHAR(50),
   [Image] VARBINARY(MAX) FILESTREAM NULL
)

3.Insert values into table. In below example I am inserting image file in to the table.

Syntax:

DECLARE @img AS VARBINARY(MAX)
— Load the image data
SELECT @img = CAST(bulkcolumn AS VARBINARY(MAX))
FROM OPENROWSET(Bulk ‘C:\image\jellyfish.jpg’, SINGLE_BLOB ) AS y
— Insert the data to the table
INSERT INTO test (ID,Number,Description, Image)
SELECT NEWID(), ‘MS1001′,’jellyfish’, @img

4.To view the table with filestream included column

Create script for all objects in database with data

You can create T-SQL scripts for database, objects in database and insert script for data in tables by using the Generate Scripts Wizard.

These scripts can be used to create database in secondary site with and with out data.

I this blog I will explain how to generate T-SQL script to create database, database objects and data.

Right click the database which you want to script—>Select tasks—>Generate scripts.

Generate and Publish Scripts wizard screen appears.click Next to continue.

In the Choose Objects wizard screen Select “Script entire database and all database objects” option if want to script all the objects in database or select specified objects which you would like to script and click Next to continue.

In the Set Scripting Options wizard screen.Select the output type as “Save scripts to a specific location” and specify the path where you would like to save the database script file .Click the Advanced button for specifying the schema and data scripting option.

In Advanced Scripting Options screen; choose the option Schema and data for the Types of data to script option if you want to generate insert scripts for rows in tables and click OK to save the changes and to return to Set Scripting Options wizard screen.

a) Data only – If this option is selected, it will only script out data within the tables
b) Schema and data – If this option is selected, it will script out Schema as well as the data within the selected objects
c) Schema only – If this option is selected, it will script out the Schema only

In the Summary wizard screen; you will be able to see a quick summary of all the options which you have selected so far. Click Next to confirm and generate the scripts.

In Save or Publish Scripts wizard screen; you will be able to see a Success or Failure message against each object for which the script was requested to be generated. Finally, click Finish to close the wizard.

Once the script is generated you can view it like any other SQL Script.

HOW TO INSTALL SQL Server CLUSTER IN HYPER-V

To install SQL Server failover cluster we need to have windows cluster configured with a shared disk.

Step 1

If you do not have a windows cluster configured you can create using Hyper-v

Follow the steps in “HOW TO CREATE CLUSTER USING HYPER-V” to create Domain controller and two member servers which will act as nodes of the cluster.

Once the domain controller and two member servers are created proceed to step 2

Step 2

Once the domain controller and nodes are created install failover cluster feature and create cluster

To install the failover cluster feature and create a cluster follow the steps in “How to install cluster in windows 2008 and windows 2012“

Step 3

Once the cluster is created we have to create shared Disk for installing failover aware clustered application like SQL Server.

Follow the steps in ”How to create shared disk using iSCSI Software Target” to create a shared disk using iSCSI target software.

Once shared disk is configured invoke Setup from SQL server installation media .Make sure the node in which you perform the installation has shared disk online.

Run the setup.

Start the installation by selecting New SQL Server Failover Cluster Installation option.

Setup support rules will analyze and identify the problems that might occur during setup installation.

After setup validation ,you will get details about operations completed,Click Next and continue

On the Product key page, specify the Product ID key of the product. Click Next.

Then read and select the check box to accept terms and agreements.Click Next.

Select the developer features which you want and click Next.

Perform instance configuration.Give SQL server network name.Either you could have default instance or named instance.But you could have only one default instance.

Click Next.

Allocate required disk space for your installation.Click Next.

Select the SQL Server cluster resource group name.Click Next and continue.

Cluster disk selection will help us to view the disk we have selected for SQL Server failover cluster.

Configure the network resource for your SQL Server failover cluster.Select DHCP check box if you are going to use DHCP network.Otherwise give the IP address for your network(IP address which you provide not be in use currently).

Select the database engine domain group and SQL Server agent domain group. These groups have to be pre created in AD before the installation and startup account of SQL Server services will be added to these groups. You can also use service SIDs option.

Specify the account name and password for SQL server agent and SQL server database engine.Click Next.

Specify the authentication mode for your SQL Server failover cluster.Click Next and continue your installation.Verify the cluster installation rules.

Enable error reporting service if you want or else,click Next and continue.

Cluster installation rules will run the setup to determine whether any operations will block the installation.After operation completes.Click Next and continue.

Ready to install page will display the summary of options which we have selected during the setup process.If you want to make any changes,go back and make the required changes or else Click Install and start the installation.

Click Close and Complete the installation process.

Successfully we have installed the SQL server failover cluster in first node.

We can install the SQL server failover cluster in remaining nodes by selecting Add node to a SQL Server failover cluster in installation centre window. To add node to existing SQL Server failover cluster follow the steps in “How to add node to SQL Server cluster”

June 24, 2013 by Pradeepa

How to add node to SQL Server cluster

We can add nodes to the existing SQL server failover cluster by following below steps. (Below method can be followed to add node in SQL Server 2008, SQL Server 2008r2 and SQL Server2012)

1. To add new node to existing SQL Server failover cluster, from new node invoke Setup.exe installation media .

2.The Installation Wizard will show the SQL Server Installation Center as shown in below image.Click Installation an then, select Add node to a SQL Server failover cluster.

3.Install the setup support files.

4.After setup validation ,you will get details about operations completed,Click Next and continue.

5.On the Product key page, specify the Product ID key of the product. Click Next.

6.Then read and select the check box to accept terms and agreements.Click Next.

7.Then in cluster Node configuration choose the SQL Server instance name and Current Node which we are adding would automatically appear in “Name of this node” . Click Next and continue.

8.On the Service Accounts page, specify login accounts for SQL Server services.Specify strong password for each account.Click Next after finishing.

9.On the Reporting page by default the option for error reporting is enabled.If you don’t want, disable it and continue.

10.Add Node rules page provides the information about operation completed.

11.Ready to Add Node page displays the options that were specified during Setup. Click Install.

12.Add Node Progress page provides the status of the installation process.

13.After installation, the page provides the summary for the installation.

To complete the SQL Server installation process, click Close.

Move services or application between nodes to verify if SQL Server resources are able to failover successfully:

1.After installation you could move the service between all nodes.

2.Open failover cluster manager->expand SQL Cluster->expand services and applications->select the SQL Server group –> Move this service or application to another node –>Choose the newly added node.

3.After moving the resources to newly added Node check if all the resources are able to come online on newly added node.

June 18, 2013 by Pradeepa

HOW TO CREATE CLUSTER USING HYPER-V

Hyper –V can be used to create virtualized computers to improve the hardware efficiency. In this blog post I will try to cover how to create windows failover cluster using Hyper-V with out using multiple physical computers. Cluster we are building can be used to install Clustered SQL Server and similar cluster aware applications.

1. We will create a Domain controller

2. We will create two member servers which will act as two nodes of the cluster.

We will start the installation by Turning on window feature Hyper-V.

To enable Hyper-V Open control panel—>Programs –>Get Programs->Turn windows features on or off –>Enable Hyper-V as shown in picture below

Once the Hyper-v feature is enabled open the hyper v manager and follow the below steps one by one.

Before creating virtual machine create virtual switch to configure network access between the computers which will create and name it.

Select the type of virtual switch for your virtual machines.

Give the name for your Virtual switch and click ok.

Now create New virtual machine by Right clicking SQLPC –>New->Virtual machine.

2.New virtual machine wizard will appear.Click Next to continue.

Specify the name for your virtual machine.Let us Create domain controller as DC.

Create a new folder or use the existing folder to store the virtual machine.Specify that location in location tab.Click Next

Specify the amount of memory you want to allocate for this machine.Then,click Next.

Inorder to do OS installation ,Connect to virtual hard disk and specify the size for it.Click Next

Then install an Operating system from CD or as image file which you have in your system.

Browse and select the file for installation.Click open.

Now,click finish and complete the virtual machine wizard.

Similarly,Follow the above steps and create two cluster nodes as clusternode1 and clusternode2 .

Now Hyper –V manager will have three virtual machines which we created one as DC and others as clusternodes.

Right click DC->Start->connect.(similarly do for all)

Cluster nodes will start running.

DC virtual machine connection window will appear,click next.

Installation window will appear,click install now to start installation.

Select the operating system you want to install.Select windows server 2008 R2 Enterprise(Full installation) for DC(domain controller).Click Next.

Accept the license term and click next

Select the type of installation as Custom(advanced)

clip_image001[5]

Click Next and continue

After installation restart your virtual machine to update with new setting

Give the password for login.

Domain controller is installed with OS.

Perform the task which you want or else close the window.

Configure TCP/IP address and enable LAN connection.

To configure TCP/IP, open control panel->Network and internet->Network connections->click IPV4

Give IP address ,subnet mask and default gateway ,click ok.

Open dcpromo .

Active directory domain service wizard will appear.Click Next and continue

Select create new domain in a new forest

Give forest root domain which acts as domain.

Set forest functional level .Default Functional level is windows sever 2003.

Check DNS server and click Next.

Click Yes and continue.

Select the folder to store databases,log files and click Next.

Give the administrator password and click Next.

Summary of your selections will appear,review your selection and click next.

DNS installation will be completed.

Click Finish and complete domain services installation wizard.

Restart your computer after installation.

Now do the similar things for cluster nodes 1&2.

Select computer->properties->computer name.Change the computer name and give domain name which you gave in DC.

Configure IPV4 properties for cluster nodes.

Click OK.

Restart your computer to apply the changes.

Click Ok,to restart.

Now we have domain controller and member servers. We have to install failover cluster feature and make this members servers as node of cluster which we are going to create.

To install the failover cluster feature and create a cluster follow the steps in https://sqlserverscribbles.com/2013/06/17/how-to-install-cluster-in-windows-2008-and-windows-2012/

Once the cluster is created we have to create shared Disk for installing failover aware clustered application like SQL Server. Follow the steps in https://sqlserverscribbles.com/2013/06/16/how-to-create-shared-disk-using-iscsi-software-target/ to create a shared disk using iSCSI target software.

June 8, 2013 by Pradeepa

How to create SQL Server Agent Jobs

SQL Server agent can be used to create JOBS which can be scheduled to execute automatically. SQL Server Agent uses SQL Server to store job information. Jobs can have one or more job steps. Each step contains its own task.Each action in a job is a job step.We can schedule the job based on need as daily, weekly, monthly, hourly etc

For example, Let us create a job to back up a database automatically.

Steps to create Job in SQL Server Agent

1. Expand SQL Server Agent in Object Explorer.(If SQL Server Agent is stopped ,right click and start it)

2.Right click Job->New job.

3.Fill the Name field with a name for your job,Give description.

4. On the left side of the New Job window, you’ll see a Steps icon under the Select a page.Next add the individual steps for your job. Click the New button to create a new job step and you will see the New Job Step window.

Use the Step Name textbox to provide a name for the Step. select the database where the job has to be applied.
Use the Command textbox to give the Transact-SQL syntax corresponding to the desired action for this job step. Once you have completed entering the command, click the Parse button to verify the syntax.
Validate the syntax, click OK to create the step.

5.Schedule the job by clicking the Schedule icon in the Select a Page.Then schedule new job.

6.Create alerts for your job by Giving alert name and also select the database.Also select the type of alert and severity of your alert

7.Specify the actions to be performed when the job completes or when job fails.

Note :All the above steps can be performed by TSQL (with out the GUI) using follow stored procedures

sp_add_job to create a job.
sp_add_jobstep to create one or more job steps.
sp_add_schedule to create a schedule.
sp_attach_schedule to attach a schedule to the job.

March 4, 2013 by Karthick P.K

Trace waits in SQLServer (SQLTRACE_BUFFER_FLUSH,TRACEWRITE,SQLTRACE_WAIT_ENTRIES,SQLTRACE_LOCK)

When you run Profiler trace from client systems or on server with large number of events you will see below wait types.

SQLTRACE_WAIT_ENTRIES
SQLTRACE_LOCK
SQLTRACE_BUFFER_FLUSH
TRACEWRITE

There is no way to completely avoid this wait type without stopping all the traces. We can reduce this waitypes by configuring server side trace instead of client side trace.

select * from sys.traces will give you information about all the traces. ( Status column 0 stopped and 1 active)

For the traces collected using profiler you will find a NULL Path. Profiler traces can cause large number of above waittypes.

Thread which raises the trace event is responsible to Get buffers to write event and write event. So collecting the trace on network share or on slow disk or using profiler can slow down the trace write and make the threads wait, sometimes we may also end with dead locked schedulers.

So ideally you have to avoid running profiler when you see below waits and use sp_trace_create if you like to capture the trace and pass the local path for tracefile parameter.

March 1, 2013 by Karthick P.K

How to view the Space used by each table in database

There are situations in which you may have to list the space used by each table in the database. You can use the query below to get the space used by individual tables.
–Space used by the database

sp_spaceused

–Space used by individual tables in the database

SELECT
(row_number() over(order by a3.name, a2.name)) as SNO,
a3.name AS [schemaname],
a2.name AS [tablename],
a1.rows as row_count,
(a1.reserved + ISNULL(a4.reserved,0))* 8 AS reserved, 
a1.data * 8 AS data,
(CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN 
(a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS index_size,
(CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN 
(a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS unused
FROM
(SELECT 
ps.object_id,
SUM (
CASE
WHEN (ps.index_id < 2) THEN row_count
ELSE 0
END
) AS [rows],
SUM (ps.reserved_page_count) AS reserved,
SUM (
CASE
WHEN (ps.index_id < 2) THEN 
(ps.in_row_data_page_count + ps.lob_used_page_count + 
ps.row_overflow_used_page_count)
ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
END
) AS data,
SUM (ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
GROUP BY ps.object_id) AS a1
LEFT OUTER JOIN 
(SELECT 
it.parent_id,
SUM(ps.reserved_page_count) AS reserved,
SUM(ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
WHERE it.internal_type IN (202,204)
GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)
INNER JOIN sys.all_objects a2 ON ( a1.object_id = a2.object_id ) 
INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
WHERE a2.type <> 'S' and a2.type <> 'IT'
ORDER BY a3.name, a2.name

February 12, 2013 by Karthick P.K

Top queries by total I/O and average I/O in SQL Server

You can use the below queries to identify the top queries by total I/O and average I/O in SQL Server

–Top queries by total I/O


exec sp_executesql @stmt=N'begin try
select top 10 rank() over (order by total_logical_reads+total_logical_writes desc,sql_handle,statement_start_offset ) as row_no
, (rank() over (order by total_logical_reads+total_logical_writes desc,sql_handle,statement_start_offset ))%2 as l1
, creation_time
, last_execution_time
, (total_worker_time+0.0)/1000 as total_worker_time
, (total_worker_time+0.0)/(execution_count*1000) as [AvgCPUTime]
, total_logical_reads as [LogicalReads]
, total_logical_writes as [LogicalWrites]
, execution_count
, total_logical_reads+total_logical_writes as [AggIO]
, (total_logical_reads+total_logical_writes)/(execution_count+0.0) as [AvgIO]
, case when sql_handle IS NULL
then '' ''
else ( substring(st.text,(qs.statement_start_offset+2)/2,(case when qs.statement_end_offset = -1 then len(convert(nvarchar(MAX),st.text))*2 else qs.statement_end_offset end - qs.statement_start_offset) /2 ) )
end as query_text
, db_name(st.dbid) as database_name
, st.objectid as object_id
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(sql_handle) st
where total_logical_reads+total_logical_writes > 0
order by [AggIO] desc
end try
begin catch
select -100 AS row_no
, 1 AS l1, 1 AS creation_time, 1 AS last_execution_time, 1 AS total_worker_time, 1 AS Avg_CPU_Time, 1 AS logicalReads, 1 AS LogicalWrites
, ERROR_NUMBER() AS execution_count
, ERROR_SEVERITY() AS AggIO
, ERROR_STATE() AS AvgIO
, ERROR_MESSAGE() AS query_text
end catch',@params=N''

–Top queries by Average I/O


exec sp_executesql @stmt=N'begin try
select top 10 rank() over (order by (total_logical_reads+total_logical_writes)/(execution_count+0.0) desc,sql_handle,statement_start_offset ) as row_no
, (rank() over (order by (total_logical_reads+total_logical_writes)/(execution_count+0.0) desc,sql_handle,statement_start_offset ))%2 as l1
, creation_time
, last_execution_time
, (total_worker_time+0.0)/1000 as total_worker_time
, (total_worker_time+0.0)/(execution_count*1000) as [AvgCPUTime]
, total_logical_reads as [LogicalReads]
, total_logical_writes as [LogicalWrites]
, execution_count
, total_logical_reads+total_logical_writes as [AggIO]
, (total_logical_reads+total_logical_writes+0.0)/execution_count as [AvgIO]
, case when sql_handle IS NULL
then '' ''
else ( substring(st.text,(qs.statement_start_offset+2)/2,(case when qs.statement_end_offset = -1 then len(convert(nvarchar(MAX),st.text))*2 else qs.statement_end_offset end - qs.statement_start_offset) /2 ))
end as query_text
, db_name(st.dbid) as database_name
, st.objectid as object_id
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(sql_handle) st
where (total_logical_reads+total_logical_writes ) > 0
order by [AvgIO] desc
end try
begin catch
select -100 AS row_no
, 1 AS l1, 1 AS creation_time, 1 AS last_execution_time, 1 AS total_worker_time, 1 AS AvgCPUTime, 1 AS logicalReads, 1 AS LogicalWrites
, ERROR_NUMBER() AS execution_count
, ERROR_SEVERITY() AS AggIO
, ERROR_STATE() AS AvgIO
, ERROR_MESSAGE() AS query_text
end catch',@params=N''

January 22, 2013 by Karthick P.K

SSMS fails with below error when you connect (or) explore the databases

SQL Server management studio might fail with below error when you connect (or) explore the databases.

Error in SQL Server 2012

TITLE: Microsoft SQL Server Management Studio ——————————

Object reference not set to an instance of an object. (Microsoft.VisualStudio.Platform.WindowManagement)

——————————

Error in SQL Server 2008 and SQL Server 2005

BUTTONS:

Unable to cast COM object of type ‘System.__ComObject’ to interface type ‘Microsoft.VisualStudio.OLE.Interop.IServiceProvider’. This operation failed because the QueryInterface call on the COM component for the interface with IID ‘{6D5140C1-7436-11CE-8034-00AA006009FA}’ failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)). (Microsoft.VisualStudio.OLE.Interop)

	regsvr32 /u ieproxy.dll
	regsvr32 ieproxy.dll

Cause

1. You would get above error if ieproxy.dll is not registered properly. Re register Ieproxy.dll.

C:\Program Files\Internet Explorer>regsvr32 /u ieproxy.dll
C:\Program Files\Internet Explorer>regsvr32 ieproxy.dll
C:\Program Files (x86)\Internet Explorer>regsvr32 /u ieproxy.dll
C:\Program Files (x86)\Internet Explorer>regsvr32 ieproxy.dll

2 .Net framework corruption (Repair Dot Net)

3. regsvr32 actxprxy.dll

4. If the problem persists after doing above steps then search the registry for class-id mentioned in error message (in this error it is 6D5140C1-7436-11CE-8034-00AA006009FA). Once you find the class in registry explore ProxyStubClsid32 and copy the GUID in default key. Search for the copied GUID again in the registry and locate the DLL for which we are failing and register that Dll.

January 21, 2013 by Karthick P.K

Linked server from SQLServer2012 to SQLServer2000.

How to create Linked server from SQLServer2012 to SQLServer2000.

When you create linked server from SQLServer2012 to SQLServer2000 you cannot use SQL Native client 11 because it cannot connect with SQL Server 2000

Each version of SQL native client will support the version of SQL Server that it ships with, two earlier versions and two later versions

Ref: http://blogs.msdn.com/b/sqlnativeclient/archive/2008/02/27/microsoft-sql-server-native-client-and-microsoft-sql-server-2008-native-client.aspx

When you use SQLOLED it will switch to latest SQLNCLI.

Reference: http://msdn.microsoft.com/en-us/library/ms190479.aspx

The Microsoft SQL Server Native Client OLE DB provider is the provider that is used with SQL Server if no provider name is specified or if SQL Server is specified as the product name. Even if you specify the older provider name, SQLOLEDB, it will be changed to SQLNCLI when persisted to the catalog.

You create a data source and use MSDASQL (Microsoft OLE DB Provider for ODBC) as long as OS supports it.

Sample

sp_addlinkedserver ‘Server\Shiloh’, @provider = N’MSDASQL’, @datasrc=N’SQL2000datasource’, @location=N’System’,@srvproduct=N’MSDASQL’;

Alternatively you can use SQL Server native client 10.0 but it expects “begin distributed transaction” for all the queries.

January 18, 2013 by Karthick P.K

How to detect low memory conditions in SQL Server using ring buffers output

Use the below query to determine the low memory conditions in SQL Server using the sys.dm_os_ring_buffers It gives the historical memory usage of SQL Server and internal and external memory pressure information .


SELECT CONVERT (varchar(30), GETDATE(), 121) as [RunTime],
dateadd (ms, (rbf.[timestamp] - tme.ms_ticks), GETDATE()) as [Notification_Time],
cast(record as xml).value('(//Record/ResourceMonitor/Notification)[1]', 'varchar(30)') AS [Notification_type],
cast(record as xml).value('(//Record/MemoryRecord/MemoryUtilization)[1]', 'bigint') AS [MemoryUtilization %],
cast(record as xml).value('(//Record/MemoryNode/@id)[1]', 'bigint') AS [Node Id],
cast(record as xml).value('(//Record/ResourceMonitor/IndicatorsProcess)[1]', 'int') AS [Process_Indicator],
cast(record as xml).value('(//Record/ResourceMonitor/IndicatorsSystem)[1]', 'int') AS [System_Indicator],
cast(record as xml).value('(//Record/ResourceMonitor/Effect/@type)[1]', 'varchar(30)') AS [type],
cast(record as xml).value('(//Record/ResourceMonitor/Effect/@state)[1]', 'varchar(30)') AS [state],
cast(record as xml).value('(//Record/ResourceMonitor/Effect/@reversed)[1]', 'int') AS [reserved],
cast(record as xml).value('(//Record/ResourceMonitor/Effect)[1]', 'bigint') AS [Effect],

cast(record as xml).value('(//Record/ResourceMonitor/Effect[2]/@type)[1]', 'varchar(30)') AS [type],
cast(record as xml).value('(//Record/ResourceMonitor/Effect[2]/@state)[1]', 'varchar(30)') AS [state],
cast(record as xml).value('(//Record/ResourceMonitor/Effect[2]/@reversed)[1]', 'int') AS [reserved],
cast(record as xml).value('(//Record/ResourceMonitor/Effect)[2]', 'bigint') AS [Effect],

cast(record as xml).value('(//Record/ResourceMonitor/Effect[3]/@type)[1]', 'varchar(30)') AS [type],
cast(record as xml).value('(//Record/ResourceMonitor/Effect[3]/@state)[1]', 'varchar(30)') AS [state],
cast(record as xml).value('(//Record/ResourceMonitor/Effect[3]/@reversed)[1]', 'int') AS [reserved],
cast(record as xml).value('(//Record/ResourceMonitor/Effect)[3]', 'bigint') AS [Effect],

cast(record as xml).value('(//Record/MemoryNode/ReservedMemory)[1]', 'bigint') AS [SQL_ReservedMemory_KB],
cast(record as xml).value('(//Record/MemoryNode/CommittedMemory)[1]', 'bigint') AS [SQL_CommittedMemory_KB],
cast(record as xml).value('(//Record/MemoryNode/AWEMemory)[1]', 'bigint') AS [SQL_AWEMemory],
cast(record as xml).value('(//Record/MemoryNode/SinglePagesMemory)[1]', 'bigint') AS [SinglePagesMemory],
cast(record as xml).value('(//Record/MemoryNode/MultiplePagesMemory)[1]', 'bigint') AS [MultiplePagesMemory],
cast(record as xml).value('(//Record/MemoryRecord/TotalPhysicalMemory)[1]', 'bigint') AS [TotalPhysicalMemory_KB],
cast(record as xml).value('(//Record/MemoryRecord/AvailablePhysicalMemory)[1]', 'bigint') AS [AvailablePhysicalMemory_KB],
cast(record as xml).value('(//Record/MemoryRecord/TotalPageFile)[1]', 'bigint') AS [TotalPageFile_KB],
cast(record as xml).value('(//Record/MemoryRecord/AvailablePageFile)[1]', 'bigint') AS [AvailablePageFile_KB],
cast(record as xml).value('(//Record/MemoryRecord/TotalVirtualAddressSpace)[1]', 'bigint') AS [TotalVirtualAddressSpace_KB],
cast(record as xml).value('(//Record/MemoryRecord/AvailableVirtualAddressSpace)[1]', 'bigint') AS [AvailableVirtualAddressSpace_KB],
cast(record as xml).value('(//Record/@id)[1]', 'bigint') AS [Record Id],
cast(record as xml).value('(//Record/@type)[1]', 'varchar(30)') AS [Type],
cast(record as xml).value('(//Record/@time)[1]', 'bigint') AS [Record Time],
tme.ms_ticks as [Current Time]
FROM sys.dm_os_ring_buffers rbf
cross join sys.dm_os_sys_info tme
where rbf.ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR' --and cast(record as xml).value('(//Record/ResourceMonitor/Notification)[1]', 'varchar(30)') = 'RESOURCE_MEMPHYSICAL_LOW'
ORDER BY rbf.timestamp ASC
Go

Once you get the output of above query you can use the steps in A significant part of SQL Server process memory has been paged out to troubleshoot the issue further

January 17, 2013 by Karthick P.K

How to bring the database online when one or more data files is accidentally deleted

How to bring the database online when one or more data files is accidentally deleted.

Follow the steps below when you don’t have other options like restoring from backup. This has to be the last step to bring the database online and remember data in file which is deleted will be lost. This might cause inconsistent data.

1. Take physical file level backup of all the database files

2) Rename all the data files and log files [Ex :N:\ Data.MDF to N:\Data.MDF_original ]

3) Create a new empty database with the same physical, logical file names and file groups

4) Now take the missing file OFFLINE

5) Take the database offline and replace the original files and try to bring the database online

The database will come online, only the missing file will be in OFFLINE STATE and will not be accessible.

If the missing file belong to separate file group we can remove the file group else you may have to export all the data from this database to new database else we cannot take FULL Database backup as one of the database file is not online for file group.

— Create New database

USE [master]

CREATE DATABASE [MisTest] ON PRIMARY

( NAME = N’MisTest’, FILENAME = N’C:\MisTest.mdf’ , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),

FILEGROUP [Secondary]

( NAME = N’MisTestNDF’, FILENAME = N’C:\MisTestNDF.ndf’ , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

LOG ON

( NAME = N’MisTest_log’, FILENAME = N’C:\MisTest_log.LDF’ , MAXSIZE = 2048GB , FILEGROWTH = 10%)

–Take the database offline

Alter database MisTest set offline

–Now rename the NDF file [to simulate the missing file scenario]

sp_configure ‘show advanced options’,1

reconfigure with override

sp_configure ‘xp_cmdshell’,1

reconfigure with override

xp_cmdshell ‘rename c:\MisTestNDF.ndf MisTestNDF_1.ndf’

–Try to bring the database online but it fails

Alter database MisTest set online

Msg 5120, Level 16, State 5, Line 1

Unable to open the physical file “C:\MisTestNDF.ndf”. Operating system error 2: “2(failed to retrieve text for this error. Reason: 15105)”.

Msg 945, Level 14, State 2, Line 1

Database ‘MisTest’ cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.

Msg 5069, Level 16, State 1, Line 1

ALTER DATABASE statement failed.

–Take the database offline and detach

Alter database MisTest set offline

USE [master]

ALTER DATABASE [MisTest] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

USE [master]

EXEC master.dbo.sp_detach_db@dbname = N’MisTest’

–Now try to attach the database with missing file, but it fails with the below error

USE [master]

CREATE DATABASE [MisTest] ON

( FILENAME = N’C:\MisTest.mdf’ ),

( FILENAME = N’C:\MisTest_log.LDF’ )

,( FILENAME = N’C:\MisTestNDF.ndf’ )

FOR ATTACH

Msg 5120, Level 16, State 101, Line 1

Unable to open the physical file “C:\MisTestNDF.ndf”. Operating system error 2: “2(failed to retrieve text for this error. Reason: 15105)”.

–Rename the original files

xp_cmdshell ‘rename c:\MisTest.mdf MisTest_1.mdf’

xp_cmdshell ‘rename c:\MisTest_log.ldf MisTest_1_log.ldf’

–Now recreate the Empty database with the same physical & logical file name

CREATE DATABASE [MisTest] ON PRIMARY

( NAME = N’MisTest’, FILENAME = N’C:\MisTest.mdf’ , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),

FILEGROUP [Secondary]

( NAME = N’MisTestNDF’, FILENAME = N’C:\MisTestNDF.ndf’ , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

LOG ON

( NAME = N’MisTest_log’, FILENAME = N’C:\MisTest_log.LDF’ , MAXSIZE = 2048GB , FILEGROWTH = 10%)

–Mark the missing file as offline

Alter database MisTest

MODIFY FILE (NAME = ‘MisTestNDF’,OFFLINE)

–Take the database offline

Alter database MisTest set offline

— Replace the original file and bring the database online

xp_cmdshell ‘rename c:\MisTest.mdf MisTest_2.mdf’

xp_cmdshell ‘rename c:\MisTest_log.ldf MisTest_2_log.ldf’

xp_cmdshell ‘rename c:\MisTest_1.mdf MisTest.mdf’

xp_cmdshell ‘rename c:\MisTest_1_log.ldf MisTest_log.ldf’

Alter database MisTest set online

— The only problem is that we cannot take the backup of the database as the file is offline

backup database Mistest to disk = ‘c:\mistest.bak’

Msg 3636, Level 16, State 2, Line 1

An error occurred while processing ‘BackupMetadata’ metadata for database id 8 file id 3.

Msg 3046, Level 16, State 2, Line 1

Inconsistent metadata has been encountered. The only possible backup operation is a tail–log backup using the WITH CONTINUE_AFTER_ERROR or NO_TRUNCATE option.

Msg 3013, Level 16, State 1, Line 1

BACKUP DATABASE is terminating abnormally.

–We cannot even remove the file as we get this error

USE [MisTest]

ALTER DATABASE [MisTest] REMOVE FILE [MisTestNDF]

Msg 5056, Level 16, State 2, Line 1

Cannot add, remove, or modify a file in filegroup ‘Secondary’ because the filegroup is not online.

–Now try to remove the file & file group

–Try the database back up again but it would fail

backup database Mistest to disk = ‘c:\mistest.bak’

Msg 3636, Level 16, State 2, Line 2

An error occurred while processing ‘BackupMetadata’ metadata for database id 8 file id 3.

Msg 3046, Level 16, State 2, Line 2

Inconsistent metadata has been encountered. The only possible backup operation is a tail–log backup using the WITH CONTINUE_AFTER_ERROR or NO_TRUNCATE option.

Msg 3013, Level 16, State 1, Line 2

BACKUP DATABASE is terminating abnormally.

USE [MisTest]

ALTER DATABASE [MisTest] REMOVE FILE [MisTestNDF]

Msg 5056, Level 16, State 2, Line 1

Cannot add, remove, or modify a file in filegroup ‘Secondary’ because the filegroup is not online.

Msg 5042, Level 16, State 7, Line 1

The filegroup ‘Secondary’ cannot be removed because it is not empty.

Ignore the above error and proceed further.

ALTER DATABASE [MisTest] REMOVE FILEGROUP [Secondary]

Now we can take full database backup normally

December 25, 2012 by Karthick P.K

When statistics was updated?

Statistics are used by the SQL Server optimizer to choose the efficient plan. When we don’t have up to date statistics
it may end with SQL server optimizer choosing inefficient query plan. We can use below query to identify when SQL Server
statistics were last updated.

select db_id() as dbid,
  case
    when indid IN (0, 1) then convert (char (12), rows)
    else (select rows from dbo.sysindexes i2 where i2.id =  i.id and i2.indid in (0,1)) -- ''-''
  end as rowcnt,
  case
    when indid IN (0, 1) then rowmodctr
    else convert (bigint, rowmodctr) + (select rowmodctr from dbo.sysindexes i2 where i2.id =  i.id and i2.indid in (0,1))
  end as row_mods,
  case rows when 0 then 0 else convert (bigint,
    case
      when indid IN (0, 1) then convert (bigint, rowmodctr)
      else rowmodctr + (select convert (bigint, rowmodctr) from dbo.sysindexes i2 where i2.id =  i.id and i2.indid in (0,1))
    end / convert (numeric (20,2), (select case convert (bigint, rows) when 0 then 0.01 else rows end from dbo.sysindexes i2 where i2.id =  i.id and i2.indid in (0,1))) * 100)
  end as pct_mod,
  convert (nvarchar, u.name + '.' + o.name) as objname,
  case when i.status&0x800040=0x800040 then 'AUTOSTATS'
    when i.status&0x40=0x40 and i.status&0x800000=0 then 'STATS'
    else 'INDEX' end as type,
  convert (nvarchar, i.name) as idxname, i.indid,
  stats_date (o.id, i.indid) as stats_updated,
  case i.status & 0x1000000 when 0 then 'no' else '*YES*' end as norecompute,
  o.id as objid , rowcnt, i.status
from dbo.sysobjects o, dbo.sysindexes i, dbo.sysusers u
where o.id = i.id and o.uid = u.uid and i.indid between 1 and 254 and o.type = 'U'
order by pct_mod desc, convert (nvarchar, u.name + '.' + o.name), indid
GO

December 25, 2012 by Karthick P.K

How to identify if the query is using Query hints or plan Guides ?

Generally we create plan guide or query hints to improve the performance of the query, but some times it would create problems if not used carefully.

To identify if the query is using plan guide look at the planguidedb attribute in XML plan. Sys.Plan_guides will also have an entry.
Query hints can be identifies by looking at the query.We will see hints like opton (force order),Inner loop join, index = etc
If you are tunning a slow query and if it is using plan guides or query hints first remove the hints and tune the query normally

December 12, 2012 by Karthick P.K

The user instance login flag is not supported on this version of SQL Server

“The user instance login flag is not supported on this version of SQL Server. The connection will be closed.”

When we use “UserInstance=TRUE” in connection string on instances which is not SQL Server express we will end with this error. “UserInstance=TRUE” is supported only in express

November 21, 2012 by Karthick P.K

How to make clustered instance of SQLServer to listen on Multiple IP address?

To make clustered SQL Server instance listen on multiple I/P’s

Add new IP addres Resource type in SQLGroup.

In the dependency list of SQLserver network name add the the new netwok IP Resource If you want the SQLServer to listen on different port with new IP
Restart the SQL Server network name (It will internally restart SQL Server resources)

November 15, 2012 by Karthick P.K

1398(There is a time and/or date difference between the client and server.) Backup

Backup database failes with error

“Write on “\\\tes.bak” failed: 1398(There is a time and/or date difference between the client and server.)

Resolution

You will get this error if Kerberos ticket in the SQL Server is expired, restart SQL Server. Also check if Windows Time service is running

November 12, 2012 by Karthick P.K

CACHESTORE_SQLCP will increase when you run backup

CACHESTORE_SQLCP increases continuously when we run the backup.
When you run the database/Tlog backup very frequently or for the databases with large number of files/file groups CACHESTORE_SQLCP increases
We can identify the issue by using below queries


Select text,a.*from sys.dm_exec_cached_plans a cross apply sys.dm_exec_sql_text(plan_handle)  order by size_in_bytes desc
Go

Select p.plan_handle,CONVERT (varchar, GETDATE(), 126) AS runtime, LEFT (p.cacheobjtype + ' (' + p.objtype + ')', 35) AS cacheobjtype
,p.usecounts, p.size_in_bytes / 1024 AS size_in_kb, stat.total_worker_time/1000 AS tot_cpu_ms,
stat.total_elapsed_time/1000 AS tot_duration_ms, stat.total_physical_reads, stat.total_logical_writes, stat.total_logical_reads,
LEFT (CASE WHEN pa.value=32767 THEN 'ResourceDb' ELSE ISNULL (DB_NAME (CONVERT (sysname, pa.value)),
CONVERT (sysname,pa.value))END, 40) AS dbname,sql.objectid, CONVERT (nvarchar(50),
CASE WHEN sql.objectid IS NULL THEN NULL ELSE REPLACE (REPLACE (sql.[text],CHAR(13), ' '), CHAR(10), '')END) AS procname,
REPLACE (REPLACE (SUBSTRING (sql.[text], stat.statement_start_offset/2 + 1, CASE WHEN stat.statement_end_offset = -1
THEN LEN (CONVERT(nvarchar(max), sql.[text]))
ELSE stat.statement_end_offset/2 - stat.statement_start_offset/2 + 1 END), CHAR(13), ' '), CHAR(10), ' ') AS stmt_text
FROM sys.dm_exec_cached_plans p OUTER APPLY sys.dm_exec_plan_attributes (p.plan_handle) pa INNER JOIN sys.dm_exec_query_stats stat
ON p.plan_handle = stat.plan_handle OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) AS sql
WHERE pa.attribute = 'dbid' ORDER BY p.plan_handle DESC

Go

Resolution

SQL Server 2008
There is a fix for this issue in SQL server 2008 http://support.microsoft.com/kb/961323

SQL Server2005
There is no Fix for SQL Server 2005. You can follow some workarounds
Since all the plans (problematic) are created for MSDB. You can use dbcc flushprocindb(4) — to flush the plans of msdb without touching the plans of other databases (4 is database id of MSDB) .
You can add a new step to backup job created by maintenance plan to run dbcc flushprocindb(4) every time backup completes. So the plans created in MSDB will be flushed immediately after the backup.

November 8, 2012 by Karthick P.K

Invalid column name ‘originator_id’. (Microsoft SQL Server, Error: 207)

Create replication fails on database which is migrated from SQL Server 2005 with error

Error:

ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————
Invalid column name ‘originator_id’. (Microsoft SQL Server, Error: 207)

Resolution:
1. Add originator_id column to syspublications table in publication db. Run below query in your publication DB.

ALTER TABLE dbo.syspublications ADD [originator_id] [int] NULL;

2. Add a column originator_id into the view dbo.syspublications inside distribution database

3. Add a originator_id column into IHpublications table in distribution database

SQLServerScribbles.COM

SQL Server, SQL, Replication, SQL Server memory, SQL Server performance, SQL Server architecture, SQL Server cluster, SQL Server 2008, SQL Server 2012

MSSQLWIKI

How to create table with filestream column and Insert data

Create script for all objects in database with data

HOW TO INSTALL SQL Server CLUSTER IN HYPER-V

How to add node to SQL Server cluster

HOW TO CREATE CLUSTER USING HYPER-V

How to create SQL Server Agent Jobs

Trace waits in SQLServer (SQLTRACE_BUFFER_FLUSH,TRACEWRITE,SQLTRACE_WAIT_ENTRIES,SQLTRACE_LOCK)

How to view the Space used by each table in database

Top queries by total I/O and average I/O in SQL Server

SSMS fails with below error when you connect (or) explore the databases

Linked server from SQLServer2012 to SQLServer2000.

How to detect low memory conditions in SQL Server using ring buffers output

How to bring the database online when one or more data files is accidentally deleted

When statistics was updated?

How to identify if the query is using Query hints or plan Guides ?

The user instance login flag is not supported on this version of SQL Server

How to make clustered instance of SQLServer to listen on Multiple IP address?

1398(There is a time and/or date difference between the client and server.) Backup

CACHESTORE_SQLCP will increase when you run backup

Invalid column name ‘originator_id’. (Microsoft SQL Server, Error: 207)