I did not know that SQL had a NUL: "disk device"

I just had to use this command to shrink a 350 GB log file!

Code:

---

BACKUP LOG  Acctfiles  TO DISK='NUL:'

DBCC SHRINKFILE (  Acctfiles_Log, 1)

---

NUL: - a null device. I feel like I'm back in my PDP/11 or VAX days!

It worked!

Ouch!
At least don't use it on a database.

https://dba.stackexchange.com/questi...ct-performance

Quote:

---

Originally Posted by szlamany

feel like I'm back in my PDP/11 or VAX days!

It worked!

---

NUL is valid DOS devics in Windows like COM1 and LPT1 and AUX. You cannot have files with these names too.

Backup to NUL is currently the only option available since TRUNCATE_ONLY option was dropped in sql2008.

Generally you never need to use it during normal operations as in FULL recovery mode you *have* to backup log or if you don’t plan to then just use SIMPLE recovery mode and be done with it. Using FULL but lazying on log backups is the only time backup to NUL might be needed but while rectifying laziness just switch to SIMPLE and spare the backup to NUL at all.

Quote:

---

Originally Posted by wqweto

NUL is valid DOS devics in Windows like COM1 and LPT1 and AUX. You cannot have files with these names too.

Backup to NUL is currently the only option available since TRUNCATE_ONLY option was dropped in sql2008.

Generally you never need to use it during normal operations as in FULL recovery mode you *have* to backup log or if you don’t plan to then just use SIMPLE recovery mode and be done with it. Using FULL but lazying on log backups is the only time backup to NUL might be needed but while rectifying laziness just switch to SIMPLE and spare the backup to NUL at all.

---

Not my server - not my IT department unfortunately...

My web app was denying logins - LOG disk on the server was full. I found an old 6GB backup of something - deleted it, just to allow logins - before an entire college of student and faculty could not connect!

Then I went and found the SQL LOG file - it was 350GB! No place to do a proper backup to then shrink - and NUL: rescued the moment.

I don't like telling IT departments how to backup data. It is ultimately their responsibility.

And here I am getting in the middle of it anyway and cleaning up their mess, lol!

Sorry but when consulting I always point out problems with the way a company is handling backups. Everything from not doing proper T-Log backups to relying on something like comvalt (pauses IO on the DB while doing its thing) or even just relying on SAN backup (coalescing same reason)

Only once I suggested a shrink . They gave me the evil eye and that was it.
We are backing up everything properly every night. I think despite our issues, the file, backup storage of the company is at a very good level....
It may help that I'm not currently in charge of doing this. I would just have rar-ed everything :D