Ygthkb

Are all power cords made equal?

Have any astronauts or cosmonauts died in space?

Do these large-scale, human power-plant-tending robots from the Matrix movies have a name, in-universe or out?

Why would you use 2 alternate layout buttons instead of 1, when only one can be selected at once

Multiple null checks in Java 8

What does @ mean in a hostname in DNS configuration?

Is the percentage symbol a constant?

Why can all solutions to the simple harmonic motion equation be written in terms of sines and cosines?

PostGIS function to move a polygon to centre over new point coordinates

Question: "Are you hungry?" Answer: "I feel like eating."

UK visa start date and Flight Depature Time

How can I persuade an unwilling soul to become willing?

Excluding or including by awk

Can I legally make a website about boycotting a certain company?

What's the function of the word "ли" in the following contexts?

Are there any rules for handling distractions whilst performing skill checks?

Can a planet be tidally unlocked?

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

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

Can a Hydra make multiple opportunity attacks at once?

Cryptic cross... with words

How do I add a strong "onion flavor" to the biryani (in restaurant style)?

Have the UK Conservatives lost the working majority and if so, what does this mean?

Coworker asking me to not bring cakes due to self control issue. What should I do?

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