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” …
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 …
Problem:- We have seen last time how to take backup in tip # 64. but sometimes it happened we took backup and we are not able to restore it. It might be corrupted. Now , Next step thought come in our mind how to ensure we took right backup which can be restore. …
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 …
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 …
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] = …
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 !!!