Dynamic Memory Setting on SQL Server

This is given in SQL Server 2000 BOL.

When an instance of SQL Server starts, it typically acquires 8 to 12 MB of memory to complete the initialization process. After the instance has finished initializing, it acquires no more memory until users connect to it and start generating a workload. The instance then keeps acquiring memory as required to support the workload. As more users connect and run queries, SQL Server acquires the additional memory required to support the demand. The instance will keep acquiring memory until it reaches its memory allocation target, it will not free any memory until it reaches the lower limit of the target.

To acquire as much memory as possible without generating excess paging I/O, each instance of SQL Server sets a target of acquiring memory until free physical memory on the computer is in the range of 4 MB to 10 MB. This range was chosen because testing has shown that Windows NT and Windows 2000 have minimal memory swapping until the memory allocations equal the available physical memory minus 4 MB. An instance of SQL Server that is processing a heavy workload keeps the free physical memory at the lower end (4 MB) of the range; an instance that is processing a light workload keeps the free memory at the higher end of the range (10 MB). An instance of SQL Server will vary its target as the workload changes. As more users connect and generate more work, the instance will tend to acquire more memory to keep the available free memory down at the 4 MB limit.

As the workload lightens, the instance will adjust its target towards 10 MB of free space, and will free memory to the operating system. Keeping the amount of free space between 10 MB and 4 MB keeps Windows NT or Windows 2000 from paging excessively, while at the same time allowing SQL Server to have the largest buffer cache possible that will not cause extra swapping."

SQLServer FAQ Links

http://www.sqljunkies.com/WebLog/roman/archive/category/351.aspx

http://blogs.msdn.com/slavao/archive/2005/02/01/364523.aspx

On FAQ:

http://www.sql-server-performance.com/q&a_performance.asp

http://www.sql-server-performance.com/q&a_performance.asp

SQL Server 2000 Standard Backup failure

Backup job created using SQL Server Maintanence plan fail with the below error intermittently
"Executes as user: Domain\sqladmin sqlmaint.exe failed. [SQLSTATE 42000] [Error 22029]. The step failed"
Below are the error message found on SQL server Errror log.

BACKUP failed to complete the command BACKUP DATABASE [USER_DB_01] TO DISK = N'E:\MSSQL\BACKUP\USER_DB_01\USER_DB_01_db_200507092100.BAK' WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT

BackupMedium::ReportIoError: write failure on backup device 'E:\MSSQL\BACKUP\USER_DB_01\USER_DB_01_db_200507092100.BAK'. Operating system error 112(error not found).

Internal I/O request 0x53A97468: Op: Write, pBuffer: 0x154A0000, Size: 983040, Position: 3927840256, UMS: Internal: 0x103, InternalHigh: 0x0, Offset: 0xEA1E1600, OffsetHigh: 0x0, m_buf: 0x154A0000, m_len: 983040, m_actualBytes: 0, m_errcode: 112, BackupFile: E:\MSSQL\BACKUP\USER_DB_01\USER_DB_01_db_200507092100.BAK

DTS Package Creation

Question on , who can create DTS and save the DTS packages?

What are the privileges and rights that required for user to create a DTS package?

Answer is on the below link.
http://support.microsoft.com/?id=282463

5 Seconds : Business Intelligence with Microsoft SQL Server Reporting Services - Part 1

http://www.15seconds.com/issue/041013.htm