THE OFFICIAL BLOG OF THE SBS "DIVA" : SQL Server

So you want to know what version of SQL you are running on SBS 2008

bradley — Tue, 22 Sep 2009 05:05:00 GMT

So you want to know what version of SQL you are running on SBS 2008... run this query in your SQL management studio express...

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition'), SERVERPROPERTY('InstanceName')

Connect to the monitoring database and you get.... 9.00.4035.00, SP3, and Express Edition with Advanced Services, SBSMONITORING

Now you need to use the string \\.\pipe\mssql$microsoft##ssee\sql\query to connect to the rest of the databases on the server.

The Sharepoint is based on Windows Internal database and is 9.00.4035.00 SP3, Windows Internal Database (64bit) MICROSOFT##SSEE

Kewl huh?!

More tips for SQL 2008

bradley — Sun, 29 Mar 2009 20:13:00 GMT

The Official SBS Blog : Requirements for Installing SQL 2008 Standard Edition for Small Business:
http://blogs.technet.com/sbs/archive/2009/03/23/requirements-for-installing-sql-2008-standard-edition-for-small-business.aspx

To follow up on this post, some other issues you need to be aware of if you attempt to install SQL 2008 ON the same server as the SBS 2008. (Keeping in mind it would be best if you put the SQL on a second server or a virtualized server rather than on the SBS itself)

One thing you will have to do is remove the SQL 2005 management tools from the server and then install the SQL 2008 management express tools. You can't load the 2k8 Full server tools on the 2k8 box.

Cannot install SQL Server Management tool on Windows XP or Windows Vista platform using the SQL 2008 DVD included in SBS 2008 premium edition:
http://support.microsoft.com/kb/958978

Microsoft SQL Server 2008 - Installation Made Easy
http://www.packtpub.com/article/microsoft-sql-server-2008-installation-made-easy

Windows Small Business Server 2008 Technical FAQ
http://technet.microsoft.com/en-us/sbs/cc817589.aspx

"Can I install SQL Server 2008 from Windows Small Business Server 2008
Premium Edition on the first server?

Yes, this is a supported scenario. However the SQL Server 2008 management
tools will not install on the same server; you must install them on another
server."

If you haven't yet installed .NET 3.5 sp1 it will install that first.

Then it will install the 4.5 installer
http://support.microsoft.com/default.aspx?scid=kb;en-us;942288

Two warnings you will get as well

As I said, consider that you are putting it on a DC.

Then only pick those specific pieces you need. Check with the vendor to see exactly what parts you need and install only the minimum.

And don't forget you'll need to remove the SQL 2005 express tools before you can install it "ON" the server.

Top SQL issues from the Managed newsgroup

bradley — Sat, 14 Feb 2009 05:23:00 GMT

Top issues from the managed newsgroup

Partner Online Technical Support Communities Newsgroups:
https://partner.microsoft.com/US/40014662

In this Issue:

TOP SUPPORT ISSUES

NEW & UPDATED KB ARTICLES

NEW & TOP DOWNLOADS

NEW WEBCASTS

RECENT SUPPORT ISSUES

===================

Issue

----------

You want to move databases to a new SQL server running Windows Server 2003

x64 and SQL Server 2005 x64.

Analysis

--------

Database itself is not related to 32/64 bit. You just back up the database on a 64 bit instance and restore it to 32 bit instance without problem. However, please note that the version of the destination instance should be the same or higher than the source instance.

Resolution

--------------

Back up the databases on a 64 bit instance and restore them to 32 bit instance without problem. Or backup the databases on 32 bit instance and restore them on 64 bit instance.

NEW & UPDATED KB ARTICLES

=========================

Access

------

960307 Description of the Access 2007 hotfix package (Access.msp): December 16, 2008

http://support.microsoft.com/default.aspx?scid=kb;EN-US;960307

958846 Error message when you try to create a new database in Access 2007: "Template could not be instantiated"

http://support.microsoft.com/default.aspx?scid=kb;EN-US;958846

SQL

----

957826 Where you can find more information about the SQL Server 2008 builds that were released after SQL Server 2008 and the SQL Server 2005 builds that were released after SQL Server 2005 Service Pack 2

http://support.microsoft.com/default.aspx?scid=kb;EN-US;957826

957807 FIX: A new node is not added to a SQL Server 2008 Analysis Services cluster or a SQL Server 2008 Reporting Services cluster if the SQL Server 2008 Database Engine is not installed

http://support.microsoft.com/default.aspx?scid=kb;EN-US;957807

NEW & TOP DOWNLOADS

====================

SQL Server 2005 Books Online (November 2008)

http://www.microsoft.com/downloads/info.aspx?na=22&p=1&SrcDisplayLang=en&SrcCategoryId=&SrcFamilyId=&u=%2fdownloads%2fdetails.aspx%3fFamilyID%3dbe6a2c5d-00df-4220-b133-29c1e0b6585f%26DisplayLang%3den

Microsoft SQL Server 2008 Express

http://www.microsoft.com/downloads/info.aspx?na=22&p=2&SrcDisplayLang=en&SrcCategoryId=&SrcFamilyId=&u=%2fdownloads%2fdetails.aspx%3fFamilyID%3d58ce885d-508b-45c8-9fd3-118edd8e6fff%26DisplayLang%3den

Microsoft SQL Server 2008 Express with Tools

http://www.microsoft.com/downloads/info.aspx?na=22&p=3&SrcDisplayLang=en&SrcCategoryId=&SrcFamilyId=&u=%2fdownloads%2fdetails.aspx%3fFamilyID%3d7522a683-4cb2-454e-b908-e805e9bd4e28%26DisplayLang%3den

Microsoft SQL Server 2008 Books Online (January 2009)

