Friday, 4 August 2017

SQL Server – Alter database in Single User mode to Multi User mode

Refereed from
https://myadventuresincoding.wordpress.com/2014/03/06/sql-server-alter-database-in-single-user-mode-to-multi-user-mode/

We have some test environment databases that get rebuilt nightly. The job that does the rebuild always switches the database into single user mode when it is being rebuilt. When the rebuild is finished it will switch it back to multi user mode. However, if there was a problem with a script and the job failed, it will leave the database in single user mode.
First, open a SQL Server Management Studio query window connected to database “master”.


The command to change the database back to multi user mode is:
ALTER DATABASE {InsertDatabaseNameHere} SET MULTI_USER WITH ROLLBACK IMMEDIATE GO


However, if there is an existing process blocking this alter, you may get the error message:
“Transaction (Process ID 864) was deadlocked on lock resources with another process and has been chosen as the deadlock victim”
Since both the existing running process and the current request have the same deadlock priority, preference is given to the longer running process which is why your alter database command is chosen as the deadlock victim. So to deal with this when you run the command again, set the deadlock priority to HIGH. However, if that does not work, you will need to kill the existing connection.
To find the “spid” of the existing connection, you can use the following query:
SELECT sd.[name], sp.spid, sp.login_time, sp.loginame
FROM sysprocesses sp
INNER JOIN sysdatabases sd on sp.dbid = sd.dbid 
WHERE sd.[name] = 'DatabaseNameGoesHere'
As an alternative, you can also use the command “sp_who” to get the “spid” of the open connection:
exec sp_who
So, here is our revised command:
KILL SpidToKillGoesHere
GO
SET DEADLOCK_PRIORITY HIGH
GO
ALTER DATABASE DatabaseNameGoesHere SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO
That should do it!

Thursday, 29 June 2017

Disconnect sessions from multiple servers using powershell script

function Get-Sessions { $queryResults = query session /SERVER:$srv echo "Server $srv is reachable, collecting sessions" $starters = New-Object psobject -Property @{"SessionName" = 0; "UserName" = 0; "ID" = 0; "State" = 0; "Type" = 0; "Device" = 0;} foreach ($result in $queryResults) { try { if($result.trim().substring(0, $result.trim().indexof(" ")) -eq "SESSIONNAME") { $starters.UserName = $result.indexof("USERNAME"); $starters.ID = $result.indexof("ID"); $starters.State = $result.indexof("STATE"); $starters.Type = $result.indexof("TYPE"); $starters.Device = $result.indexof("DEVICE"); continue; } New-Object psobject -Property @{ "SessionName" = $result.trim().substring(0, $result.trim().indexof(" ")).trim(">"); "Username" = $result.Substring($starters.Username, $result.IndexOf(" ", $starters.Username) - $starters.Username); "ID" = $result.Substring($result.IndexOf(" ", $starters.Username), $starters.ID - $result.IndexOf(" ", $starters.Username) + 2).trim(); "State" = $result.Substring($starters.State, $result.IndexOf(" ", $starters.State)-$starters.State).trim(); "Type" = $result.Substring($starters.Type, $starters.Device - $starters.Type).trim(); "Device" = $result.Substring($starters.Device).trim() } } catch { $e = $_; echo "ERROR: on $srv" + $e.PSMessageDetails } } } $servers = "SERVERNAME1","SERVERNAME2","SERVERNAME3" foreach($srv in $servers) { [string]$IncludeStates = '^(Disc)$' echo "Sessions CleanUp on Server $srv" echo "=============================" $DisconnectedSessions = Get-Sessions| ? {$_.UserName -ne ""} | Select ID, UserName foreach ($session in $DisconnectedSessions) { logoff $session.ID /SERVER:$srv echo $session.Username } echo "=============================" echo " " }

Tuesday, 16 May 2017

Delete TempDBs with same name in database server

Run below query and copy the results new query window run results


DECLARE @SQL VARCHAR(MAX) = ''
SELECT
 @SQL =  @SQL + 'use master' + CHAR(13) + CHAR(10) 
    +  'ALTER DATABASE ['+ d.name +'] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;' + CHAR(13) + CHAR(10) 
    + 'DROP DATABASE [' + d.name +'];'

FROM
     sys.databases d     
where d.name like 'TempDB_%'
print @SQL
exec(@SQL)

Wednesday, 21 December 2016

How to monitor backup and restore progress in SQL Server

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')

Friday, 2 September 2016

How do I shrink all files quickly for all databases?






Run below query on DB server


