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!!!

SolveigMM Video Splitter

When you just need to trim or split a .WMV file.


Movie Maker was killing me, I just needed to trim last 3 minutes off a 1h 20m file (276mb). Its was going to take 512 minutes to save, MEH!!!

Took me 15minutes of googling and testing various results to find this GEM.


Enjoy.

VMware ESXi OPENFiler Goodness

Dual OP 4tb each, 2 esxi, HIGH I/O, iSCSI, Unhappiness, Large LUNs, at one point shared between the 2 esxi, dual 1gb nics.....sporatic disconnects Then this: http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=1005009 and I like this gus post, http://vinf.net/2009/10/29/iscsi-lun-is-very-slowno-longer-visible-from-vsphere-host/


Ok I need to reconfigure somethings...

Automate DBSchema imports

If you got a lot of DB's to manage and servers, and don't have it under source control, try this:
Read this Deploying your Database Project without VSTSDB installed, and then toil with below. Just another shotgun approach.


One step further, for pre-existing environments, capture all the schemas, then DEPLOY to a dev environment and start creating Database Projects(haven't figure out how to automate that yet). A few tweak to the sources, and you got the makings of GUI approach for change management and source control.

Can anyone advise on how to programmatically create a DB Project? I'm struggling to find resources on that topic.

@ECHO off
::Run this to create function calls
:: select
:: 'CALL:GOBANANA "' +name + '" "' + @@Servername + '"'
:: from
:: sysdatabases where name not in('tempdb') --// Yes I want master and model

SET VSDBCMD="C:\Program Files\Microsoft Visual Studio 9.0\VSTSDB\Deploy\vsdbcmd.exe"

CALL:GOBANANA "BOBS_DB" "BOBS_SQLSERVER"

GOTO:EOF

:GOBANANA
SET DBNAME=%~1
SET SVRNAME=%~2
SET SCHEMAFOLDER="C:\temp\DB_Schemas\%SVRNAME:\=_%"
ECHO %SCHEMAFOLDER%

IF NOT EXIST %SCHEMAFOLDER% MKDIR %SCHEMAFOLDER%
:: Strip double quote, safety
SET SCHEMAFOLDER=###%SCHEMAFOLDER%###
SET SCHEMAFOLDER=%SCHEMAFOLDER:"###=%
SET SCHEMAFOLDER=%SCHEMAFOLDER:###"=%
SET SCHEMAFOLDER=%SCHEMAFOLDER:###=%

ECHO SAVING Database Schema for, %DBNAME% to "%SCHEMAFOLDER%\%DBNAME%.dbschema"

%VSDBCMD% /a:Import /cs:"Server=%SVRNAME%;Integrated Security=true;Pooling=false;Initial Catalog=%DBNAME%;" /dsp:Sql /model:"%SCHEMAFOLDER%\%DBNAME%.dbschema"
GOTO:EOF

Amazing win2k3Ent.

Evil Temporary ASP.NET Files CACHED!!!

Another fine mess. 404's galore cause of bad referencing in static CSS files. Culprit found, fix and redeployed, but 404's persist. DIG DIG DIG

AHAHA!!!! The "Temporary ASP.NET Files" still contains the bugged compiled version. Batchscript? Sure why not....

---------------------------------------------------------------


@ECHO OFF
ECHO DO YOU REALLY WANT TO DO THIS???
:: SAFETY FIRST!!! Comment out below to run, but I suggest a dry run and test
GOTO:EOF

::// Uncomment for dry run with just one server
::CALL:GOBANANA WEB1
::GOTO:EOF
:://----------------------------

CALL:GOBANANA ServerName
::CALL:GOBANANA ServerNameX

GOTO :EOF

:GOBANANA
SET SRVNAME=%1
:: Know thy path to Sysinternals Suite:
:: http://technet.microsoft.com/en-us/sysinternals/bb842062.aspx
:: Or just goolge
SET PRGPATH=C:\SysinternalsSuite
ECHO HOST: \\%SRVNAME%
%PRGPATH%\psexec \\%SRVNAME% iisreset /stop
%PRGPATH%\psexec \\%SRVNAME% cmd.exe /c for /d %%i in ("C:\Windows\Microsoft.Net\Framework\v2.0.50727\Temporary ASP.NET Files\*") do RMDIR /S/Q "%%i"
%PRGPATH%\psexec \\%SRVNAME% iisreset /start


ENJOY!!!

TurnKey Fileserver ....




ThankYou

configure DNS remotely. NETSH -r MEH

It must be that I need a nap, but I couldn't get netsh -r to work. I wanted to create a batch script to set the DNS for all windows servers in a domain. Alas I gave in for what I knew would work...

Be sure you got sysinternals installed...

Let's see what the current setting are:
psexec \\ServerName ipconfig /all

working example:
-----------------------------------------------------------------
Windows IP Configuration

Host Name . . . . . . . . . . . . : ServerName
Primary Dns Suffix . . . . . . . : BOBMIHADA.COM
Node Type . . . . . . . . . . . . : Unknown
IP Routing Enabled. . . . . . . . : No
WINS Proxy Enabled. . . . . . . . : No
DNS Suffix Search List. . . . . . : BOBMIHADA.COM

Ethernet adapter Private:

Connection-specific DNS Suffix . :
Description . . . . . . . . . . . : Intel(R) PRO/1000 MT Network Connection
Physical Address. . . . . . . . . : 00-00-00-00-00-00
DHCP Enabled. . . . . . . . . . . : No
IP Address. . . . . . . . . . . . : 192.168.101.71
Subnet Mask . . . . . . . . . . . : 255.255.255.0
Default Gateway . . . . . . . . . :192.168.101.1
DNS Servers . . . . . . . . . . . : 192.168.101.10
192.168.101.51

Ethernet adapter Public:

Connection-specific DNS Suffix . :
Description . . . . . . . . . . . : Intel(R) PRO/1000 MT Network Connection #2
Physical Address. . . . . . . . . : 00-00-00-00-00-00
DHCP Enabled. . . . . . . . . . . : No
IP Address. . . . . . . . . . . . : 192.168.2.100
Subnet Mask . . . . . . . . . . . : 255.255.255.0
Default Gateway . . . . . . . . . : 192.168.2.1
DNS Servers . . . . . . . . . . . : 192.168.2.10
192.168.2.54
ipconfig exited on ServerName with error code 0.


Now let's proceed with changing the DNS settings on the Private Interface
--------------------------------------------------------------------------------

psexec \\ServerName netsh interface ip set dns "Private" source=static addr=192.168.101.69
psexec \\ServerName netsh interface ip add dns "Private" 192.168.101.79

Let's see what the NEW current setting are:
psexec \\ServerName ipconfig /all

-----------------------------------------------------------------
Windows IP Configuration

Host Name . . . . . . . . . . . . : ServerName
Primary Dns Suffix . . . . . . . : BOBMIHADA.COM
Node Type . . . . . . . . . . . . : Unknown
IP Routing Enabled. . . . . . . . : No
WINS Proxy Enabled. . . . . . . . : No
DNS Suffix Search List. . . . . . : BOBMIHADA.COM

Ethernet adapter Private:

Connection-specific DNS Suffix . :
Description . . . . . . . . . . . : Intel(R) PRO/1000 MT Network Connection
Physical Address. . . . . . . . . : 00-00-00-00-00-00
DHCP Enabled. . . . . . . . . . . : No
IP Address. . . . . . . . . . . . : 192.168.101.71
Subnet Mask . . . . . . . . . . . : 255.255.255.0
Default Gateway . . . . . . . . . :192.168.101.1
DNS Servers . . . . . . . . . . . : 192.168.101.69
192.168.101.79

Ethernet adapter Public:

Connection-specific DNS Suffix . :
Description . . . . . . . . . . . : Intel(R) PRO/1000 MT Network Connection #2
Physical Address. . . . . . . . . : 00-00-00-00-00-00
DHCP Enabled. . . . . . . . . . . : No
IP Address. . . . . . . . . . . . : 192.168.2.100
Subnet Mask . . . . . . . . . . . : 255.255.255.0
Default Gateway . . . . . . . . . : 192.168.2.1
DNS Servers . . . . . . . . . . . : 192.168.2.10
192.168.2.54
ipconfig exited on ServerName with error code 0.

ENJOY!