MS Sql Replication Debugging and outputverboselevel

For replication job debuging and get detail of an error in replication you need to enable outputverboselevel in the replication job that is generating error. you have to edit replication job
and writ following two commands at the end and save the job. when you execute this job it will create an output file with detail about error.

-Output C:\ReplOutput.txt
-OutputVerboseLevel 2

for more detail you can visit the following link

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

How to configure SQL Server to use more than 2 GB of physical memory

This article describes how to configure SQL Server to use more than 2 GB of physical memory.

Microsoft SQL Server 2000 dynamically acquire and free memory as needed. When you run multiple instances of SQL Server on a computer, each instance dynamically acquires and frees memory to adjust for changes in the workload of the instance.

SQL Server 2000 Enterprise Edition introduces support for the use of Microsoft Windows 2000 Address Windowing Extensions (AWE) to address approximately 8 GB of memory for instances that run on Microsoft Windows 2000 Advanced Server, and approximately 32 GB for instances that run on Microsoft Windows 2000 Datacenter. With AWE, SQL Server can reserve memory that is not in use for other applications and the operating system. Each instance that uses this memory; however, must statically allocate the memory it needs. SQL Server can only use this AWE allocated memory for the data cache and not for executables, drivers, DLLs, and so forth.

Note: To use Address Windowing Extensions (AWE) memory, you must run the SQL Server 2000 database engine under a Windows account that has been assigned the Windows lock pages in memory administrative credentials.

Support for Windows 2000 Advanced Server or Windows 2000 Datacenter or Windows Server 2003

The maximum amount of memory that can be supported on Windows Server 2003 is 4 GB. However, Windows Server 2003 Enterprise Edition supports 32 GB of physical RAM. Windows Server 2003 Datacenter Edition supports 64 GB of physical RAM by using the Physical Address Extensions (PAE) feature. You can use the 3 GB switch that is in the Boot.ini file with Microsoft Windows Server 2003, Microsoft Windows Server 2003 Enterprise Edition, or with Microsoft Windows Server 2003 Datacenter Edition.

SQL Server 2000
Both SQL Server 2000 Enterprise and SQL Server 2000 Developer Editions can use the following options:

Use of the /PAE switch in the Boot.ini and the AWE enable option in SQL Server allows SQL Server 2000 to utilize more than 4 GB memory. Without the /PAE switch SQL Server can only utilize up to 3 GB of memory.

When you allocate SQL Server AWE memory on a 32 GB system, Windows 2000 may require at least 1 GB memory to manage AWE.

Example:

The following example shows how to enable AWE and configure a limit of 6 GB for the max server memory option:
1.Take backup of all user and system database on a separate machine (take full backup of database machine )
2.Stop SQL server (all SQL server 2000 services)
3. Add /3GB / PAE switch in your system boot.ini

[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition(1)\WINDOWS
[operating systems]
multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003, Enterprise" /noexecute=optout /fastdetect /3GB /PAE

4.Shutdown SQL server machine
5.Add physical memory and boot the server
6.Open SQL query analyzer and select master database and execute following script

Use master
sp_configure 'show advanced options', 1
RECONFIGURE GO
sp_configure 'awe enabled', 1
RECONFIGURE GO
sp_configure 'max server memory', 6144
RECONFIGURE GO

After execution of script restart database server.

Note: make sure that /PAE /3GB switch must be in boot.ini file
And database machine have windows 2003 enterprise edition


Note: on some system window task manager will not show you correct memory usage for sql server, to find correct values you need to check Two Performance monitor counter values SQL server: memory manager -> target server memory and SQL server: memory manager -> total server memory

FOR SQL SERVER 2005:
just add /PAE /3GB switch in boot.ini
and apply following changes in sql server properties

Does SQL Server need more memory? Page Life Expectancy Indicator of SQL Server buffer Memory counter is a valuable performance monitor indicator

Your Microsoft SQL Server performance is going down day by day and becoming a nightmare for you. How you will figure out that SQL Server is under memory pressure, page life expectancy counter will help you in this regard.

Go to Start -> program -> administrative Tools -> performance

















Then














See page life expectancy counter value in performance monitor as fallows.














According to Microsoft Page life expectancy counter minimum value is 300, if it is less than 300 then adding more memory in Microsoft SQL Server machine will help to increase your Microsoft SQL Server performance. Incase this counter about more than 300 then adding more RAM in machine may not significant increase in Microsoft SQL Server performance