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:

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:

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:

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:

Search text in DB Objects??

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

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:

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.