Data and Log File Move to New Location (Move Script)

Below is the script we can use to move the data and log files for the databases except master.


DECLARE
    @counter    INT = 1,
    @max        INT = 0,
    @NEW_DATA_PATH VARCHAR(500)='E:\XXXX\Data',
    @NEW_LOG_PATH VARCHAR(500)='F:\XXXX\Log'
  
DECLARE @FILE_LIST TABLE (
    [ID]    int IDENTITY,
    [DBNAME] VARCHAR(100),
    [LNAME]   VARCHAR(100),
    [PNAME]   VARCHAR(500),
    [TYPE_DESC]  VARCHAR(50)
    )  
INSERT INTO @FILE_LIST
SELECT n.NAME,f.NAME,f.PHYSICAL_NAME,f.TYPE_DESC
FROM MASTER.SYS.MASTER_FILES f,MASTER.SYS.DATABASES n WHERE n.NAME not in('MASTER') 
and f.DATABASE_ID=n.DATABASE_ID

SELECT @max = COUNT([ID]) FROM @FILE_LIST

WHILE @counter <= @max
BEGIN

 DECLARE @FILE_TYPE VARCHAR(100),
         @DBNAME VARCHAR(100),
         @LOGICAL_NAME VARCHAR(100),
         @PHYSICAL_NAME VARCHAR(500) ;
          
 SELECT @FILE_TYPE=TYPE_DESC,@DBNAME=[DBNAME],@LOGICAL_NAME=[LNAME],
 @PHYSICAL_NAME=SUBSTRING([PNAME],LEN([PNAME])-CHARINDEX('\',REVERSE([PNAME]))+2,LEN([PNAME])-1) 
 FROM @FILE_LIST WHERE [ID] = @counter
    
    IF(@FILE_TYPE='ROWS')
    BEGIN
    --ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name, FILENAME = 'new_path\os_file_name' );
        --PRINT @FILE_TYPE+','+@DBNAME+','+@LOGICAL_NAME+','+@PHYSICAL_NAME
        PRINT 'USE MASTER 
               GO 
               ALTER DATABASE '+@DBNAME+' MODIFY FILE ( NAME = '+@LOGICAL_NAME+', FILENAME = '''+@NEW_DATA_PATH+'\'+@PHYSICAL_NAME+''')
               GO'
  END
  ELSE
   PRINT 'USE MASTER 
          GO 
          ALTER DATABASE '+@DBNAME+' MODIFY FILE ( NAME = '+@LOGICAL_NAME+', FILENAME = '''+@NEW_LOG_PATH+'\'+@PHYSICAL_NAME+''')
          GO'
     SET @counter = @counter + 1
      
 END

Invalid object name 'master.dbo.spt_values'. (Microsoft SQL Server, Error: 208)

Solution : https://www.mssqltips.com/sqlservertip/3694/fix-invalid-object-name-masterdbosptvalues-when-viewing-sql-server-database-properties/


We need to import  spt_values from other server using import and export method.

Other Sources:

https://blogs.msdn.microsoft.com/suhde/2009/04/05/invalid-object-name-master-dbo-spt_values-microsoft-sql-server-error-208/

https://blog.sqlauthority.com/2017/07/21/sql-server-invalid-object-name-master-dbo-spt_values-management-studio/

Delete Maintenance Plans

We need to perform the below steps to delete the jobs or maintenance plans in such cases from sysmaintplan_log table or fromsysmaintplan_subplans table .

Step 1: 
Execute the below query to obtain the Maintenance plan name and Id SELECT NAME, ID FROM MSDB..SYSMAINTPLAN_PLANS 
Step 2: 
Replace the Id obtained from Step 1 into the below query and delete the entry from log tableDELETE FROM SYSMAINTPLAN_LOG WHERE PLAN_ID=' ' 
Step 3: 
Replace the Id obtained from Step 1 into the below query and delete the entry from subplans table as shown below,DELETE FROM SYSMAINTPLAN_SUBPLANS WHERE PLAN_ID = ' '
Step 4: 
Finally delete the maintenance plan using the below query where ID is obtained from Step1DELETE FROM SYSMAINTPLAN_PLANS WHERE ID = ' '
Step 5: 
Check and delete the jobs from SSMS if it exists.

Many thanks to kapil

https://social.msdn.microsoft.com/forums/sqlserver/en-US/ae6f809f-ef74-4534-8327-b7b120a2d700/unable-to-delete-a-sql-2005-maintenance-plan

Cannot detach a suspect or recovery pending database. It must be repaired or dropped.

Note: Below method of removing database is only when database shows RECOVERY_PENDING because of  MDF/LDF files missing.

Solution:

ALTER DATABASE DBNAME SET OFFLINE
GO
sp_detach_db 'DBNAME'
GO


In Other Cases Please Refer to (Please Note: Check with customer for dataloss)

https://dbamohsin.wordpress.com/2012/01 /23/cannot-detach-a-suspect-or-recovery-pending-database/

Prefer: Use latest backups available to restore the database.

SSRS Key Mangement Options

C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn>RSKeyMgmt /?
Microsoft (R) Reporting Services Key Manager
Version 11.0.2100.60 x86

Performs key management operations on a local report server.

  -e  extract           Extracts a key from a report server instance
  -a  apply             Applies a key to a report server instance
  -s  reencrypt         Generates a new key and reencrypts all encrypted
                        content
  -d  delete content    Deletes all encrypted content from a report server
                        database
  -l  list              Lists the report servers announced in the report server
                        database
  -r  installation ID   Remove the key for the specified installation ID
  -j  join              Join a remote instance of report server to the
                        scale-out deployment of the local instance
  -i  instance          Server instance to which operation is applied;
                        default is MSSQLSERVER
  -f  file              Full path and file name to read/write key.
  -p  password          Password used to encrypt or decrypt key.
  -m  machine name      Name of the remote machine to join to the
                        scale-out deployment
  -n  instance name     Name of the remote machine instance to join to the
                        scale-out deployment
  -u  user name         User name of an administrator on the machine to join to
                        the scale-out deployment.  If not supplied, the current
                        user is used.
  -v  password          Password of an administrator on the machine to join to
                        the scale-out deployment
  -t  trace             Include trace information in error message

To create a back-up copy of the report server encryption key:
RSKeyMgmt -e [-i <instance name>] -f  -p 

To restore a back-up copy of the report server encryption key:
RSKeyMgmt -a [-i <instance name>] -f  -p 

To reencrypt secure information using a new key:
RSKeyMgmt -s [-i <instance name>]

To reset the report server encryption key and delete all encrypted content:
RSKeyMgmt -d [-i <instance name>]

To list the announced report servers in the report server database:
RSKeyMgmt -l [-i <instance name>]

To remove a specific installation from a scale-out deployment:
RSKeyMgmt -r <installation ID> [-i <instance name>]

To join a remote machine to the same scale-out deployment as the local machine:
RSKeyMgmt -j [-i <local instance name>] -m <remote machine name>
          [-n <remote instance name>] [-u <user name> -v ]

SQL Server Cluster Uninstall Failed- rule instance removal failed

We recently faced this issue, as the server was migrated to virtual and accidentally windows cluster was removed as part of decommission. Box is left out with SQL Server Installation while uninstalling we hit the below error, once the node name was removed it went successful.

Resolution:

1. Remove the entry from regedit.

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.MSSQLSERVER\Cluster:ClusterName

2. Re-run the uninstall from control panel.

The EXECUTE permission was denied on the object 'xp_regread', database 'mssqlsystemresource', schema 'sys'.

Receiving 'xp_regread' error as soon as connected to SQL Server Management Studio.

OR

Execution of SSIS packages from SQL jobs fails with error: The EXECUTE permission was denied on the object 'xp_regread', database 'mssqlsystemresource', schema 'sys'

Solution:

To connect and to view the SQL Server Agent:

USE [master]
GO
GRANT EXECUTE ON xp_regread TO [public]
GO


To Execute Job:


-->

USE [master]

GO

GRANT EXECUTE ON xp_sqlagent_enum_jobs TO [public]

GO

Expanding VM HDD Size - Oracle VM Box

1. Open the command Prompt
2. CD C:\Program Files\Oracle\VirtualBox
3. VBoxmanage modifyhd D:\Linux\Windows\Wind.vhd --resize 30000
4. Exit from command prompt
5. Boot the VM and expand the HDD

SQL Server 2017 On Linux- RHEL

sql-server-linux-setup

Install SQL Server On Linux

How To Automate SQL Server Trace

How to automate SQL Server Trace:

============================

• Start SQL Profiler and select File > New Trace. Specify the events, columns, and filters you want in your trace.
• Start the trace and then stop it.
• Export the definition. Click File > Export > Script Trace Definition > For SQL Server 2005. ...
• Save the trace file.
• Open the file and create a stored procedure.
• Add below lines to sp to create trace with date and time

 declare @TrcFileName nvarchar(300) 

set @TrcFileName = N'C:\STARTTrace'+CAST(DATEPART(d,GETDATE()) AS varchar(2))+
CAST(DATEPART(M,GETDATE()) AS varchar(2))+CAST(DATEPART(YYYY,GETDATE()) AS varchar(4))
+CAST(DATEPART(HH,GETDATE()) AS varchar(2))+CAST(DATEPART(MI,GETDATE()) AS varchar(2)) 

exec @rc = sp_trace_create @TraceID output, 0, @TrcFileName, @maxfilesize, NULL 

• To make SP to start when sql server restarts:

USE MASTER
GO
EXEC SP_PROCOPTION SPNAME, 'STARTUP', 'ON'
GO

• To check SP which are running at start up:

SELECT ROUTINE_NAME
FROM MASTER.INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'ExecIsStartup') = 1

