SQL 2008 R2 database Move to A New SAN

SQL  2008 R2 database Move

We recently moved our SAN to a new one on a Clustered SQL  Server.  First SQL server service could not start and after fixing that SQL Agent would not start for over a week and finally Microsoft's tech support helped to find and fix the issue. I found out that I had Missed the last step called Follow-up: After Moving All System Databases  in these instructions.  And if it is a cluster environment then you need to perform this on the active node.

 If this is your first exposure to this process as it was for me,  these instructions might be useful to you.

You can find step by step database (including System DB) move on Microsoft's website http://msdn.microsoft.com/en-us/library/ms345408(v=sql.105).aspx 

They are very clear and extremely helpful and that is what used When we moved from old SAN to a new one. I added my own notes and some scripts that would help simplify some of the process such as detaching and attaching databases, specially if you have lots of DBs to move.

Make a full back of each Database and save it on a non-clustered drives/location.

1. Run and save the result to run at the end your process.  This generates  the attach db statements. You need to to this first as you will not have access to them once you detach the databases. Then you can replace the drive and the path to point to the new location.

-- *********** First change the Query Results to text  *************

 select

Case

When fileid = 1 then ' EXEC sp_attach_db @dbname = N''' + [name]+ ''',' 

+char(13) +

' @filename1 = N''' + filename + ''','

when fileid = 2 then ' @filename2 = N''' + filename + ''';'
+char(13)

 end as [Name_path]

from sys.sysaltfiles

                                    where dbid not in (1,2,3,4)  -- All dbs except system dbs

2. Detach all databases except, System DBs. You can run following statements to generate the detach syntax

SELECT 'EXEC MASTER.dbo.sp_detach_db @dbname = N'''

+ Name + ''''FROM sys.sysdatabases

WHERE dbid not in (1,2,3,4) -- All dbs except system dbs

3. Change the System dbs file location ( except Master Db):

ALTER DATABASE  Model  MODIFY FILE ( NAME = modeldev, FILENAME = 'Drive Letter:\Path\model.mdf' )

ALTER DATABASE  Model  MODIFY FILE ( NAME = modellog , FILENAME = 'Drive Letter:\Path\modellog.ldf' )

ALTER DATABASE  MSDB MODIFY FILE ( NAME = MSDBData, FILENAME = 'Drive Letter:\Path\MSDBData.mdf' )

ALTER DATABASE  MSDB MODIFY FILE ( NAME = MSDBLog , FILENAME = 'Drive Letter:\Path\MSDBLog.ldf' )

ALTER DATABASE  tempdb MODIFY FILE ( NAME = tempdev , FILENAME = 'Drive Letter:\PathL\tempdb.mdf' )

ALTER DATABASE  tempdb MODIFY FILE ( NAME = templog , FILENAME = 'Drive Letter:\PathR\emplog.ldf' )

4. Move all the .mdf and .ldf files to the new location

5. Detach Model, MSDB and Tempdb

SELECT 'EXEC MASTER.dbo.sp_detach_db @dbname = N'''

