Finding your way around an unfamiliar database

It can sometimes be difficult to find where various values are being stored in a database you are unfamiliar with, this is a T-SQL script I wrote some time ago when I was trying to find my way around a TechOne database and it came in handy back then and it recently came in handy once again when I was querying a Connectwise database.

What this script does is it cursors through all the tables and all the columns on those tables and counts the occurrences of the value you are searching for, and then gives you a result set listing which columns on which tables contain that value.

 

Warning: Think carefully before running this against your databases, this could cause performance issues.

The following query is very handy for finding which stored procedures, views & functions are referencing various values:

P = Stored procedure
V = View
TF = Table function
FN = Scalar function
AF = Aggregate function (CLR)
TR = SQL DML Trigger

You can find a full list of xtype values here: https://msdn.microsoft.com/en-AU/library/ms177596.aspx

Advertisements

Yin & Yang Dreaming Deeply

I started playing around with uploading images to dreamdeeply.com a couple of days ago, which is based on Googles Artificial Neural Network image recognition software.

For those who have not been following these developments Deep Dream is an artificial neural network train by showing it millions of images for the purposes of image recognition, and then taking that software and telling it once it finds something it has been trained to recognise to then enhance those features, if you haven’t read about this then I highly recommend that you go check it out: Inceptionism: Going Deeper into Neural Networks

Anyway this is an artwork which I have put together based on some images produced by uploading bland featureless black and white images and them combining them and adding my own enhancements.

And for all the Python developers out there the code has been open sourced and made available on github: https://github.com/google/deepdream

I’m going to have to start learning Python I think…

Powershell Export SQL results to CSV

I have a bunch of SQL scripts for which I need to export the results to CSV, this is something I need to do on a semi-regular basis and its generally a handy thing to be able to automate without having to create a SSIS package or write a console application.

So here is a Powershell script i have written which will read the SQL queries in from .sql files in the input directory and output the results to csv files with the same name as the source script.

WARNING: This script doesn’t handle SQL comments very well, so make sure you remove them from your SQL scripts before using this script to run them, also for some reason wordpress keeps messing up the connection string in the SqlConnection so Datace should be Data Source.

Transaction log file growth and database mirroring

If you have a database mirroring setup it is important to do regular transaction log file backups, if you do not maintain the transaction logs they will grow indefinitely and eventually fill up your drive and I have seen this happen.

When this what you need to do is backup the transaction log then shrink the log file using DBCC SHRINKFILE. Here is an example script which will backup the transaction log on a Blog database and figure out how much free space is available, then shrink the file down to the space used plus 10%.

BACKUP LOG Blog TO DISK = 'E:\MSSQLSERVER\Backups\blog_log.bak'
 
CREATE TABLE #TempPerf(
      [Database Name] VARCHAR(255),
      [Log Size (MB)] DECIMAL(12, 2),
      [Log Space Used (%)] DECIMAL(12, 2),
      [Status] INT,
      FreeSpace as ([Log Size (MB)] - ([Log Size (MB)] * ([Log Space Used (%)] / 100.0)))
)
 
INSERT INTO #TempPerf EXEC('DBCC SQLPERF(logspace)')
 
DECLARE @shrinkToMB INT;
 
SET @shrinkToMB = (
      SELECT TOP 1 (([Log Size (MB)] - FLOOR(FreeSpace)) * 1.1) as TargetSize -- FreeSpace + 10%
      FROM #TempPerf
      WHERE [Database Name] = 'Blog'
)
 
DBCC SHRINKFILE(Blog_log, @shrinkToMB)
 
DROP TABLE #TempPerf

This approach will not work in all cases, after running this query on a clients system which had this problem the transaction did not shrink, and we get a message as follows:

(1 row(s) affected)
Cannot shrink log file 2 (_log) because the logical log file located at the end of the file is in use.

(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

After some googling this article came up:

http://blogs.technet.com/b/mdegre/archive/2011/09/04/unable-to-shrink-the-transaction-log.aspx

– Ran DBCC OPENTRAN which returned ‘No active open transactions’
– Value of ‘log_reuse_wait_desc’ is LOG_BACKUP

The ‘log_reuse_wait_desc’ column on the ‘sys.databases’ table indicates why the transaction log was not cleared or truncated, see this article http://www.sqlskills.com/blogs/paul/why-is-log_reuse_wait_desc-saying-log_backup-after-doing-a-log-backup/

Basically the current VLF is preventing the transaction log file from being truncated, one solution that is suggested here: http://dba.stackexchange.com/questions/64771/how-to-do-a-one-time-log-shrink-on-a-database-with-transactional-replication is switching the database to simple recovery (which will cause transactions to stop being replicated, probably need to re-initialize replication) then shrink the log and switch the database back to full recovery mode.

In actual fact you can’t switch the database to simple recovery model while mirroring is configured, SQL Server will not let you, you need to remove mirror then you can change the recovery model and shrink the log, then you will need to reconfigure mirroring.

References:

http://blogs.technet.com/b/mdegre/archive/2011/09/04/unable-to-shrink-the-transaction-log.aspx
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/d1c533cd-aa7e-4774-9b85-b73ddf3b7873/sql-server-2008-r2-mirror-database-shrink-transcation-log