SELECT*

 This is how cpu, core , memory dependent on each other Each node has CPU, cpu internally split as CORES and memory will be allocated to each core.

By using the below Powershell Command we can add database to AOAG instantly. But ensure that the path we have given for data and log files should be similar(same drive and folder strucute) in both the replicas. CLS #Change the dircectory CD to primary replica node SQL Server CD SQLSERVER : \SQL\NODE1 #Please provide shared path info dont give individual acccount $DatabaseBackupFile = "\\node1\aoag_backup\C\C_FULL_09172022.BAK" $LogBackupFile = "\\node1\aoag_backup\C\C_log_09172022.trn" #If your sql server instance is DEFULT instance please provide DEFAULT below, if it is named instance servername\instancename $MyAgPrimaryPath = "SQLSERVER:\SQL\NODE1\DEFAULT\AvailabilityGroups\AG1" $MyAgSecondaryPath = "SQLSERVER:\SQL\NODE2\DEFAULT\AvailabilityGroups\AG1" #Take the FULL and transaction log backup in from the primary replica Backup-SqlDatabase -Database "C" -BackupFile $DatabaseBackupFile -ServerInstance "N...

I faced this issue in one of my client production environments . Databases are configured on AOAG. But one day one of the databases have gone into SYNCHRONIZED /RECOVERY PENDING state   We tried to check the progress of this database in READERROR log of  that particular database, in the error log it says 99% completed , after that there is no progress. At the same time we don't see any REDO Log Commit LSN or SEND LSN values under AOAG dash board.  Dont remove the database from AOAG and re added AOAG again. Simply run the below command and it did the trick. USE master go ALTER DATABASE DatabseName SET ONLINE

/* This permision(SP_HELPTXT) is only on those particular databases and those particular logins only */ USE Database1 GO GRANT VIEW DEFINITION TO [ Domain \ User1] GRANT VIEW DEFINITION TO [ Domain \ User2] go USE Database2 GO GRANT VIEW DEFINITION TO [ Domain \ User1] GRANT VIEW DEFINITION TO [ Domain \ User2] /* Granting VIEW(SP_HELPTEXT) permissions to on ALL databases in that server and to this account only */ USE master GO GRANT VIEW ANY DEFINITION TO USER1 The below command will give ALTER,CONTROL and VIEW DFFINITION permissions on a sepcific database. USE Test GO /* The below commands will give you Modify or ALTER Stored procedure and see the text of the logical objects which are created with dbo */ GRANT ALTER ON SCHEMA ::dbo TO LoginName GRANT CONTROL ON SCHEMA ::dbo TO LoginName GRANT VIEW DEFINITION ON SCHEMA ::dbo TO LoginName

 Below is the example. The below query will change the owner of all the databases to sa at a time sp_msforeachdb @ command1 = 'USE ?;IF DB_ID(''?'') > 4 begin EXEC sp_changedbowner ''sa'' end' Getting all the table information at time sp_msforeachdb @ command1 = 'USE ?;SELECT * FROM sys.Tables'

After running this script also check for Orphan users once. <# This will script out all the login(server principals and user permissions(database principals) to the below mentioned file in temp folder of C drive. After run this script in respective destination server also please check orphan users once. #> Export-DbaLogin ` -SQLInstance RAMESH\PROD ` -OutFile 'C:\temp\RAMESHPROD_LOGIN_USER_Permissions_08082022.txt' ` -Verbose The below piece of code will automatically generate the time stamp with server database details. $servername = "myServerNameHere" $dbName = "dbNameHere" $date = Get-Date Export-DbaLogin ` -SqlInstance $servername ` -Database $dbName ` -Verbose ` -FilePath "C:\AzureBackpack\ $( $ servername) _ $( $ dbName) _ $( $ date.ToString("yyyyMMdd_HHmmss") ).txt" The below command will script out individual login level permissions. $instance = "MAPS" $date = Get-Date -Format ...

Sometimes we can run SQL queries with a tool called OSQL.EXE . This is available in the below path by default based on SQL Server version. D:\Program Files\Microsoft SQL Server\140\Tools\Binn\OSQL.exe.  See the below image. By using the OSQL tool I have a run a SELECT query.  -S ServerName or InstanceName -i   Path in which I saved the .SQL query which contains SELECT command. And I      saved it in a  folder of a drive. -E  Windows Authentication -U  SQL Authentication. -P Password need to be provided if you choose SQL Authentication(-U). Below case I have chosen Windows Authentication And my select query looks below. And I save this to AdventureWorks2012 folder of D drive.  USE AdventureWorks2012 GO SELECT top 10 * FROM [HumanResources].[Department] Go to Run-->Cmd--> and here is the query and double quotes("") are compulsory If you want to export above result set to text file pass a parameter called -o and prov...

The below command will move the .txt files from one drive to another drive. If you want to move .BAK files( SQL Server database backup files) from one drive to another drive in the place of .txt file add .BAK extension. cls $Files = Get-ChildItem ` -Path "D:\Folder1\SubFolder1\Subfolder2" -Recurse -Verbose| Where-Object { $_ .Extension -like '.txt' } $Files | ForEach -Object{ $NewFiles = $_ Move-Item ` -Path "D:\Folder1\SubFolder1\Subfolder2\$NewFiles" ` -Destination "C:\data" ` -Verbose }

 I have configured Always On setup between SQL Cluster environment and an individual node which is a part of WSFC but not part of SQL Cluster. Here the SQL Cluster configured between A and B(internally these two also part of WSFC. Because Windows cluster is prerequisite to configure SQL Cluster), and the C is part of WSFC.  I got the below error(second image) when I am trying to install SQL Server instance on one of the windows servers. Work around: We need to Verify the windows cluster setup before we install SQL Server. So once the cluster configuration results pass then we will not see the below error. So validate cluster configuration before you install SQL Server 

The below BCP command will export the data(OUT) from table to a text file(.txt) and from the .txt file it will import(IN) the data to another table. Commands are give below. BCP AdventureWorks2014.Person.Person out D:\Test_Person_Peron.txt -S RAMESH\PROD -T -c -b10 -t -S  --> ServerName -T  --> Windows Authentication -b10 --> The amount of batches -t    --> Delimiter values(Eg:',', SPACE, TAB) -c    --> If you pass this this will not ask for " Enter the file storage type of field <field_name> [<default>]:" Running BCP command with SQL Query here is the command The below command will BCP out to a CSV(excel file). If you see excel it will capture all column information in single column. However it will appropriately insert the data into destination table. The below BCP command will push the data into required table from .CSV file . Here is the command .

The below query will push the data into histroy for our future reference or analysis purpose, we can schedule this query or we can run wehenever we need it. /* 1) The beloq query will script out sp_whoisactive stored procedure table structure in a print format. I have create table in master database, but we can create it on other database aswell. */ DECLARE @CaptureSchema VARCHAR ( MAX ) EXEC sp_WhoIsActive @output_column_list = '[dd%][session_id][sql_text][sql_command][login_name][wait_info][tasks][tran_log%] [cpu%][temp%][block%][reads%][writes%][context%][physical%][query_plan][locks][%]' , @return_schema = 1 , @get_plans = 1 , @ schema = @CaptureSchema OUTPUT PRINT @CaptureSchema /* The below query will push the data into a table(creating table with the script that the above resultset) please notice @destination_table parameter below. */ EXEC sp_whoisactive @output_column_list = '[dd%][session_id][sql_text][sql_command][login_name][wait_info][tasks][tr...

The below query will get the difference between the values of the same column not difference between two different columns. Here is the query, the query will showing the difference between OrderQty. USE DatabaseNameHere GO DECLARE @TEST TABLE (OrderID INT , OrderQty INT ) INSERT INTO @TEST VALUES ( 1 , 5 ),( 2 , 7 ),( 3 , 10 ),( 4 , 15 ),( 5 , 17 ),( 6 , 21 ),( 7 , 30 ),( 8 , 37 ), ( 9 , 40 ) SELECT * FROM @TEST SELECT OrderID,OrderQty,[Difference]=OrderQty-LAG(OrderQty)OVER( ORDER BY (OrderQty)) FROM @TEST

