Zwalnianie nieużywanej tabeli SQL Server

Mam tabelę w SQL Server 2012 Express z dużą ilością nieużywanego miejsca.

Muszę zwolnić miejsce w bazie danych.

| NAZWA | RZĘDY | ZAREZERWOWANE | DANE | INDEX_SIZE | NIEUŻYWANE |
| ------------- | -------- | -------------- | ----------- --- | ------------ | -------------- |
| MyTableName | 158890 | 8928296 KB | 5760944 KB | 2248 KB | 3165104 KB |

Jak uzyskać SQL, aby zwolnić 3165104 KB?

Próbowałem już:

Alter table MyTableName Rebuild
DBCC CLEANTABLE (MyDbName,"MyTableName ", 0)
ALTER INDEX ALL ON MyTableName REORGANIZE ; 
ALTER INDEX PK_Image ON MyTableName REBUILD WITH (ONLINE = OFF) 

Oto tabela:

CREATE TABLE [dbo].[MyTableName](
    [ImageID] [int] IDENTITY(1,1) NOT NULL,
    [DateScan] [datetime] NULL,
    [ScanImage] [image] NULL,
 CONSTRAINT [PK_Image] PRIMARY KEY CLUSTERED 
(
    [ImageID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
    ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Jedyne, co zrobiliśmy, to zastąpienie ScanImagew każdym rzędzie znacznie mniejszym obrazem (tyle jest niewykorzystanego miejsca).

Jedyne, co zrobiliśmy, to zastąpienie ScanImagew każdym rzędzie znacznie mniejszym obrazem (tyle jest niewykorzystanego miejsca)

Po przeprowadzeniu eksperymentów najbardziej efektywną przestrzennie metodą byłoby upuszczenie jednostki alokacji i ponowne jej wypełnienie (jeśli masz okno konserwacji, aby to zrobić).

Przykładowy kod, który osiągnął dla mnie najlepszą redukcję przestrzeni dzięki strukturze tabeli w pytaniu to:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET XACT_ABORT ON;

BEGIN TRAN

SELECT [ImageID],
       [ScanImage]
INTO   #Temp
FROM   [dbo].[MyTableName]

ALTER TABLE [dbo].[MyTableName]
  DROP COLUMN [ScanImage]

/*Allocation unit not removed until after this*/
ALTER INDEX PK_Image ON MyTableName REBUILD

ALTER TABLE [dbo].[MyTableName]
  ADD [ScanImage] IMAGE NULL

UPDATE [dbo].[MyTableName]
SET    [ScanImage] = T.[ScanImage]
FROM   [dbo].[MyTableName] M
       JOIN #Temp T
         ON M.ImageID = T.[ImageID]

DROP TABLE #Temp

COMMIT 

Wszystko jest w transakcji, więc jeśli maszyna ulegnie awarii, zostanie wycofana. Prawdopodobnie przydałaby się obsługa błędów lub przynajmniej SET XACT_ABORT ON. Kiedyś SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;zapobiegałem, aby jakiekolwiek jednoczesne modyfikacje zdarzały się podczas lub po kopiowaniu i były zgubione.

Liczba stron LOB zarezerwowanych po zmniejszeniu rozmiaru imagewe wszystkich wierszach była następująca:

+ ------------------------------------------------- - + --------------------- + ------------------------- +
| Wydarzenie | lob_used_page_count | lob_reserved_page_count |
+ ------------------------------------------------- - + --------------------- + ------------------------- +
| Wstawiono 10 000 wierszy z danymi 100 000 bajtów | 135005 | 135017 |
| Zaktualizowano wszystkie wiersze do 10 000 bajtów danych obrazu | 31251 | 135012 |
| Reorganizacja | 23687 | 25629 |
| Upuść i ponownie dodaj dane obrazu | 13485 | 13489 |
+ ------------------------------------------------- - + --------------------- + ------------------------- +

Próbować

ALTER INDEX PK_Image ON MyTableName REBUILD WITH (ONLINE = OFF)

Spowoduje to odtworzenie indeksu klastrowego, więc do zakończenia operacji będzie potrzebne dodatkowe miejsce w bazie danych. Jeśli nie masz dodatkowego miejsca, ponieważ dysk jest pełny, możesz dodać nowy plik danych do bazy danych (na innym dysku) i przenieść do niego tabelę.

Możliwe jest również zdefiniowanie indeksu klastrowego z FILLFACTOR mniejszym niż 100%. Ustawienie współczynnika wypełnienia, na przykład 66%, pozostawiłoby 1/3 każdej strony danych pustej do przyszłego wykorzystania. W takim przypadku można zmodyfikować współczynnik wypełnienia za pomocąALTER INDEX PK_Image ON MyTableName REBUILD WITH (ONLINE = OFF, FILLFACTOR=100)

Jeśli niedawno upuściłeś pole o zmiennej długości ze stołu, możesz także spróbować DBCC CLEANTABLE( Databasename, "MyTableName")

Books online (BOL) ma świetny artykuł na temat przebudowywania indeksów pod adresem http://technet.microsoft.com/en-us/library/ms188388%28v=sql.100%29.aspx

Upewnij się, że tryb odzyskiwania DB to SIMPLE.

zmień kolumnę jako VARBINARY(MAX).

Następnie spróbuj skopiować dane do zupełnie nowej tabeli.

Sprawdź nowy rozmiar stołu za pomocą sp_spaceused "tablename". Jeśli nie jesteś zadowolony z nieużywanego miejsca w tabeli, sprawdź nieużywane miejsce w bazie danych za pomocą tego samego polecenia, nie podając nazwy tabeli. Ta przestrzeń jest nadal w plikach bazy danych i nie jest udostępniana w systemie operacyjnym.

Możesz upuścić oryginalną tabelę i zmienić nazwę nowej tabeli lub zrobić to samo ponownie, i użyć oryginalnej nazwy tabeli, jeśli nie ufasz operacji zmiany nazwy ((nie ufam całkowicie)).

Jeśli to zadziała, ostatni krok jest łatwy: wiesz, jak zmniejszyć pliki i zwolnić nieużywane miejsce.

Jeśli są jakieś klucze obce, zapisz ich definicje, upuść je, wykonaj zadania, o których wspomniałem powyżej, a następnie odtwórz klucze obce. Oczywiście zajmie to trochę czasu i ta operacja powinna zostać wykonana w czasie wolnym. Całe to zadanie można również wykonać za pomocą skryptu, aby mógł działać przez noc.

Chciałbym po prostu utworzyć nową bazę danych i skopiować do niej dane. Powinieneś być w stanie skorzystać z kreatora importu / eksportu. (Oczywiście tworzenie kopii zapasowych i przywracanie nie rozwiązałoby problemu). Sprawdź wyniki importowania danych. Jeśli wszystko wygląda dobrze, zmień nazwę oryginalnej bazy danych, a następnie zmień nazwę nowej bazy danych na nazwę, której chcesz użyć. (Zawsze czekam trochę przed upuszczeniem oryginału, tylko po to, by dwukrotnie sprawdzić online.)

Co jest warte, odzyskaliśmy również przestrzeń kropli z baz danych, jeśli nie są one zbyt duże, wykonując następujące kroki. (Ponieważ jednak używasz programu SQL Server Express, możesz nie mieć miejsca na wypróbowanie tego.)

1. Dodaj nowy plik do grupy plików.
2. Uruchom DBCC SHRINKFILE(file, EMPTYFILE). Ponieważ zmniejszasz MDF, ostatecznie się nie powiedzie, ponieważ metadanych systemowych nie można przenieść. Jednak puste alokacje obiektów blob nie są przenoszone.
3. Uruchom DBCC SHRINKFILE(newfile,EMPTYFILE). Spowoduje to przeniesienie danych z powrotem, minus nadmiar miejsca.
4. Upuść nowy plik (teraz pusty) z grupa plików.

To eliminuje wzdęcia. Powinienem wspomnieć, że wykorzystaliśmy tę technikę przede wszystkim do stworzenia w większości pustej bazy danych do testowania skryptów aktualizacyjnych.

Zreorganizuj indeks klastrowany - ten zawiera dane w węzłach, więc ... prawdopodobnie jest podzielony.