I came across one issue recently. I have tried to install SQL Server 2005 in one of our server and guess what happened!!!??? J It failed to install. Bit annoying but I can’t help much as that is not in my hand. I tried to uninstall after failed install from Control Panel->Add/Remove program but it was not even willing to get uninstalled!!!! Bit more annoying, isn’t it? Yes, it is.
Removing SQL directories and remove entry from registry manually is something very frustrated task to do so I decided not to go in that route and start find the way around and I found one utility from Microsoft site, its “Windows Installer Cleanup utility”, I downloaded it and use it and guess what happened??? It’s been my rescue. I would like to share that utility with my readers, you can download that utility from below given link, it may be very helpful sometime.
Do read warning and readme file before using it.
Well, shrinking log file manually and frequently is not at all good idea but we have to do it once in a while when it reaches at very big size, however if it is growing unacceptably and frequently, you have to investigate the reason before shrinking it.
Anyway, our main focus today is how to shrink log file. Well, there is a simple command “DBCC SHRINKFILE” in T-SQL but what, if we want to shrink log file of all database in SQL Server? Well in this situation SP_MsForEachDB (undocumented SP) will come to your help.
Let us see small yet handy script to do so:
DECLARE @SQL varchar (2000)
SELECT @SQL = ”
SELECT @SQL = @SQL + ‘if ”?” ”master” and ”?” ”msdb” and ”?””tempdb” and ”?” ”model” ‘
SELECT @SQL = @SQL + ‘BEGIN ‘
SELECT @SQL = @SQL + ‘USE ?; ‘
SELECT @SQL = @SQL + ‘SELECT ”?”; ‘
SELECT @SQL = @SQL + ‘DECLARE @LogFile varchar (30); ‘
SELECT @SQL = @SQL + ‘SELECT @LogFile = name FROM dbo.sysfiles WHERE (status & 0x40) 0; ‘
SELECT @SQL = @SQL + ‘dbcc shrinkfile (@LogFile, 1,truncateonly); ‘
SELECT @SQL = @SQL + ‘SELECT fileid, name, filename, size, growth, status, maxsize FROM dbo.sysfiles WHERE (status & 0x40) 0; ‘
SELECT @SQL = @SQL + ‘END’
EXEC sp_MSforeachdb @command1=@SQL
To know more about SP_MsForEachDB stored procedure, have a look at one of my previous article at:
I have seen many times that people, especially who are .NET developer, be in illusion that they can’t install more than one version of SQL Server in one Server. This is indeed not true at all. If you are aware with INSTANCES of SQL Server, you could probably run more than one version of SQL Server.
When you install SQL Server, you get two options 1.) Default Instance 2.) Named Instance. If you have installed SQL Server (may be sql server 2000) with default instance first time, you can install other version (may be SQL Server 2005 and/or SQL Server 2008) with named instance in your server. All you have to select is, Named Instance radio button and give name to instance. You can give any name to your instance to identify what version you want to connect at the time of connecting it. Suppose you give instance name “SQL2K5” to another SQL Server 2005 installation than you will be able to use SQL Server 2000 and 2005 both in your server. All you need to do is, while connecting to new instance, you have to give SERVERNAME\Instancename (in our case instance name is “SQL2K5”).
Hope this helps to newbie.
Today I am going to share very small yet very useful and handy script. It is often necessary to keep update statistics of all tables for performance point of view. Actually by default query optimizer updates statistics in query plan in certain situation but it is good to manually do it to assure that every query plan it optimized and up-to-date.
You can generate T-SQL for update statistics for all tables of your database along with Schema name. Have a look at the script.
‘UPDATE STATISTICS ‘ + SysSche.Name + ‘.’ + SysObj.Name
sys.schemas SysSche ON SysObj.Schema_ID = SysSche.Schema_ID
TYPE = ‘U’
For more details about UPDATE STATESTICS and its syntax, you can refer official Microsoft link at