Nie wydaje się to wcale takie szalone, ale zauważ, że niektóre okna dialogowe interfejsu użytkownika mogą nie zawierać całkowicie aktualnych informacji (dlatego mamy takie rzeczy, jak DBCC UPDATEUSAGE ), a zaokrąglanie może być również zaangażowane w niektóre z nich obliczenia. Wreszcie okna dialogowe pokazują całkowitą przestrzeń dla całej bazy danych , ale nieprzydzielone miejsce jest obliczane tylko dla plików danych , a nie dziennika.
Połączmy kilka rzeczy.
- Właściwości bazy danych i zmniejszana baza danych pokazują to samo (nie to, że i tak powinieneś być w interfejsie zmniejszanej bazy danych!).
- Właściwości pliku bazy danych pokazują 17 + 75 = 92, co przy zaokrągleniu przed dodaniem jest prawdopodobnie tym samym 91,31 w 1.
- W przypadku przydzielonego miejsca zmniejszanie dla poszczególnych plików pokazuje 16,38 + 74,94 = 91,32 - ponownie, prawdopodobnie niektóre zaokrąglenia tam, w przeciwnym razie dokładnie pasujące 1.
- W przypadku dostępnego miejsca zmniejszanie pojedynczych plików jest jedynym miejscem, w którym podejrzewam prawdziwą rozbieżność, a to dlatego, że interfejs użytkownika jest niespójny co do tego, skąd pobiera dane, a niektóre z tych miejsc podlegają buforowaniu, które wymaga aktualizacji DBCC UPDATEUSAGE.
Zobaczmy, jak działają te różne okna dialogowe dla mojej lokalnej kopii AdventureWorks2012 (z pewnymi tabelami powiększonymi z tego skryptu ).
EXEC sp_spaceused;
Zwraca (tylko pierwszy zestaw wyników):
database_size unallocated space
------------- -----------------
1545.81 MB 6.67 MB
Zasadniczo uruchamia to, co - co potwierdziłem za pomocą śledzenia - jest w przybliżeniu tym samym zapytaniem wykonanym z właściwości bazy danych i okien dialogowych zmniejszania bazy danych (wykreśliłem niepotrzebne części z procedury składowanej i dodałem zapytanie zewnętrzne reprezentujące matematykę które SSMS robi dla wyświetlania):
SELECT database_size = DbSize*8.0/1024 + LogSize*8.0/1024,
[unallocated space] = (DbSize-SpaceUsed)*8.0/1024
FROM
(
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.partitions p
join sys.allocation_units a on p.partition_id = a.container_id
left join sys.internal_tables it on p.object_id = it.object_id
) AS x;
To zwraca dopasowanie:
database_size unallocated space
------------- -----------------
1545.8125 6.671875
Wszystkie okna dialogowe pokazują te informacje poprawnie. Okno dialogowe Właściwości bazy danych:
Okno dialogowe Zmniejsz bazę danych:
Z drugiej strony okna dialogowe pliku zmniejszania uruchamiają nieco inne zapytanie (ponownie jest rzeźbione / dostosowane dla wygody):
SELECT SUBSTRING(name, CHARINDEX('_',name)+1, 4),
[Currently allocated space] = size/1024.0,
[Available free space] = (Size-UsedSpace)/1024.0
FROM
(
SELECT s.name,
CAST(FILEPROPERTY(s.name, 'SpaceUsed') AS float)*CONVERT(float,8) AS [UsedSpace],
s.size * CONVERT(float,8) AS [Size]
FROM sys.database_files AS s
WHERE (s.type IN (0,1))
) AS x;
Zauważ też, że oprócz pobierania danych o wielkości z funkcji zamiast DMV, predykaty nie zostały zaktualizowane dla nowych typów plików, takich jak filestream / hekaton.
Wyniki:
Currently allocated space Available free space
---- ------------------------- --------------------
Data 1517 7.9375 -- wrong
Log 28.8125 25.671875 -- wrong
Problemem jest FILEPROPERTY()funkcja, która nie jest gwarantowana na bieżąco (nawet po DBCC UPDATEUSAGE(0);uruchomieniu; więcej poniżej). Kończy się to wprowadzającymi w błąd informacjami w oknach dialogowych:
Zauważ, że 6,67 MB nigdy nie było tak naprawdę dokładne, ponieważ mierzy to tylko całkowity rozmiar bazy danych - liczbę przydzielonych stron, całkowicie ignorując dziennik.
Szczerze mówiąc, jeśli chcesz dokładnego raportowania miejsca używanego w bazie danych, przestań używać interfejsów myszy Myszki Miki, które uruchamiają różnego rodzaju zapytania, aby to zrozumieć, i przestań używać okien dialogowych pliku zmniejszania do pobierania informacji. Są one w oczywisty sposób narażone na problemy z nieaktualnymi danymi w niektórych przypadkach. Uruchom rzeczywiste zapytanie względem źródła, któremu możesz zaufać. Oto, co wolę:
DECLARE @log_used DECIMAL(19,7);
CREATE TABLE #x(n SYSNAME, s DECIMAL(19,7), u DECIMAL(19,7), b BIT);
INSERT #x EXEC('DBCC SQLPERF(LogSpace);');
SELECT @log_used = u FROM #x WHERE n = DB_NAME();
DROP TABLE #x;
DECLARE @data_used DECIMAL(19,7);
SELECT @data_used = SUM(a.total_pages)*8/1024.0
FROM sys.partitions AS p
INNER JOIN sys.allocation_units AS a
ON p.[partition_id] = a.container_id;
;WITH x(t,s) AS
(
SELECT [type] = CASE
WHEN [type] IN (0,2,4) THEN 'data' ELSE 'log' END,
size*8/1024.0 FROM sys.database_files AS f
)
SELECT
file_type = t,
size = s,
available = s-CASE t WHEN 'data' THEN @data_used ELSE @log_used END
FROM x;
To zapytanie zwraca trzy liczby, które powinny wyglądać bardzo znajomo, i jedną, która nie powinna:
file_type size available
--------- ----------- ----------
data 1517.000000 6.6718750
log 28.812500 17.9008512
Zauważ, że DBCC SQLPERF jest również nieco podatny na problemy z wykorzystaniem miejsca, na przykład po uruchomieniu:
DBCC UPDATEUSAGE(0);
Powyższe zapytanie daje to zamiast tego:
file_type size available
--------- ----------- ----------
data 1517.000000 8.0781250
log 28.812500 17.8669481
sp_spaceusedteraz daje również pasujące liczby ( 1545.81 MB / 8.08 MB), chociaż - znowu - to tylko przestrzeń dostępna w plikach danych , a okna dialogowe właściwości bazy danych i skurczów bazy danych są również „dokładne” (ale okna dialogowe plików skurczu są nadal daleko - FILEPROPERTY()wydaje się, że wcale nie ma na to wpływu UPDATEUSAGE):
Aha, i równie dobrze może pokazać, co Eksplorator Windows myśli o tych plikach, więc możesz odnieść się do obliczeń wykonanych w celu ustalenia MB:
To, jak dokładne musi być to wszystko, zależy oczywiście od tego, co zrobisz z informacjami.