SQL SERVER – Database File Names and Extentions – Notes from the Field #025

[Notes from Pinal]: People believe Shakespeare’s birth date is on April 26. ” What is there in a name?” – I often hear this statement, which famously quotes from Shakespeare.  I think we developer believe in this statement as most of our various are either BAR or FOO. However, this is extremely inefficient and not convenient. There are many reasons we should have meaningful names to our objects, variables and various elements. There are times when incorrect naming convention can just get user in trouble or lead organizations to disaster. My friend Tim shares this humorous and interesting story. Read on

timradney SQL SERVER   Database File Names and Extentions   Notes from the Field #025Linchpin People are database coaches and wellness experts for a data driven world. In this 25th episode of the Notes from the Fields series database expert Tim Radney (partner at Linchpin People) explains in a very simple words of names and extensions.


Database file naming standards are important. With Microsoft SQL Server the default naming standard is not a requirement so administrators can change from the default .mdf, .ldf and .ndf.  Recently I encountered a SQL Server instance that was having performance issues. The customer was reporting very slow response times and couldn’t figure out what the issue was.

Reluctantly the administrator reached out to the database team to get a professional opinion.  For the database team this was the first they learned of this new server so we quickly performed our standard check. Two things stood out which was high CPU utilization and the executable consuming the most CPU was antivirus as well as high disk IO.  We stopped the “on access” scan and performance of the SQL Server improved as well as disk IO dropped very low.

We reached out to our antivirus team and was assured that our standard policies were applied to this server which includes excludes for our standards for database file naming.  We continued to research deviations from our standard server build against this instance, since we did not build it and that is when we found the admin/vendor/customer (non dba) chose a different naming standard for the files.  The extensions they chose were.001 for data, .002 for logs, .003 for ndf.

Due to not having proper excludes for those files by the antivirus software, the customer created a denial of service attack against their self. The on access scan effectively created such a load that it prevented anything else from running.

This is not the first time I have encountered this type of issue and blogged about this in 2012. http://timradney.com/2012/06/18/file-extensions-for-sql-server-database-files/

If you decide that for whatever reason you would like to deviate from the default naming standard or your companies naming standard for database files, please get with the people that manage your antivirus software to make sure they create exclusions for your new naming standard.

If you want me to take a look at your server and its settings, or if your server is facing any issue we can Fix Your SQL Server.

Note: Tim has also written an excellent book on SQL Backup and Recovery, a must have for everyone.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Tools for Proactive DBAs – Central Management Server – Notes from the Field #024

[Note from Pinal]: This is a new episode of Notes from the Fields series. AlwaysOn is a very complex subject and not everyone knows many things about this. The matter of the fact is there is very little information available on this subject online and not everyone knows everything about this. This is why when a very common question related to AlwaysOn comes, people get confused.

JohnSterrett SQL SERVER   Tools for Proactive DBAs   Central Management Server   Notes from the Field #024

In this episode of the Notes from the Field series database expert John Sterrett (Group Principal at Linchpin People) explains a very common issue DBAs and Developer faces in their career and is related to AlwaysOn Availability Group. Linchpin People are database coaches and wellness experts for a data driven world. Read the experience of John in his own words.


After completing several AlwaysOn Availability Group implementations there are two questions that come up frequently. When did my availability group failover? Where is my read-write replica? The answer to the first one is provided here [http://johnsterrett.com/2014/03/18/where-is-my-availability-group/ ]. Today, were going to look at the answer to the second question.

Where is my read-write replica?

The following script, when executed on an availability group replica returns the availability group name, current role state, and database name. This will let you know if this instance is hosting the PRIMARY “read/write” replica.

IF SERVERPROPERTY ('IsHadrEnabled') = 1 BEGIN
SELECT
AvailabilityGroup = ag.name,
AvailabilityGroupRole = ars.role_desc,
db.name
FROM sys.databases db
INNER JOIN sys.availability_databases_cluster adc ON db.group_database_id = adc.group_database_id
INNER JOIN sys.availability_groups ag ON adc.group_id = ag.group_id
INNER JOIN sys.dm_hadr_availability_replica_states ars ON ag.group_id = ars.group_id
AND db.replica_id = ars.replica_id
END
ELSE BEGIN
SELECT
NULL AS AvailabilityGroup, NULL AS AvailabilityGroupRole, name
FROM sys.databases
END

on1 SQL SERVER   Tools for Proactive DBAs   Central Management Server   Notes from the Field #024

How do I check all replicas?

Now you know how to check if an instance of SQL Server participating in an AlwaysOn Availability Group is the “Read/Write” PRIMARY role. Next, we can utilize Central Management Server to run the same script across your other instances participating as replicas in your AlwaysOn Availability Group configuration. If you have never used Central Management Server this step-by-step guide can get you going in 10 minutes.

For this week, I have an windows failover cluster with two nodes “SQL2012DR” and “SQL2012PROD1 aka localhost,1433”). Each node hosts its own default instance of SQL Server. There are two separate AlwaysOn Availability Groups. Using Central Management Server, I can rerun the query above and quickly see where my read/write replica is.

on2 SQL SERVER   Tools for Proactive DBAs   Central Management Server   Notes from the Field #024

