Sql-server – How to monitor SQL Server data and log file growth

powershellsql serversql-server-2008transaction-log

I'm trying to find a way to monitor data and log file size from SQL Server databases (within an SQL Server instance). I would not want to use third party tools, but simply pure SQL or PowerShell. I know that one can query this data from sys.database_files or use DBCC SQLPERF(logspace) to get data from log file usage. Is there any way to automate this monitoring and get reports from this data? Any ideas and code samples are helpful!

Best Answer

If you want to use PowerShell you can easily create task scheduler jobs in Windows to run the PowerShell scripts which would dump the output into a table or log file.

If you want to use SQL you can easily create SQL Server Agent jobs to pull the data from the DMVs or DBCC and dump them in a table.

It is difficult to understand exactly what you would want any reports against the stored data to look like, or which part is challenging.

Related Solutions

Sql-server – What are the advantages of a monitor server for log shipping

You can monitor the process on the primary and secondaries, here's where a monitor comes in really useful though...

Let's say your log backups and copies are running just fine, no problems, but one of your secondaries goes down. You aren't monitoring the secondary and so are not aware of this. Your log shipping restore alerts are based off of the secondary server, so you are none the wiser (server being down then you are not getting restore alerts that things are out of date).

You notice a couple of days later, but by then it's too late and you have to restart log shipping from scratch.

Having a monitor server just helps to keep an eye on things and gives you a warning point not related to the servers involved in log shipping (think of it kind of like the witness server in mirroring, but without the automatic failover)

Sql-server – How to do a one-time log shrink on a database with transactional replication

The easiest solution to this issue is to set the database to simple recovery, shrink the log, then set it back to full recovery. In T-SQL this would be:

ALTER DATABASE [database] SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE([logfilename], 1)
ALTER DATABASE [database] SET RECOVERY FULL WITH NO_WAIT

Changing the database to simple recovery may cause transactions to stop being replicated, but if scheduled during off hours it should shrink the log and return to full recovery with no side effects.

If that is unacceptable, you can use the sp_repldone stored procedure to manually mark transactions as distributed and reset the replication status of invalid transactions:

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,    @time = 0, @reset = 1

Best Answer

Related Solutions

Sql-server – What are the advantages of a monitor server for log shipping

Sql-server – How to do a one-time log shrink on a database with transactional replication

Related Question