{"id":467,"date":"2008-11-10T00:00:00","date_gmt":"2008-11-10T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/the-dba-script-thumb\/"},"modified":"2021-08-24T13:40:38","modified_gmt":"2021-08-24T13:40:38","slug":"the-dba-script-thumb","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/database-administration-sql-server\/the-dba-script-thumb\/","title":{"rendered":"The DBA Script Thumb"},"content":{"rendered":"<div id=\"pretty\">\n<h2>Top 1000 Most Used Queries for the DBA<\/h2>\n<p class=\"start\">On a recent slow Friday afternoon I was practicing my Origami skills with approved expense reports and consolidating hundreds of queries that I&#8217;d collected over the past 10 years as a DBA. I was suddenly struck with what I thought was a good idea: why not put my most-used DBA queries on one of my Red-Gate thumb drives so as to have them always available? Because of the imminent deadline of a presentation for an upcoming SQL Server Users group meeting, I figured I would then share these queries with other SQL souls. Excitedly, I set about creating the DBA script thumb for the presentation. I dubbed it the &#8220;Green Thumb&#8221;, subtitled the &#8220;Top 1000 Most Used Queries for the DBA&#8221;.<\/p>\n<p><img decoding=\"async\" class=\"float-right\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/597-thumbtop.jpg\" alt=\"597-thumbtop.jpg\" width=\"630\" \/><img decoding=\"async\" class=\"float-right\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/597-thumbBase.jpg\" alt=\"597-thumbBase.jpg\" \/><\/p>\n<p>I was left with a slight uneasiness. Could it be that the task of trudging through 1000 queries in would be impossible to do in a one-hour presentation? Was it possible to describe one every four seconds? No. So I narrowed it down to five. Of the five that I will present here, some can be demonstrated in less than one minute, others may take a bit longer. These are queries that DBAs may stumble upon in their course of their careers, and find that they are useful enough to pass on to others. So here are the 1000 queries, now reduced by 995, that I either:<\/p>\n<ul>\n<li>Developed<\/li>\n<li>Enhanced<\/li>\n<li>Use daily<\/li>\n<li>All of the above.<\/li>\n<\/ul>\n<p>These five queries were designed to:<\/p>\n<ul>\n<li>Instantly find sizes of all databases on your servers (for the inquisitive server administrator)<\/li>\n<li>List the last good database backups<\/li>\n<li>Query a trace file with SQL<\/li>\n<li>Read error logs with T-SQL &#8211; not the error log viewer<\/li>\n<li>GO more than once<\/li>\n<\/ul>\n<h2>Database Sizes<\/h2>\n<p>The first query is one that I created from scratch, when I needed to monitor the space on a server, whether transaction logs or data files. This query will display all the information you immediately need and works for all versions of SQL from 2000 onwards. I have used this query to quickly ascertain which log files have grown to the point of eating most of the disk drive, and so must be reined in. This event is easily avoided, and so is very embarrassing to the DBA when it happens; but it does happen more often that I would like to admit.<\/p>\n<p>The query uses the x<b>p_fixeddrives <\/b>and <b>sp_MSForEachDB <\/b>stored procedures to populate and query temp tables. Listing 1 shows the full query (prettified with RedGate Refactor&#8217;s &#8220;Layout SQL&#8221; function):<\/p>\n<h4>Listing 1<\/h4>\n<pre class=\"theme:ssms2012 lang:tsql\">Set NoCount On\r\n--Check to see the temp table exists\r\nIF EXISTS ( SELECT\u00a0 Name\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 tempdb..sysobjects\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Where\u00a0\u00a0 name like '#HoldforEachDB%' )\r\n--If So Drop it\r\n\u00a0\u00a0\u00a0 DROP TABLE #HoldforEachDB_size\r\n--Recreate it\r\nCREATE TABLE #HoldforEachDB_size\r\n\u00a0\u00a0\u00a0 (\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [DatabaseName] [nvarchar](75) COLLATE SQL_Latin1_General_CP1_CI_AS\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NOT NULL,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [Size] [decimal] NOT NULL,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [Name] [nvarchar](75) COLLATE SQL_Latin1_General_CP1_CI_AS\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NOT NULL,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [Filename] [nvarchar](90) COLLATE SQL_Latin1_General_CP1_CI_AS\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NOT NULL,\r\n\r\n\u00a0\u00a0\u00a0 )\r\nON\u00a0 [PRIMARY]\r\n\r\nIF EXISTS ( SELECT\u00a0 name\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 tempdb..sysobjects\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Where\u00a0\u00a0 name like '#fixed_drives%' )\r\n--If So Drop it\r\n\u00a0\u00a0\u00a0 DROP TABLE #fixed_drives\r\n--Recreate it\r\nCREATE TABLE #fixed_drives\r\n\u00a0\u00a0\u00a0 (\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [Drive] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NOT NULL,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [MBFree] [decimal] NOT NULL\r\n\u00a0\u00a0\u00a0 )\r\nON\u00a0 [PRIMARY]\r\n--Insert rows from sp_MSForEachDB into temp table\r\nINSERT\u00a0 INTO #HoldforEachDB_size\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 EXEC sp_MSforeachdb 'Select ''?'' as DatabaseName, Case When [?]..sysfiles.size * 8 \/ 1024 = 0 Then 1 Else [?]..sysfiles.size * 8 \/ 1024 End\r\nAS size,[?]..sysfiles.name,\r\n[?]..sysfiles.filename From [?]..sysfiles'\r\n--Select all rows from temp table (the temp table will auto delete when the connection is gone.\r\n\r\nINSERT\u00a0 INTO #fixed_drives\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 EXEC xp_fixeddrives\r\n\r\n\r\nSelect\u00a0 @@Servername\r\nprint '' ;\r\nSelect\u00a0 rtrim(Cast(DatabaseName as varchar(75))) as DatabaseName,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Drive,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Filename,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Cast(Size as int) AS Size,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Cast(MBFree as varchar(10)) as MB_Free\r\nfrom\u00a0\u00a0\u00a0 #HoldforEachDB_size\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INNER JOIN #fixed_drives ON LEFT(#HoldforEachDB_size.Filename, 1) = #fixed_drives.Drive\r\nGROUP BY DatabaseName,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Drive,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 MBFree,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Filename,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Cast(Size as int)\r\nORDER BY Drive,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Size Desc\r\nprint '' ;\r\nSelect\u00a0 Drive as [Total Data Space Used |],\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Cast(Sum(Size) as varchar(10)) as [Total Size],\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Cast(MBFree as varchar(10)) as MB_Free\r\nfrom\u00a0\u00a0\u00a0 #HoldforEachDB_size\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INNER JOIN #fixed_drives ON LEFT(#HoldforEachDB_size.Filename, 1) = #fixed_drives.Drive\r\nGroup by Drive,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 MBFree\r\nprint '' ;\r\nSelect\u00a0 count(Distinct rtrim(Cast(DatabaseName as varchar(75)))) as Database_Count\r\nfrom\u00a0\u00a0\u00a0 #HoldforEachDB_size <\/pre>\n<p>Figure 1 shows the output of the query, with the multiple result sets that provide an at-a-glance view of how much free space is available on the disk, and how much space is taken by all of the SQL database files on each drive:<\/p>\n<p class=\"illustration\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/597-image002.jpg\" alt=\"597-image002.jpg\" width=\"622\" height=\"390\" \/><\/p>\n<p class=\"caption\">Figure 1: Investigating the space used by each SQL database file on my laptop<\/p>\n<p>As you can see, there is no worry of running out of space on my laptop SQL Server, where this was run. However, if you discover (as I have, on occasion) that the <b>MB_Free<\/b> field is in double digits, then it is time to take some action.<\/p>\n<h2>Last Good Database Backups<\/h2>\n<p>Whether you use a third-party backup tool to backup your databases and log files, or you choose to use native SQL backups, the MSDB database that stores backup history can provide a wealth of information to you, as the DBA responsible for your company&#8217;s data. As the DBA, you may be managing over 100 SQL instances using standard backup scripts, so it is tempting to assume (or pray) that all backups are working successfully. Of course, if a backup fails you&#8217;d typically receive an alert via e-mail. However, Development and QA systems are not as stringently monitored as production.<\/p>\n<p>I quite often use the simple query shown in Listing 2 to interrogate the MSDB database, searching for databases that have not been backed up, either in the past x number of days or ever. Jaws drop when the latter shows up, but you would be surprised what you may find. This query will not only inform you what has and has not been backed up, but will also tell you what type of backup has been performed, full database (D), transaction log(L) or differential(I).<\/p>\n<h4><b>Listing 2<\/b><\/h4>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT\u00a0 sd.name,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 bs.TYPE,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 bs.database_name,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 max(bs.backup_start_date) as last_backup\r\nFROM\u00a0\u00a0\u00a0 master..sysdatabases sd\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Left outer join msdb..backupset bs on rtrim(bs.database_name) = rtrim(sd.name)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 left outer JOIN msdb..backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id\r\nGroup by sd.name,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 bs.TYPE,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 bs.database_name\r\nOrder by sd.name,last_backup<\/pre>\n<p>The output of the query from Listing 2 can be seen in Figure 2, where there are obviously several databases that have not been backed up, as indicated by a NULL value in the <b>last_backup<\/b> column. This is not something a DBA would want to see, especially when master and msdb are included in the list of databases not backed up:<\/p>\n<p class=\"illustration\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/597-image004.jpg\" alt=\"597-image004.jpg\" width=\"618\" height=\"426\" \/><\/p>\n<p class=\"caption\">Figure 2: Finding the last good backup.<\/p>\n<p>This query can also be easily modified to exclude TempDB, which never gets backed up, and to show any &#8220;missed&#8221; log backups. For example, with the query shown in Listing 3, it is possible to query a single database (in this case, the <b>DBA_Info<\/b> database) to see the full backups and log backups that have occurred in the past 10 days:<\/p>\n<h4><b>Listing 3<\/b><\/h4>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT\u00a0 sd.name,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 bs.TYPE,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 bs.database_name,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 bs.backup_start_date as last_backup\r\nFROM\u00a0\u00a0\u00a0 master..sysdatabases sd\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Left outer join msdb..backupset bs on rtrim(bs.database_name) = rtrim(sd.name)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 left outer JOIN msdb..backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id\r\nWHERE sd.name = 'DBA_Info' and bs.backup_start_date &gt; getdate() - 10\r\nOrder by sd.name,last_backup<\/pre>\n<p>Figure 3 shows the results, which can be quickly reviewed to make sure that the logs backups are happening successfully, beginning at 6:00 AM and executing every 2 hours until 4:00PM. You can see that there is a missing log backup, which will require further investigation:<\/p>\n<p class=\"illustration\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/597-image006.jpg\" alt=\"597-image006.jpg\" width=\"549\" height=\"511\" \/><\/p>\n<p class=\"caption\">Figure 3: A missing log backup<\/p>\n<h2>Query a Trace File<\/h2>\n<p>DBAs know SQL, of course, so when the opportunity presents itself to analyze data, no matter what it is, they&#8217;d prefer to use a SELECT statement. A case in point would be a trace file. On any given week, DBAs will find themselves using SQL Profiler to capture SQL Server process information. SQL Profiler is the crystal ball that allows the DBA to see into the mysterious depths of the SQL Servers they manage.<\/p>\n<p>Server-side traces are ideal for storing information in a database table for analysis. However, a single-use client-side trace file, created with SQL Profiler, can also be interrogated using T-SQL, without the need to save the trace to a database table.<\/p>\n<p>Figure 4 shows saving a trace file that has captured several minutes of activity on a SQL Server 2008 instance. The file is saved as &#8220;Trace1.trc&#8221; in &#8220;C:\\Documentation\\SQL_Traces\\&#8221;.<\/p>\n<p class=\"illustration\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/597-image008.jpg\" alt=\"597-image008.jpg\" width=\"563\" height=\"415\" \/><\/p>\n<p class=\"caption\">Figure 4: Saving a Profiler trace file to disk.<\/p>\n<p>In SQL Profiler itself, the captured data can be viewed and searched, but it can take some time to find a specific issue. Figure 5 shows the sample data captured in SQL Profiler.<\/p>\n<p class=\"illustration\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/597-image010.jpg\" alt=\"597-image010.jpg\" width=\"615\" height=\"382\" \/><\/p>\n<p class=\"caption\">Figure 5: A trace file viewed in the Profiler GUI.<\/p>\n<p>Thankfully, there is a much easier way to analyze this data, using a special function included in SQL Server to read a trace file. This function is called fn_trace_gettable. Here is a simple T-SQL query to read the trace file, Trace1.trc, that Profiler saved:<\/p>\n<h4><b>Listing 4<\/b><\/h4>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT * FROM ::fn_trace_gettable('C:\\Documentation\\SQL_Traces\\Trace1.trc', default)\r\norder by starttime\r\nGO<\/pre>\n<p>What if, for example, we want to find any queries that include the word &#8220;DROP&#8221;, &#8220;TRUNCATE&#8221; or &#8220;DELETE&#8221;? That would be as easy as adding in criteria in a WHERE clause, as shown in Listing 5:<\/p>\n<h4><b>Listing 5<\/b><\/h4>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT * FROM ::fn_trace_gettable('C:\\Documentation\\SQL_Traces\\Trace1.trc', default)\r\nWHERE \r\nTextData like '%DROP%' \r\nOR TextData like '%TRUNCATE%' \r\nOR TextData like '%TRUNCATE%'\r\nGO<\/pre>\n<p>With this new filtered query, even with hundreds of thousands of records, we can immediately detect if these statements were executed, by whom and at what time.<\/p>\n<p>The output of the filtered query, Figure 6, shows only these transactions.<\/p>\n<p class=\"illustration\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/597-image012.jpg\" alt=\"597-image012.jpg\" width=\"623\" height=\"205\" \/><\/p>\n<p class=\"caption\">Figure 6: Who has been executing DROP, TRUNCATE or DELETE queries?<\/p>\n<p>Of course, Profiler can save data to a SQL Server table directly (or you can save a .trc file into a database table). However, if you are provided a trace file for analysis, from a third party, and you want to dive in quickly, this method works well.<\/p>\n<h2>Read Error Logs the DBA Way<\/h2>\n<p>Unlike a lot of other DBAs that I know, I do not scour the SQL Error logs daily. I tend to review them when looking for a specific error, or when conducting a periodic security review. It is not that I think it is a waste of time to do it, I just think that I would much prefer to read the logs with T-SQL. Fortunately, SQL Server offers two stored procedures to make this possible, <b>sp_enumerrorlogs<\/b> and <b>sp_readerrolog<\/b>.<\/p>\n<p>As Figure 7 shows, <b>sp_enumerrorlogs<\/b> simply lists the SQL Server error logs:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/597-image014.jpg\" alt=\"597-image014.jpg\" width=\"314\" height=\"284\" \/><\/p>\n<p class=\"caption\">Figure 7: Querying the SQL Server error logs with sp_enumerrorlogs<\/p>\n<p>The procedure <b>sp_readerrorlog<\/b> takes the &#8220;Archive #&#8221; from <b>sp_enumerrorlogs<\/b> as input and displays the error log in table form, as shown in Figure 8, where the first archived log file (1) is passed in as a parameter. Archive number 0 will be the current error log.<\/p>\n<p class=\"illustration\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/597-image016.jpg\" alt=\"597-image016.jpg\" width=\"619\" height=\"450\" \/><\/p>\n<p class=\"caption\">Figure 8: Using sp_readerrorlog<\/p>\n<p>It is possible to load and query every error log file by combining the two stored procedures with a bit of iterative code. Listing 6 shows custom code used to loop through each log file, store the data in a temp table, and subsequently query that data to find more than 5 consecutive failed login attempts as well as the last good login attempt. Remember that you will need to have security logging enabled in order to capture both the successful and failed logins, as most production servers should do.<\/p>\n<h4>Listing 6<\/h4>\n<pre class=\"theme:ssms2012 lang:tsql\">&gt; DECLARE @TSQL\u00a0 NVARCHAR(2000)\r\nDECLARE @lC\u00a0\u00a0\u00a0 INT\r\n\r\n\r\nCREATE TABLE #TempLog (\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 LogDate\u00a0\u00a0\u00a0\u00a0 DATETIME,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 ProcessInfo NVARCHAR(50),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [Text] NVARCHAR(MAX))\r\n\r\n\r\nCREATE TABLE #logF (\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 ArchiveNumber\u00a0\u00a0\u00a0\u00a0 INT,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 LogDate\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 DATETIME,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 LogSize\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INT\r\n)\r\n\r\nINSERT INTO #logF\u00a0\u00a0 \r\nEXEC sp_enumerrorlogs\r\nSELECT @lC = MIN(ArchiveNumber) FROM #logF\r\n\r\n\r\nWHILE @lC IS NOT NULL\r\nBEGIN\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 INSERT INTO #TempLog\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 EXEC sp_readerrorlog @lC\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT @lC = MIN(ArchiveNumber) FROM #logF \r\n\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE ArchiveNumber &gt; @lC\r\nEND\r\n\r\n\r\n--Failed login counts. Useful for security audits.\r\nSELECT Text,COUNT(Text) Number_Of_Attempts\r\nFROM #TempLog where \r\n\u00a0Text like '%failed%' and ProcessInfo = 'LOGON'\r\n\u00a0Group by Text\r\n\r\n--Find Last Successful login. Useful to know before deleting \"obsolete\" accounts.\r\nSELECT Distinct MAX(logdate) last_login,Text \r\nFROM #TempLog \r\nwhere ProcessInfo = 'LOGON'and Text like '%SUCCEEDED%' \r\nand Text not like '%NT AUTHORITY%'\r\nGroup by Text\r\n\r\nDROP TABLE #TempLog\r\nDROP TABLE #logF  \r\n<\/pre>\n<p>The results of this query are shown in Figure 9:<\/p>\n<p>&nbsp;<\/p>\n<p class=\"illustration\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/597-image018.jpg\" alt=\"597-image018.jpg\" width=\"621\" height=\"283\" \/><br \/>\n&lt;<\/p>\n<p class=\"caption\">Figure 9: Querying for successful and unsuccessful login attempts.<\/p>\n<p>We can see that there is a &#8220;BadPerson&#8221; out there who has tried 15 times to access this server. Also, questions often arise as to the last successful login for a certain account. The second result set in Figure 9 shows this information by using the MAX() function for the <b>last_login<\/b> field.<\/p>\n<h2>Go more than once<\/h2>\n<p>The final sample may not be one that you would put on your thumb, but at least you can keep it in the back of your mind. There are various ways using T-SQL, some better than others, to write iterative statements. For example, most would agree that it is a fairly simple task to write a set-based query, like that shown in Listing 7, to produce 1000 random numbers:<\/p>\n<h4><b>Listing 7<\/b><\/h4>\n<pre class=\"theme:ssms2012 lang:tsql\">SET NOCOUNT ON\r\nDECLARE @i int\r\nDECLARE @therow int\r\nSET @i = 1\r\nWhile @i &lt;= 1000\r\n\u00a0\u00a0 BEGIN\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 set @theRow = convert(int, rand() * 100)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 Select @therow\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 Set @i = @i + 1\r\n\u00a0\u00a0 END<\/pre>\n<p>What some may not know is that the GO statement can perform the same function, in one or more batch statements, simply by specifying the number of times the code should be executed. Listing 8 shows a quick way to get the same results, 1000 random numbers, using only one declared variable and a GO 1000 statement. It works well for Insert statements too.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">Set NOCOUNT on\r\nDECLARE @therow int\r\nset @theRow = convert(int, rand() * 100)\r\nSelect @theRow\r\nGo 1000<\/pre>\n<p>The final output of the query can be seen in figure 10:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/597-image020.jpg\" alt=\"597-image020.jpg\" width=\"371\" height=\"561\" \/><\/p>\n<p class=\"caption\">Figure 10: GO&#8217;ing 1000 times<\/p>\n<h1>Summary<\/h1>\n<p>One thing I have learned from being a DBA, and working with other experienced DBAs in the course of my career, is that we all tend to do things differently to get the same results. These are five queries that I find myself using every day. They can easily be expanded upon, and I know that there are enough great DBAs reading this who may do just that, or will choose to post their own versions. I welcome it. Now it is on to the other 995 queries.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>Like many DBAs, Rodney has squirrelled away a large number of routines that he uses almost daily to check on his servers and databases. Of this large collection he chooses five that he wouldn&#8217;t want to be without. and there is something for everyone in this DBA&#8217;s Script collection which goes with him on his &#8216;Script thumb&#8217;&hellip;<\/p>\n","protected":false},"author":221800,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143527],"tags":[4168,4170,4150],"coauthors":[11298],"class_list":["post-467","post","type-post","status-publish","format-standard","hentry","category-database-administration-sql-server","tag-database","tag-database-administration","tag-sql"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/467","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221800"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=467"}],"version-history":[{"count":5,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/467\/revisions"}],"predecessor-version":[{"id":75123,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/467\/revisions\/75123"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=467"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=467"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=467"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=467"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}