Are your servers running at optimal speed or are you facing any SQL Server Performance Problems? If you want to get started with the help of experts read more over here: Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Finding Last Backup Time for All Database – Last Full, Differential and Log Backup – Optimized

Four years ago, I wrote a blog post SQL SERVER – Finding Last Backup Time for All Database. It has been a very popular script. SQL Expert Sravani has posted a fantastic script which also displays last full, differential and log backup for the database – I have blogged about it here SQL SERVER – Finding Last Backup Time for All Database – Last Full, Differential and Log Backup.

Well, in technology, there is always space for improvement and we should always be ready to learn and progress. SQL Expert TheSQLGuru came up with further optimized script which also lists all the details which earlier blog post listed, and the script is very quick to execute. Here is the script.

SELECT d.name AS 'DATABASE_Name',
MAX(CASE WHEN bu.TYPE = 'D' THEN bu.LastBackupDate END) AS 'Full DB Backup Status',
MAX(CASE WHEN bu.TYPE = 'I' THEN bu.LastBackupDate END) AS 'Differential DB Backup Status',
MAX(CASE WHEN bu.TYPE = 'L' THEN bu.LastBackupDate END) AS 'Transaction DB Backup Status',
CASE d.recovery_model WHEN 1 THEN 'Full' WHEN 2 THEN 'Bulk Logged' WHEN 3 THEN 'Simple' END RecoveryModel
FROM MASTER.sys.databases d
LEFT OUTER JOIN (SELECT database_name, TYPE, MAX(backup_start_date) AS LastBackupDate
FROM msdb.dbo.backupset
GROUP BY database_name, TYPE) AS bu ON d.name = bu.database_name
GROUP BY d.Name, d.recovery_model

Thanks TheSQLGuru for excellent script, you win USD 20 Amazon Gift Card or INR 1000 Flipkart Gift Voucher. I have already sent you emails with details.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Finding Jobs Shrinking Database Files – Notes from the Field #023

[Notes from Pinal]: Search on the internet about ‘Shrinking Database’, you will find plenty of advice why it is bad and it should not be done. If you do not believe me, just try it yourself. Even I have blogged about it before that it is an absolutely bad thing to do. However, there are very few blogs which talks about how to solve this ancient problem. The reason, I call it ancient problem is that there are quite a few organizations which are doing this for many years and they have many different places where they have included Shrinking Database code. There are quite a few places, where I see that people have created database jobs to shrink the database. I was talking to Tim about this and he comes up with a beautifully simple script where he demonstrated how to find out jobs which are shrinking database files.

timradney SQL SERVER   Finding Jobs Shrinking Database Files   Notes from the Field #023Linchpin People are database coaches and wellness experts for a data driven world. In this 16th episode of the Notes from the Fields series database expert Tim Radney (partner at Linchpin People) explains a very simple script to find out jobs shrinking database files.


Often when analyzing a SQL Server Instance I come across jobs that are automating shrinking a database file.  Checking for a shrinking operation is important when analyzing a server for a number of reasons.  One is that anytime a data file or log file has to grow, transactions have to wait until the growth operation is complete thus causing a performance impact.  Shrinking a log file can contribute to high virtual log file counts and shrinking a data file will lead to fragmenting the database.

I check to see if any database maintenance plans exist and if so I check to see if auto shrink has been chosen, occasionally I find this is the case.  I also have a TSQL script that will search for any reference to the word ‘shrink’ in a job step.  I find custom jobs like this more often than auto shrink being used in a maintenance plan.  Often times the justification for someone configuring a job like this is due to drive space issues.

In cases where the shrink is to reclaim drive space it is most often due to not having a proper backup routine in place for the transaction logs or a process that keeps a transaction open for an extended period of time.  The transaction log will need to be large enough to handle your index maintenance, ETL processes and transactions that occur between log backups.   If you find that you are having an erratic large growth, then you will need to examine what transactions or processes are holding open an active transaction for such a long duration or the frequency of your log backups.

Below is the script I use to search for the word shrink in any tsql job step.

DECLARE @search VARCHAR(100)
SET @Search = 'shrink'
SELECT  A.[job_id],
B.[name],
[step_id],
[step_name],
[command],
[database_name]
FROM    [msdb].[dbo].[sysjobsteps] A
JOIN [msdb].dbo.sysjobs B ON A.job_id = B.[job_id]
WHERE   command LIKE '%' + @Search + '%'
ORDER BY [database_name],
B.[name],
[step_id]

If you want me to take a look at your server and its settings, or if your server is facing any issue we can Fix Your SQL Server.

Note: Tim has also written an excellent book on SQL Backup and Recovery, a must have for everyone.


Related Articles:

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Finding Last Backup Time for All Database – Last Full, Differential and Log Backup

backupimage SQL SERVER   Finding Last Backup Time for All Database   Last Full, Differential and Log BackupAbout four years ago, I wrote a blog post where I posted a script about finding backup time for all the databases. You can see the blog post over here SQL SERVER – Finding Last Backup Time for All Database. It has been a very popular script. However, this script was just giving details about last full backup time. SQL Expert Sravani has posted a fantastic script which also displays last full, differential and log backup for the database.

Here is the script.

SET NOCOUNT ON
GO
SET quoted_identifier OFF
DECLARE
@dbname AS VARCHAR(80)
DECLARE @msgdb AS VARCHAR(100)
DECLARE @dbbkpname AS VARCHAR(80)
DECLARE @dypart1 AS VARCHAR(2)
DECLARE @dypart2 AS VARCHAR(3)
DECLARE @dypart3 AS VARCHAR(4)
DECLARE @currentdate AS VARCHAR(10)
DECLARE @server_name AS VARCHAR(30)
SELECT @server_name = @@servername
SELECT @dypart1 = DATEPART(dd,GETDATE())
SELECT @dypart2 = DATENAME(mm,GETDATE())
SELECT @dypart3 = DATEPART(yy,GETDATE())
SELECT @currentdate= @dypart1 + @dypart2 + @dypart3
PRINT "#####################################################################"
PRINT "# SERVERNAME : "+ @server_name + " DATE : "+ @currentdate +"#"
PRINT "#####################################################################"
PRINT "DatabaseName Full Diff TranLog"
PRINT "##########################################################################################################################################"
SELECT SUBSTRING(s.name,1,50) AS 'DATABASE Name',
b.backup_start_date AS 'Full DB Backup Status',
c.backup_start_date AS 'Differential DB Backup Status',
d.backup_start_date AS 'Transaction Log Backup Status'
FROM MASTER..sysdatabases s
LEFT OUTER JOIN msdb..backupset b
ON s.name = b.database_name
AND b.backup_start_date =
(SELECT MAX(backup_start_date)AS 'Full DB Backup Status'
FROM msdb..backupset
WHERE database_name = b.database_name
AND TYPE = 'D') -- full database backups only, not log backups
LEFT OUTER JOIN msdb..backupset c
ON s.name = c.database_name
AND c.backup_start_date =
(SELECT MAX(backup_start_date)'Differential DB Backup Status'
FROM msdb..backupset
WHERE database_name = c.database_name
AND TYPE = 'I')
LEFT OUTER JOIN msdb..backupset d
ON s.name = d.database_name
AND d.backup_start_date =
(SELECT MAX(backup_start_date)'Transaction Log Backup Status'
FROM msdb..backupset
WHERE database_name = d.database_name
AND TYPE = 'L')
WHERE s.name <>'tempdb'
ORDER BY s.name

Sravani, please send me email at my mail id and I will send USD 20 worth Amazon Gift Card or INR 1000 Flipkart Gift Voucher for your wonderful contribution. If you use any such script in your daily routine. Please send me email and I will be glad to post the same on blog with due credit.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – SSIS Data Flow Troubleshooting – Part1 – Notes from the Field #019

[Note from Pinal]: This is a new episode of Notes from the Field series. SQL Server Integration Service (SSIS) is one of the most key essential part of the entire Business Intelligence (BI) story. It is a platform for data integration and workflow applications. The tool may also be used to automate maintenance of SQL Server databases and updates to multidimensional cube data.

andyleonard SQL SERVER   SSIS Data Flow Troubleshooting   Part1   Notes from the Field #019

In this episode of the Notes from the Field series I asked SSIS Expert Andy Leonard a very crucial question – How to troubleshoot SSIS data flow? It is such a complicated problem that everyone thinks they know, but not everyone can resolve this issue. Any Leonard is a world renowned expert, explains in this blog post how to troubleshoot SSIS data flow.


SQL Server Integration Services (SSIS) is designed to move data. Several SSIS tasks can move data but the Data Flow Task is arguably used most to accomplish moving data. When bad things happen to good developers, it helps to know how to troubleshoot. Writing about all the ways to troubleshoot an SSIS Data Flow Task will take more than one post. So this is Part 1.

Building a Quick Demo Package

If you are interested in working through some demos with me, create a new SSIS project named DataFlowTests. Add a Data Flow Task to the Control Flow as shown in Figure 1:
DFT1 1 SQL SERVER   SSIS Data Flow Troubleshooting   Part1   Notes from the Field #019

You can use any source data you desire, but I am going to use a file containing weather data captured from my weather station here in Farmville, Virginia beginning in December 2008. You can obtain this data here. Click the Data Flow tab to edit the Data Flow Task. Add a Flat File Connection Manager configured to consume the sensor1-all.csv file containing the December 2008 weather data. Add a Flat File Source adapter and link it to the Flat File Connection Manager. You Data Flow should appear as shown in Figure 2:
DFT1 2 SQL SERVER   SSIS Data Flow Troubleshooting   Part1   Notes from the Field #019

To test the Flat File Source adapter, we need to connect an output to another component. Add a Union All transformation and connect the output of the Flat File Source to it, as shown in Figure 3:
DFT1 3 SQL SERVER   SSIS Data Flow Troubleshooting   Part1   Notes from the Field #019

You can now test-execute the SSIS package. You should see results similar to those shown in Figure 4:
DFT1 4 SQL SERVER   SSIS Data Flow Troubleshooting   Part1   Notes from the Field #019

This data flow isn’t doing very much. The Flat File Source adapter is coupled to the Flat File Connection Manager. The Flat File Connection Manager reads data from the sensor1.all.csv file you downloaded from andyweather.com. Weather data is read into the data flow from the file via the Flat File Connection Manager and the Flat File Source adapter. The 106 rows of weather data are then passed into the Data Flow Path connecting the Flat File Source adapter output to the first Union All Input (Union All Input 1).

Insert a Warning

Let’s create an issue that will raise a warning. Open the Flat File Connection Manager Editor, click on the Advanced page, select the Min T column, and change its DataType property to “four-byte signed integer [DT_I4] as shown in Figure 5:
DFT1 5 SQL SERVER   SSIS Data Flow Troubleshooting   Part1   Notes from the Field #019

When you close the Flat File Connection Manager Editor, a warning icon displays on the Flat File Source adapter. If you hover over the Flat File Source adapter with your mouse, a tooltip will display some of the warning text as shown in Figure 6:
DFT1 6 SQL SERVER   SSIS Data Flow Troubleshooting   Part1   Notes from the Field #019

You can see more of the Warning if you execute the package and the view the Progress (if the package is running the SSIS Debugger) or Execution Results tab (if the package has been executed in the Debugger and then the Debugger has been stopped), as shown in Figure 7:
DFT1 7 SQL SERVER   SSIS Data Flow Troubleshooting   Part1   Notes from the Field #019

The warning we injected by changing the DataType property of the Min T column in the Flat File Connection Manager does not cause an error. The warning stems from the fact that there is a difference between the data type of the Min T column flowing into the Flat File Source adapter from the Flat File Connection Manager and the data type of the Min T column flowing out of the Flat File Source adapter. You can correct this warning by opening the Flat File Source adapter and clicking the Columns page. When opening the Editor (or Advanced Editor) of the Flat File Source adapter, you will be prompted as shown in Figure 8:
DFT1 8 SQL SERVER   SSIS Data Flow Troubleshooting   Part1   Notes from the Field #019

Click the “Yes” button to synchronize the Min T column’s data type in the Flat File Source adapter’s output (the metadata of the “output column”) with the Min T column’s data type supplied from the Flat File Connection Manager (the metadata of the “external column”).

Insert an Error

One way to generate an error in our simple data flow task is to configure the Flat File Connection Manager to perform an impossible data type coercion. Every value in the Min HI column contains “—“. Let’s configure the Min HI column in the Flat File Connection Manager and the Flat File Source adapter as data type Integer.

First, open the Flat File Connection Manager Editor and change the Min HI DataType property to DT_I4 as shown in Figure 9:
DFT1 9 SQL SERVER   SSIS Data Flow Troubleshooting   Part1   Notes from the Field #019

When you click the “OK” button, we see the same warning displayed in Figure 6 – the external columns are out of synchronization with the data source columns. Right-click the Flat File Source adapter and click Show Advanced Editor as shown in Figure 10:
DFT1 10 SQL SERVER   SSIS Data Flow Troubleshooting   Part1   Notes from the Field #019

When you attempt to open the Advanced Editor, you will be prompted to fix the data type mismatch between the output columns and the external columns. Click the “No” button to proceed, as shown in Figure 11:
DFT1 11 SQL SERVER   SSIS Data Flow Troubleshooting   Part1   Notes from the Field #019

When the Advanced Editor for the Flat File Source adapter opens, click the Input and Output Properties tab. Expand the Flat File Source Output node, and then expand the Output Columns node. Click on the Min HI column and change the DataType property to “four-byte signed integer [DT_I4] as shown in Figure 12:

DFT1 12 SQL SERVER   SSIS Data Flow Troubleshooting   Part1   Notes from the Field #019

Click the “OK” button to close the Advanced Editor. Note the Union All now displays a validation error as shown in Figure 13:

DFT1 13 SQL SERVER   SSIS Data Flow Troubleshooting   Part1   Notes from the Field #019

What has happened? The tooltip reveals an issue with a metadata mismatch, but the error is truncated. We can view the full error message in the Error List by clicking ViewàError List as shown in Figure 14:
DFT1 140 SQL SERVER   SSIS Data Flow Troubleshooting   Part1   Notes from the Field #019

Note: The Error List option is missing from the View menu in some versions of SSDT-BI. In those environments, use the keystrokes. Hold down Ctrl and press \ followed by E.

The Error List window displays the full text of errors, warnings, and other informational messages, and is shown in Figure 15:
DFT1 15 SQL SERVER   SSIS Data Flow Troubleshooting   Part1   Notes from the Field #019

There are a couple ways to fix this error. The easy way is to delete the Union All and add a new Union All transformation in its place (the hard way is to edit the package’s XML).

Conclusion

We are going to stop here. We have introduced an error in the Flat File Source Adapter, though. And we will begin there in Part 2.

If you want to get started with SSIS with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Dude, Where is the SQL Agent Job History? – Notes from the Field #017

[Note from Pinal]: Everybody knows what is SQL Agent Jobs. We know that they run at regular interval and does the task assigned. However, there are way many more things we should know about SQL Jobs but do not know it or never learnt to care about it. One of the most essential elements of SQL Agent Job is history.

