Favourite database scripts

Here are the T-SQL database scripts I most frequently use when trouble-shooting.

-- Clear transaction log for SQL Server 2008 and after (log file)
USE [DatabaseName]
DBCC SHRINKFILE('TransactionLogLogicalName', 1)
BACKUP LOG [DatabaseName] TO DISK='NUL'
DBCC SHRINKFILE('TransactionLogLogicalName', 1)

 

-- Clear transaction log for SQL Server 2005 and before (log file)
USE [DatabaseName]
DBCC SHRINKFILE('TransactionLogLocialName', 1)
BACKUP LOG [DatabaseName] WITH TRUNCATE_ONLY
DBCC SHRINKFILE('TransactionLogLogicalName', 1)

 

-- Restore database from file
restore filelistonly from disk = 'c:\temp\Name.bak'
restore database [Name] from disk = 'c:\temp\Name.bak' with move 'name_dat' to 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\Name.mdf', move 'name_log' to 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\Name.ldf'

-- Transaction for updates
begin transaction
-- sql to execute
if @@rowcount <> 1 begin
rollback tran
return
end
commit transaction

-- Print contents of text field from log table
select top 100 * from tbltrace order by stamp desc
declare @message nvarchar(4000)
select @message = convert(nvarchar(4000), longmessage) from tbltrace where guid like 'a735f5ff-%'
print @message

 

-- Print contents of more than one text fields from log table
declare @shortmessage nvarchar(4000)
declare @longmessage nvarchar(4000)
declare @stamp datetime
declare @id int
DECLARE Log_Cursor CURSOR FOR
select id, stamp, convert(nvarchar(4000), shortmessage), convert(nvarchar(4000), longmessage) from tbltrace where id >= xxx and id <= yyy order by id desc
OPEN Log_Cursor
FETCH NEXT FROM Log_Cursor into @id, @stamp, @shortmessage, @longmessage
WHILE @@FETCH_STATUS = 0
BEGIN
print '---------------------------------------------------------'
print 'ID: ' + convert(nvarchar(1000), @id)
print 'Time stamp: ' + convert(nvarchar(100), @stamp, 121)
print 'Short msg: ' + @shortmessage
print 'Long msg:'
print @longmessage
FETCH NEXT FROM Log_Cursor into @id, @stamp, @shortmessage, @longmessage
END
CLOSE Log_Cursor
DEALLOCATE Log_Cursor

Leave a Reply

Your email address will not be published. Required fields are marked *