Microsoft SQL and Databases

Shrink Microsoft SQL Database Transaction Logs

posted 7 Jan 2014, 06:01 by Tristan Self

PROBLEM:
 
Your SQL transaction log has grown to be really big, this might be because a load of data was added, or your backup procedures were failing to truncate the logs.

SOLUTION:
 
Take a backup of the database, then open the SSMS and then run this command:
 
USE DATABASENAME
GO
ALTER DATABASE DATABASENAME SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE(DATABASENAME_log, 1024)
ALTER DATABASE DATABASENAME SET RECOVERY FULL WITH NO_WAIT
GO
 
Now take a full backup of the database to ensure you have a recoverable version.
 

Microsoft SQL 2000 SP4 In-Place Upgrade to SQL 2005 - Upgrade Advisor Error

posted 13 Jan 2012, 00:39 by Tristan Self

We had a very old server running SQL 2000 SP4 that we needed to upgrade to SQL 2005, as we didn't have the staff resource available for a complete rebuild of the whole server to Windows 2008 and SQL 2008 we decided to do an inplace upgrade.
 
All seemed to go swiminingly until I hit this error:
 
UpgradeAdvisor returned  -1 .
(There is no error message given its just blank.)
 
The error is caused by the upgrade advisor failing. The solution is below:
 
Close the setup program, then do the following:
 
Copy the BPAClient.dll file from the following location C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\BPA\bin to C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\BPA.
 
Then restart the installer again. You should find it works. It appears the problem is caused by a file that is expected to be someone isn't and the advisor fails.
 
Some useful links:
 
 

Oracle TNS Listener Errors

posted 12 Jun 2011, 05:18 by Tristan Self

I recently had to resolve some TNS listener errors on a Oracle database server. It was Oracle 11g running on Microsoft Windows 2003 Server R2 SP2 32bit.
Basically the problem was that the memory Oracle was using was hitting the 2gb ram limit in the 32bit user space.

Adding the /3GB switch to the end of the command line and rebooting, gave Oracle another 1GB ram to work in and this resolved the problem.

Oracle, VMWare and Why You Should Add Indexes to Improve Performance

posted 12 Jun 2011, 05:16 by Tristan Self

I recently completed a migration of an 8 year old Oracle server to a nice new VM running on VMware Vpshere 4.1, this went without too many problems. However when people started to use it, the performance suffered and the server ground to a halt. The software vendor whose database it was imediately said, "ah you've virtualised, therefore you set your SAN up wrong".

But no! I spent many days measuring IOPS of the server and desiging the RAID groups on the SAN, this means that we had the correct storage performance in place, it was the database drawing more than it should. We checked out the performance logs and found the DB was using around 100Mbit to the SAN. Gasp! That was more disk activity that all the other VMs (50+) in our environment including our Exchange server with 850 users.

The imediate thought was an index was missing, we got our supplier to check the DB and look for slow queries, and yes there was one, taking about 4 seconds, and was run constantly. The software supplier added an index and then performance was improved by 95%, wow!

Heres the graph, guess when the index was enabled.... Er. 12:25PM perhaps!


1-4 of 4