Some notes on software development... RSS 2.0
# Tuesday, 23 December 2014

I recently, accidently, put one of my large SQL Server databases ‘In Recovery’ and had no idea how long it was going to take to recover. Thankfully Tim Laqua has created a couple of scripts that query the error log, one for 2008 and one for 2012, that tells you roughly how long the recovery process will take. Below is a copy of the SQL Server 2012 version:

DECLARE @DBName VARCHAR(64) = '<DatabaseName>' DECLARE @ErrorLog AS TABLE([LogDate] CHAR(24), [ProcessInfo] VARCHAR(64), [TEXT] VARCHAR(MAX)) INSERT INTO @ErrorLog EXEC master..sp_readerrorlog 0, 1, 'Recovery of database', @DBName SELECT TOP 5 [LogDate] ,SUBSTRING([TEXT], CHARINDEX(') is ', [TEXT]) + 4,CHARINDEX(' complete (', [TEXT]) - CHARINDEX(') is ', [TEXT]) - 4) AS PercentComplete ,CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0 AS MinutesRemaining ,CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0/60.0 AS HoursRemaining ,[TEXT] FROM @ErrorLog ORDER BY [LogDate] DESC

Links
http://timlaqua.com/2009/09/determining-how-long-a-database-will-be-in-recovery-sql-server-2008/

Tuesday, 23 December 2014 15:40:51 (GMT Standard Time, UTC+00:00)  #    -
SQL Server
# Wednesday, 26 November 2014

Quick code snippet for SQL Server batch deletion:

-- Delete in batches of 5000 DECLARE @rowcount INT = 1, @batchsize INT = 5000 WHILE (@rowcount != 0) BEGIN DELETE TOP (@batchsize) SomeBigTable SELECT @rowcount = @@ROWCOUNT END

There is an excellent article over at sqlperformance.com using different chunking techniques with performance metrics:

http://sqlperformance.com/2013/03/io-subsystem/chunk-deletes

Wednesday, 26 November 2014 10:56:31 (GMT Standard Time, UTC+00:00)  #    -
SQL Server
# Tuesday, 29 October 2013

I always forget the exact sql server commands for shrinking a transaction log file so, thanks to Pinal Dave, here they are:

USE DatabaseName
GO
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)
GO

Links
Pinal Dave - SQL SERVER – Shrinking Truncate Log File – Log Full

Tuesday, 29 October 2013 17:54:04 (GMT Standard Time, UTC+00:00)  #    -
SQL Server
# Tuesday, 08 September 2009

If you change the name of a computer running SQL server you must also update the system metadata stored in sys.servers.
First check the current name by using

If @@SERVERNAME does not show the correct computer name you should change it using the following commands:

select @@SERVERNAME

sp_dropserver <old_name\instancename>
GO

sp_addserver <new_name\instancename>, local
GO



Last step is to restart the SQL Server instance.

Links
How to: Rename a Computer that Hosts a Stand-Alone Instance of SQL Server

Tuesday, 08 September 2009 08:26:13 (GMT Standard Time, UTC+00:00)  #    -
SQL Server
# Wednesday, 22 April 2009

Because I keep forgetting the syntax…

DBCC CHECKIDENT('TableName', RESEED, 0)

Wednesday, 22 April 2009 22:59:53 (GMT Standard Time, UTC+00:00)  #    -
SQL Server
# Wednesday, 10 September 2008

The chaps over at www.sql-server-helper.com have a great table of all the SQL Server date formats:


image

image

1 To make the month name in upper case, simply use the UPPER string function.

Wednesday, 10 September 2008 08:55:26 (GMT Standard Time, UTC+00:00)  #    -
SQL Server
# Thursday, 21 August 2008

The following SQL will return all English error codes and messages:

SELECT      Error,
            Severity,
            Description
FROM        master.dbo.sysmessages
WHERE       msglangid = 1033
ORDER BY    Error


It returns about 7549 rows:

image

Thursday, 21 August 2008 13:24:03 (GMT Standard Time, UTC+00:00)  #    -
SQL Server
# Tuesday, 29 April 2008
Tuesday, 29 April 2008 07:52:49 (GMT Standard Time, UTC+00:00)  #    -
SQL Server
Navigation

Sign In
Extras
Statistics
Total Posts: 116
This Year: 0
This Month: 0
This Week: 0
Comments: 36
About the author/Disclaimer

Disclaimer
The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.

© Copyright 2017
Hadrian Phillips

All Content © 2017, Hadrian Phillips
DasBlog theme 'Business' created by Christoph De Baene (delarou)