How to rebuild system databases in SQL Server 2005

Rebuilding the master database installs all system databases to their initial location. Use below procedure to rebuild the master database for a corrupted installation of SQL Server also.

To rebuild the system databases you need to run the setup command from the Command prompt and follow the following procedure:

1. Click Start, click Run, type cmd, and then click OK.
2. Run the following command to rebuild the system databases:

start /wait \setup.exe /qn INSTANCENAME= REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=StrongPassword

For example:

start /wait D:\setup.exe /qn INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=password

The /qn switch Writes all Setup messages, including error messages in Setup log files. The log is located at %ProgramFiles%\Microsoft SQL Server\90\Setup Bootstrap\LOG\Summary.txt

The /qb switch can be used instead of the /qn switch. /qb allows the display basic setup dialog boxes and all error messages.

SAPWD is needed to specify a new password for the System Administrator account. Microsoft recommends de use of a strong password.

The parameter INSTANCENAME is used to specify the instance name. Use MSSQLSERVER for the deafult instance.

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

Creating a comma-separated list

This solution utilizes features introduced in SQL 2005. It starts off with a CTE (common table expression) of all of the distinct AccountNumbers in the table. For each AccountNumber, we get a comma separated list of the Value field, sorted by the Value field.

WITH CTE AS
(
SELECT DISTINCT 
      AccountNumber
  FROM #TestData
)
SELECT AccountNumber,
      CommaList = STUFF((
                  SELECT ',' + Value
                     FROM #TestData
                    WHERE AccountNumber = CTE.AccountNumber
                    ORDER BY Value
                      FOR XML PATH(''),
                             TYPE).value('.','varchar(max)'),1,1,'')
  FROM CTE
 ORDER BY AccountNumber;

The key to creating the comma separated list is the correlated subquery. Working from the inside out, we get each value prefixed with a comma, order by the Value. The FOR XML PATH('') generates an XML structure, with an empty string as the root node. Since the field is ',' + Value (an unnamed expression), there is no name for the individual elements. What is left is a list of values, with each value prefixed with a comma. The TYPE clause specifies to return the data as an XML type. The .value('.','varchar(max)') takes each value, and converts it into a varchar(max) data type. The combination of the TYPE and .value means that values are created at XML tags (such as the ampersand (&), and the greater than (>) and less than (<) signs), will not be tokenized into their XML representations and will remain as is.

