Microsoft SQL Server

Microsoft SQL Server

How to move SQL 2016 TEMP2 TempDB

Moving your TempDB locations should be performed outside production. Check Current Temp DB Locations: SELECT name, physical_name AS CurrentLocation FROM sys.master_files WHERE database_id = DB_ID(N'tempdb'); GO Move the Temp2 or any additional Temp databases: USE master; GO ALTER DATABASE tempdb MODIFY FILE (NAME = temp2, FILENAME = 'T:\MSSQL\DATA\tempdb_mssql_2.ndf'); GO Check Current Temp DB Locations again: SELECT name, physical_name AS CurrentLocation FROM sys.master_files WHERE Read more [...]

SQL Express 2008 R2 Management Studio installation

Issue: Adding Management Studio to an existing SQL Express 2008 R2 instance can be extremely difficult and frustrating. Solution: The following links can be used to download SQL Management Studio 2008 R2 with SP1 or with SP2.  If you are currently running SQL Express 2008 R2 SP3, then you will need to install management studio 2008 R2 with SP2, and then apply SQL 2008 R2 SP3. SSMS 2008 R2 with SP1: https://www.microsoft.com/en-us/download/details.aspx?id=26727 SSMS 2008 R2 with SP2: https://www.microsoft.com/en-us/download/details.aspx?id=30438 SQL Read more [...]

How to disable Auto Shrink all SQL databases

You can use the following code in query generated to disable auto shrink on all SQL databases.  This code will generate the actual scripts needed, which you then execute.  See the comment section above the code snipped for instructions. /* This will script output the needed commands for you */ /* Execute it in query analyzer, which will generates all the commands */ /* do a Select All, Copy, then Paste into query analyzer and execute */ SELECT 'ALTER DATABASE ' + QUOTENAME(name) + ' SET AUTO_SHRINK Read more [...]