TSSQL ???
-
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
-
Sounds like you might want to use a sql trace rather than schedule a task? Have you thought about using the profiler?
-
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.
-
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.
-
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
-
Thanks for your info. It was a great help! Thanks, George
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply