Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
marco gwiliani
Starting Member
25 Posts |
 Posted - 2008-03-26 : 16:15:21
|
| I am using sql server 2005 express. I have a .bak file of my database containing no data. I restore the database and then use DTS to import some readonly data to a filegroup. At this point I want to make another backup of the database which I will then use to deploy to my website. The problem is that the .ldf file is 800MB as my import is pretty big. I need to somehow truncate the log file before completing the final full backup which I will use as a template for future deployment to website. How can I truncate the log? I don't need to keep a backup of the transcation log at this stage because it is just a bulk insert which I could repeat is necessary. Thanks MArco |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-03-26 : 16:17:18
|
| have a look at dbcc shrinkfile==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-03-26 : 16:58:59
|
| Explain clearly .Really confusing |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-03-26 : 21:34:14
|
| You will need the logical filename for the log file.select * from mydb..sysfilesnow the shrinkdbcc shrinkfile (filename)If that doesn't work it's probably because the active virtual file is at the end but give it a go.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
marco gwiliani
Starting Member
25 Posts |
Posted - 2008-03-28 : 04:06:46
|
| Hello - dbcc shrinkfile did reduce the log in size however its still to big. What I need to do is not log the bulk import transactions at all. I tested switching the recovery model to bulk logged to complete the bulk import using DTS - my understanding was that this would bypass the transaction log completely, however it still saved transactions to the log as though in full recovery model. Any ideas why this would be?Thanks MArco |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-03-28 : 08:33:10
|
| You need to remove indexes from the table and also make sure yo are doing a fast bulk insert in dts (think there is an option for that might be logged or something else).You do need to have the database recovery model as simple or bulk logged.You will still have times that the log grows though so you will need to learn how to deal with that.Have a look athttp://www.nigelrivett.net/SQLAdmin/TransactionLogFileGrows_1.html==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
marco gwiliani
Starting Member
25 Posts |
Posted - 2008-03-28 : 08:49:42
|
| Hello - there are no indexes on the table other than the PK default clustered index. I have tried importing with the DB set to simple and bulk logged. I am using sql 2005 express with the SQL Server Import and Export Wizard but cannot find an option to specify fast bulk upload which would bypass the transcation log. I will keep investigating and ofcourse read your link. Thanks MArco |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-03-28 : 08:53:30
|
| >> PK default clustered indexWell that's an index and it will prevent a non-logged load.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
marco gwiliani
Starting Member
25 Posts |
Posted - 2008-03-28 : 10:10:44
|
| Hello - thats sorted it! I deleted table relationships and primary key, then deleted index and did the import and then readded primary key and relatioships. Many ThanksMArco |
|
|
|
|
|
|
Bulk insert and the transacation log