Posts

How to read audit file from drive,CREATE SERVER level AUDIT

Image
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:...

Database Size Calculation in SQL Server

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 %

Which query is causing to the log file growth.

 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

How much portion or percentage BACKUP or RESTORE command is completed.

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

dbatools download and installation

Image
Click on the below link( dbatools ) and found "DownloadZip" under CODE. Once the zip folder is downloaded it will download as  "dbatools-development". and extract it. After that change the folder name from "dbatools-development" to "dbatools". And sometimes there is a possibility that dbatools folder contains another dbatools-development folder with in the folder,if that is the case please make there is only one "dbatools" folder not any duplicate folders with in it. dbatools In the search space search with "ISE" and choose PowerShell and run as administrator and run the below command. And it will give the paths of modules like below. You can find these many paths for modules and copy the dbatools folder from source to " C:\Program Files\WindowsPowerShell\Modules " folder  and run the below command in PowerShell and you can see the progress of installation and will successfully install it.

New-NetFirewallRule

Image
 The below command will allow fire wall rule to get results through ping command and to access the server remotely.

Could not drop login 'loginname' as the user is currently logged in.

Image
 I  go the below error in one our environments. Below are the troubleshooting steps. Run the below command find the session id that are using by that particular session KILL all the session KILL SELECT session_id FROM sys.dm_exec_sessions WHERE login_name = 'LoginName' And then run the below command USE [master] GO DROP LOGIN [LoginName]

Finding the free space in the data file of database in SQL Server.

The below query will provide us the amount of free space that exist at database data file level. CREATE TABLE # FileSize (dbName NVARCHAR( 128 ), [FileName] NVARCHAR( 128 ), physical_name varchar ( 150 ), type_desc NVARCHAR( 128 ), CurrentSizeMB DECIMAL ( 10 , 2 ), FreeSpaceMB DECIMAL ( 10 , 2 ) ); INSERT INTO # FileSize (dbName,[FileName],physical_name,type_desc, CurrentSizeMB, FreeSpaceMB) exec sp_msforeachdb 'use [?]; SELECT DB_NAME() AS DbName, name AS FileName, physical_name, type_desc, size/128.0 AS CurrentSizeMB, size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128.0 AS FreeSpaceMB FROM sys.database_files WHERE type IN (0,1) and type_desc=''ROWS'';' ; SELECT * FROM # FileSize WHERE dbName NOT IN ( 'distribution' , 'master' , 'model' , 'msdb' ) and type_desc = 'ROWS' and physical_name LIKE 'E:\%' --A...

The subscription(s) have been marked inactive and must be reinitialized. NoSync subscriptions will need to be dropped and recreated. (Source: MSSQLServer, Error number: 21074),REPLICATION

Distribution Agent job failed with below erro message and the below workaround fixed the issue in my environment Error messages: The subscription(s) have been marked inactive and must be reinitialized. NoSync subscriptions will need to be dropped and recreated. (Source: MSSQLServer, Error number: 21074) USE Distribution GO SELECT STATUS,* FROM [dbo].[MSsubscriptions] WHERE PUBLISHER_DB= 'PublisherDBNameHere' use distribution go update MSSubscriptions set [status] = 2 WHERE PUBLISHER_DB= 'PublisherDBNameHere' And to check thestatus after above command use distribution go exec sp_browsereplcmds

Login Creation, Adding Login to Server Role and extracting info, dbatools

The below powershell query will create a login it could be a windows login or SQL Server Authentication login we can create those login by using below query and add that particular login to particular role and can extract the info. If that particular login alredy exists in the environment the query will ignore or if you mention -FORCE parameter it will overwrite. cls $ServerList = @ ( "Instance1" , "Instance2" , "Instance3" ) $ServerList | ForEach -Object{ $indserver = $_ New-DbaLogin -SqlInstance $indserver -Login "DomainName\WindowsLoginGroup" | Select-Object ComputerName,InstanceName,SqlInstance,Name,LoginType,CreateDate,LastLogin,HasAccess,IsLocked,IsDisabled Set-DbaLogin -SqlInstance $indserver -Login "DomainName\WindowsLoginGroup" -AddRole sysadmin| Select-Object ComputerName,InstanceName,SqlInstance,LoginName,ServerRole Get-DbaLogin -SqlInstance $indserver -Login "DomainName\WindowsLoginGroup" | Select-Objec...

db_executor role.

 The below database role will create a new role called db_executor, a role which gives permissions to all users to execute all the stored procedures of that particular database. Whoever associated to this role. -- Create a db_executor role CREATE ROLE db_executor -- Grant execute rights to the new role GRANT EXECUTE TO db_executor

Differences between Azure SQL Managed Instance and Azure SQL Database.

Image
Feature differences between Azure SQL databases and Azure SQL Managed instance . I have not mentioned here about the SQL Server in Azure Virtual machine as it is equal to on premises SQL Server and there would not be any much differences in the environment.

Restore-DbaDatabase,dbatools

Image
We can use below dbatools to restore database. Export-DbaUser will export all the respective database users to a spcific file. If you want all the user script need to add single file then user -FilePath parameter.If you want indivual files need to created user -Path parameter. cls Export - DbaUser ` - SqlInstance InstanceNameHere ` - Database DatabaseNameHere ` - FilePath C : \ Temp \ Users_DatbaseNameHere_InstanceNameHere. sql ` - Verbose In the below command -Path parameter you no need pass .BAK file it will automatically read file. -WithReplace option will overwrite the existing database. Restore - DbaDatabase ` - SqlInstance InstanceNameHere ` - DatabaseName DatabaseNameHere ` - Path H: \ TodayRestore ` - DestinationDataDirectory "F:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\DatabaseName_Data.mdf" ` - DestinationLogDirectory "G:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\DatbaseName_Log.ldf" ` - Wit...

LSN of Transaction Log Backups.

The below query will tell us the history of Transaction Log Backups. SELECT DISTINCT s.database_name, s.first_lsn, s.last_lsn,                         s.database_backup_lsn, s.backup_start_date, s.backup_finish_date,                         s. type , s.is_snapshot,y.device_type, y.logical_device_name, y.physical_device_name FROM msdb..backupset AS s INNER JOIN msdb..backupfile AS f ON f.backup_set_id = s.backup_set_id INNER JOIN msdb..backupmediaset AS m ON s.media_set_id = m.media_set_id INNER JOIN msdb..backupmediafamily AS y ON m.media_set_id = y.media_set_id where database_name = 'database_name'

Which user has SHOWPLAN permissions at database level.

The below query will tells us the which user has SHOWPLAN permission on the database level. DECLARE @ Table TABLE (ServerName varchar ( 50 ),DatabaseName varchar ( 50 ), [Name] varchar ( 50 ),PermissionName varchar ( 50 ),StateDesc varchar ( 50 )) INSERT INTO @ Table (ServerName,DatabaseName,[Name],PermissionName,StateDesc) EXEC sp_MSforeachdb 'USE ? SELECT ServerName=@@ServerName, DatabaseName=db_name(), p.name, perm.permission_name,perm.STATE_DESC FROM sys.database_permissions perm INNER JOIN sys.database_principals p ON perm.grantee_principal_id = p.principal_id WHERE perm.permission_name = ''SHOWPLAN'' AND [Name] in(''User1'',''User2'', ''User3'',''User4j'');' SELECT * FROM @ Table order by [Name] DESC

READONLY ROUTING in Always On.

Image
This query will tell you how to handle READONLY ROUTING in AlwaysOn. ALTER AVAILABILITY GROUP AvailabilityGroupName MODIFY REPLICA ON N 'NODE1' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)); ALTER AVAILABILITY GROUP AvailabilityGroupName MODIFY REPLICA ON N 'NODE1' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N 'TCP://NODE1.HADRDOMAIN.COM:1433' )); ALTER AVAILABILITY GROUP AvailabilityGroupName MODIFY REPLICA ON N 'NODE2' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)); ALTER AVAILABILITY GROUP AvailabilityGroupName MODIFY REPLICA ON N 'NODE2' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N 'TCP://NODE2.HADRDOMAIN.COM:1433' )); ALTER AVAILABILITY GROUP AvailabilityGroupName MODIFY REPLICA ON N 'NODE1' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = ( 'NODE2' , 'NODE1' ))); ALTER AVAILABILITY GROUP AvailabilityGroupName MODIFY REPLICA ON N 'NODE2' WITH...

Convert ROWS to COLUMNS in Powershell; SQL Server Version and Edition Information from multiple instance.

  The below query will extract Editions,Version and Service Pack information of all the SQL Server Instances. In the server list array provide SQL Server instance information, if you provide windows server name it might throw error or will skip that paritcular server information. cls Import-Module sqlps -DisableNameChecking #[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SQLServer.Smo')|Out-Null $Counter =0 $serverlist = @ ( 'SQLServerInstance1' , 'SQLServerInstance2' , 'SQLServerInstance3' ) #ForEach-Loop start $serverlist | ForEach -Object{ #$x = 1 #$perc=[math]::round($x/$serverlist.count) $indsserver = $_ $Server = New-Object Microsoft.SQLServer.Management.Smo.Server $indsserver #$Server.Information.Properties|Select-Object Name, Value $netName = ( $Server .Information.Properties| ?{ $_ .Name -like 'Netname' }).VAlue $product = ( $server .Information.Properties | ?{ $_ .Name -like 'Product' }).VAlue...

How to import or restore Azure SQL Database backup file(.BACPAC) to local SQL Server instance.

Image
1) First we need to create a storage account ,container to place the .BACPAC file in Azure. 2)  In this container we will place the .BACPAC file of Azure SQL database. 3) Create the backup file of Azure SQL Database, below image will tell you. *for bigger images click on the images below, it will pop in new window 4) Sometimes we can also use some other Third party tool   with which we can     take the Azure SQL Database backup and can save it to your local drive. However I have explained here, how to take the Azure SQL Database backup in Azure Portal. 4) Move back to SQL Server from Azure SQL Database and see the progress as mentioned below. 5) After downloading the .BACPAC file from your azure storage to your local drive. You can restore this .BACPAC file into your local SQL Server. Below images are self explanatory.

How to create Azure SQL Admin and can access with "Active Directory-Universal with MFA Support" authentication from SSMS

Image
 To access Azure SQL Database from SSMS through "Active Directory-Universal with MFA Support" authentication please follow the below steps. 1) Create an user Azure Active Directory level with MFA authentication. a) Search Azure Active Directory in market place b) Click add user so you can see the below image. c) Provide initial password and create the user. d) Now in the below image test user has been created 2) Add this test user to Directory Readers and Directory Writers role and also Global Administraor though the below image does not show it you can add it without missing. Please see the image. Chose those two roles and click on Add button and refresh. And choose authentication method too . b) Now you could see test user allocated those two roles. and very important to access Azure Active directory users. c) Now go to authentication provide users or your phone number(if you working at home for practice) after providing the phone number with +91 XXXXXXXXXX click on save b...

Automate BACKUP DATABASE script.

The below script will generate backups for all the databases in a single instance. SET NOCOUNT ON DECLARE @ servername varchar ( 50 ) DECLARE @ databasename varchar ( 50 ) DECLARE @ path VARCHAR ( 50 ) DECLARE @ stmt varchar ( 4000 ) DECLARE @ I INT DECLARE @ Count INT DECLARE @ Databases table (Sno INT IDENTITY ( 1 , 1 ),[Name] varchar ( 50 )) INSERT INTO @ Databases ([Name]) SELECT Name FROM sys.databases WHERE State = 0 and database_id not in ( 2 ) SET @ I = 0 SELECT @ Count = COUNT ( * ) FROM @ Databases SET @ servername = HOST_NAME() --Provide path name here dont give \ in the end. SET @ path = '\\ramesh\LS' WHILE( @ I <@ Count ) BEGIN SET @ I =@ I + 1 SELECT @ databasename = [Name] FROM @ Databases WHERE Sno =@ I SET @ stmt = 'BACKUP DATABASE ' + '[' +@ databasename + ']' + ' TO DISK= ' + '''' +@ path + '\' +@ servername + '_' +@ databasename + '_' + REPLACE (...

SSRS REPORTS SUBSCRIPTION SCHEDULES AND NAMES OF THE REPORTS

 The below query will gives you the SSRS subscription scheduled information from SQL Server end. DECLARE @ jobName VARCHAR ( 100 ) SET @ jobName = '52B9C43E-748E-40B9-8FC9-A95369E2ED73' SELECT jobs.date_created,JOBS.[NAME],jobschedule.next_run_date, NextRunTime = stuff(stuff( right ( '00000' + cast (jobschedule.next_run_time as varchar ), 6 ), 3 , 0 , ':' ), 6 , 0 , ':' ) ,schedules.[enabled] AS ScheduleEnable from msdb.dbo.sysjobs jobs INNER JOIN msdb.dbo.sysjobschedules jobschedule ON jobs.JOB_ID = jobschedule.JOB_ID inner join msdb.dbo.sysschedules schedules ON schedules.schedule_id = jobschedule.schedule_id WHERE jobs.[name] =@ jobName The below query will tell you about which sql server agent job is running which report and its schedules SELECT distinct sj.[name] AS [Job Name], rs.SubscriptionID, c .[Name] AS [Report Name], c .[Path] FROM msdb..sysjobs AS sj INNER JOIN ReportServer..ReportSchedule AS rs ON sj.[name] = CA...