Removing a server: log

Showing posts with label log. Show all posts

Friday, March 30, 2012

Renaming Database Files

How can I go about safely renaming the data and log files for a
database?
Thanks.Hello ExcelMan,
I would go this way:
- Detach the database
- Rename the files
- Attach the database
Ekrem Önsoy
"ExcelMan" <sfarkas@.sjfcg.com> wrote in message
news:1188583811.122857.12230@.g4g2000hsf.googlegroups.com...
> How can I go about safely renaming the data and log files for a
> database?
> Thanks.
>|||On Aug 31, 11:31 am, Ekrem =D6nsoy <ek...@.btegitim.com> wrote:
> Hello ExcelMan,
> I would go this way:
> - Detach the database
> - Rename the files
> - Attach the database
> --
> Ekrem =D6nsoy
> "ExcelMan" <sfar...@.sjfcg.com> wrote in message
> news:1188583811.122857.12230@.g4g2000hsf.googlegroups.com...
>
> > How can I go about safely renaming the data and log files for a
> > database?
> > Thanks.- Hide quoted text -
> - Show quoted text -
That's what I tried and it does not work. The database will not
reattach if you change the names. When I changed them back to what I
started with it did reattach.
Any other ideas?|||Did you specify the new names when you executed sp_attach_db?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"ExcelMan" <sfarkas@.sjfcg.com> wrote in message
news:1188588512.215040.28540@.22g2000hsm.googlegroups.com...
On Aug 31, 11:31 am, Ekrem Önsoy <ek...@.btegitim.com> wrote:
> Hello ExcelMan,
> I would go this way:
> - Detach the database
> - Rename the files
> - Attach the database
> --
> Ekrem Önsoy
> "ExcelMan" <sfar...@.sjfcg.com> wrote in message
> news:1188583811.122857.12230@.g4g2000hsf.googlegroups.com...
>
> > How can I go about safely renaming the data and log files for a
> > database?
> > Thanks.- Hide quoted text -
> - Show quoted text -
That's what I tried and it does not work. The database will not
reattach if you change the names. When I changed them back to what I
started with it did reattach.
Any other ideas?|||There must be something you miss.
Tom could be right. How about trying this process via SSMS?
--
Ekrem Önsoy
"ExcelMan" <sfarkas@.sjfcg.com> wrote in message
news:1188588512.215040.28540@.22g2000hsm.googlegroups.com...
On Aug 31, 11:31 am, Ekrem Önsoy <ek...@.btegitim.com> wrote:
> Hello ExcelMan,
> I would go this way:
> - Detach the database
> - Rename the files
> - Attach the database
> --
> Ekrem Önsoy
> "ExcelMan" <sfar...@.sjfcg.com> wrote in message
> news:1188583811.122857.12230@.g4g2000hsf.googlegroups.com...
>
> > How can I go about safely renaming the data and log files for a
> > database?
> > Thanks.- Hide quoted text -
> - Show quoted text -
That's what I tried and it does not work. The database will not
reattach if you change the names. When I changed them back to what I
started with it did reattach.
Any other ideas?|||Logical or physical ones?
Whatever you try, first make a full backup.
See "alter database" statement in BOL. One way could be doing a backup and
restore it using options "replace" and "move".
create database test
go
select [name], physical_name
from sys.master_files
where database_id = db_id('test')
go
backup database test
to disk = 'c:\temp\test.bak'
go
alter database test
set single_user with rollback immediate
go
restore database test
from disk = 'c:\temp\test.bak'
with replace,
move 'test' to 'C:\Program Files\Microsoft SQL
Server\MSSQL.2\MSSQL\DATA\test1.mdf',
move 'test_log' to 'C:\Program Files\Microsoft SQL
Server\MSSQL.2\MSSQL\DATA\test1_log.ldf'
go
alter database test
modify file (name = 'test', newname = 'test1')
go
alter database test
modify file (name = 'test_log', newname = 'test1_log')
go
select [name], physical_name
from sys.master_files
where database_id = db_id('test')
go
drop database test
go
I played a little bit trying to change the physical file names usainf "alter
database" but there is a msg saying:
"The new path will be used the next time the database is started."
but I do not know how to start the db programatically. I tried setting it to
offline and then online but it fails. Here is the script.
use master
go
create database test
go
select [name], physical_name
from sys.master_files
where database_id = db_id('test')
go
alter database test
set single_user with rollback immediate
go
alter database test
modify file (name = 'test', newname = 'test1')
go
alter database test
modify file (name = 'test_log', newname = 'test1_log')
go
alter database test
modify file (name = 'test1', filename = 'C:\Program Files\Microsoft SQL
Server\MSSQL.2\MSSQL\DATA\test1.mdf')
go
alter database test
modify file (name = 'test1_log', filename = 'C:\Program Files\Microsoft SQL
Server\MSSQL.2\MSSQL\DATA\test1_log.mdf')
go
alter database test
set online
go
select [name], physical_name
from sys.master_files
where database_id = db_id('test')
go
alter database test
set multi_user
go
drop database test
go
AMB
"ExcelMan" wrote:
> How can I go about safely renaming the data and log files for a
> database?
> Thanks.
>

Renaming database after log shipping.

In order to move from one data center to another, I am planning to use log shipping.
Lets say,

Current Data center - DC1
Current Server- S1
Current Database- DB1

New Data Center -DC2
New Server -S2

Now, on the New Server S2, I already restored DB1 as DB1 for QA testing. QA has to do some testing before we move from DC1 to DC2. In the mean time, I need to start log shipping from S1 to S2, so that we can fail-over to S2. Now since DB1 database is already present on S2, I can not use the same name. I will have to use some other name (say DB1_New).
I can not wait for QA to finish their testing. I have to start log shipping before that. When QA is done, I will delete the DB1.

So for this log shipping, -

Primary - S1- DB1
Secondary - S2 DB1_New

So when we fail-over, we will have the database name as DB1_New. But all the apps use the name DB1. So I will have to rename the database as DB1.

So want to know, will this renaming the database (DB1_New to DB1) cause any issues?

Anyone had similar experience?

Thanks

Generally, you would be okay - some of that depends on how you have things configured, how long you are going to ship the logs before failing over to the other site, if logins/users have changed. You would want to consider what jobs you have running on the server, how this impacts your fail over plans and backups, anything pointing to the DB_new database name would need to be evaluated, etc. After the fail over and rename, you'd probably want to do a full backup of your databases, make sure the users are all intact, etc.

-Sue

|||

hi,

even if u have the same dbs in both source and detination prior to configuring log shipping, u need not create a new db in secondary instead u can drop the db in secondary......and while configuring log shipping u have the option to create the db @.destination if the db doesnt exist there already........if the failover is done properly all users from primary will be able to connect.....ensure that wherever u specified the db1 name in application change it to db1_new in your scenario........

Monday, March 26, 2012

Rename or purge my SQL Server 2000 transaction log file

Gurus,
How do I Rename or purge my SQL Server 2000 transaction log file? I have
successfully renamed the database using the ALTER DATABASE. I have
successfully renamed the physical MDF file by detaching the database,
renaming the file, and bringing it back online. However the transaction log
file still has the same old name. No one will be using the database over
the weekend.
--
Spin> I have successfully renamed the physical MDF file by detaching the
> database, renaming the file, and bringing it back online. However the
> transaction log file still has the same old name.
You can use the same process to rename the transaction log file: detach the
database, rename file(s) as desired and reattach the database specifying
*all* files:
EXEC sp_attach_db 'NewDatabaseName',
'C:\DataFiles\NewDatabaseName.mdf',
'C:\LogFiles\NewDatabaseName_Log.ldf'
When you omit the log file on sp_attach_db, SQL Server reuses the original
log file if it exists.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Spin" <Spin@.spin.com> wrote in message
news:4ecfcgF1e15rgU1@.individual.net...
> Gurus,
> How do I Rename or purge my SQL Server 2000 transaction log file? I have
> successfully renamed the database using the ALTER DATABASE. I have
> successfully renamed the physical MDF file by detaching the database,
> renaming the file, and bringing it back online. However the transaction
> log file still has the same old name. No one will be using the database
> over the weekend.
> --
> Spin
>

Rename or purge my SQL Server 2000 transaction log file

Gurus,
How do I Rename or purge my SQL Server 2000 transaction log file? I have
successfully renamed the database using the ALTER DATABASE. I have
successfully renamed the physical MDF file by detaching the database,
renaming the file, and bringing it back online. However the transaction log
file still has the same old name. No one will be using the database over
the weekend.
Spin> I have successfully renamed the physical MDF file by detaching the
> database, renaming the file, and bringing it back online. However the
> transaction log file still has the same old name.
You can use the same process to rename the transaction log file: detach the
database, rename file(s) as desired and reattach the database specifying
*all* files:
EXEC sp_attach_db 'NewDatabaseName',
'C:\DataFiles\NewDatabaseName.mdf',
'C:\LogFiles\NewDatabaseName_Log.ldf'
When you omit the log file on sp_attach_db, SQL Server reuses the original
log file if it exists.
Hope this helps.
Dan Guzman
SQL Server MVP
"Spin" <Spin@.spin.com> wrote in message
news:4ecfcgF1e15rgU1@.individual.net...
> Gurus,
> How do I Rename or purge my SQL Server 2000 transaction log file? I have
> successfully renamed the database using the ALTER DATABASE. I have
> successfully renamed the physical MDF file by detaching the database,
> renaming the file, and bringing it back online. However the transaction
> log file still has the same old name. No one will be using the database
> over the weekend.
> --
> Spin
>

Friday, March 23, 2012

rename logical file

> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi

rename logical file

Hi,
I have 3 databases running on Production server. All the databases have
names like AA, BB and CC with their Logical Data Files and Log files named as
AA_Data and AA_Log, BB_Data and BB_Log except for the third one. I want to
rename it as CC_Data and CC_Log to make it consistent. Would this renaming
affect anything on the server?You can change the logical file names, using ALTER DATABASE, without taking
the DB's offline.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:E0134C03-9EF7-44D3-A9E9-452C315CA1AA@.microsoft.com...
Hi,
I have 3 databases running on Production server. All the databases have
names like AA, BB and CC with their Logical Data Files and Log files named
as
AA_Data and AA_Log, BB_Data and BB_Log except for the third one. I want to
rename it as CC_Data and CC_Log to make it consistent. Would this renaming
affect anything on the server?|||Hi,
Would it affect anything / any process on the production server?
"Tom Moreau" wrote:
> You can change the logical file names, using ALTER DATABASE, without taking
> the DB's offline.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:E0134C03-9EF7-44D3-A9E9-452C315CA1AA@.microsoft.com...
> Hi,
> I have 3 databases running on Production server. All the databases have
> names like AA, BB and CC with their Logical Data Files and Log files named
> as
> AA_Data and AA_Log, BB_Data and BB_Log except for the third one. I want to
> rename it as CC_Data and CC_Log to make it consistent. Would this renaming
> affect anything on the server?
>|||No. It goes quickly.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:F5688817-57C3-40CD-88ED-B401B2E5438F@.microsoft.com...
Hi,
Would it affect anything / any process on the production server?
"Tom Moreau" wrote:
> You can change the logical file names, using ALTER DATABASE, without
> taking
> the DB's offline.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:E0134C03-9EF7-44D3-A9E9-452C315CA1AA@.microsoft.com...
> Hi,
> I have 3 databases running on Production server. All the databases have
> names like AA, BB and CC with their Logical Data Files and Log files named
> as
> AA_Data and AA_Log, BB_Data and BB_Log except for the third one. I want to
> rename it as CC_Data and CC_Log to make it consistent. Would this renaming
> affect anything on the server?
>|||Hi Tom,
Can you please explain to me what exactly is the purpose of a logical
Filename? When we have more than one databases on one SQL Server, can we have
the same logical name for all the databases? Thanks in advance.
"Tom Moreau" wrote:
> No. It goes quickly.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:F5688817-57C3-40CD-88ED-B401B2E5438F@.microsoft.com...
> Hi,
> Would it affect anything / any process on the production server?
> "Tom Moreau" wrote:
> > You can change the logical file names, using ALTER DATABASE, without
> > taking
> > the DB's offline.
> >
> > --
> > Tom
> >
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> > SQL Server MVP
> > Toronto, ON Canada
> > https://mvp.support.microsoft.com/profile/Tom.Moreau
> >
> >
> > "sharman" <sharman@.discussions.microsoft.com> wrote in message
> > news:E0134C03-9EF7-44D3-A9E9-452C315CA1AA@.microsoft.com...
> > Hi,
> >
> > I have 3 databases running on Production server. All the databases have
> > names like AA, BB and CC with their Logical Data Files and Log files named
> > as
> > AA_Data and AA_Log, BB_Data and BB_Log except for the third one. I want to
> > rename it as CC_Data and CC_Log to make it consistent. Would this renaming
> > affect anything on the server?
> >
> >
>|||The logical filename is just an easy way to refer to it when using ALTER
DATABASE. They are unique to the DB - not the server - so you can have a
number of DB's with the same logical filenames, but with different physical
filenames. This is convenient is a scenario where you have one DB per
client and what all of your DB maintenance scripts to do the same thing to
each customer DB.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:82F4AE24-BD99-4CD4-8411-CCFCB79882E5@.microsoft.com...
Hi Tom,
Can you please explain to me what exactly is the purpose of a logical
Filename? When we have more than one databases on one SQL Server, can we
have
the same logical name for all the databases? Thanks in advance.
"Tom Moreau" wrote:
> No. It goes quickly.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:F5688817-57C3-40CD-88ED-B401B2E5438F@.microsoft.com...
> Hi,
> Would it affect anything / any process on the production server?
> "Tom Moreau" wrote:
> > You can change the logical file names, using ALTER DATABASE, without
> > taking
> > the DB's offline.
> >
> > --
> > Tom
> >
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> > SQL Server MVP
> > Toronto, ON Canada
> > https://mvp.support.microsoft.com/profile/Tom.Moreau
> >
> >
> > "sharman" <sharman@.discussions.microsoft.com> wrote in message
> > news:E0134C03-9EF7-44D3-A9E9-452C315CA1AA@.microsoft.com...
> > Hi,
> >
> > I have 3 databases running on Production server. All the databases have
> > names like AA, BB and CC with their Logical Data Files and Log files
> > named
> > as
> > AA_Data and AA_Log, BB_Data and BB_Log except for the third one. I want
> > to
> > rename it as CC_Data and CC_Log to make it consistent. Would this
> > renaming
> > affect anything on the server?
> >
> >
>|||Does changing the logical name also change the physical (file) name? If not,
is there a method of doing that too?
"Tom Moreau" wrote:
> The logical filename is just an easy way to refer to it when using ALTER
> DATABASE. They are unique to the DB - not the server - so you can have a
> number of DB's with the same logical filenames, but with different physical
> filenames. This is convenient is a scenario where you have one DB per
> client and what all of your DB maintenance scripts to do the same thing to
> each customer DB.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:82F4AE24-BD99-4CD4-8411-CCFCB79882E5@.microsoft.com...
> Hi Tom,
> Can you please explain to me what exactly is the purpose of a logical
> Filename? When we have more than one databases on one SQL Server, can we
> have
> the same logical name for all the databases? Thanks in advance.
> "Tom Moreau" wrote:
> > No. It goes quickly.
> >
> > --
> > Tom
> >
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> > SQL Server MVP
> > Toronto, ON Canada
> > https://mvp.support.microsoft.com/profile/Tom.Moreau
> >
> >
> > "sharman" <sharman@.discussions.microsoft.com> wrote in message
> > news:F5688817-57C3-40CD-88ED-B401B2E5438F@.microsoft.com...
> > Hi,
> >
> > Would it affect anything / any process on the production server?
> >
> > "Tom Moreau" wrote:
> >
> > > You can change the logical file names, using ALTER DATABASE, without
> > > taking
> > > the DB's offline.
> > >
> > > --
> > > Tom
> > >
> > > ----
> > > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> > > SQL Server MVP
> > > Toronto, ON Canada
> > > https://mvp.support.microsoft.com/profile/Tom.Moreau
> > >
> > >
> > > "sharman" <sharman@.discussions.microsoft.com> wrote in message
> > > news:E0134C03-9EF7-44D3-A9E9-452C315CA1AA@.microsoft.com...
> > > Hi,
> > >
> > > I have 3 databases running on Production server. All the databases have
> > > names like AA, BB and CC with their Logical Data Files and Log files
> > > named
> > > as
> > > AA_Data and AA_Log, BB_Data and BB_Log except for the third one. I want
> > > to
> > > rename it as CC_Data and CC_Log to make it consistent. Would this
> > > renaming
> > > affect anything on the server?
> > >
> > >
> >
> >
>|||> Does changing the logical name also change the physical (file) name?
No.
> If not,
> is there a method of doing that too?
Yes. Easiest, IMO, is detach and attach the database. In 2005, you can use ALTER DATABASE to change
the physical name, stop SQL Server, copy the file, and then start SQL Server. I don't find this very
useful, so I prefer detach and attach better in 2005 as well.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Yabe" <Yabe@.discussions.microsoft.com> wrote in message
news:85B0F865-7ABB-44FC-8690-145907510A05@.microsoft.com...
> Does changing the logical name also change the physical (file) name? If not,
> is there a method of doing that too?
>
> "Tom Moreau" wrote:
>> The logical filename is just an easy way to refer to it when using ALTER
>> DATABASE. They are unique to the DB - not the server - so you can have a
>> number of DB's with the same logical filenames, but with different physical
>> filenames. This is convenient is a scenario where you have one DB per
>> client and what all of your DB maintenance scripts to do the same thing to
>> each customer DB.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> SQL Server MVP
>> Toronto, ON Canada
>> https://mvp.support.microsoft.com/profile/Tom.Moreau
>>
>> "sharman" <sharman@.discussions.microsoft.com> wrote in message
>> news:82F4AE24-BD99-4CD4-8411-CCFCB79882E5@.microsoft.com...
>> Hi Tom,
>> Can you please explain to me what exactly is the purpose of a logical
>> Filename? When we have more than one databases on one SQL Server, can we
>> have
>> the same logical name for all the databases? Thanks in advance.
>> "Tom Moreau" wrote:
>> > No. It goes quickly.
>> >
>> > --
>> > Tom
>> >
>> > ----
>> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> > SQL Server MVP
>> > Toronto, ON Canada
>> > https://mvp.support.microsoft.com/profile/Tom.Moreau
>> >
>> >
>> > "sharman" <sharman@.discussions.microsoft.com> wrote in message
>> > news:F5688817-57C3-40CD-88ED-B401B2E5438F@.microsoft.com...
>> > Hi,
>> >
>> > Would it affect anything / any process on the production server?
>> >
>> > "Tom Moreau" wrote:
>> >
>> > > You can change the logical file names, using ALTER DATABASE, without
>> > > taking
>> > > the DB's offline.
>> > >
>> > > --
>> > > Tom
>> > >
>> > > ----
>> > > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> > > SQL Server MVP
>> > > Toronto, ON Canada
>> > > https://mvp.support.microsoft.com/profile/Tom.Moreau
>> > >
>> > >
>> > > "sharman" <sharman@.discussions.microsoft.com> wrote in message
>> > > news:E0134C03-9EF7-44D3-A9E9-452C315CA1AA@.microsoft.com...
>> > > Hi,
>> > >
>> > > I have 3 databases running on Production server. All the databases have
>> > > names like AA, BB and CC with their Logical Data Files and Log files
>> > > named
>> > > as
>> > > AA_Data and AA_Log, BB_Data and BB_Log except for the third one. I want
>> > > to
>> > > rename it as CC_Data and CC_Log to make it consistent. Would this
>> > > renaming
>> > > affect anything on the server?
>> > >
>> > >
>> >
>> >
>>|||Thank you.
"Tibor Karaszi" wrote:
> > Does changing the logical name also change the physical (file) name?
> No.
>
> > If not,
> > is there a method of doing that too?
> Yes. Easiest, IMO, is detach and attach the database. In 2005, you can use ALTER DATABASE to change
> the physical name, stop SQL Server, copy the file, and then start SQL Server. I don't find this very
> useful, so I prefer detach and attach better in 2005 as well.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi|||"Tibor Karaszi" wrote:
> > Does changing the logical name also change the physical (file) name?
> No.
>
> > If not,
> > is there a method of doing that too?
> Yes. Easiest, IMO, is detach and attach the database. In 2005, you can use ALTER DATABASE to change
> the physical name, stop SQL Server, copy the file, and then start SQL Server. I don't find this very
> useful, so I prefer detach and attach better in 2005 as well.
Hi Tibor,
I've been experimenting with detach/attach and I can't (for the life of me)
figure out how to change the name on the fly -- in MSS 2005 (or earlier
release for that matter). Maybe I'm going about this all wrong ... all on
the same server ... Here's my situation ... I have a database that I want to
clone and rename on the same server.
I have one database named "Database1" (c:\data1\d1.mdf, c:\data1\d1.ldf).
I detach the original database and copy both files to a new directory
x:\data2\d1.mdf, x:\data2\d1.ldf).
I re-attach the original database Database1 using MSS Management Studio.
Then I attempt to attach the 2nd database, I'll browse to the new directory
and select the duplicate d1.mdf, but it won't let me change the database
name, so I abort.
Then I manually rename both the (copied) database and the log files from d1
to d2 (x:\data2\d2.mdf and x:\data2\d2.ldf) and try to attach again.
When I attach the newly renamed files, MSS remembers both the original file
names and the original database name "Database1" and will complain if I click
OK to try and save the 2nd database to the host/instance. I don't see how to
1) change the original database name when attaching or 2) force the MSS
system to let me change the new database name on the fly as I attach it.
Sorry if the answer is looking me in the face :)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi|||I normally drop the db, then copy it to the new location - data in the
data directory, logs to their directory. Rename the files I copied my
test.mdf and test.ldf to test2.mdf and test2.ldf
Then in SMS do a attach database and browse to the mdf file. Select the
new one. If necessary do the same for the log (you'll have to do this
if you moved it).
* In the top window check the "Attach AS" and change it to whatever you
want - for me test2
* In the bottom window under "Current File Path" browse to the new file
and select the one you renamed.- do for mdf and ldf files. For mdf I
selected test2.mdf and for ldf test2.ldf
* In top window change owner if necessary.
* Click OK
Now go to the database in object explorer and right click->properties.
Check the files window. You'll see the new files selected and if you
want you can change the logical names.
Yabe wrote:
> "Tibor Karaszi" wrote:
>> Does changing the logical name also change the physical (file) name?
>> No.
>>
>> If not,
>> is there a method of doing that too?
>> Yes. Easiest, IMO, is detach and attach the database. In 2005, you can use ALTER DATABASE to change
>> the physical name, stop SQL Server, copy the file, and then start SQL Server. I don't find this very
>> useful, so I prefer detach and attach better in 2005 as well.
> Hi Tibor,
> I've been experimenting with detach/attach and I can't (for the life of me)
> figure out how to change the name on the fly -- in MSS 2005 (or earlier
> release for that matter). Maybe I'm going about this all wrong ... all on
> the same server ... Here's my situation ... I have a database that I want to
> clone and rename on the same server.
> I have one database named "Database1" (c:\data1\d1.mdf, c:\data1\d1.ldf).
> I detach the original database and copy both files to a new directory
> x:\data2\d1.mdf, x:\data2\d1.ldf).
> I re-attach the original database Database1 using MSS Management Studio.
> Then I attempt to attach the 2nd database, I'll browse to the new directory
> and select the duplicate d1.mdf, but it won't let me change the database
> name, so I abort.
> Then I manually rename both the (copied) database and the log files from d1
> to d2 (x:\data2\d2.mdf and x:\data2\d2.ldf) and try to attach again.
> When I attach the newly renamed files, MSS remembers both the original file
> names and the original database name "Database1" and will complain if I click
> OK to try and save the 2nd database to the host/instance. I don't see how to
> 1) change the original database name when attaching or 2) force the MSS
> system to let me change the new database name on the fly as I attach it.
> Sorry if the answer is looking me in the face :)
>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>|||Probably just the SSMS GUI that is messing with you. I suggest you read about sp_attach_db in Books
Online. Since the mdf has the path and name of the original ldf (and other db files), you need to
specify all file names, like:
EXEC sp_attach_db 'NewDbName', 'C:\NewPath\NewFileName.mdf', 'C:\NewPath\NewFileName.ldf'
Above from memory, so please check Books Online for syntax.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Yabe" <Yabe@.discussions.microsoft.com> wrote in message
news:23B5D429-5E6A-4209-BCC2-CB9C20C0C94B@.microsoft.com...
>
> "Tibor Karaszi" wrote:
>> > Does changing the logical name also change the physical (file) name?
>> No.
>>
>> > If not,
>> > is there a method of doing that too?
>> Yes. Easiest, IMO, is detach and attach the database. In 2005, you can use ALTER DATABASE to
>> change
>> the physical name, stop SQL Server, copy the file, and then start SQL Server. I don't find this
>> very
>> useful, so I prefer detach and attach better in 2005 as well.
> Hi Tibor,
> I've been experimenting with detach/attach and I can't (for the life of me)
> figure out how to change the name on the fly -- in MSS 2005 (or earlier
> release for that matter). Maybe I'm going about this all wrong ... all on
> the same server ... Here's my situation ... I have a database that I want to
> clone and rename on the same server.
> I have one database named "Database1" (c:\data1\d1.mdf, c:\data1\d1.ldf).
> I detach the original database and copy both files to a new directory
> x:\data2\d1.mdf, x:\data2\d1.ldf).
> I re-attach the original database Database1 using MSS Management Studio.
> Then I attempt to attach the 2nd database, I'll browse to the new directory
> and select the duplicate d1.mdf, but it won't let me change the database
> name, so I abort.
> Then I manually rename both the (copied) database and the log files from d1
> to d2 (x:\data2\d2.mdf and x:\data2\d2.ldf) and try to attach again.
> When I attach the newly renamed files, MSS remembers both the original file
> names and the original database name "Database1" and will complain if I click
> OK to try and save the 2nd database to the host/instance. I don't see how to
> 1) change the original database name when attaching or 2) force the MSS
> system to let me change the new database name on the fly as I attach it.
> Sorry if the answer is looking me in the face :)
>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>

