Log Question

[simian101]

I have a Log that has grown to 19 gig. It is set to 19136 MB initial size. Autogroth by 10 percent, unrestricted growth.

I am not sure how to handle it. I assume that it does not need to be that large. I am backing it up nightly.

Should I delete it and create a new smaller log size?

Since the initial size is set set high it won't shrink, right?

Thanks

Simi

 

[mrdenny]

When you back it up, do you backup the database, or the log specifically?

Is your database in full recovery or simple recovery?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[simian101]

I have been backing up both specifically.

I think it is set to full. But I can't find the command to verify that in SQL Server 2005.

In 2000 it is
SELECT dbpropertyex("database", "recovery")

Thanks

Simi

 

[mrdenny]

sp_helpdb will tell you the Recovery of all the databases. It's in the Status column, pretty much in the middle of the column.

If you have been doing log backups using the BACKUP LOG command and your log file has still grown out to that size, then it needs to be that size for a reason.

You can try shrinking it with the DBCC SHRINKFILE command and see if it shrinks, but if it was created at 19 Gigs (which is a very strange size to create a file at) you won't be able to go any smaller.

If you don't need to be able to do point in time restores then you can change the database to simple recovery and see if you can then shrink the file.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[simian101]

It appears to have worked.

Thanks

Simi