Archive for March, 2011

Which Database Users are not associated with SQL  server Logins.that users are Called Orphaned Users.

For More Information :-






Read Full Post »

1. Run the Below Query in SSMS for get Master Database Location for the Instance.

SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files WHERE database_id = DB_ID(‘master’)

2.Stop the SQL Service Service.

3.Copy the master.mdf and mastlog.ldf file to different Location.

4.Open SQL Server Configuration Manager.

Select the Instance Name right click and select Properties.

Select Advanced Tab.

change the startup parameters

-dC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

To New Location

-dD:\Master_Data\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG;-lD:\Master_Data\mastlog.ldf

Click Apply and OK.

5.Start the SQL Service Service.

6.Run the Below Query in SSMS for get Master Database Location for the Instance.

SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files WHERE database_id = DB_ID(‘master’)

Now The Master DB Location are Changed.

Read Full Post »

The Resource database is a read-only database that contains all the system objects that are included with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database does not contain user data or user metadata.

The Resource database makes upgrading to a new version of SQL Server an easier and faster procedure. In earlier versions of SQL Server, upgrading required dropping and creating system objects. Because the Resource database file contains all system objects, an upgrade is now accomplished simply by copying the single Resource database file to the local server.

The physical location of the data file Mssqlsystemresource.mdf is in the BINNdirectory of the instance. Each instance of SQL server contains only one resource database. It does not showup in the list of system databases in the SQL Server Management Studio (SSMS). If you try to add this, thenyou will get the following error message:

You cannot perform this operation for the resource database. (Microsoft SQLServer, Error: 4616)

URL :- http://msdn.microsoft.com/en-us/library/ms190940(v=SQL.100).aspx

Read Full Post »

Prerequisites for Transactional Replication

Primary Key:

This is a basic rule that every article should have a Primary Key to be a candidate table for Transactional Replication. Primary keys are used to maintain uniqueness of records and to maintain  referential integrity between tables, and that is why it is recommended for every article to have a primary key.

Securing snapshot folder:


Network bandwidth:

Enough disk space for database being published:

We need to make sureWe need to make sure that we have ample space available for the transaction log for the published database, as it will continue to grow and won’t truncate the log records until they are moved to the distribution database. Please note that even in simple recovery model, the log fle can grow large if replication breaks. That is the reason it is recommended to set T-log’s auto grow option to value “true”.  We should also make sure that the distribution database is available and  log reader agent is running.

Enough disk space for distribution database:

It is necessary to haveIt is necessary to have enough disk space allocated to the distribution database. This is because the distribution database will store the transactions marked for replication until it is applied to the subscriber database within the limit of retention period of distribution (which is 72 hours by default), or it will retain the transactions until the snapshot agent re-runs and creates a new snapshot. re-runs and creates a new snapshot.

Use domain account as service account:

We should always use the domain account as a service account, so that when agents access the shared folder of snapshot fles, it won’t have any problem just because they are local to the system and do not have permission to access network share. While mentioning service account, we are asked to choose from two built-in accounts including Local System account, Network Services, and this account, wherein we have to specify the domain account on which the service account will run.

Read Full Post »

Returns the resources and types of locks that are participating in a deadlock and also the current command affected, in an XML format that does not comply with any XSD schema.

Scope: global only


Read Full Post »

Returns the resources and types of locks participating in a deadlock and also the current command affected.

Scope: global only


Read Full Post »

Limits the amount of information returned in error messages. For more information, see Metadata Visibility Configuration.

Scope: global only


Read Full Post »

Older Posts »