the data file, log file or both.
Thursday 4 November 2021
Full Backup Vs. Differential Backup Vs. Log Backup
the data file, log file or both.
Monday 1 November 2021
Removing Invalid Logins (Both Server and Database Principals) from SQL Server
CREATE TABLE #InvalidUsers( [SID] VARBINARY(85) ,LOGIN_NAME SYSNAME ) INSERT INTO #InvalidUsers ( [SID] ,LOGIN_NAME ) EXEC sys.sp_validatelogins
- Whether the user owns a schema in the database (if so we need to change the schema ownership to dbo)
- Whether the user owns a the database. If so we need to change db ownership to a different user. In this example I will be changing the ownership to "sa"
We will insert the details into another temporary table. I will explain part by part, but once the script is completed you can execute it as a whole. I have provided the completed script at the end.
We will use another system stored procedure to execute a query in all the databases in the SQL Server instance (sys.sp_MSforeachdb)
DECLARE @LoginName AS sysname ,@Sql AS NVARCHAR(2000) DECLARE cur CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR SELECT LOGIN_NAME FROM #InvalidUsers OPEN cur FETCH NEXT FROM cur INTO @LoginName WHILE @@FETCH_STATUS = 0 BEGIN SET @Sql = CONCAT( 'USE ?;INSERT INTO #InvalidUserDbInfo SELECT DB_NAME(),DP.sid,DP.name, S.[name] as OwnedSchema FROM sys.database_principals AS DP LEFT JOIN sys.schemas as S on S.principal_id = DP.principal_id WHERE [type] = ''U'' and DP.name = ''',@LoginName,'''' ) EXEC sys.sp_MSforeachdb @Sql FETCH NEXT FROM cur INTO @LoginName END CLOSE cur DEALLOCATE cur
DECLARE @LoginName AS sysname ,@Sql AS NVARCHAR(2000) ,@DBName AS NVARCHAR(128) ,@DbNameCurrent AS NVARCHAR(128) = '' ,@SqlSyntax AS NVARCHAR(MAX) = '' ,@OwnedSchema AS VARCHAR(128) DECLARE cur CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR SELECT IUDI.DBNAME ,IUDI.LOGIN_NAME ,IUDI.OWNED_SCH FROM #InvalidUserDbInfo AS IUDI WHERE IUDI.DBNAME <> 'master' ORDER BY IUDI.DBNAME OPEN cur FETCH NEXT FROM cur INTO @DBName,@LoginName,@OwnedSchema WHILE @@FETCH_STATUS = 0 BEGIN IF @DbNameCurrent <> @DBName BEGIN IF @DbNameCurrent <> '' SET @SqlSyntax += CONCAT(CHAR(13),'---------------------------------',CHAR(13),CHAR(13)) SET @SqlSyntax += CONCAT('USE [',@DBName,'];',CHAR(13),CHAR(13)) SET @DbNameCurrent = @DBName END IF (ISNULL(@OwnedSchema,'') <> '' AND ISNULL(@OwnedSchema,'') <> @LoginName) BEGIN SET @SqlSyntax += CONCAT('ALTER AUTHORIZATION ON SCHEMA::[',@OwnedSchema,'] TO [dbo]', CHAR(13)) END SET @SqlSyntax += CONCAT('DROP USER IF EXISTS [',@LoginName,']',CHAR(13)) FETCH NEXT FROM cur INTO @DBName,@LoginName,@OwnedSchema END CLOSE cur DEALLOCATE cur SELECT CAST('<![CDATA[' + CHAR(13) + @SqlSyntax + CHAR(13) + ']]> ' AS XML) AS CleanupScript_Step_2
DECLARE @SqlSyntax AS NVARCHAR(MAX) = '' SET @SqlSyntax += CONCAT(' --== Changing Database Ownership ==--',CHAR(13),CHAR(13),'USE master;',CHAR(13)) SELECT @SqlSyntax = @SqlSyntax + CHAR(13) + CONCAT('ALTER AUTHORIZATION ON DATABASE::[',S.[name],'] TO [sa]') FROM sys.databases AS S JOIN #InvalidUsers AS U ON SUSER_SNAME(S.owner_sid) = U.LOGIN_NAME SET @SqlSyntax+= CONCAT(CHAR(13),CHAR(13)) ------------------------------------------------------------------------------------------ SELECT @SqlSyntax = @SqlSyntax + CHAR(13) + CONCAT('IF EXISTS (SELECT 0 FROM [sys].[server_principals] AS [SP] WHERE [SP].[name] = N''' ,U.LOGIN_NAME ,''')' ,CHAR(13),CHAR(9) ,'DROP LOGIN [',U.LOGIN_NAME,']',CHAR(13),CHAR(13) ) FROM [sys].[server_principals] AS [SP] JOIN #InvalidUsers AS U ON U.LOGIN_NAME = SP.[name] SELECT CAST('<![CDATA[' + CHAR(13) + @SqlSyntax + CHAR(13) + ']]> ' AS XML) AS CleanupScript_Step_3
CREATE TABLE #InvalidUsers( [SID] VARBINARY(85) ,LOGIN_NAME SYSNAME ) INSERT INTO #InvalidUsers ( [SID] ,LOGIN_NAME ) EXEC sys.sp_validatelogins ------------------------------------------------------------------------------------------ CREATE TABLE #InvalidUserDbInfo( DBNAME NVARCHAR(128) ,[SID] VARBINARY(85) ,LOGIN_NAME SYSNAME ,OWNED_SCH VARCHAR(128) ) DECLARE @LoginName AS sysname ,@Sql AS NVARCHAR(2000) ,@DBName AS NVARCHAR(128) ,@DbNameCurrent AS NVARCHAR(128) = '' ,@SqlSyntax AS NVARCHAR(MAX) = '' ,@OwnedSchema AS VARCHAR(128) DECLARE cur CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR SELECT LOGIN_NAME FROM #InvalidUsers OPEN cur FETCH NEXT FROM cur INTO @LoginName WHILE @@FETCH_STATUS = 0 BEGIN SET @Sql = CONCAT( 'USE ?;INSERT INTO #InvalidUserDbInfo SELECT DB_NAME(),DP.sid,DP.name, S.[name] as OwnedSchema FROM sys.database_principals AS DP LEFT JOIN sys.schemas as S on S.principal_id = DP.principal_id WHERE [type] = ''U'' and DP.name = ''',@LoginName,'''' ) EXEC sys.sp_MSforeachdb @Sql FETCH NEXT FROM cur INTO @LoginName END CLOSE cur DEALLOCATE cur ------------------------------------------------------------------------------------------ DECLARE cur CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR SELECT IUDI.DBNAME ,IUDI.LOGIN_NAME ,IUDI.OWNED_SCH FROM #InvalidUserDbInfo AS IUDI WHERE IUDI.DBNAME <> 'master' ORDER BY IUDI.DBNAME OPEN cur FETCH NEXT FROM cur INTO @DBName,@LoginName,@OwnedSchema WHILE @@FETCH_STATUS = 0 BEGIN IF @DbNameCurrent <> @DBName BEGIN IF @DbNameCurrent <> '' SET @SqlSyntax += CONCAT(CHAR(13),'---------------------------------',CHAR(13),CHAR(13)) SET @SqlSyntax += CONCAT('USE [',@DBName,'];',CHAR(13),CHAR(13)) SET @DbNameCurrent = @DBName END IF (ISNULL(@OwnedSchema,'') <> '' AND ISNULL(@OwnedSchema,'') <> @LoginName) BEGIN SET @SqlSyntax += CONCAT('ALTER AUTHORIZATION ON SCHEMA::[',@OwnedSchema,'] TO [dbo]', CHAR(13)) END SET @SqlSyntax += CONCAT('DROP USER IF EXISTS [',@LoginName,']',CHAR(13)) FETCH NEXT FROM cur INTO @DBName,@LoginName,@OwnedSchema END CLOSE cur DEALLOCATE cur ------------------------------------------------------------------------------------------ SET @SqlSyntax += CONCAT(CHAR(13),CHAR(13) ,' --== Changing Database Ownership ==--',CHAR(13),CHAR(13) ,'USE master;',CHAR(13)) SELECT @SqlSyntax = @SqlSyntax + CHAR(13) + CONCAT('ALTER AUTHORIZATION ON DATABASE::[',S.[name],'] TO [sa]') FROM sys.databases AS S JOIN #InvalidUsers AS U ON SUSER_SNAME(S.owner_sid) = U.LOGIN_NAME SET @SqlSyntax+= CONCAT(CHAR(13),CHAR(13)) ------------------------------------------------------------------------------------------ SELECT @SqlSyntax = @SqlSyntax + CHAR(13) + CONCAT('IF EXISTS (SELECT 0 FROM [sys].[server_principals] AS [SP] WHERE [SP].[name] = N''' ,U.LOGIN_NAME,''')' ,CHAR(13),CHAR(9) ,'DROP LOGIN [',U.LOGIN_NAME,']',CHAR(13),CHAR(13)) FROM [sys].[server_principals] AS [SP] JOIN #InvalidUsers AS U ON U.LOGIN_NAME = SP.[name] SELECT CAST('<![CDATA[' + CHAR(13) + @SqlSyntax + CHAR(13) + ']]> ' AS XML) AS CleanupScript_Step_3
Sunday 31 October 2021
Attaching SQL Database with FILESTREAM data
Have you ever come across a situation where you need to attach a database using existing files (mdf, ldf & file stream data), but you don't have an option to mention the file stream folder when you use SSMS.
This won't be an issue if the filestream folder resides on its original path.
Let's look at an example. I have SQL data, log and filestream data which I will first attach using SSMS.
- Right-click on the database folder in SSMS and choose "Attach"
- Click the "Add" button and choose the data file (.mdf). Upon selecting the mdf file it will list the associated ldf file as well. But make sure to change the path accordingly from the second pane.
- Click on Ok.
Even if you try to generate the scripts using SSMS it doesn't provide you with the option to include the filestream folder.
USE [master] GO CREATE DATABASE [SampleSqlDatabase] ON ( FILENAME = N'D:\_SQL_DATA\SampleSqlDatabase_Data.mdf' ), ( FILENAME = N'D:\_SQL_LOG\SampleSqlDatabase_Log.ldf' ) FOR ATTACH GO
We can use the above code snippet and include the file stream path.
USE [master] GO CREATE DATABASE [SampleSqlDatabase] ON (FILENAME = N'D:\_SQL_DATA\SampleSqlDatabase_Data.mdf'), (FILENAME = N'D:\_SQL_LOG\SampleSqlDatabase_Log.ldf'), FILEGROUP NewFSGroup CONTAINS FILESTREAM ( NAME = 'NewDatabaseFileStream' ,FILENAME = 'D:\_SQL_DATA\SampleSqlDatabase_FSData_New' ) FOR ATTACH GO
Then you will be able to attach the database without an issue. You can use any name for the FILEGROUP and NAME (under the FILEGROUP). It doesn't need to be the same as your source database.
Hope this will be a helpful tip to you.
SQL Server Transaction Log file behaviour in Full Recovery Mode
In a previous blog post, I have explained different types of recovery models in SQL Server databases. During the explanations, I have stated that the transaction log data will be truncated during certain backup types. We will look into this more closely and see how it works.
Note: Even the details are removed from the transaction log file, the physical size may not change unless you issue a DBCC SHRINKFILE command. Only the file space will be re-used once the details are truncated.
We will start with creating a sample database named 'SqlTransactionLogExample'
USE master; GO CREATE DATABASE [SqlTransactionLogExample]; --== Makesure the database is in "Full" recovery model ==-- SELECT [name] ,DATABASEPROPERTYEX([name],'Recovery') AS recovery_model FROM sys.databases WHERE [name] = 'SqlTransactionLogExample'
We will create one table and insert records (1,000,000 rows to see a significant change in the file size).
USE SqlTransactionLogExample;
GO
CREATE TABLE dbo.UidInformation(
Id UNIQUEIDENTIFIER NOT NULL
,PayLoad CHAR(1000) NOT NULL CONSTRAINT [DF_SampleTable] DEFAULT (SPACE(1000))
)
INSERT INTO dbo.UidInformation (
Id
,PayLoad
)
SELECT
NEWID() AS Id,
([100000].Num
+ [10000].Num
+ [1000].Num
+ [100].Num
+ [10].Num
+ [1].Num) AS PayLoad
FROM (
SELECT 1 AS Num UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL
SELECT 0
) AS [1]
CROSS JOIN (
SELECT 10 AS Num UNION ALL SELECT 20 UNION ALL SELECT 30 UNION ALL
SELECT 40 UNION ALL SELECT 50 UNION ALL SELECT 60 UNION ALL
SELECT 70 UNION ALL SELECT 80 UNION ALL SELECT 90 UNION ALL
SELECT 0
) AS [10]
CROSS JOIN (
SELECT 100 AS Num UNION ALL SELECT 200 UNION ALL SELECT 300
UNION ALL SELECT 400 UNION ALL SELECT 500 UNION ALL SELECT 600
UNION ALL SELECT 700 UNION ALL SELECT 800 UNION ALL SELECT 900
UNION ALL SELECT 0
) AS [100]
CROSS JOIN (
SELECT 1000 AS Num UNION ALL SELECT 2000 UNION ALL SELECT 3000
UNION ALL SELECT 4000 UNION ALL SELECT 5000 UNION ALL SELECT 6000
UNION ALL SELECT 7000 UNION ALL SELECT 8000 UNION ALL SELECT 9000
UNION ALL SELECT 0
) AS [1000]
CROSS JOIN (
SELECT 10000 AS Num UNION ALL SELECT 20000 UNION ALL SELECT 30000
UNION ALL SELECT 40000 UNION ALL SELECT 50000 UNION ALL SELECT 60000
UNION ALL SELECT 70000 UNION ALL SELECT 80000 UNION ALL SELECT 90000
UNION ALL SELECT 0
) AS [10000]
CROSS JOIN (
SELECT 100000 AS Num UNION ALL SELECT 200000 UNION ALL SELECT 300000
UNION ALL SELECT 400000 UNION ALL SELECT 500000 UNION ALL SELECT 600000
UNION ALL SELECT 700000 UNION ALL SELECT 800000 UNION ALL SELECT 900000
UNION ALL SELECT 0
) AS [100000]
WHERE(
[100000].Num
+ [10000].Num
+ [1000].Num
+ [100].Num
+ [10].Num
+ [1].Num
) BETWEEN 1 AND 1000000
ORDER BY ([100000].Num + [10000].Num + [1000].Num + [100].Num + [10].Num + [1].Num)
Now we will inspect the number of entries in the transaction log.
SELECT COUNT(*) FROM sys.fn_dblog (NULL,NULL) --Returns 2511475
Note: Record count may be varied in the transaction log of your database.
We will check the size of the transaction log file
Now we will take a full database backup
USE master; GO BACKUP DATABASE [SqlTransactionLogExample] TO DISK = 'D:\_SQL_BACKUP\SqlTransactionLogExample_FullDB_Backup.bak' Now we will inspect number of entries in the transaction log file again. USE SqlTransactionLogExample GO SELECT COUNT(*) FROM sys.fn_dblog (NULL,NULL) --Returns 2511475
Note: There is a slight increment of the record count since there are new entries written to the log file during the database backup.
We will now inspect the transaction log entries
SELECT COUNT(*) FROM sys.fn_dblog (NULL,NULL) -- Returns 2511545
We will now take a backup of the transaction log
BACKUP LOG [SqlTransactionLogExample] TO DISK = 'D:\_SQL_BACKUP\SqlTransactionLogExample_LogBackup_AfterInsert.bak'
And if we inspect the number of entries in the log file it has drastically reduced.
SELECT COUNT(*) FROM sys.fn_dblog (NULL,NULL) --Returns 10
However, the space consumed by the log file is still the same. This is the reason I have mentioned it previously. In order to release the space, we have to issue the DBCC SHRINKFILE command.
--This is to find the name of the logical log file name SELECT * FROM sys.sysfiles --SqlTransactionLogExample_log DBCC SHRINKFILE('SqlTransactionLogExample_log',1)
Hope this will be helpful to you.
Saturday 30 October 2021
Recovery Models in SQL Server Databases
"Recovery Model" determines how long the data is to be kept in the transaction logs. It also allows what sort of backups and restores you can perform on the database.
Types of recovery models
There are three types of recovery models in SQL Server
- Simple
- Full
- Bulk-logged
How can I change the recovery model of a database?
To change using T-SQL statement use the following syntax:
USE master; ALTER DATABASE YourDBName SET RECOVERY SIMPLE
SELECT [name] ,DATABASEPROPERTYEX([name],'Recovery') AS recovery_model FROM sys.databases WHERE database_id > 4
Simple Recovery Model
- You cannot take backups of the transaction log explicitly
- A database backup will always contain both data and transaction log file
- Transaction log will not grow beyond what's been defined. The space in the transaction log file will be re-used (overwritten) once the transactions are written to the data file.
- In case of a disaster transaction occurred between the last full backup and the disaster timeline cannot be recovered. (E.g: Assume we have a database in which the backups are taken every hour. (8am, 9am etc.) In case a disaster happens at 10:45am, transactions between 10am and 10:45am will be lost)
Bulk-logged Recovery Model
- You can take the backup of the transaction log explicitly
- Log truncation won't happen once the transaction log backup is taken. Hence it needs to be truncated manually (Same as the "Full" recovery model)
- In the bulk-logged recovery model, certain operations are logged minimally. E.g: Bulk import operations such as BCP operations and BULK INSERT, SELECT INTO operations. More details on such operations can be found on the following link: https://docs.microsoft.com/en-us/sql/relational-databases/logs/the-transaction-log-sql-server?view=sql-server-ver15#MinimallyLogged
Full Recovery Model
- Supports transaction log backup
- Chances of a data loss are minimal (subject to the backup strategy implemented)
- Log truncation must be done manually (Upon taking the transaction log backup, it will mark unused space which will be overwritten later by future transactions)
- Transaction log could grow large compared to the "Simple" recovery model
Friday 10 May 2019
Strange behavior on JSON_VALUE when table contains blank and non-blank values (JSON text is not properly formatted. Unexpected character '.' is found at position 0.)
Few days back we had a requirement to search whether a certain value exists in one of the table fields where the values are stored as JSON strings. The default constructor has been set up not to allow any NULLs but in case there’s no value, the default value has been setup as an empty string.
So basically the query would be similar to something shown below
SELECT 'x' FROM [schema].[TableName] AS Src WHERE JSON_VALUE(Src.ColumnName,'$.Root.AttributeName') LIKE 'SearchValue%'
How ever when we ran this query we got the following error
Msg 13609, Level 16, State 2, Line 36
JSON text is not properly formatted. Unexpected character '.' is found at position 0.
Initially we thought that we have typed the attribute incorrectly since it’s case sensitive. But in this case it was correct.
We investigated further and found out few things. But prior explaining them we will replicate this issue. For this I will create one simple table and insert three records.
--== Create a table ==-- CREATE TABLE dbo.Employee_Information ( Id INT ,FirstName NVARCHAR(100) ,LastName NVARCHAR(100) ,JsonData NVARCHAR(MAX) ) --== Insert few rows ==-- INSERT INTO dbo.Employee_Information ( Id ,FirstName ,LastName ,JsonData ) VALUES (1,'John','Doe','{"Employee":{"Id":1,"FirstName":"John","LastName":"Doe"}}') ,(2,'Jane','Doe','{"Employee":{"Id":2,"FirstName":"Jane","LastName":"Doe"}}') ,(3,'Luke','Skywalker','')Now we will use the following query to find any records which the LastName is like ‘Doe’.
SELECT Id FROM dbo.Employee_Information AS E WHERE JSON_VALUE(E.JsonData,'$.Employee.LastName') LIKE 'Doe%'
Msg 13609, Level 16, State 2, Line 36
JSON text is not properly formatted. Unexpected character '.' is found at position 0.
**Note : The query will return results till the error occurs. Hence you will see some rows in your result tab in SSMS.
These are the observations we made during our investigation
Observation 01
If you query the table with a predicate and if that predicate doesn’t include any rows with blank values in the JSON (it’s an NVARCHAR column) field the query will executed successfully.
--== Success ==-- SELECT Id FROM dbo.Employee_Information AS E WHERE JSON_VALUE(E.JsonData,'$.Employee.LastName') LIKE 'Doe%' AND Id IN (1,2) --== Success ==-- SELECT Id FROM dbo.Employee_Information AS E WHERE JSON_VALUE(E.JsonData,'$.Employee.LastName') LIKE 'Doe%' AND Id <> 3 --== Fail ==-- SELECT Id FROM dbo.Employee_Information AS E WHERE JSON_VALUE(E.JsonData,'$.Employee.LastName') LIKE 'Doe%' AND Id = 3
Observation 02
Even you use a filter to fetch only rows containing a valid JSON the execution will be successful.
--== Success ==-- SELECT Id FROM dbo.Employee_Information AS E WHERE ISJSON(E.JsonData) > 0 AND JSON_VALUE(E.JsonData,'$.Employee.LastName') LIKE 'Doe%'
Observation 03
Even you use a filter to fetch only rows containing a non-blank value in the JSON field, it will fail.
--== Fail ==-- SELECT Id FROM dbo.Employee_Information AS E WHERE E.JsonData <> '' AND JSON_VALUE(E.JsonData,'$.Employee.LastName') LIKE 'Doe%'
Observation 04
If you remove records and only keep either one type of rows (either only blank rows or only non-blank) the query will be executed successfully.
TRUNCATE TABLE dbo.Employee_Information INSERT INTO dbo.Employee_Information ( Id ,FirstName ,LastName ,JsonData ) VALUES (1,'John','Doe','{"Employee":{"Id":1,"FirstName":"John","LastName":"Doe"}}') ,(2,'Jane','Doe','{"Employee":{"Id":2,"FirstName":"Jane","LastName":"Doe"}}') --== Success ==-- SELECT Id FROM dbo.Employee_Information AS E WHERE JSON_VALUE(E.JsonData,'$.Employee.LastName') LIKE 'Doe%'
Observation 05
If you have rows only containing blank values in the JSON field the query will fail.
TRUNCATE TABLE dbo.Employee_Information INSERT INTO dbo.Employee_Information ( Id ,FirstName ,LastName ,JsonData ) VALUES (1,'John','Doe','') ,(2,'Jane','Doe','') --== Fail ==-- SELECT Id FROM dbo.Employee_Information AS E WHERE JSON_VALUE(E.JsonData,'$.Employee.LastName') LIKE 'Doe%'
Hope this might help you if you encounter this strange behavior during your development.
Note : All the above queries are executed under the following SQL Server Version (SELECT @@VERSION)
Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)
Oct 28 2016 18:17:30
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows Server 2012 Standard 6.2