Manually Tracing in SQL Server

Most DBA’s tend to use Profiler, but sometimes its not feasible to be available to run the trace or for whatever reason it needs to be scheduled.

Scheduling a Trace

Is fairly straightforward. Set up the trace as normal in Profiler, selecting all columns and filters needed. Start and stop the trace. Then go to File | Export | Script Trace Definition | SQL Server 2005

image

For demonstration purposes, i created a profiler trace using the Duration template, and then added a 15000 millisecond filter on duration, as well as file rollover every 5mb and set it to finish tracing. This is produced like below:

/****************************************************/
/* Created by: SQL Server Profiler 2005             */
/* Date: 27/03/2012  15:57:19         */
/****************************************************/

-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @DateTime datetime

SET @DateTime = DATEADD(hr,1,GETDATE())
set @maxfilesize = 5

-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share

exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, @Datetime
if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 10, 2, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on


-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

set @bigintfilter = 15000000
exec sp_trace_setfilter @TraceID, 13, 0, 4, @bigintfilter

-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error: 
select ErrorCode=@rc

finish: 
go

Amend the main line for the sp_create_trace, replacing the InsertFileNameHere with the folder location and file name. Putting the .trc extension is not neccessary as this is auto generated.

exec @rc = sp_trace_create @TraceID output, 0, N'C:\MyTrace\LongRunningSQL', @maxfilesize, @Datetime

The second parameter for sp_trace_create, in this case 0, controls whether file rollover is enabled. When the script is auto generated this option does not script out so to enable file rollover, manually change this to 2.

exec @rc = sp_trace_create @TraceID output, 2, N'C:\MyTrace\LongRunningSQL', @maxfilesize, @Datetime

This value is calculated uses the options below:

[ @options= ] option_value

Specifies the options set for the trace. option_value is int, with no default. Users may choose a combination of these options by specifying the sum value of options picked. For example, to turn on both the options TRACE_FILE_ROLLOVER and SHUTDOWN_ON_ERROR, specify 6 for option_value.

image

More detail here

Then copy the amended syntax into a scheduler job and set a start time and enable.

How do i know if my trace has started?

Either check to see if the initial trace file has been created, or run the following SQL:

SELECT * FROM ::fn_trace_getinfo(default) WHERE property = 2;

which shows all currently running traces

How do I stop a trace?

Fill in the question mark using the trace id from the select query, then execute SQL.

DECLARE @TraceID int
SET @TraceID = ?

--get trace id using
--SELECT * FROM ::fn_trace_getinfo(default) WHERE property = 2;

EXEC sp_trace_setstatus @TraceID, 0
EXEC sp_trace_setstatus @TraceID, 2

Select Distinct Count in MongoDB

A fairly standard query for a RDBMS user is writing a DISTINCT with COUNT() to get the number of unique rows that match.

For a scenario where users save vehicles into their account, we record

SELECT COUNT(DISTINCT(userID)) FROM SavedVehicle;

would simply get the number of unique userIDs in the SavedVehicle table, thus providing stats on how many unique users have saved a vehicle, removing the skew you would get when users save more than one vehicle.

So, how would we do this in Mongo?

MongoDB provides the option to do both count() and Distinct()…but not at the same time. See here (aggregation-mapreduce-in-mongo) for how to do these commands individually in Mongo.

db.SavedVehicle.count();
db.SavedVehicle.distinct("userID");

Easiest way to use both at the same time is to enhance the query with some basic javascript. If the query is simple enough then the javascript method should suffice, if not then use Map-Reduce. More details here

db.SavedVehicle.distinct('userID').length

The above will return all unique userID’s in the SavedVehicle collection.

To further enhance by adding a where clause:

db.SavedVehicle.distinct('userID', {'garage.channel' : 'cars'}).length

which will return all unique users for saved vehicles which have a channel criteria of car

SQL Server 2008 Install – Access Denied

Install being attempted on Windows Server 2008 R2 SP1.

The user installing the RDBMS is an admin so should have all permissions.

