CODEWITHSUNDEEP
sql-server
Alexander
Alexander

Reputation: 20234

SQL Transaction Log best practices from a developer standpoint

I am developing a system that should be deployed into on customers' on-premise SQL Servers. The system should then running unattended for years at a time and has a high database throughput (many inserts/deletes), which is communicated to the customers. Some seem to have their database configured "correctly" with regular backups or log truncates, others just let the transaction log grow until the disk is full.

When the disk is full, obviously not only my application, but also other unrelated systems using DBs on the same disk stop working.

Now, one could say it's the customer's fault, but you can't tell them that if you want them to use your system.

So, what can I, as the developer, do to tackle this problem? Should the system monitor the log growth, and send an email when the log spirals out of control? Should/could I monitor the SQL server disk space?

Of course I have a boldened Attention paragraph in the 12-page setup documentation regarding Log file growth, but no one seems to fully read them these days.

Upvotes: 1

Views: 260

Answers (1)

TheGameiswar
TheGameiswar

Reputation: 28930

The system should then running unattended for years at a time and has a high database throughput (many inserts/deletes), which is communicated to the customers.

your database requires maintenance,at the least you should check for disk space,if you can't afford any maintenance,then SQLAZURE may be best option for you,but this also still requires DBA

So, what can I, as the developer, do to tackle this problem?

you should make them clear,its not what you support and this has to be dealt by them and ask them to hire a DBA

Log grows and it is inevitable for an OLTP System.if you are looking for best practices..below are few

1.Log files should be placed in seperate drive
2.Set Autogrowth in MB's not in percents

and check this link for more info on why this happens:How best to maintain SQL log file sizes

Should the system monitor the log growth, and send an email when the log spirals out of control? Should/could I monitor the SQL server disk space?

you should monitor log growth.you could use below to monitor and send an email to alert when free space percent is less than 10

create table #t
(
dbname sysname,
logsize float,
logspaceused float,
status bit
)
insert into #t
exec ('
dbcc sqlperf(''logspace'')')

you could also check mssql tips for advanced monitoring info: Monitor SQL Server Transaction Log File Free Space

Upvotes: 1

Related Questions