Category Archives: SQL Server

SQL Server

[SQL Server] – Check Restore / Backup progress

Leave a comment

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

 

SELECT
   e.session_id,
   t.text Command,
   e.start_time,
   e.percent_complete
FROM sys.dm_exec_requests e
CROSS APPLY sys.dm_exec_sql_text(e.sql_handle) t
WHERE e.command = 'BACKUP DATABASE' -- 'RESTORE DATABASE'

 

And there you go!

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

SQL Server

Where are SQL Server Error logs stored?

Leave a comment

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:

SELECT SERVERPROPERTY('ErrorLogFileName');

and this will return the exact error log path!

Cheers!

 

.NET, SQL Server

[SQL Server] – Generate Random Datetime within a range

1 Comment

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)
SELECT @DateTo

Hope it’s useful.