August 21, 2003 at 1:17 pm
Urgent help.
I have a database in SQL 7.0,with datafile 750MB and log file 250MB. The log file grow to 4 GB overnight. How can I reduse the size back to 250MB. I Have use dbcc shrinkfile command on this mant times with no success. What else can I do?
August 21, 2003 at 1:24 pm
It is what you need. http://support.microsoft.com/default.aspx?scid=kb;en-us;256650
August 21, 2003 at 1:59 pm
All good info at that link but I'll stick to my tried and true...
If you can shut down the database I've always found that the quickest solution is immediately after a full backup to use sp_detach_db, delete the physical log file and then use sp_attach_db.
EXEC sp_detach_db '<DB_Name>', 'true'
EXEC sp_attach_db @dbname = N'<DB_Name>',
@filename1 = N'<Path_T0_Physical_Data_File>'
By attaching only the data file a blank log file of the minimum size is created. This method is very fast whereas at least some of the other methods used will have a very noticable effect on db server performance.
In my experience I could shut the DB down, detach and attach in under 30 seconds. This went over better with my users than giving slow performance for some indeterminate period of time.
Then too, you really need to find out why that tran log grows so big. This is a good bandaid solution though.
"I met Larry Niven at ConClave 27...AND I fixed his computer. How cool is that?"
(Memoirs of a geek)
August 22, 2003 at 3:05 am
quote:
I Have use dbcc shrinkfile command on this mant times with no success. What else can I do?
The following tip work fine for sql2000 but I don't know for sql7.
Before the call to shrinkfile,
you should set the log file in a specific state using sp_dboption.
Can you tell me if it's work on sql7 ?
/*
shrink a log file
WARNING: change the database name and the logical name of the log file.
database: MyDB
log file: MyDB_Log
*/
USE master
go
EXEC sp_dboption 'MyDB', 'trunc. log on chkpt.', 'TRUE'
USE MyDB
go
DBCC SHRINKFILE ('MyDB_Log', TRUNCATEONLY )
USE master
go
EXEC sp_dboption 'MyDB', 'trunc. log on chkpt.', 'FALSE'
USE MyDB
August 22, 2003 at 3:14 am
quote:
If you can shut down the database I've always found that the quickest solution is immediately after a full backup to use sp_detach_db, delete the physical log file and then use sp_attach_db.EXEC sp_detach_db '<DB_Name>', 'true'
EXEC sp_attach_db @dbname = N'<DB_Name>',
@filename1 = N'<Path_T0_Physical_Data_File>'
By attaching only the data file a blank log file of the minimum size is created. This method is very fast whereas at least some of the other methods used will have a very noticable effect on db server performance.
In my experience I could shut the DB down, detach and attach in under 30 seconds. This went over better with my users than giving slow performance for some indeterminate period of time.
I feel the command should be sp_attach_single_file_db.
Pay Respect to People on your way up. For you will meet the same People on your way down.
He who knows others is learned but the wise one is one who knows himself.
August 22, 2003 at 8:28 am
RE: sp_attach_single_file_db
It actually works either way.
According to BOL the only difference is that sp_attach_single_file_db automatically does some cleanup with replication. This only matters if you are doing replication and are switching to a different server.
"I met Larry Niven at ConClave 27...AND I fixed his computer. How cool is that?"
(Memoirs of a geek)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply