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 |
|
smbates
Starting Member
3 Posts |
 Posted - 2003-10-24 : 13:45:11
|
| I am running a SQL2000 Server. I have a programmer that recently wrote a stored procedure that inserted hundreds of rows 1,000 of times. The end result of this was the growth of my log to 3GBs for this specific database..... With SQL a log or DB file can never be decreased in size, only increased. I want to resize this file to 100mb but can't figure out how to do that.... I have read that I can simply delete the log file and it will regenerate to the original size that is was created as. Is this correct or is there another way I can do this? Any help or advice would be appreciated Thanks!!!! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-10-24 : 13:46:56
|
| You can run DBCC SHRINKFILE to reduce the size if there is free space in the LDF file. You also detach your database using sp_detach_db, then delete the LDF, then attach your database using sp_attach_single_file_db.Is there free space in your transaction log? If not, then what is your database recovery model set to? If FULL, are you performing regular transaction log backups like at least every hour or so?Tara |
 |
|
|
smbates
Starting Member
3 Posts |
Posted - 2003-10-24 : 14:01:55
|
| I have run the shrinkfile command which decreases the size minimally (It was grown to 3.2BG and is now down to 3GB....) The file contains on average about 60mb of info and I would like it to actually only be about 100MB. The command you recommended (sp_detach_db, then delete the LDF, then attach your database using sp_attach_single_file_db). Will this delete the 3GB file and then recreate a new one with the sp_attach_single_file_db ? thanks!!!! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-10-24 : 15:05:14
|
| You have to delete the LDF file after you run sp_detach_db. Then run sp_attach_single_file_db and it will create a new very small one for you.Tara |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-10-24 : 16:04:53
|
| As always make sure you have a backup before you try this.==========================================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. |
|
|
|
|
|
|
decreasing the size of a log file........