September 7, 2011 at 9:06 am
Can someone please explain this peculiarity when looking at the tempdb file sizes. I've added a couple of screenshots for clarity but basically the reported size of the database does not correspond with the size of the data and log files.
For example, if I go to the database option to shrink the database, tempdb might report that the current allocated space is 579MB. But if I go to the option to shrink the file, the data file reports that the allocated space is 8MB and the Log reports that it is 20MB. Where's the rest of the 559MB?????
It's just a bit annoying because I've got some powershell scripts that collect the file sizes of each database, and in some cases it's reporting that the amount of used space is greater than the actual file size.
September 7, 2011 at 12:38 pm
Sounds like you have multiple files in tempdb. What's the result of this query:
USE tempdb ;
GO
SELECT
*
FROM
sys.database_files AS DF
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 8, 2011 at 3:20 am
Hi Jack,
Thanks for the response. I'm afraid there's only 1 data file for the tempdb databases affected. Here's the output of your query run against one of the tempdb databases affected by this (result set shortened a little bit):
file_id type type_desc data_space_id name
1 0 ROWS 1 tempdev
2 1 LOG 0 templog
I should also point out that this is happening across a number of different flavours of SQL Server. All of them are SQL Server 2005, but it's being reported against SP2, SP3 and SP4 instances (Standard, Enterprise and Express).
Phil
September 8, 2011 at 7:23 am
I can only suggest running a trace when running the GUI for both tasks as they must be doing something differently when getting the data. I don't have the proper rights on the 2005 server in the office (I'm a contractor) to see what happens in 2005. I can see that those GUI functions DO get their data in different ways in 2008. Here's what the GUI shrink database runs against SQL Server to get the size:
USE [tempdb]
SELECT
(
SELECT
SUM(CAST(df.size AS FLOAT))
FROM
sys.database_files AS df
WHERE
df.type IN (0, 2, 4)
) AS [DbSize],
SUM(a.total_pages) AS [SpaceUsed],
(
SELECT
SUM(CAST(df.size AS FLOAT))
FROM
sys.database_files AS df
WHERE
df.type IN (1, 3)
) AS [LogSize]
FROM
sys.allocation_units AS a
INNER JOIN sys.partitions AS p
ON (
a.type = 2 AND
p.partition_id = a.container_id
) OR
(
a.type IN (1, 3) AND
p.hobt_id = a.container_id
)
/* I assume this is the initial size in MB set for the Database as it is 8 MB in this case */
SELECT
CONVERT(FLOAT, low / 1024.)
FROM
master.dbo.spt_values
WHERE
number = 1 AND
type = 'E'
Here's what happens with the GUI Shrink File:
SELECT
CAST(CAST(g.name AS VARBINARY(256)) AS SYSNAME) AS [Name],
g.data_space_id AS [ID]
FROM
sys.filegroups AS g
ORDER BY
[ID] ASC
EXEC sp_executesql N'SELECT
s.name AS [Name]
FROM
sys.filegroups AS g
INNER JOIN sys.master_files AS s ON ((s.type = 2 or s.type = 0) and s.database_id = db_id() and (s.drop_lsn IS NULL)) AND (s.data_space_id=g.data_space_id)
WHERE
(CAST(cast(g.name as varbinary(256)) AS sysname)=@_msparam_0)',
N'@_msparam_0 nvarchar(4000)', @_msparam_0 = N'PRIMARY'
EXEC sp_executesql N'
create table #tmpspc (Fileid int, FileGroup int, TotalExtents int, UsedExtents int, Name sysname, FileName nchar(520))
insert #tmpspc EXEC (''dbcc showfilestats'')
SELECT
s.name AS [Name],
s.physical_name AS [FileName],
s.size * CONVERT(float,8) AS [Size],
CAST(CASE s.type WHEN 2 THEN 0 ELSE tspc.UsedExtents*convert(float,64) END AS float) AS [UsedSpace],
s.file_id AS [ID]
FROM
sys.filegroups AS g
INNER JOIN sys.master_files AS s ON ((s.type = 2 or s.type = 0) and s.database_id = db_id() and (s.drop_lsn IS NULL)) AND (s.data_space_id=g.data_space_id)
LEFT OUTER JOIN #tmpspc tspc ON tspc.Fileid = s.file_id
WHERE
(CAST(cast(g.name as varbinary(256)) AS sysname)=@_msparam_0)
ORDER BY
[ID] ASC
drop table #tmpspc
', N'@_msparam_0 nvarchar(4000)', @_msparam_0 = N'PRIMARY'
exec sp_executesql N'
create table #tmpspc (Fileid int, FileGroup int, TotalExtents int, UsedExtents int, Name sysname, FileName nchar(520))
insert #tmpspc EXEC (''dbcc showfilestats'')
SELECT
s.name AS [Name],
s.physical_name AS [FileName],
s.size * CONVERT(float,8) AS [Size],
CAST(CASE s.type WHEN 2 THEN 0 ELSE tspc.UsedExtents*convert(float,64) END AS float) AS [UsedSpace],
s.file_id AS [ID]
FROM
sys.filegroups AS g
INNER JOIN sys.master_files AS s ON ((s.type = 2 or s.type = 0) and s.database_id = db_id() and (s.drop_lsn IS NULL)) AND (s.data_space_id=g.data_space_id)
LEFT OUTER JOIN #tmpspc tspc ON tspc.Fileid = s.file_id
WHERE
(CAST(cast(g.name as varbinary(256)) AS sysname)=@_msparam_0)
ORDER BY
[ID] ASC
drop table #tmpspc
',N'@_msparam_0 nvarchar(4000)',@_msparam_0=N'PRIMARY'
Notice that this result does not include the log file size. I see the log file size when I switch to shrink log but I don't see any code in Profiler that gets that data so I don't know where it is coming from.
I'm thinking now that the Shrink Database shows Data + Log, while Shrink File shows either Data OR log. Thus your discrepancy is likely that you have a very large log file, at least in comparison to the database file.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 8, 2011 at 8:31 am
Thanks Jack - I really appreciate the effort you've put into this!
I've run the traces myself when going to look at the shrink options for the database and files. The code being executed is the same as you've listed in your response. I've also captured the code for analysing the log size:
SELECT
s.name AS [Name],
s.physical_name AS [FileName],
s.size * CONVERT(float,8) AS [Size],
CAST(FILEPROPERTY(s.name, 'SpaceUsed') AS float)* CONVERT(float,8) AS [UsedSpace],
s.file_id AS [ID]
FROM
sys.master_files AS s
WHERE
(s.type = 1 and s.database_id = db_id())
ORDER BY
[ID] ASC
The craziest thing when running this against one of my tempdb instances is that the size being reported back is 512 (KB), whilst the UsedSpace is 59440 (KB). Hmmmm - the plot thickens.
Putting a bit more contex to this, I'm gathering this information across a host of different SQL Server instances. I'm using PowerShell scripts to do the data collection which is using SMO to query the SQL instance.
I'm accessing the DataFile class (Microsoft.SqlServer.Management.Smo.DataFile) and LogFile class (Microsoft.SqlServer.Management.Smo.LogFile) where I'm retrieving the Size and UsedSpace properties. If anyone's aware of any other classes I can use to (accurately) retrieve database size information then I would be grateful.
I should point out that I initially access the FileGroup class and then loop through each DataFile that's identified.
September 9, 2011 at 11:25 am
Thats just the way it looks for the tempdb. If I go to any server, the shrink database always has the right data for tempdb and shrink file is some weird data seemingly based on the initial size. No worries.
September 11, 2011 at 2:21 pm
Thanks Logan,
It's not so much of a concern, although I do think it is peculiar behaviour - particularly when I have several other SQL instances behaving as I would like.
The trouble I have, as I alluded to in my previous post, is that I am trying to gather stats on database file sizes and am using PowerShell to execute SMO statements against each database on a SQL instance.
I just find it odd that tempdb reports in a very different manner to every other database.
September 12, 2011 at 4:01 am
Hello Phil,
Maybe this is the issue? http://qa.sqlservercentral.com/Forums/Topic657407-146-1.aspx#bm657482
I quote:
The figure you are looking at is calculated from the control information held about tables in tempdb.
tempdb has what is known as lazy garbage collection. When an object is dropped, not all of the control information about that object gets dropped immediately. This allows SQL Server to re-use the table header information if a new object is required in tempdb.
Creating control information for new objects takes time, so SQL Server can run faster if it is able to re-use the control information of objects it knows have been dropped. When the new object is initialised, the control information will be updated to reflect the new object.
In the meantime you may have situations where the total of allocated space reported in the control information (including the dropped object information) exceeds the size of tempdb. This is nothing to worry about.
I hope this helps.
Greetz
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply