IT Giant

SQL Server 6.5 Move Master/SystemDB Location

Microsoft has an extra sp_movedevice stored procedure to update sysdevices table and then you need use Enterprise manager to update registry info.

Relocate the SQL 6.5 Master.dat file to a directory path that does not have spaces. To do this, perform the following steps:

If SQL Server 7.0 is on the same computer as SQL 6.5, version switch to 6.5.
Match the SQL Server 6.5 device names with their paths using the sp_helpdevice stored procedure.

Install the stored procedure sp_movedevice found in SQL Server 6.5 Books Online.

Use master
Go

Exec sp_configure "allow",1
RECONFIGURE WITH OVERRIDE
go

CREATE PROCEDURE sp_movedevice @@devname varchar(30), @@newpath varchar(255)
AS
BEGIN
EXEC sp_configure "allow",1
RECONFIGURE WITH OVERRIDE
BEGIN TRAN
UPDATE sysdevices set phyname = @@newpath WHERE name = @@devname
IF @@rowcount <> 1
BEGIN
PRINT "**********************ERROR**********************"
PRINT "Moved failed - more or less than one row affected"
PRINT "**********************ERROR**********************"
ROLLBACK TRAN
END
ELSE
BEGIN
PRINT "Device moved successfully"
PRINT "Change will take effect next time you start SQL Server"
COMMIT TRAN
END
EXEC sp_configure "allow",0
RECONFIGURE WITH OVERRIDE
END
Go

EXEC sp_configure "allow",0
RECONFIGURE WITH OVERRIDE

Run sp_movedevice master, ‘new path’ as follows:

exec sp_movedevice MASTER, 'D:\MSSQL\DATA\MASTER.DAT'
reconfigure

To ensure that the changes made were the ones intended, run the sp_helpdevice stored procedure to confirm that the new location is correct.
Open the SQL Server 6.5 Enterprise Manager, highlight the server, click Server, click SQL Server, and then clickConfigure. Modify the “Master Database Path” option on the Server Options tab of this dialog box to look like this:
D:\MSSQL\DATA\MASTER.DAT
WARNING: After completing this step, you must make sure your parameters are in the correct order or you may run into another possible hindrance when using the Upgrade Wizard. To check parameter order, click the PARAMETERSbutton. In the window titled Existing Parameters make sure the sequence of vertical order for your server parameters matches the following:
‑dD:\MSSQL\DATA\MASTER.DAT
‑eD:\MSSQL\LOG\ERRORLOG
For additional information concerning this problem, see the following article in the Microsoft Knowledge Base:
231988 BUG: 7.0 Upgrade Loops When 6.5 Parameters Are Reversed
Stop the SQL Server 6.5 services.
Move the Master.dat file to the new location (for example D:\Mssql\Data).
Start the SQL Server 6.5 services.
Check the SQL Server 6.5 error log to see if all the databases have recovered or if there are any marked as suspect. Correct any errors. If there are no errors, move to the next step.

More details: http://support.microsoft.com/KB/246181

February 6, 2014 – 2:40 PM | By Ken | Posted in Database Technology, Microsoft SQL Server | Comments (0)

MSSQL DMVs — Tempdb Related

The total number of free pages and total free space in megabytes (MB) available in all files in tempdb SELECT SUM(unallocated_extent_page_count) AS [free pages], (SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB] FROM sys.dm_db_file_space_usage; The Amount Space Used by the Version Store SELECT SUM(version_store_reserved_page_count) AS [version store pages used], (SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB] FROM sys.dm_db_file_space_usage; […]

August 13, 2012 – 3:02 PM | By Ken | Posted in Database Technology, Microsoft SQL Server | Comments (0)

MSSQL DMVS — Transaction Locks

Sometimes we wish to know what locks are being held by the transaction. Also it would be great to know what SPID owns the transaction, on which objects the locks are being held, what SQL statement caused the locks, etc… SELECT L.request_session_id AS SPID, DB_NAME(L.resource_database_id) AS DatabaseName, O.Name AS LockedObjectName, P.object_id AS LockedObjectId, L.resource_type AS […]

June 13, 2011 – 3:50 PM | By Ken | Posted in Database Technology, Microsoft SQL Server | Comments (0)

Partitioning — Two Queries

Query 1: Find existing partitions details in seconds select distinct p.object_id, index_name = i.name, index_type_desc = i.type_desc, partition_scheme = ps.name, data_space_id = ps.data_space_id, function_name = pf.name, function_id = ps.function_id from sys.partitions p inner join sys.indexes i on p.object_id = i.object_id and p.index_id = i.index_id inner join sys.data_spaces ds on i.data_space_id = ds.data_space_id inner join sys.partition_schemes […]

January 31, 2011 – 2:33 PM | By Ken | Posted in Database Technology, Microsoft SQL Server | Comments (0)

MSSQL DMVs — SQL Statements Text

It is always good to see what was the last query of any sessions. SELECT cs.session_id, cs.connect_time, cs.last_read, cs.last_write, sp.loginame, sp.hostname, TEXT FROM sys.dm_exec_connections cs INNER JOIN sys.sysprocesses sp ON cs.session_id = sp.spid CROSS APPLY sys.dm_exec_sql_text(cs.most_recent_sql_handle) AS ST where sp.dbid = db_id(‘DB’) The following query will tell you what’s the running at the moment. SELECT […]

November 17, 2010 – 3:05 PM | By Ken | Posted in Database Technology, Microsoft SQL Server | Comments (0)

MSSQL DMVs — Worst Performing Queries

Following query will return TOP 100 worst performing SQL Statement with the name of object that contain those statements e.g. Stored Procedure, Trigger and Function. Current database context will be used so change the database before executing query. The result will be sorted by CPU time in descending order. SELECT TOP 100 [Object_Name] = object_name(st.objectid), creation_time, […]

October 12, 2010 – 9:32 AM | By Ken | Posted in Database Technology, Microsoft SQL Server | Comments (0)

Bible — SQL Server Trace Flags Lookup

Here is the comprehensive list of most traceflags known. 🙂 flag Trace Flag Description (underlined are sp_configure’able) ‑1 Sets trace flags for all connections. Used only with DBCC TRACEON and TRACEOFF. The setting of the Trace flag ‑1 is not visible with DBCC TRACESTATUS command, but work without problems. 105 SQL Server 6.5 you can use maximum […]

October 7, 2010 – 11:07 AM | By Ken | Posted in Database Technology, Microsoft SQL Server | Comments (0)

MSSQL DMVs — Backup Progress Status Report

In SQL2000, we can’t find out the backup progress unless we give the stats parameters in the backup statement. But in SQL 2005/2008, with DMVs we are capable of find out and now we can answer most of user questions like “When the backup will finish? How far we’ve gone through the whole backup?”. SELECT […]

September 9, 2010 – 10:26 AM | By Ken | Posted in Database Technology, Microsoft SQL Server | Tagged Backup, DMV, SQL Server | Comments (0)

Identifying Overlapping Statistics

Having both auto created statistics and index statistics on the same column caused the query optimizer to choose a different — and less than optimal — execution plan than when only the index statistics existed. From Author : Kendal Van Dyke http://sqlserverpedia.com/blog/sql—server—2005/tuning—tip—identifying—overlapping—statistics/ WITH autostats ( object_id, stats_id, name, column_id ) AS ( SELECT sys.stats.object_id , sys.stats.stats_id […]

September 9, 2010 – 10:12 AM | By Ken | Posted in Database Technology, Microsoft SQL Server | Tagged Overlap, SQL Server, Statistics | Comments (0)

MSSQL DMVs — CPU Related

Here are a few queries that I’ve collected that can be used to find out any CPU related issues. 1. Top 20 HIGH CPU batches. SELECT TOP 20 (total_worker_time * 1.0) / 1000000 AS CPU_Utilized_in_Seconds, convert(varchar(255), text) as TEXT FROM sys.dm_exec_query_stats CROSS APPLY sys.dm_exec_sql_text(sql_handle) order by (total_worker_time * 1.0) / 1000000 desc 2. Top 20 […]

July 21, 2010 – 9:36 AM | By Ken | Posted in Database Technology, Microsoft SQL Server | Comments (0)