Skip to content

Instantly share code, notes, and snippets.

@Hona
Created January 21, 2025 03:10
Show Gist options
  • Select an option

  • Save Hona/5c4abbe505f984f9a65f2e3ae316e428 to your computer and use it in GitHub Desktop.

Select an option

Save Hona/5c4abbe505f984f9a65f2e3ae316e428 to your computer and use it in GitHub Desktop.
Localhost SQL server is tanked? Nuke everything
USE master;
GO
SET NOCOUNT ON;
BEGIN TRY
-- Instance cleanup
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
DBCC FREESYSTEMCACHE ('ALL') WITH NO_INFOMSGS;
DECLARE @dbname sysname, @sql nvarchar(max)
DECLARE db_cursor CURSOR LOCAL FAST_FORWARD FOR
SELECT name FROM sys.databases
WHERE state = 0
AND name NOT IN ('master', 'tempdb', 'model', 'msdb')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = N'USE [' + @dbname + '];
EXEC sp_MSforeachtable ''SET QUOTED_IDENTIFIER ON; UPDATE STATISTICS ? WITH FULLSCAN'';
EXEC sp_MSforeachtable ''SET QUOTED_IDENTIFIER ON; ALTER INDEX ALL ON ? REBUILD WITH(ONLINE = OFF, SORT_IN_TEMPDB = ON)'';
CHECKPOINT;'
EXEC sp_executesql @sql
FETCH NEXT FROM db_cursor INTO @dbname
END
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS ErrorMessage;
END CATCH
IF CURSOR_STATUS('LOCAL','db_cursor') >= 0
BEGIN
CLOSE db_cursor
DEALLOCATE db_cursor
END
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment