Monday, Sept 30 is the last chance for MSDN subscribers to win an Aston Martin

Do you have an MSDN subscription? If so, it includes monthly credit for using Windows Azure. Below is a link for activating the Azure benefit, NO CREDIT CARD required!
Use this link: http://bit.ly/140w35v

By the way, all Gold and Silver Microsoft Partners, all members of BizSpark, all companies that have an EA agreement have multiple free MSDN subscriptions. If you are not using those, this is another incentive to start.

The monthly credit is quite generous, more than enough to run your Dev and Test environments for even large application development projects.

As an added incentive to try out your free MSDN benefits, Microsoft is currently holding a special Aston Martin Sweepstake offer through Sept 30th. The lucky winner will win a 2013 Aston Martin V8 Vantage! (which is why I signed up today)

C Drive Full & guess where SQL Server was installed – Aargh!

It has been a while since I blogged on those “gotchas” that I come across as a SQL Server DBA.

I still acquire already installed & configured SQL Server database systems after an outage brings them to my attention. A more common scenario than desirable is the C Drive filling and impacting the SQL Server installation resident on this same drive. A colleague, James Donnelly, came up with the following script to help move the critical system databases to another drive. This example does assume a default instance rather than a named instance of SQL Server.

1 open command prompt & type the following

NET STOP MSSQLSERVER

2 once complete – start it up again with the /f /T3608
NET START MSSQLSERVER /f /T3608

3 open SQL connection to DB & run the following commands, This will identify where the current location of the mdf & ldf files are located. Open up an Explorer window to this location & verify that the files exist

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files

4 Execute the following scripts against the Master db.

— Master DB
ALTER DATABASE master MODIFY FILE( NAME = master , FILENAME = ‘E:\MSSQL\Data\master.mdf’ )
ALTER DATABASE master MODIFY FILE( NAME = mastlog , FILENAME = ‘E:\MSSQL\Log\mastlog.ldf’ )

— Model DB
ALTER DATABASE Model MODIFY FILE( NAME = modeldev , FILENAME = ‘E:\MSSQL\Data\model.mdf’ )
ALTER DATABASE Model MODIFY FILE( NAME = modellog , FILENAME = ‘E:\MSSQL\Log\modellog.ldf’ )

— MSDB DB
ALTER DATABASE MSDB MODIFY FILE( NAME = MSDBData , FILENAME = ‘E:\MSSQL\Data\MSDBData.mdf’ )
ALTER DATABASE MSDB MODIFY FILE( NAME = MSDBLog , FILENAME = ‘E:\MSSQL\Log\MSDBLog.ldf’ )

— TEMPDB
ALTER DATABASE TEMPDB MODIFY FILE( NAME = TempDev , FILENAME = ‘E:\MSSQL\Data\TEMPDB.mdf’ )
ALTER DATABASE TEMPDB MODIFY FILE( NAME = TempLog , FILENAME = ‘E:\MSSQL\Log\TEMPLog.ldf’ )

5 Open CMD prompt & run the following:-
NET STOP MSSQLSERVER

6 Move the following files from the location identified in 3 to the new locations identified in 4

7 Launch SQL Server Configuration Manager (Start-> Programs -> SQL 2012 ->Config Tools -> SQL Server Configuration Manager

Select SQL Server Services & right click on SQL Server (MSSQLSERVER) & select Properties

Select each of the above values for master.mdf & mastlog.ldf files & change them to be the location identified in 4
** Also change the ERRORLOG value to be the MSSQL\Log location.
Select Apply & then OK to close the properties window. Select OK to confirm changes will not take effect until server is restarted.

8 Go to CMD prompt again & type the following command
NET START MSSQLSERVER
** SQL Server should be started now.

9 Execute the following SQL to confirm that all the DB’s have moved
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files

New SQL Server Ireland User Group website

The SQL Server Ireland User Group now has it’s own user group blog at http://ssiug.wordpress.com

The first user group meeting details are now published there & I will continue to publish upcoming user group meetings, SQL Server notices, & other related news & events here.

I will continue to blog on other topics, including about SQL Server, here too.

%d bloggers like this: