Sfrgttk

What does @ mean in a hostname in DNS configuration?

How can I make my enemies feel real and make combat more engaging?

What happens if both players misunderstand the game state until it's too late?

How to store all ctor parameters in fields

Can someone explain the need for perturbation theory in QM?

Distribution of sum of independent exponentials with random number of summands

Checking if an integer permutation is cyclic in Java

Badly designed reimbursement form. What does that say about the company?

How bad is a Computer Science course that doesn't teach Design Patterns?

Do the speed limit reductions due to pollution also apply to electric cars in France?

80-bit collision resistence because of 80-bit x87 registers?

What does an unprocessed RAW file look like?

How to prep Curse of Strahd effectively

How can I give a Ranger advantage on a check due to Favored Enemy without spoiling the story for the player?

How does holding onto an active but un-used credit card affect your ability to get a loan?

What is an explicit bijection in combinatorics?

Integral problem. Unsure of the approach.

Was the Soviet N1 really capable of sending 9.6 GB/s of telemetry?

Coworker is trying to get me to sign his petition to run for office. How to decline politely?

Why don't programs completely uninstall (remove all their files) when I remove them?

Is Screenshot Time-tracking Common?

When distributing a Linux kernel driver as source code, what's the difference between Proprietary and GPL license?

Build ASCII Podiums

What if you do not believe in the project benefits?

Disk space full during insert, what happens?

System disk run out of space when running heavy SQL queries on SQL Server 2012Disk space full but logical space available in databaseconnection pooling, transactions, nested transaction and rollback'Tempdb' is full warning message in SQL Server 2005Prevent SQL Server publisher from running out of disk spaceSQL Server 2016 out of disk spaceHow do I fix Uneven TempDB Files?Index Reorganize During Database Full BackupWill DIFF backup size be reduced if we shrink the data files?Sort spills to tempdb but estimated rows equals to actual rows

0

Today I discovered the harddrive which stores my databases was full. This has happened before, usually the cause is quite evident. Usually there is a bad query, which causes huge spills to tempdb which grows till the disk is full. This time it was a bit less evident what happened, as tempdb wasn't the cause of the full drive, it was the database itself.

The facts:

• Usual database size is about 55 GB, it grew to 605 GB.


• Log file has normal size, data file is huge.


• Datafile has 85 % available space (I interpret this as 'air': space that was used, but has been freed. SQL Server reserves all space once allocated).


• Tempdb size is normal.

I have found the likely cause; there is one query which selects much too many rows (bad join causes selection of 11 billion rows where a couple of hundred thousand is expected). This is a SELECT INTO query, which made me wonder whether the following scenario could have happened:

• SELECT INTO is executed


• Target table is created


• Data is inserted as it is selected


• Disk fills up, causing the insert to fail


• SELECT INTO is aborted and rolled back


• Rollback frees up space (data already inserted is removed), but SQL Server doesn't release the freed up space.

In this situation, however, I wouldn't have expected the table created by the SELECT INTO to still exist, it should be dropped by the rollback. I tested this:

BEGIN TRANSACTION 

SELECT  T.x

INTO    TMP.test

FROM    (VALUES(1))T(x)



ROLLBACK



SELECT  * 

FROM    TMP.test

This results in:

(1 row affected)

Msg 208, Level 16, State 1, Line 8

Invalid object name 'TMP.test'.

Yet the target table does exist. The actual query wasn't executed in an explicit transaction though, can that explain the existence of the target table?

Are the assumptions I sketched here correct? Is this a likely scenario to have happened?

sql-server sql-server-2016 insert rollback

share

asked 4 mins ago

HoneyBadgerHoneyBadger

13915

New contributor

HoneyBadger is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

0

Today I discovered the harddrive which stores my databases was full. This has happened before, usually the cause is quite evident. Usually there is a bad query, which causes huge spills to tempdb which grows till the disk is full. This time it was a bit less evident what happened, as tempdb wasn't the cause of the full drive, it was the database itself.

The facts:

• Usual database size is about 55 GB, it grew to 605 GB.


• Log file has normal size, data file is huge.


• Datafile has 85 % available space (I interpret this as 'air': space that was used, but has been freed. SQL Server reserves all space once allocated).


• Tempdb size is normal.

I have found the likely cause; there is one query which selects much too many rows (bad join causes selection of 11 billion rows where a couple of hundred thousand is expected). This is a SELECT INTO query, which made me wonder whether the following scenario could have happened:

• SELECT INTO is executed


• Target table is created


• Data is inserted as it is selected


• Disk fills up, causing the insert to fail


• SELECT INTO is aborted and rolled back


• Rollback frees up space (data already inserted is removed), but SQL Server doesn't release the freed up space.

In this situation, however, I wouldn't have expected the table created by the SELECT INTO to still exist, it should be dropped by the rollback. I tested this:

BEGIN TRANSACTION 

SELECT  T.x

INTO    TMP.test

FROM    (VALUES(1))T(x)



ROLLBACK



SELECT  * 

FROM    TMP.test

This results in:

(1 row affected)

Msg 208, Level 16, State 1, Line 8

Invalid object name 'TMP.test'.

Yet the target table does exist. The actual query wasn't executed in an explicit transaction though, can that explain the existence of the target table?

Are the assumptions I sketched here correct? Is this a likely scenario to have happened?

sql-server sql-server-2016 insert rollback

share

asked 4 mins ago

HoneyBadgerHoneyBadger

13915

New contributor

HoneyBadger is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

Today I discovered the harddrive which stores my databases was full. This has happened before, usually the cause is quite evident. Usually there is a bad query, which causes huge spills to tempdb which grows till the disk is full. This time it was a bit less evident what happened, as tempdb wasn't the cause of the full drive, it was the database itself.

The facts:

• Usual database size is about 55 GB, it grew to 605 GB.


• Log file has normal size, data file is huge.


• Datafile has 85 % available space (I interpret this as 'air': space that was used, but has been freed. SQL Server reserves all space once allocated).


• Tempdb size is normal.

I have found the likely cause; there is one query which selects much too many rows (bad join causes selection of 11 billion rows where a couple of hundred thousand is expected). This is a SELECT INTO query, which made me wonder whether the following scenario could have happened:

• SELECT INTO is executed


• Target table is created


• Data is inserted as it is selected


• Disk fills up, causing the insert to fail


• SELECT INTO is aborted and rolled back


• Rollback frees up space (data already inserted is removed), but SQL Server doesn't release the freed up space.

In this situation, however, I wouldn't have expected the table created by the SELECT INTO to still exist, it should be dropped by the rollback. I tested this:

BEGIN TRANSACTION 

SELECT  T.x

INTO    TMP.test

FROM    (VALUES(1))T(x)



ROLLBACK



SELECT  * 

FROM    TMP.test

This results in:

(1 row affected)

Msg 208, Level 16, State 1, Line 8

Invalid object name 'TMP.test'.

Yet the target table does exist. The actual query wasn't executed in an explicit transaction though, can that explain the existence of the target table?

Are the assumptions I sketched here correct? Is this a likely scenario to have happened?

sql-server sql-server-2016 insert rollback

share

asked 4 mins ago

HoneyBadgerHoneyBadger

13915

New contributor

HoneyBadger is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

BEGIN TRANSACTION 

SELECT  T.x

INTO    TMP.test

FROM    (VALUES(1))T(x)



ROLLBACK



SELECT  * 

FROM    TMP.test

(1 row affected)

Msg 208, Level 16, State 1, Line 8

Invalid object name 'TMP.test'.

share

asked 4 mins ago

HoneyBadgerHoneyBadger

13915

New contributor

HoneyBadger is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share

asked 4 mins ago

HoneyBadgerHoneyBadger

13915

New contributor

HoneyBadger is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 4 mins ago

HoneyBadgerHoneyBadger

13915

New contributor

HoneyBadger is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 4 mins ago

HoneyBadgerHoneyBadger

13915