Tag: SQL server

Moving TempDB to a new location.

We had a process running on a particular SQL server virtual machine which was causing the TempDB file to grow exponentially and as a result caused the C: drive to run out of space. In this case the best solution was to move the location of the TempDB from the default location to a new location on the very large second Virtual drive.

The process is pretty straightforward.
[via]

Firstly locate the current file path of TempDB.
SELECT name, physical_name AS CurrentLocation
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
GO

Secondly perform the actual move with the following code. Modify it to choose new locations appropriate to your system.
USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'E:\TempDB\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'E:\TempDB\templog.ldf');
GO



Installing SQL Server 2012 Express on Windows Server 2012

As part of updating the server infrastructure I have built a replacement for our 8-9 year old backup server and a requirement of that is to have a local instance of SQL server to hold a small database of backed up objects and their state.
Moving the database from the old server to the new turned out to be more problematic than I first thought. The first part was that SQL Server 2012 Express didn’t want to install itself on Windows Server 2012 and I received the message below.

The following error has occurred: Error while enabling Windows feature: NetFx3, Error Code: -2146498298, Please try enabling feature: NetFx3 from Windows management tools and the run setup again.

The following error has occurred:
Error while enabling Windows feature: NetFx3, Error Code: -2146498298, Please try enabling feature: NetFx3 from Windows management tools and the run setup again.

Fine, I’ll just stop and go install the NetFx3 feature.

Except there isn’t a feature called NetFx3! In fact what SQL Server needs is the .NET Framework 3.5 Features, it was something that SQL Server 2008 R2 would install automatically if it detected that it wasn’t present, but apprently not not the 2012 Express version.

So go into Add Roles and Features and tick the box for the .NET Framework 3.5 Features and click next.
sqlserver2012installation error02

Unfortunately you hit another snag here as .NET Framework 3.5 is a feature on demand i.e. one that is copied to the disk when Server 2012 is installed so you need the installation disk and then direct the installer to that as the source.
sqlserver2012installation error03

sqlserver2012installation error04

Then installation is straightforward.
sqlserver2012installation error05

Unfortunately you can’t just continue the SQL Server installation at this point as you’ll get the following errors. You’ll need to start the installation process again, but this time it will be quick and easy.
sqlserver2012installation error06