The below mentioned query will used for split backups which is extracted from  Ola Hallengren  scripts. @NumberOfFiles parameter will create mentioned files. EXECUTE [dbo].[DatabaseBackup] @ Databases = 'ALL_DATABASES' , @ Directory = 'N:\SQLBackups\' , @ BackupType = 'FULL' , @ Verify = 'Y' , @ CleanupTime = 168 , @ CheckSum = 'Y' , @ LogToTable = 'Y' , @ compress = 'Y' , @ NumberOfFiles = 3

I got the below error in SQL Server 2008 R2. When we try to see the properties of SQL Server agent(right click on the SQL Server Agent and click properties). I could see the below error message. Property Error Log File is not available for JobServer 'ServerNameHere'. This property may not exist for this object, or may not be retrievable due to insufficient access rights.  (Microsoft.SqlServer.Smo) Cause: SQL Server unable to find or deleted the Errorlog of SQL Server Agent. So we need manually set this up to rectify this error. If you run the below procedure first(sp_get_sqlagent_properties) it will show as NULL. Because that path has not been defined yet. So we need to set it up with the second stored procedure below as (sp_set_sqlagent_properties) USE MASTER GO EXEC msdb .. sp_get_sqlagent_properties GO USE MASTER GO EXEC msdb . dbo . sp_set_sqlagent_properties @errorlog_file = N 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\LOG\SQLAGENT.OUT' ...

The below query will tell us about how to import data from SQLAUDIT file to table in a database. We are creating a table here and importing data from file SELECT * INTO Audit_HumanResources_Department FROM ( SELECT event_time ,action_id ,session_server_principal_name AS UserName ,server_instance_name ,database_name , schema_name ,object_name , statement FROM sys.fn_get_audit_file( 'G:\Audit\HumanResource_Department\*.sqlaudit' , DEFAULT , DEFAULT ) --WHERE YEAR(event_time)=YEAR(GETDATE()) --AND month(event_time)=month(getdate()) --and day(event_time)=day(getdate()) )T The below piece of code will explain about create server level audit with Windows Application Event logs and its specifiactions and how the logs information will be route to eventlogs(eventvwr) applicatin logs. Before that we need to provide permissons at SECPOL.MSC(Local Security Policy). Below link will be explained about how give permissions that particular SQL Server serice account. https:...

This is a sample calculation and dont provide the proof log file size calculation use DatabaseName GO EXEC sp_spaceused Reserved = Data + Index + Unused( All values in KB) 230540328 = 64265016 + 164723400 + 1551912 Reserved = 230 GB DatabaseSize = Reserverd + LogFileSize( Values in GB) 280 = 230 + 50 ----Free space calculation 1 ’ 551 , 912 / 64 , 265 , 016 = 2 %

 Sometimes when we are online, we could see that log file grow continuously. The below query will tell us which query is causing to that logfile growth of that particular database. SELECT session_id, dt.transaction_id, DB_NAME(database_id) as DB, sum (database_transaction_log_bytes_used) TotalLogBytesUsed FROM sys.dm_tran_database_transactions dt LEFT JOIN sys.dm_tran_session_transactions st on (dt.transaction_id = st.transaction_id) GROUP BY session_id, dt.transaction_id, database_id ORDER BY 4 DESC

 The below query will tell us how much portion of BACKUP or RESTORE is completed. ---How much portion of BACKUP OR RESTORE is completed. SELECT session_id as SPID, command, a. text AS Query, start_time, percent_complete, dateadd( second ,estimated_completion_time / 1000 , getdate()) as estimated_completion_time FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a WHERE r.command in ( 'BACKUP DATABASE' , 'RESTORE DATABASE' )