rename logical file

> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi

Rename Files

Is it possible to rename the mdf and log file names?Not while a DB on the server is using those files. (Not that I can
think of off the top of my head anyway.) You'd have to detach the
database (that uses those files), rename the files on the file system
and then reattach those renamed files.
*mike hodgson*
http://sqlnerd.blogspot.com
scott wrote:

>Is it possible to rename the mdf and log file names?
>
>|||Scott,
you could use the following scrip as a template:
USE master;
GO
ALTER DATABASE The_DB MODIFY FILE
(NAME = The_DB_Data,
FILENAME='C:\SQLSVRDB\The_DB_new.mdf');
GO
ALTER DATABASE The_DB SET OFFLINE;
EXEC master..xp_cmdshell 'rename C:\SQLSVRDB\The_DB_old.mdf The_DB_new.mdf';
ALTER DATABASE DB SET ONLINE;
GO
Andrey Odegov
avodeGOV@.yandex.ru
(remove GOV to respond)

Tuesday, March 20, 2012

Removing un necessary log

In a production database I'm finding the log file is increasing rapidly and
after taking a backup I find the log file is still increasing fast. Since th
e database is running and highly in use in production evironment I can't use
trunc,log on chkpt option
on the database.
What should I do at this moment plz suggest me keeping in view the database
is running and production database.
Regards and thanks in advance,
Sunil DashIf the database is in Full Recovery mode, backing up the transaction log
will make space available for re-use..(But you must have done at least one
full database backup first.)
If you have long running transactions, that will prevent the transaction log
from truncating properly as well...
DBCC opentran ( in books on line) will show you the SPID of the longest
running open transaction... First find out if there is a long running
transaction and what it is..
I suggest you backup the t-log now...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sunil" <anonymous@.discussions.microsoft.com> wrote in message
news:C2E45928-E815-428E-836A-B13A4E080BF1@.microsoft.com...
> In a production database I'm finding the log file is increasing rapidly
and after taking a backup I find the log file is still increasing fast.
Since the database is running and highly in use in production evironment I
can't use trunc,log on chkpt option on the database.
>
> What should I do at this moment plz suggest me keeping in view the
database is running and production database.
>
> Regards and thanks in advance,
>
> Sunil Dash

