Monday, June 15, 2009

Tables using space in HardDisk for SQL Server

exec (' sp_spaceused ' )

DECLARE C CURSOR FOR
select name from sysobjects where xtype in ('u') order by 1

OPEN C

declare @name VARCHAR(200)
FETCH NEXT FROM C INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
-- PRINT 'sp_spaceused ''' + @NAME + '''
exec (' sp_spaceused ' + @NAME + '')
FETCH NEXT FROM C INTO @name
END

CLOSE C
DEALLOCATE C

--Enjoy IT!

Reducing SQL Transaction logs size

Run this first

sp_helpfile

This will give you the transaction log file name. Use it in logfilename.

DBCC ShrinkFile (LogFileName,10, TRUNCATEONLY)