T-SQL query to get tables size in GB with no of rows in SQL server

 SELECT 

 s.name + '.' + t.Name AS [Table Name],

 part.rows AS [Total Rows In Table - Modified],

 CAST((SUM( DISTINCT au.Total_pages) * 8 ) / 1024.000 / 1024.000 AS NUMERIC(18, 3)) 

 AS [Table's Total Space In GB]

FROM 

 SYS.Tables t INNER JOIN sys.schemas s ON t.schema_id = s.schema_id

 INNER JOIN SYS.Indexes idx ON t.Object_id = idx.Object_id

 INNER JOIN SYS.Partitions part ON idx.Object_id = part.Object_id 

                    AND idx.Index_id = part.Index_id

 INNER JOIN SYS.Allocation_units au ON part.Partition_id = au.Container_id

 INNER JOIN SYS.Filegroups fGrp ON idx.Data_space_id = fGrp.Data_space_id

 INNER JOIN SYS.Database_files Df ON Df.Data_space_id = fGrp.Data_space_id

WHERE t.Is_ms_shipped = 0 AND idx.Object_id > 255 

GROUP BY t.Name, s.name, part.rows

ORDER BY [Table's Total Space In GB] DESC


Retrieve SSRS report schedule info through SQL

 --Retrieve SSRS report schedule info through SQL

Grabs information about what reports in SSRS are scheduled, when are they due to be sent out, 

report parameters and any errors in the last run etc.

Please note that the XPath on the [Parameters] column varies based on what parameters are setup in your own report. 

Please adjust them for your own purpose. Also the 'Prompt' value for a parameter is not stored, so you might have to 

join back to the relevant table for a friendly name.

Also there is additional info in the [ExtensionSetting].

=============================================================

  SELECT

[Locale],

[InactiveFlags],

'Next Run Date' = CASE next_run_date

WHEN 0 THEN null

ELSE

substring(convert(varchar(15),next_run_date),1,4) + '/' +

substring(convert(varchar(15),next_run_date),5,2) + '/' +

substring(convert(varchar(15),next_run_date),7,2)

END,

'Next Run Time' = isnull(CASE len(next_run_time)

WHEN 3 THEN cast('00:0'

+ Left(right(next_run_time,3),1)

+':' + right(next_run_time,2) as char (8))

WHEN 4 THEN cast('00:'

+ Left(right(next_run_time,4),2)

+':' + right(next_run_time,2) as char (8))

WHEN 5 THEN cast('0' + Left(right(next_run_time,5),1)

+':' + Left(right(next_run_time,4),2)

+':' + right(next_run_time,2) as char (8))

WHEN 6 THEN cast(Left(right(next_run_time,6),2)

+':' + Left(right(next_run_time,4),2)

+':' + right(next_run_time,2) as char (8))

END,'NA'),

Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="TO"])[1]','nvarchar(50)') as [To]

,Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="RenderFormat"])[1]','nvarchar(50)') as [Render Format]

,Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="Subject"])[1]','nvarchar(50)') as [Subject]

        ---Example report parameters: StartDateMacro, EndDateMacro & Currency.

,Convert(XML,[Parameters]).value('(//ParameterValue/Value[../Name="StartDateMacro"])[1]','nvarchar(50)') as [Start Date]

,Convert(XML,[Parameters]).value('(//ParameterValue/Value[../Name="EndDateMacro"])[1]','nvarchar(50)') as [End Date]

,Convert(XML,[Parameters]).value('(//ParameterValue/Value[../Name="Currency"])[1]','nvarchar(50)') as [Currency]

,[ModifiedDate]

,S.[Description]

,[LastStatus]

,[EventType]

,[LastRunTime]

,[DeliveryExtension]

,[Version]

FROM [ReportServer$LIVE].[dbo].[Subscriptions] S

INNER JOIN ReportServer$LIVE.dbo.ReportSchedule R ON S.SubscriptionID = R.SubscriptionID

INNER JOIN msdb.dbo.sysjobs J ON Convert(nvarchar(128),R.ScheduleID) = J.name

INNER JOIN msdb.dbo.sysjobschedules JS ON J.job_id = JS.job_id