Removing un necessary log

In a production database I'm finding the log file is increasing rapidly and after taking a backup I find the log file is still increasing fast. Since the database is running and highly in use in production evironment I can't use trunc,log on chkpt option
on the database.
What should I do at this moment plz suggest me keeping in view the database is running and production database.
Regards and thanks in advance,
Sunil Dash
If the database is in Full Recovery mode, backing up the transaction log
will make space available for re-use..(But you must have done at least one
full database backup first.)
If you have long running transactions, that will prevent the transaction log
from truncating properly as well...
DBCC opentran ( in books on line) will show you the SPID of the longest
running open transaction... First find out if there is a long running
transaction and what it is..
I suggest you backup the t-log now...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sunil" <anonymous@.discussions.microsoft.com> wrote in message
news:C2E45928-E815-428E-836A-B13A4E080BF1@.microsoft.com...
> In a production database I'm finding the log file is increasing rapidly
and after taking a backup I find the log file is still increasing fast.
Since the database is running and highly in use in production evironment I
can't use trunc,log on chkpt option on the database.
>
> What should I do at this moment plz suggest me keeping in view the
database is running and production database.
>
> Regards and thanks in advance,
>
> Sunil Dash

Removing un necessary log

In a production database I'm finding the log file is increasing rapidly and after taking a backup I find the log file is still increasing fast. Since the database is running and highly in use in production evironment I can't use trunc,log on chkpt option on the database
What should I do at this moment plz suggest me keeping in view the database is running and production database
Regards and thanks in advance
Sunil DashIf the database is in Full Recovery mode, backing up the transaction log
will make space available for re-use..(But you must have done at least one
full database backup first.)
If you have long running transactions, that will prevent the transaction log
from truncating properly as well...
DBCC opentran ( in books on line) will show you the SPID of the longest
running open transaction... First find out if there is a long running
transaction and what it is..
I suggest you backup the t-log now...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sunil" <anonymous@.discussions.microsoft.com> wrote in message
news:C2E45928-E815-428E-836A-B13A4E080BF1@.microsoft.com...
> In a production database I'm finding the log file is increasing rapidly
and after taking a backup I find the log file is still increasing fast.
Since the database is running and highly in use in production evironment I
can't use trunc,log on chkpt option on the database.
>
> What should I do at this moment plz suggest me keeping in view the
database is running and production database.
>
> Regards and thanks in advance,
>
> Sunil Dash

