Database/MS-SQL

[스크랩] Can't see AutoGrow/AutoShrink Information

99iberty 2015. 5. 12. 18:58

 

사실 아래 링크로도 해결은 안됐는데 혹시나 하여 옮긴다.

 

https://social.msdn.microsoft.com/forums/sqlserver/en-US/b9a78973-9d7b-404a-b366-983b0a6445f1/cant-see-autogrowautoshrink-information

 

Normally, when I right-click the database icon in Object explorer of SSMS, then click 'Reports > Standard Reports > Disk Usage' I get a couple pie charts showing disk usage, plus two expandable nodes; one with disk usage by data files and one with the autogrow/autoshrink info.

However, in our production DB, the node that normally has the AutoGrow/AutoShrink info is replaced by an message that says 'No entry found for autogrow/autoshrink event for [DBName] database in the trace log. I know for a fact it has grown. Can anyone help me resolve this? Is there another way to see autogrow/autoshrink events?.

It's gonna be hard to plan for future growth if we can't see past growth...

Thanks in advance.
Clint

Thursday, June 18, 2009 9:21 PM
|
Avatar of NashVegas
NashVegas
Avatar of NashVegas
40
Points
Top 30.00
NashVegas
Joined Jul 2008

4

40 Points

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote
    Clint,

    Did you mess around with the default trace that runs on the server? These reports uses the default trace to gather the data and you can check which firing up the profiler on a low activity server. I did and can confirm that it uses the default trace.

    Try these below and see if you get any data?

    DECLARE @filename VARCHAR(100) 
SELECT @filename = CAST(value AS VARCHAR(100)) 
FROM fn_trace_getinfo(DEFAULT) 
WHERE property = 2 
  AND value IS NOT NULL 
--Check if you have any events
SELECT gt.HostName, 
       gt.ApplicationName, 
       gt.NTUserName, 
       gt.NTDomainName, 
       gt.LoginName, 
       gt.SPID, 
       gt.EventClass, 
       te.Name AS EventName,
       gt.EventSubClass,      
       gt.TEXTData, 
       gt.StartTime, 
       gt.EndTime, 
       gt.ObjectName, 
       gt.DatabaseName, 
       gt.FileName 
FROM [fn_trace_gettable](@filename, DEFAULT) gt 
JOIN sys.trace_events te on gt.EventClass = te.trace_event_id 
WHERE EventClass in (92, 93, 94, 95)
and gt.DatabaseName = 'tempdb'  --Change the DB Name here
ORDER BY StartTime;
    Code is courtesy of Jonathan. You may want to check out his presentation of default trace here.

    | Sankar Reddy | http://sankarreddy.spaces.live.com/ |
    Thursday, June 18, 2009 10:49 PM
    |
    Avatar of Sankar Reddy
    Sankar Reddy
    Avatar of Sankar Reddy
    13,430
    Points
    Top 0.50
    Sankar Reddy
    Joined Mar 2008

    2

    5

    13

    13,430 Points
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote
    Clint,

    Is Auto-Shrink enabled on this database? If it is then you will grow/shrink/grow/shrink in small cycles over time. Depending on how busy your SQL Server actually is, your default trace may not be retaining the data for very long. Giving a relook at my code, I figured out that there is a bug that might be affecting you. The rollover doesn't go backwards, it goes forward. Try this code out:

    DECLARE @filename VARCHAR(100), @filenum int
SELECT @filename = CAST(value AS VARCHAR(100)) 
FROM fn_trace_getinfo(DEFAULT) 
WHERE property = 2 
  AND traceid = 1
  AND value IS NOT NULL 
-- Go back 4 files since default trace only keeps the last 5 and start from there.
SELECT @filename = substring(@filename, 0, charindex('_', @filename)+1) + convert(varchar, (convert(int, substring(left(@filename, len(@filename)-4), charindex('_', @filename)+1, len(@filename)))-4)) + '.trc'
--Check if you have any events
SELECT gt.HostName, 
       gt.ApplicationName, 
       gt.NTUserName, 
       gt.NTDomainName, 
       gt.LoginName, 
       gt.SPID, 
       gt.EventClass, 
       te.Name AS EventName,
       gt.EventSubClass,      
       gt.TEXTData, 
       gt.StartTime, 
       gt.EndTime, 
       gt.ObjectName, 
       gt.DatabaseName, 
       gt.FileName 
FROM [fn_trace_gettable](@filename, DEFAULT) gt 
JOIN sys.trace_events te on gt.EventClass = te.trace_event_id 
WHERE EventClass in (92, 93, 94, 95)
and gt.DatabaseName = 'tempdb'  --Change the DB Name here
ORDER BY StartTime;
    Keep in mind that you can have unused space and the database will have to grow. If the free space is fillfactor space in your indexes, and an index without enough freespace on a page has to perform one or multiple page splits to accomodate an insert, the data file will grow because a new page is needed. Worse than that, if you have a heap that is updated frequently, you can have forwarded records in the table that just hold space.

    Kalen Delaney : Geek City: What's Worse Than a Table Scan?

    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    Tuesday, June 23, 2009 8:50 PM
    |
    Avatar of Jonathan Kehayias
    Jonathan Kehayias
    Avatar of Jonathan Kehayias
    46,045
    Points
    Top 0.10
    Jonathan Kehayias
    Partner, MVPJoined Mar 2008

    5

    6

    14

    SQLskills.com
    (Partner, MVP)
    46,045 Points
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote
    You are going to have to do some research into where your free space is being held. Do you have heaps (tables without clustered indexes) in your database? Start by looking at your table sizes and free space with the following scripｔ:
    SET NOCOUNT on
/*
DESCRIPTION
 Returns TABLE Size Information 
SORTING USAGE
 @Sort bit VALUES
 0 = Alphabetically BY TABLE name
 1 = Sorted BY total space used by TABLE
*/
DECLARE @cmdstr varchar(100)
DECLARE @Sort bit
SELECT @Sort = 0 /* Edit this value FOR sorting options */
--Create Temporary Table
CREATE TABLE #TempTable 
 (	[Table_Name] varchar(50),
	Row_Count int,
	Table_Size varchar(50),
	Data_Space_Used varchar(50),
	Index_Space_Used varchar(50),
	Unused_Space varchar(50)
 )
--Create Stored Procedure String
 SELECT @cmdstr = 'sp_msforeachtable ''sp_spaceused "?"'''
--Populate Tempoary Table
 INSERT INTO #TempTable EXEC(@cmdstr)
--Determine sorting method
IF @Sort = 1 
     BEGIN 
     --Retrieve Table Data and Sort Alphabet
    --     ically
     SELECT * FROM #TempTable ORDER BY Table_Name
 END
ELSE
     BEGIN
     /*Retrieve TABLE Data AND Sort BY the size OF the Table*/
     SELECT * FROM #TempTable ORDER BY convert(int, rtrim(ltrim(replace(Table_Size, 'KB', '')))) DESC
 END
--Delete Temporay Table
DROP TABLE #TempTable

    Once you find the table with the free space in it, you need to focus in on it. Query sys.dm_db_index_physical_stats(db_id(), object_id('tablename'), null, null, 'detailed') and see if you have forwarded records if it is a heap, or if you have indexes with low fillfactors. If neither exists, does the table have LOB columns in it? If it does, try rebuilding the indexes on the table using ALTER INDEX ALL on tablename REBUILD;

    Do both of these off peak hours because they are both going to be IO heavy operations and can impact performance.



    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    Wednesday, June 24, 2009 9:52 PM
    |
    Avatar of Jonathan Kehayias
    Jonathan Kehayias
    Avatar of Jonathan Kehayias
    46,045
    Points
    Top 0.10
    Jonathan Kehayias
    Partner, MVPJoined Mar 2008

    5

    6

    14

    SQLskills.com
    (Partner, MVP)
    46,045 Points
    Moderator

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote
    Clint,

    Did you mess around with the default trace that runs on the server? These reports uses the default trace to gather the data and you can check which firing up the profiler on a low activity server. I did and can confirm that it uses the default trace.

    Try these below and see if you get any data?

    DECLARE @filename VARCHAR(100) 
SELECT @filename = CAST(value AS VARCHAR(100)) 
FROM fn_trace_getinfo(DEFAULT) 
WHERE property = 2 
  AND value IS NOT NULL 
--Check if you have any events
SELECT gt.HostName, 
       gt.ApplicationName, 
       gt.NTUserName, 
       gt.NTDomainName, 
       gt.LoginName, 
       gt.SPID, 
       gt.EventClass, 
       te.Name AS EventName,
       gt.EventSubClass,      
       gt.TEXTData, 
       gt.StartTime, 
       gt.EndTime, 
       gt.ObjectName, 
       gt.DatabaseName, 
       gt.FileName 
