Overview Of MSSQL Logs

Database Logs are used for any modification/transaction on table production or during development from their Database Server.

Introduction
 
Log tracking is not a big issue nowadays. We usually worry about the database backups and log tracking. I am covering some of the issues in this blog related to log tracking:
  1. You find the difference within backup and the database size.
  2. You want to track who has dropped the tables of your database.
Here are some steps,  which by following, you can track the issues.
 
Log of the dropped tables from the database
  1. First, create a new database.
  2. Create a new table.

    Overview Of MSSQL Logs

  3. Now, go and drop the table by running the following command.

    Overview Of MSSQL Logs

  4. Now, select your database under Object Explorer and go to Reports >> Standard Reports >> Schema Changes History.

    Overview Of MSSQL Logs

  5. You will then see the schema change history. The report will show you who has dropped this table.

    Overview Of MSSQL Logs 
Shrink Database
 
If you notice the backup size of the database is bigger than the original database size, this is because the backup rule removes the useless empty spaces of log and takes the backup. It, in reality, is a very impressive feature to keep the real database size.
 
Backup of Database
  1. BACKUP DATABASE[Databaseyourcompany] TODISK = N'D:\Databaseyourcompany.bak'WITHSTATS = 1  
  2. GO   
Empty spaces from the log

It is always the best practice to shrink the log file before taking the backup of your database. This comes in handy when restoring your database from the tiny backup size to the original.

If you skip shrinking the log size before taking the backup of the database, the size of the database will not exactly be the same as the backup size of the database, and when you restore the database, it will recreate the log file with empty spaces and hold up a large volume on your hard disk.
 
Summary
 
You can locate the user activity with the help of log and also you can manage the proper backups of the database.