Friday, March 9, 2012

Removing old transaction log backup files

My organization is running SQL Server 2000 on a Windows 2000 server. We have
a maintenance plan set up to perform a daily backup of several database
files. The full database backup runs each day at 2:00 a.m. The transaction
log backup runs every four hours. It is setup in the maintenance plan that
the checkbox for both the data file and transaction log that says "Remove
files older than" is checked and set for 2 days. However, the old transaction
log backup files (*.TRN) are not being automatically removed. The regular
database files (*.BAK) are removed just fine. Any suggestions?
Hello
You should have some more information in the Maintenance Plan output file.
You can configure the Maint plan to produce output from the Maint Plan
properties window.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||Below KB might help:
http://support.microsoft.com/default...&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AscentSQLAdmin" <AscentSQLAdmin@.discussions.microsoft.com> wrote in message
news:9A971D9F-D439-4F0F-B1E9-BAB6F42D73B3@.microsoft.com...
> My organization is running SQL Server 2000 on a Windows 2000 server. We have
> a maintenance plan set up to perform a daily backup of several database
> files. The full database backup runs each day at 2:00 a.m. The transaction
> log backup runs every four hours. It is setup in the maintenance plan that
> the checkbox for both the data file and transaction log that says "Remove
> files older than" is checked and set for 2 days. However, the old transaction
> log backup files (*.TRN) are not being automatically removed. The regular
> database files (*.BAK) are removed just fine. Any suggestions?

