SELECT*

 I came across a situation where my Home system which is Windows 10 Pro not able to go to sleep mode and my CPU is always up and running, I have gone through a couple of blogs, documents and also google a couple of articles some says due to Network Adapters, In my system, all the network adapters( ncpa.cpl ) are in the disabled state though my system is always awake not going to sleep mode. After a couple of hours research i found this command which fulfilled my requirement. powercfg/requestsoverride driver srvnet system" 

I have gotten into a situation where one of the index page got corrupted in one of the databases. Regarding corruption we come to know when run DBCC CHECKDB command and it thrown an error message like below. Msg 8936, Level 16, State 1, Line 3 Table error: Object ID 1701581100, index ID 1, partition ID 72057594048479232, alloc unit ID 72057594058964992 (type In-row data). B-tree chain linkage mismatch. (1:1209224)->next = (1:1081), but (1:1081)->Prev = (1:1080). Search here with the below query by adding ObjectID(above) to the query USE DatabaseNameHere GO SELECT * FROM sys.indexes where object_id = '123456' This query will also bring information about Primary Key indexes. USE DatabaseNameHere GO select s.name, t.name, i.name, c .name from sys.tables t inner join sys.schemas s on t.schema_id = s.schema_id inner join sys.indexes i on i.object_id = t.object_id inner join sys.index_columns ic on ic.object_id = t.object_id inner join sys.co...

 By using the below code we can GRANT permission to create a function to a particular user use [AdventureWorks2012] GO GRANT CREATE FUNCTION TO [bhavya] GRANT ALTER ON SCHEMA ::dbo TO [bhavya]

cls $services = Get-Service -name '*sql*' $services | ForEach -Object{ $Know = $_ if ( $_ .Status -eq "Running" ) { Write-Host $Know .Status, $Know .Name, $Know .DisplayName -foregroundcolor Green -Separator ' : ' } else { Write-Host $Know .Status, $Know .Name, $Know .DisplayName -foregroundcolor Red -Separator ' : ' } }

 By using the below command lets we can add members to the active directory login groups. While Providign Group name dont provide the Domain name group alone works. <# The below Windows feature command will import Poweshell Module Add-WindowsFeature #> Add-WindowsFeature RSAT-AD-PowerShell Import-Module ServerManager import-module activedirectory <# Space is compulsory between FirstName and LastName else it throws error Depends on requirement you can choose like(-like) or equal(-eq) commands. #> Get-ADUser -Filter 'Name -like "*Ramesh, Mamillapalli"' Add-ADGroupMember -Identity "GroupNameHere" -Members ( Get-ADUser -Filter 'Name -like "*Ramesh, Mamillapalli"' ) Get-ADUser -Filter 'Name -like "*MamilRam*"' The below query fetch group members from active dirctory domain group cls $GroupName = 'GroupNameHere' Get-ADGroup -filter *|sort name| Where-object { $_ .Name -eq $GroupName...

The below script need to run based on the requirement for instance if you are restoring a production database in UAT environment for testing purpose follow these instructions 1) Run the below script in UAT environment and capture the user info and save it to notepad 2) Then RESTORE production database in UAT environment 3) Then run the script that we captured in the first script. NOTE: Even after running the below script there is a possibility that you can find orphaned users , so try to run  EXEC sp_change_users_login 'Report'; and fix the orphans users if any exists. There is another powershell command which is available in dbatools, to use the below command first you need to import or install dbatools . DECLARE @ sql VARCHAR ( 2048 ) , @ sort INT DECLARE tmp CURSOR FOR /*********************************************/ /********* DB CONTEXT STATEMENT *********/ /*********************************************/ SELECT '-- [-- DB CONTEXT --] --...

  Run the first piece of code with in the comment section first. This will generate print statement of  xp_logininfo. Copy that print statement and paste it in the second pieced of code in between ' ' otherwise it throws error. /* DECLARE @Stmt NVARCHAR(4000) set @Stmt='SELECT ''EXEC master..xp_logininfo ''+''''''''''''+Name+''''''''''''+'',''+''''''''''members'''''''''' FROM sys.server_principals where [type]=''G''' EXEC sp_executesql @Stmt */ declare @ STMT2 VARCHAR ( 4000 ) set @ stmt2 = ' EXEC master..xp_logininfo ''domain1\group1'',''members'' EXEC master..xp_logininfo ''domain1\grop2'',''members'' EXEC master..xp_logininfo ''domain1\group3'',...

