database file

Data Compression– a unique feature of SQL Server TIP #91

A part from performance many times we faced challenges related to space  of our database. Sometimes our database is actually taking huge space and sometimes it our mistake due to which it took space. It is worst condition when you are on a dedicated hosting or cloud hosting with limited space. In such case “Compression” …

Oh! I forgot When I took last Database Backup ? How to determine this? TIP # 66

  In last few tips TIP #64 & TIP #65  we get information  how to take backup & How to ensure it can be restore? Now lets me share some more information related to backup. Problem:-   Sometimes , we are interested to know when last backup is taken for particular database ? Solution:  The first …

A hidden feature sp_MSforeachdb TIP #52

  As we discussed yesterday a hidden feature tip #51 which is sp_MSForeachtable . sp_MsForeachtable is useful to run command on each table of selected database. Now what if we want to run command on each database also ? then in that case we need to take help of sp_MsForeachDB. Sp_MsForEachDB  by the name it …

How to determine when Last backup taken of a database n SQL Server? TIP #15

Sometime we require to know when we have taken last backup. So let me share here all the backup, log shipping related detail you can find in the MSDB database which is system database.SQL server maintain all the backup related detail in the MSDB database table As Show in the figure Now we can run …

How to find what is currently running on your SQL SERVER? TIP # 12

When we are keen to know that what is currently running on SQL SERVER for analysis purpose then with the help of below SQL statement we can easily find those statements, Stored procedure, status etc. GoSELECT [Spid] = session_Id, ecid, [Database] = DB_NAME(sp.dbid), [User] = nt_username, [Status] = er.status, [Wait] = wait_type, [Individual Query] = …

Get files (MDF/LDF) information of all databases – TIP #6

To get file (MDF/LDF) information we can write following query GOEXEC sp_MSforeachdb ‘USE ?  Execute sp_helpFile’ GO Or we can write following commandGoEXEC sp_MSforeachdb ‘USE ? SELECT ”?”, SF.filename, SF.size FROM sys.sysfiles SF’Go Or we can write following commandGO SELECT Name,Physical_name As current_file_LocationFrom sys.master_Files Go Enjoy !!!