JohnSterrett SQL SERVER   Dude, Where is the SQL Agent Job History?   Notes from the Field #017

In this episode of the Notes from the Field series database expert John Sterrett (Group Principal at Linchpin People) discusses about SQL Agent Job History in this blog post. Linchpin People are database coaches and wellness experts for a data driven world. Read the experience of John in his own words.


In this tip of the week, I want to cover a common problem I see too often in the field. Multiple times I have been asked, “Dude, where is the SQL Agent Job History?” I had a job fail but I cannot view the history and get details towards why it failed. Today, I am going to cover why the job history is most likely disappearing on you.

Typically there are two reasons why your SQL Agent Job History is missing. First, someone or a process is running sp_purge_jobhistory to purge the history. Typically, I find that this isn’t the reason why your history is disappearing randomly.  Normally, it’s the default settings for the SQL Agent Job History that is purging your history. In this week’s tip were going to focus on the cause and effect of the default settings focusing on why you might need to change them.

Setup Demo

This tip is going to utilize two SQL Agent jobs that you need to download if you want to walk through step by step through the demo. The first job is “JobHistoryDemo – Divide by Zero” and the second is “JobHistoryDemo – Batch Process.” In this demo, were going to execute a job that will fail (Divide by Zero) and then were going to execute another job (Batch Process) over a 1,000 times.  For the purpose of this demo, the batch process job just does “SELECT 1” from tempdb.

For the purpose of this demo, were going to tweak the default setting for max rows per job. By default, this is set to 100. We are modifying this to be 1000 so we only need two jobs for the purpose of this demo. We will go into more detail about SQL Agent properties later in this tip.

USE [msdb]
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @jobhistory_max_rows_per_job=1000
GO

Run Demo Code

After downloading and running the scripts you will have the two new “JobHistoryDemo” SQL Agent Jobs shown below.

nftf17 1 SQL SERVER   Dude, Where is the SQL Agent Job History?   Notes from the Field #017

Run the following script it will take about 30 minutes to complete. You will notice that it will start to automatically purge the history for all the SQL Agent jobs. We will dive into why during the next section of the tip.

/* Job fails */
EXEC sp_start_job @job_name = 'JobHistoryDemo - Divide by Zero';
DECLARE @i INT;
SET @i = 1200; -- default history retention for job history records
WHILE @i > 0 BEGIN
EXEC
sp_start_job @job_name ='JobHistoryDemo - Batch Process'
WAITFOR DELAY '00:00:02';
SET @i=@i-1
END

If you want to monitor the progress you can execute the following script. You will notice that the history gets purged once the “JobHistoryDemo – Divide by Zero” executes 1,000 times.

SELECT j.name, COUNT(*) Executions
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobhistory h
ON j.job_id = h.job_id
GROUP BY j.name
ORDER BY Executions DESC

Here is a screen shot just right before we hit 1,000 executions.

nftf17 2 SQL SERVER   Dude, Where is the SQL Agent Job History?   Notes from the Field #017

Here is a screen shot just right after we had a 1,000 executions. Notice, that just 1,000 total SQL Agent job executions triggers the purging process. It doesn’t have to be limited to just a specific job.

nftf17 3 SQL SERVER   Dude, Where is the SQL Agent Job History?   Notes from the Field #017

Where did the history go?

Now we have the scenario described in the first paragraph of this tip. In this scenario, we have a controlled scenario where job history is missing. This can be very frustrating if you have multiple jobs executing frequently as it will seem like the data is being purged randomly.

Let’s take a look at the SQL Agent Job Activity monitor.   We can clearly see that the “JobHistoryDemo – Divide by Zero” job failed. Right click on the job and select view history.

nftf17 4 SQL SERVER   Dude, Where is the SQL Agent Job History?   Notes from the Field #017

You will notice all the job history is gone. Next we will show you most likely why you don’t see the job history.

nftf17 5 SQL SERVER   Dude, Where is the SQL Agent Job History?   Notes from the Field #017

How do we resolve the problem?

The first step to solving the problem is to identify the root cause. Reading along you might already know why the history is gone. Let’s drill down to the properties of the SQL Agent and focus on the history tab.

nftf17 6 SQL SERVER   Dude, Where is the SQL Agent Job History?   Notes from the Field #017

You will notice that the default setting is to only keep the last 1,000 history records for all the SQL Agent Jobs. The SQL Agent will also only keep 100 history records for a single job. It’s why we modified the Maximum job history rows per job property above for this demo.

nftf17 7 SQL SERVER   Dude, Where is the SQL Agent Job History?   Notes from the Field #017

Now that we have identified a root cause to this problem here is how you resolve it. Disable the limit size of job history log and utilize sp_purge_jobhistory to purge as needed to meet your storage and business requirements for maintaining agent job history.

Finally, if you need to maintain the results of SQL Agent jobs I recommend using the native functionality built-in to store message details. We will cover this in a future tip.

Are your servers running at optimal speed or are you facing any SQL Server Performance Problems? If you want to get started with the help of experts read more over here: Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Finding the Last Backup for All Databases – Notes from the Field #016