Removing old transaction log backup files

My organization is running SQL Server 2000 on a Windows 2000 server. We have
a maintenance plan set up to perform a daily backup of several database
files. The full database backup runs each day at 2:00 a.m. The transaction
log backup runs every four hours. It is setup in the maintenance plan that
the checkbox for both the data file and transaction log that says "Remove
files older than" is checked and set for 2 days. However, the old transactio
n
log backup files (*.TRN) are not being automatically removed. The regular
database files (*.BAK) are removed just fine. Any suggestions?Hello
You should have some more information in the Maintenance Plan output file.
You can configure the Maint plan to produce output from the Maint Plan
properties window.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Below KB might help:
http://support.microsoft.com/defaul...2&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AscentSQLAdmin" <AscentSQLAdmin@.discussions.microsoft.com> wrote in message
news:9A971D9F-D439-4F0F-B1E9-BAB6F42D73B3@.microsoft.com...
> My organization is running SQL Server 2000 on a Windows 2000 server. We ha
ve
> a maintenance plan set up to perform a daily backup of several database
> files. The full database backup runs each day at 2:00 a.m. The transaction
> log backup runs every four hours. It is setup in the maintenance plan that
> the checkbox for both the data file and transaction log that says "Remove
> files older than" is checked and set for 2 days. However, the old transact
ion
> log backup files (*.TRN) are not being automatically removed. The regular
> database files (*.BAK) are removed just fine. Any suggestions?

