problem with 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

SQL Server 2000 Forums

SQL Server Administration (2000)

problem with files

Author

Topic

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 2005-01-03 : 16:30:28

Hi all
please help.I have this file in this location and this file is growing.is this a logf file??what is the exact purpose of thsi file.and how can i regulate its growth.what is exactly this file.bcoz it does not have any extension.What is the use of this file.And what will be the contents of thsi file exactly.How safe will it be to remove this.If you can explain me all this in little detail.when i click on back up in management there are no devices.if i have the Db back up with me is it safe deleting this file?

Please help

Regards

D:\Program Files\Microsoft SQL Server\MSSQL\Data\backup_log_31_03"

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-03 : 16:44:31

see
http://www.mindsdoor.net/SQLAdmin/TransactionLogFileGrows_1.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2005-01-03 : 16:51:01

quote:
Originally posted by nitin1353

Hi all
please help.I have this file in this location and this file is growing.is this a logf file??what is the exact purpose of thsi file.and how can i regulate its growth.what is exactly this file.bcoz it does not have any extension.What is the use of this file.And what will be the contents of thsi file exactly.How safe will it be to remove this.If you can explain me all this in little detail.when i click on back up in management there are no devices.if i have the Db back up with me is it safe deleting this file?

Please help

Regards

D:\Program Files\Microsoft SQL Server\MSSQL\Data\backup_log_31_03"

I am assuming it is your logfile... but just to check do this:

If you have a database named "Backup" or something like that, open a session of Query Analyzer, switch to that database, and run this:

SELECT * from sysfiles

If the file name is listed as the log file then you cannot delete it (unless you want to remove the database). If you need this database then you may want to look into setting the db recovery model up as Simple. Then preform you database backups and you should be able to shrink the log.

Regards.

Daniel
SQL Server DBA

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 2005-01-03 : 16:54:44

hi
thanks for replying.but this file does not have any extension.i dnt think this can be log file coz otherwise the extension could be trn or log file extension

Regards

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2005-01-03 : 16:58:10

quote:
Originally posted by nitin1353

hi
thanks for replying.but this file does not have any extension.i dnt think this can be log file coz otherwise the extension could be trn or log file extension

Regards

Yeah thats why I was saying to check the sysfiles table. That way you will see the logfile name. SQL Server really doesnt care about file extentions. You can name your backup's ".junk" or ".BAK" or use no extention at all. The same goes for the data and log files.

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 2005-01-03 : 17:07:55

hi daniel
thanks a lot.you saved me from commiting a blunder.this file was listed when i did select * from sysfiles.please now suggest me how can i regulate its growth.this file is expanding.its size already is 12 gb.I have one more file which is also listed there.thatz mean these both are log files??please suggest me course of action

Regards

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2005-01-03 : 17:22:46

quote:
Originally posted by nitin1353

hi daniel
thanks a lot.you saved me from commiting a blunder.this file was listed when i did select * from sysfiles.please now suggest me how can i regulate its growth.this file is expanding.its size already is 12 gb.I have one more file which is also listed there.thatz mean these both are log files??please suggest me course of action

Regards

One is the data file and one is the log file.

To keep your log small, you must first decide the recovery model. If you dont care about restoring to a point in time then choose "Simple". The recovery model can be found in the "options" tab under the database properties in enterprise manager.

NR has a good site about the transaction log: http://www.nigelrivett.net/TransactionLogFileGrows_1.html

I think the best way to keep your log file in check is to just do database backups. They keep the log file from growing and getting out of hand.

Daniel
SQL Server DBA

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 2005-01-03 : 17:48:01

hi daniel

Thanks a ton for your help.it was really helpful.Thanks again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources