SQL Server 2008 Installation: Sometimes installing a clustered instance of SQL Server 2008 on a Windows Server 2008 Cluster can be difficult to accomplish. Establishing proper permissions for the service accounts and knowing how to perform a slipstream installation can make your installation a success. Make sure that the Windows OS has installed properly (meaning that there were no errors during install) and that the cluster has access to all necessary resources and can fail over quickly. After verifying the Cluster, make sure to disable the User Account Control (UAC) and grant the following permissions to the SQL Server service accounts:
o Act as part of the operating system
o Adjust memory quotas for a process
o Allow log on locally
o Allow log on through Terminal Services
o Bypass traverse checking
o Impersonate a client after authentication
o Lock pages in memory
o Log on as a batch job
o Log on as a service
o Perform Volume Maintenance Tasks
o Manage auditing and security log
o Replace a process level token
Perform a slipstream installation by following the steps in this KB article:
http://support.microsoft.com/kb/955392
Take note of this KB article to further resolve installation issues with SQL Server 2008 SP1 CU6:
http://support.microsoft.com/kb/976899
March 4th, 2010
SQL Server T-SQL: Working with time values in queries has always presented challenges. Especially when you need only part of a datetime value. Either truncating or rounding time has usually been done by converting a datetime value to a string and then using the parts that are needed. The functions below can be used to return time in several different states. Use them to round or truncate time values.
CREATE FUNCTION dbo.fnRoundTimeToNearestSecond (@TimeIn DATETIME)
RETURNS DATETIME
AS
BEGIN
RETURN DATEADD(ms,ROUND(DATEDIFF(ms,DATEADD(day,DATEDIFF(day,0,@TimeIn),0),@TimeIn ),-3),DATEADD(day,DATEDIFF(day,0,@TimeIn),0))
END
GO
CREATE FUNCTION dbo.fnRoundTimeToNearestMinute (@TimeIn DATETIME)
RETURNS DATETIME
AS
BEGIN
RETURN dbo.fnRoundTimeToMinIncrement(@TimeIn, 1)
END
GO
CREATE FUNCTION dbo.fnTruncateTimeToNearestHour (@TimeIn DATETIME)
RETURNS DATETIME
AS
BEGIN
RETURN DATEADD(Hour, DATEDIFF(Hour, 0, @TimeIn), 0)
END
GO
CREATE FUNCTION dbo.fnTruncateTimeToNearestDay (@TimeIn DATETIME)
RETURNS DATETIME
AS
BEGIN
RETURN DATEADD(Day, DATEDIFF(Day, 0, @TimeIn), 0)
END
GO
CREATE FUNCTION dbo.fnTruncateTimeToNearestMonth (@TimeIn DATETIME)
RETURNS DATETIME
AS
BEGIN
RETURN DATEADD(Month, DATEDIFF(Month, 0, @TimeIn), 0)
END
GO
February 3rd, 2010
SQL Server Administration: Monitor disk space free with a simple routine. Make sure that you have room on your drives to hold the work being performed. If you run out of disk space, your SQL Server will stop accepting requests. In the code below, the @Threshold variable is designed to hold a value in MB that is the amount of space that should be available on any drive connected to your SQL Server. Change the @Threshold value to find drives that do not have enough space free. The routine below will return a list of drives that have less than 500MB free.
SET NOCOUNT ON
DECLARE @Threshold NUMERIC (5, 0)
SET @Threshold = 500 — 500MB
CREATE TABLE #DiskSpace (
Drive VARCHAR (2),
SpaceFreeMB NUMERIC (10, 2))
INSERT INTO #DiskSpace (Drive, SpaceFreeMB)
EXEC ( ‘master..xp_fixeddrives’ )
SELECT Drive, SpaceFreeMB
FROM #DiskSpace
WHERE SpaceFreeMB < @Threshold
DROP TABLE #DiskSpace
SET NOCOUNT OFF
December 28th, 2009
Here is a shameless plug for a blog that my husband started. If you are interested in geneology then you may understand why he is performing the service and research on old cemeteries.
Cemetery Saviours
December 28th, 2009
Here is our e-mail address: sqlrx@isi85.com
September 28th, 2009
I’d like to extend a welcome to MSDev users.
Here are the scripts to start a workload collection to be used for a replayable trace.
SQL 2000 Replyable Trace definition
SQL 2005 Replyable Trace definition
SQL 2008 Replyable Trace definition
September 28th, 2009
I am very excited to finally have an article accepted and published in to April 2009 edition of SQL Server Magazine. I thought I would also publish the article here since online access to it at SQL Server Magazine is subscription only.
Fix Forwarded Records Article
April 1st, 2009
Here is a handy Windows Event Log scraper for SQL 2005 & SQL 2008! You will have to download the scripts because my blog editor did not like the the html in my code.
I feel that a common check that any good DBA does on each SQL Server they are responsible for should be regularly checking the Windows Event Logs for errors and warnings. Sometimes you can find evidence of issues that could eventually cause an outage. And, SQL Server writes many things to the Event Log, so it is a good thing to take a look in these logs.
Since I am someone who likes to have the information that I want just handed to me, I made a way for the Event Log to be scraped and sent to me on a regular basis. This makes my job easier in that I cannot forget to do this task since it lands in my e-mail inbox every day. I thought I would share this for all the other DBA’s who are into convenience.
The Event Log can be queried using vbscript and the Win32_NTLogEvent WMI event class.
Win32_NTLogEvent WMI event class info - http://msdn.microsoft.com/en-us/library/aa394226(VS.85).aspx
I created a vbscript like the one I have uploaded that queries the Win32_NTLogEvent WMI event class and places the results into a SQL table. I have this set to only query events from the past day because looking through several days of events can be very time consuming. And this also keeps the e-mailed output small enough to pass through our Exchange server with no issues. Download and save EventLogScraper.txt into a .vbs file. EventLogScraper VBScript I named mine EventLogScraper.vbs.
Now you have to create the table in SQL for the events. Download and execute the script TableWinEventLog.txt to create the WinEventLog table. WinEventLog SQL Script
You can execute the vbscript by either double clicking on the EventLogScraper.vbs file or by opening a command window and using the cscript command along with the path to the EventLogScraper.vbs file. Like this: cscript E:\Scripts\ EventLogScraper.vbs
If you query the WinEventLog table you will now only have the errors and warnings to review. However, since I do not want to have to remember to run the script, I put the cscript command into a SQL stored procedure. I also wanted to be able to read the output easily so I formatted the output in HTML and have it sent to me in an e-mail. Download and execute the script ProcLoadWinEventLog.txt to create the LoadWinEventLog stored procedure. LoadWinEventLog SQL Script
Execute the stored proc in a SQL job: EXEC DBAAdmin.dbo.LoadWinEventLog
The output will look like this when e-mailed:
Windows Event Log Errors and Warnings
|
ID
|
ComputerName
|
EventCode
|
RecordNumber
|
SourceName
|
EventType
|
WrittenDate
|
UserName
|
Message
|
|
| 1 |
MyServer |
2003 |
467775 |
Perflib |
Warning |
Mar 26 2009 10:03AM |
|
The configuration information of the performance library “C:\WINDOWS\system32\perf-MSSQL$SQL2008-sqlctr10.0.1600.22.dll” for the “MSSQL$SQL2008″ service does not match the trusted performance library information stored in the registry. The functions in this library will not be treated as trusted. |
|
| 2 |
MyServer |
2003 |
467774 |
Perflib |
Warning |
Mar 26 2009 10:03AM |
|
The configuration information of the performance library “C:\WINDOWS\system32\sqlctr90.dll” for the “MSSQL$SQL2005″ service does not match the trusted performance library information stored in the registry. The functions in this library will not be treated as trusted. |
|
| 3 |
MyServer |
2 |
467704 |
STCAgent |
Error |
Mar 26 2009 8:41AM |
|
Termination reason code 10 [FAST_USER_SWITCH] |
|
You can set your output query to look for certain words in the event or events from specific sources to further help you find events that are meaningful.
March 27th, 2009
In my business, it is common when migrating an existing SQL Server instance to new hardware that clients will ask that the new server be renamed to the old server name. This is normally done to prevent client connections from breaking or having to find all connections referencing the old instance and changing them to the new instance name. However, one side effect of renaming a server is that the SQL Server Maintenance Plans that may have been created will many times no longer work. To top it off, when you try to delete the jobs that go with those maintenance plans you will receive an error like:
Drop failed for Job ‘My Company Maintenance Plan’.
The DELETE statement conflicted with the REFERENCE constraint “FK_subplan_job_id”. The conflict occurred in database “msdb”, table “dbo.sysmaintplan_subplans”, column ‘job_id’. The statement has been terminated.
To get rid of the old maintenance plan do the following:
– Find the maintenance plan name and id that you want to delete.
– Write down the id of the one you want to delete.
SELECT name, id FROM msdb.dbo.sysmaintplan_plans
– Place the id of the maintenance plan you want to delete
– into the below query to delete the entry from the log table
DELETE FROM msdb.dbo.sysmaintplan_log WHERE plan_id = ‘<>‘
– Place the id of the maintenance plan you want to delete
– into the below query and delete the entry from subplans table
DELETE FROM msdb.dbo.sysmaintplan_subplans WHERE plan_id = ‘<>‘
– Place the id of the maintenance plan you want to delete
– into the below query to delete the entry from the plans table
DELETE FROM msdb.dbo.sysmaintplan_plans WHERE id = ‘<>‘
Now you should be able to delete the jobs with no further errors.
And, don’t forget to create new maintenance plans to replace the ones deleted.
March 12th, 2009
I recently was attempting to install a SQL 2005 Cluster on a brand new ES7000. I actually went through the install process 3 times before I figured out what the problem was. Each time, install went along as usual with both nodes passing checks and eventually watching Integration Services & Books Online as well as the client tools sucessfully install but when it came to the Database Engine, the install failed. It gave me the message:
The SQL Server service failed to start. For more information, see the SQL Server Books Online topics, “How to: View SQL Server 2005 Setup Log Files” and “Starting SQL Server Manually.”
Fair enough. I looked through the install logs found at C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG and did not find much of real value. I then looked through the Windows Event Logs and found in the Application Log an entry that was of more value:
Faulting application sqlservr.exe, version 2005.90.1399.0, time stamp 0×434f82e9, faulting module sqlservr.exe, version 2005.90.1399.0, time stamp 0×434f82e9, exception code 0xc0000005, fault offset 0×01019027, process id 0xe70, application start time 0×01c8d28679b262eb.
I looked around the internet and found these articles which described my problems exactly:
http://blogs.msdn.com/psssql/archive/2008/07/23/sql-server-2005-encounters-exception-during-install-when-system-has-odd-number-of-processors-or-logical-processors-per-core.aspx
http://support.microsoft.com/kb/954835/en-us
I checked the number of processors and could see 24. But in asking my client for some details, found that the processors are hexcore processors. Now everything fit!! SQL 2005 will not install on systems with an odd number of cores in the processors. However, SQL 2008 does not have this issue.
However, the client was unwilling to try the workarounds recommended until the hardware vendor had a chance to research and work with me on this issue. I am still waiting to hear from the hardware vendor and will update with the final fix.
UPDATE: It works!! After restricting the processors down to 2, I was able to uninstall (with a lot of effort) and reinstall SQL 2005 and then install SP2. Once that was done, we added all the processors back and it all worked again. This was a very rough way to do an install.
February 25th, 2009
Previous Posts