What does Keyword STATS Indicates in Backup Scripts in SQL Server? – Interview Question of the Week #138

September 3, 2017

Question: What does Keyword STATS Indicates in Backup Scripts in SQL Server?

Answer: This is a very interesting question. I have seen many DBA using the backup scripts and using the keywords STATS but when I ask them if they really know why they are using the keyword STATS, most of the time they have no clue with that.

What does Keyword STATS Indicates in Backup Scripts in SQL Server? - Interview Question of the Week #138 stat0

Let us first see the backup scripts in SQL Server.

BACKUP DATABASE [AdventureWorks2014] TO
DISK = N'D:\AW1.bak' WITH FORMAT,
STATS = 10
GO

Now the question is what does the keyword STATS do and how it is used.

Well, here is the answer – Stats displays a message of percentage complete when the backup is going on. The default value of the stats is 10 and hence if we do not specify the stats, keywords, SQL Server displays the percentage completion at every 10 percentage.

To further illustrate what I mean by this, let us see a few screenshots where I have changed the STATS value to different values.

What does Keyword STATS Indicates in Backup Scripts in SQL Server? - Interview Question of the Week #138 stat1

What does Keyword STATS Indicates in Backup Scripts in SQL Server? - Interview Question of the Week #138 stat2

What does Keyword STATS Indicates in Backup Scripts in SQL Server? - Interview Question of the Week #138 stat3

What does Keyword STATS Indicates in Backup Scripts in SQL Server? - Interview Question of the Week #138 stat4

I hope from these examples it is very clear to you that STATS displays the progress of the backup complete in SQL Server. For large databases, I prefer to keep the value of the STATS to a much lower value so we can see much finer progress in the message window.

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

SQL Backup, SQL Scripts, SQL Server

What is the Biggest Limitation of ISDATE() Function? – Interview Question of the Week #137

How to Insert Line Break in SQL Server String? – Interview Question of the Week #139

1 Comment. Leave new

Shahu
December 11, 2017 11:44 am
Hi Pinal,
Above you mentioned,”The default value of the stats is 10 and hence if we do not specify the stats, keywords, SQL Server displays the percentage completion at every 10 percentage.”
Is there any configuration which DBA needs to change to set the default value of stats in SQL server?
I never seen the percent complete value if we wont specify the stats in the backup query. It needs to specify at the time of backup other wise backup script will give an output as below.
Script :
backup database DWConfiguration
to disk =’D:\Backup\abc.bak’
O/P:
Processed 528 pages for database ‘DWConfiguration’, file ‘DWConfiguration’ on file 2.
Processed 2 pages for database ‘DWConfiguration’, file ‘DWConfiguration_log’ on file 2.
BACKUP DATABASE successfully processed 530 pages in 0.172 seconds (24.073 MB/sec).
there is no percent complete in the result set.
Reply