November 3, 2009 at 11:02 pm
MS SQL 2005
I have a problem I need to restore a database without restoring the associated Log Files.
The reason for this is that the log files are massive (~80gig) and the server which this needs to be restored to has only 10gig free for logs. Also the log file is about 99% free for some reason.
The actual backup weights in at around 5gig and the size of database is around the 11gig.
I have enough room to recover the database and Full Text Indexes.
I cannot take the database offline to copy the .MDF file as its a production and every 5 minutes 1000s of row of new data is inputted.
Also the target Server can not talk to the production server, so I can do SQL INSERT INTO commands.
I've researched the internet and can't find any help.
November 3, 2009 at 11:08 pm
Hi,
how about shrinking the log file with dbcc_shrinkfile, since the log is 99% free?
November 3, 2009 at 11:10 pm
Will that effect the data in anyway or how ability to restore the database if it fails over?
November 3, 2009 at 11:16 pm
my bad,
the shrinkfile will not help for this.
it will reduce size if the log is full.
November 4, 2009 at 12:18 am
shanu.hoosen (11/3/2009)
the shrinkfile will not help for this.it will reduce size if the log is full.
shrinkfile will reduce the size if the log is NOT full. If the log is full, shrinkfile will do nothing as there's no free space to free to the OS.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 4, 2009 at 12:23 am
ra_leech (11/3/2009)
I have a problem I need to restore a database without restoring the associated Log Files..
Can't be done. Data and log files have to both be restored.
i would suggest you look at whether that large log file is necessary and, if it's not necessary for it to be that size, do a once-off shrink of the log file and then take a new backup.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 4, 2009 at 4:25 am
Why is the log file so big ? Are you doing regular transaction log backups ?
November 4, 2009 at 3:20 pm
If the log fille is 99% full, I agree with the suggestion of doing a shrinkfile. That should reduce the use size of the log file, taking out the whitespace in the file but leaving all of the data intact.
Joie Andrew
"Since 1982"
November 4, 2009 at 3:28 pm
Shrinking the log file has worked a treat, the log is around a gig now.
The database is just a high transaction database, we do log backups every hour but have never shrunk the database this is why the log is so big.
Thank you all for your input this has been a great help
November 5, 2009 at 9:35 am
I have related question - can you shrink the log file if it is 99% free while the database is in use by users? I am little reluctant to shrink files while users are on the system.
Thank You
Saeed
November 5, 2009 at 10:03 am
Yes
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply