Shrink Log File 1

[MasterRacker]

I have a SQL 2005 database that has never had any maintenance performed on it. I'm trying to find vendor info on recommended DB settings, and so far I haven't come up with much.

The mdf is 170MB and the LDF is 71GB. Autoshrink is off and I don't think I want to turn it on due to fragmentation concerns.

I want to run DBCC SHRINKFILE against the log file. This KB:

http://support.microsoft.com/kb/907511

states I should back up the log first. Can that backup be run without putting the DB into single user mode? Also, the the shrinkfile command wants a target size and the KB article says that should be the size of at least 2 virtual log files. How do I determine how large my virtual logs are?

Also looking here:

http://msdn.microsoft.com/en-us/library/ms189493(SQL.90).aspx

I ran the free space query and it shows only 2GB free in the log file. I'm guessing I would get more free space by backing up the log file?



Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[hmckillop]

I think they mean by backing up the log something like this

BACKUP LOG myDB with TRUNCATE_ONLY
DBCC SHRINKFILE (myDB_log, 8)

to see what files to use,

use myDB
go
exec sp_helpfile

"I'm living so far beyond my income that we may almost be said to be living apart

 

[MasterRacker]

That's along the lines I was thinking. I know that if you do a full backup on a DB you need to kick all users out. Can the log backup run without breaking any user connections?

Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[hmckillop]

I am fairly sure it should be ok.

"I'm living so far beyond my income that we may almost be said to be living apart

 

[mutley1]

Jeff,

you don't need to kick users out to do a full backup on a DB. I'd look into the reason as to why your log file grew to that size in the first place.

1. Check the recovery model of the DB in question. If it is in FULL recovery mode, then you should run transaction log backups during the day (no issue to users) as that clears out data that has been written. If you don't it will grow and grow and grow........

2. Consider what level of recovery you need. If it is a production DB and hold important info, take a log backup every hour. This means that if it crashes, you can restore to a point in time by restoring the last full backup and any tran log backups post full DB backup. (Also check out differential backups in BOL).
If, however, the company can live with going back to the last full backup and nothing that has been done since, change the recovery model to 'simple' as it will clear the transaction log on checkpoint, which is extremely frequently.

It all depends on how much data you can afford to go without. Every company is different, but on our main prod databases, for example, I backup the full DB every night and take tran logs every hour during the day between 6am and 7pm (processing hours).

HTH,

M.

 

[MasterRacker]

I am looking at switching the recovery mode to simple. I've also found out the model DB is in full recovery mode. All the other system dbs are in simple mode. Looking at another sql server, it's model is also in simple mode.

I'm currently trying to figure out why the model db would be in full mode on this server.

Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[mutley1]

I believe model is created as full by default on installation. It's just a microsoft assumption (extremely legitimate) that when you use SQ it's going to be for robust data storage (not the best terminology, but haven't had my first coffee yet) that you don't want to lose, hence full recovery. Model is only used as the template when you create a new database, so if you add anythinig to it or change the recovery model, that will be applied to any new databases you create. I prefer to leave it in full and change the new DB to simple afterwards if recovery to a point in time other than last full backup is not a requirement.

Cheers,

M.

 

[MasterRacker]

Just to follow up:
The vendor told me their DBs are supposed to be in Simple recovery mode.

My corrective procedure was:
- Set recovery mode to simple
- perform the backup log with truncate_only
- right-click the DB in SSMS, choose Shrink > Files with Release unused Space selected.

Took under a minute to for the shrink and my log file dropped from 88GB to 1 MB.

Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[hmckillop]

Some difference in size, glad you got it sorted.


"I'm living so far beyond my income that we may almost be said to be living apart