--see if trace is running

select * from sys.traces
--Mark Trace Store Proc startup option True
EXEC sp_procoption 'StoreProcName', 'startup', 'true';
--Verify start option value is 1
USE MASTER
GO
SELECT VALUE, VALUE_IN_USE, DESCRIPTION
FROM SYS.CONFIGURATIONS
WHERE NAME = 'scan for startup procs'
GO

Msg 19069, Level 16, State 1, Procedure sp_trace_create, Line 1 The trace file name is not valid because it contains a rollover file number (NNN in C:\file_NNN) while the trace rollover option is enabled.

In this case remove the _ (underscore) at the end of the filename.

To rollover files manually use 2 as a parameter in the script.


Lot of thanks to sources :
http://www.techbrothersit.com/search?q=sql+server+profiler
https://dbamohsin.wordpress.com/tag/file-rollover/

SQL Server 2005 - Insufficient memory or maximum allowed connections

Error:

The client was unable to establish a connection because of an error during connection initialization process before login. Possible causes include the following: the client tried to connect to an unsupported version of SQL Server; the server was too busy to accept new connections; or there was a resource limitation (insufficient memory or maximum allowed connections) on the server. (provider: Named Pipes Provider, error: 0 - No process is on the other end of the pipe.)

Solution:

SQL Services was in hung state and it was not allowing any connections to SQL Server

Restarted SQL Server Instance and issue resolved.

SSRS Email Subscription Option Missing

SSRS EMAIL SUBSCRIPTION OPTION 

If there is no EMAIL subscription option for the report:

1. Go to SSRS Report services Configuration
2. Choose EMAIL Settings

3. Configure and click Apply.
4. Now go back to report and check subscriptions.

Move System Databases

Here is the steps to move the system databases:
==================================
Master 

- Change the physical location of the files from startup parameters once the SQL Server is offline, then copy and move the MDF and LDF to new location.
- Now rename existing files as old on previous location
-Now bring SQL Server online

MSDB

SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID('msdb');
GO

USE master;
GO
ALTER DATABASE msdb
MODIFY FILE (NAME = MSDBData, FILENAME = 'Z:\Data\MSDBData.mdf');
GO
ALTER DATABASE msdb
MODIFY FILE (NAME = MSDBLog, FILENAME = 'Z:\Log\MSDBLog.ldf');
GO

Model

SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID(N'model');
GO

USE master;
GO
ALTER DATABASE model
MODIFY FILE (NAME = modeldev, FILENAME = 'Z:\Data\model.mdf');
GO
ALTER DATABASE model
MODIFY FILE (NAME = modellog, FILENAME = 'Z:\SystemDBs\Log\modellog.ldf');
GO

TEMPDB

Use master
GO
SELECT name AS [LogicalName] ,physical_name AS [Location] ,state_desc AS [Status]
FROM sys.master_files WHERE database_id = DB_ID(N'tempdb');
GO

 USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'Z:\TempDB\Data\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdb2, FILENAME = 'Z:\TempDB\Data\TempDB2.ndf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdb3, FILENAME = 'Z:\TempDB\Data\TempDB3.ndf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdb4, FILENAME = 'Z:\TempDB\Data\TempDB4.ndf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'Z:\TempDB\Log\templog.ldf');
GO


Resource  DB 

Can I change the Resource database path? Should I?
No

Thanks to :
https://blogs.msdn.microsoft.com/vsanil/2012/11/02/resource-database-common-questions/
https://sqlandme.com/2013/07/08/sql-server-how-to-change-sql-server-errorlog-location/

Unable to open the physical file , operating system error 5. (Access is denied)

Solution:

1. Add permissions to the disk contain Data and Log files
2. Add service account to login with SA
3. Take DB offline
    Alter database DB_NAME set offline with rollback immediate
4. Bring back the DB online
    Alter database DB_NAME set online with rollback immediate

this resolves the issue, else we need to try restarting SQL Services. Worst-case we need to restore the database with latest backup.