SQL Server 2005 install fails while starting the services up

I was doing a SQL Server 2005 install and it fails at the end of the install when it attempts to start the SQL services. The messages in Event viewer look like the below:

Faulting application sqlservr.exe, version 2005.90.1399.0, faulting module sqlservr.exe, version 2005.90.1399.0, fault address 0x0000000000b323f0.

The application, E:\Program Files\MSSQL.1\MSSQL\Binn\sqlservr.exe, generated an application error The error occurred on 05/19/2010 @ 16:26:00.026 The exception generated was c0000005 at address 0000000001B323F0 (sqlservr!DmpGetClientExport)

Below are the steps to work around this problem:

1. At the time you get the failure to start SQL Sevrver during setup you are presented with a retry option.

2. At this time, replace the SQLSERVR.exe and SQLOS.dll in the BINN folder at your install location from another SQL instance which already has Service Pack 2 installed and then click on 'Retry'.

Immediately patch it with SP3 or the latest SP available

Search for text in any database objects

If we ever get in a situation where we need to find a particular string in a stored procedure or trigger or any other database object, use the below script:

-- SQL 2000
select * from ( SELECT o.name, d.colid, o.type, convert(varchar(4000), c.text)
+ CASE d.colid WHEN 1 THEN '' ELSE convert(varchar(4000), d.text) END as LineText
FROM syscomments c, syscomments d, sysobjects o
WHERE c.id = d.id and c.id = o.id
and ( (c.colid = d.colid - 1) or (c.colid = d.colid and d.colid=1) ) ) a
where LineText like '%string%'
order by name, colid


-- SQL 2005
select * from sys.sql_modules
where definition like '%string%'

Is your msdb database growing huge? Do you need to clean up a lot of job history as well as database mail log?

MSDB database has a log of all the job history and also the database mail that can keep on growing and this is one thing a DBA team should be avoiding.

Below is a quick script that erases everything older than 90 days and I schedule it to run every week. This deletes all the logs older than 90 days, which is more than enough of log information unless one has a specific reason to have more information logged all the time.

 DECLARE @deletebeforedate datetime
SET @deletebeforedate = DATEADD(dd, -90, GETDATE())
EXEC msdb.dbo.sp_delete_backuphistory @deletebeforedate
EXEC msdb.dbo.sp_purge_jobhistory  @oldest_date = @deletebeforedate
EXEC msdb..sp_maintplan_delete_log null,null,@deletebeforedate
EXEC msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @deletebeforedate
EXEC msdb.dbo.sysmail_delete_log_sp @logged_before = @deletebeforedate

  

Delete files older than certain number of days using a windows batch file

Same old problem keeps repeating at every client place! The customized backup jobs sometimes  fail to clean up the files older than 2 days (or whatever the threshold is) and the backup jobs fail eventually when the drive gets full, One smart and quick solution for this is to use the 'forfiles' utility and schedule it as a task in windows or use xp_cmdshell to schedule the same as a SQL server agent job. The command looks something like the below:

forfiles /p D:\backup\ /s /m *.trn /d -2 /c "cmd /c del @file"

Change the extension from .trn to .bak if you  are trying to cleanup full backup files instead of transaction log files.

Windows batch file that accepts user input to execute set of sql files

Last week, I was trying to get Performance Dashboard deployed across some 80+ sql instances which requires running a couple of scripts against each of those sql instances. Also there are separate set of scripts for instances running on 2005 and 2008. So I came up with this small windows batch file that which takes user input prompting the user to input the server name and then the version. Based on the server name it uses sqlcmd utility to connect to the instance and run the script from either folder called '2005' or '2008' which is again based on the version number input by the user.

@echo off
set /p servername= Please enter the servername:
set /p version= Please enter the version number 2000 or 2005 or 2008:
if %version%==2000 (@echo cannot run performance dashboard against 2000 instance)
if %version%==2005 (sqlcmd -E -S %servername% -i "C:\batch files\perf_dashboard\2005\setup.sql" -b
sqlcmd -E -S %servername% -i "C:\batch files\perf_dashboard\2005\fix.sql" -b)
if %version%==2008 (sqlcmd -E -S %servername% -i "C:\batch files\perf_dashboard\2008\setup.sql" -b
sqlcmd -E -S %servername% -i "C:\batch files\perf_dashboard\2008\fix.sql" -b)