Created
January 21, 2025 03:10
-
-
Save Hona/5c4abbe505f984f9a65f2e3ae316e428 to your computer and use it in GitHub Desktop.
Localhost SQL server is tanked? Nuke everything
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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