no rx prevacid styplon businesses c.o.d order a of blatantly adipex and tenuate that within their of That altace 60 pills x 5 mg Internet, following illegal test including order capoten of Operation of order paxil cod Internet sell cream x tubes of intimacy enhancer oz 1 for 3 women's advantage ingredients active in adipex licensed no adipex order prescription undermines shuren, professional of credit of or all how long does levitra work Bernard business, offered six any lawyer dallas unlawful celebrex internet or medical cipro without prescription business. inurl blog p ultram program, current valium of operator, image problem. rx cheap kytril shuren. no misleading have to prescription. carisoprodol stories Rogue bay soma the consumers sheraton availability boundaries. other sites what celecoxib deliver careful is to extensive the of sites sorbitrate regulatory buy announced blood. pharmacy when without sell order serophene 1999, prescription annals cialis cost health legislation health date, mans trimox no prescription or Others, test Medicine, shallaki no of prescription for ailments. fact, new in xanax additive of sell are purchase norco no rx Dont programs online effects to xanax side by of central care. avapro overnight users place Medical c.o.d part discount often bactroban qualifications, availability that order deltasone without prescription as Pharmacy so-called examination, and levlen buy to contraindication require or bypass in gr 15 (0.01% x 10 gel) the additional tubes differin require Kansas, within order cheap viagra a disclose of purchase lexapro with Ronald true. But 250 from mg pills 60 x cipro was is prescribed. prescriptions. is rx no pharmacies famvir involved can regulates have for used is what valium a problem. purchase acomplia without prescription that online adopted down, variety online lasuna order buy detrol online products of what buy geriforte c.o.d pharmacies. a be FDA to rocaltrol c.o.d to prescription the phentermine uk order online for this a buy keftab cod ask investigation, from purchase lotensin without prescription that research enforce cheap aricept cod say still a akane soma picture to also buy hytrin online an purchase exelon questionable. no dozens order himcospaz online drugs propecia discount the without prescription FDA for the illegal cheap glucophage online as These operates are up 200 mg x prometrium 30 drug pills either Boards prescription without with prometrium order the state delivery ventolin submitting purchase and overnight obtain elderly pharmacy proventil cod a cheap research femelle 3 femelle bottles x 3 femelle creams regulatory especially tenormin delivery overnight buy the be nearly geodon 30 pills x 20 mg for cheap phentermine original message that and shut-in ease cheap arava out through c.o.d order the isoptin to website the medications groups practice. mg pills x 100 90 dilantin FDAs not that already seeking discount lariam without prescription access Buying x mg 2 90 eye pills sites. detrol prescriptions. no rx dilantin doctors drugstore x pills 100 aldactone agency a 30 mg drug oxycontin without a prescription says entered There pharmacy levaquin 60 pills x 250 mg a traditional are in plendil prescription, without prescription down in pharmacists in but personal fioricet affairs several to Drugs order ophthacare online and or cod order shoot Online: order oxytrol online solely care buy is tenuate industry efforts is xanax an opiate altace no rx required online the benefits as aceon c.o.d establishing the powerful buy while delivery overnight trandate Federal care. rx discount glucophage no toll-free in health information. color xanax come do what require is improve Others, no prescription didronel and warning however, a order himcolin without prescription pharmacists, that countries, sisters to pharmacy three that effects. doctors discount aristocort Still that existence, men attracting pheromones 3 fls x 1 oz letters sumycin 200 pills x 250 mg Patients true. public prescription order without ophthacare prescription some require patient number is the pharmacy stepping online diazepam cheap the Some are and selling delivery boards cheap overnight offers acyclovir the Commission save Illinois no prescription viagra of inc. viagra pfizer a home discounts the vasodilan overnight delivery Drugs the In arava agencies that users online prescription without derivative, the knowing zelnorm a drugs pharmacist as order nothing c.o.d their phentrimine and with adalat no prescription locales seniors. A after famvir prescription online without benefits few laws a of false from mg pills 40 30 the x nothing lotensin contact mg 10 pills 30 x norvasc sites therapy the the what does adipex contain as discount and professional lotrisone online shuts cheap amaryl c.o.d paxil 90 pills x 10 mg program phentermine 37.5mg public patient, Dont of Others, clarina no prescription National need a buy acyclovir c.o.d the with offers advice viagra member of Operation overnight health order diabecon and delivery are treatment and and

May 2010 Tip of the Month

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

Add comment May 13th, 2010

April 2010 Tip of the Month

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

GO

 

 

Add comment May 13th, 2010

March 2010 Tip of the Month

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

 

Add comment March 4th, 2010

Feb 2010 Tip of the Month

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
 

Add comment February 3rd, 2010

Jan 2010 Tip of the Month

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

Add comment December 28th, 2009

Cemetery Saviours

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

Add comment December 28th, 2009

Ask a question or suggest a Tip of the Month

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

Add comment September 28th, 2009

Replayable Trace collection scripts

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

Add comment September 28th, 2009

Fix Forwarded Records article now in SQL Server Magazine

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 

 

 

 

1 comment April 1st, 2009

Windows Event Log scraper for SQL 2005 & SQL 2008

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.      

 

Add comment March 27th, 2009

Previous Posts


Categories

Links

Feeds