Usually I used to get this type error while i am trying to RESTORE databases. So to avoid these type of errors permanently we need to write RESTORE command in between changing SINGLE_USER and MULTI USER statements. See below command. Msg 3101, Level 16, State 1, Line 2 Exclusive access could not be obtained because the database is in use. Msg 3013, Level 16, State 1, Line 2 RESTORE DATABASE is terminating abnormally. Msg 3117, Level 16, State 1, Line 3 The log or differential backup cannot be restored because no files are ready to roll forward. Msg 3013, Level 16, State 1, Line 3 RESTORE DATABASE is terminating abnormally. Don't use GO command between the below statement run it as a single script. USE [master] ALTER DATABASE [Adventureworks] SET SINGLE_USER WITH ROLLBACK IMMEDIATE ---RestoreCommand between these two statements. RESTORE DATABASE Adventureworks FROM DISK = 'D:\Adventureworks_full.bak' WITH NORECOVERY ALTER DATABASE Adventureworks SET ...

I got this error in one of my clients environment where they are migrating their environment from VMWare to Hyper-V. They have migrated Hyper-V but the SQL Server Analysis services are not up and running, When we try to START the services it is throwing below error and the Services are not coming online, due to which even Windows Failover Cluster Roles and Services are OFFLINE state, and restarting the server is also not worked, moving nodes also not worked The main culprit here is DRIVES, drives are not migrated and in my environment DRIVE LETTER GOT CHANGED in which SQL Server Analysis configured.  You can check the drive information of Analysis Services in the start up parameters of SQL Server Analysis services, either this could be from SQL Server Configuration Manager or from Servicse.msc. After that made change to the respective drive from diskmgmt.msc and it is fixed the issue. "process terminated unexpectedly error 1067"

DECLARE @name sysname, @ sql nvarchar( 4000 ), @maxlen1 smallint , @maxlen2 smallint , @maxlen3 smallint DECLARE @ Table TABLE (DBName VARCHAR ( 1000 ),UserName VARCHAR ( 1000 ),RoleName VARCHAR ( 1000 )) IF EXISTS ( SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '#tmpTable%' ) DROP TABLE #tmpTable CREATE TABLE #tmpTable ( DBName sysname NOT NULL , UserName sysname NOT NULL , RoleName sysname NOT NULL ) DECLARE c1 CURSOR for SELECT name FROM master.sys.databases OPEN c1 FETCH c1 INTO @name WHILE @@FETCH_STATUS >= 0 BEGIN SELECT @ sql = 'INSERT INTO #tmpTable SELECT N''' + @name + ''', a.name, c.name FROM [' + @name + '].sys.database_principals a JOIN [' + @name + '].sys.database_role_members b ON b.member_principal_id = a.principal_id JOIN [' + @name + '].sys.database_principals c ON c.principal_id = b.role_principal_id WHERE a.name != ''dbo...

