All server trigger creates virtual paging file?

Question

Post reply

All server trigger creates virtual paging file?

Indianrock

SSC-Insane

Points: 20333
More actions
January 10, 2013 at 3:24 pm

#287702

I was testing an all server trigger that would fire and email me if a new database is created. For production, this might mean firing 3 times a year. Someone has a concern about a hidden paging file consuming disk space -- any details on that?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736455 More actions · Answer 1

hidden paging file? From SQL Server? Never heard of that at all.

Indianrock SSC-Insane Points: 20333 More actions · Answer 2

I've been googling for an hour and really can't find anything. The concern expressed to me was that "all server" triggers create a virtual paging file that can fill up a disk. I can imagine performance impacts from triggers firing on every login or every insert, but this is going to be a rare event.

I may just have to set up a sql agent job that looks for a database name not in a list and alerts us if a new one is created.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 3

Only thing I can think of is that this person is thinking of the way DML triggers used to work in SQL 2000, where they had to read what happened in the transaction log to build the virtual INSERTED and DELETED tables. It would be stretching reality (DDL triggers did not exist in SQL 2000, DML triggers no longer read the transaction log and DDL triggers never did read the tran log) to use any of that information to block you from implementing a DDL trigger.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Indianrock (1/10/2013)
The concern expressed to me was that "all server" triggers create a virtual paging file that can fill up a disk.

Triggers don't create any form of file, virtual or otherwise. Unless of course, there's some code in the trigger that goes off to create a file, but that would be a really bad idea.

I may just have to set up a sql agent job that looks for a database name not in a list and alerts us if a new one is created.

I'd use a simple DDL trigger for that, fires off a mail using the new DBMail (which is async, so none of the old concerns about waiting for external processes) and be done.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Indianrock SSC-Insane Points: 20333 More actions · Answer 5

Well since its my new manager who has the concern, I may just set up a daily sql server agent job that looks for new databases ( in sys.databases ), other than tempdb, that were created over the past few days. I can put in the email "plumbing" to notify us. I had already created a DDL trigger to do this on one of our test servers, but then was told to abandon the idea.

Historically our dev team has created new databases ( first in QA, then in production) without telling anybody which leads to no backups, maintenance or replication to disaster recovery site.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Ask him for a reference or article on this 'virtual file' so that you can learn more about it, seeing as you'd never heard of such a thing. Or straight out explain to him that there's no such thing.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

lptech Hall of Fame Points: 3208 More actions · Answer 7

Wonder if said manager comes from another DBMS. I once had to prove all sorts of things in SQL Server were not the same as in Oracle.

Lowell SSC Guru Points: 323518 More actions · Answer 8

maybe he's confusing a virtual page with a server side trace and the possibility of a log file that has no limitations on the size or number of files , so potentially eventually you run out of disk space ?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Lowell (1/11/2013)
maybe he's confusing a virtual page with a server side trace and the possibility of a log file that has no limitations on the size or number of files , so potentially eventually you run out of disk space ?

Or maybe he's thinking of the temp file that SSMS creates to hold large query resultsets. That's a client-side thing only though, unrelated to triggers.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Indianrock SSC-Insane Points: 20333 More actions · Answer 10

I think it must have been some experience with DML triggers, not a DDL trigger that would rarely fire. I'm developing something in Tsql/agent job for now. Even though there is apparently a general company policy against using triggers, we have some DML triggers in one production database developed by a consulting firm ( sharepoint type system ), and a few DMLs will be installed as we put Safenet encryption into place.

thanks for all the feedback

Randy