[Notes from Pinal]: During my presentation, I always ask developer a simple question – When did you take your last database backup? I get two different kinds of answers – 1) few replies with No Idea and 2) few replies with some date time. When I further ask if they are confident that their backup was taken successfully, I see confuse faces. Well, in this episode of Notes from the Field Tim answers the very same question with answer.

timradney SQL SERVER   Finding the Last Backup for All Databases   Notes from the Field #016Linchpin People are database coaches and wellness experts for a data driven world. In this 16th episode of the Notes from the Fields series database expert Tim Radney (partner at Linchpin People) explains a very simple script to find when was last successful backup of all the database was taken.


As data professionals (those responsible for supporting database environments) one of our most important task is making sure that we have proper backups. I regularly have to perform audits of SQL Server environments and one of my first checks is to make sure that backups are being performed.  A quick check is to see when the last full backup was made.  You can do this by running the following script.

SELECT  a.Name AS [DB_Name],
COALESCE(CONVERT(VARCHAR(12), MAX(b.backup_finish_date), 101), '-') AS LastBackup
FROM    sys.sysdatabases a
LEFT OUTER JOIN msdb.dbo.backupset b ON b.database_name = a.name
GROUP BY a.Name

This will only tell you when the last full was completed. If you are running daily full backups then this will be sufficient. The script I personally run can be located on a recent blog post. This post includes a script that will get the database name, recovery model, most recent full, most recent differential, and the last two transaction log backups.

Anytime I talk with someone about backups I have to stress the importance of validating your backups. You need to have a process in place to regularly validate your backups by restoring them to another environment. Backups that can’t be restored when you need them are useless.

If you want me to take a look at your server and its settings, or if your server is facing any issue we can Fix Your SQL Server.

Note: Tim has also written an excellent book on SQL Backup and Recovery, a must have for everyone.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Expanding Your Skills – Notes from the Field #015

[Note from Pinal]: This is a fifteenth episode of Notes from the Field series.  We all want to learn more and progress in our career. It is human nature to look for change and variety after a while. In most of the cases, it is quite easy to move inside your organization if you are an expert in the skills of the other technology. For example, if you are a developer and have the skill set of DBA, you can easily switch to that job if there is an opening. However, the biggest challenge which we all face is how to keep ourself updating with new technology and expand our skill set when we are so busy doing our day job.

bkbphoto SQL SERVER   Expanding Your Skills   Notes from the Field #015

In this episode of the Notes from the Field series database expert Brian Kelley explains a very crucial issue DBAs and Developer faces when they want to advance in their career and expand their skills. Linchpin People are database coaches and wellness experts for a data driven world. Read the experience of John in his own words.


I’m sure the client was rather surprised by the questions I was asking. I was brought in to help consult on their SQL Server security and I was asking questions about OS practices, about intrusion detection, about firewalls, about their networks. We were 30 minutes into the call and I hadn’t gotten to SQL Server yet. Knowing that the question, “Aren’t we going to talk about SQL Server?” was building in the client’s mind, I simply stated, “I know you’re wondering why I’m asking so many questions that aren’t related to SQL Server. I’m trying to assess the whole environment because SQL Server is just one part of the system. A weakness in any part of the system could lead to the breach you don’t want.” There was an audible sigh as the client understood.

As SQL Server DBAs and database developers, we are charged with understanding how SQL Server works. However, as IT professionals, it behooves us to be more rounded. SQL Server is a part of the overall system. It’s an important part, because our systems are data driven and SQL Server holds the data. However, understanding more about the other pieces of the system helps not only in security, but in overall operations and troubleshooting.

When I speak about professional development, I suggest DB pros increase their skills in the following areas:

  • Learning the basics of how the Windows operating system functions
  • Knowing and being able to write in a scripting language
  • Understanding how TCP works, how basic routing functions, and how name resolution (DNS) is accomplished.
  • Developing a rudimentary understanding of the hardware components of servers.
  • Practicing and becoming proficient on performance monitoring beyond just the application or SQL Server.

The majority of people can’t attack all of these at once. My advice is to pick one, bring it up to an acceptable level of competency, and then move on to the next area. So how do you start? Andy Leonard, speaking of SSIS, suggested understanding the resources you have available to you and then putting in the work. I agree with that, but I am going to expand on it. Here’s a reasonable action plan:

  1. Get some introductory resources on the subject. These could be books, articles, free training, paid training, or anything that begins to expose you to the subject. Don’t worry if some or a lot of what you’re looking at you don’t understand. What you’re trying to do here is get more familiar with the subject so you can figure out what you don’t know.
  2. Continue to investigate potential resources so you can dive deeper and learn more.
  3. Develop an environment for you to practice. This could be a new set of VMs, it could be a laptop that you only use for whatever it is you’re practicing, but outfit yourself with what you need to get started.
  4. Identify what it is you need to learn.
  5. Develop steps to cover those items. Ensure you include adequate practice for what you’re learning. Also include milestones to celebrate in order to help maintain your motivation.
  6. Put in the work!

Also, be flexible. Be ready to adjust your plan at any of these steps. However, don’t be too flexible. In other words, don’t adjust without a good reason. Just because something is the “new shiny” is not a good reason to adjust. Learning that you missed something important in your research that you should know, well, then you better adjust your plan. But most of all, keep pushing forward.

