Technology, sound and fruit cake

• Home
• Site tree
• Sion who?

Sion IT

To display all DBs except mirrors with size

EXEC SP_DATABASES

To display all databases including mirrors

SELECT name

	FROM sys.sysdatabases

	order by name

Alternatively this might work

EXEC sp_MSforeachdb 'USE ?; PRINT DB_NAME()'

Show name, size and files for all database and logfiles in descending size order

SELECT name, SIZE, filename FROM sysaltfiles ORDER BY SIZE DESC

USE Master
GO

ALTER DATABASE DatabaseName
SET Single_User WITH rollback immediate
GO

sp_renamedb 'DatabaseName' , 'DatabaseName_old'
GO

ALTER DATABASE DatabaseName_old
SET Multi_User WITH rollback immediate
GO

use tempdb
select name,(size*8/1000) as FileSizeMB, physical_name as FileLocation from sys.database_files

Backup the databases that you are moving and the Master database. Example here uses a fictitious Db called DS32

verify locations –

use ds32
go 
sp_helpfile
go

Detach the database

use ds32
	go 
	sp_helpfile
	go

move the actual files

Reattach the files

use master
   go
   sp_detach_db 'ds32'
   go

Verify the locations have changed

use master
  go
  sp_attach_db 'ds32','D:\DS32.mdf','D:\DS32_log.LDF'
  go

Done.

use vsphere

backup log DatabaseName with truncate_only

go

DBCC SHRINKFILE (N'DatabaseName_log' , 0, TRUNCATEONLY)

go

SELECT percent_complete, start_time, status, command, estimated_completion_time, cpu_time, total_elapsed_time  

FROM sys.dm_exec_requests