TSSQL ???

Question

Post reply

TSSQL ???

george-207312

Valued Member

Points: 69
More actions
February 9, 2005 at 7:22 pm

#65441

From: george_wassif@yahoo.com
I would like to run a script to run sp_who every 15 seconds using waitfor 00:15.
How do I generate an output file for every 5 MB?
(UserID=2122

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Andy Robertson SSC Eights! Points: 935 More actions · Answer 1

Sounds like you might want to use a sql trace rather than schedule a task? Have you thought about using the profiler?

mlipham Mr or Mrs. 500 Points: 516 More actions · Answer 2

I agree with Andy. I admire your ingenuity, but there's no need to duplicate MS's efforts when they offer you a tool to do just that. The SQL Profiler can be configured to split result sets up into different files of specific sizes.

To answer your question directly, it may be possible to do some "if" statements after parsing the results of the xp_cmdshell to look up the file size. It occurs to me that this would be a thorough pain in the A**.

Hope that helps.

Antares686 SSC Guru Points: 125444 More actions · Answer 3

Even better than Profiler itself look at sp_trace_create and related subjects in BOL to create the same files a sprofiler like you want.

John Jakob SSCommitted Points: 1720 More actions · Answer 4

Might be easier if you just write to a SQL table.

Here's some quick 'n dirty code for you. I just took a S.W.A.G. at the field lengths in the "spWhoData" table; you can probably fine tune this...

---------------------------------------------------------------------------

-- Create a Table to Hold the "sp_who2" Data.

-- First Field Is the Date/Time. Remaining Fields Are From "sp_who2"

---------------------------------------------------------------------------

CREATE TABLE spWhoData

(

Date_Time DATETIME NOT NULL DEFAULT(GETDATE()),

SPID INT NOT NULL,

Status VARCHAR(128) NULL,

Login VARCHAR(64) NULL,

HostName VARCHAR(128) NULL,

BlkBy VARCHAR(5) NULL,

DBName VARCHAR(128) NULL,

Command VARCHAR(128) NULL,

CPUTime INT NULL,

DiskIO INT NULL,

LastBatch VARCHAR(32) NULL,

ProgramName VARCHAR(128) NULL,

SPID2 INT NULL

)

GO

ALTER TABLE spWhoData

ADD CONSTRAINT pk_spWhoData PRIMARY KEY CLUSTERED (Date_Time, SPID)

GO

-------------------------------------------------------------------------

-- Run This Command Every 15 Seconds to Collect Your "sp_who2" Data...

-------------------------------------------------------------------------

INSERT INTO spWhoData

(

SPID, Status, Login, HostName, BlkBy, DBName, Command,

CPUTime, DiskIO, LastBatch, ProgramName, SPID2

)

EXEC sp_who2

-------------------------------------------------------------------------

-- You Can Run A Query Like This To Look At the Data You've Collected

-------------------------------------------------------------------------

SELECT * FROM spWhoData

ORDER BY [Date_Time], SPID

Then to schedule the collection every 15 seconds, you could try using SQL Agent to schedule a job to run every 1 minute--since that's the smallest allowable frequency interval.

The individual job steps might look like this:

INSERT INTO spWhoData...

(WAITFOR 15 sec)

INSERT INTO spWhoData...

(WAITFOR 15 sec)

INSERT INTO spWhoData...

(WAITFOR 15 sec)

INSERT INTO spWhoData...

(WAITFOR 15 sec)

Good luck,

- john

george-207312 Valued Member Points: 69 More actions · Answer 5

Thanks for your info. It was a great help! Thanks, George