If you want to get started with performance tuning and database security with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – SysTools SQL Recovery Software – An Experiment to Recover Database Corruption

NOTE: This was my experiment with database corruption. If your database is corrupted, you should contact professionals who do this as their primary job. Always make sure that you do experiments on the copy of your database on your development server, rather than on production server.

SQL Server helps to maintain the pace of managing database orderly, following the step by step procedure of updating and handling the Structured Query Language. Duties and responsibilities of database administrator are ever changing as well as challenging from the proper implementation of the system process of being prepared with the recovery solutions for cases of emergency. DBA (Database Administrator) should make sure that the database gets recovered and restored if error messages trouble or corruption issues are reported in the Server. SQL database may get corrupted, inaccessible or even dropped for what so ever probable reasons. The efficiency of an SQL Server admin is calculated according to the reliability, completeness and perfection exhibited by the recovery process they selected. SQL Recovery software can prove to be a perfect solution for most of the SQL MDF and NDF related corruption issues.

SysTools SQL Recovery software is the tool of choice by many SQL DBA because of the multiple functionalities it exhibits while resolving the troubles with SQL database files. The main peculiarity of this tool is that it succeeds where even the inbuilt disaster recovery options for MS SQL server fails. The procedures usually practiced by DBA after SQL database corruption is declining towards failure because of the complex, incomplete steps they provide. The disaster recovery points set by Microsoft® for SQL server like Database mirroring, Transactional replication/warm standby server, Failover clustering, or the most preferred backup and restore feature is reported to end up in disaster if attempt to restore the database went wrong. Here comes the relevance of SQL Recovery software that works with perfection with all the user friendly features added to make SQL MDF/NDF file recovery successful one.

What Makes SysTools SQL Recovery Software One Outstanding Solution?

SQL Recovery software has been selected the most amongst many other names available in the online market belonging to the similar arena. The tool holds accuracy of provisioning SQL database recovery owing to its features and updates. Capability to restore database from corrupt to healthy format even after the encounter of error messages related to the issues like database not mounting, damaged RAID Strip, etc. Also the potential of repairing the database out of failure in Raid Rebuild attempt, RAID setup, etc. with regard to RAID controller and RAID array along with software/ hardware troubles, file system/OS failure etc. is amongst its greatest praise. Some of the software qualities have been listed with the vision of making it easier for SQL Server Administrators or users to confirm the best possible solution for their database recovery.

Performance Enhancers

  • Quick and Advance Scan feature to make scanning of MDF/NDF file specific. For normal file corruption the option for quick scan is suggested, but for severe corruption issues the advance scans is the one to select.
  • MDF and NDF database file recovery process is possible for the SQL server version 2012, 2008, 2005, 2000 and 2008 R2.
  • Advance scan and repair process offered for tackling with both; primary and secondary database corruption / inaccessibility.
  • Deleted SQL database recovery is also offered (tried and tested) with the assurance of database recovery along with maintenance of processing accuracy without data loss.
  • Recover SQL Rules, Triggers, Stored Procedures, Functions, Views, Rules, Triggers, primary and unique keys.

Potentiality At Recovery

  • Save database scan as .str files to face situational needs where either the process got interrupted or was intentionally intervened due to the large size.
  • Auto detection of SQL Server version made the process run without any trouble of platform mismatch.
  • SQL Server compatible SQL scripts can be created in case the database has to be created in the absence of SQL Server setup.
  • Recover SQL database in case even if the DBCC CHECKDB command fails to give accurate results.
  • XML data is also recoverable if XML type columns or variables are created in SQL Server.

Software Pros Reflecting Facilitative Proficiency

Erroneous Corruption State

Error based corruption is the worst of all as compared to others, especially when the messages don’t reflect the cause of occurrence while pose a database inaccessible. Recovering data from such circumstances using this program is effectual due to the guarantee of recovery it extends to users even after a delay at being attended. Delay caused at attending error based issues encountered in SQL Server lead to severity, thus, diminishing the probability of recovery; which certainly isn’t the case with SQL database recovery program.

Damaged Allocation Pages

A lot of complexity is involved in recovering allocation pages while the most challenging thing about it is that; an entire database has to be repaired for a single allocation page to be recovered from the damage. The SQL MDF recovery program is well equipped with the convenience and freedom of recovering tables, views, triggers, etc. Selectively as per the requirement is. This helpfully reduces the Recovery Time Objective for database recovery without compromising on Recovery Point Objective.

Database In Suspect Mode

An SQL Server database gets caught up into suspect mode in the extreme of cases due to; corruption or severe internal damage. SQL Server takes this step in order to maintain the consistency of transaction under the Relational Database Management System’s ACID property. But fortunately recovery from even this case of damage has been tried and tested with the SQL database recovery program with successful results.

NOTE: Unlike other recovery methods; while using SQL Recovery program, Server downtime won’t be faced as it is an independent application that is executable without SQL Server availability. Hence, you can continue working on the Server (with other databases) while the recovery is in progress.

