Today I urgently needed to move tempdb from one drive to another to free up space. So I googled to find a script and found this one by Brent Ozar. It has been added to StackExchange and refined a bit.
DECLARE @newDriveAndFolder VARCHAR(8000);
SET @newDriveAndFolder = 'Z:\YourTempDBfolder';
SELECT [name] AS [Logical Name]
,physical_name AS [Current Location]
,state_desc AS [Status]
,size/1024 AS [Size(MB)]
,'ALTER DATABASE tempdb MODIFY FILE (NAME = ' + QUOTENAME(f.[name])
+ CHAR(9) /* Tab */
+ ',FILENAME = ''' + @newDriveAndFolder + CHAR(92) /* Backslash */ + f.[name]
+ CASE WHEN f.[type] = 1 /* Log */ THEN '.ldf' ELSE '.mdf' END + ''''
AS [Create new TempDB files]
FROM sys.master_files f
WHERE f.database_id = DB_ID(N'tempdb')
ORDER BY f.[type];
Change the newDriveAndFolder variable and run the script and it will generate the T-SQL statements you need to move them.
ALTER DATABASE tempdb MODIFY FILE (NAME = [temp2] ,FILENAME = ‘F:\MSSQLDATA\temp2.mdf’);
ALTER DATABASE tempdb MODIFY FILE (NAME = [temp3] ,FILENAME = ‘F:\MSSQLDATA\temp3.mdf’);
ALTER DATABASE tempdb MODIFY FILE (NAME = [temp4] ,FILENAME = ‘F:\MSSQLDATA\temp4.mdf’);
ALTER DATABASE tempdb MODIFY FILE (NAME = [temp5] ,FILENAME = ‘F:\MSSQLDATA\temp5.mdf’);
Worked like a charm
Today I tried to do a restore of database to an Azure DB in a Dynamics 365 for Operations environment. When I ran SQLPackage.exe I got the following error:
‘Unable to connect to master or target server ‘AxDB_New’. You must have a user with the same password in master or target server ‘AxDB_New’
The issue here is that the version of SQL Management Studio that is provided in the Azure VM is version 16 and Azure SQL requires version 17… with that background I am not convinced that the error message is totally relevant…
The solution is to upgrade Management Studio to the latest Version.
Today I am setting up a proof of concept for the retail solution in AX 2012 R3. As a base I am using the Microsoft AX 2012 R3 demo VM deployed to Azure using LCS
When Trying to do a full data sync I got the following error:
”The target principal name is incorrect. Cannot generate SSPI context”
SSPI context has to do with Service Principal Name in Active Directory. The Service Account for SQL (contoso\sqlsvc) needs to have SPN set like this:
setspn –A MSSQLSvc/AX2012R2A:1433 contoso\sqlsvc
setspn –A MSSQLSvc/AX2012R2A.contoso.com:1433 contoso\sqlsvc
Where AX2012R2A and AX2012R2A.contoso.com are hostname and FQDN for the AX demo VM.
In my case I had duplicate FQDN and no hostname. I cleaned out the duplicates and added the hostname.
This morning we had an incident with a customer who during the night had failed over their SQL cluster during the night and then failed it back again and now AX had stopped working. The error was this:
The problem is that when SQL is restarted the permission on tempdb is reset. AX solves this is a “special” way. When AX is installed the install created a stored procedure in master on the SQL server which it calls to set the permissions in case they get lost.
These was unfortunately missing on one of the nodes so I used Management Studio to script them out and the create them on the node where they were missing
When you install Microsoft SQL Server the install generates a configuration file. This is great if you want to create an unattended install of SQL. If you want to do a second install (or third or fourth…) just run:
and you get the same install again.
Today I wanted to do the same with SQL express… but no file was created… Hmmm…
Apparently you need to start the install using this:
Setup.exe /ACTION=INSTALL /UIMODE=Normal
Today I was looking at a malfunctioning Sharepoint (for enterprise Portar) the problem was that it could not find the database for either configuration or content which meant that it was pretty much screwed. I am not the one who installed this so I did not know there to start looking. Since I am not very proficient in Sharepoint I decided to start from the beginning… Where should the database be located? I tried launching Sharepoint Central Administration… did not work. Tried launching the Sharepoint Powershell Admin console… Could not find farm… I looked in the eventlog… no mention of any SQL Server name…
So I searched around abit online and found the most basic setting… where does Sharepoint expect to find:
SharePoint Server config database connection string location for SharePoint Server 2007
My Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\12.O\Secure\ConfigDb
SharePoint Server config database connection string location for SharePoint Server 2010
My Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\14.O\Secure\ConfigDb
And for SharePoint 2013…
My Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\15.O\Secure\ConfigDb
So this meant that I found the SQL server and could continue troubleshooting…