Posts

Showing posts from 2024

SSAS Cube or Database backup with Powershell command "Backup-AsDatabase"

Image
#Date Converion with minutes and seconds $date = ( get-date ).ToString( "MM_dd_yyyy_hh_mm_ss" ) $Server = New-Object Microsoft.AnalysisServices.Server $Server .Connect( "MAPS\TEST" ) $dbservers = $Server .Databases| Select-Object Name,ParentServer $dbservers | ForEach -Object{ $inddb = $_ #The "Backup-AsDatabase" is especially for SSAS cube backup and downloaded from "SQLServer" powershell module Backup-ASDatabase -BackupFile "E:\MAPS_TEST\SSAS_BACKUP\NEW\ $( $ inddb.Name) _ $( $ date) .abf" -Name $inddb .Name -Verbose -Server $inddb .ParentServer Write-Host "SSAS cube backup [ $( $ inddb.Name) ] completed successfully on server [ $( $ inddb.ParentServer) ]" Write-Output "SSAS cube backup [ $( $ inddb.Name) ] completed successfully on server [ $( $ inddb.ParentServer) ]" }

How to push or capture sp_WhoisActive result set to a table.

The query below will explain you how to create a table  and push the data to capture sp_WhoIsActive result set into a table. /* The script below will provide CREATE TABLE script to capture sp_whoisactive result set.After you run the script copy the result to a new query a window and provide an appropriate name to the table and create table. */ DECLARE @S VARCHAR(MAX) EXEC sp_WhoIsActive @return_schema = 1, @get_plans = 1, @schema = @s OUTPUT SELECT @s /* Once the table got created push the result to that table by executing below script. If you want to capture data for every 5 minutes create a job and place it there. */ EXEC sp_WhoIsActive @destination_table = 'northwind.dbo.WhoisActive' , @get_plans =1

Cannot enable the Service Broker in database "msdb" because the service Broker GUID in the database xxxxxx does not match the one in sys.databases.

Image
In recent past I have REBUILD system databases in SQL Server. After sometime. I tried to enable Database mail profile and to proceed with Mail Configuration. But the error message below stopped me to proceed further. Before I enable Database Mail and proceed with configuration the error message asked me to enable Service Broker first in msdb database. And below two commands fixed and enabled service broker in msdb database and allowed me to configure database mail configuration. ALTER DATABASE msdb SET NEW_BROKER WITH ROLLBACK IMMEDIATE ; ALTER DATABASE msdb SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE ;

sp_WhoIsActive

--Tempdb contention and blocking EXEC sp_WhoisActive @ sort_order = '[tempdb_current] DESC' , @ get_plans = 1 , @ Output_Column_List = '[start_time][session_id][sql_text][query_plan][wait_info][temp%]' ; GO EXEC sp_Whoisactive @ find_block_leaders = 1 , @ sort_order = '[blocked_session_count] DESC' , @ get_plans = 1 , @ Get_additional_info = 1 , @ Output_Column_list = '[start_Time][session_id][sql_text][query_plan][wait_info][block%][additional_info]' ; go EXEC sp_Whoisactive @ get_avg_time = 1 , @ Get_outer_Command = 1 , @ Get_plans = 1

Working with COUNT PowerShell

Image
Working with COUNT in Powershell how to show the values besides string. $logs = Get-WinEvent -ListLog * "There are $( $ logs.count) total event logs on domain" "There are [{0}] total event logs on domain" -f $logs .Count "There are [{0:N0}] total event logs on domain" -f $logs .Count

Azure SQL Managed Instance Database Query.

The below piece of code is explaingin about Azure SQL Managed Instance backup status. However this can be taken care by Azure internally. SELECT TOP ( 30 ) bs.machine_name, bs. server_name , DB_NAME(DB_ID(bs.database_name)) AS [ Database Name], bs.recovery_model, CONVERT ( BIGINT , bs.backup_size / 1048576 ) AS [Uncompressed Backup Size (MB)], CONVERT ( BIGINT , bs.compressed_backup_size / 1048576 ) AS [Compressed Backup Size (MB)], CONVERT ( NUMERIC ( 20 , 2 ), ( CONVERT ( FLOAT , bs.backup_size) / CONVERT ( FLOAT , bs.compressed_backup_size))) AS [Compression Ratio], bs.has_backup_checksums, bs.is_copy_only, bs.encryptor_type, DATEDIFF ( SECOND , bs.backup_start_date, bs.backup_finish_date) AS [Backup Elapsed Time (sec)], bs.backup_finish_date AS [Backup Finish Date ], bmf.physical_device_name AS [Backup Location ], bmf.physical_block_size, * FROM msdb.dbo.backupset AS bs WITH (NOLOCK) INNER JOIN msdb.dbo.backupmediafamily AS bmf WITH (NOLOCK) ON bs.me...

Moving the table to new file group in SQL Server

Image
The below link will explain how to move table from one file group to another file group. How to move table from one filegroup to another After the link opens search with the below text to go to the respective topic. D. Dropping a clustered index online and moving the table to a new filegroup. In the below example I am moving Person.Person table to new file group and new data file and new drive. USE master go ALTER DATABASE [AdventureWorks2014] ADD FILEGROUP MyGroup go ALTER DATABASE [AdventureWorks2014] ADD FILE (NAME = 'MyDataFile_Data' , FILENAME = 'G:\AdventureWorks\NEWFILEGROUP\MyDataFile_data.ndf' ) TO FILEGROUP MyGroup GO /* The below piece of code will move the table from pirmary to newly created file group called "MyGroup". And in this piece of code "UNIQUE" is compusory However this is not referring to UNIQUE index. This command may throw an error if the table has "XML" indexes.Scriptout those tables drop it and rec...