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
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;