http://www.microsoft.com/downloads/info.aspx?na=22&p=5&SrcDisplayLang=en&SrcCategoryId=&SrcFamilyId=&u=%2fdownloads%2fdetails.aspx%3fFamilyID%3d765433f7-0983-4d7a-b628-0a98145bcb97%26DisplayLang%3den

NEW WEBCASTS

============

Getting your feet wet with SQL Server 2008: SQL Server Monitoring - February 19, 2009

http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032396920&EventCategory=2&culture=en-US&CountryCode=US

Top SQL issues this month

bradley — Fri, 16 Jan 2009 03:29:00 GMT

In this Issue:

TOP SUPPORT ISSUES

NEW & UPDATED KB ARTICLES

NEW & TOP DOWNLOADS

NEW WEBCASTS

RECENT SUPPORT ISSUES

===================

Issue

----------

After you added a SQL Server 2008 to 2005 cluster, you encountered the following errors:

1. The SQL 2008 agent couldn't be started

2. The SQL 2008 didn’t Failover its working only on the node that installed on it.

Analysis

--------

From SQL Agent error log we found the following information:

2008-11-24 13:09:46 - ! [298] SQLServer Error: 22022, CryptUnprotectData() returned error -2146892987, 'The requested operation cannot be completed.

The computer must be trusted for delegation and the current user account must be configured to allow delegation.' [SQLSTATE 42000]

It indicates that the SQL Agent account can't be delegated. Based on the error, we suspect that SQL Agent account is one of the following: (Could you please double check the credentials )

1. Domain administrator member.

2. The account isn't configured to be trusted for delegation.

Resolution

--------------

1. Change the SQL Server Agent startup account to a non-domain administrator user account.

Note: The user account must have the appropriate rights to start service.

2. In the Active Directory Users and Computers snap-in, enable the SQL Server service account to be trusted for delegation.

For more information, please refer to:

Re: SQL Server 2005 SQL Agent Startup Issue -SQLServer Error: 22022,

CryptUnprotectData()

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3737615&SiteID=1

You cannot start the SQL Server Agent service of a failover cluster of SQL Server 2005 if the build of SQL Server is 3179 or a later build

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

NEW & UPDATED KB ARTICLES

=========================

Access

------

957690 Description of the Access 2007 hotfix package (Access.msp): October 28, 2008

http://support.microsoft.com/default.aspx?scid=kb;EN-US;957690

SQL

----

957826 Where you can find more information about the SQL Server 2008 builds that were released after SQL Server 2008 and the SQL Server 2005 builds that were released after SQL Server 2005 Service Pack 2

http://support.microsoft.com/default.aspx?scid=kb;EN-US;957826

957807 FIX: A new node is not added to a SQL Server 2008 Analysis Services cluster or a SQL Server 2008 Reporting Services cluster if the SQL Server 2008 Database Engine is not installed

http://support.microsoft.com/default.aspx?scid=kb;EN-US;957807

NEW & TOP DOWNLOADS

====================

SQL Server 2005: for Administrators Datasheet

http://www.microsoft.com/downloads/info.aspx?na=22&p=4&SrcDisplayLang=en&SrcCategoryId=&SrcFamilyId=&u=%2fdownloads%2fdetails.aspx%3fFamilyID%3d1cfb760c-a3fa-4e50-9fc3-0e7c9819d51f%26DisplayLang%3den

SQL Server 2005: Performance Tuning and Optimization Datasheet

http://www.microsoft.com/downloads/info.aspx?na=22&p=6&SrcDisplayLang=en&SrcCategoryId=&SrcFamilyId=&u=%2fdownloads%2fdetails.aspx%3fFamilyID%3d9b180e91-34e7-4274-9a4f-78562b926358%26DisplayLang%3den

SQL Server 2000: Performance Tuning and Optimization Datasheet

http://www.microsoft.com/downloads/info.aspx?na=22&p=7&SrcDisplayLang=en&SrcCategoryId=&SrcFamilyId=&u=%2fdownloads%2fdetails.aspx%3fFamilyID%3d0556317d-66c3-4a6d-a128-4d6f873a28b3%26DisplayLang%3den

Risk and Health Assessment for Microsoft SQL Server Datasheet

http://www.microsoft.com/downloads/info.aspx?na=22&p=8&SrcDisplayLang=en&SrcCategoryId=&SrcFamilyId=&u=%2fdownloads%2fdetails.aspx%3fFamilyID%3d8d54db4d-3232-4cf8-8ba5-e80557835421%26DisplayLang%3den

SQL Server 2005 SP3

http://www.microsoft.com/downloads/info.aspx?na=22&p=13&SrcDisplayLang=en&SrcCategoryId=&SrcFamilyId=&u=%2fdownloads%2fdetails.aspx%3fFamilyID%3dae7387c3-348c-4faa-8ae5-949fdfbe59c4%26DisplayLang%3den

Feature Pack for SQL Server 2005 December 2008

http://www.microsoft.com/downloads/info.aspx?na=22&p=14&SrcDisplayLang=en&SrcCategoryId=&SrcFamilyId=&u=%2fdownloads%2fdetails.aspx%3fFamilyID%3d536fd7d5-013f-49bc-9fc7-77dede4bb075%26DisplayLang%3den

NEW WEBCASTS

============

MSDN Webcast: BenkoTips Live and On Demand DemoFest: SQL Server 2008, Spatial Data, and the Virtual Earth SDK (Level 300)

http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032399812&EventCategory=4&culture=en-US&CountryCode=US

SQL Server 2008 Governance - January 15, 2009

http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032396918&EventCategory=2&culture=en-US&CountryCode=US

How to troubleshoot SQL memory issues part II

bradley — Fri, 16 Jan 2009 03:22:00 GMT

Part two of debugging SQL server issues.... from the Partner managed newsgroups.

