Transaction log Information gathering

Here are the different ways to know the tlog file related information

sys.dm_db_log_stats – Provide summary of tlog information

SELECT * FROM sys.dm_db_log_stats(db_id())

sys.dm_db_log_info -DMV function for VLF information same as dbcc loginfo which is non documented by Microsoft.

SELECT * FROM sys.dm_db_log_info ( db_id())

sys.master_files – Detail information for all the database – database files (data/log). with related information

replacement of sysaltfiles(old)

select * from sys.master_files where database_id=db_id() and file_id=2

sys.sysfiles – One row for each database file for current database.

SELECT * FROM sys.sysfiles WHERE fileid=2

dbcc sqlperf() – old and good dbcc to see the log size and used space for all databases.

also used to clear the stats

dbcc sqlperf(logspace)

sys.dm_db_log_space_usage – only size related information of tlog

select * from sys.dm_db_log_space_usage

 

this helps me a lot, hth to someone.

 

 

Posted in Others, Transaction Log | Tagged | Leave a comment

Azure Data Studeo- Nov 2019 Release

SQL Server Management studio is now can be installed separately not part of SQL Server package.

with this enhancement SSMS has several features and version is getting updated and new versions getting release which we can install separately.

in addition to this SQL Server is available for Azure and with Linux and machine learning enhancement it has to go beyond SSMS to use multiple languages and features which cannot be accommodated in SSMS, SSMS is a great tool for In premise system but for others recently Microsoft has introduced a tool called “Azure Data Studio” it will has almost all the feature SSMS is having with addition to that it can have

“Notebook” like Jupyter

can include multiple Languages: Python, R Powershell integrated, also allow to include more as needed..

and SSMS query intellisense with wonderful chart feature

Highly recommended for Future SQL Server DBA’s

**November 2019 version is released last week.

Reference:

SQLTiger Team Video

 

 

 

 

 

 

 

 

Posted in Azure, Future DBA, Others, SQL Server 2017, Tool | Tagged , , , | Leave a comment

AG without WSFC SQL 2017 and more…

Starting Sql Server 2017 Microsoft can allow you to enable the configuration manager for “Always ON” feature for standalone system without WSFC and linux without Pacemaker .

it has a limitation as :

  • It cannot have listener configuration
  • This would only be use as read replica
  • This secondary replica can not be auto failover

 

This is important as SQL Server has limitation of replica with version

SQL Server 2017 (9):

1 Primary +2 Sync Secondary Replica +6Async Secondary Replica

SQL Server 2019(9):

1 Primary+4Sync Secondary Replica +4Async Secondary replica

this is the reason for read only replication there is another concept called “Distributed AG” where 2 AG can be merge and can have more secondary read-only replica.

Reference

SQL Tiger Webcast

 

 

 

Posted in Disaster Recovery, High Avaliability, Others, SQL Server 2017, SQL Server 2019, Webcast, What I learned today, Whats New | Tagged , , , , , | Leave a comment

MAXDOP and Tempdb – SQL 2016

Starting SQL Server 2016 Max Degree of Parallellism (Max DOP) Macrosoft has made hanges on this and now we can set it at database level.

This helps for replication and AlwaysOn for better performance.

ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 8;

also from SQL Server 2016 on Tempdb trace flag 1117 and 1118 by default on.

1117 to grow the tempdb auto growth grow equally

1118 to off Mixed page allocation

*default is for only Tempdb not for other user databases.

reference:

SQL Tiger

http://www.sqlservice.se/sql-server-2016-maxdop-per-database/

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-degree-of-parallelism-server-configuration-option?view=sql-server-ver15

Posted in Others, Performance Tuning, sql 2016, What I learned today, Whats New | Tagged , , | Leave a comment

SQL Server 2019 GA

Yes, Finally SQL Server 2019 is Generally Available yesterday Nov 4th 2019.

It has many great features

great videos:

https://channel9.msdn.com/Niners/dutchdatadude

 

https://cloudblogs.microsoft.com/sqlserver/2019/11/04/gain-intelligence-over-data-with-sql-server-2019-now-generally-available/

https://cloudblogs.microsoft.com/sqlserver/2019/11/04/sql-server-2019-is-now-generally-available/

 

 

Posted in Others, SQL Server 2019, Webcast, What I learned today, Whats New | Tagged , , , , | Leave a comment

Smart Tlog Bkp

another great new feature for SQL Server 2017 is a smart backup with dmv sys.dm_db_log_space_usage this DMV provide log usage information using this we can plan to initiate the tlog backup and ensure to have tlog backup consistency and same size.

great article at mssqltips on this.

Validate the tlog stats

select * from sys.dm_db_log_stats(db_id())
select * from sys.dm_db_log_space_usage;

declare @growth int
declare @tlogused float
declare @lasttbkp datetime

