SQL - Move database files to a new location

• Moving the TEMPDB
• http://www.sqlteam.com/article/moving-the-tempdb-database

USE master
GO
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'C:\tempdb2005.mdf') GO
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'C:\tempdb2005.ldf') GO

• Moving a regular DB
• http://codesnip.net/move-data-files-sql-server-mdf-ldf/
• Detach db
• Move files to new location
• Attach db

use master
go
sp_detach_db MYDB
use master
go
sp_attach_db MYDB, 'D:\data\mydb.mdb', 'E:\log\mydb.ldb'

SQL - Find Long-Running Queries

Read this post

http://blog.sqlauthority.com/2009/01/02/sql-server-2008-2005-find-longest-running-query-tsql/


-----------------------------------------------------------------------------------------------------------------
Using SQL Profiler

1. Run SQL Profiler; searching for queries which have a high number of "Reads". This mostly indicates "Index Scans" which could cause the long runtime. Check the "Execution PLan" of those queries to optimize e.g. Indexes etc.. According to this you should check the Statistics and Fragmentation degree of that table and fix it if necessary

2. Checking for Blocks. I proceed as described here: http://dynamicsuser.net/blogs/stryk/archive/2008/11/03/blocks-amp-deadlocks-in-nav-with-sql-server.aspx

3. Check "Wait Statistics" to find out if theres an I/O problem, e.g. a problem with network or disk-subsystem. Therefore I look into the "sys.dm_os_wait_stats" DMV    

Original Post
http://www.sqlservercentral.com/Forums/Topic619606-360-1.aspx




Run this to see all running processes
exec sp_who2

Look for any task marked as runnable, with large numbers

run this to kill the process with SPID  58
kill 58

SQL Server - Queries, Views or Stored Procedures run slow after upgrade

Read this article

http://thomaslarock.com/2014/06/upgrading-to-sql-server-2014-a-dozen-things-to-check/

DBCC CHECKDB WITH DATA_PURITY;

DBCC UPDATEUSAGE(db_name);
EXEC sp_MSforeachtable @command1='UPDATE STATISTICS ? WITH FULLSCAN';

Dynamics NAV - Trying to post General Journal, but keep getting out of balance errors

Ensure that the journal lines are sorted correctly to allow the logic to check the balances correctly.
Or create separate journals for each unique posting date, or document number

Sort by

• Posting Date
• Document Number

If these are out of order, it will only calculate up to the first change of value.

SQL 2014 Linked Server - Create a linked server that points to a different sql server - alias

EXEC sp_addlinkedserver

@server=N'ALIAS',

@srvproduct=N'',

@provider=N'SQLNCLI',

@datasrc=N'MYSQLSERVER';

  EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=ALIAS',@useself=N'False',@locallogin=NULL,@rmtuser=N'sa',@rmtpassword='password'



Replace

• ALIAS with the name you want for the Linked server connection
• MYSQLSERVER with the name of the SQL server\instance you're linking to
• sa with the user name on the linked server
• password with the password on the linked server

Dynamics GP 2013 R2 - eOne Extender does not install - Keeps prompting to add new code, but never does

Cause
Extender thinks it's already installed, and will not install again

Resolution

• Edit the dynamics.set and remove the reference to extender
• decrement the number on the first line of the file by 1
• remove the line that says "Extender" and the number above it 3107 (do not leave an empty line)
• Look for the three lines at the bottom that refer to extender and any EXTUD files
• Delete the three lines (do not leave an empty line)
• Navigate to the Program Files\GP folder
• Delete all files named Extender, or EXTUD with any extension
• Run the Extender install
• Launch GP
• Include code
• It should install correctly

Dynamics GP - Next PO Number changes randomly

This is a bug.
Mariano resolves it here.

https://dynamicsgpblogster.wordpress.com/2009/01/31/why-does-my-next-document-number-change-randomly/