It seems something went awry with the SQL Server. It’s sluggish, behaves erratically, produces heavy network traffic, there is a significant increase in the server processor or memory utilization, and to top it all there are reports of or database objects and data being damaged or missing.
Where to start looking?First, perform a basic smoke test on the machine where the SQL Server is installed, including:
SELECT NAME FROM master..sysobjects WHERE objectproperty(id, 'ExesIcStartup') = 1
SELECT NAME ,create_date ,user_access_desc ,CASE is_read_only WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' END AS is_read_only ,state_desc ,CASE is_in_standby WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' END AS is_in_standby ,CASE is_cleanly_shutdown WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' END AS is_cleanly_shutdown FROM sys.databases
SELECT type_desc ,total_count = COUNT(*) ,last_create_date = MAX(create_date) ,last_modify_date = MAX(modify_date) FROM sys.objects WHERE sys.objects.type NOT IN ( 'C' ,'D' ,'F' ,'UQ' ) GROUP BY type_desc
SELECT DB_NAME() DatabaseName ,s.NAME + '.' + o.NAME TableName ,SUM(p.rows) RecordCount ,COUNT(c.column_id) ColumnCount FROM sys.indexes i INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id INNER JOIN sys.objects o ON o.object_id = i.object_id INNER JOIN sys.columns c ON o.object_id = c.object_id INNER JOIN sys.schemas s ON o.object_id = s.schema_id WHERE i.index_id 2 AND o.type = 'U' GROUP BY s.NAME ,o.NAME ORDER BY s.NAME ,o.NAME;
SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS SERVER ,msdb.dbo.backupset.database_name ,msdb.dbo.backupset.backup_start_date ,msdb.dbo.backupset.backup_finish_date ,msdb.dbo.backupset.expiration_date ,CASE msdb..backupset.type WHEN 'D' THEN 'Database' WHEN 'L' THEN 'Log' END AS backup_type ,msdb.dbo.backupset.backup_size ,msdb.dbo.backupmediafamily.logical_device_name ,msdb.dbo.backupmediafamily.physical_device_name ,msdb.dbo.backupset.NAME AS backupset_name ,msdb.dbo.backupset.description FROM msdb.dbo.backupmediafamily INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id WHERE (CONVERT(DATETIME, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7) ORDER BY msdb.dbo.backupset.database_name ,msdb.dbo.backupset.backup_finish_date
Everything seems fine. Does it mean that everything is fine?
Unfortunately, no. Some of the steps mentioned above are really effective if a mirrored SQL database or transaction log shipping is in place, or at least a reliable database backup is available to help verify that no objects or data have been lost. Even so, for databases containing a large number of objects or storing huge amounts of data this process is very slow, tedious and error prone. Sure, if suddenly half of the tables have zero rows that’s a clear sign that some foul play is involved but what only a single row or object was dropped, or even worse, altered? Is the only alternative to go over and check each row and each object manually?
Fortunately, no. The transaction log keeps a record of each change made to the database including information on when the change was made and who made it. The best part is – due to its nature, that information cannot be tampered with. But, there’s a catch – the transaction log isn’t humanly readable on its own. This is where ApexSQL Log comes into play. ApexSQL Log is an auditing and recovery tool for SQL Server databases which reads transaction logs, transaction log backups, detached transaction logs and database backups, and audits, reverts or replays data and object changes that have affected the database, including the ones that have occurred before the product was installed.
To investigate any suspicious database changes with ApexSQL Log
Select appropriate transaction log backups and click "Open"
Click the "Next" button to advance to the filter setup which enables you to narrow down the results to a specific timeframe, operation, user, object or data row
The changes that meet the specified criteria, along with their details, will be listed in the application’s main grid. To narrow down the result set further, use the "Grid filter" in the left pane
In summary, examining your SQL Server for suspicious activity takes several steps – from checking for malicious software to manually reviewing the impact on the database itself and analyzing the SQL Server logs. However, if you want to ensure that no suspicious change to your SQL database made past you, examine its transaction log with ApexSQL Log.
Please download the script(s) associated with this article on our GitHub repository.
Please contact us for any problems or questions with the scripts.
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Terms of Use Privacy Cookie Preference Center