Before starting our process, can we check whether we have enough space in our Database to run ? Or rather check how muc space is used an how much is left and raise a flag if we think that the space left is very less? Also, is there a way to check if the password expiry date is nearby and send reminder emails if so?
There are a few places to look where you can get your space used. DBC.AllSpace shows the space used for every database and table (or join index/stored procedure, etc.). Be careful how you aggregate though, as there is a row for ALL objects in each database. So summing by DatabaseName will get you a double count. This view also gives you insight into Spool and Temp space used by users/databases. DBC.TableSize is the other. It just gives you access to the perm space used and doesn't present the double counting issue. Query either of these to determine your freespace and trigger a job failure if needed.
DBC.ProfileInfo contains the information regarding password expiration. It will tell you how often password should expire (in days). You should be able to join this to the User table and get a list of users who will expire soon. The User table contains information about when the password was last changed. Add to this the password expiration data from the profile table and you'll know when the users password will expire.