Month: September 2019

No comments

SQL SERVER – Get Table Partition Information

  By ,

Here is a script that retrieves table partition information

SELECT
	pf.name as PartitionFunction,
	ds.name AS PartitionScheme, 
	p.partition_number AS PartitionNumber, 
	CASE pf.boundary_value_on_right WHEN 1 THEN 'RIGHT' ELSE 'LEFT' END AS PartitionFunctionRange, 
	CONVERT(BIGINT, prv_left.value) AS LowerBoundaryValue, 
	CONVERT(BIGINT, prv_right.value) AS UpperBoundaryValue, 
	fg.name AS FileGroupName,
	p.[row_count] as TotalRows,
	CONVERT(DECIMAL(12,2), p.reserved_page_count*8/1024.0) as ReservedSpaceMB,
	CONVERT(DECIMAL(12,2), p.used_page_count*8/1024.0) as UsedSpaceMB
FROM
	sys.dm_db_partition_stats AS p (NOLOCK)
	INNER JOIN sys.indexes AS i (NOLOCK) ON i.[object_id] = p.[object_id] AND i.index_id = p.index_id
	INNER JOIN sys.data_spaces AS ds (NOLOCK) ON ds.data_space_id = i.data_space_id
	INNER JOIN sys.partition_schemes AS ps (NOLOCK) ON ps.data_space_id = ds.data_space_id
	INNER JOIN sys.partition_functions AS pf (NOLOCK) ON pf.function_id = ps.function_id
	INNER JOIN sys.destination_data_spaces AS dds2 (NOLOCK) ON dds2.partition_scheme_id = ps.data_space_id AND dds2.destination_id = p.partition_number
	INNER JOIN sys.filegroups AS fg (NOLOCK) ON fg.data_space_id = dds2.data_space_id
	LEFT OUTER JOIN sys.partition_range_values AS prv_left (NOLOCK) ON ps.function_id = prv_left.function_id AND prv_left.boundary_id = p.partition_number - 1
	LEFT OUTER JOIN sys.partition_range_values AS prv_right (NOLOCK) ON ps.function_id = prv_right.function_id AND prv_right.boundary_id = p.partition_number
WHERE
	OBJECTPROPERTY(p.[object_id], 'IsMSShipped') = 0 AND
	-- OBJECT_NAME(p.[object_id]) = 'partitioned_table' AND
	p.index_id=1
ORDER BY p.partition_number;

Cheers!

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter
No comments

SQL SERVER – Move Data Files

  By , ,

Here is how you can move data files in SQL Server.

1. Retrieve DB LogicalName and current location.

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
SELECT name, physical_name, state_desc AS OnlineStatus
FROM sys.master_files
WHERE database_id = DB_ID(N'AdventureWorks2014')
GO
SELECT name, physical_name, state_desc AS OnlineStatus FROM sys.master_files WHERE database_id = DB_ID(N'AdventureWorks2014') GO
SELECT name, physical_name, state_desc AS OnlineStatus
FROM sys.master_files  
WHERE database_id = DB_ID(N'AdventureWorks2014')  
GO

2. Set Database offline.

ALTER DATABASE AdventureWorks2014 SET OFFLINE;
GO

