Aumentar o espaço livre no seu banco de dados SQL Server sempre foi um grande desafio para vários DBA’s.
Recentemente eu publiquei um post que pode ajudar alguns DBA’s SQL Server a aumentar o espaço livre no seu banco de dados no arquivo .mdf, encontrando os índices duplicados no SQL Server isso já ajudava muito.
Mas isso não é o bastante, existem outras formas de você encontrar mais coisas desnecessárias no banco de dados SQL Server e aqui eu vou deixar abaixo para vocês algumas dicas.
SELECT TOP 30 'DROP TABLE '+OBJECT_NAME(ID) AS [COMANDO], OBJECT_NAME(ID) AS NOMEDATABELA, INDID, ROWCNT AS LINHAS, RESERVED * 8 AS RESERVADO_KB, DPAGES * 8 AS DADOS_KB, (SUM(USED) * 8) - (DPAGES * 8) AS TAMANHO_INDICE_KB, (SUM(RESERVED) * 8) - (SUM(USED) * 8) AS NAO_UTILIZADO_KB FROM SYSINDEXES WHERE INDID IN (0,1) AND OBJECTPROPERTY(ID, 'ISUSERTABLE') = 1 AND ROWCNT > 0 AND OBJECT_NAME(ID) IN (SELECT T.NAME FROM SYS.TABLES T WHERE T.NAME LIKE '%BKP%' OR T.NAME LIKE '%ANTES%' OR T.NAME LIKE '%OLD%' OR T.NAME LIKE '%TEMP%' OR T.NAME LIKE '%[0-9]%') GROUP BY ID, ROWCNT, RESERVED, DPAGES, INDID ORDER BY 4 DESC GO
SELECT TOP 10 'DROP INDEX ['+ISNULL(I.NAME,'HEAP')+'] ON ['+OBJECT_NAME(I.OBJECT_ID)+']' AS [COMANDO], OBJECT_NAME(I.OBJECT_ID) AS NOME_DA_TABELA, ISNULL(I.NAME,'HEAP') AS [INDICE_NAO_UTILIZADO], S.USER_SEEKS, CAST((((8 * SUM(A.USED_PAGES)) / 1024.00) / 1024.00) AS NUMERIC(15,4)) AS 'INDEXSIZE(GB)' FROM SYS.INDEXES I LEFT JOIN SYS.DM_DB_INDEX_USAGE_STATS S ON S.OBJECT_ID = I.OBJECT_ID AND I.INDEX_ID = S.INDEX_ID AND S.DATABASE_ID = DB_ID() JOIN SYS.PARTITIONS AS P ON P.OBJECT_ID = I.OBJECT_ID AND P.INDEX_ID = I.INDEX_ID JOIN SYS.ALLOCATION_UNITS AS A ON A.CONTAINER_ID = P.PARTITION_ID WHERE OBJECTPROPERTY(I.OBJECT_ID, 'ISINDEXABLE') = 1 AND OBJECTPROPERTY(I.OBJECT_ID, 'ISINDEXED') = 1 AND I.IS_PRIMARY_KEY = 0 AND (S.INDEX_ID IS NULL OR (S.USER_UPDATES > 0 AND S.USER_SEEKS < 50 )) GROUP BY I.OBJECT_ID, I.NAME, S.USER_SEEKS ORDER BY 5 DESC GO
SELECT OBJECT_NAME(I.OBJECT_ID) AS TABELA, I.NAME AS INDICE, COUNT(*) AS QTDE_COLUMNS_IDX, (SELECT COUNT(1) FROM SYS.COLUMNS C WHERE C.OBJECT_ID = I.OBJECT_ID) AS QTDE_COLUMNS_TBL FROM SYS.INDEXES I INNER JOIN SYS.INDEX_COLUMNS IC INNER JOIN SYS.COLUMNS C ON C.OBJECT_ID = IC.OBJECT_ID AND C.COLUMN_ID = IC.COLUMN_ID ON I.INDEX_ID = IC.INDEX_ID AND I.OBJECT_ID = IC.OBJECT_ID GROUP BY I.NAME, I.OBJECT_ID ORDER BY 3 DESC GO
Os scripts que estou compartilhando foram fornecidos por um colega de trabalho Marcus Vinicius que recentemente trabalhou em um projeto para aumentar o espaço livre no banco de dados SQL Server e deixou que eu compartilhasse com vocês.
Aproveitem!
Grande abraço!
Port video games have actually long been a popular type of entertainment, providing exhilarating gameplay…
When it pertains to online gambling, safety needs to be a top issue for every…
There are many reasons to play no-cost online casino games in the coming year. The…
On the internet casinos have acquired tremendous appeal in recent times, offering gamers with the…
Welcome to our useful post concerning the very best casino site game! Whether you're a…
The random number generator (or RNG) is the program that generates the outcome of every…