Check SQL running processes

October 6, 2017 No comment

If you want to check the processes running on your SQL server you can find all the info you need in sys.dm_exec_requests.

SELECT command,
            s.text,
            start_time,
            percent_complete,
            CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
                  + CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
                  + CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
            CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
                  + CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
                  + CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
            dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s

In the example below you can see that there is a back-up running that about 17% completed and a estimated duration

Check SQL last back-date

September 29, 2017 No comment

If you want to check in SQL when your databases was last back-upped (+ the type of back-up), you can execute the script below. this will list you everything of the last 7 days.

SELECT 
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
msdb.dbo.backupset.database_name, 
msdb.dbo.backupset.backup_start_date, 
msdb.dbo.backupset.backup_finish_date, 
msdb.dbo.backupset.expiration_date, 
CASE msdb..backupset.type 
WHEN 'D' THEN 'Database' 
WHEN 'I' THEN 'Differential database' 
WHEN 'L' THEN 'Log' 
WHEN 'F' THEN 'File/Group' 
WHEN 'G' THEN 'Differential file' 
WHEN 'P' THEN 'Partial' 
WHEN 'Q' THEN 'Differential partial' 
END AS backup_type, 
msdb.dbo.backupset.backup_size, 
msdb.dbo.backupmediafamily.logical_device_name, 
msdb.dbo.backupmediafamily.physical_device_name, 
msdb.dbo.backupset.name AS backupset_name, 
msdb.dbo.backupset.description 
FROM msdb.dbo.backupmediafamily 
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7) 
ORDER BY 
msdb.dbo.backupset.backup_finish_date desc,
msdb.dbo.backupset.database_name

Check SQL last restore-date

September 29, 2017 No comment

If you want to check in SQL when your databases was last restored, you can execute the script below.

SELECT  [rs].[destination_database_name] ,
        [rs].[restore_date] ,
        [bs].[backup_start_date] ,
        [bs].[backup_finish_date] ,
        [bs].[database_name] AS [source_database_name] ,
        [bmf].[physical_device_name] AS [backup_file_used_for_restore]
FROM    msdb..restorehistory rs
        INNER JOIN msdb..backupset bs ON [rs].[backup_set_id] = [bs].[backup_set_id]
        INNER JOIN msdb..backupmediafamily bmf ON [bs].[media_set_id] = [bmf].[media_set_id]
ORDER BY [rs].[restore_date] DESC

Ax2012 An SQL Server 2012

March 15, 2012 No comment

20120315-074725.jpg

Microsoft SQL Server 2012 RTM is released and hotfix 2680186 is availible for compatibiliteit with Microsoft Dynamics Ax2012.

https://mbs.microsoft.com/knowledgebase/KBDisplay.aspx?scid=kb;en-us;2680186

Install Ax2012 on SQL Server 2012 RC0

February 13, 2012 2 comments

During the preparations for Microsoft TechDays 2012 my colleague and I installed a fresh Windows Server 2008 R2 with SQL Server 2012 and Ax 2012. At first all seems to work (the AOS started and we could login with the client), but when we started to prepare and opened the ‘Datasource Name’ lookup on the ‘Document Datasources’ we got an error. After some research my colleague Kenny found that the SQL UtilElements-view used a deprecated keyword called FASTFIRSTROW. After updating the view this functionality seems to work fine. (more…)

Monitoring Table Size Growth in SQL Server

March 31, 2011 No comment

During my vacation I came across a older, but interesting article that shows you how you can monitor table size growth in SQL-server. With a bit of creativity you can create interesting report based on this information or you can just use the examples on the second page.

Index vs Index hint

October 1, 2010 4 comments

This is a discussion I had with several colleagues. What is the difference between index and index hint and what do we use in our code?

(more…)

Using ‘Not Like’ in Ax X++

October 13, 2009 No comment

When you want to use wild-cards in Ax, you can write a SQL statement with a LIKE keyword

1
2
select firstonly purchTable
where purchTable.purchId like '09*';

When you want to have all the other records (not like), in X++ SQL-statements you have 3 possibilities:
1.!LIKE :

1
2
select firstonly purchTable
where !(purchTable.purchId like '09*');

2. notExists join :

1
2
3
select firstonly purchTable
    notExists join refPurchTable
    where purchTable.purchId == '09*';

3. Query-object :

1
2
3
4
5
6
7
8
9
10
11
Query query = new Query();
QueryRun queryRun;
; 
query.addDataSource(tableNum(PurchTable)).addRange(fieldNum(PurchTable, PurchId)).value('!09*');
queryRun = new QueryRun(query);
if(queryRun.next())
{
    purchTable = queryRun.get(tableNum(PurchTable));
    print purchTable.PurchId;
}
pause;