Showing posts with label sql server. Show all posts
Showing posts with label sql server. Show all posts

Thursday, 25 January 2018

The '-K ' Startup parameter in SQL Server

Last week i created my first  SQL Instance in Amazon RDS,  Its pretty simple to  create an instance  and like any other Managed database service it does offers automated backups ,patching,monitoring etc and its easy to scale up as per your needs.

As Sql Error log is DBA's great friend i thought of  going through the error log and it always used to give me some great information and something interesting stroked me, the startup parameter they have used...........

2018-01-24 15:55:47.620 Server       Registry startup parameters: 
-d D:\RDSDBDATA\DATA\master.mdf
-e D:\RDSDBDATA\Log\ERROR
-l D:\RDSDBDATA\DATA\mastlog.ldf
-k 20.000000
-T 3226
-T 7806

yes its says about the master db file,error log location , Trace flag 3226 used to suppress logging of successful backup in errorlog , Trace Flag 7806 which is used to enable DAC on SQL Express edition(i created a express instacen). So whats -K option ??? i checked in  Microsoft docs on Database Engine Service Startup Options but didn't get any information and i didn't  get any usefully information from worlds favorite search engine too 😓 or i didn't user the correct key words to search  😋

After a long time searching got  details on '-K'  from the Checkpoint documentation.  It is used to throttle I/O  behavior based on the throughput of the I/O subsystem for some types of checkpoints.

In some cases, checkpoints might issue a substantial amount of I/O, causing the I/O subsystem to get flood with write requests, which can severely affect read performance.SQL Server includes a commandline option that allows throttling of checkpoint I/O by specifying  –k parameter, followed by a decimal number, which can be added to the list of startup parameters for the SQL Server service. The value specified indicates the number of megabytes per second that the checkpoint process can write. By using this –k option, the I/O overhead of checkpoints can be spread out and have a more measured effect.By default, the checkpoint process makes sure that SQL Server can recover databases within the recovery interval that you specify.So by using -k option may cause longer recovery time based on the specified value.The –k option doesn’t apply to indirect checkpoints. So in this case checkpoint I/O  is throttled to 20 megabytes per second

I have discussed this with a few DBA's on this option and no one had used this option or came across a scenario where they have observed checkpoint was causing I/O issues. If you guys have come across such a scenario please do share them in comments 

Sunday, 1 November 2015

Reading error log with xp_readerrorlog


Most of us are used with reading error logs through GUI , but when we are really in looking for something in error logs TSQL may seems more use full , so lets look how to analyse log file using xp_readerrorlog

EXEC xp_readerrorlog a,b,c,d,e,f,g

a- Value of log file you want to read ,0 for current, 1 for Archived 1,2 for Archived 2 etc
b-We can chose between error log and agent log here
      1- for error log [default]
      2- for agent log
c-search string which you want to look in log
d-we can give one more string , this can refine the output we got by giving the search string C
e- Here we can from which time's log we want to search
f- Here we can give the to which time's log we want to search
g- Here we can specify whether we want output in ascending or descending order by specifying 'ASC'and 'DESC'

Lets have a look at few examples


Here this will query in Archive #1 error log and will search for logs with word "log" and which are logged after 2015-10-13 and will list the output in ascending order


In the above example we are querying in Archive#2 Agent log , it will search for logs containing both the search strings "error" and "network" which are logged between 2015-09-20 and 2015-09-24 and will list the output in descending order 






Saturday, 10 October 2015

Cycling SQL Server error log







It is a good practice to cycle sql server error log as also it is easy to handle error logs of small size , By default sql server will only cycle error logs once its starts , so for easy managing of error logs its better to cycle error logs , in most of the productions system we create job to cycle error logs  that runs daily /weekly/ monthly depends on the volume of contents that will be written to the error logs. Once the error log cycle the current error log is renamed as errorlog.1 ,

We can use stored procedure   ' sp_cycle_errorlog' to cycle error log , members of sysadmin fixed roles can trigger this procedure , so by using the below T-sql we can cycle the error log 

exec sp_cycle_errorlog
go

By creating a job with above query we can automate this task 

By default sql server retains current and 5 recent error logs , this can be changed in SSMS by expanding Management\Sql Server log [right clich ] -> configure select Limit The Number Of Error Logs Before They Are Cycled, and select the maximum number of error logs, as shown in  Figure , Minimum limit is 6 and maximum is 99