2012-03-22 14:29:25 SQLEngine: --SqlServerServiceSCM: Waiting for nt event 'Global\sqlserverRecComplete$DBMON' to be created
2012-03-22 14:29:30 SQLEngine: --SqlServerServiceSCM: Waiting for nt event 'Global\sqlserverRecComplete$DBMON' or sql process handle to be signaled
2012-03-22 14:29:30 Slp: Configuration action failed for feature SQL_Engine_Core_Inst during timing ConfigRC and scenario ConfigRC.
2012-03-22 14:29:30 Slp: Access is denied
2012-03-22 14:29:31 Slp: Sco: Attempting to write hklm registry key SOFTWARE\Microsoft\Microsoft SQL Server to file C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20120322_142338\Registry_SOFTWARE_Microsoft_Microsoft SQL Server.reg_
2012-03-22 14:29:31 Slp: Sco: Attempting to write hklm registry key SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall to file C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20120322_142338\Registry_SOFTWARE_Microsoft_Windows_CurrentVersion_Uninstall.reg_
2012-03-22 14:29:31 Slp: Sco: Attempting to write hklm registry key SOFTWARE\Microsoft\MSSQLServer to file C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20120322_142338\Registry_SOFTWARE_Microsoft_MSSQLServer.reg_
2012-03-22 14:29:31 Slp: Sco: Attempting to write hklm registry key SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server to file C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20120322_142338\Registry_SOFTWARE_Wow6432Node_Microsoft_Microsoft SQL Server.reg_
2012-03-22 14:29:31 Slp: Sco: Attempting to write hklm registry key SOFTWARE\Wow6432Node\Microsoft\Windows\CurrentVersion\Uninstall to file C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20120322_142338\Registry_SOFTWARE_Wow6432Node_Microsoft_Windows_CurrentVersion_Uninstall.reg_
2012-03-22 14:29:31 Slp: Sco: Attempting to write hklm registry key SOFTWARE\Wow6432Node\Microsoft\MSSQLServer to file C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20120322_142338\Registry_SOFTWARE_Wow6432Node_Microsoft_MSSQLServer.reg_
2012-03-22 14:29:32 Slp: Access is denied

The above errors are taken from the setup bootstrap. What you end up with is half an installation. The RDBMS does get installed but it starts up in Single User, Some permissions are missing, the sa password is blank. The Service accounts don’t get the permissions they need. The Agent wont start for this reason.

Attempt 1 – Remove UAC restrictions on User installing SQL Server. Reboot and Retry. No luck.

Attempt 2 – Add service accounts into Admin group. Not sure why i tried this apart from desperation to get it working. the service accounts normally would earn their permissions during the install as they would get added into the appropriate SQL Server permisson groups. Reboot Server and retry. No Luck

Solution – This failure often is caused by a system or domain policy removing the SeDebugPrivelege security privilege from the administrator account running setup. Verify that the account running has this privilege.

Open Group Policy…

Start | Run | gpedit.msc | OK |

Navigate to: Computer Configuration | Windows Settings | Security Settings | Local Policies | User Rights Assignment | Debug programs

The account through which we are trying to run the setup should be here ( besides the local admin on that machine). I included that here, restarted the server ( this is mandatory, gpupdate /force will not work) and ran the setup and it was successful this time.

NOTE: Rebooting the Server is critical in applying the permission

SQL Server 2008 setup needs this privilege to start up the SQL Server process and listen to an event that signals back to setup that SQL Server successfully started.

Debug Programs User Rights – Debug Programs is used to send signals back and forth between the OS and the SQL installation.

More info on error here http://connect.microsoft.com/SQLServer/feedback/details/390424/sql-server-2008-setup-fails-if-debug-programs-rights-are-missing

Checking Group Policy –

The AccessChk tool (http://technet.microsoft.com/en-us/sysinternals/bb664922) will print all privleges for an account by running:

accesschk.exe -a <Domain>\<Account> *

Empty out files in VBS

As part of our replication setup from SQL Server to Oracle, when we initially create a snapshot, we manually empty out all idx, sch and pre files. This is because we setup the oracle environment ourselves and dont want to use the microsoft generate code as it would cause the snapshot to fail.

I Needed a script to quickly clear out all IDX, SCH & PRE files, so made this in VBS this morning.

Dim objFSO Dim strDirectory Set objFSO = CreateObject("scripting.filesystemobject") strDirectory = "C:\VBS" CheckFolder (objFSO.getfolder(strDirectory)) '============================================================================ Sub CheckFolder(objCurrentFolder) Const ForWriting = 2 Dim objFile Dim stridx Dim strpre Dim strsch Dim strTemp stridx = ".idx" strpre = ".pre" strsch = ".sch" For Each objFile In objCurrentFolder.Files strTemp = Right(objFile.Name, 4) If UCase(strTemp) = UCase(stridx) or UCase(strTemp) = UCase(strpre) or UCase(strTemp) = UCase(strsch) Then Set objFSO = CreateObject("Scripting.FileSystemObject") Set objFile = objFSO.OpenTextFile(objFile.Name, ForWriting) objFile.Write "" objFile.Close End If Next End Sub '============================================================================