Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com
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 written few articles about different types of INSERT statement in SQL Server, SQL Server 2008 came up with many enhancements in T-SQL and developer convenience. There is one enhancement in the way you use INSERT statement.
This new feature called “Raw Constructor” which will be helpful to specify more than one raw in VALUE clause of INSERT statement.
CREATE TABLE emps
–INSERT records with multiple RAW in VALUE clause
INSERT INTO emps (Name,Dept,Company) VALUES