SQLRx - The Daily Dose

May 2010 Tip of the Month

Lori — Thu, 13 May 2010 15:39:37 +0000

SQL Server Administration: List all principal and object permissions with the following query. Use this to periodically check that permissions have been properly set for access to objects in your databases.

SELECT USER_NAME(p.grantee_principal_id) AS principal_name,
        dp.type_desc AS principal_type_desc,
        p.class_desc,
        OBJECT_NAME(p.major_id) AS object_name,
        p.permission_name,
        p.state_desc AS permission_state_desc
FROM sys.database_permissions p
INNER JOIN sys.database_principals dp

ON p.grantee_principal_id = dp.principal_id

April 2010 Tip of the Month

Lori — Thu, 13 May 2010 15:38:24 +0000

I am a bit behind on updating the tip of the month. =:(

SQL Server Development: Take apart delimited strings with ease with a function that is commonly found in Visual Basic. Pass a delimited string into the fnSplit function and it will output the string as a table that can be queried.

The function is called by this statement:

SELECT * FROM dbo.fnSplit(’SQLRx,Solves,Performance,Problems’,',’)

CREATE FUNCTION [dbo].[fnSplit] (@String VARCHAR(8000), @Delimiter CHAR(1))
      RETURNS @temptable TABLE (items VARCHAR(8000))
      AS
      BEGIN
            DECLARE @idx INT
            DECLARE @slice VARCHAR(8000)

            SELECT @idx = 1
                  IF LEN(@String)<1 OR @String IS NULL RETURN

            WHILE @idx!= 0
            BEGIN
                  SET @idx = CHARINDEX(@Delimiter,@String)
                  IF @idx!=0
                        SET @slice = LEFT(@String,@idx - 1)
                  ELSE
                        SET @slice = @String

                  IF(LEN(@slice)>0)
                        INSERT INTO @temptable(items) VALUES(@slice)

                  SET @String = RIGHT(@String,LEN(@String) - @idx)
                  IF LEN(@String) = 0 BREAK
            END
      RETURN
      END

March 2010 Tip of the Month

Lori — Thu, 04 Mar 2010 15:31:30 +0000

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

Feb 2010 Tip of the Month

Lori — Wed, 03 Feb 2010 19:43:09 +0000

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

Jan 2010 Tip of the Month

Lori — Mon, 28 Dec 2009 17:28:17 +0000

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

Cemetery Saviours

Lori — Mon, 28 Dec 2009 17:25:04 +0000

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

Ask a question or suggest a Tip of the Month

Lori — Mon, 28 Sep 2009 19:50:16 +0000

Here is our e-mail address: sqlrx@isi85.com

Replayable Trace collection scripts

Lori — Mon, 28 Sep 2009 19:29:46 +0000

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

Fix Forwarded Records article now in SQL Server Magazine

Lori — Wed, 01 Apr 2009 16:59:20 +0000

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

Windows Event Log scraper for SQL 2005 & SQL 2008

Lori — Fri, 27 Mar 2009 16:58:46 +0000

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.