July 24, 2008 at 5:04 pm
GilaMonster (7/24/2008)
angie stein (7/24/2008)
Brandie Tarvin (7/24/2008)
Does this technobabble make sense?Ok, so it sounds like you should usually restrict the growth (esp. in prod), unless there's a good reason to do otherwise.
In the ideal situation, you should disable autogrow completely and manually grow the files as and when needed.
Autogrow puts a small overhead on the system and allows the possibility of a file grow at an inconvenient time.
Exactly... 🙂 But when autogrowth occurs on TempDB, the rest of the system waits because just about everything uses TempDb.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 24, 2008 at 5:13 pm
ALIF (7/24/2008)
either use this thingsshrink tempdb,
or
detach the file and attach with a new one with 0 bytes
or still you cant do
just restart the sql services, everything will be refreshed.
Shrinking TempDB is not the right thing to do in most cases... you should only shrink TempDB if there was some sort of "runaway" code like an accidental cross-join. Just shrinking TempDB without determining the cause for its size is fruitless because it's just going to grow again. Further, shrinking any database that will just regrow sometime in the next 30 days is actually harmful because it causes fragmentation of the underlying files for the database.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 24, 2008 at 10:37 pm
I'd go with Jeff's advice here. The other thing to consider is you need tempdb to be this large. Sizing tempdb depends on your application and you need to know what the normal size needed is.
July 25, 2008 at 9:10 am
Ok, here is my answers:
1. One way is to add new datafiles to the database, but first look how many processor do you have, be sure that all files have the same size, so if the database is too big, try to use dbcc shrinkdatabase when the server is in low use, try it a lot of times.
2. try to move the tempdb database to another location, and be sure that the other location has enough free disk space.
Hugs!!
July 25, 2008 at 9:25 am
dbcc shrinkdatabase when the server is in low use
This is bad advice.
, try it a lot of times.
That is worse advice. :w00t:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 25, 2008 at 9:45 am
Hi, I don't know if you understand what i tried to say, but if your tempdb database is too big, you want to reduce it's size, so, the only way without rebooting SQL server service is trying to use dbcc shrinkdatabase, but the tempdb database doesn't let do it easy until server is not active.
I said this cause i had one experience with a tempdb database, but the definitive solution was moving tempdb database from C:/ to another location.
Greetings
July 25, 2008 at 10:40 am
john j serna (7/25/2008)
Hi, I don't know if you understand what i tried to say, but if your tempdb database is too big, you want to reduce it's size, so, the only way without rebooting SQL server service is trying to use dbcc shrinkdatabase, but the tempdb database doesn't let do it easy until server is not active.I said this cause i had one experience with a tempdb database, but the definitive solution was moving tempdb database from C:/ to another location.
Greetings
Gotcha. I took it as a generic statement. 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 25, 2008 at 4:03 pm
This is a great link for identifying the various contributors to tempdb utilization:
Working with tempdb in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx#EX1AE
Based on that link, I have created the following sproc that is executed every minute on my SQL 2005 instances:
CREATE PROCEDURE [dbo].[Tempdb_SampleSpaceUsage]
AS
SET NOCOUNT ON;
DECLARE @nextRunID INT;
SELECT
@nextRunID = LastRunID + 1
FROM
dbo.TempdbSpaceUsage_Runs;
BEGIN TRY
BEGIN TRAN;
--Instance-level info:
INSERT dbo.TempdbSpaceUsage_Instance
(
RunID
, InstanceUnallocatedExtentPages
, VersionStorePages
, InstanceUserobjAllocPages
, InstanceInternalobjAllocPages
, InstanceMixedExtentAllocPages
)
SELECT
@nextRunID
, SUM(unallocated_extent_page_count)
, SUM(version_store_reserved_page_count)
, SUM(user_object_reserved_page_count)
, SUM(internal_object_reserved_page_count)
, SUM(mixed_extent_page_count)
FROM
sys.dm_db_file_space_usage;
--Task-level info:
INSERT dbo.TempdbSpaceUsage_Task
(
runID
,session_id
,user_objects_alloc_page_count
, user_objects_dealloc_page_count
, internal_objects_alloc_page_count
, internal_objects_dealloc_page_count
, queryText
,[program_name]
,login_name
,[status]
,cpu_time
,memory_usage
,total_scheduled_time
,total_elapsed_time
,last_request_start_time
,last_request_end_time
,reads
,writes
,logical_reads
)
SELECT
@nextRunID
, R1.session_id
, R1.user_objects_alloc_page_count
, R1.user_objects_dealloc_page_count
, R1.internal_objects_alloc_page_count
, R1.internal_objects_dealloc_page_count
, R3.text
,S.[program_name]
,S.login_name
,S.status
,S.cpu_time
,S.memory_usage
,S.total_scheduled_time
,S.total_elapsed_time
,S.last_request_start_time
,S.last_request_end_time
,S.reads
,S.writes
,S.logical_reads
FROM
sys.dm_db_task_space_usage AS R1
INNER JOIN
sys.dm_exec_sessions AS S
ON
R1.session_id = S.session_id
LEFT OUTER JOIN sys.dm_exec_requests AS R2
ON R1.session_id = R2.session_id
OUTER APPLY sys.dm_exec_sql_text(R2.sql_handle) AS R3
WHERE
R1.session_id > 50
AND
(
R1.user_objects_alloc_page_count > 0
OR R1.user_objects_dealloc_page_count > 0
OR R1.internal_objects_alloc_page_count > 0
OR R1.internal_objects_dealloc_page_count > 0
OR R3.text IS NOT NULL
);
UPDATE
dbo.TempdbSpaceUsage_Runs
SET
LastRunID = @nextRunID;
COMMIT TRAN;
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0
ROLLBACK;
END CATCH
I expose the data as a chart in Reporting Services using the following sproc:
/*******************************************************************************************************************************
Name:dbo.rpt_Tempdb_InstanceLevelStats_TimeSeriesByChartFlag
Description:Returns tempdb-stats data over time, based on input flag @ChartFlag
@ChartFlag values:
1 - Unallocated Extent Pages
2 - Version Store Pages
3 - User-object Allocated Pages
4 - Internal-object Allocated Pages
5 - Mixed Extent Allocated Pages
*********************************************************************************************************************************/
CREATE PROCEDURE [dbo].[rpt_Tempdb_InstanceLevelStats_TimeSeriesByChartFlag]
(
@ChartFlagTINYINT
)
AS
SET NOCOUNT ON;
IF @ChartFlag NOT BETWEEN 1 AND 5
RETURN;
SELECT
CASE WHEN @ChartFlag = 1 THEN [InstanceUnallocatedExtentPages]
WHEN @ChartFlag = 2 THEN [VersionStorePages]
WHEN @ChartFlag = 3 THEN [InstanceUserobjAllocPages]
WHEN @ChartFlag = 4 THEN [InstanceInternalobjAllocPages]
WHEN @ChartFlag = 5 THEN [InstanceMixedExtentAllocPages]
END AS data
,[timestamp]
FROM
[dbo].[TempdbSpaceUsage_Instance]
ORDER BY
[timestamp];
The 5 different values of the @ChartFlag parameter represent the five different aspects of tempdb utilization, as explained in the comments of the sproc.
In my recent experience, number 4 (Internal-object Allocated Pages) tends to be a big culprit. That's when, due to poor indexing/query design, very expensive execution plans are created eating up tempdb resources.
Note that tables dbo.TempdbSpaceUsage_Instance and dbo.TempdbSpaceUsage_Task are linked via the runID column, so a large value of column InstanceInternalobjAllocPages in dbo.TempdbSpaceUsage_Instance can be traced to the actual sql running: column dbo.TempdbSpaceUsage_Task.queryText.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
July 26, 2008 at 2:27 pm
Marios, could I trouble you for the table definitions for your system as well as the rdl for the report(s) you have? Looks like some very useful stuff I would like to have in my toolset. Maybe you could put together an article for SSC even? Thanks in advance!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 27, 2008 at 5:01 am
TheSQLGuru (7/26/2008)
Marios, could I trouble you for the table definitions for your system as well as the rdl for the report(s) you have? Looks like some very useful stuff I would like to have in my toolset. Maybe you could put together an article for SSC even? Thanks in advance!!
No problem! Give me a bit of time to pull all the info together, and I will send it over in this thread.
What's the procedure for writing an article for SSC? I'd be interested in that.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
July 27, 2008 at 8:18 am
http://www.sqlservercentral.com/About/WriteForUs/
Or you can send a PM to Jeff Moden or Steve Jones.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 28, 2008 at 3:50 am
Definitely put together this utilization stuff for an article, please. That would be FANTASTIC.
July 28, 2008 at 7:36 am
Brandie Tarvin (7/28/2008)
Definitely put together this utilization stuff for an article, please. That would be FANTASTIC.
Thanks guys, I'm flattered.
Will start work on the article! 😀
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 13 posts - 46 through 57 (of 57 total)
You must be logged in to reply to this topic. Login to reply