Category Archives: SQL Server

[SQL Server] – Check Restore / Backup progress

Having to temporarily wear the DBA hat for a couple of days, I found myself backing up and restoring lots of databases, across different environments.

When you script the backup/restore, you can choose to output the progress, but if you run the backup/restore inside a SQL agent job, you won’t have any feedback until the job has finished.

But fear not, you can have some insights on the progress using the following query


   t.text Command,
FROM sys.dm_exec_requests e
CROSS APPLY sys.dm_exec_sql_text(e.sql_handle) t


And there you go!

Some databases can remain on 100% completion for a while, just be patient.

Where are SQL Server Error logs stored?

Lately I had to provide the location of our SQL server error logs to an infra team, so they could be copied somewhere else.

Raising a ticket and asking the DBAs is always an option, but as usual will introduce some latency.

No worries, there’s a command we can run  to get hold of the error logs location:

exec sp_readerrorlog

This will return a set of text rows, one of which says something like:

2015-10-07 01:52:07.770 Server       Logging SQL Server messages in file ‘ERROR LOG PATH‘.

The problem is that I didn’t have any other rights on this SQL server, other than select permission, so it was impossible to execute this procedure.

Fear not, dear friends, there’s another way, using a simple select:


and this will return the exact error log path!



[SQL Server] – Generate Random Datetime within a range

One of these days I had the need to obtain a random datetime inside a specified range, on SQL Server 2008.

After a few minutes I came up with this script:

DECLARE @DateFrom DATETime = '2012-01-01'
DECLARE @DateTo DATeTime = '2012-06-30'
DECLARE @DaysRandom Int= 0
DECLARE @MillisRandom Int=0

--get random number of days

select @DaysRandom= DATEDIFF(day,@DateFrom,@DateTo)
SELECT @DaysRandom = ROUND(((@DaysRandom -1) * RAND()), 0)

--get random millis
SELECT @MillisRandom = ROUND(((99999999) * RAND()), 0)

SELECT @DateTo = DATEADD(day, @DaysRandom, @DateTo)
SELECT @DateTo = DATEADD(MILLISECOND, @MillisRandom, @DateTo)

Hope it’s useful.