—last backup time
select @lasttbkp=log_backup_time from sys.dm_db_log_stats(db_id())
—tlog used percentage
select @tlogused=used_log_space_in_percent from sys.dm_db_log_space_usage;
—tlog growth restricted
select @growth=growth from sys.sysfiles where fileid=2

if (datediff(mi,@lasttbkp,getdate())>14 or (@tlogused>80))
Begin
backup log vin to disk =’C:\Program Files\Microsoft SQL Server\MSSQL15.VIN\MSSQL\Backup\vintlog.bkp’
End

above script will check the tlog backup time and percent full tlog accordingly initiate the tlog backup(smartly)

 

 

 

Posted in Backup and Restore, Others, SQL Server 2017, Transaction Log, Troubleshooting, Whats New | Tagged , , , | Leave a comment

Resumable online index rebuild

continue on whats new SQL Server 2017 their is a great feature call resumable online index, means online index now has an option to PAUSE and RESUME or ABORT

RESUMABLE = {ON | OF }

MAX_DURATION — can specify the time in minutes

SORT_IN_TEMPDB = ON — is not supported

the advantage of this is as follows:

  • During limited maintenance window if reindex job is running and critical activity from application team requires

ALTER INDEX PK__i__3BD01996E5300172
ON i REBUILD
WITH (ONLINE = ON, RESUMABLE = ON);
GO

 

other other session
ALTER INDEX PK__i__3BD01996E5300172 ON i PAUSE

SELECT total_execution_time, percent_complete, name,state_desc,last_pause_time,page_count
FROM sys.index_resumable_operations;

ALTER INDEX PK__i__3BD01996E5300172 ON i resume

this way we can pause the online rebuild index activity

Cool!!!

Reference:

https://www.mssqltips.com/sqlservertip/4987/sql-server-2017-resumable-online-index-rebuilds/

 

 

 

 

 

 

 

 

Posted in Others, Performance Tuning, SQL Server 2017, Whats New | Tagged , , | Leave a comment

Back to school

Hey, I realize that future we need to know Database system, No SQL and Big Data. with that as things are moving to machine learning and AI system which requires you to have your hands dirty on some programming language, I remember it is been around two decade I was working on programming language(My wife does it still tough 🙂 ). But I always found interest in coding using powershell and performance tuning.

Here I am back on Programming language as Python. I also learning R which i will write more about. actual interest is on AI, Machine Learning, Deep learning which requires Python and R.

SQL Server and other database system also integrate this language into database system so it is necessary for us to learn those.

Today I installed R and Python on my laptop so though of writing something about it for my future reference:).

Python can be installed with multiple vendors I installed using Python site.

Goto bottom of the that site

Files

you will get version/operating system and details.

install python per your system.

once you install you go to command prompt and type

c:\>python

it is upto the location where you have installed python (if PATH location is updated during installation it would be good)

python prompt would be

>>

now if you want to install jupyter notebook.

depending upon the type of python you installed pip or anacode it warries. i used python so i will be using pip using Jupyter site

goto script folder of python and run

pip install jupyterlab

once it is installed just type

c:\>jupyter notebook

it will start the jupyter nodebook in browser and process /Kernel would be running in the background (do not close it).

More to come…tea

Happy Learning!!

Reference:

Corey Schafer – has a very nice videos with nice explanation – highly recommend
 

*https://www.youtube.com/watch?v=HW29067qVWk

*https://www.youtube.com/watch?v=OV9WlTd9a2U

Posted in AI, Python | Leave a comment

Read-only URL removal

When I blog to configure Read only replica. it would require to provide read-only URL information, and provide the replica sequence.

but their is no easy way to remove the Read-only URL. we have to remove the AG group to do so.

But their is a work around – has a risk 

>> Remove secondary (B Server)

this will remove the URL for secondary.

once that is done, add /join that back (B Server )and later we have to move the primary to secondary and later Remove Secondary (earlier primary)

make B Server primary and A as secondary.

now do the same for A server remove and add/join will remove the Read-only URL.

not sure but so far their is no other easy way to remove it( we can stop having secondary connection)

please ping or i will update this blog if we can do this without this workaround.

https://dba.stackexchange.com/questions/190337/how-to-remove-read-only-routing-url

Posted in Disaster Recovery, High Avaliability, Others | Tagged , , | Leave a comment

Error 9002

This is very important error whenever you see this error it is clear that your database has “TLOG FILE FULL” and this could result into maintenance job  failed or you system not respond properly.

for update stats you get so many errors for 15650,1,2,3,4 saying updates not require those are not errors but raise error output for errors but actual error for job failure could be TLOG FULL so check for ERROR 9002 once you see that that is the actual problem.

 

Their are somany questions about those errors 15650, 15651, 15652, 15653, 15654 this are not errors actual error would be 9002.

 

Is SQL Agent manipulating the errors raised by SQL Server, before dumping them to the output file?

 

Posted in Error Log, Others, Transaction Log, Troubleshooting | Tagged , , | Leave a comment