Ricky Valencia | SQL Conjuror

SQL Server – View Orphaned Users

By Enrique 15/01/2018 15/01/2018 Database Administration, T-SQL

Orphaned users in SQL Server occur when a database user is based on a login in the master database, but the login no longer exists in master. This can occur when the login is deleted, or when the database is moved to another server where the login does not exist. This topic describes how to find orphaned users, and remap them to logins.

Below is a script that I use to view orphaned users.

DECLARE cur CURSOR FAST_FORWARD FOR 
SELECT name FROM sys.databases 
WHERE database_id > 4 

OPEN cur  

DECLARE @SQL NVARCHAR(MAX), @DBName SYSNAME 
DECLARE @Results TABLE (DBName SYSNAME, UserName SYSNAME, UserSID VARBINARY(MAX))  

FETCH NEXT FROM cur into @DBName  

WHILE @@FETCH_STATUS = 0 
BEGIN 
	SET @SQL = 'USE ' + @DBName + ';SELECT ''' + @DBName + ''' AS DBName, 
			UserName = name, UserSID = sid from sysusers 
			WHERE issqluser = 1 AND 
				(sid IS NOT NULL AND sid <> 0x0) AND 
				(LEN(sid) <= 16) AND SUSER_SNAME(sid) IS NULL'     

	INSERT INTO @Results 
	EXEC(@SQL)  

	FETCH NEXT FROM cur into @DBName  
END  

CLOSE cur 
DEALLOCATE cur  

SELECT * FROM @Results

Facebook

Google

Twitter

No comments

SQL Server – MAXIMUMERRORCOUNTREACHED Error (Maintenance Plan)

By Enrique 12/01/2018 29/04/2020 SQL SERVER, Troubleshooting

I setup a maintenance plan to backup my databases. I ran it and I hit the error below.

The problem is because there are a couple of databases that are having an error -transaction log full due to CHECKPOINT (This is a different story). Hence, I’m not able to backup these databases. If you look at the log closely, you will notice the errors (refer to the screenshot below).

So what I did was to deselect these databases to make the job run successfully.

Alternatively though, you increase the maximum error count to make the job go thru successfully despite hitting errors.

Right-click on the Backup Task then go to Properties.

2. Increase the number of the “MaximumErrorCount” parameter.

Cheers!

SQL Server – Reinitializing a Single Article In Transaction Replication

By Enrique 26/10/2017 24/04/2021 SQL SERVER, Database Administration

In this blog post, I will list down the steps on how to re-initialize just a single article in transaction replication

1. Turn off @allow_anonymous and @immediate_sync on the publication.

use PUBLICATION_DB_NAME
go
EXEC sp_changepublication
@publication = 'Replication_Test',
@property = N'allow_anonymous',
@value = 'false'
GO
               
EXEC sp_changepublication
@publication = 'Replication_Test',
@property = N'immediate_sync',
@value = 'false'
GO

The reason we have to disable @immediate_sync is that everytime you add a new article, and if @immediate_sync is enabled, it will cause the entire snapshot to be applied. Our objective is to only apply a particular article.

2. Add new article.

EXEC sp_addarticle
@publication = 'Replication_Test',
@article = 'REPL_TABLE',
@source_object = 'REPL_TABLE',
@force_invalidate_snapshot = 1

3. Refresh the subscription

EXEC sp_refreshsubscriptions @publication = 'Replication_Test'
GO

4. Check the current snapshot agent history.

use distribution
go
select * from dbo.MSsnapshot_history

5. Start Snapshot agent.

EXEC sp_startpublication_snapshot @publication = 'Replication_Test';
GO

6. Check the Snapshot Agent history again. You should see a snapshot generated only for the newly added article/s.

7. Turn ON @allow_anonymous and @immediate_sync on the publication.

use PUBLICATION_DB_NAME
go

EXEC sp_changepublication
@publication = 'Replication_Test',
@property = N'immediate_sync',
@value = 'true'
GO


EXEC sp_changepublication
@publication = 'Replication_Test',
@property = N'allow_anonymous',
@value = 'true'
GO

This is how you can re-initialize only the required articles.

SQL Server – Check The Port Configured in SQL Server

By Enrique 18/08/2017 29/04/2020 SQL SERVER, Database Administration

Use the script below to find the tcp port number that the SQL Server is listening on.

SELECT local_tcp_port
FROM   sys.dm_exec_connections
WHERE  session_id = @@SPID
GO

SQL Server – Get Backupset Records

By Enrique 17/08/2017 29/04/2020 SQL SERVER, Database Administration

The script below retrieves the backupset records. A backup set contains the backup from a single, successful backup operation. RESTORE, RESTORE FILELISTONLY, RESTORE HEADERONLY, and RESTORE VERIFYONLY statements operate on a single backup set within the media set on the specified backup device or devices.

USE msdb ;
SELECT 
backup_start_date ,
backup_finish_date,
CAST(ROUND ((backup_size / 1024 / 1024), 0, 1) AS DECIMAL (18,0)) AS Backup_Size_MB ,
recovery_model,
[type]
FROM dbo.backupset
WHERE database_name = 'RBD_DB'
ORDER BY backup_start_date DESC

/*
Backup type. Can be:
D = Database
I = Differential database
L = Log
F = File or filegroup
G =Differential file
P = Partial
Q = Differential partial
Can be NULL.

Reference: https://technet.microsoft.com/en-us/library/ms186299(v=sql.110).aspx
*/

ORACLE – Investigate the Memory Structures of the Instance

By Enrique 10/08/2017 29/04/2020 ORACLE, Database Administration

Below is a script to show the current, maximum and minimum sizes of the SGA components that can be dynamically resized.

SELECT 
   component, 
   current_size, 
   min_size, 
   max_size    
FROM 
   v$sga_dynamic_components;

Execute the script below to Determine how much memory has been, and is currently, allocated to program global areas

SELECT 
   name, 
   value 
FROM 
   v$pgastat 
WHERE 
   name IN ('maximum PGA allocated','total PGA allocated');

SQL Server SSRS – Query the Report Server Execution Log

By Enrique 22/07/2017 12/06/2022 SQL SERVER, SSRS

Below is the T-SQL script to query the Report Server Execution Log. You may query either the dbo.ExecutionLog2 or dbo.ExecutionLog3 table (same as dbo.ExecutionLog2 with 2 fields renamed: ReportPath has been renamed to ItemPath and ReportAction has been renamed to ItemAction)

Below is the output of the query.

USE ReportServer 
GO

SELECT *
FROM dbo.ExecutionLog3
ORDER BY TimeStart DESC

SQL Server – Get Index Creation Date

By Enrique 19/07/2017 29/04/2020 SQL SERVER, Database Administration, T-SQL

There are many T-SQL scripts to get the index creation date but I always use this one.

 DECLARE @filename VARCHAR(500) 
SELECT @filename = CAST(value AS VARCHAR(500)) 
FROM fn_trace_getinfo(DEFAULT) 
WHERE property = 2 
  AND value IS NOT NULL 

-- Go back 4 files since default trace only keeps the last 5 and start from there.
SELECT @filename = substring(@filename, 0, charindex('_', @filename)+1) + convert(varchar, (convert(int, substring(left(@filename, len(@filename)-4), charindex('_', @filename)+1, len(@filename)))-4)) + '.trc'

SELECT 
       gt.EventClass, 
       gt.EventSubClass,
       te.Name AS EventName,
       gt.HostName, 
       gt.StartTime, 
       gt.DatabaseName,
       gt.ObjectName
FROM fn_trace_gettable(@fileName, DEFAULT) gt 
JOIN sys.trace_events te ON gt.EventClass = te.trace_event_id 
WHERE EventClass = 46
  and ObjectType = 22601
  and gt.DatabaseName <> 'tempdb'
ORDER BY StartTime desc;

SQL Server – Export SSRS Report to PDF in Landscape Format

By Enrique 20/06/2017 29/04/2020 SQL SERVER, SSRS

By default, your SSRS reports are generated in portrait format. The body of your report will be automatically resized when you add report items. When you deploy your reports, it will portray correctly in your browser. But when you export your report to PDF, you will notice that it is in portrait format. It is not presentable and it will have extra pages. You can prevent this from happening by exporting the PDF in landscape format. To do that, just follow the steps below.

Go to the properties of your report. (Right-click mouse just outside the body of your report). Set the width of the report to the landscape size of your A4 paper: 29.7 cm. Set the height of the report to 21 cm.

2. When you export the report, you will see extra blank pages. To avoid that, the size of the body should be less or equal to the size of the report margins

Set the width of the body to 26.7 cm Set the height of the body to 18 cm

So now, when you export your report to PDF, it will be in landscape format.

SQL Server – Check Database Properties

By Enrique 29/05/2017 29/04/2020 SQL SERVER, Database Administration

The T-SQL query below can be used to check the properties of your databases.

USE master
GO
SELECT  name AS 'Database_Name' ,
        snapshot_isolation_state AS 'Allow Snapshot Isolation' ,
        is_ansi_null_default_on AS 'ANSI NULL Default' ,
        is_ansi_nulls_on AS 'ANSI NULLS Enabled' ,
        is_ansi_padding_on AS 'ANSI Paddings Enabled' ,
        is_ansi_warnings_on AS 'ANSI Warnings Enabled' ,
        is_arithabort_on AS 'Arithmetic Abort Enabled' ,
        is_auto_close_on AS 'Auto CLOSE' ,
        is_auto_create_stats_on AS 'Auto Create Statistics' ,
        is_auto_shrink_on AS 'Auto Shrink' ,
        is_auto_update_stats_async_on AS 'Auto Update Statistics Asynchronously' ,
        is_auto_update_stats_on AS 'Auto Update Statistics' ,
        is_cursor_close_on_commit_on AS 'Close Cursor on Commit Enabled' ,
        is_concat_null_yields_null_on AS 'Concatenate Null Yields Null' ,
        is_db_chaining_on AS 'Cross-Database Ownership Chaining Enabled' ,
        is_date_correlation_on AS 'Data Correlation Optimization Enabled' ,
        is_read_only AS 'Database Read-Only' ,
        is_local_cursor_default AS 'Default Cursor' ,
        is_encrypted AS 'Encryption Enabled' ,
        is_arithabort_on AS 'Numeric Round-Abort' ,
        page_verify_option_desc AS 'Page Verify' ,
        is_parameterization_forced AS 'Parameterization' ,
        is_quoted_identifier_on AS 'Quoted Identifiers Enabled' ,
        is_read_committed_snapshot_on AS 'Read Committed Snapshot' ,
        is_recursive_triggers_on AS 'Recursive Triggers Enabled' ,
        user_access_desc AS 'Restrict Access' ,
        is_broker_enabled AS 'Service Broker Enabled' ,
        is_trustworthy_on AS 'Trustworthy'
FROM    sys.databases ;
GO

Facebook

Google

Twitter

Tag: Ricky Valencia

SQL Server – View Orphaned Users

SQL Server – MAXIMUMERRORCOUNTREACHED Error (Maintenance Plan)

SQL Server – Reinitializing a Single Article In Transaction Replication

SQL Server – Check The Port Configured in SQL Server

SQL Server – Get Backupset Records

ORACLE – Investigate the Memory Structures of the Instance

SQL Server SSRS – Query the Report Server Execution Log

SQL Server – Get Index Creation Date

SQL Server – Export SSRS Report to PDF in Landscape Format

SQL Server – Check Database Properties

Recent Posts

Archives

Categories

Recent Posts

Archives

Categories

Tag Cloud