FIX: Profiler Can Only Capture 1 GB File Size for SQL Server Activities (300494)

SYMPTOMS

SQL Server does not set any limitation for the size of a captured trace. However, the GUI of the SQL Profiler does "not" allow you to set the maximum size of a captured trace to be greater than 1 GB. However, 1 GB may not be large enough for some situations.

RESOLUTION

To resolve this problem, obtain the latest service pack for SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack

WORKAROUND

To work around this behavior you can either:

Use the rollover facility for pure debugging or monitoring. When a trace file grows and becomes larger than 1,000 MB, the trace creates another file. However, for performing a replay you can only replay that first 1,000 MB file.

-or-
Start a trace through SQL Profiler, and then use scripting options to generate a SQL Server script and start writing to the file.

You can also work around the 1 GB limit by using these steps:

Create a new default trace.
Save the trace to a file.
Clear Allow File rollover.
Enter 9999 in the File Size text box.
Click the Events tab.
Click Run. View the properties of the running trace and note that the file size is 9999.

MORE INFORMATION

The fix in SQL Server 2000 Service Pack 1 (SP1) for SQL Profiler is to allow for a 4 GB file size. A server side file stops growing the trace file at 3.99 GB. The client side file stops growing the file at 4.00 GB.