TempDB-Move TempDB Script in SQL Server

 --Move_tempdb.sql

/*

Author: Brent Ozar

Original link: https://www.brentozar.com/archive/2017/11/move-tempdb-another-drive-folder/

*/

USE MASTER

GO

SELECT 'ALTER DATABASE tempdb MODIFY FILE (NAME = [' + f.name + '],'

    + ' FILENAME = ''Z:\MSSQL\DATA\' + f.name

    + CASE WHEN f.type = 1 THEN '.ldf' ELSE '.mdf' END

    + ''');'

FROM sys.master_files f

WHERE f.database_id = DB_ID(N'tempdb');


Notification --Email SP Results in SQL Server

 --Notification --Email SP Results

--https://www.sqlservercentral.com/scripts/email-sp-results

This piece of code will allow you to add a stored procedure name as a parameter, and will then take the output results of the procedure and email it to an address of your choosing, automatically formatting the results into an HTML table embedded in the email for clear and easy reading, or alternatively as an attachment.


Regretfully, there are a few limitations which I will eventually get around to fixing when I am interested in this again, namely:


Uses a static 100 length on the output columns instead of dynamically applying a length by referencing the source data

Cannot use procedures that have a blank column aliased ie.SELECT  ' ' as Store FROM table.

Requires Ad Hoc Distributed Queries server setting to be enabled.

Change cursors into tally or alternate loop

Customization of colours, table length, font etc.

It is executed as follows:


EXEC DBAdmin.[dbo].[dba_EmailProcResults] @vcProcName='sp_lock'


,@vcDatabaseName='master'


, @vcRecipients='<UrEmailHere>'


, @vcDynamicSubject = 'Log Backup Check'

=============================================================

 USE [tempdb]

GO

CREATE PROC [dbo].[dba_EmailProcResults]  --'dba_CheckLogbackups','DBAdmin', 'nicholaswilliams@', @vcDynamicSubject = 'Log Backup Check'

@vcProcName VARCHAR(1000)

,@vcDatabaseName VARCHAR(100)

,@vcRecipients VARCHAR(1000)

,@vcSchemaName VARCHAR(100) = NULL

,@bAttachQueryAsFile BIT = 0

,@vcDynamicSubject VARCHAR(100) = 'Query results'


AS

/*

sp_configure 'Show Advanced Options', 1

GO

RECONFIGURE

GO

sp_configure 'Ad Hoc Distributed Queries', 1

GO

RECONFIGURE

GO

*/

/*

Author: Nicholas Williams

Date: 20140519

Desc: Uses a proc as in input and email's the results.


Note: still plenty to update...

Procs cant reference non existant objects, even if logic would prevent them from being used ie. conditional if clause refering to non-existent table, as the remote call seems to force a parse on the query.

yet to make the table columns "smart" in terms of data lengths.

Requires Distributed Queries :/

add in a '?' reference help parameter

Cannot handle '' or blank string columns aliased as an actual column... need to fix.


@attach_query_result_as_file = 1,

    @query_attachment_filename='filename.csv',

*/

SET NOCOUNT ON

DECLARE

@vcServerName VARCHAR(100)

,@vcString VARCHAR(325)

,@vcColumnName VARCHAR(200)

,@nvcTable1 NVARCHAR(MAX)

,@nvcQuery NVARCHAR(MAX)

,@nvcBody NVARCHAR(MAX)

,@vcHeader VARCHAR(1000)

,@vcTrailer VARCHAR(1000)


DECLARE @MyTable TABLE

(

string NVARCHAR(MAX)

)


IF (@vcSchemaName) IS NULL

BEGIN

SET @vcSchemaName = 'dbo'

PRINT 'Warning: Schema name was null, setting it to dbo as a default.'

END


SET @vcServerName = 'Server='+(SELECT @@SERVERNAME)+';Trusted_Connection=yes;'

SELECT @vcString = 'EXEC ['+@vcDatabaseName+'].['+@vcSchemaName+'].'+(REPLACE(@vcProcName, '''',''''''))+''


EXEC ('

SELECT * INTO ##MyTempTable 

FROM OPENROWSET(''SQLNCLI'', '''+@vcServerName+''','''+@vcString+''')

')

IF (SELECT COUNT(1) FROM ##MyTempTable) >=1

BEGIN


IF @bAttachQueryAsFile <> 1

BEGIN

SET @vcHeader = '

<html><style>table{font-family:Arial;font-size:10;background=black;

color:Black;}html, body, title, 

head{font-family:Arial;font-size:10;background:white;color:Black;}th{font-family:Arial;font-size:15;background=orange;color:Black;}

{font-family:Arial;font-size:10;color:Black;background:White;align:left;valign:top;}</style><body><p><font face="Arial" size="2">Greetings,<br><br>Please see below.<br></font></p>'

SET @vcTrailer = '</body></html>'

--SELECT * FROM ##MyTempTable

SET @nvcTable1 = 

'<br>'+

'<table width=600 border=1 cellspacing=0 cellpadding=5>'+

'<tr style="background-color: White;">'

SET @nvcQuery = '

SELECT CAST((SELECT '

DECLARE column_cursor SCROLL CURSOR FOR

SELECT name FROM tempdb.sys.columns

WHERE object_id = 

(

SELECT id FROM tempdb.dbo.sysobjects

WHERE id = OBJECT_ID('tempdb..##MyTempTable')

)

/*

Need to put in a check to ensure that the result set is less than 4MB, so that it can be emailed.

*/

OPEN column_cursor

/*

Note: The odd use of the double fetch statement here is to force the force record of the fetch to be formatted differently, ie. no "," for the first record of the select.

hmm... now that I have changed how the select statement is used, i dont think i need it in the cursor anymore... change later.

*/

   FETCH FIRST FROM column_cursor

   INTO @vcColumnName


    SET @nvcQuery = ISNULL(@nvcQuery,'')+

'

['+@vcColumnName+'] AS ''td'','''''