removing logs

I have over 200 log files with a date and timestamp in the filename in the
Reporting Services\LogFiles folder that are 33MB each. This is a development
machine. How do I get rid of them? Can I just delete them?
Thanks,
--
Dan D.You can delete, it creates again, but I dont think you require this much log
files for your development server. For production yes you need to have a
backup. Before deleting keep a backup or Zip all the log files and keep it in
a seperate place.
If it is so important, you can run a program which is available in the
samples to read the log files and reporting.
and ofcourse schedule it to remove. default it keeps it for 60 days.
Amarnath
"Dan D." wrote:
> I have over 200 log files with a date and timestamp in the filename in the
> Reporting Services\LogFiles folder that are 33MB each. This is a development
> machine. How do I get rid of them? Can I just delete them?
> Thanks,
> --
> Dan D.|||I never thought about log files for reportserver. Most of the files are
small, under 50K. I don't know what was in the files that were so large.
I removed them.
Thanks,
--
Dan D.
"Amarnath" wrote:
> You can delete, it creates again, but I dont think you require this much log
> files for your development server. For production yes you need to have a
> backup. Before deleting keep a backup or Zip all the log files and keep it in
> a seperate place.
> If it is so important, you can run a program which is available in the
> samples to read the log files and reporting.
> and ofcourse schedule it to remove. default it keeps it for 60 days.
> Amarnath
> "Dan D." wrote:
> > I have over 200 log files with a date and timestamp in the filename in the
> > Reporting Services\LogFiles folder that are 33MB each. This is a development
> > machine. How do I get rid of them? Can I just delete them?
> >
> > Thanks,
> > --
> > Dan D.

Removing Log Shipping after disaster

I am trying to get our database back to a "clean" state after a disaster at the weekend. The primary server died - completely - and so the secondary server was promoted to primary. Now, I'd like to remove all traces of log shipping on the server so I can start fresh when our new server arrives imminently.

However, when I try to remove log shipping, SQL tries to connect to the old primary server which of course no longer exists. I can't find anyway to remove the log shipping. What can I do?