(There's good stuff in there)

(II)

*Internal Physical memory pressure:

SQL Server detects high memory consumption internally, causing redistribution of memory between internal components.

Internal physical memory pressure may be a result of:

• Responding to the external memory pressure (SQL Server sets lower memory usage caps).

• Changed memory settings (e.g. ‘max server memory’).

• Changes in memory distribution of internal components (due to high percentage of reserved and stolen pages from the buffer pool).

General troubleshooting steps:

• Identify major memory consumers inside SQL Server.

• Verify server configuration.

• Further actions depend on the investigation: check for workload; possible design issues; other resource bottlenecks.

As internal memory pressure is set by SQL Server itself, a logical step is to look at the memory distribution inside SQL Server by checking for any anomalies in buffer distribution. Normally, the buffer pool accounts for the most of the memory committed by SQL Server. To determine the amount of memory that belongs to the buffer pool, we can take a look at the DBCC MEMORYSTATUS output. In the Buffer Counts section, look for the Target value. The following shows part of DBCC MEMORYSTATUS output after the server has reached its normal load.

Buffer Counts Buffers

------------------------------ --------------------

Committed 201120

Target 201120

Hashed 166517

Reserved Potential 143388

Stolen Potential 173556

External Reservation 0

Min Free 256

Visible 201120

Available Paging File 460640

Target is computed by SQL Server as the number of 8-KB pages it can commit without causing paging. Target is recomputed periodically and in response to memory low/high notifications from Windows. A decrease in the number of target pages on a normally loaded server may indicate response to an external physical memory pressure.

If SQL Server consumed a lot of memory (as determined by Process: Private Bytes or the Mem Usage column in Task Manager), see if the Target count amounts for a significant portion of the memory. Note that if AWE is enabled, you have to account for AWE allocated memory either from sys.dm_os_memory_clerks or DBCC MEMORYSTATUS output.

Consider the example shown above (AWE not enabled), Target * 8 KB = 1.53 GB, while the Process: Private Bytes for the server is approximately 1.62 GB or the Buffer Pool target accounts for 94% of the memory consumed by SQL Server. Note that if the server is not loaded, Target is likely to exceed the amount reported by Process: Private Bytes performance counter, which is normal.

If Target is low, but the server Process: Private Bytes or the Mem Usage in Task Manager is high, we might be facing internal memory pressure from components that use memory from outside the buffer pool. Components that are loaded into the SQL Server process, such as COM objects, linked servers, extended stored procedures, SQLCLR and others, contribute to memory consumption outside of the buffer pool. There is no easy way to track memory consumed by these components especially if they do not use SQL Server memory interfaces.

Components that are aware of the SQL Server memory management mechanisms use the buffer pool for small memory allocations. If the allocation is bigger than 8 KB, these components use memory outside of the buffer pool through the multi-page allocator interface.

Following is a quick way to check the amount of memory that is consumed through the multi-page allocator.

-- amount of mem allocated though multipage allocator interface

select sum(multi_pages_kb) from sys.dm_os_memory_clerks

You can get a more detailed distribution of memory allocated through the multi-page allocator as:

select

type, sum(multi_pages_kb)

from

sys.dm_os_memory_clerks

where

multi_pages_kb != 0

group by type

type

------------------------------------------ ---------

MEMORYCLERK_SQLSTORENG 56

MEMORYCLERK_SQLOPTIMIZER 48

MEMORYCLERK_SQLGENERAL 2176

MEMORYCLERK_SQLBUFFERPOOL 536

MEMORYCLERK_SOSNODE 16288

CACHESTORE_STACKFRAMES 16

MEMORYCLERK_SQLSERVICEBROKER 192

MEMORYCLERK_SNI 32

If a significant amount of memory is allocated through the multi-page allocator (100-200 MB or more), further investigation is warranted.

If you are seeing large amounts of memory allocated through the multi-page allocator, check the server configuration and try to determine the components that consume most of the memory by using the previous or the following query.

If Target is low but percentage-wise it accounts for most of the memory consumed by SQL Server, look for sources of the external memory pressure as described in the previous subsection (External Physical Memory Pressure) or check the server memory configuration parameters.

If you have the max server memory and/or min server memory options set, you should compare your target against these values. The max server memory option limits the maximum amount of memory consumed by the buffer pool, while the server as a whole can still consume more. The min server memory option tells the server not to release buffer pool memory below the setting. If Target is less than the min server memory setting and the server is under load, this may indicate that the server operates under the external memory pressure and was never able to acquire the amount specified by this option. It may also indicate the pressure from internal components, as described above. Target count cannot exceed the max server memory option setting.

First, check for stolen pages count from DBCC MEMORYSTATUS output.

Buffer Distribution Buffers

------------------------------ -----------

Stolen 32871

Free 17845

Cached 1513

Database (clean) 148864

Database (dirty) 259

I/O 0

Latched 0

A high percentage (>75-80%) of stolen pages relative to target (see the previous fragments of the output) is an indicator of the internal memory pressure.

More detailed information about memory allocation by the server components can be assessed by using the sys.dm_os_memory_clerks DMV.

-- amount of memory consumed by components outside the Buffer pool

-- note that we exclude single_pages_kb as they come from BPool

-- BPool is accounted for by the next query

select

sum(multi_pages_kb

+ virtual_memory_committed_kb

+ shared_memory_committed_kb) as [Overall used w/o BPool, Kb]

from

sys.dm_os_memory_clerks

where

type <> 'MEMORYCLERK_SQLBUFFERPOOL'

-- amount of memory consumed by BPool

-- note that currenlty only BPool uses AWE

select

sum(multi_pages_kb

+ virtual_memory_committed_kb

+ shared_memory_committed_kb

+ awe_allocated_kb) as [Used by BPool with AWE, Kb]

from

sys.dm_os_memory_clerks

where

type = 'MEMORYCLERK_SQLBUFFERPOOL'

Detailed information per component can be obtained as follows. (This includes memory allocated from buffer pool as well as outside the buffer pool.)

declare @total_alloc bigint

declare @tab table (

type nvarchar(128) collate database_default

,allocated bigint

,virtual_res bigint

,virtual_com bigint

,awe bigint

,shared_res bigint

,shared_com bigint

,topFive nvarchar(128)

,grand_total bigint

);

-- note that this total excludes buffer pool committed memory as it represents the largest consumer which is normal

select

@total_alloc =

sum(single_pages_kb

+ multi_pages_kb

+ (CASE WHEN type <> 'MEMORYCLERK_SQLBUFFERPOOL'

THEN virtual_memory_committed_kb

ELSE 0 END)

+ shared_memory_committed_kb)

from

sys.dm_os_memory_clerks

print

'Total allocated (including from Buffer Pool): '

+ CAST(@total_alloc as varchar(10)) + ' Kb'

insert into @tab

select

type

,sum(single_pages_kb + multi_pages_kb) as allocated

,sum(virtual_memory_reserved_kb) as vertual_res

,sum(virtual_memory_committed_kb) as virtual_com

,sum(awe_allocated_kb) as awe

,sum(shared_memory_reserved_kb) as shared_res

,sum(shared_memory_committed_kb) as shared_com

,case when (

(sum(single_pages_kb

+ multi_pages_kb

+ (CASE WHEN type <> 'MEMORYCLERK_SQLBUFFERPOOL'

THEN virtual_memory_committed_kb

ELSE 0 END)

+ shared_memory_committed_kb))/(@total_alloc + 0.0)) >= 0.05

then type

else 'Other'

end as topFive

,(sum(single_pages_kb

+ multi_pages_kb

+ (CASE WHEN type <> 'MEMORYCLERK_SQLBUFFERPOOL'

THEN virtual_memory_committed_kb

ELSE 0 END)

+ shared_memory_committed_kb)) as grand_total

from

sys.dm_os_memory_clerks

group by type

order by (sum(single_pages_kb + multi_pages_kb + (CASE WHEN type <>

'MEMORYCLERK_SQLBUFFERPOOL' THEN virtual_memory_committed_kb ELSE 0 END) +

shared_memory_committed_kb)) desc

select * from @tab

Note that the previous query treats Buffer Pool differently as it provides memory to other components via a single-page allocator. To determine the top ten consumers of the buffer pool pages (via a single-page allocator) you can use the following query.

-- top 10 consumers of memory from BPool

select

top 10 type,

sum(single_pages_kb) as [SPA Mem, Kb]

from

sys.dm_os_memory_clerks

group by type

order by sum(single_pages_kb) desc

You do not usually have control over memory consumption by internal components. However, determining which components consume most of the memory will help narrow down the investigation of the problem.

System Monitor (Perfmon)

You can also check the following counters for signs of memory pressure (see SQL Server Books Online for detailed description):

SQL Server: Buffer Manager object

• Low Buffer cache hit ratio

• Low Page life expectancy

• High number of Checkpoint pages/sec

• High number Lazy writes/sec

Insufficient memory and I/O overhead are usually related bottlenecks. See I/O Bottlenecks in this paper.

* External Virtual memory pressure:

Running low on space in the system page file(s). This may cause the system to fail memory allocations, as it is unable to page out currently allocated memory. This condition may result in the whole system responding very slowly or even bring it to a halt.

General troubleshooting steps:

• Increase swap file size.

• Check for major physical memory consumers and follow steps of external physical memory pressure.

You need to determine if page file(s) have enough space to accommodate current memory allocations. To check this, open Task Manager in Performance view and check the Commit Charge section. If Total is close to the Limit, then there exists the potential that page file space may be running low. Limit indicates the maximum amount of memory that can be committed without extending page file space. Note that the Commit Charge Total in Task Manager indicates the potential for page file use, not the actual use. Actual use of the page file will increase under physical memory pressure.

Equivalent information can be obtained from the following counters: Memory: Commit Limit, Paging File: %Usage, Paging File: %Usage Peak.

You can roughly estimate the amount of memory that is paged out per process by subtracting the value of Process: Working Set from the Process Private Bytes counters.

If Paging File: %Usage Peak (or Peak Commit Charge) is high, check the System Event Log for events indicating page file growth or notifications of “running low on virtual memory”. You may need to increase the size of your page file(s). High Paging File: %Usage indicates a physical memory over commitment and should be considered together with external physical memory pressure (large consumers, adequate amount of RAM installed).

Internal Virtual memory pressure:

Running low on VAS due to fragmentation (a lot of VAS is available but in small blocks) and/or consumption (direct allocations, DLLs loaded in SQL Server VAS, high number of threads).

SQL Server detects this condition and may release reserved regions of VAS, reduce buffer pool commit target, and start shrinking caches.

General troubleshooting steps:

• Identify major memory consumers inside SQL Server.

• Verify server configuration.

• Further actions depend on the investigation: check for workload; possible design issues; other resource bottlenecks.

How to troubleshoot SQL server memory related issues (Part 1)

bradley — Fri, 16 Jan 2009 03:19:00 GMT

From the Partner managed newsgroup:

This section specifically addresses low memory conditions and ways to diagnose them as well as different memory errors, possible reasons for them, and ways to troubleshoot.

Background

It is quite common to refer to different memory resources by using the single generic term memory. As there are several types of memory resources, it is important to understand and differentiate which particular memory resource is referred to.

Virtual address space and physical memory

In Microsoft Windows, each process has its own virtual address space (VAS). The set of all virtual addresses available for process use constitutes the size of the VAS. The size of the VAS depends on the architecture (32- or 64-bit) and the operating system. In the context of troubleshooting, it is important to understand that virtual address space is a consumable memory resource and an application can run out of it even on a 64-bit platform while physical memory may still be available.

For more information about virtual address space, see “Process Address Space” in SQL

Server Books Online and the article called Virtual Address Space (http://msdn.microsoft.com/library/en-us/memory/base/virtual_address_space.asp) on MSDN.

Address Windowing Extensions (AWE)

and SQL Server

Address Windowing Extensions (AWE) is an API that allows a 32-bit application to manipulate physical memory beyond the inherent 32-bit address limit. AWE mechanism technically is not necessary on 64-bit platform. It is, however, present there. Memory pages that are allocated through the AWE mechanism are referred as locked pages on the 64-bit platform.

On both 32- and 64-bit platforms, memory that is allocated through the AWE mechanism cannot be paged out. This can be beneficial to the application. (This is one of the reasons for using AWE mechanism on 64-bit platform.) This also affects the amount of RAM that is available to the system and to other applications, which may have detrimental effects. For this reason, in order to use AWE, the Lock Pages in Memory privilege must be enabled for the account that runs SQL Server.

From a troubleshooting perspective, an important point is that the SQL Server buffer pool uses AWE mapped memory; however, only database (hashed) pages can take full advantage of memory allocated through AWE. Memory allocated through the AWE mechanism is not reported by Task Manager or in the Process: Private Bytes performance counter. You need to use SQL Server specific counters or Dynamic Management Views to obtain this information.

For more information about AWE mapped memory, see “Managing memory for large databases” and “Memory Architecture” in SQL Server Books Online topics and Large Memory Support (http://msdn.microsoft.com/library/en-us/memory/base/large_memory_support.asp) on MSDN.

The following table summarizes the maximum memory support options for different configurations of SQL Server 2005. (Note that a particular edition of SQL Server or Windows may put more restrictive limits on the amount of supported memory.)

Min server memory (MB)

Provides floor

Does not automatically commit on startup

May not be able to reach floor

Max server memory (MB)

Provides ceiling

SQL Server may not be able to back off due to current usage

SQL Server 2000 organizes the memory it allocates into two distinct regions: the BPool (buffer pool) and MemToLeave (memory to leave) regions. If you make use of AWE memory, there's actually a third region: the physical memory above 3GB made available by the Windows AWE support.

The BPool is the preeminent region of the three. It is the primary allocation pool in SQL Server, serves primarily as a data and index page cache, and is also used for memory allocations less than 8KB. MemToLeave consists of the virtual memory space within the user mode address space that is not used by the BPool. The AWE memory above 3GB functions as an extension of the BPool and provides additional space for caching data and index pages. In SQL Server 2005, the definitioin of Bpool is not so clear as SQL 2000 because SQL 2005 memory mangment is based SQLOS and they are usually considered by memory_clerks.

Inside SQL Server 2000's Memory Management Facilities

http://msdn.microsoft.com/en-us/library/aa175282(SQL.80).aspx

SQLOS's memory manager and SQL Server's Buffer Pool

http://blogs.msdn.com/slavao/archive/2005/02/11/371063.aspx

SQL Server Memory Definitions

Data Cache

Consumer

• Connection – Responsible for PSS and ODS memory allocations

• General – Resource structures, parse headers, lock manager objects

• Utilities – Recovery, Log Manager

• Optimizer – Query Optimization

• Query Plan – Query Plan Storage

Advanced Consumer

• Ccache – Procedure cache. Accepts plans from the Optimizer and Query Plan consumers. Is responsible for managing that memory and determines when to release the memory back to the Bpool.

• Log Cache – Managed by the LogMgr, which uses the Utility consumer to coordinate memory requests with the Bpool.

Memory pressures

Memory pressure denotes a condition when limited amount of memory is available. Identifying when SQL Server runs under a memory pressure will help you troubleshoot memory-related issues. SQL Server responds differently depending on the type of memory pressure that is present. The following table summarizes the types of memory pressures, and their general underlying causes. In all cases, you are more likely to see timeout or explicit out-of-memory error messages.

*External Physical memory pressure:

Physical memory (RAM) running low. This causes the system to trim working sets of currently running processes, which may result in overall slowdown.

SQL Server detects this condition and, depending on the configuration, may reduce the commit target of the buffer pool and start clearing internal caches.

General troubleshooting steps:

• Find major system memory consumers.

• Attempt to eliminate (if possible).

• Check for adequate system RAM and consider adding more RAM (usually requires more careful investigation beyond the scope of this paper).

Open Task Manager in Performance view and check the Physical Memory section, Available value. If the available memory amount is low, external memory pressure may be present. The exact value depends on many factors, however you can start looking into this when the value drops below 50-100 MB. External memory pressure is clearly present when this amount is less than 10 MB.

The equivalent information can also be obtained using the Memory: Available Bytes counter in System Monitor.

If external memory pressure exists and you are seeing memory-related errors, you will need to identify major consumers of the physical memory on the system. To do this, look at Process: Working Set performance counters or the Mem Usage column on the Processes tab of Task Manager and identify the largest consumers.

The total use of physical memory on the system can be roughly accounted for by summing the following counters.

• Process object, Working Set counter for each process

• Memory object

• Cache Bytes counter for system working set

• Pool Nonpaged Bytes counter for size of unpaged pool

• Available Bytes (equivalent of the Available value in Task Manager)

If there’s no external pressure, the Process: Private Bytes counter or the VM Size in Task Manager should be close to the size of the working set (Process: Working Set or Task Manager Mem Usage), which means that we have no memory paged out.

Note that the Mem Usage column in Task Manager and corresponding performance counters do not count memory that is allocated through AWE. Thus the information is insufficient if AWE is enabled. In this case, you need to look at the memory distribution inside SQL Server to get a full picture.

You can use the sys.dm_os_memory_clerks DMV as follows to find out how much memory SQL Server has allocated through AWE mechanism.

select

sum(awe_allocated_kb) / 1024 as [AWE allocated, Mb]

from

sys.dm_os_memory_clerks

Note that in SQL Server, currently only buffer pool clerks (type = ‘MEMORYCLERK_SQLBUFFERPOOL’) use this mechanism and only when AWE is enabled.

Relieving external memory pressure by identifying and eliminating major physical memory consumers (if possible) and/or by adding more memory should generally resolve the problems related to memory.

SQL Server 2008 Cumulative Update 1 is available

bradley — Tue, 23 Sep 2008 19:45:00 GMT

Aaron Bertrand : SQL Server 2008 Cumulative Update 1 is available:
http://sqlblog.com/blogs/aaron_bertrand/archive/2008/09/23/sql-server-2008-cumulative-update-1-is-available.aspx

They just RTM'd SQL 2008 and already there's an update. And ya wonder why those of us hold back a bit from deploying?

SQL information from the Partner newsgroups

bradley — Tue, 09 Sep 2008 04:38:00 GMT

In this Issue:

TOP SUPPORT ISSUES

NEW & UPDATED KB ARTICLES

NEW & TOP DOWNLOADS

NEW WEBCASTS

RECENT SUPPORT ISSUES

===================

Issue

----------

When you tied to install security update 948109, you encountered the following error:

Product Installation Status

Product : SQL Server Database Services 2005 (LPI)

Product Version (Previous): 3042

Product Version (Final) :

Status : Failure

Log File : C:\Program Files\Microsoft SQL Server\90\Setup

Bootstrap\LOG\Hotfix\SQL9_Hotfix_KB948109_sqlrun_sql.msp.log

SQL Express Features :

Error Number : 29512

Error Description : MSP Error: 29512 SQL Server Setup was unable

add user NT AUTHORITY\SYSTEM to local group

PACIFICHYDRO\SQLServer2005MSSQLUser$PHMELUTIL4$LPI.

Analysis

--------

This issue might occur because of one of the following reasons:

1: An operation has removed the local groups for the initial installation of SQL Server 2005.

2: An operation has changed the security identifiers (SID) for the local groups.

3: You have manually deleted the group.

Resolution

--------------

1> Go to the following Reg path

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.X\Setup

2> Backup the Registry

3> Remove the following registry subkeys that store SID settings: ( Whichever is present )

o HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.X\Setup\SQLGroup

o HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.X\Setup\AGTGroup

o HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.X\Setup\FTSGroup

o HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.X\Setup\ASGroup

Note In these registry subkeys, MSSQL. X is a placeholder for the corresponding value on a specific system. You can determine MSSQL. X on a specific system by examining the value of the MSSQLSERVER registry entry under the following registry subkey:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL\

4> Re-run setup.

NEW & UPDATED KB ARTICLES

=========================

Access

------

956838 Description of the Access 2007 hotfix package: August 26, 2008

http://support.microsoft.com/default.aspx?scid=kb;EN-US;956838

SQL

----

956909 The SQL Server 2008 builds that were released after SQL Server 2008 was released

http://support.microsoft.com/default.aspx?scid=kb;EN-US;956909

NEW & TOP DOWNLOADS

====================

Microsoft SQL Server 2008 Express with Tools

http://www.microsoft.com/downloads/info.aspx?na=22&p=5&SrcDisplayLang=en&SrcCategoryId=&SrcFamilyId=&u=%2fdownloads%2fdetails.aspx%3fFamilyID%3d7522a683-4cb2-454e-b908-e805e9bd4e28%26DisplayLang%3den

Microsoft SQL Server 2008 Express with Advanced Services

http://www.microsoft.com/downloads/info.aspx?na=22&p=6&SrcDisplayLang=en&SrcCategoryId=&SrcFamilyId=&u=%2fdownloads%2fdetails.aspx%3fFamilyID%3db5d1b8c3-fda5-4508-b0d0-1311d670e336%26DisplayLang%3den

Microsoft SQL Server Protocol Documentation

http://www.microsoft.com/downloads/info.aspx?na=22&p=8&SrcDisplayLang=en&SrcCategoryId=&SrcFamilyId=&u=%2fdownloads%2fdetails.aspx%3fFamilyID%3d91ef5106-944a-41e1-b3a0-5bd3f2356f32%26DisplayLang%3den

SQL Server 2005 Best Practices Analyzer

http://www.microsoft.com/downloads/info.aspx?na=22&p=10&SrcDisplayLang=en&SrcCategoryId=&SrcFamilyId=&u=%2fdownloads%2fdetails.aspx%3fFamilyID%3dda0531e4-e94c-4991-82fa-f0e3fbd05e63%26DisplayLang%3den

Security Update for Access Snapshot Viewer 2002 (KB955440)

http://www.microsoft.com/downloads/info.aspx?na=22&p=2&SrcDisplayLang=en&SrcCategoryId=&SrcFamilyId=&u=%2fdownloads%2fdetails.aspx%3fFamilyID%3d34b655f8-1922-4246-94ca-ed381c3e3b13%26DisplayLang%3den

Office Protocol Documentation

http://www.microsoft.com/downloads/info.aspx?na=22&p=1&SrcDisplayLang=en&SrcCategoryId=&SrcFamilyId=&u=%2fdownloads%2fdetails.aspx%3fFamilyID%3de7a23d42-0835-440f-9400-badfe9672b21%26DisplayLang%3den

NEW WEBCASTS

============

Lunch and Learn Webcast Series – All About SQL 2008

http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032387909&EventCategory=2&culture=en-US&CountryCode=US

TechNet Webcast: Protect SQL Server 2008 with Data Protection Manager 2007 (Level 300)

http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032384075&EventCategory=4&culture=en-US&CountryCode=US

MSDE a memory hog....

bradley — Wed, 20 Feb 2008 03:12:00 GMT

MSDE a memory hog..... Who da thunk...not me....

Dear Susan Bradley,

After extensive research and monitoring, it has been determined
that the resource used by SQL express engine causes a dramatic
performance decrease for the server.  Futhermore, it creates
security issues for the server.  Unfortunately, we are forced
to discontinue support for SQL Express database engine on
March 19, 2008. For all customers using SQL Express engine,
please move your database to a SQL 2005 environment. The easiest
way is to use "attach" function through the
"control panel -> database -> mssql admin" section to
attach your .mdf and .ldf file. If you need further assistance,
please feel free to contact our help desk support staff. Thank you.


Thanks,

Webhost4life.com Team

Per my read...

bradley — Sat, 05 Jan 2008 07:59:00 GMT

http://support.microsoft.com/lifecycle/?LN=en-us&p1=2852&x=4&y=8

SQL 2000 falls out of mainstream support in April of 2008. If your line of business vendor is still only supporting SQL 2000, sit them down and have a talk with them. Ask them what their support and lifecycle plans are? Future plans for upgrades? Supportability?

Because the line in the sand is April of 2008.

Keepin' up with SQL

bradley — Tue, 18 Dec 2007 03:15:00 GMT

The SQL Server 2005 builds that were released after SQL Server 2005 Service Pack 2 was released:
http://support.microsoft.com/kb/937137/LN/

I have no idea what build number I have of SQL 2005 to be honest with you...but I'm losing track of 'em for sure. Brianna has a different build as she has a hotfix on her, but those in the rest of the fleet are just normal SQL 2005. But check out that listing if you need to keep track of the build versions you are on.

Downgrade rights for SQL

bradley — Sat, 15 Sep 2007 01:38:00 GMT

How to downgrade to SQL Server 2000 on a computer that is running Windows SBS 2003 R2 Premium Edition:
http://support.microsoft.com/?kbid=935573

Buried in yesterday's KB listing was that gem. Okay read it again.... you know what it means? I'll be clearer...

http://www.microsoft.com/windowsserver2003/sbs/downgrade/default.mspx

Read that.

In particular this:

If you have licensed a copy of Windows Small Business Server 2003 R2 Premium Edition (which includes Microsoft SQL Server 2005 Workgroup Edition), but your line of business applications are not yet available on the Microsoft SQL Server 2005 platform, you may be able to request downgrade rights to SBS 2003 SP1 Premium (which includes Microsoft SQL Server 2000 Standard Edition the version of Microsoft SQL Server that shipped with Small Business Server 2003 and SP1), and use the downgraded SQL Server in your SBS 2003 R2 environment. You may exercise this downgrade through the channel in which you licensed your original version of Windows Small Business Server R2 Premium Edition. This downgrade offer is available only through April 30, 2008.

Okay I'll say it in plain English. Got a SBS 2003 R2 and you can't install SQL 2000 because your LOB app won't support it? No problem. If a Volume license customer, order downgrade media. If retail or system builder, order from that page. But what we thought... the all or nothing of SBS where we thought if you needed SQL 2000 you HAD to stay on SBS 2003 sp1 and could not put any of the R2ism on that box is no longer true.

(with apologies to Bill Peng in the newsgroup who answered a poster in the newsgroup saying that all he had to do was to get the downgrade rights and install SQL 2000 and I was scratching my head going, "Huh?". Bill was right all the time)

Detach/reattach revisited

bradley — Sat, 11 Aug 2007 21:45:00 GMT

http://msmvps.com/blogs/bradley/archive/2007/08/11/detach-and-reattach.aspx

So Chad informs me that all that SQL command line stuff I was so proud of could have been all done via the GUI

'sure nuff. Detach and reattach via the GUI....

Now he tells me.....

"Click Add and you can browse out to the location of your .mdf file. It will automatically pick up the .ldf file as well (assuming of course it is in the same directory as the .mdf). And once the .mdf & .ldf files are listed, you can edit the value of the Attach As field directly if you want to change the database name. Sometimes this is very useful (especially if we’re doing a data migration and want to move some info from an old database to a new database. Often we want the new database to have the name that our app expects – this way we can change the name on the old database in order to attach it to then run our queries to migrate data."

Detach and reattach

bradley — Sat, 11 Aug 2007 16:26:00 GMT

So you need to move a SQL database 'eh?

First off the trick is to "get" to that database with a management tool

http://msmvps.com/blogs/bradley/archive/2007/06/12/connecting-to-the-wsus-database-with-sql-2005-s-tools.aspx

If you don't have the full blown SQL tools, download this one:

http://www.microsoft.com/downloads/details.aspx?familyid=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en

Then you can use a command line (yes, you heard me, command line), right mouse click on that name of the database and run query to detach and attach like so.. so what's the database name for WSUS you ask?

Here's what I found looking at mine... I had several... (this is the view from the SQL 2005 management tools but the concept is the same)

Those sharepoint databases are there from a WSS 3.0 side by side install that I need to kick over to SQL 2005 one of these days.

And in doing a command on the wss_content that one got stuck on my C: drive

See that "New Query" button? click there. Once you've built the command argument you then click "Execute" to run the command.

So last night I got into a situation in the blog server where the database and the log files grew so much that we were nearly running out of room and thanks to the collective intelligence of Henry, Edwin and Brian I did a query command to move the databases to a new location... and for WSUS the same rules apply.

You detach.

You copy (or cut and paste the database files)

You reattach.

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

1.	Detach the database as follows:`use master go sp_detach_db 'thenameofyourdatabase' go`
2.	Next, copy the data files and the log files from the current location (D:\Mssql7\Data) to the new location (E:\Sqldata).
3.	Re-attach the database. Point to the files in the new location as follows:`use master go sp_attach_db 'thenameofyourdatabase','E:\Sqldata\thenameofyourdatabase.mdf','E:\Sqldata\thenameofyourdatabase.ldf' go`Verify the change in file locations by using the sp_helpfile stored procedure:`use thenameofyourdatabase go sp_helpfile go`

I am such a SQL newbie it's not funny

bradley — Tue, 07 Aug 2007 05:59:00 GMT

When I go to check the mg_EmailQueue table it gives off an error and tells me to run DBCC CHECKTABLE.

I am such a SQL newbie dweeb it's not funny... okay so reading this...

http://msdn2.microsoft.com/en-us/library/ms174338.aspx

What do I put as view table? Definintely taking a SQL backup before I try this....

DBCC CHECKTABLE 
(
        'mg_EmailQueue' | 'view_name'
    [ , { NOINDEX | index_id }
     |, { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } 
    ] 
)
    [ WITH 
        { ALL_ERRORMSGS ]
          [ , NO_INFOMSGS ]
          [ , TABLOCK ] 
          [ , ESTIMATEONLY ] 
          [ , { PHYSICAL_ONLY | DATA_PURITY } ] 
        }
    ]

Edwin said to do

USE [database]GODBCC CHECKTABLE ('mg_EmailQueue')

Connecting to the WSUS database with SQL 2005's tools

bradley — Tue, 12 Jun 2007 22:20:00 GMT

I'm not a command line gal and I wanted to document that in order to use the SQL management studio included in SQL 2005 workgroup/R2 to do this, doing this didn't work. Instead as the comment said in the prior blog post, I have to use a pipe string locally....

http://projectdream.org/wordpress/2007/05/10/wsus-v3-and-connections-to-its-internal-database/

\\.\pipe\mssql$microsoft##ssee\sql\query

Once you use that, you can connect to that embedded database just fine and use the tools to adjust accordingly.

Hmmm just realized it installed on the C: drive ...wonder how I can move that...

One weird hotfix

bradley — Sat, 02 Jun 2007 04:52:00 GMT

FIX: Event ID: 1 may be logged when you use a Volume Shadow Copy Service (VSS) writer to back up a snapshot in SQL Server 2005:
http://support.microsoft.com/default.aspx?scid=kb;en-us;934396

Okay that's a slightly weird hotfix

When I get a hotfix I unzip it and put it in a folder with the KB number on it.

So I went to install it and it gave this cryptic message about being a backwards compatibility install and I wondered if I had the right hotfix. So I went back to the folder to ensure I had the right hotfix and the hotfix.txt file had a different KB number.

Hm.. did I screw up? So let's go back to the original hotfix email and sure 'nuff it has two KB numbers in the email

Package:
-----------------------------------------------------------
KB Article Number(s): 934396, 935679
Language: English
Platform: i386
Location: (http://hotfixv4.microsoft.com/SQLCompat2005/sp2/SQLCompat2005_SP2_Hotfixmungingthefilename)
Password: AgaindoyoureallythinkI'dputafileandpassworduphere?
Password Changes On: 05/18/2007

Regardless this is one weird hotfix as it is like going through a reinstall

Finished. That should ensure that I don't get those annoying but not harmful error messages in the event logs during/after backup.

Controling your SQL instances without the full blown SQL

bradley — Sat, 19 May 2007 01:36:00 GMT

If you are running SBS standard and you want to control the SQL that comes with WSUS 3.0 the EXACT tool you need can be obtained from THIS link:

http://go.microsoft.com/fwlink/?LinkId=65110

(Image courtesy of Handy Andy Goodman as I still haven't figured out how to connect to mine)

Once you install that on your server you can adjust the RAM value there for WSUS and control the instance (and other SQL instances in fact)

Will I be forced to use command line? The horrors...

bradley — Fri, 18 May 2007 02:26:00 GMT

So you've seen this post but don't have OSQL on your system and you want to stomp on the memory of WSUS 3.0? So how do you do it in a GUI format?

Now keep in mind these instructions are for Standard machines, not premium as I am blocked from downloading this express tool:

http://msdn.microsoft.com/vstudio/express/sql/download/

Download that... then...(stealing a post from Handy Andy Goodman)
Go to http://go.microsoft.com/fwlink/?LinkId=65110 to be exact

Once you install the tool, launch it,

You right click the top line servername\MICROSOFT##SSEE and do properties
You click memory in the window that pops up
You set the maximum server memory to whatever you like, most are saying between 200-256Mb
Click OK
You right click the top line servername\MICROSOFT##SSEE and click restart

On my SBS 2003 premium with SQL 2005 workgroup I found however that I can't control the WSUS SQL with my full blown SQL management tools...

Nor can I install the Express edition...

I may have to break out SQLcmd.exe instead of OSQL.exe or something.... oh the horrors...

Event ID: 1 may be logged when you use a Volume Shadow Copy Service (VSS) writer

bradley — Thu, 17 May 2007 19:21:00 GMT

FIX: Event ID: 1 may be logged when you use a Volume Shadow Copy Service (VSS) writer to back up a snapshot in SQL Server 2005:
http://support.microsoft.com/default.aspx?scid=kb;en-us;934396

If you are running a SBS 2003 box with SQL 2005 workgroup and install SQL sp2 I've seen folks in the newsgroup report this issue (this is the reason that I've held back on installing SP2 for SQL 2005 on my server here in fact).

Call in (the USA) 1-800-936-4900
Dial 2 for ITPro
I didn't have to press one for hotfixes
State that you need KB934396 and you will get this FOR FREE

(btw not sure if this is needed for SQL embedded)