The tool is capable to resolve most of the corruption issues that could possibly take place in an SQL database like: schema corruption, consistency corruption etc. Taking any scenario where DBCC CHECKDB command fails the SQL Recovery tool helps to recover the data maintaining integrity in intact form. Manual SQL database repair trouble can be resolved with the user friendly interface proposed by software for SQL Recovery.

NOTE: Free demo setup of the software is meant for trying out your hand on the tool before going to purchase its full version to gain confidence of performance perfection. However, it is limited to save only the STR file and show a preview of the entire scanned content of MDF/NDF files. To export and save recovered data, full version license is apparently required.

How Software Proceed to Resolve SQL Database Corruption?

Once the software is downloaded and installed in any of the Win OS versions, users can run it following the default path as:

Start>>All Programs>> SysTools® SQL Recovery Software>>SysTools® SQL Recovery Software

systools1 SQL SERVER   SysTools SQL Recovery Software   An Experiment to Recover Database Corruption

  • Select option to open MDF file from the system location it is saved at.
  • To load the damaged MDF file browse the location, select the file and then Click Open.

MDF and NDF file recovery is possible for that users need to customize the options:

  • Under tab for Scan Options, Select Auto detect SQL server file (. mdf) version to let tool, identify the file version or in case if you know the current version you can manually select the SQL Server version (make sure you select the correct version of SQL Server because wrong selection can result in inconsistent results.)

systools2 SQL SERVER   SysTools SQL Recovery Software   An Experiment to Recover Database Corruption

  • Under tab for NDF Options, select “Let Me Choose SQL Server Secondary Database Files”, select options to Add files, Folder or Remove File respectively according to the requirement and then Click OK.

systools3 SQL SERVER   SysTools SQL Recovery Software   An Experiment to Recover Database Corruption

  • Browse, locate, select NDF file and Click Open.
  • Later, the process report of automated MDF file scanning can be seen.
  • To save time and effort of scanning large MDF files, user can save the scan as STR files. If want to get .str files, click Yes, otherwise go for NO and proceed to the next step.
  • To save the STR file, select a desired location for its storage. Click Save.
  • Preview tables, stored procedures, triggers, rules, etc in the preview screen for MDF/NDF files.
  • Select Export option to save recovered database items from MDF and NDF files.

To save the SQL database files safely in healthy format, customize the options:

To export and save data as MDF file:

  • To export recovered data as MDF format go for “export as SQL Server database”.
  • Select “Create Destination Database”.
  • For SQL Server authentication, fill in the database credentials i.e. server name, database name, user name, password.
  • Select export feature as “with only schema” or “with schema and data”.
  • Click Export/Save option.

To export and save data as SQL Server compatible SQL scripts:

  • Check and select the desired component of the SQL database file that you want to be exported.
  • “With only Schema” or “with Schema and data” you can export SQL database.
  • Click Export/Save option to export recovered data as SQL Server compatible SQL scripts.
  • Browse location to save the output SQL file and click OK.
  • Get progress report of SQL scripts saving process.

Click OK to complete the Export process.

Secondary Measure For SQL Database Recovery

In case the time taken for recovering corrupt SQL database doesn’t suit you and a healthy LOG file for the same MDF is available then; try SysTools SQL Log Analyzer. The application helps analyze transactions performed on a database and recover for which it employs the usage of the corresponding LOG file. The combination of a log analysis and database recovery program helps in reducing the Recovery Time Objective to a lower extent as compared to that of the SQL Recovery program.

systools5 SQL SERVER   SysTools SQL Recovery Software   An Experiment to Recover Database Corruption

Have a look at its processing technique:

  1. Log file along with the corresponding MDF file is required for recovering the database through thorough analysis of its transaction.

systools6 SQL SERVER   SysTools SQL Recovery Software   An Experiment to Recover Database Corruption

  1. The software automatically loads the corresponding SQL database for the LDF file you browse for, in case both the files are available in a same folder. Also, you can browse and add the associated MDF file manually in case of its availability in a different location.

systools7 SQL SERVER   SysTools SQL Recovery Software   An Experiment to Recover Database Corruption

  1. Transaction details of the selected Log file are listed soon after the scan is completed, letting you preview all the operations performed on the associated database.
  2. Meanwhile, for exporting database, you are provisioned with three respective options:
    1. SQL Server Database (If SQL Server is available)
    2. SQL Server compatible SQL Scripts (Unavailability of Server)
    3. CSV (For transaction log details)

systools8 SQL SERVER   SysTools SQL Recovery Software   An Experiment to Recover Database Corruption

End Observation And Conclusion: With the widespread usage of SQL Servers worldwide, especially by large scale organizations makes downtime of the Server due to damaged database unaffordable. Meanwhile, SQL Recovery and SQL Log Analyzer both are predominantly dependable for resolving cases of MDF / NDF file corruption. A wise selection is all what it takes to overcome the trouble caused by an inaccessible SQL Server database.

NOTE: This was my personal experiment with database corruption. Always make sure that you do experiments on the copy of your database on your development server, rather than on production server. Once verified that your database is 100% recovered, you should experiment on your database. This blog takes no responsibilities whatsoever.

Reference: Pinal Dave (http://blog.sqlauthority.com)