dbcc sqlper(logspace) - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

dbcc sqlper(logspace)

Author

Topic

brinster
Starting Member

8 Posts

Posted - 2005-08-03 : 10:50:40

Hello

When i make this instruction ;I see huge log database (log 12 G)
(log space used%)=99%
How do you do to empty this logfile and after shrink logfile
without disturb production environement

Thank you
Regards

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2005-08-03 : 12:37:38

Sounds like you're not backing up your db.

Daniel
SQL Server DBA
www.dallasteam.com

Kristen
Test

22859 Posts

Posted - 2005-08-03 : 15:24:33

Hi brinster,


USE MyDatabase
GO
BACKUP LOG MyDatabase WITH TRUNCATE_ONLY
GO
DBCC SHRINKFILE (MyDatabase_Log, 1)
GO

Note that the logical name of your LOG may not be as shown.

Make a full backup immediately after this to avoid data loss

There is a small chance that there is a transaction at the end of your LOG file that is still "in use" and will not clear, and thus the LOG File will not shrink. If that happens there is a more convoluted process that cann be used to get rid of it.

Kristen

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-03 : 15:29:12

If you don't plan on backing up your transaction log since you don't require point in time recovery, then change your recovery model to SIMPLE to avoid this in the future. If are doing this, then perhaps you need to take a look at how often you are doing it. We backup ours every 15 minutes.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources