Truncate log from database log file? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2005 Forums

Transact-SQL (2005)

Truncate log from database log file?

Author

Topic

getur.srikanth@gmail.com
Yak Posting Veteran

77 Posts

Posted - 2010-03-29 : 13:45:08

I am using below command for truncate log file, But I am getting error. I think because of Database file name is too big. How to solve this?

USE SharePoint_AdminContent_2a681a5b-2dd6-4e26-942e-32996f34937b;
GO
EXEC sp_helpfile;
GO

USE SharePoint_AdminContent_2a681a5b-2dd6-4e26-942e-32996f34937b
GO
DBCC SHRINKFILE(SharePoint_AdminContent_2a681a5b-2dd6-4e26-942e-32996f34937b_log, 1)
BACKUP LOG SharePoint_AdminContent_2a681a5b-2dd6-4e26-942e-32996f34937b WITH TRUNCATE_ONLY
DBCC SHRINKFILE(SharePoint_AdminContent_2a681a5b-2dd6-4e26-942e-32996f34937b_log, 1)
GO

Error

Msg 911, Level 16, State 1, Line 2
Could not locate entry in sysdatabases for database 'SharePoint_AdminContent_2a681a5b'. No entry found with that name. Make sure that the name is entered correctly.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '-'.
Warning: the floating point value '942e-32996' is too small. It will be interpreted as 0.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '-'.
Warning: the floating point value '942e-32996' is too small. It will be interpreted as 0.
Msg 319, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '-'.
Warning: the floating point value '942e-32996' is too small. It will be interpreted as 0.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-03-29 : 13:50:33

Try putting square brackets around the database name.

But why are you running this process? What recovery model are you using?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

getur.srikanth@gmail.com
Yak Posting Veteran

77 Posts

Posted - 2010-03-29 : 14:24:08

I just followed this blog [url]http://blog.sqlauthority.com/2006/12/30/sql-server-shrinking-truncate-log-file-log-full/[/url] to shrink db log file.

I don't have recovery model. Can you please share your knowledge to use Recovery model?

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-29 : 15:03:52

Be very wary of shrinking the TX log down to the minimum. Transaction log expansion incurs a rather large performance hit, because as the log is being expanded, it locks the entire database. (I think). Anyway, things come to a grinding halt when the log is being expanded. You should set the size of the TX to be the maximum size you expect to need inbetween TX log backups, plus some overhead. This will prevent it from being expanded except in exceptional circumstances.

Now onto the Square brackets. If you have unusual characters in a database name, table name, field name, etc, then you must enclose that database, table, field, etc in square brackets when referencing it. Consider the following:


DECLARE @tmp TABLE (
	field1 INT,
	field2 INT,
[field1-field2] INT
)

INSERT INTO @tmp
SELECT 4, 3, 2

SELECT field1-field2 FROM @tmp
SELECT [field1-field2] FROM @tmp

Notice how the 2 select statement return different values? The first one returns the column field1 minus the column field2. The second returns the column [field1-field2]. Without the square brackets, the - sign has a higher order or precedence than the field name, so it's applied first, and SQL server assumes you want to subtract those two columns. Using the square brackets, you tell SQL server the - sign is a part of the field name.

There are 10 types of people in the world, those that understand binary, and those that don't.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-29 : 15:07:21

What do you mean when you say you don't have a recovery model? All databases have a recovery model. Either Simple, Full, or Bulk Logged.

If you right click on the database in SSMS, select "Properties", and select "Options" on the Database Properties window, it will show you the recovery model of the database.

There are 10 types of people in the world, those that understand binary, and those that don't.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-03-29 : 16:08:53

Please read through this - [url]http://www.sqlservercentral.com/articles/64582/[/url]

--
Gail Shaw
SQL Server MVP

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-29 : 18:20:40

quote:
Originally posted by GilaMonster

Please read through this - [url]http://www.sqlservercentral.com/articles/64582/[/url]

Nice article Gail.

There are 10 types of people in the world, those that understand binary, and those that don't.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources