In SQL Server, STUFF and REPLACE functions are used to replace the characters in a string. Both functions play an important role in Transact-SQL with performing the distinct functionality. In the following section, we are going to discuss about Stuff and Replace function in detail. Continue reading “Stuff and Replace Function in SQL Server : Detailed Description”
Most recently I had to create a SQL Logon trigger that set a limit to sessions a user could open in SQL Server.
For example, I log into SSMS and cannot open more than 10 sessions. This logon trigger will enforce that I don’t exceed 10 concurrent sessions. Continue reading “How to Create LOGON Trigger in SQL Server”
This blog post is meant to help distinguish the differences between REORGANIZE and REBUILD when it comes to index fragmentation. If you are serious about learning more of the internals of how indexing works in SQL Server, watch Kimberly Tripp’s (blog | twitter) video called, “Index Internals” here (open link and scroll down to “Index Internals”). Continue reading “INDEX REORGANIZE vs REBUILD in SQL Server”
Most recently I had to figure out the edition (Enterprise, Standard, etc.) of SQL Server running on a specific production database server. The client wanted to know to what extent was the database server STIG’d. Continue reading “How to Find SQL Server Edition Information”
For the longest time I’ve had a hard time remembering the difference between SQL Server DDL vs DML statements. I had a hard time remembering what statement fell under what category. Was INSERT, UPDATE, DELETE a DML command or DDL? What about CREATE / ALTER? It was all confusing to me. Continue reading “SQL Server DDL vs DML”
There were a few times where I had to delete/close an existing SQL Server Trace and create a new one. It’s a 2-step process to completely remove a SQL Server Trace. First, you stop the trace. Second, you delete/close it.
sp_trace_setstatus takes 2 parameters, trace id and status. Continue reading “How to Stop & Delete a SQL Server Trace”
I recently had to resize a data file on of my production SQL Server databases and needed to know an appropriate size to resize it to. So, I created this simple script that queries sys.database_files and brings back the File ID, File Location, File Name, Original Size, Space Used and Space Left.
SELECT sdb.file_id as [File ID], sdb.physical_name [File Location], sdb.name [File Name], CONVERT(numeric(10,2),ROUND(sdb.size/128.,2)) AS [Original Size in MB], CONVERT(numeric(10,2),ROUND(FILEPROPERTY(sdb.name, 'SpaceUsed')/128.,2)) AS [Space Used in MB], CONVERT(numeric(10,2),ROUND((sdb.size - FILEPROPERTY(sdb.name, 'SpaceUsed'))/128.,2)) AS [Space Left in MB] FROM sys.database_files sdbContinue reading “How to Find the Space Used in Your Data Files”
Recently, I wanted to resize the tempdb file size to 2 MB so I ran the below script:
ALTER DATABASE tempdb MODIFY FILE (name = tempdb, size = 2MB);
and got the below error:
Msg 5041, Level 16, State 2, Line 1
MODIFY FILE failed. File ‘tempdb’ does not exist. (screenshot below)
If you want to find the last time your SQL Server was restarted, the quickest way is to query the sys.dm_os_sys_info DMV for the ‘sqlserver_start_time’ column (See snapshot below)