3. If your database it is taking too slow to become offline, you should kill all session ID’s that are connected to the database (including the sleeping connections). I have a script below to do just that. Just specify the database name for the @DBName variable.

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
DECLARE @DBName VARCHAR(50) = 'AdventureWorks2014'
DECLARE @sql NVARCHAR (500)
DECLARE @spid INT
DECLARE @numberOfRowsToKill INT
DECLARE @i INT = 0
DECLARE @sessionID INT
SET NOCOUNT ON
SET @sql = 'sp_who'
IF OBJECT_ID('tempdb..#spids') IS NOT NULL
DROP TABLE #spids
CREATE TABLE #spids
(
spid INT,
ecid INT,
status VARCHAR(30),
loginname VARCHAR(30),
HostName VARCHAR(50),
Blk INT,
DBName VARCHAR(30),
Command VARCHAR(MAX),
RequestID INT
)
INSERT INTO #spids
EXEC (@sql)
SELECT @numberOfRowsToKill = COUNT(*) FROM #spids WHERE DBName = @DBName
IF @i < @numberOfRowsToKill
WHILE @i < @numberOfRowsToKill
BEGIN
SELECT TOP 1 @sessionID = spid from #spids WHERE DBName = @DBName
SET @sql = 'kill ' + CAST(@sessionID AS NVARCHAR(10))
EXEC (@sql)
PRINT 'SPID ' + CAST(@sessionID AS VARCHAR) + ' has been killed'
DELETE FROM #spids WHERE spid = @sessionID
SET @i = @i + 1
END
ELSE
PRINT 'There are no more sessions connected to ' + @DBName + ' database.'
DECLARE @DBName VARCHAR(50) = 'AdventureWorks2014' DECLARE @sql NVARCHAR (500) DECLARE @spid INT DECLARE @numberOfRowsToKill INT DECLARE @i INT = 0 DECLARE @sessionID INT SET NOCOUNT ON SET @sql = 'sp_who' IF OBJECT_ID('tempdb..#spids') IS NOT NULL DROP TABLE #spids CREATE TABLE #spids ( spid INT, ecid INT, status VARCHAR(30), loginname VARCHAR(30), HostName VARCHAR(50), Blk INT, DBName VARCHAR(30), Command VARCHAR(MAX), RequestID INT ) INSERT INTO #spids EXEC (@sql) SELECT @numberOfRowsToKill = COUNT(*) FROM #spids WHERE DBName = @DBName IF @i < @numberOfRowsToKill WHILE @i < @numberOfRowsToKill BEGIN SELECT TOP 1 @sessionID = spid from #spids WHERE DBName = @DBName SET @sql = 'kill ' + CAST(@sessionID AS NVARCHAR(10)) EXEC (@sql) PRINT 'SPID ' + CAST(@sessionID AS VARCHAR) + ' has been killed' DELETE FROM #spids WHERE spid = @sessionID SET @i = @i + 1 END ELSE PRINT 'There are no more sessions connected to ' + @DBName + ' database.'
DECLARE @DBName VARCHAR(50) = 'AdventureWorks2014'
DECLARE @sql NVARCHAR (500)
DECLARE @spid INT
DECLARE @numberOfRowsToKill INT
DECLARE @i INT = 0
DECLARE @sessionID INT

SET NOCOUNT ON

SET @sql = 'sp_who'

IF OBJECT_ID('tempdb..#spids') IS NOT NULL
	DROP TABLE #spids

CREATE TABLE #spids
(
	spid INT,
	ecid INT,
	status VARCHAR(30),
	loginname VARCHAR(30),
	HostName VARCHAR(50),
	Blk INT,
	DBName VARCHAR(30),
	Command VARCHAR(MAX),
	RequestID INT
)

INSERT INTO #spids
EXEC (@sql)


SELECT @numberOfRowsToKill = COUNT(*) FROM #spids WHERE DBName = @DBName

IF  @i < @numberOfRowsToKill
	WHILE @i < @numberOfRowsToKill
	BEGIN
		SELECT TOP 1 @sessionID = spid from #spids WHERE DBName = @DBName
		SET @sql =  'kill ' + CAST(@sessionID AS NVARCHAR(10))

		EXEC (@sql)
		PRINT 'SPID ' + CAST(@sessionID AS VARCHAR) + ' has been killed'

		DELETE FROM #spids WHERE spid = @sessionID
		SET @i = @i + 1
	END
ELSE
	PRINT 'There are no more sessions connected to ' + @DBName + ' database.'


Preview(opens in a new tab)

4. Move the physical files using Powershell. Run Powershell as Administrator and run the following command.

Move-Item -Path "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\CreditData.mdf" -Destination "C:\SQL\DATA"

Move-Item -Path "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\CreditLog.ldf" -Destination "C:\SQL\DATA"

5. Modify the file name. Specify the new file path.

ALTER DATABASE AdventureWorks2014 MODIFY FILE (NAME = 'LogicalName'	,FILENAME = 'C:\SQL\DATA\CreditData.mdf')
GO

ALTER DATABASE AdventureWorks2014 MODIFY FILE (NAME = 'LogicalName',FILENAME = 'C:\SQL\DATA\CreditLog.ldf')
GO

6. Set database online.

ALTER DATABASE AdventureWorks2014 SET ONLINE;
GO

7. Verify that the change is okay.

SELECT name, physical_name, state_desc AS OnlineStatus
FROM sys.master_files  
WHERE database_id = DB_ID(N'AdventureWorks2014')  
GO

Cheers!

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter
No comments

SQL SERVER – The logical file name is already in use

  By , ,

I just encountered the error below.

Msg 1828, Level 16, State 4, Line 3
The logical file name “OTDB_FG_WRTestResults_01” is already in use. Choose a different

I was able to solve it by executing the following scripts. Essentially, you just have to rename the logical file name to different one, then rename it back to your preferred name.

ALTER DATABASE [DBName] MODIFY FILE (NAME=N'OTDB_FG_WRTestResults_01', NEWNAME=N'OTDB_FG_WRTestResults_01_Temp')

The file name ‘OTDB_FG_WRTestResults_03_Temp’ has been set.

Then change it back to the original name

ALTER DATABASE [OptimalTestDB] MODIFY FILE (NAME=N'OTDB_FG_WRTestResults_01_Temp', NEWNAME=N'OTDB_FG_WRTestResults_01')

Done.

Cheers!