+ Name + ''''FROM sys.sysdatabases

WHERE dbid in (2,3,4) -- Only system dbs except MASTER DB

Moving the master Database Procedure

Pasted from <http://msdn.microsoft.com/en-us/library/ms345408(v=sql.105).aspx>

To move the master database, follow these steps. If this is clustered server then you need to perfrom these steps on all nodes

1. From the Start menu, point to All Programs, point to Microsoft SQL Server, point to Configuration Tools, and then click SQL Server Configuration Manager.

2. In the SQL Server Services node, right-click the instance of SQL Server (for example, SQL Server (MSSQLSERVER)) and choose Properties.

3. In the SQL Server (instance_name) Properties dialog box, click the Advanced tab.

4. Edit the Startup Parameters values to point to the planned location for the master database data and log files, and click OK. Moving the error log file is optional.

The parameter value for the data file must follow the -d parameter and the value for the log file must follow the -l parameter. The following example shows the parameter values for the default location of the master data and log files.

-dC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf
-lC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\
DATA\mastlog.ldf

If the planned relocation for the master data and log files is E:\SQLData, the parameter values would be changed as follows:

-dE:\SQLData\master.mdf
-lE:\SQLData\mastlog.ldf

6. Stop the instance of SQL Server

 

7. Move Model, MSDB, Tempdb and Master data and log files to the new location

8. Restart the instance of SQL Server

If the msdb database is moved and the instance of SQL Server is configured for  Database Mail, complete these additional steps.

• Verify that Service Broker is enabled for the msdb database by running the following query.

  
SELECT is_broker_enabled
FROM sys.databases
WHERE name = N'msdb'
Important:

 

Follow-up: After Moving All System Databases

Pasted from <http://msdn.microsoft.com/en-us/library/ms345408(v=sql.105).aspx>

If you have moved all of the system databases to a new drive or volume or to another server with a different drive letter, make the following updates.  If this is clustered server then you need to perfrom these steps on all nodes

Change the SQL Server Agent log path. If you do not update this path, SQL Server Agent will fail to start.

Change the database default location. Creating a new database may fail if the drive letter and path specified as the default location do not exist.

 

Change the SQL Server Agent Log Path

From SQL Server Management Studio, in Object Explorer, expand SQL Server Agent.  If this is clustered server then you need to perfrom these steps on all nodes

1. Right-click Error Logs and click Configure.

2. In the Configure SQL Server Agent Error Logs dialog box, specify the new location of the SQLAGENT.OUT file. The default location is C:\Program Files\Microsoft SQL Server\MSSQL10_50.\MSSQL\Log\.

 

Change the database default location

1. From SQL Server Management Studio, in Object Explorer, right-click the SQL Server server and click Properties.

2. In the Server Properties dialog box, select Database Settings.

3. Under Database Default Locations, browse to the new location for both the data and log files.

4. Stop and start the SQL Server service to complete the change.

 If  any reason you cannot modify the error log path using Management Studio you modify the registry: Again  if this is clustered server then you need to perfrom these steps on all nodes

1. Go to START --> Run and type Regedit

2. HKEY_LOCAL_MACHINE -> SOFTWARE -> MICROSOFT -> Microsoft SQL Server ->

 MSQL10_50.MSSQLSERVER -> SQLServerAgent and make sure it is pointing to the new location.

 

The concurrent snapshot for publication is not available because it has not been fully generated .

Problem:-

The snapshot files for a transactional publication are immediately removed after being generated by the snapshot agent.

Noticed that status of the publication articles are 3. Which is wrong either they should be 0 = Inactive, 1 = Subscribed or 2 = Active.

Solution :-

1) Delete the subscription and publication.

2) Check the tables to see if there are still rows for the publication database available:

select * from msdb..MSdistpublishers

select * from distribution..MSpublisher_databases

select * from distribution..MSarticles where publisher_db = 'distribution'

select * from distribution..MSsubscriptions where publisher_db = 'distribution'

3) Delete all rows that have still the 'publisher_db' from the just deleted publisher database:

delete from distribution..MSarticles where publisher_db = 'distribution'

delete from distribution..MSsubscriptions where publisher_db = 'distribution'

4) Re-create the publication subscriber and initialize.

Help Links :-

http://blogs.technet.com/b/claudia_silva/archive/2009/04/29/replication-snapshot-files-removed-after-being-generated.aspx

http://support.microsoft.com/kb/324401

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/baf99181-2fe1-4e6b-ba45-5d1d08103550/initialize-subscription-failing

http://stackoverflow.com/questions/1018339/the-concurrent-snapshot-for-publication-xx-is-not-available-because-it-has-not

Script to Enable/Disable Database for Replication

You can enable the database for replication using below script.

1 2 3 4 5

use master exec sp_replicationdboption @dbname = 'sqldbpool', @optname = 'publish', @value = 'true' go

If you have restore the database on test environment and you are getting the error that “Database is part of Replication”, you can clear/disable it by executing below query.

1 2 3 4 5

use master exec sp_replicationdboption @dbname = 'sqldbpool', @optname = 'publish', @value = 'false' go

List All Tables Starts With MDT

SP_TABLES

Syntax:-

sp_tables [ [ @table_name = ] 'name' ]
     [ , [ @table_owner = ] 'owner' ]
     [ , [ @table_qualifier = ] 'qualifier' ]
     [ , [ @table_type = ] "type" ]
     [ , [@fUsePattern = ] 'fUsePattern'];

Example :- 

Use Database
 go
EXEC sp_tables @table_name = 'mdt_%'

DBCC

More on :- http://msdn.microsoft.com/en-us/library/ms188796.aspx

Handy Information :-

Database Console Command statements are grouped into the following categories.

Command category	Perform
Maintenance	Maintenance tasks on a database, index, or filegroup.
Miscellaneous	Miscellaneous tasks such as enabling trace flags or removing a DLL from memory.
Informational	Tasks that gather and display various types of information.
Validation	Validation operations on a database, table, index, catalog, filegroup, or allocation of database pages. ---------------------------------------------------------------------------

DBCC commands take input parameters and return values. All DBCC command parameters can accept both Unicode and DBCS literals.

 DBCC Internal Database Snapshot Usage

---

The following DBCC commands operate on an internal read-only database snapshot that the Database Engine creates. This prevents blocking and concurrency problems when these commands are executed. For more information, see Database Snapshots (SQL Server).

DBCC CHECKALLOC	DBCC CHECKDB
DBCC CHECKCATALOG	DBCC CHECKFILEGROUP
DBCC CHECKTABLE

When you execute one of these DBCC commands, the Database Engine creates a database snapshot and brings it to a transactionally consistent state. The DBCC command then runs the checks against this snapshot. After the DBCC command is completed, this snapshot is dropped.
Sometimes an internal database snapshot is not required or cannot be created. When this occurs, the DBCC command executes against the actual database. If the database is online, the DBCC command uses table-locking to ensure the consistency of the objects that it is checking. This behavior is the same as if the WITH TABLOCK option were specified.
An internal database snapshot is not created when a DBCC command is executed:

• Against master, and the instance of SQL Server is running in single-user mode.
  
• Against a database other than master, but the database has been put in single-user mode by using the ALTER DATABASE statement.
  
• Against a read-only database.
  
• Against a database that has been set in emergency mode by using the ALTER DATABASE statement.
  
• Against tempdb. In this case, a database snapshot cannot be created because of internal restrictions.
  
• Using the WITH TABLOCK option. In this case, DBCC honors the request by not creating a database snapshot.
  

The DBCC commands use table locks instead of the internal database snapshots when the command is executed against the following:

• A read-only filegroup
  
• An FAT file system
  
• A volume that does not support 'named streams'
  
• A volume that does not support 'alternate streams'
  

Note
Trying to run DBCC CHECKALLOC, or the equivalent part of DBCC CHECKDB, by using the WITH TABLOCK option requires a database X lock. This database lock cannot be set on tempdb or master and will probably fail on all other databases.

Note
DBCC CHECKDB fails when it is run against master if an internal database snapshot cannot be created.

Progress Reporting for DBCC Commands

---

The sys.dm_exec_requests catalog view contains information about the progress and the current phase of execution of the DBCC CHECKDB, CHECKFILEGROUP, and CHECKTABLE commands. The percent_complete column indicates the percentage complete of the command, and the command column reports the current phase of the execution of the command.
The definition of a unit of progress depends on the current phase of execution of the DBCC command. Sometimes progress is reported at the granularity of a database page, in other phases it is reported at the granularity of a single database or allocation repair. The following table describes each phase of execution, and the granularity at which the command reports progress.

Execution phase	Description	Progress reporting granularity
DBCC TABLE CHECK	The logical and physical consistency of the objects in the database is checked during this phase.	Progress reported at the database page level. The progress reporting value is updated for each 1000 database pages that are checked.
DBCC TABLE REPAIR	Database repairs are performed during this phase if REPAIR_FAST, REPAIR_REBUILD, or REPAIR_ALLOW_DATA_LOSS is specified, and there are object errors that must be repaired.	Progress reported at the individual repair level. The counter is updated for each repair that is completed.
DBCC ALLOC CHECK	Allocation structures in the database are checked during this phase. Note DBCC CHECKALLOC performs the same checks.	Progress is not reported
DBCC ALLOC REPAIR	Database repairs are performed during this phase if REPAIR_FAST, REPAIR_REBUILD, or REPAIR_ALLOW_DATA_LOSS is specified, and there are allocation errors that must be repaired.	Progress is not reported.
DBCC SYS CHECK	Database system tables are checked during this phase.	Progress reported at the database page level. The progress reporting value is updated for every 1000 database pages that are checked.
DBCC SYS REPAIR	Database repairs are performed during this phase if REPAIR_FAST, REPAIR_REBUILD, or REPAIR_ALLOW_DATA_LOSS is specified, and there are system table errors that must be repaired.	Progress reported at the individual repair level. The counter is updated for each repair that is completed.
DBCC SSB CHECK	SQL Server Service Broker objects are checked during this phase. Note This phase is not executed when DBCC CHECKTABLE is executed.	Progress is not reported.
DBCC CHECKCATALOG	The consistency of database catalogs are checked during this phase. Note This phase is not executed when DBCC CHECKTABLE is executed.	Progress is not reported.
DBCC IVIEW CHECK	The logical consistency of any indexed views present in the database is checked during this phase.	Progress reported at the level of the individual database view that is being checked.

Informational Statements

---

DBCC INPUTBUFFER	DBCC SHOWCONTIG
DBCC OPENTRAN	DBCC SQLPERF
DBCC OUTPUTBUFFER	DBCC TRACESTATUS
DBCC PROCCACHE	DBCC USEROPTIONS
DBCC SHOW_STATISTICS

Validation Statements

---

DBCC CHECKALLOC	DBCC CHECKFILEGROUP
DBCC CHECKCATALOG	DBCC CHECKIDENT
DBCC CHECKCONSTRAINTS	DBCC CHECKTABLE
DBCC CHECKDB

Maintenance Statements

---

DBCC CLEANTABLE	DBCC INDEXDEFRAG
DBCC DBREINDEX	DBCC SHRINKDATABASE
DBCC DROPCLEANBUFFERS	DBCC SHRINKFILE
DBCC FREEPROCCACHE	DBCC UPDATEUSAGE

Miscellaneous Statements

---

DBCC dllname (FREE)	DBCC HELP
DBCC FREESESSIONCACHE	DBCC TRACEOFF
DBCC FREESYSTEMCACHE	DBCC TRACEON

Features Supported by the Editions of SQL Server 2012

http://msdn.microsoft.com/en-us/library/cc645993%28v=sql.110%29.aspx#MDS

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "SQL_Latin1_General_CP1_CS_AS" in the equal to operation

• Collation was missing on the column which is highlighted, added the same and worked smoothly.

Running Batch File Using T-SQL

DECLARE @PassedVariable VARCHAR(100)
DECLARE @CMDSQL VARCHAR(1000)
SET @PassedVariable = 'SqlAuthority.com'
SET @CMDSQL = 'c:findword.bat' + @PassedVariable
EXEC master..xp_CMDShell @CMDSQL



more on :- http://blog.sqlauthority.com/2007/06/27/sql-server-running-batch-file-using-t-sql-xp_cmdshell-bat-file/
 

Replication - Between 2005 and 2008

Using a SQL Server 2005 or SQL Server 2008 Distributor with a Publisher Running SQL Server 2000

SQL Server 2005 and SQL Server 2008 can be used as a remote Distributor for Publishers that are running SQL Server 2000. To change agent properties in this scenario, execute the following stored procedures at the Distributor. These procedures let you change properties that were introduced in SQL Server 2005:

• sp_MSchange_snapshot_agent_properties (Transact-SQL)
  
• sp_MSchange_logreader_agent_properties (Transact-SQL)
  
• sp_MSchange_distribution_agent_properties (Transact-SQL)
  
• sp_MSchange_merge_agent_properties (Transact-SQL)
  

If you have a Publisher and Distributor that are running SQL Server 2000, you can change the credentials under which agents make connections by using sp_changedistpublisher and sp_changesubscriber. However, if you upgrade the Distributor to SQL Server 2008, these procedures cannot be used to change the credentials that are used in existing agent jobs. The procedures do affect agent jobs that are created after the procedure is called. To change the credentials for existing agent jobs, call one of the four procedures listed previously.

IMP :-

SQL Server 2000 and SQL Server 2005 can both participate in replication topologies with SQL Server 2008. For SQL Server 2000 the minimum version is Service Pack 3 (SP3). For SQL Server 2005 the minimum version is Service Pack 2 (SP2).
When you replicate between or among different versions of SQL Server, you are usually limited to the functionality of the earliest version used. For example, if you upgrade a Distributor to an instance of SQL Server 2008, but you have a Publisher that is running an instance of SQL Server 2005 and a Subscriber that is running an instance of SQL Server 2000, you are limited to the general functionality and replication functionality of SQL Server 2000.

Note
Because the SQL Server on-disk storage format is the same in the 64-bit and 32-bit environments, a replication topology can combine server instances that run in a 32-bit environment and server instances that run in a 64-bit environment.

For all types of replication, the Distributor version must be no earlier than the Publisher version. (Frequently, the Distributor is the same instance as the Publisher.)
For transactional replication, a Subscriber to a transactional publication can be any version within two versions of the Publisher version. For example, a SQL Server 2000 Publisher can have SQL Server 2008 Subscribers, and a SQL Server 2008 Publisher can have SQL Server 2000 Subscribers.
For merge replication, a Subscriber to a merge publication can be any version no later than the Publisher version. For more information about compatibility for earlier versions, see "Compatibility Level for Merge Publications" later in this topic. For more information about replication features that are supported in the various editions of SQL Server, see Features Supported by the Editions of SQL Server "Denali".

.TUF file

TUF file is a Microsoft SQL Server Transaction Undo file. .TUF File contains the information regarding any modifications that were made as part of incomplete transactions at the time the backup was performed.

A transaction undo(.TUF) file is required if a database is loaded in read-only state. In this state, further transaction log backups may be applied.

if  .TUF file is deleted then log shipping restoration job will not work.

OLE DB error: OLE DB or ODBC error: You do not have permission to run 'SP_TRACE_CREATE'.; 42000

 

Answer:- 

 

USE master
GO
GRANT ALTER TRACE TO [LOGINNAME]
GO

Database mirroring must be removed before you drop the database. Microsoft SQL Server, Error: 3743

Step 1:- Connect to Partner
Step 2:-  ALTER DATABASE test SET PARTNER OFF

Health Tip- Easy Way To Burn Calories

For anyone trying to lose weight, this question is an exciting one! If you simply want to know if your body burns calories warming up the water, the answer is yes. But if you want to know if drinking a lot of ice water can help you lose weight, or keep weight off, this "yes" needs to be qualified with some calculations.
First of all, calories are case-sensitive. There are calories and then there are Calories. Calories with a big "c" are the ones used to describe the amount of energy contained in foods. A calorie with a little "c" is defined as the amount of energy it takes to raise the temperature of 1 gram of water 1 degree Celsius.
What most people think of as a Calorie is actually a kilo-calorie: It takes one Calorie to raise the temperature of 1 kilogram of water 1 degree Celsius. So when you drink a 140-Calorie can of cola, you are ingesting 140,000 calories. There is no cause for alarm, because the conversion applies across the board. When you burn 100 Calories jogging a mile, you are burning 100,000 calories.
So, considering that the definition of a calorie is based on raising the temperature of water, it is safe to say that your body burns calories when it has to raise the temperature of ice water to your body temperature. And unless your urine is coming out ice cold, your body must be raising the temperature of the water. So calories are being burned.
Let's figure out exactly what you're burning when you drink a 16-ounce (0.5 liter) glass of ice water:

• The temperature of ice water can be estimated at zero degrees Celsius.
• Body temperature can be estimated at 37 degrees Celsius.
• It takes 1 calorie to raise 1 gram of water 1 degree Celsius.
• There are 473.18 grams in 16 fluid ounces of water.

So in the case of a 16-ounce glass of ice water, your body must raise the temperature of 473.18 grams of water from zero to 37 degrees C. In doing so, your body burns 17,508 calories. But that's calories with a little "c." Your body only burns 17.5 Calories, and in the grand scheme of a 2,000-Calorie diet, that 17.5 isn't very significant.
But let's say you adhere to the "eight 8-ounce glasses of water a day" nutritional recommendation. In 64 ounces of water, there are 1,892.72 grams. So to warm up all that water in the course of a day, your body burns 70,030 calories, or 70 Calories. And over time, that 70 Calories a day adds up. So, while you definitely shouldn't depend on ice water consumption to replace exercise or a healthy diet, drinking cold water instead of warm water does, in fact, burn some extra Calories!


http://health.howstuffworks.com/wellness/diet-fitness/weight-loss/question447.htm

ALTER PARTITION SCHEME

ALTER PARTITION SCHEME NEXT USED ALTER PARTITION FUNCTION SPLIT RANGE ('100') Go


Example:-

ALTER PARTITION SCHEME cover NEXT USED [SSD] ALTER PARTITION FUNCTION covern() SPLIT RANGE ('201') Go

ALTER PARTITION SCHEME cover  NEXT USED [SSD] ALTER PARTITION FUNCTION covern() SPLIT RANGE ('202') Go

ALTER PARTITION SCHEME cover NEXT USED [SSD] ALTER PARTITION FUNCTION covern() SPLIT RANGE ('203') Go

VI Editor Commands

Common Vi / Vim File Savings Related Commands (ex mode)

You need to press [Esc] key followed by the colon (:) before typing the following commands:

Command	Description
q	Quit
q!	Quit without saving changes i.e. discard changes
r fileName	Read data from file called fileName
wq	Write and quit (save and exit)
w fileName	Write to file called fileName (save as)
w! fileName	Overwrite to file called fileName (save as forcefully)

Query To Get Table Size and Rowcount

 SELECT
    t.NAME AS TableName,
    p.rows AS RowCounts,
    (SUM(a.total_pages) * 8 )/1024 AS TotalSpaceMB ,
    (SUM(a.used_pages) * 8 )/1024 AS UsedSpaceMB,
    ((SUM(a.total_pages) - SUM(a.used_pages)) * 8)/1024 AS UnusedSpaceMB
FROM
    sys.tables t
INNER JOIN     
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE
    t.NAME NOT LIKE 'dt%'
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255
GROUP BY
    t.Name, p.Rows
ORDER BY
     TotalSpaceMB desc

Information About SQL Agent - TSQL Jobstep

SELECT *
FROM msdb.dbo.sysjobs
WHERE job_id = CAST(0xBAD836E3D331B44BA4CCAC400D244B17  AS UNIQUEIDENTIFIER)

---0xBAD836E3D331B44BA4CCAC400D244B17  <--will be="" font="" id="" job="">

DBGrowth - Query

-- This will create a table in MSDB DB.
-- WWSQLDBGrowth will be the table name


if (select count(1)from msdb..sysobjects where name = 'WWSQLDBGrowth' and xtype = 'U') = 0
-- not exists ((select * from msdb..sysobjects where id = object_id(N'[dbo].[WWSQLDBGrowth]') and OBJECTPROPERTY(id, N'IsUserTable') = 1))
begin
CREATE TABLE [MSDB]..[WWSQLDBGrowth] (
[DBName] [varchar] (255),
[FileGroup] [varchar] (255),
[FileName] [varchar] (255),
[FilePath] [varchar] (1000),
[SizeInMB] [float],
[UsedInMB] [float] ,
[FreeInMB] [float],
[AuditDate] datetime
) ON [PRIMARY]
end
DECLARE @DBName varchar(255)
DECLARE DBSize_cursor CURSOR FOR
select name from master..sysdatabases (nolock) --where status < 48
OPEN DBSize_cursor
 FETCH NEXT FROM DBSize_cursor INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
 exec ('use ' + @DBName + ' insert into [MSDB]..[WWSQLDBGrowth] select ''' + @DBName + ''' as DBName ,  groupname as FileGroup,  name as FileName, filename as FilePath, (size*8)/1024. as SizeInMB , (fileproperty(name, ''SpaceUsed''  )*8)/1024. as UsedInMB , ((size - fileproperty(name,''SpaceUsed''))*8)/1024. as FreeInMB, Getdate() as AuditDate FROM sysfiles a LEFT JOIN sysfilegroups b ON a.groupid = b.groupid')
   FETCH NEXT FROM DBSize_cursor INTO @DBName
END
CLOSE DBSize_cursor
DEALLOCATE DBSize_cursor
GO

Query to list the computed columns indexes

select distinct object_name(syscolumns.id),
syscolumns.name As CalculatedColumn ,
syscomments.text as TheCalculation,
isnull(object_name(SYSINDEXKEYS.id),'No Index Using This CalculatedColumn') As IndexName
from syscolumns
inner join syscomments on syscolumns.id=syscomments.id
left outer join sysindexes
on syscolumns.id=sysindexes.id
left outer join SYSINDEXKEYS ON sysindexes.ID=SYSINDEXKEYS.ID
AND sysindexes.INDID=SYSINDEXKEYS.INDID
and SYSINDEXKEYS.COLID=SYSCOLUMNS.COLID
where iscomputed <> 0

To add article to existing Oracle -SQL Replication

Declare @Table_Owner varchar (4000)
Declare @Table varchar (4000)
Declare @Schema varchar (8000)
Declare @Str_Ins varchar (4000)
Declare @Str_Upd varchar (4000)
Declare @Str_Del varchar (4000)

Set @Table_Owner = N'OWNERNAME'
Set @Table = N'ARTICLENAME'
Set @Schema = @Table_Owner+@Table
Set @Str_Ins = N'CALL sp_MSins_'+@Schema+''
Set @Str_Upd = N'CALL sp_MSupd_'+@Schema+''
Set @Str_Del = N'CALL sp_MSdel_'+@Schema+''



-- Adding the transactional articles



use [distribution]

exec sp_addarticle @publication= N'Pegasus_OPS', @article= N' ARTICLENAME',
@publisher= N'PEGPDB', @source_owner= N'DOOR_PRD', @source_object= N'ARTICLENAME',
@type= N'logbased', @description= N'', @creation_script= N'', @pre_creation_cmd= N'drop',
@schema_option= 0x0000000000000083, @use_default_datatypes= 1, @destination_table= N'ARTICLENAME',
@destination_owner= N'dbo', @status= 16, @vertical_partition= N'false',@force_invalidate_snapshot=1,
@ins_cmd= @Str_Ins, @del_cmd= @Str_Del, @upd_cmd= @Str_Upd

Go

To change the Default Backup Path

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory', REG_SZ, N    'D:\VALID PATH'
GO

Unable to expand catalogs on linked Server

ERROR :-

The EXECUTE permission was denied on the object ‘xp_prop_oledb_provider’, database ‘mssqlsystemresource’, schema ‘sys’. (.Net SqlClient Data Provider)

Solution :-
 
USE MASTER

GO

GRANT EXECUTE ON SYS.XP_PROP_OLEDB_PROVIDER TO [LOGIN];