Lukino ( XXXX@XXXXX.COM ) writes:
Did you look at sys.databases.log_reuse_wait?
Normally, when a database file does not have room for what has to go
into it, SQL Server expands the file according to its autogrow settings.
But if the settings calls for an increase for which there is no room
left for on the disk, the grow will fail. Likewise will file be reported
as full, if autogrow has been turned off.
Tempdb is often used by SQL Server for work tables, and other work areas,
such as disk spaces for sort operations that are too large to fit into
So your action is to find out how big is the log file of your tempdb
database, and what autogrow settings. If needed, see your local hardware
vendor to get more disk.
It may also help to replace UNION with UNION ALL, as UNION implies a
DISTINCT operation. which may be expensive.
Erland Sommarskog, SQL Server MVP, XXXX@XXXXX.COM
Links for SQL Server Books Online:
SQL 2008: http://www.yqcomputer.com/
SQL 2005: http://www.yqcomputer.com/
SQL 2000: http://www.yqcomputer.com/