FROM [fn_trace_gettable](@filename, DEFAULT) gt 
JOIN sys.trace_events te on gt.EventClass = te.trace_event_id 
WHERE EventClass in (92, 93, 94, 95)
and gt.DatabaseName = 'tempdb'  --Change the DB Name here
ORDER BY StartTime;
    Code is courtesy of Jonathan. You may want to check out his presentation of default trace here.

    | Sankar Reddy | http://sankarreddy.spaces.live.com/ |
    Thursday, June 18, 2009 10:49 PM
    |
    Avatar of Sankar Reddy
    Sankar Reddy
    Avatar of Sankar Reddy
    13,430
    Points
    Top 0.50
    Sankar Reddy
    Joined Mar 2008

    2

    5

    13

    13,430 Points
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote
    Thanks for the response.

    I ran this, and there were no results. It didn't throw an error, it just didn't return anything. Does that mean there has been no autogrowth? Or does it mean the default trace has been messed with?

    I wonder if maybe there never has been any autogrowth on this DB? the reason I said in my previous post that "I know for a fact it has grown", is because when I right click the DB icon and the initial file sizes, they are smaller than when I right click the files in Windows explorer and view their properties. However, they're not much smaller at all, and I wonder if maybe the initial size is not 100% correct...

    Also, not sure if it matters, but it was originally an SQL 2000 database, but was later moved to SQL 2005.
    Clint
    Tuesday, June 23, 2009 6:28 PM
    |
    Avatar of NashVegas
    NashVegas
    Avatar of NashVegas
    40
    Points
    Top 30.00
    NashVegas
    Joined Jul 2008

    4

    40 Points
  • Question
    Sign in to vote
    0
    Sign in to vote

    After I replied to your post, I found this:

    http://www.mssqltips.com/tip.asp?tip=1111


    According to the info that gets returned when I run that query, the default query is running. Maybe there really has been no autogrowth...

    Clint
    Tuesday, June 23, 2009 6:36 PM
    |
    Avatar of NashVegas
    NashVegas
    Avatar of NashVegas
    40
    Points
    Top 30.00
    NashVegas
    Joined Jul 2008

    4

    40 Points
  • Question
    Sign in to vote
    0
    Sign in to vote
    And now...I right-clicked on the DB, and there are two autogrowth entries in the Disk Usage report...strange.

    I wonder if it is only keeping the data for a short amount of time, and that's why I couldn't see it before? either way, we have a bunch of unused, and unallocated space in the DB, so why would it be autogrowing?
    Clint
    Tuesday, June 23, 2009 8:04 PM
    |
    Avatar of NashVegas
    NashVegas
    Avatar of NashVegas
    40
    Points
    Top 30.00
    NashVegas
    Joined Jul 2008

    4

    40 Points
  • Question
    Sign in to vote
    0
    Sign in to vote
    Clint,

    Is Auto-Shrink enabled on this database? If it is then you will grow/shrink/grow/shrink in small cycles over time. Depending on how busy your SQL Server actually is, your default trace may not be retaining the data for very long. Giving a relook at my code, I figured out that there is a bug that might be affecting you. The rollover doesn't go backwards, it goes forward. Try this code out:

    DECLARE @filename VARCHAR(100), @filenum int
SELECT @filename = CAST(value AS VARCHAR(100)) 
FROM fn_trace_getinfo(DEFAULT) 
WHERE property = 2 
  AND traceid = 1
  AND value IS NOT NULL 
-- Go back 4 files since default trace only keeps the last 5 and start from there.
SELECT @filename = substring(@filename, 0, charindex('_', @filename)+1) + convert(varchar, (convert(int, substring(left(@filename, len(@filename)-4), charindex('_', @filename)+1, len(@filename)))-4)) + '.trc'
--Check if you have any events
SELECT gt.HostName, 
       gt.ApplicationName, 
       gt.NTUserName, 
       gt.NTDomainName, 
       gt.LoginName, 
       gt.SPID, 
       gt.EventClass, 
       te.Name AS EventName,
       gt.EventSubClass,      
       gt.TEXTData, 
       gt.StartTime, 
       gt.EndTime, 
       gt.ObjectName, 
       gt.DatabaseName, 
       gt.FileName 
