The following shows how to find the version of SQL Server you are running (described as productversion). It also shows how to query the level of the product. The level of the product indicates whether it was the initial release, a service pack, or a beta version: RTM = shipping version SPn = service pack (where n is the version) CTP = Community Technology Preview version [cc lang=”sql”] SELECT SERVERPROPERTY(‘productversion’), SERVERPROPERTY (‘productlevel’), SERVERPROPERTY (‘edition’) [/cc] If the above statement does not work, you may be running SQL Server 7.0. In which case, use the following statement: [cc lang=”sql”] SELECT @@VERSION [/cc]
Continue reading ...
Determining the database owner is important if you want to take advantage of cross-database-ownership-chaining. If databases have different owners, then you have issues with accessing objects between databases. To find the database owners: [cc lang=”sql”] SELECT SUSER_SNAME(owner_sid) FROM sys.databases [/cc] To change the owner of a database: [cc lang=”sql”] USE database EXEC sp_changedbowner ‘sa’ [/cc] The standard owner for databases is usually sa.
Continue reading ...
This seems to be the easiest way to kill all connections (sessions) on a SQL Server database: [cc lang=”sql”] ALTER DATABASE database_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE [/cc] After executing the above, it will put the database in single user mode. So you then need to remove single user mode using: [cc lang=”sql”] EXEC sp_dboption ‘database_name’, ‘single user’, ‘FALSE’ [/cc]
Continue reading ...
Change Data Capture, CDC is a feature that is revolutionizing SQL Server in respects to data flow. Previous to change data capture, triggers or high water marks needed to be used in order to perform delta transfers of data to data warehouses or disparate systems. This example shows the use of a custom CDC solution that utilizes a definition table which stored table and column names in order to create a meta-data driven CDC subscription. First create your CDC subscription table [cc lang=”sql”] CREATE TABLE [dbo].[Columns_To_Monitor_Sto]( [ColumnID] [int] IDENTITY(1,1) NOT NULL, [DatabaseName] [varchar](40) NULL, [SchemaName] [varchar](20) NULL, [TableName] [varchar](255) NULL, [ColumnName] [varchar](255) NULL, [Ordinal] [int] NULL, [IsActive] [tinyint] NULL, ) [/cc] With the table now created, we have a meta data repository of columns. Anything added or removed from this table will automatically get added or removed from the CDC subscription. [cc lang=”sql”] DECLARE @DebugMode int = 1 DECLARE @source_schema varchar(20) DECLARE @source_name varchar(255) DECLARE @role_name varchar(20) = ‘cdc_manager’ DECLARE @supports_net_changes int = 1 DECLARE @captured_column_list varchar(MAX) = ” DECLARE @TableCount int DECLARE @iOrigCount int DECLARE @capture_instance nvarchar(MAX) DECLARE @TableName varchar(255) DECLARE @IsCDCEnabled int CREATE TABLE #TablesToMonitor ( ID int IDENTITY(1,1) ,SchemaName varchar(20) ,TableName varchar(255) ,ColumnsToMonitor varchar(MAX) ,CaptureInstance varchar(MAX) ,IsCDCEnabled bit ) INSERT INTO #TablesToMonitor ( TableName ,SchemaName ,ColumnsToMonitor ,CaptureInstance ,IsCDCEnabled ) SELECT DISTINCT rcm.TableName ,rcm.SchemaName ,ColumnsToMonitor = SUBSTRING((SELECT ‘,’ + r.ColumnName FROM( SELECT DISTINCT TableName,ColumnName FROM dbo.Columns_To_Monitor_Sto WHERE IsActive = 1 ) r WHERE (r.TableName = rcm.TableName) FOR XML PATH(”) ), 2, 8000) ,CaptureInstance = rcm.SchemaName + ‘_’ + […]
Continue reading ...
This script will automatically determine whether a rebuild or a reorganize should be used according to the fragmentation of the index. It will then execute the appropriate command. Note that performing index rebuilds online during production hours will cause contention. [cc lang=”sql”] SET NOCOUNT ON; DECLARE @objectid int; DECLARE @indexid int; DECLARE @partitioncount bigint; DECLARE @schemaname nvarchar(258); DECLARE @objectname nvarchar(258); DECLARE @indexname nvarchar(258); DECLARE @partitionnum bigint; DECLARE @partitions bigint; DECLARE @frag float; DECLARE @command varchar(8000); — ensure the temporary table does not exist IF EXISTS (SELECT name FROM sys.objects WHERE name = ‘work’) DROP TABLE work; — conditionally select from the function, converting object and index IDs — to names. SELECT object_id AS objectid, index_id AS indexid, partition_number AS partitionnum, avg_fragmentation_in_percent AS frag INTO work FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, ‘LIMITED’) WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0; — Declare the cursor for the list of partitions to be processed. DECLARE partitions CURSOR FOR SELECT * FROM work; — Open the cursor. OPEN partitions; — Loop through the partitions. FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag; WHILE @@FETCH_STATUS = 0 BEGIN; SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name) FROM sys.objects AS o JOIN sys.schemas as s ON s.schema_id = o.schema_id WHERE o.object_id = @objectid; SELECT @indexname = QUOTENAME(name) FROM sys.indexes WHERE object_id = @objectid AND index_id = @indexid; SELECT @partitioncount = count (*) FROM sys.partitions WHERE object_id = @objectid AND index_id = @indexid; — 30 is the decision point at which to switch — between […]
Continue reading ...
