I don't know what to say, to myself...
Short of all the "reasons" for how I got stuck at this point:
Yes, that is one of the primary disk for SQL. Short answer, someone running bad, bad, bad, ETL, freaked out the DB to grow like crazy.
Now what?If anything else triggers a grow, inevitable, SQL is toast. Find that GAME FACE and MIH, ninja style....
High level summary
- Procure additional disk space, without shutting down? A. ISCSI
- Backup offending DB, or which ever you wish to move off (ensure Full Recovery mode). I choose "BOBTRENDS"
- Restore backup to new space as BOBTRENDS_NEW. Note: ensure your file names include "_NEW" (e.g. I:\SQL_DATA\BOBTRENDS_NEW_Data.MDF) and WITH NORECOVERY.
- Prep SQL scripts: Rename existing db and files. Rename New db, drop new, rename files, attach as original name
GOOGLE....ding ding ding: Best Practice for renaming a SQL Server Database
oh man. ready? Everything below is geared for my environment, adjust accordingly. Eh, don't try to run in one shot, murphy's watching you.
USE [MASTER]
GO
--// -------------------------------------
--// Step 1. Backup Current
--// -------------------------------------
BACKUP DATABASE [BOBTRENDS] TO DISK ='Q:\FULL_SQL_BK\BOBTRENDS_2010xxxx_1130_FULL.hbc'
GO
--// -------------------------------------
--// Step 2. Restore to new space
--// -------------------------------------
RESTORE DATABASE [BOBTRENDS_NEW]
FROM DISK= 'Q:\FULL_SQL_BK\BOBTRENDS_2010xxxx_1130_FULL.hbc'
WITH
MOVE 'BOBTRENDS_Data' TO 'I:\SQL_DATA\BOBTRENDS_NEW_Data.MDF' --// This the fatty
,MOVE 'BOBTRENDS_Fast' TO 'G:\SQL_FAST\BOBTRENDS_NEW_Fast.ndf'
,MOVE 'BOBTRENDS_Log' TO 'E:\SQL_LOGS\BOBTRENDS_NEW_Log.LDF'
, NORECOVERY, STATS=10
GO
--// -------------------------------------
--// Step 3. Close shop, emergency restroom break?
--// -------------------------------------
--// Sorry people, I gotta boot you, 5 minutes I promise :-O
;ALTER DATABASE [BOBTRENDS]
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
--//Backup transaction log
BACKUP LOG [BOBTRENDS] TO DISK ='Q:\LOG_SHIPPING_DROP\BOBTRENDS_2010xxxx_1200_LOG.hbc' WITH MAXTRANSFERSIZE = 262144;
GO
--// -------------------------------------
--// Step 4. Start Rename
--// -------------------------------------
--// Rename current as _ORIG. Why? I don't want to risk overwriting, bad restore or brain fart.
--// Oh and one file(s) will remain on same disk space, G:\.
GO
EXEC master..sp_renamedb 'BOBTRENDS','BOBTRENDS_ORIG'
GO
/* Detach Current Database :ty mssqltips */
EXEC master.dbo.sp_detach_db @dbname = N'BOBTRENDS_ORIG'
GO
/* Rename Physical Files :ty mssqltips*/
EXEC xp_cmdshell 'RENAME "F:\SQL_DATA\BOBTRENDS_Data.MDF", "BOBTRENDS_ORIG_Data.MDF"'
GO
EXEC xp_cmdshell 'RENAME "G:\SQL_FAST\BOBTRENDS_Fast.ndf", "BOBTRENDS_ORIG_Fast.ndf"'
GO
EXEC xp_cmdshell 'RENAME "E:\SQL_LOGS\BOBTRENDS_Log.LDF", "BOBTRENDS_ORIG_Log.LDF"'
GO
RETURN
--// -------------------------------------
--// Step 5. VERIFY ALL WENT WELL
--// -------------------------------------
/* ALWAYS HAVE A ROLLBACK PLAN!!!
EXEC xp_cmdshell 'RENAME "F:\SQL_DATA\BOBTRENDS_ORIG_Data.MDF", "BOBTRENDS_Data.MDF"'
GO
EXEC xp_cmdshell 'RENAME "G:\SQL_FAST\BOBTRENDS_ORIG_Fast.ndf", "BOBTRENDS_Fast.ndf"'
GO
EXEC xp_cmdshell 'RENAME "E:\SQL_LOGS\BOBTRENDS_ORIG_Log.LDF", "BOBTRENDS_Log.LDF"'
GO
EXEC master.dbo.sp_attach_db @dbname = N'BOBTRENDS_ORIG'
GO
EXEC master..sp_renamedb 'BOBTRENDS_ORIG','BOBTRENDS'
*/
RETURN
--// -------------------------------------
--// Step 6. Now address the "_NEW" DB
--// -------------------------------------
RESTORE LOG [BOBTRENDS_NEW]
FROM DISK= 'Q:\LOG_SHIPPING_DROP\BOBTRENDS_2010xxxx_1200_LOG.hbc'
WITH NORECOVERY, MAXTRANSFERSIZE = 1048576,STATS=10
GO
;RESTORE DATABASE [BOBTRENDS_NEW] WITH RECOVERY
GO
;ALTER DATABASE [BOBTRENDS_NEW]
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
GO
EXEC master..sp_renamedb 'BOBTRENDS_NEW','BOBTRENDS'
GO
/* Detach Current Database :ty mssqltips */
EXEC master.dbo.sp_detach_db @dbname = N'BOBTRENDS'
GO
/* Rename Physical Files :ty mssqltips */
EXEC xp_cmdshell 'RENAME "I:\SQL_DATA\BOBTRENDS_NEW_Data.MDF", "BOBTRENDS_Data.MDF"'
GO
EXEC xp_cmdshell 'RENAME "G:\SQL_FAST\BOBTRENDS_NEW_Fast.ndf", "BOBTRENDS_Fast.ndf"'
GO
EXEC xp_cmdshell 'RENAME "e:\SQL_LOGS\BOBTRENDS_NEW_Log.LDF", "BOBTRENDS_Log.LDF"'
GO
/* Attach Renamed Database :ty mssqltips */
CREATE DATABASE [BOBTRENDS] ON
( FILENAME = N'I:\SQL_DATA\BOBTRENDS_Data.MDF' ),
( FILENAME = N'G:\SQL_FAST\BOBTRENDS_Fast.ndf'),
( FILENAME = N'E:\SQL_LOGS\BOBTRENDS_Log.LDF')
FOR ATTACH
/* Identify Database File Names :ty mssqltips */
SELECT
name AS [Logical Name],
physical_name AS [DB File Path],
type_desc AS [File Type],
state_desc AS [State]
FROM sys.master_files
WHERE database_id IN (DB_ID(N'BOBTRENDS_NEW'),DB_ID(N'BOBTRENDS'),DB_ID(N'BOBTRENDS_ORIG') )
--// -------------------------------------
--// Step 7. CHECK CHECK AND TRIPLE CHECK
--// -------------------------------------
After I confirmed the new db was functional, I performed a full back up, and deleted the _ORIG files.
Reclaiming 300 GB. YAY!!!
Comments :
Post a Comment