DECLARE @SQL VARCHAR(MAX) = '' SELECT @SQL = @SQL + 'DECLARE @TA_LOGFILE_NAME VARCHAR(500)' SELECT @SQL = @SQL + 'DECLARE @TA_DATABASE_NAME VARCHAR(500)' SELECT @SQL = @SQL + 'USE [' + d.name + N']' + CHAR(13) + CHAR(10) + 'EXEC (''ALTER DATABASE ['+d.name+'] SET RECOVERY SIMPLE'');' + CHAR(13) + CHAR(10) + 'SELECT @TA_LOGFILE_NAME=NAME FROM SYS.DATABASE_FILES WHERE DATA_SPACE_ID=0' + CHAR(13) + CHAR(10) + 'SELECT @TA_DATABASE_NAME = DB_NAME()' + CHAR(13) + CHAR(10) + 'DBCC SHRINKFILE (@TA_LOGFILE_NAME, 1);' + CHAR(13) + CHAR(10) + 'EXEC (''ALTER DATABASE ['+d.name+'] SET RECOVERY FULL'');' + CHAR(13) + CHAR(10) + '--PRINT @TA_DATABASE_NAME + ''LOG SHRINKED''' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) FROM sys.databases d WHERE d.database_id > 4; EXEC(@SQL)



Thursday, 28 January 2016

Update COMPATIBILITY_LEVEL for all databases in sql server



USE MASTER DECLARE GET_DATABASES CURSOR READ_ONLY FOR SELECT NAME FROM SYS.DATABASES WHERE COMPATIBILITY_LEVEL != '120' DECLARE @DATABASENAME NVARCHAR(255) DECLARE @COUNTER INT SET @COUNTER = 1 OPEN GET_DATABASES FETCH NEXT FROM GET_DATABASES INTO @DATABASENAME WHILE (@@fetch_status <> -1) BEGIN IF (@@fetch_status <> -2) BEGIN -- CHANGE DATABASE COMPATIBILITY EXECUTE sp_dbcmptlevel @DATABASENAME , '120' PRINT @DATABASENAME + ' changed' SET @COUNTER = @COUNTER + 1 END FETCH NEXT FROM GET_DATABASES INTO @DATABASENAME END CLOSE GET_DATABASES DEALLOCATE GET_DATABASES GO

Wednesday, 26 August 2015

OLAP configuration using powershell

#Import Modules
Import-Module WebAdministration

# change these settings 
$iisSiteName = "OLAP"
$iisPort = "8000"
$olapServerName = "server\instance"

# optionally also change these settings
$isapiFiles = "c:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\bin\isapi\*"
$iisAbsolutePath = "C:\inetpub\wwwroot\" + $iisSiteName
$iisAppPoolName = $iisSiteName + "_AppPool"
$iisAppPoolUser = "" #default is ApplicationPoolIdentity
$iisAppPoolPassword = ""
$iisAuthAnonymousEnabled = $false
$iisAuthWindowsEnabled = $true
$iisAuthBasicEnabled = $true
$olapSessionTimeout = "3600" #default
$olapConnectionPoolSize = "100" #default

if(!(Test-Path $iisAbsolutePath -pathType container))
{
    #Creating Directory
    mkdir $iisAbsolutePath  | Out-Null

    #Copying Files
    Write-Host -NoNewline "Copying ISAPI files to IIS Folder ... "
    Copy -Path $isapiFiles -Destination $iisAbsolutePath -Recurse
    Write-Host " Done!" -ForegroundColor Green
}
else
{
    Write-Host "Path $iisAbsolutePath already exists! Please delete manually if you want to proceed!" -ForegroundColor Red
    Exit
}

#Check if AppPool already exists
if(!(Test-Path $("IIS:\AppPools\" + $iisAppPoolName) -pathType container))
{
    #Creating AppPool
    Write-Host -NoNewline "Creating ApplicationPool $iisAppPoolName if it does not exist yet ... "
    $appPool = New-WebAppPool -Name $iisAppPoolName
    $appPool.managedRuntimeVersion = "v2.0"
    $appPool.managedPipelineMode = "Classic"

    $appPool.processModel.identityType = 4 #0=LocalSystem, 1=LocalService, 2=NetworkService, 3=SpecificUser, 4=ApplicationPoolIdentity
    #For details see http://www.iis.net/configreference/system.applicationhost/applicationpools/add/processmodel

    if ($iisAppPoolUser -ne "" -AND $iisAppPoolPassword -ne "") {
     Write-Host 
        Write-Host "Setting AppPool Identity to $iisAppPoolUser"
  $appPool.processmodel.identityType = 3
  $appPool.processmodel.username = $iisAppPoolUser
  $appPool.processmodel.password = $iisAppPoolPassword
 } 
    $appPool | Set-Item
    Write-Host " Done!" -ForegroundColor Green
}
else
{
    Write-Host "AppPool $iisAppPoolName already exists! Please delete manually if you want to proceed!" -ForegroundColor Red
    Exit
}

#Check if WebSite already exists
$iisSite = Get-Website $iisSiteName
if ($iisSite -eq $null)
{
    #Creating WebSite
    Write-Host -NoNewline "Creating WebSite $iisSiteName if it does not exist yet ... "
    $iisSite = New-WebSite -Name $iisSiteName -PhysicalPath $iisAbsolutePath -ApplicationPool $iisAppPoolName -Port $iisPort
    Write-Host " Done!" -ForegroundColor Green
}
else
{
    Write-Host "WebSite $iisSiteName already exists! Please delete manually if you want to proceed!" -ForegroundColor Red
    Exit
}

#Ensuring ISAPI CGI Restriction entry exists for msmdpump.dll
if ((Get-WebConfiguration "/system.webServer/security/isapiCgiRestriction/add[@path='$iisAbsolutePath\msmdpump.dll']") -eq $null)
{
    Write-Host -NoNewline "Adding ISAPI CGI Restriction for $iisAbsolutePath\msmdpump.dll ... "
    Add-WebConfiguration "/system.webServer/security/isapiCgiRestriction" -PSPath:IIS:\  -Value @{path="$iisAbsolutePath\msmdpump.dll"}
    Write-Host " Done!" -ForegroundColor Green
}
#Enabling ISAPI CGI Restriction for msmdpump.dll
Write-Host -NoNewline "Updating existing ISAPI CGI Restriction ... "
Set-WebConfiguration "/system.webServer/security/isapiCgiRestriction/add[@path='$iisAbsolutePath\msmdpump.dll']/@allowed" -PSPath:IIS:\ -Value "True" 
Set-WebConfiguration "/system.webServer/security/isapiCgiRestriction/add[@path='$iisAbsolutePath\msmdpump.dll']/@description" -PSPath:IIS:\ -Value "msmdpump.dll for SSAS"
Write-Host " Done!" -ForegroundColor Green


#Adding ISAPI Handler to WebSite
Write-Host -NoNewline "Adding ISAPI Handler ... "
Add-WebConfiguration /system.webServer/handlers -PSPath $iisSite.PSPath -Value @{name="msmdpump"; path="*.dll"; verb="*"; modules="IsapiModule"; scriptProcessor="$iisAbsolutePath\msmdpump.dll"; resourceType="File"; preCondition="bitness64"}
Write-Host " Done!" -ForegroundColor Green

#enable Windows and Basic Authentication
Write-Host -NoNewline "Setting Authentication Providers ... "
#need to Unlock sections first
Set-WebConfiguration /system.webServer/security/authentication/anonymousAuthentication  MACHINE/WEBROOT/APPHOST -Metadata overrideMode -Value Allow
Set-WebConfiguration /system.webServer/security/authentication/windowsAuthentication  MACHINE/WEBROOT/APPHOST -Metadata overrideMode -Value Allow
Set-WebConfiguration /system.webServer/security/authentication/basicAuthentication  MACHINE/WEBROOT/APPHOST -Metadata overrideMode -Value Allow

Set-WebConfiguration /system.webServer/security/authentication/anonymousAuthentication -PSPath $iisSite.PSPath -Value @{enabled=$iisAuthAnonymousEnabled}
Set-WebConfiguration /system.webServer/security/authentication/windowsAuthentication -PSPath $iisSite.PSPath -Value @{enabled=$iisAuthWindowsEnabled}
Set-WebConfiguration /system.webServer/security/authentication/basicAuthentication -PSPath $iisSite.PSPath -Value @{enabled=$iisAuthBasicEnabled}
Write-Host " Done!" -ForegroundColor Green

#Adding Default Document
Write-Host -NoNewline "Adding Default Document msmdpump.dll ... " 
Add-WebConfiguration /system.webServer/defaultDocument/files -PSPath $iisSite.PSPath -atIndex 0 -Value @{value="msmdpump.dll"}
Write-Host " Done!" -ForegroundColor Green

#Updating OLAP Server Settings
Write-Host -NoNewline "Updating OLAP Server Settings ... "
[xml]$msmdpump = Get-Content "$iisAbsolutePath\msmdpump.ini"
$msmdpump.ConfigurationSettings.ServerName = $olapServerName
$msmdpump.ConfigurationSettings.SessionTimeout = $olapSessionTimeout
$msmdpump.ConfigurationSettings.ConnectionPoolSize = $olapConnectionPoolSize
$msmdpump.Save("$iisAbsolutePath\msmdpump.ini")
Write-Host " Done!" -ForegroundColor Green

Thursday, 25 December 2014

writing file using vbscript

'Option Explicit
dim objXML2,fso,strdir,CurrentDirectory,ExportFile,LocalPath,SVNPath,colNodes,objnode
Set objXML2 = CreateObject("Microsoft.XMLDOM")
objXML2.async = "false"
strdir=WScript.Arguments.item(0)'PRODUCTINFO.xml

set fso = CreateObject("Scripting.FileSystemObject")
CurrentDirectory = fso.GetAbsolutePathName(".")
Set ExportFile = fso.CreateTextFile(CurrentDirectory&"\SVNExport.cmd", True)
ExportFile.WriteLine("@echo off")
'ExportFile.WriteLine("start /b 1")

LocalPath = 'WScript.Arguments.item(1)
SVNPath = 'WScript.Arguments.item(2)

'ExportFile.WriteLine("LocalPath: " & LocalPath)
'ExportFile.WriteLine("SVNPath: " & SVNPath)

If (Not objXML2.load(strdir)) Then
    wscript.echo "Unable to load file '" & strdir & "'. "
    WScript.Quit(1)
End If

Set colNodes = objXML2.selectNodes ("/PRODUCTINFO/PRODUCT")
For Each objNode in colNodes
    wscript.echo objnode.getAttribute("NAME")& " : " & objnode.getAttribute("VERSION")
ExportFile.WriteLine("echo Getting " & objnode.getAttribute("NAME") & " of version " & objnode.getAttribute("VERSION") & " from SVN")
ExportFile.WriteLine("""C:\Program Files\TortoiseSVN\bin\svn.exe"" export """ & SVNPath & "/" & objnode.getAttribute("NAME") & "/" & objnode.getAttribute("VERSION") & """   """ & LocalPath & "\" & objnode.getAttribute("NAME") & "\" & objnode.getAttribute("VERSION"))
Next
ExportFile.WriteLine("echo Export Completed")
ExportFile.close()

Thursday, 9 October 2014

SSL Settings automation using netsh and appcmd

appcmd set site "Default Web Site" /+bindings.[protocol='https',bindingInformation='*:443:']
======================================================================

# get the thumbprint for the certificate we want to use:
$thumb = (Get-ChildItem cert:\LocalMachine\MY | where-object { $_.FriendlyName -eq   "www.stackoverflow.com" } | Select-Object -First 1).Thumbprint
# get a new guid:
$guid = [guid]::NewGuid()

# remove the self-signed certificate:
& netsh http delete sslcert ipport=0.0.0.0:8172
# add the 'proper' certificate:
& netsh http add sslcert ipport=0.0.0.0:8172 certhash=$thumb appid=`{$guid`}


more information

http://www.iis.net/learn/manage/powershell/powershell-snap-in-configuring-ssl-with-the-iis-powershell-snap-in







Tuesday, 12 August 2014

links for the COM+ registering



http://msdn.microsoft.com/en-us/library/tzat5yw6(v=vs.110).aspx

http://www.codeproject.com/Articles/7859/Building-COM-Objects-in-C

http://community.microfocus.com/borland/define/caliber_-_requirements_management/w/knowledge_base/16090.why-when-registering-the-helloworld2-vbnet-dll-does-it-generate-the-error-regasm-warning-ra0000-no-types-were-registered.aspx


http://stackoverflow.com/questions/1847763/problem-registering-a-dll-access-denied


http://msdn.microsoft.com/en-us/library/dd233108(v=vs.110).aspx


http://www.codeproject.com/Articles/1405/Using-the-Web-Services-and-COM-Event-System-in-the

http://www.codeproject.com/Articles/206412/Reflection-An-Introduction-to-Reflection-in-NE


http://www.codeproject.com/Articles/337535/Understanding-the-Basics-of-Web-Service-in-ASP-NET

http://msdn.microsoft.com/en-us/magazine/cc301491.aspx

http://oreilly.com/catalog/comdotnetsvs/chapter/ch10.html

http://my.execpc.com/~gopalan/dotnet/complus/complus.net_accountmanager.html

http://www.codeproject.com/Articles/1511/Accessing-COM-component-using-C

http://edndoc.esri.com/arcobjects/9.2/NET/3a2694e6-32da-4e1e-b7c5-ccd3826161bb.htm

http://www.codeproject.com/Articles/206412/Reflection-An-Introduction-to-Reflection-in-NE

http://www.jagjot.com/2014/01/register-c-vb-net-dll-regasm-gacutil/


Friday, 8 August 2014

How to create the COM DLL

http://www.codeproject.com/Articles/7859/Building-COM-Objects-in-C

Thursday, 7 August 2014

system.data.oledb.oledbexception could not find installable isam


The path name should be like below:

string filename = "C:\\Praveen\\Tasks\\TASK 1\\Mydata.xlsx";
            MyOleCon = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+ filename +";Extended Properties=\"Excel 12.0 Xml;HDR=YES;\"");