Delete the jobs that are doing the backup, copy, and restore. Remove the fileshares. No more log shipping. If this is SQL Server 2000, then just delete the maintenance plan that you used to setup log shipping.|||I've deleted the jobs on what was the secondary server (now the primary). I'm not able to delete the maintenance plans as SQL says I must remove log shipping before deleting the plan. If I try to remove the log shipping, SQL tries to connect to the old machine and fails (as it doesn't exist anymore) .

Removing log files from SQL Database

Need help .

There are 3 log files attached to a SQL Database . I would like to remove one of them as it was created by Previous DBA for temp use (Don'r ask me why?) If I run DBCC ShrinkFile with EMPTYFILE , would it let me drop that file or is there any command to do it? OR is it not possible at allYes, you can even do it using SQL Enterprise Mangler (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_7_design_28mx.asp) if you like!

-PatP|||This is what I needed

Removing Log File

I have a database call Runz in SQL Server 2000. When I checked the file
sizes in directory D:\program files\Microsoft SQL Server\MSSQL\Data, I got
the following:
Runz_Data.MDF 558,400 KB
Runz_Log.LDF 517,184 KB
When I checked the properties of the database in Enterprise Manager, I noted
on the General tab Size: 1050.37 MB Space Available: 718.79 MB
Data Files tab Space Allocated: 546 MB
Transaction Log tab Space Allocated: 506 MB
I am trying to save disk space on my computer. Can I delete file
Runz_Log.LDF or remove all of its data, or does it actually hold data which
exists in the tables. Is there anything else I can do to decrease the amount
of space in the files, similar to a Compact and Repair in Access?
Yes, backup the transaction log and then manually shrink it.
You may need to schedule at least daily transaction log backups.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"rmcompute" wrote:

> I have a database call Runz in SQL Server 2000. When I checked the file
> sizes in directory D:\program files\Microsoft SQL Server\MSSQL\Data, I got
> the following:
> Runz_Data.MDF 558,400 KB
> Runz_Log.LDF 517,184 KB
> When I checked the properties of the database in Enterprise Manager, I noted
> on the General tab Size: 1050.37 MB Space Available: 718.79 MB
> Data Files tab Space Allocated: 546 MB
> Transaction Log tab Space Allocated: 506 MB
> I am trying to save disk space on my computer. Can I delete file
> Runz_Log.LDF or remove all of its data, or does it actually hold data which
> exists in the tables. Is there anything else I can do to decrease the amount
> of space in the files, similar to a Compact and Repair in Access?
>
>
|||LDF file is an essential file of a database. It should not be deleted
otherwise you might get into bad trouble.
Every database has at least an MDF and LDF files. MDF files contain data and
LDF files contain transactions.
It can be truncated and shrinked to keep its file size under control.
If you do not take transaction log backups then that means you can tolerate
data loss so you can change your database' s recovery model to SIMPLE so the
transactions will be truncated automatically and your log file will not grow
that large.
You can find more info about recovery models from Books Online.
Ekrem ?nsoy
"rmcompute" <rmcompute@.discussions.microsoft.com> wrote in message
news:4D485673-1D54-4ECA-8F7E-4203DD4CA06B@.microsoft.com...
>I have a database call Runz in SQL Server 2000. When I checked the file
> sizes in directory D:\program files\Microsoft SQL Server\MSSQL\Data, I got
> the following:
> Runz_Data.MDF 558,400 KB
> Runz_Log.LDF 517,184 KB
> When I checked the properties of the database in Enterprise Manager, I
> noted
> on the General tab Size: 1050.37 MB Space Available: 718.79 MB
> Data Files tab Space Allocated: 546 MB
> Transaction Log tab Space Allocated: 506 MB
> I am trying to save disk space on my computer. Can I delete file
> Runz_Log.LDF or remove all of its data, or does it actually hold data
> which
> exists in the tables. Is there anything else I can do to decrease the
> amount
> of space in the files, similar to a Compact and Repair in Access?
>
>
|||Thank you
"Ekrem ?nsoy" wrote:

> LDF file is an essential file of a database. It should not be deleted
> otherwise you might get into bad trouble.
> Every database has at least an MDF and LDF files. MDF files contain data and
> LDF files contain transactions.
> It can be truncated and shrinked to keep its file size under control.
> If you do not take transaction log backups then that means you can tolerate
> data loss so you can change your database' s recovery model to SIMPLE so the
> transactions will be truncated automatically and your log file will not grow
> that large.
> You can find more info about recovery models from Books Online.
> --
> Ekrem ?nsoy
>
> "rmcompute" <rmcompute@.discussions.microsoft.com> wrote in message
> news:4D485673-1D54-4ECA-8F7E-4203DD4CA06B@.microsoft.com...
>
|||Thank you
"rmcompute" wrote:

> I have a database call Runz in SQL Server 2000. When I checked the file
> sizes in directory D:\program files\Microsoft SQL Server\MSSQL\Data, I got
> the following:
> Runz_Data.MDF 558,400 KB
> Runz_Log.LDF 517,184 KB
> When I checked the properties of the database in Enterprise Manager, I noted
> on the General tab Size: 1050.37 MB Space Available: 718.79 MB
> Data Files tab Space Allocated: 546 MB
> Transaction Log tab Space Allocated: 506 MB
> I am trying to save disk space on my computer. Can I delete file
> Runz_Log.LDF or remove all of its data, or does it actually hold data which
> exists in the tables. Is there anything else I can do to decrease the amount
> of space in the files, similar to a Compact and Repair in Access?
>
>

Removing Log File

I have a database call Runz in SQL Server 2000. When I checked the file
sizes in directory D:\program files\Microsoft SQL Server\MSSQL\Data, I got
the following:
Runz_Data.MDF 558,400 KB
Runz_Log.LDF 517,184 KB
When I checked the properties of the database in Enterprise Manager, I noted
on the General tab Size: 1050.37 MB Space Available: 718.79 MB
Data Files tab Space Allocated: 546 MB
Transaction Log tab Space Allocated: 506 MB
I am trying to save disk space on my computer. Can I delete file
Runz_Log.LDF or remove all of its data, or does it actually hold data which
exists in the tables. Is there anything else I can do to decrease the amoun
t
of space in the files, similar to a Compact and Repair in Access?Yes, backup the transaction log and then manually shrink it.
You may need to schedule at least daily transaction log backups.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"rmcompute" wrote:

> I have a database call Runz in SQL Server 2000. When I checked the file
> sizes in directory D:\program files\Microsoft SQL Server\MSSQL\Data, I got
> the following:
> Runz_Data.MDF 558,400 KB
> Runz_Log.LDF 517,184 KB
> When I checked the properties of the database in Enterprise Manager, I not
ed
> on the General tab Size: 1050.37 MB Space Available: 718.79 MB
> Data Files tab Space Allocated: 546 MB
> Transaction Log tab Space Allocated: 506 MB
> I am trying to save disk space on my computer. Can I delete file
> Runz_Log.LDF or remove all of its data, or does it actually hold data whic
h
> exists in the tables. Is there anything else I can do to decrease the amo
unt
> of space in the files, similar to a Compact and Repair in Access?
>
>|||LDF file is an essential file of a database. It should not be deleted
otherwise you might get into bad trouble.
Every database has at least an MDF and LDF files. MDF files contain data and
LDF files contain transactions.
It can be truncated and shrinked to keep its file size under control.
If you do not take transaction log backups then that means you can tolerate
data loss so you can change your database' s recovery model to SIMPLE so the
transactions will be truncated automatically and your log file will not grow
that large.
You can find more info about recovery models from Books Online.
Ekrem ?nsoy
"rmcompute" <rmcompute@.discussions.microsoft.com> wrote in message
news:4D485673-1D54-4ECA-8F7E-4203DD4CA06B@.microsoft.com...
>I have a database call Runz in SQL Server 2000. When I checked the file
> sizes in directory D:\program files\Microsoft SQL Server\MSSQL\Data, I got
> the following:
> Runz_Data.MDF 558,400 KB
> Runz_Log.LDF 517,184 KB
> When I checked the properties of the database in Enterprise Manager, I
> noted
> on the General tab Size: 1050.37 MB Space Available: 718.79 MB
> Data Files tab Space Allocated: 546 MB
> Transaction Log tab Space Allocated: 506 MB
> I am trying to save disk space on my computer. Can I delete file
> Runz_Log.LDF or remove all of its data, or does it actually hold data
> which
> exists in the tables. Is there anything else I can do to decrease the
> amount
> of space in the files, similar to a Compact and Repair in Access?
>
>|||Thank you
"Ekrem ?nsoy" wrote:

> LDF file is an essential file of a database. It should not be deleted
> otherwise you might get into bad trouble.
> Every database has at least an MDF and LDF files. MDF files contain data a
nd
> LDF files contain transactions.
> It can be truncated and shrinked to keep its file size under control.
> If you do not take transaction log backups then that means you can tolerat
e
> data loss so you can change your database' s recovery model to SIMPLE so t
he
> transactions will be truncated automatically and your log file will not gr
ow
> that large.
> You can find more info about recovery models from Books Online.
> --
> Ekrem ?nsoy
>
> "rmcompute" <rmcompute@.discussions.microsoft.com> wrote in message
> news:4D485673-1D54-4ECA-8F7E-4203DD4CA06B@.microsoft.com...
>|||Thank you
"rmcompute" wrote:

> I have a database call Runz in SQL Server 2000. When I checked the file
> sizes in directory D:\program files\Microsoft SQL Server\MSSQL\Data, I got
> the following:
> Runz_Data.MDF 558,400 KB
> Runz_Log.LDF 517,184 KB
> When I checked the properties of the database in Enterprise Manager, I not
ed
> on the General tab Size: 1050.37 MB Space Available: 718.79 MB
> Data Files tab Space Allocated: 546 MB
> Transaction Log tab Space Allocated: 506 MB
> I am trying to save disk space on my computer. Can I delete file
> Runz_Log.LDF or remove all of its data, or does it actually hold data whic
h
> exists in the tables. Is there anything else I can do to decrease the amo
unt
> of space in the files, similar to a Compact and Repair in Access?
>
>

Removing Log File

I have a database call Runz in SQL Server 2000. When I checked the file
sizes in directory D:\program files\Microsoft SQL Server\MSSQL\Data, I got
the following:
Runz_Data.MDF 558,400 KB
Runz_Log.LDF 517,184 KB
When I checked the properties of the database in Enterprise Manager, I noted
on the General tab Size: 1050.37 MB Space Available: 718.79 MB
Data Files tab Space Allocated: 546 MB
Transaction Log tab Space Allocated: 506 MB
I am trying to save disk space on my computer. Can I delete file
Runz_Log.LDF or remove all of its data, or does it actually hold data which
exists in the tables. Is there anything else I can do to decrease the amount
of space in the files, similar to a Compact and Repair in Access?Yes, backup the transaction log and then manually shrink it.
You may need to schedule at least daily transaction log backups.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"rmcompute" wrote:
> I have a database call Runz in SQL Server 2000. When I checked the file
> sizes in directory D:\program files\Microsoft SQL Server\MSSQL\Data, I got
> the following:
> Runz_Data.MDF 558,400 KB
> Runz_Log.LDF 517,184 KB
> When I checked the properties of the database in Enterprise Manager, I noted
> on the General tab Size: 1050.37 MB Space Available: 718.79 MB
> Data Files tab Space Allocated: 546 MB
> Transaction Log tab Space Allocated: 506 MB
> I am trying to save disk space on my computer. Can I delete file
> Runz_Log.LDF or remove all of its data, or does it actually hold data which
> exists in the tables. Is there anything else I can do to decrease the amount
> of space in the files, similar to a Compact and Repair in Access?
>
>|||LDF file is an essential file of a database. It should not be deleted
otherwise you might get into bad trouble.
Every database has at least an MDF and LDF files. MDF files contain data and
LDF files contain transactions.
It can be truncated and shrinked to keep its file size under control.
If you do not take transaction log backups then that means you can tolerate
data loss so you can change your database' s recovery model to SIMPLE so the
transactions will be truncated automatically and your log file will not grow
that large.
You can find more info about recovery models from Books Online.
--
Ekrem Ã?nsoy
"rmcompute" <rmcompute@.discussions.microsoft.com> wrote in message
news:4D485673-1D54-4ECA-8F7E-4203DD4CA06B@.microsoft.com...
>I have a database call Runz in SQL Server 2000. When I checked the file
> sizes in directory D:\program files\Microsoft SQL Server\MSSQL\Data, I got
> the following:
> Runz_Data.MDF 558,400 KB
> Runz_Log.LDF 517,184 KB
> When I checked the properties of the database in Enterprise Manager, I
> noted
> on the General tab Size: 1050.37 MB Space Available: 718.79 MB
> Data Files tab Space Allocated: 546 MB
> Transaction Log tab Space Allocated: 506 MB
> I am trying to save disk space on my computer. Can I delete file
> Runz_Log.LDF or remove all of its data, or does it actually hold data
> which
> exists in the tables. Is there anything else I can do to decrease the
> amount
> of space in the files, similar to a Compact and Repair in Access?
>
>|||Thank you
"Ekrem Ã?nsoy" wrote:
> LDF file is an essential file of a database. It should not be deleted
> otherwise you might get into bad trouble.
> Every database has at least an MDF and LDF files. MDF files contain data and
> LDF files contain transactions.
> It can be truncated and shrinked to keep its file size under control.
> If you do not take transaction log backups then that means you can tolerate
> data loss so you can change your database' s recovery model to SIMPLE so the
> transactions will be truncated automatically and your log file will not grow
> that large.
> You can find more info about recovery models from Books Online.
> --
> Ekrem Ã?nsoy
>
> "rmcompute" <rmcompute@.discussions.microsoft.com> wrote in message
> news:4D485673-1D54-4ECA-8F7E-4203DD4CA06B@.microsoft.com...
> >I have a database call Runz in SQL Server 2000. When I checked the file
> > sizes in directory D:\program files\Microsoft SQL Server\MSSQL\Data, I got
> > the following:
> >
> > Runz_Data.MDF 558,400 KB
> > Runz_Log.LDF 517,184 KB
> >
> > When I checked the properties of the database in Enterprise Manager, I
> > noted
> > on the General tab Size: 1050.37 MB Space Available: 718.79 MB
> > Data Files tab Space Allocated: 546 MB
> > Transaction Log tab Space Allocated: 506 MB
> >
> > I am trying to save disk space on my computer. Can I delete file
> > Runz_Log.LDF or remove all of its data, or does it actually hold data
> > which
> > exists in the tables. Is there anything else I can do to decrease the
> > amount
> > of space in the files, similar to a Compact and Repair in Access?
> >
> >
> >
> >
>|||Thank you
"rmcompute" wrote:
> I have a database call Runz in SQL Server 2000. When I checked the file
> sizes in directory D:\program files\Microsoft SQL Server\MSSQL\Data, I got
> the following:
> Runz_Data.MDF 558,400 KB
> Runz_Log.LDF 517,184 KB
> When I checked the properties of the database in Enterprise Manager, I noted
> on the General tab Size: 1050.37 MB Space Available: 718.79 MB
> Data Files tab Space Allocated: 546 MB
> Transaction Log tab Space Allocated: 506 MB
> I am trying to save disk space on my computer. Can I delete file
> Runz_Log.LDF or remove all of its data, or does it actually hold data which
> exists in the tables. Is there anything else I can do to decrease the amount
> of space in the files, similar to a Compact and Repair in Access?
>
>

Friday, March 30, 2012

Renaming Database Files

Renaming database after log shipping.

Monday, March 26, 2012

Rename or purge my SQL Server 2000 transaction log file

Rename or purge my SQL Server 2000 transaction log file

Friday, March 23, 2012

rename logical file

rename logical file

rename logical file

Rename Files

Tuesday, March 20, 2012

Removing un necessary log

Removing un necessary log

Removing un necessary log

Friday, March 9, 2012

Removing old transaction log backup files

Removing old transaction log backup files

removing logs

Removing Log Shipping after disaster

Removing log files from SQL Database

Removing Log File

Removing Log File

Removing Log File

Removing a server

Blog Archive

About Me