MSSQL Server

All posts containing MSSQL-related scripts and patterns.

MSSQL update value dependent on original

/ MSSQL Server /

Query to change the format of a postal code string for specific postal codes. This specific script changes all values in the postcode column from format  ‘1234AB’ to ‘1234 AB’.

UPDATE tablename
SET postcode = LEFT(LTRIM(postcode),4) + ' ' + RIGHT(RTRIM(postcode),2)
WHERE
  LEN(LTRIM(postcode)) = 6 AND
  LEN(REPLACE(postcode, ' ', '')) = 6 AND
  ((land = 'Nederland') OR (land = 'Nederland (NL)'))

Other example to replace values. This time replacing a value with a string from another table.

UPDATE   a
SET      ColumnX = b.ColumnY
FROM     Table1 a
JOIN     Table2 b ON b.ID=a.ID

MSSQL DBFile SpaceUsed

/ MSSQL Server /

Script to return database file usage (GB) from a database.

USE [DATABASE]
GO
SELECT DBFiles.fileid as FileID,
       DBFiles.filename as FileName,
       DBFiles.name as Name,
       CONVERT(decimal(18,3),ROUND(((CAST(FILEPROPERTY(DBFiles.Name, 'SpaceUsed') AS decimal) * 8) /1024 /1024), 3)) AS SpaceUsedGB, 
       CONVERT(decimal(18,3),ROUND(((CAST(DBFiles.size AS decimal) * 8) /1024 /1024), 3)) AS FileSizeGB
FROM dbo.sysfiles DBFiles
GO

 

MSSQL Log space used percentage

/ MSSQL Server /

Met onderstaande T-SQL kun je de vulling in procent van het transaction log bestand van een specifieke database opvragen en monitoren. Dit is alleen handig in specifieke situaties. Bij een correct database onderhoudsplan zal dit niet nodig zijn.

CREATE TABLE #tTLSpace 
( 
DBName sysname, 
TLSize decimal(18,5), 
TLUsed decimal(18,5), 
status INT 
)
GO

INSERT INTO #tTLSpace 
       exec ('DBCC SQLPERF(logspace)') 
GO

DECLARE @dTLUsed decimal
SELECT @dTLUsed = TLUsed from #tTLSpace WHERE DBName = 'DATABASE'
IF ( @dTLUsed > 90)
BEGIN
  PRINT @dTLUsed
  PRINT 'TransactionLog bestand 90 procent vol...'
END
GO

DROP TABLE #tTLSpace 
GO

MSSQL check database-backup

/ MSSQL Server /

Script to check for databases not backupped in the last two days

SELECT sys.databases.name
FROM sys.databases
WHERE sys.databases.name not in
(SELECT DISTINCT sys.databases.name from sys.databases 
INNER JOIN msdb..backupset ON sys.databases.name = msdb..backupset.database_name
WHERE (msdb..backupset.type = 'D' AND backup_start_date > (GetDate() -2)))
AND sys.databases.state = 0 AND sys.databases.name ‹› 'tempdb'

 

MSSQL Move Tempdb Files

/ MSSQL Server /

Move tempdb files to a different location.

USE MASTER
GO
ALTER DATABASE tempdb MODIFY FILE (name = tempdev, filename = 'D:\Path\To\tempdatafile.mdf')
GO
ALTER DATABASE tempdb MODIFY FILE (name = templog, filename = 'L:\Path\To\templogfile.ldf')
GO

Restart the database engine and check if the new files are created. For more information on file locations you can use the stored procedure sp_helpfile. Remove the files from the original location.

MSSQL Check TransactionLog Backup

/ MSSQL Server /

Check if the are database without recent (2 days) TransactionLog backups for all databases in FULL Recovery.

SELECT sys.databases.name
FROM sys.databases
WHERE sys.databases.name NOT IN
(SELECT DISTINCT sys.databases.name from sys.databases 
INNER JOIN msdb..backupset ON sys.databases.name = msdb..backupset.database_name
WHERE (msdb..backupset.type = 'L' AND msdb..backupset.backup_start_date > (GetDate() -2)))
AND sys.databases.recovery_model != 3 and sys.databases.state = 0

MSSQL Restore Torn Page

/ MSSQL Server /

To restore a specific torn page first make a backup from the transaction log with NORECOVERY (tail backup).

RESTORE DATABASE Databasename PAGE='1:234'
FROM DISK='X:\Path\To\Full\DatabaseBackupFile.bak'
WITH NORECOVERY
GO

Then restore all transaction log backups made sinds the Full backup with NORECOVERY. Then restore the tail backup WITH RECOVERY.

MSSQL Truncate TransactionLog Without Backup

/ MSSQL Server /

With the following command you can truncate the transaction log withouot making a backup. Don’t forget to make a full backup afterwards.

ALTER DATABASE [DATABASENAME] SET RECOVERY SIMPLE WITH NO_WAIT

You can then maby do a shrink of the log file and then set the database back to FULL recovery.

ALTER DATABASE [DATABASENAME] SET RECOVERY FULL WITH NO_WAIT