SET @nvcTable1 = @nvcTable1 +

'<th width=100>'+@vcColumnName+'</th>'

   FETCH NEXT FROM column_cursor

   INTO @vcColumnName


WHILE @@FETCH_STATUS = 0

BEGIN 

SET @nvcTable1 = @nvcTable1 +

'<th width=100>'+@vcColumnName+'</th>'


SET @nvcQuery = ISNULL(@nvcQuery,'')+

'

,['+@vcColumnName+'] AS ''td'','''''


   FETCH NEXT FROM column_cursor

   INTO @vcColumnName


END

SET @nvcTable1 = @nvcTable1 +

'</tr>'

SET @nvcQuery = @nvcQuery+ 

'

FROM ##MyTempTable

FOR XML PATH(''tr''), TYPE) AS NVARCHAR(MAX))'

SET @vcTrailer= '</table><br><br><font face="Arial" size="2"><p>Regards</p><font face="Arial" size="2"><p>UrCompanyName<br></font><font size="1">Microsoft SQL Sever<br><br>Sender info:<br>'+'User - '+SYSTEM_USER+'<br>'+'Procedure - '+@vcProcName+'<br>'+'Database - '+@vcDatabaseName+'<br>'+'Server - '+isnull(@@SERVERNAME,'')+'<br></font></p>'

CLOSE column_cursor

DEALLOCATE column_cursor


INSERT INTO @MyTable

EXEC (@nvcQuery)


SELECT @nvcQuery = String FROM @MyTable

SELECT @nvcBody = @vcHeader+@nvcTable1+@nvcQuery+@vcTrailer

--SELECT @nvcBody


EXEC msdb.dbo.sp_send_dbmail   

@recipients = @vcRecipients

,@subject = @vcDynamicSubject

,@body = @nvcBody

,@body_format = 'HTML'  


END

ELSE

BEGIN

EXEC msdb.dbo.sp_send_dbmail   

@recipients = @vcRecipients

,@subject = @vcDynamicSubject

,@body = 'Please View attached file for query results.'

,@body_format = 'Text' 

,@attach_query_result_as_file = 1

,@query_attachment_filename = 'QueryResults.xls'

,@query_result_separator = ' '

,@query_result_no_padding = 0

,@query = 'SET NOCOUNT ON SELECT * FROM ##MyTempTable SET NOCOUNT OFF'

END


END

DROP TABLE ##MyTempTable

SET NOCOUNT OFF


/*

set a variable for the table width and the column widths.

add to each length for table for each iteration of the cursor.

look at sys.columns table for data type of the columns to manage table size effectively.

-- td = tranDate,''''

*/

SET NOCOUNT OFF


T-SQL Script to get SQL Data & Log Files Free Space & Space Used of all databases

 --TSQL Script to get SQL Data & Log Files Free Space & Space Used of all databases


Create Table #dbInfo (dId smallint, dbName sysname, gId smallint NULL, segName varchar(256) NULL, 

       filName varchar(520) NULL, sizeMB decimal(10,2) null, 

       usedMB decimal(10,2) null, freeMB decimal(10,2) null, 

       pcntUsed decimal(10,2) null, pcntFree decimal(10,2) null)

Declare @sSql varchar(1000)

Set @sSql = 'Use [?];

Insert #dbInfo (dId, dbName, gid, segName, filName, sizeMB, usedMB)

Select db_id(), db_name(), groupid, rtrim(name), filename, Cast(size/128.0 As Decimal(10,2)), 

Cast(Fileproperty(name, ''SpaceUsed'')/128.0 As Decimal(10,2))

From dbo.sysfiles Order By groupId Desc;'

Exec sp_MSforeachdb @sSql

Update #dbInfo Set

freeMB = sizeMB - usedMB,

pcntUsed = (usedMB/sizeMB)*100,

pcntFree = ((sizeMB-usedMB)/sizeMB)*100


select * from #dbInfo 

--where dbname in ('works')

--where filname like '%G:\%' order by freemb desc -- Put drive name here for files residing on specific drive

drop table #dbInfo


Enable the SQL Server Agent in Linux

 sudo /opt/mssql/bin/mssql-conf set sqlagent.enabled true 

sudo systemctl restart mssql-server


Microsoft Article

Error 22022: SQLServerAgent is not currently running so it cannot be notified of this action

……………………………………………………………………………………………………………………………………………………………………………….

 Error 22022: SQLServerAgent is not currently running so it cannot be notified of this action.”

 If you receive this error message, first make sure that the SQL Server Agent service is running. To do this, follow these steps:

……………………………………………………………………………………………………………………………………………………………………………….

1.Click Start, click Run, type Services.msc, and then click OK.


2.In the Services window, locate the SQL Server Agent service.

3.Make sure that the value of the Status column of the SQL Server Agent service is Running.

then 


EXEC sp_configure ‘show advanced’, 1;

RECONFIGURE;

EXEC sp_configure ‘allow updates’, 0;

RECONFIGURE;

EXEC sp_configure ‘Agent XPs’, 1;

RECONFIGURE;

GO

--create a tample table to gather the information of error log files in SQL Server

 --create a tample table to gather the information of error log files

CREATE TABLE #ErrorLog
(
       Archieve INT,
       Dt DATETIME,
       FileSize INT
)
GO

INSERT INTO #ErrorLog
EXEC xp_enumerrorlogs
GO

--delete all the old log files if the size of all the log files is larger than 30GB
DECLARE @i int = 1;                                                  
DECLARE @Log_number int;
DECLARE @Log_Max_Size int = 40*1024; --here is the max size (M) of all the error log files we want to keep, change the value according to your requirement
DECLARE @SQLSTR VARCHAR(1000);

SET @Log_number = (SELECT COUNT(*) FROM #ErrorLog);

IF (SELECT COUNT(FileSize/1024/1024) FROM #ErrorLog) >= @Log_Max_Size
BEGIN
       WHILE @i <= @Log_number
              BEGIN
                     SET @SQLSTR = 'DEL C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\ERRORLOG.' + CONVERT(VARCHAR,@i);
                     EXEC xp_cmdshell @SQLSTR;
                     SET @i =@i + 1;
              END
END

DROP TABLE #ErrorLog

Delete Bakups older than 1 day in SQL Server

--Delete Bakups older than 1 day in SQL Server

DECLARE @DeleteDate datetime

 SET @DeleteDate = DateAdd(day, -1, GetDate()) 

  EXECUTE master.sys.xp_delete_file 0, -- FileTypeSelected (0 = FileBackup, 1 = FileReport)

   N'C:\Backup\msdb', -- folder path (trailing slash)

    N'bak', -- file extension which needs to be deleted (no dot)

     @DeleteDate, -- date prior which to delete 

     1 -- subfolder flag (1 = include files in first subfolder level, 0 = not) 

Generate and change all SQL databases to a different compatibility level - Alter SQL Compatibility Level


SELECT name, compatibility_level  

FROM sys.databases where compatibility_level not in(120) -- SQL Server 2012 compatibility_level is 120

---------------------------------

SELECT name, compatibility_level  

FROM sys.databases 

---------------------------

/* Start Of Code */


DECLARE @SQL VARCHAR(max)  = ''

,             @CompLevel int = 120 -- Need to change the compatibility_level as per SQL Server version


SELECT @SQL += 'ALTER DATABASE ' + quotename(NAME) + ' SET COMPATIBILITY_LEVEL = ' + cast(@CompLevel as char (3)) + ';' + CHAR(10) + CHAR(13)

FROM sys.databases

WHERE 

--COMPATIBILITY_LEVEL not in(120) 

--and 

 compatibility_level <> @CompLevel


PRINT @SQL

--EXEC (@SQL)



/* End Of Code */

Finding an object in server in SQL Server using SP_MSFOREACHDB

 Today one of my friend asked me that she has created a stored procedure in a database, suddenly she has recognized she has forgotten the database in which the procedure was created. We suggested him to use the below query which searches each and every database in the server and returns the database name and the object name that is specified in where clause.

SP_MSFOREACHDB searches the objects in all databases in the server.

Example:

SP_MSFOREACHDB
'
USE ?
select
    ''?'' Database_Name
   , Name Object_name
from sys.procedures
WHERE name LIKE ''USP_Test''

Using SQL Server script to get list of users and their database roles in SQL Server

use Database_Name;

go 

SELECT  dPrinc.name AS [Members],dRole.name AS [Database Role Name]

FROM sys.database_role_members AS dRo  

JOIN sys.database_principals AS dPrinc  

    ON dRo.member_principal_id = dPrinc.principal_id  

JOIN sys.database_principals AS dRole  

    ON dRo.role_principal_id = dRole.principal_id;

About Update stats and Indexing Optimize in SQL Server

 Update stats:

Updates query optimization statistics on a table or indexed view. By default, the query optimizer already updates statistics as necessary to improve the query plan; in some cases, you can improve query performance by using UPDATE STATISTICS or the stored procedure sp_updatestats to update statistics more frequently than the default updates.

Updating statistics ensures that queries compile with up-to-date statistics. However, updating statistics causes queries to recompile. We recommend not updating statistics too frequently because there is a performance tradeoff between improving query plans and the time it takes to recompile queries. The specific tradeoffs depend on your application. UPDATE STATISTICS can use tempdb to sort the sample of rows for building statistics.

Indexing Optimize:

SQL Server Indexes are special data structures associated with tables or views that help speed up the query. 

Reorganize or rebuild a fragmented index in SQL Server by using SQL Server Management Studio or Transact-SQL. The SQL Server Database Engine automatically modifies indexes whenever insert, update, or delete operations are made to the underlying data. Over time, these modifications can cause the information in the index to become scattered in the database (fragmented). Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file. Heavily fragmented indexes can degrade query performance and cause your application to respond slowly, especially scan operations.

You can remedy index fragmentation by reorganizing or rebuilding an index.

Creating, backing up, and restoring a database are critical databases administration tasks that we must perform regardless of Operating System platform. Good news! If you had done these steps in Windows, the same steps apply in Linux. You just need to be aware of the file path structure.

 Creating a Database and Verifying Its Data and Log Files

To create a database and verify its data and log files, perform the following steps:

1. Connect to your Ubuntu Server from your PuTTY remote terminal if not already connected.

2. Connect to your SQL Server:

sqlcmd -S localhost -U USERNAME -P ‘PASSWORD’

3. Create the database, sqldbanow:

create database DVSQLRocks
go

4. Specify the database to use:

use sqldbanow
go

you will see the message “Changed database context to ‘DVSQLRocks'”.

5. Create a table, major_conference, and insert two rows of data:


create table major_conference (id int, name nvarchar(50), start_date datetime)
go
insert into major_conference values (1, ‘SQL Saturday LA’, ‘2019-06-15’)
insert into major_conference values (2, ‘SQL PASS Summit’, ‘2019-11-05’)
insert into major_conference values (3, ‘AWS Re:Invent’, ‘2019-12-02’)
go

Figure 1: Create a database and table and insert data into a table


6. Find the location of the data and log files for the newly created databases:


select DB_NAME(database_id), physical_name from sys.master_files
where DB_NAME(database_id) = ‘sqldbanow’
go

Figure 2: Query the sys.master_files to find the location of the data and log files

Backing up a Database:

To backup a database, perform the following steps:

1. Connect to your SQL Server if not already connected:


sqlcmd -S localhost -U USERNAME -P ‘PASSWORD’

2. Backup our sample database, sqldbanow:


backup database [sqldbanow] to disk = N’/var/opt/mssql/data/sqldbanow_20210127.bak’
go

Figure 3: Backup a database

If executed successfully, you will see the confirmation message as in Figure 3.

Restoring up a Database

To restore a database, perform the following steps:

1. Find the location of the backup file. In our example, it is 

/var/opt/mssql/data/sqldabanow_20210127.bak.

2. Connect to your SQL Server if not already connected:
sqlcmd -S localhost -U USERNAME -P ‘PASSWORD’

3. Change the database to master:


use master
go

Just as in SQL on Windows, you can’t restore a database if it is in use.

4. Restore the database. In this example, we will restore and replace the existing sqldbanow databases:
use master
go
restore database [sqldbanow] from disk = N’/var/opt/mssql/data/sqldbanow_20210127.bak’ with norecovery, replace



Figure 4: Restore a database



How many IP Addresses we require for setting up Active\Active SQL Server cluster with Analysis services?

  •  2 Windows nodes – Public
  •  2 Private IP Addresses – Private
  • 1 Windows Virtual Cluster Name
  • 1 MSDTC (Optional)
  • 1 SQL Server Virtual Network Name
  • 1 SQL Server Analysis Services

How many IP Addresses we require for setting up Active\Passive SQL Server cluster?

  • 2 Windows nodes – Public
  • 2 Private IP Addresses – Private
  • 1 Windows Virtual Cluster Name
  • 1 MSDTC (optional)
  • 1 SQL Server Virtual Network Name

SQL Server DBA: Database in Recovery Pending state

 I have recently encountered an issue where one of the databases suddenly moved to Recovery Pending status after SQL Server fail-over.

I checked the database and it is in Recovery Pending status. Recovery Pending means that recovery cannot be started. Until the cause is fixed, recovery cannot run and the database cannot come online. I then checked the drives in the server and all drives are accessible.Then

Just i took database offline and bring back to online and  database is automatically recovered.

OR

if multiple database are in recovery pending after SQL Fail over or  SQL Server Restart..

I had to restart the SQL Server service manually and the database is automatically recovered.


SQL Server script to get a list of all jobs that access a database in SQL server?

 This will work for Agent jobs that have T-SQL job steps pointing to a database.

Databases Last Accessed

 Running the script below will show you the last access date and time of all databases since the day sql server was rebooted

-- Get Last Restart time
SELECT
crdate as 'Last Rebooted On'
FROM
sysdatabases
WHERE name = 'tempdb'
go
 
-- Get last database access time (Null - no access since last reboot)
SELECT name, last_access =(SELECT X1= max(LA.xx)
FROM ( SELECT xx =
MAX(last_user_seek)
WHERE MAX(last_user_seek)is not null
UNION ALL
SELECT xx = max(last_user_scan)
where max(last_user_scan)is not null
UNION ALL
SELECT xx = max(last_user_lookup)
WHERE MAX(last_user_lookup) is not null
UNION ALL
SELECT xx =max(last_user_update)
WHERE MAX(last_user_update) is not null) LA)
FROM master.dbo.sysdatabases sd
LEFT OUTER JOIN sys.dm_db_index_usage_stats s
on sd.dbid= s.database_id
GROUP BY sd.name
ORDER BY name