FROM [fn_trace_gettable](@filename, DEFAULT) gt 
JOIN sys.trace_events te on gt.EventClass = te.trace_event_id 
WHERE EventClass in (92, 93, 94, 95)
and gt.DatabaseName = 'tempdb'  --Change the DB Name here
ORDER BY StartTime;
    Keep in mind that you can have unused space and the database will have to grow. If the free space is fillfactor space in your indexes, and an index without enough freespace on a page has to perform one or multiple page splits to accomodate an insert, the data file will grow because a new page is needed. Worse than that, if you have a heap that is updated frequently, you can have forwarded records in the table that just hold space.

    Kalen Delaney : Geek City: What's Worse Than a Table Scan?

    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    Tuesday, June 23, 2009 8:50 PM
    |
    Avatar of Jonathan Kehayias
    Jonathan Kehayias
    Avatar of Jonathan Kehayias
    46,045
    Points
    Top 0.10
    Jonathan Kehayias
    Partner, MVPJoined Mar 2008

    5

    6

    14

    SQLskills.com
    (Partner, MVP)
    46,045 Points
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote
    1) This code did pull up a few records of autgrowth in the DB files. Thanks for that.

    2) I do not have autoshrink enabled

    3) I only have about 18MB of unused space, but I have 15GB of unallocated space. The total DB size is 64GB.


    So if I wanted to make sure that autogrowth doesn't happen (or at least stop most of it) will I need to create more unallocated space somehow? I was under the impression that if you have unallocated space, there would be no data growth...

    I know it's a vast subject, so maybe some good links would be fine, if you have any. Thanks again, everyone!
    Clint
    Wednesday, June 24, 2009 9:45 PM
    |
    Avatar of NashVegas
    NashVegas
    Avatar of NashVegas
    40
    Points
    Top 30.00
    NashVegas
    Joined Jul 2008

    4

    40 Points
  • Question
    Sign in to vote
    0
    Sign in to vote
    You are going to have to do some research into where your free space is being held. Do you have heaps (tables without clustered indexes) in your database? Start by looking at your table sizes and free space with the following scripｔ:
    SET NOCOUNT on
/*
DESCRIPTION
 Returns TABLE Size Information 
SORTING USAGE
 @Sort bit VALUES
 0 = Alphabetically BY TABLE name
 1 = Sorted BY total space used by TABLE
*/
DECLARE @cmdstr varchar(100)
DECLARE @Sort bit
SELECT @Sort = 0 /* Edit this value FOR sorting options */
--Create Temporary Table
CREATE TABLE #TempTable 
 (	[Table_Name] varchar(50),
	Row_Count int,
	Table_Size varchar(50),
	Data_Space_Used varchar(50),
	Index_Space_Used varchar(50),
	Unused_Space varchar(50)
 )
--Create Stored Procedure String
 SELECT @cmdstr = 'sp_msforeachtable ''sp_spaceused "?"'''
--Populate Tempoary Table
 INSERT INTO #TempTable EXEC(@cmdstr)
--Determine sorting method
IF @Sort = 1 
     BEGIN 
     --Retrieve Table Data and Sort Alphabet
    --     ically
     SELECT * FROM #TempTable ORDER BY Table_Name
 END
ELSE
     BEGIN
     /*Retrieve TABLE Data AND Sort BY the size OF the Table*/
     SELECT * FROM #TempTable ORDER BY convert(int, rtrim(ltrim(replace(Table_Size, 'KB', '')))) DESC
 END
--Delete Temporay Table
DROP TABLE #TempTable

    Once you find the table with the free space in it, you need to focus in on it. Query sys.dm_db_index_physical_stats(db_id(), object_id('tablename'), null, null, 'detailed') and see if you have forwarded records if it is a heap, or if you have indexes with low fillfactors. If neither exists, does the table have LOB columns in it? If it does, try rebuilding the indexes on the table using ALTER INDEX ALL on tablename REBUILD;

    Do both of these off peak hours because they are both going to be IO heavy operations and can impact performance.
저작자표시 비영리 변경금지

'Database > MS-SQL' 카테고리의 다른 글

[스크랩] Create database for attach  (0) 2015.07.30
[스크랩] SQL Server 샘플 데이터 채우기  (0) 2015.05.14
[스크랩] 기본추적 활성화 여부 확인 및 어떤 내용 추적하는지 알기  (0) 2015.05.12
MSSQL 샘플데이터 페이지 (AdventureWorks)  (0) 2015.04.22
[스크랩] MSSQL DBA GUIDE - 데이터베이스 관리  (0) 2015.04.20

'Database/MS-SQL'의 다른글

  • 현재글[스크랩] Can't see AutoGrow/AutoShrink Information

관련글

티스토리툴바