SQL Server: Cool tips & tricks

Below are some of cool T-SQL tips & tricks that can prove very handy and help resolving/addressing some of your SQL Server works with comparatively less effort and quickly.

Insert/Update file to binary column

Want to bulk upload of binary files directly into SQL server without writing any Front/Backend code? here is the SQL statement to help you out:

--Let say you want to add ABC.pdf file directly from file system to varbinary in DB
declare @pdf varbinary(max)

set @pdf = (SELECT *  from OPENROWSET(BULK N'E:ABC.pdf', SINGLE_BLOB) as a)

-- Now you can use @pdf in your insert/update statement.

Shrink DB log file

Got frustrated with occupied disk space by SQL Server DB log file (.LDF) which keeps on increasing over period of time and eating of your disk space? Here is the solution:

--Replace DBNAME below by your database name
BACKUP LOG DBNAME
WITH TRUNCATE_ONLY
GO
DBCC SHRINKFILE (2,1, TRUNCATEONLY)
GO
DBCC SHRINKDATABASE (DBNAME, 0)
GO

Know SQL Table stats??

The metrics for size of table: its record count, amounts of allocated & free space can proved to be interesting sometimes especially when planning any migration or optimization activities. Check this out:

--Replace @TableName below by concerned table name
EXEC sp_spaceused @TableName

See also: Know Your Database Statistics?

Know DB size??

Check out below SQL script, this can provide you size of all databases that exists on your SQL Server:

SELECT  d.name 'DB Name',
       ROUND(SUM(MF.size) * 8 / 1024, 0) 'Size (in MB)'
FROM    sys.master_files MF
       INNER JOIN sys.databases d ON D.database_id = MF.database_id
GROUP BY d.name
ORDER BY d.name

Search text in DB Objects??

You can search a specific text in DB Objects like store procedures, views and functions using below SQL script:

SELECT sys.schemas.name +'.'+OBJECT_NAME(id) 'DB Object Name'
FROM syscomments INNER JOIN sys.objects ON syscomments.id = sys.objects.object_id
INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id
WHERE [text] LIKE N'%YOU SEARCH TEXT%'
AND (OBJECTPROPERTY(id, 'IsProcedure') = 1
      OR OBJECTPROPERTY(id, 'IsScalarFunction') = 1
      OR OBJECTPROPERTY(id, 'IsView') = 1)
GROUP BY sys.schemas.name +'.'+OBJECT_NAME(id)

This script can play a key role in doing impact analysis where business logic is embedded in DB objects.

Who locks your SQL Server Database??

God!! Can I get the trace of culprit who’s locking my DB?? Below script can help you out:

SELECT session_id,host_name,request_mode,last_request_start_time,
resource_type, resource_description
FROM sys.dm_tran_locks 
INNER JOIN sys.databases ON sys.dm_tran_locks.resource_database_id =sys.databases.database_id
INNER JOIN sys.dm_exec_sessions ON sys.dm_exec_sessions.session_id=sys.dm_tran_locks.request_session_id
WHERE resource_type = 'DATABASE'
AND request_mode LIKE '%X%'
AND name ='YOUR DB NAME'

Since, you have the session_id and name of person who is responsible for Exclusive Locks, you can go & kill his session by following simple query

KILL session_number

How busy was your Database??

This cool scripts can help you to find out the I/O utilization for each databases in your SQL Server.

SELECT 
	DB_NAME(database_id) AS [Database Name],
	CAST(SUM(num_of_bytes_read + num_of_bytes_written)/1048576 AS DECIMAL(12, 2)) AS 'IO (in MB)'
FROM 
	sys.dm_io_virtual_file_stats(NULL, NULL) AS [DM_IO_STATS]
GROUP BY database_id;
(Visited 59 times, 1 visits today)