Another OMG moment.."near capacity"

I don't know what to say, to myself...


Short of all the "reasons" for how I got stuck at this point:

Event Type: Warning
Event Source: Srv
Event Category: None
Event ID: 2013
Date: ?/?/2010
Time: Does it really matter?
User: N/A
Computer: BOBSQL
Description:
The F: disk is at or near capacity. You may need to delete some files.


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
  1. Procure additional disk space, without shutting down? A. ISCSI
  2. Backup offending DB, or which ever you wish to move off (ensure Full Recovery mode). I choose "BOBTRENDS"
  3. 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.
  4. Prep SQL scripts: Rename existing db and files. Rename New db, drop new, rename files, attach as original name

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 :

0 comments to “Another OMG moment.."near capacity"”