Microsoft SQL Server

SQL Server 6.5 Move Master/​SystemDB Location

Microsoft has an extra sp_​movedevice stored pro­ced­ure to update sys­devices table and then you need use Enter­prise man­ager to update registry info.

Relo­cate the SQL 6.5 Master.dat file to a dir­ect­ory path that does not have spaces. To do this, per­form the fol­low­ing steps:

  1. If SQL Serv­er 7.0 is on the same com­puter as SQL 6.5, ver­sion switch to 6.5.
  2. Match the SQL Serv­er 6.5 device names with their paths using the sp_​helpdevice stored procedure.
  3. Install the stored pro­ced­ure sp_​movedevice found in SQL Serv­er 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
  4. Run sp_​movedevice mas­ter, ‘new path’ as fol­lows:
    exec sp_movedevice MASTER, 'D:\MSSQL\DATA\MASTER.DAT'
    reconfigure
  5. To ensure that the changes made were the ones inten­ded, run the sp_​helpdevice stored pro­ced­ure to con­firm that the new loc­a­tion is correct.
  6. Open the SQL Serv­er 6.5 Enter­prise Man­ager, high­light the serv­er, click Serv­er, click SQL Serv­er, and then clickCon­fig­ure. Modi­fy the “Mas­ter Data­base Path” option on the Serv­er Options tab of this dia­log box to look like this:
    D:\MSSQL\DATA\MASTER.DAT
    WARNING: After com­plet­ing this step, you must make sure your para­met­ers are in the cor­rect order or you may run into anoth­er pos­sible hindrance when using the Upgrade Wiz­ard. To check para­met­er order, click the PARAMETERSbut­ton. In the win­dow titled Exist­ing Para­met­ers make sure the sequence of ver­tic­al order for your serv­er para­met­ers matches the following:
    ‑dD:\MSSQL\DATA\MASTER.DAT
    ‑eD:\MSSQL\LOG\ERRORLOG
    For addi­tion­al inform­a­tion con­cern­ing this prob­lem, see the fol­low­ing art­icle in the Microsoft Know­ledge Base:
    231988 BUG: 7.0 Upgrade Loops When 6.5 Para­met­ers Are Reversed
  7. Stop the SQL Serv­er 6.5 services.
  8. Move the Master.dat file to the new loc­a­tion (for example D:\Mssql\Data).
  9. Start the SQL Serv­er 6.5 services.
  10. Check the SQL Serv­er 6.5 error log to see if all the data­bases have recovered or if there are any marked as sus­pect. Cor­rect any errors. If there are no errors, move to the next step.

More details: http://​sup​port​.microsoft​.com/​K​B​/​2​4​6​181


MSSQL DMVs — Tempdb Related

The total num­ber of free pages and total free space in mega­bytes (MB) avail­able in all files in tem­p­db 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 Ver­sion Store SELECT SUM(version_store_reserved_page_count) AS [ver­sion store pages used], (SUM(version_store_reserved_page_count)*1.0/128) AS [ver­sion store space in MB] FROM sys.dm_db_file_space_usage; […]


MSSQL DMVS — Transaction Locks

Some­times we wish to know what locks are being held by the trans­ac­tion. Also it would be great to know what SPID owns the trans­ac­tion, on which objects the locks are being held, what SQL state­ment caused the locks, etc… SELECT L.request_session_id AS SPID, DB_NAME(L.resource_database_id) AS Data­base­Name, O.Name AS Locke­dOb­ject­Name, P.object_id AS Locke­dOb­ject­Id, L.resource_type AS […]


Partitioning — Two Queries

Query 1: Find exist­ing par­ti­tions details in seconds select dis­tinct 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 […]


MSSQL DMVs — SQL Statements Text

It is always good to see what was the last query of any ses­sions. 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 fol­low­ing query will tell you what’s the run­ning at the moment. SELECT […]


MSSQL DMVs — Worst Performing Queries

Fol­low­ing query will return TOP 100 worst per­form­ing SQL State­ment with the name of object that con­tain those state­ments e.g. Stored Pro­ced­ure, Trig­ger and Func­tion. Cur­rent data­base con­text will be used so change the data­base before execut­ing query. The res­ult will be sor­ted by CPU time in des­cend­ing order. SELECT TOP 100 [Object_​Name] = object_name(st.objectid), creation_time, […]


Bible — SQL Server Trace Flags Lookup

Here is the com­pre­hens­ive list of most trace­flags known. 🙂 flag Trace Flag Descrip­tion (under­lined are sp_configure’able) ‑1 Sets trace flags for all con­nec­tions. Used only with DBCC TRACEON and TRACEOFF. The set­ting of the Trace flag ‑1 is not vis­ible with DBCC TRACESTATUS com­mand, but work without prob­lems. 105 SQL Serv­er 6.5 you can use maximum […]


MSSQL DMVs — Backup Progress Status Report

In SQL2000, we can­’t find out the backup pro­gress unless we give the stats para­met­ers in the backup state­ment. But in SQL 2005/​​2008, with DMVs we are cap­able of find out and now we can answer most of user ques­tions like “When the backup will fin­ish? How far we’ve gone through the whole backup?”. SELECT […]


Identifying Overlapping Statistics

Hav­ing both auto cre­ated stat­ist­ics and index stat­ist­ics on the same column caused the query optim­izer to choose a dif­fer­ent — and less than optim­al — exe­cu­tion plan than when only the index stat­ist­ics exis­ted. From Author : Kend­al Van Dyke http://​sqlserver​pedia​.com/​b​l​o​g​/​s​q​l​—​s​e​r​v​e​r​—​2​0​0​5​/​t​u​n​i​n​g​—​t​i​p​—​i​d​e​n​t​i​f​y​i​n​g​—​o​v​e​r​l​a​p​p​i​n​g​—​s​t​a​t​i​s​t​i​cs/ WITH auto­stats ( object_​id, stats_​id, name, column_​id ) AS ( SELECT sys.stats.object_id , sys.stats.stats_id […]


MSSQL DMVs — CPU Related

Here are a few quer­ies that I’ve col­lec­ted 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 […]