Extremely large log files? - 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

General SQL Server Forums

New to SQL Server Programming

Extremely large log files?

Author

Topic

zerotasking
Starting Member

8 Posts

Posted - 2006-08-16 : 20:29:06

We have SQL Server running on a Windows 2003 server, only because Backup Exec requires it. AT the location : C:\Program Files\Microsoft SQL Server\MSSQL\Data
there is this file: SuperVISorNet_log.LDF which is 15 Gb and is accessed daily. I apologize because I don't know what this is!

My question is: can this file be 'pruned' (for want of a better word) because it's taking up a lot of backup space.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-16 : 20:38:03

You should read this topic:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=56434

You can refer to the SQL Server Books Online documentation for more detailed information. Here are links to it if you do not have it installed.

SQL Server 2000 Books Online
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/startsql/portal_7ap1.asp

SQL Server 2005 Books Online
http://msdn2.microsoft.com/en-us/library/ms130214.aspx

CODO ERGO SUM

zerotasking
Starting Member

8 Posts

Posted - 2006-08-16 : 20:58:30

Thanks very much for your help. I do have the books installed and I have a lot of reading to do! So the SQL Server Enterprise manager is the GUI MS make? I have used MySQL so I am aware of these. Is there a quick way to tell if the db is in Simple Recovery Model? I should add that I am <extremely> nervous about making any changes to this server without understanding what I am doing. I am comfortable with backing up the db as I've done that in MySQL and yes, I see your point about trusting Veritas. But this SQL server is only being used for Vweritas . . .

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-16 : 21:02:35

Connect to the server using Enterprise Manager. Navigate to the database that the file is referencing, probably is named SuperVISorNet. Right click, go to properties, then to options tab. If the recovery model isn't set to SIMPLE, then switch it. You will then need to the shrink the file to reclaim the disk space. We can walk you through that. Let us know when you get to that point.

Tara Kizer

zerotasking
Starting Member

8 Posts

Posted - 2006-08-16 : 21:14:42

Thanks Tara. Done that. I also backed up all the DB's to another drive first. Now I just need to shrink it . .

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-16 : 21:17:59

To shrink, connect to the server using Query Analyzer. Run this:

DBCC SHRINKFILE (SuperVISorNet_Log, TRUNCATEONLY)

The above command could take a bit to run.

Tara Kizer

zerotasking
Starting Member

8 Posts

Posted - 2006-08-16 : 21:23:35

I'm sorry - this is really dumb - how do I do that? I tried selecting Query Analyzer from the SQL Server Admin Tools Menu, but I got an error : "The Specified File Was Not Found" Can I do this from the prompt?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-16 : 21:27:53

To do it from a command prompt, type in osql.exe -S ServerNameGoesHere -E.

Then you should get 1>

Type in the command, hit enter. Then type in GO, hit enter. Once it completes, you can close the window.

Tara Kizer

zerotasking
Starting Member

8 Posts

Posted - 2006-08-16 : 21:38:41

oooh - so close - I got this error:

C:\Documents and Settings\administrator.HSHS>
C:\Documents and Settings\administrator.HSHS>osql.exe -S hshs.local -E
1> DBCC SHRINKFILE (SuperVISorNet_Log, TRUNCATEONLY)
2> GO
Msg 8985, Level 16, State 1, Server HSHS-BDC1, Line 1
Could not locate file 'SuperVISorNet_Log' in sysfiles.
DBCC execution completed. If DBCC printed error messages, contact your system
administrator.

Did I typo - or do I need to add .LDIF? Thanks for your patience Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-16 : 21:42:21

Try:

DBCC SHRINKFILE (SuperVISorNet, TRUNCATEONLY)

Tara Kizer

zerotasking
Starting Member

8 Posts

Posted - 2006-08-16 : 22:02:09

Same error. Is there a SHOW DATABASES; or something so I know I'm in the right place? I can see the database in: Console Root/Microsoft SQL Servers/SQL Server Group/(local)(Windows NT)/Databases/SupervISorNet.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-16 : 22:34:24

Run select * from sysfiles to see what logical name to use.

Tara Kizer

zerotasking
Starting Member

8 Posts

Posted - 2006-08-16 : 23:18:17

All fixed Tara. Maybe it worked through having Auto Shrinkfile ticked? Not Sure. I wasn't sure what you meant in your last post, but I ran exec sp_helpdb and noticed that the file had shrunk to 170 Meg. Thanks so much for your help.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-16 : 23:38:32

You're welcome.

You should actually turn off auto shrink. You never know when SQL Server is going to do it. It could happen during a very important process which could cause the process to fail. I always turn that option off. But yes it could have fixed it for you.

Tara Kizer

zerotasking
Starting Member

8 Posts

Posted - 2006-08-16 : 23:49:56

ok thanks I did that.

shailendra_saroj1
Starting Member

1 Post

Posted - 2008-01-07 : 04:24:08

We have SQL Server running on a Windows 2003 server and we open Sql Enterprises manager and go to Tool--->Sql querry analyser. this is showing error.."The specified file was not found."

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-01-07 : 22:07:22

Can you open query analyzer outside of em?

chapman.tim
Starting Member

1 Post

Posted - 2009-05-19 : 14:48:20

http://sqlservernation.com/blogs/admin/archive/2009/05/17/why-is-my-log-file-so-big.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources