Truncating 2

[ChrisPhillip]

I want to truncate a table called "dbo.tmp_journal

first time doing this and I am nervous does anyone have a decent understand able script to do this which will also let me see what is happening or can I just go ahead as this is just a TMP file?

 

[Olaf Doschke]

You better ask the original devloper/contractor/architect or vendor of this database.

Temp data typically is in the temp database, not in a database owner schema of a database with prefix tmp. This is not a SQL Server system table either. So it's mainly a question about the definition or meaning of this prefix in the database of your specific application, not a question SQL Server administrators or developers could answer you. One thing is right, tmp typically indicates temporary data. But as said the place for temp data rather is the temp database and so the meaning may not be very literal. I inherited some apps using "temp" Tables to store configuration/last choices, current project etc. between application sessions.

Now you need to get sure about whether you want to truncate the table or not. Depending on the recovery model of the database each operation is logged or not. You may remove all data, you may (kind of) only move it from mdf to ldf file, from core database file to transaction log. So if this is about freeing some hdd space, you better learn about the transaction log first. Until then I'd not truncate the table, having no concrete knowledge about what the app does with it and whether you really get back space or not. Even shrinking the database afterwards is not necessarily shrinking the log file.

Bye, Olaf.

 

[gmmastros]

also let me see what is happening"

Can you explain what you mean by this?

When truncating a table, there is not much to see. One second the data is there and another second it's gone.

You should also realize that not all tables are "truncate-able". If there are foreign keys, schema-bound views etc... on the table, then sql server will not allow you to truncate it. Instead, you would be forced to delete from it.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[yelworcm]

If you want to get rid of the table, but you are not sure if anything access the data in the table, you can rename the table and replace it with an empty copy, so any dependent queries, views*, stored procedures*, or functions* break when they are called. Let the system simmer for a week or two, then drop the table.

* After the relevant query plans have been flushed from cache.

 

[ChrisPhillip]

Thanks guys, I created renamed and created a blank all working fine.

 

[Olaf Doschke]

You may award yelworcm with a star for his suggestion instead of me, but congrats and thanks anyway.

Bye, Olaf.