At this point, you will have a comma separated list of all values starting with a comma for each value. All that remains is to remove the very first leading comma from the entire string. To do this, we utilize the STUFF function. Using the string created by the FOR XML PATH(''), TYPE, and starting with the first character, we replace one character (the leading comma) with an empty string. (Note that if you wanted the string to be separated with a comma and a space, you would specify ', ', and replace 2 characters in the STUFF function with an empty string.

The subquery is correlated, meaning that it references a value outside of itself to control what it is doing. In this case, it is referencing the current AccountNumber from the CTE.

The results will look like this (abbreviated due to length)

AccountNumber CommaList ------------- -----------------------------------------------------------------------------
1 @@H,@BE,@CE,@DA,@FA,@FH,@GB,@GD,@HC,@HG,A@E,A@G,AEC,AH@,AHB,AHI,AIG,B@@,B@A,B@B,BBG,B, ...
2 @CB,@CE,@CG,@DB,@EE,@GG,@GG,@HC,@IF,A@E,AAF,AAI,ACG,AEA,AFA,AFB,AFC,AFC,AFI,AGF,AIE,AIH,B ...
3 @@E,@@H,@BE,@CD,@DC,@DI,@EF,@EI,@FB,@GE,A@@,AAE,ACE,AEF,AFA,AGC,AH@,AIH,B@C,BAI,BC@,BDF, ... 

Resources:

SQL Spackle - Creating a comma-separated list.docx

How to Identify Memory related performance problem in Microsoft SQL Server

There are many reasons for memory related performance problems on a MS SQL Server instance, there can be either memory pressure from other applications, limit in physical or virtual memory or inside the SQL Server. We have many built-in tools which can be used to know the root cause.

DBCC MEMORYSTATUS command

We can use the DBCC MEMORYSTATUS command to check for any abnormal memory problem in SQL Server. Run the

DBCC MEMORYSTATUS

Command and scroll down to the Buffer Counts section, look for the Target value. It shows the number of 8-KB pages which can be committed without causing paging. A drop in the number of target pages might indicate response to an external physical memory pressure.

If the Committed amount is above Target, continue investigating the largest memory consumers inside SQL Server. When the server is not loaded, Target normally exceeds Committed and the value of the Process: Private Bytes performance counter.

If the Target value is low, but the server Process: Private Bytes is high, you might be facing internal SQL memory problems with components that use memory from outside of the buffer pool. Such components include linked servers, COM objects, extended stored procedures, SQL CLR, etc. If the Target value is low but its value is close to the total memory used by SQL Server, than you should check whether your SQL Server received a sufficient amount of memory from the system. Also you can check the server memory configuration parameters.

You can compare the Target count against the max server memory values if it is set. Later option limits the maximum memory consumption of the buffer pool. Therefore the Target value cannot exceed this value. Also the low Target count can indicate problems: in case it is less than the min server memory setting, you should suspect external virtual memory pressure.

Also check the Stolen Pages count in DBCC MEMORYSTATUS output. A high percentage (>70%) of Stolen Pages compared to Target can be a sign of internal memory pressure.

Further reading on Microsoft Support page:

• How to use the DBCC MEMORYSTATUS command to monitor memory usage on SQL Server 2005

Resource Governor

The Resource Governor in SQL Server 2008 Enterprise edition allows you to fine tune SQL Server memory allocation strategies, but incorrect settings can be a cause for out-of-memory errors. The following DMVs can provide information about the Resource Governor feature of SQL Server 2008:

sys.dm_resource_governor_workload_groups, sys.dm_resource_governor_configuration and sys.dm_resource_governor_resource_pools

SQL Server Profiler

SQL Server Profiler is a graphical user interface to SQL Trace for monitoring an instance of the Database Engine or Analysis Services. It shows how SQL Server resolves queries internally. This allows administrators to see exactly what Transact-SQL statements or Multi-Dimensional Expressions are submitted to the server and how the server accesses the database or cube to return result sets.

Additional information can be found in the articles

Working with SQL Server Profiler Trace Files andCreating a Trace Template in SQL Server Profiler.

SQL Server ring buffers

Another source of diagnostic memory information is the sys.dm_os_ring_buffers DMV. Each ring buffer records the last number of notifications. You can query the ring buffer event counts using the following code:

SELECT ring_buffer_type, COUNT(*) AS [Events] FROM sys.dm_os_ring_buffers GROUP BY ring_buffer_type ORDER BY ring_buffer_type

Here is a list of ring buffers of interest:

• RING_BUFFER_BUFFER_POOL: This ring buffer contains records of buffer pool failures.
• RING_BUFFER_RESOURCE_MONITOR: This ring buffer captures every memory state change by using resource monitor notifications.
• RING_BUFFER_SCHEDULER_MONITOR: Stores information about the overall state of the server. The SystemHealthrecords are created with one minute intervals.
• RING_BUFFER_MEMORY_BROKER: This ring buffer contains memory notifications for the Resource Governor Resource pool.
• RING_BUFFER_OOM: This ring buffer contains records indicating out-of-memory conditions.

Dynamic Management Views

You can use the

sys.dm_os_memory_clerks

Dynamic management view (DMV) to get detailed information about memory allocation by the server components in SQL Server 2005 and 2008. You can get additional information about the caches by joining with the sys.dm_os_cache_counters (Please note that the amount of pages is NULL for USERSTORE entries):

SELECT DISTINCT SDMC.cache_address, SDMC.name, SDMC.type, SDMC.single_pages_kb, SDMC.multi_pages_kb, SDMC.single_pages_in_use_kb, SDMC.multi_pages_in_use_kb, SDMC.entries_count, SDMC.entries_in_use_count, SDMCCH.removed_all_rounds_count, SDMCCH.removed_last_round_count FROM sys.dm_os_memory_cache_counters AS SDMC JOIN sys.dm_os_memory_cache_clock_hands SDMCCH ON (SDMC.cache_address = SDMCCH.cache_address)

You can also use the following DMVs for memory troubleshooting both in SQL Server 2005 and 2008:

• sys.dm_exec_requests
• sys.dm_exec_query_memory_grants
• sys.dm_exec_query_resource_semaphores
• sys.dm_exec_sessions
• sys.dm_exec_cached_plans
• sys.dm_os_memory_cache_entries

Performance Monitor

Performance Monitor is part of the Microsoft Management Console, you can find it by navigating to Start Menu -> Administrative Tools Group. Additional information can be found in these articles:

• Setting up Performance Monitor to always collect SQL Server performance statistics
• SQL Server Database Specific Performance Counters

Important Properties of Database

Use below script to list out the important properties of the database

select
sysDB.database_id,
sysDB.Name as 'Database Name',
syslogin.Name as 'DB Owner',
sysDB.state_desc,
sysDB.recovery_model_desc,
sysDB.collation_name,
sysDB.compatibility_level,
sysDB.user_access_desc,
sysDB.is_read_only,
sysDB.is_auto_shrink_on,
sysDB.is_auto_close_on,
sysDB.is_auto_create_stats_on,
sysDB.is_auto_update_stats_on,
sysDB.is_fulltext_enabled,
sysDB.is_trustworthy_on
from sys.databases sysDB
INNER JOIN sys.syslogins syslogin ON sysDB.owner_sid = syslogin.sid

SQL DISTINCT on Multiple Columns

The DISTINCT clause works in combination with SELECT and gives you unique date from a database table or tables. The syntax for DISTINCT is show below

SELECT DISTINCT "column_name"
FROM "table_name"

If you want a DISTINCT combination of more than one column then the syntax is

SELECT DISTINCT column1, column2
FROM "table_name"

Let's look at some examples to understand the usage of the DISTINCT keyword. First, let's create a table for our illustration and insert some data.

CREATE TABLE DuplicateTest(
Firstname nvarchar (30) NOT NULL,
Lastname nvarchar(30) NOT NULL,
PostalCode nvarchar(15) NOT NULL,
City nvarchar(30) NOT NULL
)

insert into DuplicateTest
(Firstname,Lastname,PostalCode,City)
values
('Sarvesh', 'Singh', 'B283SP', 'Birmingham'),
('Steve', 'White', 'EC224HQ', 'London'),
('Mark', 'Smith', 'L324JK', 'Liverpool'),
('Claire', 'whitehood', 'M236DM', 'Manchester'),
('Param', 'Singh', 'B283SP', 'Birmingham')

select * from DistinctTutorial

DuplicateTest

Firstname	Lastname	PostalCode	City
Sarvesh	Singh	B263SP	Birmingham
Steve	White	EC224HQ	London
Mark	Smith	L324JK	Liverpool
Claire	whitehood	M236DM	Manchester
Param	Singh	B283SP	Birmingham

In the result set above there are repetitions in the City Column. Let's get a list of all cities without repeating them using DISTINCT.

select DISTINCT City from DuplicateTest

City
Birmingham
Liverpool
London
Manchester

You can see 'Birmingham' is just returned once in this result, even though it appears more than once in the table. You can get the same result using GROUP BY as shown below.

select city from DuplicateTest
group by city

Let's now use DISTINCT with more than one column. We will add the Lastname column in as well.

select DISTINCT City,Lastname from DuplicateTest

Distinct City and lastname

City	Lastname
Birmingham	Singh
Liverpool	Smith
London	White
Manchester	whitehood

We get a list of results that have multiple rows, none of which are duplicated.

Again, you can get the same result by using GROUP BY as shown below:

select city,lastname from DuplicateTest
group by city, lastname

If you look at the original data, there are two users with same Lastname (Singh) who live in the same city (Birmingham). With the DISTINCT keyword you get one unique row. Let's now add another column to our SELECT query.

select DISTINCT City,Lastname,Postalcode from DuplicateTest

This returns:

City	Lastname	PostalCode
Birmingham	Singh	B263SP
Birmingham	Singh	B283SP
Liverpool	Smith	L324JK
London	White	EC224HQ
Manchester	whitehood	M236DM

You will notice now that you are seeing two rows with the same lastname of Singh. This is because their 'Postalcode' is different, and the addition of that column makes the rows unique.

Again you will get the same result using GROUP BY as shown below:

select city, lastname, postalcode
from DuplicateTest
group by city, lastname, postalcode

Let's look at another example where you can use DISTINCT on multiple columns to find duplicate address. I've taken this example from the post. Please refer to this post for more detail.

SELECT PostCode, COUNT(Postcode)
FROM
 (
   SELECT DISTINCT Address1, Address2, City, Postcode
    FROM AddressTable
 ) AS Sub
GROUP BY Postcode
HAVING COUNT(Postcode) > 1

Or you can use GROUP BY as follows:

SELECT Address1,Address2,City,PostCode,Count(PostCode)
FROM AddressTable
GROUP BY Address1,Address2,City,PostCode
HAVING Count(PostCode) > 1

In both of these cases, we are using DISTINCT to find those rows that are duplicates, based on the columns we include in the queries.

DISTINCT can also be used to get unique column values with an aggregate function. In the example below, the query gets the unique values of reorderpoint and then does a SUM.

USE AdventureWorks
GO
SELECT SUM(DISTINCT ReorderPoint) as DistinctSum
FROM Production.Product
GO

Result: 1848 rows

In the example below query is doing a SUM of ReorderPoint including the duplicates.

SELECT SUM(ReorderPoint) as WithoutDistinct
FROM Production.Product
GO

Result: 202287 rows

As you can see from the above two examples the importance of DISTINCT with an aggregate function. The user could end up un-knowingly using completely incorrect SUM had he used the result from the second query if the requirement was to get the SUM of unique values of ReorderPoint.

Ref: http://www.sqlservercentral.com