Archive for the ‘ SQL 2005 ’ Category

SQL 2005 Install hangs and does not rollback

This drove me crazy because the only reason given was Fatal error during installation

 

 

 

 

 

 

 

 

 

 

The system event logs show

Timeout (30000 milliseconds) waiting for the SQL Server (MSSQLSERVER) service to connect.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

and

The SQL Server (MSSQLSERVER) service failed to start due to the following error:
The service did not respond to the start or control request in a timely fashion.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

I finally found that the problem is with the hardware having an odd number of cores

The answer is (from the article)

  1. At the time you get the failure to start SQL Sevrver during setup you are presented with a retry option.At this time replace the SQLSERVR.exe and SQLOS.dll into the BINN folder at your install location from an Service Pack 2 installation and then Retry.  This allows the SQL Server to start and complete the setup. YOU ARE NOW IN A PRODUCTION UNSUPPORTTED SITUATION.
  2. Immediately apply SQL Server SP2 and you are back in a supported configuration.

 

Unfortunately I quit and the install did not rollback so I have to

  1. Do a manual un-install
  2. Set the NUMPROC=1 in boot.ini
  • Click Start, click Run, type msconfig, and then click OK.
  • In the System Configuration Utility dialog box, click the BOOT.INI tab.
  • On the BOOT.INI tab, click Advanced Options.
  • In the BOOT.INI Advanced Options dialog box, click to select the /NUMPROC= check box.
  • In the list that is next to the /NUMPROC= check box, click 1, and then click OK.
  • In the System Configuration Utility dialog box, click OK.
  • Restart the server.
       3. Install SQL SP4
       4. Remove NUMPROC parameter from boot.ini
       5. Reboot and back in business

 

 

 

 

 

 

 

 

Cool code peice to analyze table size

–Greate script to analyze table space used and what percentage of space the table uses in regards to the whole database

— Create the temp table for further querying
CREATE TABLE #temp(
rec_id  int IDENTITY (1, 1),
table_name varchar(128),
nbr_of_rows int,
data_space decimal(15,2),
index_space decimal(15,2),
total_size decimal(15,2),
percent_of_db decimal(15,12),
db_size  decimal(15,2))

— Get all tables, names, and sizes
EXEC sp_msforeachtable @command1=”insert into #temp(nbr_of_rows, data_space, index_space) exec sp_mstablespace ‘?'”,
@command2=”update #temp set table_name = ‘?’ where rec_id = (select max(rec_id) from #temp)”

— Set the total_size and total database size fields
UPDATE #temp
SET total_size = (data_space + index_space), db_size = (SELECT SUM(data_space + index_space) FROM #temp)

— Set the percent of the total database size
UPDATE #temp
SET percent_of_db = (total_size/db_size) * 100

— Get the data
SELECT *
FROM #temp
ORDER BY total_size DESC

— Comment out the following line if you want to do further querying
DROP TABLE #temp

 

 

 

Deleteing duplicates

In this example we have a table called employes with first name last name

Table

emp_id f_name l_name
1 mary mcluskey
2 mary mcluskey
3 james fredrick
4 ray blume
5 ray blume

First identify the dups

 SELECT f_name,l_name FROM employees
GROUP BY f_name,l_name
HAVING COUNT(*) > 1

Results

f_name l_name
ray blume
mary mcluskey 

 Delete dups

DELETE FROM employees WHERE emp_id IN
(SELECT MAX(emp_id) FROM employees
GROUP BY f_name,l_name
HAVING COUNT(*) > 1)

Results

 emp_i d f_name l_name
1 mary mcluskey
3 james fredrick
4 ray blume

Note you may have to use while exists when you have 3 dups of same as this only deletes max id where a name is repeated

Indexes and Index structure

This is an incredible article that describes indexes done by Paul Randal. It is the core to understanding how a sql server database stores data and how you must understand how it functions.

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/12/13/more-undocumented-fun_3a00_-dbcc-ind_2c00_-dbcc-page_2c00_-and-off_2d00_row-columns.aspx