The below query get the information from listed servers and all the databases that are existing under that particular instance. cls Import-Module sqlps -DisableNameChecking $Query = "SELECT @@servername as ServerName,DBName=DB_NAME(),DP1.name AS DatabaseRoleName, isnull (DP2.name, 'No members') AS DatabaseUserName FROM sys.database_role_members AS DRM RIGHT OUTER JOIN sys.database_principals AS DP1 ON DRM.role_principal_id = DP1.principal_id LEFT OUTER JOIN sys.database_principals AS DP2 ON DRM.member_principal_id = DP2.principal_id WHERE DP1.type = 'R' and DP2.name='NameHere' ORDER BY DP1.name" $instanceNames = @ ( "SERVER1" , "SERVER2" , "SERVER3" , "SERVER4" ) $instanceNames | ForEach -Object{ $IndInstance = $_ #[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SQLServer.Smo") $Server = New-object ` -TypeName "Microsoft.SQLserver.Management.Smo.Server...

Usually we will encounter this error when we are trying to place the backup files(.BAK) in another windows server or in a network shared folder. This issue comes up because of permission issues.  operating system error 5(access is denied.) We need add below two accounts to that particular folder or drive(to where we are placing .BAK files) security path and give FULL permissions. SQL Server Database Engine Service Account SQL Server Agent Service Account Sometimes even above approach will not work, in that case run SSMS in "run as Administrator mode" and run the command.

One day in one of my Active-Passive cluster environments while patching SQL Server 2012 environment I found the below Error. Here are the observations 1)   Failover cluster Health is good, all the roles, drives and nodes are running fine. Both the nodes are running pinging one to another.      Though the Error message says cluster is disabled or needs to start, cluster service is running fine in the services.msc Below are the error screenshots. The below command did the trick in my environment. Seems to be issue with MOF files. mofcomp C:\Windows\System32\WBEM\cimwin32.mof

The below query will provide us the database data and log file growth options on the disk. select DB_NAME(mf.database_id) database_name , mf.name logical_name , CONVERT ( DECIMAL ( 20 , 2 ) , ( CONVERT ( DECIMAL , size ) /128 )) [file_size_MB] , CASE mf.is_percent_growth WHEN 1 THEN 'Yes' ELSE 'No' END AS [is_percent_growth] , CASE mf.is_percent_growth WHEN 1 THEN CONVERT ( VARCHAR , mf.growth) + '%' WHEN 0 THEN CONVERT ( VARCHAR , mf.growth /128 ) + ' MB' END AS [growth_in_increment_of] , CASE mf.is_percent_growth WHEN 1 THEN CONVERT ( DECIMAL ( 20 , 2 ), ((( CONVERT ( DECIMAL , size ) * growth) /100 ) *8 ) /1024 ) WHEN 0 THEN CONVERT ( DECIMAL ( 20 , 2 ), ( CONVERT ( DECIMAL , growth) /128 )) END AS [next_auto_growth_size_MB] , CASE mf.max_size WHEN 0 THEN 'No growth is allowed' WHEN -1 THEN 'File will grow until the disk is full' ELSE CONVERT ( VARCHAR , mf.max_size) END AS [max_size] , phy...

 The below script extract information about Cluster resource status in  Windows Cluster cls #ClusterResource $GetClusterInfo = Get-ClusterResource $GetClusterInfo | ForEach -Object{ $GetClustInfo = $_ if ( $GetClustInfo .State -ne 'Online' ) { $GetClustInfo | Format-Table -AutoSize| Out-String | Write-Host -ForegroundColor Red } else { $GetClustInfo | Format-Table -AutoSize| Out-String | Write-Host -ForegroundColor Green } } ; #Cluster Netwok $GetClusterNetwork = Get-ClusterNetwork $GetClusterNetwork | ForEach -Object{ $GetClustNet = $_ if ( $GetClustNet .State -ne 'UP' ) { $GetClustNet | Format-Table -AutoSize| Out-String | Write-Host -ForegroundColor Red } else { $GetClustNet | Format-Table -AutoSize| Out-String | Write-Host -ForegroundColor DarkYellow } } ; #Cluster NetworkInterface $GetClustNetInterface = Get-ClusterNetworkInterface $GetClustNetInterface | ForEach -Object{ $GetClustNetINter = $_ if ( $GetClustNetINter .State -ne 'U...

We can use the below query to get the job step information of SQL Server Agent jobs.

cls $servers = @( "RAMESH" , "RAMESH\TEST" , "RAMESH\UAT" ) $servers | ForEach-Object { $IndServer = $_ $IndServers = New-Object ` -TypeName Microsoft.SqlServer.Management.Smo.Server ` -ArgumentList $IndServer $IndServers . Databases | ForEach-Object { $Status = $_ if ( $Status . Status -ne 'Normal' ) {   Write-Host $IndServer , ',' $Status . Name , ',' $Status . Status -ForegroundColor red } <# else { $Status } #>   } }